The Questions are in the file.
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.21 Write an SQL statement to display all of the columns without using the SQL asterisk
(*) wildcard character.
2.22 Write an SQL statement to display all of the columns using the SQL asterisk (*) wildcard character.
2.
23
Write an SQL statement to display all data on products having a QuantityOnHand
greater than 0.
20130001 100100
CatalogID SKU
20130002 100500
20130003 100600
20130004 101100
20130005 101200
Std. Scuba Tank, Yellow
SKU_Description
Std. Scuba Tank, Light Green
Std. Scuba Tank, Dark Green
Dive Mask, Small Clear
Water Sports
Department CatalogPage DateOnWebSite
Water Sports
Water Sports
Water Sports
Dive Mask, Med Clear Water Sports
20130006 201000
20130007 202000
Half-dome Tent Camping
Half-dome Tent Vestibule Camping
20130008 301000
20130009 302000
Light Fly Climbing Harness Climbing
Locking Carabiner, Oval Climbing
23
NULL
NULL
24
24
45
47
76
78
2013-01-01
2013-07-01
2013-07-01
2013-01-01
2013-01-01
2013-01-01
2013-01-01
2013-01-01
2013-01-01
Figure 2-40
Cape Codd Database
CATALOG_SKU_2013
Table Data
144 Part 1 Getting Started
2.24 Write an SQL statement to display the SKU and SKU_Description for products
having QuantityOnHand equal to 0.
2.25 Write an 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 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.27 Write an 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 an 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 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.30 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. Use the BETWEEN keyword.
2.31 Write an SQL statement to show a unique SKU and SKU_Description for all products having an SKU description starting with ‘Half-Dome’.
2.32 Write an SQL statement to show a unique SKU and SKU_Description for all products having a description that includes the word ‘Climb’.
2.33 Write an SQL statement to show a unique SKU and SKU_Description for all products having a ‘d’ in the third position from the left in SKU_Description.
2.34 Write an SQL statement that uses all of the SQL built-in functions on the
QuantityOnHand column. Include meaningful column names in the result.
2.35 Explain the difference between the SQL built-in functions COUNT and SUM.
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 the WarehouseID
only for warehouses having fewer than 2 SKUs in their TotalItemsOnHandLT3.
Display the results in descending order of TotalItemsOnHandLT3.
2.39 In your answer to Review Question 2.38, was the WHERE clause or the HAVING
clause applied first? Why?
