SQL Quiz: Multi Table Joins
10 questions · exam conditions
0:00
Multi Table JoinsQuestion 1 of 10

The database contains Employees(employee_id, employee_name, manager_id, department_id) and Departments(department_id, department_name). manager_id refers to another employee and may be null. Every employee has a department. The report must include all employees, even those without managers, and show both the employee's department and the manager's department when available.

Which query correctly produces the requested report?

SELECT e.employee_name, ed.department_name, m.employee_name, md.department_name FROM Employees e JOIN Departments ed ON ed.department_id = e.department_id LEFT JOIN Employees m ON m.employee_id = e.manager_id LEFT JOIN Departments md ON md.department_id = e.department_id;
SELECT e.employee_name, ed.department_name, m.employee_name, md.department_name FROM Employees e JOIN Departments ed ON ed.department_id = e.department_id JOIN Employees m ON m.employee_id = e.manager_id LEFT JOIN Departments md ON md.department_id = m.department_id;
SELECT e.employee_name, ed.department_name, m.employee_name, md.department_name FROM Employees e JOIN Departments ed ON ed.department_id = e.department_id LEFT JOIN Employees m ON m.employee_id = e.manager_id LEFT JOIN Departments md ON md.department_id = m.department_id;
SELECT e.employee_name, ed.department_name, m.employee_name, md.department_name FROM Employees e JOIN Departments ed ON ed.department_id = e.department_id LEFT JOIN Employees m ON m.manager_id = e.employee_id LEFT JOIN Departments md ON md.department_id = m.department_id;
← Back to quizzes

SQL Quiz

SQL Quiz: Multi Table Joins

Practice Multi Table Joins in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Multi Table Joins, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

The database contains Employees(employee_id, employee_name, manager_id, department_id) and Departments(department_id, department_name). manager_id refers to another employee and may be null. Every employee has a department. The report must include all employees, even those without managers, and show both the employee's department and the manager's department when available.

Which query correctly produces the requested report?

  1. SELECT e.employee_name, ed.department_name, m.employee_name, md.department_name FROM Employees e JOIN Departments ed ON ed.department_id = e.department_id LEFT JOIN Employees m ON m.employee_id = e.manager_id LEFT JOIN Departments md ON md.department_id = e.department_id;
  2. SELECT e.employee_name, ed.department_name, m.employee_name, md.department_name FROM Employees e JOIN Departments ed ON ed.department_id = e.department_id JOIN Employees m ON m.employee_id = e.manager_id LEFT JOIN Departments md ON md.department_id = m.department_id;
  3. SELECT e.employee_name, ed.department_name, m.employee_name, md.department_name FROM Employees e JOIN Departments ed ON ed.department_id = e.department_id LEFT JOIN Employees m ON m.employee_id = e.manager_id LEFT JOIN Departments md ON md.department_id = m.department_id; (correct answer)
  4. SELECT e.employee_name, ed.department_name, m.employee_name, md.department_name FROM Employees e JOIN Departments ed ON ed.department_id = e.department_id LEFT JOIN Employees m ON m.manager_id = e.employee_id LEFT JOIN Departments md ON md.department_id = m.department_id;
Explanation: When a query needs to preserve all rows from one table while optionally matching rows from another, you need a LEFT JOIN. This question tests your ability to chain multiple LEFT JOINs correctly across a self-referencing table and a lookup table. The report requires every employee (even those without managers), the employee's own department, the manager's name when one exists, and the manager's department when one exists. The correct approach in C is: INNER JOIN Departments to get the employee's department (every employee has one, so a regular JOIN is safe), then LEFT JOIN Employees on m.employee_id = e.manager_id to retrieve the manager (null-safe for employees without managers), then LEFT JOIN Departments again using m.department_id to get the manager's department. Each step logically follows the previous one, and nulls propagate naturally when no manager exists. A is wrong because the final LEFT JOIN uses e.department_id instead of m.department_id — it joins the manager's department lookup back to the employee's department, so you'd always see the employee's department twice rather than the manager's actual department. B is wrong because it uses an INNER JOIN for the manager lookup (JOIN Employees m). This eliminates every employee whose manager_id is null, directly violating the requirement to include all employees. D is wrong because the LEFT JOIN condition is reversed: m.manager_id = e.employee_id finds employees managed by the current employee, not the current employee's own manager. A useful rule of thumb: when chaining optional lookups, each LEFT JOIN's ON clause should reference the alias from the immediately preceding joined table, following the chain of relationships step by step.

