uestion-4 The relational database schema of Question-3 is reproduced here. COURS

Question

uestion-4 The relational database schema of Question-3 is reproduced here. COURSE (NUMBER, TITLE, ENROLLED) RANK-1 (CNUMBER, R1-SSN) RANK-2 (CNUMBER, R2-SSN) TALLEST (CNUMBER, TALL-SSN) STUDENT (SSN, NAME, MAJOR, SEX, GPA) Formulate the following queries in soL. (30] (a) List the course numbers in which "Jack Hernandez" is the first ranked student. You are not allowed to use any JOIN operation and your answer must be in alphabetical order of the course titles two top ranked students number of courses where she was ranked first in class, and the Average number of students (b) For every course that enrolls at least 20 students, list the Course title, and the names of its (c) For each student that ranked first in at least three courses, specify the student's SSN, the enrolled in those classes (d) List the Course number and the top ranked student's SSN for all courses where the top ranked student is also the tallest in class. You are not allowed to use the WHERE clause and the JOIN operation in your answer

Explanation / Answer

Please note that there is no records given for the tables so it is not possible to provide sample output. Below are the answers:

a.
select number from course where number in (select cnumber from rank-1 where r1-ssn in (select ssn from student where name = 'Jack Hernandez')) order by title;

b.
select '1' as rank,c.title,s.name from course c
join rank-1 r1
on c.number=r1.cnumber
join student s
on r1.r1-ssn=s.ssn
where c.number in (
select cnumber from
(select cnumber,count(rnk) cnt from
(select cnumber,r1-ssn rnk from rank-1
union
select cnumber,r2-ssn rnk from rank-2) course_all
group by cnumber) a
where cnt >=20)
union
select '2' as rank,c.title,s.name from course c
join rank-2 r2
on c.number=r2.cnumber
join student s
on r2.r2-ssn=s.ssn
where c.number in (
select cnumber from
(select cnumber,count(rnk) cnt from
(select cnumber,r1-ssn rnk from rank-1
union
select cnumber,r2-ssn rnk from rank-2) course_all
group by cnumber) a
where cnt >=20)

c.
select s.ssn,x.cnt,x.avg_enrl
from
(select a.r1-ssn,a.cnt,avg(c.enrolled) avg_enrl
from
(select r1.r1-ssn,r1.cnt,rnk1.cnumber
from
(select r1-ssn,count(cnumber) cnt
from rank-1
group by r1-ssn
having count(cnumber) >=3) r1
join rank-1 rnk1
on r1.r1-ssn=rnk1.r1-ssn) a
join course c
on a.cnumber=c.number
group by a.r1-ssn,a.cnt) x
join student s
on x.r1-ssn=s.ssn

d.
select cnumber,r1-ssn
from rank-1
intersect
select cnumber,tall-ssn
from tallest

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.