Students and Examinations - Solution & Explanation

EasyDatabase14 min readAsked at: Amazon, Microsoft, Meta +5

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 Overview

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 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

C C++Java Python C#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

C C++Java Python C#JavaScript

C++

Java

Python

JavaScript

Complexity

Not applicable for this solution in C.

Try this approach in the editor →

Approach 5: Two Joins + Grouping

We can first join the Students table and the Subjects table to obtain all combinations of students and subjects, and then join the Examinations table with the condition of student_id and subject_name. This way, we can get the number of times each student has taken each subject's test. Finally, we can group by student_id and subject_name to count the number of times each student has taken each subject's test.

Code

MySQL

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.
Two Joins + Grouping	—

Detailed Complexity Analysis

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

Video Solution

Students and Examinations | Leetcode 1280 | Crack SQL Interviews in 50 Qs #mysql #leetcode • Learn With Chirag • 27,516 views views

Watch 9 more video solutions →

Frequently Asked Questions

Is Students and Examinations easy or hard?

Students and Examinations is classified as Easy. The challenge mainly tests understanding of SQL joins and aggregation rather than complex algorithms. Once you recognize the need to generate all student–subject pairs, the solution becomes straightforward.

Students and Examinations Python/Java solution

In Python or Java, iterate through the exam records and store counts in a dictionary or HashMap keyed by (student_id, subject). Then loop through every student and subject combination and output the stored count or 0 if the key does not exist. This runs in O(S × Sub + E) time.

What is the best approach for Students and Examinations?

The most efficient SQL approach uses a CROSS JOIN between Students and Subjects to generate every possible pair, followed by a LEFT JOIN with Examinations and a GROUP BY to count attendance. This guarantees missing combinations appear with count 0. The time complexity is roughly O(S × Sub + E), where S is the number of students, Sub is subjects, and E is exam records.

Is Students and Examinations asked at Google/Amazon/Meta?

Database aggregation and join problems like this frequently appear in SQL interviews at companies such as Amazon, Meta, and Google. While the exact question may vary, the core concept—generating combinations and counting matches with joins—is a common pattern.

What data structure is used in Students and Examinations?

In SQL solutions, relational joins and aggregation functions act as the primary mechanism instead of explicit data structures. In programming-language solutions, a hash map keyed by (student_id, subject_name) efficiently tracks exam counts with constant-time lookups.

What is the time complexity of Students and Examinations?

The typical SQL solution runs in O(S × Sub + E). Generating the student–subject matrix requires S × Sub rows, and counting exam records adds a pass over the Examinations table. Space overhead is minimal because aggregation happens directly in the query.

How to solve Students and Examinations in O(S × Sub + E)?

Create all student–subject pairs with a CROSS JOIN. Then LEFT JOIN the Examinations table using student_id and subject_name, and count matches with COUNT() grouped by student and subject. Missing matches produce NULL values, which aggregate to 0, ensuring complete coverage of combinations.

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%

Approaches5

Reading time14 min

Asked at

Amazon Microsoft Meta Accenture Cognizant

Practice this problem

Open in Editor

Problem Statement

Approach Overview

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

Approach 5: Two Joins + Grouping

Code

Complexity Comparison

Detailed Complexity Analysis

Video Solution

Frequently Asked Questions

Related Problems

Ready to solve this problem?

Problem Info

Table of Contents

Problem Statement

Approach Overview

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

Approach 5: Two Joins + Grouping

Code

Complexity Comparison

Detailed Complexity Analysis

Video Solution

Frequently Asked Questions

Related Problems

Ready to solve this problem?

Problem Info

Table of Contents