Given the following schema:
Student(Id,Name)
Transcript(StudId,CourseName,Semester,Grade)
Formulate the following query in SQL: Create a list of all students (Id, Name) and, for each
student, list the average grade for the courses taken in the S2002 semester.
Note that there can be students who did not take any courses in S2002. For these, the average
grade should be listed as 0.
Solution:
We first create a view which augments Transcript with rows that enroll every student into a
NULL course with the grade of 0. Therefore, students who did not take anything in semester
'S2002' will have the average grade of 0 for that semester.
CREATE VIEW TranscriptView AS (
( SELECT * FROM Transcript)
UNION
( SELECT S.Id,NULL,'S2002',0
FROM Student S)
WHERE S.Id NOT IN ( SELECT T.StudId
FROM Transcript T
WHERE T.Semester = 'S2002') )
)
SELECT S.Id, S.Name, AVG(T.Grade)
FROM Student S, TranscriptView T
WHERE S.Id = T.StudId AND T.Semester = 'S2002'
GROUP BY S.Id
You might also like to view...
What question should be asked at the end of an interview?
A) "Are there any answers that you would like to change, now that you have had time to think things over?" B) "Others in your organization have provided conflicting information. Do we have your permission to explain your point of view?" C) "Do you mind if we check your answers out with others in future interviews?" D) "Is there anything we haven't touched on that you think is important for me to know?"
Which monitor works without the use of a backlight?
A) LED B) OLED C) LCD D) RGB
________ is the program within Windows that displays the files and folders on your computer
Fill in the blank(s) with correct word
File ________ uses a storage system similar to what you would use in a file cabinet
Fill in the blank(s) with correct word