SQL Server Training

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