프로그래머스 코딩테스트 연습 Lv2

프로그래머스 MySQL Lv2

---

 

PRODUCT 테이블

 

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

 

풀이

- Oracle TRUNC(숫자, 위치 n) / MySQL TRUNCATE(숫자, 위치 n)

  : 버림 연산, n이 양수면 소수점 n번째 자리까지 유지, n이 음수면 정수 n번째에서 버림

- Oracle과 MySQL 모두 실행 순서는 GROUP BY → SELECT지만

  GROUP BY 1에서의 1은 SELECT절의 컬럼 위치를 참조하는 문법적 표현으로 사용 가능

  (GROUP BY절에 칼럼 별칭을 사용하는 것은 MySQL에서만 가능)

SELECT TRUNCATE(PRICE, -4) AS PRICE_GROUP, COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY 1
ORDER BY 1

 

 

카테고리 별 상품 개수 구하기
: 상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 상품 개수를 출력하는 SQL문을 작성해주세요. 결과는 상품 카테고리 코드를 기준으로 오름차순 정렬해주세요.

 

풀이

- Oracle SUBSTR(문자열, n, m) / MySQL SUBSTRING(문자열, n, m)

  : 문자열의 n번째 위치에서부터 m개의 문자열 추출, 이때 문자열의 index는 1부터 시작

SELECT SUBSTRING(PRODUCT_CODE, 1, 2) AS CATEGORY, COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY 1
ORDER BY 1

 

 

 

 

HR_DEPARTMENT 테이블
: DEPT_ID, DEPT_NAME_KR, DEPT_NAME_EN, LOCATION은 각각 부서 ID, 국문 부서명, 영문 부서명, 부서 위치를 의미

HR_EMPLOYEES 테이블
: EMP_NO, EMP_NAME, DEPT_ID, POSITION, EMAIL, COMP_TEL, HIRE_DATE, SAL은 각각 사번, 성명, 부서 ID, 직책, 이메일, 전화번호, 입사일, 연봉을 의미

HR_GRADE 테이블
: EMP_NO, YEAR, HALF_YEAR, SCORE는 각각 사번, 연도, 반기, 평가 점수를 의미

 

조건에 맞는 사원 정보 조회하기
: 2022년도 평가 점수가 가장 높은 사원들의 점수, 사번, 성명, 직책, 이메일을 조회하는 SQL문을 작성해주세요. 2022년도의 평가 점수는 상,하반기 점수의 합을 의미하고, 평가 점수를 나타내는 컬럼의 이름은 SCORE로 해주세요.

 

풀이 1

- FROM절 서브쿼리(인라인 뷰) 사용

SELECT SCORE, EMP_NO, EMP_NAME, POSITION, EMAIL
FROM HR_EMPLOYEES LEFT OUTER JOIN (
    SELECT EMP_NO, SUM(SCORE) AS SCORE
    FROM HR_GRADE
    GROUP BY EMP_NO) A USING(EMP_NO)
ORDER BY 1 DESC
LIMIT 1

 

풀이 2

- GROUP BY를 사용한 경우 SELECT절에는 GROUP BY에 사용한 칼럼 + 집계함수 결과만 올 수 있음

  SELECT할 모든 칼럼에 대해서 GROUP BY를 해주어야 에러가 발생하지 않음 

SELECT SUM(SCORE) AS SCORE, EMP_NO, EMP_NAME, POSITION, EMAIL
FROM HR_EMPLOYEES LEFT OUTER JOIN HR_GRADE USING(EMP_NO)
GROUP BY EMP_NO, EMP_NAME, POSITION, EMAIL
ORDER BY 1 DESC
LIMIT 1

 

 

 

 

CAR_RENTAL_COMPANY_CAR 테이블

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

 

풀이 1

- 대상 문자열 LIKE '%찾을 문자열%' → 특정 문자열을 포함하는지 확인 

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

 

풀이 2

- 정규 표현식 이용

- REGEXP_LIKE(대상 문자열, 찾을 패턴, [옵션]) : 대상 문자열이 특정 패턴을 갖는 경우 리턴

SELECT CAR_TYPE, COUNT(*) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE REGEXP_LIKE(OPTIONS, '통풍시트|열선시트|가죽시트')
GROUP BY CAR_TYPE
ORDER BY 1

 

 

 

 

