Practice Exercises

Forty graded exercises on the course sample database (eh_company) unless stated otherwise.

Concept

Forty graded exercises on the course sample database (eh_company) unless stated otherwise. Each has a task, a hint, the expected output, and a solution. Attempt each before reading the solution.

Why It Matters

Repetition with verifiable answers builds fluency and confidence.

Level 1 — Basics (1–10)

E1. List products in the Electronics category ordered by price descending. *Hint:* WHERE + ORDER BY. *Expected:* Laptop 75000.00, Monitor 18000.00, Mouse 800.00.

E2. Show employees hired in 2022. *Hint:* date range on a DATE column. *Expected:* Priya (2022-04-20), Meera (2022-07-18).

E3. Show customers whose email is missing, displaying 'NO EMAIL'. *Hint:* IFNULL. *Expected:* Diya Patel — NO EMAIL.

E4. Show the 3 most recent orders. *Expected:* 1006, 1005, 1004.

E5. List distinct countries of customers alphabetically. *Expected:* India, Italy, Singapore, UK.

E6. Full names in the form Rao, Asha for Engineering (dept 1), by emp_id. *Hint:* CONCAT. *Expected:* Rao, Asha / Shah, Vikram / Iyer, Neha / Pillai, Meera.

E7. Products whose name contains "desk" (any case). *Expected:* Desk Chair, Standing Desk.

E8. Insert a product 107 'Keyboard', Electronics, 1500, stock 80 inside a transaction, verify, and roll back. *Expected:* count of Electronics products is 4 before rollback, 3 after.

E9. Increase Mouse stock by 50 using its key; report affected rows. *Expected:* ROW_COUNT() = 1; stock 250.

E10. Page 2 of employees by emp_id with page size 3. *Expected:* emp_id 4, 5, 6.

SELECT product_name, price FROM products WHERE category = 'Electronics' ORDER BY price DESC;

SELECT first_name, hire_date FROM employees
WHERE hire_date >= '2022-01-01' AND hire_date < '2023-01-01' ORDER BY hire_date;

SELECT name, IFNULL(email, 'NO EMAIL') AS email FROM customers WHERE email IS NULL;

SELECT order_id, order_date FROM orders ORDER BY order_date DESC, order_id DESC LIMIT 3;

SELECT DISTINCT country FROM customers ORDER BY country;

SELECT CONCAT(last_name, ', ', first_name) AS full_name FROM employees WHERE dept_id = 1 ORDER BY emp_id;

SELECT product_name FROM products WHERE product_name LIKE '%desk%';

START TRANSACTION;
INSERT INTO products (product_id, product_name, category, price, stock) VALUES (107, 'Keyboard', 'Electronics', 1500, 80);
SELECT COUNT(*) FROM products WHERE category = 'Electronics';   -- 4
ROLLBACK;
SELECT COUNT(*) FROM products WHERE category = 'Electronics';   -- 3

UPDATE products SET stock = stock + 50 WHERE product_id = 102;
SELECT ROW_COUNT();

SELECT emp_id, first_name FROM employees ORDER BY emp_id LIMIT 3 OFFSET 3;

Level 2 — Functions, grouping, joins (11–22)

E11. Number of employees per job title with more than one employee. *Expected:* Engineer 2.

E12. Total stock per category with a grand total (WITH ROLLUP). *Expected:* Electronics 240, Furniture 30, Stationery 1000, total 1270 (assuming E9 was rolled back or not run; with E9 applied Electronics is 290 and total 1320).

E13. Monthly order count and net revenue (excluding cancelled). *Expected:* 2026-01: 2 / 87800.00; 2026-02: 2 / 18100.00; 2026-03: 2 / 94600.00.

E14. Every customer with order count (including zero). *Expected:* Ananya 2, Ben 2, Chen 1, Diya 1, Elena 0.

E15. Employees with their department name, showing 'Unassigned' for none. *Expected:* 8 rows; Kiran → Unassigned.

E16. Each employee with their manager's name. *Expected:* Asha → NULL, Vikram → Asha, Neha → Vikram, Rahul → Asha, Priya → Rahul, Arjun → Asha, Kiran → Asha, Meera → Vikram.

E17. Product names bought by each customer as a comma-separated list. *Expected:* Ananya: Laptop, Monitor, Mouse, Notebook; Ben: Desk Chair, Laptop, Monitor; Chen: Mouse; Diya: Mouse.

E18. Units sold per product for DELIVERED orders. *Expected:* Laptop 2, Desk Chair 1, Monitor 1, Mouse 1.

E19. Salary band counts (A ≥ 150000, B ≥ 90000, else C). *Expected:* A 2, B 4, C 2.

E20. Days between each order and 2026-03-31. *Expected:* 1001: 85, 1002: 72, 1003: 57, 1004: 45, 1005: 30, 1006: 21.

