SQL Topics
Quick Nav
| Topic | Notes |
|---|---|
| Basics | [[Basics]] — SELECT, WHERE, ORDER BY, DISTINCT, NULL, CASE WHEN, data types |
| JOINs | [[JOINs]] — INNER, LEFT, RIGHT, FULL OUTER, CROSS, self-join, anti-join |
| Aggregates | [[Aggregates]] — COUNT, SUM, AVG, GROUP BY, HAVING vs WHERE |
| Subqueries & CTEs | [[Subqueries & CTEs]] — correlated subqueries, EXISTS, CTEs, recursive CTEs |
| Window Functions | [[Window Functions]] — ROW_NUMBER, RANK, LAG/LEAD, running totals, frames |
| Indexes & Performance | [[Indexes & Performance]] — B-tree, composite, partial, EXPLAIN ANALYZE, N+1 |
| Transactions | [[Transactions]] — ACID, isolation levels, locking, deadlocks, MVCC |
| String & Date Functions | [[String & Date Functions]] — LIKE, COALESCE, date math, CAST, JSON |
| UNION & Set Ops | [[UNION & Set Operations]] — UNION vs UNION ALL, INTERSECT, EXCEPT |
| Pivoting | [[Pivoting]] — CASE-based pivot, CROSSTAB, unpivot |
| DDL/DML/DCL/TCL | [[DDL DML DCL TCL]] — statement classification, DELETE vs TRUNCATE vs DROP |
| Constraints | [[Constraints]] — NOT NULL, UNIQUE, PK, FK, CHECK, DEFAULT, AUTO_INCREMENT |
| Views/Procs/Triggers | [[Views, Procedures, Triggers]] — views, stored procs, cursors, triggers |
| SQL Security | [[SQL Security]] — SQL injection defense, privileges, GRANT/REVOKE, least privilege |
| Clean SQL | [[Clean SQL]] — formatting, naming, CTEs over subqueries, index-friendly patterns |
| Interview prep | [[SQL for Interviews]] — JOINs, window functions, CTEs, indexes, ACID, Instacart case |
Practice Progress
DataLemur Learn SQL Course
✅ Course completed (2026-05-10) — full curriculum covered:
- SELECT, WHERE, ORDER BY, DISTINCT
- JOINs (INNER/LEFT/RIGHT/FULL OUTER/SELF)
- Aggregates + GROUP BY / HAVING
- Subqueries + CTEs
- Window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, running totals)
LeetCode SQL 50 — DONE (53 problems, 2026-05-10)
| Status | Count |
|---|---|
| ✅ Done | 53 |
| 🔄 In progress | 0 |
| ⬜ Not started | 0 |
- 0175 combine-two-tables
- 0176 second-highest-salary
- 0178 rank-scores
- 0180 consecutive-numbers
- 0181 employees-earning-more-than-their-managers
- 0184 department-highest-salary
- 0185 department-top-three-salaries
- 0196 delete-duplicate-emails
- 0262 trips-and-users
- 0550 game-play-analysis-iv
- 0570 managers-with-at-least-5-direct-reports
- 0577 employee-bonus
- 0584 find-customer-referee
- 0585 investments-in-2016
- 0595 big-countries
- 0601 human-traffic-of-stadium
- 0602 friend-requests-ii-who-has-the-most-friends
- 0608 tree-node
- 0610 triangle-judgement
- 0619 biggest-single-number
- 0620 not-boring-movies
- 0626 exchange-seats
- 1045 customers-who-bought-all-products
- 1068 product-sales-analysis-i
- 1070 product-sales-analysis-iii
- 1075 project-employees-i
- 1141 user-activity-for-the-past-30-days-i
- 1148 article-views-i
- 1164 product-price-at-a-given-date
- 1174 immediate-food-delivery-ii
- 1193 monthly-transactions-i
- 1204 last-person-to-fit-in-the-bus
- 1251 average-selling-price
- 1280 students-and-examinations
- 1321 restaurant-growth
- 1327 list-the-products-ordered-in-a-period
- 1341 movie-rating
- 1378 replace-employee-id-with-the-unique-identifier
- 1484 group-sold-products-by-the-date
- 1517 find-users-with-valid-e-mails
- 1527 patients-with-a-condition
- 1581 customer-who-visited-but-did-not-make-any-transactions
- 1633 percentage-of-users-attended-a-contest
- 1661 average-time-of-process-per-machine
- 1667 fix-names-in-a-table
- 1683 invalid-tweets
- 1729 find-followers-count
- 1731 the-number-of-employees-which-report-to-each-employee
- 1757 recyclable-and-low-fat-products
- 1789 primary-department-for-each-employee
- 1907 count-salary-categories
- 1934 confirmation-rate
- 1978 employees-whose-manager-left-the-company
Concept Checklist
Fundamentals
- Execution order — FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY ✅ 2026-05-01
- INNER / LEFT / RIGHT / FULL OUTER JOIN
- GROUP BY + HAVING vs WHERE ✅ 2026-05-01
- Subqueries vs CTEs — when to use each
Window Functions
- ROW_NUMBER() — unique rank per partition ✅ 2026-05-10
- RANK() / DENSE_RANK() — tie handling difference ✅ 2026-05-10
- LAG() / LEAD() — access previous/next row ✅ 2026-05-10
- SUM() / AVG() OVER () — running totals ✅ 2026-05-10
Classic Problems (must solve cold)
- Second highest salary (LC 176) ✅ 2026-05-10
- Top N per group (LC 185 dept top-3) ✅ 2026-05-10
- Consecutive numbers (LC 180) ✅ 2026-05-10
- Department highest salary (LC 184) ✅ 2026-05-10
- Delete duplicate emails (LC 196) ✅ 2026-05-10
Advanced
- Indexes — B-tree, composite column order, partial index
- EXPLAIN ANALYZE — seq scan vs index scan
- N+1 problem — detect and fix
- ACID — verbal 4-sentence explanation
- DELETE vs TRUNCATE vs DROP — DML/DDL, rollback, speed
- PRIMARY KEY vs UNIQUE — NULLs, count per table, clustering
- FOREIGN KEY — ON DELETE CASCADE/SET NULL/RESTRICT, must-index
- SQL Injection — parameterized queries, why blocklists fail
- Views vs Materialized Views — refresh model, when to use each
- Triggers — BEFORE/AFTER, NEW/OLD, why use sparingly
Resources
| Resource | Use for | URL |
|---|---|---|
| DataLemur | Concept + company-tagged practice | datalemur.com |
| LeetCode SQL 50 | Volume + classic patterns | leetcode.com/studyplan/top-sql-50 |
| Mode Analytics | Window functions deep read | mode.com/sql-tutorial |
| Use The Index Luke | Indexing theory | use-the-index-luke.com |