Skip to content

3188. Find Top Scoring Students II πŸ”’

Description

Table: students

+-------------+----------+
| Column Name | Type     | 
+-------------+----------+
| student_id  | int      |
| name        | varchar  |
| major       | varchar  |
+-------------+----------+
student_id is the primary key for this table. 
Each row contains the student ID, student name, and their major.

Table: courses

+-------------+-------------------+
| Column Name | Type              |       
+-------------+-------------------+
| course_id   | int               |    
| name        | varchar           |      
| credits     | int               |           
| major       | varchar           |       
| mandatory   | enum              |      
+-------------+-------------------+
course_id is the primary key for this table. 
mandatory is an enum type of ('Yes', 'No').
Each row contains the course ID, course name, credits, major it belongs to, and whether the course is mandatory.

Table: enrollments

+-------------+----------+
| Column Name | Type     | 
+-------------+----------+
| student_id  | int      |
| course_id   | int      |
| semester    | varchar  |
| grade       | varchar  |
| GPA         | decimal  | 
+-------------+----------+
(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 leastΒ 2.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:

 +-----------+-------------------+---------+------------------+----------+
 | course_id | name              | credits | major            | mandatory|
 +-----------+-------------------+---------+------------------+----------+
 | 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       |
 +-----------+-------------------+---------+------------------+----------+

enrollments table:

 +------------+-----------+-------------+-------+-----+
 | student_id | course_id | semester    | grade | GPA |
 +------------+-----------+-------------+-------+-----+
 | 1          | 101       | Fall 2023   | A     | 4.0 |
 | 1          | 102       | Spring 2023 | A     | 4.0 |
 | 1          | 105       | Spring 2023 | A     | 4.0 |
 | 1          | 107       | Fall 2023   | B     | 3.5 |
 | 2          | 101       | Fall 2023   | A     | 4.0 |
 | 2          | 102       | Spring 2023 | B     | 3.0 |
 | 3          | 103       | Fall 2023   | A     | 4.0 |
 | 3          | 104       | Spring 2023 | A     | 4.0 |
 | 3          | 106       | Spring 2023 | A     | 4.0 |
 | 3          | 108       | Fall 2023   | B     | 3.5 |
 | 4          | 103       | Fall 2023   | B     | 3.0 |
 | 4          | 104       | Spring 2023 | B     | 3.0 |
 +------------+-----------+-------------+-------+-----+

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. 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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
# 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;

Comments