📌 1. SQL 코딩 테스트

---

📌 1-1. 연도별 대장균 크기의 편차 구하기

• 연도별 최대 사이즈를 구해서 다시 그것들을 모두 활용해 편차를 구해야함.
• JOIN = 열 추가
• FROM절에서 위에서 정의했던 서브쿼리의 별칭을 사용할 수 없다.
• 문제 구성에 맞는 새로운 테이블들을 정의하고 합쳐서 나만의 테이블을 완성하고 거기서 결과를 도출하는 사고를 해야한다.

SELECT I.YEAR, (J.MAXS-I.SIZE_OF_COLONY) AS YEAR_DEV, I.ID
    FROM (SELECT ID, SIZE_OF_COLONY, YEAR(DIFFERENTIATION_DATE) AS YEAR
            FROM ECOLI_DATA) I
    JOIN (SELECT MAX(SIZE_OF_COLONY) AS MAXS, YEAR(DIFFERENTIATION_DATE) AS YEAR -- MAX 열을 만들어서 원본 테이블과 합쳐버린다.
            FROM ECOLI_DATA
            GROUP BY YEAR(DIFFERENTIATION_DATE)) J 
        ON I.YEAR = J.YEAR
    ORDER BY I.YEAR ASC, YEAR_DEV ASC;

• WITH을 사용해서 미리 테이블 두개(CTE)를 만들어두고 JOIN으로 합쳐서 사용해도 된다.
• WITH은 쉼표로 서브쿼리를 여러개 정의해둘 수 있다.

WITH I AS ( -- 원본 테이블에서 필요한 값만 추출한 새로운 테이블 
    SELECT ID, SIZE_OF_COLONY, YEAR(DIFFERENTIATION_DATE) AS YEAR
        FROM ECOLI_DATA
),
J AS (  -- 새로운 MAX열을 추가하여 I와 잇기 위한 새로운 테이블 J
    SELECT MAX(SIZE_OF_COLONY) AS MAXS, YEAR(DIFFERENTIATION_DATE) AS YEAR 
            FROM ECOLI_DATA
            GROUP BY YEAR(DIFFERENTIATION_DATE)
)

SELECT I.YEAR, MAXS - I.SIZE_OF_COLONY AS YEAR_DEV, I.ID
    FROM I -- 원본테이블을 메인으로 삼음.
    JOIN J ON I.YEAR = J.YEAR
    ORDER BY I.YEAR ASC, YEAR_DEV ASC;

---

📌 1-2. 조건에 맞는 개발자 찾기

SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
    FROM DEVELOPERS 
    WHERE (SKILL_CODE & 1024 = 1024) OR (SKILL_CODE & 256 = 256)
    ORDER BY ID ASC;

• 내가 작성한 답안은 위와 같으나 문제에서 요구한 답안은 아래와 같다.

SELECT D.ID, D.EMAIL, D.FIRST_NAME, D.LAST_NAME
    FROM DEVELOPERS D
    JOIN SKILLCODES S ON (D.SKILL_CODE & S.CODE) = S.CODE -- 가지고 있는 모든 스킬코드별로 분해되어서 같은 ID가 여러개 있는 테이블이 완성된다.  
    WHERE S.NAME IN ('Python', 'C#') -- 파이썬이랑 C# 외의 중복된 코드는 삭제
    GROUP BY D.ID, D.EMAIL, D.FIRST_NAME, D.LAST_NAME -- 파이썬과 C#을 둘 다 가지고 있는 ID를 제거하기 위해 GROUP BY로 중복제거 -> 근데 여기서는 DISTINCT로 충분했음.
    ORDER BY D.ID;

---

📌 1-3. 부모의 형질을 모두 가지는 대장균 찾기

• JOIN할땐 FROM에 있는 원래 테이블과 JOIN한 테이블 모두 별칭을 쓰고 모든 변수에 별칭을 써주는게 마음에 편하다.

WITH P AS (
            SELECT ID AS PID, GENOTYPE AS PGT
                FROM ECOLI_DATA )
                
SELECT E.ID, E.GENOTYPE, P.PGT AS PARENT_GENOTYPE
    FROM ECOLI_DATA E
    JOIN P ON E.PARENT_ID = P.PID
    WHERE E.GENOTYPE & P.PGT = P.PGT
    ORDER BY E.ID;

---

📌 1-4. 오랜 기간 보호한 동물(1)

• NOT IN으로 테이블의 항목들과 비교할때는 서브쿼리형태로 적어줘야한다.
• SELECT로 출력해줘야 바로 비교할 수 있기 때문이다. 그래야 값들의 집합이 된다.
• SELECT는 컬럼을 집합으로 만든다.

WITH O AS (
            SELECT ANIMAL_ID
                FROM ANIMAL_OUTS)
                
SELECT NAME, DATETIME
    FROM ANIMAL_INS I
    WHERE I.ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM O)
    ORDER BY I.DATETIME ASC
    LIMIT 3;

---

📌 1-5. 조건별로 분류하여 주문상태 출력하기

• 일반적인 DATE_FORMAT문제

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 '출고대기'
            WHEN OUT_DATE IS NULL THEN '출고미정'
        END AS '출고여부'
    FROM FOOD_ORDER 
    ORDER BY ORDER_ID ASC;

---

📌 1-6. 카테고리 별 도서 판매량 집계하기

WITH T AS( 
    SELECT BOOK_ID, SUM(SALES) AS TOTAL
        FROM BOOK_SALES 
        WHERE DATE_FORMAT(SALES_DATE,'%Y-%m-%d') BETWEEN '2022-01-01' AND '2022-01-31'
        GROUP BY BOOK_ID
)

SELECT B.CATEGORY, SUM(T.TOTAL) AS TOTAL_SALES
    FROM BOOK B
    JOIN T ON B.BOOK_ID = T.BOOK_ID
    GROUP BY B.CATEGORY
    ORDER BY CATEGORY ASC;

---

📌 1-7. 대여 기록이 존재하는 자동차 리스트 구하기

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

---

📌 1-8. 있었는데요 없었습니다

• 테이블이 합쳐지는 과정을 머릿속으로 그리면서 푸니까 잘풀린다.

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 ASC;

---

📌 1-9. 오랜 기간 보호한 동물(2)

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

---

📌 1-10. 조건에 맞는 사용자와 총 거래금액 조회하기

• 너무 잘풀어버렸다.

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

---

📌 1-11. 즐겨찾기가 가장 많은 식당 정보 출력하기

• 세트로 비교 가능하다.
• SQL 다중 컬럼 서브쿼리 문법: IN 문법에서 쌍으로도 비교할 수 있다.

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;

• 윈도우 함수: 전체 데이터의 특정 그룹이나 범위에 대해 행마다 연산을 수행하는 함수
• OVER 더 윈도우라고 생각 어떤 기준으로 데이터(창문)을 볼 건지 정한다는 뜻
• ROW는 행을 의미한다. -> ROW_NUMBER는 행에 숫자를 붙이는 것을 의미한다.

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
    FROM (
        SELECT * ,
            ROW_NUMBER() OVER (PARTITION BY FOOD_TYPE ORDER BY FAVORITES DESC) AS RN
        FROM REST_INFO
    ) AS SUBQUERY
    WHERE RN = 1
    ORDER BY FOOD_TYPE DESC;

---

📌 1-12. 부서별 평균 연봉 조회하기

• 평균 연봉 테이블 따로 구해서 원래 테이블 옆에 딱 붙여버리기

WITH A AS( SELECT DEPT_ID, ROUND(AVG(SAL),0) AS AVG_SAL
    FROM HR_EMPLOYEES
    GROUP BY DEPT_ID
)

SELECT D.DEPT_ID, D.DEPT_NAME_EN, A.AVG_SAL
    FROM HR_DEPARTMENT D
    JOIN A ON D.DEPT_ID = A.DEPT_ID
    ORDER BY A.AVG_SAL DESC;

---

📌 1-13. 조건에 맞는 사용자 정보 조회하기

• 프로그래머스 문제 풀때 주소 나열할때는 띄어쓰기도 공백을 추가해서 일일히 해줘야 한다.

SELECT USER_ID, NICKNAME, 
        CONCAT(CITY, ' ', STREET_ADDRESS1, ' ', STREET_ADDRESS2) AS '전체주소',
        CONCAT(LEFT(TLNO, 3),  '-', SUBSTRING(TLNO, 4, 4), '-', RIGHT(TLNO,4)) AS '전화번호'
    FROM USED_GOODS_USER 
    WHERE USER_ID IN (SELECT WRITER_ID
                        FROM USED_GOODS_BOARD 
                        GROUP BY WRITER_ID
                        HAVING COUNT(BOARD_ID) >= 3)
    ORDER BY USER_ID DESC;

• 조인을 통해 데이터를 축소하는 방식(원래 중복이 없는 데이에서 특정 테이터만 남기기)이 나중에 확장하기 쉬워서 나을 수 있다.

SELECT U.USER_ID, U.NICKNAME,
       CONCAT(U.CITY, ' ', U.STREET_ADDRESS1, ' ', U.STREET_ADDRESS2) AS '전체주소',
       CONCAT(LEFT(U.TLNO, 3), '-', SUBSTRING(U.TLNO, 4, 4), '-', RIGHT(U.TLNO, 4)) AS '전화번호'
FROM USED_GOODS_USER U
JOIN (
    SELECT WRITER_ID
    FROM USED_GOODS_BOARD
    GROUP BY WRITER_ID
    HAVING COUNT(BOARD_ID) >= 3
) B ON U.USER_ID = B.WRITER_ID -- 해당하는 것만 조인시키면서 자동으로 범위 축소 -> 확장성이 더 좋음.
ORDER BY U.USER_ID DESC;

---

📌 1-14. 없어진 기록 찾기

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

---

📌 1-15. 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

• 중복된 데이터에서 대표적인 하나의 데이터를 기준(GROUP BY + 집계함수)으로 출력문을 나누기(CASE WHEN)
• 원래 중복이 있는 데이터에서 추리고 조인하면 중복된 얘들한테 조인이 다 붙어버려서 조인이 안된다.
• MAX 안에 CASE WHEN을 넣으면 모든 행을 돌아가면서 CASE WHEN을 시행한다.
• 띄어쓰기까지 완전 똑같이 해야 같은 값이 나온다.

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

---

📌 1-16. 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

• 최댓값 하나를 찾아서 그 값과 같은 행의 정보 뽑기
• GROUP BY 없이 집계함수 쓰지 말자 -> 만약 써야하면 다른 방법이 있을 것이다.
• 기본에 충실하자. 원래 가장 ~가 높은 값을 찾는 것처럼 찾고 WHERE로 비교해야한다.

SELECT CONCAT('/home/grep/src','/',BOARD_ID,'/',FILE_ID,FILE_NAME,FILE_EXT) AS FILE_PATH
    FROM USED_GOODS_FILE 
    WHERE BOARD_ID = (SELECT BOARD_ID 
                        FROM USED_GOODS_BOARD 
                        ORDER BY VIEWS DESC
                        LIMIT 1)
    ORDER BY FILE_ID DESC;

---

📌 1-17. 헤비 유저가 소유한 장소

• 등록된 개수가 2개 이상인 유저가 점유한 공간 정보만 출력하기
• 조인으로 하는 방법도 가능한게 아까는 조인했을때 조인 하는것만 출력이 되긴하지만 중복이 있어서 문제였다면 지금은 중복되도 상관없기 때문에 조인을 써도 된다.

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 P.ID, P.NAME, P.HOST_ID
FROM PLACES P
JOIN (
    SELECT HOST_ID
    FROM PLACES
    GROUP BY HOST_ID
    HAVING COUNT(*) >= 2
) H ON P.HOST_ID = H.HOST_ID
ORDER BY P.ID;

---

📌 1-18. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

• MySQL 은 GROUP BY 에 없는 컬럼을 SELECT 하려면 집계 함수가 필요하다. -> 그룹지어지지 않으면 어떻게 처리할지 곤란하기 때문
• 귀찮아도 연조건이랑 CAR_ID 조건을 나열하기

SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE YEAR(START_DATE) = 2022 AND MONTH(START_DATE) BETWEEN 8 AND 10 -- 귀찮아도 연조건이랑 CAR_ID 조건을 나열하기
  AND CAR_ID IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE YEAR(START_DATE) = 2022 AND MONTH(START_DATE) BETWEEN 8 AND 10
    GROUP BY CAR_ID
    HAVING COUNT(*) >= 5
)
GROUP BY MONTH(START_DATE), CAR_ID
ORDER BY MONTH ASC, CAR_ID DESC;

---

📌 1-19. 특정 조건을 만족하는 물고기별 수와 최대 길이 구하기

WITH FI AS( SELECT FISH_TYPE, CASE
                    WHEN LENGTH IS NULL THEN 10
                    ELSE LENGTH
                    END AS LEN
                FROM FISH_INFO 
)

SELECT COUNT(FISH_TYPE) AS FISH_COUNT, MAX(LEN) AS MAX_LENGTH, FISH_TYPE
    FROM FI
    GROUP BY FISH_TYPE
    HAVING AVG(LEN) >= 33
    ORDER BY FISH_TYPE ASC;

---

📌 2. SQL 함수 (3) - 수업 문제 풀이

📌 2-1. 교수의 입사일로부터의 근속연수 계산하기

• 연끼리는 빼기가 가능하다.

SELECT NAME, YEAR(CURDATE())- YEAR(hiredate) AS '근속 연수'
    FROM professors

---

📌 2-2. departments 테이블에서 설립 연도가 30년 이상 지난 학과명과 설립 연도를 조회

SELECT dname, established AS '설립연도'
    FROM departments
    WHERE YEAR(CURDATE())- established >= 30;

---

📌 2-3. students 테이블에서 생년월일을 'MM월 DD일' 형식으로 변환하여 출력

SELECT name, DATE_FORMAT(birthdate, '%m월-%d일') 
    FROM students

---

📌 2-4. enrollments 테이블에서 수강신청일로부터 30일 후 날짜를 다음 시험일로 간주하고 출력

SELECT subject_id, DATE_ADD(enroll_date, INTERVAL 30 DAY) AS '다음 시험일'
    FROM enrollments

---

📌 2-5. students 테이블에서 생년월일 기준으로 나이가 가장 많은 학생의 이름과 나이(년)를 출력

SELECT NAME, YEAR(CURDATE())-YEAR(birthdate) + 1 AS '나이'
    FROM students
    ORDER BY birthdate ASC
    LIMIT 1;

---