✅ 코딩테스트 SQL 문제 준비 ✅

🤘 PGS_131120

문제링크

🤷‍♀️ YEAR, HOUR, MONTH, DAY, ...

• 날짜와 관련된 필드에서 원하는 데이터의 일부를 추출

select * 
from member
where month(date_of_birth) = 3;

🤷‍♀️ DATE_FORMAT

• 날짜 시간을 지정한 형식으로 출력

SELECT DATE_FORMAT(DATETIME, '%Y-%m-%d')...

• %Y, %y, %D, %d, %M, %m, %S, %T : 대문자, 소문자 출력 형식 다름

SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH) = 03 AND TLNO is not null AND GENDER = 'W'
ORDER BY MEMBER_ID ASC;

🤘 PGS_133025

문제링크

-- FIRST_HALF:  shipment_id 출하번호, flavor 맛(기본키, 외래키), total_order 총주문량
-- ICECREAM_INFO: FLAVOR 맛(기본키), ingredient_type 성분 타입 (fruit_based, sugar_based)
-- 코드를 입력하세요
SELECT f.flavor
FROM FIRST_HALF f inner join icecream_info i on (f.flavor = i.flavor)
where f.total_order > 3000 and i.ingredient_type = 'fruit_based'
order by f.total_order desc;

🤘 PGS_132201

문제링크

-- PATIENT: pt_no 환자번호, pt_name 환자이름, gend_cd 성별코드, age 나이, tlno 전화번호

SELECT p.PT_NAME, p.PT_NO,  p.GEND_CD, p.AGE, ifnull(p.tlno, 'NONE') as 'TLNO'
from patient p
where p.age <= 12 and p.gend_cd = 'W'
order by p.age desc, p.pt_name asc;

🤘 PGS_132203

문제링크

-- DOCTOR: DR_NAME 의사이름 DR_ID 의사ID LCNS_NO 면허번호, HIRE_YMD 고용일자, MCDP_CD 진료과코드
-- TLNO 전화번호

SELECT dr_name, dr_id, mcdp_cd, date_format(hire_ymd,'%Y-%m-%d') as hire_ymd
FROM DOCTOR
WHERE MCDP_CD in ('CS','GS') 
order by hire_ymd desc, dr_name asc;

🤘 PGS_151136

문제링크

SELECT ROUND(avg(daily_fee),0) as average_fee
from car_rental_company_car
group by car_type
having car_type = 'SUV';

🤘 PGS_133024

문제링크

-- 코드를 입력하세요
SELECT flavor
from first_half
order by total_order desc, shipment_id asc;

🤘 PGS_131119

문제링크

SELECT i.rest_id, i.rest_name, i.food_type, i.favorites, i.address, 
    round(avg(r.review_score),2) as SCORE
from rest_info i right join rest_review r on (i.rest_id = r.rest_id) 
group by i.address
having i.address like ('서울%')
order by score desc, i.favorites desc;

🤘 PGS_164673

문제링크

SELECT b.title, b.board_id, r.reply_id, r.writer_id, r.contents, date_format(r.created_date, '%Y-%m-%d')
from used_goods_board b inner join used_goods_reply r 
    on (b.board_id = r.board_id) 
where YEAR(b.created_date) = 2022 and MONTH(b.created_date) = 10
order by r.created_date asc, b.title;

🤘 PGS_17042

문제링크

SELECT book_id, date_format(published_date, '%Y-%m-%d')
from book
where YEAR(published_date) = 2021 and category = '인문'
order by published_date asc;

🤘 PGS_131112

문제링크

SELECT factory_id, factory_name, address
from food_factory
where address like ('강원%')
order by factory_id asc;

🤘 PGS_59034

문제링크

SELECT *
from animal_ins
order by animal_id;

🤘 PGS_131536

문제링크

SELECT a.user_id, a.product_id
from online_sale a inner join online_sale b 
where a.user_id = b.user_id and a.product_id = b.product_id and a.sales_date != b.sales_date
group by a.user_id,a.product_id
order by a.user_id asc, a.product_id desc;
-- 동일한 회원, 동일 상품 구매
-- 날짜가 다르고 회원id와 상품id가 같은 것을 찾아야 한다. 
-- 셀프 조인 ? 

