Database

Question 2 Tables below are extracted from a manufacturing company database: Table 5: WAREHOUSE Warehouse D WarehouseCity WarehouseState Manager SqFeet 100 Atlanta GA Dave Jones 125,000 200 Chicago IL Lucille Smith 100,000 300 Bangor MA Bart Evans 150,000 400 Seattle WA Dale Rogers 130,000 Table 6: INVENTORY Warehouse ID SKU SKU Description QuantityOn QuantityOn Hand Order 250 0 100 100100 Std. Scuba Tank, Yellow 200 100100 100 50 Std. Scuba Tank, Yellow Std. Scuba Tank, Yellow 300 100100 100 0 400 100100 200 0 100 100200 200 30 200 100200 Std. Scuba Tank, Yellow Std. Scuba Tank, Magenta Std. Scuba Tank, Magenta Std. Scuba Tank, Magenta Std. Scuba Tank, Magenta 75 75 300 100200 100 100 400 100200 250 0 (a) 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. (6) Write an SQL statement to show a unique SKU and SKL Description for all products having a 'd' in the third position from the left in SKU Description. [4 marks] (c) Write an SQL statement to create table WAREHOUSE. The structure of the table is given in Table 7 below. Table 7: WAREHOUSE_STRUCTURE Column Name Type Key Required Remarks Warehouse Integer Primary Key YES Warehouse City VARCHAR2(30) YES WarehouseState VARCHAR2(2) YES IN ("GA', 'IL', 'MA', 'WA') only Manager VARCHAR2(35) SgFeet Integer [8 marks]