Day 3: Subqueries & derived tables

A. Lesson overview

Activity breakdown

Business scenario. The customer team is preparing a re-engagement campaign. They want to know which customers have never ordered, which have gone quiet, which bought premium products, and which placed unusually large orders compared with their own history. Each question compares a row with something computed from other rows — the natural job of subqueries.

B. Theory explained clearly

Part 1 — Self-contained subqueries

B1. What a subquery is

A subquery is a SELECT statement nested inside another statement, enclosed in parentheses. The outer statement uses its result as a value, a list of values or a table.

A subquery is self-contained when it does not refer to the outer query: you could highlight it and run it on its own. It is correlated when it refers to a column of the outer query (Part 3).

B2. Scalar subqueries

USE RetailLab;
GO

-- Which products cost more than the average list price?
SELECT  ProductCode, ProductName, ListPrice
FROM    Inventory.Product
WHERE   ListPrice > (SELECT AVG(ListPrice) FROM Inventory.Product)
ORDER BY ListPrice DESC, ProductCode;

The inner query runs once and returns 146.749000; the outer query compares every product with it. You could not write WHERE ListPrice > AVG(ListPrice) — an aggregate cannot appear in WHERE (Day 2).

If a scalar subquery returns more than one row, SQL Server raises error 512: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression. If it returns no rows, the value is NULL — and a comparison with NULL is UNKNOWN, so the outer row is filtered out silently.

A scalar subquery can also appear in the SELECT list:

SELECT  ProductCode,
        ListPrice,
        ListPrice - (SELECT AVG(ListPrice) FROM Inventory.Product) AS DiffFromAverage
FROM    Inventory.Product
ORDER BY ProductCode;

B3. ANY, SOME and ALL (for recognition)

> ALL (subquery) is TRUE when the value is greater than every value returned; > ANY (subquery) (or SOME) when it is greater than at least one. They are rarely needed — > (SELECT MAX(…)) and > (SELECT MIN(…)) say the same thing more clearly — but you will meet them in existing code. Watch out: > ALL over an empty subquery is TRUE.

Part 2 — IN, NOT IN and the NULL trap

B4. IN with a subquery

-- Customers who have bought the Handheld GPS Unit (product 22)
SELECT  CustomerID, FirstName, LastName
FROM    Sales.Customer
WHERE   CustomerID IN (SELECT o.CustomerID
                       FROM   Sales.SalesOrder     AS o
                       JOIN   Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
                       WHERE  l.ProductID = 22)
ORDER BY CustomerID;

The subquery returns a list of customer IDs (with duplicates — that does not matter to IN). Each customer appears once in the result, even if they bought the GPS several times. Compare that with joining Sales.Customer directly to the order lines, which would repeat a customer once per matching line: IN filters, a join multiplies.

B5. NOT IN and NULLs

x NOT IN (a, b, c) means x <> a AND x <> b AND x <> c. If any list value is NULL, one of those comparisons is UNKNOWN, so the whole condition can never be TRUE. With a literal list you can see the NULL; with a subquery you usually cannot.

A real example: “Which employees manage nobody?”

-- Returns 0 rows!
SELECT  EmployeeID, FirstName, LastName
FROM    HR.Employee
WHERE   EmployeeID NOT IN (SELECT ManagerID FROM HR.Employee);

Eleanor Hughes, the Managing Director, has ManagerID = NULL, so the subquery’s list contains a NULL and every row is filtered out. Two fixes:

-- Fix 1: exclude NULLs from the list
WHERE EmployeeID NOT IN (SELECT ManagerID FROM HR.Employee WHERE ManagerID IS NOT NULL)

-- Fix 2 (preferred): NOT EXISTS, which is not affected by NULLs (Part 3)
WHERE NOT EXISTS (SELECT 1 FROM HR.Employee AS r WHERE r.ManagerID = e.EmployeeID)

Rule of thumb: use NOT EXISTS for “has no matching rows”. Use NOT IN only with literal lists or with a subquery column that cannot be NULL.

Part 3 — Correlated subqueries and EXISTS

B6. Correlated subqueries

A correlated subquery refers to a column of the outer query, so its result can be different for every outer row.

