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

프로그래머스 MySQL Lv3

---

ANIMAL_INS 테이블

 

ANIMAL_OUTS 테이블 

: ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냄. ANIMAL_OUTS 테이블의 ANIMAL_ID는 ANIMAL_INS의 ANIMAL_ID의 외래 키

 

풀이 1 - NOT IN 연산자 사용 

SELECT NAME, DATETIME
FROM ANIMAL_INS
WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_OUTS)
ORDER BY 2
LIMIT 3

 

풀이 2 - OUTER JOIN 사용 

SELECT A.NAME, A.DATETIME
FROM ANIMAL_INS A LEFT OUTER JOIN ANIMAL_OUTS B
    ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.ANIMAL_ID IS NULL
ORDER BY 2
LIMIT 3;

 

 

 

 

 

REST_INFO 테이블

: REST_ID, REST_NAME, FOOD_TYPE, VIEWS, FAVORITES, PARKING_LOT, ADDRESS, TEL은 식당 ID, 식당 이름, 음식 종류, 조회수, 즐겨찾기수, 주차장 유무, 주소, 전화번호를 의미

즐겨찾기가 가장 많은 식당 정보 출력하기
: 음식종류별로 즐겨찾기수가 가장 많은 식당의 음식 종류, ID, 식당 이름, 즐겨찾기수를 조회하는 SQL문을 작성해주세요. 이때 결과는 음식 종류를 기준으로 내림차순 정렬해주세요.

 

풀이 1

- WHERE절 서브쿼리, 연관 서브쿼리

- 메인 쿼리 table을 테이블 별칭으로 지칭하고 서브쿼리로 전달

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO A
WHERE FAVORITES = (SELECT MAX(FAVORITES)
                FROM REST_INFO
                WHERE A.FOOD_TYPE = FOOD_TYPE)
ORDER BY 1 DESC

 

풀이 2

- WHERE절 서브쿼리, IN 함수 사용

- 다중 칼럼 서브쿼리 : 비교할 칼럼을 ( )로 묶어서 IN 연산 

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO A
WHERE (FOOD_TYPE, FAVORITES) IN (SELECT FOOD_TYPE, MAX(FAVORITES)
                FROM REST_INFO
                GROUP BY FOOD_TYPE)
ORDER BY 1 DESC

 

풀이 3 

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

- WINDOW function, RANK / ROW_NUMBER 함수 

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM (SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES,
      RANK() OVER (PARTITION BY FOOD_TYPE ORDER BY FAVORITES DESC) AS RANKING
      FROM REST_INFO) A
WHERE RANKING = 1
ORDER BY 1 DESC

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM (SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES,
      ROW_NUMBER() OVER (PARTITION BY FOOD_TYPE ORDER BY FAVORITES DESC) AS RNUM
      FROM REST_INFO) A
WHERE RNUM = 1
ORDER BY 1 DESC

 

 

 

 

 

USED_GOODS_BOARD 테이블

USED_GOODS_USER 테이블

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

 

풀이 1 

- CONCAT(문자열1, 문자열2, ....) : 나열한 여러 문자열을 바로 연결

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

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

SELECT USER_ID, NICKNAME,
        CONCAT(CITY, ' ', STREET_ADDRESS1, ' ', STREET_ADDRESS2) AS '전체주소',
        CONCAT(
            SUBSTRING(TLNO, 1, 3), '-', 
            SUBSTRING(TLNO, 4, 4), '-', 
            SUBSTRING(TLNO, 8, 4)
          ) AS '전화번호'
FROM USED_GOODS_BOARD A INNER JOIN USED_GOODS_USER B
ON A.WRITER_ID = B.USER_ID
GROUP BY USER_ID, NICKNAME
HAVING COUNT(*) >= 3
ORDER BY 1 DESC

 

풀이 2

- CONCAT_WS(구분자, 문자열1, 문자열2 ...) : 구분자와 함께 나열된 문자열들을 연결

SELECT USER_ID, NICKNAME,
CONCAT_WS(' ', CITY, STREET_ADDRESS1, STREET_ADDRESS2) AS '전체주소',
CONCAT(
    SUBSTRING(TLNO, 1, 3), '-', 
    SUBSTRING(TLNO, 4, 4), '-', 
    SUBSTRING(TLNO, 8, 4)
  ) AS '전화번호'
FROM USED_GOODS_BOARD A INNER JOIN USED_GOODS_USER B
ON A.WRITER_ID = B.USER_ID
GROUP BY USER_ID, NICKNAME
HAVING COUNT(*) >= 3
ORDER BY 1 DESC

 

 

 

 

 

 

ECOLI_DATA 테이블

대장균의 크기에 따라 분류하기 2
: 대장균 개체의 크기를 내름차순으로 정렬했을 때 상위 0% ~ 25% 를 'CRITICAL', 26% ~ 50% 를 'HIGH', 51% ~ 75% 를 'MEDIUM', 76% ~ 100% 를 'LOW' 라고 분류합니다. 대장균 개체의 ID(ID) 와 분류된 이름(COLONY_NAME)을 출력하는 SQL 문을 작성해주세요. 이때 결과는 개체의 ID 에 대해 오름차순 정렬해주세요.

 

