Introduction To DBMS
- 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
- Connecting To Server
- Server Type
- Server Name
- Authentication Modes
- SQL Server Authentication Mode
- Windows Authentication Mode
- Login & Password
- SQL Server Management Studio & Tools In Management Studio
- TSQL (Transact-Structured Query Language)
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Query Language (DQL)
- Data Control Language (DCL)
- Transaction Control Language (TCL)
Database
- Altering Database
- Deleting Database
- Constrains
- Procedural Integrity Constraints
- Declarative Integrity Constraints
- Not Null, Unique, Default & check constraints
- Primary Key and Referential Integrity or Foreign Key constraints
- Data Types In SQL
- 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 & truncate
Data Query Language (DQL)
- Select
- Where clause
- Order By Clause
- Distinct Keyword
- ISNULL() function
- Column aliases
- Predicates
- Between … And
- In
- Like
- Built-in functions
- Scalar functions
- Numeric functions
- Character functions
- Conversion functions
Date Functions
- Aggregate functions
- Group by and having clauses
- Over(partition by …) clause
- Ranking functions
- Common table expressions (CTE)
- Top n Clause
Set Operators
- Union
- Union All
- 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 & Drop Indexes
- Using Indexes
Views
- Purpose Of Views
- 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
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
Triggers
- Purpose of Triggers
- Differences Between Stored Procedures and User Defined Functions & Triggers
- Creating, Altering and Dropping Triggers