-- Products priced above the average of their OWN category
SELECT  p.ProductCode, p.ProductName, p.CategoryID, p.ListPrice
FROM    Inventory.Product AS p
WHERE   p.ListPrice > (SELECT AVG(x.ListPrice)
                       FROM   Inventory.Product AS x
                       WHERE  x.CategoryID = p.CategoryID)   -- p comes from the outer query
ORDER BY p.CategoryID, p.ProductCode;

How to read it. For each product p, the inner query computes the average price of products in p’s category, then the outer WHERE compares. Logically the subquery runs once per outer row. SQL Server’s optimiser is free to execute it more cleverly (often as a join with a pre-computed aggregate), so a correlated subquery is not automatically slow — but it helps to understand the logical model.

Aliases matter. The same table appears twice, so give each occurrence its own alias (p outer, x inner) and qualify every column. If you write WHERE CategoryID = CategoryID, SQL Server resolves both names to the inner table and the condition is always true — no error, wrong answer.

B7. EXISTS and NOT EXISTS

EXISTS (subquery) is TRUE if the subquery returns at least one row, FALSE otherwise. It is never UNKNOWN, so NULLs cannot trip it up.

-- Customers who ordered in December 2025
SELECT  c.CustomerID, c.FirstName, c.LastName
FROM    Sales.Customer AS c
WHERE   EXISTS (SELECT 1
                FROM   Sales.SalesOrder AS o
                WHERE  o.CustomerID = c.CustomerID
                  AND  o.OrderDate >= '2025-12-01')
ORDER BY c.CustomerID;

• The select list inside EXISTS is ignored; SELECT 1 is a common convention that signals “I only care whether a row exists”.
• EXISTS can stop as soon as it finds one matching row.
• NOT EXISTS answers “has no matching rows” correctly even when columns contain NULLs.

B8. Correlated scalar subqueries in the SELECT list

SELECT  c.CustomerID,
        c.FirstName,
        c.LastName,
        (SELECT MAX(o.OrderDate)
         FROM   Sales.SalesOrder AS o
         WHERE  o.CustomerID = c.CustomerID) AS LastOrderDate
FROM    Sales.Customer AS c
ORDER BY c.CustomerID;

Customers with no orders get NULL, because MAX over no rows is NULL. This style is easy to read for one or two values. When you need several values from the same related rows (count, last date and total), a derived table that computes them all at once is usually clearer — Part 4.

Part 4 — Derived tables

B9. What a derived table is

A derived table is a subquery in the FROM clause. Its result is treated as a table for the outer query.

SELECT  t.SalesChannel,
        COUNT(*)                              AS Orders,
        CAST(AVG(t.OrderNet) AS decimal(10,2)) AS AvgOrderValue
FROM   (SELECT  o.OrderID,
                o.SalesChannel,
                SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS OrderNet
        FROM    Sales.SalesOrder     AS o
        JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
        WHERE   o.OrderStatus <> 'Cancelled'
        GROUP BY o.OrderID, o.SalesChannel) AS t          -- one row per ORDER
GROUP BY t.SalesChannel
ORDER BY t.SalesChannel;

This is the clean way to aggregate an aggregate. Day 2 computed average order value as SUM ÷ COUNT(DISTINCT OrderID). A derived table makes the grain explicit: the inner query returns one row per order, so the outer AVG averages orders — and you can also take MIN and MAX of order values, which the Day 2 formula cannot.

Rules for derived tables

A derived table also lets you name a calculation once and filter on it — the limitation from Day 1, where a SELECT alias could not be used in WHERE:

SELECT  t.ProductCode, t.UnitMargin
FROM   (SELECT ProductCode, ListPrice - StandardCost AS UnitMargin
        FROM   Inventory.Product) AS t
WHERE   t.UnitMargin > 100          -- the alias is now a real column of t
ORDER BY t.UnitMargin DESC, t.ProductCode;

B10. Table value constructors

A VALUES list in parentheses with an alias and column names is a derived table built from literals. You met it on Day 2:

SELECT  v.Segment, v.DiscountPct
FROM   (VALUES ('Consumer', 0.00), ('Business', 0.05), ('VIP', 0.10)) AS v (Segment, DiscountPct)
ORDER BY v.DiscountPct;

It is handy for small lookup lists inside a query.

Part 5 — Primer: LEFT JOIN

B11. Keeping rows that have no match

INNER JOIN keeps only rows that match. A LEFT JOIN (in full, LEFT OUTER JOIN) keeps every row from the left table; where there is no matching right-hand row, the right-hand columns are NULL.

SELECT  c.CustomerID, c.FirstName, c.LastName, o.OrderID, o.OrderDate
FROM    Sales.Customer   AS c
LEFT JOIN Sales.SalesOrder AS o ON o.CustomerID = c.CustomerID
ORDER BY c.CustomerID, o.OrderID;

Result: 381 rows — the 376 orders paired with their customers, plus one row each for the 5 customers who have never ordered, with NULL in OrderID and OrderDate.

B12. The anti-join pattern

Adding WHERE o.OrderID IS NULL keeps only the unmatched rows:

SELECT  c.CustomerID, c.FirstName, c.LastName
FROM    Sales.Customer   AS c
LEFT JOIN Sales.SalesOrder AS o ON o.CustomerID = c.CustomerID
WHERE   o.OrderID IS NULL                 -- test a column that is never NULL in a real match
ORDER BY c.CustomerID;

Two cautions, both covered fully on Day 8:

• Test a right-hand column that cannot be NULL in a matched row — normally its primary key. Testing a nullable column such as o.StoreID would also return customers whose only orders were online.
• Conditions on the right-hand table belong in ON, not WHERE, if you want to keep unmatched left rows. WHERE o.OrderDate >= '2025-12-01' after a LEFT JOIN removes every customer with no December order — including the NULL rows you wanted to keep — so the join silently behaves like an inner join.

For “has no orders”, NOT EXISTS and the LEFT JOIN … IS NULL pattern return the same rows. Many developers find NOT EXISTS states the intent more directly; SQL Server often produces the same execution plan for both.

C. Dataset and starting state

Preparation. None if RetailLab is unchanged since setup. Otherwise run scripts/setup/04_reset_training_data.sql in SQLCMD Mode (destructive: drops and rebuilds RetailLab) or scripts 00 → 03.

Validation. Expected: 40, 35, 5, 1.

USE RetailLab;
GO

SELECT  (SELECT COUNT(*) FROM Sales.Customer)                     AS Customers,
        (SELECT COUNT(DISTINCT CustomerID) FROM Sales.SalesOrder) AS CustomersWhoOrdered,
        (SELECT COUNT(*) FROM Sales.Customer AS c
         WHERE NOT EXISTS (SELECT 1 FROM Sales.SalesOrder AS o
                           WHERE o.CustomerID = c.CustomerID))     AS CustomersNeverOrdered,
        (SELECT COUNT(*) FROM HR.Employee WHERE ManagerID IS NULL) AS EmployeesWithNoManager;

D. Worked examples

Worked example 1 — Above-average products (scalar subquery)

SELECT  ProductCode, ProductName, ListPrice
FROM    Inventory.Product
WHERE   ListPrice > (SELECT AVG(ListPrice) FROM Inventory.Product)
ORDER BY ListPrice DESC, ProductCode;

Expected result: 12 rows, from TNT-004 (599.00) to JKT-004 (159.00). The average is 146.749; the next product down, FTW-001 at 139.00, is below it.

Worked example 2 — The NOT IN trap, live

-- A: returns 0 rows because the subquery's list contains a NULL
SELECT  EmployeeID, FirstName, LastName
FROM    HR.Employee
WHERE   EmployeeID NOT IN (SELECT ManagerID FROM HR.Employee);

-- B: correct
SELECT  e.EmployeeID, e.FirstName, e.LastName, e.JobTitle
FROM    HR.Employee AS e
WHERE   NOT EXISTS (SELECT 1
                    FROM   HR.Employee AS r          -- r = a possible direct report
                    WHERE  r.ManagerID = e.EmployeeID)
ORDER BY e.EmployeeID;

Expected result: A returns 0 rows. B returns 10 rows: employees 5 (Aisha Khan, IT Manager — a manager by title, but with no reports in the data), 7, 8, 9, 10, 12, 13, 14, 15 and 16.

Business meaning: the job title and the reporting line disagree for Aisha Khan. Data like this is worth raising with HR rather than “fixing” in a query.

Worked example 3 — Products above their category average (correlated subquery)

SELECT  p.ProductCode,
        p.ProductName,
        p.CategoryID,
        p.ListPrice,
        (SELECT CAST(AVG(x.ListPrice) AS decimal(10,2))
         FROM   Inventory.Product AS x
         WHERE  x.CategoryID = p.CategoryID)          AS CategoryAvgPrice
FROM    Inventory.Product AS p
WHERE   p.ListPrice > (SELECT AVG(x.ListPrice)
                       FROM   Inventory.Product AS x
                       WHERE  x.CategoryID = p.CategoryID)
ORDER BY p.CategoryID, p.ProductCode;

Expected result: 15 rows. First three: TNT-002 (349.00 vs 346.50), TNT-004 (599.00 vs 346.50), SLP-002 (299.00 vs 162.33). Category 14 contributes no rows: both waterproof jackets cost 189.00, so neither is above the average.

What to notice: the same correlated subquery appears twice. That repetition is a sign a derived table or (tomorrow) a CTE would be cleaner. You will rewrite this query on Day 4.

Worked example 4 — Recent buyers (EXISTS)

SELECT  c.CustomerID, c.FirstName, c.LastName
FROM    Sales.Customer AS c
WHERE   EXISTS (SELECT 1
                FROM   Sales.SalesOrder AS o
                WHERE  o.CustomerID = c.CustomerID
                  AND  o.OrderDate >= '2025-12-01'
                  AND  o.OrderDate <  '2026-01-01')
ORDER BY c.CustomerID;

Expected result: 20 customers, starting with 1 (Alice Morgan), 2, 4, 6, 8 and ending with 33, 34, 35. Each customer appears once, however many December orders they placed.

Worked example 5 — Who bought the GPS? (IN)

SELECT  CustomerID, FirstName, LastName
FROM    Sales.Customer
WHERE   CustomerID IN (SELECT o.CustomerID
                       FROM   Sales.SalesOrder     AS o
                       JOIN   Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
                       WHERE  l.ProductID = 22)
ORDER BY CustomerID;

Expected result: 15 customers: 1, 4, 6, 9, 12, 13, 17, 18, 20, 22, 23, 24, 26, 27 and 29. Rewriting with EXISTS and a correlated subquery gives the same 15 rows.

Worked example 6 — Last order date per customer (correlated scalar subquery)

SELECT  c.CustomerID,
        c.FirstName,
        c.LastName,
        (SELECT MAX(o.OrderDate)
         FROM   Sales.SalesOrder AS o
         WHERE  o.CustomerID = c.CustomerID) AS LastOrderDate
FROM    Sales.Customer AS c
WHERE   c.CustomerID IN (1, 5, 14, 36, 37)
ORDER BY c.CustomerID;

Expected result

Worked example 7 — Order value statistics by channel (derived table)

SELECT  t.SalesChannel,
        COUNT(*)                                AS Orders,
        CAST(AVG(t.OrderNet) AS decimal(10,2))  AS AvgOrderValue,
        CAST(MIN(t.OrderNet) AS decimal(10,2))  AS SmallestOrder,
        CAST(MAX(t.OrderNet) AS decimal(10,2))  AS LargestOrder
FROM   (SELECT  o.OrderID,
                o.SalesChannel,
                SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS OrderNet
        FROM    Sales.SalesOrder     AS o
        JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
        WHERE   o.OrderStatus <> 'Cancelled'
        GROUP BY o.OrderID, o.SalesChannel) AS t
GROUP BY t.SalesChannel
ORDER BY t.SalesChannel;

Expected result

Why the inner GROUP BY includes SalesChannel: the outer query needs it, and the golden rule from Day 2 says a non-aggregated column must be grouped. Because each order has exactly one channel, adding it does not change the grain.

Worked example 8 — Customers who have never ordered (LEFT JOIN vs NOT EXISTS)

-- Anti-join with LEFT JOIN
SELECT  c.CustomerID, c.FirstName, c.LastName, c.DateJoined
FROM    Sales.Customer   AS c
LEFT JOIN Sales.SalesOrder AS o ON o.CustomerID = c.CustomerID
WHERE   o.OrderID IS NULL
ORDER BY c.CustomerID;

-- The same question with NOT EXISTS
SELECT  c.CustomerID, c.FirstName, c.LastName, c.DateJoined
FROM    Sales.Customer AS c
WHERE   NOT EXISTS (SELECT 1 FROM Sales.SalesOrder AS o WHERE o.CustomerID = c.CustomerID)
ORDER BY c.CustomerID;

Expected result (both): 5 rows — 36 Mia Harper (joined 2024-03-12, now inactive), 37 Leo Fitzgerald, 38 Freya Lindqvist, 39 Arjun Mehta and 40 Hugo Bernard (all joined November–December 2025).

Business meaning: four of the five joined in the last two months — they are new, not lost. A useful campaign needs that context, which the guided lab adds.

E. Guided practical lab — Customer health check

The customer team wants every active customer classified as Active, Lapsed (no order since 1 September 2025) or Never ordered, with their order count, last order date and lifetime net sales. Cancelled orders do not count.

Step 1 — Summarise orders per customer in a derived table

1. Do: Write the per-customer summary on its own first.
2. Run:

   USE RetailLab;
   GO
   
   SELECT  o.CustomerID,
           COUNT(DISTINCT o.OrderID)                            AS Orders,
           MAX(o.OrderDate)                                     AS LastOrderDate,
           SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct))  AS LifetimeNet
   FROM    Sales.SalesOrder     AS o
   JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
   WHERE   o.OrderStatus <> 'Cancelled'
   GROUP BY o.CustomerID
   ORDER BY o.CustomerID;

3. Observe: 35 rows — one per customer who has ordered.
4. Why it matters: Testing the inner query on its own is the quickest way to debug a derived table.
5. Confirm: 35 rows; customer 1 shows 27 orders, last order 2025-12-29.

Step 2 — Bring in every customer with a LEFT JOIN

1. Do: Remove the ORDER BY (not allowed inside a derived table), wrap the query in parentheses, alias it s, and LEFT JOIN it to Sales.Customer.
2. Run:

   SELECT  c.CustomerID,
           CONCAT_WS(N' ', c.FirstName, c.LastName) AS CustomerName,
           s.Orders,
           s.LastOrderDate,
           CAST(s.LifetimeNet AS decimal(12,2))     AS LifetimeNet
   FROM    Sales.Customer AS c
   LEFT JOIN (SELECT  o.CustomerID,
                      COUNT(DISTINCT o.OrderID)                            AS Orders,
                      MAX(o.OrderDate)                                     AS LastOrderDate,
                      SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct))  AS LifetimeNet
              FROM    Sales.SalesOrder     AS o
              JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
              WHERE   o.OrderStatus <> 'Cancelled'
              GROUP BY o.CustomerID) AS s
          ON s.CustomerID = c.CustomerID
   WHERE   c.IsActive = 1
   ORDER BY c.CustomerID;

3. Observe: 37 rows. Customers 37–40 show NULL in every summary column.
4. Why it matters: The LEFT JOIN keeps customers with no orders. The WHERE c.IsActive = 1 filter is on the left table, so it is safe in WHERE.
5. Confirm: 37 rows (40 customers minus 3 inactive).

Step 3 — Classify each customer

1. Do: Add a CASE column. Test “never ordered” first, because for those customers LastOrderDate is NULL.
2. Run: add this column to the SELECT list of step 2:

           , CASE
                 WHEN s.CustomerID IS NULL            THEN 'Never ordered'
                 WHEN s.LastOrderDate < '2025-09-01'  THEN 'Lapsed'
                 ELSE                                      'Active'
             END AS HealthStatus

3. Observe: customers 21, 22 and 31 are Lapsed; 37–40 Never ordered; the other 30 Active.
4. Why it matters: If you tested s.LastOrderDate < '2025-09-01' without the first branch, a NULL date would be UNKNOWN and fall to ELSE — never-ordered customers would be labelled Active.
5. Confirm: exactly 3 Lapsed and 4 Never ordered.

Step 4 — Show only the customers to re-engage

1. Do: You cannot filter on the alias HealthStatus in WHERE. Wrap the whole step-3 query in a second derived table and filter that.

