Twenty Practice Queries on One Small Schema

Reading a query is easier than writing one from a blank window. These practice queries use the same small sales dataset from start to finish. Predict each result before running the answer, then explain any difference in your own words.

Build One Dataset You Can Understand

Run the setup once in a single SSMS connection. Every answer uses these temporary tables, so keep that connection open. I ran every query on SQL Server 2025, and each expected result below matches what came back.

I prefer a tiny dataset when checking the meaning of a query. I also include missing relationships and tied values deliberately. Large random inputs can hide a logical mistake behind a very convincing grid.

The sample contains customers, products, orders, and order lines. Product prices on order lines preserve each transaction's selling price. The product catalog price remains a separate value that can change independently.

DROP TABLE IF EXISTS #Lines;
DROP TABLE IF EXISTS #Orders;
DROP TABLE IF EXISTS #Products;
DROP TABLE IF EXISTS #Customers;
CREATE TABLE #Customers
(CustomerId int PRIMARY KEY, CustomerName varchar(30) NOT NULL);
CREATE TABLE #Products
(ProductId int PRIMARY KEY, ProductName varchar(30) NOT NULL, CatalogPrice decimal(10,2) NOT NULL);
CREATE TABLE #Orders
(OrderId int PRIMARY KEY, CustomerId int NOT NULL, OrderDate date NOT NULL);
CREATE TABLE #Lines
(OrderId int NOT NULL, ProductId int NOT NULL, Quantity int NOT NULL,
 UnitPrice decimal(10,2) NOT NULL, PRIMARY KEY(OrderId, ProductId));
INSERT #Customers VALUES (1,'Ada'),(2,'Ben'),(3,'Casey'),(4,'Drew');
INSERT #Products VALUES (1,'Pen',2),(2,'Pad',5),(3,'Mug',8),(4,'Eraser',1);
INSERT #Orders VALUES
(101,1,'20250101'),(102,1,'20250103'),(103,2,'20250103'),(105,3,'20250106');
INSERT #Lines VALUES
(101,1,2,2),(101,2,1,5),(102,3,1,8),(103,1,5,2),(105,2,2,5);

These temporary tables keep the exercise self-contained, but they omit foreign-key enforcement. Real persistent tables should enforce those relationships where the design permits. Do not copy the simplified schema as a complete production sales model.

Practice Queries That Filter Before Joining

Query 1 asks for catalog products priced at least five dollars. Expect Pad and Mug, ordered by price and identifier. Filtering a catalog price answers a different question from filtering historical order-line prices.

SELECT ProductId, ProductName, CatalogPrice
FROM #Products
WHERE CatalogPrice >= 5
ORDER BY CatalogPrice, ProductId;

Query 2 finds orders on January 3. Expect orders 102 and 103. The equality predicate works because OrderDate is a date column; a timestamp column would need an appropriate half-open daily range.

SELECT OrderId, CustomerId, OrderDate
FROM #Orders
WHERE OrderDate = '20250103'
ORDER BY OrderId;

Query 3 finds lines containing at least two units. Expect the Pen lines from orders 101 and 103, plus the Pad line from 105. Returning line keys keeps separate purchases visible even when they share a product.

SELECT OrderId, ProductId, Quantity
FROM #Lines
WHERE Quantity >= 2
ORDER BY OrderId, ProductId;

Query 4 searches names beginning with A. Expect Ada from this dataset. LIKE follows the column's collation rules. Include case and accent behavior in your expectations when testing other strings.

SELECT CustomerId, CustomerName
FROM #Customers
WHERE CustomerName LIKE 'A%'
ORDER BY CustomerId;

Join at the Right Grain

Query 5 attaches customer names to orders. Expect four order rows, with Ada appearing twice. That repetition is correct because the result describes orders rather than one summary row per customer.

SELECT o.OrderId, c.CustomerName, o.OrderDate
FROM #Orders AS o
JOIN #Customers AS c ON c.CustomerId = o.CustomerId
ORDER BY o.OrderId;

Query 6 attaches product names to lines and calculates each line's value. Expect five line rows. Order 101 remains two rows because it contains two different products, which matters before calculating order totals.

