Chapter 12 Intermediate 49 Questions

Practice Questions — Self Joins and Multi-Table Queries

← Back to Notes
12 Easy
11 Medium
4 Hard

Topic-Specific Questions

Question 1
Easy
Given the employees table with 7 rows, how many rows does this return?
SELECT e.name, m.name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
LEFT JOIN keeps every employee. CEO has NULL manager.
7 rows
Question 2
Easy
How many rows does this return given the same employees table?
SELECT e.name, m.name
FROM employees e
JOIN employees m ON e.manager_id = m.id;
INNER JOIN drops rows with NULL manager_id.
6 rows
Question 3
Easy
Which employee earns more than their manager?
Sample data: Divya (190k) reports to Rahul (180k).
Divya Nair (190000) - Rahul Verma (180000)
Question 4
Easy
How many direct reports does Ananya (CEO) have?
Count employees where manager_id = 1.
2 (Rahul Verma, Sneha Kapoor)
Question 5
Easy
How many tables are joined in this query?
SELECT c.name, p.name
FROM customers c
JOIN orders o       ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p     ON oi.product_id = p.id;
Count the tables in FROM and JOIN.
4 tables (customers, orders, order_items, products)
Question 6
Easy
True or False: A self join requires at least two different aliases for the same table.
Otherwise columns are ambiguous.
True
Question 7
Easy
What is the problem with this old-style join?
SELECT c.name, o.id
FROM customers c, orders o
WHERE c.id = o.customer_id;
Modern style vs implicit style.
It works but is discouraged. Forgetting the WHERE creates a cartesian product. Modern style: FROM customers c JOIN orders o ON c.id = o.customer_id.
Question 8
Medium
What does this query return on the sample customers/orders/order_items data?
SELECT c.name, SUM(oi.qty * oi.price) AS total
FROM customers c
JOIN orders o       ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.id, c.name;
Sum line totals per customer.
Aarav Kumar: 56000.00, Priya Sharma: 1000.00
Question 9
Medium
What does this self-join query produce?
SELECT e.name
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE m.name = 'Sneha Kapoor';
Employees whose manager is Sneha.
Karthik Raja, Pooja Menon
Question 10
Medium
What does this query count?
SELECT m.name, COUNT(e.id) AS reports
FROM employees m
LEFT JOIN employees e ON e.manager_id = m.id
GROUP BY m.id, m.name;
LEFT JOIN on the 'manager side'.
Number of direct reports per employee (0 if none).
Question 11
Medium
Why does this return 0 rows instead of the CEO?
SELECT e.name
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.name = 'Ananya Reddy';
CEO has NULL manager_id.
Ananya's manager_id is NULL, so the INNER JOIN finds no match and drops her row.
Question 12
Medium
What does this query do?
SELECT e1.name, e2.name
FROM employees e1
JOIN employees e2
  ON e1.department = e2.department
 AND e1.id <> e2.id;
Same department, different people.
Every ordered pair of co-workers in the same department. Includes both (A,B) and (B,A).
Question 13
Medium
How many rows does this return for the sample employees table?
SELECT e1.name, e2.name
FROM employees e1
JOIN employees e2
  ON e1.department = e2.department
 AND e1.id < e2.id;
3 Engineering + 3 Marketing + 1 CEO (alone). Pairs = C(3,2) + C(3,2) + 0.
6 rows
Question 14
Hard
What does this three-level self join return?
SELECT e.name, gm.name AS grandmanager
FROM employees e
JOIN employees m  ON e.manager_id = m.id
JOIN employees gm ON m.manager_id = gm.id;
Employee -> manager -> grandmanager. INNER JOIN requires all three.
Only employees at depth >= 2 with their grandmanager. Arjun -> Rahul -> Ananya, Divya -> Rahul -> Ananya, Karthik -> Sneha -> Ananya, Pooja -> Sneha -> Ananya.
Question 15
Hard
What is returned by this query given our sample tables?
SELECT c.name, p.category, SUM(oi.qty * oi.price) AS cat_total
FROM customers c
JOIN orders o       ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p     ON oi.product_id = p.id
GROUP BY c.id, c.name, p.category;
Group by customer and category.
Aarav / Electronics: 56000, Priya / Stationery: 1000

