Programmers SQL 문제 풀이 31 ~ 40

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

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

-- FROM 동물 보호소 정보 테이블과
-- OUTER JOIN 입양 보낸 동물 정보 테이블을 조인하여
-- WHERE 입양 보낸 동물 정보에는 없는 동물 중
-- ORDER BY 가장 오래 보호소에 있었던
-- SELECT 이름과 보호 시작일을 조회
-- LIMIT 3마리만
SELECT t1.name, t1.datetime
FROM animal_ins t1
    LEFT JOIN animal_outs t2
    ON t1.animal_id = t2.animal_id
WHERE t2.animal_id IS NULL
ORDER BY t1.datetime 
limit 3

 

카테고리 별 도서 판매량 집계하기

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

-- 1. 2020년 1월의 카테고리 별 도서 판매량 합산
-- 2. 카테고리, 총 판매량을 출력
-- 3. 카테고리명을 기준으로 오름차순 정렬
-- ----------------------------------------
-- 1. book 테이블과 book_sales 테이블을 INNER JOIN한다.
-- 2. book_sales 테이블의 sales_date가 '2022년 1월'의 범위라는 조건으로 건다.
-- 3. book 테이블의 category를 기준으로 그룹화한다.
-- 4. book 테이블의 category와 book_sales 테이블의 sales(판매량 합산이므로 sum)를 조회한다.
-- 5. 조회된 데이터를 카테고리를 기준으로 오름차순 정렬한다.
SELECT t1.category, sum(sales)
FROM book t1
    JOIN book_sales t2
    ON t1.book_id = t2.book_id
WHERE t2.sales_date BETWEEN '2022-01-01' AND '2022-01-31'
GROUP BY t1.category
ORDER BY category

 

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

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

-- 1. PRODUCT, OFFLINE_SALE 테이블에서 상품코드 별 매출액(판매가 * 판매량) 합계 출력
-- 2. 매출액 기준 내림차순, 상품코드 기준 오름차순 정렬
-- ----------------------------------------------
-- 1. product 테이블과 offline_sale 테이블을 JOIN한다.
-- 2. 상품코드(product_code)로 그룹화한다.
-- 3. 상품코드(product_code)와 매출액(price * sales_amount)의 합계 출력한다.
-- 4. 1. 매출액 기준 내림차순, 2. 상품코드 기준 오름차순으로 정렬한다.
SELECT product_code, price * sum(sales_amount) AS sales
FROM product t1
    JOIN offline_sale t2
    ON t1.product_id = t2.product_id
GROUP BY product_code
ORDER BY sales DESC, product_code

-- sum을 빼고 price * sales_amount로 조회해서 틀렸었음

 

있었는데요 없었습니다

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

-- 1. 보호 시작일 보다 입양일이 빠른 동물의 아이디와 이름 조회
-- 2. 보호 시작일이 빠른 순으로 정렬
-- ----------------------------------------------------
-- 1. animal_outs 테이블과 animal_ins 테이블을 JOIN한다.
-- 2. 입양일(animal_outs의 datetime)보다 보호 시작일(animal_ins의 datetime)이 늦다(크다)는 조건을 건다.
-- 3. 동물의 아이디와 이름을 출력한다.
-- 4. 보호 시작일기준 오름차순으로 정렬한다.
SELECT t1.animal_id, t1.name
FROM animal_outs t1
    JOIN animal_ins t2
    ON t1.animal_id = t2.animal_id
WHERE t1.datetime < t2.datetime
ORDER BY t2.datetime

 

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

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

-- 1. 입양을 간 동물
-- 2. 보호 기간이 가장 긴 두 마리
-- 4. 아이디와 이름 조회
-- 5. 보호 기간이 긴 순으로 출력
-- ---------------------------
-- 1. animla_outs 테이블을 기준으로 animal_ins 테이블과 JOIN한다.
-- 2. 아이디, 이름, 시간차(입양일 - 보호 시작일)를 출력하는 서브쿼리로 만든다.
-- 3. 위 서브쿼리의 데이터에서 아이디, 이름을 출력한다.
-- 4. 정렬은 시간차(time_diff)를 기준으로 내림차순한다.
-- 5. 레코드 수를 2개로 제한한다.
with t1 as (
    SELECT t1.animal_id, t1.name, t1.datetime - t2.datetime AS time_diff
    FROM animal_outs t1
        JOIN animal_ins t2
        ON t1.animal_id = t2.animal_id
)
SELECT animal_id, name
FROM t1
ORDER BY time_diff DESC
LIMIT 2

 

보호소에서 중성화한 동물

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

