Day 1: Advanced SELECT & filtering

A. Lesson overview

Activity breakdown

Business scenario. It is the first working day of 2026 at Ridgeway Outdoor. Three colleagues need lists before lunch:

• Merchandising wants a product price list grouped into Budget, Mid-range and Premium bands.
• Marketing is planning a January campaign for active UK customers and needs to know how each one can be contacted — some have no email address, some have no phone number.
• Customer Services wants to see recent orders that still need attention.

None of these requests requires anything more than one table at a time, but each requires precise filtering and careful handling of missing values. That is today’s work.

B. Theory explained clearly

Part 1 — Relational basics and your first queries

B1. Tables, rows, columns and keys

A database is an organised collection of data managed by a database engine such as SQL Server. Inside RetailLab, data is stored in tables.

So “which customer placed order 1001?” is answered by reading CustomerID on order 1001 and finding the customer row with that same CustomerID. Combining tables like this is called a join. You will meet joins on Day 2 and study them in depth on Day 8; today every query uses one table.

The full table list and every column are in the course guide’s data dictionary. Keep it open in another tab.

Rows have no built-in order. A table is a set of rows. When you query it, SQL Server may return the rows in any order it finds efficient, and that order can change between runs. If order matters, you must ask for it with ORDER BY.

B2. Your first query: SELECT … FROM

USE RetailLab;   -- make RetailLab the current database for this query window
GO

SELECT  CustomerID, FirstName, LastName, City
FROM    Sales.Customer;

• USE RetailLab; switches the query window to the training database. The toolbar’s database drop-down then shows RetailLab. If you forget it and the drop-down says master, you will see Invalid object name ‘Sales.Customer’.
• GO is not T-SQL. It is an instruction to SSMS meaning “send everything above me to the server as one batch”. You will learn more about batches on Day 4.
• SELECT lists the columns you want, separated by commas.
• FROM names the table, written as schema.table. Always include the schema: it removes ambiguity and avoids extra name-resolution work.
• The semicolon ends the statement. T-SQL often tolerates missing semicolons, but some statements require one before them, so the course always uses them.

SELECT * returns every column. It is useful for a first look at a small table, but avoid it in real queries: it returns data you do not need, and the result changes shape if someone adds a column.

Running only part of a script. In SSMS, highlight the lines you want and press F5. Only the highlighted text runs. This is how you will run the individual examples in this lesson from one query window.

Comments. -- starts a comment that runs to the end of the line. /* … */ encloses a comment over several lines. SQL Server ignores comments.

B3. Data types you will meet today

Every column has a data type that fixes what it can hold.

Points to remember:

• Text literals use single quotes. Double quotes are not string delimiters in SQL Server.
• Prefix Unicode literals with N: N'Zoë'. Without it, characters outside the database’s code page can be lost.
• To include an apostrophe inside a string, double it: N'O''Connor'.
• Write dates as 'YYYY-MM-DD'. This format is interpreted the same way regardless of the language settings of your login.
• To convert explicitly from one type to another, use CAST(expression AS type), for example CAST(ManagerID AS varchar(10)).

B4. Expressions and column aliases

A SELECT list can contain expressions — calculations built from columns, literals, operators and functions.

SELECT  ProductCode,
        ListPrice,
        StandardCost,
        ListPrice - StandardCost AS UnitMargin   -- an expression with an alias
FROM    Inventory.Product;

AS UnitMargin gives the calculated column a name, called an alias. Without it, SSMS shows (No column name). You can alias ordinary columns too: ProductName AS Product. If an alias contains spaces, wrap it in square brackets: AS [Unit Margin]. The course avoids spaces in aliases.

Arithmetic operators are +, -, *, / and % (remainder). Two traps:

SELECT  7 / 2   AS IntegerDivision,   -- 3   : int / int discards the remainder
        7 / 2.0 AS DecimalDivision,   -- 3.500000 : one decimal operand gives a decimal result
        7 % 2   AS Remainder;         -- 1

The second trap is joining text. The + operator joins strings, but if any part is NULL the whole result is NULL. CONCAT() treats NULL as an empty string, and CONCAT_WS() (concatenate with separator) skips NULLs entirely:

SELECT  CustomerID,
        Email + N' / ' + Phone          AS PlusOperator,
        CONCAT(Email, N' / ', Phone)    AS ConcatFunction,
        CONCAT_WS(N' / ', Email, Phone) AS ConcatWithSeparator
FROM    Sales.Customer
WHERE   CustomerID IN (3, 5, 14)
ORDER BY CustomerID;

B5. The order SQL Server logically processes a query

You write SELECT first, but SQL Server logically evaluates the clauses in this order:

This explains a classic error. Because WHERE is evaluated before SELECT, an alias defined in the SELECT list does not exist yet in WHERE:

-- Fails: Invalid column name 'UnitMargin'.
SELECT ListPrice - StandardCost AS UnitMargin
FROM   Inventory.Product
WHERE  UnitMargin > 100;

-- Works: repeat the expression in WHERE.
SELECT ListPrice - StandardCost AS UnitMargin
FROM   Inventory.Product
WHERE  ListPrice - StandardCost > 100;

ORDER BY runs after SELECT, so it can use the alias. (On Day 3 you will learn derived tables and on Day 4 CTEs, which let you define a calculation once and filter on it.)

B6. Sorting with ORDER BY

SELECT  ProductCode, ProductName, ListPrice
FROM    Inventory.Product
ORDER BY ListPrice DESC,   -- most expensive first
         ProductCode;      -- tie-breaker: ASC is the default

• ASC (ascending, the default) or DESC (descending) applies to each column separately.
• Always add a tie-breaker that is unique, usually the primary key or another unique column. Three products cost £189.00; without ProductCode as a second sort column, their relative order is not guaranteed and could differ between runs or machines.
• In SQL Server, NULLs sort first in ascending order and last in descending order. Sorting products by Colour puts the six products without a colour at the top.

B7. Removing duplicates with DISTINCT

DISTINCT removes rows that are identical across every column in the SELECT list.

SELECT DISTINCT Country
FROM   Sales.Customer
ORDER BY Country;           -- 9 rows: one per country

Compare two versions on the product table, which has 30 rows:

DISTINCT treats all NULLs as equal to each other for this purpose. It also has a cost: SQL Server must sort or hash the rows to find duplicates. If you find yourself adding DISTINCT to “fix” unexpected duplicates, stop and work out why the duplicates appeared — on Day 8 you will see that this usually signals a join problem.

Part 2 — Filtering rows and combining conditions

B8. WHERE and comparison operators

WHERE keeps only rows for which the condition is TRUE.

LIKE wildcards: % matches any number of characters (including none); _ matches exactly one character. ProductCode LIKE 'T_T-%' matches TNT-001 to TNT-004.

Case sensitivity depends on the column’s collation. RetailLab uses a case-insensitive collation (_CI_), so ProductName LIKE N'%jacket%' and LIKE N'%Jacket%' return the same four products.

Dates and BETWEEN. On a date column, OrderDate BETWEEN '2025-12-01' AND '2025-12-31' correctly covers all of December. Columns that also store a time of day behave differently, so the course prefers the half-open pattern OrderDate >= '2025-12-01' AND OrderDate < '2026-01-01', which is correct for both. Day 12 explains why.

B9. AND, OR, NOT — and why parentheses matter

SQL Server evaluates NOT first, then AND, then OR, just as multiplication happens before addition in arithmetic. So

WHERE OrderDate >= '2025-11-01' AND OrderStatus <> 'Delivered' OR ShippingCost > 0

is read as

WHERE (OrderDate >= '2025-11-01' AND OrderStatus <> 'Delivered') OR ShippingCost > 0

which returns every order ever charged for shipping, regardless of date. Worked example 3 shows the difference: 41 rows instead of the intended 20. Whenever a condition mixes AND and OR, add parentheses, even where they are not strictly needed. They document your intent.

Part 3 — NULL, CASE and NULL functions

B10. What NULL means

NULL is a marker for “no value here”. It is not zero, not an empty string and not a value at all. In Ridgeway’s data it carries different business meanings:

Knowing which meaning applies is a business question, and it affects how you label or replace NULLs in output.

B11. Three-valued logic

Because NULL means “no value”, comparing it with anything — even another NULL — cannot be TRUE or FALSE. The result is a third logical value, UNKNOWN.

WHERE keeps only TRUE rows, so UNKNOWN rows are silently dropped. Three consequences:

1. WHERE Email = NULL returns no rows, ever. Use WHERE Email IS NULL or WHERE Email IS NOT NULL.
2. <> silently excludes NULLs. WHERE Colour <> N'Black' returns 18 products, not 24: the six products with no colour are neither “black” nor “not black” — they are unknown. If you want them, say so: WHERE Colour <> N'Black' OR Colour IS NULL.
3. NOT IN with a NULL in the list returns nothing. WHERE Colour NOT IN (N'Black', NULL) expands to Colour <> N'Black' AND Colour <> NULL; the second part is always UNKNOWN, so no row can be TRUE. This becomes a real risk on Day 3, when the list comes from a subquery.

These rules assume the default ANSI_NULLS ON setting, which SSMS uses and which you should never turn off.

B12. CASE expressions

CASE returns a value chosen by conditions. It is an expression, so it can appear anywhere a value can: in SELECT, ORDER BY, WHERE and elsewhere.

Searched CASE — each WHEN has its own condition:

CASE
    WHEN ListPrice < 50  THEN 'Budget'
    WHEN ListPrice < 150 THEN 'Mid-range'
    ELSE                      'Premium'
END

Simple CASE — compares one expression with a list of values:

CASE CustomerSegment
    WHEN 'VIP'      THEN 1
    WHEN 'Business' THEN 2
    ELSE                 3
END

Rules and pitfalls:

• WHEN clauses are checked top to bottom, and the first TRUE one wins. Put the most specific condition first. If the first test were ListPrice >= 50 THEN 'Mid-range', a £599 tent would be labelled Mid-range and the Premium branch would never be reached.
• Without ELSE, unmatched rows get NULL. Including an explicit ELSE makes the intent clear.
• All branches should return compatible data types. Mixing 'Premium' with the number 1 in one CASE fails when SQL Server tries to convert the text to a number.
• A simple CASE uses =, so CASE Colour WHEN NULL THEN … never matches. Use a searched CASE: WHEN Colour IS NULL THEN ….

B13. Functions for NULLs

The data-type difference between ISNULL and COALESCE matters. Inventory.Product.Size is nvarchar(10):

SELECT  ProductCode,
        Size,
        ISNULL(Size, N'One size fits all')   AS IsNullResult,
        COALESCE(Size, N'One size fits all') AS CoalesceResult
FROM    Inventory.Product
WHERE   ProductCode IN ('TNT-001', 'JKT-001')
ORDER BY ProductCode;

ISNULL returns nvarchar(10), so the replacement text is silently truncated to ten characters. COALESCE picks a type wide enough for its longest argument. Prefer COALESCE unless you have a specific reason to use ISNULL.

NULLIF in action — division by zero raises error 8134, but dividing by NULL simply returns NULL:

SELECT 10 / NULLIF(0, 0) AS SafeDivision;   -- NULL, no error

C. Dataset and starting state

Relevant columns

Preparation. If you have not changed anything since setup, no preparation is needed. If you are unsure, run scripts/setup/04_reset_training_data.sql in SQLCMD Mode (destructive: drops and rebuilds RetailLab), or run scripts 00 → 03.

Validation. Run this before you start. It should return 30, 40, 376 and 16.

USE RetailLab;
GO

SELECT  (SELECT COUNT(*) FROM Inventory.Product) AS Products,
        (SELECT COUNT(*) FROM Sales.Customer)    AS Customers,
        (SELECT COUNT(*) FROM Sales.SalesOrder)  AS Orders,
        (SELECT COUNT(*) FROM HR.Employee)       AS Employees;

(COUNT(*) counts rows. You will study it properly tomorrow.)

D. Worked examples

Open a new query window, run USE RetailLab; once, then highlight and run each example.

Worked example 1 — Product margins, sorted with a tie-breaker

/* Which products earn the most per unit sold?
   UnitMargin   = price minus cost
   MarginPct    = margin as a percentage of price, rounded to 2 decimal places */
SELECT  ProductCode,
        ProductName,
        ListPrice,
        StandardCost,
        ListPrice - StandardCost AS UnitMargin,
        CAST((ListPrice - StandardCost) * 100 / NULLIF(ListPrice, 0)
             AS decimal(5,2))    AS MarginPct
FROM    Inventory.Product
ORDER BY UnitMargin DESC,        -- ORDER BY may use the alias
         ProductCode;            -- unique tie-breaker