2. Run:

   SELECT  h.*
   FROM   (SELECT  c.CustomerID,
                   CONCAT_WS(N' ', c.FirstName, c.LastName) AS CustomerName,
                   s.Orders,
                   s.LastOrderDate,
                   CAST(s.LifetimeNet AS decimal(12,2))     AS LifetimeNet,
                   CASE
                       WHEN s.CustomerID IS NULL            THEN 'Never ordered'
                       WHEN s.LastOrderDate < '2025-09-01'  THEN 'Lapsed'
                       ELSE                                      'Active'
                   END AS HealthStatus
           FROM    Sales.Customer AS c
           LEFT JOIN (SELECT  o.CustomerID,
                              COUNT(DISTINCT o.OrderID)                            AS Orders,
                              MAX(o.OrderDate)                                     AS LastOrderDate,
                              SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct))  AS LifetimeNet
                      FROM    Sales.SalesOrder     AS o
                      JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
                      WHERE   o.OrderStatus <> 'Cancelled'
                      GROUP BY o.CustomerID) AS s
                  ON s.CustomerID = c.CustomerID
           WHERE   c.IsActive = 1) AS h
   WHERE   h.HealthStatus <> 'Active'
   ORDER BY h.HealthStatus, h.CustomerID;

3. Observe: 7 rows.

   CustomerID	CustomerName	Orders	LastOrderDate	LifetimeNet	HealthStatus
   21	Victor Dubois	6	2025-07-25	1806.98	Lapsed
   22	Willow Green	3	2025-07-22	958.00	Lapsed
   31	Noah Baker	5	2025-02-16	1608.70	Lapsed
   37	Leo Fitzgerald	NULL	NULL	NULL	Never ordered
   38	Freya Lindqvist	NULL	NULL	NULL	Never ordered
   39	Arjun Mehta	NULL	NULL	NULL	Never ordered
   40	Hugo Bernard	NULL	NULL	NULL	Never ordered

4. Why it matters: Nesting derived tables lets you build a query in layers, each of which you can test. It also becomes hard to read — tomorrow’s CTEs express the same layers top to bottom.

5. Confirm: 7 rows in the order shown.

Step 5 — Cross-check the “never ordered” group with NOT EXISTS

1. Do: Independently count active customers with no non-cancelled orders.
2. Run:

   SELECT  COUNT(*) AS ActiveNeverOrdered
   FROM    Sales.Customer AS c
   WHERE   c.IsActive = 1
     AND   NOT EXISTS (SELECT 1
                       FROM   Sales.SalesOrder AS o
                       WHERE  o.CustomerID = c.CustomerID
                         AND  o.OrderStatus <> 'Cancelled');

3. Observe: 4.
4. Why it matters: Checking a result with a second, independent method is one of the most effective habits in data work.
5. Confirm: 4 matches the Never ordered count in step 4.

F. Independent practice

Use ORDER BY with a unique tie-breaker in every query.

Task 1 — Products never sold (straightforward)

Return ProductID, ProductCode, ProductName and LaunchDate for products that appear on no order line. Write it once with NOT EXISTS and once with NOT IN.

Success criteria: 1 row: product 29, NAV-004 Satellite Messenger, launched 2025-12-01. Both versions agree.

Task 2 — Paid above the department average (correlated subquery)

Return EmployeeID, FirstName, LastName, DepartmentID, Salary and DeptAvgSalary (2 decimal places) for employees whose salary is above their own department’s average. Include leavers. Sort by DepartmentID, then salary (highest first), then EmployeeID.

Success criteria: 4 rows — Raj Patel and Liam O’Connor (Sales, average 41,250.00), Fiona Campbell (Warehouse & Logistics, 42,250.00) and Tom Baxter (Finance, 50,500.00).

Task 3 — Unusually large 2025 orders (derived table + correlation)

Customer Services wants 2025 orders whose net value is more than double that customer’s own average order value. Use all of the customer’s non-cancelled orders (2024 and 2025) to compute the average, and exclude cancelled orders from the result.

Return OrderID, CustomerID, OrderDate, OrderNet and CustomerAvgOrder (both 2 decimal places), sorted by OrderDate, then OrderID.

Success criteria: 19 rows, starting with order 1177 (customer 13, 648.85 vs 319.10) and ending with order 1376 (customer 17, 707.00 vs 264.71). Customer 13 appears three times.

Task 4 — Each customer’s largest order (challenging)

For every customer who has ordered, return the single largest non-cancelled order: CustomerID, OrderID, OrderDate and OrderNet (2 decimal places). Sort by OrderNet highest first, then CustomerID.

Success criteria: 35 rows (no customer has two orders tied for largest). The first is customer 26, order 1014 (2024-02-07, 1577.70); the last is customer 7, order 1142 (248.00).

Stretch challenge — Bought the whole tent range (relational division)

