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 INPUT TABLES students id name major_id 1 Alice 101 2 Bob 101 courses id name major_id C1 Math 101 C2 Physics 101 enrollments student course grade 1 C1 A 1 C2 A 2 C1 B ALGORITHM STEPS 1 Count Major Courses Count total courses per major major_id: 101 --> 2 courses 2 Filter A Grades Keep only grade = 'A' records Student 1: C1(A), C2(A) Student 2: (filtered out) 3 Count A's Per Student Group by student, count A's Student 1: 2 A grades Student 2: 0 A grades 4 Compare Counts A count = total major courses? Student 1: 2 = 2 [OK] Student 2: 0 != 2 [FAIL] FINAL RESULT Top Scoring Students 1 ID Alice Major: 101 All A's in All Courses Math: A, Physics: A 2 ID Bob Not all A grades EXCLUDED OUTPUT student_id name 1 Alice Key Insight: Use HAVING clause to compare COUNT of A grades with total courses in student's major. Join students with courses (same major), then with enrollments. Filter grade='A', group by student, and keep only those where A-count equals total major courses. ORDER BY student_id. TutorialsPoint - Find Top Scoring Students | Optimal Solution

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