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: