Mastering in SQL Server

Course details

COURSE CONTENT :

• Introduction To DBMS
  
• Introduction To SQL Server
  
• TSQL (Transact Structured Query Language)
  
• 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)
  
• Data Definition Language(DDL)
  
• Creating/Altering/Deleting  Database
  
• Constraints
  
• Not Null, Unique, Default and Check constraints
  
• Primary Key and Referential Integrity or foreign key constraints
  
• Delete and update Rules in Foreign Key
  
• On update/delete no action
  
• On update/delete cascade
  
• On update/delete set null
  
• On update/delete set default
  
• Data Manipulation Language(DML)
  
• 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
  
• Merge Statement***
  
• Data Query Language (DQL)
  
• Select
  
• Where clause
  
• Order By Clause
  
• Distinct Keyword
  
• Isnull() function
  
• Column & table aliases
  
• Operators:
  
• Arithmetic operators
  
• comparison operators
  
• Range operators
  
• List operators
  
• string /pattern matching operator
  
• Unknown value operators
  
• Logical operators
  
• Set operators
  
• 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
  
• Rank()
  
• Dense_rank()
  
• Row_Number()
  
• Ntile(n)
  
• Table Expressions
  
• Derived tables
  
• Common Table Expressions (CTE)
  
• Top n Clause
  
• Joins
  
• Inner Join
  
• Equi 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
  
• NonClustered Index
  
• Create , Alter and Drop Indexes
  
• Creating indexed view
  
• Security
  
• Login Creation
  
• SQL Server Authenticated Login
  
• Windows Authenticated Login
  
• User Creation
  
• Granting Permissions
  
• Revoking Permissions
  
• Schema
  
• Creating a schema
  
• Creating an object under schema
  
• Altering a schema
  
• Dropping a schema
  
• Providing security to schema
  
• Views
  
• Creating , Altering and Dropping Views
  
• Simple and Complex Views
  
• Updating(insert/delete/update) the views
  
• With check option
  
• Encryption and Schema Binding Options in creating views
  
• Transaction Management-(TCL)
  
• Explicit Transactions
  
• Begin Transaction
  
• Commit Transaction
  
• Rollback Transaction
  
• Save Transaction
  
• Implicit Transactions
  
• TSQL Programming (like PL/SQL in Oracle)
  
• 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
  
• Cursors with functions, procedures and triggers(will be dealt at the end)
  
• 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
  
• Diff between functions and procedures
  
• Triggers
  
• Purpose of Triggers
  
• Differences Between Stored Procedures and User Defined Functions and Triggers
  
• Creating, Altering and Dropping Triggers
  
• After Triggers
  
• Magic Tables
  
• Instead Of Triggers
  
• Updating the complex view using instead of triggers
  
• Exception Handling
  
• Implementing Exception Handling
  
• Try -catch mechanism
  
• Adding and removing User Defined Error Messages To And From SQL Server Error Messages List
  
• Raising Exceptions Manual
  
• Generating Errors through throws key word
  
• Normalization
  
• First Normal Form
  
• Second Normal Form
  
• Third Normal Form
  
• Boyce-Codd Normal Form
  
• Backup and Restore Of Database
  
• Attach and Detach of Database