Week 1 · SQL Basics / Day 3
Goal: control the order and size of results, and reshape values with string and date functions.
Read: SQL Order By, SQL Select Top, SQL Aliases, and the SQL Server Functions reference (string and date sections).
SELECT Name, ListPrice FROM Production.Product ORDER BY ListPrice;ORDER BY sorts the result. The default direction is ascending (ASC), smallest first, so the 0-priced components come first. ORDER BY is the only way to guarantee the order of rows; without it SQL Server returns rows in whatever order is quickest.
SELECT Name, ListPrice FROM Production.Product ORDER BY ListPrice DESC;DESC reverses the order: most expensive first. The Road-150 bikes at 3578.27 are at the top.
SELECT Name, Color, ListPrice FROM Production.Product ORDER BY Color, ListPrice DESC;Sorting by two columns: first by Color (A to Z), then, inside each colour, by ListPrice from highest to lowest. Each column has its own direction: DESC only applies to ListPrice, Color is still ascending.
SELECT FirstName, LastName, PersonType FROM Person.Person
ORDER BY PersonType, LastName, FirstName;Three sort levels. SQL Server sorts by PersonType first; ties are broken by LastName; any remaining ties are broken by FirstName. Like a phone book grouped by type.
SELECT TOP 5 Name, ListPrice FROM Production.Product ORDER BY ListPrice DESC;TOP 5 with ORDER BY ... DESC is the standard 'top N' pattern: sort from most expensive and keep the first five.
SELECT TOP 5 Name, ListPrice FROM Production.Product WHERE ListPrice > 0 ORDER BY ListPrice;Without WHERE ListPrice > 0 the cheapest five would all be components priced 0. The WHERE runs first and removes them, then ORDER BY sorts the remaining products, then TOP keeps five.
WHERE ListPrice > 0 removes the components, otherwise the cheapest five would all be 0.
SELECT Name FROM Production.Product ORDER BY ListPrice DESC;You can sort by a column you are not showing. SQL Server still has ListPrice available for each row while it sorts.
SELECT DISTINCT Name FROM Production.Product ORDER BY ListPrice DESC;Error 145. DISTINCT first collapses the rows down to unique names. After that, one name could in theory stand for several rows with different prices, so SQL Server does not know which ListPrice to sort by. Rule: with DISTINCT, every ORDER BY column must be in the SELECT list.
Error 145: with DISTINCT, ORDER BY items must appear in the select list.
SELECT ProductID, Name FROM Production.Product
ORDER BY ProductID OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;OFFSET 10 ROWS skips the first 10 rows (page 1); FETCH NEXT 10 ROWS ONLY returns the following 10 (page 2). OFFSET-FETCH must come after an ORDER BY, because pages only make sense in a fixed order.
SELECT ProductID, Name FROM Production.Product
ORDER BY ProductID OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;Page 3 skips 20 rows. The general formula for page n with page size s is OFFSET (n - 1) * s ROWS FETCH NEXT s ROWS ONLY. This is how websites show results one page at a time.
Page n of size s: OFFSET (n - 1) * s ROWS FETCH NEXT s ROWS ONLY.
SELECT TOP 3 Name, ListPrice FROM Production.Product ORDER BY ListPrice DESC;TOP 3 returns exactly 3 rows. But five Road-150 bikes share the top price of 3578.27, so two of them are cut off, and which two is not defined.
SELECT TOP 3 WITH TIES Name, ListPrice FROM Production.Product ORDER BY ListPrice DESC;WITH TIES also returns any row whose ORDER BY value equals the last row's value. Because five bikes cost 3578.27, this returns 5 rows instead of 3. WITH TIES requires an ORDER BY.
WITH TIES also returns every row that ties with the last one, so it returns 5 rows, not 3.
SELECT p.Name AS ProductName, p.ListPrice Price FROM Production.Product AS p;Aliases rename things. Column aliases (ProductName, Price) change the heading in the result. AS is optional: 'p.ListPrice Price' works too, but AS is clearer. The table alias p is a short name for Production.Product, so columns can be written p.Name. Table aliases become essential when joining several tables.
SELECT p.Name, p.ListPrice FROM Production.Product p;The same table alias without AS. When a table has an alias you refer to its columns through the alias (p.Name).
SELECT Name, ListPrice FROM Production.Product ORDER BY 2 DESC;ORDER BY 2 means 'sort by the second column in the SELECT list' (ListPrice). It works, but it is a bad habit: if someone later adds or reorders columns, the query silently sorts by the wrong column with no error. Always write the column name.
Bad habit: if the SELECT list changes, ORDER BY 2 silently sorts by a different column.
SELECT Name, Color FROM Production.Product ORDER BY Color;In SQL Server, NULL counts as lower than any value, so in an ascending sort the 248 products with no colour come first.
SELECT Name, Color FROM Production.Product
ORDER BY CASE WHEN Color IS NULL THEN 1 ELSE 0 END, Color;The CASE expression makes a hidden sort key: 1 for NULL colours, 0 for everything else. Sorting on that first puts real colours (0) before NULLs (1); the second sort key, Color, then sorts the colours alphabetically. SQL Server has no NULLS LAST keyword, so this is the standard workaround.
The CASE gives NULL colours a 1 and the rest a 0, so NULLs sort last.
SELECT TOP 1 SalesOrderID, OrderDate, TotalDue FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC;TOP 1 + ORDER BY ... DESC returns the single most recent order. This shape (latest login, newest order, highest score) comes up constantly. If several orders share the latest date, add a second sort column such as SalesOrderID DESC so the result is predictable.
SELECT TOP 10
FirstName,
UPPER(FirstName) AS Upper,
LOWER(LastName) AS Lower,
LEN(LastName) AS LastNameLength,
LEFT(FirstName, 1) AS Initial,
CONCAT(FirstName, ' ', MiddleName, ' ', LastName) AS FullName, -- CONCAT treats NULL as empty; + does not
REPLACE(LastName, 'a', '@') AS Replaced,
REVERSE(FirstName) AS Backwards
FROM Person.Person;UPPER and LOWER change case. LEN counts the characters (ignoring trailing spaces). LEFT(text, n) takes the first n characters, here the initial. CONCAT joins values and treats NULL as an empty string, so FullName is never NULL, unlike the + operator on Day 1. REPLACE swaps every 'a' for '@' (case-insensitive here because of the CI collation). REVERSE writes the text backwards.
CONCAT treats NULL as an empty string, so FullName is never NULL (compare Day 1 drill 9).
SELECT TOP 10 EmailAddress, LEFT(EmailAddress, CHARINDEX('@', EmailAddress) - 1) AS Username
FROM Person.EmailAddress;CHARINDEX('@', EmailAddress) returns the position of the @ (for example 6). LEFT(..., position - 1) keeps everything before it. The -1 removes the @ itself. A common pattern for splitting text at a separator.
SELECT TOP 5 Name, FORMAT(ListPrice, 'N2') AS Price FROM Production.Product WHERE ListPrice > 0;FORMAT(value, 'N2') turns a number into text with thousands separators and 2 decimal places (3,578.27). The result is text, so use FORMAT only for display, not for further calculations. FORMAT is also slow on large tables.
SELECT TOP 10
SalesOrderID,
OrderDate,
YEAR(OrderDate) AS Yr,
MONTH(OrderDate) AS Mth,
DATENAME(month, OrderDate) AS MonthName,
DATENAME(weekday, OrderDate) AS DayName,
DATEDIFF(day, OrderDate, ShipDate) AS DaysToShip,
DATEADD(day, 30, OrderDate) AS PaymentDue,
EOMONTH(OrderDate) AS MonthEnd,
CAST(OrderDate AS DATE) AS DateOnly,
CONVERT(varchar, OrderDate, 103) AS UKFormat,
FORMAT(OrderDate, 'yyyy-MM') AS YearMonth
FROM Sales.SalesOrderHeader;YEAR and MONTH return numbers. DATENAME returns names ('May', 'Tuesday'). DATEDIFF(day, start, end) counts the days between two dates. DATEADD(day, 30, date) adds 30 days. EOMONTH gives the last day of the month. CAST(... AS DATE) drops the time. CONVERT(varchar, date, 103) formats as dd/mm/yyyy (style 103 = British). FORMAT(date, 'yyyy-MM') gives a year-month label, useful for monthly reports.
SELECT GETDATE(), SYSDATETIME(), GETUTCDATE();GETDATE() returns the current local date and time (datetime, about 3 ms precision). SYSDATETIME() returns the same moment with higher precision (datetime2, 100 ns). GETUTCDATE() returns the current time in UTC. Pakistan time is UTC+5, so GETUTCDATE() is 5 hours behind GETDATE() on this PC.
SELECT TOP 10 Name, Color,
ISNULL(Color, 'No colour') AS ColorOrDefault,
COALESCE(Color, Class, 'Unknown') AS FirstNonNull
FROM Production.Product;ISNULL(Color, 'No colour') replaces a NULL with a default value. COALESCE takes any number of arguments and returns the first one that is not NULL: the colour if there is one, otherwise the class, otherwise 'Unknown'. COALESCE is standard SQL and works in other databases; ISNULL is SQL Server only.
SELECT TOP 5 Name, ListPrice,
ROUND(ListPrice * 1.17, 2) AS WithTax,
CEILING(ListPrice) AS Up,
FLOOR(ListPrice) AS Down,
ListPrice % 100 AS Remainder
FROM Production.Product WHERE ListPrice > 0;ROUND(x, 2) rounds to 2 decimal places. CEILING always rounds up to the next whole number, FLOOR always rounds down. % is modulo: the remainder after dividing by 100 (for 337.22 it is 37.22).
SELECT TOP 5 CAST(ListPrice AS INT) AS Whole,
CAST(ListPrice AS VARCHAR(20)) + ' USD' AS Labelled
FROM Production.Product WHERE ListPrice > 0;CAST(value AS type) converts between data types. CAST(ListPrice AS INT) removes the decimals (SQL Server rounds money values to the nearest whole number here). Casting to VARCHAR turns the number into text so it can be joined to ' USD' with +; adding a number and text directly would cause a conversion error.
SELECT TOP 1 Name FROM Production.Product ORDER BY NEWID(); -- random rowNEWID() creates a new random unique identifier for every row. Sorting by it shuffles the rows, and TOP 1 picks the first, so you get a random product each time you run it. Fine for small tables; slow on very large ones because every row must be sorted.
SELECT TOP 3 SalesOrderID, OrderDate FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC;The 'latest' shape from drill 10, with TOP 3 and only the two requested columns.
SELECT ProductID, Name FROM Production.Product
ORDER BY Name OFFSET 75 ROWS FETCH NEXT 25 ROWS ONLY;Page 4 with 25 per page skips (4 - 1) * 25 = 75 rows and fetches the next 25. Rows 76 to 100 in name order.
SELECT UPPER(LastName) + ', ' + UPPER(LEFT(FirstName, 1))
+ LOWER(SUBSTRING(FirstName, 2, LEN(FirstName))) AS PersonName
FROM Person.Person;UPPER(LastName) gives the capitals. For 'Firstname' casing, the first letter is made upper case with UPPER(LEFT(FirstName, 1)) and the rest lower case with LOWER(SUBSTRING(FirstName, 2, LEN(FirstName))). SUBSTRING(text, start, length) cuts out part of a string. The pieces are joined with +; neither name can be NULL in this table, so + is safe.
SELECT SalesOrderID, OrderDate, ShipDate, DATEDIFF(day, OrderDate, ShipDate) AS DaysToShip
FROM Sales.SalesOrderHeader
ORDER BY DaysToShip DESC;DATEDIFF(day, OrderDate, ShipDate) calculates the gap in days. ORDER BY can use the alias DaysToShip because ORDER BY runs after SELECT. (WHERE runs before SELECT, so it could not use the alias.) AdventureWorks orders ship 7 or 8 days after ordering, so the 8-day orders come first.
SELECT TOP 1 Name, ListPrice
FROM (SELECT TOP 2 Name, ListPrice FROM Production.Product ORDER BY ListPrice DESC) AS t
ORDER BY ListPrice;The inner query (subquery) takes the 2 most expensive products. The outer query sorts those two ascending and keeps the first, which is the second most expensive. Watch out for ties: five products share the top price, so this returns another 3578.27 product. The next query solves the 'second highest price' version.
Ties: the two dearest products cost the same, so this returns a 3578.27 product. The next query asks for the second highest price.
SELECT TOP 1 ListPrice
FROM (SELECT DISTINCT TOP 2 ListPrice FROM Production.Product ORDER BY ListPrice DESC) AS t
ORDER BY ListPrice;Adding DISTINCT inside the subquery takes the two highest different prices (3578.27 and 3399.99). The outer query keeps the lower of those, so the answer is the second highest price, 3399.99. Subqueries are covered properly on Day 6.