SQL Syllabus

Module 1: Introduction to Databases and SQL

1. Understanding Databases
   • What is a Database?
   • Types of Databases (Relational vs. Non-Relational)
   • Introduction to RDBMS (Relational Database Management Systems)
2. SQL Basics
   • What is SQL?
   • Types of SQL Commands: DDL, DML, DQL, DCL, TCL
   • Installing and Setting Up SQL Tools (MySQL, SQL Server, PostgreSQL, etc.)

Module 2: Data Definition Language (DDL)

1. Creating Database Objects
   • CREATE DATABASE
   • CREATE TABLE with Constraints (Primary Key, Foreign Key, Unique, Not Null)
2. Modifying Database Structure
   • ALTER TABLE (Add/Modify/Drop Columns)
   • Adding and Removing Constraints
3. Deleting Database Objects
   • DROP TABLE
   • DROP DATABASE

Module 3: Data Manipulation Language (DML)

1. Inserting Data
   • INSERT INTO with Single and Multiple Rows
2. Updating Data
   • UPDATE with Conditions
3. Deleting Data
   • DELETE with Conditions

Module 4: Data Query Language (DQL)

1. Retrieving Data
   • SELECT Basics
   • Filtering with WHERE Clause
   • Sorting with ORDER BY
   • Limiting Results with LIMIT/TOP
2. Aggregate Functions
   • COUNT, SUM, AVG, MAX, MIN
   • Grouping Data with GROUP BY and HAVING
3. Joining Tables
   • Inner Join, Left Join, Right Join, Full Join
   • Self Join and Cross Join

Module 5: Advanced Querying Techniques

1. Subqueries
   • Single-Row and Multi-Row Subqueries
   • Correlated Subqueries
2. Set Operations
   • UNION, UNION ALL, INTERSECT, EXCEPT
3. Common Table Expressions (CTEs)
   • Writing and Using CTEs
4. Window Functions
   • Ranking (ROW_NUMBER, RANK, DENSE_RANK)
   • Aggregate Functions Over a Partition
   • Sliding Window Calculations (LEAD, LAG)

Module 6: Transaction Control

1. Understanding Transactions
   • ACID Properties of Transactions
2. Transaction Control Commands
   • COMMIT, ROLLBACK, SAVEPOINT

Module 7: Data Control Language (DCL)

1. Granting Permissions
   • GRANT Command
   • Role-Based Permissions
2. Revoking Permissions
   • REVOKE Command

Module 8: Advanced Database Concepts

1. Indexes
   • Clustered vs. Non-Clustered Indexes
   • Creating and Managing Indexes
2. Views
   • Creating, Altering, and Dropping Views
   • Materialized Views
3. Stored Procedures and Functions
   • Writing Stored Procedures with Parameters
   • Writing User-Defined Functions
4. Triggers
   • Creating and Managing Triggers

Module 9: Performance Tuning and Optimization

1. Query Optimization
   • Understanding Execution Plans
   • Using Indexes Effectively
   • Avoiding Common Performance Bottlenecks
2. Database Maintenance
   • Backups and Restore
   • Archiving and Partitioning Large Tables