SELECT l.OrderId, p.ProductName, l.Quantity,
       l.Quantity * l.UnitPrice AS LineValue
FROM #Lines AS l
JOIN #Products AS p ON p.ProductId = l.ProductId
ORDER BY l.OrderId, l.ProductId;

Query 7 groups line values by order. Expect totals of nine, eight, ten, and ten for orders 101, 102, 103, and 105. Those totals follow directly from the sample quantities and selling prices.

SELECT OrderId, SUM(Quantity * UnitPrice) AS OrderValue
FROM #Lines
GROUP BY OrderId
ORDER BY OrderId;

Query 8 counts orders for every customer, including customers without orders. Expect counts of two, one, one, and zero. COUNT of the matched OrderId avoids counting Drew's unmatched outer-join placeholder as a real order.

SELECT c.CustomerId, c.CustomerName, COUNT(o.OrderId) AS OrderTotal
FROM #Customers AS c
LEFT JOIN #Orders AS o ON o.CustomerId = c.CustomerId
GROUP BY c.CustomerId, c.CustomerName
ORDER BY c.CustomerId;

Aggregate Without Losing Missing Relationships

Query 9 calculates spending for every customer. Expect Ada's total to be seventeen, Ben's and Casey's ten each, and Drew's zero. Aggregating lines to orders first makes the intermediate grain clear.

;WITH Totals AS
(
    SELECT OrderId, SUM(Quantity * UnitPrice) AS OrderValue
    FROM #Lines GROUP BY OrderId
)
SELECT c.CustomerName, COALESCE(SUM(t.OrderValue),0) AS CustomerValue
FROM #Customers AS c
LEFT JOIN #Orders AS o ON o.CustomerId = c.CustomerId
LEFT JOIN Totals AS t ON t.OrderId = o.OrderId
GROUP BY c.CustomerId, c.CustomerName
ORDER BY c.CustomerId;

Query 10 finds products never purchased. Expect Eraser only. NOT EXISTS tests whether a related line exists without multiplying catalog rows. It also avoids nullable-list complications associated with NOT IN.

