For every course, return the names of the highest-scoring students (result schem
ID: 3890907 • Letter: F
Question
For every course, return the names of the highest-scoring students (result schema: course title and student name).
List all the students (sid and name) that are enrolled in courses for which they have taken the prerequisites.
Consider the following database schema and example instance: Course Student title Databases dept credits cid CS425 name dept sid 001 Alice CS 002 Bob EE 003 Carol CS 004 David PHYS CS CS595 Database Security CS EE VLSI Design EE 3 591 Microcomputers EE401 4 PHYS571 Radiation Physics PHYS 3 Enroll sid grade gradepoint cid CS425001A CS595 001B CS595 002A EE401 001 A EE401 002 B EE401 004 A 4.0 3.0 4.0 4.0 3.0 4.0 2.0 4.0 Prereq cid pi CS595 CS425 EE591 EE401 PHYS571 002 C PHYS571 004A Hints: Attributes shown with grey background form the primary key of a relation.Explanation / Answer
1)
SELECT E1.cid, S.name FROM Enroll E1, Student S WHERE E1.sid = S.sid AND NOT EXISTS SELECT * FROM Enroll E2 WHERE E2.cid = E1.cid AND E2.grade > E1.grade
;
2)
SELECT S.sid, S.NAME FROM ENROLL E1, STUDENT S, Prereq P WHERE E1.sid = S.sid AND E1.cid = P.cid GROUP BY S.sid;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.