Students and Examinations - Solution & Explanation

EasyDatabase11 min read

Problem Statement

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.

Approach 1: Cross Join and Count with Group By

This approach involves joining the Students and Subjects tables to generate all combinations of students and subjects. Then, we use a left join with the Examinations table to count how many times each student attended each exam. Group By is used to aggregate and count the occurrences, ensuring that all combinations are accounted for.

The solution begins with a cross join between the Students and Subjects tables to list every student-subject combination. Then, a left join is performed with the Examinations table on both student_id and subject_name to account for the attendance. We count these occurrences with COUNT(e.student_id) and group the results by student and subject. Finally, we order the results by student_id and subject_name.

Code

SQL

Complexity

Time Complexity: O(N * M + E), where N is the number of students, M is the number of subjects, and E is the number of examination records.
Space Complexity: O(N * M), for storing the cross join results before joining with Examinations.

Try this approach in the editor →

Approach 2: Nested Query with Union

This approach uses nested queries to first produce all combinations of students and subjects. The inner query selects results from the Examinations table and the outer query ensures all zero-attendance cases are captured properly using a union operation with defaults.

Here, we perform a cross join between Students and Subjects to list all student-subject pairs. A nested query inside the left join counts the occurrences of each student-subject appearing in the Examinations table. COALESCE is used to replace NULL with 0 for cases where a student did not attend a particular subject. Finally, results are ordered by student_id and subject_name.

Code

SQL

Complexity

Time Complexity: O(N * M + E), where N is the number of students, M is the number of subjects, and E is the number of examination records.
Space Complexity: O(N * M), for storing the resulting dataset.

Try this approach in the editor →

Approach 3: Approach 1: Using a Hash Map or Dictionary

This approach involves using a hash map (also called a dictionary in some languages) to keep track of the elements we've seen so far and their counts or indices. This allows us to efficiently find the solution in linear time.

This solution iterates through the array and uses a hash map (implemented with a simple array for space efficiency in this specific example) to store indices of the numbers. For each number, it checks if the complement exists in the map.

Code

CC++JavaPythonC#JavaScript

C++

Java

Python

JavaScript

Complexity

Time Complexity: O(n).
Space Complexity: O(n).

Try this approach in the editor →

Approach 4: Approach 2: Sorting and Two-Pointer Technique

This approach involves first sorting the array and then using a two-pointer technique to find the two numbers that sum to the target. This can be more space-efficient but requires sorting, which takes O(n log n) time.

Sorting followed by two-pointer technique is not practical as it changes the indices of elements, which is crucial for providing the correct solution indices.

Code

CC++JavaPythonC#JavaScript

C++

Java

Python

JavaScript

Complexity

Not applicable for this solution in C.

Try this approach in the editor →

Complexity Comparison

Approach	Complexity
Cross Join and Count with Group By	Time Complexity: O(N * M + E), where N is the number of students, M is the number of subjects, and E is the number of examination records. Space Complexity: O(N * M), for storing the cross join results before joining with Examinations.
Nested Query with Union	Time Complexity: O(N * M + E), where N is the number of students, M is the number of subjects, and E is the number of examination records. Space Complexity: O(N * M), for storing the resulting dataset.
Approach 1: Using a Hash Map or Dictionary	Time Complexity: O(n). Space Complexity: O(n).
Approach 2: Sorting and Two-Pointer Technique	Not applicable for this solution in C.

Video Solution

LeetCode 1280 Interview SQL Question with Detailed Explanation | Practice SQL • Everyday Data Science • 16,070 views views

Watch 9 more video solutions →

Ready to solve this problem?

Practice Students and Examinations with our built-in code editor and test cases.

Practice on FleetCode

Combine Two Tables

Second Highest Salary

Problem Info

DifficultyEasy

Acceptance59.6%

Approaches4

Reading time11 min

Practice this problem

Open in Editor

Students and Examinations - Solution & Explanation

Problem Statement

Approach 1: Cross Join and Count with Group By

Code

Complexity

Approach 2: Nested Query with Union

Code

Complexity

Approach 3: Approach 1: Using a Hash Map or Dictionary

Code

Complexity

Approach 4: Approach 2: Sorting and Two-Pointer Technique

Code

Complexity

Complexity Comparison

Video Solution

Ready to solve this problem?

Problem Info

Table of Contents

Students and Examinations - Solution & Explanation

Problem Statement

Approach 1: Cross Join and Count with Group By

Code

Complexity

Approach 2: Nested Query with Union

Code

Complexity

Approach 3: Approach 1: Using a Hash Map or Dictionary

Code

Complexity

Approach 4: Approach 2: Sorting and Two-Pointer Technique

Code

Complexity

Complexity Comparison

Video Solution

Ready to solve this problem?

Problem Info

Table of Contents

Problem Statement

Approach 1: Cross Join and Count with Group By

Code

Complexity

Approach 2: Nested Query with Union

Code

Complexity

Approach 3: Approach 1: Using a Hash Map or Dictionary

Code

Complexity

Approach 4: Approach 2: Sorting and Two-Pointer Technique

Code

Complexity

Complexity Comparison

Video Solution

Related Problems

Ready to solve this problem?

Problem Info

Table of Contents

Problem Statement

Approach 1: Cross Join and Count with Group By

Code

Complexity

Approach 2: Nested Query with Union

Code

Complexity

Approach 3: Approach 1: Using a Hash Map or Dictionary

Code

Complexity

Approach 4: Approach 2: Sorting and Two-Pointer Technique

Code

Complexity

Complexity Comparison

Video Solution

Related Problems

Ready to solve this problem?

Problem Info

Table of Contents