LeetCode MySQL-596. Classes More Than 5 Students

    LeetCode MySQL

    Write an SQL query to report all the classes that have at least five students.
    Return the result table in any order.

    寫一個 SQL 查詢有最少五個學生的班級。
    可任意排序結果。
    

    Table: Courses

    +-------------+---------+
    | Column Name | Type    |
    +-------------+---------+
    | student     | varchar |
    | class       | varchar |
    +-------------+---------+
    (student, class) is the primary key column for this table.
    Each row of this table indicates the name of a student and the class in which they are enrolled.
    

    Example 1:

    Input: 
    Courses table:
    +---------+----------+
    | student | class    |
    +---------+----------+
    | A       | Math     |
    | B       | English  |
    | C       | Math     |
    | D       | Biology  |
    | E       | Math     |
    | F       | Computer |
    | G       | Math     |
    | H       | Math     |
    | I       | Math     |
    +---------+----------+
    Output: 
    +---------+
    | class   |
    +---------+
    | Math    |
    +---------+
    Explanation: 
    - Math has 6 students, so we include it.
    - English has 1 student, so we do not include it.
    - Biology has 1 student, so we do not include it.
    - Computer has 1 student, so we do not include it.
    

    Solution:
    1. 選擇標題 class 來自 Courses 的 table。
    2. 以 class 為群組分類。
    3. 有在這個 class 群組中不重複的 student 次數大於等於 5。

    +----------+----------+
    | class    | count    |
    +----------+----------+
    | Biology  | 1        |
    | Computer | 1        |
    | English  | 1        |
    | Math*    | 6        |
    +----------+----------+
    

    Code.1:

    SELECT class FROM Courses
    	GROUP BY class
        HAVING COUNT(DISTINCT student) >= 5;
    

    newCourses