Northwind Traders: Orders and Line Items
Order lines with quantities, prices and discounts. Practice calculated columns, SUMIFS and lookups.
DownloadStart here
Lessons and reference
Look things up
Start here
SQL
Power Query
Data modeling
Power BI and DAX
Finance playbooks
Run queries
Build and check
A small trading company that buys from suppliers and sells to customers: orders, order details, products, shippers, employees and purchase orders.
Write each as a query in the SQL editor, or ask an AI assistant to walk you through it.
Server learnsql.cpetoday.com,1433, database northwind, in Excel or Power BI (Import mode) or
DBeaver. In queries use the schema-qualified name shown for each table (for example northwind.Customers).
Download the Excel connection (.odc)
How to use it: download the file and open it. Excel opens it as a connection to northwind
and asks for the SQL Server username and password from your CPE Today course.
The file holds no password. The first table is selected by default, and you can pick others from the Data window.
Setup details for other tools are on Connect.
Click a table to preview it in the Playground.
Order lines with quantities, prices and discounts. Practice calculated columns, SUMIFS and lookups.
DownloadThe Northwind orders data with realistic problems planted in it: blanks, stray spaces, numbers stored as text and duplicate rows. Find and fix them.
DownloadWhat each column means, its type and keys, and for codes and statuses the values seen in this copy. 7 of 16 tables are described so far. Read it as Markdown.
Click a table in the diagram above to jump to it.
Customers, with business contact details.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
ID |
int | PK | Unique customer ID. | |
Company |
nvarchar(255) | Company name. | ||
Last Name |
nvarchar(50) | Contact's last name. | ||
First Name |
nvarchar(50) | Contact's first name. | ||
E-mail Address |
nvarchar(50) | Not described yet | ||
Job Title |
nvarchar(50) | Contact's job title. | Purchasing Manager 13 Owner 6 Purchasing Representative 6 Accounting Assistant 2 Accounting Manager 1 Purchasing Assistant 1 | |
Business Phone |
nvarchar(25) | Not described yet | ||
Home Phone |
nvarchar(25) | Not described yet | ||
Mobile Phone |
nvarchar(25) | Not described yet | ||
Fax Number |
nvarchar(25) | Not described yet | ||
Address |
nvarchar | Not described yet | ||
City |
nvarchar(50) | City. | ||
State/Province |
nvarchar(50) | Not described yet | ||
ZIP/Postal Code |
nvarchar(15) | Not described yet | ||
Country/Region |
nvarchar(50) | Country or region. | ||
Web Page |
nvarchar | Not described yet | ||
Notes |
nvarchar | Not described yet | ||
Attachments |
nvarchar | Not described yet |
Staff members, with job title and contact details.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
ID |
int | PK | Unique employee ID. | 1 1 2 1 3 1 4 1 5 1 6 1 7 1 8 1 9 1 |
Company |
nvarchar(50) | Not described yet | ||
Last Name |
nvarchar(50) | Last name. | Cencini 1 Freehafer 1 Giussani 1 Hellung-Larsen 1 Kotas 1 Neipper 1 Sergienko 1 Thorpe 1 Zare 1 | |
First Name |
nvarchar(50) | First name. | Andrew 1 Anne 1 Jan 1 Laura 1 Mariya 1 Michael 1 Nancy 1 Robert 1 Steven 1 | |
E-mail Address |
nvarchar(50) | Not described yet | ||
Job Title |
nvarchar(50) | Job title. | Sales Representative 6 Sales Coordinator 1 Sales Manager 1 Vice President, Sales 1 | |
Business Phone |
nvarchar(25) | Not described yet | ||
Home Phone |
nvarchar(25) | Not described yet | ||
Mobile Phone |
nvarchar(25) | Not described yet | ||
Fax Number |
nvarchar(25) | Not described yet | ||
Address |
nvarchar | Not described yet | ||
City |
nvarchar(50) | City. | Seattle 4 Redmond 3 Bellevue 1 Kirkland 1 | |
State/Province |
nvarchar(50) | Not described yet | ||
ZIP/Postal Code |
nvarchar(15) | Not described yet | ||
Country/Region |
nvarchar(50) | Country or region. | ||
Web Page |
nvarchar | Not described yet | ||
Notes |
nvarchar | Not described yet | ||
Attachments |
nvarchar | Not described yet |
An invoice for each order: the due date, tax, shipping and the amount due.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
Order ID |
int | PK FK to Orders | Order invoiced (Orders). | |
Invoice Date |
datetime2 | Date invoiced. | 2026-08-10T00:00:00 23 2026-07-30T00:00:00 9 2026-07-28T00:00:00 2 2026-08-09T00:00:00 1 | |
Due Date |
datetime2 | Payment due date. | ||
Tax |
decimal | Tax on the invoice. | ||
Shipping |
decimal | Shipping charged. | ||
Amount Due |
decimal | Total due. |
Customer orders: who ordered, when, where it ships, the shipping fee and the status.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
Order ID |
int | PK | Unique order ID. | |
Employee |
nvarchar(255) | Employee who took the order (Employees.ID). | Nancy Freehafer 12 Anne Hellung-Larsen 10 Mariya Sergienko 8 Jan Kotas 6 Andrew Cencini 4 Michael Neipper 4 Laura Giussani 2 Robert Zare 2 | |
Customer |
nvarchar(255) | FK to Customers | Customer who placed the order (Customers.ID). | |
Order Date |
datetime2 | Date the order was placed. | ||
Shipped Date |
datetime2 | Date it shipped (blank if not yet shipped). | ||
Shipper Via |
nvarchar(50) | Shipping company (Shippers.ID). | ||
Ship Name |
nvarchar(50) | Not described yet | ||
Ship Address |
nvarchar | Not described yet | ||
Ship City |
nvarchar(50) | Not described yet | ||
Ship State/Province |
nvarchar(50) | Not described yet | ||
Ship ZIP/Postal Code |
nvarchar(50) | Not described yet | ||
Ship Country/Region |
nvarchar(50) | Country the order ships to. | ||
Shipping Fee |
decimal | Shipping charge. | ||
Taxes |
decimal | Tax on the order. | ||
Payment Type |
nvarchar(50) | How the customer paid (see the values). | Check 18 Credit Card 16 10 Cash 4 | |
Paid Date |
datetime2 | Date the order was paid. | ||
Notes |
nvarchar | Not described yet | ||
Tax Rate |
float | Not described yet | ||
Tax Status |
tinyint | FK to Orders Tax Status | Whether the order is taxed (see the values). | |
Status ID |
nvarchar(50) | FK to Orders Status | Order status code (see the values). | Closed 31 New 16 Shipped 1 |
Ship Via |
nvarchar(50) | FK to Shippers | Not described yet |
Products the company sells, with cost, list price, reorder level and category.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
Supplier IDs |
nvarchar | Supplier (Suppliers.ID). | Supplier F 11 Supplier A 9 Supplier J 5 Supplier B, Supplier F 4 Supplier G 4 Supplier B 3 Supplier H 3 Supplier D 2 Supplier C 1 Supplier C, Supplier D 1 Supplier E 1 Supplier I 1 | |
ID |
int | PK | Unique product ID. | |
Product Code |
nvarchar(25) | Short product code. | ||
Product Name |
nvarchar(255) | Product name. | ||
Description |
nvarchar | Not described yet | ||
Standard Cost |
decimal | Cost to the company. | ||
List Price |
decimal | Price to customers. | ||
Reorder Level |
decimal | Stock level at which to reorder. | 10.0 21 25.0 5 30.0 5 20.0 4 5.0 3 100.0 3 15.0 2 50.0 2 | |
Target Level |
decimal | Stock level to restock up to. | 40.0 19 100.0 6 20.0 5 200.0 4 50.0 3 60.0 2 80.0 2 120.0 2 75.0 1 125.0 1 | |
Quantity Per Unit |
nvarchar(255) | Packaging, such as 24 bottles. | ||
Discontinued |
nvarchar(255) | True if the product is no longer sold. | ||
Minimum Reorder Quantity |
decimal | Not described yet | ||
Category |
nvarchar(50) | Product category (see the values). | ||
Attachments |
nvarchar | Not described yet |
Shipping companies used for orders.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
ID |
int | PK | Unique shipper ID. | 1 1 2 1 3 1 |
Company |
nvarchar(50) | Shipping company name. | Shipping Company A 1 Shipping Company B 1 Shipping Company C 1 | |
Last Name |
nvarchar(50) | Not described yet | ||
First Name |
nvarchar(50) | Not described yet | ||
E-mail Address |
nvarchar(50) | Not described yet | ||
Job Title |
nvarchar(50) | Not described yet | ||
Business Phone |
nvarchar(25) | Business phone. | ||
Home Phone |
nvarchar(25) | Not described yet | ||
Mobile Phone |
nvarchar(25) | Not described yet | ||
Fax Number |
nvarchar(25) | Not described yet | ||
Address |
nvarchar | Not described yet | ||
City |
nvarchar(50) | Not described yet | ||
State/Province |
nvarchar(50) | Not described yet | ||
ZIP/Postal Code |
nvarchar(15) | Not described yet | ||
Country/Region |
nvarchar(50) | Not described yet | ||
Web Page |
nvarchar | Not described yet | ||
Notes |
nvarchar | Not described yet | ||
Attachments |
nvarchar | Not described yet |
Companies that supply products.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
ID |
int | PK | Unique supplier ID. | 1 1 2 1 3 1 4 1 5 1 6 1 7 1 8 1 9 1 10 1 |
Company |
nvarchar(50) | Supplier company. | Supplier A 1 Supplier B 1 Supplier C 1 Supplier D 1 Supplier E 1 Supplier F 1 Supplier G 1 Supplier H 1 Supplier I 1 Supplier J 1 | |
Last Name |
nvarchar(50) | Not described yet | ||
First Name |
nvarchar(50) | Not described yet | ||
E-mail Address |
nvarchar(50) | Not described yet | ||
Job Title |
nvarchar(50) | Not described yet | ||
Business Phone |
nvarchar(25) | Not described yet | ||
Home Phone |
nvarchar(25) | Not described yet | ||
Mobile Phone |
nvarchar(25) | Not described yet | ||
Fax Number |
nvarchar(25) | Not described yet | ||
Address |
nvarchar | Not described yet | ||
City |
nvarchar(50) | City. | ||
State/Province |
nvarchar(50) | Not described yet | ||
ZIP/Postal Code |
nvarchar(15) | Not described yet | ||
Country/Region |
nvarchar(50) | Country or region. | ||
Web Page |
nvarchar | Not described yet | ||
Notes |
nvarchar | Not described yet | ||
Attachments |
nvarchar | Not described yet |