Question

The head of school would like to know the performance of students in a set of CSE subjects. Subjects in this set have their subject codes starting with “COMP90” and are offered in every major semester (i.e., S1 and S2) in a given period (from 2003 (inclusive) to 2012 (inclusive)). To evaluate the performance of students, the head of school requested to know the PASS rate in every major semester in the given period. The PASS rate is defined as the number of students who have got PASS (Course_enrolments.mark >= 50) / number of students who have actually received a mark (i.e. Course_enrolments.mark >= 0). Define an SQL view Q12(code,name,year,s1_ps_rate,s2_ps_rate) that gives a list of subjects. Each tuple in the view should contain the following: ⚫ the subject code (Subject.code field) ⚫ the subject name (Subject.name field) ⚫ the year (Semesters.year field in the format of the last 2 digits (i.e., 12 for 2012)) ⚫ semester 1 PASS rate as numeric (4,2) ⚫ semester 2 PASS rate as numeric (4,2) Note: • We only consider the students who receive a mark for the course taken. You may use Course_enrolments.mark >= 0 to retrieve a list of valid students. • Use numeric (4,2) to restrict the precision of the ratios, and be careful about typecasting between integers and reals. • If in some semesters there exists no students with a mark for a course, just output the pass rate as null in this case.

Answer 1

SQL VIEW

The SQL view can be done as-

CREATE VIEW Student_pass_rate AS

SELECT code, name,

RIGHT(YEAR(year) , 2) yy ,

CAST(s1_ps_rate AS decimal(4,2)),

CAST(s2_ps_rate AS decimal(4,2))

FROM Course_enrolments

WHERE code = "COMP90" AND Course_enrolment.mark >= 0

The view is created for Student