📌1. sql 코딩테스트

📌 1-1. 조건에 맞는 도서와 저자 리스트 출력하기

• DATE() 함수는 날짜를 잘라내지만, 내부적으로 데이터 타입은 DATE 로 유지함.
• MySQL 은 ORDER BY 시 원래 컬럼을 기준으로 정렬하려는 최적화 동작을 한다.
• 반면, DATE_FORMAT() 은 문자열로 바뀌어서 강제로 정렬이 적용된다.
• 그래서 DATE() 는 가끔 정렬에서 먹히지 않는 것처럼 보일 수 있다.
• 알고 싶은 것의 주어에 해당하는 걸 FROM에 넣고 조인하면 편하다. ("내가 쓴 책들" 을 보기 위해 -> 저자조인)
• 관점의 조인

SELECT B.BOOK_ID, A.AUTHOR_NAME, DATE_FORMAT(B.PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
    FROM AUTHOR A 
    JOIN BOOK B  ON A.AUTHOR_ID = B.AUTHOR_ID
    WHERE B.CATEGORY = '경제'
    ORDER BY PUBLISHED_DATE;

📌 1-2. 성분으로 구분한 아이스크림 총 주문량

SELECT I.INGREDIENT_TYPE, SUM(TOTAL_ORDER) AS TOTAL_ORDER
    FROM FIRST_HALF F
    JOIN ICECREAM_INFO I ON F.FLAVOR = I.FLAVOR
    GROUP BY I.INGREDIENT_TYPE
    ORDER BY TOTAL_ORDER;

📌 1-3. 상품 별 오프라인 매출 구하기

• 일대다 관계
• "상품별 총 판매 금액" 을 알고 싶다. -> 상품을 기준으로 판매 데이터를 모아서 집계
• 결론: 상품 테이블에서 출발해서 판매 내역을 붙이고 집계하는 흐름이다.

SELECT P.PRODUCT_CODE AS PRODUCT_CODE, SUM(O.SALES_AMOUNT * P.PRICE) AS SALES
    FROM PRODUCT P
    JOIN OFFLINE_SALE O ON P.PRODUCT_ID = O.PRODUCT_ID 
    GROUP BY P.PRODUCT_CODE
    ORDER BY SALES DESC, PRODUCT_CODE ASC;

📌 1-4. 3월에 태어난 여성 회원 목록 출력하기

SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') AS DATE_OF_BIRTH
    FROM MEMBER_PROFILE 
    WHERE MONTH(DATE_OF_BIRTH) = 3 
        AND GENDER = 'W'
        AND TLNO IS NOT NULL
    ORDER BY MEMBER_ID ASC;

📌 1-5. 루시와 엘라 찾기

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
    FROM ANIMAL_INS 
    WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty');

📌 1-6. 가격대 별 상품 개수 구하기

• 만원 단위로 그룹화 하기
• HAVING 은 필터링용 이지, 그룹핑용이 아니다.
• SELECT 별칭을 GROUP BY에서 못쓴다. -> 논리연산 순서 때문에
• FLOOR(): 내림함수
• 같은 계산이 여러번 등장하고 그것이 기준일때 WITH 구문을 사용한다.

WITH PRICE_DATA AS (
    SELECT
        CASE
            WHEN FLOOR(PRICE / 10000) * 10000 >= 10000 THEN FLOOR(PRICE / 10000) * 10000
            ELSE 10000
            END AS PRICE_GROUP
        FROM PRODUCT
)

SELECT PRICE_GROUP, COUNT(PRICE_GROUP) AS PRODUCTS
    FROM PRICE_DATA  -- 여기는 WITH절거를 가져다가 써야함.
    GROUP BY PRICE_GROUP
    ORDER BY PRICE_GROUP;

📌 1-7. 연도 별 평균 미세먼지 농도 조회하기

SELECT YEAR(YM) AS YEAR, 
        ROUND(AVG(PM_VAL1), 2) AS 'PM10', 
        ROUND(AVG(PM_VAL2), 2) AS 'PM2.5'
    FROM AIR_POLLUTION 
    WHERE LOCATION2 = '수원'
    GROUP BY YEAR(YM)
    ORDER BY YEAR

📌 1-8. 물고기 종류 별 잡은 수 구하기

• N.FISH_NAME 은 하나의 "열(column)" 을 가리키지만, GROUP BY 로 묶인 하나의 그룹 안에서는 N.FISH_NAME 이 여러 값(row) 을 가질 수 있다.
• 그런데 SQL 은 그룹마다 어떤 FISH_NAME 을 출력해야 할지 알 수 없으므로 오류가 발생한다.
• 해결하려면: 집계 함수 (MAX(), MIN(), GROUP_CONCAT() 등) 를 써서 하나로 모아주거나, GROUP BY 에 N.FISH_NAME 을 포함해서 완전히 일대일 대응 으로 만들어야 한다.
• GROUP BY가 늘어나면 소분류가 늘어나는 것을 의미한다.

SELECT COUNT(F.ID) AS FISH_COUNT, N.FISH_NAME AS FISH_NAME
    FROM FISH_NAME_INFO N
    JOIN FISH_INFO F ON F.FISH_TYPE = N.FISH_TYPE
    GROUP BY N.FISH_TYPE, N.FISH_NAME
    ORDER BY FISH_COUNT DESC;

📌 1-9. ROOT 아이템 구하기

SELECT I.ITEM_ID AS ITEM_ID, I.ITEM_NAME AS ITEM_NAME
    FROM ITEM_INFO I
    JOIN ITEM_TREE T ON I.ITEM_ID = T.ITEM_ID
    WHERE T.PARENT_ITEM_ID IS NULL
    ORDER BY ITEM_ID;

📌 1-10. 월별 잡은 물고기 수 구하기

• GROUP BY는 이미 존재하는 월만 그룹화한다. 굳이 걸러주지 않아도 된다.
• DATE_FORMAT(TIME, '%c') 이러면 문자열이 되므로 정렬할때 문자열 정렬로 되어 1다음에 12가 온다.
• • 0 → 숫자 연산이니까, SQL 이 문자열을 숫자로 자동 변환하려 한다. (암묵적 변환)
• CAST나 CONVERT 쓰는게 표준이긴하다.
• MONTH(TIME)은 숫자형으로 반환하여 0이 붙지 않는다.

SELECT COUNT(ID) AS FISH_COUNT, MONTH(TIME) AS MONTH
    FROM FISH_INFO 
    GROUP BY MONTH(TIME)
    ORDER BY MONTH;

📌 1-11. 조건에 부합하는 중고거래 상태 조회하기

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

📌 1-12. 조건에 맞는 사원 정보 조회하기

• 기본적으로 관계가 가장 뚜렷한 테이블부터 JOIN

SELECT SUM(G.SCORE) AS SCORE, E.EMP_NO, E.EMP_NAME, E.POSITION, E.EMAIL
    FROM HR_EMPLOYEES E
    JOIN HR_GRADE G ON E.EMP_NO = G.EMP_NO
    GROUP BY E.EMP_NO
    ORDER BY SCORE DESC
    LIMIT 1;

• 만약 만점자가 여러명이라면? -> 서브쿼리 사용
• SQL 에서는 FROM 절에 오는 서브쿼리 는 반드시 별칭(alias)을 붙여줘야 한다. // 일시적으로 만들어진 테이블 취급을 하기 때문이다.

SELECT SUM(G.SCORE) AS SCORE, E.EMP_NO, E.EMP_NAME, E.POSITION, E.EMAIL
    FROM HR_EMPLOYEES E
    JOIN HR_GRADE G ON E.EMP_NO = G.EMP_NO
    GROUP BY E.EMP_NO
    ORDER BY SCORE DESC
    HAVING SCORE = (
        SELECT MAX(TOTAL_SCORE) 
        FROM (
            SELECT SUM(G.SCORE) AS total_score
            FROM HR_EMPLOYEES E
            JOIN HR_GRADE G ON E.EMP_NO = G.EMP_NO
            GROUP BY E.EMP_NO
        ) AS SUBQUERY
    )
    ORDER BY E.EMP_NO;

📌 1-13. 특정 물고기를 잡은 총 수 구하기

SELECT COUNT(F.ID) AS FISH_COUNT
    FROM FISH_NAME_INFO N
    JOIN FISH_INFO F ON N.FISH_TYPE = F.FISH_TYPE
    WHERE N.FISH_NAME IN ('BASS', 'SNAPPER');

📌 1-14. 대장균의 크기에 따라 분류하기

• 틀릴 이유가 너무 없는데 틀린다면 그건 오타 때문이다.

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

📌 1-15. 재구매가 일어난 상품과 회원 리스트 구하기

• GROUP BY 의 본질: → 중복 제거
• SELECT 절에 집계 함수가 없으면, 그냥 각 그룹당 하나의 행만 남는다.
• 개수는 세지 않고, "묶인다"는 것만 일어나는 것!

SELECT USER_ID, PRODUCT_ID
    FROM ONLINE_SALE 
    GROUP BY USER_ID, PRODUCT_ID
    HAVING COUNT(PRODUCT_ID) >= 2
    ORDER BY USER_ID ASC, PRODUCT_ID DESC;

📌 1-16. 자동차 평균 대여 기간 구하기

• SQL은 선언형 언어라 내가 명시적으로 "순회해!" 라고 쓰지 않아도, 집계 함수(AVG, SUM, COUNT 등)를 사용하는 순간 SQL 엔진은 자동으로 그룹 내의 각 행을 반복해서 연산한다. -> C언어를 통해 내부에서 절차적 코딩을 수행한다.
• GROUP BY 의 인수가 2개일 때, 첫 번째 인수의 "중복 제거 효과" 는 풀리기 때문에 아래에서 CAR_1D를 중복제거해서 내보내려면 CAR_ID만 GROUP_BY에 남겨두어야한다.

SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE) + 1),1) AS AVERAGE_DURATION
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
    GROUP BY CAR_ID
    HAVING  ROUND(AVG(DATEDIFF(END_DATE, START_DATE) + 1)) >= 7  
    ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC;

