SQL 코딩테스트 대비 연습 스킬 복기하기

0. 주의점

 

실행 예시만 보고 하면 안됨

 

코딩테스트 연습 - 고양이와 개는 몇 마리 있을까 | 프로그래머스 스쿨

프로그래머스

 

 

실행 예시에 고양이, 개만 있어서 그냥

 

-- 코드를 입력하세요
SELECT ANIMAL_TYPE,COUNT(ANIMAL_TYPE) FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE;

 

 

이러면 되나?했는데 틀리더라고

 

요구조건에 '개와 고양이가 몇마리인지', '고양이를 먼저 조회해야'를 모두 맞춰야함

 

테이블 상태에 따라, WHERE절, ORDER절 안쓰면 다르게 나올듯?

 

 

0.주의점2

 

칼럼명 안먹힐때 `을 사용해보기

 

PM2.5만 쓰면 안먹히는데, `PM2.5`라고 하면 먹힘

 

-- 코드를 작성해주세요
select year(ym) as YEAR, round(avg(pm_val1),2) AS `PM10`,round(avg(pm_val2),2) as `PM2.5` from air_pollution
where location2 = '수원'
group by year(ym)
ORDER BY YEAR;

 

 

 

1. 중복 제거

 

distinct로 중복 제거 가능

 

count(컬럼)하면, null을 제외하고 행 수를 센다.

 

count(*)은 null을 포함해서 행 수를 센다.

 

 

2. MAX를 이용해 최댓값을 가지는 행 조회하기(서브쿼리)

 

SELECT MAX(PRICE) FROM FOOD_PRODUCT;로 PRICE가 최댓값인 행을 조회하고,

 

WHERE절을 이용해서

 

SELECT * FROM FOOD_PRODUCT
WHERE PRICE = (SELECT MAX(PRICE) FROM FOOD_PRODUCT);

 

 

 

WHERE 절에서는 집계 함수(COUNT, SUM 등)를 사용할 수 없습니다.

 

WHERE는 개별 행(row)을 필터링할 때 쓰이기 때문입니다

 

서브쿼리가 여러 값을 반환할 수 있을 땐 IN, EXISTS, ANY 같은 키워드를 사용하면 됩니다.

 

SELECT * 
FROM FOOD_PRODUCT
WHERE PRICE IN (SELECT PRICE FROM FOOD_PRODUCT WHERE ...);

 

 

3. 와일드카드 %

 

where절에서 like를 사용해야 의미있음

 

특히 el이 어디에나 들어간 문자열을 인식하려면 %el%로 사용

 

대소문자를 구별하지 않는다

 

like binary를 사용하면 대소문자 구분함

 

-- 코드를 입력하세요
SELECT ANIMAL_ID, NAME FROM ANIMAL_INS
WHERE ANIMAL_TYPE = 'Dog' and NAME like '%el%'
order by name;

 

 

=을 쓰면 % 그 자체로 의미되어서 의미 없어짐

 

-- 잘못된 사용: =는 %를 특별한 의미로 인식하지 않음
SELECT * 
FROM ANIMAL_INS
WHERE NAME = 'Co%';

 

 

 

4. null값 치환

 

ifnull(칼럼, null이면 치환할 값)

 

혹은

 

coalesce(칼럼1,칼럼2,칼럼3,....,null이면 치환할 값)

 

은 칼럼1,칼럼2,..를 순서대로 보면서 null이 아닌 값을 반환한다.

 

칼럼1이 null값이 아니면 칼럼1 반환

 

칼럼1이 null이고 칼럼2가 null이 아니면 칼럼2를 반환

 

칼럼1이 null이고 칼럼2가 null이면 칼럼3이 null이 아니면 칼럼3을 반환

 

칼럼1이 null이고 칼럼2가 null이고,... 칼럼n이 null이면 "null이면 치환할 값"반환

 

-- 코드를 입력하세요
SELECT ANIMAL_TYPE, IFNULL(NAME,'No name'), SEX_UPON_INTAKE FROM ANIMAL_INS;

 

 

 

5. DATE_FORMAT()

 

연-월-일-시-분-초형태의 datetime type을 연-월-일로 바꿀려면 DATE_FORMAT()함수를 이용

 

-- 코드를 입력하세요
SELECT animal_id, name, DATE_FORMAT(datetime, '%Y-%m-%d') from animal_ins;

 

 

 

6. substr()

 

문자열의 일부분을 자르는 함수

 

substr(str,pos,len)

 

pos부터 시작해서 len만큼 자른다.

 

pos는 1부터 시작

 

다음은 product_code의 앞 2글자를 기준으로 그룹화 시켜서, 카운팅하는 코드

 

-- 코드를 입력하세요
SELECT SUBSTR(PRODUCT_CODE,1,2) AS CATEGORY,COUNT(PRODUCT_ID) FROM PRODUCT
GROUP BY CATEGORY
ORDER BY CATEGORY;

 

 

참고로 substring_index(str,delim,count)

 

문자열 str을 구분자 delim을 기준으로 분리하는 함수

 

SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', 1);  -- apple
SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', 2);  -- apple,banana
SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', -1); -- cherry
SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', -2); -- banana,cherry

 

 

7. YEAR(), MONTH()

 

시간 값에서 YEAR()은 연을 가져오고, MONTH()는 월을 가져옴

 

-- 코드를 입력하세요
SELECT MCDP_CD AS '진료과코드',COUNT(APNT_YMD) AS '5월예약건수' FROM APPOINTMENT
WHERE YEAR(APNT_YMD) = 2022 AND MONTH(APNT_YMD) = 5
GROUP BY MCDP_CD
ORDER BY `5월예약건수`,`진료과코드`;

 

 

8. 컬럼명

 

이렇게 쓰면 틀리더라고?

 

-- 코드를 입력하세요
SELECT MCDP_CD AS '진료과코드',COUNT(APNT_YMD) AS '5월예약건수' FROM APPOINTMENT
WHERE YEAR(APNT_YMD) = 2022 AND MONTH(APNT_YMD) = 5
GROUP BY MCDP_CD
ORDER BY '5월예약건수','진료과코드';

 

 

진료과 코드가 오름차순 정렬돼야하는데 안됨

 

 

 

GPT피셜

 

 

 

백틱 `으로 감싸거나, 그냥 컬럼명 그대로 쓰면 됨

 

