I am unable to post the database itself but please post what you can using the p

Question

I am unable to post the database itself but please post what you can using the prompts below.

USING THE ‘INVENTORY’ TABLE ONLY:

Write an SQL statement to display data for all of the columns. Provide a screenshot of the result as well.

Write an SQL statement to display data for SKU_Description and SKU. Provide a screenshot of the result as well.

Write an SQL statement to Create A View to display data for unique WarehouseIDs. Provide a screenshot of the result as well.

Write an SQL statement to display all data on products having a QuantityOnHand greater than 0. Provide a screenshot of the result as well.

Write an SQL statement to display the data for SKU, SKU_Description, and WarehouseID for product having QuantityOnHand equal to 0. Sort the results in ascending order by WarehouseID. Provide a screenshot of the result as well.

Write an SQL statement to display the data for SKU, SKU_Description, and WarehouseID for products having QuantityOnHand greater than 0. Sort the results in descending order by WarehouseID and ascending order by SKU. Provide a screenshot of the result as well.

Write an SQL statement to Create A View to display the data for SKU, SKU_Description, WarehouseID, and QuantityOnHand for all products having a QuantityOnHand greater than 1 and less than 10. Provide a screenshot of the result as well. Provide a screenshot of the result as well.

Write an SQL statement to show data for SKU and SKU_Description for all products having an SKU description starting with ‘Half-dome’. Provide a screenshot of the result as well.

Write an SQL statement to display the data for WarehouseID and the sum of QuantityOnHand, grouped by WarehouseID. Name the sum ‘TotalItemsOnHand’. Provide a screenshot of the result as well.

Write an SQL statement to display the data for WarehouseID and the sum of QuantityOnHand grouped by WarehouseID. Omit all SKU items that have 3 or more items on hand from the sum, and name the sum ‘TotalItemsOnHandLT3’. Display the results in descending order of ‘TotalItemsOnHandLT3’. Provide a screenshot of the result as well.

Explanation / Answer

Please find answers below:

1. SQL statement to display data for all of the columns:

select * from INVENTORY;

2. SQL statement to display data for SKU_Description and SKU:

select * from SKU_Description and SKU from INVENTORY;

3. SQL statement to Create A View to display data for unique WarehouseID:

CREATE VIEW UNIQUE_WAREHOUSE as SELECT DISTINCT(WarehouseID) FROM INVENTORY;

4. SQL statement to display all data on products having a QuantityOnHand greater than 0:

SELECT * FROM INVENTORY WHERE QuantityOnHand > 0;

5. SQL statement to display the data for SKU, SKU_Description, and WarehouseID for product having QuantityOnHand equal to 0:

SELECT SKU, SKU_Description, WarehouseID FROM INVENTORY WHERE QuantityOnHand = 0;

6. SQL statement to display the data for SKU, SKU_Description, and WarehouseID for products having QuantityOnHand greater than 0. Sort the results in descending order by WarehouseID and ascending order by SKU:

SELECT SKU, SKU_Description, WarehouseID FROM INVENTORY WHERE QuantityOnHand >0 order by WarehouseID DESC, SKU ASC;

7. SQL statement to Create A View to display the data for SKU, SKU_Description, WarehouseID, and QuantityOnHand for all products having a QuantityOnHand greater than 1 and less than 10.

CREATE VIEW DISPLAY_DATA as SELECT SKU, SKU_Description, WarehouseID FROM INVENTORY WHERE QuantityOnHand >1 and QuantityOnHand<1;

Provide a screenshot of the result as well:

SELECT * FROM DISPLAY_DATA;

8. SQL statement to show data for SKU and SKU_Description for all products having an SKU description starting with ‘Half-dome’:

SELECT SKU, SKU_Description, WarehouseID FROM INVENTORY WHERE SKU_Description like 'Half-dome%';

9. SQL statement to display the data for WarehouseID and the sum of QuantityOnHand, grouped by WarehouseID. Name the sum ‘TotalItemsOnHand’:

SELECT WarehouseID , SUM ( QuantityOnHand ) AS TotalItemsOnHand FROM INVENTORY GROUP BY WarehouseID ;

10. SQL statement to display the data for WarehouseID and the sum of QuantityOnHand grouped by WarehouseID. Omit all SKU items that have 3 or more items on hand from the sum, and name the sum ‘TotalItemsOnHandLT3’. Display the results in descending order of ‘TotalItemsOnHandLT3’.

SELECT WarehouseID, SUM(QuantityOnHand) as TotalItemsOnHandLT3 FROM INVENTORY WHERE QuantityOnHand < 3 GROUP BY WarehouseID ORDER BY TotalItemsOnHandLT3 DESC.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.