[데이터베이스] 프로그래머스 SQL 고득점 Kit 답안

2022. 10. 25. 17:25

1. SELECT

Lv1. 강원도에 위치한 생산공장 목록 출력하기

SELECT FACTORY_ID, FACTORY_NAME, ADDRESS 
FROM FOOD_FACTORY
WHERE ADDRESS like "%강원도%"
ORDER BY FACTORY_ID ASC

Lv1. 동물의 아이디와 이름

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC

Lv1. 모든 레코드 조회하기

SELECT  *
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC

Lv1. 상위 n개 레코드

SELECT NAME from ANIMAL_INS order by DATETIME asc limit 5

Lv1. 아픈 동물 찾기

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION = 'Sick'
ORDER BY ANIMAL_ID ASC

Lv1. 어린 동물 찾기

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS 
WHERE INTAKE_CONDITION != 'Aged'
ORDER BY ANIMAL_ID ASC

Lv1. 여러 기준으로 정렬하기

SELECT ANIMAL_ID, NAME, DATETIME 
FROM ANIMAL_INS 
ORDER BY NAME ASC, DATETIME DESC

Lv1. 역순 정렬하기

SELECT NAME, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC

Lv1. 조건에 맞는 회원수 구하기

SELECT COUNT(*)
    FROM USER_INFO 
    WHERE DATE_FORMAT(JOINED, '%Y') = '2021'
    AND (AGE >=20 and AGE<=29)

Lv2. 3월에 태어난 여성 회원 목록 출력하기

# SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH, "%Y-%m-%d") as DATE_OF_BIRTH
# FROM MEMBER_PROFILE
# WHERE TLNO is not NULL and DATE_FORMAT(DATE_OF_BIRTH, '%m') = '03' and GENDER='W'
# ORDER BY MEMBER_ID ASC

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 TLNO IS NOT NULL
    AND GENDER = 'W'
ORDER BY MEMBER_ID ASC

Lv2. 재구매가 일어난 상품과 회원 리스트 구하기

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

Lv4. 년, 월, 성별 별 상품 구매 회원 수 구하기

-- MySQL
SELECT YEAR(S.SALES_DATE) as YEAR, MONTH(S.SALES_DATE) as MONTH, I.GENDER as GENDER, count(DISTINCT(S.USER_ID)) as USERS
FROM USER_INFO as I RIGHT JOIN ONLINE_SALE as S ON (I.USER_ID = S.USER_ID)
WHERE I.GENDER IS NOT NULL
GROUP BY YEAR, MONTH, GENDER
ORDER BY YEAR, MONTH, GENDER ASC

Lv4. 서울에 위치한 식당 목록 출력하기

-- MySQL
SELECT I.REST_ID, I.REST_NAME, I.FOOD_TYPE, I.FAVORITES, I.ADDRESS, ROUND(AVG(R.REVIEW_SCORE), 2) as SCORE
FROM REST_INFO as I JOIN REST_REVIEW as R ON (I.REST_ID = R.REST_ID)
GROUP BY I.REST_ID
    HAVING I.ADDRESS LIKE '서울%'
ORDER BY SCORE DESC, I.FAVORITES DESC

Lv4. 오프라인,온라인 판매 데이터 통합하기

SELECT *
FROM (
        SELECT TO_CHAR(SALES_DATE, 'YYYY-MM-DD') as SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
        FROM ONLINE_SALE
        WHERE TO_CHAR(SALES_DATE, 'YYYY-MM') = '2022-03'
    UNION ALL
        SELECT TO_CHAR(SALES_DATE, 'YYYY-MM-dd') as SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
        FROM OFFLINE_SALE
        WHERE TO_CHAR(SALES_DATE, 'YYYY-MM') = '2022-03'
    )
ORDER BY SALES_DATE ASC, PRODUCT_ID ASC, USER_ID ASC

2. SUM, MAX, MIN

Lv1. 가장 비싼 상품 구하기

SELECT MAX(PRICE) as MAX_PRICE
FROM PRODUCT

Lv1. 최댓값 구하기