USED_GOODS_BOARD 테이블

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

 

풀이 

- CASE WHEH ~ THEN ~

             WHEN ~ THEN ~

             ELSE ~ END 

- CASE ~ WHEN ~ THEN ~

                WHEN ~ THEN ~

                ELSE ~ END 

  이 경우 비교하는 두  대상의 데이터 타입이 반드시 일치해야 함  

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

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE,
(CASE STATUS WHEN 'SALE' THEN '판매중'
            WHEN 'RESERVED' THEN '예약중'
            WHEN 'DONE' THEN '거래완료'
            ELSE '기타'
            END) AS STATUS
FROM USED_GOODS_BOARD
WHERE CREATED_DATE = DATE('2022-10-05')
ORDER BY 1 DESC

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE,
(CASE WHEN STATUS = 'SALE' THEN '판매중'
      WHEN STATUS = 'RESERVED' THEN '예약중'
      WHEN STATUS = 'DONE' THEN '거래완료'
      ELSE '기타'
            END) AS STATUS
FROM USED_GOODS_BOARD
WHERE CREATED_DATE LIKE '2022-10-05'
ORDER BY 1 DESC

 

 

 

 

ECOLI_DATA 테이블

분기별 분화된 대장균의 개체 수 구하기
: 각 분기(QUARTER)별 분화된 대장균의 개체의 총 수(ECOLI_COUNT)를 출력하는 SQL 문을 작성해주세요. 이때 각 분기에는 'Q' 를 붙이고 분기에 대해 오름차순으로 정렬해주세요. 대장균 개체가 분화되지 않은 분기는 없습니다.

 

풀이

- FROM절 서브쿼리(인라인 뷰) 사용

- Oracle의 경우 SELECT절에서 정의한 칼럼 별칭을 GROUP BY절에서 사용 불가능

  MySQL의 경우 SELECT절에서 정의하는 칼럼 별칭을 GROUP BY에서도 사용 가능 

SELECT QUARTER, COUNT(*) AS ECOLI_COUNT
FROM (SELECT (CASE WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 1 AND 3 THEN '1Q'
                   WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 4 AND 6 THEN '2Q'
                   WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 7 AND 9 THEN '3Q'
                   ELSE '4Q'
             END) AS QUARTER
      FROM ECOLI_DATA) E
GROUP BY QUARTER
ORDER BY 1

SELECT (CASE WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 1 AND 3 THEN '1Q'
                   WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 4 AND 6 THEN '2Q'
                   WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 7 AND 9 THEN '3Q'
                   ELSE '4Q'
             END) AS QUARTER, COUNT(*) AS ECOLI_COUNT
FROM ECOLI_DATA
GROUP BY QUARTER
ORDER BY 1

 

 

연도별 대장균 크기의 편차 구하기
: 분화된 연도(YEAR), 분화된 연도별 대장균 크기의 편차(YEAR_DEV), 대장균 개체의 ID(ID) 를 출력하는 SQL 문을 작성해주세요. 결과는 연도에 대해 오름차순으로 정렬하고 같은 연도에 대해서는 대장균 크기의 편차에 대해 오름차순으로 정렬해주세요.

 

풀이 1

- FROM절 서브쿼리(인라인 뷰) 사용

SELECT A.YEAR, (M_COLONY - SIZE_OF_COLONY) AS YEAR_DEV, ID
FROM ECOLI_DATA LEFT OUTER JOIN (
    SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR, MAX(SIZE_OF_COLONY) AS M_COLONY
    FROM ECOLI_DATA
    GROUP BY YEAR(DIFFERENTIATION_DATE)) A
    ON YEAR(ECOLI_DATA.DIFFERENTIATION_DATE) = A.YEAR
ORDER BY 1, 2

 

풀이 2

- WINDOW 함수 사용 : 같은 테이블 내 그룹별 함수 적용 결과를 출력에 사용하고 싶은 경우

SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR,
	MAX(SIZE_OF_COLONY) OVER (PARTITION BY YEAR(DIFFERENTIATION_DATE)) - SIZE_OF_COLONY AS YEAR_DEV, ID
FROM ECOLI_DATA
ORDER BY 1, 2