[강의 수강 6일차] SELECT절 연습 문제 풀어보기 (3)

SQL 실전, 문제를 풀어보면서 이해하는 시간을 가져보기 3번째

짧고 굵게나마 짬을 내서 SELECT 절에 익숙해지고 업무 시간에도 SQL을 활용해보고자 한다.

 

✏️ 조건들을 파악할 때 왜 그 조건이지? 생각해보는 과정이 필요하다. 

✏️ 쿼리 작성할 때 쿼리 작동 흐름 / 조건들을 파악하기 위해 흐름을 잡아 연습할 필요가 있다. 

✔️ 어떤 테이블? FROM > 어떤 그룹? GROUP BY > 어떤 컬럼? SELECT, 어떤 집계? COUNT > 어떤 조건 ? WHERE

 

10. type2가 존재하는 포켓몬의 수는 얼마나 되나요? 

✅ 쿼리 조건

- 어떤 테이블 ? 포켓몬 

- 어떤 조건 ? type2 is not null 

- 어떤 컬럼 ? 포켓몬 수

- 어떻게 집계 ? 포켓몬 수 count

 

SELECT
	count(id) as pokemon_cnt
FROM basic.pokemon
WHERE 
	type2 IS NOT NULL

 

11. type2가 있는 포켓몬 중에 제일 많은 type 1은 무엇인가요? 

✅ 쿼리 조건

- 어떤 테이블 ? 포켓몬 

- 어떤 조건? type is not null 

- 어떤 컬럼 ? type1 

- 어떻게 집계 ? type 1 그룹핑 후 수

SELECT
	type1,
    count(id) as pokemon_cnt
FROM basic.pokemon
WHERE 
	type2 IS NOT NULL
GROUP BY
	type1
ORDER BY
	pokemon_cnt DESC
LIMIT 1

 

✏️ LIMIT + DESC 를 이용하면 내림차순 상위만 볼 수 있음

12. 단일(하나의 타입만 있는) 타입 포켓몬 중 많은 type1은 무엇일까요? 

✅ 쿼리 조건

- 어떤 테이블 ? 포켓몬 

- 어떤 조건? 단일 타입, 하나의 타입만 존재 

- 어떤 컬럼 ? type2 is null 

- 어떻게 집계 ? count 

SELECT
	type1,
    count(id) as pokemon_cnt
FROM basic.pokemon
WHERE 
	type2 IS NULL
GROUP BY
	type1
ORDER BY
	pokemon_cnt DESC
LIMIT 1

 

13. 포켓몬 이름에 "파"가 들어가는 포켓몬은 어떤 포켓몬이 있을까요? 

✏️ (힌트) 컬럼 LIKE "파%" 

 

✅ 쿼리 조건

- 어떤 테이블 ? 포켓몬 

- 어떤 조건? 파가 들어가는 포켓몬 이름 

- 어떤 컬럼 ? 포켓몬 이름 name

- 어떻게 집계 ? 없음

SELECT
	kor_name
FROM basic.pokemon
WHERE
	kor_name LIKE "파%"

 

✏️ 컬럼 LIKE : "특정단어%", %는 앞에 붙을 수 있고 뒤에도 붙을 수 있음

"%파" : 파로 끝나는 단어, "파%" : 파로 시작하는 단어, "%파%" : 파가 들어간 단어

문자열 컬럼에서 특정 단어가 포함되어 있는 지 알고 싶은 경우에는 LIKE를 사용하면 편함!

14. 뱃지가 6개 이상인 트레이너는 몇 명이 있나요?

✅ 쿼리 조건

- 어떤 테이블 ? 트레이너

- 어떤 조건? 뱃지 6개 이상 badge_cnt >= 6

- 어떤 컬럼 ? 이름

- 어떻게 집계 ? 트레이너 수

SELECT
	count(id) AS trainer_cnt
FROM basic.trainer
WHERE
	badge_cnt >= 6

15.  트레이너가 보유한 포켓몬(trainer_pokemon)이 제일 많은 트레이너는 누구일까요?

✅ 쿼리 조건

- 어떤 테이블 ? trainer_pokemon 

- 어떤 조건? 없음

- 어떤 컬럼 ? trainer_id

