复习题
Use only the INVENTORY table to answer Review Questions 2.17 through 2.40:
2.17 Write an SQL statement to display SKU and SKU_Description.
|
2.18 Write an SQL statement to display SKU_Description and SKU.
|
2.19 Write an SQL statement to display WarehouseID.
|
2.20 Write an SQL statement to display unique WarehouseIDs.
|
2.26 Write an 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.29 Write an 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.36 Write an SQL statement to display the WarehouseID and the sum of QuantityOnHand,grouped by WarehouseID. Name the sum TotalItemsOnHand and display the results in descending order of TotalItemsOnHand.
|
2.37 Write an SQL statement to display the 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 and display the results in descending order of TotalItemsOnHandLT3.
|
2.38 Write an SQL statement to display the 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. Show WarehouseID only for warehouses having fewer than 2 SKUs in their TotalItemesOnHandLT3 and display the results in descending order of TotalItemsOnHandLT3.
|
Use both the INVENTORY and WAREHOUSE tables to answer Review Questions 2.40 through 2.52:
2.42 Write an SQL statement to display the SKU, SKU_Description, WarehouseID, Ware-houseCity, and WarehouseState of all items not stored in the Atlanta, Bangor, or Chicago warehouse. Do not use the NOT IN keyword.
|
2.44 Write an SQL statement to produce a single column called ItemLocation that combines the SKU_Description, the phrase “is in a warehouse in”, and WarehouseCity. Do not be concerned with removing leading or trailing blanks.
|
2.45 Write an SQL statement to show the SKU, SKU_Description, WarehouseID for all items stored in a warehouse managed by ‘Lucille Smith’. Use a subquery.
|
2.46 Write an SQL statement to show the SKU, SKU_Description, WarehouseID for all items stored in a warehouse managed by ‘Lucille Smith’. Use a join.
|
2.50 Write an SQL statement to show the WarehouseID and average QuantityOnHand of all items stored in a warehouse managed by ‘Lucille Smith’. Use a join using JOIN ON syntax.
|
2.55 Write an SQL statement to join WAREHOUSE and INVENTORY and include all rows of WAREHOUSE in your answer, regardless of whether they have any INVENTORY. Run this statement.
|