Find Top Scoring Students - Problem

You are given three tables: students, courses, and enrollments.

Write a SQL query to find students who have:

  • Taken all courses offered in their major
  • Achieved a grade of 'A' in all these courses

Return the result table ordered by student_id in ascending order.

Table Schema

students
Column Name Type Description
student_id PK int Primary key, unique student identifier
name varchar Student's full name
major varchar Student's major field of study
Primary Key: student_id
courses
Column Name Type Description
course_id PK int Primary key, unique course identifier
name varchar Course name
credits int Number of credits for the course
major varchar Major that this course belongs to
Primary Key: course_id
enrollments
Column Name Type Description
student_id PK int Foreign key referencing students table
course_id PK int Foreign key referencing courses table
semester PK varchar Semester when course was taken
grade varchar Grade received in the course
Primary Key: (student_id, course_id, semester)

Input & Output

Example 1 — Complete Major Requirements
Input Tables:
students
student_id name major
1 Alice Computer Science
2 Bob Computer Science
3 Charlie Mathematics
4 David Mathematics
courses
course_id name credits major
101 Algorithms 3 Computer Science
102 Data Structures 3 Computer Science
103 Calculus 4 Mathematics
104 Linear Algebra 4 Mathematics
enrollments
student_id course_id semester grade
1 101 Fall 2023 A
1 102 Fall 2023 A
2 101 Fall 2023 B
2 102 Fall 2023 A
3 103 Fall 2023 A
3 104 Fall 2023 A
4 103 Fall 2023 A
4 104 Fall 2023 B
Output:
student_id
1
3
💡 Note:

Alice (ID 1) completed both CS courses with A grades. Charlie (ID 3) completed both Math courses with A grades. Bob and David didn't achieve A in all required courses.
Example 2 — Empty Result
Input Tables:
students
student_id name major
1 John Physics
courses
course_id name credits major
201 Mechanics 4 Physics
202 Thermodynamics 4 Physics
enrollments
student_id course_id semester grade
1 201 Spring 2023 A
1 202 Spring 2023 B
Output:
student_id
💡 Note:

John took both Physics courses but received a B in Thermodynamics, so he doesn't qualify as a top-scoring student.

Constraints

  • 1 ≤ student_id ≤ 1000
  • 1 ≤ course_id ≤ 1000
  • grade is one of ['A', 'B', 'C', 'D', 'F']
  • major names are consistent across tables

Visualization

Tap to expand
Find Top Scoring Students OverviewInput: Three Related TablesStudentsID: 1AliceCSID: 2BobCSID: 3CharlieMathEnrollments1→101A2→101B3→103ACourses101CS102CS103MathJOIN → Filter A Grades → GROUP BY → COUNT CoursesPerfect Studentsstudent_id1 (Alice)3 (Charlie)
Understanding the Visualization
1
Input Tables
Students, courses, and enrollment data
2
Join & Filter
Connect tables and filter A grades
3
Group & Verify
Count courses and verify completion
Key Takeaway
🎯 Key Insight: Use HAVING with subquery to verify students completed ALL major requirements with perfect grades

Related Problems

Asked in
Amazon 28 Google 22 Meta 15
28.5K Views
Medium Frequency
~18 min Avg. Time
842 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen