rm
rm question_1
rm
create table j
(jno varchar2(5) not null primary key,
jname varchar2(10) not null,
city varchar2(10) default ' ');
load data
infile *
into table j
fields terminated by ',' optionally enclosed by '"'
(jno,jname,city)
begindata
j1,sorter,paris
j2,punch,rome
j3,reader,athens
j4,console,athens
j5,collator,london
j6,terminal,oslo
j7,tape,london
create table p
(pno varchar2(5) not null primary key,
pname varchar2(10) not null,
color varchar2(10) default ' ',
weight number(5,0) default null,
city varchar2(10) default ' ');
load data
infile *
into table p
fields terminated by ',' optionally enclosed by '"'
(pno,pname,color,weight,city)
begindata
p1,nut,red,12,london
p2,bolt,green,17,paris
p3,screw,blue,17,rome
p4,screw,red,14,london
p5,cam,blue,12,paris
p6,cog,red,19,london
create table s
(sno varchar2(5) not null primary key,
sname varchar2(10) not null,
status number(5,0) default null,
city varchar2(10) default ' ');
load data
infile *
into table s
fields terminated by ','optionally enclosed by '"'
(sno,sname,status,city)
begindata
s1,smith,20,london
s2,jones,10,paris
s3,blake,30,paris
s4,clark,20,london
s5,adams,30,athens
create table spj
(sno varchar2(5) not null,
pno varchar2(5) not null,
jno varchar2(5) not null,
qty number(5,0) default null,
primary key(sno,pno,jno));
load data
infile *
into table spj
fields terminated by ',' optionally enclosed by '"'
(sno,pno,jno,qty)
begindata
s1,p1,j1,200
s1,p1,j4,700
s2,p3,j1,400
s2,p3,j2,200
s2,p3,j3,200
s2,p3,j4,500
s2,p3,j5,600
s2,p3,j6,400
s2,p3,j7,800
s2,p5,j2,100
s3,p3,j1,200
s3,p4,j2,500
s4,p6,j3,300
s4,p6,j7,300
s5,p2,j2,200
s5,p2,j4,100
s5,p5,j5,500
s5,p5,j7,100
s5,p6,j2,200
s5,p1,j4,100
s5,p3,j4,200
s5,p4,j4,800
s5,p5,j4,400
s5,p6,j4,500
rem
rem question_2
rem
create view project_in_london (jno,jname,city)
as select jno,jname,city
from j
where city='london';
rem
rem question_3
rem
create view sp (sno,pno)
as select distinct sno,pno
from spj;
rem
rem question_4
rem
create view project_city (jno,city)
as select distinct j.jno,j.city
from j,spj
where j.jno=spj.jno
and spj.sno= 's1' or spj.pno= 'p1';
rem
rem question_5
rem
create view supplier_part (sno,pno)
as select distinct spj.sno,spj.pno
from p,s,spj
where p.pno=spj.pno and s.sno=spj.sno
and s.city<>p.city;
rem
spool simple_q.lst
rem question_6
rem
select distinct j.*,spj.qty,s.*,p.*
from j,spj,s,p
where j.jno=spj.jno and spj.pno=p.pno and spj.sno=s.sno
order by j.jno;
rem
rem question_7
rem
select distinct j.*,spj.qty,s.*,p.*
from j,spj,s,p
where j.jno=spj.jno and spj.sno=s.sno and p.pno=spj.pno
and j.city='london'
order by j.jno;
rem
rem question_8
rem
select distinct spj.sno
from spj
where jno='j1';
rem
rem question_9
rem
select *
from spj
where qty>=300 and qty<=750;
rem
rem question_10
rem
select distinct color,city
from p;
rem
rem question_11
rem
select *
from spj
where not qty is null;
rem
rem question_12
rem
select distinct jno,city
from j
where city like '_o%';
spool off
rm
rm output on simple queries from q6 to q12
rm
JNO JNAME CITY QTY SNO SNAME STATUS CITY
----- ---------- ---------- ---------- ----- ---------- ---------- ----------
PNO PNAME COLOR WEIGHT CITY
----- ---------- ---------- ---------- ----------
j1 sorter paris 200 s1 smith 20 london
p1 nut red 12 london
j1 sorter paris 200 s3 blake 30 paris
p3 screw blue 17 rome
j1 sorter paris 400 s2 jones 10 paris
p3 screw blue 17 rome
JNO JNAME CITY QTY SNO SNAME STATUS CITY
----- ---------- ---------- ---------- ----- ---------- ---------- ----------
PNO PNAME COLOR WEIGHT CITY
----- ---------- ---------- ---------- ----------
j2 punch rome 100 s2 jones 10 paris
p5 cam blue 12 paris
j2 punch rome 200 s2 jones 10 paris
p3 screw blue 17 rome
j2 punch rome 200 s5 adams 30 athens
p2 bolt green 17 paris
JNO JNAME CITY QTY SNO SNAME STATUS CITY
----- ---------- ---------- ---------- ----- ---------- ---------- ----------
PNO PNAME COLOR WEIGHT CITY
----- ---------- ---------- ---------- ----------
j2 punch rome 200 s5 adams 30 athens
p6 cog red 19 london
j2 punch rome 500 s3 blake 30 paris
p4 screw red 14 london
j3 reader athens 200 s2 jones 10 paris
p3 screw blue 17 rome
JNO JNAME CITY QTY SNO SNAME STATUS CITY
----- ---------- ---------- ---------- ----- ---------- ---------- ----------
PNO PNAME COLOR WEIGHT CITY
----- ---------- ---------- ---------- ----------
j3 reader athens 300 s4 clark 20 london
p6 cog red 19 london
j4 console athens 100 s5 adams 30 athens
p1 nut red 12 london
j4 console athens 100 s5 adams 30 athens
p2 bolt green 17 paris
JNO JNAME CITY QTY SNO SNAME STATUS CITY
----- ---------- ---------- ---------- ----- ---------- ---------- ----------
PNO PNAME COLOR WEIGHT CITY
----- ---------- ---------- ---------- ----------
j4 console athens 200 s5 adams 30 athens
p3 screw blue 17 rome
j4 console athens 400 s5 adams 30 athens
p5 cam blue 12 paris
j4 console athens 500 s2 jones 10 paris
p3 screw blue 17 rome
JNO JNAME CITY QTY SNO SNAME STATUS CITY
----- ---------- ---------- ---------- ----- ---------- ---------- ----------
PNO PNAME COLOR WEIGHT CITY
----- ---------- ---------- ---------- ----------
j4 console athens 500 s5 adams 30 athens
p6 cog red 19 london
j4 console athens 700 s1 smith 20 london
p1 nut red 12 london
j4 console athens 800 s5 adams 30 athens
p4 screw red 14 london
JNO JNAME CITY QTY SNO SNAME STATUS CITY
----- ---------- ---------- ---------- ----- ---------- ---------- ----------
PNO PNAME COLOR WEIGHT CITY
----- ---------- ---------- ---------- ----------
j5 collator london 500 s5 adams 30 athens
p5 cam blue 12 paris
j5 collator london 600 s2 jones 10 paris
p3 screw blue 17 rome
j6 terminal oslo 400 s2 jones 10 paris
p3 screw blue 17 rome
JNO JNAME CITY QTY SNO SNAME STATUS CITY
----- ---------- ---------- ---------- ----- ---------- ---------- ----------
PNO PNAME COLOR WEIGHT CITY
----- ---------- ---------- ---------- ----------
j7 tape london 100 s5 adams 30 athens
p5 cam blue 12 paris
j7 tape london 300 s4 clark 20 london
p6 cog red 19 london
j7 tape london 800 s2 jones 10 paris
p3 screw blue 17 rome
24 rows selected.
JNO JNAME CITY QTY SNO SNAME STATUS CITY
----- ---------- ---------- ---------- ----- ---------- ---------- ----------
PNO PNAME COLOR WEIGHT CITY
----- ---------- ---------- ---------- ----------
j5 collator london 500 s5 adams 30 athens
p5 cam blue 12 paris
j5 collator london 600 s2 jones 10 paris
p3 screw blue 17 rome
j7 tape london 100 s5 adams 30 athens
p5 cam blue 12 paris
JNO JNAME CITY QTY SNO SNAME STATUS CITY
----- ---------- ---------- ---------- ----- ---------- ---------- ----------
PNO PNAME COLOR WEIGHT CITY
----- ---------- ---------- ---------- ----------
j7 tape london 300 s4 clark 20 london
p6 cog red 19 london
j7 tape london 800 s2 jones 10 paris
p3 screw blue 17 rome
SNO
-----
s1
s2
s3
SNO PNO JNO QTY
----- ----- ----- ----------
s1 p1 j4 700
s2 p3 j1 400
s2 p3 j4 500
s2 p3 j5 600
s2 p3 j6 400
s3 p4 j2 500
s4 p6 j3 300
s4 p6 j7 300
s5 p5 j5 500
s5 p5 j4 400
s5 p6 j4 500
11 rows selected.
COLOR CITY
---------- ----------
blue paris
blue rome
green paris
red london
SNO PNO JNO QTY
----- ----- ----- ----------
s1 p1 j1 200
s1 p1 j4 700
s2 p3 j1 400
s2 p3 j2 200
s2 p3 j3 200
s2 p3 j4 500
s2 p3 j5 600
s2 p3 j6 400
s2 p3 j7 800
s2 p5 j2 100
s3 p3 j1 200
SNO PNO JNO QTY
----- ----- ----- ----------
s3 p4 j2 500
s4 p6 j3 300
s4 p6 j7 300
s5 p2 j2 200
s5 p2 j4 100
s5 p5 j5 500
s5 p5 j7 100
s5 p6 j2 200
s5 p1 j4 100
s5 p3 j4 200
s5 p4 j4 800
SNO PNO JNO QTY
----- ----- ----- ----------
s5 p5 j4 400
s5 p6 j4 500
24 rows selected.
JNO CITY
----- ----------
j2 rome
j5 london
j7 london
rem
spool joins.lst
rem question_13
rem
select distinct s.sno, p.pno, j.jno
from s, p, j, spj
where s.sno=spj.sno and p.pno=spj.pno and j.jno=spj.jno
and j.city=p.city and p.city=s.city;
rem
rem question_14
rem
select distinct s.sno, p.pno, j.jno
from s,p,j,spj
where s.sno=spj.sno and p.pno=spj.pno and j.jno=spj.jno
and not (s.city=p.city and p.city=j.city);
rem
rem question_15
rem
select distinct s.sno, p.pno, j.jno
from s,p,j,spj
where s.sno=spj.sno and p.pno=spj.pno and j.jno=spj.jno
and not (s.city=p.city or p.city=j.city or s.city=j.city);
rem
rem question_16
rem
select distinct spj.pno
from spj,s
where spj.sno=s.sno
and s.city='london';
rem
rem question_17
rem
select distinct spj.pno
from s,spj,j
where s.sno=spj.sno and j.jno=spj.jno
and s.city='london' and j.city='london';
rem
rem question_18
rem
select distinct s.city,j.city
from s,j,spj
where s.sno=spj.sno and j.jno=spj.jno;
rem
rem question_19
rem
select distinct spj.pno
from s,spj,j
where s.sno=spj.sno and j.jno=spj.jno
and s.city=j.city;
rem
rem question_20
rem
select distinct j.jno
from s, j, spj
where s.sno=spj.sno and j.jno=spj.jno
and j.city<>s.city;
rem
rem question_21
rem
select distinct spj.pno, spjx.pno
from spj, spj spjx
where spj.pno>spjx.pno and spj.sno=spjx.sno;
spool off
rem
rem output on joins from q13 to q21
rem
SNO PNO JNO
----- ----- -----
s4 p6 j7
SNO PNO JNO
----- ----- -----
s1 p1 j1
s1 p1 j4
s2 p3 j1
s2 p3 j2
s2 p3 j3
s2 p3 j4
s2 p3 j5
s2 p3 j6
s2 p3 j7
s2 p5 j2
s3 p3 j1
SNO PNO JNO
----- ----- -----
s3 p4 j2
s4 p6 j3
s5 p1 j4
s5 p2 j2
s5 p2 j4
s5 p3 j4
s5 p4 j4
s5 p5 j4
s5 p5 j5
s5 p5 j7
s5 p6 j2
SNO PNO JNO
----- ----- -----
s5 p6 j4
23 rows selected.
SNO PNO JNO
----- ----- -----
s2 p3 j3
s2 p3 j4
s2 p3 j5
s2 p3 j6
s2 p3 j7
s3 p4 j2
s5 p2 j2
s5 p5 j5
s5 p5 j7
s5 p6 j2
10 rows selected.
PNO
-----
p1
p6
PNO
-----
p6
CITY CITY
---------- ----------
athens athens
athens london
athens rome
london athens
london london
london paris
paris athens
paris london
paris oslo
paris paris
paris rome
11 rows selected.
PNO
-----
p1
p2
p3
p4
p5
p6
6 rows selected.
JNO
-----
j1
j2
j3
j4
j5
j6
j7
7 rows selected.
PNO PNO
----- -----
p2 p1
p3 p1
p3 p2
p4 p1
p4 p2
p4 p3
p5 p1
p5 p2
p5 p3
p5 p4
p6 p1
PNO PNO
----- -----
p6 p2
p6 p3
p6 p4
p6 p5
15 rows selected.
rem
spool subq.lst
rem question_22
rem
select distinct j.jno
from j
where j.jno in (select spj.jno
from spj
where spj.sno='s1');
rem
rem question_23
rem
select distinct p.color
from p
where p.pno in (select spj.pno
from spj
where spj.sno='s1');
rem
rem question_24
rem
select distinct spj.pno
from spj
where spj.jno in (select j.jno
from j
where j.city='london');
rem
rem question_25
rem
select distinct spj.jno
from spj
where spj.pno in (select spj.pno
from spj
where spj.sno='s1');
rem
rem question_26
rem
select distinct spj.sno
from spj
where spj.pno in (select spj.pno
from spj
where spj.sno in (select spj.sno
from spj
where spj.pno in (select p.pno
from p
where p.color='red')));
rem
rem question_27
rem
select distinct s.sno
from s
where s.status < (select s.status
from s
where s.sno='s1');
rem
rem question_28
rem
select distinct spj.sno
from spj
where spj.pno= 'p1' and spj.qty > (select avg(spjx.qty)
from spj spjx
where spj.jno=spjx.jno and
spjx.pno='p1');
rem
rem question_29
rem
select j.jno
from j
where not exists
(select *
from spj
where spj.jno=j.jno
and spj.pno in (select p.pno
from p
where p.color= 'red')
and spj.sno in (select s.sno
from s
where s.city= 'london'));
rem
rem question_30
rem
select distinct spjx.jno
from spj spjx
where not exists
(select *
from spj spjy
where spjy.jno=spjx.jno
and spjy.sno<>'s1');
rem
rem question_31
rem
select distinct spjx.pno
from spj spjx
where not exists
(select *
from j
where j.city='london'
and not exists
(select *
from spj spjy
where spjy.pno=spjx.pno
and spjy.jno=j.jno));
rem
rem question_32
rem
select distinct spjx.sno
from spj spjx, p
where exists
(select p.pno
from spj spjy
where not exists
(select j.jno
from j
where not exists
(select *
from spj spjz
where spjz.sno=spjx.sno
and spjz.pno=spjy.pno
and spjz.jno=j.jno)));
rem
rem question_33
rem
select distinct spjx.jno
from spj spjx
where not exists
(select spjy.pno
from spj spjy
where spjy.sno='s1'
and not exists
(select *
from spj spjz
where spjz.pno=spjy.pno
and spjz.jno=spjx.jno));
spool off
rem
rem output on subqueries from q22 to q33
rem
JNO
-----
j1
j4
COLOR
----------
red
PNO
-----
p3
p5
p6
JNO
-----
j1
j4
SNO
-----
s1
s2
s3
s4
s5
SNO
-----
s2
SNO
-----
s1
JNO
-----
j2
j5
j6
no rows selected
PNO
-----
p3
p5
SNO
-----
s2
JNO
-----
j1
j4
rem
spool builtin.lst
rem question_34
rem
select count (distinct jno)
from spj
where sno='s1';
rem
rem question_35
rem
select sum(qty)
from spj
where pno='p1' and sno='s1';
rem
rem question_36
rem
select distinct pno,jno,sum(qty)
from spj
group by pno,jno;
rem
rem question_37
rem
select distinct jno
from j
where city= (select min(city)
from j);
rem
rem question_38
rem
select distinct spj.jno
from spj
where spj.pno='p1'
group by spj.jno having avg(qty) > (select max(qty)
from spj
where spj.jno='j1');
rem
rem question_39.sql
rem
select distinct spj.sno
from spj
where spj.pno='p1' and spj.qty > (select avg(spjx.qty)
from spj spjx
where spjx.pno='p1' and
spjx.jno=spj.jno);
rem
rem question_40
rem
select s.city
from s
union (select p.city
from p
union
select j.city
from j);
spool off
rem
rem output on builtin functions and union from q34 to q40
rem
COUNT(DISTINCTJNO)
------------------
2
SUM(QTY)
----------
900
PNO JNO SUM(QTY)
----- ----- ----------
p1 j1 200
p1 j4 800
p2 j2 200
p2 j4 100
p3 j1 600
p3 j2 200
p3 j3 200
p3 j4 700
p3 j5 600
p3 j6 400
p3 j7 800
PNO JNO SUM(QTY)
----- ----- ----------
p4 j2 500
p4 j4 800
p5 j2 100
p5 j4 400
p5 j5 500
p5 j7 100
p6 j2 200
p6 j3 300
p6 j4 500
p6 j7 300
21 rows selected.
JNO
-----
j3
j4
no rows selected
SNO
-----
s1
CITY
----------
athens
london
oslo
paris
rome
rem
rem question_41
rem
update p set color='orange'
where color='red';
rem
rem question_42
rem
delete
from j
where not exists
(select *
from spj
where spj.jno=j.jno);
rem
rem question_43
rem
update spj set qty=qty*1.1
where pno in (select pno
from p
where color='red');
rem
rem question_44
rem
delete
from spj
where spj.jno in (select j.jno
from j
where j.city='rome');
rem
rem question_45
rem
insert into s (sno,sname,city)
values ('s10','white','new york');
rem
rem question_46
rem
create table x (pno varchar2(5) not null primary key);
rem
rem question_46
rem
insert into x (pno)
select distinct spj.pno
from spj,s,j
where spj.sno=s.sno and spj.jno=j.jno
and (s.city='london' or j.city='london');
rem
rem question_47
rem
create table y (jno varchar2(5) not null primary key);
rem
rem question_47
rem
insert into y (jno)
select distinct spj.jno
from spj,j,s
where spj.jno=j.jno and spj.sno=s.sno
and (j.city='london' or s.city='london');
rem
rem question_48
rem
update s set status=status+10
where status < (select status
from s
where sno='s4');
rem
rem answer to question_49 see question_1
spool SQL.lst
rem question_50
rem
select distinct pno
from spj
where exists
(select *
from j
where j.jno=spj.jno
and j.city='london');
rem
rem question_51
rem
select distinct spj.jno
from spj
where exists
(select *
from spj spjx
where spj.pno=spjx.pno
and spjx.sno='s1');
spool off
rem
rem output on SQL for q50 and q51
rem
PNO
-----
p3
p5
p6
JNO
-----
j1
j4
rem
rem result of running question_52
rem
COLOR
----------
blue
green
red
Answers to question_53 to question_62:
The query expressions to question_55, question_57, question_58, question_59, question_60, question_61,
question_62 are illegal.
The legal ones are question_53, question_54, question_56. The translated equivalents are as follows:
question_53
select sno, pno, max(qty), min(qty), avg(qty)
from spj
group by sno, pno having sum(qty)>50;
question_54
select sno, pno, max(qty), min(qty), avg(qty)
from spj
where sno<> ‘s1’
group by sno, pno having sum(qty)>50;
question_56
select max(qty)-min(qty), sno, pno
from spj
where sno= ‘s1’ and pno= ‘p1’
group by sno, pno having sum(qty)>50;