Question 2

The database contains Customers, Orders, and OrderItems. Ann has no orders. Ben has order 10, which has no item rows. Cara has order 20, which has two item rows. The query is: SELECT c.name, COUNT(DISTINCT o.order_id) AS order_count, COUNT(oi.product_id) AS item_count FROM Customers c LEFT JOIN Orders o ON o.customer_id = c.customer_id LEFT JOIN OrderItems oi ON oi.order_id = o.order_id GROUP BY c.name;

Which counts does the query return?

  1. Ann: 0,00,0; Ben: 1,01,0; Cara: 1,21,2 (correct answer)
  2. Ann: 1,01,0; Ben: 1,11,1; Cara: 2,22,2
  3. Ann: 0,00,0; Ben: 0,00,0; Cara: 2,22,2
  4. Ann: 1,11,1; Ben: 1,11,1; Cara: 1,21,2
Explanation: When chaining multiple LEFT JOINs, you need to trace exactly what rows each customer contributes to the result set before aggregation — this question tests your understanding of how NULLs propagate and how COUNT behaves differently depending on what you pass it. Here's the row-level picture: Ann has no orders, so both o.order_id and oi.product_id are NULL for her single result row. Ben has order 10 but no item rows, so o.order_id = 10 but oi.product_id is NULL — one result row. Cara has order 20 with two items, producing two result rows, each with o.order_id = 20 and a non-NULL product_id. Now apply the aggregate functions. COUNT(DISTINCT o.order_id) ignores NULLs and counts distinct values; COUNT(oi.product_id) also ignores NULLs but counts all non-NULL occurrences. For Ann: both columns are NULL → 0,00, 0. For Ben: one non-NULL order_id, one NULL product_id → 1,01, 0. For Cara: one distinct order_id (20 appears twice), two non-NULL product_ids → 1,21, 2. This confirms answer A. Choice B is wrong because it implies Ann has an order and Ben has an item — neither is true. Choice C gives Ben 0,00, 0, incorrectly treating his non-NULL order_id as NULL. Choice D gives Ann counts of 1,11, 1, which would require actual data rows she doesn't have. The key study tip: COUNT(column) never counts NULLs — so a LEFT JOIN that produces a NULL-filled row contributes zero to any COUNT(column) call, not one.

Question 3

The database contains Suppliers(supplier_id), Products(product_id, supplier_id), and OrderItems(order_id, product_id). A supplier may have no products, and a supplier may have a mixture of products that have and have not been ordered.

Which query returns every supplier for which no supplied product has ever appeared in an order item?

  1. SELECT s.supplier_id FROM Suppliers s WHERE EXISTS (SELECT 1 FROM Products p WHERE p.supplier_id = s.supplier_id AND NOT EXISTS (SELECT 1 FROM OrderItems oi WHERE oi.product_id = p.product_id));
  2. SELECT DISTINCT s.supplier_id FROM Suppliers s LEFT JOIN Products p ON p.supplier_id = s.supplier_id LEFT JOIN OrderItems oi ON oi.product_id = p.product_id WHERE oi.order_id IS NULL;
  3. SELECT s.supplier_id FROM Suppliers s WHERE NOT EXISTS (SELECT 1 FROM Products p WHERE p.supplier_id = s.supplier_id);
  4. SELECT s.supplier_id FROM Suppliers s WHERE NOT EXISTS (SELECT 1 FROM Products p JOIN OrderItems oi ON oi.product_id = p.product_id WHERE p.supplier_id = s.supplier_id); (correct answer)
