The requirement “include users with zero orders and show 0” is solved by combining LEFT JOIN and COALESCE. This applies the NULL padding from Basic Q2 in a more practical way with aggregate functions and COALESCE.

SELECT
  u.name,
  COUNT(o.order_id)          AS order_count,    -- skip NULLs and return 0
  COALESCE(SUM(o.amount), 0) AS total_amount    -- convert SUM(NULL) → NULL to 0
FROM users AS u
LEFT JOIN orders AS o ON u.user_id = o.user_id
GROUP BY u.user_id, u.name;

For every user in users, retrieve the order count (order_count) and total amount (total_amount). Display users with zero orders as 0. Output the results in descending order of total amount.

※ Yamada (user_id=3) has no order in orders.

SELECT
  u.name,
  COUNT(o.order_id)          AS order_count,  -- COUNT(column) skips NULL → a zero-order user automatically gets 0
  COALESCE(SUM(o.amount), 0) AS total_amount  -- SUM(NULL) = NULL, so COALESCE converts it to 0
FROM users AS u
LEFT JOIN orders AS o                         -- keep every user (Yamada is NULL-padded)
  ON u.user_id = o.user_id
GROUP BY u.user_id, u.name                    -- group together the NULL-padded row too
ORDER BY total_amount DESC;

/*
  Execution order:
  1. FROM users AS u             → read users
  2. LEFT JOIN orders AS o       → match and NULL-pad
  3. GROUP BY u.user_id, u.name  → group by user
  4. COUNT / COALESCE(SUM,0)     → count and total (NULL corrected to 0)
  5. SELECT / ORDER BY           → output in total_amount DESC order
  */

A SELF JOIN is an operation that JOINs the same table twice. Giving the two references different aliases lets you represent relationships between rows in one table, such as managers and employees or parent and child categories.

FROM   employees AS e            -- e = employee role
INNER JOIN employees AS m      -- m = manager role (the same table)
  ON e.manager_id = m.emp_id;     -- the employee's manager_id matches the manager's emp_id

From the employees table, retrieve each employee's name (employee) and their direct manager's name (manager). Exclude employees without a manager (the president, Tanaka).

※ manager_id=NULL (Tanaka, the president) is automatically excluded by INNER JOIN.

SELECT
  e.name AS employee,             -- name from the employee-side alias e
  m.name AS manager               -- name from the manager-side alias m
FROM employees AS e
INNER JOIN employees AS m         -- join the same table as separate roles: e.manager_id = m.emp_id identifies the manager
  ON e.manager_id = m.emp_id      -- manager_id=NULL (Tanaka) does not satisfy the ON condition → excluded
ORDER BY e.manager_id, e.emp_id;

/*
  Execution order:
  1. FROM employees AS e       → read all 5 rows as the employee role
  2. INNER JOIN employees AS m → match each manager_id to an emp_id as the manager
                                 manager_id=NULL (Tanaka) does not satisfy ON and is excluded
  3. SELECT e.name, m.name     → project the employee and manager names
  4. ORDER BY                  → sort by manager_id, then emp_id
*/

A CASE expression is a conditional expression in SQL that returns different values depending on a column's value. Combined with JOIN, it lets you join data and transform or label values at the same time.

SELECT
  column_name,
  CASE column_name                        -- simple CASE: branch on a column's value
    WHEN 'pending'   THEN 'Processing'
    WHEN 'shipped'   THEN 'Shipped'
    ELSE             'Unknown'         -- fallback for an undefined status
  END AS status_label

Join the orders and users tables and retrieve the order list. Convert the English values in status into labels (status_label) with a CASE expression.

SELECT
  o.order_id,
  u.name AS user_name,
  o.amount,
  CASE o.status                    -- simple CASE: branch on o.status and convert it to an English label
    WHEN 'pending'   THEN 'Processing'
    WHEN 'shipped'   THEN 'Shipped'
    WHEN 'delivered' THEN 'Delivered'
    WHEN 'cancelled' THEN 'Cancelled'
    ELSE             'Unknown'          -- fallback for an undefined status
  END AS status_label
FROM orders AS o
INNER JOIN users AS u
  ON o.user_id = u.user_id
ORDER BY o.order_id;

/*
  Execution order:
  1. FROM orders AS o          → read 5 orders
  2. INNER JOIN users AS u     → match by user_id and add user_name
  3. CASE o.status ...         → evaluate each status and convert it to an English label
  4. SELECT / ORDER BY         → output 4 columns in order_id ascending order
*/

A derived table is a technique that treats a subquery written in the FROM or JOIN clause as a temporary table. Give the subquery an alias and you can JOIN it just like an ordinary table.

FROM users AS u
INNER JOIN (                       -- the derived table (subquery) starts here
  SELECT
    user_id,
    MAX(ordered_at) AS latest,
    SUM(amount)     AS total
  FROM orders
  GROUP BY user_id
) AS s                             -- give the derived table the alias s
  ON u.user_id = s.user_id;

Group and aggregate the orders table by user_id (latest order date and total amount), then JOIN that result to users to retrieve it together with the user name. Sort the output by latest order date in descending order.

SELECT
  u.name,
  s.latest_order_date,
  s.total_amount
FROM users AS u
INNER JOIN (                        -- subquery in the FROM clause (derived table)
  SELECT
    user_id,
    MAX(ordered_at) AS latest_order_date,  -- latest order date
    SUM(amount)     AS total_amount        -- total amount
  FROM orders
  GROUP BY user_id
) AS s                             -- give the derived table the alias s
  ON u.user_id = s.user_id
ORDER BY s.latest_order_date DESC; -- latest order date descending

/*
  Execution order:
  1. Execute the subquery       → aggregate orders by user_id
                                 derived table s (3 rows) is generated
  2. FROM users AS u           → read the 3 users
  3. INNER JOIN ... AS s       → match u.user_id with s.user_id (3 rows retained)
  4. SELECT u.name, s.*        → project the requested 3 columns
  5. ORDER BY latest_order_date DESC → sort by latest order date descending
*/