SELECT max(DATETIME)
FROM ANIMAL_INS

Lv2. 가격이 제일 비싼 식품의 정보 출력하기

-- 서브쿼리 이용해서 풀 것. 참고로 = 아닌 IN도 가능
SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE
FROM FOOD_PRODUCT
WHERE PRICE = (SELECT MAX(PRICE) FROM FOOD_PRODUCT)

Lv2. 동물 수 구하기

SELECT count(ANIMAL_ID)
FROM ANIMAL_INS

Lv2. 중복 제거하기

SELECT count(unique(NAME))
FROM ANIMAL_INS
WHERE NAME is not NULL

Lv2. 최솟값 구하기

SELECT MIN(DATETIME)
FROM ANIMAL_INS

3. GROUP BY

Lv2. 가격대 별 상품 개수 구하기

SELECT TRUNC(price / 10000)*10000 as PRICE_GROUP, count(TRUNC(price / 10000)) as PRODUCTS
FROM PRODUCT
GROUP BY TRUNC(price / 10000)
ORDER BY TRUNC(price / 10000) ASC

Lv2. 고양이와 개는 몇 마리 있을까

SELECT ANIMAL_TYPE, count(ANIMAL_TYPE)
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
order by ANIMAL_TYPE ASC

Lv2. 동명 동물 수 찾기

SELECT NAME, count(NAME) as COUNT
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
    HAVING count(*) >=2
ORDER BY NAME ASC

Lv2. 입양 시각 구하기(1)

-- SELECT TO_CHAR(DATETIME, 'HH24') as HOUR, count(TO_CHAR(DATETIME, 'HH24')) as COUNT
-- FROM ANIMAL_OUTS
-- WHERE TO_CHAR(DATETIME, 'HH24') BETWEEN '09' and '19'
-- GROUP BY TO_CHAR(DATETIME, 'HH24')
-- ORDER BY HOUR ASC

SELECT TO_CHAR(DATETIME, 'HH24') HOUR, COUNT(TO_CHAR(DATETIME, 'HH24')) COUNT
FROM ANIMAL_OUTS
GROUP BY TO_CHAR(DATETIME, 'HH24')
HAVING TO_CHAR(DATETIME, 'HH24') >= 9 and TO_CHAR(DATETIME, 'HH24') <= 19
ORDER BY TO_CHAR(DATETIME, 'HH24') ASC

Lv3. 즐겨찾기가 가장 많은 식당 정보 출력하기

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

Lv4. 입양 시각 구하기(2)

-- MySQL
SET @HOUR:= -1;
SELECT (@HOUR:= @HOUR+1) as HOUR, (SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @HOUR) as COUNT
FROM ANIMAL_OUTS
WHERE @HOUR < 23

-- Oracle
SELECT HOUR, COUNT(B.DATETIME) COUNT FROM (SELECT LEVEL-1 HOUR 
                  FROM DUAL 
                  CONNECT BY LEVEL<=24) 
                  A LEFT JOIN ANIMAL_OUTS B ON A.HOUR = TO_CHAR(B.DATETIME, 'HH24')
GROUP BY HOUR
ORDER BY HOUR ASC

4. IS NULL

Lv1. 경기도에 위치한 식품창고 목록 출력하기

-- SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, NVL(FREEZER_YN, 'N') as FREEZER_YN
-- FROM FOOD_WAREHOUSE
-- WHERE ADDRESS like '경기도%'
-- ORDER BY WAREHOUSE_ID ASC

-- SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, CASE WHEN FREEZER_YN IS NULL THEN 'N' ELSE FREEZER_YN END
-- FROM FOOD_WAREHOUSE
-- WHERE ADDRESS like '경기도%'
-- ORDER BY WAREHOUSE_ID ASC

SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, DECODE(FREEZER_YN, null, 'N', FREEZER_YN) as FREEZER_YN
FROM FOOD_WAREHOUSE
WHERE ADDRESS like '경기도%'
ORDER BY WAREHOUSE_ID ASC

Lv1. 나이 정보가 없는 회원 수 구하기

SELECT count(USER_ID) as USERS
FROM USER_INFO
WHERE AGE IS NULL

