Skip to content

Home / 001_Getting_Started_with_SQL_Server / 001.004.SQL_Server_Sample_Database

001.004. SQL Server Sample Database

1. Introduction

This tutorial introduces the BikeStores sample database for SQL Server. It includes two schemas:

  • sales
  • production

These schemas contain a total of nine tables.

2. Database Diagram

SQL-Server-Sample-Database

The database is logically separated into sales-related and production-related data.

3. Database Tables

3.1 sales.stores

Stores information about each retail store including contact details and address.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CREATE TABLE sales.stores (
 store_id INT IDENTITY (1, 1) PRIMARY KEY,
 store_name VARCHAR (255) NOT NULL,
 phone VARCHAR (25),
 email VARCHAR (255),
 street VARCHAR (255),
 city VARCHAR (255),
 state VARCHAR (10),
 zip_code VARCHAR (5)
);

3.2 sales.staffs

Contains information about store staff including their role, manager relationships, and store association.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
CREATE TABLE sales.staffs (
 staff_id INT IDENTITY (1, 1) PRIMARY KEY,
 first_name VARCHAR (50) NOT NULL,
 last_name VARCHAR (50) NOT NULL,
 email VARCHAR (255) NOT NULL UNIQUE,
 phone VARCHAR (25),
 active TINYINT NOT NULL,
 store_id INT NOT NULL,
 manager_id INT,
 FOREIGN KEY (store_id) REFERENCES sales.stores (store_id) ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY (manager_id) REFERENCES sales.staffs (staff_id) ON DELETE NO ACTION ON UPDATE NO ACTION
);

3.3 sales.customers

Stores personal and contact details for customers.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
CREATE TABLE sales.customers (
 customer_id INT IDENTITY (1, 1) PRIMARY KEY,
 first_name VARCHAR (255) NOT NULL,
 last_name VARCHAR (255) NOT NULL,
 phone VARCHAR (25),
 email VARCHAR (255) NOT NULL,
 street VARCHAR (255),
 city VARCHAR (50),
 state VARCHAR (25),
 zip_code VARCHAR (5)
);

3.4 sales.orders

Represents the sales order header, including customer, store, staff, and order statuses.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
CREATE TABLE sales.orders (
 order_id INT IDENTITY (1, 1) PRIMARY KEY,
 customer_id INT,
 order_status TINYINT NOT NULL,  -- 1 = Pending; 2 = Processing; 3 = Rejected; 4 = Completed
 order_date DATE NOT NULL,
 required_date DATE NOT NULL,
 shipped_date DATE,
 store_id INT NOT NULL,
 staff_id INT NOT NULL,
 FOREIGN KEY (customer_id) REFERENCES sales.customers (customer_id) ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY (store_id) REFERENCES sales.stores (store_id) ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY (staff_id) REFERENCES sales.staffs (staff_id) ON DELETE NO ACTION ON UPDATE NO ACTION
);

3.5 sales.order_items

Details each line item within a sales order, including product, quantity, and pricing.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
CREATE TABLE sales.order_items (
 order_id INT,
 item_id INT,
 product_id INT NOT NULL,
 quantity INT NOT NULL,
 list_price DECIMAL (10, 2) NOT NULL,
 discount DECIMAL (4, 2) NOT NULL DEFAULT 0,
 PRIMARY KEY (order_id, item_id),
 FOREIGN KEY (order_id) REFERENCES sales.orders (order_id) ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY (product_id) REFERENCES production.products (product_id) ON DELETE CASCADE ON UPDATE CASCADE
);

3.6 production.categories

Stores product category types such as electric bikes, comfort bikes, etc.

1
2
3
4
CREATE TABLE production.categories (
 category_id INT IDENTITY (1, 1) PRIMARY KEY,
 category_name VARCHAR (255) NOT NULL
);

3.7 production.brands

Stores information about bicycle brands.

1
2
3
4
CREATE TABLE production.brands (
 brand_id INT IDENTITY (1, 1) PRIMARY KEY,
 brand_name VARCHAR (255) NOT NULL
);

3.8 production.products

Includes details for each product such as name, model year, brand, and category.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CREATE TABLE production.products (
 product_id INT IDENTITY (1, 1) PRIMARY KEY,
 product_name VARCHAR (255) NOT NULL,
 brand_id INT NOT NULL,
 category_id INT NOT NULL,
 model_year SMALLINT NOT NULL,
 list_price DECIMAL (10, 2) NOT NULL,
 FOREIGN KEY (category_id) REFERENCES production.categories (category_id) ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY (brand_id) REFERENCES production.brands (brand_id) ON DELETE CASCADE ON UPDATE CASCADE
);

3.9 production.stocks

Keeps track of product inventory at each store.

1
2
3
4
5
6
7
8
CREATE TABLE production.stocks (
 store_id INT,
 product_id INT,
 quantity INT,
 PRIMARY KEY (store_id, product_id),
 FOREIGN KEY (store_id) REFERENCES sales.stores (store_id) ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY (product_id) REFERENCES production.products (product_id) ON DELETE CASCADE ON UPDATE CASCADE
);

4. Sample Database Script

You can download the full SQL script to install the sample database:

SQL-Server-Sample-Database

5. Conclusion

You are now familiar with the BikeStores sample database structure and its tables. You can use this database to practice and explore SQL Server queries and data relationships.