PART # $1$

QUESTIONS: $($Repeated from Textbook, for Convenience$)$:

Once you have setup your Cape$\_$Codd database, record and store SQL statements that answer each of the following questions. You must also show the results of executing each of the SQL statements that answer each of the following questions.

Use both the INVENTORY and WAREHOUSE tables to answer Questions $2\mathrm{.}40$ through $2\mathrm{.}52$ below:

$2\mathrm{.}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.

$2\mathrm{.}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.

$2\mathrm{.}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.

$2\mathrm{.}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.

$2\mathrm{.}44)$ Write an SQL statement to produce a single column called ItemLocation that combines the SKU$\_$Description, the phrase $"$is located in$",$ and WarehouseCity. Do not be concerned with removing leading or trailing blanks.

$2\mathrm{.}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.

$2\mathrm{.}46)$ 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, but do not use JOIN ON syntax.

$2\mathrm{.}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.

$2\mathrm{.}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.

$2\mathrm{.}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 JOIN ON syntax.

$2\mathrm{.}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.

$2\mathrm{.}51)$ Write an SQL statement to show the WarehouseID, WarehouseCity, WarehouseState, Manager, SKU, SKU$\_$Description, and QuantityOnHand of all items stored in a warehouse managed by 'Lucille Smith'. Use a join using JOIN ON syntax.

$2\mathrm{.}52)$ Write an SQL statement to display the WarehouseID, the sum of QuantityOnOrder, and sum of QuantityOnHand, grouped by WarehouseID and QuantityOnOrder. Name the sum of QuantityOnOrder as TotalItemsOnOrder and the sum of QuantityOnHand as TotalItemsOnHand. Use only the INVENTORY table in your SQL statement.