Watch 10 video solutions for Students and Examinations, a easy level problem involving Database. This walkthrough by Learn With Chirag has 27,516 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: Students
+---------------+---------+ | Column Name | Type | +---------------+---------+ | student_id | int | | student_name | varchar | +---------------+---------+ student_id is the primary key (column with unique values) for this table. Each row of this table contains the ID and the name of one student in the school.
Table: Subjects
+--------------+---------+ | Column Name | Type | +--------------+---------+ | subject_name | varchar | +--------------+---------+ subject_name is the primary key (column with unique values) for this table. Each row of this table contains the name of one subject in the school.
Table: Examinations
+--------------+---------+ | Column Name | Type | +--------------+---------+ | student_id | int | | subject_name | varchar | +--------------+---------+ There is no primary key (column with unique values) for this table. It may contain duplicates. Each student from the Students table takes every course from the Subjects table. Each row of this table indicates that a student with ID student_id attended the exam of subject_name.
Write a solution to find the number of times each student attended each exam.
Return the result table ordered by student_id and subject_name.
The result format is in the following example.
Example 1:
Input: Students table: +------------+--------------+ | student_id | student_name | +------------+--------------+ | 1 | Alice | | 2 | Bob | | 13 | John | | 6 | Alex | +------------+--------------+ Subjects table: +--------------+ | subject_name | +--------------+ | Math | | Physics | | Programming | +--------------+ Examinations table: +------------+--------------+ | student_id | subject_name | +------------+--------------+ | 1 | Math | | 1 | Physics | | 1 | Programming | | 2 | Programming | | 1 | Physics | | 1 | Math | | 13 | Math | | 13 | Programming | | 13 | Physics | | 2 | Math | | 1 | Math | +------------+--------------+ Output: +------------+--------------+--------------+----------------+ | student_id | student_name | subject_name | attended_exams | +------------+--------------+--------------+----------------+ | 1 | Alice | Math | 3 | | 1 | Alice | Physics | 2 | | 1 | Alice | Programming | 1 | | 2 | Bob | Math | 1 | | 2 | Bob | Physics | 0 | | 2 | Bob | Programming | 1 | | 6 | Alex | Math | 0 | | 6 | Alex | Physics | 0 | | 6 | Alex | Programming | 0 | | 13 | John | Math | 1 | | 13 | John | Physics | 1 | | 13 | John | Programming | 1 | +------------+--------------+--------------+----------------+ Explanation: The result table should contain all students and all subjects. Alice attended the Math exam 3 times, the Physics exam 2 times, and the Programming exam 1 time. Bob attended the Math exam 1 time, the Programming exam 1 time, and did not attend the Physics exam. Alex did not attend any exams. John attended the Math exam 1 time, the Physics exam 1 time, and the Programming exam 1 time.
Problem Overview: You are given three tables: Students, Subjects, and Examinations. The task is to report how many times each student attended each subject exam. Every student–subject pair must appear in the result, even if the student never took that exam, which means missing combinations should return a count of 0.
Approach 1: Cross Join and Count with Group By (O(S × Sub + E) time, O(1) extra space)
This is the most direct SQL solution. First generate every possible student–subject pair using a CROSS JOIN between the Students and Subjects tables. This guarantees the result includes combinations that may not exist in the Examinations table. Then perform a LEFT JOIN with Examinations and count matching rows using COUNT() grouped by student and subject. The key insight is that CROSS JOIN constructs the full matrix of combinations, while LEFT JOIN ensures missing exam records become zero counts. This approach is standard for database aggregation problems.
Approach 2: Nested Query with Union (O(S × Sub + E) time, O(S × Sub) space)
This strategy explicitly builds student–subject combinations in a nested query and then merges them with exam records. One query retrieves actual exam attendance counts grouped by student and subject. Another query generates all missing combinations and assigns them a count of 0. A UNION merges both datasets into the final result. The logic is slightly more complex than the join-based approach but useful when you want clearer separation between existing records and generated pairs.
Approach 3: Using a Hash Map or Dictionary (O(S × Sub + E) time, O(E) space)
Outside SQL environments, you can solve the problem with a hash map. First iterate through the Examinations list and build a dictionary keyed by (student_id, subject_name) to count occurrences. Next iterate over every student and subject combination and retrieve the count from the hash map, defaulting to 0 when the key is missing. Hash lookups run in constant time, which keeps the overall complexity linear relative to the number of exam records. This approach relies on a hash map for fast aggregation.
Approach 4: Sorting and Two-Pointer Technique (O(E log E + S × Sub) time, O(1) extra space)
If the examination records are sorted by student_id and subject_name, you can scan them using a two-pointer style traversal. The idea is to keep a pointer over the sorted exam list and count consecutive matches for each student–subject pair generated from the student and subject lists. Sorting enables grouped counting without extra data structures. This method demonstrates a common pattern used in two pointers or sequential scanning problems.
Recommended for interviews: The SQL CROSS JOIN + LEFT JOIN + GROUP BY solution is the expected answer. It shows you understand relational joins, aggregation, and how to handle missing relationships. Discussing the hash map version also helps demonstrate algorithmic thinking outside SQL environments.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Cross Join + Left Join + Group By | O(S × Sub + E) | O(1) | Best SQL approach when you must generate all student–subject combinations |
| Nested Query with Union | O(S × Sub + E) | O(S × Sub) | When separating existing exam records and missing pairs improves readability |
| Hash Map / Dictionary | O(S × Sub + E) | O(E) | Non-SQL environments where exam records are processed programmatically |
| Sorting + Two Pointers | O(E log E + S × Sub) | O(1) | When records are sorted or memory usage must stay minimal |
