1. 흉부외과 또는 일반외과 의사 목록 출력하기
select DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD, '%Y-%m-%d')
from DOCTOR
where MCDP_CD = 'CS' or MCDP_CD = 'GS'
order by HIRE_YMD desc, DR_NAME asc;
2. 과일로 만든 아이스크림 고르기
select F.FLAVOR
from FIRST_HALF F 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;
3. 12세 이하인 여자 환자 목록 출력하기
select pt_name, pt_no, gend_cd, age, ifnull(tlno, 'NONE') as tlno
from patient
where age <= 12 and gend_cd = 'W'
order by age desc, pt_name asc;
4. 평균 일일 대여 요금 구하기
select round(avg(daily_fee),0) as average_fee
from CAR_RENTAL_COMPANY_CAR
where car_type = 'SUV';
5. 3월에 태어난 여성 회원 목록 출력하기
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) = '3' and tlno is not null and gender = 'W'
order by member_id asc;
6. 조건에 부합하는 중고거래 댓글 조회하기
select
b.title,
b.board_id,
r.reply_id,
r.writer_id,
r.contents,
date_format(r.created_date, '%Y-%m-%d') as created_date
from used_goods_board b join used_goods_reply r
on b.board_id = r.board_id
where b.created_date like '2022-10%'
order by r.created_date asc, b.title asc;
7. 재구매가 일어난 상품과 회원 리스트 구하기
select user_id, product_id
from online_sale
group by user_id, product_id
having count(product_id) >= 2
order by user_id asc, product_id desc;
8. 가장 큰 물고기 10마리 구하기
select id, length
from fish_info
order by length desc, id asc
limit 10;
