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