68번
문제 설명
다음은 어느 한 서점에서 판매중인 도서들의 도서 정보(BOOK), 저자 정보(AUTHOR) 테이블입니다.
BOOK 테이블은 각 도서의 정보를 담은 테이블로 아래와 같은 구조로 되어있습니다.
Column name Type Nullable Description
BOOK_ID INTEGER FALSE 도서 ID
CATEGORY VARCHAR(N) FALSE 카테고리 (경제, 인문, 소설, 생활, 기술)
AUTHOR_ID INTEGER FALSE 저자 ID
PRICE INTEGER FALSE 판매가 (원)
PUBLISHED_DATE DATE FALSE 출판일
AUTHOR 테이블은 도서의 저자의 정보를 담은 테이블로 아래와 같은 구조로 되어있습니다.
Column name Type Nullable Description
AUTHOR_ID INTEGER FALSE 저자 ID
AUTHOR_NAME VARCHAR(N) FALSE 저자명
BOOK_SALES 테이블은 각 도서의 날짜 별 판매량 정보를 담은 테이블로 아래와 같은 구조로 되어있습니다.
Column name Type Nullable Description
BOOK_ID INTEGER FALSE 도서 ID
SALES_DATE DATE FALSE 판매일
SALES INTEGER FALSE 판매량
문제
2022년 1월의 도서 판매 데이터를 기준으로
저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 을 구하여,
저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES)
리스트를 출력하는 SQL문을 작성해주세요.
결과는 저자 ID를 오름차순으로, 저자 ID가 같다면 카테고리를 내림차순 정렬해주세요.
SELECT b.author_id, author_name, category, sum(sales*price) as total_sales
from book b
inner join author a on b.author_id = a.author_id
inner join book_sales s on b.book_id = s.book_id
where sales_date like '2022-01%'
group by 1,2,3
order by b.author_id, category desc
sum(sales*price)와
3개 테이블의 조회가 핵심
69번
문제 설명
다음은 어느 자동차 대여 회사의 자동차 대여 기록 정보를 담은
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블입니다.
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블은 아래와 같은 구조로 되어있으며,
HISTORY_ID, CAR_ID, START_DATE, END_DATE 는 각각
자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다.
Column name Type Nullable
HISTORY_ID INTEGER FALSE
CAR_ID INTEGER FALSE
START_DATE DATE FALSE
END_DATE DATE FALSE
문제
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서
대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서
해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 월을 기준으로 오름차순 정렬하고, 월이 같다면 자동차 ID를 기준으로 내림차순 정렬해주세요.
특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외해주세요.SELECT case when start_date like '2022-08-%' then 08
when start_date like '2022-09-%' then 09
when start_date like '2022-10-%' then 10
end as month
, car_id
, count(history_id) as records
from car_rental_company_rental_history
where start_date between '2022-08-01' and '2022-10-31'
group by month, car_id
having count(history_id) >= 5
order by 1, 2 desc
SELECT case when start_date like '2022-08-%' then 08
when start_date like '2022-09-%' then 09
when start_date like '2022-10-%' then 10
end as month
, car_id
, count(history_id) as records
from car_rental_company_rental_history h
where start_date between '2022-08-01' and '2022-10-31'
and car_id in (
select car_id
from car_rental_company_rental_history
where start_date between '2022-08-01' and '2022-10-31'
group by car_id
having count(history_id) >= 5)
group by month, car_id
order by 1, 2 desc
아래는 제공하신 SQL 쿼리의 각 줄에 대한 설명입니다:
SELECT case when start_date like '2022-08-%' then 08
when start_date like '2022-09-%' then 09
when start_date like '2022-10-%' then 10
end as month
- SELECT: 쿼리의 결과에서 선택할 열을 정의합니다. CASE 문을 사용하여 START_DATE의 월을 추출하고, 해당 월에 따라 08, 09, 10을 반환합니다. 이 결과는 month라는 별칭을 가집니다.
, car_id
- car_id: 대여한 자동차의 ID를 결과에 포함합니다.
, count(history_id) as records
- count(history_id): 각 car_id와 month 조합에 대한 대여 기록의 수를 계산합니다. 이 결과는 RECORDS라는 별칭을 가집니다.
from car_rental_company_rental_history h
- FROM: CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블을 쿼리의 데이터 소스로 지정합니다. h는 이 테이블의 별칭입니다.
where start_date between '2022-08-01' and '2022-10-31'
- WHERE: 대여 시작일이 2022년 8월 1일부터 10월 31일 사이인 기록만 선택합니다.
and car_id in (
select car_id
from car_rental_company_rental_history
where start_date between '2022-08-01' and '2022-10-31'
group by car_id
having count(history_id) >= 5)
- AND car_id IN: 서브쿼리를 사용하여 대여 횟수가 5회 이상인 자동차 ID만 선택합니다. 서브쿼리는 동일한 테이블에서 대여 시작일이 같은 기간 동안 대여 횟수를 계산하고, 5회 이상인 자동차 ID를 반환합니다.
group by month, car_id
- GROUP BY: month와 car_id로 그룹화하여, 각 월별로 각 자동차의 대여 횟수를 집계합니다.
order by 1, 2 desc
- ORDER BY: 첫 번째 열(월) 기준으로 오름차순 정렬하고, 두 번째 열(자동차 ID) 기준으로 내림차순 정렬합니다.
특정 월의 총 대여 횟수가 0인 경우 자동으로 제외되는 이유:
- 쿼리에서 HAVING 절이 사용되면, 그룹화된 결과에 대해 필터링을 수행합니다. HAVING COUNT(history_id) >= 5 조건에 의해 대여 횟수가 5회 미만인 자동차는 결과에서 제외됩니다.
- 또한, WHERE 절에서 대여 시작일이 2022년 8월 1일부터 10월 31일 사이로 제한되므로, 특정 월의 대여 기록이 아예 없다면 해당 자동차는 서브쿼리에서 아예 선택되지 않게 되어 결과에 포함되지 않습니다.
결론적으로, 대여 횟수가 0인 자동차는 서브쿼리에서 제외되므로, 최종 결과에 나타나지 않습니다.
뤼튼에 물어봐도 계속 틀린 답만 알려줘서
오래 걸려서 푼 다음 다시 뤼튼한테 설명하라고 했다...
잘하자..?
70번
문제 설명
다음은 고객의 정보를 담은 MEMBER_PROFILE테이블과 식당의 리뷰 정보를 담은 REST_REVIEW 테이블입니다.
MEMBER_PROFILE 테이블은 다음과 같으며
MEMBER_ID, MEMBER_NAME, TLNO, GENDER, DATE_OF_BIRTH는
회원 ID, 회원 이름, 회원 연락처, 성별, 생년월일을 의미합니다.
Column name Type Nullable
MEMBER_ID VARCHAR(100) FALSE
MEMBER_NAME VARCHAR(50) FALSE
TLNO VARCHAR(50) TRUE
GENDER VARCHAR(1) TRUE
DATE_OF_BIRTH DATE TRUE
REST_REVIEW 테이블은 다음과 같으며
REVIEW_ID, REST_ID, MEMBER_ID, REVIEW_SCORE, REVIEW_TEXT,REVIEW_DATE는 각각
리뷰 ID, 식당 ID, 회원 ID, 점수, 리뷰 텍스트, 리뷰 작성일을 의미합니다.
Column name Type Nullable
REVIEW_ID VARCHAR(10) FALSE
REST_ID VARCHAR(10) TRUE
MEMBER_ID VARCHAR(100) TRUE
REVIEW_SCORE NUMBER TRUE
REVIEW_TEXT VARCHAR(1000) TRUE
REVIEW_DATE DATE TRUE
문제
MEMBER_PROFILE와 REST_REVIEW 테이블에서
리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문을 작성해주세요.
회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성해주시고,
결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬해주세요.
SELECT
p.member_name,
review_text,
date_format(review_date, '%Y-%m-%d') review_date
FROM
member_profile p
JOIN
rest_review r ON p.member_id = r.member_id
WHERE
p.member_id = (
SELECT
member_id
FROM
rest_review
GROUP BY
member_id
ORDER BY
COUNT(review_id) DESC
limit 1
)
ORDER BY
review_date,
review_text;