📌 1-17. 노선별 평균 역 사이 거리 조회하기

• ORDER BY에서 TOTAL_DISTANCE를 사용하면 CONCAT때문에 제대로 처리되지 않을 수 있다.
• 처리가 들어간 별칭일 경우 한번더 확인해야한다.

SELECT ROUTE, 
        CONCAT(ROUND(SUM(D_BETWEEN_DIST), 1), 'km') AS TOTAL_DISTANCE,
        CONCAT(ROUND(AVG(D_BETWEEN_DIST), 2), 'km') AS AVERAGE_DISTANCE
    FROM SUBWAY_DISTANCE 
    GROUP BY ROUTE
    ORDER BY SUM(D_BETWEEN_DIST) DESC;

📌 1-18. 분기별 분화된 대장균의 개체 수 구하기

• 특정 규칙으로 그룹 묶기
• GROUP BY에서는 AS로 정의를 못함. 특정 규칙으로 그룹을 묶으려면 그 규칙을 FROM에서 서브쿼리로 정의하고 원본 SELECT에서 필요한 부분들을 같이 첨부한다.

SELECT QUARTER, COUNT(ID) AS ECOLI_COUNT
    FROM (SELECT ID, 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'
                WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 10 AND 12 THEN '4Q'
                END AS QUARTER
            FROM ECOLI_DATA) AS SUBQUERY
    GROUP BY QUARTER
    ORDER BY LEFT(QUARTER,1) ASC;

