Here are 100 SQL practice problems with solutions, covering querying, joins, aggregations, subqueries, window functions, DDL/DML, and more. Assume the following sample schema unless otherwise specified:
- employees (employee_id, first_name, last_name, department_id, salary, hire_date, manager_id)
- departments (department_id, department_name, location_id)
- locations (location_id, city, country)
- orders (order_id, customer_id, order_date, total_amount)
- customers (customer_id, name, country, credit_limit)
- products (product_id, product_name, category_id, unit_price)
- categories (category_id, category_name)
- order_items (order_id, product_id, quantity, unit_price)
Also try it: 100 MySQL practice problems with solutions
1. Select All Columns from Employees
Write a query to retrieve all columns and rows from the employees table.
sql
SELECT * FROM employees;
2. Select Specific Columns
Retrieve only first_name, last_name, and salary from employees.
sql
SELECT first_name, last_name, salary FROM employees;
3. Alias Columns
Select employee full name as a column called full_name (first name + space + last name) and salary.
sql
SELECT first_name || ' ' || last_name AS full_name, salary FROM employees;
4. Filter with WHERE
List employees with salary greater than 50,000.
sql
SELECT * FROM employees WHERE salary > 50000;
5. Multiple Conditions (AND)
Find employees in department 10 with salary > 60,000.
sql
SELECT * FROM employees WHERE department_id = 10 AND salary > 60000;
6. OR Condition
List employees either in department 5 or with salary > 80,000.
sql
SELECT * FROM employees WHERE department_id = 5 OR salary > 80000;
7. Range with BETWEEN
Find employees hired between January 1, 2020 and December 31, 2022.
sql
SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2022-12-31';
8. IN Operator
List employees in departments 1, 3, 5.
sql
SELECT * FROM employees WHERE department_id IN (1, 3, 5);
9. Pattern Matching with LIKE
Find employees whose last name starts with ‘S’.
sql
SELECT * FROM employees WHERE last_name LIKE 'S%';
10. NULL Handling
List employees who have no manager (manager_id is NULL).
sql
SELECT * FROM employees WHERE manager_id IS NULL;
11. ORDER BY Ascending
Sort employees by salary ascending.
sql
SELECT * FROM employees ORDER BY salary ASC;
12. ORDER BY Descending and Multiple Columns
Sort employees by department_id ascending, then by hire_date descending within each department.
sql
SELECT * FROM employees ORDER BY department_id, hire_date DESC;
13. DISTINCT Values
Find all unique department IDs from employees.
sql
SELECT DISTINCT department_id FROM employees;
14. LIMIT / FETCH (Top N)
Retrieve the 5 highest‑paid employees (Oracle: use ROWNUM or FETCH FIRST; standard SQL).
sql
SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY;
15. Aggregate: COUNT
Count total number of employees.
sql
SELECT COUNT(*) FROM employees;
16. Count with Condition
Count how many employees have a manager (manager_id is not null).
sql
SELECT COUNT(manager_id) FROM employees;
17. SUM
Calculate total salary payout for department 20.
sql
SELECT SUM(salary) FROM employees WHERE department_id = 20;
18. AVG
Find the average salary of all employees.
sql
SELECT AVG(salary) FROM employees;
19. MIN and MAX
Find the minimum and maximum hire dates.
sql
SELECT MIN(hire_date) AS earliest_hire, MAX(hire_date) AS latest_hire FROM employees;
20. GROUP BY
List each department ID and the number of employees in it.
sql
SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id;
21. Group By with Multiple Columns
Count number of employees per department and job title (if job_title column exists, otherwise just department).
Assume a job_title column. If not, adapt using a generic concept.
sql
SELECT department_id, job_title, COUNT(*) FROM employees GROUP BY department_id, job_title;
22. HAVING Clause
Find departments with more than 5 employees.
sql
SELECT department_id, COUNT(*) AS cnt FROM employees GROUP BY department_id HAVING COUNT(*) > 5;
23. Aggregate and Filtered Group
For departments with average salary above 60,000, list department ID and average salary.
sql
SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id HAVING AVG(salary) > 60000;
24. Simple Subquery (Scalar)
Find employees who earn more than the average salary.
sql
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
25. Subquery with IN
Find employees who work in departments located in city ‘New York’.
sql
SELECT * FROM employees
WHERE department_id IN (
SELECT department_id FROM departments
WHERE location_id IN (
SELECT location_id FROM locations WHERE city = 'New York'
)
);
26. EXISTS
Retrieve all departments that have at least one employee.
sql
SELECT department_name
FROM departments d
WHERE EXISTS (
SELECT 1 FROM employees e WHERE e.department_id = d.department_id
);
27. Correlated Subquery
Find employees whose salary is above the average salary of their own department.
sql
SELECT employee_id, salary, department_id
FROM employees e
WHERE salary > (
SELECT AVG(salary) FROM employees WHERE department_id = e.department_id
);
28. INNER JOIN
List employees along with their department names.
sql
SELECT e.employee_id, e.first_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;
29. Left Join
Show all departments, including those with no employees, and the count of employees in each.
sql
SELECT d.department_name, COUNT(e.employee_id) AS emp_count FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_name;
30. Right Join (or just another left join, but to demonstrate)
List all employees and their department names, ensuring all employees appear even if department is missing.
sql
SELECT e.first_name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;
31. Full Outer Join
Write a full join between employees and departments to show all department‑employee relationships.
sql
SELECT e.first_name, d.department_name FROM employees e FULL OUTER JOIN departments d ON e.department_id = d.department_id;
32. Multiple Joins
List each order, customer name, and the total order amount.
sql
SELECT o.order_id, c.name, o.total_amount FROM orders o JOIN customers c ON o.customer_id = c.customer_id;
33. Three‑Table Join
Retrieve order ID, customer name, and product names for each order item.
sql
SELECT o.order_id, c.name, p.product_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN order_items oi ON o.order_id = oi.order_id JOIN products p ON oi.product_id = p.product_id;
34. Self‑Join: Manager Hierarchy
List employees together with their manager’s name.
sql
SELECT e.first_name AS employee, m.first_name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.employee_id;
35. UNION
Combine two SELECT statements that fetch product names from shirts and pants tables (assume similar structure) into one list.
sql
SELECT product_name FROM shirts UNION SELECT product_name FROM pants;
36. UNION ALL
List all product IDs from two tables, keeping duplicates.
sql
SELECT product_id FROM current_products UNION ALL SELECT product_id FROM historical_products;
37. INTERSECT
Find product IDs that exist in both current_products and discontinued_products.
sql
SELECT product_id FROM current_products INTERSECT SELECT product_id FROM discontinued_products;
38. EXCEPT / MINUS
Find employees who are not managers.
sql
SELECT employee_id FROM employees EXCEPT SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULL;
39. INSERT
Insert a new department ‘Training’ with department_id 50 and location_id 200.
sql
INSERT INTO departments (department_id, department_name, location_id) VALUES (50, 'Training', 200);
40. UPDATE
Give a 10% raise to all employees in department 10.
sql
UPDATE employees SET salary = salary * 1.10 WHERE department_id = 10;
41. DELETE
Delete employees with salary less than 20,000 (be careful with foreign keys).
sql
DELETE FROM employees WHERE salary < 20000;
42. Create Table
Create a projects table with columns: project_id (INT primary key), project_name (VARCHAR(100)), start_date (DATE), end_date (DATE), budget (DECIMAL(10,2)).
sql
CREATE TABLE projects (
project_id INT PRIMARY KEY,
project_name VARCHAR(100),
start_date DATE,
end_date DATE,
budget DECIMAL(10,2)
);
43. ALTER TABLE – Add Column
Add a column email (VARCHAR(100)) to the employees table.
sql
ALTER TABLE employees ADD email VARCHAR(100);
44. ALTER TABLE – Modify Column
Change the data type of salary to DECIMAL(10,2) (assume it was just INT).
sql
ALTER TABLE employees MODIFY salary DECIMAL(10,2);
45. Drop Column
Remove the nickname column from employees.
sql
ALTER TABLE employees DROP COLUMN nickname;
46. Primary Key Constraint
Define a composite primary key for order_items on (order_id, product_id) (if not already).
sql
ALTER TABLE order_items ADD PRIMARY KEY (order_id, product_id);
47. Foreign Key
Add a foreign key to employees that references departments(department_id).
sql
ALTER TABLE employees ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id);
48. Unique Constraint
Ensure employee_email column in employees is unique.
sql
ALTER TABLE employees ADD UNIQUE (employee_email);
49. Index Creation
Create an index on last_name for faster search.
sql
CREATE INDEX idx_last_name ON employees (last_name);
50. Drop Index
Remove an index.
sql
DROP INDEX idx_last_name;
51. View
Create a view high_earners that shows employees with salary > 100,000.
sql
CREATE VIEW high_earners AS SELECT * FROM employees WHERE salary > 100000;
52. Use View in Query
Select first name and salary from the view high_earners.
sql
SELECT first_name, salary FROM high_earners;
53. String Functions – UPPER/LOWER
Display employee first name in uppercase and last name in lowercase.
sql
SELECT UPPER(first_name), LOWER(last_name) FROM employees;
54. CONCAT
Full name as “LastName, FirstName”.
sql
SELECT last_name || ', ' || first_name AS full_name FROM employees;
55. SUBSTRING
Extract the first 3 characters of first_name.
sql
SELECT SUBSTRING(first_name FROM 1 FOR 3) FROM employees;
56. LENGTH
List employee names and their name length.
sql
SELECT first_name, LENGTH(first_name) AS name_length FROM employees;
57. Date Functions – YEAR, MONTH, DAY (pseudo: EXTRACT)
List employees hired in 2021.
sql
SELECT * FROM employees WHERE EXTRACT(YEAR FROM hire_date) = 2021;
58. Date Arithmetic – Add Days
Show orders and a new date 7 days after order_date as follow_up_date.
sql
SELECT order_id, order_date, order_date + INTERVAL '7' DAY AS follow_up_date FROM orders;
59. DATEDIFF
Find the number of days between order date and current date.
sql
SELECT order_id, CURRENT_DATE - order_date AS days_ago FROM orders;
60. COALESCE
List employees and their assigned department name; show ‘No Department’ if department_id is NULL.
sql
SELECT e.first_name, COALESCE(d.department_name, 'No Department') AS department FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;
61. NULLIF
In a report, show the manager name, displaying NULL if the manager is the employee itself (avoid self-loop).
sql
SELECT e.first_name, NULLIF(m.first_name, e.first_name) AS manager_name FROM employees e LEFT JOIN employees m ON e.manager_id = m.employee_id;
62. CASE Statement
Categorize employees by salary range: ‘Low’ (<50k), ‘Medium’ (50k-100k), ‘High’ (>100k).
sql
SELECT first_name, salary,
CASE
WHEN salary < 50000 THEN 'Low'
WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium'
ELSE 'High'
END AS salary_level
FROM employees;
63. CASE with Aggregate
Count employees in each custom salary bucket.
sql
SELECT
CASE
WHEN salary < 50000 THEN 'Low'
WHEN salary <= 100000 THEN 'Medium'
ELSE 'High'
END AS salary_range,
COUNT(*) AS count
FROM employees
GROUP BY 1;
64. Window Function – ROW_NUMBER
Assign a row number to each employee ordered by salary descending.
sql
SELECT employee_id, first_name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;
65. Window – RANK
Rank employees by salary within each department (ties share same rank, skip next rank).
sql
SELECT department_id, employee_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;
66. DENSE_RANK
Dense rank employees by hire date (no gaps).
sql
SELECT employee_id, hire_date,
DENSE_RANK() OVER (ORDER BY hire_date) AS seniority
FROM employees;
67. Window – LEAD
Show each employee’s salary and the next hired employee’s salary (within the whole company).
sql
SELECT employee_id, salary,
LEAD(salary) OVER (ORDER BY hire_date) AS next_hired_salary
FROM employees;
68. LAG
Compare each order total with the previous order total for the same customer.
sql
SELECT customer_id, order_id, total_amount,
LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount
FROM orders;
69. Window – SUM OVER for Running Total
Calculate a running total of salary expenses per department as hires accumulate.
sql
SELECT department_id, employee_id, hire_date, salary,
SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS running_total
FROM employees;
Also try it: 100 PostgreSQL practice problems with solutions
70. Moving Average
Compute a 3-month moving average of order total.
sql
SELECT order_date, total_amount,
AVG(total_amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM orders;
71. Top-N per Group (with ROW_NUMBER)
Find the highest‑paid employee in each department.
sql
SELECT department_id, employee_id, first_name, salary
FROM (
SELECT e.*,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
FROM employees e
) sub
WHERE rn = 1;
72. CTE – Simple
Write a CTE that produces employees with salary > 80k, then select count from it.
sql
WITH high_salary AS (
SELECT * FROM employees WHERE salary > 80000
)
SELECT COUNT(*) FROM high_salary;
73. Recursive CTE – Manager Chain
Retrieve the chain of command for a specific employee (assuming manager_id self-references).
sql
WITH RECURSIVE emp_hierarchy AS (
SELECT employee_id, first_name, manager_id, 0 AS level
FROM employees
WHERE employee_id = 100 -- starting employee
UNION ALL
SELECT e.employee_id, e.first_name, e.manager_id, eh.level + 1
FROM employees e
JOIN emp_hierarchy eh ON e.employee_id = eh.manager_id
)
SELECT * FROM emp_hierarchy;
74. Pivot – Simple (Use CASE + GROUP BY)
Show total sales per year as separate columns.
sql
SELECT
EXTRACT(YEAR FROM order_date) AS year,
SUM(CASE WHEN category = 'Electronics' THEN total_amount ELSE 0 END) AS electronics,
SUM(CASE WHEN category = 'Clothing' THEN total_amount ELSE 0 END) AS clothing
FROM orders
GROUP BY year;
75. Cross Join
Generate a set of all employee‑department pairs (Cartesian product).
sql
SELECT e.employee_id, d.department_name FROM employees e CROSS JOIN departments d;
76. Aggregate with NULLs
Count all rows vs. count of a nullable column; show the difference.
sql
SELECT COUNT(*) AS all_rows, COUNT(manager_id) AS have_manager FROM employees;
77. Handling Duplicates
Delete duplicate employees based on email, keeping the one with smallest employee_id.
sql
DELETE FROM employees
WHERE employee_id NOT IN (
SELECT MIN(employee_id)
FROM employees
GROUP BY email
);
78. EXPLAIN Plan
Show execution plan for a join query.
sql
EXPLAIN SELECT * FROM employees e JOIN departments d ON e.department_id = d.department_id;
79. Rollback
Demonstrate a transaction that inserts and then rolls back.
sql
START TRANSACTION; INSERT INTO employees (employee_id, first_name, last_name, salary) VALUES (999, 'Test', 'User', 50000); ROLLBACK;
80. COMMIT
sql
START TRANSACTION; UPDATE employees SET salary = salary * 1.05 WHERE department_id = 20; COMMIT;
81. Savepoints
Use savepoint to partially roll back.
sql
START TRANSACTION; UPDATE employees SET salary = salary + 5000 WHERE employee_id = 1; SAVEPOINT sp1; UPDATE employees SET salary = salary + 10000 WHERE employee_id = 2; ROLLBACK TO sp1; COMMIT;
82. Check Constraint
Add a constraint that salary must be > 0.
sql
ALTER TABLE employees ADD CONSTRAINT chk_salary_positive CHECK (salary > 0);
83. Default Value
Set default department_id for new employees to 1.
sql
ALTER TABLE employees ALTER COLUMN department_id SET DEFAULT 1;
84. Sequence (Oracle/PostgreSQL)
Create a sequence for employee IDs.
sql
CREATE SEQUENCE emp_seq START WITH 1000 INCREMENT BY 1;
85. Using a Sequence in INSERT
Insert a new employee using the sequence.
sql
INSERT INTO employees (employee_id, first_name, last_name) VALUES (NEXTVAL('emp_seq'), 'Jane', 'Doe');
86. Find Missing Indexes (Conceptual, using system tables – PostgreSQL example)
List tables with high sequential scans.
sql
SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE seq_scan > idx_scan;
87. Information Schema – List all tables
sql
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
88. Grant Privileges
Grant SELECT on employees to user hr_admin.
sql
GRANT SELECT ON employees TO hr_admin;
89. Revoke
Revoke INSERT on employees from hr_admin.
sql
REVOKE INSERT ON employees FROM hr_admin;
90. N‑Tiles
Divide employees into 3 salary groups using NTILE.
sql
SELECT employee_id, salary,
NTILE(3) OVER (ORDER BY salary DESC) AS salary_group
FROM employees;
91. Gaps and Islands – Identify Gaps in Serial IDs
Assume a table numbers with column id (integer). Find missing IDs.
sql
SELECT t1.id + 1 AS missing_start FROM numbers t1 LEFT JOIN numbers t2 ON t1.id + 1 = t2.id WHERE t2.id IS NULL AND t1.id < (SELECT MAX(id) FROM numbers);
92. List all Tables with Row Counts
sql
SELECT schemaname, relname, n_live_tup FROM pg_stat_user_tables;
93. HAVING with Multiple Criteria
Find departments where the maximum salary is at least twice the minimum salary.
sql
SELECT department_id FROM employees GROUP BY department_id HAVING MAX(salary) >= 2 * MIN(salary);
94. Correlated Update
Increase salary by 5% for employees whose salary is less than the department average.
sql
UPDATE employees e
SET salary = salary * 1.05
WHERE salary < (
SELECT AVG(salary) FROM employees WHERE department_id = e.department_id
);
95. Returning Clause (PostgreSQL)
Insert a new department and return the created ID.
sql
INSERT INTO departments (department_name, location_id) VALUES ('AI', 200)
RETURNING department_id;
96. IS DISTINCT FROM
Find rows where email has changed (compare old and new email with NULL-safe).
sql
SELECT a.employee_id, a.email AS old_email, b.email AS new_email FROM employee_audit a JOIN employees b ON a.employee_id = b.employee_id WHERE a.email IS DISTINCT FROM b.email;
97. Count by Date Trunc
Count orders per month.
sql
SELECT DATE_TRUNC('month', order_date) AS month, COUNT(*)
FROM orders
GROUP BY month
ORDER BY month;
98. Concatenate Strings with Aggregate (LISTAGG / STRING_AGG)
List all employee names in each department separated by comma.
sql
SELECT department_id,
STRING_AGG(first_name, ', ' ORDER BY first_name) AS employees_list
FROM employees
GROUP BY department_id;
99. Find Percentage of Total
Show each department’s salary total as a percentage of the overall salary total.
sql
SELECT department_id,
SUM(salary) * 100.0 / SUM(SUM(salary)) OVER () AS pct
FROM employees
GROUP BY department_id;
100. Complex Query: Customers who placed orders over $1000 and also live in same city as a store.
Assume a stores table with city. List customer names who ordered something >$1000 and they live in a city where at least one store exists.
sql
SELECT DISTINCT c.name FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE o.total_amount > 1000 AND c.city IN (SELECT city FROM stores);
Final Thought
And just like that — 100 SQL queries danced from your fingertips! 🎉
Can we pause for a happiness break? Because you genuinely deserve it. You didn’t just read about SELECT and JOIN — you lived them. You made data sing, tamed wild subqueries, made friends with GROUP BY, and maybe even fell a little in love with the elegant logic of a well‑crafted WHERE clause. Every solved problem sprinkled a bit more joy into your developer heart — the kind of joy that comes from truly getting it.
SQL isn’t cold or boring when you realize the power it gives you. It’s a superpower wrapped in simple words, and now it lives inside your happy, curious mind. Databases used to feel like dark caves — now they look like friendly libraries just waiting for your questions.
Keep this sunny confidence close. Come back and solve a few problems just for fun. Build little projects, query everything you can (responsibly!), and share the happiness with a friend just starting out. You’re not just someone who knows SQL — you’re a joyful query wizard, and the data world is your playground. Happy, happy coding!