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.

4. Find out the total revenue collected by each store.

5. Which department is associated with the highest revenue?

6. Which department is associated with the lowest revenue?

7. Find out the storenumber and zipcode of the store that sold the maximum number of products.

8. Find out other warehouses whose average quantity on hand is not smaller than That of Atlanta warehouse.

9. List skus and descriptions for all products whose sku starts with a "2". Use the BETWEEN operator. You may assume all sku's have 6 digits.

10. List the warehouses that currently have on average less than 225 items in stock of the products they carry. Use the HAVING operator.

11. List all order items for products currently out of stock in Atlanta. Use a subquery.

12. Get a list of buyers and their departments for any products out of stock (at any warehouse, not all warehouses). Use a join.

13. Get a list of departments and the total items on order for each department, sorted from largest to smallest quantity. Use a join.

Use the PC Manufacturer sample DB to write these additional queries as well as relational algebra expression.

1. Find the model number and price of all products (of any type) made by maker B.

2. Find those makers that sell laptops, not PCs.

3. Find those makers of at least two different computers. All queries should be contained in a single plain text file with a sql extension. Queries must be in the order above. Don't forget to separate each query with a semi-colon

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);