SQL: Novice to Pro Guide

1. Novice Level (Data Retrieval)

1.1 Introduction

• What is SQL? Structured Query Language.
• RDBMS concepts (Tables, Rows, Columns, Keys).
• SELECT Statement: SELECT * FROM table;

1.2 Filtering & Sorting

• WHERE clause: =, <>, >, <, IN, BETWEEN, LIKE (% wildcard).
• ORDER BY: ASC, DESC.
• LIMIT (or TOP depending on DB).

1.3 Basic Manipulation (DML)

• INSERT INTO table (col1, col2) VALUES (val1, val2);
• UPDATE table SET col1 = val1 WHERE condition;
• DELETE FROM table WHERE condition; (Careful!)

2. Intermediate Level (Relational Power)

2.1 Joins (Merging Data)

• INNER JOIN: Matches in both.
• LEFT (OUTER) JOIN: All from left, match from right (or null).
• RIGHT JOIN: All from right.
• FULL JOIN: All from both.
• CROSS JOIN: Cartesian product.

2.2 Aggregation & Grouping

• Basic functions: COUNT(), SUM(), AVG(), MIN(), MAX().
• GROUP BY: Aggregating by category.
• HAVING: Filtering after aggregation (unlike WHERE).

2.3 Subqueries

• Nested queries in WHERE clause.
• Subqueries in FROM clause (Derived tables).
• EXISTS vs IN.

3. Advanced Level (Optimization & Logic)

3.1 Advanced Functions

• String Functions: CONCAT, SUBSTRING, TRIM, UPPER/LOWER.
• Date Functions: DATEADD, DATEDIFF, NOW().
• Case Statements: Logic in SELECT (CASE WHEN ... THEN ... ELSE ... END).

3.2 Window Functions (Analytic queries)

• RANK(), DENSE_RANK(), ROW_NUMBER().
• Aggregates over windows: SUM(val) OVER (PARTITION BY col ORDER BY col).
• LEAD(), LAG(): accessing previous/next rows.

3.3 Views & Indexes

• Views: Virtual tables for simplifying complex queries.
• Indexes: Creating B-Tree indexes for performance.
• Unique Indexes vs Composite Indexes.

4. Expert Level (Database Design & Tuning)

4.1 Stored Procedures & Functions

• Writing reusable code blocks.
• Parameters (IN/OUT).
• Triggers (actions on INSERT/UPDATE/DELETE).

4.2 Transactions (ACID)

• Atomicity, Consistency, Isolation, Durability.
• BEGIN TRANSACTION, COMMIT, ROLLBACK.
• Isolation Levels (Read Committed, Serializable, etc.).

4.3 Performance Tuning

• EXPLAIN PLAN: Analyzing query execution paths.
• Optimizing Joins and avoiding full table scans.
• Normalization (1NF, 2NF, 3NF) vs Denormalization.

4.4 Constraints

• Primary Key (PK), Foreign Key (FK).
• NOT NULL, UNIQUE, CHECK, DEFAULT.