SQL Server Mastery

Module 1: Introduction to SQL Server (2 hours)

·       What is a relational database?

·       Introduction to SQL Server and SSMS (SQL Server Management Studio)

·       SQL Server editions and components

·       Installing SQL Server and tools

Module 2: Database Design & Modeling (5 hours)

·       Tables, columns, data types

·       Primary and foreign keys

·       ER diagrams and normalization (1NF to 3NF)

·       Designing relational schemas

·       Naming conventions and best practices

Module 3: SQL Fundamentals (8 hours)

·       SELECT statements and column aliases

·       WHERE clause and operators

·       Sorting using ORDER BY

·       Filtering with BETWEEN, IN, LIKE, NULL

·       DISTINCT, TOP, and pagination with OFFSET-FETCH

·       Basic string and date functions

Module 4: Joins & Set Operations (6 hours)

·       INNER JOIN

·       LEFT, RIGHT, FULL OUTER JOIN

·       CROSS JOIN and SELF JOIN

·       UNION, UNION ALL, INTERSECT, EXCEPT

·       Subqueries and derived tables

Module 5: Aggregation & Grouping (4 hours)

·       GROUP BY, HAVING

·       Aggregate functions: COUNT, SUM, AVG, MIN, MAX

·       Conditional aggregation with CASE

·       ROLLUP and CUBE

Module 6: DML – Data Manipulation (4 hours)

·       INSERT INTO, INSERT SELECT

·       UPDATE and DELETE

·       Transactions and BEGIN, COMMIT, ROLLBACK

·       Identity columns and sequences

Module 7: DDL – Data Definition (4 hours)

·       Creating and altering tables

·       Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT

·       Dropping and renaming objects

·       Temporary and global temp tables

Module 8: Stored Procedures & User-defined Functions (6 hours)

·       Creating and executing stored procedures

·       Input/output parameters

·       Scalar and table-valued functions

·       Error handling with TRY...CATCH

·       Benefits and limitations

Module 9: Views, Indexing & Performance (7 hours)

·       Creating and using views

·       Indexed views and limitations

·       Index types: clustered, non-clustered, unique, filtered

·       Index tuning tips

·       Execution plans and performance analysis

Module 10: Security & Permissions (4 hours)

·       SQL Server authentication modes

·       Users, roles, and permissions

·       GRANT, DENY, REVOKE

·       Schema-level security

·       SQL Injection and prevention

Module 11: Triggers & Transactions (4 hours)

·       DML Triggers: AFTER, INSTEAD OF

·       Nested and recursive triggers

·       Transactions and locking mechanisms

·       Isolation levels

Module 12: Advanced T-SQL & Optimization (4 hours)

·       Common Table Expressions (CTEs)

·       Window functions: ROW_NUMBER, RANK, LEAD, LAG

·       Pivot and unpivot

·       Dynamic SQL

·       Query optimization techniques

Module 13: Capstone Project + Case Studies (2 hours)

Project: Company HR Database

·       Schema design

·       Create views, stored procedures, triggers

·       Run complex queries and reports

·       Performance tuning tasks