SELECT p.ProductId, p.ProductName
FROM #Products AS p
WHERE NOT EXISTS
(SELECT 1 FROM #Lines AS l WHERE l.ProductId = p.ProductId)
ORDER BY p.ProductId;

Query 11 finds customers without orders. Expect Drew only. This applies the same absence pattern to another relationship. Recognize the reusable query idea rather than memorizing a particular table name.

SELECT c.CustomerId, c.CustomerName
FROM #Customers AS c
WHERE NOT EXISTS
(SELECT 1 FROM #Orders AS o WHERE o.CustomerId = c.CustomerId)
ORDER BY c.CustomerId;

Query 12 selects customers with at least two orders. Expect Ada. HAVING filters the grouped count, while WHERE would filter individual order rows before the groups and their counts were formed.

SELECT c.CustomerId, c.CustomerName, COUNT(*) AS OrderTotal
FROM #Customers AS c
JOIN #Orders AS o ON o.CustomerId = c.CustomerId
GROUP BY c.CustomerId, c.CustomerName
HAVING COUNT(*) >= 2
ORDER BY c.CustomerId;

Make Ranking and Cumulative Totals Deterministic

Query 13 finds the two products with the largest purchased quantities. Expect Pen with seven units and Pad with three. ProductId breaks quantity ties so a TOP query has a stable selection rule.

SELECT TOP (2) p.ProductName, SUM(l.Quantity) AS UnitsSold
FROM #Products AS p
JOIN #Lines AS l ON l.ProductId = p.ProductId
GROUP BY p.ProductId, p.ProductName
ORDER BY UnitsSold DESC, p.ProductId;

Query 14 selects exactly two highest-value orders. Expect 103 and 105, both valued at ten. The identifier resolves the tie; TOP WITH TIES would express a different requirement if additional orders shared that value.

SELECT TOP (2) OrderId, SUM(Quantity * UnitPrice) AS OrderValue
FROM #Lines
GROUP BY OrderId
ORDER BY OrderValue DESC, OrderId;

Query 15 gives equal order values the same dense rank. Expect the two ten-dollar orders at rank one, nine at rank two, and eight at rank three. The ranking expression intentionally leaves the identifier outside its tie definition.

;WITH Totals AS
(
    SELECT OrderId, SUM(Quantity * UnitPrice) AS OrderValue
    FROM #Lines GROUP BY OrderId
)
SELECT OrderId, OrderValue,
       DENSE_RANK() OVER (ORDER BY OrderValue DESC) AS ValueRank
FROM Totals
ORDER BY ValueRank, OrderId;

Query 16 calculates cumulative sales in date and order sequence. Expect running values of nine, seventeen, twenty-seven, and thirty-seven. The explicit ROWS frame accumulates individual orders rather than treating same-date orders as one peer group.

;WITH Totals AS
(
    SELECT OrderId, SUM(Quantity * UnitPrice) AS OrderValue
    FROM #Lines GROUP BY OrderId
)
SELECT o.OrderId, o.OrderDate,
       SUM(t.OrderValue) OVER
       (ORDER BY o.OrderDate, o.OrderId ROWS UNBOUNDED PRECEDING) AS RunningValue
FROM #Orders AS o
JOIN Totals AS t ON t.OrderId = o.OrderId
ORDER BY o.OrderDate, o.OrderId;

Practice Queries for Previous Rows and Gaps

Query 17 compares each order value with the preceding order in the chosen sequence. Expect NULL for the first difference, then minus one, two, and zero. A previous row is defined by ordering, not physical storage position.

;WITH Totals AS
(
    SELECT OrderId, SUM(Quantity * UnitPrice) AS OrderValue
    FROM #Lines GROUP BY OrderId
)
SELECT o.OrderId, t.OrderValue,
       t.OrderValue - LAG(t.OrderValue) OVER
       (ORDER BY o.OrderDate, o.OrderId) AS ChangeFromPrevious
FROM #Orders AS o
JOIN Totals AS t ON t.OrderId = o.OrderId
ORDER BY o.OrderDate, o.OrderId;

Query 18 calculates days since each customer's preceding order. Expect two days for Ada's second order and NULL for each first order. Partitioning by CustomerId prevents one customer's purchase from becoming another customer's previous event.

SELECT CustomerId, OrderId, OrderDate,
       DATEDIFF(day, LAG(OrderDate) OVER
       (PARTITION BY CustomerId ORDER BY OrderDate, OrderId), OrderDate) AS DaysSincePrevious
FROM #Orders
ORDER BY CustomerId, OrderDate, OrderId;

Query 19 returns the first and last missing identifier for each internal gap. Expect 104 at both ends, between 103 and 105. A longer gap comes back as one range, not as a list of every value. Identifier gaps alone do not prove lost business records.

;WITH PreviousIds AS
(
    SELECT OrderId, LAG(OrderId) OVER (ORDER BY OrderId) AS PreviousId
    FROM #Orders
)
SELECT PreviousId + 1 AS FirstMissingId, OrderId - 1 AS LastMissingId
FROM PreviousIds
WHERE OrderId > PreviousId + 1
ORDER BY FirstMissingId;

Query 20 lists calendar dates without orders between January 1 and January 6. Expect January 2, 4, and 5. A generated calendar makes missing dates visible because the order table cannot supply rows that do not exist.

;WITH Calendar AS
(
    SELECT DATEADD(day,n,CONVERT(date,'20250101')) AS CalendarDate
    FROM (VALUES (0),(1),(2),(3),(4),(5)) AS d(n)
)
SELECT c.CalendarDate
FROM Calendar AS c
WHERE NOT EXISTS
(SELECT 1 FROM #Orders AS o WHERE o.OrderDate = c.CalendarDate)
ORDER BY c.CalendarDate;

I change one input after these practice queries work as expected. I then predict which answers must change and which must remain unchanged. Adding a duplicate join match is particularly educational; SQL does not apologize before multiplying your totals.

Which answer changes if another ten-dollar order is added on January 3? Explain both the TOP and ranking behavior before running it. Repeating practice queries with one intentional change builds reasoning that survives a different schema.

Related reading on this blog: Eleven SQL Server Interview Questions That Look Far Too Easy and SQL SERVER Challenge: Cumulative Total Calculation.

Query practice is not memorizing syntax, it is predicting the meaning of returned rows.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.