4EME3ui9

1create table Dept(dnum int, dname varchar(20),dloc varchar(10),PRIMARY KEY(dnum));
2
3create table Emp(eno int, ename varchar(15),job varchar(10),mgr int,hiredate date,sal int,comm int,deptno int,PRIMARY KEY(eno), FOREIGN KEY (mgr) REFERENCES Emp(eno),FOREIGN KEY (deptno) REFERENCES Dept(dnum));
4
5insert into dept values(10,'accounting', 'new york');
6insert into dept values(20,'research', 'dallas');
7insert into dept values(30,'sales', 'chicago');
8insert into dept values(40,'operations', 'boston');
9
10insert into emp values(7369,'smith','clerk',NULL,'17-DEC-80',800,NULL,20);
11insert into emp values(7499,'allen','salesman',NULL,'20-FEB-81',1600,300,30);
12insert into emp values(7521,'ward','salesman',NULL,'22-FEB-81',1250,500,30);
13
14
15insert into emp values(7566,'jones','manager',NULL,'02-APR-81',2975,NULL,20);
16insert into emp values(7654,'martin','salesman',NULL,'28-SEP-81',1250,1400,30);
17insert into emp values(7698,'blake','manager',NULL,'01-MAY-81',2850,NULL,30);
18
19insert into emp values(7782,'clark','manager',NULL,'09-JUN-81',2450,NULL,10);
20insert into emp values(7788,'scott','analyst',NULL,'19-APR-81',3000,NULL,20);
21insert into emp values(77839,'king','president',NULL,'17-NOV-81',5000,NULL,10);
22
23insert into emp values(7844,'turner','salesman',NULL,'08-SEP-81',1500,0,30);
24insert into emp values(7676,'adams','clerk',NULL,'23-MAY-81',1100,NULL,20);
25insert into emp values(7900,'james','clerk',NULL,'03-DEC-81',950,NULL,30);
26
27insert into emp values(7902,'ford','analyst',NULL,'03-DEC-81',3000,NULL,20);
28insert into emp values(7934,'miller','clerk',NULL,'23-JAN-81',1300,NULL,10);
29
30update emp set mgr = 7902 where eno = 7369;
31update emp set mgr = 7698 where eno = 7499;
32update emp set mgr = 7698 where eno = 7521;
33
34update emp set mgr = 7893 where eno = 7566; # didnt work
35update emp set mgr = 7698 where eno = 7654;
36update emp set mgr = 7839 where eno = 7698;
37
38update emp set mgr = 7893 where eno = 7566;
39
40
41select * from emp where sal > (select avg(sal) from emp);
42
43
44select * from dept where dnum not in (select deptno from dept,emp where dnum=deptno);
45
46(select dnum,dname from dept minus select deptno,dname from emp,dept where dnum = deptno)
47 
48select e1.eno,e1.ename,e1.mgr,e2.ename,e2.eno from emp e1,emp e2 where e1.mgr = e2.eno; 
49
50eval set soln
51select eno,ename,deptno from emp,dept where dnum=deptno minus
52select eno,ename,deptno from emp,dept where dnum=deptno and dname='sales';
53
54
55create table course(cid int,cname varchar(20),category varchar(20), PRIMARY KEY(cid));
56
57alter table course add credits int;
58
59select eno,ename from emp where deptno not in (select deptno from dept,emp where dloc = 'sales' and dnum=deptno );
60
61where deptno not in (select deptno from dept,emp where dept = dept.dnum and dname = 'sales')
62
63
64-- Query: find the emp's with mgr in sales
65-- Non-nested
66select e1.ename,e1.mgr,e2.eno from emp e1,emp e2 where e1.mgr = e2.eno and e2.deptno = 30;
67-- Nested
68select ename,mgr from emp e1 where exists (select * from emp e2,dept d where e1.mgr = e2.eno and e2.deptno = d.dnum and d.dname = 'sales');
69
70-- Query: find max min and avg of emp in dept 10
71select avg(sal), min(sal),max(sal) from emp,dept where deptno=dnum and deptno = 10;
72select deptno , avg(sal), min(sal),max(sal) from emp group by deptno; // given by sir, apparantly upper one has multiple duplicate rows
73
74-- Query: find total number of emp in dept 10 and 20 seperately 
75select deptno,count(eno) from emp where deptno = 10 or deptno = 20 group by deptno;
76
77-- Query: print total salary given to clerks
78select sum(sal) from emp where job = 'clerk' group by job;
79
80-- Query: for each job print job, number of emp and average salary
81select job,count(*),avg(sal) from emp group by job;
82
83-- Query: get eid,ename,dname for those whose names end with 'EN' or 'ER'
84select eno,ename,dname from emp,dept d where (deptno=d.dnum) and (ename like '%er' or ename like '%en'); //basically, without the brackets the or statement interfere
85
86select * from emp join dept on deptno=dnum;
87
88-- Query: get eid,ename,mnager name for emp in sales
89select e1.eno,e1.ename,e1.job,e1.mgr,e2.eno,e2.ename,e2.job from ((emp e1 join dept on deptno=dnum) join emp e2 on e1.mgr = e2.eno) where dname='sales';
90
91-- Query: for each dept print dnum,dname, and number of emp whose salary is greater than 100
92select d.dnum,d.dname,count(*) from dept d,emp e where d.dnum = e.deptno and e.sal > 1300 group by d.dnum,d.dname; // why group by d.dname?
93
94-- Query: print eid,ename for those who are managing at least one employee;
95select e.eno,e.ename from emp e where e.eno in (select e1.mgr from emp e1 where e1.mgr is not null);
96select e.eno,e.ename from emp e where e.eno exists (select * from emp e1 where e1.mgr=e.eno); //I have no clue
97
98
99-- Query: for each emp print eid,ename and the number of emp he manages(only for those with atleast one emp)
100select e2.ename,e2.eno,count(*) from emp e1,emp e2 where e1.mgr = e2.eno group by e2.eno,e2.ename;
101
102-- Query: get eid, ename,de not workign with sales dept;(can use minus)
103(select eno,ename,deptno from emp,dept where deptno=dnum) minus
104(select eno,ename,deptno from emp,dept where deptno=dnum and dname='sales');
105select e.eno,e.ename from emp e where not exists (select * from dept where e.deptno = dnum and dname = 'sales'); //yee, this one might not work
106
107
108
109-- New Table made:
110create table vendor(vid int, vname varchar(20),vloc varchar(20),primary key(vid));
111create table part(pid int, pname varchar(20),price int,primary key(pid));
112create table supply(pid int ,vid int, qty int,FOREIGN KEY (pid) REFERENCES part(pid),FOREIGN KEY (vid) REFERENCES vendor(vid),PRIMARY KEY(pid,vid));
113
114insert into part values(101,'scale',30);
115insert into part values(104,'sketch',10);
116insert into part values(105,'tape',15);
117insert into part values(107,'pad',160);
118insert into part values(110,'ink',25);
119insert into part values(112,'pin',5);
120insert into part values(124,'chalk',120);
121insert into part values(135,'paper',80);
122
123insert into vendor values(3,'crown','delhi');
124insert into vendor values(5,'topper','delhi');
125insert into vendor values(7,'avon','mumbai');
126insert into vendor values(11,'swan','hyderabad');
127insert into vendor values(13,'rally','chennai');
128insert into vendor values(15,'buddy','channai');
129
130
131insert into supply values(104,7,420);
132insert into supply values(104,13,380);
133insert into supply values(105,13,350);
134insert into supply values(107,5,500);
135insert into supply values(107,11,200);
136insert into supply values(107,7,10);
137insert into supply values(110,3,22);
138insert into supply values(110,5,35);
139insert into supply values(110,13,150);
140insert into supply values(110,15,20);
141insert into supply values(124,7,300);
142insert into supply values(124,11,34);
143insert into supply values(124,13,12);
144
145
146-- query: get pid,pname for those not supplied by any vendor
147With set:
148(select pid,pname from part) minus (select p.pid,p.pname from part p,supply s where p.pid = s.pid);
149With not in:
150select pid,pname from part where pid not in (select pid from supply);
151With exists:
152select pid,pname from part p where not exists (select * from supply s where p.pid=s.pid);
153
154
155-- query: get pid,pname which are supplied by all vendors from delhi (IMP)
156select p.pid,p.pname from part p where not exists (
157(select v.vid from vendor v where v.vloc='delhi') -- this always gives[3,5]
158 minus 
159 (select s.vid from supply s where s.pid = p.pid) --only if this returns [3,5] do we print
160);
161-- Also use count to do this
162
163-- Query: for Delhi based vendors if they supply atleast 3 parts
164select v.vid,v.vname from supply s, vendor v where v.vloc = 'delhi' and ((select count(*) from supply s where s.vid = v.vid)>1); 
165select v.pid,v.vname from vendor v where v.loc = 'delhi' and v.vid in (select s.vid from supply s group by )
166-- I have no clue, look it up;
167
168-- Query: get vid, vname for those not supplying any part with price greater than 100 (Evl)
169select vid,vname from vendor v where not exists (select * from  supply s,part p where s.pid = p.pid and v.vid = s.vid and p.price >100);
170--Query for each part get pid pname and number of vendors supplying it (Evl)
171select p.pid,p.pname,count(*) from part p,supply s where p.pid = s.pid group by p.pid,p.pname;
172select count(*) from part p, supply s;
173
174--Query: for each part get pid, pname and number of channai based vendors supplying it
175select p.pid,p.pname,count(*) from part p,supply s,vendor v where p.pid = s.pid and v.vid = s.vid and v.vloc = 'chennai' group by p.pid,p.pname;
176
177--Query: get vid and vname for those vendors supplying all parts with price greater than 100
178
179select v.vid,v.vname from vendor v where not exists ((select p.pid from part p where p.price >100) minus (select s.pid from supply s where s.vid = v.vid));
180
181--Query: get vid,vname if they are supplying a total quantity of 43 parts
182select v.vid, v.vname from vendor v, supply s group by v.vid, v.vname having sum(qty)>43;
183
184--Query get pid,pname for those supplied by atleast one vendor
185select distinct p.pid,p.pname from part p, supply s where p.pid = s.pid;
186select p.pid,p.pname from part p where p.pid in (select s.pid from supply s);
187select p.pid, p.pname from part p where exists (select * from supply s where s.pid=p.pid);
188
189--Query get pid,price and average price
190select p1.pid,p1.price,avg(p2.price) from part p1,part p2 group by p1.pid,p1.price;
191select pid,price,(select avg(price) from part) from part;
192select * from (select pid,pname from part) p,(select avg(price) from part) s; 
193select p.pid,p.price,s.* from (select pid,pname from part) p,(select avg(price) from part) s; 
194
195--Query get pid,pname which are supplied by all vendors from delhi (Same as one above [very imp])
196select p.pid,p.pname from part p where not exists ((select v.vid from vendor v where v.vloc='delhi')  minus  (select s.vid from supply s where s.pid = p.pid));
197select p.pid,p.pname from part p where (select count(*) from supply s,vendor v where s.vid=v.vid and v.vloc = 'delhi' ) = (select count(*) from vendor v where v.loc='delhi') --I have no idea what he tried to do here
198
199--Query get pid,pname for parts which are not being supplied from any vendor from delhi
200select p.pid, p.pname from part p where p.pid not in (select s.pid from supply s,vendor v where v.vid=s.vid and v.vloc='delhi'); 
201
202--Query get pid,pname for parts in supply which are not being supplied from any vendor from delhi
203
204--Query get vid and vname for those supplying either part tape or ink (no duplicates) (eval)
205select v.vid,v.vname from vendor v where v.vid in (select s.vid from supply s,part p where v.vid=s.vid and p.pid=s.pid and p.pname in ('tape','ink'));
206
207--Query get vid vname for those vendor not supplying ink (eval)
208select v.vid,v.vname from vendor v where v.vid not in (select s.vid from supply s,part p where p.pid=s.pid and p.pname='ink');
209
210--Query get vid,vname for those vendors not supplying both tape or ink
211select v.vid,v.vname from vendor v where not exists ( (select p.pid from part p where p.pname='tape' or p.pname='ink') minus (select s.pid from supply s where s.vid=v.vid));
212
213--Query get vid and vname for those vendors supplying all parts price greater than 100 [Imp]
214
215
216--Query for each part get pid,pname and total qty in supply
217select p.pid,p.pname,sum(qty) from part p, supply s where p.pid=s.pid group by p.pid,p.pname;
218
219--Query get vid and vname for those supplying only one part
220select v.vid, v.vname from vendor v, supply s where s.vid=v.vid group by v.vid, v.vname having count(*)=1;