Welcome to the lab. You have learned the fundamentals DBAs must be fluent in:
SELECT filtering/sorting, aggregates, joins, subqueries, safe write patterns, and temp storage.
Now you will apply these skills to a real-world sample database: AdventureWorks.
This lab is designed to feel like the kind of reporting and investigation tasks a junior DBA
would be asked to do in a real company.
Important: This lab is read-only. We will not change AdventureWorks data.
Your goal is to produce accurate result sets, build disciplined query habits,
and create a reusable “report script pack” you can keep for your portfolio.
Lesson description: You will confirm AdventureWorks is installed and accessible,
learn how to explore schema safely, identify the correct tables and relationships,
and then build a set of job-ready report queries including sales summaries, top customers,
product performance, inventory checks, and data-quality validation queries. You will also stage intermediate
results in temp tables to demonstrate professional DBA scripting practices.
SELECT name FROM sys.databases WHERE name LIKE 'AdventureWorks%' ORDER BY name;
You might see names like: AdventureWorks2019, AdventureWorks2022, etc.
Choose one and set your context to it.
USE AdventureWorks2022; GO
If your database name is different, replace it accordingly.
DBAs often enter a system they didn’t design. Your first job is to map the schema without breaking anything.
SELECT name AS SchemaName FROM sys.schemas ORDER BY name;
In AdventureWorks you will commonly work with schemas like:
Person, Sales, Production, HumanResources.
Use keyword search to find tables for sales orders, customers, products, etc.
SELECT
s.name AS SchemaName,
t.name AS TableName
FROM sys.tables AS t
JOIN sys.schemas AS s
ON t.schema_id = s.schema_id
WHERE t.name LIKE '%Order%'
OR t.name LIKE '%Customer%'
OR t.name LIKE '%Product%'
ORDER BY s.name, t.name;
Example: Sales.SalesOrderHeader is a key table for many reports.
SELECT
c.COLUMN_NAME,
c.DATA_TYPE,
c.CHARACTER_MAXIMUM_LENGTH,
c.IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_SCHEMA = 'Sales'
AND c.TABLE_NAME = 'SalesOrderHeader'
ORDER BY c.ORDINAL_POSITION;
This query shows foreign key relationships for a specific table.
SELECT
fk.name AS ForeignKeyName,
OBJECT_SCHEMA_NAME(fk.parent_object_id) AS FromSchema,
OBJECT_NAME(fk.parent_object_id) AS FromTable,
c1.name AS FromColumn,
OBJECT_SCHEMA_NAME(fk.referenced_object_id) AS ToSchema,
OBJECT_NAME(fk.referenced_object_id) AS ToTable,
c2.name AS ToColumn
FROM sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fkc
ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns AS c1
ON fkc.parent_object_id = c1.object_id
AND fkc.parent_column_id = c1.column_id
JOIN sys.columns AS c2
ON fkc.referenced_object_id = c2.object_id
AND fkc.referenced_column_id = c2.column_id
WHERE OBJECT_SCHEMA_NAME(fk.parent_object_id) = 'Sales'
AND OBJECT_NAME(fk.parent_object_id) = 'SalesOrderHeader'
ORDER BY ForeignKeyName;
DBA habit: Always follow real PK/FK relationships when you build joins.
The following reports are intentionally chosen to practice the skills you learned:
filtering, sorting, grouping, joins, subqueries, temp tables, and correctness checks.
Run each query and review results. Then save them into a single file named something like:
AdventureWorks_ReportPack_Section3.sql.
This is a classic business question: how much revenue per day.
It teaches date filtering and grouping.
SELECT
CAST(soh.OrderDate AS date) AS OrderDate,
COUNT(*) AS OrderCount,
SUM(soh.TotalDue) AS TotalRevenue
FROM Sales.SalesOrderHeader AS soh
WHERE soh.OrderDate >= DATEADD(DAY, -30, CAST(SYSDATETIME() AS date))
GROUP BY CAST(soh.OrderDate AS date)
ORDER BY OrderDate DESC;
DBA check: confirm your date filter is correct and that order counts look realistic.
This report teaches multi-table joins and aggregation by customer.
SELECT TOP (20)
c.CustomerID,
COALESCE(p.FirstName + ' ' + p.LastName, 'Unknown Name') AS CustomerName,
COUNT(soh.SalesOrderID) AS Orders,
SUM(soh.TotalDue) AS TotalSpent
FROM Sales.Customer AS c
JOIN Sales.SalesOrderHeader AS soh
ON soh.CustomerID = c.CustomerID
LEFT JOIN Person.Person AS p
ON c.PersonID = p.BusinessEntityID
GROUP BY
c.CustomerID,
COALESCE(p.FirstName + ' ' + p.LastName, 'Unknown Name')
ORDER BY TotalSpent DESC;
DBA note: Some customers may be stores (not Person.Person). That is why COALESCE is used.
This is common in real reporting: grouping by organizational unit.
SELECT
st.TerritoryID,
st.Name AS TerritoryName,
COUNT(soh.SalesOrderID) AS Orders,
SUM(soh.TotalDue) AS TotalRevenue
FROM Sales.SalesTerritory AS st
JOIN Sales.SalesOrderHeader AS soh
ON soh.TerritoryID = st.TerritoryID
GROUP BY st.TerritoryID, st.Name
ORDER BY TotalRevenue DESC;
This report teaches one-to-many joins (OrderHeader → OrderDetail)
and how to aggregate correctly at the product level.
SELECT TOP (20)
p.ProductID,
p.Name AS ProductName,
SUM(sod.OrderQty) AS TotalQuantity,
SUM(sod.LineTotal) AS Revenue
FROM Sales.SalesOrderDetail AS sod
JOIN Production.Product AS p
ON sod.ProductID = p.ProductID
GROUP BY p.ProductID, p.Name
ORDER BY Revenue DESC;
DBA check: This is the exact scenario where row multiplication is correct (one order has many lines).
DBAs often get asked to find anomalies. This report filters on a computed ratio.
SELECT TOP (50)
soh.SalesOrderID,
soh.OrderDate,
soh.SubTotal,
soh.Freight,
soh.TotalDue,
CAST((soh.Freight / NULLIF(soh.SubTotal, 0)) * 100.0 AS DECIMAL(10,2)) AS FreightPctOfSubtotal
FROM Sales.SalesOrderHeader AS soh
WHERE soh.SubTotal > 0
AND (soh.Freight / NULLIF(soh.SubTotal, 0)) >= 0.15
ORDER BY FreightPctOfSubtotal DESC;
DBA safety detail: NULLIF prevents divide-by-zero.
This is a classic “missing relationship” report used for marketing, cleanup, and reconciliation.
SELECT
c.CustomerID,
COALESCE(p.FirstName + ' ' + p.LastName, 'Unknown Name') AS CustomerName
FROM Sales.Customer AS c
LEFT JOIN Sales.SalesOrderHeader AS soh
ON soh.CustomerID = c.CustomerID
LEFT JOIN Person.Person AS p
ON c.PersonID = p.BusinessEntityID
WHERE soh.SalesOrderID IS NULL
ORDER BY c.CustomerID;
DBA note: You can also write this using NOT EXISTS. Both patterns are acceptable.
DBAs often support operations teams. This report gives a simple view of stock by product and location.
SELECT TOP (100)
p.ProductID,
p.Name AS ProductName,
i.LocationID,
i.Shelf,
i.Bin,
i.Quantity
FROM Production.ProductInventory AS i
JOIN Production.Product AS p
ON i.ProductID = p.ProductID
ORDER BY i.Quantity DESC, p.ProductID ASC;
This final report demonstrates a professional pattern: stage intermediate results first, then report from them.
This is useful when you do multi-step logic or want repeatable outputs without re-running expensive joins.
This script stages the last 30 days of orders, then produces a territory revenue report from the staged set.
IF OBJECT_ID('tempdb..#RecentOrders') IS NOT NULL
DROP TABLE #RecentOrders;
CREATE TABLE #RecentOrders
(
SalesOrderID INT NOT NULL PRIMARY KEY,
TerritoryID INT NULL,
OrderDate DATE NOT NULL,
TotalDue MONEY NOT NULL
);
INSERT INTO #RecentOrders (SalesOrderID, TerritoryID, OrderDate, TotalDue)
SELECT
soh.SalesOrderID,
soh.TerritoryID,
CAST(soh.OrderDate AS date),
soh.TotalDue
FROM Sales.SalesOrderHeader AS soh
WHERE soh.OrderDate >= DATEADD(DAY, -30, CAST(SYSDATETIME() AS date));
-- Index to speed grouping by TerritoryID (often helpful)
CREATE INDEX IX_RecentOrders_TerritoryID ON #RecentOrders (TerritoryID);
SELECT
ro.TerritoryID,
COALESCE(st.Name, 'Unknown Territory') AS TerritoryName,
COUNT(ro.SalesOrderID) AS Orders,
SUM(ro.TotalDue) AS TotalRevenue
FROM #RecentOrders AS ro
LEFT JOIN Sales.SalesTerritory AS st
ON ro.TerritoryID = st.TerritoryID
GROUP BY ro.TerritoryID, COALESCE(st.Name, 'Unknown Territory')
ORDER BY TotalRevenue DESC;
DROP TABLE #RecentOrders;
DBA skill demonstrated: controlled staging, safe re-runs, indexing, and clean cleanup.
For each report query, answer these questions:
Create a single .sql file containing:
This file becomes evidence that you can work like a DBA: explore a schema, build correct joins,
summarize data, and stage results professionally.
Next, you will level up into operational T-SQL:
CTEs, window functions, CASE logic, defensive TRY/CATCH scripting, and transaction patterns used in production.
This is where you start writing scripts that look like real DBA tooling.
Not a member yet? Register now
Are you a member? Login now