프로그래머스 문제 모음(Level2~4)[SQL]

- 서울에 위치한 식당목록

- Level : 4

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

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 AS a
    INNER JOIN rest_review AS b
    ON a.rest_id = b.rest_id
    WHERE a.address LIKE "서울%"
    GROUP BY a.rest_id
    ORDER BY score DESC, a.favorites DESC

 

 

 

- 보호소에서 중성화한 동물

- Level : 4

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

 

(Join Sol)

SELECT outs.animal_id, outs.animal_type, outs.name
    FROM animal_outs as outs
    INNER JOIN animal_ins as ins
    ON outs.animal_id = ins.animal_id
    WHERE outs.sex_upon_outcome NOT LIKE "Intact%" AND
          ins.sex_upon_intake LIKE "Intact%"
    ORDER BY 1

(Subquery sol)

SELECT animal_id, animal_type, name
    FROM animal_outs as outs
    WHERE sex_upon_outcome NOT LIKE "Intact%" AND
        animal_id IN
        (SELECT animal_id
            FROM animal_ins
            WHERE sex_upon_intake LIKE "Intact%")
    ORDER BY 1

 

- 없어진 기록 찾기

- Level : 3

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

SELECT outs.animal_id, outs.name
    FROM animal_ins as ins
    RIGHT OUTER JOIN animal_outs as outs
    ON ins.animal_id = outs.animal_id
    WHERE ins.animal_id IS NULL

 

 

- 헤비 유저가 소유한 장소

- Level : 3

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

SELECT id, name, host_id
    FROM places
    WHERE host_id IN (
        SELECT host_id
            FROM places
            GROUP BY host_id
            HAVING COUNT(id) >= 2
    )
    ORDER BY id

 

 

 

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

- Level : 3 (다시 보기)

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

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 1 DESC

★GROUP BY 로 묶을때 select 절의 컬럼이 GROUP BY 로 묶는 컬럼 이외의 값이 있다면

ex) SELECT rest_id, food_type, MAX(favorites)

첫번째 컬럼의 첫번째 값을 가져오는 것에 대해 주의하자

 

 

 

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

- Level : 3

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

SELECT ins.animal_id, ins.name
    FROM animal_ins as ins
    INNER JOIN animal_outs as outs
    ON ins.animal_id = outs.animal_id
    ORDER BY outs.datetime - ins.datetime DESC
    limit 2

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

- Level : 3

- https://school.programmers.co.kr/learn/courses/30/lessons/59044?

SELECT a.name, a.datetime
    FROM ANIMAL_INS as a
    LEFT JOIN ANIMAL_OUTS as b
    ON a.animal_id = b.animal_id
    WHERE b.datetime IS NULL
    ORDER BY 2 ASC
    LIMIT 3

 

- 이름에 el이 들어가는 동물 찾기

- Level : 3

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

 

SELECT animal_id, name
    FROM ANIMAL_INS
    WHERE name LIKE "%el%"
    AND animal_type = "dog"
    ORDER BY 2

 

- DATETIME에서 DATE로 형 변환

- Level : 2

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

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