assignment2.sql

create table userdata(
	userid varchar2(6) primary key,
	email varchar2(20) unique,
	fullname varchar2(18) not null,
	gender char(1),
	address varchar2(35)
);
create table itemcategory(
	catid char(3) primary key,
	catname varchar2(15),
	description varchar2(30)
);
create table item(
	itemid char(3) primary key,
	itemtype char(5),
	itemname varchar2(50) unique,
	unitprice number(6,2) not null,
	Foreign Key(itemtype) references itemcategory(catid) on delete cascade
);
create table orderlist(
	orderid number generated by default as identity primary key,
	userid varchar2(6),
	itemid char(3),
	qty number(2) not null,
	amount number(8,2) not null,
	orderdate date,
	paymode char(4)
);

/*2a*/
rename userdata to alluser;

/*2b*/
alter table itemcategory modify description varchar2(50);
alter table itemcategory rename column description to descr;
alter table itemcategory modify catname not null;

/*2c*/
alter table orderlist add constraint FK foreign key(userid) references alluser(userid) on delete cascade;
alter table orderlist add constraint FK1 foreign key(itemid) references item(itemid) on delete cascade;

/*2d*/
alter table alluser drop column gender;
alter table alluser add phone char(10) unique;

/*3a*/
insert into itemcategory values('BVG','Beverages','Can be taken at any time.');
insert into itemcategory values('FTF','Fast Food','Can be taken as breakfast or tiffin.');
insert into itemcategory values('MNC','Main Course','Can be taken as lunch or dinner.');
insert into itemcategory values('DRT','Desert','Usually taken after main course.');

/*3b*/
alter table item modify itemtype char(3); 
insert into item values('F01','FTF','Paneer Tikka Wrap Combo',130.75);
insert into item values('M01','MNC','Rajma Chawal Bowl',189.25);
insert into item values('M02','MNC','Hyderabadi Chicken Biryani',220.50);
insert into item values('B01','BVG','Mint Chaas',89.00);
insert into item values('M03','MNC','Kadai Paneer-Thali',209.75);
insert into item values('F02','FTF','Chicken Tikka Sandwich',139.25);
insert into item values('M04','MNC','Chana Masala Pulao',179.25);
insert into item values('F03','FTF','Chilli Corn 65',179.25);
insert into item values('B02','BVG','Masala Lemonade',89.00);

/*4*/
insert into alluser values('doro77','dorothy77@gmail.com','Dorothy Jones','4D/1, Fern Road, Kolkata','4578895623');
insert into alluser values('eve80','evelyn@gmail.com','Evelyn Simon',null,'7788778877');
insert into alluser values('harry','harry_t@yahoo.com','Harry Thompson','14, BB Ganguly St., Kolkata',null);
insert into alluser values('raja.h','rhasan@hotmail.com','Hasan Raja','AE34, Laketown, Kolkata','3222548522');
insert into alluser values('james5','james5@rediff.com','James Andy',null,null);
/*4f*/
update alluser set address='Flat 207, Block K,
New Town' where fullname='Evelyn Simon';
/*4g*/
update alluser set address='255, Jessore Road,
Barasat',phone='2200560087' where fullname='James Andy';


/*5*/
/*5a*/
alter table orderlist drop column amount;
/*5b*/
insert into orderlist values(default,'doro77','F02',3,TO_DATE('2022-Jan-02','YYYY-MM-DD'),'UPI');
insert into orderlist values(default,'harry','M01',1,TO_DATE('2022-Jan-04','YYYY-MM-DD'),'NETB');
insert into orderlist values(default,'harry','B02',1,TO_DATE('2022-Jan-04','YYYY-MM-DD'),'NETB');
insert into orderlist values(default,'doro77','F02',3,TO_DATE('2022-Jan-08','YYYY-MM-DD'),'UPI');
insert into orderlist values(default,'doro77','B02',2,TO_DATE('2022-Jan-08','YYYY-MM-DD'),'UPI');
insert into orderlist values(default,'raja.h','M02',3,TO_DATE('2022-Jan-18','YYYY-MM-DD'),'UPI');
insert into orderlist values(default,'eve80','M01',1,TO_DATE('2022-Jan-21','YYYY-MM-DD'),'NETB');
insert into orderlist values(default,'eve80','B02',1,TO_DATE('2022-Jan-21','YYYY-MM-DD'),'NETB');
insert into orderlist values(default,'doro77','M04',2,TO_DATE('2022-Feb-03','YYYY-MM-DD'),'CRDC');
insert into orderlist values(default,'james5','M03',1,TO_DATE('2022-Feb-03','YYYY-MM-DD'),'NETB');
insert into orderlist values(default,'james5','B02',2,TO_DATE('2022-Feb-03','YYYY-MM-DD'),'NETB');
insert into orderlist values(default,'doro77','M02',2,TO_DATE('2022-Feb-07','YYYY-MM-DD'),'UPI');
insert into orderlist values(default,'doro77','B01',4,TO_DATE('2022-Feb-09','YYYY-MM-DD'),'UPI');
insert into orderlist values(default,'james5','F02',2,TO_DATE('2022-Feb-12','YYYY-MM-DD'),'DEBT');
insert into orderlist values(default,'eve80','M04',10,TO_DATE('2022-Feb-20','YYYY-MM-DD'),'CRDC');
insert into orderlist values(default,'eve80','B01',6,TO_DATE('2022-Feb-20','YYYY-MM-DD'),'CRDC');
insert into orderlist values(default,'eve80','F02',2,TO_DATE('2022-Feb-20','YYYY-MM-DD'),'CRDC');
insert into orderlist values(default,'harry','M03',3,TO_DATE('2022-Feb-23','YYYY-MM-DD'),'NETB');
insert into orderlist values(default,'raja.h','F02',4,TO_DATE('2022-Feb-26','YYYY-MM-DD'),'DEBT');
insert into orderlist values(default,'harry','B02',2,TO_DATE('2022-Feb-28','YYYY-MM-DD'),'NETB');