Explanation: When a question asks you to find suppliers where none of their products have ever been ordered, you need to think in terms of double negation: a supplier qualifies if there does NOT exist even a single product of theirs that appears in any order. This is a classic use case for nested NOT EXISTS. Answer D captures this logic precisely. For each supplier, the subquery looks for any product belonging to that supplier that has a matching row in OrderItems (via the JOIN). If no such product exists, NOT EXISTS returns true and the supplier is included. This correctly handles the edge case of suppliers with no products at all — since there are no products to join with OrderItems, the subquery returns nothing, and NOT EXISTS evaluates to true, including those suppliers as well. Answer A is tempting but logically inverted. It uses NOT EXISTS on the OrderItems check, but the outer condition is EXISTS — meaning it finds suppliers who have at least one product that hasn't been ordered. That's the opposite of the requirement. Answer B uses a double LEFT JOIN and filters on oi.order_id IS NULL. The problem is that a supplier with a mix of ordered and unordered products will still have some NULL rows after the left join, causing them to incorrectly appear in the results. The DISTINCT doesn't fix this logic flaw. Answer C only checks whether a supplier has any products at all — it completely ignores OrderItems and would return suppliers with zero products, missing the entire order-history condition. Study tip: When filtering on an absence across a whole group, reach for NOT EXISTS with a correlated subquery. LEFT JOIN ... WHERE IS NULL is unreliable when rows have mixed matches.

Question 4

The database contains Students(student_id), Enrollments(student_id, course_id), Courses(course_id, instructor_id), and Instructors(instructor_id). A student may take several courses from the same instructor, and students with no enrollments must appear with a count of zero.

Which query returns each student and the number of different instructors teaching that student's enrolled courses?

  1. SELECT s.student_id, COUNT(DISTINCT i.instructor_id) FROM Students s LEFT JOIN Enrollments e ON e.student_id = s.student_id LEFT JOIN Courses c ON c.course_id = e.course_id LEFT JOIN Instructors i ON i.instructor_id = e.student_id GROUP BY s.student_id;
  2. SELECT s.student_id, COUNT(i.instructor_id) FROM Students s LEFT JOIN Enrollments e ON e.student_id = s.student_id LEFT JOIN Courses c ON c.course_id = e.course_id LEFT JOIN Instructors i ON i.instructor_id = c.instructor_id GROUP BY s.student_id;
  3. SELECT s.student_id, COUNT(DISTINCT i.instructor_id) FROM Students s JOIN Enrollments e ON e.student_id = s.student_id JOIN Courses c ON c.course_id = e.course_id JOIN Instructors i ON i.instructor_id = c.instructor_id GROUP BY s.student_id;
  4. SELECT s.student_id, COUNT(DISTINCT i.instructor_id) FROM Students s LEFT JOIN Enrollments e ON e.student_id = s.student_id LEFT JOIN Courses c ON c.course_id = e.course_id LEFT JOIN Instructors i ON i.instructor_id = c.instructor_id GROUP BY s.student_id; (correct answer)