-- 코드를 입력하세요
SELECT MCDP_CD AS '진료과코드',COUNT(APNT_YMD) AS '5월예약건수' FROM APPOINTMENT
WHERE YEAR(APNT_YMD) = 2022 AND MONTH(APNT_YMD) = 5
GROUP BY MCDP_CD
ORDER BY 5월예약건수,진료과코드;

 

 

 

 

9. WHERE절

 

WHERE절은 SELECT의 별칭을 쓸 수 없다

 

논리적인 처리 순서상 WHERE가 SELECT보다 먼저 실행되니까 그런다는데

 

GROUP BY, ORDER BY는 예외로 별칭을 사용 가능

 

 

 

 

-- 코드를 입력하세요
SELECT HOUR(DATETIME) AS HOUR, COUNT(HOUR(DATETIME)) AS COUNT FROM ANIMAL_OUTS
WHERE HOUR >= 9 AND HOUR <= 19
GROUP BY HOUR
ORDER BY HOUR;

SQL 실행 중 오류가 발생하였습니다.
Unknown column 'HOUR' in 'where clause'

 

 

10. limit

 

limit n 하면 위에서부터 n행만 출력함

 

#위에서부터 3줄

SELECT animal_ins.name,animal_ins.datetime from animal_ins left outer join animal_outs on animal_ins.animal_id = animal_outs.animal_id
where animal_outs.animal_id is null
order by animal_ins.datetime
limit 3;

 

 

 

