Homework #3

due Tuesday, January 24^th

This assignment is to be done in pairs using the pairs assigned in class for HW #2

Hand printed names of pairs: _________________ ____________________

Do the following problems on page 63 of our text. Type in each SQL query using the computer on this sheet. Don’t forget the ending semi-colon.

2.13

SELECT SKU, Description

FROM INVENTORY;

2.15

SELECT Warehouse

FROM INVENTORY;

2.16

SELECT DISTINCT Warehouse

FROM INVENTORY;

2.17

SELECT SKU, Description, QuantityOnHand, QuantityOnOrder, Warehouse

FROM INVENTORY;

2.18

SELECT *

FROM INVENTORY;

2.19

SELECT SKU, Description, QuantityOnHand,QuantityOnOrder,Warehouse

FROM INVENTORY

WHERE QuantityOnHand > 0;

2.21

SELECT SKU, Description, Warehouse

FROM INVENTORY

WHERE QuantityOnHand = 0

ORDER BY Warehouse ASC;

2.23

SELECT SKU Description

FROM INVENTORY

WHERE QuantityOnHand = 0 AND QuantityOnOrder > 0;

2.25

SELECT SKU, Description

FROM INVENTORY

WHERE Warehouse = ‘Seattle’ OR Warehouse = ‘Chicago’ OR Warehouse = ‘New Jersey’;

2.28

SELECT SKU, Description

FROM INVENTORY

WHERE Warehouse NOT IN (‘Seattle’, ‘Chicago’, ‘New Jersey’);

2.29

SELECT SKU, Description, QuantityOnHand

FROM INVENTORY

WHERE QuantityOnHand BETWEEN 2 and 9;

2.31

SELECT SKU, Description

FROM Inventory

WHERE Description LIKE ‘Foot’;

2.33

SELECT SUM (QuantityOnHand) AS SumOfQuantityOnHand,

COUNT (*) AS NumberOfRowsInInventoryTable,

AVG(QuantityOnHand) AS AverageOfQuantityOnHand,

MIN(QuantityOnHand) AS MinimumOfQuantityOnHand,

MAX (QuantityOnHand) AS MaximumOfQuantityOnHand

FROM INVENTORY;