SQL 프로그래머스 예제풀이(SELECT)

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

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

#답1
SELECT DISTINCT A.USER_ID, A.PRODUCT_ID FROM ONLINE_SALE A, ONLINE_SALE B
WHERE A.USER_ID = B.USER_ID AND A.PRODUCT_ID = B.PRODUCT_ID AND A.ONLINE_SALE_ID != B.ONLINE_SALE_ID
ORDER BY USER_ID, PRODUCT_ID DESC

#답2
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

 

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

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

#답1
SELECT A.FLAVOR FROM FIRST_HALF A, ICECREAM_INFO B
WHERE EXISTS (SELECT * FROM ICECREAM_INFO WHERE A.TOTAL_ORDER > 3000 AND B.INGREDIENT_TYPE = "fruit_based" AND A.FLAVOR = B.FLAVOR)
ORDER BY A.TOTAL_ORDER

#답2
SELECT A.FLAVOR FROM FIRST_HALF A
LEFT JOIN ICECREAM_INFO B ON A.FLAVOR = B.FLAVOR
WHERE A.TOTAL_ORDER > 3000 AND B.INGREDIENT_TYPE = "fruit_based"
ORDER BY A.TOTAL_ORDER DESC

 

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

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

#답1
SELECT A.REST_ID, A.REST_NAME, A.FOOD_TYPE, A.FAVORITES, A.ADDRESS, ROUND(AVG(REVIEW_SCORE),2) AS SCORE
FROM REST_INFO A
RIGHT JOIN REST_REVIEW B ON A.REST_ID = B.REST_ID
WHERE LEFT(ADDRESS, 2) = "서울"
GROUP BY B.REST_ID
ORDER BY SCORE DESC, FAVORITES DESC

#답2
SELECT A.REST_ID, A.REST_NAME, A.FOOD_TYPE, A.FAVORITES, A.ADDRESS, ROUND(AVG(B.REVIEW_SCORE),2) AS SCORE
FROM REST_INFO A, REST_REVIEW B
WHERE EXISTS (SELECT * FROM REST_REVIEW WHERE A.REST_ID = B.REST_ID AND LEFT(ADDRESS,2) = "서울")
GROUP BY B.REST_ID
ORDER BY SCORE DESC, FAVORITES DESC

 

조건에 부합하는 중고거래 댓글 조회하기

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

#1
SELECT TITLE, A.BOARD_ID, REPLY_ID, B.WRITER_ID, B.CONTENTS, DATE_FORMAT(B.CREATED_DATE,'%Y-%m-%d') AS CREATED_DATE
FROM USED_GOODS_BOARD A 
INNER JOIN USED_GOODS_REPLY B ON A.BOARD_ID = B.BOARD_ID
WHERE YEAR(A.CREATED_DATE) = 2022 AND MONTH(A.CREATED_DATE) = 10
ORDER BY B.CREATED_DATE, TITLE

 

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

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

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 ALL
SELECT DATE_FORMAT(SALES_DATE,"%Y-%m-%d") as SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT FROM OFFLINE_SALE
WHERE YEAR(SALES_DATE) = 2022 AND MONTH(SALES_DATE) = 3
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID

 

대장균들의 자식의 수 구하기

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

SELECT A.ID, COALESCE(COUNT(B.ID),0) AS CHILD_COUNT FROM ECOLI_DATA A
LEFT JOIN ECOLI_DATA B ON A.ID = B.PARENT_ID
GROUP BY A.ID
ORDER BY ID

 

대장균의 크기에 따라 분류하기1

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

SELECT ID, 
CASE 
    WHEN SIZE_OF_COLONY <= 100 THEN "LOW"
    WHEN SIZE_OF_COLONY <= 1000 THEN "MEDIUM"
    WHEN SIZE_OF_COLONY >1000 THEN "HIGH"
END AS SIZE
FROM ECOLI_DATA
ORDER BY ID

 

특정 형질을 가지는 대장균 찾기

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

#1
SELECT COUNT(BIN(GENOTYPE)) AS COUNT FROM ECOLI_DATA
WHERE LEFT(RIGHT(LPAD(BIN(GENOTYPE),1000,0),2),1) = 0 AND
(
    (LEFT(RIGHT(LPAD(BIN(GENOTYPE),1000,0),3),1) = 1) 
    OR 
    (RIGHT(LPAD(BIN(GENOTYPE),1000,0),1) = 1)
)

#2 비트연산자
SELECT
    COUNT(ID) COUNT
FROM
    ECOLI_DATA
WHERE
    GENOTYPE & 5         -- 0101
    AND NOT GENOTYPE & 2 -- 0010

 

특정 물고기를 잡은 총 수 구하기

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

#1
SELECT COUNT(ID) AS FISH_COUNT FROM FISH_INFO A
INNER JOIN FISH_NAME_INFO B ON A.FISH_TYPE = B.FISH_TYPE
WHERE B.FISH_NAME = "BASS" OR B.FISH_NAME = "SNAPPER"

#2
SELECT COUNT(ID) AS FISH_COUNT FROM FISH_INFO A
WHERE EXISTS (SELECT * FROM FISH_NAME_INFO B WHERE A.FISH_TYPE = B.FISH_TYPE AND (B.FISH_NAME = "BASS" OR B.FISH_NAME = "SNAPPER")) 

 

멸종 위기의 대장균 찾기

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

WITH RECURSIVE CTE AS (
    SELECT A.ID, A.PARENT_ID, 1 AS GEN FROM ECOLI_DATA A
    WHERE PARENT_ID IS NULL
    
    UNION ALL
    
    SELECT B. ID, B. PARENT_ID, (CTE.GEN + 1) AS GEN FROM ECOLI_DATA B
    JOIN CTE ON CTE.ID = B.PARENT_ID
)

SELECT COUNT(A.ID)AS COUNT, A.GEN AS GENERATION FROM CTE A
LEFT JOIN ECOLI_DATA C ON A.ID = C.PARENT_ID
WHERE C.ID IS NULL
GROUP BY A.GEN
ORDER BY A.GEN

#2
WITH RECURSIVE CTE AS (
    SELECT ID, PARENT_ID, 1 AS GEN FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL
    
    UNION ALL
    
    SELECT A.ID, A.PARENT_ID, CTE.GEN + 1 FROM CTE
    INNER JOIN ECOLI_DATA A ON CTE.ID = A.PARENT_ID
)

SELECT COUNT(A.ID) AS COUNT, A.GEN AS GENERATION FROM CTE A
LEFT JOIN ECOLI_DATA B ON A.ID = B.PARENT_ID
WHERE B.ID IS NULL
GROUP BY A.GEN
ORDER BY A.GEN