82|SQL – Bike Shop Available Inventory

[Andrew]

BYU Student Author: @Andrew
Reviewers: @Donovon, @Mike_Paulsin, @Parker_Sherwood
Estimated Time to Solve: 30 Minutes

We provide the solution to this challenge using:

• SQL – Azure Data Studio

Need a program? Click here.

This is an add-on to Challenge 69|SQL – Bike Shop Shipping Delays.

Overview
You are an accountant for a small bicycle company. Your company owns and operates three different locations. Recently, you received an email from the company’s COO asking for a table of all available inventory located in each store. She further clarified in her email, “The database already shows the inventory for each product and store; however I want a list showing available inventory (inventory amounts excluding sales made, but not shipped).”

Write a query to output a table showing each store, store name, product, product name, and available quantity. Order the table by product then store. Store this query within a view, so it can be easily retrievable.

Instructions
First, follow the instructions below to install Azure Data Studio and then load the database. If you have already installed Azure Data Studio and setup a local instance of Microsoft SQL Server, then skip to loading the database. If you have already loaded the database from Challenge 69|SQL – Bike Shop Shipping Delays, then skip to the next instructions.

Azure Data Studio Installation

1. Install Microsoft SQL Server (if not already installed) found here. Download the developer version (seen in the image below) and install package. When prompted select the basic installation package.
   

2. Install Azure Data Studio (if not already installed). Installation instructions can be found here.

3. Open Azure Data Studio and set up a new local server instance (if not already done):

   

   

Database Loading Instructions

1. Download the database setup file: Challenge82_Database.sql.
2. Open it in Azure Data Studio, make sure the selected server is “localhost”, and run the file. Running the file will create a database named BikeStores, then create tables and insert data into those tables.

For more information on the database structure, tables, and fields see SQLServerTutorial.net’s data dictionary here.

This database, ERD, and data dictionary are all owned by SQLServerTutorial.net and used within this challenge under their terms of use.

Second, review the entity-relationship diagram for the BikeStores database. The ERD will be useful in writing your queries.

Third, setup a new query (CTRL + N shortcut) to complete your data retrieval to build the table for the COO. Additional instructions on your deliverable:

1. Create a view in the production schema named ‘available_inventory’ that will contain the select query to display the available inventory for each store and product.
2. The ‘available_inventory’ view should output 5 columns
   • Store ID
   • Store Name
   • Product ID
   • Product Name
   • Quantity of available inventory ([current inventory]-[all orders that haven’t shipped yet])

Suggestions and Hints

First, focus on writing a select statement containing the total units sold of each product from each store for each order that has not shipped yet.

SELECT so.store_id, soi.product_id, SUM(soi.quantity)  AS q_sold 
   FROM sales.order_items soi JOIN sales.orders so ON soi.order_id = so.order_id  
   WHERE so.shipped_date IS NULL 
   GROUP BY so.store_id, soi.product_id 

Second, join the stocks table with the table you made in the previous step and subtract the total units of each product sold from the inventory in the stocks table. Make sure you include all items in the stocks table regardless of if they are in the table from the previous step (use a left join).

Third, create a view with your desired SELECT contained within.

Solution

Output Table

The output should have 939 rows. Here are the top 28 rows from the query:

SELECT * FROM production.available_inventory 
ORDER BY product_id, store_id; 

Solution Code

CREATE VIEW production.available_inventory 
AS 

SELECT 
    ps.store_id 
    , ss.store_name 
    , ps.product_id 
    , pp.product_name 
    , ps.quantity - ISNULL(t1.q_sold,0) AS quantity_available 
    FROM  
        production.stocks ps 
        JOIN production.products pp ON ps.product_id = pp.product_id 
        JOIN sales.stores ss ON ps.store_id = ss.store_id 
        LEFT JOIN ( SELECT so.store_id, soi.product_id, SUM(soi.quantity)  AS q_sold 
            FROM sales.order_items soi JOIN sales.orders so ON soi.order_id = so.order_id WHERE so.shipped_date IS NULL 
            GROUP BY so.store_id, soi.product_id ) t1 
            ON ps.store_id = t1.store_id AND ps.product_id = t1.product_id 
; 

Solution Video: Challenge 82|SQL – Bike Shop Available Inventory