✍🏻 programmers Lv3. MySQL 완료(24.12.20)

66) 오랜 기간 보호한 동물(1)

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

LEFT JOIN을 사용하는 이유는 INNER JOIN은 두 테이블 모두 일치하는 레코드만을 반환하기 때문에 ANIMAL_INS에만 있는 동물을 찾을 수 없다.

여기서 ANIMAL_OUTS는 포함되지 않아야된다.

LEFT JOIN은 ANIMAL_INS에 있는 모든 동물을 반환하고, ANIMAL_OUTS에 데이터가 없는 경우 NULL을 반환하게 된다.

67) 조건별로 분류하여 주문상태 출력하기

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 > '2022-05-01' THEN '출고대기'
        ELSE '출고미정'
    END '출고여부'
FROM FOOD_ORDER
ORDER BY ORDER_ID ASC;

날짜 형식은 DATE_FORMAT으로 바꿔서 출력

조건문 CASE WHEN ELSE END 기억하기

68) 있었는데요 없었습니다

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

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

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

ORDER BY DATEDIFF(B.DATETIME, A.DATETIME) DESC 는 B.DATETIME과 A.DATETIME의 일 수를 계산해서 날짜 차이가 큰 수부터 정렬

70) 대여 기록이 존재하는 자동차 리스트 구하기

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

중복이 없어야 되면 : DISTINCT

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

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

먼저 집계함수는 SELECT문에서 쓸 수 없다. 이 함수는 그룹화된 결과에 대해 적용해야 한다.

TOTAL_SALES >= 700000 해당 조건은 HAVING절에서만 사용이 가능하다.

WHERE 절은 그룹화 이전의 데이터에 조건을 적용하고, HAVING은 그룹화된 후에 조건을 적용하는 차이점이 있다.

72) 즐겨찾기가 가장 많은 식당 정보 출력하기

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;

여기서 처음에는

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

다음과 같이 풀었었는데 이게 성립되지 않는 이유는 SELECT문에서 단순히 MAX(FAVORITES)를 수행하면 음식 종류별 가장 많은 즐겨찾기 수만을 구하는 것이고 가장 많은 즐겨찾기 수의 식당 정보를 구해주는 것은 아니다.

따라서 서브 쿼리를 사용하여 음식 종류별 가장 큰 즐겨찾기 수를 구하고 GROUP BY를 통해 음식 종류별로 좋아요 수가 위 서브 쿼리에서 구한 가장 큰 값의 즐겨찾기 수와 일치하는 행의 정보를 출력한다.

73) 조건에 맞는 사용자 정보 조회하기

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 A
JOIN USED_GOODS_USER B ON A.WRITER_ID = B.USER_ID
GROUP BY A.WRITER_ID
HAVING COUNT(A.WRITER_ID) >= 3
ORDER BY B.USER_ID DESC;

CONCAT : 여러 문자열을 하나로 합칠 때 사용

• CONnect (연결) + cat = 고양이가 귀엽게 연결되어있는 거 생각 😸

SUBSTR(A, 1, 4) : A 문자열을 1번 자리부터 4글자 자름

74) 부서별 평균 연봉 조회하기

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

• 소수점 첫째 자리에서 반올림 : ROUND(AVG(SAL), 0) -> 첫째 자리면 1이 아니라 0임.

75) 대장균의 크기에 따라 분류하기 1

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

• CASE WHEN 조건 THEN ‘반환 값’ END

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

SELECT CAR_ID, 
    MAX(CASE
       WHEN '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;

• MAX 함수를 하는 이유는 CAR_ID가 같은 차의 경우 여러 번 대여한 정보를 나타내기 때문에 가장 최신 날짜 데이터를 조회해야 하기 때문이다.

77) 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

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

1. USED_GOODS_BOARD와 USED_GOODS_FILE 테이블이므로 JOIN으로 묶기

2. 조회수가 가장 높은 중고거래 게시글 구하기

3. 첨부파일 경로는 FILE ID를 기준으로 내림차순 정렬

4. 파일경로 CONCAT 이용해서 연결하기

서브쿼리(Subquery)는 하나의 SQL 쿼리 내부에서 하위 쿼리를 사용하는 것으로, 특정 조건이나 데이터를 계산하고 이를 기반으로 주 쿼리(Main Query)를 수행할 때 사용된다.

78) 헤비 유저가 소유한 장소

