Welcome to Subscribe On Youtube
3182. Find Top Scoring Students 🔒
Description
Table: students
+-+-+ \| student_id \| int \| \| name \| varchar \| \| major \| varchar \| +-+-+ \| Column Name \| Type \| +-+-+ course_id is the primary key (combination of columns with unique values) for this table. Each row of this table contains the course ID, course name, the number of credits for the course, and the major it belongs to.
Table: enrollments
+-+-+ \| student_id \| int \| \| course_id \| int \| \| semester \| varchar \| \| grade \| varchar \| +-+--+++ \| course_id \| name \| credits \| major \| +--+--+++
enrollments table:
++-+-+ \| 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 \| ++
Explanation:
- Alice (student_id 1) is a Computer Science major and has taken both "Algorithms" and "Data Structures", receiving an 'A' in both.
- 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.
- 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.
</div>
Solutions
Solution 1: Join Tables + Grouping
We can join the students table and courses table based on the major field, then left join the enrollments table to the resulting table, and finally group by student_id to filter out the students who meet the conditions.
-
# Write your MySQL query statement below SELECT student_id FROM students JOIN courses USING (major) LEFT JOIN enrollments USING (student_id, course_id) GROUP BY 1 HAVING SUM(grade = 'A') = COUNT(major) ORDER BY 1;