11. 새로운 컬럼 만들기 + 날짜 리터럴 지정

 

코딩테스트 연습 - 조건별로 분류하여 주문상태 출력하기 | 프로그래머스 스쿨

프로그래머스

 

 

'출고여부'라는 열을 만들어야하는데, out_date를 기준으로 2022-05-01이내면 출고완료, null이면 출고미정, 초과면 출고대기

 

아래와 같이 out_date에 대한 if문을 이용해서 새로운 컬럼을 만들 수 있음

 

-- 코드를 입력하세요
SELECT order_id,product_id,date_format(out_date,'%Y-%m-%d'),
if(out_date is null,'출고미정',if(out_date <= '2022-05-01','출고완료','출고대기')) from food_order
order by order_id;

 

 

날짜값 비교할때 반드시 out_date <= '2022-05-01'로 ','를 사용해서 지정해야함

 

out_date <= 2022-05-01하면 2022-5-1 = 2016으로 인식함

 

 

12. in 연산자

 

다음과 같이 or 연산 여러개를 in연산자 하나로 바꿀 수 있다

 

-- 코드를 작성해주세요
select id,email,first_name,last_name from developer_infos
where skill_1 = 'Python' or skill_2 = 'Python' or skill_3 = 'Python'
order by id;

 

 

은 다음과 같이 'Python' in (skill_1, skill_2, skill_3)로 변경 가능하다

 

-- 코드를 작성해주세요
select id,email,first_name,last_name from developer_infos
where 'Python' in (skill_1,skill_2,skill_3)
order by id;

 

 

13. 날짜에서 월을 가져오는 함수 month, date_format

 

만약 1~12월 추출하고 싶은데, 1,2,3,..,9는 한자리만 쓰고 싶으면 month(datetime)이나 date_format(datetime,'%c')를 쓴다

 

date_format(datetime,'%m')은 1,2,3,..,9 앞에 0을 붙여서 출력함

 

 

 

 

date_format(datetime, '%c')은 문자열타입으로 반환

 

month(datetime)은 정수형으로 반환함

 

그래서 두 값을 정렬할때 차이가 있다

 

문자열같은 경우는 파이썬처럼 정렬되어서 

 

 

 

 

 

반면 month(time)은 숫자라서 정렬할때, 숫자로 취급하고 정렬됨

 

 

 

 

14. 반올림함수 round

 

평균을 구하는 함수는 참고로 avg()

 

 

 

 

15. concat함수

 

값을 이어 붙이는데 사용

 

예를 들어 length에 'cm'를 붙여서 출력하거나, 돈에 '원'을 붙여서 출력하고 싶을때

 

다음은 가장 긴 길이에 'cm'를 붙여서 출력하는 코드

 

-- 코드를 작성해주세요
select concat(max(length),'cm') as max_length from fish_info;

 

 

 

 

 

문자열끼리가 아니고 숫자끼리도 가능

 

 

 

 

 

 

 

 

 

비슷하게 concat_ws()라는 함수도 있음

 

 

 

 

 

 

16. 구간별로 구간화?

 

코딩테스트 연습 - 가격대 별 상품 개수 구하기 | 프로그래머스 스쿨

프로그래머스

 

 

가격을 0 ~ 10000을 0원, 10000~20000을 10000원, 20000~30000을 20000원...으로 그룹화할려면?

 

따로 함수는 없고 수학적으로 구해야함

 

price값을 10000으로 나누면, 예를 들어 9000은 0.xxx가 되니까 floor하면 0이 되어서 0에 속할 수 있고

 

12345는 10000으로 나누면 1.2345에서 floor하면 1이고 여기에 10000곱하면 10000에 속할 수 있고

 

....

 

-- 코드를 입력하세요
SELECT floor(price/10000)*10000 as price_group,count(price) as products from product
group by price_group
order by price_group;