Here are 100 MySQL practice problems with solutions, using a sample schema similar to many standard SQL exercises:
- 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, city, credit_limit)
- order_items (order_id, product_id, quantity, unit_price)
- products (product_id, product_name, category_id, unit_price)
- categories (category_id, category_name)
The solutions are written in MySQL syntax and leverage MySQL-specific functions where appropriate.
Also try it: 100 SQL practice problems with solutions
1. Select All Employees
Write a query to retrieve all columns and rows from the employees table.
sql
SELECT * FROM employees;
2. Select Specific Columns
List only first_name, last_name, and salary of all employees.
sql
SELECT first_name, last_name, salary FROM employees;
3. Concatenate Columns (Full Name)
Show each employee’s full name as full_name (first name followed by a space and last name) and their salary.
sql
SELECT CONCAT(first_name, ' ', last_name) AS full_name, salary FROM employees;
4. Alias for Computed Column
Return employee id and annual salary (monthly salary * 12) as annual_salary.
sql
SELECT employee_id, salary * 12 AS annual_salary FROM employees;
5. Filter by Salary
List employees earning more than 50,000.
sql
SELECT * FROM employees WHERE salary > 50000;
6. Multiple Conditions (AND)
Find employees in department 10 with a salary greater than 60,000.
sql
SELECT * FROM employees WHERE department_id = 10 AND salary > 60000;
7. OR Condition
List employees who work in department 5 or earn more than 80,000.
sql
SELECT * FROM employees WHERE department_id = 5 OR salary > 80000;
8. Between Operator
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';
9. IN Operator
List employees who belong to departments 1, 3, or 5.
sql
SELECT * FROM employees WHERE department_id IN (1, 3, 5);
10. Pattern Matching (LIKE)
Find employees whose last name starts with ‘S’.
sql
SELECT * FROM employees WHERE last_name LIKE 'S%';
11. NULL Handling
List employees who do not have a manager (manager_id is NULL).
sql
SELECT * FROM employees WHERE manager_id IS NULL;
12. Order By (Ascending)
Sort employees by salary from lowest to highest.
sql
SELECT * FROM employees ORDER BY salary ASC;
13. Order By Multiple Columns
Sort by department_id ascending and within each department by hire_date newest first.
sql
SELECT * FROM employees ORDER BY department_id, hire_date DESC;
14. Distinct Values
Display all unique department IDs from the employees table.
sql
SELECT DISTINCT department_id FROM employees;
15. LIMIT – Top N Rows
Get the three highest‑paid employees.
sql
SELECT * FROM employees ORDER BY salary DESC LIMIT 3;
16. Offset (Pagination)
Return the second page of results with 10 employees per page (skip first 10).
sql
SELECT * FROM employees ORDER BY employee_id LIMIT 10 OFFSET 10;
17. Count All Rows
Count the total number of employees.
sql
SELECT COUNT(*) AS total_employees FROM employees;
18. Count Non‑NULL
Count how many employees have a manager (manager_id is not NULL).
sql
SELECT COUNT(manager_id) AS managed_employees FROM employees;
19. Sum
Calculate total salary paid in department 20.
sql
SELECT SUM(salary) FROM employees WHERE department_id = 20;
20. Average
Find the average salary of all employees.
sql
SELECT AVG(salary) FROM employees;
21. Minimum and Maximum
Show the earliest and latest hire dates.
sql
SELECT MIN(hire_date) AS first_hire, MAX(hire_date) AS last_hire FROM employees;
22. Group By (Single Column)
List each department ID and the number of employees in it.
sql
SELECT department_id, COUNT(*) AS num_employees FROM employees GROUP BY department_id;
23. Group By with Multiple Columns
Count the number of employees per department and job title (if your employees table has job_title; if not, use department_id and manager_id).
sql
-- Assuming a job_title column exists SELECT department_id, job_title, COUNT(*) FROM employees GROUP BY department_id, job_title;
24. HAVING Clause
Show only departments that have more than 5 employees.
sql
SELECT department_id, COUNT(*) AS cnt FROM employees GROUP BY department_id HAVING cnt > 5;
25. Having with Average
Find departments where the average salary exceeds 60,000.
sql
SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id HAVING avg_sal > 60000;
26. Simple Subquery (Scalar)
Return employees who earn above the overall average salary.
sql
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
27. Subquery with IN
Find employees working in departments located in ‘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'
)
);
28. EXISTS
List 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
);
29. Correlated Subquery
Show employees whose salary is greater than the average salary of their own department.
sql
SELECT employee_id, first_name, salary, department_id
FROM employees e
WHERE salary > (
SELECT AVG(salary) FROM employees
WHERE department_id = e.department_id
);
30. INNER JOIN
Return employee names along with their department names.
sql
SELECT e.first_name, e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;
31. LEFT JOIN
List all departments with the count of employees in each (including departments with zero).
sql
SELECT d.department_name, COUNT(e.employee_id) AS employee_count FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_name;
32. RIGHT JOIN (or another LEFT JOIN)
Show all employees and their department names (include employees without a department).
sql
SELECT e.first_name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;
33. Three‑Table Join
List orders with customer names and the city of the customer.
sql
SELECT o.order_id, c.name, c.city, o.total_amount FROM orders o JOIN customers c ON o.customer_id = c.customer_id;
34. Self‑Join: Manager Hierarchy
For each employee, show their name and their manager’s name (if any).
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 a list of all customer names and all employee last names into a single column.
sql
SELECT name AS person_name FROM customers UNION SELECT last_name FROM employees;
36. UNION ALL
Combine product IDs from order_items and product IDs from wishlist (assume wishlist table). Keep duplicates.
sql
SELECT product_id FROM order_items UNION ALL SELECT product_id FROM wishlist;
37. INTERSECT (MySQL 8.0.31+)
Find product IDs that appear in both order_items and a featured_products table.
sql
SELECT product_id FROM order_items INTERSECT SELECT product_id FROM featured_products;
38. EXCEPT (MySQL 8.0.31+ as EXCEPT, or earlier use NOT IN)
List employees who are not managers.
sql
SELECT employee_id, first_name FROM employees EXCEPT SELECT DISTINCT manager_id, '' FROM employees WHERE manager_id IS NOT NULL;
(For MySQL <8.0.31, you’d use a NOT IN or NOT EXISTS.)
39. INSERT a New Row
Insert a new department: id 50, name ‘AI Lab’, location 200.
sql
INSERT INTO departments (department_id, department_name, location_id) VALUES (50, 'AI Lab', 200);
40. UPDATE
Give a 10% salary increase to all employees in department 10.
sql
UPDATE employees SET salary = salary * 1.10 WHERE department_id = 10;
41. DELETE
Remove employees with salary less than 20,000 (and ensure they have no orders if foreign keys exist).
sql
DELETE FROM employees WHERE salary < 20000;
42. Create Table
Create a projects table with columns: project_id INT PRIMARY KEY AUTO_INCREMENT, project_name VARCHAR(100), start_date DATE, end_date DATE, budget DECIMAL(10,2).
sql
CREATE TABLE projects (
project_id INT PRIMARY KEY AUTO_INCREMENT,
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 salary column to allow DECIMAL(10,2) and not NULL.
sql
ALTER TABLE employees MODIFY salary DECIMAL(10,2) NOT NULL;
45. Drop Column
Remove the nickname column from the employees table.
sql
ALTER TABLE employees DROP COLUMN nickname;
46. Primary Key Constraint
Add a primary key to order_items on (order_id, product_id) if not already set.
sql
ALTER TABLE order_items ADD PRIMARY KEY (order_id, product_id);
47. Foreign Key
Make employees.department_id a foreign key referencing departments(department_id).
sql
ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY (department_id) REFERENCES departments(department_id);
Also try it: 100 PostgreSQL practice problems with solutions
48. Unique Constraint
Ensure email values in employees are unique.
sql
ALTER TABLE employees ADD UNIQUE (email);
49. Create Index
Create an index on last_name for faster searches.
sql
CREATE INDEX idx_lastname ON employees (last_name);
50. Drop Index
Remove the index idx_lastname.
sql
DROP INDEX idx_lastname ON employees;
51. View
Create a view high_earners that shows employees earning over 100k.
sql
CREATE VIEW high_earners AS SELECT employee_id, first_name, last_name, salary FROM employees WHERE salary > 100000;
52. Query a View
Retrieve all columns from high_earners.
sql
SELECT * 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_WS
Create a full address: “city, country” from the locations table using CONCAT_WS.
sql
SELECT CONCAT_WS(', ', city, country) AS address FROM locations;
55. SUBSTRING
Extract the first three characters of first_name.
sql
SELECT SUBSTRING(first_name, 1, 3) FROM employees;
56. LENGTH
List employees and the length of their first name.
sql
SELECT first_name, LENGTH(first_name) AS name_len FROM employees;
57. Date Functions – Extract Year
Find employees hired in 2020 using YEAR().
sql
SELECT * FROM employees WHERE YEAR(hire_date) = 2020;
58. Date Arithmetic
Add 7 days to each order date to show a follow‑up date.
sql
SELECT order_id, order_date,
DATE_ADD(order_date, INTERVAL 7 DAY) AS follow_up
FROM orders;
59. DATEDIFF
Calculate how many days ago each order was placed (compared to today).
sql
SELECT order_id, DATEDIFF(NOW(), order_date) AS days_ago FROM orders;
60. IFNULL (or COALESCE)
List employees and their department name; show ‘No Dept’ if department is unknown.
sql
SELECT e.first_name, IFNULL(d.department_name, 'No Dept') AS department FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;
61. NULLIF
Report manager names; show NULL if the manager is the same as the employee.
sql
SELECT e.first_name AS employee,
NULLIF(m.first_name, e.first_name) AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
62. CASE Expression
Classify employees into salary tiers: ‘Low’ (< 50k), ‘Medium’ (50–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_tier
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 tier,
COUNT(*) AS cnt
FROM employees
GROUP BY tier;
64. Window Function – ROW_NUMBER
Assign a row number to employees ordered by salary descending.
sql
SELECT employee_id, first_name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;
65. RANK vs DENSE_RANK
Rank employees by salary within department (ties get same rank, skip next).
sql
SELECT department_id, employee_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_dense
FROM employees;
66. LEAD
For each employee, show the salary of the person hired next (by hire date).
sql
SELECT employee_id, hire_date, salary,
LEAD(salary) OVER (ORDER BY hire_date) AS next_hired_salary
FROM employees;
67. LAG
Compare each order total with the previous order total for the same customer.
sql
SELECT customer_id, order_id, order_date, total_amount,
LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_total
FROM orders;
68. Running Total (SUM with window)
Calculate a running total of salary expenses per department by hire date.
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;
69. Moving Average
Compute a 3‑month moving average of total_amount for orders globally.
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;
70. Top‑N per Group (with ROW_NUMBER)
Get the highest‑paid employee in each department.
sql
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
FROM employees
)
SELECT department_id, employee_id, first_name, salary
FROM ranked WHERE rn = 1;
71. CTE (Common Table Expression)
Create a CTE high_salary for 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;
72. Recursive CTE (Org Chart)
Assuming employees has a self‑referencing manager_id, display the chain of command for employee 10.
sql
WITH RECURSIVE emp_chain AS (
SELECT employee_id, first_name, manager_id, 0 AS depth
FROM employees WHERE employee_id = 10
UNION ALL
SELECT e.employee_id, e.first_name, e.manager_id, ec.depth + 1
FROM employees e
JOIN emp_chain ec ON e.employee_id = ec.manager_id
)
SELECT * FROM emp_chain;
73. GROUP_CONCAT
List all employee names in each department as a comma‑separated string.
sql
SELECT department_id,
GROUP_CONCAT(first_name ORDER BY first_name SEPARATOR ', ') AS employees_list
FROM employees
GROUP BY department_id;
74. IF Statement
Show ‘Bonus’ if salary > 70k, otherwise ‘No Bonus’.
sql
SELECT first_name, salary,
IF(salary > 70000, 'Bonus', 'No Bonus') AS bonus_status
FROM employees;
75. IF with Aggregate
Count employees per department and label if count > 5 as ‘Large’, else ‘Small’.
sql
SELECT department_id, COUNT(*) AS cnt,
IF(COUNT(*) > 5, 'Large', 'Small') AS dept_size
FROM employees
GROUP BY department_id;
76. COALESCE with Multiple Columns
Show the first non‑NULL value among preferred_name, first_name, and a default string.
sql
SELECT COALESCE(preferred_name, first_name, 'Unknown') AS call_name FROM employees;
77. Find Employees with Same Hire Date
List pairs of employees hired on the same date (excluding self‑match).
sql
SELECT a.first_name AS emp1, b.first_name AS emp2, a.hire_date
FROM employees a
JOIN employees b ON a.hire_date = b.hire_date
AND a.employee_id < b.employee_id;
78. Second Highest Salary
Write a query to find the second highest salary (using OFFSET or subquery).
sql
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
79. Employees with Salary = Department Max
Find employees earning the maximum salary in their department (correlated subquery).
sql
SELECT employee_id, department_id, salary
FROM employees e
WHERE salary = (
SELECT MAX(salary) FROM employees WHERE department_id = e.department_id
);
80. Identify Duplicate Emails
List emails that appear more than once and their count.
sql
SELECT email, COUNT(*) AS occurrences FROM employees GROUP BY email HAVING COUNT(*) > 1;
81. Find Missing Employee IDs (Gaps)
Assuming employee_id should be sequential starting from 1, find gaps.
sql
SELECT t1.employee_id + 1 AS missing_id FROM employees t1 LEFT JOIN employees t2 ON t1.employee_id + 1 = t2.employee_id WHERE t2.employee_id IS NULL AND t1.employee_id < (SELECT MAX(employee_id) FROM employees);
82. Running Total Without Window Function
Use a self‑join to compute cumulative salary per department ordered by employee_id.
sql
SELECT a.employee_id, a.department_id, a.salary,
SUM(b.salary) AS running_total
FROM employees a
JOIN employees b ON a.department_id = b.department_id
AND b.employee_id <= a.employee_id
GROUP BY a.employee_id, a.department_id, a.salary
ORDER BY a.department_id, a.employee_id;
83. Pivot with CASE and SUM
Total sales (total_amount) per year, broken into columns for each customer country (US, UK, Others).
sql
SELECT YEAR(order_date) AS year,
SUM(CASE WHEN c.country = 'US' THEN total_amount ELSE 0 END) AS US_sales,
SUM(CASE WHEN c.country = 'UK' THEN total_amount ELSE 0 END) AS UK_sales,
SUM(CASE WHEN c.country NOT IN ('US','UK') THEN total_amount ELSE 0 END) AS other_sales
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY YEAR(order_date);
84. Delete Duplicate Rows (Keep One)
Keep the smallest employee_id for each email and remove others.
sql
DELETE e1 FROM employees e1 JOIN employees e2 ON e1.email = e2.email AND e1.employee_id > e2.employee_id;
85. Date Format
Display hire_date in ‘Month dd, YYYY’ format.
sql
SELECT first_name, DATE_FORMAT(hire_date, '%M %d, %Y') AS formatted_hire FROM employees;
86. Weekday Name
Show the day of the week for each order date.
sql
SELECT order_id, order_date,
DAYNAME(order_date) AS weekday
FROM orders;
87. Random Rows
Return 5 random employees.
sql
SELECT * FROM employees ORDER BY RAND() LIMIT 5;
88. Conditional Update with JOIN
Set credit_limit to 5000 for customers who have placed at least one order over 2000.
sql
UPDATE customers c
JOIN (
SELECT DISTINCT customer_id FROM orders WHERE total_amount > 2000
) o ON c.customer_id = o.customer_id
SET c.credit_limit = 5000;
89. Alter Table Engine
Change the storage engine of employees to InnoDB.
sql
ALTER TABLE employees ENGINE = InnoDB;
90. Check Constraints (MySQL 8.0.16+)
Add a check that salary is >= 0.
sql
ALTER TABLE employees ADD CONSTRAINT chk_salary_nonnegative CHECK (salary >= 0);
91. Transactions with Savepoint
Start a transaction, increase salary by 1000 for employee 1, create a savepoint, then increase for employee 2, and rollback to the savepoint.
sql
START TRANSACTION; UPDATE employees SET salary = salary + 1000 WHERE employee_id = 1; SAVEPOINT sp1; UPDATE employees SET salary = salary + 1000 WHERE employee_id = 2; ROLLBACK TO sp1; COMMIT;
92. Locking with SELECT … FOR UPDATE
In a transaction, lock a row for update, then update it.
sql
START TRANSACTION; SELECT salary FROM employees WHERE employee_id = 5 FOR UPDATE; UPDATE employees SET salary = salary * 1.05 WHERE employee_id = 5; COMMIT;
93. Information Schema: List All Tables in current database
sql
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE();
94. Data Type Conversion
Convert hire_date to a string in ‘YYYYMMDD’ format for export.
sql
SELECT employee_id, DATE_FORMAT(hire_date, '%Y%m%d') AS hire_date_str FROM employees;
95. LAST_INSERT_ID
Insert a new customer and retrieve the generated customer_id.
sql
INSERT INTO customers (name, city) VALUES ('Maria Gomez', 'Barcelona');
SELECT LAST_INSERT_ID();
96. Order Items with Product Name (avoid subquery)
Join order_items with products to show product name.
sql
SELECT oi.order_id, p.product_name, oi.quantity, oi.unit_price FROM order_items oi JOIN products p ON oi.product_id = p.product_id;
97. Count Orders per Customer with Zero
Show all customers and the number of orders they’ve placed (include those with none).
sql
SELECT c.customer_id, c.name, COUNT(o.order_id) AS order_count FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.name;
98. Find Customers with Orders Above Their Credit Limit
List any order that exceeded the customer’s credit limit.
sql
SELECT o.order_id, c.name, o.total_amount, c.credit_limit FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.total_amount > c.credit_limit;
99. NTILE Distribution
Divide employees into 4 salary groups (quartiles).
sql
SELECT employee_id, salary,
NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;
100. Complex Query: Customers with all orders above average
Find customers for which every order they’ve placed has a total_amount larger than the average total_amount across all orders.
sql
SELECT c.customer_id, c.name FROM customers c JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.name HAVING MIN(o.total_amount) > (SELECT AVG(total_amount) FROM orders);
Final Thought
You’ve just sailed through 100 MySQL problems — and that’s no small feat. From SELECT basics to intricate joins, subqueries, indexing, and beyond, you’ve not only practiced; you’ve built a quiet, solid confidence that no tutorial can gift you. Every query you now write will feel less like a struggle and more like a conversation with your data.
Before you go, here’s a surprising little fact to carry with you: MySQL was named after co-founder Michael Widenius’s daughter, My. That’s right — the “My” in MySQL isn’t a possessive pronoun; it’s the name of a real person, just as MariaDB is named after his younger daughter, Maria. Behind one of the world’s most powerful database engines beats a surprisingly tender, family story.
Let that remind you: technology isn’t cold — it’s built by humans with heart, and so is your learning journey. Keep this collection close, revisit the tricky problems on a slow afternoon, and never stop querying the world with curiosity. You’re now a MySQL problem‑solver — and that’s something to smile about. Happy coding!