Explanation: When a question asks you to count distinct related entities while preserving rows with no matches, you need to think about two things simultaneously: JOIN type (to keep unenrolled students) and aggregate function (to avoid double-counting). The correct approach, as in D, is to chain three LEFT JOINs — Students → Enrollments → Courses → Instructors — using the proper foreign keys at each step, then use COUNT(DISTINCT i.instructor_id). The LEFT JOINs ensure students with no enrollments still appear (their instructor columns will be NULL, and COUNT on NULL returns 0 automatically). The DISTINCT handles the case where a student takes multiple courses from the same instructor, preventing that instructor from being counted twice. A is close in structure but contains a critical typo in the final join condition: i.instructor_id = e.student_id joins instructors to student IDs — a nonsensical cross-entity match that will produce wrong or empty results for most data. B uses the correct join chain and keys, but omits DISTINCT from the COUNT. If a student has three courses all taught by the same instructor, they'd be counted as three distinct instructors instead of one. This is a classic overcounting trap. C uses COUNT(DISTINCT i.instructor_id) correctly and joins on the right keys, but uses inner JOINs (JOIN) instead of LEFT JOINs. This silently drops students with no enrollments, violating the requirement that unenrolled students appear with a count of zero. Study tip: On SQL questions involving "count of distinct X per Y, including zeros," always pair LEFT JOIN (to preserve all rows) with COUNT(DISTINCT ...) (to avoid overcounting). These two requirements are almost always tested together.

Question 5

The database contains Departments(department_id), Employees(employee_id, department_id), and Certifications(certification_id, employee_id, status). Some departments have no employees, and some employees have no active certifications.

Which query returns every department and the number of active certification rows held by its employees, reporting zero when none exist?

  1. SELECT d.department_id, COUNT(c.certification_id) FROM Departments d LEFT JOIN Employees e ON e.department_id = d.department_id LEFT JOIN Certifications c ON c.employee_id = e.employee_id AND c.status = 'Active' GROUP BY d.department_id; (correct answer)
  2. SELECT d.department_id, COUNT(c.certification_id) FROM Departments d LEFT JOIN Employees e ON e.department_id = d.department_id LEFT JOIN Certifications c ON c.employee_id = e.employee_id WHERE c.status = 'Active' GROUP BY d.department_id;
  3. SELECT d.department_id, COUNT(*) FROM Departments d LEFT JOIN Employees e ON e.department_id = d.department_id LEFT JOIN Certifications c ON c.employee_id = e.employee_id AND c.status = 'Active' GROUP BY d.department_id;
  4. SELECT d.department_id, COUNT(c.certification_id) FROM Departments d JOIN Employees e ON e.department_id = d.department_id LEFT JOIN Certifications c ON c.employee_id = e.employee_id AND c.status = 'Active' GROUP BY d.department_id;
Explanation: When chaining multiple joins and needing to preserve all rows from a "master" table (here, Departments), you need to think carefully about two things: where your filter conditions live, and which joins are LEFT vs. INNER. Option A is correct because it uses two LEFT JOINs, ensuring every department appears even if it has no employees or no active certifications. Critically, the filter c.status = 'Active' is placed in the JOIN condition (the ON clause), not in a WHERE clause. This means departments with no matching certifications still appear in the result — their certification columns simply come back as NULL — and COUNT(c.certification_id) correctly counts NULL as zero. Option B is the most tempting trap. It looks almost identical to A, but moves c.status = 'Active' into a WHERE clause. Filtering in WHERE happens after the LEFT JOIN, which eliminates any row where c.status is NULL (i.e., employees with no active certifications and departments with no employees). This effectively converts the LEFT JOIN into an INNER JOIN, dropping departments you want to keep. Option C uses COUNT(*) instead of COUNT(c.certification_id). Even though the joins are correct, COUNT(*) counts every row — including the NULL placeholder row that LEFT JOIN produces — so departments with no active certifications would report 1 instead of 0. Option D uses an INNER JOIN between Departments and Employees, which immediately excludes any department that has no employees, breaking the "every department" requirement. Strategy tip: Whenever you need zero-reporting with LEFT JOINs, always put your filter conditions in the ON clause — never in WHERE. A WHERE filter on a nullable column silently destroys your outer join.

Question 6

The database contains Customers(customer_id, region), Orders(order_id, customer_id), OrderItems(order_id, product_id), and Products(product_id, category). A customer may place multiple orders, and an order may contain multiple products.

