1. Introduction to SQL
- What is SQL?
- History and importance of SQL.
- Difference between SQL and other programming languages.
- RDBMS overview.
- Popular SQL databases: MySQL, PostgreSQL, SQLite, SQL Server, Oracle DB.
2. SQL Basics
- Understanding tables, rows, and columns.
- Data types in SQL: numeric, string, date/time.
- Basic SQL syntax and structure.
- Writing your first query (`SELECT`, `FROM`).
3. DDL (Data Definition Language)
- `CREATE`: Creating databases and tables.
- `ALTER`: Modifying existing table structures.
- `DROP`: Deleting tables or databases.
- `TRUNCATE`: Clearing all data from a table.
- Understanding constraints: `NOT NULL`, `UNIQUE`, `DEFAULT`, `CHECK`, `PRIMARY KEY`, `FOREIGN KEY`.
4. DML (Data Manipulation Language)
- `INSERT`: Adding new data.
- `UPDATE`: Modifying existing data.
- `DELETE`: Removing data.
- Best practices: Using `WHERE` to avoid unintentional updates/deletes.
5. DQL (Data Query Language)
- Retrieving data with `SELECT`.
- Using `DISTINCT` to filter duplicates.
- Filtering results with `WHERE`:
- Operators: `=`, `<`, `>`, `BETWEEN`, `LIKE`, `IN`.
- Sorting data with `ORDER BY`.
- Limiting results with `LIMIT` (or `FETCH FIRST`).
6. Joins in SQL
- Inner Join: Combining rows with matching values in both tables.
- Left Join (or Left Outer Join): Retrieving all rows from the left table.
- Right Join (or Right Outer Join): Retrieving all rows from the right table.
- Full Join (or Full Outer Join): Retrieving matching rows and non-matching rows from both tables.
7. Grouping and Aggregating Data
- `GROUP BY`: Grouping rows that have the same values.
- Aggregate functions: `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`.
- Filtering grouped data with `HAVING`.
- Example: Counting total orders per customer.
8. Subqueries
- What is a subquery?
- Types of subqueries: Single-row subqueries, Multiple-row subqueries.
- Using subqueries with `SELECT`, `INSERT`, `UPDATE`, `DELETE`.
- Correlated vs Non-correlated subqueries.
9. Indexing in SQL
- What are indexes and why are they used?
- Creating indexes using `CREATE INDEX`.
- Types of indexes: Unique index, composite index.
- Indexing performance considerations.
10. Normalization
- What is normalization and why is it important?
- Normal forms: 1NF, 2NF, 3NF.
- Understanding functional dependencies.
- Example of normalization in a database schema.
11. Views in SQL
- What is a view?
- Creating and managing views: `CREATE VIEW`, `DROP VIEW`.
- Advantages of using views.
- Views vs Tables.
12. Transactions in SQL
- What is a transaction?
- ACID properties: Atomicity, Consistency, Isolation, Durability.
- Managing transactions with `BEGIN`, `COMMIT`, `ROLLBACK`.
- Locking and isolation levels.
13. Stored Procedures
- What are stored procedures?
- Creating and executing stored procedures: `CREATE PROCEDURE`, `CALL`.
- Advantages of stored procedures.
- Parameterized procedures and their use cases.
14. Triggers
- What are triggers?
- Types of triggers: `BEFORE`, `AFTER`, `INSTEAD OF`.
- Creating triggers with `CREATE TRIGGER`.
- Use cases of triggers in data validation and auditing.
15. Advanced SQL Concepts
- Window functions: `ROW_NUMBER`, `RANK`, `DENSE_RANK`, `PARTITION BY`.
- Recursive CTEs: Writing recursive queries.
- JSON data handling: `JSON_EXTRACT`, `JSON_OBJECT`.
- Using advanced database functions for analytics.
16. SQL Optimization Techniques
- Using `EXPLAIN` or `EXPLAIN PLAN` for query analysis.
- Best practices for indexing.
- Avoiding unnecessary joins and subqueries.
- Optimizing performance with `WHERE` and `LIMIT`.
17. Real-World Applications
- Building databases for e-commerce platforms.
- Analyzing sales and marketing data.
- Writing complex queries for ERP systems.
- Case studies and practical projects.
18. Resources and Practice
- Free practice databases: Chinook, Sakila.
- Tools for SQL practice: MySQL Workbench, SQLite.
- Online platforms: db-fiddle, W3Schools.
- Sample projects and challenges.
