Intermediate Practice: Joins and Aggregation

Joins

1. List every employee with their department name.

SELECT e.first_name, d.name AS department
FROM   employees e
LEFT JOIN departments d ON d.id = e.dept_id
ORDER BY d.name, e.first_name;

LEFT JOIN, not INNER: Nikhil has no department and an inner join would silently drop him.

2. List every department with its employee count, including empty departments.

SELECT d.name, COUNT(e.id) AS headcount
FROM   departments d
LEFT JOIN employees e ON e.dept_id = d.id
GROUP BY d.name
ORDER BY headcount DESC;

COUNT(e.id), not COUNT(*). The unmatched department still produces one row, so COUNT(*) would report 1 instead of 0.

3. List every order with its customer name.

SELECT o.id, o.order_date, o.total, c.name AS customer
FROM   orders o
JOIN   customers c ON c.id = o.customer_id
ORDER BY o.order_date DESC;

4. List every employee with their manager's name.

SELECT e.first_name AS employee, m.first_name AS manager
FROM   employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY manager, employee;

5. Find customers who have never placed an order.

SELECT c.name
FROM   customers c
WHERE  NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

6. Find products that have never been sold.

SELECT p.name
FROM   products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE  oi.product_id IS NULL;

7. List every project with the names of everyone assigned to it.

SELECT p.name AS project, e.first_name AS member, a.hours
FROM   projects p
LEFT JOIN assignments a ON a.project_id  = p.id
LEFT JOIN employees   e ON e.id          = a.employee_id
ORDER BY p.name, e.first_name;

Grouping

8. Total revenue and order count per customer.

SELECT c.name,
       COUNT(o.id)               AS orders,
       COALESCE(SUM(o.total), 0) AS revenue
FROM   customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY revenue DESC;

9. Average salary per department, excluding inactive staff.

SELECT d.name, ROUND(AVG(e.salary), 2) AS avg_salary, COUNT(*) AS headcount
FROM   employees e
JOIN   departments d ON d.id = e.dept_id
WHERE  e.status = 'active'
GROUP BY d.name
ORDER BY avg_salary DESC;

10. Customers with more than one order.

SELECT c.name, COUNT(*) AS orders
FROM   customers c
JOIN   orders o ON o.customer_id = c.id
GROUP BY c.name
HAVING COUNT(*) > 1;

WHERE filters rows, HAVING filters groups. The order count does not exist until the grouping has happened.

11. Revenue per product category, shipped orders only.

SELECT p.category,
       SUM(oi.quantity)                   AS units,
       SUM(oi.quantity * oi.unit_price)   AS revenue
FROM   order_items oi
JOIN   products p ON p.id = oi.product_id
JOIN   orders   o ON o.id = oi.order_id
WHERE  o.status = 'shipped'
GROUP BY p.category
ORDER BY revenue DESC;

12. Total hours worked per employee, including those on no project.

SELECT e.first_name, COALESCE(SUM(a.hours), 0) AS total_hours
FROM   employees e
LEFT JOIN assignments a ON a.employee_id = e.id
GROUP BY e.first_name
ORDER BY total_hours DESC;

13. Order count by status per customer, as columns.

SELECT c.name,
       COUNT(*)                                            AS total,
       COUNT(CASE WHEN o.status = 'shipped'   THEN 1 END)  AS shipped,
       COUNT(CASE WHEN o.status = 'pending'   THEN 1 END)  AS pending,
       COUNT(CASE WHEN o.status = 'cancelled' THEN 1 END)  AS cancelled
FROM   customers c
JOIN   orders o ON o.customer_id = c.id
GROUP BY c.name;

No ELSE inside COUNT(CASE ...). COUNT ignores NULL, which is exactly what makes the conditional count work.

14. Each order with its line count and line value, without inflating the order total.

SELECT o.id, o.total,
       li.line_count,
       li.line_value
FROM   orders o
LEFT JOIN (SELECT order_id,
                  COUNT(*)                   AS line_count,
                  SUM(quantity * unit_price) AS line_value
           FROM   order_items
           GROUP BY order_id) li ON li.order_id = o.id
ORDER BY o.id;

Aggregate the many side first. Joining directly to order_items and summing o.total would count each order once per line item.

15. Countries with more than one customer and their total revenue.

SELECT c.country,
       COUNT(DISTINCT c.id)      AS customers,
       COALESCE(SUM(o.total), 0) AS revenue
FROM   customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.country
HAVING COUNT(DISTINCT c.id) > 1
ORDER BY revenue DESC;

Check your understanding

1. In question 2, what does COUNT(*) return for the empty department, and why?
2. In question 14, what would SUM(o.total) return if you joined order_items directly?
3. Why does question 9 use WHERE for the status filter rather than HAVING?