Sql Server Syllabus

ABFIRST Pvt Ltd      

    SQL Server 

   Introduction

        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
• Non-Clustered 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