create table customer(custno int primary key, cname varchar(20), city varchar(20));

create table ordr(orderno int primary key,odate date,custno int,ordamt int,
foreign key(custno) references customer(custno));

create table item(itemno int primary key,unitprice int);

create table orderitem(orderno int not null, itemno int not null, qty int,
primary key(orderno,itemno),foreign key(orderno) references ordr(orderno),
foreign key(itemno) references item(itemno)
);

create table ware(wareno int primary key,city varchar(20));

create table shipment(orderno int not null,wareno int not null,shipdate date,
primary key(orderno,wareno),foreign key (orderno) references ordr(orderno),
foreign key (wareno) references ware(wareno)
);


insert into customer values(1,'Abhijit','Bangalore');
insert into customer values(2,'Abhishek','Bangalore');
insert into customer values(3,'Aditya','Mysore');
insert into customer values(4,'Anant','Bangalore');
insert into customer values(5,'Arun','Mumbai');

insert into ordr values(1460,'06-feb-04',1,2000);
insert into ordr values(1461,'06-may-04',1,4000);
insert into ordr values(1462,'01-feb-04',2,5000);
insert into ordr values(1463,'07-feb-04',3,1000);

insert into item values(6,400);
insert into item values(7,100);
insert into item values(8,50);
insert into item values(9,900);
insert into item values(10,20);

insert into orderitem values(1460,10,4);
insert into orderitem values(1460,8,7);
insert into orderitem values(1461,9,4);
insert into orderitem values(1462,7,4);
insert into orderitem values(1463,10,4);

insert into ware values(26,'Bangalore');
insert into ware values(27,'Mysore');
insert into ware values(28,'Mandya');
insert into ware values(29,'Mangalore');
insert into ware values(30,'Bangalore');


insert into shipment values(1460,26,'08-feb-04');
insert into shipment values(1460,30,'08-feb-04');
insert into shipment values(1460,37,'08-feb-04');
insert into shipment values(1461,26,'10-feb-04');
insert into shipment values(1462,27,'12-feb-04');

commit;

select cname as custname,count(*) as no_of_orders,avg(ordamt) as avg_ord_amt 
from customer,ordr where customer.custno=ordr.custno
group by customer.custno,customer.cname;




/* minus is to be used as an operator which automatically shows on screen
  don't use select * again 
 
//VALID
create view a as select ...;
select ... from ... minus select ... from ...;

 every ( has to be followed by another ( or a "select" keyword.
 doesn't support except keyword. use "minus".

A(x,y)=(shipment(wareno,orderno));
B(x)=(select wareno from ware where city='Bangalore');
A.Y- (B*A.Y - A).Y

*/



select orderno from shipment minus (select orderno from (select ware.wareno,orderno from ware,shipment where city='Bangalore'  minus (select wareno,orderno from shipment)));


update orderitem set itemno=NULL where itemno=10;
delete from item where itemno=10;


drop table orderitem;
drop table shipment;
drop table ordr;
drop table customer;
drop table item;
drop table ware;

