Welcome to Subscribe On Youtube
3188. Find Top Scoring Students II ðŸ”’
Description
Table: students
+++ \ student_id \ int \ \ name \ varchar \ \ major \ varchar \ +++ \ Column Name \ Type \ +++ (student_id, course_id, semester) is the primary key (combination of columns with unique values) for this table. Each row contains the student ID, course ID, semester, and grade received.
Write a solution to find the students who meet the following criteria:
 Have taken all mandatory courses and at least two elective courses offered in their major.
 Achieved a grade of A in all mandatory courses and at least B in elective courses.
 Maintained an average
GPA
of at least2.5
across all their courses (including those outside their major).
Return the result table ordered by student_id
in ascending order.
Example:
Input:
students table:
++++ \ student_id \ name \ major \ ++++ \ 1 \ Alice \ Computer Science \ \ 2 \ Bob \ Computer Science \ \ 3 \ Charlie \ Mathematics \ \ 4 \ David \ Mathematics \ ++++
courses table:
++++++ \ 101 \ Algorithms \ 3 \ Computer Science \ yes \ \ 102 \ Data Structures \ 3 \ Computer Science \ yes \ \ 103 \ Calculus \ 4 \ Mathematics \ yes \ \ 104 \ Linear Algebra \ 4 \ Mathematics \ yes \ \ 105 \ Machine Learning \ 3 \ Computer Science \ no \ \ 106 \ Probability \ 3 \ Mathematics \ no \ \ 107 \ Operating Systems \ 3 \ Computer Science \ no \ \ 108 \ Statistics \ 3 \ Mathematics \ no \ +++++++++++++
Explanation:
 Alice (student_id 1) is a Computer Science major and has taken both Algorithms and Data Structures, receiving an A in both. She has also taken Machine Learning and Operating Systems as electives, receiving an A and B respectively.
 Bob (student_id 2) is a Computer Science major but did not receive an A in all required courses.
 Charlie (student_id 3) is a Mathematics major and has taken both Calculus and Linear Algebra, receiving an A in both. He has also taken Probability and Statistics as electives, receiving an A and B respectively.
 David (student_id 4) is a Mathematics major but did not receive an A in all required courses.
Note: Output table is ordered by student_id in ascending order.
Solutions
Solution 1: Joining + Grouping + Conditional Filtering
First, we filter out students with an average GPA greater than or equal to 2.5 and record them in table T
.
Next, we join the T
table with the students
table based on student_id
, then join with the courses
table based on major
, and finally perform a left join with the enrollments
table based on student_id
and course_id
.
After that, we group by student ID, use the HAVING
clause to filter out students who meet the conditions, and finally sort by student ID.

# Write your MySQL query statement below WITH T AS ( SELECT student_id FROM enrollments GROUP BY 1 HAVING AVG(GPA) >= 2.5 ) SELECT student_id FROM T JOIN students USING (student_id) JOIN courses USING (major) LEFT JOIN enrollments USING (student_id, course_id) GROUP BY 1 HAVING SUM(mandatory = 'yes' AND grade = 'A') = SUM(mandatory = 'yes') AND SUM(mandatory = 'no' AND grade IS NOT NULL) = SUM(mandatory = 'no' AND grade IN ('A', 'B')) AND SUM(mandatory = 'no' AND grade IS NOT NULL) >= 2 ORDER BY 1;