{"id":83,"date":"2018-04-01T22:43:11","date_gmt":"2018-04-01T15:43:11","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=83"},"modified":"2021-07-26T07:45:57","modified_gmt":"2021-07-26T00:45:57","slug":"sql-server-sample-database","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/getting-started\/sql-server-sample-database\/","title":{"rendered":"SQL Server Sample Database"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn about the SQL Server sample database called BikeStores.<\/p>\n\n\n\n

The following illustrates the BikeStores database diagram:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

As you can see from the diagram, the BikeStores sample database has two schemas sales and production, and these schemas have nine tables.<\/p>\n\n\n\n

Database Tables #<\/a><\/h2>\n\n\n\n

Table sales.stores #<\/a><\/h3>\n\n\n\n

The  sales.stores<\/code> table includes the store’s information. Each store has a store name, contact information such as phone and email, and an address including street, city, state, and zip code.<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> sales.stores (\n\tstore_id INT<\/span> IDENTITY<\/span> (1<\/span>, 1<\/span>) PRIMARY KEY<\/span>,\n\tstore_name VARCHAR<\/span> (255<\/span>) NOT<\/span> NULL<\/span>,\n\tphone VARCHAR<\/span> (25<\/span>),\n\temail VARCHAR<\/span> (255<\/span>),\n\tstreet VARCHAR<\/span> (255<\/span>),\n\tcity VARCHAR<\/span> (255<\/span>),\n\tstate VARCHAR<\/span> (10<\/span>),\n\tzip_code VARCHAR<\/span> (5<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Table sales.staffs #<\/a><\/h3>\n\n\n\n
The  sales.staffs<\/code> table stores the essential information of staffs including first name, last name. It also contains the communication information such as email and phone.<\/p>\n\n\n\n
A staff works at a store specified by the value in the store_id<\/code> column. A store can have one or more staffs.<\/p>\n\n\n\n
A staff reports to a store manager specified by the value in the manager_id<\/code> column. If the value in the manager_id is null, then the staff is the top manager.<\/p>\n\n\n\n
If a staff no longer works for any stores, the value in the active column is set to zero.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> sales.staffs (\n\tstaff_id INT<\/span> IDENTITY<\/span> (1<\/span>, 1<\/span>) PRIMARY KEY<\/span>,\n\tfirst_name VARCHAR<\/span> (50<\/span>) NOT<\/span> NULL<\/span>,\n\tlast_name VARCHAR<\/span> (50<\/span>) NOT<\/span> NULL<\/span>,\n\temail VARCHAR<\/span> (255<\/span>) NOT<\/span> NULL<\/span> UNIQUE<\/span>,\n\tphone VARCHAR<\/span> (25<\/span>),\n\tactive tinyint<\/span> NOT<\/span> NULL<\/span>,\n\tstore_id INT<\/span> NOT<\/span> NULL<\/span>,\n\tmanager_id INT<\/span>,\n\tFOREIGN<\/span> KEY<\/span> (store_id) \n        REFERENCES<\/span> sales.stores (store_id) \n        ON<\/span> DELETE<\/span> CASCADE<\/span> ON<\/span> UPDATE<\/span> CASCADE<\/span>,\n\tFOREIGN<\/span> KEY<\/span> (manager_id) \n        REFERENCES<\/span> sales.staffs (staff_id) \n        ON<\/span> DELETE<\/span> NO<\/span> ACTION<\/span> ON<\/span> UPDATE<\/span> NO<\/span> ACTION<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Table production.categories #<\/a><\/h3>\n\n\n\n
The production.categories<\/code> table stores the bike’s categories such as children bicycles, comfort bicycles, and electric bikes.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> production.categories (\n\tcategory_id INT<\/span> IDENTITY<\/span> (1<\/span>, 1<\/span>) PRIMARY KEY<\/span>,\n\tcategory_name VARCHAR<\/span> (255<\/span>) NOT<\/span> NULL<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Table production.brands #<\/a><\/h3>\n\n\n\n
The  production.brands<\/code> table stores the brand’s information of bikes, for example, Electra, Haro, and Heller.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> production.brands (\n\tbrand_id INT<\/span> IDENTITY<\/span> (1<\/span>, 1<\/span>) PRIMARY KEY<\/span>,\n\tbrand_name VARCHAR<\/span> (255<\/span>) NOT<\/span> NULL<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Table production.products #<\/a><\/h3>\n\n\n\n
The production.products<\/code> table stores the product’s information such as name, brand, category, model year, and list price.<\/p>\n\n\n\n
Each product belongs to a brand specified by the brand_id<\/code> column. Hence, a brand may have zero or many products.<\/p>\n\n\n\n
Each product also belongs a category specified by the category_id<\/code> column. Also, each category may have zero or many products.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> production.products (\n\tproduct_id INT<\/span> IDENTITY<\/span> (1<\/span>, 1<\/span>) PRIMARY KEY<\/span>,\n\tproduct_name VARCHAR<\/span> (255<\/span>) NOT<\/span> NULL<\/span>,\n\tbrand_id INT<\/span> NOT<\/span> NULL<\/span>,\n\tcategory_id INT<\/span> NOT<\/span> NULL<\/span>,\n\tmodel_year SMALLINT<\/span> NOT<\/span> NULL<\/span>,\n\tlist_price DECIMAL<\/span> (10<\/span>, 2<\/span>) NOT<\/span> NULL<\/span>,\n\tFOREIGN<\/span> KEY<\/span> (category_id) \n        REFERENCES<\/span> production.categories (category_id) \n        ON<\/span> DELETE<\/span> CASCADE<\/span> ON<\/span> UPDATE<\/span> CASCADE<\/span>,\n\tFOREIGN<\/span> KEY<\/span> (brand_id) \n        REFERENCES<\/span> production.brands (brand_id) \n        ON<\/span> DELETE<\/span> CASCADE<\/span> ON<\/span> UPDATE<\/span> CASCADE<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Table sales.customers #<\/a><\/h3>\n\n\n\n
The  sales.customers<\/code> table stores customer’s information including first name, last name, phone, email, street, city, state and zip code.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> sales.customers (\n\tcustomer_id INT<\/span> IDENTITY<\/span> (1<\/span>, 1<\/span>) PRIMARY KEY<\/span>,\n\tfirst_name VARCHAR<\/span> (255<\/span>) NOT<\/span> NULL<\/span>,\n\tlast_name VARCHAR<\/span> (255<\/span>) NOT<\/span> NULL<\/span>,\n\tphone VARCHAR<\/span> (25<\/span>),\n\temail VARCHAR<\/span> (255<\/span>) NOT<\/span> NULL<\/span>,\n\tstreet VARCHAR<\/span> (255<\/span>),\n\tcity VARCHAR<\/span> (50<\/span>),\n\tstate VARCHAR<\/span> (25<\/span>),\n\tzip_code VARCHAR<\/span> (5<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Table sales.orders #<\/a><\/h3>\n\n\n\n
The sales.orders<\/code> table stores the sales order’s header information including customer, order status, order date, required date, shipped date.<\/p>\n\n\n\n
It also stores the information on where the sales transaction was created (store) and who created it (staff).<\/p>\n\n\n\n
Each sales order has a row in the sales_orders table. A sales order has one or many line items stored in the sales.order_items<\/code> table.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> sales.orders (\n\torder_id INT<\/span> IDENTITY<\/span> (1<\/span>, 1<\/span>) PRIMARY KEY<\/span>,\n\tcustomer_id INT<\/span>,\n\torder_status tinyint<\/span> NOT<\/span> NULL<\/span>,\n\t-- Order status: 1 = Pending; 2 = Processing; 3 = Rejected; 4 = Completed<\/span>\n\torder_date DATE<\/span> NOT<\/span> NULL<\/span>,\n\trequired_date DATE<\/span> NOT<\/span> NULL<\/span>,\n\tshipped_date DATE<\/span>,\n\tstore_id INT<\/span> NOT<\/span> NULL<\/span>,\n\tstaff_id INT<\/span> NOT<\/span> NULL<\/span>,\n\tFOREIGN<\/span> KEY<\/span> (customer_id) \n        REFERENCES<\/span> sales.customers (customer_id) \n        ON<\/span> DELETE<\/span> CASCADE<\/span> ON<\/span> UPDATE<\/span> CASCADE<\/span>,\n\tFOREIGN<\/span> KEY<\/span> (store_id) \n        REFERENCES<\/span> sales.stores (store_id) \n        ON<\/span> DELETE<\/span> CASCADE<\/span> ON<\/span> UPDATE<\/span> CASCADE<\/span>,\n\tFOREIGN<\/span> KEY<\/span> (staff_id) \n        REFERENCES<\/span> sales.staffs (staff_id) \n        ON<\/span> DELETE<\/span> NO<\/span> ACTION<\/span> ON<\/span> UPDATE<\/span> NO<\/span> ACTION<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Table sales.order_items #<\/a><\/h3>\n\n\n\n
The sales.order_items<\/code> table stores the line items of a sales order. Each line item belongs to a sales order specified by the order_id<\/code> column.<\/p>\n\n\n\n
A sales order line item includes product, order quantity, list price, and discount.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> sales.order_items(\n\torder_id INT<\/span>,\n\titem_id INT<\/span>,\n\tproduct_id INT<\/span> NOT<\/span> NULL<\/span>,\n\tquantity INT<\/span> NOT<\/span> NULL<\/span>,\n\tlist_price DECIMAL<\/span> (10<\/span>, 2<\/span>) NOT<\/span> NULL<\/span>,\n\tdiscount DECIMAL<\/span> (4<\/span>, 2<\/span>) NOT<\/span> NULL<\/span> DEFAULT<\/span> 0<\/span>,\n\tPRIMARY KEY<\/span> (order_id, item_id),\n\tFOREIGN<\/span> KEY<\/span> (order_id) \n        REFERENCES<\/span> sales.orders (order_id) \n        ON<\/span> DELETE<\/span> CASCADE<\/span> ON<\/span> UPDATE<\/span> CASCADE<\/span>,\n\tFOREIGN<\/span> KEY<\/span> (product_id) \n        REFERENCES<\/span> production.products (product_id) \n        ON<\/span> DELETE<\/span> CASCADE<\/span> ON<\/span> UPDATE<\/span> CASCADE<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Table production.stocks #<\/a><\/h3>\n\n\n\n
The production.stocks<\/code> table stores the inventory information i.e. the quantity of a particular product in a specific store.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> production.stocks (\n\tstore_id INT<\/span>,\n\tproduct_id INT<\/span>,\n\tquantity INT<\/span>,\n\tPRIMARY KEY<\/span> (store_id, product_id),\n\tFOREIGN<\/span> KEY<\/span> (store_id) \n        REFERENCES<\/span> sales.stores (store_id) \n        ON<\/span> DELETE<\/span> CASCADE<\/span> ON<\/span> UPDATE<\/span> CASCADE<\/span>,\n\tFOREIGN<\/span> KEY<\/span> (product_id) \n        REFERENCES<\/span> production.products (product_id) \n        ON<\/span> DELETE<\/span> CASCADE<\/span> ON<\/span> UPDATE<\/span> CASCADE<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Click the following link to download the sample database script:<\/p>\n\n\n\n
Now, you should be familiar with the BikeStores sample database and ready to load it into the SQL Server.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Download SQL Server Sample Database<\/a><\/p>\n\n\n\n