크리스마스를 기념할 완벽한 와인 찾기SELECT * FROM winesWHERE color = 'white'AND quality >= 7AND density > ( SELECT AVG(density) FROM wines)AND residual_sugar > ( SELECT AVG(residual_sugar) FROM wines)AND pH ( SELECT AVG(citric_acid) FROM wines WHERE color = 'white');WHERE절에서 평균은 서브쿼리로 구하자. 두 대회 연속으로 출전한 기록이 있는 배구 선수WITH successive_record AS ( SELECT A.id AS `id`, A.name AS `name`, G.year AS `year`, LAG..

멘토링 짝꿍 리스트WITH mentee AS ( SELECT employee_id AS `mentee_id`, name AS `mentee_name`, department FROM employees WHERE join_date >= DATE_SUB('2021-12-31', INTERVAL 3 MONTH) AND join_date ~개월, ~년 이내 데이터를 구하려면 DATE_SUB(날짜, INTERVAL N MONTH/YEAR) 함수를 사용하면 된다.가능한 모든 멘티-멘토 짝꿍을 매칭해주기 위해 CROSS JOIN을 활용한다.

점검이 필요한 자전거 찾기SELECT bike_id FROM rental_historyWHERE rent_at >= '2021-01-01' AND rent_at 50000

12월 우수 고객 찾기SELECT customer_id FROM recordsWHERE order_date >= '2020-12-01' AND order_date = 1000 스탬프를 찍어드려요SELECT ( CASE WHEN total_bill >= 25 THEN 2 WHEN total_bill >= 15 THEN 1 ELSE 0 END) AS `stamp`,COUNT(*) AS `count_bill`FROM tipsGROUP BY `stamp`ORDER BY `stamp`CASE WHEN THEN ELSE END 구문으로 스탬프의 개수를 분류한다. DVD 대여점 우수 고객 찾기SELECT R.customer_id FROM rental RLEFT JOIN customer C ON R.custom..

일부 데이터 조회하기SELECT * FROM pointsWHERE quartet = "I"; 데이터 그룹으로 묶기SELECT quartet, ROUND(AVG(x), 2) AS `x_mean`, ROUND(VAR_SAMP(x), 2) AS `x_var`, ROUND(AVG(y), 2) AS `y_mean`, ROUND(VAR_SAMP(y), 2) AS `y_var`FROM pointsGROUP BY quartet반올림 함수 ROUND(N, M), 표본분산 구하기 VAR_SAMP(X)

사랑에 대한 영화 찾기SELECT title, year, rotten_tomatoes FROM moviesWHERE title LIKE '%Love%' OR title LIKE '%love%'ORDER BY rotten_tomatoes DESC, year DESC; 서울숲에 놀러 가기 좋은 날SELECT measured_at AS `good_day` FROM measurementsWHERE measured_at >= '2022-12-01' AND measured_at 펭귄의 종과 몸무게 조회하기SELECT species, body_mass_g FROM penguinsWHERE species IS NOT NULL AND body_mass_g IS NOT NULLORDER BY body_mass_g DES..

온라인 쇼핑몰의 월 별 매출액 집계SELECT STRFTIME('%Y-%m', DATE(A.order_date)) AS `order_month`, SUM(CASE WHEN B.order_id NOT LIKE 'C%' THEN B.price * B.quantity ELSE 0 END) AS `ordered_amount`, SUM(CASE WHEN B.order_id LIKE 'C%' THEN B.price * B.quantity ELSE 0 END) AS `canceled_amount`, SUM(B.price * B.quantity) AS `total_amount`FROM orders AJOIN order_items BON A.order_id = B.order_idGROUP BY STRFTIME('%Y-%..

배송 예정일 예측 성공과 실패SELECT DATE(order_purchase_timestamp) AS `purchase_date`, COUNT(DISTINCT(CASE WHEN order_delivered_customer_date order_estimated_delivery_date THEN order_id END)) AS `fail`FROM olist_orders_datasetGROUP BY DATE(order_purchase_timestamp)HAVING CAST(STRFTIME('%Y', DATE(order_purchase_timestamp)) AS INTEGER) = 2017 AND CAST(STRFTIME('%m', DATE(order_purchase_timestamp)) AS INTEGER)..

할부는 몇 개월로 해드릴까요SELECT payment_installments, COUNT(DISTINCT(order_id)) AS `order_count`, MIN(payment_value) AS `min_value`, MAX(payment_value) AS `max_value`, AVG(payment_value) AS `avg_value` FROM olist_order_payments_datasetWHERE payment_type LIKE '%credit_card%'GROUP BY payment_installments 지역별 주문의 특징같은 사람이 여러 번 주문하는 경우가 있어서 WHEN절에서 order_id를 가져오고 DISTINCT로 중복 제거한 후 order_id의 개수를 셈으로써 각 카테고리별 ..

제목이 모음으로 끝나지 않는 영화SELECT title FROM filmWHERE ( rating = 'R' OR rating = 'NC-17' ) AND ( title NOT LIKE '%A' AND title NOT LIKE '%E' AND title NOT LIKE '%I' AND title NOT LIKE '%O' AND title NOT LIKE '%U' ) 언더스코어(_)가 포함되지 않은 데이터 찾기_에는 이스케이프 문자(\)를 추가해줘야 하는데, SQLite는 추가로 ESCAPE '\'를 붙여줘야 한다고 한다.SELECT DISTINCT(page_location) AS `page_location` FROM gaWHERE page_location N..

크리스마스 게임 찾기SELECT game_id, name, year FROM gamesWHERE name LIKE '%Christmas%' OR name LIKE '%Santa%' 펭귄 조사하기SELECT DISTINCT(species), island FROM penguinsORDER BY island ASC, species ASC 지자체별 따릉이 정류소 개수 세기SELECT local, COUNT(*) as `num_stations` FROM stationGROUP BY localORDER BY `num_stations` ASC 메리 크리스마스 2024SELECT 'Merry Christmas!'

레스토랑의 대목SELECT A.total_bill, A.tip, A.sex, A.smoker, A.day, A.time, A.size FROM tips AS A JOIN ( SELECT day, SUM(total_bill) AS `sales` FROM tips GROUP BY day HAVING `sales` >= 1500) AS B ON A.day = B.day 레스토랑의 요일별 VIPSELECT A.total_bill, A.tip, A.sex, A.smoker, A.day, A.time, A.size FROM tips AS A JOIN ( SELECT MAX(total_bill) as `max`, day FROM tips GROUP BY day) AS B ON (A.day = B.day) A..

첫 주문과 마지막 주문DATE()를 사용하면 yyyy-MM-dd 형태로 변환된다.SELECT DATE(MIN(order_purchase_timestamp)) AS `first_order_date`,DATE(MAX(order_purchase_timestamp)) AS `last_order_date`FROM olist_orders_datasetORDER BY order_purchase_timestamp 많이 주문한 테이블 찾기SELECT * FROM tipsWHERE total_bill > (SELECT AVG(total_bill) FROM tips) 레스토랑의 일일 평균 매출액 계산하기SELECT ROUND(AVG(`sales`), 2) AS `avg_sales` FROM ( SELECT day, SUM..