What to notice

• NULLIF(ListPrice, 0) guards against a zero price. No product is free today, but the query will not fail if one is added.
• CAST(… AS decimal(5,2)) rounds the percentage to two decimal places for display.

Expected result: 30 rows. The first six:

Business meaning: the bell tent has the highest margin per unit, but it was discontinued at the end of 2024 — a reminder that a single number rarely tells the whole story.

Worked example 2 — Where are our customers?

SELECT DISTINCT Country
FROM   Sales.Customer
ORDER BY Country;

Expected result: 9 rows — Denmark, France, Germany, Ireland, Italy, Spain, Sweden, United Kingdom, United States. DISTINCT collapses 40 customer rows into one row per country. ORDER BY is needed: without it, the list could come back in any order.

Worked example 3 — Recent orders needing attention (parentheses matter)

Customer Services asks: “Since 1 November 2025, which orders were either not yet delivered, or charged for shipping?”

-- Correct: the OR is grouped, then combined with the date condition.
SELECT  OrderID, OrderDate, SalesChannel, OrderStatus, ShippingCost
FROM    Sales.SalesOrder
WHERE   OrderDate >= '2025-11-01'
  AND   (OrderStatus <> 'Delivered' OR ShippingCost > 0)
ORDER BY OrderDate, OrderID;

Expected result: 20 rows, from order 1333 (1 Nov, Delivered, £4.95 shipping) to order 1375 (30 Dec, Pending). They include the one cancelled order in the period (1344), eight Shipped and four Pending orders.

Now remove the parentheses:

-- Wrong: AND binds first, so the date applies only to the status test.
SELECT  OrderID, OrderDate, SalesChannel, OrderStatus, ShippingCost
FROM    Sales.SalesOrder
WHERE   OrderDate >= '2025-11-01'
  AND   OrderStatus <> 'Delivered' OR ShippingCost > 0
ORDER BY OrderDate, OrderID;

Expected result: 41 rows. The extra 21 are older orders that were charged shipping, going back to January 2024. Nothing errors — the query is simply answering a different question. That is why precedence mistakes are dangerous.

Worked example 4 — Missing contact details (IS NULL, not = NULL)

-- Returns 0 rows: a comparison with NULL is UNKNOWN, never TRUE.
SELECT CustomerID, FirstName, LastName, Email, Phone
FROM   Sales.Customer
WHERE  Email = NULL;

-- Correct.
SELECT CustomerID, FirstName, LastName, Email, Phone
FROM   Sales.Customer
WHERE  Email IS NULL
   OR  Phone IS NULL
ORDER BY CustomerID;

Expected result (second query): 12 rows — customers 3, 5, 7, 10, 14, 17, 21, 25, 29, 32, 37 and 40. Customers 14 (Niamh Kelly) and 37 (Leo Fitzgerald) have neither an email nor a phone number.

Worked example 5 — Price bands with CASE, sorted by band

SELECT  ProductCode,
        ProductName,
        ListPrice,
        CASE
            WHEN ListPrice < 50  THEN 'Budget'
            WHEN ListPrice < 150 THEN 'Mid-range'
            ELSE                      'Premium'
        END AS PriceBand
FROM    Inventory.Product
ORDER BY CASE                          -- sort Premium first, then Mid-range, then Budget
             WHEN ListPrice < 50  THEN 3
             WHEN ListPrice < 150 THEN 2
             ELSE                      1
         END,
         ListPrice DESC,
         ProductCode;

Why CASE appears twice. Sorting by the alias PriceBand would sort alphabetically (Budget, Mid-range, Premium), which is not the business order. A second CASE in ORDER BY supplies a custom sort key.

Expected result: 30 rows: 12 Premium (TNT-004 at £599.00 first), 10 Mid-range and 8 Budget (COK-003 Gas Canister at £6.50 last). Because < is used, a product priced exactly £150.00 would be Premium and one at exactly £50.00 Mid-range; boundary rules like this should be agreed with the business.

Worked example 6 — One contact detail per customer with COALESCE

SELECT  CustomerID,
        CONCAT_WS(N' ', FirstName, LastName)          AS CustomerName,
        COALESCE(Email, Phone, N'No contact details') AS ContactDetail