🤘 PGS_131537

문제링크

-- online_sale: online_sale_id, user_id, product_id, sales_amount, sales_date
-- offline_sale: offline_sale_id, product_id, sales_amount, sales_date

-- 2022년 3월 판매날짜, 상품ID, 유저ID, 판매량 출력
-- offline_sale user_id는 null
-- 판매일 오름차순, 상품id 오름차순, 유저ID 오름차순 정렬

SELECT date_format(SALES_DATE, "%Y-%m-%d") as SALES_DATE, PRODUCT_ID, user_id, sales_amount
from online_sale 
where YEAR(sales_date) = 2022 and MONTH(sales_date) = 03
union
SELECT date_format(SALES_DATE, "%Y-%m-%d") as SALES_DATE, PRODUCT_ID, NULL, sales_amount
from offline_sale
where YEAR(sales_date) = 2022 and MONTH(sales_date) = 03
order by SALES_DATE asc, product_id asc, user_id asc;

-- 빈 열을 넣기: NULL 을 삽입하여 union 시 존재하지 않는 열을 지정해줄 수 있다. 

🤘 PGS_59035

문제링크

SELECT name, datetime
from animal_ins
order by animal_id desc;

🤘 PGS_59036

문제링크

SELECT animal_id, name
from animal_ins
where intake_condition = 'Sick'
order by animal_id;

🤘 PGS_59037

문제링크

SELECT animal_id, name
from animal_ins
where INTAKE_CONDITION != 'Aged'
order by animal_id;

🤘 PGS_59403

문제링크

SELECT animal_id, name
from animal_ins
order by animal_id;

🤘 PGS_59404

문제링크

SELECT animal_id, name, datetime
from animal_ins
order by name asc, datetime desc;

🤘 PGS_59405

문제링크

SELECT name
from animal_ins
order by datetime asc
limit 1;

🤘 PGS_131535

문제링크

SELECT count(*)
from user_info
where age between 20 and 29 
and year(joined) = 2021

🤘 PGS_131124

문제링크

select m.member_name, r.review_text, date_format(r.review_date,"%Y-%m-%d")
from member_profile m inner join rest_review r
on (m.member_id = r.member_id) 
where m.member_id = 
(SELECT m.member_id
from  member_profile m inner join rest_review r 
on (m.member_id = r.member_id) 
group by m.member_id
order by count(*) desc
limit 1) 
order by r.review_date asc, r.review_text asc;

다른 풀이

WITH vipdata AS ( # 가장 리뷰를 많이 작성한 회원의 member_id 추출
    SELECT 
        MEMBER_ID,
        COUNT(distinct REVIEW_ID) as rcnt # 리뷰 작성 개수
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
    ORDER BY rcnt desc # 리뷰개수 내림차순
    LIMIT 1 # 최상위 1명만 노출
)
SELECT 
    m.MEMBER_NAME,
    r.REVIEW_TEXT,
    r.REVIEW_DATE
FROM MEMBER_PROFILE m
INNER JOIN vipdata v
    ON m.MEMBER_ID = v.MEMBER_ID
INNER JOIN REST_REVIEW r
    ON m.MEMBER_ID = r.MEMBER_ID
ORDER BY REVIEW_DATE # 리뷰 작성일을 기준으로 오름차순 정렬

👩 with 절

with 문으로 가상테이블을 만들 수 있다.

WITH 가상테이블명 as 
( 
	SELECT 쿼리
) 

WITH 를 이용해 가상 테이블 만든 후 이후 조회시 테이블에 가상테이블 명을 넣어주면 조회가 가능하다.

🤘 PGS_144854

문제링크

SELECT b.book_id, a.author_name, date_format(b.published_date,"%Y-%m-%d") as published_date
from book b inner join author a
on (b.author_id = a.author_id)
where b.category = '경제'
order by published_date asc;

🤘 PGS_133027

문제링크

-- first_half: shipment_id, flavor(기본키), total_order 
-- july:  shipment_id(기본키), flavor, total_order
-- 7월의 아이스크림 총 주문량과 상반기 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛 조회


-- 상반기 아이스크림 총 주문량 더한값

