Term
| Based on this table (open in new tab if you haven't already), write an SQL statement to display SKU and SKU_Description for all products that have a QuantityOnHand = 0, and, a QuantityOnOrder > 0. |
|
Definition
|
SELECT SKU, SKU_Description
FROM INVENTORY
WHERE QuantityOnHand = 0
AND QuantityOnHand > 0;
|
|
|
Term
| Based on this table (open in new tab if you haven't already), write an SQL statement to display SKU and SKU_Description for all products that have a QuantityOnHand = 0, OR, a QuantityOnOrder > 0. |
|
Definition
SELECT SKU, SKU_Description,
FROM INVENTORY
WHERE QuantityOnHand = 0
OR QuantityOnHand > 0 |
|
|
Term
| Based on this table (open in new tab if you haven't already), write an SQL statement to display SKU and SKU_Description for all items store in the Seattle, Chicago, or new Jersey warehouse. Use the IN keyword. |
|
Definition
|
SELECT SKU, SKU_Description
FROM INVENTORY
WHERE Warehouse IN (‘Seattle’, ‘Chicago’, ‘New Jersey’);
|
|
|
Term
| Based on this table (open in new tab if you haven't already), write an SQL statement to display SKU and SKU_Description for all items NOT stored in the Seattle, Chicago, or new Jersey warehouse. Use the NOT IN keyword. |
|
Definition
SELECT SKU, SKU_Description
FROM INVENTORY
WHERE Warehouse NOT IN (‘Seattle’, ‘Chicago’, ‘New Jersey’); |
|
|
Term
| Based on this table (open in new tab if you haven't already), write an SQL statement to display the Warehouse, the sum of QuantityOnOrder, and sum of QuantityOnHand, grouped by Warehouse and QuantityOnOrder. Omit all items that have a count greater than 2. Name the count of QuantityOnOrder as TotalItemsOnOrder and count the QuantityOnHand as TotalItemsOnHand |
|
Definition
|
SELECT Warehouse, SUM(QuantityOnOrder) AS TotalItemsOnOrder, SUM
(QuantityOnHand) AS TotatalItemsOnHand
FROM INVENTORY
WHERE QuantityOnHand <= 2
GROUP BY Warehouse, QuantityOnOrder;
|
|
|
Term
| Based on this NEW table (open in new tab if you haven't already), write an SQL statement to show the SKU and SKU_Description for all items stored in a warehouse managed by 'Smith'. Use a Subquery |
|
Definition
|
SELECT SKU, SKU_Description
FROM INVENTORY
WHERE Warehouse IN
(SELECT Warehouse
FROM WAREHOUSE
WHERE Manager = ‘Smith’);
|
|
|
Term
| Based on this NEW table (open in new tab if you haven't already), write an SQL statement to show the SKU and SKU_Description for all items stored in a warehouse managed by 'Smith'. Use a join |
|
Definition
|
SELECT SKU, SKU_Description
FROM INVENTORY, WAREHOUSE
WHERE INVENTORY.Warehouse = WAREHOUSE.Warehouse
AND Manager = ‘Smith’;
|
|
|
Term
| Based on this NEW table (open in new tab if you haven't already), write an SQL statement to show the Warehouse and average QuanityOnHand of all items stored in a warehouse managed by 'Smith'. Use a subquery |
|
Definition
|
SELECT Warehouse, AVG(QuantityOnHand) AS AverageQuantityOnHand
FROM INVENTORY
WHERE Warehouse IN
(SELECT Warehouse
FROM WAREHOUSE
WHERE Manager = ‘Smith’);
|
|
|
Term
| Based on this NEW table (open in new tab if you haven't already), write an SQL statement to show the Warehouse and average QuanityOnHand of all items stored in a warehouse managed by 'Smith'. Use a join |
|
Definition
SELECT Warehouse, AVG(QuantityOnHand) AS AverageQuantityOnHand
FROM INVENTORY, WAREHOUSE
WHERE INVENTORY.Warehouse = WAREHOUSE.Warehouse
AND Manager = ‘Smith’; |
|
|
Term
| Based on this NEW table (open in new tab if you haven't already), write an SQL statement to show the Warehouse, Manager, and QuantityOnHand of all items stored in a warehouse managed by 'Smith'. Use a join. |
|
Definition
SELECT Warehouse, Manager, QuantityOnHand
FROM INVENTORY, WAREHOUSE
WHERE INVENTORY.Warehouse = WAREHOUSE.Warehouse
AND Manager = 'Smith';
|
|
|
