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;