MySQL Test 3 – Advanced SQL
In this lesson: Free advanced SQL test — joins, subqueries, views, transactions and case study — timer and answer review. Take it now.
MySQL Test 3 – Advanced SQL and Case Study (50 Marks)
Case study tables (for questions marked [Case study])
customers (id INT PRIMARY KEY, name, city)
| id | name | city |
|---|---|---|
| 1 | Asha | Delhi |
| 2 | Ravi | Pune |
| 3 | Meena | Delhi |
| 4 | John | Jaipur |
orders (id INT PRIMARY KEY, customer_id INT REFERENCES customers(id), amount INT, order_date DATE)
| id | customer_id | amount | order_date |
|---|---|---|---|
| 101 | 1 | 500 | 2026-01-05 |
| 102 | 1 | 1200 | 2026-01-20 |
| 103 | 2 | 300 | 2026-02-02 |
| 104 | 3 | 1500 | 2026-02-10 |
| 105 | 3 | 700 | 2026-03-01 |
Every case-study answer was checked by running the query on a real MySQL-compatible server.
Question Palette
Green = answered, Purple = review, White = not answered.
Answer key with explanations
Attempt the test and submit it first, then use this key to revise. Each answer has a short explanation so you understand why it is correct.
Show all 50 answers
- Which join returns only rows that have matching values in both tables?
Answer: B. INNER JOIN – Rows without a match in either table are dropped.
- Which join returns all rows of the left table and matching rows of the right table?
Answer: A. LEFT JOIN – Unmatched right-side columns appear as NULL.
- A CROSS JOIN of a 4-row table with a 5-row table returns:
Answer: D. 20 rows – A cross join is the Cartesian product: 4 × 5.
- Joining a table with itself (e.g. employee and manager in one table) is called:
Answer: B. Self join – Use aliases: FROM emp e JOIN emp m ON e.manager_id = m.id.
- MySQL does not support FULL OUTER JOIN directly. It can be produced with:
Answer: A. LEFT JOIN UNION RIGHT JOIN – UNION removes duplicates of the matched rows.
- UNION and UNION ALL differ because:
Answer: B. UNION removes duplicate rows, UNION ALL keeps them – UNION ALL is faster when duplicates are acceptable.
- A subquery is:
Answer: B. A query written inside another query – Example: SELECT name FROM students WHERE marks > (SELECT AVG(marks) FROM students);
- A correlated subquery:
Answer: B. Refers to a column of the outer query and runs once per outer row – Example: find students scoring above their own class average.
- Which operator checks whether a subquery returns any row?
Answer: D. EXISTS – WHERE EXISTS (SELECT 1 FROM orders o WHERE o.cust_id = c.id).
- marks > ALL (SELECT marks FROM students WHERE class = 9) means:
Answer: C. Greater than the highest class-9 mark – > ANY means greater than at least one value (i.e. the minimum).
- Which window function gives 1, 2, 2, 4 for marks 95, 90, 90, 85?
Answer: A. RANK() – RANK leaves gaps after ties; DENSE_RANK would give 1, 2, 2, 3.
- Which window function gives 1, 2, 2, 3 for marks 95, 90, 90, 85?
Answer: A. DENSE_RANK() – DENSE_RANK does not skip numbers after ties (MySQL 8+).
- SUM(amount) OVER (ORDER BY order_date) calculates:
Answer: B. A running total – Window functions keep every row while adding an aggregate.
- A CTE (Common Table Expression) starts with the keyword:
Answer: D. WITH – WITH toppers AS (SELECT …) SELECT * FROM toppers; (MySQL 8+).
- Which statement shows how MySQL will execute a query (index usage etc.)?
Answer: A. EXPLAIN SELECT … – EXPLAIN shows the access type, keys used and rows estimated.
- An index on (last_name, first_name) helps most for:
Answer: A. WHERE last_name = 'Sharma' AND first_name = 'Ravi' – Composite indexes are used from the leftmost column (leftmost-prefix rule).
- A stored procedure is called using:
Answer: A. CALL proc_name(); – Procedures can take IN, OUT and INOUT parameters.
- Why is DELIMITER $$ used before CREATE PROCEDURE in the MySQL client?
Answer: B. So the semicolons inside the body do not end the statement early – Reset with DELIMITER ; afterwards.
- A trigger runs:
Answer: B. Automatically before or after INSERT, UPDATE or DELETE on a table – Example: a BEFORE INSERT trigger that converts names to upper case.
- Inside an UPDATE trigger, the new value of a column is accessed as:
Answer: B. NEW.column – OLD.column gives the value before the update.
- A SAVEPOINT allows you to:
Answer: A. Roll back part of a transaction – ROLLBACK TO sp1; undoes changes made after the savepoint.
- Which isolation level is the InnoDB default?
Answer: C. REPEATABLE READ – It prevents dirty and non-repeatable reads.
- Reading another transaction's uncommitted change is called a:
Answer: A. Dirty read – Allowed only at READ UNCOMMITTED.
- Two transactions each waiting for a lock held by the other is a:
Answer: D. Deadlock – InnoDB detects deadlocks and rolls back one transaction.
- Which tool creates a logical backup (SQL file) of a MySQL database?
Answer: C. mysqldump – mysqldump -u root -p school > school.sql; restore with mysql school < school.sql.
- Which clause prevents SQL injection in application code?
Answer: A. Prepared statements with parameters – Never build SQL by joining user input into the query string.
- CASE in SQL is used to:
Answer: D. Return values based on conditions inside a query – CASE WHEN marks >= 90 THEN 'A1' WHEN marks >= 80 THEN 'A2' ELSE 'B' END.
- ON DELETE CASCADE on a foreign key means:
Answer: A. Deleting a parent row automatically deletes its child rows – Use with care – one DELETE can remove many related rows.
- Which data type stores JSON documents with validation in MySQL 8?
Answer: B. JSON – Use ->> or JSON_EXTRACT to read values.
- GROUP_CONCAT(name) in a GROUP BY query returns:
Answer: D. All names of the group joined in one string – Default separator is a comma; SEPARATOR ' | ' changes it.
- [Case study] SELECT COUNT(*) FROM orders; returns:
Answer: D. 5 – The orders table has 5 rows (ids 101–105).
- [Case study] SELECT SUM(amount) FROM orders WHERE customer_id = 1; returns:
Answer: A. 1700 – Asha's orders: 500 + 1200 = 1700.
- [Case study] Which customer has no orders?
Answer: C. John – Customer 4 (John) has no row in orders; find him with LEFT JOIN … WHERE o.id IS NULL.
- [Case study] SELECT COUNT(*) FROM customers c JOIN orders o ON c.id = o.customer_id; returns:
Answer: A. 5 – Every order matches exactly one customer, so the inner join has 5 rows.
- [Case study] SELECT COUNT(*) FROM customers c LEFT JOIN orders o ON c.id = o.customer_id; returns:
Answer: A. 6 – 5 matched rows + 1 row for John with NULL order columns.
- [Case study] SELECT city, SUM(amount) … GROUP BY city — the total for Delhi is:
Answer: A. 3900 – Delhi customers Asha (1700) and Meena (2200): 1700 + 2200 = 3900.
- [Case study] Which customer has the highest total order amount?
Answer: D. Meena – Totals: Asha 1700, Ravi 300, Meena 2200.
- [Case study] SELECT MAX(amount) FROM orders WHERE order_date < '2026-02-01'; returns:
Answer: B. 1200 – January orders are 500 and 1200.
- [Case study] SELECT AVG(amount) FROM orders; returns:
Answer: A. 840.0000 – Total 4200 ÷ 5 orders = 840 (MySQL shows 840.0000).
- [Case study] How many customers have total orders above 1000 (… GROUP BY customer_id HAVING SUM(amount) > 1000)?
Answer: A. 2 – Asha (1700) and Meena (2200).
- [Case study] SELECT COUNT(DISTINCT customer_id) FROM orders; returns:
Answer: D. 3 – Customers 1, 2 and 3 placed orders.
- [Case study] SELECT MONTH(order_date) m, COUNT(*) FROM orders GROUP BY m — the count for month 2 is:
Answer: D. 2 – Orders 103 and 104 are in February.
- [Case study] Which query lists customers who placed at least one order?
Answer: D. SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders); – The subquery returns 1, 2, 3, 3, 1 → Asha, Ravi, Meena.
- [Case study] SELECT name FROM customers WHERE city = 'Delhi' ORDER BY name DESC LIMIT 1; returns:
Answer: A. Meena – Delhi names sorted Z–A: Meena, Asha.
- [Case study] For Asha, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) gives order 102 the number:
Answer: A. 2 – Asha's orders by date: 101 (1st), 102 (2nd).
- [Case study] To stop deleting a customer who still has orders, the foreign key should use:
Answer: D. ON DELETE RESTRICT (the default) – RESTRICT/NO ACTION blocks the delete with error 1451.
- [Case study] Which index would most speed up WHERE customer_id = ? on orders?
Answer: D. An index on orders(customer_id) – InnoDB automatically creates this index when you define the foreign key.
- [Case study] SELECT c.name, o.amount FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.amount > (SELECT AVG(amount) FROM orders); returns how many rows?
Answer: A. 2 – Average is 840; orders above it: 1200 (Asha) and 1500 (Meena).
- [Case study] SELECT COALESCE(SUM(o.amount), 0) FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE c.name = 'John'; returns:
Answer: B. 0 – John has no orders, so SUM is NULL and COALESCE turns it into 0.
- [Case study] Which statement adds a 10% discount to orders above 1000?
Answer: A. UPDATE orders SET amount = amount * 0.9 WHERE amount > 1000; – UPDATE with WHERE changes only orders 102 and 104.
