도식화 연습 - SQL 코드카타 #38 ~ #46

 

#38. 조건별로 분류하여 주문상태 출력하기

지문

FOOD_ORDER 테이블에서  5월 1일을 기준으로  주문 ID, 제품 ID, 출고일자, 출고여부를 조회하는 SQL문을 작성해주세요.  출고여부는 5월 1일까지 출고완료로 이 후 날짜는 출고 대기로 미정이면 출고미정으로 출력해주시고,  결과는 주문 ID를 기준으로 오름차순 정렬해주세요.

 

 

#39. 성분으로 구분한 아이스크림 총 주문량

지문

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

	FIRST_HALF	ICECREAM_INFO
테이블	아이스크림 맛, 상반기 아이스크림 총주문량	아이스크림 맛, 아이스크림의 성분 타입
	FLAVOR, TOTAL_ORDER	FLAVOR, INGREDIENT_TYPE

 

#40. 루시와 엘라 찾기

지문

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

 

 

#41. 조건에 맞는 도서 리스트 출력하기

지문

BOOK 테이블에서 2021년에 출판된 '인문' 카테고리에 속하는 도서 리스트를 찾아서 도서 ID(BOOK_ID), 출판일 (PUBLISHED_DATE)을 출력하는 SQL문을 작성해주세요. 결과는 출판일을 기준으로 오름차순 정렬해주세요.

 

42. 평균 일일대여요금 구하기

지문

CAR_RENTAL_COMPANY_CAR 테이블에서  자동차 종류가 'SUV'인 자동차들의 평균 일일 대여 요금을 출력하는 SQL문을 작성해주세요. 이때 평균 일일 대여 요금은 소수 첫 번째 자리에서 반올림하고, 컬럼명은 AVERAGE_FEE 로 지정해주세요.

 

#43. 조건에 맞는 사용자와 총 거래금액 조회하기

지문

USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서  완료된 중고 거래의 총금액이 70만 원 이상인 사람의  회원 ID, 닉네임, 총거래금액을 조회하는 SQL문을 작성해주세요.  결과는 총거래금액을 기준으로 오름차순 정렬해주세요.

	USED_GOODS_BOARD (B)	USED_GOODS_USER (U)
테이블	작성자ID, 가격, 거래상태	회원ID, 닉네임
	WRITER_ID, PRICE, STATUS	USER_ID, NICKNAME

SELECT  USER_ID, NICKNAME, SUM(PRICE) AS TOTAL_PRICE
FROM
(
    SELECT  B.WRITER_ID, B.PRICE, B.STATUS, 
    		U.USER_ID, U.NICKNAME
    FROM    USED_GOODS_BOARD B JOIN USED_GOODS_USER U 
    		ON B.WRITER_ID = U.USER_ID
    WHERE   B.STATUS LIKE 'DONE%'    
) A
GROUP BY USER_ID
    HAVING SUM(PRICE) >= 700000
ORDER BY 3 ASC

 

#44. 가격대 별 상품 개수 구하기

지문

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

SELECT  CASE WHEN PRICE BETWEEN 0 AND 9999 THEN 0
        WHEN PRICE BETWEEN 10000 AND 19999 THEN 10000
        WHEN PRICE BETWEEN 20000 AND 29999 THEN 20000
        WHEN PRICE BETWEEN 30000 AND 39999 THEN 30000
        WHEN PRICE BETWEEN 40000 AND 49999 THEN 40000
        WHEN PRICE BETWEEN 50000 AND 59999 THEN 50000
        WHEN PRICE BETWEEN 60000 AND 69999 THEN 60000
        WHEN PRICE BETWEEN 70000 AND 79999 THEN 70000
        WHEN PRICE BETWEEN 80000 AND 89999 THEN 80000
        WHEN PRICE BETWEEN 90000 AND 99999 THEN 90000
        END AS PRICE_GROUP,
        COUNT(*) AS PRODUCTS
FROM    PRODUCT
GROUP BY 1
ORDER BY 1

 

#45. 3월에 태어난 여성 회원 목록 출력하기

지문

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

SELECT  MEMBER_ID, MEMBER_NAME, GENDER, 
	DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') AS BIRTHDAY
FROM
#  3월에 태어난 여자 데이터 추출
(   SELECT  *
    FROM    MEMBER_PROFILE
    WHERE   DATE_FORMAT(DATE_OF_BIRTH, '%m') = '03' AND 
            GENDER = 'W'
) A

# 전화번호가 NULL인 경우 제외
WHERE   TLNO IS NOT NULL
ORDER BY    MEMBER_ID ASC

 

 

#46. 대여 기록이 존재하는 자동차 리스트 구하기

지문

CAR_RENTAL_COMPANY_CAR 테이블과  CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서  자동차 종류가 '세단'인 자동차들 중 10월에 대여를 시작한 기록이 있는  자동차 ID 리스트를 출력하는 SQL문을 작성해주세요.  자동차 ID 리스트는 중복이 없어야 하며, 자동차 ID를 기준으로 내림차순 정렬해주세요.

	CAR_RENTAL_COMPANY_CAR (C)	CAR_RENTAL_COMPANY_RENTAL_HISTORY (H)
테이블
	C.CAR_ID, C.CAR_TYPE	H.CAR_ID, H.START_DATE

SELECT  DISTINCT(CAR_ID) AS CAR_ID
FROM
(
    SELECT  C.CAR_ID, C.CAR_TYPE, H.START_DATE
    FROM    CAR_RENTAL_COMPANY_CAR C LEFT JOIN 
            CAR_RENTAL_COMPANY_RENTAL_HISTORY H ON C.CAR_ID = H.CAR_ID
    WHERE   DATE_FORMAT(H.START_DATE, '%m') >= 10 
            AND C.CAR_TYPE = '세단'
) A
ORDER BY 1 DESC