프로그래머스 sql 코테_level 1&2

✔️ 프로그래머스 코딩테스트 Level 1&2 후기

level 3부터 풀었어서 그런지 1,2에서 중복되는 내용이 많다. 

 

📕 Level 1 주요 문법 

1. 1개 추출

MYSQL: LIMIT 1

ORACLE: FETCH FIRST 1 ROWS ONLY

-- MYSQL
SELECT DATETIME AS "시간"
FROM ANIMAL_INS 
ORDER BY DATETIME ASC
LIMIT 1;

-- ORACLE
SELECT DATETIME AS "시간"
FROM ANIMAL_INS 
ORDER BY DATETIME ASC
FETCH FIRST 1 ROWS ONLY;

 

2. ~로 이어지는 문자열 

문제가 주소(address)가 강원도로 시작하는 공장 정보 추출하기 라면

WHERE 컬럼명 LIKE '문자열%'

• %는 기준 문자 앞뒤로 넣어 활용가능하다! 

SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY
WHERE ADDRESS like '강원도%'

 

3. 날짜 형식 수정

date_format('날짜컬럼', '%Y-%m-%d')

기존 날짜 데이터 형식이 2011-03-01 00:00:00 였는데

date_format시 2011-03-01 로 깔끔해짐.

SELECT DR_NAME, DR_ID, MCDP_CD, date_format(HIRE_YMD, '%Y-%m-%d')
FROM DOCTOR
WHERE MCDP_CD = 'CS' or MCDP_CD ='GS'
ORDER BY HIRE_YMD desc, DR_NAME asc

 

4. 여러 조건들

🫙 null

• null값 추출: WHERE '컬럼' is null 
• null값 대체: ifnull(컬럼명, '대체할 값')

SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name') NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS

 

🗓️ 가입일이 2021인 케이스 가져오려면

• 사이값: '컬럼' between N and M
• 특정 연도: year(joined) = 2021 
• TOCHAR(JOINED, 'YYYY')='2021'
• BETWEEN '2021-01-01' AND '2021-12-31'

SELECT count(USER_ID)
FROM USER_INFO
WHERE JOINED like '2021-%' and AGE between 20 and 29

 

🌗 조건에 따른 파생변수

문제: 중서화 여부에 따라 중성화 표시

SEX_UPON_INTAKE 컬럼에 'Neutered' 또는 'Spayed'라는 단어가 들어있으면 중성화 o 

• IF문에 조건을 여러개 걸 수 있다! and/or로 다중 조건 가능

SELECT ANIMAL_ID, NAME
	,IF(SEX_UPON_INTAKE like 'Neutered%' or SEX_UPON_INTAKE like 'Spayed%', 'O', 'X')as 중성화
FROM ANIMAL_INS

 

5. 평균값 구하기

• SUV에 해당하는 자동차들의 DAILY_FEE의 평균값

SELECT ROUND(SUM(DAILY_FEE)/COUNT(*), 0) as AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = 'SUV'

 

6. JOIN

'총주문량'이 있는 a 테이블과 '재료정보'가 있는 b 테이블을 join시켜주고

조건에 맞는 flavor 추출

SELECT a.FLAVOR
FROM FIRST_HALF a, ICECREAM_INFO b
WHERE a.flavor = b.flavor 
    and TOTAL_ORDER>3000 
    and INGREDIENT_TYPE='fruit_based'
ORDER BY a.TOTAL_ORDER desc;

 

7. 파생변수

대여일수(end_date - start_date)가 90일 초과하면 장기, 아니면 단기

IF( 변수&조건, true 값, false 값)

SELECT HISTORY_ID, CAR_ID, date_format(START_DATE, '%Y-%m-%d') START_DATE,
	date_format(END_DATE, '%Y-%m-%d') END_DATE,
    IF(END_DATE-START_DATE > 90, '장기 대여', '단기 대여') as RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE like '2022-09%'
ORDER BY HISTORY_ID desc;

 

📗 Level 2 주요 문법 

 

1. DATEDIFF 일자 계산

만약 대여일자와 반납일자가 같은 날이면 0일이 되는데 실상 하루 빌린 것으로 +1을 해줘야 한다. 

• end_date - start_date + 1
• datediff( end_date, start_date)

 

2. HAVING 집계함수 조건

