Case Study 1

Instructions

• Use the Cape_Codd database
• Use only the Inventory table
• Deliver a single sql file, along with a document that contains all the content from the sql file
• Each query should start with a comment line that looks like:
  /* *** CS1-<CH-Q> *** */
  Example for Exercise 2.17:
  /* *** CS1-2.17*** */
• Do not include the result table unless specifically directed to
• Include at least one line of white space between answers

Exercises 2.17 - 2.33

2.17) Write a SQL statement to display SKU and SKU_Description.

2.18) Write a SQL statement to display SKU_Description and SKU.

2.19) Write a SQL statement to display WarehouseID.

2.20) Write a SQL statement to display unique WarehouseIDs.

2.21) Write a SQL statement to display all of the columns without using the SQL asterisk (wildcard character).

2.22) Write a SQL statement to display all of the columns using the SQL asterisk (wildcard character)

2.23) Write a SQL statement to display all data on products having a QuantityOnHand greater than 0.

2.24) Write a SQL statement to display the SKU and SKU_Description for products having QuantityOnHand equal to 0.

2.25) Write a SQL statement to display the SKU, SKU_Description, and WarehouseID for products that have a QuantityOnHand equal to 0. Sort the results in ascending order by WarehouseID.

2.26) Write a SQL statement to display the SKU, SKU_Description, and WarehouseID for products that have a QuantityOnHand greater than 0. Sort the results in descending order by WarehouseID and in ascending order by SKU.

2.27) Write a SQL statement to display SKU, SKU_Description, and WarehouseID for all products that have a QuantityOnHand equal to 0 and a QuantityOnOrder greater than 0. Sort the results in descending order by WarehouseID and in ascending order by SKU.

2.28) Write a SQL statement to display SKU, SKU_Description, and WarehouseID for all products that have a QuantityOnHand equal to 0 or a QuantityOnOrder equal to 0. Sort the results in descending order by WarehouseID and in ascending order by SKU.

2.29) Write a SQL statement to display the SKU, SKU_Description, WarehouseID, and QuantityOnHand for all products having a QuantityOnHand greater than 1 and less than 10. Do not use the BETWEEN keyword.

2.30) Write a SQL statement to display the SKU, SKU_Description, WarehouseID, and QuantityOnHand for all products having a QuantityOnHand greater than 1 and less than 10. Use the BETWEEN keyword.

2.31) Write a SQL statement to show a unique SKU and SKU_Description for all products with an SKU description starting with ‘Half-Dome’.

2.32) Write a SQL statement to show a unique SKU and SKU_Description for all products with a description that includes the word ‘Climb’.

2.33) Write a SQL statement to show a unique SKU and SKU_Description for all products with a ‘d’ in the third position from the left in SKU_Description.