SQL SERVER



SYLLABUS:

    Concepts of DBMS
    • Introduction to DBMS
    • Data Models
    • HDBMS, NDBMS, RDBMS, ORDBMS
    • Entity–Relationship (E-R)
    • Normalization
    Introduction to SQL Server
    • Advantages & limitations
    • Login & Password
    • Server Type & Server Name
    • Authentication Modes
    – SQL Server, Windows
    • SQL Server Management Studio & Tools
    • Object Explorer
    • Query Editor
    Introduction To SQL
    • Types of SQL Commands
    • Data Types
    • DDL, DML, DQL, DCL, TCL
    • Databases
    – Create / Alter / Drop
    • Tables
    – Create / Alter / Drop
    Table Constraints
    • Not Null
    • Unique
    • Default
    • Check
    • Primary Key
    • R eferential Integrity or foreign key
    Data Manipulation Language (DML)
    • Insert / Update / Delete
    • Truncate & Drop
    • Create a Table From Another Table
    • Insert Rows From One Table To Another
    Data Query Language (DQL)
    • Simple Select
    • Select with row filters
    • Where clause with relational operators
    • Logical operators
    • Special operators
    • Between … and
    • In, Like
    • Is Null, Isnull()
    • Order By Clause
    • Distinct Keyword
    • Column aliases
    Transaction Management
    • User & Server schema
    • Implicit Transactions
    • Begin / Save Transaction
    • Commit / Rollback
    • Role of Log File in Transaction
    Management
    Built In Functions
    • Numeric/Character
    • Date/Conversion/Identity
    • Aggregate Functions
    – sum(), avg(), etc.
    • Group By & Having Clauses
    • Ranking Functions
    • Top Clause
    Set Operators
    • Union, Union all ,Intersect
    Joins
    • Simple
    • Equi / Non-Equi
    • Natural, Self
    • Inner, Outer
    • Cross or Cartesian Join
    Sub Queries
    • Single / Multi Row
    • Any, Some, ALL
    • Exists & Not Exists
    • Nested
    • Correlated
    Indexes
    • Create, Alter & Drop
    • Performance variation
    • Composite
    • Clustered / Non-Clustered
    DBA Activities
    • Authenticted Login
    – SQL Server/Windows
    • Create / Alter / Drop users
    • Granting & Revoking Permissions
    Views
    • Create / Alter / Drop Views
    • Simple, Complex Views
    • Indexed & Partition views
    • Encryption & Schema Binding Options
    TSQL Programming
    • SQL versus TSQL Programming
    • Introduction
    • Control statements – If,Case
    • While & other looping statements
    Cursors
    • Types of Cursors
    • Forward_Only, Scroll
    • Static, Dynamic
    • Keyset
    • Local & Global
    Stored Procedures
    • Creating/Calling Stored Procedures
    • Altering & Dropping
    • Optional, Input, Output Parameters
    • Permissions on Stored Procedures
    Exception Handling
    • User Defined Error Messages
    – Add & Remove
    • Raising Exceptions Manual
    User Defined Functions
    • Scalar Functions
    • Table Valued Functions
    • Permissions on User Defined Functions
    Triggers
    • Stored Procedures VS
    User Defined Functions VS Triggers
    • Creating Triggers
    • Altering Triggers
    • Dropping Triggers
    • Magic Tables
    • Instead of Triggers
    CLR Integration
    • What is CLR Integration?
    • Implementing CLR
    • A Simple Example
    Working with XML Data Type
    Database – Backup, Restore
    – Import, Export
    – Attach, Detach