Collect your answers to one text file. Submit a softcopy online to Blackboard. Using the database we learned in the laboratory, write the SQL queries for all the following questions. 1. Create the following tables: Customer Field name Data type size Note custNo Number 3 PK cname Varchar2 25 Unique city Varchar2 12 Item Field name Data type size Note Item No Number 3 PK UnitPrice Number 6,2 1 CMPS352-Sping 352 - HW Orders Data type size Note Field name Order No Odate Number 3 PK date NN CustNo Number 3 FK- Customer Ord_amt Number 5 Order_item Field name Data type size Note 3 Order No Number 3 PK Item No Number 3 FK-Item Qty Number 5 Shipment Field name Data type size Note Order No Number 3 PK WarehouseNo Varchar2 3 FK-Warehouse Date Ship_date Warehouse Field name WarehouseNo City Data type size Note 3 PK Varchar2 Varchar2 12 2CMP5352-Sping 352-HW 2. Fill the table by following data: Customer custNo 11 22 cname Sara Fatma Omar Ali City Doha Doha Khor Shimal Khor 33 44 55 Hassan Orders Order No 21 22 23 24 25 Odate 03-04-02 09-03-04 23-01-01 11-08-08 06-10-10 CustNo Ord_amt 11 10000 22 11000 33 14000 44 16000 55 20000 U AWN Item Item No Unit Price 1 1100 1200 1300 1400 1500 Item No 1 order Item OrderNo 21 25 22 24 23 5 Qty 20 30 40 10 2 4 3 60 WS shipment OrderNo Warehouse No Ship_date 21 wi 11-Jan-02 25 W2 24-Feb-10 22 W3 13-Jun-05 24 W4 16-Sep-09 23 28-Apr-03 Warehouse WarehouseNo City wi Najma W2 Khor W3 Doha W4 Muntza ws Rayaan 3. Create sequence for customer number start with 1 and end with 999. 4. Create view for all customers with their orders. 5. Create a query to display the orderNo and Ship_date for all orders shipped from warehouseNo W2. 6. Create a query to display the warehouse information from which the customer named "Fatma" was supplied her orders. Produce a listing: orderNo, WarehouseNo, 7. Create a query to display the cname, No_of_orders, Avg_order_amt, where the middle column is the total number of orders and last column is the average order amount for that customer. 8. Create a query to display the orders that were not shipped within 30 days of ordering. 9. Create a query to display the orderNo for orders that were shipped from all warehouse that the company has in Rayaan. 10. Delete all orders for customer named "Sara". 11. List all items that have a price greater than the average price. 12. Find the item with the maximum unit price. 13.List all customer names whose orders were shipped from a warehouse in the same city as they live in. 4 CMP5352-Sping 352 - HW
