[240604] SQL 코딩테스트

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

https://school.programmers.co.kr/learn/courses/30/lessons/59045

프로그래머스

SELECT a.ANIMAL_ID, a.ANIMAL_TYPE, a.NAME
FROM (SELECT ANIMAL_ID, ANIMAL_TYPE, NAME
FROM ANIMAL_INS
WHERE SEX_UPON_INTAKE LIKE 'Intact%') as a
JOIN 
(SELECT ANIMAL_ID, ANIMAL_TYPE, NAME
FROM ANIMAL_OUTS
WHERE SEX_UPON_OUTCOME LIKE 'Spayed%' or SEX_UPON_OUTCOME LIKE 'Neutered%') as b
ON a.ANIMAL_ID = b.ANIMAL_ID
ORDER BY a.ANIMAL_ID

 

---

2. 식품분류별 가장 비싼 식품의 정보 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/131116

프로그래머스

SELECT CATEGORY, PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY, PRICE) IN 
(SELECT CATEGORY, MAX(PRICE) as 'MAX_PRICE'
FROM FOOD_PRODUCT
WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
GROUP BY CATEGORY)
ORDER BY PRICE desc

 

---

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

https://school.programmers.co.kr/learn/courses/30/lessons/131117

프로그래머스

SELECT a.PRODUCT_ID, b.PRODUCT_NAME, a.TOTAL_AMOUT * b.PRICE as 'TOTAL_SALES'
FROM (SELECT PRODUCT_ID, SUM(AMOUNT) as 'TOTAL_AMOUT'
FROM FOOD_ORDER
WHERE DATE_FORMAT(PRODUCE_DATE, "%Y-%m") = '2022-05'
GROUP BY PRODUCT_ID) as a JOIN FOOD_PRODUCT as b
ON a.PRODUCT_ID = b.PRODUCT_ID
ORDER BY TOTAL_SALES desc, a.PRODUCT_ID asc

 

---

4. 취소되지 않은 진로 예약 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/132204

프로그래머스

SELECT a.APNT_NO,b.PT_NAME, b.PT_NO, a.MCDP_CD, c.DR_NAME, a.APNT_YMD
FROM (SELECT APNT_NO, PT_NO, APNT_YMD, MDDR_ID, MCDP_CD
FROM APPOINTMENT
WHERE DATE_FORMAT(APNT_YMD, "%Y-%m-%d") = '2022-04-13'
AND APNT_CNCL_YN = 'N' AND MCDP_CD = 'CS') as a
LEFT JOIN PATIENT as b ON a.PT_NO = b.PT_NO
LEFT JOIN DOCTOR as c on a.MDDR_ID = c.DR_ID
ORDER BY a.APNT_YMD

 

---

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

https://school.programmers.co.kr/learn/courses/30/lessons/131118

프로그래머스

SELECT a.REST_ID, a.REST_NAME, a.FOOD_TYPE, a.FAVORITES, a.ADDRESS, b.SCORE
FROM (SELECT REST_ID, REST_NAME, FOOD_TYPE, FAVORITES, ADDRESS
FROM REST_INFO
WHERE ADDRESS LIKE "서울%") as a JOIN 
(SELECT REST_ID, ROUND(AVG(REVIEW_SCORE),2) as 'SCORE' 
 FROM REST_REVIEW GROUP BY REST_ID) as b
ON a.REST_ID = b.REST_ID
ORDER BY b.SCORE desc, a.FAVORITES desc

 

---

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

https://school.programmers.co.kr/learn/courses/30/lessons/131532

프로그래머스

SELECT YEAR(a.SALES_DATE) as 'YEAR', MONTH(SALES_DATE) as 'MONTH', b.GENDER, 
    COUNT(DISTINCT(a.USER_ID)) as 'USERS'
FROM ONLINE_SALE as a JOIN 
(SELECT * FROM USER_INFO WHERE GENDER IS NOT NULL) as b
ON a.USER_ID = b.USER_ID
GROUP BY YEAR(a.SALES_DATE), MONTH(SALES_DATE), b.GENDER
ORDER BY YEAR, MONTH, GENDER

 

---

7. 저자 별 카테고리 별 매출액 집계하기

https://school.programmers.co.kr/learn/courses/30/lessons/144856

프로그래머스

SELECT b.author_id, c.AUTHOR_NAME, b.category, 
    SUM(a.sales * b.price) as 'TOTAL_SALES'
