Part 1: INTRODUCTION OF DBMS AND INSTALLATIONS PROCESS
-
Data, Databases and RDBMS Software’s
-
Advantages of SQL Server
-
Uses, Database Engine Many Component and OLTP Databases
-
BI Tool Components & Data Science Components
-
Uses of SQL, Real-time Usages, SQL versus Microsoft T-SQL [MSSQL]
-
Instance Name and Server Name, Features, Collation, Admin Users
-
Real-time Projects Included, Job Responsibilities; Versions and Editions of SQL Server
-
SQL Server and SSMS Installation, SQL Server - Career Options
-
SQL Server Pre-requisites: SQL Server 2019 / 2017/2014/2012 Installations
PART 2: BASICS SQL AND SSMS TOOL
-
SSMS, Different Connections (Local and Remote)
-
Sys Databases, Creating Objects with GUI
-
Insertions of data, Limit, Storage capacity
-
Sys Files, Commands with script, Select
-
Scanning and Retrieving of data(Basic Level)
PART 3: DATABASE CREATIONS AND COMANDS (DDL AND DML)
-
Creating DB, Entitles (Tables)
-
All about “SELECT”
-
Diff Data types in SQL, Select with simple and where conditions,
-
Different kinds of Operators OR, AND, IN, LIKE, NOT LIKE, Wild cards, IS, IS NOT OPERATORS, TOP AND DISTINCT keywords and Many more.
PART 4: EXPORT AND IMPORT (Different sources) AND SCHEMA, Different Modifications
-
Update, Delete and Truncate, Drop, Removing Schema,
-
Tables and Databases,
-
DDL Commands, Creation, Transfer of Table
-
GO Statement and Naming 2p 3p, Wizard(Export and Import)
-
Text Data types char and varchar
-
Nvarchar, Real time Uses, Batches, Real Time uses
-
All are based on Real Cases
PART 5: KEY CONCEPTS
-
Data Integrity
-
NULL, NOT NULL Property, PK, FK, CHECK , CK CONSTRAINTS, Default
-
ER DIGRAMS
-
Table Relation with Key, Identity property, increment
-
Adv. In Real Scenarios Cascading referential integrity constraint
-
Identity Column, Retrieving Identity Column values
PART 6: DATABASE OBJECT WITH BASIC PROCEDURE (Case 1)
-
Different types of Views with Real time examples,
-
Execution of views, UDF, Procedures, procedure with parameters
-
UD procedure and sys defined procedure like sp_
-
diff between procedure
-
Function and views with real Examples and performance wise comparisons
-
Advantages of stored procedures
-
Real Time Case Studies and Interview Preparations
PART 7: DIFF TYPES JOIN CONCEPT AND T-SQL QUERIES (CASE 1)
-
INNER AND OUTER JOINS WITH WHERE AND ON KEYWORDS,
-
FULL OUTER JOIN REAL TIME EXAMPLES,
-
MERGE JOINS, DIFFRENCES WITH JOINS SMALL AND BIG TABLES,
-
CROSS JOINS, ADVANCE JOINS
PART 8: GROUP BY AND T-SQL QUERIES (CASE 1)
-
Group by all clauses, Aggregate Functions,
-
GROUP BY Queries with Where Clause
-
Difference between where and having clause and uses with real cases
-
Grand Total, and all Roll Up and CUBE Function,
-
UNION AND UNION ALL, GROUPING Functions
-
Row status
PART 9: T-SQL QUERIES (CASE 2)
-
Joining with two and three different tables
-
Alias, where and on condition
-
Group by and Sub-Queries, Date and Time Function
-
Cast and convert, Datediff Function
-
String Functions, Trim, left, Right, Reverse, and Replace
PART 10: T-SQL QUERIES (CASE 3)
-
Complex Joining Tables in T-SQL; Using Joins with Sub Queries;
-
Using Joins with Nested Sub Queries
-
IIF ( ) and CASE Statement, CASE study in Joins
-
Joins in Group By, Rollup, Cube, Replacing Nulls
-
Is null, Coalesce Function
-
MERGE Statement For all commands like DML
-
Joins WHEN MATCHED & NOT MATCHED
-
DML Operations MERGE Statement
-
Row Number Generation in T-SQL
-
ROW_NUMBER () and RANK () Queries
-
DENSE_RANK, Sequence Identification; PARTITION
PART 11: STORED PROCEDURES (CASE 2)
-
Table valued parameters
-
IN and OUT Parameters
-
Dynamic Data with SPs
-
Variables (@variable)
-
Common Table Expression
-
Replacement @CTE with self Joins and
-
Sub-Queries
-
Recursive CTE
PART 11: STORED PROCEDURES (CASE 3)
-
Different Types of riggers (All DML AND DDL)
-
Views, Updatable Views
-
Uses of Triggers with Joins
-
Difference between procedures
-
views
-
Triggers, Cursors
-
Different kinds of Cursors
-
While Loop
-
Nested procedures
PART 12: TRANSACTIONS IN T-SQL
-
Different Kinds of Transactions (ACID)
-
Begin, Commit and Roll Back Transaction
-
Explicit and Implicit Transaction
-
Locking Process
-
Blocking and Deadlocking
-
No Lock
-
Real Time Case Studies, Project and Interview Preparations
PART 13: NORMAL FORMS IN T-SQL
-
Normal Forms
-
BCNF, 1NF, 2NF, 3NF, 4NF
-
Different kinds of Dependencies (Transitive) in Normalization Process
-
CK Keys
PART 14: FUNCTIONS (Part 2)
-
Different Types of Functions
-
Inline Function
-
Multi-Dimensional Function with Real case Examples
-
Pivot Function with Joins
-
Cascading with Delete and Update with Real Examples
-
BULK Insert, Different Files JSON File
-
XML with Different properties XML Path, Auto, Etc.
-
Temp Table with Real Examples
PART 15: INDEXING IN DATABASES
-
Architectures of Databases
-
Different kinds Files(.Ldf, .mdf)
-
Log Files, File Groups Etc.
-
Different INDEXES: Clustered and Non-Clustered
-
Online Indexes, Unique Indexes
-
Metalized Views
-
B-Tree Structure
-
Real Time Case Studies, Project and Interview Preparations
Module 2:- Query Optimization and Performance Tuning
CASE 1:- Method of Partitions and Audits
-
How to Edit Long Running Queries with DMVs, DMFs,
-
Monitor Tool
-
Plan Handling, Settings,
-
Uses of Partition and Non-Partitioning of Tables
-
Compression Method
CASE 2:- HOW TO MANAGE INDEXES
-
Statistics (Benefits, Creations, Updates, Management)
-
Fragmentation, Rebuild of Indexes
-
DMP
-
Online, Resume Option, Scanning Process
CASE 3:- FULL TEXT SEARCH (FTS) AND TABLE MEMORY
-
FTS Method,
-
Tuning Advisor
-
Database Catalog
-
ONLINE, PAUSE, RESUME, File Stream, File Group
-
Temp Table, Versioning of System
CASE 4:- LOCKING METHODS AND DIFFERENT LEVEL OF ISOLATIONS
-
Different Types of Locking
-
Shared Lock, Intent Lock, Schema Lock
-
Query Blocking
-
Read Committed
-
Read Uncommitted
-
Repeatable Read
-
Deadlock Method
-
Catching Blocking in Profiler Tools
-
Perfmon Tools