❓ 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로 입양간 데이터만 있는 레코드를 추출
❓ Question ❗ Answer # Subquery - scalar 활용 SELECT food_type, rest_id, rest_name, favorites FROM rest_info WHERE favorites IN (SELECT MAX(favorites) FROM rest_info GROUP BY food_type) GROUP BY food_type ORDER BY food_type DESC # Subquery - inner view 활용 SELECT info.food_type, info.rest_id, info.rest_name, info.favorites FROM rest_info info INNER JOIN (SELECT food_type, MAX(favorites) favorites FR..
❓ Question ❗ Answer SELECT member_id, member_name, gender, DATE_FORMAT(date_of_birth,'%Y-%m-%d') FROM member_profile WHERE MONTH(date_of_birth) = '03' and gender='W' and tlno IS NOT NULL ORDER BY member_id;📌 Discussion DATE_FORMAT으로 출력되는 날짜데이터의 형식을 변경 가능
❓ Question ❗ Answer SELECT ROUND(AVG(daily_fee)) FROM car_rental_company_car WHERE car_type = 'SUV' GROUP BY car_type;📌 Discussion where로 suv만 필터링 group by로 car_type 그루핑 avg로 그루핑한 car_type에 대한 daily_fee 평균 round로 소수 첫째자리에서 반올림
