❓ Question ❗ Answer SELECT history_id, car_id, DATE_FORMAT(start_date,'%Y-%m-%d'), DATE_FORMAT(end_date,'%Y-%m-%d'), IF(DATEDIFF(end_date,start_date)+1>=30,'장기 대여', '단기 대여') RENT_TYPE FROM car_rental_company_rental_history WHERE start_date LIKE '2022-09%' ORDER BY history_id DESC;📌 Discussion IF로 조건문으로 rent_type 표시 대여일수 = end_date - start_date + 1
❓ Question ❗ Answer # GROUP_CONCAT 활용 SELECT cart_id FROM cart_products GROUP BY cart_id HAVING GROUP_CONCAT(name) LIKE '%Milk%' AND GROUP_CONCAT(name) LIKE '%Yogurt%' ORDER BY cart_id; # sub query 활용 SELECT DISTINCT cart_id FROM cart_products WHERE name = 'Milk' AND cart_id in (SELECT cart_id FROM cart_products WHERE name = 'Yogurt') ORDER BY cart_id; # IN, DISTN..
❓ Question ❗ Answer SELECT * FROM places WHERE host_id IN (SELECT host_id FROM places GROUP BY host_id HAVING COUNT(*)>=2) ORDER BY id;📌 Discussion sub query 활용 하여 조건에 맞는 host_id에 해당하는 데이터만 출력
❓ Question ❗ Answer SELECT car_id, ROUND(AVG(DATEDIFF(end_date,start_date)+1),1) AVERAGE_DURATION FROM car_rental_company_rental_history GROUP BY car_id HAVING AVG(DATEDIFF(end_date,start_date)+1) >= 7.0 ORDER BY AVERAGE_DURATION DESC, car_id DESC;📌 Discussion DATEDIFF(나중일자,먼저일자)로 일수를 계산할 수 있다. 날짜 계산 관련 링크 : https://extbrain.tistory.com/78 빌린 기간 = 반납 일시 - 빌린 일시 + 1 HAVING에 조건을 걸어줄 때는 반올림하기 전의 값이..
❓ Question ❗ Answer # JOIN KEY : p.pt_no = a.pt_no / a.mddr_id = d.dr_id # 문제 조건 # 1) 2022-04-13 : a.apnt_ymd LIKE '2022-04-13%' # 2) 취소되지않은 : a.apnt_cncl_yn = 'N' # 3) 흉부외과(CS) : a.mcdp_cd = 'CS' # 4) 출력 : a.apnt_no 진료예약번호, p.pt_name 환자이름, p.pt_no 환자번호, # a.mcdp_cd 진료과코드, d.dr_name 의사이름, a.apnt_ymd 진료예약일시 SELECT ad.apnt_no 진료예약번호, p.pt_name 환자이름, p.pt_no 환자번호, ad.mcdp_cd..
❓ Question ❗ Answer SELECT DISTINCT(car_id) CAR_ID , IF(car_id IN (SELECT car_id FROM car_rental_company_rental_history WHERE '2022-10-16' BETWEEN start_date and end_date), '대여중','대여 가능') AVAILABILITY FROM car_rental_company_rental_history ORDER BY CAR_ID DESC;📌 Discussion 하나의 car_id에 대해 여러 렌탈 기록이 존재하여 대여 가능 여부가 여러가지가 나온다. 대여 중인 car_id의 레코드를 sub-query로 불러와서 IF 절로 하나의 car_..
❓ Question ❗ Answer SELECT YEAR(sale.sales_date) YEAR , MONTH(sale.sales_date) MONTH , info.gender GENDER , COUNT(DISTINCT(info.user_id)) USERS FROM user_info info INNER JOIN online_sale sale ON info.user_id = sale.user_id WHERE info.gender IS NOT NULL GROUP BY YEAR, MONTH, GENDER ORDER BY YEAR, MONTH, GENDER;📌 Discussion 동일한 회원이 여러 날에 거쳐서 구매한 경우에도 한 명의 회원으로 집계해야하므로 COUNT(DISTINCT)로 중복제거 후 집계
❓ Question ❗ Answer SELECT info.rest_id, info.rest_name, info.food_type, info.favorites, info.address, ROUND(AVG(review.review_score),2) score FROM rest_info info INNER JOIN rest_review review ON info.rest_id = review.rest_id WHERE info.address LIKE '서울%' GROUP BY rest_id ORDER BY score DESC, info.favorites DESC;📌 Discussion WHERE 문에서 join을 시키고 조건을 달아준 뒤 집계를 위해서 group by를 해주고, ROUND로 반올림..
