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

 +++--+-+-+
 \| 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;

All Problems

All Solutions