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