SQL Server Course Overview

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

Enquiry Form