📌 1-19. 업그레이드 된 아이템 구하기

• 가장 작은 조건을 만들고 그것을 서브쿼리로 넣으면서 코드 확장하기

SELECT T.ITEM_ID, I.ITEM_NAME, I.RARITY  
    FROM ITEM_INFO I
    JOIN ITEM_TREE T ON I.ITEM_ID = T.ITEM_ID
    WHERE PARENT_ITEM_ID IN (
        SELECT T.ITEM_ID
        FROM ITEM_INFO I
        JOIN ITEM_TREE T ON I.ITEM_ID = T.ITEM_ID
        WHERE I.RARITY ='RARE'
        )
    ORDER BY T.ITEM_ID DESC;

• 업그레이드의 업그레이드까지 하려면
• 재귀구문을 사용해야한다.
• WITH RECURSIVE = 지정한 Anchor 식 + Recursive 식을 데이터가 더 이상 추가되지 않을 때까지 자동으로 반복 실행하는 SQL 문법이다.

WITH RECURSIVE item_hierarchy AS (
    -- 1. 시작: RARE 아이템들
    SELECT I.ITEM_ID, I.ITEM_NAME, I.RARITY
    FROM ITEM_INFO I
    WHERE I.RARITY = 'RARE'

    UNION ALL -- 위 아래의 결과물을 한 테이블처럼 행단위로 합쳐짐. (UNION과 다르게 중복제거를 하지 않음.)

    -- 2. 재귀: 이전 단계에서 찾은 아이템들의 업그레이드 아이템
    SELECT I.ITEM_ID, I.ITEM_NAME, I.RARITY
    FROM ITEM_INFO I
    JOIN ITEM_TREE T ON I.ITEM_ID = T.ITEM_ID
    JOIN item_hierarchy IH ON T.PARENT_ITEM_ID = IH.ITEM_ID -- 이전단계가 현재단계의 부모여야한다. 이 조건이 맞다면 두 값은 같으므로 테이블을 합칠 수 있다.
    -- 위에서 ON이 조건문의 역할을 한것이다.
)

SELECT DISTINCT ITEM_ID, ITEM_NAME, RARITY -- 위에서 중복을 제거하지 않았으므로 DISTINCT를 이용해서 중복을 제거한다.
FROM item_hierarchy
WHERE RARITY IS NOT NULL
AND ITEM_ID NOT IN (SELECT ITEM_ID FROM ITEM_INFO WHERE RARITY = 'RARE')
ORDER BY ITEM_ID DESC;

📌 2. SQL 수업

• 필드 다 보여주는 명령어
• CALL SP_TABLE_INFO_STUDY();
• 프로그래머스 문제들
• CALL SP_TABLE_INFO_PROGRAMMERS();

📌 2-1. 데이터 검색하기

• WHERE는 기본적으로 만족하는 '행'만 조회할때 사용한다.
• AND 가 OR보다 우선하여 AND와 OR이 같이 쓰인 경우 괄호를 사용하여 순서를 잘 정리해야한다.
• NOT BETWEEN, NOT LIKE, NOT IN 모두 가능하다.

SELECT ID, NAME, CREDIT
    -> FROM SUBJECTS
    -> WHERE CREDIT NOT IN (2,3);

📌 2-2. SQL함수 (1) - 문자열 함수

• SQL에는 여러 행의 값을 하나로 요약하는 집계함수와 하나의 행에 대해 하나의 결과값을 반환하는 단일 행 함수가 있다.

함수	설명	예시	결과
UPPER(str)	문자열을 모두 대문자로 변환	UPPER('hello')	'HELLO'
LOWER(str)	문자열을 모두 소문자로 변환	LOWER('HELLO')	'hello'
CHAR_LENGTH(str)	문자열의 문자 수 반환	CHAR_LENGTH('가나다')	3
CONCAT(str1, str2, ...)	문자열을 이어붙임	CONCAT('Hello', '', 'World')	'HelloWorld'
SUBSTRING(str, pos, len)	문자열에서 일부를 추출 (1부터 시작)	SUBSTRING('abcdef', 2, 3)	'bcd'
LEFT(str, n)	왼쪽에서 n글자 추출	LEFT('abcdef', 3)	'abc'
RIGHT(str, n)	오른쪽에서 n글자 추출	RIGHT('abcdef', 2)	'ef'
TRIM(str)	문자열 양쪽의 공백 제거	TRIM(' hello ')	'hello'
REPLACE(str, from, to)	문자열 일부를 다른 문자로 변경	REPLACE('banana', 'na', 'ny')	'bannya'
INSTR(str, substr)	문자열에서 substr이 처음 등장하는 위치 반환 (1부터 시작)	INSTR('apple', 'l')	4
LPAD(str, len, pad_str)	왼쪽을 pad_str로 채워 총 길이를 len으로 맞춤	LPAD('world', 10, '*')	'*****world'
RPAD(str, len, pad_str)	오른쪽을 pad_str로 채워 총 길이를 len으로 맞춤	RPAD('world', 10, '*')	'world*****'
FORMAT(number, decimal_plc)	천 단위 콤마와 소수점 자리수 지정	FORMAT(12345, 2)	'12,345.00'

2-2-1. 학과명을 소문자로 변환하고 10글자 이상인 경우만 출력하기

SELECT LOWER(NAME) AS DNAME
    -> FROM SUBJECTS
    -> WHERE CHAR_LENGTH(NAME) >= 10;

2-2-2. 과목명의 앞 3글자와 학점을 결합하여 출력하기

SELECT CONCAT('과목코드: ', LEFT(NAME, 3), '-', CREDIT)
    -> FROM SUBJECTS

2-2-3. 전화번호 중 가운데 4자리 추출하여 출력하기

SELECT SUBSTRING(PHONE, 5, 4)
    -> FROM STUDENTS;

2-2-4. 학과명을 오른쪽으로 15자 출력, 전화번호는 왼쪽에 - 붙여서 10자 고정폭으로 값을 합쳐 출력

SELECT CONCAT(RPAD(DEPARTMENT_ID, 15, ' '), LPAD(PHONE , 10, '-'))
    FROM STUDENTS;

2-2-5. @ 앞 아이디를 대문자로 만들고 급여를 원단위로 환산하여 특정형태로 출력하기

• INSTR: 특정 문자가 있는 위치 반환

SELECT CONCAT(UPPER(SUBSTRING(EMAIL, 1, INSTR(EMAIL, '@')-1)), ': ', FORMAT(SAL*10000, 2)) AS EMAILSAL
    FROM PROFESSORS;

2-3. SQL함수 (2) ‒ 숫자 함수 / 조건, NULL 처리 함수

2-3-1. 숫자처리 함수

함수	설명	예시	결과
ABS(x)	절댓값 반환	ABS(-5)	5
POWER(x, y)	x의 y제곱 계산	POWER(2, 3)	8
ROUND(x, d)	소수점 d자리까지 반올림	ROUND(3.1415, 2)	3.14
CEIL(x) / CEILING(x)	올림값 반환 (소수점 위로)	CEIL(3.2)	4
FLOOR(x)	내림값 반환 (소수점 아래로)	FLOOR(3.8)	3
TRUNCATE(x, d)	소수점 d자리까지 자름	TRUNCATE(3.1415, 2)	3.14
MOD(x, y)	x를 y로 나눈 나머지	MOD(10, 3)	1
SIGN(x)	x가 양수면 1, 0이면 0, 음수면 -1 반환	SIGN(10)	1
RAND()	0 이상 1 미만의 난수 반환	RAND()	예: 0.5728

2-3-2. 조건문

함수	설명	예시	결과
IF(expr, t, f)	expr이 참이면 t, 거짓이면 f 반환	IF(grade=4, '졸업반', '재학생')	'졸업반'
IFNULL(expr1, expr2)	expr1이 NULL이면 expr2 반환, 아니면 expr1 반환	IFNULL(email, '없음')	'없음' (email이 NULL인 경우)
NULLIF(expr1, expr2)	두 값이 같으면 NULL 반환, 다르면 expr1 반환	NULLIF(grade, 1)	NULL (grade가 1인 경우)
CASE WHEN ... THEN ... ELSE ... END	복수 조건을 처리하는 일반적인 조건 분기 구문	CASE WHEN gender = '남' THEN 'M' ELSE 'F' END	'M' (gender가 '남'인 경우)

2-3-3. 건강지수와 건강등급 만들기

• 서브쿼리 활용
• 원본 SELECT에서 STUDENTS의 열을 쓰고 싶으면 서브쿼리에서 한번 언급해주고 넘어와야 쓸 수 있음.
• 기본적으로 서브쿼리에 있는 값밖에 사용할 수 없음.

SELECT HI AS '건강지수',
    CASE
        WHEN HI >= 35 THEN '우수'
        WHEN HI >= 30 THEN '양호'
        ELSE '주의'
        END AS '건강등급'
FROM (SELECT TRUNCATE(POWER(HEIGHT,2)/WEIGHT,2) AS HI
        FROM STUDENTS 
        ) AS SUBQUERY; 

2-3-4. 몸무게가 기준과 가장 가까운 사람 5명 뽑기

SELECT NAME, ABS(WEIGHT - 60) AS '차이', FLOOR(WEIGHT/10)*10 AS '몸무게구간'
    FROM STUDENTS
    ORDER BY 차이
    LIMIT 5;

2-3-5. 학생들을 장신과 단신으로 구분하기

SELECT NAME, CASE
    WHEN WEIGHT >= 180 THEN '장신'
    WHEN WEIGHT >= 160 THEN '보통'
    ELSE '단신'
    END AS '키등급',
    IF(MOD(ID,2) = 0, '짝수','홀수') AS '번호유형'
FROM STUDENTS;

2-3-6. 성별정보 출력하기

SELECT NAME, CASE
        WHEN GENDER = '남' THEN 'MALE'
        WHEN GENDER = '여' THEN 'FEMALE'
        WHEN GENDER IS NULL THEN '미입력'
        ELSE '기타'
        END AS '성별정보'
FROM STUDENTS;

2-3-7. 학생들의 키 분류하기

• NULL로 그냥 지정할 수 있다.

SELECT NAME, IFNULL(IF(HEIGHT >= 170, NULL, '작음'), '경고') AS '키상태'
    FROM STUDENTS;