프로그래머스 SQL 공부 정리 1

SQL을 공부할 때 사용한 여러 사이트들이 있다. (해커랭크, 프로그래머스 등등..)
나는 프로그래머스로 먼저 공부를 시작했고, 프로그래머스로 공부한 내용을 정리해 보려고 한다.

SQL 고득점 KIT

SELECT

평균 일일 대여 요금 구하기(lev1)

SELECT ROUND(AVG(DAILY_FEE)) as AVERAGE_FEE 
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = 'SUV'

• ROUND(숫자, 반올림할 자릿수): 숫자를 반올림할 자릿수+1 자릿수에서 반올림

흉부외과 또는 일반외과 의사 목록 출력하기(lev1)

SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD, '%Y-%m-%d') as HIRE_YMD
FROM DOCTOR
WHERE MCDP_CD IN ('CS', 'GS')
ORDER BY HIRE_YMD DESC, DR_NAME

• DATE_FORMAT(날짜, 형식): 날짜를 해당 형식으로 출력
• 참고 %Y: 네 자리 년도, %m: 숫자 월, %d: 숫자 일

조건에 맞는 도서 리스트 출력하기(lev1)

SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, "%Y-%m-%d") as PUBLISHED_DATE
FROM BOOK
WHERE YEAR(PUBLISHED_DATE) = '2021'
    and CATEGORY = '인문'
ORDER BY PUBLISHED_DATE

인기있는 아이스크림(lev1)

SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID

• ORDER BY DESC: 내림차순 정렬

강원도에 위치한 생산공장 목록 출력하기(lev1)

SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY 
WHERE ADDRESS LIKE '%강원도%' 
ORDER BY FACTORY_ID

• LIKE: 특정 문자 포함되어 있는 것만 추출
• 참고 hi로 시작하는 데이터 검색: like 'hi%', hi로 끝나는 데이터 검색: like '%hi', hi가 들어가는 데이터 검색: '%hi%'

과일로 만든 아이스크림 고르기(lev1)

SELECT F.FLAVOR
FROM FIRST_HALF F
LEFT JOIN ICECREAM_INFO I
    on F.FLAVOR = I.FLAVOR
WHERE TOTAL_ORDER > 3000
    and INGREDIENT_TYPE = 'fruit_based'
ORDER BY TOTAL_ORDER DESC

• LEFT JOIN: 왼쪽 테이블 기준으로 오른쪽의 테이블 매치

어린 동물 찾기(lev1)

SELECT ANIMAL_ID, NAME 
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != 'AGED'

조건에 맞는 회원수 구하기(lev1)

SELECT COUNT(USER_ID) as USERS
FROM USER_INFO
WHERE YEAR(JOINED) = '2021'
    and AGE >= 20 and AGE < = 29

• COUNT(column): 해당 컬럼 수 세기
• YEAR(날짜): 날짜 중 년도만 반환

상위 n개 레코드(lev1)

SELECT NAME FROM ANIMAL_INS 
ORDER BY DATETIME
LIMIT 1

• LIMIT + n(수): 맨 위에서부터 n개까지의 정보 조회

12세 이하인 여자 환자 목록 출력하기(lev1)

SELECT PT_NAME, PT_NO, GEND_CD, AGE, COALESCE(TLNO, 'NONE') as TLNO
FROM PATIENT
WHERE AGE <= 12
    and GEND_CD = 'W'
ORDER BY AGE DESC, PT_NAME

• COALESCE(A, B, C): A가 NULL이 아니면 A 리턴, B가 NULL이 아니면 B 리턴, 둘 다 아닐 경우 C 리턴

재구매가 일어난 상품과 회원 리스트 구하기(lev2)

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

• HAVING: GROUP BY 다음에 수행되는 WHERE 같은 절로, 컬럼의 총 갯수가 어떤 숫자 초과인 경우 COUNT(column) > 숫자 이 형태로 사용

3월에 태어난 여성 회원 목록 출력하기(lev2)

SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH) = 03
    and GENDER = 'W'
    and TLNO is not null
ORDER BY MEMBER_ID

• MONTH(날짜): 날짜 중 월만 반환

서울에 위치한 식당 목록 출력하기(lev4)

WITH rr as(
SELECT REST_ID, ROUND(AVG(REVIEW_SCORE), 2) as SCORE
FROM REST_REVIEW
GROUP BY 1
)

SELECT r.REST_ID, REST_NAME, FOOD_TYPE, FAVORITES, ADDRESS, SCORE
FROM REST_INFO r
INNER JOIN rr
    on r.REST_ID = rr.REST_ID
WHERE ADDRESS LIKE '서울%' 
GROUP BY r.REST_ID
ORDER BY SCORE DESC, FAVORITES DESC

SELECT rr.REST_ID, REST_NAME, FOOD_TYPE, FAVORITES, ADDRESS, ROUND(AVG(REVIEW_SCORE), 2) as SCORE
FROM REST_INFO ri
JOIN REST_REVIEW rr
    ON ri.REST_ID = rr.REST_ID
WHERE ADDRESS LIKE '서울%'
GROUP BY 1, 2, 3, 4, 5
ORDER BY 6 DESC, FAVORITES DESC

오프라인/온라인 판매 데이터 통합하기(lev4)

SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE 
WHERE MONTH(SALES_DATE) = '03'
UNION 
SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, NULL as USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE MONTH(SALES_DATE) = '03'
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID

• UNION: 2개 이상 테이블에 존재하는 같은 성격의 값을 하나의 쿼리로 추출

SUM, MAX, MIN

가장 비싼 상품 구하기(lev1)

SELECT MAX(PRICE) as MAX_PRICE
FROM PRODUCT

최댓값 구하기(lev1)

SELECT MAX(DATETIME) AS '시간' 
FROM ANIMAL_INS

최솟값 구하기(lev2)

SELECT MIN(DATETIME) 
FROM ANIMAL_INS

동물 수 구하기(lev2)

SELECT COUNT(ANIMAL_ID) 
FROM ANIMAL_INS

중복 제거하기(lev2)

SELECT COUNT(DISTINCT NAME)  
FROM ANIMAL_INS
WHERE NAME IS NOT NULL

가격이 제일 비싼 식품의 정보 출력하기(lev2)

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

IS NULL

경기도에 위치한 식품창고 목록 출력하기(lev1)

SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, COALESCE(FREEZER_YN, 'N') as FREEZER_YN #IFNULL(FREEZER_YN, 'N') AS FREEZER_YN
FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE '경기%'
ORDER BY WAREHOUSE_ID

이름이 있는 동물의 아이디(lev1)

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
ORDER BY ANIMAL_ID

나이 정보가 없는 회원 수 구하기(lev1)

SELECT COUNT(USER_ID) AS USERS
FROM USER_INFO
WHERE AGE IS NULL

NULL 처리하기(lev2)

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

• IFNULL(column, 값): 해당 컬럼이 null 이라면 특정 값 반환