https://school.programmers.co.kr/learn/challenges?tab=sql_practice_kit
소프트웨어 마에스트로 코딩테스트에 SQL 1문제가 출제되어 준비를 위해 프로그래머스 고득점 Kit문제를 쭉 풀이할 예정입니다.
(이후 해커랭크 Medium 문제까지 풀이 계획)
모든 문제는 MySQL을 기준으로 작성했습니다.
Select
평균 일일 대여 요금 구하기 lv.1
SELECT ROUND(AVG(daily_fee)) as 'AVERAGE_FEE'
FROM CAR_RENTAL_COMPANY_CAR
WHERE car_type = 'SUV'반올림에서는 ROUND(값, 자릿수) 사용 , 자릿수가 0이면 생략해도 됨
조건에 맞는 도서 리스트 출력하기 lv.1
SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE,'%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK
WHERE CATEGORY = '인문' AND YEAR(PUBLISHED_DATE)=2021
ORDER BY PUBLISHED_DATEDATE 형식에 YEAR()로 연도만 추출 가능.
DATE_FORMAT으로 형식 변경 가능
12세 이하인 여자 환자 목록 출력하기 lv.1
SELECT PT_NAME, PT_NO, GEND_CD, AGE, IFNULL(TLNO, "NONE") AS TLNO
FROM PATIENT
WHERE AGE <= 12 AND GEND_CD = "W"
ORDER BY AGE DESC, PT_NAME ASCIFNULL(Column명, "Null일 경우 대체 값"): 칼럼값이 NULL일때 대체할 값을 넣을 수 있다.
3월에 태어난 여성 회원 목록 출력하기 lv.1
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)=3 AND GENDER = 'W' AND TLNO IS NOT NULL
ORDER BY MEMBER_IDDATE 형식에 MONTH()로 월만 추출 가능.
DATE_FORMAT으로 형식 변경 가능(y만 대문자 주의)
인기있는 아이스크림 lv.1
SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID ASC흉부외과 또는 일반외과 의사 목록 출력하기 lv.1
SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD,'%Y-%m-%d') AS HIRE_YMD
FROM DOCTOR
WHERE MCDP_CD = 'CS' OR MCDP_CD = 'GS'
ORDER BY HIRE_YMD DESC, DR_NAME ASC조건에 부합하는 중고거래 댓글 조회하기 lv.1
SELECT UB.TITLE, UB.BOARD_ID, UR.REPLY_ID, UR.WRITER_ID, UR.CONTENTS, DATE_FORMAT(UR.CREATED_DATE, "%Y-%m-%d") AS CREATED_DATE
FROM USED_GOODS_BOARD AS UB
JOIN USED_GOODS_REPLY AS UR ON (UB.BOARD_ID = UR.BOARD_ID)
WHERE YEAR(UB.CREATED_DATE)=2022 AND MONTH(UB.CREATED_DATE)=10
ORDER BY UR.CREATED_DATE, UB.TITLE위에서 ON (UB.BOARD_ID = UR.BOARD_ID) 를 USING (BOARD_ID)로 변경 가능
FROM 테이블1, 테이블2 은 FROM 테이블1 INNER JOIN 테이블2 와 같다.
표준 SQL과는 달리 MySQL에서는 JOIN, INNER JOIN, CROSS JOIN이 모두 같은 의미로 사용
과일로 만든 아이스크림 고르기 lv.1
SELECT FLAVOR
FROM FIRST_HALF AS FH
INNER JOIN ICECREAM_INFO AS II USING (FLAVOR)
WHERE FH.TOTAL_ORDER > 3000 AND II.INGREDIENT_TYPE='fruit_based'
ORDER BY FH.TOTAL_ORDER DESC서울에 위치한 식당 목록 출력하기 lv.4
SELECT RI.REST_ID, RI.REST_NAME, RI.FOOD_TYPE, RI.FAVORITES, RI.ADDRESS, ROUND(AVG(RR.REVIEW_SCORE),2) AS SCORE
FROM REST_INFO AS RI
INNER JOIN REST_REVIEW AS RR USING (REST_ID)
WHERE RI.ADDRESS LIKE '서울%'
GROUP BY RI.REST_ID
ORDER BY SCORE DESC, RI.FAVORITES DESCLIKE %서울%로 작성하면 틀린다.
AVG 이후 ROUND를 적용해서 소숫점 반올림
HAVING => GROUP BY 이후에 테이블에 대해 조건 설정
강원도에 위치한 생산공장 목록 출력하기 lv.1
SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY
WHERE ADDRESS LIKE "강원도%"
ORDER BY FACTORY_ID재구매가 일어난 상품과 회원 리스트 구하기 lv.2
SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*) >= 2
ORDER BY USER_ID, PRODUCT_ID DESC모든 레코드 조회하기 lv.1
SELECT *
FROM ANIMAL_INS
ORDER BY ANIMAL_ID오프라인/온라인 판매 데이터 통합하기 lv.4
(SELECT DATE_FORMAT(SALES_DATE,"%Y-%m-%d") AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE YEAR(SALES_DATE)=2022 AND MONTH(SALES_DATE)=3
)
UNION
(SELECT DATE_FORMAT(SALES_DATE,"%Y-%m-%d") AS SALES_DATE, PRODUCT_ID, NULL, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE YEAR(SALES_DATE)=2022 AND MONTH(SALES_DATE)=3
)
ORDER BY SALES_DATE, PRODUCT_ID, USER_IDUNION -> 중복된 row는 제거
UNION ALL -> 중복제거 x
WHERE SALES_DATE LIKE '2022-03%'로 WHERE문 대체 가능
NULL AS USER_ID 로 작성해서 칼럼명 명시 가능
역순 정렬하기 lv.1
SELECT NAME, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC아픈 동물 찾기 lv.1
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION='Sick'
ORDER BY ANIMAL_ID어린 동물 찾기 lv.1
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != 'Aged'
ORDER BY ANIMAL_ID동물의 아이디와 이름 lv.1
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID여러 기준으로 정렬하기 lv.1
SELECT ANIMAL_ID, NAME, DATETIME
FROM ANIMAL_INS
ORDER BY NAME, DATETIME DESC상위 n개 레코드 lv.1
SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1LIMIT N -> 상위 N개 조회
조건에 맞는 회원수 구하기 lv.1
SELECT COUNT(*) AS USERS
FROM USER_INFO
WHERE YEAR(JOINED)=2021 AND (AGE BETWEEN 20 AND 29)업그레이드 된 아이템 구하기 lv.2
SELECT ITEM_ID, ITEM_NAME, RARITY
FROM ITEM_INFO
WHERE ITEM_ID IN ( -- 업그레이드 되는 아이템 ID 목록
SELECT IT.ITEM_ID
FROM ITEM_INFO AS II
JOIN ITEM_TREE AS IT ON (II.ITEM_ID = IT.PARENT_ITEM_ID)
WHERE II.RARITY = 'RARE'
)
ORDER BY ITEM_ID DESCPython 개발자 찾기 lv.1
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 ASC다음과 같은 방법도 가능하다
SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPER_INFOS
WHERE "Python" IN (SKILL1,SKILL2,SKILL3)
ORDER BY ID ASC조건에 맞는 개발자 찾기 lv.2
SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPERS
WHERE ID IN ( -- 해당되는 개발자 ID 목록
SELECT DISTINCT DEV.ID
FROM SKILLCODES AS SK, DEVELOPERS AS DEV
WHERE (SK.CODE & DEV.SKILL_CODE) > 0 -- 비트 and 연산한 결과가 0보다 크면 포함
AND SK.NAME IN ('Python', 'C#')
)
ORDER BY ID ASClv2로 선정되어 있는게 의외다. 비트연산의 & 결과가 0보다 크면 공통된 비트 1이 존재한다는 점을 이용하면 된다.
이때, FROM에 테이블 2개를 연달아 적어서 모든 가능한 경우인 카테시안 곱(Cartesian Product)을 구했다.
SUM, MAX, MIN
가격이 제일 비싼 식품의 정보 출력하기 lv.2
SELECT *
FROM FOOD_PRODUCT
ORDER BY PRICE DESC
LIMIT 1다음과 같이도 가능
SELECT *
FROM FOOD_PRODUCT
WHERE PRICE =
(SELECT MAX(PRICE) PRICE FROM FOOD_PRODUCT);가장 비싼 상품 구하기 lv.1
SELECT MAX(PRICE) AS MAX_PRICE
FROM PRODUCT최댓값 구하기 lv.1
SELECT MAX(DATETIME)
FROM ANIMAL_INS최솟값 구하기 lv.2
SELECT MIN(DATETIME)
FROM ANIMAL_INS동물 수 구하기 lv.2
SELECT COUNT(*)
FROM ANIMAL_INS중복 제거하기 lv.2
SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS
WHERE NAME IS NOT NULLDISTINCT: 칼럼명 앞에 붙여서 unique하게 처리
조건에 맞는 아이템들의 가격의 총합 구하기 lv.2
SELECT SUM(PRICE) AS TOTAL_PRICE
FROM ITEM_INFO
WHERE RARITY='LEGEND'GROUP BY
즐겨찾기가 가장 많은 식당 정보 출력하기 lv.3 ★
SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN (
SELECT FOOD_TYPE, MAX(FAVORITES)
FROM REST_INFO
GROUP BY FOOD_TYPE
)
ORDER BY FOOD_TYPE DESC처음에 못풀었다. 서브쿼리 + WHERE IN을 이용하는 방법으로 풀이가 가능하다.
조건에 맞는 사용자와 총 거래금액 조회하기 lv.3
SELECT USER_ID, NICKNAME, SUM(PRICE) AS TOTAL_SALES
FROM USED_GOODS_USER
INNER JOIN USED_GOODS_BOARD ON (WRITER_ID=USER_ID)
WHERE STATUS='DONE'
GROUP BY USER_ID
HAVING TOTAL_SALES >= 700000
ORDER BY TOTAL_SALESSQL 수행 순서 알아야
FROM+JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT
저자 별 카테고리 별 매출액 집계하기 lv.4
SELECT AUTHOR_ID, AUTHOR_NAME, CATEGORY, SUM(PRICE*SALES) AS TOTAL_SALES
FROM BOOK
INNER JOIN AUTHOR USING (AUTHOR_ID)
INNER JOIN BOOK_SALES USING (BOOK_ID)
WHERE YEAR(sales_date)=2022 AND MONTH(sales_date)=1
GROUP BY AUTHOR_ID, CATEGORY
ORDER BY AUTHOR_ID, CATEGORY DESCSUM(PRICE*SALES)와 같이 칼럼끼리의 곱 SUM한 결과 작성
카테고리 별 도서 판매량 집계하기 lv.3
SELECT CATEGORY, SUM(SALES) AS TOTAL_SALES
FROM BOOK
INNER JOIN BOOK_SALES USING (BOOK_ID)
WHERE YEAR(SALES_DATE)=2022 AND MONTH(SALES_DATE)=1
GROUP BY CATEGORY
ORDER BY CATEGORY자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기 lv.3 ★
SELECT
CAR_ID,
CASE
WHEN CAR_ID IN (
SELECT DISTINCT CAR_ID -- 대여중인 CAR_ID 목록
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE <= '2022-10-16' AND '2022-10-16' <= END_DATE
)
THEN '대여중'
ELSE '대여 가능'
END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESCCASE 사용하기
CASE
WHEN 조건
THEN '반환 값'
WHEN 조건
THEN '반환 값'
ELSE 'WHEN 조건에 해당 안되는 경우 반환 값'
END진료과별 총 예약 횟수 출력하기 lv.2 ★
SELECT MCDP_CD AS '진료과코드', COUNT(*) AS '5월예약건수'
FROM APPOINTMENT
WHERE YEAR(APNT_YMD)=2022 AND MONTH(APNT_YMD)=5
GROUP BY MCDP_CD
ORDER BY 5월예약건수, 진료과코드ORDER BY '5월예약건수', '진료과코드' 로 작성하면 틀린다.
별칭으로 인식하지 않고, 별칭을 로직에서 사용할 때는 백틱으로 별칭을 묶어주어야 한다.
또는 ORDER BY 2,1 과 같이 작성해서 순서로 정렬도 가능하다.
자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기 lv.2
SELECT CAR_TYPE, COUNT(*) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE (OPTIONS LIKE '%통풍시트%') OR (OPTIONS LIKE '%열선시트%') OR (OPTIONS LIKE '%가죽시트%')
GROUP BY CAR_TYPE
ORDER BY CAR_TYPELIKE 사용해서 포함 유무 파악하기
대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기 lv.3
SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (
SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE YEAR(START_DATE) = 2022 AND MONTH(START_DATE) IN (8,9,10)
GROUP BY CAR_ID
HAVING COUNT(*)>=5 -- 이상이니까 등호붙여야
) AND YEAR(START_DATE) = 2022 AND MONTH(START_DATE) IN (8,9,10)
GROUP BY MONTH(START_DATE), CAR_ID
ORDER BY MONTH ASC, CAR_ID DESC성분으로 구분한 아이스크림 총 주문량 lv.2
SELECT INGREDIENT_TYPE, SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF AS FH
JOIN ICECREAM_INFO AS II ON (FH.FLAVOR = II.FLAVOR)
GROUP BY INGREDIENT_TYPE
ORDER BY TOTAL_ORDER식품분류별 가장 비싼 식품의 정보 조회하기 lv.4
SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY, PRICE) IN (
SELECT CATEGORY, MAX(PRICE)
FROM FOOD_PRODUCT
WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
GROUP BY CATEGORY
)
ORDER BY MAX_PRICE DESCWHERE (A,B) IN (서브쿼리) 이용하기
고양이와 개는 몇 마리 있을까 lv.2
SELECT ANIMAL_TYPE, COUNT(*) AS count
FROM ANIMAL_INS
WHERE ANIMAL_TYPE IN ('Cat', 'Dog')
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE ASC동명 동물 수 찾기 lv.2
SELECT NAME, COUNT(*) AS COUNT
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING COUNT(*) >=2
ORDER BY NAME이름이 없는 동물은 집계에서 제외이므로 IS NOT NULL 빼먹으면 안된다.
제외해주세요 -> 처음부터 제외시키자.
년, 월, 성별 별 상품 구매 회원 수 구하기 lv.4
SELECT YEAR(SALES_DATE) AS YEAR, MONTH(SALES_DATE) AS MONTH, GENDER, COUNT(DISTINCT OS.USER_ID) AS USERS
FROM ONLINE_SALE AS OS
INNER JOIN USER_INFO AS UI ON (OS.USER_ID=UI.USER_ID)
WHERE GENDER IS NOT NULL
GROUP BY YEAR(SALES_DATE), MONTH(SALES_DATE), GENDER
ORDER BY YEAR, MONTH, GENDER성별 정보가 없는 경우 결과에서 제외 -> WHERE에서 처음부터 제거.
회원수를 구하므로 중복이 있을 수 있다. -> COUNT(DISTINCT OS.USER_ID)) 로 구하기
성장!