✈️ SQL Programmers Level 3 - 19 Solved

001. 대장균들의 자식의 수 구하기

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

 

✨

(1) 한 테이블 내부에 ID와 PARENT_ID 모두 동일한 경우는 두 동일 테이블을 JOIN 해야 한다.

: 왼쪽 A, 오른쪽 B라 했을 때 왼쪽 ID를 parent, 오른쪽 ID를 child로 설정해서 A.ID = B.PARENT_ID로 JOIN

(2) A의 정보 ID 기준 JOIN이므로 LEFT JOIN

(3) 자식의 수를 출력해야 하므로 GROUP BY A.ID로 A 기준 그룹화 

(4) 개체의 ID에 대해 오름차순 정렬이므로 ORDER BY A.ID ASC

(5) 그리고 개체 수는 COUNT(B.ID)로 해야 ID의 카운트로 개체 수 출력됨.

 

** GROUP BY 없이 아래와 같은 쿼리 결과

SELECT A.ID, B.ID
FROM ECOLI_DATA AS A LEFT JOIN ECOLI_DATA AS B ON A.ID = B.PARENT_ID
ORDER BY A.ID ASC

: 즉, inner join의 결과로 왼쪽 A.ID에 연결된 B.ID 개체가 나열되었다. 이 때, 우리는 각 개체(A.ID)에 맞는 B.ID의 개수, 즉 개체의 수를 원하므로, 일단, A.ID를 그룹화 하고, COUNT(B.ID)를 구하면 된다. 이는 곧, CHILD_COUNT

---

002. 대장균의 크기에 따라 분류하기 1

SELECT ID,
    CASE
        WHEN SIZE_OF_COLONY <= 100 THEN 'LOW'
        WHEN SIZE_OF_COLONY <= 1000 THEN 'MEDIUM'
        ELSE 'HIGH'
    END AS SIZE
FROM ECOLI_DATA
ORDER BY ID

 

✨

(1) sql에서의 if문은 CASE ~ WHEN / ELSE ~ END 문으로 진행 가능. WHEN에 if문 조건을 넣어서 표시되는 글자는 THEN 오른쪽에 표시

(아래 그림으로 기억) - 조건에 따라 A / B / C 나누어서 표현 / 나타나는 칼럼명은 D

---

003. 대장균의 크기에 따라 분류하기 2

SELECT A.ID,
    CASE
        WHEN A.PER <= 0.25 THEN 'CRITICAL'
        WHEN A.PER <= 0.5 THEN 'HIGH'
        WHEN A.PER <= 0.75 THEN 'MEDIUM'
        ELSE 'LOW'
    END AS COLONY_NAME
FROM(
    SELECT *,
    PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) AS PER
    FROM ECOLI_DATA
) AS A
ORDER BY A.ID

 

✨

** 특정 column의 percent는 따로 PERCENT_RANK() 사용

(1) FROM 문에서 A 테이블의 PER 정의. 이 때, OVER문 오른쪽에 칼럼 이름 쓰고, DESC 붙이면 내림차순 정렬된 곳에서 % 산출 → SELECT *로 전체 칼럼 해당되는 A 테이블

(2) A 테이블의 PER에 따라 case when문으로 COLONY_NAME 새 칼럼 정의

(3) A.ID 오름차순으로 출력

---

004. 물고기 종류별 대어 찾기

SELECT A.ID, B.FISH_NAME, A.LENGTH AS LENGTH 
FROM FISH_INFO AS A JOIN FISH_NAME_INFO AS B ON A.FISH_TYPE = B.FISH_TYPE
WHERE A.FISH_TYPE IN (
    SELECT FISH_TYPE
    FROM FISH_INFO
    GROUP BY FISH_TYPE
    HAVING LENGTH = MAX(LENGTH)
    )
ORDER BY A.ID

 

✨

(1) 일단 두 테이블 JOIN으로 연결하고

(2) WHERE문에 sub-query로 A.FISH_TYPE이 GROUP BY로 그룹화된 결과의 MAX(LENGTH)일 때의 FISH_TYPE 경우일 때만의 row data에서의 column을 select해서 출력

---

005. 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

SELECT CAR_ID, 
    CASE 
        WHEN CAR_ID IN 
        (
            SELECT CAR_ID
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
            WHERE '2022-10-16' BETWEEN START_DATE AND END_DATE
        ) THEN '대여중'
        ELSE '대여 가능'
    END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

 

✨

