SQL ) LV2. 연도별 대장균 크기의 편차 구하기
접근법
- 각 연도별 최대 크기를 구한뒤 각 박테리아별로 현재 속행씨는 그룹의 최대 크기와의 편차를 출력해야한다.
- 먼저, 서브쿼리를 통해 연도 기준 그룹화 후, 연도별 최대 크기와 연도 레코드를 반환하는 인라인 뷰를 만들었다(E2)
- 이후, 원본 테이블과 인라인 뷰를 카티션프로덕트하고, where절에서 두 테이블의 년도가 같은 것만 추렸다
- 마지막으로, select절에서 인라인 뷰의 year칼럼과, 편차(인라인 뷰 max_size - 원본 테이블 size_of_colony), 원본 테이블의 id를 함께 조회하도록 했다.
코드
SELECT YEAR, MAX_SIZE - SIZE_OF_COLONY YEAR_DEV, ID
FROM ECOLI_DATA, (SELECT YEAR(DIFFERENTIATION_DATE) YEAR, MAX(SIZE_OF_COLONY) MAX_SIZE FROM ECOLI_DATA GROUP BY YEAR) E2
WHERE YEAR(DIFFERENTIATION_DATE) = YEAR
ORDER BY 1, 2;
SQL ) LV3. 즐겨찾기가 가장 많은 식당 정보 출력하기
접근법
- FROM절에서 서브쿼리로 FOOD_TYPE 기준 그룹별 FAVORITES의 최댓값과 FOOD_TYPE을 조회하는 인라인 뷰 R2 생성
- 원본 테이블 REST_INFO R1을 인라인 뷰 R2와 카티션 프로덕트 후, WHERE 절에서 FOOD_TYPE 칼럼이 서로 갑으면서, FAVORITES값이 R2칼럼의 MAX_F값과 일치하는 레코드 조회
코드
SELECT R1.FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO R1, (SELECT MAX(FAVORITES) MAX_F, FOOD_TYPE FROM REST_INFO GROUP BY FOOD_TYPE) R2
WHERE R1.FOOD_TYPE = R2.FOOD_TYPE AND FAVORITES = MAX_F
ORDER BY 1 DESC;
SQL ) LV3. 조건별로 분류하여 주문상태 출력하기
접근법
- 출고날짜 OUT_DATE가 2022년 05월 01일까지 일 경우 출고완료, 이후는 출고대기, 아직 정해지지 않았다면 출고미정을 출력해야 하므로, CASE문을 사용했다.
- 이때, OUT_DATE 칼럼의 경우 DATE_FORMAT을 통해 년-월-일 형태의 문자열로 반환 시켰고, 해당 칼럼을 기준으로 CASE문을 이용해 문자열 ‘2022-05-01’과 크기 비교 후 출고상태를 보여주는 출고여부 칼럼을 생성했다.
코드
SELECT ORDER_ID, PRODUCT_ID, DATE_FORMAT(OUT_DATE, '%Y-%m-%d') OUT_DATE,
CASE
WHEN OUT_DATE <= '2022-05-01' THEN '출고완료'
WHEN OUT_DATE > '2022-05-01' THEN '출고대기'
ELSE '출고미정'
END AS 출고여부
FROM FOOD_ORDER
ORDER BY 1;
SQL ) LV3. 있었는데요 없었습니다
접근법
- 보호시작일이 입양일보다 빠른 레코드를 조회해야 하므로, ANIMAL_INS테이블의 DATETIME보다 ANIMAL_OUTS테이블의 DATETIME이 더 작은 레코드만 조회했다.
- 두 테이블의 칼럼을 비교해야하므로 JOIN연산 수행
코드
SELECT I.ANIMAL_ID, I.NAME
FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.DATETIME > O.DATETIME
ORDER BY I.DATETIME;
SQL ) LV3. 없어진 기록 찾기
접근법
- ANIMAL_OUTS 테이블에 존재하지만 ANIMAL_INS 테이블에 존재하지 않은 동물들을 찾아야하므로, ANIMAL_OUTS 기준 LEFT JOIN후 ANIMAL_INS테이블의 AIMAL_ID가 NULL인 레코드를 조회했다.
코드
SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_OUTS O LEFT JOIN ANIMAL_INS I ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.ANIMAL_ID IS NULL
ORDER BY 1, 2;
SQL ) LV3. 대장균들의 자식의 수 구하기
접근법
- 서브쿼리로 PARENT_ID기준 NULL이 아닌 값에 한해 그룹화하여, PARENT_ID와 그룹별 레코드 수를 반환하는 인라인 뷰를 생성했다
- 원본 테이블 E1기준으로 인라인뷰 E2와 LEFT JOIN 한 뒤, E2의 COUNT값이 NULL인 경우는 0, 그렇지 않은 경우 COUNT값을 출력하는 CHILD_COUNT 칼럼을 추가했다.
코드
SELECT ID, IFNULL(COUNT, 0) CHILD_COUNT
FROM ECOLI_DATA E1 LEFT
JOIN (SELECT PARENT_ID, COUNT(*) COUNT FROM ECOLI_DATA WHERE PARENT_ID IS NOT NULL GROUP BY PARENT_ID) E2 ON E1.ID = E2.PARENT_ID
ORDER BY ID;
SQL ) LV3. 업그레이드 할 수 없는 아이템 구하기
관련 개념
- IN(’A’,’B’) : A OR B에 포함되는 데이터 추출
- NOT IN(’A’,’B’,’C’) : A and B and C에 포함되지 않는 데이터 추출
- 즉, AND연산을 하게 되므로, NULL이 포함되면 무조건 NULL만 반환하게 된다.
- 따라서, NOT IN 연산을 사용할 경우 NULL처리를 따로 해줘야 한다.
접근법
- 더이상 업그레이드 할 수 없는 아이템은 누군가의 부모 아이템이 될 수 없다는 의미이므로 PARENT_ITEM_ID에 속하지 않는 ITEM_ID들을 조회하면 된다.
- 따라서 WHERE 절에서 서브쿼리로 PARENT_ITEM_ID를 반환하고, NOT IN 연산을 통해 ITEM_ID가 서브쿼리 반환값에 속하지 않는 레코드만 조회하도록 했다.
- 여기서 주의할 점은 NULL처리를 해야한다는 것이다.
코드
SELECT ITEM_ID, ITEM_NAME, RARITY
FROM ITEM_INFO
GROUP BY ITEM_ID
HAVING ITEM_ID NOT IN (SELECT PARENT_ITEM_ID FROM ITEM_TREE WHERE PARENT_ITEM_ID IS NOT NULL)
ORDER BY 1 DESC;
SQL ) LV3. 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기
접근법
- WHERE절에서 서브쿼리로 가장 최대 조회수를 갖는 게시물의 ID를 반환했을 때, 해당 ID와 일치하는 레코드에 대해서만 조회되도록 했다
- 이때, 파일 경로를 생성해야하므로, CONCAT함수를 사용했다.
코드
SELECT CONCAT('/home/grep/src/',B.BOARD_ID,'/',FILE_ID,FILE_NAME,FILE_EXT) FILE_PATH
FROM USED_GOODS_BOARD B JOIN USED_GOODS_FILE F ON B.BOARD_ID = F.BOARD_ID
WHERE B.BOARD_ID = (SELECT BOARD_ID FROM USED_GOODS_BOARD ORDER BY VIEWS DESC LIMIT 1)
ORDER BY 1 DESC;
SQL ) LV3. 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기
접근법
- 2022년 10월 16일이 START_DATE~END_DATE범위에 속하는 CAR_ID를 반환하는 서브쿼리 생성 후,
- CASE문에서 서브쿼리의 반환값에 CAR_ID가 들어 있다면 대여중, 그렇지 않다면 대여가능을 출력하는 AVAILABILITY 칼럼을 생성
- CAR_ID마다 여러개의 대여 날짜가 주어지므로, CAR_ID로 그룹화 했을 때, 한 번이라도 특정 날짜에 대여 중인 경우를 추리는 게 까다로웠다.
코드
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 1 DESC;
SQL ) LV3. 카테고리 별 도서 판매량 집계하기
접근법
- 두 테이블을 조인 후,
- WHERE 절에서 판매일자 SALES_DATE를 SUBSTR함수로 1부터 7번째까지 문자열 추출 한 값이 ‘2022-01’과 같은 경우에만 제한을 두고,
- CATEGORY로 그룹화 후, SUM 함수를 사용해 각 그룹별 SALES칼럼의 총 합계를 구했다
코드
SELECT CATEGORY, SUM(SALES) TOTAL_SALES
FROM BOOK B JOIN BOOK_SALES S ON B.BOOK_ID = S.BOOK_ID
WHERE SUBSTR(SALES_DATE, 1, 7) = '2022-01'
GROUP BY B.CATEGORY
ORDER BY 1;
