Basic Querying with SELECT

Lab 7: Write 20 queries against AdventureWorks2022 (Person.Person, Sales.SalesOrderHeader) that cover filters, sorting, paging, and CASE.

7.1 SELECT Syntax

SELECT   [DISTINCT] [TOP (n) [PERCENT] [WITH TIES]] column_list
FROM     table_source
WHERE    search_condition
GROUP BY group_by_expression
HAVING   search_condition
ORDER BY order_expression [ASC|DESC]
OFFSET n ROWS FETCH NEXT m ROWS ONLY;

7.2 Logical Query Processing Order (Critical!)

This is why you cannot use a SELECT alias in WHERE, but you can use it in ORDER BY.

1. FROM (and JOINs, APPLY, PIVOT)
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT  (expressions, aliases, DISTINCT)
6. ORDER BY
7. TOP / OFFSET-FETCH

7.3 Basic Examples

SELECT * FROM dbo.Customers;                        -- avoid * in production code
SELECT FirstName, LastName, Email FROM dbo.Customers;
SELECT FirstName + ' ' + LastName AS FullName FROM dbo.Customers;
SELECT ProductName, UnitPrice, UnitPrice * 1.18 AS PriceWithGST FROM dbo.Products;
SELECT DISTINCT Country FROM dbo.Customers;
SELECT DISTINCT City, Country FROM dbo.Customers;  -- distinct combinations

7.4 Filtering with WHERE

-- Comparison: =, <>, !=, <, >, <=, >=
SELECT * FROM dbo.Products WHERE UnitPrice > 500;

-- Logical: AND, OR, NOT (precedence: NOT > AND > OR; use parentheses)
SELECT * FROM dbo.Products WHERE (CategoryID = 1 OR CategoryID = 2) AND Stock > 0;

-- BETWEEN (inclusive)
SELECT * FROM dbo.Orders WHERE OrderDate BETWEEN '2025-01-01' AND '2025-01-31';
-- Better for dates (half-open range):
SELECT * FROM dbo.Orders WHERE OrderDate >= '2025-01-01' AND OrderDate < '2025-02-01';

-- IN / NOT IN
SELECT * FROM dbo.Customers WHERE Country IN (N'India', N'USA', N'UK');

-- LIKE with wildcards: %, _, [abc], [a-z], [^a]
SELECT * FROM dbo.Customers WHERE LastName LIKE 'S%';
SELECT * FROM dbo.Customers WHERE FirstName LIKE '_a%';
SELECT * FROM dbo.Customers WHERE Email LIKE '%[_]%' ;           -- literal underscore
SELECT * FROM dbo.Products WHERE ProductName LIKE '%50!%%' ESCAPE '!';

7.5 NULL Handling

NULL means unknown. Any comparison with NULL yields UNKNOWN, which is three-valued logic.

SELECT * FROM dbo.Customers WHERE Email IS NULL;
SELECT * FROM dbo.Customers WHERE Email IS NOT NULL;
-- WRONG: WHERE Email = NULL  (never true)
-- NOT IN with a NULL in the list returns no rows!
SELECT * FROM T WHERE x IS DISTINCT FROM y;   -- NULL-safe comparison (2022+)

7.6 Sorting

SELECT * FROM dbo.Products ORDER BY CategoryID ASC, UnitPrice DESC;
SELECT FirstName AS FN FROM dbo.Customers ORDER BY FN;   -- alias allowed
SELECT * FROM dbo.Customers ORDER BY CASE WHEN Country = N'India' THEN 0 ELSE 1 END, LastName;

7.7 Limiting Rows

SELECT TOP (10) * FROM dbo.Products ORDER BY UnitPrice DESC;
SELECT TOP (10) PERCENT * FROM dbo.Products ORDER BY UnitPrice DESC;
SELECT TOP (3) WITH TIES * FROM dbo.Products ORDER BY UnitPrice DESC;

-- Paging (requires ORDER BY)
DECLARE @Page INT = 3, @Size INT = 20;
SELECT ProductID, ProductName FROM dbo.Products
ORDER BY ProductID
OFFSET (@Page - 1) * @Size ROWS FETCH NEXT @Size ROWS ONLY;

7.8 CASE Expression

-- Simple CASE
SELECT OrderID, CASE Status WHEN 'Pending' THEN 'P' WHEN 'Shipped' THEN 'S' ELSE 'Other' END
FROM dbo.Orders;

-- Searched CASE
SELECT ProductName,
       CASE WHEN UnitPrice < 100  THEN 'Budget'
            WHEN UnitPrice < 1000 THEN 'Mid'
            ELSE 'Premium' END AS PriceBand
FROM dbo.Products;

7.9 Comments and Formatting

Lab 7: Write 20 queries against AdventureWorks2022 (Person.Person, Sales.SalesOrderHeader) that cover filters, sorting, paging, and CASE.

-- single-line comment
/* multi-line
   comment */