Which query returns each customer in the West region who has ordered at least one Electronics product, with each qualifying customer appearing exactly once?

  1. SELECT DISTINCT c.customer_id FROM Customers c JOIN Orders o ON o.customer_id = c.customer_id JOIN OrderItems oi ON oi.order_id = o.order_id JOIN Products p ON p.product_id = oi.product_id WHERE c.region = 'West' AND p.category = 'Electronics'; (correct answer)
  2. SELECT c.customer_id FROM Customers c JOIN Orders o ON o.customer_id = c.customer_id JOIN OrderItems oi ON oi.order_id = o.order_id JOIN Products p ON p.product_id = oi.product_id WHERE c.region = 'West' AND p.category = 'Electronics';
  3. SELECT DISTINCT c.customer_id FROM Customers c JOIN Orders o ON o.customer_id = c.customer_id JOIN OrderItems oi ON oi.order_id = o.order_id LEFT JOIN Products p ON p.product_id = oi.product_id AND p.category = 'Electronics' WHERE c.region = 'West';
  4. SELECT DISTINCT c.customer_id FROM Customers c JOIN Orders o ON o.customer_id = c.customer_id JOIN OrderItems oi ON oi.order_id = o.order_id JOIN Products p ON p.product_id = o.order_id WHERE c.region = 'West' AND p.category = 'Electronics';
Explanation: When a multi-table JOIN produces one row per matching combination, a single customer who ordered multiple Electronics products will appear multiple times in the result. Your job is to recognize when DISTINCT is needed and whether the JOIN conditions are logically correct. Answer A is correct because it joins all four tables using the right keys, filters for West-region customers with Electronics products in the WHERE clause, and uses SELECT DISTINCT to collapse duplicate customer_ids into a single row per qualifying customer. Every piece is in place. Answer B uses the identical JOIN logic and WHERE clause but omits DISTINCT. Because one customer can have multiple orders, and each order can have multiple Electronics items, that customer's ID will appear once for every matching row — potentially dozens of times. The question explicitly requires each customer to appear exactly once, so B fails. Answer C looks reasonable at first, but the LEFT JOIN on Products with the category filter moved into the ON clause is a trap. A LEFT JOIN retains all OrderItems rows even when no matching Electronics product exists — meaning customers who never ordered Electronics still pass through. The WHERE clause only filters by region, so non-Electronics customers appear in the result, violating the requirement. Answer D contains a critical JOIN error: it joins Products on p.product_id = o.order_id instead of p.product_id = oi.product_id. This matches product IDs against order IDs — completely unrelated columns — producing meaningless or empty results. Strategy tip: Whenever a JOIN can multiply rows (one-to-many relationships), ask yourself "do I need DISTINCT or GROUP BY to collapse duplicates?" Also always double-check every ON condition — wrong join keys are a common, easy-to-miss error in multi-table queries.

Question 7

A multi-tenant database contains Accounts(tenant_id, account_id), Invoices(tenant_id, invoice_id, account_id), and Payments(tenant_id, payment_id, invoice_id, amount). Account and invoice identifiers are unique only within a tenant. Accounts without invoices or payments must still be included.

Which query correctly calculates the total payment amount for every account without associating records from different tenants?

  1. SELECT a.tenant_id, a.account_id, COALESCE(SUM(p.amount), 0) FROM Accounts a LEFT JOIN Invoices i ON i.tenant_id = a.tenant_id AND i.account_id = a.account_id LEFT JOIN Payments p ON p.tenant_id = i.tenant_id AND p.invoice_id = i.invoice_id GROUP BY a.tenant_id, a.account_id; (correct answer)
  2. SELECT a.tenant_id, a.account_id, COALESCE(SUM(p.amount), 0) FROM Accounts a LEFT JOIN Invoices i ON i.account_id = a.account_id LEFT JOIN Payments p ON p.tenant_id = i.tenant_id AND p.invoice_id = i.invoice_id GROUP BY a.tenant_id, a.account_id;
  3. SELECT a.tenant_id, a.account_id, COALESCE(SUM(p.amount), 0) FROM Accounts a LEFT JOIN Invoices i ON i.tenant_id = a.tenant_id AND i.account_id = a.account_id LEFT JOIN Payments p ON p.invoice_id = i.invoice_id GROUP BY a.tenant_id, a.account_id;
  4. SELECT a.tenant_id, a.account_id, COALESCE(SUM(p.amount), 0) FROM Accounts a LEFT JOIN Invoices i ON i.tenant_id = a.tenant_id AND i.account_id = a.account_id JOIN Payments p ON p.tenant_id = i.tenant_id AND p.invoice_id = i.invoice_id GROUP BY a.tenant_id, a.account_id;
