문제

📌 MySQL 로 풀이

📌 문제 링크 :

📌 문제 설명 :

7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문을 작성해주세요.

📌 테이블 :

다음은 아이스크림 가게의 상반기 주문 정보를 담은 FIRST_HALF 테이블과 7월의 아이스크림 주문 정보를 담은 JULY 테이블입니다. FIRST_HALF 테이블 구조는 다음과 같으며, SHIPMENT_ID, FLAVOR, TOTAL_ORDER는 각각 아이스크림 공장에서 아이스크림 가게까지의 출하 번호, 아이스크림 맛, 상반기 아이스크림 총주문량을 나타냅니다. FIRST_HALF 테이블의 기본 키는 FLAVOR입니다. FIRST_HALF테이블의 SHIPMENT_ID는 JULY테이블의 SHIPMENT_ID의 외래 키입니다.

NAMETYPENULLABLE

JULY 테이블 구조는 다음과 같으며, SHIPMENT_ID, FLAVOR, TOTAL_ORDER 은 각각 아이스크림 공장에서 아이스크림 가게까지의 출하 번호, 아이스크림 맛, 7월 아이스크림 총주문량을 나타냅니다. JULY 테이블의 기본 키는 SHIPMENT_ID입니다. JULY테이블의 FLAVOR는 FIRST_HALF 테이블의 FLAVOR의 외래 키입니다. 7월에는 아이스크림 주문량이 많아 같은 아이스크림에 대하여 서로 다른 두 공장에서 아이스크림 가게로 출하를 진행하는 경우가 있습니다. 이 경우 같은 맛의 아이스크림이라도 다른 출하 번호를 갖게 됩니다.

NAMETYPENULLABLE

풀이

추측) 

일단 두 테이블은 연관관계가 없는 상반기와 7월 기록이므로 그냥 합쳐도 될거 같다.

두 테이블 컬럼이 이름이 같으니 합쳐서 그룹화 하고, 더한 값으로 내림차순하여 3개만 출력하면 될 듯!

쿼리) 

* 1차) 

SELECT A.FLAVOR
FROM (SELECT *
    FROM FIRST_HALF 
    UNION ALL
    SELECT *
    FROM JULY ) AS A
GROUP BY A.FLAVOR
ORDER BY SUM(A.TOTAL_ORDER) DESC
LIMIT 3

리뷰) 

UNION 존재를 알고는 있었는데 이 참에 다시 정리

UNION 키워드는 두개 이상의 SELECT문을 합치는 역할을 한다.

두 SELECT문의 결과 컬럼 이름과 타입이 동일 또는 형변환 가능한 유사 형태여야 합칠 수 있다.

기본적으로 UNION은 중복 데이터를 출력하지 않고, UNION ALL 키워드를 사용하면중복 데이터까지 출력이 가능하다. 

문제

📌 MySQL 로 풀이

📌 문제 링크 :

📌 문제 설명 :

PATIENT, DOCTOR 그리고 APPOINTMENT 테이블에서 2022년 4월 13일 취소되지 않은 흉부외과(CS) 진료 예약 내역을 조회하는 SQL문을 작성해주세요. 진료예약번호, 환자이름, 환자번호, 진료과코드, 의사이름, 진료예약일시 항목이 출력되도록 작성해주세요. 결과는 진료예약일시를 기준으로 오름차순 정렬해주세요.

📌 테이블 :

다음은 환자 정보를 담은 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, 예약취소여부, 예약취소날짜를 나타냅니다.

풀이

추측) 

예약 기준으로 해서 의사든 환자든 INNER JOIN해주면 될듯하다.

일단 예약에서 해당 조건이랑 안맞는거 먼저 row수 빼주고 inner하는 게 더 좋을 거 같아서 예약만 조건 출력하고

해당하는 의사, 환자 매칭되게 각각 inner join 해주면 될듯하다.

쿼리) 

* 1차) 성공

SELECT AP.APNT_NO, AP.PT_NAME, AP.PT_NO, AP.MCDP_CD, D.DR_NAME, AP.APNT_YMD
FROM 
    (SELECT A.APNT_NO, P.PT_NAME, P.PT_NO, A.MCDP_CD, A.MDDR_ID, A.APNT_YMD
     FROM (SELECT APNT_NO, PT_NO, MCDP_CD, MDDR_ID, APNT_YMD 
            FROM APPOINTMENT
            WHERE APNT_YMD LIKE '2022-04-13%' 
                AND MCDP_CD = 'CS' 
                AND APNT_CNCL_YN != 'Y'
         ) AS A # 필요없는 예약 먼저 필터링
    INNER JOIN PATIENT AS P
    ON A.PT_NO = P.PT_NO) AS AP
