deeprun

Classes More Than 5 Students

 문제 설명

Create table If Not Exists Courses (student varchar(255), class varchar(255))
Truncate table Courses
insert into Courses (student, class) values ('A', 'Math')
insert into Courses (student, class) values ('B', 'English')
insert into Courses (student, class) values ('C', 'Math')
insert into Courses (student, class) values ('D', 'Biology')
insert into Courses (student, class) values ('E', 'Math')
insert into Courses (student, class) values ('F', 'Computer')
insert into Courses (student, class) values ('G', 'Math')
insert into Courses (student, class) values ('H', 'Math')
insert into Courses (student, class) values ('I', 'Math')

Write an SQL query to report all the classes that have at least five students.

Return the result table in any order.

The query result format is in the following example.

5명 이상의 학생이 있는 클래스 쿼리 확인

 입출력 예

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.

 Oracle Query

/* Write your PL/SQL query statement below */
select class 
from courses
group by class
having count(distinct student) > 4

* 참고 링크 : https://leetcode.com/problems/classes-more-than-5-students/discuss/927995/Oracle-Solution

• 5명 이상의 학생이 있는 class 쿼리— having절은 group by와 같이 사용될 수 있음having count(distinct student) > 4having절은 집계함수를 가지고 조건비교를 할 때 사용한다.
  • 참고 링크 : http://www.gurubee.net/lecture/1032
  집계함수 종류
  • count / sum / avg / max / min 등
  • 참고 링크 : https://life-with-coding.tistory.com/218
• distinct 중복값 처리 (5명 이상의 학생이 중복되는 경우를 having절로 카운트)
• (5명 이상의 학생이 있는 클래스를 찾는 것이니 class로 묶어줌
• group by class

 출처

https://leetcode.com/problems/classes-more-than-5-students/