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.
SELECT SKU, SKU_Description,WarehouseIDQuantityOnHand FROM INVENTORY
WHEREQuantityOnHandBETWEEN 1 and 10 ORDER BYWarehouseIDDESC,SKU ASC;
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'.
SELECT DISTINCT SKU, SKU_Description FROM INVENTORY
WHERE SKU_description LIKE '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'.
SELECT DISTINCT SKU, SKU_Description FROM INVENTORY
WHERE SKU_description LIKE '%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.
SELECT DISTINCT SKU, SKU_Description FROM INVENTORY
WHERE SKU_description LIKE '_ _d%';
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.
built-in functions: SUM() AVG() MIN() MAX() COUNT()
SELECT SUM(QuantityOnHand) AS QuantitySUM, FROM INVENTORY SELECT AVG(QuantityOnHand) AS QuantityAVG FROM INVENTORY SELECT MIN(QuantityOnHand) AS QuantityMINFROM INVENTORY
SELECT MAX(QuantityOnHand) AS QuantityMAX FROM INVENTORY SELECT COUNT(QuantityOnHand) AS QuantityCOUNTFROMINVENTORY
2.35 Explain the difference between the SQL built-in functions COUNT and SUM.
SUM是对符合条件的记录的数值列内容求和
COUNT是对查询中符合条件的结果（或记录）的个数求和
The build-in function COUNT means that calculating how many number are there in the table. But the function SUM means that adding all the number in the table which match the condition to get the sum.
2.36 Write an SQL statement to display the WarehouseID and the sum of QuantityOnHand,
grouped by WarehouseID. Name the sum TotalItemsOnHand. Display the results in descending order of TotalItemsOnHand.
SELECT WarehouseIDSUM(QuantityOnHand) ASTotalItemsOnHand FROM INVENTORY; GROUP BYWarehouseID,
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. 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 Warehouse ID 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?
Use both the INVENTORY and WAREHOUSE tables to answer Review Questions 2.40through 2.55:
2.40 Write an SQL statement to display the SKU, SKU_Description, WarehouseID, WarehouseCity, and WarehouseState for all items stored in the Atlanta, Bangor, or Chicago warehouse. Do not use the IN keyword.
SELECTWAREHOUSE.WarehouseCity,
WAREHOUSE.WarehouseState,INVENTORY.
SKU,INVENTORY.SKU_Description, INVENTORY.WarehouseID FROMWAREHOUSE, INVENTORY WHERE WAREHOUSE.WarehouseCity=‘ Atlanta‘ ORWAREHOUSE.WarehouseCity=‘ Bangor‘ ORWAREHOUSE.WarehouseCity=‘ Chicago‘;
2.41 Write an SQL statement to display the SKU, SKU_Description, WarehouseID, WarehouseCity, and WarehouseState for all items stored in the Atlanta, Bangor, or Chicago warehouse. Use the IN keyword.
SELECTWAREHOUSE.WarehouseCity,
WAREHOUSE.WarehouseState,INVENTORY.
SKU,INVENTORY.SKU_Description, INVENTORY.WarehouseID FROMWAREHOUSE, INVENTORY
WHERE WAREHOUSE.WarehouseCityIN(‘Atlanta‘, ?Bangor‘, or?Chicago‘);
2.42 Write an SQL statement to display the SKU, SKU_Description, WarehouseID, WarehouseCity, and WarehouseState of all items not stored in the Atlanta, Bangor, or Chicago warehouse. Do not use the NOT IN keyword. SELECTWAREHOUSE.WarehouseCity,
WAREHOUSE.WarehouseState,INVENTORY.
SKU,INVENTORY.SKU_Description, INVENTORY.WarehouseID FROMWAREHOUSE, INVENTORY WHERE WAREHOUSE.WarehouseCity<>‘ Atlanta‘ ORWAREHOUSE.WarehouseCity<>‘ Bangor‘ ORWAREHOUSE.WarehouseCity<>‘ Chicago‘;
2.43 Write an SQL statement to display the SKU, SKU_Description, WarehouseID, WarehouseCity, and WarehouseState of all items not stored in the Atlanta, Bangor, or
Chicago warehouse. Use the NOT IN keyword.（写一个SQL语句显示SKU,SKU_Description,WarehouseID,
WarehouseCity,WarehouseState所有项目不存储在亚特兰大,班戈,或 芝加哥仓库。使用关键字。）
SELECT SKU, SKU_Description, WarehouseID, WarehouseCity, WarehouseState
FROM INVENTORY, WAREHOUSE
WHERE WarehouseCity NOT IN (?Atlanta‘, ‘Chicago‘, ‘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. Donot be concerned with removing leading or trailing blanks.（写一个SQL语句产生单个列称为ItemLocation结合SKU_Description,短语―在一个仓库‖,和WarehouseCity。做不关心删除前导或尾随空格。）
SELECT DISTINCT RTRIM (SKU_Description)+‘is in a warehouse‘ + RTRIM (WarehouseCity)
FROM INVENTORY, WAREHOUSE;
warehouseID是哪个表里面的啊
2.45 Write an SQL statement to show the SKU, SKU_Description, and WarehouseID for all items stored in a warehouse managed by 'Lucille Smith'. Use a subquery.（写一个SQL语句显示SKU,SKU_Description,WarehouseID物品储存在一个仓库管理的露西尔·史密斯。使用子查询。）
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, and WarehouseID for allitems stored in a warehouse managed by 'Lucille Smith'. Use a join, but do not use JOIN ON syntax.（写一个SQL语句显示SKU,SKU_Description,WarehouseID 物品储存在一个仓库管理的露西尔·史密斯。使用一个连接,但不使用连接 在语法上。）
SELECT SKU,SKU_Description,WAREHOUSE.WarehouseID(不要前缀？) FROM INVENTORY,WAREHOUSE
WHERE INVENTORY.WarehouseID=WAREHOUSE. WarehouseID AND WAREHOUSE. Manager=‘Lucille Smith‘;
2.47 Write an SQL statement to show the SKU, SKU_Description, and WarehouseID
for all
items stored in a warehouse managed by 'Lucille Smith'. Use a join using JOIN ON syntax.（写一个SQL语句显示SKU,SKU_Description,WarehouseID 物品储存在一个仓库管理的露西尔·史密斯。使用一个连接使用连接的语法。） SELECT INVENTORY.SKU ,INVENTORY.SKU_Description ,WAERHOUSE. WarehouseID
FROM INVENTORY JOIN WAREHOUSE
ON INVENTORY.WarehouseID=WAREHOUSE.WarehouseID WHERE WAREHOUSE.Manager= ?Lucille Smith‘;
2.48 Write an SQL statement to show the WarehouseID and average QuantityOnHand of all items stored in a warehouse managed by 'Lucille Smith'. Use a subquery.（写一个SQL语句显示WarehouseID和平均QuantityOnHand 物品储存在一个仓库管理的露西尔·史密斯。使用子查询。） SELECT WarehouseID, AVG(QuantityOnHand) FROM INVENTORY WHERE WarehouseID IN
(SELECT WarehouseID FROM WAREHOUSE
WHERE Manager=‘ Lucille Smith‘) GROUP BY WarehouseID;
2.49 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, but do not use JOINON syntax.（写一个SQL语句显示WarehouseID和平均QuantityOnHand 物品储存在一个仓库管理的露西尔·史密斯。使用一个连接,但不使用连接 在语法上。）
SELECT INVENTORY. WarehouseID, AVG(INVENTORY. QuantityOnHand) FROM INVENTORY ,WAREHOUSE
WHERE INVENTORY. WarehouseID= WAREHOUSE. WarehouseID AND WAREHOUSE. Manager=‘ Lucille Smith‘ GROUP BY WarehouseID;
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.（写一个SQL语句显示WarehouseID和平均QuantityOnHand物品储存在一个仓库管理的露西尔·史密斯。使用一个连接使用连接的语法。）
SELECT WarehouseID, AVG(QuantityOnHand) FROM INVENTORY JOIN WAREHOUSE
ONINVENTORY.WarehouseID=WAREHOUSE.WarehouseID
WHERE WAREHOUSE.Manager=‘ Lucille Smith‘;