Students and Examinations | Leetcode 1280 | Crack SQL Interviews in 50 Qs

This comprehensive study note covers LeetCode 1280 ("Students and Examinations"), exploring how to combine multiple tables using Cross Joins and Left Joins, aggregate data using COUNT(), and structure complex relational queries for SQL interviews.

1. Problem Statement & Schema Overview

We are given three database tables that track students, available subjects, and individual exam attendance records. Our objective is to find out how many times each student attended the exam for each subject available in the school.

Table Definitions

1. Students Table:

   • student_id (Primary Key)
   • student_name
   • Description: Contains the unique ID and name of each student.

2. Subjects Table:

   • subject_name (Primary Key)
   • Description: Contains the name of every subject/course taught in the school.

3. Examinations Table:

   • student_id
   • subject_name
   • Description: Contains records of exams attended by students. This table has no primary key and can contain duplicate rows (e.g., if a student took or logged an exam entry multiple times).

Expected Output Format

The resulting table must list every combination of student and subject, ordered by student_id and subject_name, along with the total count of exams attended:

2. Core Mental Model & Real-Life Analogy

Real-Life Analogy: The School Report Card Matrix

Imagine preparing a blank report card grid for every student in a school. Every student must be evaluated against every single subject offered by the school, regardless of whether they skipped an exam or took it multiple times.

If a student never took an exam for a specific subject, their attendance count for that subject must still appear as 0 in our final report card.

Why Standard Joins Fail Here

If we used an INNER JOIN directly on the Examinations table, students who missed exams or subjects they never attended would completely disappear from the result set. To guarantee that every student is paired with every subject, we must first generate a complete Cartesian product before matching attendance records.

3. Naive vs. Optimal Join Strategy

To solve this problem cleanly, we cannot rely on a single join. We must build our dataset in two distinct phases:

Comparative Strategy Table

4. Step-by-Step SQL Query Evolution

Let's build the SQL query step by step, analyzing each clause.

Step 4.1: Generating the Cartesian Product (CROSS JOIN)

We pair every student with every subject using CROSS JOIN.

SELECT s.student_id, s.student_name, sub.subject_name
FROM Students s
CROSS JOIN Subjects sub;

Step 4.2: Attaching Exam Records (LEFT JOIN)

We attach the Examinations table using a LEFT JOIN so that students who haven't taken specific exams are retained with NULL examination fields.

SELECT s.student_id, s.student_name, sub.subject_name, e.subject_name AS exam_subject
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;

Step 4.3: Adding Aggregation (GROUP BY & COUNT)

Because a student might take an exam multiple times (resulting in multiple rows in the Examinations table), we group by the student and subject identifiers and use COUNT() to tally occurrences.

SELECT 
    s.student_id, 
    s.student_name, 
    sub.subject_name,
    COUNT(e.subject_name) 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;

Note: Notice that COUNT(e.subject_name) correctly evaluates to 0 when the LEFT JOIN produces NULL values for students who never attended a particular subject's exam. Using COUNT(*) in SQL Server/MySQL can sometimes be tricky with outer joins because it counts rows rather than non-null column values.

5. Complete Production-Ready SQL Query

Here is the final, fully optimized SQL query matching the problem requirements and sorting the output correctly.

SELECT 
    s.student_id, 
    s.student_name, 
    sub.subject_name,
    COUNT(e.subject_name) 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;

6. Execution Flow Walkthrough

Let's trace how the database engine executes this query:

1. Table Aliasing:
   • Students is aliased as s
   • Subjects is aliased as sub
   • Examinations is aliased as e
2. Cross Product (CROSS JOIN):
   • If there are 4 students and 3 subjects, the cross join generates $4 \times 3 = 12$ distinct rows representing every possible student-subject combination.
3. Left Join Matching (LEFT JOIN ... ON ...):
   • The engine checks the Examinations table (e) for rows matching both student_id and subject_name.
   • If Alice took Math 3 times, those 3 exam records link to Alice's Math row. If Bob never took Physics, a NULL row is attached to Bob's Physics row.
4. Grouping & Counting (GROUP BY + COUNT):
   • Rows are consolidated by (student_id, student_name, subject_name).
   • COUNT(e.subject_name) tallies how many examination records matched each group (e.g., Alice's Math count = 3, Bob's Physics count = 0).
5. Sorting (ORDER BY):
   • The final result set is sorted in ascending order first by student_id and then by subject_name.

7. Summary and Key Takeaways

• [Problem Architecture]: When a problem requires pairing every entity from Table A with every entity from Table B regardless of activity, start with a CROSS JOIN.
• [Preserving Missing Records]: Use a LEFT JOIN after your cross product to ensure inactive or zero-activity records are not filtered out of your report.
• [Handling NULL Counts]: Passing a specific column name into COUNT(e.subject_name) ensures that NULL entries from un-attended exams evaluate to 0 rather than throwing off aggregate tallies.
• [Group By Rule]: Any non-aggregated column in your SELECT clause (s.student_id, s.student_name, sub.subject_name) must be included in your GROUP BY clause.
• [Ordering Results]: Always check the sorting requirements in interview prompts; here, sorting by student_id and subject_name guarantees deterministic output formatting.
• [Scalability Mindset]: While CROSS JOIN creates large intermediate sets ($N \times M$), combining it cleanly with indexed columns on Examinations ensures robust query performance in production environments.