[4월 30일]
- 보호소에서 중성화한 동물
문제
보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다.
보호소에 들어올 당시에는 중성화1되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해주세요.
-- 코드를 입력하세요
SELECT ao.ANIMAL_ID, ao.ANIMAL_TYPE, ao.NAME
FROM ANIMAL_OUTS as ao
LEFT OUTER JOIN ANIMAL_INS as ai
ON ao.ANIMAL_ID = ai.ANIMAL_ID
WHERE (ai.SEX_UPON_INTAKE LIKE "Intact%")
AND ((ao.SEX_UPON_OUTCOME LIKE "Neutered%" )
OR (ao.SEX_UPON_OUTCOME LIKE "Spayed%"))
ORDER BY 1- 우유와 요거트가 담긴 장바구니
문제
데이터 분석 팀에서는 우유(Milk)와 요거트(Yogurt)를 동시에 구입한 장바구니가 있는지 알아보려 합니다.
우유와 요거트를 동시에 구입한 장바구니의 아이디를 조회하는 SQL 문을 작성해주세요.
이때 결과는 장바구니의 아이디 순으로 나와야 합니다.
WITH milk as
(
SELECT distinct CART_ID, name
FROM CART_PRODUCTS
WHERE NAME = "Milk"
)
,
yogurt as
(
SELECT distinct CART_ID, name
FROM CART_PRODUCTS
WHERE NAME = "Yogurt"
)
SELECT milk.CART_ID
FROM milk JOIN yogurt
ON milk.CART_ID = yogurt.CART_ID우유와 요거트를 2개이상 구매한 cart_id가 있어서
distinct를 해주어야 함
- 식품분류별 가장 비싼 식품의 정보 조회하기
문제
FOOD_PRODUCT 테이블에서 식품분류별로 가격이 제일 비싼 식품의 분류, 가격, 이름을 조회하는 SQL문을 작성해주세요.
이때 식품분류가 '과자', '국', '김치', '식용유'인 경우만 출력시켜 주시고 결과는 식품 가격을 기준으로 내림차순 정렬해주세요.
WITH df as (
SELECT CATEGORY
, PRICE
, MAX(PRICE) OVER (partition by CATEGORY) as MAX_PRICE
, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
)
SELECT CATEGORY, PRICE, PRODUCT_NAME
FROM df
WHERE PRICE = MAX_PRICE
ORDER BY 2 desc- 5월 식품들의 총매출 조회하기
문제
FOOD_PRODUCT와 FOOD_ORDER 테이블에서 생산일자가 2022년 5월인 식품들의
식품 ID, 식품 이름, 총매출을 조회하는 SQL문을 작성해주세요.
이때 결과는 총매출을 기준으로 내림차순 정렬해주시고 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬해주세요.
SELECT fp.PRODUCT_ID
, fp.PRODUCT_NAME
, sum(fo.AMOUNT) * fp.price as TOTAL_SALES
FROM FOOD_ORDER as fo
JOIN FOOD_PRODUCT as fp
ON fo.PRODUCT_ID = fp.PRODUCT_ID
WHERE fo.PRODUCE_DATE between "2022-05-01" and "2022-05-31"
GROUP BY 1,2
ORDER BY 3 desc, 1[5월 1일]
- 서울에 위치한 식당 목록 출력하기
문제
REST_INFO와 REST_REVIEW 테이블에서 서울에 위치한 식당들의
식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 SQL문을 작성해주세요.
이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 해주시고 결과는 평균점수를 기준으로 내림차순 정렬해주시고,
평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬해주세요.
SELECT ri.REST_ID
, ri.REST_NAME
, ri.FOOD_TYPE
, ri.FAVORITES
, ri.ADDRESS
, round(avg(REVIEW_SCORE),2) as SCORE
FROM REST_INFO as ri
JOIN REST_REVIEW as rr
ON ri.REST_ID = rr.REST_ID
WHERE ri.ADDRESS LIKE "서울%"
GROUP BY 1,2,3,4,5
ORDER BY SCORE desc, FAVORITES desc- 그룹별 조건에 맞는 식당 목록 출력하기 🌟
문제
MEMBER_PROFILE와 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문을 작성해주세요.
회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성해주시고,
결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬해주세요.
WITH REVIEW_DF as (
SELECT MEMBER_ID
, REVIEW_TEXT
, REVIEW_DATE
, COUNT(REVIEW_ID) over (partition by MEMBER_ID) as REVIEW_CNT
FROM REST_REVIEW
)
SELECT MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE,"%Y-%m-%d") as REVIEW_DATE
FROM REVIEW_DF as rd
LEFT OUTER JOIN MEMBER_PROFILE as mp
ON rd.MEMBER_ID = mp.MEMBER_ID
WHERE REVIEW_CNT = (
SELECT MAX(REVIEW_CNT)
FROM REVIEW_DF )
ORDER BY REVIEW_DATE, REVIEW_TEXT확실히 level 4부터는 복잡해지는것 같다..ㅠㅠ
- 주문량이 많은 아이스크림들 조회하기
문제
7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문을 작성해주세요.
WITH raw as (
SELECT FLAVOR
, SUM(TOTAL_ORDER) as TOTAL_ORDER
, rank() OVER (ORDER BY SUM(TOTAL_ORDER) DESC) as ORDER_RANK
FROM (
SELECT *
FROM FIRST_HALF
UNION
SELECT *
FROM JULY
ORDER BY SHIPMENT_ID
) as df
GROUP BY 1
)
SELECT FLAVOR
FROM raw
WHERE ORDER_RANK <= 3mysql에서는 unione한 테이블에 alias를 꼭 붙혀줘야 한다 !
그리고 window funtion의 over뒤에 집계식이 들어올 수 있다는걸 알게 되었다.
- 오프라인/온라인 판매 데이터 통합하기
문제
ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력하는 SQL문을 작성해주세요.
OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시해주세요.
결과는 판매일을 기준으로 오름차순 정렬해주시고 판매일이 같다면 상품 ID를 기준으로 오름차순, 상품ID까지 같다면 유저 ID를 기준으로 오름차순 정렬해주세요.
SELECT DATE_FORMAT(SALES_DATE,'%Y-%m-%d') as SALES_DATE
, PRODUCT_ID
, USER_ID
, SALES_AMOUNT
FROM ONLINE_SALE
WHERE MONTH(SALES_DATE) = 3
UNION
SELECT DATE_FORMAT(SALES_DATE,'%Y-%m-%d') as SALES_DATE
, PRODUCT_ID
, null as USER_ID
, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE MONTH(SALES_DATE) = 3
ORDER BY SALES_DATE, PRODUCT_ID, USER_IDnull를 처리하는 법은 알고 있었는데 (nullif 등)
null을 채우는건 처음 접했다.
- 취소되지 않은 진료 예약 조회하기
문제
PATIENT, DOCTOR 그리고 APPOINTMENT 테이블에서 2022년 4월 13일 취소되지 않은 흉부외과(CS) 진료 예약 내역을 조회하는 SQL문을 작성해주세요. 진료예약번호, 환자이름, 환자번호, 진료과코드, 의사이름, 진료예약일시 항목이 출력되도록 작성해주세요. 결과는 진료예약일시를 기준으로 오름차순 정렬해주세요.
SELECT a.APNT_NO, p.PT_NAME, a.PT_NO, a.MCDP_CD, d.DR_NAME, a.APNT_YMD
FROM APPOINTMENT as a
LEFT OUTER JOIN PATIENT as p
ON a.PT_NO = p.PT_NO
LEFT OUTER JOIN DOCTOR as d
ON d.DR_ID = a.MDDR_ID
WHERE a.APNT_CNCL_YMD is null
AND DATE_FORMAT(a.APNT_YMD,"%Y-%m-%d") = "2022-04-13"
AND a.MCDP_CD = "CS"
ORDER BY APNT_YMD[5월 2일]
- 년, 월, 성별 별 상품 구매 회원 수 구하기
문제
USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요. 결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요. 이때, 성별 정보가 없는 경우 결과에서 제외해주세요
WITH df as (
SELECT YEAR(SALES_DATE) as YEAR
, MONTH(SALES_DATE) as MONTH
, GENDER
, os.USER_ID
FROM ONLINE_SALE as os
LEFT OUTER JOIN USER_INFO as ui
ON os.USER_ID = ui.USER_ID
)
SELECT YEAR, MONTH, GENDER, COUNT(distinct user_id) as USERS
FROM df
WHERE GENDER is not null
GROUP BY 1,2,3
ORDER BY 1,2,3- 저자 별 카테고리 별 매출액 집계하기
문제
2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 을 구하여, 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 저자 ID를 오름차순으로, 저자 ID가 같다면 카테고리를 내림차순 정렬해주세요.
SELECT a.AUTHOR_ID
, a.AUTHOR_NAME
, b.CATEGORY
, sum(bs.SALES * b.PRICE) as TOTAL_SALES
FROM BOOK_SALES as bs
LEFT OUTER JOIN BOOK as b
ON bs.BOOK_ID = b.BOOK_ID
LEFT OUTER JOIN AUTHOR as a
ON a.AUTHOR_ID = b.AUTHOR_ID
WHERE DATE_FORMAT(bs.SALES_DATE, "%Y-%m") = "2022-01"
GROUP BY 1,2,3
ORDER BY 1, 3 desc- 자동차 대여 기록 별 대여 금액 구하기 🌟🌟🌟🌟
문제
CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.
WITH df as (
SELECT his.HISTORY_ID
, car.CAR_TYPE
, car.DAILY_FEE
, DATEDIFF (his.END_DATE, his.START_DATE) +1 as rental_date
, CASE WHEN (DATEDIFF (his.END_DATE, his.START_DATE) +1) >= 90 THEN "90일 이상"
WHEN (DATEDIFF (his.END_DATE, his.START_DATE) +1) >= 30 THEN "30일 이상"
WHEN (DATEDIFF (his.END_DATE, his.START_DATE) +1) >= 7 THEN "7일 이상"
ELSE null END as DURATION_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY as his
LEFT OUTER JOIN CAR_RENTAL_COMPANY_CAR as car
ON his.CAR_ID = car.CAR_ID
WHERE car.CAR_TYPE = "트럭"
)
SELECT HISTORY_ID
, ROUND(
(DAILY_FEE * rental_date) * (1-(ifnull(DISCOUNT_RATE,0)*0.01))
) as FEE
FROM df
LEFT OUTER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN as plan
ON df.CAR_TYPE = plan.CAR_TYPE
AND df.DURATION_TYPE = plan.DURATION_TYPE
ORDER BY 2 desc, 1 desc복잡..
[5월 5일]
- 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 🌟🌟🌟🌟
문제
CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.
SELECT car.CAR_ID
, car.CAR_TYPE
, ROUND ((DAILY_FEE * 30) * (1-(DISCOUNT_RATE * 0.01))) as FEE
FROM CAR_RENTAL_COMPANY_CAR as car
LEFT OUTER JOIN (
SELECT CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
WHERE DURATION_TYPE = "30일 이상"
) as rent
ON car.CAR_TYPE = rent.CAR_TYPE
WHERE car.CAR_TYPE IN ("세단","SUV")
AND (DAILY_FEE * 30) * (1-(DISCOUNT_RATE * 0.01)) between 500000 and 2000000
AND CAR_ID NOT IN (
SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE (START_DATE between "2022-11-01" AND "2022-11-30")
OR (END_DATE between "2022-11-01" AND "2022-11-30")
OR (START_DATE <= '2022-11-01' AND END_DATE >= '2022-11-30')
)
ORDER BY 3 desc, 2, 1 desc다른 조건들은 쉬웠는데 날짜 조건이 가장 어려웠다.
처음에는 11월중에 내역이 없는 차들로 join을 걸려고
max(start_date)와 max(start_date)가 not betwwen "2022-11-01" AND "2022-11-30"인 내역으로
join에 서브쿼리를 작성했는데
조건이 잘못된걸 알고 where에서 not in으로 해당하는 car_id를 제외시켰다.
- 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기
문제
HR_DEPARTMENT, HR_EMPLOYEES, HR_GRADE 테이블을 이용해 사원별 성과금 정보를 조회하려합니다. 평가 점수별 등급과 등급에 따른 성과금 정보가 아래와 같을 때, 사번, 성명, 평가 등급, 성과금을 조회하는 SQL문을 작성해주세요.
평가등급의 컬럼명은 GRADE로, 성과금의 컬럼명은 BONUS로 해주세요.
결과는 사번 기준으로 오름차순 정렬해주세요
SELECT HE.EMP_NO
, HE.EMP_NAME
, CASE WHEN HG.SCORE >= 96 THEN "S"
WHEN HG.SCORE >= 90 THEN "A"
WHEN HG.SCORE >= 00 THEN "B"
ELSE "C" END as GRADE
, CASE WHEN HG.SCORE >= 96 THEN SAL * 0.2
WHEN HG.SCORE >= 90 THEN SAL * 0.15
WHEN HG.SCORE >= 00 THEN SAL * 0.1
ELSE 0 END as BONUS
FROM HR_EMPLOYEES as HE
LEFT OUTER JOIN
(SELECT EMP_NO, avg(SCORE) as SCORE
FROM HR_GRADE
GROUP BY 1) as HG
ON HE.EMP_NO = HG.EMP_NO
ORDER BY 1정답은 맞게 나오는데 통과가 자꾸 안되서 찾아보니 정확한 답은
SELECT E.EMP_NO, E.EMP_NAME, (CASE WHEN AVG(SCORE) >= 96 THEN 'S' WHEN AVG(SCORE) >= 90 THEN 'A' WHEN AVG(SCORE) >= 80 THEN 'B' ELSE 'C' END) AS GRADE, (CASE WHEN AVG(SCORE) >= 96 THEN E.SAL*0.2 WHEN AVG(SCORE) >= 90 THEN E.SAL*0.15 WHEN AVG(SCORE) >= 80 THEN E.SAL*0.1 ELSE 0 END) AS BONUS FROM HR_EMPLOYEES E INNER JOIN HR_GRADE G ON E.EMP_NO = G.EMP_NO GROUP BY E.EMP_NO ORDER BY 1;이거였다..
무슨차이인지는 잘 모르겠다.
- 특정 세대의 대장균 찾기
문제
3세대의 대장균의 ID(ID) 를 출력하는 SQL 문을 작성해주세요. 이때 결과는 대장균의 ID 에 대해 오름차순 정렬해주세요.
SELECT e1.ID
FROM ECOLI_DATA e1
LEFT OUTER JOIN ECOLI_DATA e2
ON e1.PARENT_ID = e2.ID
LEFT OUTER JOIN ECOLI_DATA e3
ON e2.PARENT_ID = e3.ID
WHERE e1.PARENT_ID is not null
AND e2.PARENT_ID is not null
AND e3.PARENT_ID is null
ORDER BY 1휴....
말장난같은문제 !!!!!!!!!!!!!!!🥲
Study Note
