Part A

Basics

• Database
• DBMS
• RDBMS

SQL Server Environment

• SQL Server 2005 and 2008
• New features in SQL Server 2005 and 2008
• SQL Server Business Intelligence Development Studio (BIDS)
  

SQL Language

• DQL/DRL (Data Query/Retrieval Language – Select Statement)
• DML (Data Manipulation Language – Insert, Update, Delete Statements)
• TCL (Transaction Control Language – Commit, Rollback Statements)
• DDL (Data Definition Language – Create, Drop, Alter Statements)
• DCL (Data Control Language – Grant Revoke Statements)

Data Integrity /Constraints

• Domain Integrity (Check, Default, Not Null Constraints)
• Entity Integrity (Primary Key, Unique Key Constraints)
• Referential Integrity (Foreign Key Constraint)

Database Design

• Normalization
• De Normalization
• Database Diagrams [E-R]

Operations

• Logical
• Special
• Set Operators

Group Functions

• Sum
• Average
• Min
• Max
• Count

Joins and Sub-Queries/Co-Related Sub Queries

• Simple Queries
• Sub – Queries / Co-Related Sub Queries
• Joins
• Types of Joins
  • Cross Join
  • Inner Join – (Equi, Non-Equi)
  • Outer Join – (left outer, right outer, full outer)
  • Self Join

Part B

Introduction to T-SQL

• Basic Programming (Variable, Initialization, Processing, Printing Variables)
• Conditional Statements (if, if...else, if...Else if...else if…else,while,case)

Transactions

• Auto Commit Transaction
• Implicit Transaction
• Explicit Transaction

Stored Procedures

• System Defined Stored Procedures
• Extended Stored Procedures
• User Defined Stored Procedures (In/Output parameters, Default values parameters, return statement in SP)

Function

• System Defined Functions
• User Defined Functions
  • Scalar Value
  • Table Value
  • Table Variable, Temporary Table

Views

• Creating View
• Creating View with Attributes
  • Check Option
  • With Encryption
  • With Schema Binding
  • Updatable Views
• Indexed Views
• Advantages of Views

Indexes

• Creating Index
• Types of Index
  • Clustered Index
  • Non Clustered Index
  • Unique Index
  • Composite Index

Cursors

• Declaring, Open, Fetch, Close , De-Allocate Cursor
• Types of Cursors
  • Forward only – Next
  • Static – Next, Prior, First, Last, Absolute, Relative
  • Dynamic – Except, Absolute
  • Keyset – Both Static & Dynamic

Triggers

• Creating Triggers
• Types of Triggers
  • For Triggers
  • Instead of Triggers

XML Integration

• For XML
• Open XML
• XML Data Type

Security

• Windows Authentication
• SQL Server Authentication
• Creating Login
• Introduction to Roles, Schema
• Dropping Login

Database Maintenance

• Backup Database
• Restore Database
• Generating T-SQL Scripts and Batches
• System Databases and System Tables

Advanced Topics

• CTE (Common Table Expression)
• CDC (Change Data Capture)
• Table Valued parameters in SP’s and Functions
• Temporary and Global Temporary Tables
• Variable Table
• Miscellaneous Topics like Sys.objects, Sys.Database, Information Schema etc…
• Performance Tunning.
• Database Administration like creating Users & Roles, Security and Backup of Databases and Shrinking Databases.
• New Features in SQL Server 2012