Relational Alegbra NOT in sql Statements Given the University Schema above, spec
ID: 3847074 • Letter: R
Question
Relational Alegbra NOT in sql Statements Given the University Schema above, specify the following queries using relational algebra. a) Retrieve the name and Transcript of each senior student (class = 5) majoring in COSC. Transcript includes course name, course number, credit hours, semester, year, and grade for each course completed by the student. b) Retrieve the names and major departments of all straight A students (students who have a grade of A in all their courses) c) Retrieve the names and major departments of all students who do not have any grade of A in any of their courses.Explanation / Answer
a)
SELECT Name, CourseName, C.CourseNumber, CreditHours, Semester, Year, Grade
FROM STUDENT ST, COURSE C, SECTION S, GRADE_REPORT G
WHERE Class=5 AND Major='COSC' AND ST.StudentNumber=G.StudentNumber AND G.SectionIdentifier=S.SectionIdentifier AND S.CourseNumber=C.CourseNumber.
b)
SELECT Name, Major
FROM STUDENT
WHERE NOT EXISTS ( SELECT *
FROM GRADE_REPORT
WHERE StudentNumber= STUDENT.StudentNumber AND NOT(Grade='A'))
c)
SELECT Name, Major
FROM STUDENT
WHERE NOT EXISTS ( SELECT *
FROM GRADE_REPORT
WHERE StudentNumber= STUDENT.StudentNumber AND Grade='A' )