Joins & Aggregations — Series 2

Preview — 3 of 10 questions

What is the difference between UNION and UNION ALL?

javascript

SELECT city FROM current_customers
UNION
SELECT city FROM past_customers;

AUNION removes duplicate rows from the combined result; UNION ALL keeps all rows, including duplicates, and is generally faster since it skips deduplication

BUNION ALL removes duplicates; UNION keeps all rows

CUNION and UNION ALL are functionally identical — only the keyword differs

DUNION requires both queries to select from the same table; UNION ALL does not

Given a single employees table where manager_id references another row in the same table, which query lists each employee alongside their manager's name (including employees with no manager)?

javascript

-- employees(id, name, manager_id)

ASELECT e.name, m.name FROM employees e, employees m WHERE e.id = m.id;

BSELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;

CSELECT name, manager_id FROM employees GROUP BY manager_id;

DA self-join is not possible in SQL — you must split the data into two separate tables

What does the price_tier column contain for a product priced at exactly 45?

javascript

SELECT name,
  CASE
    WHEN price < 20 THEN 'Cheap'
    WHEN price < 50 THEN 'Medium'
    ELSE 'Expensive'
  END AS price_tier
FROM products;

A'Cheap'

B'Expensive'

C'Medium'

DNULL, because no WHEN matches exactly

Answer all 10 questions (7 more), see explanations for every answer, and track your score.