Which customers have bought every currently available tent — every product in category 2 (Tents) with no DiscontinuedDate — in non-cancelled orders? Return CustomerID, FirstName and LastName.

Success criteria: 5 customers: 1, 13, 18, 27 and 29. Adding a new tent to the catalogue would make the answer 0 rows without changing your query.

G. Solutions and explanations

-- NOT EXISTS
SELECT  p.ProductID, p.ProductCode, p.ProductName, p.LaunchDate
FROM    Inventory.Product AS p
WHERE   NOT EXISTS (SELECT 1 FROM Sales.SalesOrderLine AS l WHERE l.ProductID = p.ProductID)
ORDER BY p.ProductID;

-- NOT IN — safe ONLY because SalesOrderLine.ProductID is declared NOT NULL
SELECT  ProductID, ProductCode, ProductName, LaunchDate
FROM    Inventory.Product
WHERE   ProductID NOT IN (SELECT ProductID FROM Sales.SalesOrderLine)
ORDER BY ProductID;

SELECT  e.EmployeeID,
        e.FirstName,
        e.LastName,
        e.DepartmentID,
        e.Salary,
        (SELECT CAST(AVG(x.Salary) AS decimal(10,2))
         FROM   HR.Employee AS x
         WHERE  x.DepartmentID = e.DepartmentID) AS DeptAvgSalary
FROM    HR.Employee AS e
WHERE   e.Salary > (SELECT AVG(x.Salary)
                    FROM   HR.Employee AS x
                    WHERE  x.DepartmentID = e.DepartmentID)
ORDER BY e.DepartmentID, e.Salary DESC, e.EmployeeID;

SELECT  t.OrderID,
        t.CustomerID,
        t.OrderDate,
        CAST(t.OrderNet AS decimal(10,2))   AS OrderNet,
        CAST(a.AvgOrder AS decimal(10,2))   AS CustomerAvgOrder
FROM   (SELECT  o.OrderID, o.CustomerID, o.OrderDate,
                SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS OrderNet
        FROM    Sales.SalesOrder     AS o
        JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
        WHERE   o.OrderStatus <> 'Cancelled'
        GROUP BY o.OrderID, o.CustomerID, o.OrderDate) AS t
JOIN   (SELECT  x.CustomerID, AVG(x.OrderNet) AS AvgOrder
        FROM   (SELECT  o.OrderID, o.CustomerID,
                        SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS OrderNet
                FROM    Sales.SalesOrder     AS o
                JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
                WHERE   o.OrderStatus <> 'Cancelled'
                GROUP BY o.OrderID, o.CustomerID) AS x
        GROUP BY x.CustomerID) AS a
       ON a.CustomerID = t.CustomerID
WHERE   t.OrderDate >= '2025-01-01'
  AND   t.OrderDate <  '2026-01-01'
  AND   t.OrderNet > 2 * a.AvgOrder
ORDER BY t.OrderDate, t.OrderID;

SELECT  t.CustomerID,
        t.OrderID,
        t.OrderDate,
        CAST(t.OrderNet AS decimal(10,2)) AS OrderNet
FROM   (SELECT  o.OrderID, o.CustomerID, o.OrderDate,
                SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS OrderNet
        FROM    Sales.SalesOrder     AS o
        JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
        WHERE   o.OrderStatus <> 'Cancelled'
        GROUP BY o.OrderID, o.CustomerID, o.OrderDate) AS t
WHERE   t.OrderNet = (SELECT MAX(y.OrderNet)
                      FROM  (SELECT  o2.CustomerID,
                                     SUM(l2.Quantity * l2.UnitPrice * (1 - l2.DiscountPct)) AS OrderNet
                             FROM    Sales.SalesOrder     AS o2
                             JOIN    Sales.SalesOrderLine AS l2 ON l2.OrderID = o2.OrderID
                             WHERE   o2.OrderStatus <> 'Cancelled'
                             GROUP BY o2.OrderID, o2.CustomerID) AS y
                      WHERE  y.CustomerID = t.CustomerID)
-- If two orders tied for a customer's maximum, BOTH would be returned.
ORDER BY t.OrderNet DESC, t.CustomerID;