with top_july as (
    select flavor, total_order
    from first_half
    union
    select flavor, total_order
    from july
)

select flavor
from top_july
group by flavor
order by sum(total_order) desc
limit 3;

🤘 PGS_157339

문제링크

-- car_rental_company_car: car_id, car_type, daily_fee, options
-- car_rental_company_rental_history: history_id, car_id, start_date, end_date
-- car_rental_company_discount_plan: plan_id, car_type, dutation_type, discount_rate

-- 자동차 종류가 '세단' 또는 'suv' (car_rental_company_car)
-- 2022년 11월 1일 부터 2022년 11월 30일까지 대여 가능: 렌탈 히스토리에서 start_date나 end_date가 11월1일과 11월30일 사이에 있는 경우 대여가 불가하다. (car_rental_company_rental_history)


with discount as (
    with can_rental as (
    SELECT distinct r.car_id, r.car_type, r.daily_fee
    from car_rental_company_car r inner join car_rental_company_rental_history h
    on (r.car_id = h.car_id)
    where car_type in ('SUV','세단') 
        and not (h.end_date >= '2022-11-01' and h.start_date < '2022-12-01')
    
    )
    select r.car_id, r.car_type, r.daily_fee, d.discount_rate 
    from can_rental r left join CAR_RENTAL_COMPANY_DISCOUNT_PLAN d
    on (r.car_type = d.car_type)
    where duration_type like ('30%')
)

select car_id, car_type, round((daily_fee * (1-discount_rate/100)*30),0) as FEE
from discount
where  500000 <= round((daily_fee * (1-discount_rate/100)*30),0) 
and round((daily_fee * (1-discount_rate/100)*30),0)  < 2000000
order by fee desc, car_type asc, car_id desc;
    

-- 30일간의 대여 금액이 50만원 이상 200만원 미만인 (자동차 종류, 자동차 ID, 자동차 요금 )
--   : 자동차 종류에 따라 (일일요금*(1-할인요금/100)) * 30
--   : 해당 금액이 50만원 이상 200만원 미만 (car_rental_company_car + discount_plan)
-- 자동차ID, 자동차종류, 대여 금액(fee) 리스트 출력 
-- 대역금액 desc, 자동차종류 asc, 자동차ID desc
  

위의코드 동작하지 않아서 답을 찾아봄

SELECT C.CAR_ID, C.CAR_TYPE, ROUND(C.DAILY_FEE*30*(100-P.DISCOUNT_RATE)/100, 0) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
ON C.CAR_TYPE = P.CAR_TYPE
WHERE C.CAR_ID NOT IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE END_DATE >= '2022-11-01' AND START_DATE < '2022-12-01')
AND C.CAR_TYPE IN ('SUV', '세단') AND P.DURATION_TYPE = '30일 이상'
AND ROUND(C.DAILY_FEE*30*(100-P.DISCOUNT_RATE)/100, 0) BETWEEN 500000 AND 2000000
ORDER BY FEE DESC, C.CAR_TYPE ASC, C.CAR_ID DESC

🤘 PGS_13117

문제링크

SELECT p.product_id, p.product_name, p.price * sum(o.amount) as total_sales
from food_product p inner join food_order o
on (p.product_id = o.product_id) 
where year(o.produce_date) = 2022 and month(o.produce_date) = 05
group by p.product_id
order by total_sales desc, p.product_id asc;

🤘 PGS_59042

문제링크

SELECT animal_id , name
from animal_outs
where animal_id not in (select animal_id from animal_ins)
order by animal_id, name

🤘 PGS_59043

문제링크

SELECT i.animal_id, i.name
from animal_ins i inner join animal_outs o
on (i.animal_id = o.animal_id)
where o.datetime < i.datetime
order by i.datetime

🤘 PGS_59044

문제링크

SELECT name, datetime
from animal_ins
where animal_id not in (select animal_id from animal_outs) 
order by datetime asc
limit 3;

🤘 PGS_131116

문제링크

with  maxtable as (select category, max(price) as price
from food_product
group by category
having category in('과자', '국', '김치','식용유'))


select f.category, m.price, f.product_name
from food_product f, maxtable m
where f.category = m.category and f.price = m.price
order by m.price desc;