4단계(2)

sql쿼리 각 절의 우선 순위

from >>> where >>> select >>> order by >>> group by >>> having >>> limit(이절은 order by 이후 실행)

 

1. 서울에 위치한 식당 목록 출력

REST_INFO 테이블 : 식당 정보를 담은

 

REST_REVIEW 테이블 : 식당 리뷰 정보를 담은

 

문제 :  서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 SQL문을 작성해주세요. 이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 해주시고 결과는 평균점수를 기준으로 내림차순 정렬해주시고, 평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬

 

나의 쿼리

SELECT a.rest_id, a.rest_name, a.food_type, a.favorites, a.address, b.score # 컬럼 선택
FROM
(SELECT rest_id, rest_name, food_type, favorites, address # 서울로 시작하는 식당 필터
FROM rest_info
WHERE address like '서울%') a
JOIN
(SELECT rest_id, round(AVG(review_score),2) as score # rest_id로 묶은 score 평균 구하기
FROM rest_review
GROUP BY rest_id) b
on a.rest_id=b.rest_id # rest_id 기준 조인
ORDER BY score desc, favorites desc # 평균점수, 즐겨찾기 내림차순

 

 

2. 우유와 요거트가 담긴 장바구니

CART_PRODUCTS 테이블 : 장바구니의 상품 정보를 담은

 

문제 :  우유(Milk)와 요거트(Yogurt)를 동시에 구입한 장바구니가 있는지 알아보려 합니다. 우유와 요거트를 동시에 구입한 장바구니의 아이디를 조회하는 SQL 문을 작성해주세요. 이때 결과는 장바구니의 아이디 순으로

 

나의 쿼리

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 # 장바구니 아이디 오름차순

 

 

3. 저자 별 카테고리 별 매출액 집계하기

BOOK 테이블 : 판매중인 도서들의 도서 정보

 

AUTHOR 테이블 : 판매중인 도서들의 저자 정보

 

BOOK_SALES 테이블 : 각 도서의 날짜 별 판매

 

문제 :  2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 을 구하여, 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 저자 ID를 오름차순으로, 저자 ID가 같다면 카테고리를 내림차순 정렬

 

나의 쿼리

SELECT b.author_id, c.author_name, b.category, sum(a.total_sales*b.price) as total_sales # 컬럼 선택
FROM
(SELECT book_id, sum(sales) as total_sales # 2022년1월 판매 데이터 필터
FROM BOOK_SALES 
WHERE date_format(sales_date,'%Y-%m')='2022-01'
GROUP BY book_id) a
left join # 왼쪽 테이블 기준 조인
(SELECT book_id, category, author_id, price # 카테고리, 저자id, 가격 테이블
FROM BOOK) b
on a.book_id=b.book_id # 책 id기준 조인
left join # 왼쪽 테이블 기준 조인
(SELECT author_id, author_name # 저자 테이블
FROM AUTHOR ) c
on b.author_id=c.author_id # 저자id 기준 조인
GROUP BY c.author_name, b.category # 저자별 카테고리별 group by
ORDER BY author_id, category desc # 저자id 오름차순, 카테고리 내림차순 정렬

 

 

4. 주문량이 많은 아이스크림들 조회하기

FIRST_HALF 테이블 : 아이스크림 가게 상반기 주문 정보

 

JULY 테이블 : 7월 아이스크림 주문 정보

 

문제 :  7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문을 작성

 

나의 쿼리

SELECT a.flavor # 컬럼 선택
FROM
(SELECT flavor, total_order # 상반기 총 주문량 데이터
FROM FIRST_HALF) a
join 
(SELECT flavor, sum(total_order) as total_order # 7월 총 주문량 데이터
FROM JULY
GROUP BY flavor) b
on a.flavor=b.flavor # 아이스크림 종류 기준 조인
ORDER BY a.total_order+b.total_order desc # 총주문량+7월주문량 기준 내림차순 정렬
LIMIT 3 # 상위 3개만 필터

 

 

5. 그룹별 조건에 맞는 식당 목록 출력

MEMBER_PROFILE 테이블 : 고객 정보를 담은

 

REST_REVIEW 테이블 : 식당 리뷰 정보를 담은

 

문제 :  리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문을 작성해주세요. 회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성해주시고, 결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬

 

나의 쿼리

SELECT b.member_name, a.review_text, date_format(a.review_date,'%Y-%m-%d') as review_date # 컬럼 선택
FROM
(SELECT member_id, review_text, review_date # 리뷰 수가 가장많은 멤버 데이터
FROM REST_REVIEW 
WHERE member_id=(SELECT member_id
FROM REST_REVIEW 
GROUP BY member_id
ORDER BY count(*) desc
LIMIT 1)) a
JOIN
(SELECT member_id, member_name # 고객 정보를 담은 데이터
FROM MEMBER_PROFILE) b
on a.member_id=b.member_id # 멤버 id기준 으로 조인
ORDER BY review_date, review_text # 리뷰date, 리뷰text 기준 오름차순 정렬