SELECT  c.CustomerID, c.FirstName, c.LastName
FROM    Sales.Customer AS c
WHERE   NOT EXISTS (                                   -- there is no available tent…
            SELECT 1
            FROM   Inventory.Product AS p
            WHERE  p.CategoryID = 2
              AND  p.DiscontinuedDate IS NULL
              AND  NOT EXISTS (                        -- …that this customer has not bought
                       SELECT 1
                       FROM   Sales.SalesOrder     AS o
                       JOIN   Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
                       WHERE  o.CustomerID  = c.CustomerID
                         AND  l.ProductID   = p.ProductID
                         AND  o.OrderStatus <> 'Cancelled'))
ORDER BY c.CustomerID;

SELECT  o.CustomerID
FROM    Sales.SalesOrder     AS o
JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
JOIN    Inventory.Product    AS p ON p.ProductID = l.ProductID
WHERE   p.CategoryID = 2 AND p.DiscontinuedDate IS NULL AND o.OrderStatus <> 'Cancelled'
GROUP BY o.CustomerID
HAVING  COUNT(DISTINCT l.ProductID) = (SELECT COUNT(*) FROM Inventory.Product
                                       WHERE CategoryID = 2 AND DiscontinuedDate IS NULL)
ORDER BY o.CustomerID;

H. Knowledge check

1. Understanding. Why does WHERE EmployeeID NOT IN (SELECT ManagerID FROM HR.Employee) return no rows?

2. Debugging. This query fails with error 512. Why, and how would you fix it to return each customer’s most recent order date?

   SELECT c.CustomerID,
          (SELECT o.OrderDate FROM Sales.SalesOrder AS o WHERE o.CustomerID = c.CustomerID) AS LastOrder
   FROM   Sales.Customer AS c;

3. Query interpretation. What does this return, and why is the select list inside EXISTS irrelevant?

   SELECT p.ProductCode
   FROM   Inventory.Product AS p
   WHERE  EXISTS (SELECT NULL FROM Inventory.Stock AS s
                  WHERE s.ProductID = p.ProductID AND s.QuantityOnHand = 0);

4. Debugging. This fails with No column name was specified for column 2 of ‘t’. Fix it.

   SELECT t.CustomerID FROM (SELECT CustomerID, COUNT(*) FROM Sales.SalesOrder GROUP BY CustomerID) AS t;

5. Understanding. A colleague writes LEFT JOIN Sales.SalesOrder AS o ON o.CustomerID = c.CustomerID WHERE o.OrderDate >= '2025-12-01' to list all customers and their December orders. Why do customers without December orders disappear?

I. Troubleshooting and cleanup

Cleanup. Day 3 is read-only; nothing to clean up. Keep RetailLab unchanged for Day 4. Save your Task 3 query — you will rewrite it with CTEs tomorrow.

J. Completion checklist, recap and references

Completion checklist

• Worked example 2 returned 0 rows with NOT IN and 10 with NOT EXISTS, and you can explain why.
• Worked example 7 shows average order values of 327.38 (Online) and 337.20 (Store).
• Your guided-lab query returns the 7 customers shown in step 4.
• Tasks 1–4 match the success criteria (1, 4, 19 and 35 rows).
• You can rewrite an IN subquery as EXISTS and a NOT EXISTS as a LEFT JOIN … IS NULL.

Recap

• A scalar subquery must return at most one value; zero rows give NULL; several rows raise error 512.
• IN filters without duplicating rows. NOT IN fails silently when the list contains NULL — prefer NOT EXISTS.
• Correlated subqueries refer to the outer row; alias every table copy and qualify every column.
• EXISTS/NOT EXISTS test for the presence of rows and are immune to NULL problems.
• Derived tables let you aggregate in stages and filter on calculated columns; they need an alias and named columns, and they cannot contain ORDER BY.
• LEFT JOIN keeps unmatched left rows with NULLs on the right. Anti-join: WHERE right.PrimaryKey IS NULL. Right-table filters belong in ON.

Next: Day 4 — Common table expressions: the same layered logic, written top to bottom, plus recursive queries over the employee and category hierarchies.

References (Microsoft Learn)

• Subqueries (SQL Server)
• EXISTS (Transact-SQL)
• IN (Transact-SQL)
• ALL (Transact-SQL) and SOME | ANY (Transact-SQL)
• FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL) — derived tables and outer joins
• Table value constructor (Transact-SQL)
• NULL and UNKNOWN (Transact-SQL)