Classic Models: Sales Order Lines
Every order line from a model-car wholesaler: customer, country, product line, quantity and price. Build a PivotTable and answer four questions.
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
Customers, sales reps, offices, orders, order lines and payments for a wholesaler of scale model cars, 2003 to 2005. Clean keys make it a good first model.
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 classicmodels, in Excel or Power BI (Import mode) or
DBeaver. In queries use the schema-qualified name shown for each table (for example classicmodels.customers).
Download the Excel connection (.odc)
How to use it: download the file and open it. Excel opens it as a connection to classicmodels
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.
Every order line from a model-car wholesaler: customer, country, product line, quantity and price. Build a PivotTable and answer four questions.
DownloadThe modeling guide has the table-by-table recipe for this database, and the Power BI projects use Contoso.
What each column means, its type and keys, and for codes and statuses the values seen in this copy. 8 of 9 tables are described so far. Read it as Markdown.
Click a table in the diagram above to jump to it.
Customers of the model-car company: contact, address and credit limit, with the sales rep who looks after them.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
customerNumber |
int | PK | Unique customer number. | |
customerName |
varchar(50) | Company name. | ||
contactLastName |
varchar(50) | Contact person's last name. | ||
contactFirstName |
varchar(50) | Contact person's first name. | ||
phone |
varchar(50) | Phone number. | ||
addressLine1 |
varchar(50) | Not described yet | ||
addressLine2 |
varchar(50) | Not described yet | ||
city |
varchar(50) | City. | ||
state |
varchar(50) | Not described yet | ||
postalCode |
varchar(15) | Not described yet | ||
country |
varchar(50) | Country. | ||
salesRepEmployeeNumber |
int | FK to employees | Sales rep for the account (employees.employeeNumber). | |
creditLimit |
decimal | Maximum credit allowed. |
Staff, with job title, office and manager.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
employeeNumber |
int | PK | Unique employee number. | |
lastName |
varchar(50) | Last name. | ||
firstName |
varchar(50) | First name. | ||
extension |
varchar(10) | Not described yet | ||
email |
varchar(100) | Email. | ||
officeCode |
varchar(10) | FK to offices | Office (offices). | 1 6 4 5 6 4 2 2 3 2 5 2 7 2 |
reportsTo |
int | FK to employees | Manager's employee number (employees). | 1102 6 1143 6 1056 4 1088 3 1002 2 (blank) 1 1621 1 |
jobTitle |
varchar(50) | Job title (for example Sales Rep). | Sales Rep 17 President 1 Sale Manager (EMEA) 1 Sales Manager (APAC) 1 Sales Manager (NA) 1 VP Marketing 1 VP Sales 1 |
Sales offices and their territories.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
officeCode |
varchar(10) | PK | Unique office code. | 1 1 2 1 3 1 4 1 5 1 6 1 7 1 |
city |
varchar(50) | City. | Boston 1 London 1 NYC 1 Paris 1 San Francisco 1 Sydney 1 Tokyo 1 | |
phone |
varchar(50) | Not described yet | ||
addressLine1 |
varchar(50) | Not described yet | ||
addressLine2 |
varchar(50) | Not described yet | ||
state |
varchar(50) | Not described yet | ||
country |
varchar(50) | Country. | USA 3 Australia 1 France 1 Japan 1 UK 1 | |
postalCode |
varchar(15) | Not described yet | ||
territory |
varchar(10) | Sales territory. | NA 3 EMEA 2 APAC 1 Japan 1 |
The products on each order: quantity and price per unit. Revenue is quantityOrdered times priceEach.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
orderNumber |
int | PK FK to orders | Order (orders). | |
productCode |
varchar(15) | PK FK to products | Product (products). | |
quantityOrdered |
int | Units ordered. | ||
priceEach |
decimal | Price per unit on this order. | ||
orderLineNumber |
smallint | Line number within the order. |
Sales orders: one row per order with its dates and status. Line items are in orderdetails.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
orderNumber |
int | PK | Unique order number. | |
orderDate |
date | Date the order was placed. | ||
requiredDate |
date | Date the customer needs the order by. | ||
shippedDate |
date | Date shipped (blank if not yet shipped). | ||
status |
varchar(15) | Order status (see the values). | Shipped 303 Cancelled 6 In Process 6 On Hold 4 Resolved 4 Disputed 3 | |
comments |
varchar | Notes on the order. | ||
customerNumber |
int | FK to customers | Customer who placed the order (customers). |
Payments customers have made, one row per check.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
customerNumber |
int | PK FK to customers | Customer who paid (customers). | |
checkNumber |
varchar(50) | PK | Check number. | |
paymentDate |
date | Date received. | ||
amount |
decimal | Amount paid. |
Product lines, such as Classic Cars or Trains.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
productLine |
varchar(50) | PK | Product line name. | Classic Cars 1 Motorcycles 1 Planes 1 Ships 1 Trains 1 Trucks and Buses 1 Vintage Cars 1 |
textDescription |
varchar(4000) | Short description. | Attention car enthusiasts: Make your wildest car ownership dreams come true. Whether you are looking for classic muscle cars, dream sports cars or movie-inspired miniatures, you will find great choices in this category. These replicas feature superb attention to detail and craftsmanship and offer features such as working steering system, opening forward compartment, opening rear trunk with removable spare wheel, 4-wheel independent spring suspension, and so on. The models range in size from 1:10 to 1:24 scale and include numerous limited edition and several out-of-production vehicles. All models include a certificate of authenticity from their manufacturers and come fully assembled and ready for display in the home or office. 1 Model trains are a rewarding hobby for enthusiasts of all ages. Whether you're looking for collectible wooden trains, electric streetcars or locomotives, you'll find a number of great choices for any budget within this category. The interactive aspect of trains makes toy trains perfect for young children. The wooden train sets are ideal for children under the age of 5. 1 Our motorcycles are state of the art replicas of classic as well as contemporary motorcycle legends such as Harley Davidson, Ducati and Vespa. Models contain stunning details such as official logos, rotating wheels, working kickstand, front suspension, gear-shift lever, footbrake lever, and drive chain. Materials used include diecast and plastic. The models range in size from 1:10 to 1:50 scale and include numerous limited edition and several out-of-production vehicles. All models come fully assembled and ready for display in the home or office. Most include a certificate of authenticity. 1 Our Vintage Car models realistically portray automobiles produced from the early 1900s through the 1940s. Materials used include Bakelite, diecast, plastic and wood. Most of the replicas are in the 1:18 and 1:24 scale sizes, which provide the optimum in detail and accuracy. Prices range from $30.00 up to $180.00 for some special limited edition replicas. All models include a certificate of authenticity from their manufacturers and come fully assembled and ready for display in the home or office. 1 The perfect holiday or anniversary gift for executives, clients, friends, and family. These handcrafted model ships are unique, stunning works of art that will be treasured for generations! They come fully assembled and ready for display in the home or office. We guarantee the highest quality, and best value. 1 The Truck and Bus models are realistic replicas of buses and specialized trucks produced from the early 1920s to present. The models range in size from 1:12 to 1:50 scale and include numerous limited edition and several out-of-production vehicles. Materials used include tin, diecast and plastic. All models include a certificate of authenticity from their manufacturers and are a perfect ornament for the home and office. 1 Unique, diecast airplane and helicopter replicas suitable for collections, as well as home, office or classroom decorations. Models contain stunning details such as official logos and insignias, rotating jet engines and propellers, retractable wheels, and so on. Most come fully assembled and with a certificate of authenticity from their manufacturers. 1 | |
htmlDescription |
varchar | Not described yet | ||
image |
varbinary | Not described yet |
The model-car catalog: name, scale, vendor, stock and prices.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
productCode |
varchar(15) | PK | Unique product code. | |
productName |
varchar(70) | Product name. | ||
productLine |
varchar(50) | FK to productlines | Product line (productlines). | Classic Cars 38 Vintage Cars 24 Motorcycles 13 Planes 12 Trucks and Buses 11 Ships 9 Trains 3 |
productScale |
varchar(10) | Scale, such as 1:18. | 1:18 42 1:24 27 1:700 10 1:12 9 1:32 8 1:10 6 1:50 4 1:72 4 | |
productVendor |
varchar(50) | Manufacturer of the model. | ||
productDescription |
varchar | Not described yet | ||
quantityInStock |
smallint | Units in stock. | ||
buyPrice |
decimal | What the company pays for the item. | ||
MSRP |
decimal | Suggested retail price. |