Grokking SQL for Tech Interviews
Ask Author
Back to course home

0% completed

Vote For New Content
11. Students Who Attended All Courses (Medium)
Table of Contents

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

Problem Statement

Table: Enrollment

This table records the enrollments of students in various courses. Each record includes a student ID and a course ID.

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| course_id    | int     |
+--------------+---------+
This table may contain duplicate rows.
student_id is not NULL.
course_id is a foreign key to the Course table.

Table: Course

This table lists all the courses available.

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| course_id    | int     |
+--------------+---------+
course_id is the primary key for this table.

Develop a solution to identify the student IDs from the Enrollment table who have enrolled in all the courses listed in the Course table.

Return the result table in any order.

Example

Input:

Enrollment table:
+------------+-----------+
| student_id | course_id |
+------------+-----------+
| 100        | 200       |
| 200        | 300       |
| 300        | 200       |
| 300        | 300       |
| 100        | 300       |
+------------+-----------+
Course table:
+-----------+
| course_id |
+-----------+
| 200       |
| 300       |
+-----------+

Output:

+------------+
| student_id |
+------------+
| 100        |
| 300        |
+------------+

Try It Yourself

MYSQL
MYSQL
. . . .

Solution

To identify students who have enrolled in all available courses, we need to compare the number of distinct courses each student has enrolled in with the total number of courses offered. This approach ensures that only those students who have enrolled in every course are selected.

  • Determine Total Number of Courses: Calculate the total count of distinct courses available in the Course table.
  • Count Student Enrollments: For each student, count the number of distinct courses they have enrolled in from the Enrollment table.
  • Compare and Select Eligible Students: Select students whose count of enrolled courses matches the total number of courses, indicating full enrollment.
  • Order the Results: Present the final list of student IDs in any order as specified.

SQL Query

SELECT student_id
FROM Enrollment
GROUP BY student_id
HAVING COUNT(DISTINCT course_id) = (SELECT COUNT(*) FROM Course)
ORDER BY student_id

Step-by-Step Approach

Step 1: Determine Total Number of Courses

Calculate the total number of distinct courses available in the Course table to establish the benchmark for full enrollment.

SQL Query:

SELECT COUNT(*) AS total_courses
FROM Course;

Explanation:

  • SELECT COUNT(*) AS total_courses:
    • Counts all the rows in the Course table, effectively determining the total number of courses available.

Output After Step 1:

+--------------+
| total_courses|
+--------------+
| 2            |
+--------------+

Step 2: Count Student Enrollments

For each student, count the number of distinct courses they have enrolled in to assess whether they meet the full enrollment criteria.

SQL Query:

SELECT student_id, COUNT(DISTINCT course_id) AS enrolled_courses
FROM Enrollment
GROUP BY student_id;

Explanation:

  • SELECT student_id, COUNT(DISTINCT course_id) AS enrolled_courses:
    • Retrieves each student_id along with the count of unique course_ids they have enrolled in.
  • FROM Enrollment:
    • Specifies the Enrollment table as the data source.
  • GROUP BY student_id:
    • Aggregates the data by student_id to perform the count for each individual student.

Output After Step 2:

+------------+-----------------+
| student_id | enrolled_courses|
+------------+-----------------+
| 100        | 2               |
| 200        | 1               |
| 300        | 2               |
+------------+-----------------+

Step 3: Compare and Select Eligible Students

Identify students whose number of enrolled courses matches the total number of available courses, indicating that they have enrolled in all courses.

SQL Query:

SELECT student_id
FROM Enrollment
GROUP BY student_id
HAVING COUNT(DISTINCT course_id) = (SELECT COUNT(*) FROM Course);

Explanation:

  • SELECT student_id:
    • Chooses the student_id of students who meet the enrollment criteria.
  • FROM Enrollment:
    • Utilizes the Enrollment table as the data source.
  • GROUP BY student_id:
    • Groups the records by student_id to perform aggregate calculations for each student.
  • HAVING COUNT(DISTINCT course_id) = (SELECT COUNT(*) FROM Course):

    • Filters the grouped students to include only those whose count of distinct enrolled courses equals the total number of courses available.

    • COUNT(DISTINCT course_id): Counts the unique courses each student has enrolled in.

    • (SELECT COUNT(*) FROM Course): Subquery that retrieves the total number of courses from the Course table.

Final Output:

+------------+
| student_id |
+------------+
| 100        |
| 300        |
+------------+

.....

.....

.....

Like the course? Get enrolled and start learning!

Table of Contents

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible