students-and-examintions.sql (587B)
1 --Given a set of students, exams taken, and exams return 2 --the number of times each student has completed each exam (The number of attended_exams me be = 0) 3 4 SELECT Students.student_id, Students.student_name, 5 Subjects.subject_name, COUNT(Examinations.subject_name) as attended_exams 6 FROM Students 7 CROSS JOIN Subjects 8 LEFT JOIN Examinations 9 ON Examinations.subject_name = Subjects.subject_name 10 AND Examinations.student_id = Students.student_id 11 GROUP BY Students.student_id, Students.student_name, Subjects.subject_name 12 ORDER BY Students.student_id, Subjects.subject_name