Classes More Than 5 Students
문제 설명
Table: Courses
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')
+-------------+---------+
| 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.
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절은 집계함수를 가지고 조건비교를 할 때 사용한다.
집계함수 종류
- count / sum / avg / max / min 등
- 참고 링크 : https://life-with-coding.tistory.com/218
- distinct 중복값 처리 (5명 이상의 학생이 중복되는 경우를 having절로 카운트)
- (5명 이상의 학생이 있는 클래스를 찾는 것이니 class로 묶어줌
- group by class
출처
'코딩테스트 > Leet Code' 카테고리의 다른 글
[Leet Code SQL] 627. Swap Salary (0) | 2022.03.22 |
---|---|
[Leet Code SQL] 620. Not Boring Movies (0) | 2022.03.11 |
[Leet Code SQL] 595. Big Countries (0) | 2022.03.04 |
[Leet Code] 217. Contains Duplicate python (0) | 2022.02.25 |
[Leet Code SQL] 184. Department Highest Salary (0) | 2022.02.11 |