SQL Server 2012 Course Content

SQL Server Training Course Prerequisite

• No Prior Experience is Presumed.

SQL Server Training Course Duration

• 30 Working days, daily one and half hours

SQL Server Training Course Overview(Course Content/Syllabus)

Introduction To DBMS

• File Management System And Its Drawbacks
• Database Management System (DBMS) and Data Models

·         Physical Data Models

·         Logical Data Models

·         Hierarchical Data Model (HDBMS)

·         Network Data Model (NDBMS)

·         Relational Data Model (RDBMS)

·         Object Data Model (ODBMS)

·         Object Relational Data Model (ORDBMS)

• Conceptual Data Models

·         Entity – Relationship (E-R) Model

Introduction To SQL Server 2012

• Advantages and Drawbacks Of SQL Server Compared To Oracle And DB2

·         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 To 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 and 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

·         Equi Join

·         Natural Join

·         Non-Equi Join

·         Self Join

·         Outer Join

·         Left Outer Join

·         Right Outer Join

·         Full Outer Join

·         Cross 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

Security

• Login Creation

·         SQL Server Authenticated Login

·         Windows Authenticated Login

·         User Creation

·         Granting Permissions

·         Revoking Permissions

·         Roles

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

Backup and Restore Of Database

Attach and Detach of Database
Normalization

Thanks

GSV