* BETWEEN 구문을 쓰면 START_DATE와 END_DATE 모두 포함. CAR_ID IN (SELECT CAR_ID ~ ) 서브쿼리 구문 사용해서 IN 구문으로 2022년 10월 16일이 포함된 CAR_ID에 들어 있는 지 확인. 들어 있다면, CASE 문으로 '대여중', ELSE 문으로 '대여 가능'

---

006. 카테고리 별 도서 판매량 집계하기

SELECT A.CATEGORY, SUM(B.SALES) AS TOTAL_SALES
FROM BOOK AS A INNER JOIN BOOK_SALES AS B ON A.BOOK_ID = B.BOOK_ID
WHERE B.SALES_DATE LIKE '2022-01%'
GROUP BY A.CATEGORY
ORDER BY A.CATEGORY ASC

 

✨

(1) FROM) 일단 두 테이블을 합쳐야 하므로 INNER JOIN 진행(공통된 부분만 뽑으므로 INNER)

(2) WHERE) 합친 두 테이블에서 2022년 1월 row만 뽑으므로 LIKE 진행

(3) GROUP BY) '카테고리 별' 이므로 카테고리로 그룹(그룹에 조건은 없으므로 HAVING x)

(4) SELECT) '카테고리 별 도서 판매량 합산'이므로, 도서 판매량에 SUM() 함수 적용.

(5) ORDER BY) 카테고리명으로 오름차순 정렬

---

007. 즐겨찾기가 가장 많은 식당 정보 출력하기

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

 

✨

(1) '음식종류별 즐겨찾기 수가 가장 많은 식당'을 구하므로, 음식종류인 FOOD_TYPE과 즐겨찾기인 FAVORITES를 WHERE문 조건으로 FOOD_TYPE과 FAVORITES 원하는 row를 먼저 필터링

: 필터링하기 위해 IN ()안에 SELECT문 중첩. 

 

(2) SELECT 일부 행 진행하고 ORDER BY로 정렬

 

** HAVING이 안 되는 이유, 그룹화한 결과에서 filtering해야 하는데 불가능. MAX를 구하려면 그 자체로 group by를 해야 하기 때문(주의!)

---

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

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

 

✨

* 중고거래의 '총' 금액이면 GROUP BY 필수, 거기에 조건이 붙었으므로 HAVING 진행

(1) 먼저 FROM으로 두 테이블 inner join

(2) WHERE 절로 status = 'done'인 행들 중에서

(3) GROUP BY로 조건에 맞는 경우만 grouping 해서 표현

(4) select 일부 행 진행

(5) order by

---

009. 부서별 평균 연봉 조회하기 / 010. 특정 조건을 만족하는 물고기별 수와 최대 길이 구하기

SELECT A.DEPT_ID, A.DEPT_NAME_EN, ROUND(AVG(B.SAL),0) AS 'AVG_SAL'
FROM HR_DEPARTMENT AS A INNER JOIN HR_EMPLOYEES AS B ON A.DEPT_ID = B.DEPT_ID
GROUP BY A.DEPT_ID
ORDER BY AVG_SAL DESC

 

SELECT COUNT(*) AS FISH_COUNT, MAX(IFNULL(LENGTH,10)) AS MAX_LENGTH, FISH_TYPE
FROM FISH_INFO
GROUP BY FISH_TYPE
HAVING AVG(IFNULL(LENGTH,10)) >= 33
ORDER BY FISH_TYPE ASC

 

✨ IFNULL()과 MAX(), 그리고 COUNT()를 활용해 쉽게 구할 수 있다. 해당 3개는 별도의 중첩없이 바로 사용 가능한 함수

---

011. 업그레이드 할 수 없는 아이템 구하기 / 012. 없어진 기록 찾기

SELECT A.ITEM_ID, A.ITEM_NAME, A.RARITY
FROM ITEM_INFO AS A INNER JOIN ITEM_TREE AS B ON A.ITEM_ID = B.ITEM_ID
WHERE A.ITEM_ID NOT IN
(SELECT PARENT_ITEM_ID
FROM ITEM_TREE
WHERE NOT ISNULL(PARENT_ITEM_ID))
ORDER BY ITEM_ID DESC

 

✨ 문제를 읽어보면, 업그레이드 할 수 없는 아이템은 PARENT_ID에 없는 ITEM_ID인 경우이다. 

 

** WHERE 절을 활용해서 NOT ISNULL을 반드시 체크! NOT IN의 경우 AND 연산이 되어서 하나라도 NULL인 데이터가 존재한다면 아예 전체 FALSE 처리. 따라서 NOT IN의 경우 ISNULL인 데이터가 존재하는 지 반드시 체크!!!

 

