❓ Question ❗ Answer SELECT users.user_id USER_ID , users.nickname NICKNAME , CONCAT_WS(' ', users.CITY, users.street_address1, users.street_address2) 전체주소 , CONCAT_WS('-', LEFT(users.tlno,3), SUBSTRING(users.tlno,4,4), RIGHT(users.tlno,4)) 전화번호 FROM used_goods_board boards INNER JOIN used_goods_user users ON boards.writer_id = users.user_id GROUP BY USER_ID HAVING COUNT(USER_ID) ..
❓ Question ❗ Answer SELECT MAX(datetime) FROM animal_ins;📌 Discussion SELECT 절에서 MAX를 사용하여 최댓값만 호출
❓ Question ❗ Answer SELECT fst.flavor FROM first_half fst LEFT OUTER JOIN icecream_info info ON fst.flavor = info.flavor WHERE fst.total_order > 3000 AND info.ingredient_type = 'fruit_based' ORDER BY fst.total_order DESC;📌 Discussion LEFT OUTER JOIN으로 key인 flavor를 기준으로 모든 데이터를 추출 where로 필터링
❓ Question ❗ Answer SELECT user_id, product_id FROM online_sale GROUP BY user_id, product_id HAVING COUNT(*)>=2 ORDER BY user_id, product_id DESC;📌 Discussion GROUP BY 이후에 HAVING 활용 가능
❓ Question ❗ Answer SELECT prod.product_id, prod.product_name, SUM(prod.price*ord.amount) total_sales FROM food_product prod LEFT OUTER JOIN food_order ord ON prod.product_id = ord.product_id WHERE DATE_FORMAT(ord.produce_date, '%Y%m') = '202205' GROUP BY prod.product_id ORDER BY total_sales DESC, prod.product_id;📌 Discussion left outer join으로 key인 product_id 기준으로 모든 데이터를 JOIN pr..
❓ Question ❗ Answer SELECT DISTINCT(car.car_id) FROM car_rental_company_car car LEFT OUTER JOIN car_rental_company_rental_history history ON car.car_id = history.car_id WHERE car.car_type = '세단' AND MONTH(history.start_date) = 10 ORDER BY car.car_id DESC;📌 Discussion LEFT OUTER JOIN으로 key인 car_id를 기준으로 모든 레코드 호출 WHERE로 필터링 DISTINCT로 중복값 제거
❓ Question ❗ Answer SELECT food.category, food.price, food.product_name FROM food_product food INNER JOIN (SELECT category, MAX(price) price FROM food_product GROUP BY category) p ON food.category = p.category AND food.price = p.price WHERE food.category IN ('과자','국','김치','식용유') ORDER BY food.price DESC;📌 Discussion INNER JOIN으로 특정 카테고리별 최고가인 제품의 레코드만 불러온다. WHERE로..
❓ Question ❗ Answer SELECT outs.animal_id, outs.name FROM animal_outs outs LEFT OUTER JOIN animal_ins ins ON outs.animal_id = ins.animal_id WHERE ins.animal_id IS NULL ORDER BY outs.animal_id;📌 Discussion left outer join으로 key인 animal_id를 기준으로 ins의 모든 데이터를 추출 where에서 is null로 입양간 데이터만 있는 레코드를 추출
