SQL Server Training

SQL Server Course 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.

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