Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

I need Q 4,5,6,7,8,9,12,13 Using the database we learned in the laboratory, write the SQL queries for all the following questions. 1. Create the following

I need Q 4,5,6,7,8,9,12,13image text in transcribedimage text in transcribedimage text in transcribedimage text in transcribed

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 Unit Price Number 6,2 Orders Data type size Note Field name Order No Number 3 PK Odate date NN CustNo Number 3 FK-Customer Number 5 Ord_amt Order_item Field name Data type size Note OrderNo Number 3 PK Item No Number 3 FK-Item Qty Number 5 Shipment Field name Data type size Note Number 3 PK OrderNo WarehouseNo Ship_date Varchar2 3 FK - Warehouse Date Warehouse Field name Data type size Note WarehouseNo Varchar2 3 PK City Varchar2 12 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 CustNo Ord_amt 11 10000 22 11000 22 Odate 03-04-02 09-03-04 23-01-01 11-08-08 06-10-10 23 24 25 33 44 55 14000 16000 20000 Item ItemNo UnitPrice 1 1100 2 3 4 5 1200 1300 1400 1500 order_item Order No Item No Qty 20 1 5 21 25 22 24 23 2 30 40 10 60 4 3 W1 11-Jan-02 W2 W4 wi shipment Order No WarehouseNo Ship_date 21 25 24-Feb-10 22 W3 13-Jun-05 24 16-Sep-09 23 W5 28-Apr-03 Warehouse WarehouseNo City Najma W2 Khor W3 Doha W4 Muntza W5 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 order No 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 order No 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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

The Database Factory Active Database For Enterprise Computing

Authors: Schur, Stephen

1st Edition

0471558443, 9780471558446

More Books

Students also viewed these Databases questions