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.
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.
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
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.
Install Azure Data Studio (if not already installed). Installation instructions can be found here.
Open Azure Data Studio and set up a new local server instance (if not already done):
Database Loading Instructions
- Download the database setup file: Challenge82_Database.sql.
- 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.
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:
- 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.
- 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.
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;
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