/*6*/
/*6a*/
select itemname,unitprice as "Price" from item order by unitprice desc;
/*6b*/
select itemtype,MAX(unitprice) as "Maximum Price",MIN(unitprice) as "Minimum Price",AVG(unitprice) as "Average Price" from item group by itemtype;
/*6c*/
select itemname from item where itemname like 'C%';
/*6d*/
select itemcategory.catid,itemcategory.catname,item.itemname from item inner join itemcategory on item.itemtype=itemcategory.catid;
/*6e*/
select B.catname,B.itemname,A.unitprice from item A inner join itemcategory B on A.itemtype=B.catid order by A.unitprice asc;
/*6f*/
select * from item i1 where 2-1=(select count(distinct unitprice) from item i2 where i2.unitprice>i1.unitprice);
/*or*/
select * from item where unitprice in (select max(unitprice) from item where unitprice in (select unitprice from item minus (select MAX(unitprice) from item)))
/*6g*/
select itemname,descr from item,itemcategory where item.itemtype=itemcategory.catid;
/*6h*/
select catid,itemname,unitprice from item,itemcategory where item.itemtype=itemcategory.catid;
/*6i*/
select * from itemcategory where not exists(select itemtype from item where item.itemtype=itemcategory.catid);
/*or*/
select * from itemcategory where catid not in (select itemtype from item);
/*6j*/
select B.catname,A.itemname,A.unitprice from item A inner join itemcategory B on A.itemtype=B.catid where B.catname='Beverages';
/*6k*/
select B.catname,A.itemname,A.unitprice from item A inner join itemcategory B on A.itemtype=B.catid where B.catname='Main Course' order by A.unitprice desc;
/*6l*/
select item.itemname,alluser.fullname,orderlist.orderdate 
from orderlist 
inner join alluser on orderlist.userid=alluser.userid 
inner join item on orderlist.itemid=item.itemid
where item.itemtype='MNC' 
order by item.itemname;

/*7*/
/*7a*/
select fullname,address from alluser where address like '%Kolkata';
/*7b*/
select email,fullname from alluser where email like '%@gmail.com';
/*7c*/
select * from orderlist A inner join alluser B on A.userid = B.userid where B.fullname='Evelyn Simon';
/*or*/
select * from orderlist where userid = (select userid from alluser where fullname='Evelyn Simon');
/*7d*/
select A.itemname as "Unsold Items",B.catname from item A inner join itemcategory B on A.itemtype = B.catid where A.itemid in (select itemid from item where itemid not in (select itemid from orderlist));
/*7e*/
select A.itemid,B.itemname,SUM(A.qty) from orderlist A inner join item B on A.itemid = B.itemid group by A.itemid,B.itemname order by SUM(A.qty) desc;
/*7f*/
select A.itemid,B.itemname,SUM(B.unitprice*A.qty) as "Total cost" from orderlist A inner join item B on A.itemid = B.itemid group by A.itemid,B.itemname order by SUM(B.unitprice*A.qty) desc;
/*7g*/
select paymode,SUM(qty) from orderlist group by paymode order by SUM(qty) desc;
/*7h*/
select A.itemid,B.itemname,SUM(B.unitprice*A.qty) as "Total amount spent" from orderlist A inner join item B on A.itemid=B.itemid where userid = (select userid from alluser where fullname='Dorothy Jones') group by itemid,itemname;
/*7i*/
select C.fullname,A.userid,C.email from orderlist A 
inner join item B on A.itemid=B.itemid
inner join alluser C on A.userid=C.userid where B.itemtype = (select catid from itemcategory where catname='Main Course') and A.orderdate like '2022-01%';
/*7j*/
select C.fullname,A.userid,C.email from orderlist A 
inner join item B on A.itemid=B.itemid
inner join alluser C on A.userid=C.userid where A.paymode='NETB';
/*7k*/
/*a*/
select C.fullname,A.userid,B.itemname,A.qty from orderlist A 
inner join item B on A.itemid=B.itemid
inner join alluser C on A.userid=C.userid where A.qty>=3 group by C.fullname,A.userid,B.itemname,A.qty order by A.userid;
/*b*/
select C.fullname,A.userid,B.itemname,A.qty from orderlist A 
inner join item B on A.itemid=B.itemid
inner join alluser C on A.userid=C.userid where A.qty>=3 group by C.fullname,A.userid,B.itemname,A.qty order by A.qty desc;