Checkpoint: SQL in the Real World

Question 1 of 5

• SELECT COUNT(DISTINCT order_id) FROM order_items
• SELECT o.* FROM orders o JOIN order_items oi ON oi.order_id = o.order_id WHERE oi.order_item_id IS NULL
• SELECT * FROM order_items WHERE order_id IS NULL
• SELECT o.* FROM orders o WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.order_id = o.order_id)

Question 2 of 5

• Add a second index on customer_id.
• Write WHERE customer_id = 42 so the indexed column stands alone.
• Add ORDER BY customer_id.
• Run ANALYZE so SQLite notices the index.

Question 3 of 5

• COUNT(*) FILTER (WHERE status = ‘returned’)
• SUM(IIF(status = ‘returned’, 1, 0))
• COUNT(status = ‘returned’)
• Keep it; PostgreSQL treats booleans as 0 and 1.

Question 4 of 5

• Converting all keywords to uppercase.
• Replacing GROUP BY 1 with the column name.
• Removing a join to a table none of whose columns are used.
• Adding a comment above every line.

Question 5 of 5

• Delete the offending order lines from the analysis without mentioning it.
• Set launched_on to the first order date in your query and say nothing.
• Ignore it, because launch dates don’t affect revenue.
• Report the discrepancy, state how you treated those products, and ask the product team what launched_on really records.