SQL · Module 6

Answers and explanations · Join relationships and preserve missing matches

Compare these references after your own attempt. You can read them at any time; they do not record a live model or vendor run.

Exercise 06a

The order has Notebook and Pen lines, each with quantity 2. The product key supplies a description without changing the historical price definition.

SELECT i.order_id,i.line_no,p.product_name,i.quantity
FROM aabha_sql.order_items AS i JOIN aabha_sql.products AS p ON p.product_id=i.product_id
WHERE i.order_id=1001 ORDER BY i.line_no;

Expected output:

order_id	line_no	product_name	quantity
1001	1	Notebook	2
1001	2	Pen	2

Runnable answer: sql/solutions/06a.sql

Exercise 06b

Only Gita, customer 7, has no order at all. Hari has a cancelled order, so he does not belong in this result.

SELECT c.customer_id,c.customer_name FROM aabha_sql.customers AS c
LEFT JOIN aabha_sql.orders AS o ON o.customer_id=c.customer_id
WHERE o.order_id IS NULL ORDER BY c.customer_id;

Expected output:

customer_id	customer_name
7	Gita

Runnable answer: sql/solutions/06b.sql

Return to this module