어려웠던 / 다시 보면 좋을 문제들만 모아서
2. JOIN + α
🔎 WHERE절의 IN 사용법
WHERE 컬럼명 IN 조건
참고
lv3. 2021 Dev-Matching: 웹 백엔드 개발자(상반기) : 헤비 유저가 소유한 장소
이 서비스에서는 공간을 둘 이상 등록한 사람을 "헤비 유저"라고 부릅니다. 헤비 유저가 등록한 공간의 정보를 아이디 순으로 조회하는 SQL문을 작성해주세요.
/* 내 코드
SELECT *
FROM PLACES
GROUP BY HOST_ID
HAVING HOST_ID > 1
ORDER BY ID
*/
# 이건 아예 근처도 못 갔음...
#WHERE 컬럼명 IN 조건
SELECT *
FROM PLACES
WHERE HOST_ID
IN
(SELECT HOST_ID
FROM PLACES
GROUP BY HOST_ID
HAVING COUNT(HOST_ID)>=2
)
ORDER BY ID🔎 SELF JOIN은 언제, 어떻게 쓸까
lv3. GROUP BY : 즐겨찾기가 가장 많은 식당 정보 출력하기
REST_INFO 테이블에서 음식종류별로 즐겨찾기수가 가장 많은 식당의 음식 종류, ID, 식당 이름, 즐겨찾기수를 조회하는 SQL문을 작성해주세요. 이때 결과는 음식 종류를 기준으로 내림차순 정렬해주세요.
#맞는 것 같은데 왜 안되지...?의 내 코드 (일단 값도 다르다ㅠ)
SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
GROUP BY FOOD_TYPE
HAVING MAX(FAVORITES)
ORDER BY FOOD_TYPE DESC
/*(안 되는 이유) : https://school.programmers.co.kr/questions/37996
좋은 생각입니다. 그러나 group by를 foodtype으로 했을 때 restid, rest_name이 max(favorites)인 row인지 아닌지 정확히 확신할 수 없습니다.
그 이유는 group by를 foodtype으로 진행했을 때 max(favorites)값은 정확히 내가 원하는 foodtype의 favorites라고 확신할 수 있지만 restid, restname은 group by로 묶여지고 제일 첫번째 로우가 가져와지기 때문에 max(favorites)랑 restid, restname이 일치 안할 수도 있습니다.
때문에 아래와 같이 foodtype과 max(favorites)를 먼저 찾고 그것과 일치하는 restid와 rest_name을 join을 통해 찾을 수 있습니다.
*/
#SELF JOIN 지금까지 푼 것중에 젤루 어렵다...
#https://kimsyoung.tistory.com/entry/SELF-JOIN-%E4%B8%8A-%EA%B0%99%EC%9D%80-%ED%85%8C%EC%9D%B4%EB%B8%94%EC%9D%84-%EC%A1%B0%EC%9D%B8%ED%95%98%EA%B8%B0
SELECT R.FOOD_TYPE, R.REST_ID, R.REST_NAME, R.FAVORITES
FROM REST_INFO AS R
JOIN (
SELECT FOOD_TYPE, MAX(FAVORITES) AS 'MF'
FROM REST_INFO
GROUP BY FOOD_TYPE
) AS M
ON R.FOOD_TYPE = M.FOOD_TYPE
AND R.FAVORITES = M.MF
ORDER BY R.FOOD_TYPE DESC🔎 SELF JOIN은 이럴 때 쓰는거다
lv4. GROUP BY : 식품분류별 가장 비싼 식품의 정보 조회하기
FOOD_PRODUCT 테이블에서 식품분류별로 가격이 제일 비싼 식품의 분류, 가격, 이름을 조회하는 SQL문을 작성해주세요. 이때 식품분류가 '과자', '국', '김치', '식용유'인 경우만 출력시켜 주시고 결과는 식품 가격을 기준으로 내림차순 정렬해주세요.
#이럴 때 SELF JOIN을 쓰는 거다...
SELECT F.CATEGORY, F.PRICE AS 'MAX_PRICE', F.PRODUCT_NAME
FROM FOOD_PRODUCT AS F
JOIN (
SELECT CATEGORY, MAX(PRICE) AS 'MAX'
FROM FOOD_PRODUCT
GROUP BY CATEGORY
) AS C
ON F.CATEGORY = C.CATEGORY
AND F.PRICE = C.MAX
WHERE F.CATEGORY IN ('과자', '국', '김치', '식용유')
ORDER BY F.PRICE DESC🔎 재귀 테이블
WITH RECURSIVE 테이블명
참고
lv4. GROUP BY : 입양 시각 구하기(2)
보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.
#내 코드
SELECT HOUR(DATETIME) AS 'HOUR', COUNT(*) AS 'COUNT'
FROM ANIMAL_OUTS
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME)
#재귀 테이블 사용
#https://hyunmin1906.tistory.com/149
WITH RECURSIVE TIME
AS(SELECT 0 AS H UNION ALL SELECT H+1 FROM TIME WHERE H < 23)
SELECT H AS 'HOUR', COUNT(HOUR(DATETIME)) AS 'COUNT'
FROM TIME
LEFT JOIN ANIMAL_OUTS
ON (H=HOUR(DATETIME))
GROUP BY H🔎 여러 개의 SELECT문 사용해 중복값 거르기
lv4. Summer/Winter Coding(2019) : 우유와 요거트가 담긴 장바구니
데이터 분석 팀에서는 우유(Milk)와 요거트(Yogurt)를 동시에 구입한 장바구니가 있는지 알아보려 합니다. 우유와 요거트를 동시에 구입한 장바구니의 아이디를 조회하는 SQL 문을 작성해주세요. 이때 결과는 장바구니의 아이디 순으로 나와야 합니다.
# 내 코드 -> 중복값 걸러줘야 함
SELECT CART_ID
FROM CART_PRODUCTS
WHERE NAME IN ('Yogurt','Milk')
GROUP BY CART_ID
HAVING COUNT(CART_ID)>1
ORDER BY CART_ID
#'동시에 구입한'
SELECT A.CART_ID
FROM
(SELECT CART_ID
FROM CART_PRODUCTS
WHERE NAME = 'Milk') AS A,
(SELECT CART_ID
FROM CART_PRODUCTS
WHERE NAME = 'Yogurt') AS B
WHERE A.CART_ID = B.CART_ID
ORDER BY A.CART_ID🔎 JOIN 안에 SELECT문
JOIN 안에 SELECT~FROM~WHERE 쓸 수 있는 걸 잊지 말자.
lv4. SELECT : 서울에 위치한 식당 목록 출력하기
REST_INFO와 REST_REVIEW 테이블에서 서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 SQL문을 작성해주세요. 이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 해주시고 결과는 평균점수를 기준으로 내림차순 정렬해주시고, 평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬해주세요.
#내 코드
SELECT A.REST_ID, A.REST_NAME, A.FOOD_TYPE, A.FAVORITES, A.ADDRESS,
(ROUND (AVG (B.REVIEW_SCORE),2)) AS SCORE
FROM REST_INFO AS A
INNER JOIN REST_REVIEW AS B
ON A.REST_ID = B.REST_ID
WHERE ADDRESS LIKE "서울%"
ORDER BY SCORE DESC, FAVORITES DESC
#JOIN 안에 SELECT~FROM~WHERE 쓸 수 있는 걸 잊지 말자.
SELECT A.REST_ID, B.REST_NAME, B.FOOD_TYPE, B.FAVORITES, B.ADDRESS,
ROUND(AVG(A.REVIEW_SCORE),2) AS SCORE
FROM REST_REVIEW AS A
JOIN (
SELECT REST_ID, REST_NAME, FOOD_TYPE, FAVORITES, ADDRESS
FROM REST_INFO
WHERE ADDRESS LIKE '서울%'
) AS B
ON A.REST_ID = B.REST_ID
GROUP BY A.REST_ID
ORDER BY SCORE DESC, FAVORITES DESC🔎 WHERE 절에 컬럼명, 조건 주기
WHERE 컬럼명 = (SELECT 문으로 조건)
lv4. JOIN : 그룹별 조건에 맞는 식당 목록 출력하기
MEMBER_PROFILE와 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문을 작성해주세요. 회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성해주시고, 결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬해주세요.
#이런 식으로 쓰는 WHERE 절이 넘 어렵다... 익숙치 않아...
SELECT P.MEMBER_NAME, R.REVIEW_TEXT,
DATE_FORMAT(R.REVIEW_DATE,'%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE AS P
JOIN REST_REVIEW AS R
ON P.MEMBER_ID = R.MEMBER_ID
WHERE P.MEMBER_ID = (
SELECT MEMBER_ID
FROM REST_REVIEW
GROUP BY MEMBER_ID
ORDER BY COUNT(MEMBER_ID) DESC
LIMIT 1
)
ORDER BY R.REVIEW_DATE, R.REVIEW_TEXT🔎 DISTINCT를 사용한 중복값 제거
DISTINCT 컬럼명
lv4. GROUP BY : 년, 월, 성별 별 상품 구매 회원 수 구하기
USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요. 결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요. 이때, 성별 정보가 없는 경우 결과에서 제외해주세요.
SELECT YEAR(S.SALES_DATE) AS YEAR,
MONTH(S.SALES_DATE) AS MONTH,
I.GENDER,
COUNT(DISTINCT I.USER_ID) AS USERS
/*원래 코드에서는 이 부분을 COUNT(*)로 출력했음.
COUNT(DISTINCT I.USER_ID)처럼 DISTINCT 사용해서 중복 값 제거.*/
FROM USER_INFO AS I
JOIN ONLINE_SALE AS S
ON I.USER_ID = S.USER_ID
WHERE I.GENDER IS NOT NULL
GROUP BY MONTH, I.GENDER
ORDER BY YEAR, MONTH, I.GENDER🔎 DISTINCT
lv5. JOIN : 상품을 구매한 회원 비율 구하기
USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.
#DISTINCT 어디에 쓸지... 생각...
SELECT YEAR(S.SALES_DATE) AS YEAR,
MONTH(S.SALES_DATE) AS MONTH,
COUNT(DISTINCT S.USER_ID) AS PUCHASED_USERS,
ROUND(
COUNT(DISTINCT S.USER_ID)
/
(SELECT DISTINCT COUNT(USER_ID)
FROM USER_INFO
WHERE YEAR(JOINED) LIKE '2021')
,1)
AS PUCHASED_RATIO
FROM ONLINE_SALE AS S
LEFT JOIN USER_INFO AS I
ON S.USER_ID = I.USER_ID
WHERE YEAR(I.JOINED) = '2021'
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH🔎 3개 이상의 테이블 JOIN
lv4. String, Date : 취소되지 않은 진료 예약 조회하기
PATIENT, DOCTOR 그리고 APPOINMENT 테이블에서 2022년 4월 13일 취소되지 않은 흉부외과(CS) 진료 예약 내역을 조회하는 SQL문을 작성해주세요. 진료예약번호, 환자이름, 환자번호, 진료과코드, 의사이름, 진료예약일시 항목이 출력되도록 작성해주세요. 결과는 진료예약일시를 기준으로 오름차순 정렬해주세요.
#3개 이상의 테이블 조인
#https://kimsyoung.tistory.com/entry/3%EA%B0%9C-%EC%9D%B4%EC%83%81%EC%9D%98-%ED%85%8C%EC%9D%B4%EB%B8%94-LEFT-JOIN-%ED%95%98%EA%B8%B0
SELECT A.APNT_NO, P.PT_NAME, P.PT_NO, A.MCDP_CD, D.DR_NAME, A.APNT_YMD
FROM APPOINTMENT AS A
LEFT JOIN PATIENT AS P
ON A.PT_NO = P.PT_NO
LEFT JOIN DOCTOR AS D
ON A.MDDR_ID = D.DR_ID
WHERE DATE_FORMAT(A.APNT_YMD,'%Y-%m-%d') = '2022-04-13'
AND A.APNT_CNCL_YN = 'N'
AND A.MCDP_CD = 'CS'
ORDER BY APNT_YMD