FROM    Sales.Customer
WHERE   CustomerID IN (1, 5, 14)
ORDER BY CustomerID;

Expected result

COALESCE checks its arguments left to right and returns the first that is not NULL: email if known, otherwise phone, otherwise the fallback text.

E. Guided practical lab — Marketing’s January contact list

Marketing wants every active UK customer, how to contact them, and a list sorted VIPs first. Build the query one step at a time in a single query window. Run each step before moving on.

Step 1 — Look at the raw data

1. Do: Inspect the whole customer table.
2. Run:

   USE RetailLab;
   GO
   SELECT * FROM Sales.Customer ORDER BY CustomerID;

3. Observe: 40 rows (the count is shown in the bottom-right of SSMS). Scroll right: some Email and Phone cells say NULL; three customers have IsActive = 0.
4. Why it matters: Looking at the data before filtering it tells you which problems to expect.
5. Confirm: The status bar shows 40 rows.

Step 2 — Keep only active UK customers

1. Do: Replace * with the columns Marketing needs and add a WHERE clause with two conditions.
2. Run:

   SELECT  CustomerID, FirstName, LastName, CustomerSegment, Email, Phone
   FROM    Sales.Customer
   WHERE   Country = N'United Kingdom'
     AND   IsActive = 1
   ORDER BY CustomerID;

3. Observe: Both conditions must be TRUE for a row to appear.
4. Why it matters: Inactive customers should not receive marketing; non-UK customers are out of scope for this campaign.
5. Confirm: 23 rows.

Step 3 — Prove to yourself that = NULL does not work

1. Do: Find the active UK customers with no email, first the wrong way, then the right way.
2. Run:

   -- Wrong
   SELECT CustomerID, FirstName, LastName
   FROM   Sales.Customer
   WHERE  Country = N'United Kingdom' AND IsActive = 1 AND Email = NULL;
   
   -- Right
   SELECT CustomerID, FirstName, LastName
   FROM   Sales.Customer
   WHERE  Country = N'United Kingdom' AND IsActive = 1 AND Email IS NULL
   ORDER BY CustomerID;

3. Observe: The first query returns 0 rows without any error. The second returns 2 rows: Emma Wilson (5) and Henry Shaw (29).
4. Why it matters: A query that silently returns nothing is harder to spot than one that fails.
5. Confirm: Second query = 2 rows.

Step 4 — Decide the contact channel with CASE

1. Do: Add a column that says which channel Marketing should use.
2. Run:

   SELECT  CustomerID, FirstName, LastName, CustomerSegment,
           CASE
               WHEN Email IS NOT NULL THEN 'Email'
               WHEN Phone IS NOT NULL THEN 'Phone'
               ELSE                        'Post only'
           END AS ContactChannel
   FROM    Sales.Customer
   WHERE   Country = N'United Kingdom'
     AND   IsActive = 1
   ORDER BY CustomerID;

3. Observe: Customers 5 and 29 show Phone; everyone else shows Email. No active UK customer is Post only (the two customers with no details at all are in Ireland, and one of them is inactive).
4. Why it matters: The order of WHEN clauses encodes a business rule — email is preferred over phone.
5. Confirm: 23 rows, exactly 2 with Phone.

Step 5 — Add the contact detail itself with COALESCE

1. Do: Add a column holding the email, or the phone if there is no email.
2. Run: add this line after the CASE expression (remember the comma before it):

           , COALESCE(Email, Phone, N'No contact details') AS ContactDetail

3. Observe: For customers 5 and 29 the column shows a phone number.
4. Why it matters: One column that is always populated is easier for a mail-merge tool than two partly empty ones.
5. Confirm: No row shows NULL in ContactDetail.

Step 6 — Sort VIPs first, then by surname

1. Do: Replace ORDER BY CustomerID with a business-friendly sort.
2. Run: the complete query:

   SELECT  CustomerID, FirstName, LastName, CustomerSegment,
           CASE
               WHEN Email IS NOT NULL THEN 'Email'
               WHEN Phone IS NOT NULL THEN 'Phone'
               ELSE                        'Post only'
           END AS ContactChannel,
           COALESCE(Email, Phone, N'No contact details') AS ContactDetail
   FROM    Sales.Customer
   WHERE   Country = N'United Kingdom'
     AND   IsActive = 1
   ORDER BY CASE CustomerSegment
                WHEN 'VIP'      THEN 1
                WHEN 'Business' THEN 2
                ELSE                 3
            END,
            LastName,
            CustomerID;   -- tie-breaker in case two customers share a surname

