Programmers SQL 문제 풀이 51 ~ 60

없어진 기록 찾기

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

-- 1. 입양 기록에 있는 동물
-- 2. 보호소 기록에는 없는 동물
-- 3. ID와 이름을 ID순으로 조회
-- --------------------------
-- 1. 보호소 기록 테이블과 입양 기록 테이블을 JOIN한다.
-- 2. 보호소 기록의 동물 ID가 NULL인 경우를 조건으로 건다.
-- 3. 입양 기록 테이블의 동물 ID, 이름을 조회한다.
-- 4. ID를 기준으로 오름차순 정렬한다.
SELECT t2.animal_id, t2.name
FROM animal_ins t1
    RIGHT JOIN animal_outs t2
    ON t1.animal_id = t2.animal_id
WHERE t1.animal_id IS NULL
ORDER BY 1

 

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

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

-- 1. 상반기 아이스크림 총주문량이 3000보다 높음
-- 2. 아이스크림 주 성분이 과일
-- 3. 총주문량이 큰 순서대로 조회
-- -----------------------------------------
-- 1. 주문 정보를 담은 테이블에서 총주문량(total_order)에 3000 초과라는 조건을 걸고 flavor만 추출한다.
-- 2. 성분에 대한 정보를 담은 테이블에서 ingredient_type이 fruit_base라는 조건과 flavor가 1번에서 추출한 데이터와 같다는 조건을 걸고 flavor를 출력한다.

SELECT flavor
FROM icecream_info
WHERE ingredient_type = "fruit_based"
    AND flavor IN (SELECT flavor
                  FROM first_half
                  WHERE total_order > 3000)

 

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

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

-- 1. 동일한 회원이 동일한 상품을 재구매한 데이터
-- 2. 재구매한 회원 ID와 재구매한 상품 ID를 출력
-- 3. 결과는 회원 ID를 기준으로 오름차순 정렬, 상품 ID를 기준으로 내림차순 정렬
-- -------------------------------------------------------------------
SELECT user_id, product_id
FROM (
    SELECT user_id, product_id, count(online_sale_id) as total_count
    FROM online_sale
    GROUP BY user_id, product_id
) t1
WHERE total_count > 1
ORDER BY user_id, product_id DESC

 

최댓값 구하기

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

SELECT max(datetime)
FROM animal_ins

 

조건에 맞는 사용자 정보 조회하기

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

-- 1. 중고 거래 게시물을 3건 이상 등록한 사용자
-- 2. 사용자 ID, 닉네임, 전체주소, 전화번호를 조회
-- 3. 전체 주소는 시, 도로명 주소, 상세 주소 함께 출력
-- 4. 전화번호는 xxx-xxxx-xxxx형태로 -를 삽입하여 출력
-- 5. 결과는 회원 ID를 기준으로 내림차순 정렬
-- -----------------------------------------------
SELECT user_id, nickname, CONCAT(city, ' ', street_address1, ' ', street_address2), 
       CONCAT(LEFT(tlno, 3), '-', MID(tlno, 4, 4), '-', RIGHT(tlno, 4))
FROM used_goods_user
WHERE user_id IN (
    SELECT writer_id
    FROM used_goods_board
    GROUP BY writer_id
    HAVING count(board_id) > 2
)
ORDER BY user_id DESC

 

특정 옵션이 포함된 자동차 리스트 구하기

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

SELECT *
FROM car_rental_company_car
WHERE options LIKE '%네비게이션%'
ORDER BY car_id DESC

 

조건에 부합하는 중고거래 상태 조회하기

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

SELECT board_id, writer_id, title, price, 
    CASE
        WHEN status = 'SALE' THEN '판매중'
        WHEN status = 'RESERVED' THEN '예약중'
        WHEN status = 'DONE' THEN '거래완료'
    END
FROM used_goods_board
WHERE created_date = '2022-10-05'
ORDER BY board_id DESC

 

취소되지 않은 진료 예약 조회하기

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

-- 1. 2022년 4월 13일 취소되지 않은 CS 진료 예약 내역 조회
-- 2. 진료예약번호, 환자이름, 환자번호, 진료과코드, 의사이름, 진료에약일시 출력
-- 3. 진료예약일시 기준 오름차순 정렬
-- -------------------------------------------------------------------
with t3 as (
    SELECT apnt_no, pt_no, mcdp_cd, mddr_id, apnt_ymd
    FROM appointment
    WHERE apnt_ymd LIKE '2022-04-13%' AND mcdp_cd = 'CS' AND apnt_cncl_yn = 'N'
)

SELECT apnt_no, t1.pt_name, t1.pt_no, mcdp_cd, dr_name, apnt_ymd
FROM patient t1
    JOIN (
        SELECT apnt_no, pt_no, t1.mcdp_cd, dr_name, apnt_ymd
        FROM doctor t1
            JOIN t3
            ON dr_id = mddr_id
    ) t2
    ON t1.pt_no = t2.pt_no
ORDER BY apnt_ymd

 

자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

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

해당 문제에 대한 기록 : https://hellou8363.tistory.com/27

with t1 as (
    SELECT car_id,
        SUM(
            CASE 
                WHEN '2022-10-16' BETWEEN start_date AND end_date THEN 1 ELSE 0
            END 
        ) AS availability
    FROM car_rental_company_rental_history
    GROUP BY car_id
)
SELECT car_id,
    CASE
        WHEN availability = 1 THEN '대여중' ELSE '대여 가능'
        END AS availability
FROM t1
ORDER BY 1 DESC

 

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

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

-- 1. 년, 월, 성별 별로 상품을 구매한 회원수를 집계
-- 2. 결과는 년, 월, 성별을 기준으로 오름차순 정렬
-- 3. 성별 정보가 없는 경우 결과에서 제외(남자 = 0, 여자 = 1)
-- -------------------------------------------
-- t2 : 성별이 남, 여인 데이터
with t2 as (
    SELECT user_id, gender
    FROM user_info
    WHERE gender IN (0, 1)
)
SELECT year(sales_date), month(sales_date), gender, count(DISTINCT t1.user_id)
FROM online_sale t1
    JOIN t2
    ON t1.user_id = t2.user_id
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3