Explanation: When working with multi-tenant databases, your biggest concern is tenant isolation — ensuring that JOIN conditions never accidentally mix data across tenants. Since identifiers like account_id and invoice_id are only unique within a tenant, every JOIN must include tenant_id as part of the condition. You also need to preserve accounts that have no invoices or payments, which requires LEFT JOINs throughout. Option A is correct because it includes tenant_id in every JOIN condition. The first JOIN matches invoices to accounts using both i.tenant_id = a.tenant_id AND i.account_id = a.account_id, and the second JOIN matches payments using both p.tenant_id = i.tenant_id AND p.invoice_id = i.invoice_id. Both JOINs are LEFT JOINs, so accounts without invoices or payments still appear. COALESCE(SUM(p.amount), 0) handles the NULL case cleanly. Option B is wrong because the first JOIN omits the tenant filter — it joins on i.account_id = a.account_id alone. Since account IDs repeat across tenants, this will cross-contaminate records, pulling invoices from the wrong tenant into your results. Option C is wrong because the second JOIN omits the tenant filter — it joins Payments to Invoices on p.invoice_id = i.invoice_id only. Since invoice IDs are not globally unique, payments from one tenant could be matched to invoices from another. Option D is wrong because the second JOIN is an INNER JOIN rather than a LEFT JOIN. This silently drops any accounts that have invoices but no payments, violating the requirement to include all accounts. Study tip: In multi-tenant schema questions, scan every JOIN condition and ask yourself: "Is the tenant scoped at every link in the chain?" One missing tenant_id anywhere can corrupt your entire result set.

Question 8

The database contains Sales(sale_id, product_id, sale_date, currency_code), ProductPrices(product_id, effective_from, effective_to, price), and ExchangeRates(currency_code, rate_date, usd_rate). Price periods are half-open: a price applies when effective_from is on or before the sale date and the sale date is before effective_to. Exchange rates are uniquely identified by both currency and date.

Which query attaches the applicable product price and exchange rate to each sale without matching unrelated products or currencies?

  1. SELECT s.sale_id, pp.price, er.usd_rate FROM Sales s JOIN ProductPrices pp ON pp.effective_from <= s.sale_date AND s.sale_date < pp.effective_to JOIN ExchangeRates er ON er.currency_code = s.currency_code AND er.rate_date = s.sale_date;
  2. SELECT s.sale_id, pp.price, er.usd_rate FROM Sales s JOIN ProductPrices pp ON pp.product_id = s.product_id AND pp.effective_from <= s.sale_date AND s.sale_date <= pp.effective_to JOIN ExchangeRates er ON er.currency_code = s.currency_code AND er.rate_date = s.sale_date;
  3. SELECT s.sale_id, pp.price, er.usd_rate FROM Sales s JOIN ProductPrices pp ON pp.product_id = s.product_id AND pp.effective_from <= s.sale_date AND s.sale_date < pp.effective_to JOIN ExchangeRates er ON er.currency_code = s.currency_code AND er.rate_date = s.sale_date; (correct answer)
  4. SELECT s.sale_id, pp.price, er.usd_rate FROM Sales s JOIN ProductPrices pp ON pp.product_id = s.product_id AND pp.effective_from <= s.sale_date AND s.sale_date < pp.effective_to JOIN ExchangeRates er ON er.rate_date = s.sale_date;
Explanation: When joining across multiple tables with range-based and lookup conditions, you need to verify every join condition independently — a missing condition on one table can silently multiply your result rows with unrelated data. Here, each sale needs exactly one matching price (same product, correct date range) and exactly one exchange rate (same currency, same date). Option C satisfies all of this: it joins ProductPrices on pp.product_id = s.product_id to filter by product, then applies the half-open interval pp.effective_from <= s.sale_date AND s.sale_date < pp.effective_to exactly as the schema specifies. It then joins ExchangeRates on both er.currency_code = s.currency_code and er.rate_date = s.sale_date. Every condition is present and correct — making C the right answer. Option A is missing pp.product_id = s.product_id entirely. Without it, every sale matches prices for all products whose date ranges overlap, exploding the result set with irrelevant prices. Option B is almost correct but uses a closed interval (s.sale_date <= pp.effective_to) instead of the required half-open interval (s.sale_date < pp.effective_to). This means a sale on the exact boundary date would incorrectly match two consecutive price periods simultaneously, creating duplicates. Option D omits er.currency_code = s.currency_code from the exchange rate join, so each sale would match exchange rates for every currency on that date — attaching unrelated currencies. A useful pattern to remember: when a join involves both an equality condition (like product_id) and a range condition, dropping either one silently corrupts your results. Always verify that multi-column relationships are fully represented in the ON clause.

Question 9

The database contains Customers(customer_id), Orders(order_id, customer_id), and OrderItems(order_id, product_id). The report must include every customer and count only orders containing at least one item. An order containing several items must be counted once, and customers with no qualifying orders must receive zero.

Which query produces the required count?

  1. SELECT c.customer_id, COUNT(DISTINCT o.order_id) FROM Customers c LEFT JOIN Orders o ON o.customer_id = c.customer_id JOIN OrderItems oi ON oi.order_id = o.order_id GROUP BY c.customer_id;
  2. SELECT c.customer_id, COUNT(DISTINCT o.order_id) FROM Customers c LEFT JOIN (Orders o INNER JOIN OrderItems oi ON oi.order_id = o.order_id) ON o.customer_id = c.customer_id GROUP BY c.customer_id; (correct answer)
  3. SELECT c.customer_id, COUNT(DISTINCT o.order_id) FROM Customers c LEFT JOIN Orders o ON o.customer_id = c.customer_id LEFT JOIN OrderItems oi ON oi.order_id = o.order_id GROUP BY c.customer_id;
  4. SELECT c.customer_id, COUNT(oi.order_id) FROM Customers c LEFT JOIN (Orders o INNER JOIN OrderItems oi ON oi.order_id = o.order_id) ON o.customer_id = c.customer_id GROUP BY c.customer_id;
Explanation: When joining multiple tables with a mix of LEFT and INNER joins, join precedence and grouping determine which rows survive. The core challenge here is preserving all customers (requiring a LEFT JOIN) while simultaneously filtering to only orders that have at least one item (requiring an INNER JOIN between Orders and OrderItems). Option B solves this correctly by parenthesizing the INNER JOIN: LEFT JOIN (Orders o INNER JOIN OrderItems oi ON oi.order_id = o.order_id). This forces the database to first compute the inner result — orders matched with their items — and then LEFT JOIN that combined set to Customers. Customers with no qualifying orders get NULL for the order columns, and COUNT(DISTINCT o.order_id) correctly returns 0 for them since COUNT ignores NULLs. Orders with multiple items are deduplicated by DISTINCT. Option A fails because the bare JOIN OrderItems after the LEFT JOIN is treated as an INNER JOIN applied to the already-joined result. This silently eliminates customers who have no orders with items — they disappear from the output entirely instead of receiving zero. Option C uses two LEFT JOINs chained together. This preserves all customers, but it also preserves orders that have no items in OrderItems, which violates the requirement to count only item-containing orders. A customer with an empty order would have that order counted. Option D uses the same correct parenthesized structure as B but counts COUNT(oi.order_id) without DISTINCT. A single order containing three items would be counted three times instead of once. Study tip: When you need "all of A, but only matching B+C," parenthesize the INNER JOIN between B and C, then LEFT JOIN that bundle to A — and always use COUNT(DISTINCT ...) when rows can multiply through joins.

Question 10

The database contains Orders(order_id), OrderItems(order_id, quantity, unit_price), and Payments(order_id, amount). An order may have multiple item rows and multiple payment rows. Legitimate item totals and payment amounts may also have identical numeric values.

Which query returns one row per order with the correct item total and payment total, including orders that have no items or no payments?

  1. SELECT o.order_id, COALESCE(SUM(oi.quantity * oi.unit_price), 0), COALESCE(SUM(p.amount), 0) FROM Orders o LEFT JOIN OrderItems oi ON oi.order_id = o.order_id LEFT JOIN Payments p ON p.order_id = o.order_id GROUP BY o.order_id;
  2. WITH it AS (SELECT order_id, SUM(quantity * unit_price) AS item_total FROM OrderItems GROUP BY order_id), pt AS (SELECT order_id, SUM(amount) AS payment_total FROM Payments GROUP BY order_id) SELECT o.order_id, COALESCE(it.item_total, 0), COALESCE(pt.payment_total, 0) FROM Orders o LEFT JOIN it ON it.order_id = o.order_id LEFT JOIN pt ON pt.order_id = o.order_id; (correct answer)
  3. SELECT o.order_id, COALESCE(SUM(DISTINCT oi.quantity * oi.unit_price), 0), COALESCE(SUM(DISTINCT p.amount), 0) FROM Orders o LEFT JOIN OrderItems oi ON oi.order_id = o.order_id LEFT JOIN Payments p ON p.order_id = o.order_id GROUP BY o.order_id;
  4. WITH it AS (SELECT order_id, SUM(quantity * unit_price) AS item_total FROM OrderItems GROUP BY order_id) SELECT o.order_id, COALESCE(SUM(it.item_total), 0), COALESCE(SUM(p.amount), 0) FROM Orders o LEFT JOIN it ON it.order_id = o.order_id LEFT JOIN Payments p ON p.order_id = o.order_id GROUP BY o.order_id;
Explanation: When joining multiple one-to-many tables in a single query, you must watch for fan-out inflation — a subtle but critical trap where rows multiply incorrectly before aggregation happens. Consider what happens when you join Orders → OrderItems → Payments in one pass: if an order has 3 item rows and 2 payment rows, the join produces 6 combined rows. Any SUM applied afterward counts values multiple times, producing inflated totals. The safe solution is to pre-aggregate each child table separately before joining, so each order maps to exactly one summary row per table. That's precisely what B does — it uses two CTEs (it and pt) to compute totals independently, then joins those already-summarized results to Orders. COALESCE handles orders with no items or payments, and LEFT JOIN ensures no orders are dropped. This is the correct answer. A falls into the fan-out trap directly: both OrderItems and Payments are joined raw, and SUM inflates results when either table has multiple matching rows. C attempts to fix A's problem using SUM(DISTINCT ...), but this is unreliable — the passage explicitly states that legitimate values can be numerically identical, so DISTINCT will silently discard real duplicate amounts, producing wrong totals. D partially solves the problem by pre-aggregating items into a CTE, but then joins Payments raw. The final SUM over it.item_total combined with multiple payment rows still causes inflation on the items side, and payments are miscounted too. Study tip: Whenever you need aggregates from two or more child tables, always pre-aggregate each one separately (via CTEs or subqueries) before joining — never let two unresolved one-to-many relationships meet in the same FROM clause.