3. Observe: Four VIPs (Ali, Bennett, Brooks, Morgan), then five Business customers (Mehta, Rahman, Shaw, Thompson, Wright), then fourteen Consumers starting with Baker and ending with Wilson.
4. Why it matters: A simple CASE in ORDER BY gives you a custom sort order without changing any data.
5. Confirm: 23 rows; first row Farah Ali (6), last row Emma Wilson (5).

You have just combined every idea from today in one realistic query.

F. Independent practice

Write each query yourself before looking at the solutions in section G. Every query must use ORDER BY with a unique tie-breaker.

Task 1 — Mid-priced products (straightforward)

Merchandising wants all products priced from £100 to £200 inclusive.

• Return ProductCode, ProductName and ListPrice.
• Sort by price, highest first, then by product code.

Success criteria: 10 rows. The first is BPK-002 (£199.00); the last is FTW-003 (£109.00). Three products at £189.00 appear in code order.

Task 2 — Everything that is not black (NULL awareness)

A photographer needs every product that is not black, including products for which colour does not apply.

• Return ProductCode, ProductName and Colour.
• Sort by product code.

Success criteria: 24 rows, including the six products whose Colour is NULL (COK-001, COK-002, COK-003, LGT-002, NAV-001, NAV-003).

Task 3 — 2025 orders that need action (precedence and CASE)

Customer Services wants all orders placed in 2025 that are either cancelled, or are online orders still pending.

• Return OrderID, OrderDate, SalesChannel, OrderStatus, and a column ActionNeeded showing Review cancellation for cancelled orders and Chase dispatch for pending ones.
• Use the half-open date pattern (>= start, < next start).
• Sort by order date, then order ID.

Success criteria: 8 rows — four cancellations (orders 1255, 1273, 1301, 1344) and four pending orders (1372–1375).

Task 4 — Employee directory (challenging)

HR wants a directory of all 16 employees with these columns:

Sort current employees before leavers, then by last name, then by employee ID.

Success criteria: 16 rows. First row Tom Baxter (4); last row James Reid (16, Left). Oliver Grant is the only New starter. Eleanor Hughes shows None (top of hierarchy). Five employees are in Band A.

Stretch challenge — Catalogue labels for waterproof products (optional)

The website needs a display label for every product whose Tags contain the word waterproof. The label is the product name, then — only if there is colour or size information — a hyphen and the details, for example:

• Storm Shell Waterproof Jacket - Navy, size M
• Waterproof Map Case (no colour or size, so no hyphen and no stray commas)

Return ProductCode and CatalogueLabel, sorted by product code.

Success criteria: 5 rows: FTW-001, FTW-002, JKT-001, JKT-002 and NAV-003, with no trailing separators or the text “size “ on its own.

G. Solutions and explanations

SELECT  ProductCode, ProductName, ListPrice
FROM    Inventory.Product
WHERE   ListPrice BETWEEN 100 AND 200
ORDER BY ListPrice DESC, ProductCode;

SELECT  ProductCode, ProductName, Colour
FROM    Inventory.Product
WHERE   Colour <> N'Black'
   OR   Colour IS NULL
ORDER BY ProductCode;

SELECT  OrderID,
        OrderDate,
        SalesChannel,
        OrderStatus,
        CASE
            WHEN OrderStatus = 'Cancelled' THEN 'Review cancellation'
            ELSE                                'Chase dispatch'
        END AS ActionNeeded
FROM    Sales.SalesOrder
WHERE   OrderDate >= '2025-01-01'
  AND   OrderDate <  '2026-01-01'
  AND   (    OrderStatus = 'Cancelled'
         OR (SalesChannel = 'Online' AND OrderStatus = 'Pending'))
ORDER BY OrderDate, OrderID;

