LeetCode 1280: Students and Examinations

Explanation:

To solve this problem, we need to count the number of times each student attended each exam. We can achieve this by joining the Students, Subjects, and Examinations tables and then aggregating the data to count the attended exams for each student and subject combination.

Algorithm:

1. Join the Students, Subjects, and Examinations tables on student_id and subject_name.
2. Use GROUP BY to group the data by student_id, student_name, and subject_name.
3. Use COUNT to calculate the number of attended exams for each student and subject combination.
4. Handle cases where a student did not attend a particular exam by using LEFT JOIN and COALESCE to fill in 0 for missing attended exams.
5. Return the result table ordered by student_id and subject_name.

Time Complexity:

The time complexity of this solution is O(n), where n is the total number of records in the Examinations table.

Space Complexity:

The space complexity of this solution is O(n) to store the result table.

:

# Write your Java solution here
SELECT s.student_id, s.student_name, sub.subject_name, COALESCE(COUNT(e.subject_name), 0) AS attended_exams
FROM Students s
CROSS JOIN Subjects sub
LEFT JOIN Examinations e ON s.student_id = e.student_id AND sub.subject_name = e.subject_name
GROUP BY s.student_id, s.student_name, sub.subject_name
ORDER BY s.student_id, sub.subject_name;