数据库复习题

复习题

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.

SELECT SKU,SKU_Description FROM INVENTORY;

2.18 Write an SQL statement to display SKU_Description and SKU.

SELECT SKU_Description,SKU FROM INVENTORY;

2.19 Write an SQL statement to display WarehouseID.

SELECT WarehouseID FROM INVENTORY;

2.20 Write an SQL statement to display unique WarehouseIDs.

SELECT DISTINCT WarehouseID FROM INVENTORY;

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.

SELECT SKU, SKU_Description, WarehouseID
FROM INVENTORY
WHERE QuantityOnHand>0
ORDER BY WarehouseID DESC, SKU ASC;

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.

SELECT SKU, SKU_Description, WarehouseID, QuantityOnHand
FROM INVENTORY
WHERE QuantityOnHand>1 AND QuantityOnOrder<10;

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.

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

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.

SELECT WarehouseID , SUM (QuantityOnHand) AS TotalItamsOnHandLT3
FROM INVENTORY
WHERE QuantityOnHand<3
GROUP BY WarehouseID
ORDER BY TotalItemsOnHandLT3 DESC;

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.

SELECT WarehouseID , SUM (QuantityOnHand) AS TotalItamsOnHandLT3,	
FROM INVENTORY
WHERE QuantityOnHand <3
GROUP BY WarehouseID
HAVING COUNT(*)<2
ORDER BY TotalItemsOnHandLT3 DESC;

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.

SELECT SKU, SKU_Description, INVENTORY.WarehouseID, WarehouseCity, WarehouseState
FROM INVENTORY, WAREHOUSE
WHERE INVENTORY.WarehouseID=WAREHOUSE.WarehouseID AND WarehouseCity!='Atlanta' AND WarehouseCity!='Chicago' AND WarehouseCity!='Bangor';

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.

SELECT DISTINCT RTRIM(SKU_Description)+ 'is in a warehouse' + RTRIM(WarehouseCity) AS ItemLocation
FROM INVENTORY, WAREHOUSE WHERE INVENTORY.WarehouseID=WAREHOUSE.WarehouseID;

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.

SELECT SKU, SKU_Description, WarehouseID
FROM INVENTORY
WHERE WarehouseID IN (SELECT WarehouseID
FROM WAREHOUSE WHERE Manager='Lucille Smith');

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.

SELECT SKU, SKU_Description, INVENTORY.WarehouseID
FROM INVENTORY, WAREHOUSE
WHERE INVENTORY.WarehouseID=WAREHOUSE.WarehouseID AND WAREHOUSE.Manager= 'Lucille Smith';

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.

SELECT INVENTORY.WarehouseID, AVG(QuantityOnHand)
FROM INVENTORY JOIN WAREHOUSE
ON INVENTORY.WarehouseID=WAREHOUSE.WarehouseID 
WHERE WAREHOUSE.Manager='Lucille Smith';

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.

SELECT *
FROM WAREHOUSE LEFT OUTER JOIN INWENTORY 
ON INVENTORY.WarehouseID=WAREHOUSE.WarehouseID;