SELECT  EmployeeID,
        CONCAT_WS(N' ', FirstName, LastName) AS FullName,
        JobTitle,
        CASE
            WHEN TerminationDate IS NOT NULL THEN 'Left'
            WHEN HireDate >= '2025-01-01'    THEN 'New starter'
            ELSE                                  'Established'
        END AS EmploymentStatus,
        COALESCE(CAST(ManagerID AS varchar(10)), 'None (top of hierarchy)') AS ReportsTo,
        CASE
            WHEN Salary >= 70000 THEN 'Band A'
            WHEN Salary >= 35000 THEN 'Band B'
            ELSE                      'Band C'
        END AS SalaryBand
FROM    HR.Employee
ORDER BY CASE WHEN TerminationDate IS NULL THEN 0 ELSE 1 END,
         LastName,
         EmployeeID;

SELECT  ProductCode,
        CONCAT(ProductName,
               N' - ' + NULLIF(CONCAT_WS(N', ', Colour, N'size ' + Size), N'')
              ) AS CatalogueLabel
FROM    Inventory.Product
WHERE   Tags LIKE N'%waterproof%'
ORDER BY ProductCode;

H. Knowledge check

1. Understanding. A colleague writes WHERE Phone = NULL to find customers without a phone number and gets no rows, although some exist. Explain why, and give the correct condition.

2. Query interpretation. How many of these orders does the query below return, and why might it not be what the author intended?

   SELECT OrderID FROM Sales.SalesOrder
   WHERE  OrderStatus = 'Pending' OR OrderStatus = 'Shipped' AND SalesChannel = 'Store';

3. Debugging. This query fails with Invalid column name ‘UnitMargin’. Why, and how do you fix it?

   SELECT ProductCode, ListPrice - StandardCost AS UnitMargin
   FROM   Inventory.Product
   WHERE  UnitMargin > 100;

4. Understanding. For the tent TNT-001, whose Size is NULL, what does ISNULL(Size, N'One size fits all') return, and why?

5. Debugging. What is wrong with this CASE expression?

   CASE
       WHEN ListPrice >= 50  THEN 'Mid-range'
       WHEN ListPrice >= 150 THEN 'Premium'
       ELSE 'Budget'
   END

I. Troubleshooting and cleanup

Cleanup. Today’s lesson is read-only and creates no objects, so there is nothing to clean up. Leave RetailLab exactly as it is: Day 2 starts from the same core data. You may close your query window without saving, or save it as day01_practice.sql for your own reference.

J. Completion checklist, recap and references

Completion checklist

• The validation query in section C returned 30, 40, 376 and 16.
• Worked example 3 returned 20 rows with parentheses and 41 without, and you can explain why.
• Your guided-lab query returns 23 rows, starting with Farah Ali and ending with Emma Wilson.
• Tasks 1–4 match the success criteria (10, 24, 8 and 16 rows).
• You answered the five knowledge-check questions before reading the answers.

Recap

• A table is a set of rows with no inherent order; primary keys identify rows and foreign keys connect tables.
• SELECT … FROM schema.table returns columns and expressions; aliases name them; ORDER BY with a unique tie-breaker fixes the order; DISTINCT removes duplicate output rows across all selected columns.
• Logical processing order is FROM → WHERE → SELECT → ORDER BY, which is why WHERE cannot see SELECT aliases but ORDER BY can.
• NOT binds before AND, and AND before OR. Parenthesise mixed conditions.
• NULL comparisons are UNKNOWN: use IS [NOT] NULL, and remember that <> and NOT IN quietly exclude NULLs.
• CASE returns the first matching branch; order the WHEN clauses deliberately and include an ELSE.
• COALESCE returns the first non-NULL value and sizes its result sensibly; ISNULL takes the first argument’s type and can truncate; NULLIF turns a value into NULL, which makes division safe; CONCAT_WS skips NULLs.

Next: Day 2 — Aggregate functions and GROUP BY, including a short primer on joining order headers to order lines.

References (Microsoft Learn)

• SELECT (Transact-SQL) — including the logical processing order of the SELECT statement
• WHERE (Transact-SQL)
• ORDER BY clause (Transact-SQL)
• Operator precedence (Transact-SQL)
• LIKE (Transact-SQL)
• NULL and UNKNOWN (Transact-SQL)
• CASE (Transact-SQL)
• COALESCE (Transact-SQL)
• ISNULL (Transact-SQL)
• NULLIF (Transact-SQL)
• CONCAT_WS (Transact-SQL)
• Data types (Transact-SQL)
• Data type precedence (Transact-SQL)