내 기준에 MySQL 코딩테스트 레벨 4, 5는 시간도 오래 걸리고 어려웠어서 다시 풀어야 할 것 같다.
우선 전체적으로 다 풀긴 했지만, 막상 다시 보면 못 풀면 안되니까 ... 파이팅 !
1. 보호소에서 중성화한 동물
[문제] ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.
ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다. ANIMAL_OUTS 테이블의 ANIMAL_ID는 ANIMAL_INS의 ANIMAL_ID의 외래 키입니다.
보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다. 보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해 주세요.
[답]
SELECT O.ANIMAL_ID, O.ANIMAL_TYPE, O.NAME
FROM ANIMAL_INS I, ANIMAL_OUTS O
WHERE I.ANIMAL_ID = O.ANIMAL_ID
AND I.SEX_UPON_INTAKE LIKE '%Intact%'
AND O.SEX_UPON_OUTCOME NOT LIKE '%Intact%';
2. 입양 시각 구하기 (2) ★
NH농협은행 2023 하반기 문제와 비슷
[문제] ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다.
보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해 주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.
[답]
SET @HOUR = -1;
SELECT (@HOUR := @HOUR + 1) AS HOUR,
(SELECT COUNT(HOUR(DATETIME))
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME)=@HOUR) AS COUNT
FROM ANIMAL_OUTS
WHERE @HOUR < 23;
★ SET 명령어 사용: 어떤 변수에 특정 값을 할당할 때 쓰는 명령어
SET @HOUR = -1;
SELECT (@HOUR := @HOUR + 1) AS HOUR
FROM ANIMAL_OUTS
WHERE @HOUR < 23;
위의 코드는 @HOUR 변수에 -1을 할당한 후, 22까지 1씩 더해준다는 의미
주의할 점은 SET 사용 시, 대입 연산자 '='을 사용하고, 그 뒤로는 :=을 사용해야 함.
3. 우유와 요거트가 담긴 장바구니
Summer/Winter Coding(2019)
[문제] CART_PRODUCTS 테이블은 장바구니에 담긴 상품 정보를 담은 테이블입니다. CART_PRODUCTS 테이블의 구조는 다음과 같으며, ID, CART_ID, NAME, PRICE는 각각 테이블의 아이디, 장바구니의 아이디, 상품 종류, 가격을 나타냅니다.
데이터 분석 팀에서는 우유(Milk)와 요거트(Yogurt)를 동시에 구입한 장바구니가 있는지 알아보려 합니다. 우유와 요거트를 동시에 구입한 장바구니의 아이디를 조회하는 SQL 문을 작성해 주세요. 결과는 장바구니의 아이디 순.
[답]
SELECT M.CART_ID
FROM (SELECT CART_ID FROM CART_PRODUCTS WHERE NAME='Milk') AS M,
(SELECT CART_ID FROM CART_PRODUCTS WHERE NAME='Yogurt') AS Y
WHERE M.CART_ID = Y.CART_ID
GROUP BY M.CART_ID
4. 식품분류별 가장 비싼 식품의 정보 조회하기
[문제] 다음은 식품의 정보를 담은 FOOD_PRODUCT 테이블입니다. FOOD_PRODUCT 테이블은 다음과 같으며 PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE는 식품 ID, 식품 이름, 식품코드, 식품분류, 식품 가격을 의미합니다.
FOOD_PRODUCT 테이블에서 식품분류별로 가격이 제일 비싼 식품의 분류, 가격, 이름을 조회하는 SQL문을 작성해 주세요. 이때 식품분류가 '과자', '국', '김치', '식용유'인 경우만 출력시켜 주시고 결과는 식품 가격을 기준으로 내림차순 정렬해 주세요.
[답]
SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE PRICE IN (SELECT MAX(PRICE) FROM FOOD_PRODUCT GROUP BY CATEGORY)
AND CATEGORY IN ('과자', '국', '김치', '식용유')
GROUP BY CATEGORY
ORDER BY MAX_PRICE DESC;
5. 5월 식품들의 총매출 조회하기
[문제] 다음은 식품의 정보를 담은 FOOD_PRODUCT 테이블과 식품의 주문 정보를 담은 FOOD_ORDER 테이블입니다. FOOD_PRODUCT 테이블은 다음과 같으며 PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE는 식품 ID, 식품 이름, 식품코드, 식품분류, 식품 가격을 의미합니다.
FOOD_ORDER 테이블은 다음과 같으며 ORDER_ID, PRODUCT_ID, AMOUNT, PRODUCE_DATE, IN_DATE, OUT_DATE, FACTORY_ID, WAREHOUSE_ID는 각각 주문 ID, 제품 ID, 주문량, 생산일자, 입고일자, 출고일자, 공장 ID, 창고 ID를 의미합니다.
FOOD_PRODUCT와 FOOD_ORDER 테이블에서 생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회하는 SQL문을 작성해 주세요. 이때 결과는 총매출을 기준으로 내림차순 정렬해 주시고 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬해 주세요.
[답]
SELECT P.PRODUCT_ID, P.PRODUCT_NAME, SUM(P.PRICE * O.AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT P, FOOD_ORDER O
WHERE P.PRODUCT_ID = O.PRODUCT_ID AND O.PRODUCE_DATE BETWEEN '2022-05-01' AND '2022-05-31'
GROUP BY P.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, P.PRODUCT_ID ASC;
6. 서울에 위치한 식당 목록 출력하기
[문제] 다음은 식당의 정보를 담은 REST_INFO 테이블과 식당의 리뷰 정보를 담은 REST_REVIEW 테이블입니다. REST_INFO 테이블은 다음과 같으며 REST_ID, REST_NAME, FOOD_TYPE, VIEWS, FAVORITES, PARKING_LOT, ADDRESS, TEL은 식당 ID, 식당 이름, 음식 종류, 조회수, 즐겨찾기수, 주차장 유무, 주소, 전화번호를 의미합니다.
REST_REVIEW 테이블은 다음과 같으며 REVIEW_ID, REST_ID, MEMBER_ID, REVIEW_SCORE, REVIEW_TEXT, REVIEW_DATE는 각각 리뷰 ID, 식당 ID, 회원 ID, 점수, 리뷰 텍스트, 리뷰 작성일을 의미합니다.
REST_INFO와 REST_REVIEW 테이블에서 서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 SQL문을 작성해 주세요. 이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림해 주시고 결과는 평균점수를 기준으로 내림차순 정렬해 주시고, 평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬해 주세요.
[답]
SELECT I.REST_ID, I.REST_NAME, I.FOOD_TYPE, I.FAVORITES, I.ADDRESS,
ROUND(AVG(R.REVIEW_SCORE), 2) AS SCORE
FROM REST_INFO I, REST_REVIEW R
WHERE I.REST_ID = R.REST_ID
GROUP BY I.REST_ID
HAVING I.ADDRESS LIKE '서울%'
ORDER BY SCORE DESC, I.FAVORITES DESC;
Like는 특정 문자가 해당 문자열 안에 포함되었는지 확인하는 것. 와일드카드로 % 와 _가 있으니 적극 사용하자!
7. 그룹별 조건에 맞는 식당 목록 출력하기 (어렵다... ★)
[문제] 다음은 고객의 정보를 담은 MEMBER_PROFILE테이블과 식당의 리뷰 정보를 담은 REST_REVIEW 테이블입니다. MEMBER_PROFILE 테이블은 다음과 같으며 MEMBER_ID, MEMBER_NAME, TLNO, GENDER, DATE_OF_BIRTH는 회원 ID, 회원 이름, 회원 연락처, 성별, 생년월일을 의미합니다.
REST_REVIEW 테이블은 다음과 같으며 REVIEW_ID, REST_ID, MEMBER_ID, REVIEW_SCORE, REVIEW_TEXT, REVIEW_DATE는 각각 리뷰 ID, 식당 ID, 회원 ID, 점수, 리뷰 텍스트, 리뷰 작성일을 의미합니다.
MEMBER_PROFILE와 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문을 작성해 주세요. 회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성해 주시고, 결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬해 주세요.
[답]
SELECT P.MEMBER_NAME, R.REVIEW_TEXT, DATE_FORMAT(R.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE P, REST_REVIEW R
WHERE P.MEMBER_ID = R.MEMBER_ID AND
P.MEMBER_ID = (SELECT MEMBER_ID FROM REST_REVIEW GROUP BY MEMBER_ID ORDER BY COUNT(*) DESC LIMIT 1)
ORDER BY R.REVIEW_DATE, R.REVIEW_TEXT;
WHERE 절 안에 서브쿼리 사용하는 것 연습하기
8. 년, 월, 성별 별 상품 구매 회원 수 구하기
[문제] 다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 USER_INFO 테이블과 온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블입니다. USER_INFO 테이블은 아래와 같은 구조로 되어있으며 USER_ID, GENDER, AGE, JOINED는 각각 회원 ID, 성별, 나이, 가입일을 나타냅니다.
GENDER 컬럼은 비어있거나 0 또는 1의 값을 가지며 0인 경우 남자를, 1인 경우는 여자를 나타냅니다. ONLINE_SALE 테이블은 아래와 같은 구조로 되어있으며, ONLINE_SALE_ID, USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE는 각각 온라인 상품 판매 ID, 회원 ID, 상품 ID, 판매량, 판매일을 나타냅니다.
동일한 날짜, 회원 ID, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다.
USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해 주세요. 결과는 년, 월, 성별을 기준으로 오름차순 정렬해 주세요. 이때, 성별 정보가 없는 경우 결과에서 제외해 주세요.
[답]
SELECT YEAR(O.SALES_DATE) AS YEAR, MONTH(O.SALES_DATE) AS MONTH, U.GENDER, COUNT(DISTINCT O.USER_ID) AS USERS
FROM USER_INFO U, ONLINE_SALE O
WHERE U.USER_ID = O.USER_ID AND U.GENDER IS NOT NULL
GROUP BY YEAR, MONTH, U.GENDER
ORDER BY YEAR, MONTH, U.GENDER;
9. 상품을 구매한 회원 비율 구하기 Lv 5 !! ★★★
[문제] 다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 USER_INFO 테이블과 온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블입니다. USER_INFO 테이블은 아래와 같은 구조로 되어있으며 USER_ID, GENDER, AGE, JOINED는 각각 회원 ID, 성별, 나이, 가입일을 나타냅니다.
GENDER 컬럼은 비어있거나 0 또는 1의 값을 가지며 0인 경우 남자를, 1인 경우는 여자를 나타냅니다. ONLINE_SALE 테이블은 아래와 같은 구조로 되어있으며 ONLINE_SALE_ID, USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE는 각각 온라인 상품 판매 ID, 회원 ID, 상품 ID, 판매량, 판매일을 나타냅니다. 동일한 날짜, 회원 ID, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다.
USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해 주세요. 상품을 구매한 회원의 비율은 소수점 두 번째 자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해 주시고 년이 같다면 월을 기준으로 오름차순 정렬해 주세요.
[답]
SELECT YEAR(S.SALES_DATE) AS YEAR, MONTH(S.SALES_DATE) AS MONTH,
COUNT(DISTINCT I.USER_ID) AS PUCHASED_USERS,
ROUND(COUNT(DISTINCT I.USER_ID) / (SELECT COUNT(*)
FROM USER_INFO
WHERE YEAR(JOINED)='2021'), 1) AS PUCHASED_RATIO
FROM USER_INFO I, ONLINE_SALE S
WHERE I.USER_ID = S.USER_ID AND YEAR(I.JOINED) = '2021'
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH;
어려웠다.....
10. 오프라인/온라인 판매 데이터 통합하기 ★★★
[문제] 다음은 어느 의류 쇼핑몰의 온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블과 오프라인 상품 판매 정보를 담은 OFFLINE_SALE 테이블입니다. ONLINE_SALE 테이블은 아래와 같은 구조로 되어있으며 ONLINE_SALE_ID, USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE는 각각 온라인 상품 판매 ID, 회원 ID, 상품 ID, 판매량, 판매일을 나타냅니다.
동일한 날짜, 회원 ID, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다. OFFLINE_SALE 테이블은 아래와 같은 구조로 되어있으며 OFFLINE_SALE_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE는 각각 오프라인 상품 판매 ID, 상품 ID, 판매량, 판매일을 나타냅니다. 동일한 날짜, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다.
ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품 ID, 유저 ID, 판매량을 출력하는 SQL문을 작성해 주세요. OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL로 표시해 주세요. 결과는 판매일을 기준으로 오름차순 정렬해 주시고 판매일이 같다면 상품 ID를 기준으로 오름차순, 상품 ID까지 같다면 유저 ID를 기준으로 오름차순 정렬해 주세요.
[답]
(SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE SALES_DATE LIKE '2022-03%'
UNION
SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE SALES_DATE LIKE '2022-03%')
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID;
어려웠다.................... UNION을 처음 써봤다.... 참고한 내용은 아래에.....
https://velog.io/@greaceh/프로그래머스-SQL-오프라인온라인-판매-데이터-통합하기\
[프로그래머스 | SQL] 오프라인/온라인 판매 데이터 통합하기
프로그래머스_오프라인/온라인 판매 데이터 통합하기
velog.io
11. 취소되지 않은 진료 예약 조회하기 ★★★
[문제] 다음은 환자 정보를 담은 PATIENT 테이블과 의사 정보를 담은 DOCTOR 테이블, 그리고 진료 예약목록을 담은 APPOINTMENT에 대한 테이블입니다. PATIENT 테이블은 다음과 같으며 PT_NO, PT_NAME, GEND_CD, AGE, TLNO는 각각 환자번호, 환자이름, 성별코드, 나이, 전화번호를 의미합니다.
DOCTOR 테이블은 다음과 같으며 DR_NAME, DR_ID, LCNS_NO, HIRE_YMD, MCDP_CD, TLNO는 각각 의사이름, 의사 ID, 면허번호, 고용일자, 진료과코드, 전화번호를 나타냅니다.
APPOINTMENT 테이블은 다음과 같으며 APNT_YMD, APNT_NO, PT_NO, MCDP_CD, MDDR_ID, APNT_CNCL_YN, APNT_CNCL_YMD는 각각 진료 예약일시, 진료예약번호, 환자번호, 진료과코드, 의사ID, 예약취소여부, 예약취소날짜를 나타냅니다.
PATIENT, DOCTOR 그리고 APPOINTMENT 테이블에서 2022년 4월 13일 취소되지 않은 흉부외과(CS) 진료 예약 내역을 조회하는 SQL문을 작성해 주세요. 진료예약번호, 환자이름, 환자번호, 진료과코드, 의사이름, 진료예약일시 항목이 출력되도록 작성해 주세요. 결과는 진료예약일시를 기준으로 오름차순 정렬해 주세요.
[답]
SELECT A.APNT_NO, P.PT_NAME, A.PT_NO, A.MCDP_CD, D.DR_NAME, A.APNT_YMD
FROM APPOINTMENT A
JOIN PATIENT P ON P.PT_NO = A.PT_NO
JOIN DOCTOR D ON D.DR_ID = A.MDDR_ID
WHERE A.APNT_CNCL_YMD IS NULL AND A.APNT_YMD LIKE '2022-04-13%' AND A.MCDP_CD = 'CS'
ORDER BY A.APNT_YMD
12. 주문량이 많은 아이스크림들 조회하기
[문제] 다음은 아이스크림 가게의 상반기 주문 정보를 담은 FIRST_HALF 테이블과 7월의 아이스크림 주문 정보를 담은 JULY 테이블입니다. FIRST_HALF 테이블 구조는 다음과 같으며, SHIPMENT_ID, FLAVOR, TOTAL_ORDER는 각각 아이스크림 공장에서 아이스크림 가게까지의 출하 번호, 아이스크림 맛, 상반기 아이스크림 총주문량을 나타냅니다. FIRST_HALF 테이블의 기본 키는 FLAVOR입니다. FIRST_HALF 테이블의 SHIPMENT_ID는 JULY 테이블의 SHIPMENT_ID의 외래 키입니다.
JULY 테이블 구조는 다음과 같으며, SHIPMENT_ID, FLAVOR, TOTAL_ORDER 은 각각 아이스크림 공장에서 아이스크림 가게까지의 출하 번호, 아이스크림 맛, 7월 아이스크림 총주문량을 나타냅니다. JULY 테이블의 기본 키는 SHIPMENT_ID입니다. JULY테이블의 FLAVOR는 FIRST_HALF 테이블의 FLAVOR의 외래 키입니다. 7월에는 아이스크림 주문량이 많아 같은 아이스크림에 대하여 서로 다른 두 공장에서 아이스크림 가게로 출하를 진행하는 경우가 있습니다. 이 경우 같은 맛의 아이스크림이라도 다른 출하 번호를 갖게 됩니다.
7월 아이스크림 총 주문량과 상반기의 아이스크림 총주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문을 작성해 주세요.
[답]
SELECT A.FLAVOR
FROM (SELECT F.FLAVOR, (SUM(F.TOTAL_ORDER) + SUM(J.TOTAL_ORDER)) AS TOTAL
FROM FIRST_HALF F, JULY J
WHERE F.FLAVOR = J.FLAVOR
GROUP BY F.FLAVOR
ORDER BY TOTAL DESC) A
LIMIT 3;
13. 저자 별 카테고리 별 매출액 집계하기
[문제] 다음은 어느 한 서점에서 판매 중인 도서들의 도서 정보(BOOK), 저자 정보(AUTHOR) 테이블입니다. BOOK 테이블은 각 도서의 정보를 담은 테이블로 아래와 같은 구조로 되어있습니다. AUTHOR 테이블은 도서의 저자의 정보를 담은 테이블로 아래와 같은 구조로 되어있습니다. BOOK_SALES 테이블은 각 도서의 날짜 별 판매량 정보를 담은 테이블로 아래와 같은 구조로 되어있습니다.
2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가)을 구하여, 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력하는 SQL문을 작성해 주세요. 결과는 저자 ID를 오름차순으로, 저자 ID가 같다면 카테고리를 내림차순 정렬해 주세요.
[답]
SELECT A.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY, SUM(B.PRICE * S.SALES) AS TOTAL_SALES
FROM BOOK B
JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
JOIN BOOK_SALES S ON B.BOOK_ID = S.BOOK_ID
WHERE S.SALES_DATE LIKE '2022-01%'
GROUP BY A.AUTHOR_ID, B.CATEGORY
ORDER BY A.AUTHOR_ID, B.CATEGORY DESC;
14. 자동차 대여 기록 별 대여 금액 구하기 ★★★
[문제] 다음은 어느 자동차 대여 회사에서 대여 중인 자동차들의 정보를 담은 CAR_RENTAL_COMPANY_CAR 테이블과 자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 자동차 종류별 대여 기간 종류별 할인 정책 정보를 담은 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블입니다. CAR_RENTAL_COMPANY_CAR 테이블은 아래와 같은 구조로 되어있으며, CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS는 각각 자동차 ID, 자동차 종류, 일일 대여 요금(원), 자동차 옵션 리스트를 나타냅니다. 자동차 종류는 '세단', 'SUV', '승합차', '트럭', '리무진'이 있습니다. 자동차 옵션 리스트는 콤마(', ')로 구분된 키워드 리스트(예: ''열선시트, 스마트키, 주차감지센서'')로 되어있으며, 키워드 종류는 '주차감지센서', '스마트키', '내비게이션', '통풍시트', '열선시트', '후방카메라', '가죽시트'가 있습니다.
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블은 아래와 같은 구조로 되어있으며, HISTORY_ID, CAR_ID, START_DATE, END_DATE는 각각 자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다.
CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블은 아래와 같은 구조로 되어있으며, PLAN_ID, CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE는 각각 요금 할인 정책 ID, 자동차 종류, 대여 기간 종류, 할인율(%)을 나타냅니다. 할인율이 적용되는 대여 기간 종류로는 '7일 이상' (대여 기간이 7일 이상 30일 미만인 경우), '30일 이상' (대여 기간이 30일 이상 90일 미만인 경우), '90일 이상' (대여 기간이 90일 이상인 경우) 이 있습니다. 대여 기간이 7일 미만인 경우 할인정책이 없습니다.
CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해 주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해 주세요.
[답]
SELECT H.HISTORY_ID,
ROUND((C.DAILY_FEE * (CASE WHEN DATEDIFF(H.END_DATE, H.START_DATE) + 1 < 7 THEN 1
WHEN DATEDIFF(H.END_DATE, H.START_DATE) + 1 < 30 THEN 0.95
WHEN DATEDIFF(H.END_DATE, H.START_DATE) + 1 < 90 THEN 0.92
ELSE 0.85
END) * (DATEDIFF(H.END_DATE, H.START_DATE)+1)), 0) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H ON C.CAR_ID = H.CAR_ID
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P ON C.CAR_TYPE = P.CAR_TYPE
WHERE C.CAR_TYPE = '트럭'
GROUP BY H.HISTORY_ID
ORDER BY FEE DESC, H.HISTORY_ID DESC;
* 나의 바보짓 *
문제에서 30일 이상 90일 미만은 할인율이 7%라고 했는데 0.93으로 치면 답이 아니라고 함.
그리고 그 외에 0.85 부분도 0.9가 되어야 맞는 것 같은데 ... 왜 아닐까 ...? 라고 생각했음.
근데 SELECT * FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN; 쳐보니까 할인율 떡하니 나왔다.
예시 자료 보고 그대로 코딩한 내 잘못. 하하...
15. 특정 기간 동안 대여 가능한 자동차들의 대여비용 구하기 ★★★
[문제] 다음은 어느 자동차 대여 회사에서 대여 중인 자동차들의 정보를 담은 CAR_RENTAL_COMPANY_CAR 테이블과 자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 자동차 종류별 대여 기간 종류별 할인 정책 정보를 담은 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블입니다. CAR_RENTAL_COMPANY_CAR 테이블은 아래와 같은 구조로 되어있으며, CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS는 각각 자동차 ID, 자동차 종류, 일일 대여 요금(원), 자동차 옵션 리스트를 나타냅니다. 자동차 종류는 '세단', 'SUV', '승합차', '트럭', '리무진'이 있습니다. 자동차 옵션 리스트는 콤마(', ')로 구분된 키워드 리스트(예: ''열선시트, 스마트키, 주차감지센서'')로 되어있으며, 키워드 종류는 '주차감지센서', '스마트키', '내비게이션', '통풍시트', '열선시트', '후방카메라', '가죽시트'가 있습니다.
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블은 아래와 같은 구조로 되어있으며, HISTORY_ID, CAR_ID, START_DATE, END_DATE는 각각 자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다.
CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블은 아래와 같은 구조로 되어있으며, PLAN_ID, CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE는 각각 요금 할인 정책 ID, 자동차 종류, 대여 기간 종류, 할인율(%)을 나타냅니다. 할인율이 적용되는 대여 기간 종류로는 '7일 이상' (대여 기간이 7일 이상 30일 미만인 경우), '30일 이상' (대여 기간이 30일 이상 90일 미만인 경우), '90일 이상' (대여 기간이 90일 이상인 경우) 이 있습니다. 대여 기간이 7일 미만인 경우 할인정책이 없습니다.
CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '세단' 또는 'SUV'인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만 원 이상 200만 원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해 주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해 주세요.
[답]
SELECT C.CAR_ID, C.CAR_TYPE, ROUND(C.DAILY_FEE*(100-P.DISCOUNT_RATE)/100 * 30, 0) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P ON C.CAR_TYPE = P.CAR_TYPE
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H ON C.CAR_ID = H.CAR_ID
WHERE C.CAR_ID NOT IN (SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE END_DATE > '2022-11-01')
AND P.DURATION_TYPE = '30일 이상'
GROUP BY CAR_ID
HAVING C.CAR_TYPE IN ('세단', 'SUV') AND (FEE >= 500000 AND FEE < 2000000)
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC;
이거 날짜 때문에 어려웠다...
2024년 6월 13일 문제 추가
16. FrontEnd 개발자 찾기
[문제] SKILLCODES 테이블은 개발자들이 사용하는 프로그래밍 언어에 대한 정보를 담은 테이블입니다. SKILLCODES 테이블의 구조는 다음과 같으며, NAME, CATEGORY, CODE는 각각 스킬의 이름, 스킬의 범주, 스킬의 코드를 의미합니다. 스킬의 코드는 2진수로 표현했을 때 각 bit로 구분될 수 있도록 2의 제곱수로 구성되어 있습니다.
DEVELOPERS 테이블은 개발자들의 프로그래밍 스킬 정보를 담은 테이블입니다. DEVELOPERS 테이블의 구조는 다음과 같으며, ID, FIRST_NAME, LAST_NAME, EMAIL, SKILL_CODE는 각각 개발자의 ID, 이름, 성, 이메일, 스킬 코드를 의미합니다. SKILL_CODE 컬럼은 INTEGER 타입이고, 2진수로 표현했을 때 각 bit는 SKILLCODES 테이블의 코드를 의미합니다. 예를 들어 어떤 개발자의 SKILL_CODE가 400 (=b'110010000')이라면, 이는 SKILLCODES 테이블에서 CODE가 256 (=b'100000000'), 128 (=b'10000000'), 16 (=b'10000')에 해당하는 스킬을 가졌다는 것을 의미합니다.
DEVELOPERS 테이블에서 Front End 스킬을 가진 개발자의 정보를 조회하려 합니다. 조건에 맞는 개발자의 ID, 이메일, 이름, 성을 조회하는 SQL 문을 작성해 주세요. 결과는 ID를 기준으로 오름차순 정렬해 주세요.
[답]
SELECT D.ID, D.EMAIL, D.FIRST_NAME, D.LAST_NAME
FROM DEVELOPERS D,
(SELECT SUM(CODE) AS SUM_CODE
FROM SKILLCODES
WHERE CATEGORY = 'Front End') S
WHERE S.SUM_CODE & D.SKILL_CODE
ORDER BY D.ID ASC;
* 비트연산자에 대해 더 공부가 필요하다.
비트 연산자(bitwise operator), 프로그래머스 문제로 이해해 보자.
📌비트 연산자란? SQL 기초 문법만 배운 상태에서 프로그래머스 SQL 고득점 Kit를 풀던 중 처음 보는 연산자를 발견했다. 평소 &(AND)라고 불리는 기호인데, SQL에서는 AND 연산에 사용되고, '비트 연
velog.io
17. 언어별 개발자 분류하기
[문제] SKILLCODES 테이블은 개발자들이 사용하는 프로그래밍 언어에 대한 정보를 담은 테이블입니다. SKILLCODES 테이블의 구조는 다음과 같으며, NAME, CATEGORY, CODE는 각각 스킬의 이름, 스킬의 범주, 스킬의 코드를 의미합니다. 스킬의 코드는 2진수로 표현했을 때 각 bit로 구분될 수 있도록 2의 제곱수로 구성되어 있습니다.
DEVELOPERS 테이블은 개발자들의 프로그래밍 스킬 정보를 담은 테이블입니다. DEVELOPERS 테이블의 구조는 다음과 같으며, ID, FIRST_NAME, LAST_NAME, EMAIL, SKILL_CODE는 각각 개발자의 ID, 이름, 성, 이메일, 스킬 코드를 의미합니다. SKILL_CODE 컬럼은 INTEGER 타입이고, 2진수로 표현했을 때 각 bit는 SKILLCODES 테이블의 코드를 의미합니다. 예를 들어 어떤 개발자의 SKILL_CODE가 400 (=b'110010000')이라면, 이는 SKILLCODES 테이블에서 CODE가 256 (=b'100000000'), 128 (=b'10000000'), 16 (=b'10000')에 해당하는 스킬을 가졌다는 것을 의미합니다.
DEVELOPERS 테이블에서 GRADE별 개발자의 정보를 조회하려 합니다. GRADE는 다음과 같이 정해집니다.
- A : Front End 스킬과 Python 스킬을 함께 가지고 있는 개발자
- B : C# 스킬을 가진 개발자
- C : 그 외의 Front End 개발자
GRADE가 존재하는 개발자의 GRADE, ID, EMAIL을 조회하는 SQL 문을 작성해 주세요. 결과는 GRADE와 ID를 기준으로 오름차순 정렬해 주세요.
[답]
WITH GRADE AS (
SELECT (CASE WHEN ID IN (SELECT F.ID
FROM (SELECT D.ID, D.EMAIL
FROM SKILLCODES S, DEVELOPERS D
WHERE S.CATEGORY = 'Front End' AND S.CODE & D.SKILL_CODE) F,
(SELECT D.ID, D.EMAIL
FROM SKILLCODES S, DEVELOPERS D
WHERE S.NAME = 'Python' AND S.CODE & D.SKILL_CODE) P
WHERE F.ID = P.ID) THEN 'A'
WHEN ID IN (SELECT D.ID
FROM SKILLCODES S, DEVELOPERS D
WHERE S.NAME = 'C#' AND S.CODE & D.SKILL_CODE) THEN 'B'
WHEN ID IN (SELECT D.ID
FROM SKILLCODES S, DEVELOPERS D
WHERE S.CATEGORY = 'Front End' AND S.CODE & D.SKILL_CODE) THEN 'C'
END) GRADE, ID, EMAIL
FROM DEVELOPERS
)
SELECT *
FROM GRADE
WHERE GRADE IS NOT NULL
ORDER BY GRADE, ID ASC;* WITH문을 사용할 줄 몰랐는데 이번 기회에 사용해보았다.
* 참고 링크
[SQL] WITH문
Query을 작성하다보면 SubQuery를 사용하는 경우가 많다. 한두개만 사용하면 괜찮지만 그 수가 많아질 수록 가독성이 떨어지며, 재사용할 수가 없어서 매번 같은 Query를 작성해줘야 한다. 그래서, 이
heodolf.tistory.com
[DB] WITH AS 문 사용하기
쿼리를 작성할때 과도한 서브 쿼리는 메인 쿼리의 더욱 복작하게 만든다. 이럴때 WITH AS 구문을 사용하면 그나마 복잡한 부분의 쿼리를 분리해 낼 수 있을 뿐만 아니라 반복적으로 쓰이는 서브
freehoon.tistory.com
[프로그래머스/MYSQL] 언어별 개발자 분류하기
👋😊 인사 안녕하세요! 새로 나온 SQL의 마지막 문제를 해결하려 왔습니다. 이번 문제는 꽤 많은 시간을 걸려서 푼 만큼 성취감 높았습니다ㅎㅎ
velog.io
18. 연간 평가점수에 해당하는 평가 등급 및 성과급 조회하기
[문제] HR_DEPARTMENT 테이블은 회사의 부서 정보를 담은 테이블입니다. HR_DEPARTMENT 테이블의 구조는 다음과 같으며 DEPT_ID, DEPT_NAME_KR, DEPT_NAME_EN, LOCATION은 각각 부서 ID, 국문 부서명, 영문 부서명, 부서 위치를 의미합니다.
HR_EMPLOYEES 테이블은 회사의 사원 정보를 담은 테이블입니다. HR_EMPLOYEES 테이블의 구조는 다음과 같으며 EMP_NO, EMP_NAME, DEPT_ID, POSITION, EMAIL, COMP_TEL, HIRE_DATE, SAL은 각각 사번, 성명, 부서 ID, 직책, 이메일, 전화번호, 입사일, 연봉을 의미합니다.
HR_GRADE 테이블은 2022년 사원의 평가 정보를 담은 테이블입니다. HR_GRADE의 구조는 다음과 같으며 EMP_NO, YEAR, HALF_YEAR, SCORE는 각각 사번, 연도, 반기, 평가 점수를 의미합니다.
HR_DEPARTMENT, HR_EMPLOYEES, HR_GRADE 테이블을 이용해 사원별 성과급 정보를 조회하려합니다. 평가 점수별 등급과 등급에 따른 성과금 정보가 아래와 같을 때, 사번, 성명, 평가 등급, 성과급을 조회하는 SQL문을 작성해 주세요. 평가등급의 컬럼명은 GRADE로, 성과급의 컬럼명은 BONUS로 해주세요. 결과는 사번 기준으로 오름차순 정렬해 주세요.
[답]
SELECT E.EMP_NO, E.EMP_NAME, G.GRADE,
(CASE WHEN G.GRADE = 'S' THEN E.SAL * 0.2
WHEN G.GRADE = 'A' THEN E.SAL * 0.15
WHEN G.GRADE = 'B' THEN E.SAL * 0.1
ELSE E.SAL * 0
END) BONUS
FROM HR_EMPLOYEES E,
(SELECT EMP_NO, AVG(SCORE) SCORE,
(CASE WHEN AVG(SCORE) >= 96 THEN 'S'
WHEN AVG(SCORE) >= 90 THEN 'A'
WHEN AVG(SCORE) >= 80 THEN 'B'
ELSE 'C'
END) GRADE
FROM HR_GRADE
GROUP BY EMP_NO) G
WHERE E.EMP_NO = G.EMP_NO
ORDER BY E.EMP_NO ASC;
19. 특정 세대의 대장균 찾기
[문제] 대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다. 다음은 실험실에서 배양한 대장균들의 정보를 담은 ECOLI_DATA테이블의 구조는 다음과 같으며, ID, PARENT_ID, SIZE_OF_COLONY, DIFFERENTIATION_DATE, GENOTYPE은 각각 대장균 개체의 ID, 부모 개체의 ID, 개체의 크기, 분화되어 나온 날짜, 개체의 형질을 나타냅니다. 최초의 대장균 개체의 PARENT_ID는 NULL 값입니다.
3세대의 대장균의 ID(ID)를 출력하는 SQL 문을 작성해 주세요. 이때 결과는 대장균의 ID에 대해 오름차순 정렬해 주세요.
[답]
SELECT E.ID
FROM -- 3세대
ECOLI_DATA E, -- 2세대
(SELECT E.ID
FROM ECOLI_DATA E,
-- 1세대
(SELECT ID FROM ECOLI_DATA WHERE PARENT_ID IS NULL) G1
WHERE E.PARENT_ID = G1.ID) G2
WHERE E.PARENT_ID = G2.ID
ORDER BY E.ID ASC;20. 멸종 위기의 대장균 찾기 Lv5 !!
[문제] 대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다. 다음은 실험실에서 배양한 대장균들의 정보를 담은 ECOLI_DATA 테이블입니다. ECOLI_DATA 테이블의 구조는 다음과 같으며, ID, PARENT_ID, SIZE_OF_COLONY, DIFFERENTIATION_DATE, GENOTYPE은 각각 대장균 개체의 ID, 부모 개체의 ID, 개체의 크기, 분화되어 나온 날짜, 개체의 형질을 나타냅니다. 최초의 대장균 개체의 PARENT_ID는 NULL 값입니다.
각 세대별 자식이 없는 개체의 수(COUNT)와 세대(GENERATION)를 출력하는 SQL문을 작성해 주세요. 이때 결과는 세대에 대해 오름차순 정렬해 주세요. 단, 모든 세대에는 자식이 없는 개체가 적어도 1 개체는 존재합니다.
[답]
-- 각 세대별 대장균 아이디
WITH GENERATION AS (
SELECT DISTINCT E.ID, (CASE WHEN E.ID = G1.ID THEN 1
WHEN E.ID = G2.ID THEN 2
WHEN E.ID = G3.ID THEN 3
WHEN E.ID = G4.ID THEN 4
END) GENERATION
FROM ECOLI_DATA E,
-- 1세대 ID
(SELECT E.ID ID
FROM ECOLI_DATA E
WHERE PARENT_ID IS NULL) G1,
-- 2세대 ID
(SELECT E.ID ID
FROM ECOLI_DATA E,
(SELECT E.ID FROM ECOLI_DATA E WHERE PARENT_ID IS NULL) G1
WHERE G1.ID = E.PARENT_ID) G2,
-- 3세대 ID
(SELECT E.ID
FROM ECOLI_DATA E,
(SELECT E.ID
FROM ECOLI_DATA E,
(SELECT E.ID FROM ECOLI_DATA E WHERE PARENT_ID IS NULL) G1
WHERE G1.ID = E.PARENT_ID) G2
WHERE G2.ID = E.PARENT_ID) G3,
-- 4세대 ID
(SELECT E.ID
FROM ECOLI_DATA E,
(SELECT E.ID
FROM ECOLI_DATA E,
(SELECT E.ID
FROM ECOLI_DATA E, (SELECT E.ID FROM ECOLI_DATA E WHERE PARENT_ID IS NULL) G1
WHERE G1.ID = E.PARENT_ID) G2
WHERE G2.ID = E.PARENT_ID) G3
WHERE G3.ID = E.PARENT_ID) G4
WHERE E.ID = G1.ID OR E.ID = G2.ID OR E.ID = G3.ID OR E.ID = G4.ID
),
-- 세대별 자식이 없는 대장균 ID
DINK AS (
SELECT E.ID
FROM ECOLI_DATA E
WHERE E.ID NOT IN (SELECT DISTINCT PARENT_ID FROM ECOLI_DATA WHERE PARENT_ID IS NOT NULL)
)
SELECT COUNT(*) AS COUNT, G.GENERATION
FROM GENERATION G, DINK D
WHERE G.ID = D.ID
GROUP BY G.GENERATION
모든 문제는 프로그래머스 코딩테스트 연습문제입니다. (24.07.15. 기준)
https://school.programmers.co.kr/learn/challenges?order=recent&languages=mysql&page=1&levels=4%2C5
코딩테스트 연습 | 프로그래머스 스쿨
개발자 취업의 필수 관문 코딩테스트를 철저하게 연습하고 대비할 수 있는 문제를 총망라! 프로그래머스에서 선발한 문제로 유형을 파악하고 실력을 업그레이드해 보세요!
school.programmers.co.kr
'Coding Test > Programmers' 카테고리의 다른 글
| [Python] 프로그래머스 알고리즘 고득점 Kit ✅ 정렬 (0) | 2024.09.29 |
|---|---|
| [Python] 프로그래머스 알고리즘 고득점 Kit ✅ 해시 (~ing) (1) | 2024.09.29 |
| [MySQL] 프로그래머스 Level 3 문제 풀이 (21문제, 240612 업데이트) (1) | 2024.06.12 |
| [MySQL] 프로그래머스 Level 2 문제 풀이 (36문제, 240530 업데이트) (1) | 2024.05.30 |
| [MySQL] 프로그래머스 Level 1 문제 풀이 (31문제, 240524 업데이트) (1) | 2024.05.24 |
