SQL Server

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
o Inner Join
o Outer Join (Left, Right, Full)
o Self Join
o Cross Join
• Sub Queries
o Single Row Sub Queries
o Nested Sub Queries
o Co-Related Sub Queries
o 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

Locks and Concurrency