In a WHERE clause, col = NULL evaluates to UNKNOWN and rows are excluded (as learned in Q1), but GROUP BY puts rows with NULL in the same group. This is a special behavior defined by the SQL standard.

-- GROUP BY special case: NULL = NULL is UNKNOWN in equality comparison, but
-- GROUP BY aggregates NULL values into one group
SELECT device, COUNT(*)
FROM   page_views
GROUP BY device;
-- rows where device = NULL are collected into one group and counted

-- HAVING can select or exclude the NULL group
HAVING device IS NULL      -- select only the NULL group
HAVING device IS NOT NULL  -- exclude the NULL group

From the page_views table, aggregate total views for each page and device. Display rows where device is NULL as 'Unknown' (using COALESCE), and return page, device_label, total_views in ascending page order, then ascending device_label order.

Note: rows with device=NULL (view_id=2,6,8) are accesses from an unknown device. Note that GROUP BY device puts the NULL values into one group.

Expected output (page ascending, then device_label ascending):

about/pc: 100+80=180. top/mobile: 500+400=900. top/NULL: 200+50=250. GROUP BY uses the original device column for grouping; SELECT's COALESCE only converts the display label.

SELECT
  page,
  COALESCE(device, 'Unknown')  AS device_label, -- convert to a display label (GROUP BY uses the original column)
  SUM(views)                 AS total_views
FROM  page_views
GROUP BY page, device          -- aggregate NULL as one group (special behavior)
ORDER BY page, device_label;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM page_views              → read the rows
  2. GROUP BY page, device        → group the rows
  3. SELECT                       → aggregate and assign labels (SUM, COALESCE)
  4. ORDER BY page, device_label  → sort and output
*/

CASE WHEN evaluates conditions from top to bottom and finalizes the result at the first WHEN that is TRUE. Comparisons with NULL (>=, <=, =) evaluate to UNKNOWN, so that WHEN is skipped and evaluation moves to the next WHEN. As a result, NULL slips through every WHEN and falls into ELSE.

-- Evaluation of each WHEN when score is NULL
CASE
  WHEN score >= 80 THEN 'A'  -- NULL >= 80 → UNKNOWN → skipped
  WHEN score >= 60 THEN 'C'  -- NULL >= 60 → UNKNOWN → skipped
  ELSE                  'D'  -- NULL falls here (an absent student becomes 'D'!)
END

-- Correct pattern: put the IS NULL check in the first WHEN
CASE
  WHEN score IS NULL THEN 'Absent'  -- IS NULL returns a definite TRUE/FALSE
  WHEN score >= 80   THEN 'A'
END

From the exam_results table, assign a grade (A/B/C/D/Absent) according to the score.
score >= 80: 'A' / 70 ≤ score < 80: 'B' / 60 ≤ score < 70: 'C' / score < 60: 'D' / score is NULL: 'Absent'
Return student_id, name, score, grade in ascending student_id order.

Note: student_id=3,6 have score=NULL (absent students). If CASE WHEN starts with score >= 80 without an IS NULL check, NULL rows fall into ELSE and are incorrectly classified as 'D'.

Expected output (student_id ascending):

SELECT
  student_id,
  name,
  score,
  CASE
    WHEN score IS NULL THEN 'Absent'  -- check NULL first (important)
    WHEN score >= 80   THEN 'A'
    WHEN score >= 70   THEN 'B'
    WHEN score >= 60   THEN 'C'
    ELSE                    'D'   -- all rows with score < 60 (NULL already caught above)
  END AS grade
FROM  exam_results
ORDER BY student_id;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM exam_results     → read the rows
  2. SELECT                → evaluate the columns (classify grade with CASE WHEN)
  3. ORDER BY student_id   → sort and output
*/

DBMSs differ in how ORDER BY sorts NULL. In PostgreSQL, the default for DESC sorting is NULLS FIRST (NULL first), so asking for “the most recent items first” can put NULL (not yet sold) at the top.

-- PostgreSQL default sort rules
ORDER BY last_sold_at ASC   -- NULLS LAST is the default (NULL last)
ORDER BY last_sold_at DESC  -- NULLS FIRST is the default (NULL first! ← trap)

-- Explicit specification (PostgreSQL / Oracle / standard SQL)
ORDER BY last_sold_at DESC NULLS LAST   -- put NULL last
ORDER BY last_sold_at ASC  NULLS FIRST  -- put NULL first

-- MySQL / SQL Server do not support NULLS FIRST/LAST → alternative
ORDER BY COALESCE(last_sold_at, '1900-01-01'::date) DESC

From the products table, display the most recently sold products first (last_sold_at DESC), with products that have not been sold yet (NULL) last. Return product_id, name, last_sold_at.

Note: product_id=3,5,7 have last_sold_at=NULL (not yet sold). With only ORDER BY ... DESC, PostgreSQL's NULLS FIRST default puts these products at the top.

Expected output (most recently sold first, unsold products last):

Note: NULL rows (product_id=3,5,7) are fixed at the end by NULLS LAST. The relative order of NULLs is undefined, so product_id order is not guaranteed.

SELECT
  product_id,
  name,
  last_sold_at
FROM  products
ORDER BY last_sold_at DESC NULLS LAST;  -- explicitly put NULL last

/*
  Execution order (SQL's logical evaluation order):
  1. FROM products                          → read the rows
  2. SELECT                                 → retrieve the columns
  3. ORDER BY last_sold_at DESC NULLS LAST  → sort and output
*/

FULL OUTER JOIN combines LEFT JOIN and RIGHT JOIN and keeps every row from both tables. When one table has no matching row, the columns from the other side become NULL.

SELECT
  COALESCE(a.id, b.id)  AS id,  -- reliably get whichever value exists
  a.value               AS val_a,
  b.value               AS val_b
FROM  table_a a
FULL OUTER JOIN table_b b ON a.id = b.id

-- a.value IS NULL → a row that exists only in table_b (missing from a)
-- b.value IS NULL → a row that exists only in table_a (missing from b)

FULL OUTER JOIN expected_revenue (budget) and actual_revenue (actuals), and output the monthly budget, actuals, and status ('Normal' / 'Actual missing' / 'Budget missing'). Return month, expected, actual, status in ascending month order.

Note: 2024-05 exists only in expected_revenue (actual missing). 2024-06 exists only in actual_revenue (budget missing). FULL OUTER JOIN detects both kinds of missing data at once.

Expected output (month ascending):

SELECT
  COALESCE(e.month, a.month)  AS month,  -- reliably get a month that exists on either side
  e.expected,
  a.actual,
  CASE
    WHEN e.month IS NULL THEN 'Budget missing'  -- actual only → e.month is NULL
    WHEN a.month IS NULL THEN 'Actual missing'  -- expected only → a.month is NULL
    ELSE                      'Normal'
  END AS status
FROM       expected_revenue e
FULL OUTER JOIN actual_revenue a
  ON e.month = a.month
ORDER BY month;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM expected_revenue e            → read the left table
  2. FULL OUTER JOIN actual_revenue a   → join while keeping all rows
  3. SELECT                             → evaluate the columns (month, status)
  4. ORDER BY month                     → sort and output
*/