SQL SERVER

SQL SERVER Online Training

Introduction to DBMS

  • File Management System And Its Drawbacks
  • Intro to Database Management System (DBMS)
  • Types of DBMS systems
  • Network DBMS (NDBMS)
  • Hierarchical DBMS (HDBMS)
  • Relational DBMS (RDBMS)

Introduction to SQL SERVER

  • SQL SERVER Versions and History
  • Connecting To Server
    • Authentication Modes
      • SQL SERVER Authentication Mode
      • Windows Authentication Mode
      • SQL Server Management Studio and Tools

SQL SERVER Database Design

  • Database Creation using GUI
  • Database Creation using T-SQL Scripts
  • DB Design using Files and File groups
  • Pages and Extents
  • Data File and Log File and Its features

Business Integrity

  • Why Business Integrity
  • Features of Business Integrity
  • Null / Not Null
  • Default
  • Candidate Keys
  • Primary Key
  • Unique Key / Alternate Keys
  • Foreign Key
  • Check Constraints
  • Identity

SQL Server Data Types

  • Unicode Vs. Non-Unicode
  • Char,  Varchar and Varchar (Max)
  • nChar,  nVarchar and nVarchar (Max)
  • Numerical Data Types
  • Currency Data Types
  • Binary Data Types
  • GUID Data Type
  • Timestamp Data Type
  • Bit Data Type
  • Spatial Data Type

Introduction to SQL

  • Types Of SQL Commands
  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)
  • Data Control Language (DCL)
  • Transaction Control Language (TCL)

Queries

  • Simple Queries
  • Filter Rows using Where Clause
  • Filter Columns using Select Clause
  • Sorting Data
  • Concatenate Strings and Columns
  • Working with Columns and Constants
  • Group by and Aggregations
  • Having Clause
  • Top (n) and Top (n) Percent
  • Union and Union All
  • Intersect and Except
  • ISNULL()
  • Joins
    • Inner Join
    • Outer Join (Left, Right, Full)
    • Self Join
    • Cross Join
  • Sub Queries
  • Single Row Sub Queries
  • Nested Sub Queries
  • Co-Related Sub Queries
  • Exists and Not Exists Functions
  • Derived Tables
  • Cube and Rollup Operators
  •  

  • Built In Functions

    • String Functions
    • Data Conversion Functions
    • Date and Time Functions
    • Aggregate Functions
      • Ranking Functions
        • Common Table Expressions (CTE)

  • Indexes

    • Heap Vs Indexes
    • Clustered Index
    • Non-Clustered Index
    • Filtered Index
    • Covered Index
    • Create , Alter and Drop Indexes
    • Statistics
    • Performance Tuning of Queries

  • Views

    • Purpose Of Views
    • Creating , Altering and Dropping Views
    • Updateable and Non-Updateable Views
    • Encryption and Schema Binding, with Check Option Options in Views
    • Indexed Views

  • Intro to T-SQL Programming

    • Declaring Variables
    • Local and Global Variables
    • Local and Global Temp Tables
    • IF…. ELSE
    • While Loop
    • Return Statement
    • Table Variable Vs. Temp Tables
    • Exception Handling with Try… Catch Blocks

  • Stored Procedures

    • Need for Stored Procedures
    • Creating , Altering and Dropping Stored Procedures
    • Optional Parameters
    • Input and Output Parameters
    • Nested Stored Procedures
    • Re-Compiler Stored Procedures
    • Advantages and Disadvantages Stored Procedures
    • Writing Complex Stored Procedures
    • Writing Stored Procedures with Cursors

  • Cursors

    • Need for Cursors
    • Types Of Cursors
    • Forward_Only and Scroll Cursors
    • Static, Dynamic and Keyset Cursors
    • Visibility of Cursors

  • User Defined Functions

    • Need for UDFs
    • Creating, Altering and Dropping
    • Types Of User Defined Functions
      • Scalar Functions
        • Inline Table Valued Functions
        • Multi Statement Table Valued Functions

  • Triggers

    • Purpose of Triggers
    • Creating, Altering and Dropping Triggers
    • Types of Triggers
    • For or After Triggers
    • Instead Of Triggers
    • Magic Tables (Inserted, Deleted)
    • Rollback in Triggers

  • Transactions

    • Introduction
    • Begin Transaction
    • Commit Transaction
    • Rollback Transaction
    • Save Transaction
    • Role Of Log File In Transaction Management
    • Implicit Transactions
    • Explicit Transactions