The document describes creating tables for an order processing database including tables for customers, orders, order items, items, warehouses, and shipments. It provides the SQL commands for creating the tables, inserting sample data, and examples of SQL queries to retrieve order information and delete an item while handling related order item rows. The tables are set up with primary and foreign keys to link the different entities as needed for the order processing application.
Copyright:
Attribution Non-Commercial (BY-NC)
Available Formats
Download as TXT, PDF, TXT or read online from Scribd
The document describes creating tables for an order processing database including tables for customers, orders, order items, items, warehouses, and shipments. It provides the SQL commands for creating the tables, inserting sample data, and examples of SQL queries to retrieve order information and delete an item while handling related order item rows. The tables are set up with primary and foreign keys to link the different entities as needed for the order processing application.
The document describes creating tables for an order processing database including tables for customers, orders, order items, items, warehouses, and shipments. It provides the SQL commands for creating the tables, inserting sample data, and examples of SQL queries to retrieve order information and delete an item while handling related order item rows. The tables are set up with primary and foreign keys to link the different entities as needed for the order processing application.
Copyright:
Attribution Non-Commercial (BY-NC)
Available Formats
Download as TXT, PDF, TXT or read online from Scribd
The document describes creating tables for an order processing database including tables for customers, orders, order items, items, warehouses, and shipments. It provides the SQL commands for creating the tables, inserting sample data, and examples of SQL queries to retrieve order information and delete an item while handling related order item rows. The tables are set up with primary and foreign keys to link the different entities as needed for the order processing application.
Copyright:
Attribution Non-Commercial (BY-NC)
Available Formats
Download as TXT, PDF, TXT or read online from Scribd
Download as txt, pdf, or txt
You are on page 1/ 3
2.
Consider the following relations for an order processing database application
in a company: CUSTOMER (cust #: int , cname: string, city: string) ORDER (order #: int, odate: date, cust #: int, ord-Amt: int) ORDER ITEM (order #: int, item #: int, qty: int) ITEM (item # : int, unit price: int) SHIPMENT (order #: int, warehouse#: int, ship-date: date) WAREHOUSE (warehouse #: int, city: string) (i) Create the above tables by properly specifying the primary keys and the fore ign keys. Customer create table customer ( cno int primary key, cname varchar(10) not null, city varchar(15) not null ); Order create table order1 ( ono int primary key, odate date, cno int references customer(cno), oamt int, ); Item create table item ( ino int primary key, uprice int, ); Order - Item create table oitem ( ono int references order1(ono) ino int references item(ino) qty int, ); Warehouse create table warehouse ( wno int primary key, city varchar(15) ); Shipment create table shipment ( ono int references order1(ono) wno int references warehouse(wno) sdate date, ); customer insert into customer values(1,'abc','bangalore'); insert into customer values(2,'cde','mysore'); insert into customer values(3,'def','chennai'); insert into customer values(4,'efg','mumbai'); insert into customer values(5,'adf','kolkata'); order1 insert into order1 values(1,'1-01-2006',1,20000); insert into order1 values(2,'26-03-2006',2,10000); insert into order1 values(3,'12-06-2006',1,5000); insert into order1 values(4,'15-09-2006',3,9000); insert into order1 values(5,'5-j01-2007',4,2500); insert into order1 values(6,'10-01-2007',4,2400); insert into order1 values(7,'3-02-2007',5,3500); item insert into item values(1,500); insert into item values(2,300); insert into item values(3,2500); insert into item values(4,800); insert into item values(5,700); oitem insert into oitem values(1,1,40); insert into oitem values(2,1,20); insert into oitem values(3,3,2); insert into oitem values(5,3,1); insert into oitem values(4,2,30); insert into oitem values(6,4,3); insert into oitem values(7,5,5); warehouse insert into warehouse values(100,'bangalore'); insert into warehouse values(101,'chennai'); insert into warehouse values(102,'mumbai'); insert into warehouse values(103,'kolkata'); insert into warehouse values(104,'mysore'); shipment insert into shipment values(1,100,'3-01-2006'); insert into shipment values(2,100,'28-03-2006'); insert into shipment values(3,101,'13-06-2006'); insert into shipment values(4,102,'18-09-2006'); insert into shipment values(5,103,'11-01-2007'); insert into shipment values(6,104,'13-01-2007'); insert into shipment values(7,103,'3-03-2007'); (iii) Produce a listing: CUSTNAME, #oforders, AVG_ORDER_AMT, where the middle co lumn is the total numbers of orders by the customer and the last column is the a verage order amount for that customer. select cname as custname,count(ono) as no_of_orders,avg(oamt) as avg_ord_a mt from customer c,order1 o where c.ono=o.ono group by c.cno,cname; (iv) List the order# for orders that were shipped from all the warehouses that t he company has in a specific city. select ono from order1 o where not exist ((select wno from warehouse where city='bangalore') minus (select wno from shipment s where s.ono=o.ono)) and exists (select wno from warehouse where city='bangalore'); (v) Demonstrate the deletion of an item from the ITEM table and demonstrate a me thod of handling the rows in the ORDER_ITEM table that contain this particular i tem. delete from item where ino=5;
