Sql Server Syllabus
Overview
This SQL Server training teaches developers all the Transact-SQL skills they need to create
database objects like Tables, Views, Stored procedures & Functions and triggers in SQL Server.
Gives idea about writing Queries & Sub-queries, working with Joins, etc. As well as database
management skills like backup, restore, etc.
Course Goals
Learn Database models
Overview of SQL Server Management Studio and Transact-SQL language
Master writing simple and complex queries that retrieve data from the database
Calculate information across result sets using aggregate queries (sum, min, max, avg,
etc.)
Insert, update, and delete data
Retrieve data from tables
Joins
Sub-queries
Working with Data Types
Procedure and Functions
Understand the different Views
Working with Triggers
Create and manage views
Ensure the integrity of multiple, related database updates by using transactions
Retrieve data using cursors
Introduction To SQL Server
o Advantages and Drawbacks Of SQL Server Compared To Oracle And DB2
o Connecting To Server
Server Type
Server Name
Authentication Modes
o Sql Server Authentication Mode
o Windows Authentication Mode
o Login and Password
o Sql Server Management Studio and Tools In Management Studio
Object Explorer
Object Explorer Details
Query Editor
TSQL (Transact Structured Query Language)
Introduction To TSQL
History and Features of TSQL
Types Of TSQL Commands
Data Definition Language (DDL)
Data Manipulation Language (DML)
Database
o Creating Database
o Altering Database
o Deleting Database
o Constrains
Procedural Integrity Constraints
Declarative Integrity Constraints
Not Null, Unique, Default and Check constraints
Primary Key and Referential Integrity or foreign key
constraints
o Data Types In TSQL
o Table
o Creating Table
o Altering Table
o Deleting Table
Data Manipulation Language
o Insert
Identity
Creating A Table From Another Table
Inserting Rows From One Table To Another
o Update
Computed Columns
o Delete
Truncate
Differences Between Delete and Truncate
Data Query Language (DQL)
o Select
o Where clause
o Order By Clause
o Distinct Keyword
o Isnull() function
o Column aliases
o Predicates
Between … And
In
Like
Is Null
Built In Functions
o Scalar Functions
o Numeric Functions
o Character Functions
o Conversion Functions
o Date Functions
o 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
o Union
o Intersect
o Except
Joins
o Inner Join
Equi Join
Natural Join
Non-Equi Join
Self Join
o Outer Join
Left Outer Join
Right Outer Join
Full Outer Join
Cross Join
Sub Queries
o Single Row Sub Queries
o Multi Row Sub Queries
Any or Some
ALL
Nested Sub Queries
Co-Related Sub Queries
Exists and Not Exists
Indexes
o Clustered Index
o NonClustered Index
o Create , Alter and Drop Indexes
o Using Indexes
Views
o Purpose Of Views
o Creating , Altering and Dropping Indexes
o Simple and Complex Views
Transaction Management
o Introduction
o Begin Transaction
o Commit Transaction
o Rollback Transaction
TSQL Programming
o Drawbacks Of TSQL that leads to TSQL Programming
o Introduction To TSQL Programming
o Control statements In TSQL Programming
Conditional Control Statements
If
Case
o Looping Control Statements
o While
Cursors
o Working With Cursors
o Types Of Cursors
o Forward_Only and Scroll Cursors
o Static, Dynamic and Keyset Cursors
o Local and Global Cursors
Stored Procedure
o Creating , Altering and Dropping
o Optional Parameters
o Input and Output Parameters
User Defined Functions
o Creating, Altering and Dropping
o Types Of User Defined Functions
Scalar Functions
Table Valued Functions
Inline Table Valued Functions
Multi Statement Table Valued Functions
Triggers
o Purpose of Triggers
o Differences Between Stored Procedures and User Defined Functions and
Triggers
o Creating, Altering and Dropping Triggers
o Magic Tables
o Instead Of Triggers
Exception Handling
o Implementing Exception Handling
o Adding and removing User Defined Error Messages To And From SQL
Server Error Messages List
o Raising Exceptions Manual
Working With XML Data Type
Backup and Restore Of Database
Attach and Detach of Database
Normalization
