Downloads
Model-car wholesaler

Classic Models

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.

classicmodelsSQL ServerMariaDB 9 tables
orderdetailsorderscustomersproductspayments

Good for

Aggregates and GROUP BYPivotTablesA first DAX model with two fact tables

Questions to try

  • What is the revenue for each product line?
  • Which customers have paid the most?
  • Which sales rep manages the most customers?

Write each as a query in the SQL editor, or ask an AI assistant to walk you through it.

Connect to 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.

Tables (9)

Click a table to preview it in the Playground.

Practice files for this database

Beginner141 KB

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.

Download

Model it for DAX

The modeling guide has the table-by-table recipe for this database, and the Power BI projects use Contoso.

Data dictionary

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 one row per customer 13 columns

Customers of the model-car company: contact, address and credit limit, with the sales rep who looks after them.

ColumnTypeKeyMeaningValues 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.
employees one row per employee 8 columns

Staff, with job title, office and manager.

ColumnTypeKeyMeaningValues 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
offices one row per office 9 columns

Sales offices and their territories.

ColumnTypeKeyMeaningValues 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
orderdetails one row per product per order 5 columns

The products on each order: quantity and price per unit. Revenue is quantityOrdered times priceEach.

ColumnTypeKeyMeaningValues 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.
orders one row per order 7 columns

Sales orders: one row per order with its dates and status. Line items are in orderdetails.

ColumnTypeKeyMeaningValues 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 one row per payment 4 columns

Payments customers have made, one row per check.

ColumnTypeKeyMeaningValues 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.
productlines one row per product line 4 columns

Product lines, such as Classic Cars or Trains.

ColumnTypeKeyMeaningValues 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
products one row per product 9 columns

The model-car catalog: name, scale, vendor, stock and prices.

ColumnTypeKeyMeaningValues 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.
1 more tables (no notes yet)

← All databases