풀이

- WINDOW function 사용

• NTILE(N) : N개의 그룹으로 나누고 그룹 번호를 리턴 (중복값이 다른 그룹 가능)
• CUME_DIST( ) : 현재 행보다 작거나 같은, 누적값의 비율을 리턴 (중복값은 비율을 가짐)
• PERCENT_RANK( ) : 순위의 백분위 (중복값은 같은 순위를 가짐)
• 3가지 모두 ORDER BY절이 필수

# NTILE
SELECT ID, (
    CASE NRANK WHEN 1 THEN 'CRITICAL'
               WHEN 2 THEN 'HIGH'
               WHEN 3 THEN 'MEDIUM'
               ELSE 'LOW' END) AS COLONY_NAME
FROM (SELECT ID, NTILE(4) OVER (ORDER BY SIZE_OF_COLONY DESC) NRANK
      FROM ECOLI_DATA) A
ORDER BY 1

# CUME_DIST
SELECT ID, (
        CASE 
        WHEN CUME <= 0.25 THEN 'CRITICAL'
        WHEN CUME <= 0.50 THEN 'HIGH'
        WHEN CUME <= 0.75 THEN 'MEDIUM'
        ELSE 'LOW' END) AS COLONY_NAME
FROM (SELECT ID, CUME_DIST() OVER (ORDER BY SIZE_OF_COLONY DESC) AS CUME
      FROM ECOLI_DATA) A
ORDER BY 1

# PERCENT_RANK
SELECT ID, (
        CASE 
        WHEN PRANK <= 0.25 THEN 'CRITICAL'
        WHEN PRANK <= 0.50 THEN 'HIGH'
        WHEN PRANK <= 0.75 THEN 'MEDIUM'
        ELSE 'LOW' END) AS COLONY_NAME
FROM (SELECT ID, PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) AS PRANK
      FROM ECOLI_DATA) A
ORDER BY 1

 

 

 

대장균들의 자식의 수 구하기
: 대장균 개체의 ID(ID)와 자식의 수(CHILD_COUNT)를 출력하는 SQL 문을 작성해주세요. 자식이 없다면 자식의 수는 0으로 출력해주세요. 이때 결과는 개체의 ID 에 대해 오름차순 정렬해주세요.

 

풀이 1

- 같은 테이블끼리 JOIN시 테이블 별칭 반드시 사용 

SELECT A.ID AS ID, COUNT(B.PARENT_ID) AS CHILD_COUNT
FROM ECOLI_DATA A LEFT OUTER JOIN ECOLI_DATA B
ON A.ID = B.PARENT_ID
GROUP BY 1
ORDER BY 1

 

풀이 2

- SELECT절 서브쿼리(스칼라 서브쿼리) 사용 

SELECT ID, (
    SELECT COUNT(*)
    FROM ECOLI_DATA B
    WHERE A.ID = B.PARENT_ID
    ) AS CHILD_COUNT
FROM ECOLI_DATA A
ORDER BY 1

 

 

 

 

 

 

ITEM_INFO 테이블
: ITEM_ID, ITEM_NAME, RARITY, PRICE는 각각 아이템 ID, 아이템 명, 아이템의 희귀도, 아이템의 가격을 나타냄

ITEM_TREE 테이블
: ITEM_ID, PARENT_ITEM_ID는 각각 아이템 ID, PARENT 아이템의 ID를 나타냄

 

업그레이드 할 수 없는 아이템 구하기
: 더 이상 업그레이드할 수 없는 아이템의 아이템 ID(ITEM_ID), 아이템 명(ITEM_NAME), 아이템의 희귀도(RARITY)를 출력하는 SQL 문을 작성해 주세요. 이때 결과는 아이템 ID를 기준으로 내림차순 정렬해 주세요.

 

풀이 1

- NOT IN 함수 사용

- NOT IN (NULL, ....)은 항상 FALSE이므로 비교할 리스트 안의 NULL을 제거해야함 

SELECT ITEM_ID, ITEM_NAME, RARITY
FROM ITEM_INFO LEFT OUTER JOIN ITEM_TREE USING(ITEM_ID)
WHERE ITEM_ID NOT IN (SELECT DISTINCT PARENT_ITEM_ID
                     FROM ITEM_TREE
                     WHERE PARENT_ITEM_ID IS NOT NULL)
ORDER BY 1 DESC

 

풀이 2

- JOIN 결과로부터 NULL인 것 찾기 

SELECT A.ITEM_ID, ITEM_NAME, RARITY
FROM ITEM_INFO A LEFT OUTER JOIN ITEM_TREE B
ON A.ITEM_ID = B.PARENT_ITEM_ID
WHERE B.ITEM_ID IS NULL
ORDER BY 1 DESC