- 어떻게 집계 ? 포켓몬 수 count

SELECT 
	trainer_id,
    COUNT(pokemon_id) AS pokemon_cnt,
    COUNT(DISTINT pokemon_id) AS pokemon_cnt2 -- 포켓몬 고유의 값 
FROM basic.trainer_pokemon
GROUP BY
	trainer_id
ORDER BY
	pokemon_cnt
    
    
--테이블 조회 후 탐색하는 습관 가지기 / 구조 파악
SELECT
	*
FROM basic.trainer_pokemon
WHERE
	trainer_id = 5

 

✏️ Distinct 를 하게 되면 중복값을 알 수 있기에 "유저 수"와 같은

고유 id 만 남겨놔야 하는 상황이라면 DISTINCT 와 값이 다를 수 있으니 확인하자! (고유값 확인하는 습관 가지기)

 

16.  포켓몬을 많이 풀어준 트레이너는 누구일까요? 

✅ 쿼리 조건

- 어떤 테이블 ? trainer_pokemon 

- 어떤 조건? status = "Released"  

- 어떤 컬럼 ? trainer_id

- 어떻게 집계 ? 포켓몬 수 count

SELECT 
	trainer_id,
    COUNT(pokemon_id) AS pokemon_cnt
FROM basic.trainer_pokemon
WHERE
	status = "Released"
GROUP BY
	trainer_id
ORDER BY
	pokemon_cnt DESC
LIMIT 1

 

17.  트레이너 별로 풀어준 포켓몬의 비율이 20%가 넘는 포켓몬 트레이너는 누구일까요?
풀어준 포켓몬의 비율 = (풀어준 포켓몬 수/전체 포켓몬의 수) 

(힌트) COUNTIF(조건) 

 

✅ 쿼리 조건

- 어떤 테이블 ? trainer_pokemon 

- 어떤 조건? 풀어준 포켓몬의 비율이 20% 넘어야 한다 

- 어떤 컬럼 ? trainer_id

- 어떻게 집계 ? countif(status = "Released") 

 

✏️ countif의 경우, 컬럼과 조건 where 절로 혼합해서 쓸 수 있지만 쿼리가 간단해진다는 장점이 있다. 

특정 컬럼의 조건과 상태를 필터링할 때 간단한 함수로 활용하면 좋다. 

 

SELECT
	trainer_id,
    COUNTIF(status = "Released") AS released_cnt, #풀어준 포켓몬의 수 
    COUNTIF(pokemon_id) AS pokemon_cnt,
    COUNTIF(status = "Released")/COUNT(pokemon_id) AS released_ratio
FROM basic.trainer_pokemon 
GROUP BY
	trainer_id
HAVING
	released_ratio >= 0.2

 

✏️ 특정 연산자를 쿼리문에 바로 써서 계산할 수 있는 것이 포인트이다. 

 

데이터 탐색과 추출을 하기 위해서는 위와 같은 프로세스를 거치는 것이 중요하다.

✏️ 특히 어떤 일을 해야 할 때 > 원하는 것을 정하고 문제와 지표를 정의하고 > 데이터를 탐색하는 과정이 중요하다.

➡️ 그래야 어떤 데이터가 필요하고 WHERE 조건, 추출 그리고 요약(집계)를 활용할 수 있게 된다.

 

LIMIT을 통해 적은 용량의 쿼리 수행으로 탐색을 해보고 HAVING절을 통해 집계 조건까지 활용해보면 좋을 것 같다.

특히 계속 문제를 풀면서 놓친 것은 집계를 이용하면 GROUP BY가 따라 붙어야 한다는 것! 잊지 말자. 

 

✏️ 이번 강의와 학습을 통해 느낀점
✅ 조건과 로직이 걸리게 되면 복잡하다고 느껴 아직까지는 풀기 어려워하는 것 같다. 현업 데이터를 통해 구조와 로직을 이해하는 연습을 꾸준히 해야 겠다. 

 

💪🏻 Next to do 

☑️ 쿼리 작성법과 로직 관련된 부분 시뮬레이션하며 구조짜기
☑️ 현업 데이터 활용하며 쿼리 로직 기록 남기기