위 링크에서 GROUP BY 문제를 풀고 풀이를 기록하는 게시글입니다.
1. 즐겨찾기가 가장 많은 식당 정보 출력하기
- 식종류별로 즐겨찾기수가 가장 많은 식당의 음식 종류, ID, 식당 이름, 즐겨찾기수를 조회하는 SQL문을 작성
- 다시풀어볼만한 문제!
SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN (SELECT FOOD_TYPE, MAX(FAVORITES)
FROM REST_INFO
GROUP BY FOOD_TYPE)
ORDER BY FOOD_TYPE DESC;
2. 조건에 맞는 사용자와 총 거래금액 조회하기
- 완료된 중고 거래의 총금액이 70만 원 이상인 사람의 회원 ID, 닉네임, 총거래금액을 조회하는 SQL문을 작성
- 완료된 : WHERE = 조건사용
- 총금액: GROUP BY , HAVING SUM 조건사용
SELECT USER_ID, NICKNAME, SUM(PRICE) AS TOTAL_SALES
FROM USED_GOODS_BOARD A, USED_GOODS_USER B
WHERE A.WRITER_ID = B.USER_ID
AND A.STATUS = 'DONE'
GROUP BY B.USER_ID, B.NICKNAME
HAVING SUM(PRICE) >= 700000
ORDER BY TOTAL_SALES
3. 저자 별 카테고리 별 매출액 집계하기
- 2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 을 구하여, 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력하는 SQL문을 작성
- 2022년 1월에 여러개의 도서 판매 기록이있을 수있으므로 해당 값들을 SUM해서 총 매출액을 구해야한다.
- 다시 풀어볼만한 문제!
-- 인라인으로 푼버전
SELECT A.AUTHOR_ID, B.AUTHOR_NAME, A.CATEGORY, SUM(A.PRICE * C.SALES) TOTAL_SALES
FROM BOOK A, AUTHOR B,
(SELECT BOOK_ID, SALES
FROM BOOK_SALES
WHERE TO_CHAR(SALES_DATE,'YYYY-MM') = '2022-01') C
WHERE A.BOOK_ID = C.BOOK_ID
AND A.AUTHOR_ID = B.AUTHOR_ID
GROUP BY A.AUTHOR_ID, B.AUTHOR_NAME, A.CATEGORY
ORDER BY A.AUTHOR_ID, A.CATEGORY DESC
-- 그냥 where 조건으로 2022년 1월 데이터 선별해도 됨
SELECT A.AUTHOR_ID, B.AUTHOR_NAME, A.CATEGORY, SUM(A.PRICE * C.SALES) TOTAL_SALES
FROM BOOK A, AUTHOR B, BOOK_SALES C
WHERE A.BOOK_ID = C.BOOK_ID
AND A.AUTHOR_ID = B.AUTHOR_ID
AND TO_CHAR(SALES_DATE,'YYYY-MM') = '2022-01'
GROUP BY A.AUTHOR_ID, B.AUTHOR_NAME, A.CATEGORY
ORDER BY A.AUTHOR_ID, A.CATEGORY DESC
4. 카테고리 별 도서 판매량 집계하기
2022년 1월의 카테고리 별 도서 판매량을 합산하고, 카테고리(CATEGORY), 총 판매량(TOTAL_SALES) 리스트를 출력하는 SQL문을 작성
SELECT CATEGORY, SUM(B.SALES) TOTAL_SALES
FROM BOOK A, BOOK_SALES B
WHERE A.BOOK_ID = B.BOOK_ID
AND TO_CHAR(B.SALES_DATE, 'YYYY-MM') = '2022-01'
GROUP BY CATEGORY
ORDER BY CATEGORY
5. 자동차 대여 기록에서 대여중/대여 가능 여부 구분하기
- TO_DATE(문자열, 포맷)
- CASE WHEN 조건 THEN expr ELSE expr END
- 해당조건에 속하는 CAR_ID 하나를 출력하기위해 GROUP BY와 IN 조건을 사용한다.
SELECT CAR_ID,
CASE WHEN
CAR_ID IN (SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE TO_DATE('2022-10-16','YYYY-MM-DD') BETWEEN START_DATE AND END_DATE)
THEN '대여중'
ELSE '대여 가능' END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC
6. 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기
공통점이 시트라는 것을 찾아낸다.
SELECT CAR_TYPE, COUNT(*) CAS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%시트%'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE
7. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기
- 까다로운 문제였다.
- n월 에서 m월 까지이면 일자도 범위에 넣어줘야한다 01 31
- 날짜에서 월 추출 EXTRACT(MONTH FROM 컬럼)
- GROUP BY이후, SELECT 가 실행되므로 GROUP BY에 작성된 column으로 select에 사용되어야한다.
SELECT EXTRACT(MONTH FROM START_DATE) MONTH, CAR_ID, COUNT(*) RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (
SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE BETWEEN TO_DATE('2022-08-01','YYYY-MM-DD') AND TO_DATE('2022-10-31','YYYY-MM-DD')
GROUP BY CAR_ID
HAVING COUNT(*) > 4)
AND START_DATE BETWEEN TO_DATE('2022-08-01','YYYY-MM-DD') AND TO_DATE('2022-10-31','YYYY-MM-DD')
GROUP BY EXTRACT(MONTH FROM START_DATE), CAR_ID
HAVING COUNT(*) > 0
ORDER BY MONTH, CAR_ID DESC
8. 진료과별 총 예약 횟수 출력하기
- 컬럼명을 한글로 입력하기 위해서는 "" 큰따옴표로 감싸주자.
- 띄어쓰기 숫자로시작하는경우에는 감싸줘야하니까 그냥 다 감싸주자.
SELECT MCDP_CD "진료과 코드", COUNT(*) "5월예약건수"
FROM APPOINTMENT
WHERE TO_CHAR(APNT_YMD,'YYYY-MM') = '2022-05'
GROUP BY MCDP_CD
ORDER BY 2, 1
9. 식품분류별 가장 비싼 식품의 정보 조회하기
가장 비싼 것은 인라인에서 max를 찾아서 IN 문으로 조회한다!
SELECT CATEGORY, PRICE MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE CATEGORY IN ('과자', '국','김치','식용유')
AND (CATEGORY, PRICE) IN
(SELECT category, max(price)
FROM food_product
GROUP BY CATEGORY)
ORDER BY PRICE DESC
10.년, 월, 성별 별 상품 구매 회원 수 구하기
- 예시에보면 회원이 같은 월에 여러번구매해도 1로 count하는 것을 볼수있다
- 그러니까 상품 구매횟수가아닌 구매 회원수라는점!!
- DISTINCT로 USER_ID를 중복제거후 하나만 count하도록 한다!
SELECT EXTRACT(YEAR FROM B.SALES_DATE) YEAR, EXTRACT(MONTH FROM B.SALES_DATE) MONTH, A.GENDER, COUNT(DISTINCT(B.USER_ID)) USERS
FROM USER_INFO A, ONLINE_SALE B
WHERE A.USER_ID = B.USER_ID
AND A.GENDER IS NOT NULL
GROUP BY EXTRACT(YEAR FROM B.SALES_DATE),EXTRACT(MONTH FROM B.SALES_DATE), A.GENDER
ORDER BY 1,2,3
11. 입양 시각 구하기(2)
- 어려움!! LEVEL 개념을 알아야 풀수 있는 문제
- 연속된 숫자를 조회할 때 CONNECT BY LEVEL절을 사용한다. (1부터 참조된)
- 우리는 0시간부터참조해야하므로 1을 빼준다!!
SELECT LEVEL AS NO --1부터 10까지 참조됨
FROM DUAL
CONNECT BY LEVEL <=10
- LEFT 조인을 사용하여 날짜데이터가 없는 데이터도 구할수있도록 한다!
- LEFT조인 조건을 지정하기위해 ON 조건절을 사용한다.
- 시간을 24시간 포맷으로 뽑아내기위해서 HH24로 사용한다.
- NULL 데이터를 1로 COUNT하지 않게하기위해서 COUNT에 칼럼을 넣는다!
SELECT HOUR, COUNT(B.DATETIME) AS COUNT
FROM (SELECT LEVEL-1 AS HOUR
FROM DUAL
CONNECT BY LEVEL < 25)A LEFT JOIN ANIMAL_OUTS B
ON A.HOUR = TO_CHAR(B.DATETIME,'HH24')
GROUP BY HOUR
ORDER BY HOUR
12. 성분으로 구분한 아이스크림 총 주문량
SELECT B.INGREDIENT_TYPE, SUM(TOTAL_ORDER) TOTAL_ORDER
FROM FIRST_HALF A, ICECREAM_INFO B
WHERE A.FLAVOR = B.FLAVOR
GROUP BY B.INGREDIENT_TYPE
ORDER BY 2
13. 고양이와 개는 몇마리 있을까?
고양이를 개(Dog)보다 먼저 조회 -> 고양이(Cat) 먼저 조회
SELECT ANIMAL_TYPE, COUNT(ANIMAL_ID) count
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE
14. 입양 시각 구하기(1)
SELECT A.HOUR, COUNT(B.ANIMAL_ID) AS COUNT
FROM
(SELECT LEVEL +8 AS HOUR
FROM DUAL
CONNECT BY LEVEL < 12) A
LEFT JOIN ANIMAL_OUTS B
ON A.HOUR = TO_CHAR(B.DATETIME,'HH24')
GROUP BY HOUR
ORDER BY HOUR
- date에서 to_char로 추출해서 between and로 쓸 수도 있다.
SELECT TO_CHAR(DATETIME, 'HH24') AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS
WHERE TO_CHAR(DATETIME, 'HH24') BETWEEN 09 AND 19
GROUP BY TO_CHAR(DATETIME, 'HH24')
ORDER BY TO_CHAR(DATETIME, 'HH24');
15. 동명 동물수 찾기
SELECT NAME, COUNT(*) COUNT
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING COUNT(*)>=2
ORDER BY NAME
16. 가격대 별 상품 개수 구하기
- 몫을 구하는 방법: TRUNC(나눈 값)
- MOD를 구하는 방법: MOD(N, 을 B로 나누고) 나머지가 나온다.
SELECT TRUNC(PRICE/10000)*10000 PRICE_GROUP, COUNT(*) PRODUCTS
FROM PRODUCT
GROUP BY TRUNC(PRICE/10000)
ORDER BY TRUNC(PRICE/10000)
17. 언어별 개발자 분류하기
- 비트마스킹 문제였다 숫자로 속하는 것을 표현하는데 이때는 & join으로 연산해주면된다.
- 해당스킬에속하는지는 비트 join해서 name, category에 각각 속하는지 확인해주면된다. IN연산으로
- Front End값만 조회해야하므로 조건에속하지않는 Back End값은 NULL처리해주고 다시 select해서 null값은 출력하지않도록 한다.
- 다중컬럼 IN 할 때는 () 사용해야함
SELECT A.GRADE, A.ID, A.EMAIL
FROM (SELECT CASE WHEN (ID, 'Python') IN (SELECT B.ID, A.NAME
FROM SKILLCODES A RIGHT JOIN DEVELOPERS B
ON A.CODE & B.SKILL_CODE)
AND (ID, 'Front End') IN (SELECT B.ID, A.CATEGORY
FROM SKILLCODES A RIGHT JOIN DEVELOPERS B
ON A.CODE & B.SKILL_CODE)
THEN 'A'
ELSE CASE WHEN (ID, 'C#') IN (SELECT B.ID, A.NAME
FROM SKILLCODES A RIGHT JOIN DEVELOPERS B
ON A.CODE & B.SKILL_CODE)
THEN 'B'
ELSE CASE WHEN (ID, 'Front End') IN (SELECT B.ID, A.CATEGORY
FROM SKILLCODES A RIGHT JOIN DEVELOPERS B
ON A.CODE & B.SKILL_CODE)
THEN 'C'
ELSE NULL
END
END
END GRADE, ID, EMAIL
FROM DEVELOPERS) A
WHERE A.GRADE IS NOT NULL
ORDER BY 1,2'SQL 고득점 Kit 문제풀이' 카테고리의 다른 글
| 프로그래머스 SQL 고득점 Kit - JOIN 문제 풀이 (Oracle) (0) | 2023.12.26 |
|---|---|
| 프로그래머스 SQL 고득점 Kit - IS NULL 문제 풀이 (Oracle) (0) | 2023.12.26 |
| 프로그래머스 SQL 고득점 Kit - SUM,MAX,MIN 문제 풀이 (Oracle) (0) | 2023.12.26 |
| 프로그래머스 SQL 고득점 Kit - SELECT 문제 풀이 (Oracle) (1) | 2023.12.26 |
| 프로그래머스 SQL 고득점 Kit 문제 풀이 (Oracle) (0) | 2023.12.26 |