Find the students (sid and name) that have taken at least one of the prerequisit

Question

Find the students (sid and name) that have taken at least one of the prerequisite(s) of course ‘CS595’ and got an ‘A’ grade in this prerequisite course.

Find all the ‘EE’ students (sid and name) that have taken all the courses oered by the ‘CS’ department.

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. Find the students (sid and name) that have taken at least one of the prerequisite(s) of course ‘CS595’ and got an ‘A’ grade in this prerequisite course.

Query:

WITH

RECURSIVE FULL(CID,PID) AS

(SELECT * FROM PREREQ)

UNION

(SELECT P.CID, F.PID

FROM PREREQ P, FULL F

WHERE P.PID=F.CID

AND F.CID='CS595')

SELECT S.NAME FROM STUDENT S, ENROLL E, FULL F

WHERE E.GRADE='A' AND E.CID=F.CID AND E.SID=S.SID

2. Find all the ‘EE’ students (sid and name) that have taken all the courses oered by the ‘CS’ department.

Query to fetch all such students:

SELECT S.NAME FROM STUDENT S

WHERE S.DEPT=’EE’

AND NOT EXISTS

SELECT * FROM COURSE C

WHERE C.DEPT=’CS’

AND NOT EXISTS

SELECT * FROM ENROLL E

WHERE E.CID=C.CID AND E.SID=S.SID

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

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