Lv1. 이름이 없는 동물의 아이디

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

Lv1. 이름이 있는 동물의 아이디

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

Lv2. NULL 처리하기

-- SELECT ANIMAL_TYPE, NVL(NAME, 'No name') as NAME, SEX_UPON_INTAKE
-- FROM ANIMAL_INS
-- ORDER BY ANIMAL_ID ASC

-- SELECT ANIMAL_TYPE, DECODE(NAME, null, 'No name', NAME) as NAME, SEX_UPON_INTAKE
-- FROM ANIMAL_INS
-- ORDER BY ANIMAL_ID ASC

SELECT ANIMAL_TYPE, CASE WHEN NAME IS NULL THEN 'No name' ELSE NAME END as NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC

5. JOIN

Lv2. 상품 별 오프라인 매출 구하기

SELECT PRODUCT_CODE, (PRICE * sum(S.SALES_AMOUNT)) AS SALES
FROM PRODUCT as P RIGHT JOIN OFFLINE_SALE as S ON P.PRODUCT_ID = S.PRODUCT_ID
GROUP BY S.PRODUCT_ID
ORDER BY SALES DESC, PRODUCT_CODE ASC

Lv3. 보호소에서 중성화한 동물

-- MYSQL
SELECT I.ANIMAL_ID, I.ANIMAL_TYPE, I.NAME
FROM ANIMAL_INS AS I INNER JOIN ANIMAL_OUTS AS O on I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.SEX_UPON_INTAKE like '%Intact%' and O.SEX_UPON_OUTCOME not like '%Intact%'

Lv3. 없어진 기록 찾기

-- Oracle
SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_OUTS O
WHERE O.ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_INS)
ORDER BY O.ANIMAL_ID, O.NAME ASC

-- Oracle 2
SELECT O.ANIMAL_ID, O.NAME 
FROM ANIMAL_OUTS O
WHERE NOT EXISTS (SELECT * FROM ANIMAL_INS I WHERE O.ANIMAL_ID = I.ANIMAL_ID)
ORDER BY ANIMAL_ID

-- MySQL 1
SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_OUTS AS O LEFT JOIN ANIMAL_INS AS I ON O.ANIMAL_ID = I.ANIMAL_ID
WHERE I.ANIMAL_ID IS NULL;

Lv3. 오랜 기간 보호한 동물(1)

-- MySQL 
SELECT NAME, DATETIME
FROM ANIMAL_INS
WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_OUTS)
ORDER BY DATETIME ASC
LIMIT 0, 3

-- ORACLE
SELECT A.NAME, A.DATETIME
FROM (
    SELECT I.NAME, I.DATETIME
    FROM ANIMAL_INS I LEFT OUTER JOIN ANIMAL_OUTS O ON (I.ANIMAL_ID = O.ANIMAL_ID)
    WHERE O.ANIMAL_ID IS NULL
    ORDER BY I.DATETIME ASC
) A
WHERE ROWNUM <= 3

Lv3. 있었는데요 없었습니다

-- ORACLE
SELECT I.ANIMAL_ID, I.NAME
FROM ANIMAL_INS I LEFT JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.DATETIME > O.DATETIME
GROUP BY I.ANIMAL_ID, I.NAME, I.DATETIME
ORDER BY I.DATETIME ASC

Lv4. 5월 식품들의 총매출 조회하기

SELECT P.PRODUCT_ID, P.PRODUCT_NAME, P.PRICE * sum(O.AMOUNT) as TOTAL_SALES
FROM FOOD_PRODUCT as P RIGHT JOIN FOOD_ORDER as O ON (P.PRODUCT_ID = O.PRODUCT_ID)
WHERE YEAR(PRODUCE_DATE) = 2022 and MONTH(PRODUCE_DATE) = 05 and P.PRODUCT_ID IS NOT NULL
GROUP BY P.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, PRODUCT_ID ASC

Lv4. 그룹별 조건에 맞는 식당 목록출력하기

