Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create table customer(cust int,cname varchar(10), city varchar(10), primary key(cust));
- create table order1(ord int, odate date,cust int,ord_amt int,primary key(ord));
- create table item(item int, unit_price int, primary key(item));
- create table order_item(ord int REFERENCES order1(ord), item int REFERENCES item(item) on delete set null, qty int);
- create table warehouse(warehouse int, city varchar(15), primary key(warehouse));
- create table shipment(ord int REFERENCES order1(ord), warehouse int REFERENCES warehouse(warehouse), ship_date date);
- insert into customer(cust,cname,city) values
- (2,'shashi','N.America'),(3,'kala','U.S.A'),(5,'Dumini','India'),(2002,'Stuart','Switz'),
- (2003,'Binny','N.America'),(2004,'Curie','U.S.A'),(2005,'Dumini','India'),(2006,'Rick','India');
- insert into item(item,unit_price) values
- (1500,250),(750,167),(125,124),(136,420),(250,138),(10,500);
- insert into shipment(ord,warehouse,ship_date) values
- (1,322,'4-03-21'),(2,234,'7-12-23'),(3,324,'14-06-24'),
- (4,325,'12-10-25'),(5,326,'12-4-25');
- insert into order1(ord,odate,cust,ord_amt) values
- (1,'2006-10-5',2,5000),(2,'2006-10-8',3,2500),(3,'2006-10-9',3,1000),
- (4,'2006-10-6',5,1000),(5,'2006-11-3',1,5000);
- insert into order_item(ord,item,qty) values
- (1,1500,1),(1,750,1),(2,125,1),(3,136,5),(4,250,2),(4,10,2);
- insert into warehouse(warehouse,city) values
- (321,'Bangalore'),(322,'Hyderabad'),(323,'Bangalore'),(324,'New Delhi'),(325,'New York');
- select c.cname, count(*) as no_of_orders, avg(o.ord_amt) as avg_order_amt from customer c,order1 o where c.cust=o.cust group by c.cname;
- select ord from warehouse w,shipment s where w.warehouse=s.warehouse and city="New Delhi";
- delete from item where item=250;
- select * from order_item;
- Output:
- +--------+--------------+---------------+
- | cname | no_of_orders | avg_order_amt |
- +--------+--------------+---------------+
- | shashi | 1 | 5000.0000 |
- | kala | 2 | 1750.0000 |
- | Dumini | 1 | 1000.0000 |
- +--------+--------------+---------------+
- +------+
- | ord |
- +------+
- | 3 |
- +------+
- +------+------+------+
- | ord | item | qty |
- +------+------+------+
- | 1 | 1500 | 1 |
- | 1 | 750 | 1 |
- | 2 | 125 | 1 |
- | 3 | 136 | 5 |
- | 4 | 10 | 2 |
- +------+------+------+
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
