Course Overview
This training teaches developers all the Transact-SQL skills they need to create database objects like Tables, Views, Stored procedures and Functions and triggers in SQL Server.
Gives idea about writing Queries and Sub-queries, working with Joins, etc. As well as database management skills like backup, restore, etc.

SQL Server Training Course Content

Introduction To SQL Server

• Advantages and Drawbacks Of SQL Server 

• Connecting To Server
  • Server Type
  • Server Name
  • Authentication Modes
    • SQL Server Authentication Mode
    • Windows Authentication Mode
    • Login and Password
    • SQL Server Management Studio and Tools In Management Studio
      • Object Explorer
      • Object Explorer Details
      • Query Editor

TSQL (Transact-Structured Query Language)

Introduction TSQL

• History and Features of TSQL
• Types Of TSQL Commands

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Query Language (DQL)
• Data Control Language (DCL)
• Transaction Control Language (TCL)
• Database
  • Creating Database
  • Altering Database
  • Deleting Database
  • Constrains
  • Procedural Integrity Constraints
  • Declarative Integrity Constraints
  • Not Null, Unique, Default and Check constraints
  • Primary Key and Referential Integrity or foreign key constraints
  • Data Types In TSQL
  •  Table
  • Creating Table
  • Altering Table
  • Deleting Table

Data Manipulation Language

• Insert

• Identity
• Creating A Table From Another Table
• Inserting Rows From One Table To Another
• Update
• Computed Columns
•  Delete
•  Truncate
• Differences Between Delete & Truncate

Data Query Language (DQL)

• Select
• Where clause
• Order By Clause
• Distinct Keyword
• Isnull() function
• Column aliases
• Predicates

• Between … And
• In
• Like
• Is Null

Built In Functions

• Scalar Functions

• Numeric Functions
• Character Functions
• Conversion Functions
• Date Functions
• Aggregate Functions
  • Convenient Aggregate Functions
  • Statistical Aggregate Functions
  • Group By and Having Clauses
  • Super Aggregates
  • Over(partition by …) Clause
  • Ranking Functions
    • Common Table Expressions (CTE)

 Top n Clause

Set Operators

• Union
• Intersect
• Except

Joins

• Inner Join

• Self Join
• Outer Join
  • Left Outer Join
  • Right Outer Join
  • Full Outer Join

Sub Queries

• Single Row Sub Queries
• Multi Row Sub Queries

• Any or Some
• ALL
• Nested Sub Queries
• Co-Related Sub Queries
• Exists and Not Exists

Indexes

• Clustered Index
• NonClustered Index
• Create , Alter and Drop Indexes
• Using Indexes

Views

• Purpose Of Views
• Creating , Altering and Dropping Indexes
• Simple and Complex Views
• Encryption and Schema Binding Options in creating views

Transaction Management

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

TSQL Programming

• Drawbacks Of TSQL that leads to TSQL Programming
• Introduction To TSQL Programming
• Control statements In TSQL Programming

• Conditional Control Statements
  • If
  • Case

• Looping Control Statements

• While

Cursors

• Working With Cursors
• Types Of Cursors

• Forward_Only and Scroll Cursors
• Static, Dynamic and Keyset Cursors
• Local and Global Cursors

Stored Sub Programs

• Advantages Of Stored Sub Programs compared to Independent SQL Statements
• Stored Procedures

• Creating , Altering and Dropping
• Optional Parameters
• Input and Output Parameters
• Permissions on Stored Procedures

User Defined Functions

• Creating, Altering and Dropping
• Types Of User Defined Functions
• Scalar Functions
• Table Valued Functions
• Inline Table Valued Functions
• Multi Statement Table Valued Functions
• Permissions On User Defined Functions

Triggers

• Purpose of Triggers
• Differences Between Stored Procedures and User Defined Functions and Triggers
• Creating, Altering and Dropping Triggers
• Magic Tables
• Instead Of Triggers

Exception Handling

• Implementing Exception Handling
• Adding and removing User Defined Error Messages To And From SQL Server Error Messages List
• Raising Exceptions Manual

 Security

• Login Creation

• SQL Server Authenticated Login
• Windows Authenticated Login
• User Creation
• Granting Permissions
• Revoking Permissions
• Roles