Consider the following schema: Transcript(StudId, CrsCode, Semester, Grade) Teac

Question

Consider the following schema:

Transcript(StudId, CrsCode, Semester, Grade)

Teach(ProfId, CrsCode, Semester)

Professor(ProfId, ProfName, Dept)

Write the following queries in relational algebra or pseudo-SQL:

(a) Find all students who have received a grade of “A” in CS317 from Professor Kelliher, showing their id number and the semester in which they received the grade.

(b) Find the Ids of all students who have taken a course from each professor in the the CS department. (Relational algebra has the / operator for division. For pseudo-SQL, you may assume that there is a DIV operator for division.)

Explanation / Answer

(a)

SELECT T.StudId,T.Semester
FROM Transcript T, Teach TH, Professor P
WHERE T.CrsCode=TH.CrsCode
AND T.Semester=TH.Semester
AND TH.ProfId=P.ProfID
AND T.Grade='A'
AND T.CrsCode='CS317'
AND P.ProfName='Professor Kelliher'

(b)

SELECT T.StudId
FROM Transcript T, Teach TH, Professor P
WHERE T.CrsCode=TH.CrsCode
AND T.Semester=TH.Semester
AND TH.ProfId=P.ProfID
AND P.Dept='CS'
  

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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