문제는 자동차의 평균 대여 기간을 구하는 것이다. 결과로 평균이 7일 이상인 행만 출력하는 것이니 집계후 조건을 주기위해 having절 사용

SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE,START_DATE)+1),1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC

<순서>

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY 

SELECT NAME, count(NAME) as COUNT
FROM ANIMAL_INS
WHERE NAME is not null
GROUP BY NAME
HAVING COUNT >= 2
ORDER BY NAME

 

3. CASE WHEN

조건에 따라 변수값을 대체하는 경우 case when 사용

CASE WHEN 변수 = 'A' THEN 'a' 

WHEN 변수 = 'B' THEN 'b' 

WHEN 변수 = 'C' THEN 'c'

END 변수

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE,
CASE WHEN STATUS='DONE' THEN '거래완료'
    WHEN STATUS='SALE' THEN '판매중'
    WHEN STATUS='RESERVED' THEN '예약중'
    END STATUS
FROM USED_GOODS_BOARD
WHERE CREATED_DATE = '2022-10-05'
ORDER BY BOARD_ID desc

 

4. DISTINCT 중복값 제외 값 확인

Distinct를 사용하면 중복값을 제외하고 count 할 수 있다. 

SELECT COUNT(Distinct NAME) as count
FROM ANIMAL_INS

 

5. SUNSTR 문자열 자르기 

SUBSTR	SUBSTR(컬럼명, 시작) SUBSTR(컬럼명, 시작위치, 개수)
LEFT, RIGHT	LEFT(컬럼명, 개수)

 

6. WHERE 컬럼명 IN ('A', 'B', ...) 특정 문자열이 들어간 행 추출

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME in ('Lucy','Ella','Pickle','Rogan','Sabrina','Mitty')
ORDER BY ANIMAL_ID, NAME, SEX_UPON_INTAKE

 

7. REGEXP 정규표현식

• WHERE 컬럼명 REGEXP
• WHERE REGEXP_LIKE( 컬럼, '정규표현식')
• WHERE IN 과 비슷한 기능같지만 IN함수는 문자열 포함(X)이 아닌 일치(O)하는 경우만 추출
  • DATA = '고양이' 이면 IN('고양'): X, IN('고양이'): O

-- '통풍시트', '열선시트', '가죽시트' 중 하나 이상의 옵션 포함
SELECT CAR_TYPE, COUNT(*)as CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS REGEXP '열선시트|통풍시트|가죽시트'
-- WHERE REGEXP_LIKE(OPTIONS, '열선시트|통풍시트|가죽시트')
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE

 

 

 

📘 틀리기 쉬운 문법

• 파생변수 만든 후 WHERE 조건 주는 경우 별칭 사용 불가
• 쿼리 실행 순서: FROM > WHERE > GROUP BY > (HAVING) > SELECT > ORDER BY
  • GROUP BY, HAVING, ORDER BY에서는 SELECT에서 지정한 별칭(*) alias를 사용할 수 있지만
  • WHERE 절에서는 별칭을 사용할 수 없음. (서브쿼리로는 사용 가능)

SELECT HOUR(DATETIME) as HOUR, count(*) as COUNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) >= 9 and HOUR(DATETIME) < 20
GROUP BY HOUR
ORDER BY HOUR

 

 

다중 GROUP BY 조건

• 그룹에 조건 걸기 HAVING

SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*) > 1
ORDER BY USER_ID, PRODUCT_ID desc

 

 

❌ 완전히 틀린 문제

 

• 목표: 단위가 10000원인 구간으로 그룹화
• CASE WHEN 보다 효율적인 쿼리 필요

1. PRICE - PRICE%10000 : 금액 - (금액을 10000으로 나누고 남은 나머지 값). 따라서 PRICE - PRICE%10000은 깔끔하게 만원 단위로 떨어짐
2. PRICE DIV 10000 : 금액을 10000으로 나눈 값 (0, 1, 2 처럼 맨앞자리 숫자가 나옴)

 

-- 1안
SELECT (PRICE - PRICE%10000) AS PRICE_GROUP, COUNT(*) AS PRODUCTS
FROM PRODUCT 
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP

-- 2안
SELECT (PRICE DIV 10000) * 10000 as PRICE_GROUP, COUNT(*) as PRODUCTS
FROM PRODUCT 
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP