Week 1 · SQL Basics / Day 7
Goal: answer 20 assessment questions in 60 minutes, notes closed, SSMS only. Pass mark 16 of 20; a query that runs but returns wrong rows is wrong.
Result: 20 of 20 answered and checked against the database. Two answer-key queries (Q15 and Q17) run but return wrong rows; both are shown next to a corrected version.
SELECT LastName, FirstName
FROM Person.Person
WHERE PersonType = 'SC'
ORDER BY LastName, FirstName;'SC' means store contact: the person a store customer deals with. Listing LastName before FirstName in the SELECT puts last name first in the output, and ORDER BY LastName, FirstName sorts A to Z with first name as the tie-breaker. 753 people.
SELECT DISTINCT Color
FROM Production.Product
WHERE Color IS NOT NULL
ORDER BY Color;DISTINCT would normally return 10 rows including NULL (Day 1). WHERE Color IS NOT NULL removes the NULL before DISTINCT runs, leaving 9 colours, sorted alphabetically.
SELECT Name, ListPrice
FROM Production.Product
WHERE ListPrice BETWEEN 500 AND 2000
AND Name NOT LIKE 'M%';BETWEEN is inclusive (Day 2), so 500 and 2000 are included. NOT LIKE 'M%' removes every name starting with M (Mountain bikes, ML frames). 73 products.
SELECT FirstName, MiddleName, LastName, Suffix
FROM Person.Person
WHERE MiddleName IS NULL AND Suffix IS NULL;Both missing values are NULL, so IS NULL is the only correct test; MiddleName = NULL would return nothing. AND requires both to be missing. 8,496 people.
SELECT TOP 5 h.SalesOrderID, h.OrderDate, pp.FirstName, pp.LastName
FROM Sales.SalesOrderHeader h
JOIN Sales.Customer c ON h.CustomerID = c.CustomerID
LEFT JOIN Person.Person pp ON c.PersonID = pp.BusinessEntityID
ORDER BY h.OrderDate DESC, h.SalesOrderID DESC;Order -> customer -> person, the path from the table map. LEFT JOIN to Person keeps an order even if its customer had no person. 40 orders were placed on the final date, 30 June 2014, so ORDER BY OrderDate alone would pick 5 of them at random; the second sort key makes the answer repeatable.
Many orders share the last date (30 June 2014), so SalesOrderID DESC is added to make the top 5 predictable.
SELECT ProductID, Name
FROM Production.Product
ORDER BY ProductID
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;Page n with page size s skips (n - 1) x s rows (Day 3). Page 3 of 20 skips 40 and fetches the next 20.
SELECT EmailAddress,
LEFT(EmailAddress, CHARINDEX('@', EmailAddress) - 1) AS Username
FROM Person.EmailAddress;CHARINDEX finds the position of the @; LEFT keeps everything before it, and -1 drops the @ itself. One row per address: 19,972.
SELECT SalesOrderID, OrderDate, ShipDate,
DATEDIFF(day, OrderDate, ShipDate) AS DaysToShip
FROM Sales.SalesOrderHeader
ORDER BY DaysToShip DESC, SalesOrderID;DATEDIFF(day, start, end) counts the days. ORDER BY can use the alias because it runs after SELECT. Orders ship in 7 or 8 days, so the 8-day orders come first; SalesOrderID breaks the ties.
SELECT SUM(TotalDue) AS OnlineRevenue
FROM Sales.SalesOrderHeader
WHERE OnlineOrderFlag = 1;WHERE keeps the 27,659 online orders, then SUM adds their TotalDue: about 32.44 million, roughly a quarter of the 123.2 million total. Online orders are many but small; store orders are few but large.
SELECT Color, CAST(ROUND(AVG(ListPrice), 2) AS DECIMAL(10,2)) AS AvgPrice
FROM Production.Product
WHERE ListPrice > 0
GROUP BY Color
ORDER BY Color;WHERE ListPrice > 0 removes the components first ('priced products only'). GROUP BY makes one row per colour. ROUND rounds to 2 decimals and the CAST makes it display with exactly 2 decimals.
NULL colours form their own group. The question did not say to exclude them, so they stay.
SELECT SUM(CASE WHEN YEAR(OrderDate) = 2012 THEN 1 ELSE 0 END) AS Orders2012,
SUM(CASE WHEN YEAR(OrderDate) = 2013 THEN 1 ELSE 0 END) AS Orders2013,
SUM(CASE WHEN YEAR(OrderDate) = 2014 THEN 1 ELSE 0 END) AS Orders2014
FROM Sales.SalesOrderHeader;Conditional aggregation (Day 4): each CASE gives a 1 for orders in its year and SUM counts them, so three counts sit side by side in one row: 3,915, 14,182 and 11,761. 2014 only covers January to June.
SELECT c.CustomerID, c.PersonID, c.StoreID
FROM Sales.Customer c
WHERE NOT EXISTS (SELECT 1 FROM Sales.SalesOrderHeader h
WHERE h.CustomerID = c.CustomerID);NOT EXISTS keeps a customer when no order with its CustomerID exists (Day 6). 701 customers. Showing PersonID and StoreID explains a Day 5 puzzle: all 701 are stores with no person attached, and none of them ever ordered, which is why the INNER JOIN to Person lost no orders.
701 customers, and every one has a NULL PersonID. These are the same 701 that made the Day 5 INNER and LEFT counts match.
SELECT p.ProductID, p.Name
FROM Production.Product p
WHERE NOT EXISTS (SELECT 1 FROM Sales.SalesOrderDetail d
WHERE d.ProductID = p.ProductID);The same NOT EXISTS pattern against order lines. 238 products, matching all three methods from Day 6.
SELECT p.Name AS Product, d.OrderQty, d.UnitPrice, d.LineTotal
FROM Sales.SalesOrderDetail d
JOIN Production.Product p ON d.ProductID = p.ProductID
WHERE d.SalesOrderID = 43659;Filter the lines to one order and join Product for the names. 12 lines.
-- Answer key version
SELECT pe.FirstName + ' ' + pe.LastName AS Employee, pm.FirstName + ' ' + pm.LastName AS Manager
FROM HumanResources.Employee e
JOIN Person.Person pe ON e.BusinessEntityID = pe.BusinessEntityID
LEFT JOIN HumanResources.Employee m ON e.OrganizationNode.GetAncestor(1) = m.OrganizationNode
LEFT JOIN Person.Person pm ON m.BusinessEntityID = pm.BusinessEntityID
WHERE pm.BusinessEntityID IS NULL;This is the answer key's query with one line added, WHERE pm.BusinessEntityID IS NULL, to list the employees it gives no manager. It returns 7: the CEO plus his 6 direct reports. In AdventureWorks2022 the CEO's OrganizationNode is NULL, so GetAncestor(1) of a vice president ('/') never matches him. The answer key runs and returns 290 rows, but 6 of them are wrong, which by the test's own rule makes it wrong.
Check of the answer key: the WHERE shows who it leaves without a manager. 7 people, not just the CEO.
-- Fixed: level-1 employees report to the CEO, whose OrganizationNode is NULL
SELECT pe.FirstName + ' ' + pe.LastName AS Employee, e.JobTitle,
pm.FirstName + ' ' + pm.LastName AS Manager
FROM HumanResources.Employee e
JOIN Person.Person pe ON e.BusinessEntityID = pe.BusinessEntityID
LEFT JOIN HumanResources.Employee m
ON m.OrganizationNode = e.OrganizationNode.GetAncestor(1)
OR (e.OrganizationLevel = 1 AND m.OrganizationNode IS NULL)
LEFT JOIN Person.Person pm ON m.BusinessEntityID = pm.BusinessEntityID
ORDER BY e.OrganizationLevel, Employee;The fix from Day 5: an OR in the ON clause matches level-1 employees to the employee whose OrganizationNode is NULL, the CEO. Every one of the 290 employees appears (CEO included, thanks to the LEFT JOIN), and the vice presidents now show Ken Sánchez.
My answer: 290 employees, and only Ken Sánchez (the CEO) has no manager.
SELECT t.Name AS Territory, COUNT(*) AS Orders
FROM Sales.SalesOrderHeader h
JOIN Sales.SalesTerritory t ON h.TerritoryID = t.TerritoryID
GROUP BY t.Name
HAVING COUNT(*) > 4000
ORDER BY Orders DESC;Group orders by territory name and filter the groups with HAVING (WHERE cannot use COUNT). 4 territories: Australia, Southwest, Northwest and Canada.
-- Answer key version: individual people only
SELECT TOP 20 pp.FirstName, pp.LastName, SUM(h.TotalDue) AS Revenue
FROM Sales.SalesOrderHeader h
JOIN Sales.Customer c ON h.CustomerID = c.CustomerID
JOIN Person.Person pp ON c.PersonID = pp.BusinessEntityID
GROUP BY pp.FirstName, pp.LastName
HAVING SUM(h.TotalDue) > 500000
ORDER BY Revenue DESC;The answer key's note expects almost nothing, because it assumes store customers have no PersonID. In this database the store customers that ordered do have one, their contact person, so the query returns rows (capped at 20 by TOP). There is a second problem: it groups by first and last name, not by customer. Roger Harui shows 989,400.98, but his store's actual revenue is 989,184.08, because a second, unrelated Roger Harui (individual customer 11509) was merged in.
The key predicts very few or zero rows. It returns 20, and grouping by name merges different people who share a name.
-- Better: group by customer and show the store name too
SELECT h.CustomerID, st.Name AS Store,
pp.FirstName + ' ' + pp.LastName AS ContactPerson,
SUM(h.TotalDue) AS Revenue
FROM Sales.SalesOrderHeader h
JOIN Sales.Customer c ON h.CustomerID = c.CustomerID
LEFT JOIN Sales.Store st ON c.StoreID = st.BusinessEntityID
LEFT JOIN Person.Person pp ON c.PersonID = pp.BusinessEntityID
GROUP BY h.CustomerID, st.Name, pp.FirstName, pp.LastName
HAVING SUM(h.TotalDue) > 500000
ORDER BY Revenue DESC;Grouping by CustomerID gives one row per real customer, so totals cannot be merged by accident. LEFT JOINs to Store and Person bring in both names and keep the query correct for individual customers too. 40 customers exceed 500,000, all of them stores; Brakes and Gears leads with 989,184.08.
My answer: 40 customers, all stores, each with its own total. Always group by the key (CustomerID), not by a name.
SELECT FORMAT(OrderDate, 'yyyy-MM') AS YearMonth, SUM(TotalDue) AS Revenue
FROM Sales.SalesOrderHeader
WHERE OrderDate >= '2013-01-01' AND OrderDate < '2014-01-01'
GROUP BY FORMAT(OrderDate, 'yyyy-MM')
ORDER BY YearMonth;WHERE limits the rows to 2013 with the safe date range from Day 2, then FORMAT builds a 'yyyy-MM' label and the rows are grouped by it. 12 months; the label sorts correctly as text because the year comes first.
SELECT p.Name, p.ListPrice, p.ProductSubcategoryID
FROM Production.Product p
WHERE p.ListPrice > (SELECT AVG(x.ListPrice) FROM Production.Product x
WHERE x.ProductSubcategoryID = p.ProductSubcategoryID);A correlated subquery: for each product, the inner query averages the prices in that product's subcategory, and the product is kept if it is more expensive. 116 products. The 209 products with no subcategory never match, which is the right behaviour here, since they have no subcategory to compare against.
Products with no subcategory are left out, because x.ProductSubcategoryID = NULL is never true.
SELECT c.Name AS Category, COUNT(*) AS Products,
MIN(p.ListPrice) AS Cheapest, MAX(p.ListPrice) AS Dearest,
CASE WHEN MAX(p.ListPrice) - MIN(p.ListPrice) > 2000 THEN 'Wide' ELSE 'Narrow' END AS PriceRange
FROM Production.Product p
JOIN Production.ProductSubcategory s ON p.ProductSubcategoryID = s.ProductSubcategoryID
JOIN Production.ProductCategory c ON s.ProductCategoryID = c.ProductCategoryID
WHERE p.ListPrice > 0
GROUP BY c.Name;Join product to category, group by category, and calculate several aggregates at once. CASE can work on aggregates: if dearest minus cheapest is over 2000 the range is 'Wide'. Only Bikes (539.99 to 3,578.27) is Wide.
How the AdventureWorks tables used this week connect. Every join follows one of these lines.
| From | Column | To | Column | Note |
|---|---|---|---|---|
| Sales.SalesOrderDetail | SalesOrderID | Sales.SalesOrderHeader | SalesOrderID | Many lines per order |
| Sales.SalesOrderDetail | ProductID | Production.Product | ProductID | |
| Sales.SalesOrderHeader | CustomerID | Sales.Customer | CustomerID | |
| Sales.SalesOrderHeader | TerritoryID | Sales.SalesTerritory | TerritoryID | |
| Sales.SalesOrderHeader | SalesPersonID | Person.Person | BusinessEntityID | NULL for online orders |
| Sales.Customer | PersonID | Person.Person | BusinessEntityID | NULL for 701 store customers, none of which ever ordered |
| Sales.Customer | StoreID | Sales.Store | BusinessEntityID | NULL for individual customers |
| Production.Product | ProductSubcategoryID | Production.ProductSubcategory | ProductSubcategoryID | NULL for components |
| Production.ProductSubcategory | ProductCategoryID | Production.ProductCategory | ProductCategoryID | |
| HumanResources.Employee | BusinessEntityID | Person.Person | BusinessEntityID | Every employee is a person |
| Person.EmailAddress | BusinessEntityID | Person.Person | BusinessEntityID |