SELECT ANIMAL_ID, NAME
FROM ANIMAL_OUTS
WHERE ANIMAL_ID NOT IN
(SELECT ANIMAL_ID
 FROM ANIMAL_INS
 WHERE NOT ISNULL(ANIMAL_ID))
ORDER BY ANIMAL_ID

 

✨ NOT IN 쓰므로 NOTISNULL 필수 + 내용에 따라 ANIMAL_ID가 한 테이블에 없는 경우이므로 중첩 SELECT문 사용

---

013. 있었는데요 없었습니다 / 014. 오랜 기간 보호한 동물 (1)

SELECT ANIMAL_INS.ANIMAL_ID, ANIMAL_INS.NAME
FROM ANIMAL_INS INNER JOIN ANIMAL_OUTS ON ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
WHERE ANIMAL_INS.DATETIME > ANIMAL_OUTS.DATETIME
ORDER BY ANIMAL_INS.DATETIME

 

✨ 테이블 조인할 때 두 칼럼명 같아도 출처 작성하면 가능

SELECT A.NAME, A.DATETIME
FROM ANIMAL_INS AS A LEFT JOIN ANIMAL_OUTS AS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.ANIMAL_ID NOT IN
(SELECT ANIMAL_ID
FROM ANIMAL_OUTS
WHERE NOT ISNULL(ANIMAL_ID))
ORDER BY A.DATETIME ASC
LIMIT 3

 

✨ LEFT JOIN 필요) 테이블 B는 없는 ANIMAL_ID이되, 테이블 A에는 있어야 하므로. 특히 B에는 없는 A에서의 ANIMAL_ID여야 하기에 그렇다. 

---

015. 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기 / 016. 조건별로 분류하여 주문상태 출력하기

SELECT CONCAT("/home/grep/src/", B.BOARD_ID, "/", B.FILE_ID, B.FILE_NAME, B.FILE_EXT) AS FILE_PATH
FROM USED_GOODS_BOARD AS A INNER JOIN USED_GOODS_FILE AS B ON A.BOARD_ID = B.BOARD_ID
WHERE A.VIEWS IN (SELECT MAX(VIEWS) FROM USED_GOODS_BOARD)
ORDER BY B.FILE_ID DESC

 

✨ 한 컬럼이 최댓값일 때의 정보를 보이게 하면 된다. SELECT 문 IN과 함께 중첩. CONCAT() 함수를 사용해 ,를 기준으로 문자열을 연결 가능.

 

SELECT ORDER_ID, PRODUCT_ID, DATE_FORMAT(OUT_DATE, '%Y-%m-%d') as OUT_DATE,
    CASE 
        WHEN OUT_DATE <= '2022-05-01' THEN '출고완료'
        WHEN OUT_DATE IS NULL THEN '출고미정'
        ELSE '출고대기'
    END AS '출고여부'
FROM FOOD_ORDER
ORDER BY ORDER_ID

 

✨ SQL에서의 IF문은 CASE - WHEN THEN / ELSE - END AS ~로 진행

---

017. 대여 기록이 존재하는 자동차 리스트 구하기 / 018. 조건에 맞는 사용자 정보 조회하기

SELECT DISTINCT(A.CAR_ID)
FROM CAR_RENTAL_COMPANY_CAR AS A INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS B ON A.CAR_ID = B.CAR_ID
WHERE CAR_TYPE = '세단' AND MONTH(START_DATE) = '10'
ORDER BY A.CAR_ID DESC

 

✨ 중복이 없게 하려면 DISTINCT() 사용

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

 

✨

* SUBSTR()을 활용해 SUBSTR(칼럼명, 시작위치, 문자열내용길이)로 전체 문자열 중 일부 문자열 추출 가능.

* 각 사용자별 3건 이상 등록이므로 HAVING 절 사용 필수.

---

019. 오랜 기간 보호한 동물(2)

SELECT A.ANIMAL_ID, B.NAME
FROM ANIMAL_INS AS A INNER JOIN ANIMAL_OUTS AS B ON A.ANIMAL_ID = B.ANIMAL_ID
ORDER BY DATEDIFF(B.DATETIME, A.DATETIME) DESC
LIMIT 2

 

✨ DATEDIFF()를 활용하여 ORDER BY로 내림차순 정렬 가능. LIMIT 2로 가장 큰 2개만 선택

---