Lesson 6 Study Guide

Lesson 6 reviews everything so far, adds the built-in functions that clean and reshape data, and teaches two habits that make queries faster and cheaper. Then play SQL Noir to practise.

Review Lessons 1–5 in one map

Concepts

How data is stored: tables, schema, database, warehouse
Data types
Fact and dimension tables, keys, relationships

Key commands

SELECT FROM WHERE
GROUP BY HAVING ORDER BY TOP
JOIN, UNION, CASE WHEN

Complex logic

Subquery and CTE
Window functions
Built-in functions (today)

Execution order to remember: FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → TOP.

Convert CAST and COALESCE

CAST(x AS type) changes a data type. You used it all through Lesson 3 to join keys of different types, and in Lesson 4 to build percentages.
COALESCE(a, b, c) returns the first value that is not NULL. Typical use: show 'Cash' when there is no card type, or 0 when a number is missing.

Dates Add, extract, compare

Function	Does
`DATEADD(day, 7, d)`	add 7 days → 2023-05-12
`YEAR / MONTH / DAY(d)`	extract a part
`DATEPART(part, d)`	year, quarter, month, weekday ...
`DATEDIFF(day, a, b)`	days between two dates
`DATE_FORMAT / FORMAT`	turn a date or number into text in a given pattern

Which exact names exist depends on the dialect. The course database is T-SQL.

Text Clean and cut strings

Function	Example	Result
`LEN`	LEN('banana')	6
`TRIM`	TRIM(' banana ')	banana
`REPLACE`	REPLACE('banana','a','e')	benene
`LEFT / RIGHT`	RIGHT('banana',3)	ana
`SUBSTRING`	SUBSTRING('banana',2,3)	ana
`CONCAT`	CONCAT('apple','banana')	applebanana
`FORMAT`	FORMAT(12345.6789,'#,##0.00')	12,345.68

Speed Two optimisation tips

Remove extra processing. Select only the columns you need. Skip DISTINCT, ORDER BY and extra joins that the result does not need.
Reduce the dataset early. Filter in WHERE, or inside a subquery or CTE, before joining and grouping. Fewer rows enter every later step.

Lesson 4's example: orders have 1M rows × 10 columns, products 100 rows × 100 columns. Trim both to what you use before the JOIN.

Practice SQL Noir

The slides end with a detective-style SQL game: sqlnoir.com. Solve cases using JOIN, GROUP BY, CTE and window functions.

Homework Where the Lesson 5–6 homework uses these functions

Function	Where it appears in the answer key
`CAST`	Joining sub-category and category keys (HW 2, 4, 5, 7, 8, 9) and `TRY_CAST` in HW 7
`COALESCE / CASE`	Cash when no card (HW 9)
`YEAR / MONTH`	Yearly and monthly grouping (HW 3, 4, 5, 7)

Open the Lesson 5 guide for the 10 homework solutions.

Watch out Mistakes with built-in functions

A function on a column blocks filtering shortcuts. WHERE YEAR(OrderDate) = 2012 is fine for learning. On huge tables a range (OrderDate >= '2012-01-01' AND OrderDate < '2013-01-01') is usually faster because it can use an index.
Dialect differences. LEN and DATEADD are T-SQL. MySQL and BigQuery use LENGTH, DATE_ADD or DATE_FORMAT. Check the dialect before copying code.
Two slide typos. REPLACE('banana','a','e') returns benene (lower case), not "Benene". SUBSTRING('banana', 2, 3) starts at position 2 (the first a) and returns ana, not "nan". Positions count from 1. Try both in the playground.
Weekday numbers depend on a server setting. Test DATEPART(weekday, ...) on a known date.

Playground Try a function on your own input

Each card runs the function in your browser and shows the SQL you would write. Edit the values.