FROM (SELECT *
FROM BOOK_SALES
WHERE DATE_FORMAT(SALES_DATE, "%Y-%m") = '2022-01') as a
LEFT JOIN BOOK as b ON a.book_id = b.book_id
LEFT JOIN AUTHOR as c ON b.AUTHOR_ID = c.AUTHOR_ID
GROUP BY b.author_id, b.category
ORDER BY b.author_id, b.category desc

 

---

8. 우유와 요거트가 담긴 장바구니

https://school.programmers.co.kr/learn/courses/30/lessons/62284

프로그래머스

SELECT CART_ID
FROM CART_PRODUCTS
WHERE NAME IN ('Milk', 'Yogurt')
GROUP BY CART_ID
HAVING COUNT(DISTINCT NAME ) >= 2
ORDER BY CART_ID

 

---

9. 주문량이 많은 아이스크림들 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/133027

프로그래머스

SELECT FLAVOR
FROM (SELECT * 
FROM FIRST_HALF 
UNION ALL 
SELECT * 
FROM JULY) as a 
GROUP BY FLAVOR
ORDER BY SUM(TOTAL_ORDER) desc 
limit 3

 

---

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

https://school.programmers.co.kr/learn/courses/30/lessons/131124

프로그래머스

WITH MAX_REVIEW AS (SELECT MAX(a.review_count) as 'max_review'
FROM (SELECT COUNT(*) as 'review_count'
FROM REST_REVIEW
GROUP BY MEMBER_ID) as a)

SELECT c.MEMBER_NAME, d.REVIEW_TEXT, 
DATE_FORMAT(REVIEW_DATE, "%Y-%m-%d") as 'REVIEW_DATE'
FROM (SELECT MEMBER_ID
FROM REST_REVIEW
GROUP BY MEMBER_ID
HAVING COUNT(*) IN (SELECT max_review FROM MAX_REVIEW)) as b 
LEFT JOIN MEMBER_PROFILE as c ON b.MEMBER_ID = c.MEMBER_ID
LEFT JOIN REST_REVIEW as d ON d.MEMBER_ID = b.MEMBER_ID
ORDER BY REVIEW_DATE, d.REVIEW_TEXT

 

---

11. 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/284528

프로그래머스

SELECT a.EMP_NO, b.EMP_NAME,
    CASE WHEN avg_score >= 96 then 'S'
    WHEN avg_score < 96 and avg_score >= 90 then 'A'
    WHEN avg_score < 90 and avg_score >= 80 then 'B'
    ELSE 'C' END AS 'GRADE',
    CASE WHEN avg_score >= 96 then b.SAL * 0.2
    WHEN avg_score < 96 and avg_score >= 90 then b.SAL * 0.15
    WHEN avg_score < 90 and avg_score >= 80 then b.SAL * 0.1
    ELSE 0 END AS 'BONUS'
FROM (SELECT EMP_NO, AVG(SCORE) as 'avg_score'
FROM HR_GRADE
GROUP BY EMP_NO) as a LEFT JOIN HR_EMPLOYEES as b ON a.EMP_NO = b.EMP_NO
ORDER BY EMP_NO

 

 

---

12. 오프라인 / 온라인 판매 데이터 통합하기

• 결측값 처리할 때 => 'NULL' 아니고 NULL 이라고 처리해줘야 함

https://school.programmers.co.kr/learn/courses/30/lessons/131537

프로그래머스

WITH ONLINE_DATA AS (SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') as 'SALES_DATE', 
    PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE DATE_FORMAT(SALES_DATE, "%Y-%m") = '2022-03'),

OFFLINE_DATA AS (SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') as 'SALES_DATE',
         PRODUCT_ID, NULL as 'USER_ID', SALES_AMOUNT
FROM OFFLINE_SALE
WHERE DATE_FORMAT(SALES_DATE, "%Y-%m") = '2022-03')

SELECT *
FROM (SELECT *
FROM ONLINE_DATA
UNION ALL 
SELECT *
FROM OFFLINE_DATA) as a
ORDER BY a.SALES_DATE, a.PRODUCT_ID, a.USER_ID

 

---

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

https://school.programmers.co.kr/learn/courses/30/lessons/59413

프로그래머스

WITH RECURSIVE q AS
        (
        SELECT  0 AS num
        UNION ALL
        SELECT  num + 1
        FROM    q
        WHERE   num <= 22
        )

SELECT q.num as 'HOUR', IF (a.COUNT is null, 0, a.COUNT) as 'COUNT'
FROM q LEFT JOIN (SELECT HOUR(DATETIME) as 'num', COUNT(*) as 'COUNT'
FROM ANIMAL_OUTS
GROUP BY HOUR(DATETIME)) as a
ON q.num = a.num
ORDER BY q.num