E21. Email domain counts. *Expected:* example.com 4.

E22. Departments with total salary above 150000. *Expected:* Engineering 590000.00, Sales 180000.00.

SELECT job_title, COUNT(*) AS n FROM employees GROUP BY job_title HAVING n > 1;

SELECT IF(GROUPING(category), 'TOTAL', category) AS category, SUM(stock) AS units
FROM products GROUP BY category WITH ROLLUP;

SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, COUNT(*) AS orders,
       SUM(IF(status <> 'CANCELLED', total_amount, 0)) AS net_revenue
FROM orders GROUP BY month ORDER BY month;

SELECT c.name, COUNT(o.order_id) AS orders
FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id ORDER BY orders DESC, c.name;

SELECT e.first_name, IFNULL(d.dept_name, 'Unassigned') AS dept
FROM employees e LEFT JOIN departments d ON d.dept_id = e.dept_id ORDER BY e.emp_id;

SELECT e.first_name, m.first_name AS manager
FROM employees e LEFT JOIN employees m ON m.emp_id = e.manager_id ORDER BY e.emp_id;

SELECT c.name, GROUP_CONCAT(DISTINCT p.product_name ORDER BY p.product_name SEPARATOR ', ') AS products
FROM customers c
JOIN orders o       ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p     ON p.product_id = oi.product_id
GROUP BY c.customer_id ORDER BY c.customer_id;

SELECT p.product_name, SUM(oi.quantity) AS units
FROM order_items oi JOIN orders o ON o.order_id = oi.order_id JOIN products p ON p.product_id = oi.product_id
WHERE o.status = 'DELIVERED' GROUP BY p.product_id ORDER BY units DESC, p.product_name;

SELECT CASE WHEN salary >= 150000 THEN 'A' WHEN salary >= 90000 THEN 'B' ELSE 'C' END AS band, COUNT(*) AS n
FROM employees GROUP BY band ORDER BY band;

SELECT order_id, DATEDIFF('2026-03-31', order_date) AS age_days FROM orders ORDER BY order_id;

Level 3 — Subqueries, CTEs, window functions (23–32)

E23. Employees earning above the company average. *Expected:* Asha, Vikram, Rahul.

E24. Customers with no orders using NOT EXISTS. *Expected:* Elena Rossi.

E25. Highest-paid employee per department with ROW_NUMBER. *Expected:* NULL Kiran, 1 Asha, 2 Rahul, 3 Arjun.

E26. Third-highest distinct salary. *Expected:* 120000.00.

E27. Running total of orders by date. *Expected final value:* 201300.00.

E28. Each customer's previous order date and days between (repeat orders only). *Expected:* customer 1: 1003 after 28 days; customer 2: 1006 after 51 days.

E29. Management levels via recursive CTE; count per level. *Expected:* level 1: 1, level 2: 4, level 3: 3.

E30. Products priced above their category average. *Expected:* Laptop (avg 31266.67), Standing Desk (avg 23500.00).

E31. Customers who bought both Laptop and Monitor. *Expected:* Ananya Gupta, Ben Carter.

E32. Each non-cancelled order's share of its month's revenue. *Expected:* 1001 86.33, 1002 13.67, 1003 100.00, 1005 1.69, 1006 98.31.

SELECT first_name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) ORDER BY salary DESC;

SELECT name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

WITH r AS (SELECT dept_id, first_name, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, emp_id) rn FROM employees)
SELECT dept_id, first_name FROM r WHERE rn = 1 ORDER BY dept_id;

SELECT DISTINCT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) dr FROM employees) t WHERE dr = 3;

SELECT order_id, SUM(total_amount) OVER (ORDER BY order_date, order_id ROWS UNBOUNDED PRECEDING) AS running FROM orders;

WITH x AS (SELECT customer_id, order_id, order_date,
                  LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev FROM orders)
SELECT customer_id, order_id, DATEDIFF(order_date, prev) AS days FROM x WHERE prev IS NOT NULL;

WITH RECURSIVE org AS (
    SELECT emp_id, 1 AS lvl FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.emp_id, o.lvl + 1 FROM employees e JOIN org o ON e.manager_id = o.emp_id
)
SELECT lvl, COUNT(*) FROM org GROUP BY lvl ORDER BY lvl;

SELECT product_name, price, ROUND(cat_avg, 2) AS cat_avg
FROM (SELECT p.*, AVG(price) OVER (PARTITION BY category) AS cat_avg FROM products p) t
WHERE price > cat_avg;

SELECT c.name
FROM customers c JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id JOIN products p ON p.product_id = oi.product_id
WHERE p.product_name IN ('Laptop', 'Monitor')
GROUP BY c.customer_id HAVING COUNT(DISTINCT p.product_name) = 2 ORDER BY c.name;

SELECT order_id,
       ROUND(100 * total_amount / SUM(total_amount) OVER (PARTITION BY DATE_FORMAT(order_date, '%Y-%m')), 2) AS pct
FROM orders WHERE status <> 'CANCELLED' ORDER BY order_id;

Level 4 — MySQL-specific and advanced (33–40)

E33. Upsert: set product 104 price to 55 and add 100 to its stock if it exists, else insert it. *Expected:* ROW_COUNT() = 2 (updated); stock 1100, price 55.00.

E34. Delete order items of cancelled orders with a multi-table DELETE. *Expected:* 1 row deleted.

E35. Create an index that makes SELECT order_date, total_amount FROM orders WHERE customer_id = 2 covering; verify with EXPLAIN. *Expected:* Extra: Using index.

E36. Store a JSON array of tags on products and find products tagged "wireless". *Expected:* Mouse.

E37. Write a stored procedure dept_headcount(IN p_dept INT, OUT p_count INT). *Expected:* CALL dept_headcount(1, @c); SELECT @c; → 4.

E38. Create a BEFORE UPDATE trigger that rejects salary cuts greater than 20%. *Expected:* UPDATE employees SET salary = 50000 WHERE emp_id = 2; fails with SQLSTATE 45000.

E39. Find duplicate rows in a staging table by normalized email and keep the latest. *Tables:* CREATE TABLE stg (id INT PRIMARY KEY, email VARCHAR(100), loaded_at DATE); INSERT INTO stg VALUES (1,'[email protected]','2026-01-01'),(2,' [email protected]','2026-02-01'),(3,'[email protected]','2026-01-05'); *Expected:* id 1 deleted; ids 2 and 3 remain.

E40. Find continuous runs of consecutive days with at least one order in March 2026 (gaps and islands). *Hint:* order_date - INTERVAL ROW_NUMBER() DAY is constant within a run. *Expected:* two single-day runs: 2026-03-01 and 2026-03-10.

INSERT INTO products (product_id, product_name, category, price, stock)
VALUES (104, 'Notebook', 'Stationery', 55, 100) AS new
ON DUPLICATE KEY UPDATE price = new.price, stock = products.stock + new.stock;

DELETE oi FROM order_items oi JOIN orders o ON o.order_id = oi.order_id WHERE o.status = 'CANCELLED';

CREATE INDEX ix_orders_cust_date_amt ON orders (customer_id, order_date, total_amount);
EXPLAIN SELECT order_date, total_amount FROM orders WHERE customer_id = 2;

ALTER TABLE products ADD COLUMN tags JSON NULL;
UPDATE products SET tags = JSON_ARRAY('wireless', 'office') WHERE product_id = 102;
SELECT product_name FROM products WHERE 'wireless' MEMBER OF (tags);

DELIMITER //
CREATE PROCEDURE dept_headcount(IN p_dept INT, OUT p_count INT)
READS SQL DATA
BEGIN
    SELECT COUNT(*) INTO p_count FROM employees WHERE dept_id = p_dept;
END //
DELIMITER ;

DELIMITER //
CREATE TRIGGER trg_employees_bu_salary BEFORE UPDATE ON employees FOR EACH ROW
BEGIN
    IF NEW.salary < OLD.salary * 0.8 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cut exceeds 20%';
    END IF;
END //
DELIMITER ;

DELETE FROM stg WHERE id IN (
    SELECT id FROM (
        SELECT id, ROW_NUMBER() OVER (PARTITION BY LOWER(TRIM(email)) ORDER BY loaded_at DESC, id DESC) AS rn FROM stg
    ) d WHERE d.rn > 1
);

WITH d AS (SELECT DISTINCT order_date FROM orders WHERE order_date >= '2026-03-01' AND order_date < '2026-04-01'),
n AS (SELECT order_date, ROW_NUMBER() OVER (ORDER BY order_date) AS rn FROM d),
g AS (SELECT order_date, order_date - INTERVAL rn DAY AS grp FROM n)
SELECT MIN(order_date) AS run_start, MAX(order_date) AS run_end, COUNT(*) AS days FROM g GROUP BY grp ORDER BY run_start;

Expected Result

Your outputs match the expected values for all forty exercises (noting where earlier exercises modified data).

Common Mistakes

Running modifying exercises without a transaction and then getting different results later.

Missing zero-row groups (E14) and NULL groups (E25).

Best Practices

Reload the sample database (DROP DATABASE eh_company; then rerun the script) whenever results drift.

Interview Relevance

E11–E32 match common interview questions; E33–E40 test MySQL-specific skills.

Practice Questions

Solve E17 without GROUP_CONCAT using JSON_ARRAYAGG.

Solve E25 using a correlated subquery instead of a window function.

Extend E40 to report the gap lengths between runs.