SQL Server 

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.

SQL Server Training Prerequisite

• No Prior Experience is Presumed.

SQL Server Training Course Objective

• 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
• Design a database
• Maintain databases, tables, and sequences with SQL statements
• Create and manage views
• Ensure the integrity of multiple, related database updates by using transactions
• Retrieve data using cursors
• Manage binary data using BLOBs

SQL Server Course Duration

• 30 Working days, daily 1.30 hours

SQL Server Training Course Content

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

• 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

CLR Integration

• What is CLR Integration and The Steps For Implementing It
• A Simple Example With CLR Integration

Working With XML Data Type

Backup and Restore Of Database

Attach and Detach of Database

Normalization