-- 1. 보호소에 들어올 당시 중성화 X
-- 2. 보호소를 나갈 당시(입양 보낸 정보에서) 중성화 O
-- 3. 1, 2 조건에 해당하는 동물의 아이디, 생물 종, 이름 조회
-- 4. 아이디 순으로 정렬
-- ----------------------------------------------------
-- 1. animal_outs 테이블을 기준으로 animal_ins 테이블과 JOIN한다.
-- 2. animal_outs 테이블의 sex_upon_outcome 컬럼에 'Intact'가 포함되지 않아야 한다는 조건을 건다.
-- 3. animal_ins 테이블의 sex_upon_intake 컬럼에 'Intact'가 포함되어야 한다는 조건을 건다.
-- 4. 동물의 아이디, 생물 종, 이름을 출력한다.
-- 5. 아이디 순으로 오름차순한다.
SELECT t1.animal_id, t1.animal_type, t1.name
FROM animal_outs t1
    JOIN animal_ins t2
    ON t1.animal_id = t2.animal_id
WHERE
    t1.sex_upon_outcome NOT LIKE 'Intact%'
    AND
    t2.sex_upon_intake LIKE 'Intact%'
ORDER BY 1

 

조건에 맞는 도서와 저자 리스트 출력하기

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

-- 1. '경제' 카테고리에 속하는 도서
-- 2. 도서ID, 저자명, 출판일 출력
-- 3. 출판일 기준 오름차순
-- ------------------------------
-- 1. book 테이블을 기준으로 author 테이블을 JOIN한다.
-- 2. category 컬럼의 데이터가 '경제'여야 한다는 조건을 건다.
-- 3. 도서ID, 저자명, 출판일(년-월-일)을 출력한다.
-- 4. 출판일을 기준으로 오름차순한다.
SELECT book_id, author_name, DATE_FORMAT(published_date, '%Y-%m-%d') AS published_date
FROM book t1
    JOIN author t2
    ON t1.author_id = t2.author_id
WHERE category = '경제'
ORDER BY 3

 

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

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

-- 1. 5월 1일까지는 '출고완료'로 표시
-- 2. 5월 1일 이후는 '출고대기'로 표시
-- 3. 5월 1일 이후 중 '미정'이 있으면 '출고미정'으로 표시(미정 : NULL)
-- 4. 주문 ID, 제품 ID, 출고일자, 출고여부를 조회
-- 5. 주문 ID를 기준으로 오름차순 정렬
-- -------------------------------------------------
-- 1. 주문정보 테이블에서 주문 ID, 제품 ID, 출고일자를 출력하는 서브쿼리를 만든다.
-- 2. 서브쿼리를 테이블로 주문 ID, 제품 ID, 출고일자를 출력한다.
-- 3. 출고일자에서 5월 1일까지는 '출고완료', NULL이면 '출고미정', 그 외는 '출고대기'로 표시하는 컬럼을 추가해서 출력한다.
-- 4. 주문 ID를 기준으로 오름차순 정렬한다.
with t1 as (
    SELECT order_id, product_id, DATE_FORMAT(out_date, '%Y-%m-%d') AS out_date
    FROM food_order
)
SELECT order_id, product_id, out_date,
    CASE 
        WHEN out_date <= '2022-05-01' then '출고완료'
        WHEN out_date IS NULL then '출고미정'
        ELSE '출고대기'
    END AS '출고여부'
FROM t1
ORDER BY 1

 

성분으로 구분한 아이스크림 총 주문량

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

-- 1. 아이스크림 성분 타입 별 총주문량
-- 2. 총주문량이 작은 순서대로 출력
-- 3. 총주문량을 나타내는 컬럼명은 'TOTAL_ORDER'로 지정
-- ------------------------------------------------
-- 1. first_half 테이블을 기준으로 icecream_info 테이블과 JOIN한다.
-- 2. 아이스크림 성분 타입 별로 그룹화한다.
-- 3. 아이스크립 성분 타입, 총주문량을 출력한다.
-- 4. 총주문량을 기준으로 오름차순한다.
SELECT ingredient_type, sum(total_order)
FROM first_half t1
    JOIN icecream_info t2
    ON t1.flavor = t2.flavor
GROUP BY ingredient_type
ORDER BY 2

 

루시와 엘라 찾기

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

-- FROM 동물의 정보를 담은 테이블에서
-- WHERE 이름이 'Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty'인
-- SELECT 동물의 아이디와 이름, 성별 및 중성화 여부를 조회
-- ORDER BY 아이디 순으로 정렬
SELECT animal_id, name, sex_upon_intake
FROM animal_ins
WHERE name IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
ORDER BY 1