Course Curriculum
WEEK 1: Introduction to Databases & SQL Server
- Understanding Database Concepts and Models
- Relational Database Management Systems (RDBMS)
- SQL Server Architecture Overview
- Installing SQL Server and SSMS
- Navigating SQL Server Management Studio
- Creating Your First Database
Hands-on Lab: Setup SQL Server environment and create sample database
WEEK 2: Data Definition Language (DDL)
- Understanding Data Types in SQL Server
- CREATE, ALTER, DROP statements
- Creating Tables with Constraints
- Primary Keys and Foreign Keys
- UNIQUE, CHECK, and DEFAULT constraints
- Indexes: Clustered and Non-Clustered
Project: Design and create a complete database schema for an e-commerce system
WEEK 3: Data Manipulation Language (DML)
- INSERT statements and bulk inserts
- UPDATE statements with conditions
- DELETE and TRUNCATE operations
- Transaction control (BEGIN, COMMIT, ROLLBACK)
- MERGE statement for upsert operations
- OUTPUT clause for data capture
Hands-on Lab: Perform CRUD operations on sample database
WEEK 4: Querying Data - SELECT Mastery
- SELECT statement fundamentals
- WHERE clause with operators (AND, OR, NOT)
- Sorting with ORDER BY
- DISTINCT and TOP clauses
- Pattern matching with LIKE and wildcards
- NULL handling (IS NULL, COALESCE, ISNULL)
- CASE expressions for conditional logic
Project: Write 20+ complex queries solving real business problems
WEEK 5: Aggregate Functions & Grouping
- Aggregate functions: COUNT, SUM, AVG, MIN, MAX
- GROUP BY clause
- HAVING clause for filtering groups
- ROLLUP and CUBE for subtotals
- Window functions introduction (ROW_NUMBER, RANK, DENSE_RANK)
- PARTITION BY clause
Hands-on Lab: Create sales reports with aggregations and rankings
WEEK 6: Joins & Subqueries
- INNER JOIN for matching rows
- LEFT, RIGHT, and FULL OUTER JOINs
- CROSS JOIN and self-joins
- Subqueries in SELECT, WHERE, and FROM clauses
- Correlated subqueries
- EXISTS and NOT EXISTS operators
- Common Table Expressions (CTEs)
Project: Build complex multi-table reports using joins and CTEs
WEEK 7: Views, Stored Procedures & Functions
- Creating and managing Views
- Indexed views for performance
- Stored Procedures: Creation and execution
- Input/Output parameters
- User-Defined Functions (Scalar and Table-valued)
- Error handling with TRY-CATCH
- Dynamic SQL basics
Hands-on Lab: Create reusable stored procedures for common operations
WEEK 8: Triggers, Security & Administration
- DML Triggers (INSERT, UPDATE, DELETE)
- DDL Triggers for schema changes
- INSTEAD OF triggers
- Database security fundamentals
- Logins, Users, and Roles
- Backup and Restore operations
- Performance tuning basics
- Query execution plans
Final Project: Complete database solution with triggers, security, and documentation