Write the following queries in SQL. No duplicates should be printed for any answ
ID: 3826256 • Letter: W
Question
Write the following queries in SQL. No duplicates should be printed for any answer.
Consider the following relations: Student(snum: integer, sname: string, major: string, level: string, age: integer)
Class(name: string, time: TIME, room: string, fid: integer)
Enrolled(snum: integer, cname: string)
Faculty(fid: integer, fname: string, deptid: integer)
The meaning of these relations is straightforward; for example, Enrolled has one record per student-class pair such that the student is enrolled in the class.
Find the major in which the most students have more than one class with a given professor.
Find all pairs of students taking the same courses.
Find all pairs of students taking courses from the same professors.
For each major, find the student with the largest gap in their schedule (time between two classes).
Explanation / Answer
SELECT DISTINCT S.sname
FROM Student S, Enrolled E, Class C, Faculty F
WHERE
S.snum = E.snum AND
E.cname = C.cname AND
C.fid = F.fid AND
F.fname = 'Prof. Harshith' AND
S.slevel = 'JR'
;
SELECT DISTINCT C.cname
FROM Class C
WHERE
C.room = 'R128' OR
C.cname IN (
SELECT E.cname
FROM Enrolled E
GROUP BY E.cname
HAVING COUNT(*) >= 5
)
;
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.cname AND
E2.cname = C2.cname AND
C1.meets_at = C2.meets_at
)
;
SELECT DISTINCT F.fname
FROM Faculty F
WHERE NOT EXISTS (
SELECT *
FROM Class C
WHERE (C.room) NOT IN (
SELECT C1.room FROM Class C1
WHERE C1.fid = F.fid
)
)
;
SELECT DISTINCT F.fname
FROM Faculty F
WHERE 5 > (
SELECT COUNT(E.snum)
FROM Class C, Enrolled E
WHERE
C.cname = E.cname AND
C.fid = F.fid
)
;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.