❓ Question ❗ Answer SELECT animal_id, name, sex_upon_intake FROM animal_ins WHERE name IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty') ORDER BY animal_id; 📌 Discussion IN으로 name이 목록에 있는지 확인
❓ Question ❗ Answer SELECT book.book_id, author.author_name, DATE_FORMAT(book.published_date, '%Y-%m-%d') FROM book LEFT OUTER JOIN author ON book.author_id = author.author_id WHERE book.category = '경제' ORDER BY book.published_date; 📌 Discussion - join으로 병합 - date_format으로 형식 변환
❓ Question ❗ Answer SELECT book.category, SUM(sales.sales) FROM book LEFT OUTER JOIN book_sales sales ON book.book_id = sales.book_id WHERE DATE_FORMAT(sales.sales_date, '%Y%m') = '202201' GROUP BY book.category ORDER BY book.category 📌 Discussion - WHERE -> GROUP BY -> SELECT 순서 - JOIN으로 병합하고 WHERE로 조건에 맞는 레코드 추출
❓ Question ❗ Answer SELECT ins.animal_id, ins.animal_type, ins.name FROM animal_ins ins LEFT OUTER JOIN animal_outs outs ON ins.animal_id = outs.animal_id WHERE (ins.sex_upon_intake LIKE '%Intact%') AND (outs.sex_upon_outcome LIKE '%Spayed%' OR outs.sex_upon_outcome LIKE '%Neutered%') ORDER BY ins.animal_id; 📌 Discussion join으로 동일한 key를 가진 데이터를 추출 후 WHERE 문에서 LIKE 조건으로 원하는 데이터를 필터링
❓ Question ❗ Answer SELECT ins.animal_id, ins.name FROM animal_ins ins LEFT OUTER JOIN animal_outs outs ON ins.animal_id = outs.animal_id ORDER BY outs.datetime - ins.datetime DESC LIMIT 2; 📌 Discussion - ORDER BY에서도 집계량 활용이 가능하다.
❓ Question ❗ Answer SELECT product.product_code 상품코드, SUM(product.price*sale.sales_amount) 매출액 FROM product product LEFT OUTER JOIN offline_sale sale ON product.product_id = sale.product_id WHERE sale.sales_amount IS NOT NULL GROUP BY product.product_code ORDER BY 매출액 DESC, 상품코드; 📌 Discussion GROUP BY 이후에 집계함수 활용 WHERE -> GROUP BY -> HAVING 순으로 작동
❓ Question ❗ Answer SELECT car_type, COUNT(*) 'CARS' FROM car_rental_company_car WHERE options LIKE '%시트%' GROUP BY car_type ORDER BY car_type; 📌 Discussion - 시트의 종류 세개 중에 하나만 포함되면 되므로 LIKE '%시트%'로 조건 지정
❓ Question ❗ Answer SELECT ins.animal_id, ins.name FROM animal_ins ins LEFT OUTER JOIN animal_outs outs ON ins.animal_id = outs.animal_id WHERE ins.datetime > outs.datetime ORDER BY ins.datetime; 📌 Discussion LEFT OUTER JOIN으로 JOIN 후에도 오른쪽 테이블의 값이 NULL인 레코드도 가져온다. WHERE로 JOIN 후 합쳐진 테이블에서 컬럼 데이터를 비교