-- ORACLE
SELECT P.MEMBER_NAME AS MEMBER_NAME, R.REVIEW_TEXT AS REVIEW_TEXT, TO_CHAR(R.REVIEW_DATE, 'YYYY-MM-DD') AS REVIEW_DATE
FROM MEMBER_PROFILE P INNER JOIN REST_REVIEW R ON P.MEMBER_ID = R.MEMBER_ID
WHERE R.MEMBER_ID IN (SELECT MEMBER_ID -- 리뷰 쓴 개수가 COUNT(*)인 경우에 대해 GROUP BY후 사람들 ID 출력
                       FROM REST_REVIEW
                       GROUP BY MEMBER_ID -- 사람들이 리뷰 쓴 개수가 COUNT(*)인 경우에 대해 GROUP BY. 
                       HAVING COUNT(*) = (SELECT MAX(COUNT(*)) -- 최대 리뷰 쓴 사람들의 개수를 구함
                                          FROM REST_REVIEW
                                          GROUP BY MEMBER_ID)
                        )
ORDER BY R.REVIEW_DATE

Lv5. 상품을 구매한 회원 비율 구하기

-- MySQL
SELECT YEAR(SALES_DATE) as YEAR, MONTH(SALES_DATE) as MONTH, count(DISTINCT(I.USER_ID)) as PUCHASED_USERS, ROUND(count(DISTINCT(I.USER_ID)) / (SELECT count(DISTINCT(USER_ID)) FROM USER_INFO WHERE YEAR(JOINED) = 2021),1) as PUCHASED_RATIO
FROM USER_INFO AS I RIGHT JOIN ONLINE_SALE as S ON (I.USER_ID = S.USER_ID)
WHERE I.USER_ID in (SELECT USER_ID FROM USER_INFO WHERE YEAR(JOINED) = 2021)
GROUP BY YEAR(SALES_DATE), MONTH(SALES_DATE)
ORDER BY YEAR, MONTH ASC

6. String, Date

Lv2. 오랜 기간 보호한 동물(2)

SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_INS A, ANIMAL_OUTS B
WHERE A.ANIMAL_ID = B.ANIMAL_ID
ORDER BY DATEDIFF(A.DATETIME, B.DATETIME) limit 0, 2

Lv2. 이름에 el이 없는 동물 찾기

-- MySQL 1
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE ANIMAL_TYPE = 'Dog' and (NAME like '%el%' or NAME like '%EL')
ORDER BY NAME ASC

-- MySQL 2
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE ANIMAL_TYPE = 'Dog' AND UPPER(NAME) LIKE '%EL%'
ORDER BY UPPER(NAME) ASC

Lv2. 중성화 여부 파악하기

SELECT ANIMAL_ID, NAME, CASE WHEN SEX_UPON_INTAKE like '%Neutered%' or SEX_UPON_INTAKE like '%Spayed%' THEN 'O' ELSE 'X' END as 중성화
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

Lv2. 카테고리 별 상품 개수 구하기

SELECT SUBSTRING(PRODUCT_CODE, 1, 2) as CATEGORY, count(SUBSTRING(PRODUCT_CODE, 1, 2)) as PRODUCTS
FROM PRODUCT
GROUP BY SUBSTRING(PRODUCT_CODE, 1, 2)

Lv2. DATETIME에서 DATE로 형 변환

-- ORACLE
SELECT ANIMAL_ID, NAME, TO_CHAR(DATETIME, 'YYYY-MM-DD') as 날짜
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC

-- MYSQL
SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') AS 날짜
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC

Lv3. 조건별로 분류하여 주문상태 출력하기

SELECT ORDER_ID, PRODUCT_ID, DATE_FORMAT(OUT_DATE, '%Y-%m-%d'), 
IF (OUT_DATE <= '2022-05-01', '출고완료', IF(OUT_DATE IS NULL, '출고미정', '출고대기')) as 출고여부
FROM FOOD_ORDER
ORDER BY ORDER_ID ASC

저작자표시

'Computer Science > 데이터베이스' 카테고리의 다른 글

[데이터베이스] Pymongo 쿼리 파이프라인 작성 (0)	2023.06.11

For a better world