SELECT ID, NAME, HOST_ID
FROM PLACES 
WHERE HOST_ID IN (SELECT HOST_ID FROM PLACES GROUP BY HOST_ID HAVING COUNT(HOST_ID) >=2)
ORDER BY ID;

• 둘 이상인 헤비유저들의 리스트를 출력해야 함. 서브 쿼리 사용하여 코드 작성

SELECT *
FROM PLACES 
GROUP BY HOST_ID
HAVING COUNT(*) >= 2
ORDER BY ID

이와 같은 코드가 안 되는 이유는 HOST_ID가 2건 이상인 헤비 유저들의 전체 리스트가 출력되어야 한다.

이의 결과는 헤비 유저의 값의 HOST_ID의 행이 한개씩만 출력됨.

79) 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트 : 서브 쿼리 사용, 자동차 ID 별이므로 SELECT CAR_ID, 같은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블 이므로 FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY, 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지이므로 WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31', 대여횟수 5회 이상 -> HAVING COUNT(CAR_ID) >= 5

SELECT MONTH(START_DATE) AS 'MONTH', CAR_ID, COUNT(*) AS 'RECORDS'
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY  
WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
AND CAR_ID IN ( 
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY  
    WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
    GROUP BY CAR_ID
    HAVING COUNT(CAR_ID) >= 5
)
GROUP BY MONTH, CAR_ID
HAVING RECORDS > 0
ORDER BY MONTH, CAR_ID DESC;

80) 특정 조건을 만족하는 물고기별 수와 최대 길이 구하기

1) 평균 길이가 33cm 이상이 물고기들을 종류별로 분류 하므로 AVG(LENGTH) >= 33, 단 10cm 이하의 물고기들은 10cm로 취급 -> AVG(IFNULL(LENGTH,10)) >= 33

SELECT COUNT(FISH_TYPE) 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

81) 업그레이드 할 수 없는 아이템 구하기

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

# E,D,C는 다른 아이템의 부모 역할을 하지 않는다.
# ITEM ID = 0은 부모가 없으므로 가장 상위 레벨 아이템
# ITEM ID = 1, 2는 부모가 0 이므로 ITEM_A의 자식
# ITEM_ID = 3, 4는 부모가 1 이므로 ITEMP_B의 자식

즉, WHERE B.PARENT_ITEM_ID IS NULL 이 말은 ITEM_C, D, E는 다른 아이템의 부모 역할을 하지 않는 것을 의미한다.

82) 대장균들의 자식의 수 구하기

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

ON A.ID = B.PARENT_ID:

• 이 조건은 A.ID와 B.PARENT_ID가 일치하는 행을 찾아서 결합하는 것.

ID	PARENT_ID	SIZE_OF_COLONY	DIFFERENTIATION_DATE	GENOTYPE
1	NULL	10	2019/01/01	5
2	NULL	2	2019/01/01	3
3	1	100	2020/01/01	4
4	2	17	2020/01/01	4
5	2	10	2020/01/01	6
6	4	101	2021/01/01	22

원래 예시표이고 A.ID와 B.PARENT_ID를 결합하면

A.ID	B.ID (자식)	CHILD_COUNT
1	3	1
2	4, 5	2
3	NULL	0
4	6	1
5	NULL	0
6	NULL	0

다음과 같이 된다.

83) 물고기 종류 별 대어 찾기

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

서브쿼리는 FISH_INFO 테이블에서 FISH_TYPE이 A.FISH_TYPE과 동일한 물고기들 중에서 가장 큰 LENGTH 값을 찾음.

84) 대장균의 크기에 따라 분류하기 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 ID,
    PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) AS 'PER'
    FROM ECOLI_DATA
) AS A
ORDER BY ID;

• PERCENT_RANK() 함수는 각 행에 대해 주어진 컬럼(여기서는 SIZE_OF_COLONY)을 기준으로 백분위 랭크를 계산.

• ORDER BY SIZE_OF_COLONY DESC는 SIZE_OF_COLONY 값을 내림차순으로 정렬하여 큰 값일수록 높은 백분위 랭크를 부여한다.

• 예를 들어, SIZE_OF_COLONY 값이 가장 큰 행은 PER 값이 1에 가까운 값을 가지며, 가장 작은 값은 0에 가까운 값을 가진다.

• 서브쿼리는 각 ID와 그에 대응하는 백분위 랭크(PER) 값을 반환한다.