INNER JOIN DOCTOR AS D
ON AP.MDDR_ID = D.DR_ID
ORDER BY

* 2차) 성공: 위에 꺼가 너무 지저분해서 좀 정리

SELECT A.APNT_NO, P.PT_NAME, P.PT_NO, A.MCDP_CD, D.DR_NAME, A.APNT_YMD
FROM (SELECT APNT_NO, PT_NO, MCDP_CD, MDDR_ID, APNT_YMD 
      FROM APPOINTMENT
      WHERE APNT_YMD LIKE '2022-04-13%' 
         AND MCDP_CD = 'CS' 
         AND APNT_CNCL_YN != 'Y'
      ) AS A 					 # 필요없는 예약 먼저 필터링
INNER JOIN PATIENT AS P ON A.PT_NO = P.PT_NO
INNER JOIN DOCTOR AS D ON A.MDDR_ID = D.DR_ID
ORDER BY A.APNT_YMD ASC

리뷰) 

쿼리도 좀더 깔끔하게 쓸 수 있는 법을 많이 생각해봐야할 거 같다 

1차 쿼리 너무 지저분했음 ㅎㅎ..

문제

📌 MySQL 으로 풀이

📌 문제 링크 :

📌 문제 설명 :

USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요. 결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요. 이때, 성별 정보가 없는 경우 결과에서 제외해주세요.

📌 테이블 : 

다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 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 조합에 대해서는 하나의 판매 데이터만 존재합니다.

풀이

추측) 

분류하고자 하는 3개 컬럼과 회원 아이디 만을 질의하는 쿼리 먼저하고 이를 분류 컬럼으로 Group by해줘서 count하면 될듯하다. 

쿼리) 

* 1차) 성공

SELECT A.YEAR, A.MONTH, A.GENDER, COUNT(A.USER_ID) AS USERS
FROM
    (SELECT 
        YEAR(SALES_DATE) AS YEAR, 
        MONTH(SALES_DATE) AS MONTH,
        GENDER,
        U.USER_ID
    FROM ONLINE_SALE AS O
    LEFT OUTER JOIN USER_INFO AS U
    ON O.USER_ID = U.USER_ID
    WHERE U.GENDER IS NOT NULL
    GROUP BY YEAR, MONTH, GENDER, U.USER_ID) AS A
GROUP BY A.YEAR, A.MONTH, A.GENDER
ORDER BY YEAR ASC, MONTH ASC, GENDER ASC

* 2차) 성공

SELECT A.YEAR, A.MONTH, A.GENDER, COUNT(A.USER_ID) AS USERS
FROM
    (SELECT DISTINCT
        YEAR(SALES_DATE) AS YEAR, 
        MONTH(SALES_DATE) AS MONTH,
        GENDER,
        U.USER_ID
    FROM ONLINE_SALE AS O
    LEFT OUTER JOIN USER_INFO AS U
    ON O.USER_ID = U.USER_ID
    WHERE U.GENDER IS NOT NULL) AS A
GROUP BY A.YEAR, A.MONTH, A.GENDER
ORDER BY YEAR ASC, MONTH ASC, GENDER ASC

리뷰) 

+ 같은 달의 중복 구매 회원을 제외 할 필요도 있었다

그리고 좀더 효율적으로 바꾸고 싶어!

1차에서의 Group by를 제외하고 Distinct로 중복을 제거하는 방식으로 같은 달에 여러번 구매한 회원수를 제외했다.

이러니까 여러모로 공수가 많이 드는 Group by를 하나 제외할 수 있었음!

문제

📌 MySQL 로 풀이

📌 문제 링크 :

📌 문제 설명 :

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중' 이라고 표시하고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명: AVAILABILITY)을 추가하여 자동차 ID와 AVAILABILITY 리스트를 출력하는 SQL문을 작성해주세요. 이때 반납 날짜가 2022년 10월 16일인 경우에도 '대여중'으로 표시해주시고 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요.

📌 테이블 :

