preloader

khansqlserver@gmail.com

Complete SQL Server

Synopsis

This SQL Server training course has been designed  to introduce you to Relational Databases (RDBMS),  Structured Query Language (SQL)  and  T-SQL Programming.  This course develops your knowledge of writing SQL scripts and managing data stored in relational database. It is suitable for anyone who wants to write SQL scripts  using Conditional statements in Stored Procedures, User Defined  Functions, Triggers and  Cursors. The course contains elements of ANSI SQL using SQL Server as an example database. 
 

Prerequisites

There are no Prerequisites for this course.

 

Exercises

This course is instructor-led. Each section begins with an explanation and demonstration of a key concept. You then work through several practical exercises to apply your knowledge and test your understanding.  
 

Approach

All contents in this course are designed to make you a faster and efficient SQL developer.

Day 1/2

Data, Database, DBMS and Database Concepts

Introduction of MS SQL Server

Database and DBMS

Database Types

  • Flat-type databases
  • Hierarchical databases
  • Network databases
  • Relational databases

Rules of Cardinality

  • 1:1 Relation
  • 1:M or M:1 Relation
  • M:M  Relation

Data Models

  • Conceptual data model
  • Logical data model
  • Physical data model
  • Entity – Relationship Diagram

Day 3/4/5

Editions of MS SQL Server

Introduction to SQL Server Management Studio

Planning and Creating Databases

  • Creating  database
  • Understanding  SQL Architecture
  • Overview of SQL Language

Introduction of SQL and Transact-SQL (T-SQL)

  • Types of Tables

Data Definition Language (DDL)

  • USE Command, Create, Alter and Drop Tables using T-SQL

Introduction of Data Manipulation Language (DML)

  • Insert, Delete, Update, Merge  Statements
  • Difference between DELETE and TRUNCATE

Commit, Rollback, and Save  (TCL)

  • With (NoLock) Commands

Identity Specification, Computed columns

Day 6

Data types in SQL Server
  • Exact, Approximate, Unicode, Non-Unicode, DateTime, Binary, Others

Day 7

SQL Operations

  • Arithmetic, List, Pattern, Is Null, Range, and other Operators.
  • Set Clauses using Intersect, Union, Union All, Except Operators.

Day 8

SQL Clauses used to Filter Data Using

  • Distinct,  Top, Where, Group By, Having, Rollup, Cube, Order by, Offset, Fetch
  • Using Aggregate Function

Day 9/10

Constraints and Data Integrity.
Introduction of Database Constraint.

  • Creating default values
  • Creating Null values
  • Creating Check Constraint
  • Unique Key
  • Primary and Foreign Key
  • Composite Primary Key
  • Referential Integrity
  • Setting Cascade Update and Delete

Day 11/12

System Functions

  • Scalar Function
  • Ranking Function
  • Aggregate Function

Day 13/14

Joins using Multiple Tables

Inner join

  • Equi Join
  • Non Equi Join
  • Self Join

Outer join

  • Left, Right, Full Outer Join

Cross join

Day 15

  • Using Sub-queries
  • Simple Sub-queries
  • Correlated Subqueries
  • Using All, Any, Exists Predicates with Subqueries
  • Limitations of Sub-queries
  • CTE’s

Day 16

  • Creating a Schema
  • Referencing Schema Object
  • Creating Synonyms

Day 17/18

  • Views and Advantages of Views
  • Create View
  • Manipulating View
  • Schema Binding
  • Indexing Views
  • Views Restriction

Day 19/20

  • Understanding Indexes
      • 1. Table Scan, Index Scan, Index Seek
      • 2. Clustered Index, Non-Clustered Index
  • Creating Indexes
  • Managing Indexes
  • Migrating, Importing and Exporting
  • Generate SQL Scripts
  • Import and Export Wizard
  • Import Data into SQL Server
  • Bulk Insertions
  • Database Security
      • 1. Creating and Managing Logins
      • 2. Creating and Managing Database Users
      • 3. Using SQL Server Roles
  • Other functions
  • Quick Recap of SQL Summary and Query Solving

Day 21

  • T-SQL Programming
  • Variables
  • Control Flow – IF…ELSE
  • CASE Statement/Expression
  • The WHILE statement

Day 22

  • Using Cursors / Life cycle of Cursor
  • Types of Cursors
  • WHERE CURRENT OF
  • Restriction on Cursor Operation

Day 23

  • Stored Procedures
  • Creating SP
  • Parameters
  • 1. Input, Optional, No Parameters
  • Return Statements
  • Output Parameters
  • Handling Errors

Day 24/25/26

  • User Defined Functions (UDF’s)
  • Creating UDF’s
  • Returning Value from a function
  • Calling Scalar Function
  1. Inline table-valued function
  2. Multi-lined table-valued function
  • Return Statements
  • Output Parameters
  • Handling Errors

1. Try .. Catch Block
2. Raiserror, Throw
3. Working with Error Information

  • Transaction Handling
  • With Recompile
  •  

Day 27

  • Introduction to Triggers
  • FAQ’s in Interviews
  • Recap on entire SQL course contents
Register Now
Submit your review
1
2
3
4
5
Submit
     
Cancel

Create your own review

Average rating:  
 0 reviews