Tugas 1
Tugas individual
Jika berlaku curang, maka nilai=E
Student(snum: integer, sname: string, major: string, level: string, age: integer)
Class(name: string, meets at: string, room: string, fid: integer)
Enrolled(snum: integer, cname: string)
Faculty(fid: integer, fname: string, deptid: integer)
Tulislah query-query berikut ini dengan SQL. Tidak ada duplikat yang harus dicetak pada seluruh jawaban.
Soal :
- Cari seluruh nama junior (level JR) yang mengikuti kelas yang diajar oleh fakultas informatika
- Hitung umur siswa yang paling tua baik yang mengikuti jurusan Sejarah atau yang mengikuti kelas yang diajar oleh fakultas informatika
- Cari nama dari seluruh kelas yang mengadakan pertemuan pada ruang R128 atau memiliki siswa lebih dari lima
- Cari nama dari seluruh siswa yang mengikuti dua buah kelas yang diadakan pada jam yang sama.
- Cari nama fakultas yang kelasnya telah dilakukan proses belajar mengajar
- Cari nama fakultas dimana terdapat kelas yang mahasiswanya berjumlah kurang dari lima
- Cetak level dan umur rata-rata umur siswa untuk masing-masing level
- Cetak level dan umur rata-rata umur siswa untuk masing-masing level kecuali level JR
- Untuk masing-masing fakultas yang telah mengajar hanya kelas pada ruang R218, cetak nama fakultas dan jumlah kelas yang telah diajar.
- Cari nama siswa yang mengikuti kelas yang jumlahnya maksimal
- Cari nama siswa yang tidak mengikuti kelas apapun
- Untuk masing-masing nilai umur yang muncul pada student, cari nilai level yang paling sering muncul. Misalnya jika terdapat lebih banyak siswa dengan level FR berumur 18 tahun dibandingkan dengan siswa yang berumur 18 dan memiliki level SR, JR, atau SO maka anda harus mencetak pasangan (18,FR)
Jawab:
1. select distinct s.sname
from student s, class c, enrolled e, faculty f
where s.snum = e.snum and e.cname = c.name and c.fid = f.fid and
f.fname = ‘Informatika and s.level = ‘JR’
2. select max(s.age)
from student s
where (s.major = ‘history’)
or s.snum in (select e.snum
from class c, enrolled e, faculty f
where e.cname = c.name and c.fid = f.fid
and f.fname = ‘Informatika)
3. select c.name
from class c
where c.room = ‘r128’
or c.name in (select e.cname
from enrolled e
group by e.cname
having count (*) >= 5)
4. select distinct s.sname
from student s
where s.snum in (select e1.snum
from enrolled e1, enrolled e2, class c1, class c2
where e1.snum = e2.snum and e1.cname <> e2.cname
and e1.cname = c1.name
and e2.cname = c2.name and c1.meets at = c2.meets at)
5. select distinct f.fname
from faculty f
where not exists (( select *
from class c )
except
(selectc1.room
from class c1
where c1.fid = f.fid ))
6. select distinct f.fname
from faculty f
where 5 > (select count (e.snum)
from class c, enrolled e
where c.name = e.cname
and c.fid = f.fid)
7. select s.level, avg(s.age)
from student s
group by s.level
8. select s.level, avg(s.age)
from student s
where s.level <> ‘JR’
group by s.level
9. select f.fname, count(*) as coursecount
from faculty f, class c
where f.fid = c.fid
group by f.fid, f.fname
having every ( c.room = ‘R128’ )
10. select distinct s.sname
from student s
where s.snum in (select e.snum
from enrolled e
group by e.snum
having count (*) >= all (select count (*)
from enrolled e2
group by e2.snum ))
11. select distinct s.sname
from student s
where s.snum not in (select e.snum
from enrolled e )
12. select s.age, s.level
from student s
group by s.age, s.level,
having s.level in (select s1.level
from student s1
where s1.age = s.age
group by s1.level, s1.age
having count (*) >= all (select count (*)
from student s2
where s1.age = s2.age
group by s2.level, s2.age))
Assignment 2
This is an individual assignment, that is, no group submissions are allowed.
Cheating Policy: If you are caught cheating, your grade is E.
Suppliers(sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)
The Catalog relation lists the prices charged for parts by Suppliers. Write the following
queries in SQL:
1. Find the pnames of parts for which there is some supplier.
2. Find the snames of suppliers who supply every part.
3. Find the snames of suppliers who supply every red part.
4. Find the pnames of parts supplied by Acme Widget Suppliers and no one else.
5. Find the sids of suppliers who charge more for some part than the average cost of
that part (averaged over all the suppliers who supply that part).
6. For each part, find the sname of the supplier who charges the most for that part.
7. Find the sids of suppliers who supply only red parts.
8. Find the sids of suppliers who supply a red part and a green part.
9. Find the sids of suppliers who supply a red part or a green part.
10. For every supplier that only supplies green parts, print the name of the supplier and the total number of parts that she supplies.
11. For every supplier that supplies a green part and a red part, print the name and price of the most expensive part that she supplies.
Answer:
1. select distinct p.pname
from parts p, catalog c
where p.pid = c.pid
2. select s.sname
from suppliers s
where not exists (( select p.pid
from parts p )
except
( select c.pid
from catalog c
where c.sid = s.sid ))
3. select s.sname
from suppliers s
where not exists (( select p.pid
from parts p
where p.color = ‘red’ )
except
( select c.pid
from catalog c, parts p
where c.sid = s.sid and
c.pid = p.pid and p.color = ‘red’ ))
4. select p.pname
from parts p, catalog c, suppliers s
where p.pid = c.pid and c.sid = s.sid
and s.sname = ‘acme widget suppliers’
and not exists ( select *
from catalog c1, suppliers s1
where p.pid = c1.pid and c1.sid = s1.sid and
s1.sname <> ‘acme widget suppliers’ )
5. select distinct c.sid
from catalog c
where c.cost > ( select avg (c1.cost)
from catalog c1
where c1.pid = c.pid )
6. select p.pid, s.sname
from parts p, suppliers s, catalog c
where c.pid = p.pid
and c.sid = s.sid
and c.cost = (select max (c1.cost)
from catalog c1
where c1.pid = p.pid)
7. select distinct c.sid
from catalog c
where not exists ( select *
from parts p
where p.pid = c.pid and p.color <> ‘red’ )
8. select distinct c.sid
from catalog c, parts p
where c.pid = p.pid and p.color = ‘red’
intersect
select distinct c1.sid
from catalog c1, parts p1
where c1.pid = p1.pid and p1.color = ‘green’
9. select distinct c.sid
from catalog c, parts p
where c.pid = p.pid and p.color = ‘red’
union
select distinct c1.sid
from catalog c1, parts p1
where c1.pid = p1.pid and p1.color = ‘green’
10. select s.sname, count(*) as partcount
from suppliers s, parts p, catalog c
where p.pid = c.pid and c.sid = s.sid
group by s.sname, s.sid
having every (p.color=’green’)
11. select s.sname, max(c.cost) as maxcost
from suppliers s, parts p, catalog c
where p.pid = c.pid and c.sid = s.sid
group by s.sname, s.sid
having any ( p.color=’green’ ) and any ( p.color = ’red’ )
PBDR IF 33 07 (Dosen :Intan Nurma)