Mixed & Application Questions

Question 1
Easy
What is a self join?
A table joined with...
A JOIN where a table is joined with itself using two different aliases. Used for hierarchical or same-table relationships like employee-manager.
Question 2
Easy
Why should you prefer LEFT JOIN over INNER JOIN for 'list all employees with their manager'?
Top of hierarchy.
To include employees whose manager_id is NULL (like the CEO). INNER JOIN would drop them.
Question 3
Easy
What is the problem with NATURAL JOIN?
Silent behavior when schema changes.
It silently joins on every same-named column. Adding a new column (like created_at) in both tables changes the join conditions unexpectedly.
Question 4
Easy
Does join order affect the final result for INNER JOINs?
Associative property.
No. INNER JOINs are associative — (A JOIN B) JOIN C equals A JOIN (B JOIN C). Only performance may differ.
Question 5
Easy
Rewrite this as an explicit join: 
SELECT * FROM a, b WHERE a.id = b.a_id;
Use JOIN ... ON.
SELECT * FROM a JOIN b ON a.id = b.a_id;
Question 6
Medium
Write a query to find all employees managed by Sneha (directly or indirectly, up to 1 level deep).
Sneha has direct reports only.
SELECT e.name
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE m.name = 'Sneha Kapoor';
Question 7
Medium
Given customers, orders, and order_items, write a query to find total orders and total revenue per customer.
DISTINCT count of orders + SUM of line totals.
SELECT c.name, COUNT(DISTINCT o.id) AS orders,
       SUM(oi.qty * oi.price) AS revenue
FROM customers c
JOIN orders o       ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.id, c.name;
Question 8
Medium
Given the sample data, what is Aarav's total revenue?
Order 101: 1 laptop @ 55000 + 2 mice @ 500.
56000
Question 9
Medium
Why does a multi-table JOIN sometimes count rows incorrectly?
Row duplication from joining one-to-many relationships.
Because joining parents to children duplicates parent rows (one per child). Aggregations must use DISTINCT or GROUP BY correctly. COUNT(c.id) over a customer-orders-items join counts items, not customers.
Question 10
Medium
When does a self join become insufficient and you need a recursive CTE?
Think about hierarchy depth.
When the depth of the hierarchy is unknown or variable. A self join handles exactly N levels by writing N-1 joins. A recursive CTE handles arbitrary depth in a single expression.
Question 11
Hard
Write a query to find pairs of employees in the same department where one earns significantly more than the other (at least 50k difference).
Self join on department + salary difference condition.
SELECT e1.name AS high_earner, e2.name AS low_earner,
       e1.department, e1.salary - e2.salary AS diff
FROM employees e1
JOIN employees e2
  ON e1.department = e2.department
 AND e1.salary >= e2.salary + 50000;
Question 12
Hard
Write a query that lists each product along with total units sold and which customers bought it.
Four-table join + GROUP_CONCAT (MySQL) or aggregation.
SELECT p.name,
       SUM(oi.qty) AS units,
       GROUP_CONCAT(DISTINCT c.name ORDER BY c.name) AS buyers
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN orders o       ON oi.order_id = o.id
LEFT JOIN customers c    ON o.customer_id = c.id
GROUP BY p.id, p.name;

Multiple Choice Questions

MCQ 1
What is a self join?
  • A. Joining two different tables
  • B. Joining a table with itself using two aliases
  • C. Joining with a view instead of a table
  • D. A CROSS JOIN with a single table
Answer: B
B is correct. Self join uses two aliases of the same table to represent two roles of the same entity (like employee and manager).
MCQ 2
Why must you alias the table in a self join?
  • A. For performance
  • B. To avoid ambiguous column references
  • C. MySQL does not require it
  • D. To reduce memory
