Week 1 · SQL Basics / Day 2
Goal: filter rows with any condition an interviewer could name, and never be surprised by NULL again.
Read: SQL Where, SQL And Or Not, SQL Null Values, SQL Like, SQL Wildcards, SQL In, SQL Between, SQL Operators.
SELECT Name, Color FROM Production.Product WHERE Color = 'Red';WHERE filters rows: only rows where the condition is true are returned. Color = 'Red' keeps the 38 red products. Text values go inside single quotes.
SELECT Name, Color FROM Production.Product WHERE Color <> 'Red';<> means 'not equal to'. It returns every product whose colour is something other than red: 218 rows.
SELECT Name, Color FROM Production.Product WHERE Color != 'Red';!= is the second spelling of 'not equal'. It gives exactly the same 218 rows as <>. Notice that 38 + 218 = 256, not 504: the 248 products whose Color is NULL appear in neither result, because comparing NULL with anything gives 'unknown', not true.
<> and != are the same operator and return the same row count. Neither one returns products whose Color is NULL.
SELECT Name, ListPrice FROM Production.Product WHERE ListPrice > 1000;> means strictly greater than. Products priced exactly 1000 would not be included.
SELECT Name, ListPrice FROM Production.Product WHERE ListPrice >= 1431.50;>= means greater than or equal to, so products priced exactly 1431.50 (the HL Road Frames) are included.
SELECT Name, ListPrice FROM Production.Product WHERE ListPrice < 10;< means strictly less than. Most of the 213 rows have ListPrice 0: they are components, not products for sale. That is why 'WHERE ListPrice > 0' is often the first filter you add.
Includes the components with ListPrice 0.
SELECT Name, ListPrice FROM Production.Product WHERE ListPrice <= 9.50;<= means less than or equal to. Products priced exactly 9.50 are included, products at 9.99 are not.
SELECT Name, ListPrice FROM Production.Product WHERE ListPrice BETWEEN 500 AND 2000;BETWEEN a AND b selects a range. It is inclusive: products priced exactly 500 or exactly 2000 would be included.
SELECT Name, ListPrice FROM Production.Product WHERE ListPrice >= 500 AND ListPrice <= 2000;The long way of writing the same range. It returns the same 101 rows as BETWEEN, proving that BETWEEN includes both ends. Always put the smaller value first: BETWEEN 2000 AND 500 returns nothing.
Same row count as BETWEEN, which proves BETWEEN includes both end values.
SELECT Name, Color FROM Production.Product
WHERE Color = 'Red' OR Color = 'Black' OR Color = 'Silver';OR returns a row if any one of the conditions is true, so this returns products that are red, black or silver: 174 rows.
SELECT Name, Color FROM Production.Product WHERE Color IN ('Red', 'Black', 'Silver');IN (list) is shorthand for a chain of ORs on the same column. It returns the same 174 rows and is easier to read and extend.
IN is a shorter way to write a list of ORs. Same row count.
SELECT Name, Color FROM Production.Product WHERE Color NOT IN ('Red', 'Black', 'Silver');NOT IN returns rows whose value is not in the list: 82 rows. 174 + 82 = 256, so again the 248 NULL-colour products are missing. To include them you must add OR Color IS NULL.
NOT IN does not return the products whose Color is NULL.
SELECT SalesOrderID, OrderDate, Status, TotalDue
FROM Sales.SalesOrderHeader
WHERE Status = 5 AND OrderDate > '2013-06-01';AND requires both conditions to be true. Status 5 means 'shipped'. Dates are written as text in 'YYYY-MM-DD' format and SQL Server converts them to dates automatically. Result: shipped orders placed after 1 June 2013.
SELECT Name, Color FROM Production.Product WHERE Color IS NULL;IS NULL is the correct way to find missing values: 248 products have no colour.
SELECT Name, Color FROM Production.Product WHERE Color IS NOT NULL;IS NOT NULL returns rows that do have a value: 256 rows. 248 + 256 = 504, every product.
SELECT Name, Color FROM Production.Product WHERE Color = NULL; -- zero rows, alwaysThe wrong way. NULL means 'unknown', so Color = NULL asks 'is this colour equal to an unknown value?' The answer is unknown, never true, so no rows come back. There is no error message, which makes this mistake easy to miss.
NULL means unknown, so Color = NULL is never true. Always use IS NULL or IS NOT NULL.
SELECT Name, Color, ListPrice FROM Production.Product
WHERE Color = 'Red' AND ListPrice > 1000 OR Color = 'Black';No brackets. AND is evaluated before OR, so SQL Server reads this as (Red AND price > 1000) OR Black. Result: 113 rows, including cheap black products.
SELECT Name, Color, ListPrice FROM Production.Product
WHERE Color = 'Red' AND (ListPrice > 1000 OR Color = 'Black');Brackets around the OR change the meaning: the product must be red, and then either expensive or black. A red product can never be black, so this only returns expensive red products: 20 rows.
SELECT Name, Color, ListPrice FROM Production.Product
WHERE (Color = 'Red' AND ListPrice > 1000) OR Color = 'Black';Brackets around the AND make the default order explicit. Same 113 rows as the first query, which proves AND binds before OR. Always use brackets when you mix AND and OR, so the reader does not have to remember the rule.
The first and third queries return the same rows because AND binds before OR. The second query returns a different count. Always use brackets.
SELECT Name FROM Production.Product WHERE Name LIKE 'Mountain%';LIKE does pattern matching on text. % means 'any number of characters, including none'. 'Mountain%' matches every name that starts with Mountain.
SELECT Name FROM Production.Product WHERE Name LIKE '%Frame%';A % on both sides means 'Frame appears anywhere in the name': 79 products.
SELECT Name FROM Production.Product WHERE Name LIKE '%Black, 42';A % only at the start means 'ends with'. This finds the 5 products whose name ends in 'Black, 42' (black, size 42).
SELECT DISTINCT FirstName FROM Person.Person WHERE FirstName LIKE '____';_ (underscore) matches exactly one character. Four underscores match names that are exactly four letters long. DISTINCT removes repeated names.
Each _ matches exactly one character, so this returns four-letter names.
SELECT DISTINCT FirstName FROM Person.Person WHERE FirstName LIKE 'A___';Combines a fixed letter with underscores: names that start with A and are exactly four letters long (Adam, Alex, Anna...).
SELECT COUNT(*) FROM Production.Product WHERE 1 = 1; -- always true1 = 1 is true for every row, so the filter keeps everything: 504. Developers often start dynamic queries with WHERE 1 = 1 so every real condition can be added as 'AND ...'.
SELECT COUNT(*) FROM Production.Product WHERE 1 = 0; -- always false1 = 0 is never true, so no rows pass and the count is 0. A common trick to get a table's column structure without any data.
SELECT Name FROM Production.Product WHERE Color = 'red';Lowercase 'red' still finds the 38 'Red' products, because the database comparison rules (the collation) ignore case.
SELECT SERVERPROPERTY('Collation'); -- CI = case-insensitive, AS = accent-sensitiveSERVERPROPERTY('Collation') shows the server's default comparison rules: SQL_Latin1_General_CP1_CI_AS. CI = case-insensitive ('red' = 'Red'). AS = accent-sensitive ('e' and 'é' are different).
CI in the result means case-insensitive, so 'red' matches 'Red'. AS means accent-sensitive, so 'e' and 'é' are treated as different characters.
SELECT Name FROM Production.Product WHERE ListPrice > '1000';'1000' is text, but ListPrice is a number (money). SQL Server converts the text to a number automatically (implicit conversion) and the query works.
SELECT Name FROM Production.Product WHERE Name > 100;Now the comparison goes the other way. Numbers have higher priority than text, so SQL Server tries to convert every Name into an integer. 'Adjustable Race' is not a number, so the query fails with error 245 (conversion failed). Always compare a column with a value of the same type.
Error 245: SQL Server tries to turn every Name into a number and fails.
SELECT Name FROM Production.Product WHERE Name LIKE '[HM]%';[HM] matches one character that is either H or M. So this returns names starting with H or M. Square-bracket wildcards are a SQL Server (T-SQL) feature; standard SQL does not have them.
SELECT Name FROM Production.Product WHERE Name LIKE '[^A-M]%';[A-M] is a range of letters and ^ inside the brackets means NOT. So this returns names whose first letter is not between A and M, i.e. names starting with N to Z (or a digit or symbol).
SELECT Name FROM Production.Product WHERE Name LIKE '%[0-9]%';[0-9] matches any single digit. With % on both sides this returns every product name that contains at least one number, such as sizes (58, 44) or model numbers (Road-150).
SELECT Name FROM Production.Product WHERE Name LIKE '%\_%' ESCAPE '\';_ is normally a wildcard. To search for a real underscore you 'escape' it: ESCAPE '\' tells SQL Server that a character after \ should be taken literally. No product names contain an underscore, so the correct answer here is 0 rows. Without the escape, '%_%' would match every name with at least one character.
SELECT TOP 5 ModifiedDate FROM Sales.SalesOrderHeader WHERE ModifiedDate = '2011-06-07';ModifiedDate is a datetime, so it stores a time as well as a date. '2011-06-07' means midnight (00:00:00.000) on that day. The query only matches rows modified at exactly midnight, so any row with a later time on that day is missed. In AdventureWorks every ModifiedDate happens to be at midnight, so here it still finds rows; on real data with times it would silently miss most of them.
SELECT TOP 5 ModifiedDate FROM Sales.SalesOrderHeader
WHERE CAST(ModifiedDate AS DATE) = '2011-06-07';CAST(ModifiedDate AS DATE) removes the time part, so every row from 7 June 2011 matches, whatever the time. The faster way on large tables is a range: ModifiedDate >= '2011-06-07' AND ModifiedDate < '2011-06-08', because it lets SQL Server use an index.
CAST(... AS DATE) drops the time, so every row from that day matches whatever its time.
SELECT EmailAddress FROM Person.EmailAddress WHERE EmailAddress LIKE '%@%.%';The pattern means: some characters, an @, some characters, a dot, some characters. Every real email address matches this rough shape. It is not full validation, just a quick sanity check.
SELECT EmailAddress FROM Person.EmailAddress WHERE EmailAddress NOT LIKE '%@%.%';NOT LIKE returns the rows that fail the check. 0 rows means every email address in the table has the expected shape.
SELECT Name, Color, ListPrice, Class
FROM Production.Product
WHERE (Color IN ('Black', 'Silver') OR Color IS NULL)
AND ListPrice BETWEEN 100 AND 2000
AND Name NOT LIKE '%Frame%'
AND Class = 'H';In English before running: 'high-class (H) products that are black, silver or have no colour, priced from 100 to 2000, and are not frames.' The brackets around the colour test matter: without them the OR would apply to the whole WHERE clause and let in unwanted rows.
In English: high-class products that are black, silver or have no colour, priced 100 to 2000, and are not frames.
SELECT Name, Color, ListPrice FROM Production.Product
WHERE ListPrice BETWEEN 800 AND 3000
AND (Color NOT IN ('Red', 'Black', 'Silver') OR Color IS NULL);NOT IN alone would drop the NULL colours (see drill 4), so OR Color IS NULL adds them back. The brackets keep that OR separate from the price condition.
SELECT FirstName, MiddleName, LastName FROM Person.Person WHERE MiddleName IS NULL;A missing middle name is stored as NULL, so IS NULL is the only correct test. MiddleName = NULL would return nothing.
SELECT FirstName, LastName FROM Person.Person WHERE LastName LIKE '%ez';% at the start and nothing after 'ez' means 'ends with ez': Sanchez, Lopez, Gonzalez and so on.
SELECT SalesOrderID, OrderDate, TotalDue FROM Sales.SalesOrderHeader
WHERE OrderDate >= '2013-07-01' AND OrderDate < '2013-08-01'
AND TotalDue > 10000;For a whole month use 'start inclusive, next month exclusive': >= 1 July and < 1 August. This catches every time on 31 July. BETWEEN '2013-07-01' AND '2013-07-31' would miss orders placed after midnight on 31 July.
Start inclusive, next month exclusive. Safer than BETWEEN for dates.
SELECT Name FROM Production.Product WHERE Name LIKE '%,%';A comma is not a wildcard, so it can be searched for directly with % on both sides. These are mostly products with a size or colour after the comma, like 'HL Road Frame - Red, 58'.
ListPrice = 0 are components, not free items. WHERE ListPrice > 0 is often the first filter you add.