Master SQL
Choose a quiz to begin. Reference the schema and sample data anytime using the panels below.
🟢
Easy Quiz
Core SELECT fundamentals — filtering, sorting, and basic clauses.
10 QUESTIONS · BEGINNER
Start →
🔵
Intermediate Quiz
Aggregates, JOINs, subqueries, and window functions.
10 QUESTIONS · INTERMEDIATE
Start →
⭐ Premium
🔒
Master Quiz
All questions combined — the full beginner-to-intermediate gauntlet.
40 QUESTIONS · ALL LEVELS
Start →
⭐ Premium
🔴
Advanced Quiz
CTEs, recursive queries, window frames, transactions, and performance. The deepest SQL challenge.
20 QUESTIONS · ADVANCED
Start →
┌────────────────────────────────────────────────────────────────────────┐ │ DATABASE: company_db │ ├─────────────────────┬──────────────────────┬───────────────────────────┤ │ employees │ departments │ projects │ ├─────────────────────┼──────────────────────┼───────────────────────────┤ │ id INT PK │ id INT PK │ id INT PK │ │ first_name VARCHAR│ name VARCHAR │ name VARCHAR │ │ last_name VARCHAR│ budget DECIMAL │ start_date DATE │ │ email VARCHAR│ location VARCHAR │ end_date DATE │ │ salary DECIMAL│ manager_id INT │ budget DECIMAL │ │ department_id INT │ │ status VARCHAR │ │ hire_date DATE │ │ department_id INT │ │ job_title VARCHAR│ │ │ │ manager_id INT │ │ │ └─────────────────────┴──────────────────────┴───────────────────────────┘ ┌──────────────────────────────────┬──────────────────────────────────────┐ │ employee_projects │ salary_history │ ├──────────────────────────────────┼──────────────────────────────────────┤ │ employee_id INT FK→employees │ employee_id INT FK→employees │ │ project_id INT FK→projects │ amount DECIMAL │ │ role VARCHAR │ effective_date DATE │ │ hours_worked INT │ end_date DATE │ └──────────────────────────────────┴──────────────────────────────────────┘
employees (sample rows):
id │ first_name │ last_name │ salary │ dept_id │ hire_date │ job_title │ manager_id
1 │ Alice │ Smith │ 95000 │ 1 │ 2019-03-15 │ Engineer │ 3
2 │ Bob │ Jones │ 72000 │ 2 │ 2020-07-01 │ Analyst │ 5
3 │ Carol │ White │ 110000 │ 1 │ 2017-01-10 │ Sr. Engineer │ NULL
4 │ Dan │ Brown │ 65000 │ 3 │ 2021-05-20 │ Coordinator │ 7
5 │ Eve │ Davis │ 88000 │ 2 │ 2018-11-30 │ Sr. Analyst │ NULL
6 │ Frank │ Miller │ 77000 │ 1 │ 2022-02-14 │ Engineer │ 3
7 │ Grace │ Lee │ 92000 │ 3 │ 2016-09-05 │ Ops Manager │ NULL
8 │ Henry │ Wang │ 58000 │ 2 │ 2023-01-18 │ Jr. Analyst │ 5
departments (sample rows):
id │ name │ budget │ location
1 │ Engineering │ 500000 │ New York
2 │ Analytics │ 300000 │ Chicago
3 │ Operations │ 200000 │ Austin
projects (sample rows):
id │ name │ status │ dept_id
1 │ Data Pipeline v2 │ Active │ 1
2 │ Dashboard Refresh │ Active │ 2
3 │ Cloud Migration │ Closed │ 1
employee_projects (sample rows):
employee_id │ project_id │ role │ hours_worked
1 │ 1 │ Lead │ 240
3 │ 1 │ Architect │ 180
5 │ 2 │ Analyst │ 120
6 │ 3 │ Engineer │ 90
📖 SQL Study Sheet — bookstore_db
All examples use a fictional bookstore database with tables:
books, authors, customers, orders, order_itemsbooks: id, title, author_id, genre, price, stock, published_year authors: id, name, country, birth_year customers: id, name, email, city, signup_date orders: id, customer_id, order_date, status, total_amount order_items: order_id, book_id, quantity, unit_price
① SELECT — Retrieve Data
Select all columns
SELECT * FROM books;
Select specific columns
SELECT title, price FROM books;
Rename with alias
SELECT title AS book_name, price AS cost FROM books;
② WHERE — Filter Rows
Exact match
SELECT * FROM books WHERE genre = 'Fiction';
Comparison operators
SELECT title, price FROM books WHERE price > 15.00;
Multiple conditions (AND / OR)
SELECT * FROM books WHERE genre = 'Fiction' AND price < 20; SELECT * FROM books WHERE genre = 'Fiction' OR genre = 'Mystery';
Negate with NOT
SELECT * FROM books WHERE NOT genre = 'Romance';
Range with BETWEEN
SELECT title, price FROM books WHERE price BETWEEN 10 AND 25;
Match a list with IN
SELECT * FROM books WHERE genre IN ('Fiction', 'Mystery', 'Sci-Fi');
Pattern match with LIKE
SELECT * FROM customers WHERE name LIKE 'J%'; -- starts with J SELECT * FROM books WHERE title LIKE '%Guide%'; -- contains Guide
NULL checks
SELECT * FROM books WHERE stock IS NULL; SELECT * FROM books WHERE stock IS NOT NULL;
③ ORDER BY — Sort Results
Ascending (default)
SELECT title, price FROM books ORDER BY price ASC;
Descending
SELECT title, price FROM books ORDER BY price DESC;
Sort by multiple columns
SELECT * FROM books ORDER BY genre ASC, price DESC;
④ LIMIT — Restrict Row Count
Return only N rows
SELECT * FROM books ORDER BY price DESC LIMIT 5;
⑤ Aggregate Functions
COUNT — total rows
SELECT COUNT(*) FROM orders; SELECT COUNT(DISTINCT customer_id) FROM orders;
SUM, AVG, MIN, MAX
SELECT SUM(total_amount) FROM orders WHERE status = 'Completed'; SELECT AVG(price) FROM books; SELECT MIN(price), MAX(price) FROM books WHERE genre = 'Fiction';
⑥ GROUP BY — Aggregate Per Group
Group and count
SELECT genre, COUNT(*) AS total_books FROM books GROUP BY genre;
Group and sum
SELECT customer_id, SUM(total_amount) AS total_spent FROM orders GROUP BY customer_id;
Filter groups with HAVING
SELECT genre, AVG(price) AS avg_price FROM books GROUP BY genre HAVING AVG(price) > 18;
💡 WHERE filters rows before grouping. HAVING filters groups after aggregation.
⑦ JOINs — Combine Tables
INNER JOIN — only matching rows
SELECT b.title, a.name AS author FROM books b INNER JOIN authors a ON b.author_id = a.id;
LEFT JOIN — all left rows, NULLs if no match
SELECT c.name, o.order_date FROM customers c LEFT JOIN orders o ON c.id = o.customer_id;
Multiple JOINs
SELECT o.id, c.name, b.title, oi.quantity FROM orders o JOIN customers c ON o.customer_id = c.id JOIN order_items oi ON o.id = oi.order_id JOIN books b ON oi.book_id = b.id;
💡 Use table aliases (b, a, c) to keep multi-join queries readable.
⑧ Subqueries
Subquery in WHERE
-- Books priced above average SELECT title, price FROM books WHERE price > (SELECT AVG(price) FROM books);
Subquery with IN
-- Customers who placed at least one order SELECT name FROM customers WHERE id IN (SELECT DISTINCT customer_id FROM orders);
Correlated subquery
-- Authors who have written more than 3 books SELECT name FROM authors a WHERE (SELECT COUNT(*) FROM books b WHERE b.author_id = a.id) > 3;
⑨ Window Functions
ROW_NUMBER — rank within a partition
SELECT title, genre, price, ROW_NUMBER() OVER (PARTITION BY genre ORDER BY price DESC) AS rank_in_genre FROM books;
RANK — same value = same rank (with gaps)
SELECT name, total_amount, RANK() OVER (ORDER BY total_amount DESC) AS spend_rank FROM customers JOIN orders ON customers.id = orders.customer_id;
Running total with SUM OVER
SELECT order_date, total_amount, SUM(total_amount) OVER (ORDER BY order_date) AS running_total FROM orders;
💡 Window functions compute across a set of rows related to the current row — without collapsing rows like GROUP BY does.
⑩ DISTINCT & CASE
DISTINCT — remove duplicates
SELECT DISTINCT genre FROM books; SELECT DISTINCT city FROM customers;
CASE — conditional logic
SELECT title, price,
CASE
WHEN price < 10 THEN 'Budget'
WHEN price BETWEEN 10 AND 25 THEN 'Standard'
ELSE 'Premium'
END AS price_tier
FROM books;
⑪ Query Execution Order
SQL runs clauses in this order — not the order you write them:
① FROM / JOIN → ② WHERE → ③ GROUP BY → ④ HAVING → ⑤ SELECT → ⑥ DISTINCT → ⑦ ORDER BY → ⑧ LIMIT
① FROM / JOIN → ② WHERE → ③ GROUP BY → ④ HAVING → ⑤ SELECT → ⑥ DISTINCT → ⑦ ORDER BY → ⑧ LIMIT
⑫ CTEs — Common Table Expressions
Basic CTE
WITH high_earners AS ( SELECT * FROM employees WHERE salary > 90000 ) SELECT first_name, salary FROM high_earners ORDER BY salary DESC;
Multiple CTEs
WITH dept_avg AS ( SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id ), above_avg AS ( SELECT e.first_name, e.salary, d.avg_sal FROM employees e JOIN dept_avg d ON e.department_id = d.department_id WHERE e.salary > d.avg_sal ) SELECT * FROM above_avg ORDER BY salary DESC;
💡 CTEs are temporary named result sets. They make complex queries readable by breaking them into named steps. They exist only for the duration of the query.
⑬ Recursive CTEs
Walk an org chart (manager → employee hierarchy)
WITH RECURSIVE org_chart AS ( -- Base case: top-level employees (no manager) SELECT id, first_name, manager_id, 0 AS depth FROM employees WHERE manager_id IS NULL UNION ALL -- Recursive case: find direct reports SELECT e.id, e.first_name, e.manager_id, oc.depth + 1 FROM employees e JOIN org_chart oc ON e.manager_id = oc.id ) SELECT depth, first_name FROM org_chart ORDER BY depth, first_name;
💡 Recursive CTEs have two parts: a base case (anchor) and a recursive step joined with UNION ALL. They repeat until no new rows are produced.
⑭ Advanced Window Functions
LAG / LEAD — access previous or next row
SELECT first_name, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary, LEAD(salary) OVER (ORDER BY hire_date) AS next_salary FROM employees;
NTILE — divide rows into N buckets
SELECT first_name, salary, NTILE(4) OVER (ORDER BY salary DESC) AS quartile FROM employees;
FIRST_VALUE / LAST_VALUE
SELECT first_name, department_id, salary,
FIRST_VALUE(salary) OVER (
PARTITION BY department_id ORDER BY salary DESC
) AS top_salary_in_dept
FROM employees;
Sliding window frame
-- 3-row moving average of salary by hire date
SELECT first_name, hire_date, salary,
AVG(salary) OVER (
ORDER BY hire_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS moving_avg
FROM employees;
💡 Window frames (ROWS BETWEEN ...) let you define exactly which rows are included in each calculation relative to the current row.
⑮ PIVOT-style Queries
Conditional aggregation (cross-tab)
-- Count employees per department broken out by job seniority SELECT department_id, COUNT(CASE WHEN job_title LIKE 'Jr%' THEN 1 END) AS junior, COUNT(CASE WHEN job_title LIKE 'Sr%' THEN 1 END) AS senior, COUNT(CASE WHEN job_title LIKE '%Manager%' THEN 1 END) AS managers FROM employees GROUP BY department_id;
💡 Standard SQL has no PIVOT keyword. Use CASE inside aggregate functions to rotate rows into columns manually.
⑯ EXISTS & NOT EXISTS
EXISTS — rows where a subquery returns any result
-- Employees assigned to at least one project SELECT first_name FROM employees e WHERE EXISTS ( SELECT 1 FROM employee_projects ep WHERE ep.employee_id = e.id );
NOT EXISTS — rows where subquery returns nothing
-- Employees with no projects at all SELECT first_name FROM employees e WHERE NOT EXISTS ( SELECT 1 FROM employee_projects ep WHERE ep.employee_id = e.id );
💡 EXISTS is often faster than IN on large datasets because it short-circuits — it stops as soon as it finds the first matching row.
⑰ Set Operations
UNION — combine and deduplicate
SELECT first_name FROM employees WHERE department_id = 1 UNION SELECT first_name FROM employees WHERE salary > 90000;
UNION ALL — combine keeping duplicates
SELECT 'Employee' AS type, first_name FROM employees WHERE manager_id IS NULL UNION ALL SELECT 'Contractor', first_name FROM employees WHERE job_title LIKE '%Lead%';
INTERSECT — rows in both results
-- Employees in dept 1 who also earn over 90k SELECT id FROM employees WHERE department_id = 1 INTERSECT SELECT id FROM employees WHERE salary > 90000;
EXCEPT — rows in first but not second
-- Employees in dept 1 who do NOT earn over 90k SELECT id FROM employees WHERE department_id = 1 EXCEPT SELECT id FROM employees WHERE salary > 90000;
💡 All set operations require the same number of columns and compatible data types. Column names come from the first SELECT.
⑱ String & Date Functions
String functions
SELECT
UPPER(first_name),
LOWER(last_name),
LENGTH(email),
SUBSTRING(job_title, 1, 3) AS title_abbr,
TRIM(' hello ') AS trimmed,
REPLACE(email, '@gmail', '@company') AS updated_email
FROM employees;
Date functions
SELECT hire_date, YEAR(hire_date) AS hire_year, MONTH(hire_date) AS hire_month, DATEDIFF(NOW(), hire_date) AS days_employed, DATE_ADD(hire_date, INTERVAL 1 YEAR) AS first_anniversary FROM employees;
⑲ Indexes & Query Performance
Create an index
-- Speed up lookups on department_id CREATE INDEX idx_emp_dept ON employees(department_id); -- Composite index for queries filtering on both columns CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary);
EXPLAIN — inspect query execution plan
EXPLAIN SELECT * FROM employees WHERE department_id = 1 AND salary > 80000;
💡 Indexes speed up reads but slow down writes. Add them on columns used in WHERE, JOIN ON, and ORDER BY. Avoid over-indexing — each index takes storage and must be updated on every INSERT/UPDATE/DELETE.
⑳ Transactions & ACID
Basic transaction
BEGIN; UPDATE employees SET salary = salary * 1.10 WHERE department_id = 1; INSERT INTO salary_history (employee_id, amount, effective_date) SELECT id, salary, NOW() FROM employees WHERE department_id = 1; COMMIT;
Rollback on error
BEGIN; DELETE FROM employee_projects WHERE project_id = 3; DELETE FROM projects WHERE id = 3; -- Something went wrong: ROLLBACK;
💡 ACID: Atomicity (all or nothing), Consistency (data stays valid), Isolation (transactions don't interfere), Durability (committed data survives crashes). COMMIT saves. ROLLBACK undoes.
┌────────────────────────────────────────────────────────────────────────┐ │ DATABASE: company_db │ ├─────────────────────┬──────────────────────┬───────────────────────────┤ │ employees │ departments │ projects │ ├─────────────────────┼──────────────────────┼───────────────────────────┤ │ id INT PK │ id INT PK │ id INT PK │ │ first_name VARCHAR│ name VARCHAR │ name VARCHAR │ │ last_name VARCHAR│ budget DECIMAL │ start_date DATE │ │ email VARCHAR│ location VARCHAR │ end_date DATE │ │ salary DECIMAL│ manager_id INT │ budget DECIMAL │ │ department_id INT │ │ status VARCHAR │ │ hire_date DATE │ │ department_id INT │ │ job_title VARCHAR│ │ │ │ manager_id INT │ │ │ └─────────────────────┴──────────────────────┴───────────────────────────┘ ┌──────────────────────────────────┬──────────────────────────────────────┐ │ employee_projects │ salary_history │ ├──────────────────────────────────┼──────────────────────────────────────┤ │ employee_id INT FK→employees │ employee_id INT FK→employees │ │ project_id INT FK→projects │ amount DECIMAL │ │ role VARCHAR │ effective_date DATE │ │ hours_worked INT │ end_date DATE │ └──────────────────────────────────┴──────────────────────────────────────┘
employees (sample rows):
id │ first_name │ last_name │ salary │ dept_id │ hire_date │ job_title │ manager_id
1 │ Alice │ Smith │ 95000 │ 1 │ 2019-03-15 │ Engineer │ 3
2 │ Bob │ Jones │ 72000 │ 2 │ 2020-07-01 │ Analyst │ 5
3 │ Carol │ White │ 110000 │ 1 │ 2017-01-10 │ Sr. Engineer │ NULL
4 │ Dan │ Brown │ 65000 │ 3 │ 2021-05-20 │ Coordinator │ 7
5 │ Eve │ Davis │ 88000 │ 2 │ 2018-11-30 │ Sr. Analyst │ NULL
6 │ Frank │ Miller │ 77000 │ 1 │ 2022-02-14 │ Engineer │ 3
7 │ Grace │ Lee │ 92000 │ 3 │ 2016-09-05 │ Ops Manager │ NULL
8 │ Henry │ Wang │ 58000 │ 2 │ 2023-01-18 │ Jr. Analyst │ 5
departments (sample rows):
id │ name │ budget │ location
1 │ Engineering │ 500000 │ New York
2 │ Analytics │ 300000 │ Chicago
3 │ Operations │ 200000 │ Austin
projects (sample rows):
id │ name │ status │ dept_id
1 │ Data Pipeline v2 │ Active │ 1
2 │ Dashboard Refresh │ Active │ 2
3 │ Cloud Migration │ Closed │ 1
employee_projects (sample rows):
employee_id │ project_id │ role │ hours_worked
1 │ 1 │ Lead │ 240
3 │ 1 │ Architect │ 180
5 │ 2 │ Analyst │ 120
6 │ 3 │ Engineer │ 90
Q1 OF 22
Beginner
💡
Explanation
📊 My Dashboard
🔖 Saved Questions
0 saved
SHOW:
🏆 Quiz History
⭐ Premium
🔥 Wrong Answer Heatmap
⭐ Premium
Your 10 most-missed questions
📈 Topic Breakdown
⭐ Premium
Accuracy by SQL topic — worst first
Final Score
0/22
0
Correct
0
Wrong
0%
Accuracy
SHOW: