Checkpoint: Aggregation and Joins

Question 1 of 5

• Each order’s fee appears once per item line, so orders with many lines weigh more in the average.
• AVG can’t be used together with a join.
• Nothing; an average isn’t affected by duplicated rows.
• The fee should be divided by the number of items first.

Question 2 of 5

• SELECT customer_id FROM orders WHERE COUNT(*) >= 5 AND status <> ‘cancelled’ GROUP BY customer_id
• SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) >= 5
• SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) >= 5 AND status <> ‘cancelled’
• SELECT customer_id FROM orders WHERE status <> ‘cancelled’ GROUP BY customer_id HAVING COUNT(*) >= 5

Question 3 of 5

• categories c JOIN products p ON p.category_id = c.category_id WHERE p.is_active = 1, then COUNT(*)
• categories c LEFT JOIN products p ON p.category_id = c.category_id AND p.is_active = 1, then COUNT(p.product_id)
• categories c LEFT JOIN products p ON p.category_id = c.category_id WHERE p.is_active = 1, then COUNT(*)
• categories c LEFT JOIN products p ON p.category_id = c.category_id AND p.is_active = 1, then COUNT(*)

Question 4 of 5

• SELECT p.name FROM products p LEFT JOIN returns r ON r.order_item_id = p.product_id WHERE r.return_id IS NULL
• SELECT p.name FROM products p JOIN order_items oi ON oi.product_id = p.product_id JOIN returns r ON r.order_item_id = oi.order_item_id WHERE r.return_id IS NULL
• SELECT p.name FROM products p WHERE NOT EXISTS (SELECT 1 FROM order_items oi JOIN returns r ON r.order_item_id = oi.order_item_id WHERE oi.product_id = p.product_id)

Question 5 of 5

• WHERE e.hire_date < m.hire_date
• WHERE m.hire_date < e.hire_date
• WHERE e.hire_date < e.manager_id
• WHERE e.employee_id < m.employee_id