다음은 어느 자동차 대여 회사의 자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블입니다. CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블은 아래와 같은 구조로 되어있으며, HISTORY_ID, CAR_ID, START_DATE, END_DATE 는 각각 자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다.

풀이

쿼리) 

SELECT 
    C.CAR_ID,
    CASE WHEN A.CAR_ID IS NULL THEN '대여 가능' ELSE '대여중' END AS AVAILABILITY
FROM (SELECT DISTINCT CAR_ID 
      FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) AS C
LEFT OUTER JOIN 
    (SELECT DISTINCT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE '2022-10-16' BETWEEN START_DATE AND END_DATE) AS A
ON C.CAR_ID = A.CAR_ID
ORDER BY C.CAR_ID DESC

리뷰) 

특정 날짜가 어느 날짜 사이에 있는지 알아보려면 BETWEEN이나 부등호를 써야한다.

그리고 그것보다 더 중요했던 건

데이터베이스는 데이터가 어떤식으로 쌓여있는지 잘 파악하여야 한다는 걸 알려준 문제였다.

렌트 내역을 쌓아놓은 테이블이라 같은 car_id가 렌트되어진 내역이 여럿 존재할 수 있다는 것을 생각했어야했다.

그래서 distinct로 중복을 제거한 C와 2022-10-16일에 대여중인 car_id만 질의하여 리턴한 테이블을 join하는데

이때 모든 car_id에 대해 대여중인 car_id가 1:1 매칭이 안되므로 모든 car_id를 가진 C를 기준으로 left outer join하였고,

A의 car_id가 null이면 대여가능 아니면 대여중으로 반환해준다.

문제

📌 MySQL로 풀이

📌 문제 링크 :

📌 문제 설명 :

USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 중고 거래 게시물을 3건 이상 등록한 사용자의 사용자 ID, 닉네임, 전체주소, 전화번호를 조회하는 SQL문을 작성해주세요. 이때, 전체 주소는 시, 도로명 주소, 상세 주소가 함께 출력되도록 해주시고, 전화번호의 경우 xxx-xxxx-xxxx 같은 형태로 하이픈 문자열(-)을 삽입하여 출력해주세요. 결과는 회원 ID를 기준으로 내림차순 정렬해주세요.

📌 테이블 :

다음은 중고 거래 게시판 정보를 담은 USED_GOODS_BOARD 테이블과 중고 거래 게시판 첨부파일 정보를 담은 USED_GOODS_FILE 테이블입니다. USED_GOODS_BOARD 테이블은 다음과 같으며 BOARD_ID, WRITER_ID, TITLE, CONTENTS, PRICE, CREATED_DATE, STATUS, VIEWS는 게시글 ID, 작성자 ID, 게시글 제목, 게시글 내용, 가격, 작성일, 거래상태, 조회수를 의미합니다.

USED_GOODS_USER 테이블은 다음과 같으며 USER_ID, NICKNAME, CITY, STREET_ADDRESS1, STREET_ADDRESS2, TLNO는 각각 회원 ID, 닉네임, 시, 도로명 주소, 상세 주소, 전화번호를 의미합니다.

풀이

추측) 

이번 문제는 데이터를 가공할 수 있는 함수를 알아야 풀 수 있었던 문제

쿼리) 

SELECT 
    USER_ID, 
    NICKNAME, 
    CONCAT(CITY, ' ' , STREET_ADDRESS1, ' ', STREET_ADDRESS2) AS '전체주소',
    CONCAT(LEFT(TLNO,3), '-', MID(TLNO, 4, 4), '-', RIGHT(TLNO,4)) AS '전화번호'
FROM USED_GOODS_USER 
WHERE USER_ID IN (SELECT WRITER_ID
                    FROM USED_GOODS_BOARD 
                    GROUP BY WRITER_ID
                    HAVING COUNT(WRITER_ID) >= 3)
ORDER BY USER_ID DESC

리뷰) 

CONCAT함수는 여러 문자열 또는 컬럼 값을 합쳐서 조회할 수 있도록 하는 함수이다. 

기본 형식은 

CONCAT(문자열1|컬럼1 , 문자열2|컬럼2 [, 문자열3|컬럼3 ....]) AS 칼럼명

그러니까 문자열과 문자열, 컬럼과 컬럼 뿐만 아니라

CONCAT ('제 닉네임은 ', NICKNAME, '입니다') AS '닉네임' 

식으로 문자열과 컬럼을 섞어 출력도 가능하다