프로그래머스 SQL문 LV. 2 (2)

 

*Oracle 기준

 

 

13. 진료과별 총 예약 횟수 출력하기 ( https://school.programmers.co.kr/learn/courses/30/lessons/132202 )

APPOINTMENT 테이블에서 2022년 5월에 예약한 환자 수를 진료과코드 별로 조회하는 SQL문을 작성해주세요. 이때, 컬럼명은 '진료과 코드', '5월예약건수'로 지정해주시고 결과는 진료과별 예약한 환자 수를 기준으로 오름차순 정렬하고, 예약한 환자 수가 같다면 진료과 코드를 기준으로 오름차순 정렬해주세요.

SELECT MCDP_CD AS "진료과 코드", COUNT(*) AS "5월예약건수"
FROM APPOINTMENT
WHERE TO_CHAR(APNT_YMD, 'YYYY-MM') = '2022-05'
GROUP BY MCDP_CD
ORDER BY COUNT(*) ASC, MCDP_CD ASC;

 

14. 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기 ( https://school.programmers.co.kr/learn/courses/30/lessons/151137 )

CAR_RENTAL_COMPANY_CAR 테이블에서 '통풍시트', '열선시트', '가죽시트' 중 하나 이상의 옵션이 포함된 자동차가 자동차 종류 별로 몇 대인지 출력하는 SQL문을 작성해주세요. 이때 자동차 수에 대한 컬럼명은 CARS로 지정하고, 결과는 자동차 종류를 기준으로 오름차순 정렬해주세요.

SELECT CAR_TYPE, COUNT(*) CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%통풍시트%' OR OPTIONS LIKE '%열선시트%' OR OPTIONS LIKE '%가죽시트%'
GROUP BY CAR_TYPE
ORDER BY 1 ASC;

 

15. 상품 별 오프라인 매출 구하기 ( https://school.programmers.co.kr/learn/courses/30/lessons/131533 )

PRODUCT 테이블과 OFFLINE_SALE 테이블에서 상품코드 별 매출액(판매가 * 판매량) 합계를 출력하는 SQL문을 작성해주세요. 결과는 매출액을 기준으로 내림차순 정렬해주시고 매출액이 같다면 상품코드를 기준으로 오름차순 정렬해주세요.

SELECT PRODUCT_CODE, SUM(SALES_AMOUNT*PRICE) SALES
FROM PRODUCT P
JOIN OFFLINE O
ON P.PRODUCT_ID = O.PRODUCT_ID
GROUP BY PRODUCT_CODE
ORDER BY SALES DESC, PRODUCT_CODE ASC;

- 처음에 여기서 좀 막혔었다... 지금은 겨우 풀었고 이해도 된다지만 한참 뒤에 다시 풀라고하면 못풀듯..

- 우선 JOIN을 시켜주고, PRODUCT_CODE로 GROUP BY하는건 알겠는데 SUM 함수를 생각하기가 힘들었던 것 같다.

 

16. 루시와 엘라 찾기 ( https://school.programmers.co.kr/learn/courses/30/lessons/59046 )

동물 보호소에 들어온 동물 중 이름이 Lucy, Ella, Pickle, Rogan, Sabrina, Mitty인 동물의 아이디와 이름, 성별 및 중성화 여부를 조회하는 SQL 문을 작성해주세요.

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', "Mitty')
ORDER BY ANIMAL_ID ASC;

 

17. 조건에 맞는 도서와 저자 리스트 출력하기 ( https://school.programmers.co.kr/learn/courses/30/lessons/144854 )

'경제' 카테고리에 속하는 도서들의 도서 ID(BOOK_ID), 저자명(AUTHOR_NAME), 출판일(PUBLISHED_DATE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 출판일을 기준으로 오름차순 정렬해주세요.

SELECT BOOK_ID, AUTHOR_NAME, TO_CHAR(PUBLISHED_DATE, 'YYYY-MM-DD') PUBLISHED_DATE
FROM BOOK B 
JOIN AUTHOR A
ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE CATEGORY = '경제'
ORDER BY PUBLISHED_DATE ASC;

 

18. 성분으로 구분한 아이스크림 총 주문량 ( https://school.programmers.co.kr/learn/courses/30/lessons/133026 )

상반기 동안 각 아이스크림 성분 타입과 성분 타입에 대한 아이스크림의 총주문량을 총주문량이 작은 순서대로 조회하는 SQL 문을 작성해주세요. 이때 총주문량을 나타내는 컬럼명은 TOTAL_ORDER로 지정해주세요.

SELECT INGREDIENT_TYPE, SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF F
JOIN ICECREAM_INFO I
ON F.FLAVOR = I.FLAVOR
GROUP BY INGREDIENT_TYPE
ORDER BY TOTAL_ORDER;

 

19. 가격대 별 상품 개수 구하기 ( https://school.programmers.co.kr/learn/courses/30/lessons/131530 )

PRODUCT 테이블에서 만원 단위의 가격대 별로 상품 개수를 출력하는 SQL 문을 작성해주세요. 이때 컬럼명은 각각 컬럼명은 PRICE_GROUP, PRODUCTS로 지정해주시고 가격대 정보는 각 구간의 최소금액(10,000원 이상 ~ 20,000 미만인 구간인 경우 10,000)으로 표시해주세요. 결과는 가격대를 기준으로 오름차순 정렬해주세요.

SELECT TRUNC(PRICE, -4) AS PRICE_GROUP, COUNT(*)
FROM PRODUCT
GROUP BY TRUNC(PRICE, -4)
ORDER BY 1 ASC;

- TRUNC 함수는 숫자나 시간, 일자, 요일, 소수점을 절사하는 함수이다.

- 위의 SELECT문의 경우에는 12345원이라는 가격이 있다면 옵션으로 -4를 입력했으므로 뒤에서 4자리까지 절사하고 초기값인 '0'을 넣어 10000이 된다.

- SUBSTR 함수를 이용하거나 FLOOR 함수를 이용해 10000을 나누고 다시 10000을 곱하는 형식으로 대신 표현할 수도 있을 것 같다.

 

20. 3월에 태어난 여성 회원 목록 출력하기 ( https://school.programmers.co.kr/learn/courses/30/lessons/131120 )

MEMBER_PROFILE 테이블에서 생일이 3월인 여성 회원의 ID, 이름, 성별, 생년월일을 조회하는 SQL문을 작성해주세요. 이때 전화번호가 NULL인 경우는 출력대상에서 제외시켜 주시고, 결과는 회원ID를 기준으로 오름차순 정렬해주세요.

SELECT MEMBER_ID, MEMBER_NAME, GENDER, TO_CHAR(DATE_OF_BIRTH, 'YYYY-MM-DD')
FROM MEMBER_PROFILE
WHERE TLNO IS NOT NULL AND GENDER = 'W' AND TO_CHAR(DATE_OF_BIRTH, 'MM') = '03'
ORDER BY MEMBER_ID ASC;

 

21. 재구매가 일어난 상품과 회원 리스트 구하기 ( https://school.programmers.co.kr/learn/courses/30/lessons/131536 )

ONLINE_SALE 테이블에서 동일한 회원이 동일한 상품을 재구매한 데이터를 구하여, 재구매한 회원 ID와 재구매한 상품 ID를 출력하는 SQL문을 작성해주세요. 결과는 회원 ID를 기준으로 오름차순 정렬해주시고 회원 ID가 같다면 상품 ID를 기준으로 내림차순 정렬해주세요.

SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(PRODUCT_ID) > 1
ORDER BY USER_ID ASC, PRODUCT_ID DESC;

- GROUP BY에 컬럼이 두개 이상 들어가는 이유는 나누는 기준이 두 개이기 때문이다. 

- 1) 먼저 같은 USER_ID여야하고

- 2) 같은 PRODUCT_ID끼리 또 묶어야하기 때문에

- GROUP BY에 USER_ID, PRODUCT_ID 두 컬럼이 들어가는 것이다.

 

자세한 설명이 되어있는 블로그

 

22. 조건에 부합하는 중고거래 상태 조회하기 ( https://school.programmers.co.kr/learn/courses/30/lessons/164672 )

USED_GOODS_BOARD 테이블에서 2022년 10월 5일에 등록된 중고거래 게시물의 게시글 ID, 작성자 ID, 게시글 제목, 가격, 거래상태를 조회하는 SQL문을 작성해주세요. 거래상태가 SALE 이면 판매중, RESERVED이면 예약중, DONE이면 거래완료 분류하여 출력해주시고, 결과는 게시글 ID를 기준으로 내림차순 정렬해주세요.

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE, 
	CASE
    	WHEN STATUS = 'SALE' THEN '판매중'
        WHEN STATUS = 'RESERVED' THEN '예약중'
    	ELSE '거래완료'
    END AS STATUS
FROM USED_GOODS_BOARD
WHERE TO_CHAR(CREATED_DATE, 'YYYY-MM-DD') = '2022-10-05'
ORDER BY BOARD_ID DESC;

 

23. 자동차 평균 대여 기간 구하기 ( https://school.programmers.co.kr/learn/courses/30/lessons/157342 )

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 평균 대여 기간이 7일 이상인 자동차들의 자동차 ID와 평균 대여 기간(컬럼명: AVERAGE_DURATION) 리스트를 출력하는 SQL문을 작성해주세요. 평균 대여 기간은 소수점 두번째 자리에서 반올림하고, 결과는 평균 대여 기간을 기준으로 내림차순 정렬해주시고, 평균 대여 기간이 같으면 자동차 ID를 기준으로 내림차순 정렬해주세요.

SELECT CAR_ID, ROUND(AVG(END_DATE-START_DATE+1), 1) AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
GROUP BY CAR_ID
HAVING AVG(END_DATE-START_DATE)>6
ORDER BY 2 DESC, 1 DESC;