프로그래머스 SQL 고득점 kit 사이트
모든 레코드 조회하기
Level 1
내 답안
SELECT *
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC;역순 정렬하기
Level 1
내 답안
SELECT NAME
, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC;동물의 아이디와 이름
Level 1
내 답안
SELECT ANIMAL_ID
, NAME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC;평균 일일 대여 요금 구하기
Level 1
내 답안
SELECT ROUND(AVG(daily_fee)) AS AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR
WHERE car_type = 'SUV';인기있는 아이스크림
Level 1
내 답안
SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID ASC;조건에 맞는 도서 리스트 출력하기
Level 1
내 답안
SELECT BOOK_ID
, DATE_FORMAT(published_date, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK
WHERE YEAR(PUBLISHED_DATE) = 2021
AND CATEGORY = '인문';12세 이하인 여자 환자 목록 출력하기
Level 1
내 답안
SELECT PT_NAME
, PT_NO
, GEND_CD
, AGE
, CASE
WHEN TLNO IS NULL THEN 'NONE'
ELSE TLNO
END AS TLNO
FROM PATIENT
WHERE AGE <= 12
AND GEND_CD = 'W'
ORDER BY AGE DESC, PT_NAME ASC;SELECT 절에 CASE 문을 사용해 TLNO 컬럼에 NONE값 추가
과일로 만든 아이스크림 고르기
Level 1
내 답안
SELECT FH.FLAVOR
FROM FIRST_HALF AS FH
LEFT JOIN ICECREAM_INFO AS II ON FH.FLAVOR = II.FLAVOR
WHERE FH.TOTAL_ORDER >= 3000
AND II.INGREDIENT_TYPE = 'fruit_based'
ORDER BY TOTAL_ORDER DESC;흉부외과 또는 일반외과 의사 목록 출력하기
Level 1
내 답안
SELECT DR_NAME
, DR_ID
, MCDP_CD
, DATE_FORMAT(HIRE_YMD, '%Y-%m-%d') AS HIRE_YMD
FROM DOCTOR
WHERE MCDP_CD IN ('GS', 'CS')
ORDER BY HIRE_YMD DESC, DR_NAME ASC;WHERE절에 OR 조건을 주기보다는, IN을 썼다
3월에 태어난 여성 회원 목록 출력하기
Level 2
내 답안
SELECT MEMBER_ID
, MEMBER_NAME
, GENDER
, DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE GENDER = 'W'
AND MONTH(DATE_OF_BIRTH) = 3
AND TLNO IS NOT NULL
ORDER BY MEMBER_ID ASC;강원도에 위치한 생산공장 목록 출력하기
Level 1
내 답안
SELECT FACTORY_ID
, FACTORY_NAME
, ADDRESS
FROM FOOD_FACTORY
WHERE ADDRESS LIKE '%강원도%'
ORDER BY FACTORY_ID ASC;서울에 위치한 식당 목록 출력하기
Level 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 ON RI.REST_ID = RR.REST_ID
WHERE ADDRESS LIKE '서울%'
GROUP BY RI.REST_NAME
ORDER BY SCORE DESC, RI.FAVORITES DESC;주의
WHERE이나 HAVING절에 LIKE '%서울%'를 하면 틀렸다고 나온다.
'서울%'로 해야한다.
조건에 부합하는 중고거래 댓글 조회하기
Level 1
내 답안
SELECT UGB.TITLE
, UGB.BOARD_ID
, UGR.REPLY_ID
, UGR.WRITER_ID
, UGR.CONTENTS
, DATE_FORMAT(UGR.CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE
FROM USED_GOODS_BOARD AS UGB
INNER JOIN USED_GOODS_REPLY AS UGR ON UGB.BOARD_ID = UGR.BOARD_ID
WHERE UGB.CREATED_DATE BETWEEN '2022-10-01 00:00:00' AND '2022-10-31 23:59:59'
ORDER BY CREATED_DATE ASC, UGB.TITLE ASC;재구매가 일어난 상품과 회원 리스트 구하기
Level 2
내 답안
SELECT USER_ID
, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*) >= 2
ORDER BY USER_ID ASC, PRODUCT_ID DESC;오프라인/온라인 판매 데이터 통합하기
Level 4
내 답안
SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE
, PRODUCT_ID
, USER_ID
, SALES_AMOUNT
FROM ONLINE_SALE
WHERE SALES_DATE BETWEEN '2022-03-01 00:00:00' AND '2022-03-31 23:59:59'
UNION ALL
SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE
, PRODUCT_ID
, NULL AS USER_ID
, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE SALES_DATE BETWEEN '2022-03-01 00:00:00' AND '2022-03-31 23:59:59'
ORDER BY SALES_DATE ASC, PRODUCT_ID ASC, USER_ID ASC주의
필자는 문제에서 NULL로 표시해달라 그래서 문자열인 'NULL'을 넣었는데 틀렸다고 나왔다. 문자열 'NULL'이 아닌 NULL을 집어넣어야된다.
아픈 동물 찾기
Level 1
내 답안
SELECT ANIMAL_ID
, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION = 'Sick'어린 동물 찾기
Level 1
내 답안
SELECT ANIMAL_ID
, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != 'Aged'여러 기준으로 정렬하기
Level 1
내 답안
SELECT ANIMAL_ID
, NAME
, DATETIME
FROM ANIMAL_INS
ORDER BY NAME ASC, DATETIME DESC;상위 n개 레코드
Level 1
내 답안
SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME ASC
LIMIT 1;업그레이드 된 아이템 구하기
Level 2
내 답안
SELECT II.ITEM_ID
, II.ITEM_NAME
, II.RARITY
FROM ITEM_INFO AS II
INNER JOIN ITEM_TREE AS IT ON II.ITEM_ID = IT.ITEM_ID
WHERE IT.PARENT_ITEM_ID IN (SELECT ITEM_ID
FROM ITEM_INFO
WHERE RARITY='RARE')
ORDER BY ITEM_ID DESC;조건에 맞는 개발자 찾기
Level 2
내 답안
SELECT D.ID
, D.EMAIL
, D.FIRST_NAME
, D.LAST_NAME
FROM DEVELOPERS AS D
WHERE (SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME = 'Python')) != 0
OR (SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME = 'C#')) != 0
ORDER BY D.ID ASC;잔챙이 잡은 수 구하기
Level 1
내 답안
SELECT COUNT(ID) AS FISH_COUNT
FROM FISH_INFO
WHERE LENGTH IS NULL;가장 큰 물고기 10마리 구하기
Level 1
내 답안
SELECT ID
, LENGTH
FROM FISH_INFO
ORDER BY LENGTH DESC, ID ASC
LIMIT 10;특정 물고기를 잡은 총 수 구하기
Level 2
내 답안
SELECT COUNT(ID) AS FISH_COUNT
FROM FISH_INFO AS FI
INNER JOIN FISH_NAME_INFO AS FNI ON FI.FISH_TYPE = FNI.FISH_TYPE
WHERE FNI.FISH_NAME = 'BASS'
OR FNI.FISH_NAME = 'SNAPPER'대장균들의 자식의 수 구하기
Level 3
내 답안
WITH NEW_ECOLI AS (
SELECT ED1.ID AS ID1
, ED1.PARENT_ID AS PID
, ED2.ID AS ID2
, ED2.PARENT_ID AS CID
FROM ECOLI_DATA AS ED1
LEFT JOIN ECOLI_DATA AS ED2 ON ED2.PARENT_ID = ED1.ID
)
SELECT ID1 AS ID
, COUNT(CID) AS CHILD_COUNT
FROM NEW_ECOLI
GROUP BY ID1
ORDER BY ID1 ASC;대장균의 크기에 따라 분류하기 1
Level 3
내 답안
SELECT ID
, CASE
WHEN SIZE_OF_COLONY <= 100 THEN 'LOW'
WHEN SIZE_OF_COLONY <= 1000 THEN 'MEDIUM'
ELSE 'HIGH'
END AS SIZE
FROM ECOLI_DATA
ORDER BY ID ASC;특정 형질을 가지는 대장균 찾기
Level 1
내 답안
SELECT COUNT(ID) AS COUNT
FROM ECOLI_DATA
WHERE GENOTYPE & 2 = 0
AND (GENOTYPE & 1 != 0 OR GENOTYPE & 4 != 0)대장균의 크기에 따라 분류하기 2
Level 3
내 답안
WITH new_table AS (
SELECT *
, ROW_NUMBER() OVER() AS RNK
, MAX(ID) OVER() AS MAX_RNK
FROM ECOLI_DATA
ORDER BY SIZE_OF_COLONY DESC
), final_table AS (
SELECT ID
, (RNK / MAX_RNK) * 100 AS PCT
FROM new_table
)
SELECT ID
, CASE
WHEN PCT <= 25 THEN 'CRITICAL'
WHEN PCT <= 50 THEN 'HIGH'
WHEN PCT <= 75 THEN 'MEDIUM'
ELSE 'LOW'
END AS 'COLONY_NAME'
FROM final_table
ORDER BY ID ASC;특정 세대의 대장균 찾기
Level 4
내 답안
WITH FIRST_SECOND_GENERATION_TABLE AS (
SELECT ED1.*
, ED2.ID AS ID2
, ED2.PARENT_ID AS PARENT_ID2
FROM ECOLI_DATA AS ED1
LEFT JOIN ECOLI_DATA AS ED2 ON ED1.ID = ED2.PARENT_ID
)
SELECT DISTINCT ED3.ID AS ID
FROM FIRST_SECOND_GENERATION_TABLE AS FSGT
LEFT JOIN ECOLI_DATA AS ED3 ON FSGT.ID2 = ED3.PARENT_ID
WHERE FSGT.PARENT_ID IS NULL
AND ED3.ID IS NOT NULL
ORDER BY ID ASC;부모의 형질을 모두 가지는 대장균 찾기
Level 2
내 답안
WITH NEW_ECOLI_DATA AS (
SELECT ED2.ID AS CHILD_ID
, ED2.GENOTYPE AS CHILD_GENOTYPE
, ED1.ID AS PARENT_ID
, ED1.GENOTYPE AS PARENT_GENOTYPE
FROM ECOLI_DATA AS ED1
INNER JOIN ECOLI_DATA AS ED2 ON ED2.PARENT_ID = ED1.ID
)
SELECT CHILD_ID AS ID
, CHILD_GENOTYPE AS GENOTYPE
, PARENT_GENOTYPE
FROM NEW_ECOLI_DATA
WHERE CHILD_GENOTYPE & PARENT_GENOTYPE = PARENT_GENOTYPE
Hi