Answer: B
B is correct. Without distinct aliases, MySQL cannot tell which instance of the table a column refers to, producing the error 'Not unique table/alias'.
MCQ 3
Which JOIN type should you use to list all employees with their manager, INCLUDING the CEO?
  • A. INNER JOIN
  • B. LEFT JOIN
  • C. RIGHT JOIN
  • D. CROSS JOIN
Answer: B
B is correct. The CEO has manager_id = NULL. LEFT JOIN keeps every employee and shows NULL for the CEO's manager. INNER JOIN would drop the CEO.
MCQ 4
How do JOINs compose with 3 tables?
  • A. Only INNER JOIN can combine 3 tables
  • B. Each JOIN needs its own ON clause; tables are chained left-to-right
  • C. You need a subquery for the third table
  • D. You can only JOIN 2 tables in MySQL
Answer: B
B is correct. JOINs are naturally chainable. Each additional JOIN brings in one more table with its own ON condition, all in the same query.
MCQ 5
Is implicit join (FROM a, b WHERE ...) equivalent to INNER JOIN?
  • A. Yes, logically equivalent; prefer explicit JOIN syntax
  • B. No, implicit join is CROSS JOIN only
  • C. No, implicit join is LEFT JOIN
  • D. Implicit join is illegal in modern MySQL
Answer: A
A is correct. When the WHERE supplies the matching condition, the result equals INNER JOIN. But explicit JOIN ... ON is preferred for clarity and safety.
MCQ 6
Why is NATURAL JOIN risky?
  • A. It always produces cartesian products
  • B. It joins on every same-named column, breaking silently when schemas change
  • C. It cannot be used with aliases
  • D. It does not exist in MySQL
Answer: B
B is correct. Adding a column with a common name (like created_at) to both tables silently adds a new join condition. The query may return different results without any obvious change.
MCQ 7
What does this query find?
SELECT e.name FROM employees e JOIN employees m ON e.manager_id = m.id WHERE e.salary > m.salary;
  • A. Managers who earn more than their employees
  • B. Employees who earn more than their manager
  • C. Employees with no manager
  • D. Managers with many direct reports
Answer: B
B is correct. e is the employee, m is the manager. The WHERE filters for employees whose salary exceeds their manager's.
MCQ 8
How do you get unique unordered pairs in a self join without duplicates?
  • A. Use DISTINCT
  • B. Use ON e1.id <> e2.id
  • C. Use ON e1.id < e2.id
  • D. Use NATURAL JOIN
Answer: C
C is correct. e1.id < e2.id eliminates both self-pairs and the reverse duplicate (A,B) vs (B,A).
MCQ 9
In a customers -> orders -> order_items join, what would COUNT(*) return?
  • A. Number of customers
  • B. Number of orders
  • C. Number of order items (line items)
  • D. Number of products
Answer: C
C is correct. The finest-grain table drives the row count after JOINs. COUNT(*) counts order items. Use COUNT(DISTINCT c.id) for customers, COUNT(DISTINCT o.id) for orders.
MCQ 10
Which statement about JOIN order is TRUE?
  • A. The order affects the result rows for INNER JOIN
  • B. The order can affect performance but not result rows
  • C. JOINs must always be written in PK-FK order
  • D. LEFT JOIN and INNER JOIN cannot be mixed
Answer: B
B is correct. INNER JOINs are associative and commutative. The optimizer may choose a different execution plan, affecting speed. For readability, write JOINs in the order you naturally think about the relationships.
MCQ 11
What does this produce?
SELECT m.name, COUNT(e.id) AS n FROM employees m LEFT JOIN employees e ON e.manager_id = m.id GROUP BY m.id, m.name;
  • A. Count of all employees
  • B. Count of direct reports per person (0 for individual contributors)
  • C. Count of managers only
  • D. Error
Answer: B
B is correct. LEFT JOIN + COUNT on the 'reports side' gives zero for individual contributors. Every employee is treated as a potential manager.
MCQ 12
A query joins 4 tables with 100 rows each. One JOIN condition is missing. How many rows could the result have?
  • A. 400
  • B. 10,000
  • C. 100,000,000
  • D. 100
Answer: C
C is correct. A missing JOIN condition on one table produces a partial cartesian: 100 * 100 * 100 * 100 = 100 million rows. This is why multi-table queries must be sanity-checked during development.
MCQ 13
When would you choose a recursive CTE over a self join?
  • A. Always — CTEs are always better
  • B. When the hierarchy depth is unknown or variable
  • C. When you want exactly 2 levels
  • D. Only for outer joins
Answer: B
B is correct. Self join handles a known, fixed depth with N-1 joins. Recursive CTEs handle arbitrary depth. For 'find all ancestors' or 'all descendants', use recursive CTEs.
MCQ 14
What is wrong with NATURAL JOIN in production code?
  • A. It is slower than ON joins
  • B. Schema changes can silently alter its semantics
  • C. It cannot be combined with WHERE
  • D. It only works with integer columns
Answer: B
B is correct. Adding a column with a common name (e.g., created_at, updated_at) to both tables changes what NATURAL JOIN matches on — silently. Queries that worked yesterday return different results today. Always use explicit ON.

Coding Challenges

Challenge 1: Employees with Their Manager Name

Easy
Write a query that lists every employee with their manager's name. Include the CEO with NULL manager.
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.id;

Challenge 2: Employees Earning More Than Their Manager

Easy
Find all employees whose salary exceeds their manager's salary.
SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS mgr_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

Challenge 3: Count Direct Reports Per Manager

Medium
For each employee, show the number of direct reports (0 for individual contributors).
SELECT m.name AS manager, COUNT(e.id) AS reports
FROM employees m
LEFT JOIN employees e ON e.manager_id = m.id
GROUP BY m.id, m.name
ORDER BY reports DESC;

Challenge 4: Total Revenue Per Customer (3-Table Join)

Medium
Calculate total revenue per customer using customers, orders, and order_items.
SELECT c.name, SUM(oi.qty * oi.price) AS revenue
FROM customers c
JOIN orders o       ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.id, c.name
ORDER BY revenue DESC;

Challenge 5: Pairs of Employees in Same Department

Medium
List all unique pairs of employees who work in the same department. No self-pairs, no duplicates.
SELECT e1.name AS emp1, e2.name AS emp2, e1.department
FROM employees e1
JOIN employees e2
  ON e1.department = e2.department
 AND e1.id < e2.id
ORDER BY e1.department, e1.id;

Challenge 6: Top-Selling Product Category

Hard
Using all 4 tables (customers, orders, order_items, products), find the product category with the highest total revenue.
SELECT p.category, SUM(oi.qty * oi.price) AS revenue
FROM customers c
JOIN orders o       ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p     ON oi.product_id = p.id
GROUP BY p.category
ORDER BY revenue DESC
LIMIT 1;

Challenge 7: Employee, Manager, Grandmanager Chain

Hard
For each employee, show their manager and their manager's manager (grandmanager). Include all employees, even those at the top.
SELECT e.name AS employee,
       m.name AS manager,
       gm.name AS grandmanager
FROM employees e
LEFT JOIN employees m  ON e.manager_id = m.id
LEFT JOIN employees gm ON m.manager_id = gm.id
ORDER BY e.id;

Challenge 8: Customers Who Have Never Ordered Electronics

Hard
Find customers who have placed orders but never bought anything in the 'Electronics' category.
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE NOT EXISTS (
    SELECT 1
    FROM order_items oi
    JOIN products p ON oi.product_id = p.id
    WHERE oi.order_id = o.id AND p.category = 'Electronics'
);

Need to Review the Concepts?

Go back to the detailed notes for this chapter.

Read Chapter Notes

Want to master SQL and databases with a mentor?

Explore our MySQL Masterclass