Query writing and relational algebra Expression Write the following sql queries using the Cape Codd DB on bottom

1. Write a SQL statement to show sku and Description for all products having a SKU description that includes the word foot.

2. Write a SQL statement to display the warehouse and a count of QuantityOnHand grouped by warehouse.

3. Write a SQL statement to show the SKU and SKU_Description for all items stored in a warehouse managed by each manager.

CREATE DATABASE cape_codd; USE cape_codd; CREATE TABLE RETAIL_ORDER ( OrderNumber Integer NOT NULL, StoreNumber Integer NULL, StoreZip Char (9) NULL, OrderMonth Char (12) NOT NULL, OrderYear Integer NOT NULL, OrderTotal Decimal(9,2) NULL, CONSTRAINT RETAIL_ORDER_PK PRIMARY KEY (OrderNumber) ); CREATE TABLE SKU_DATA ( SKU Integer NOT NULL, SKU_Description Char (35) NOT NULL, Department Char (30) NOT NULL, Buyer Char (30) NULL, CONSTRAINT SKU_DATA_PK PRIMARY KEY (SKU) ); CREATE TABLE ORDER_ITEM ( OrderNumber Integer NOT NULL, SKU Integer NOT NULL, Quantity Integer NOT NULL, Price Decimal(9,2) NOT NULL, ExtendedPrice Decimal(9,2) NOT NULL, CONSTRAINT ORDER_ITEM_PK PRIMARY KEY (SKU, OrderNumber), CONSTRAINT SKU_Relationship FOREIGN KEY (SKU) REFERENCES SKU_DATA (SKU), CONSTRAINT RETAIL_ORDER_Relationship FOREIGN KEY (OrderNumber) REFERENCES RETAIL_ORDER (OrderNumber) ); CREATE TABLE WAREHOUSE ( Warehouse Char(30) NOT NULL, Manager Char(30) NULL, SquareFeet Integer NULL, CONSTRAINT WAREHOUSE_PK PRIMARY KEY (Warehouse) ); CREATE TABLE INVENTORY ( SKU Integer NOT NULL, Warehouse Char (30) NOT NULL, Description Char (35) NOT NULL, QuantityOnHand Integer NULL, QuantityOnOrder Integer NULL, CONSTRAINT INVENTORY_PK PRIMARY KEY (SKU, Warehouse), CONSTRAINT SKU_DATA_Relationship FOREIGN KEY (SKU) REFERENCES SKU_DATA (SKU), CONSTRAINT WAREHOUSE_Relationship FOREIGN KEY (Warehouse) REFERENCES WAREHOUSE (Warehouse) ); INSERT INTO RETAIL_ORDER VALUES ( 1000, 10, '98110', 'December', 2008, 445); INSERT INTO RETAIL_ORDER VALUES ( 2000, 20, '02335', 'December', 2008, 310); INSERT INTO RETAIL_ORDER VALUES ( 3000, 10, '98110', 'January', 2009, 480); INSERT INTO SKU_DATA VALUES ( 100100, 'Std. Scuba Tank, Yellow', 'Water Sports', 'Pete Hansen'); INSERT INTO SKU_DATA VALUES ( 100200, 'Std. Scuba Tank, Magenta', 'Water Sports', 'Pete Hansen'); INSERT INTO SKU_DATA VALUES ( 101100, 'Dive Mask, Small Clear', 'Water Sports', 'Nancy Meyers'); INSERT INTO SKU_DATA VALUES ( 101200, 'Dive Mask, Med Clear', 'Water Sports', 'Nancy Meyers'); INSERT INTO SKU_DATA VALUES ( 201000, 'Half-dome Tent', 'Camping', 'Cindy Lo'); INSERT INTO SKU_DATA VALUES ( 202000, 'Half-dome Tent Footprint', 'Camping', 'Cindy Lo'); INSERT INTO SKU_DATA VALUES ( 301000, 'Light Fly Climbing Harness', 'Climbing', 'Jerry Martin'); INSERT INTO SKU_DATA VALUES ( 302000, 'Locking carabiner, Oval', 'Climbing', 'Jerry Martin'); INSERT INTO ORDER_ITEM VALUES ( 1000, 201000, 1, 300, 300); INSERT INTO ORDER_ITEM VALUES ( 1000, 202000, 1, 130, 130); INSERT INTO ORDER_ITEM VALUES ( 2000, 101100, 4, 50, 200); INSERT INTO ORDER_ITEM VALUES ( 2000, 101200, 2, 50, 100); INSERT INTO ORDER_ITEM VALUES ( 3000, 100200, 1, 300, 300); INSERT INTO ORDER_ITEM VALUES ( 3000, 101100, 2, 50, 100); INSERT INTO ORDER_ITEM VALUES ( 3000, 101200, 1, 50, 50); /* WAREHOUSE data */ INSERT INTO WAREHOUSE VALUES ( 'Atlanta', 'Jones', 125000); INSERT INTO WAREHOUSE VALUES ( 'Chicago', 'Smith', 100000); INSERT INTO WAREHOUSE VALUES ( 'New Jersey', 'Evans', 150000); INSERT INTO WAREHOUSE VALUES ( 'Seattle', 'Rogers', 130000); /* INVENTORY data */ INSERT INTO INVENTORY VALUES ( 100100, 'Atlanta', 'Std. Scuba Tank, Yellow', 250, 0); INSERT INTO INVENTORY VALUES ( 100100, 'Chicago', 'Std. Scuba Tank, Yellow', 100, 50); INSERT INTO INVENTORY VALUES ( 100100, 'New Jersey', 'Std. Scuba Tank, Yellow', 100, 0); INSERT INTO INVENTORY VALUES ( 100100, 'Seattle', 'Std. Scuba Tank, Yellow', 200, 0); INSERT INTO INVENTORY VALUES ( 100200, 'Atlanta', 'Std. Scuba Tank, Magenta', 200, 30); INSERT INTO INVENTORY VALUES ( 100200, 'Chicago', 'Std. Scuba Tank, Magenta', 75, 75); INSERT INTO INVENTORY VALUES ( 100200, 'New Jersey', 'Std. Scuba Tank, Magenta', 100, 100); INSERT INTO INVENTORY VALUES ( 100200, 'Seattle', 'Std. Scuba Tank, Magenta', 250, 0); INSERT INTO INVENTORY VALUES ( 101100, 'Atlanta', 'Dive Mask, Small Clear', 0, 500); INSERT INTO INVENTORY VALUES ( 101100, 'Chicago', 'Dive Mask, Small Clear', 0, 500); INSERT INTO INVENTORY VALUES ( 101100, 'New Jersey', 'Dive Mask, Small Clear', 300, 200); INSERT INTO INVENTORY VALUES ( 101100, 'Seattle', 'Dive Mask, Small Clear', 450, 0); INSERT INTO INVENTORY VALUES ( 101200, 'Atlanta', 'Dive Mask, Med Clear', 100, 500); INSERT INTO INVENTORY VALUES ( 101200, 'Chicago', 'Dive Mask, Med Clear', 50, 500); INSERT INTO INVENTORY VALUES ( 101200, 'New Jersey', 'Dive Mask, Med Clear', 475, 0); INSERT INTO INVENTORY VALUES ( 101200, 'Seattle', 'Dive Mask, Med Clear', 250, 250); INSERT INTO INVENTORY VALUES ( 201000, 'Atlanta', 'Half-dome Tent', 2, 100); INSERT INTO INVENTORY VALUES ( 201000, 'Chicago', 'Half-dome Tent', 10, 250); INSERT INTO INVENTORY VALUES ( 201000, 'New Jersey', 'Half-dome Tent', 250, 0); INSERT INTO INVENTORY VALUES ( 201000, 'Seattle', 'Half-dome Tent', 0, 250); INSERT INTO INVENTORY VALUES ( 202000, 'Atlanta', 'Half-dome Tent Footprint', 10, 250); INSERT INTO INVENTORY VALUES ( 202000, 'Chicago', 'Half-dome Tent Footprint', 1, 250); INSERT INTO INVENTORY VALUES ( 202000, 'New Jersey', 'Half-dome Tent Footprint', 100, 0); INSERT INTO INVENTORY VALUES ( 202000, 'Seattle', 'Half-dome Tent Footprint', 0, 200); INSERT INTO INVENTORY VALUES ( 301000, 'Atlanta', 'Light Fly Climbing Harness', 300, 250); INSERT INTO INVENTORY VALUES ( 301000, 'Chicago', 'Light Fly Climbing Harness', 250, 250); INSERT INTO INVENTORY VALUES ( 301000, 'New Jersey', 'Light Fly Climbing Harness', 0, 250); INSERT INTO INVENTORY VALUES ( 301000, 'Seattle', 'Light Fly Climbing Harness', 0, 250); INSERT INTO INVENTORY VALUES ( 302000, 'Atlanta', 'Locking carabiner', 1000, 0); INSERT INTO INVENTORY VALUES ( 302000, 'Chicago', 'Locking carabiner', 1250, 0); INSERT INTO INVENTORY VALUES ( 302000, 'New Jersey', 'Locking carabiner', 500, 500); INSERT INTO INVENTORY VALUES ( 302000, 'Seattle', 'Locking carabiner', 0, 1000);