MS SQL DEVELOPER

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications which may run either on the same computer or on another computer across a network.

Pre-Requisites:

Basic understanding of computer programming language, internet, database, especially RDBMS is very helpful.

Part A
Basics

    • Database
    • DBMS
    • RDBMS

SQL Server Environment

    • SQL Server 2008 and 2008R2
    • New features in SQL Server 20012 and 2014
    • 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 – (Equip, Non-Equip)
    • 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, and Information Schema etc…
    • Performance Tunning.
    • Database Administration like creating Users & Roles, Security and Backup of Databases and Shrinking Databases.
    • New Features in MS SQL Server 2014