Computer Science Knowledge Storage

제가 공부한 내용을 정리하는 블로그입니다.
아직 많이 부족하고 배울게 너무나도 많습니다. 틀린내용이 있으면 언제나 가감없이 말씀해주시면 감사하겠습니다😁
Programmers SQL 고득점 Kit(IS NULL)입니다.

경기도에 위치한 식품창고 목록 출력하기

SELECT
    WAREHOUSE_ID,
    WAREHOUSE_NAME,
    ADDRESS,
    NVL(FREEZER_YN, 'N') AS FREEZER_YN
FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE '경기%'
ORDER BY WAREHOUSE_ID ASC

• 경기도에 위치한 공장을 통해 like를 사용합니다.
  WHERE ADDRESS LIKE '경기%'
• 냉동 여부가 null 것을 n으로 처리하기 위해 NVL을 사용합니다.
  NVL(FREEZER_YN, 'N') AS FREEZER_YN
• 창고 ID를 기준으로 오름차순 정렬합니다.

이름이 없는 동물의 아이디

SELECT
    ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NULL
ORDER BY ANIMAL_ID ASC

• 이름이 없는 동물의 아이디를 찾기 위해 IS NULL을 사용합니다.
  WHERE NAME IS NULL
• ANIMAL_ID를 기준으로 오름차순 정렬합니다.

이름이 있는 동물의 아이디

SELECT
    ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
ORDER BY ANIMAL_ID;

• 이름이 있는 동물의 아이디를 찾기 위해 IS NOT NULL을 사용합니다.
  WHERE NAME IS NOT NULL
• ANIMAL_ID를 기준으로 오름차순 정렬합니다.

NULL 처리하기

SELECT
    ANIMAL_TYPE,
    NVL(NAME, 'No name'),
    SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

• NAME이 null인 레코드를 No name으로 처리하기 위해 NVL을 사용합니다.
  NVL(NAME, 'No name')
• ANIMAL_ID를 기준으로 오름차순 정렬합니다.

나이 정보가 없는 회원 수 구하기

SELECT
    COUNT(*) AS USERS
FROM USER_INFO
WHERE AGE IS NULL

• 나이 정보가 없는 회원을 구하기 위해 IS NULL을 사용합니다.
  WHERE AGE IS NULL
• 회원 수를 구하기 위해 COUNT 함수를 이용합니다.

ROOT 아이템 구하기

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

• ROOT ITEM을 구하기 위해 ITEM_TREE와 ITEM_INFO 테이블을 ITEM_ID를 기준으로 정렬합니다.
  FROM ITEM_TREE T JOIN ITEM_INFO I ON T.ITEM_ID = I.ITEM_ID
• PARENT_ITEM_ID가 null인 것이 root 아이템이므로 해당 레코드를 조회합니다.
  WHERE PARENT_ITEM_ID IS NULL
• ITEM_ID를 기준으로 오름차순 정렬합니다.

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

SELECT
    I.ITEM_ID,
    I.ITEM_NAME,
    I.RARITY
FROM ITEM_INFO I LEFT OUTER JOIN ITEM_TREE T
ON I.ITEM_ID = T.PARENT_ITEM_ID
WHERE T.PARENT_ITEM_ID IS NULL
ORDER BY I.ITEM_ID DESC

• 업그레이드 할 수 없는 아이템을 구하기 위해 ITEM_INFO ITEM_ID와 ITEM_TREE PARENT_ITEM_ID를 LEFT OUTER JOIN 후 PARENT_ITEM_ID가 null인 레코드를 조회합니다.
  FROM ITEM_INFO I LEFT OUTER JOIN ITEM_TREE T ON I.ITEM_ID = T.PARENT_ITEM_ID
  WHERE T.PARENT_ITEM_ID IS NULL
• ITEM_ID를 기준으로 내림차순 정렬합니다.

잡은 물고기의 평균 길이 구하기

SELECT
    ROUND(AVG(IFNULL(LENGTH, 10)), 2) AVERAGE_LENGTH
FROM FISH_INFO

• LENGTH가 null이면 10으로 처리하기 위해 IFNULL을 사용하여 평균값을 소수점 셋째자리에서 반올림합니다.
  SELECT ROUND(AVG(IFNULL(LENGTH, 10)), 2) AVERAGE_LENGTH

제가 공부한 내용을 정리하는 블로그입니다.
아직 많이 부족하고 배울게 너무나도 많습니다. 틀린내용이 있으면 언제나 가감없이 말씀해주시면 감사하겠습니다😁
Programmers SQL 고득점 Kit(GROUP BY)입니다.

노선별 평균 역 사이 거리 조회하기

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;

• 노선 별로 구하기 위해 ROUTE를 그룹화합니다.
  GROUP BY ROUTE
• 총 누계거리와 평균 역사이를 구하기 위해 SUM과 AVG 함수를 사용하고 문자열을 합성하기 위해 CONCAT 함수를 사용합니다.
  CONCAT(ROUND(SUM(D_BETWEEN_DIST), 1), 'km') AS TOTAL_DISTANCE,
  CONCAT(ROUND(AVG(D_BETWEEN_DIST), 2), 'km') AS AVERAGE_DISTANCE
• 총 누계거리를 기준으로 내림차순 정렬합니다.

물고기 종류 별 잡은 수 구하기

SELECT COUNT(*) AS FISH_COUNT, FISH_NAME
FROM FISH_INFO A
JOIN FISH_NAME_INFO B
  ON A.FISH_TYPE = B.FISH_TYPE
GROUP BY FISH_NAME
ORDER BY 1 DESC

• 잡은 물고기와 이름을 매핑하기 위해 JOIN 합니다.
  JOIN FISH_NAME_INFO B ON A.FISH_TYPE = B.FISH_TYPE
• 물고기 종류를 위해 그룹화합니다.
  GROUP BY FISH_NAME
• 물고기 잡은 수를 기준으로 내림차순 정렬합니다.

월별 잡은 물고기 수 구하기

SELECT
    COUNT(*) AS FISH_COUNT,
    EXTRACT(MONTH FROM TIME) AS MONTH
FROM FISH_INFO
GROUP BY EXTRACT(MONTH FROM TIME)
ORDER BY EXTRACT(MONTH FROM TIME) ASC

• 월별로 잡은 물고기를 구하기 위해 그룹화합니다.
  GROUP BY EXTRACT(MONTH FROM TIME)
• 월을 기준으로 오름차순 정렬합니다.

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

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

• 길이가 10 이하면 null이므로 null을 10으로 지정하기 위해 IFNULL 함수를 사용한 서브 테이블을 F로 지정합니다.
  (
      SELECT
          ID,
          FISH_TYPE,
          IFNULL(LENGTH, 10) AS LENGTH
      FROM FISH_INFO
  ) F
• 평균 길이가 33인 물고기 종류 별로 그룹화합니다.
  GROUP BY FISH_TYPE
  HAVING AVG(LENGTH) >= 33
• 최대 길이와 물고기 잡은 수를 위해 COUNT 함수와 MAX 함수를 사용합니다.
• 물고기 종류를 기준으로 오름차순 정렬합니다.

제가 공부한 내용을 정리하는 블로그입니다.
아직 많이 부족하고 배울게 너무나도 많습니다. 틀린내용이 있으면 언제나 가감없이 말씀해주시면 감사하겠습니다😁
Programmers SQL 고득점 Kit(GROUP BY)입니다.

고양이와 개는 몇마리 있을까

SELECT
    ANIMAL_TYPE,
    COUNT(*) AS COUNT
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE

• 고양이와 개를 그룹화합니다.
  GROUP BY ANIMAL_TYPE
• 개수를 구하기 위해 COUNT 함수를 사용하고 고양이가 먼저 나오기 위해 오름차순 정렬합니다.

동명 동물 수 찾기

SELECT
    NAME,
    COUNT(*) AS COUNT
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING COUNT(*) > 1
ORDER BY NAME

• 이름이 있으며, 이름을 기준으로 그룹화합니다.
  WHERE NAME IS NOT NULL
  GROUP BY NAME
• 동일한 이름의 동물을 찾기 위해 HAVING 절을 통해 COUNT 함수를 사용합니다.
  HAVING COUNT(*) > 1
• 동물 이름을 기준으로 오름차순 정렬합니다.

년, 월, 성별 별 상품 구매 회원 수 구하기

SELECT
    TO_CHAR(O.SALES_DATE, 'YYYY') AS YEAR, 
    EXTRACT(MONTH FROM O.SALES_DATE) AS MONTH, 
    U.GENDER AS GENDER,
    COUNT(DISTINCT U.USER_ID) AS USERS
FROM USER_INFO U JOIN ONLINE_SALE O
ON U.USER_ID = O.USER_ID
WHERE U.GENDER IS NOT NULL
GROUP BY TO_CHAR(O.SALES_DATE, 'YYYY'), EXTRACT(MONTH FROM O.SALES_DATE), U.GENDER
ORDER BY TO_CHAR(O.SALES_DATE, 'YYYY'), EXTRACT(MONTH FROM O.SALES_DATE), U.GENDER

• 상품 구매한 회원을 구하기 위해 JOIN합니다.
  FROM USER_INFO U JOIN ONLINE_SALE O ON U.USER_ID = O.USER_ID
• 성별 정보가 없는 데이터는 제외하기 위해 조건을 걸어줍니다.
  WHERE U.GENDER IS NOT NULL
• 연도, 월, 성별을 기준으로 그룹화 및 정렬합니다.
  GROUP BY TO_CHAR(O.SALES_DATE, 'YYYY'), EXTRACT(MONTH FROM O.SALES_DATE), U.GENDER
  ORDER BY TO_CHAR(O.SALES_DATE, 'YYYY'), EXTRACT(MONTH FROM O.SALES_DATE), U.GENDER

입양 시각 구하기(1)

SELECT 
    TO_NUMBER(TO_CHAR(DATETIME, 'HH24') AS HOUR,
    COUNT(*) AS COUNT
FROM ANIMAL_OUTS
WHERE TO_CHAR(DATETIME, 'HH24') BETWEEN 09 AND 19
GROUP BY TO_CHAR(DATETIME, 'HH24')
ORDER BY HOUR ASC

• 09시와 19시 사이의 데이터를 조회하기 위해 TO_CHAR와 BETWEEN 함수를 사용합니다.
  WHERE TO_CHAR(DATETIME, 'HH24') BETWEEN 09 AND 19
• 입양 시각을 기준으로 그룹화합니다.
  GROUP BY TO_CHAR(DATETIME, 'HH24')
• 정렬하기 위해 TO_NUMBER 함수를 사용하고 HOUR를 기준으로 오름차순 정렬합니다.

입양 시각 구하기(2)

SELECT 
    L.HOUR AS HOUR, 
    NVL(COUNT, 0) AS COUNT
FROM (
    SELECT TO_CHAR(DATETIME, 'HH24') AS HOUR, COUNT(*) AS COUNT 
    FROM ANIMAL_OUTS 
    GROUP BY TO_CHAR(DATETIME, 'HH24') 
    ORDER BY HOUR
) O, (
    SELECT LEVEL -1 AS HOUR 
    FROM DUAL 
    CONNECT BY LEVEL <= 24
) L      
WHERE L.HOUR = O.HOUR(+)
ORDER BY L.HOUR;

• 입양 시각을 통해 그룹화를 진행합니다.
  (
      SELECT TO_CHAR(DATETIME, 'HH24') AS HOUR, COUNT(*) AS COUNT 
      FROM ANIMAL_OUTS 
      GROUP BY TO_CHAR(DATETIME, 'HH24') 
      ORDER BY HOUR
  ) O
• 가상의 테이블와 계층 쿼리를 통해 LEVEL을 0부터 23까지 숫자를 반환합니다.
  ( SELECT LEVEL -1 AS HOUR FROM DUAL CONNECT BY LEVEL <= 24 ) L
• O 테이블과 L 테이블을 LEFT OUTER JOIN합니다. 레코드가 없는 행을 0으로 표현하기 위함입니다.
  WHERE L.HOUR = O.HOUR(+)
• NULL인 데이터를 0으로 표현하기 위해 NVL함수를 사용하고 HOUR를 기준으로 오름차순 정렬합니다.
  NVL(COUNT, 0) AS COUNT

가격대 별 상품 개수 구하기

SELECT
    FLOOR(PRICE / 10000) * 10000 AS PRICE_GROUP,
    COUNT(*)
FROM PRODUCT
GROUP BY FLOOR(PRICE / 10000) * 10000
ORDER BY PRICE_GROUP

• 만원 단위의 가격대로 표현하기 위해 버림과 10000 곱하기를 통해 그룹화합니다.
  GROUP BY FLOOR(PRICE / 10000) * 10000
• 가격대를 기준으로 오름차순 정렬합니다.

언어별 개발자 분류하기

WITH J AS (
    SELECT 
        ID,
        EMAIL,
        CASE -- 조건에 맞게 SCORE를 기록
           WHEN COUNT(CASE WHEN CATEGORY = 'Front End' THEN 1 END) > 0 AND 
                COUNT(CASE WHEN NAME = 'Python' THEN 1 END) > 0 
                THEN 3
           WHEN COUNT(CASE WHEN NAME = 'C#' THEN 1 END) > 0 THEN 2
           WHEN COUNT(CASE WHEN CATEGORY = 'Front End' THEN 1 END) > 0 THEN 1
        END AS SCORE
    FROM
        DEVELOPERS AS D
    JOIN -- 비트 논리 연산을 조건으로 JOIN
        SKILLCODES AS S ON D.SKILL_CODE & S.CODE
    GROUP BY 
        ID,EMAIL
    HAVING 
        SCORE > 0
)
SELECT 
    CASE 
        WHEN SCORE = 3 THEN 'A'
        WHEN SCORE = 2 THEN 'B'
        WHEN SCORE = 1 THEN 'C'
    END AS GRADE,
    ID,
    EMAIL
FROM 
    J
ORDER BY 
    1,2

• DEVELOPERS 테이블의 SKILL_CODE와 SKILLCODES 테이블의 CODE 값을 비트 논리 AND 연산(&)을 통해 조건에 맞는 조인을 수행
  FROM DEVELOPERS AS D JOIN SKILLCODES AS S ON D.SKILL_CODE & S.CODE
• ID와 EMAIL을 통해 그룹화 합니다.
  GROUP BY ID, EMAIL
• SCORE는 0보다 큰 것들만 조회합니다.
  HAVING SCORE > 0
• 개발자의 점수를 결정하기 위해 CASE 문을 사용합니다.
• WITH 절을 이용합니다.
• CASE 절을 통해 A, B, C를 결정합니다.
  CASE 
          WHEN SCORE = 3 THEN 'A'
          WHEN SCORE = 2 THEN 'B'
          WHEN SCORE = 1 THEN 'C'
      END AS GRADE,
• GRADE와 ID를 통해 오름차순 정렬합니다.

조건에 맞는 사원정보 조회하기

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

• 사원 정보와 연도를 기준으로 GROUP화하여 SCORE가 가장 높은 사원을 조회하는 서브 테이블을 G로 설정합니다.
  (
      SELECT
          EMP_NO,
          SUM(SCORE) AS SCORE
      FROM HR_GRADE
      GROUP BY EMP_NO, YEAR
      ORDER BY SCORE DESC
      LIMIT 1
  ) G
• HR_EMPLOYEES 와 회원 넘버를 통해 JOIN합니다.
  FROM (...) G JOIN HR_EMPLOYEES E ON G.EMP_NO = E.EMP_NO

연간 평가 점수에 해당하는 평가 등급 및 성과금 조회하기

SELECT
    G.EMP_NO,
    E.EMP_NAME,
    CASE
        WHEN G.SCORE / 2 >= 96 THEN 'S'
        WHEN G.SCORE / 2 >= 90 THEN 'A'
        WHEN G.SCORE / 2 >= 80 THEN 'B' 
        ELSE 'C'
    END AS GRADE,
    E.SAL * CASE
        WHEN G.SCORE / 2 >= 96 THEN 0.2
        WHEN G.SCORE / 2 >= 90 THEN 0.15
        WHEN G.SCORE / 2 >= 80 THEN 0.1
        ELSE 0
    END AS BONUS
FROM (
    SELECT
        EMP_NO,
        SUM(SCORE) AS SCORE
    FROM HR_GRADE
    GROUP BY EMP_NO, YEAR
    ORDER BY SCORE DESC
) G
JOIN HR_EMPLOYEES E ON G.EMP_NO = E.EMP_NO
ORDER BY G.EMP_NO;

• 사원 정보와 연도를 기준으로 GROUP화하여 합산 SCORE 사원을 조회하는 서브 테이블을 G로 설정합니다.
  (
      SELECT
          EMP_NO,
          SUM(SCORE) AS SCORE
      FROM HR_GRADE
      GROUP BY EMP_NO, YEAR
      ORDER BY SCORE DESC
  ) G
• HR_EMPLOYEES 와 회원 넘버를 통해 JOIN합니다.
  FROM (...) G JOIN HR_EMPLOYEES E ON G.EMP_NO = E.EMP_NO
• CASE 절을 통해 성과금과 평가등급을 설정합니다.
  CASE
          WHEN G.SCORE / 2 >= 96 THEN 'S'
          WHEN G.SCORE / 2 >= 90 THEN 'A'
          WHEN G.SCORE / 2 >= 80 THEN 'B' 
          ELSE 'C'
      END AS GRADE,
      E.SAL * CASE
          WHEN G.SCORE / 2 >= 96 THEN 0.2
          WHEN G.SCORE / 2 >= 90 THEN 0.15
          WHEN G.SCORE / 2 >= 80 THEN 0.1
          ELSE 0
      END AS BONUS

부서별 평균 연봉 조회하기

SELECT
    E.DEPT_ID,
    D.DEPT_NAME_EN,
    E.AVG_SAL
FROM HR_DEPARTMENT D JOIN (
    SELECT
        DEPT_ID,
        ROUND(AVG(SAL)) AS AVG_SAL
    FROM HR_EMPLOYEES
    GROUP BY DEPT_ID
) E ON D.DEPT_ID = E.DEPT_ID
ORDER BY E.AVG_SAL DESC

• 부서 아이디를 통해 그룹화하여 평균 연봉을 서브테이블 E로 구합니다.
  (
      SELECT
          DEPT_ID,
          ROUND(AVG(SAL)) AS AVG_SAL
      FROM HR_EMPLOYEES
      GROUP BY DEPT_ID
  ) E
• HR_DEPARTMENT 테이블과 E 테이블을 부서 아이디를 통해 JOIN합니다.
  FROM HR_DEPARTMENT D JOIN (...) E ON D.DEPT_ID = E.DEPT_ID
• 평균 연봉을 통해 내림차순 정렬합니다.

제가 공부한 내용을 정리하는 블로그입니다.
아직 많이 부족하고 배울게 너무나도 많습니다. 틀린내용이 있으면 언제나 가감없이 말씀해주시면 감사하겠습니다😁
Programmers SQL 고득점 Kit(GROUP BY)입니다.

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

SELECT 
    B.CATEGORY,
    SUM(S.SALES)
FROM BOOK B JOIN (
    SELECT *
    FROM BOOK_SALES
    WHERE TO_CHAR(SALES_DATE, 'YYYY-MM') = '2022-01'
) S
ON B.BOOK_ID = S.BOOK_ID
GROUP BY B.CATEGORY
ORDER BY B.CATEGORY

• 2022년 1월에 판매된 레코드를 조회합니다. (테이블 S)

(
    SELECT *
    FROM BOOK_SALES
    WHERE TO_CHAR(SALES_DATE, 'YYYY-MM') = '2022-01'
) S

• BOOK 테이블과 BOOK_ID를 기준으로 JOIN합니다.
  (FROM BOOK B JOIN (...) S ON B.BOOK_ID = S.BOOK_ID)
• 카테고리를 기준으로 GROUP BY 해줍니다.
  (GROUP BY B.CATEGORY)
• 판매량의 합계를 위해 SUM 함수를 사용하고 CATEGORY를 오름차순으로 정렬합니다.

저자 별 카테고리 별 매출액 집계하기

SELECT 
    A.AUTHOR_ID, 
    A.AUTHOR_NAME, 
    O.CATEGORY, 
    O.TOTAL_SALES
FROM AUTHOR A JOIN (
    SELECT 
        B.AUTHOR_ID, 
        B.CATEGORY, 
        SUM(B.PRICE * BS.SALES) AS TOTAL_SALES
    FROM BOOK B JOIN (
        SELECT BOOK_ID, SUM(SALES) AS SALES
        FROM BOOK_SALES
        WHERE TO_CHAR(SALES_DATE, 'YYYY-MM') = '2022-01'
        GROUP BY BOOK_ID
    )BS ON B.BOOK_ID = BS.BOOK_ID
    GROUP BY AUTHOR_ID, B.CATEGORY
) O ON A.AUTHOR_ID = O.AUTHOR_ID
ORDER BY AUTHOR_ID, CATEGORY DESC

• 2022년 1월에 판매된 책의 BOOK_ID와 판매량의 합계를 조회합니다.(테이블 BS)

(
    SELECT BOOK_ID, SUM(SALES) AS SALES
    FROM BOOK_SALES
    WHERE TO_CHAR(SALES_DATE, 'YYYY-MM') = '2022-01'
    GROUP BY BOOK_ID
)BS

• BS 테이블과 BOOK 테이블과 BOOK_ID를 기준으로 조인합니다.
  (FROM BOOK B JOIN (...) BS ON B.BOOK_ID = BS.BOOK_ID)
• 저자별, 카테고리별 그룹화를 해줍니다.
  (GROUP BY AUTHOR_ID, B.CATEGORY)
• 판매량과 가격을 기준으로 TOTAL_SLAES를 구합니다.
  (SUM(B.PRICE * BS.SALES) AS TOTAL_SALES)
• 해당 결과를 구한 테이블 O와 AUTHOR 테이블을 AUTHOR_ID를 기준으로 조인합니다.
  (FROM AUTHOR A JOIN (...) O ON A.AUTHOR_ID = O.AUTHOR_ID)
• AUTHOR_ID를 오름차순으로, 카테고리를 내림차순으로 정렬합니다.

식품분류별 가장 비싼 식품의 정보 조회하기

SELECT
    F1.CATEGORY,
    F2.MAX_PRICE,
    F1.PRODUCT_NAME
FROM FOOD_PRODUCT F1 JOIN (
    SELECT
        CATEGORY,
        MAX(PRICE) AS MAX_PRICE
    FROM FOOD_PRODUCT F2
    GROUP BY CATEGORY
    HAVING CATEGORY IN ('과자', '국', '김치', '식용유')
)F2 ON F1.PRICE = F2.MAX_PRICE AND F1.CATEGORY IN ('과자', '국', '김치', '식용유')
ORDER BY F2.MAX_PRICE DESC

• 식품 분류를 통해 그룹화를 합니다.
  (GROUP BY CATEGORY)
• 식품 분류에는 '과자', '국', '김치', '식용유' 만 가능하기에 그룹화에서 HAVING절을 이용합니다.
  (HAVING CATEGORY IN ('과자', '국', '김치', '식용유'))
• 그룹화한 테이블에서 각 식품 분류 별 가장 가격이 높은 필드를 선택하고 F2라는 테이블의 별칭을 설정합니다.

(
    SELECT
        CATEGORY,
        MAX(PRICE) AS MAX_PRICE
    FROM FOOD_PRODUCT F2
    GROUP BY CATEGORY
    HAVING CATEGORY IN ('과자', '국', '김치', '식용유')
)F2

• FOOD_PRODUCT와 F2 테이블에서 가장 비싼 가격과 일치하고 식품 분류를 기준으로 JOIN합니다.
  (FROM FOOD_PRODUCT F1 JOIN (...) F2 ON F1.PRICE = F2.MAX_PRICE AND
  F1.CATEGORY IN ('과자', '국', '김치', '식용유'))
• 식품 가격을 기준으로 내림차순 정렬합니다.

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

SELECT
    B.FOOD_TYPE,
    A.REST_ID,
    A.REST_NAME,
    B.FAVORITES
FROM REST_INFO A JOIN (
    SELECT
        FOOD_TYPE,
        MAX(FAVORITES) AS FAVORITES
    FROM REST_INFO
    GROUP BY FOOD_TYPE
)B ON A.FAVORITES = B.FAVORITES AND A.FOOD_TYPE = B.FOOD_TYPE
ORDER BY FOOD_TYPE DESC

• 음식 종류를 기준으로 그룹화를 진행한 후 즐겨찾기가 가장 많은 음식 종류를 구한 테이블을 B라고 지정합니다.

(
    SELECT
        FOOD_TYPE,
        MAX(FAVORITES) AS FAVORITES
    FROM REST_INFO
    GROUP BY FOOD_TYPE
)B

• REST_INFO 테이블과 B 테이블의 즐겨찾기 수와 음식 종류를 기준으로 JOIN합니다.
  (FROM REST_INFO A JOIN (...)B ON A.FAVORITES = B.FAVORITES AND A.FOOD_TYPE = B.FOOD_TYPE)
• 음식 종류를 기준으로 내림차순 정렬합니다.

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

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

• CAR_ID를 기준으로 그룹화합니다.
  (GROUP BY CAR_ID)
• 2022년 10월 16일을 기준으로 대여중인 차는 대여중, 대여중이지 않은 차는 대여 가능으로 표시하기 위해 CASE 절을 사용합니다.
  시작일자가 대여 가능보다 대여중이 우선적으로 선택하기 위해 MAX 함수를 사용합니다.
  (MAX(CASE WHEN '2022-10-16' BETWEEN TO_CHAR(START_DATE,'YYYY-MM-DD') AND TO_CHAR(END_DATE,'YYYY-MM-DD') THEN '대여중' ELSE '대여 가능' END) AS AVAILABILITY)
• CAR_ID를 기준으로 내림차순 정렬합니다.

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

SELECT
    EXTRACT(MONTH FROM S.START_DATE) AS MONTH,  -- 월을 숫자로 바로 추출
    S.CAR_ID,
    COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY S
JOIN (
    SELECT 
        CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE TO_CHAR(START_DATE, 'YYYY-MM') BETWEEN '2022-08' AND '2022-10'
    GROUP BY CAR_ID
    HAVING COUNT(*) >= 5
) R ON S.CAR_ID = R.CAR_ID
WHERE TO_CHAR(S.START_DATE, 'YYYY-MM') BETWEEN '2022-08' AND '2022-10'
GROUP BY EXTRACT(MONTH FROM S.START_DATE), S.CAR_ID
ORDER BY EXTRACT(MONTH FROM S.START_DATE) ASC, S.CAR_ID DESC;

• 2022년 8월부터 10월까지 대여횟수가 5회 이상인 자동차를 구하기 위해 CAR_ID를 기준으로 그룹화 한 후 HAVING 절을 통해 조건을 걸어 서브 테이블 R을 생성합니다.

(
    SELECT 
        CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE TO_CHAR(START_DATE, 'YYYY-MM') BETWEEN '2022-08' AND '2022-10'
    GROUP BY CAR_ID
    HAVING COUNT(*) >= 5
) R

• CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 CAR_ID를 기준으로 R과 JOIN합니다.
  (FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY S JOIN (...) R ON S.CAR_ID = R.CAR_ID)
• JOIN 조건에는 맞지만 START_DATE의 조건을 위해 한번 더 걸어줍니다.
  (WHERE TO_CHAR(S.START_DATE, 'YYYY-MM') BETWEEN '2022-08' AND '2022-10')
• 대여 월과 CAR_ID를 기준으로 그룹화합니다.
  월을 구하는 방법에는 EXTRACT(MONTH FROM S.START_DATE)를 이용합니다. => 숫자만 추출하기 위함입니다.
  (GROUP BY EXTRACT(MONTH FROM S.START_DATE), S.CAR_ID)
• 월을 기준으로 오름차순 정렬하고 자동차 ID를 기준으로 내림차순 정렬합니다.

자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기

SELECT
    CAR_TYPE,
    COUNT(*) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%통풍시트%' 
   OR OPTIONS LIKE '%열선시트%' 
   OR OPTIONS LIKE '%가죽시트%'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE ASC;

• OPTION이 '통풍시트', '열선시트', '가죽시트'를 구하기 위해 like절을 이용합니다. (OPTIONS가 VARCHAR로 이루어져있기 때문에 like 절을 통해 조회합니다)
  (WHERE OPTIONS LIKE '%통풍시트%' 
     OR OPTIONS LIKE '%열선시트%' 
     OR OPTIONS LIKE '%가죽시트%')
• 자동차 종류 별로 묶기 위해 그룹화합니다.
  (GROUP BY CAR_TYPE)
• 자동차 종류를 기준으로 오름차순 정렬합니다.

성분으로 구분한 아이스크림 총 주문량

SELECT
    I.INGREDIENT_TYPE,
    SUM(F.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 ASC;

• 상반기 주문정보와 아이스크림 정보를 FLAVOR를 통해 JOIN합니다.
  (FROM FIRST_HALF F JOIN ICECREAM_INFO I ON F.FLAVOR = I.FLAVOR)
• 아이스크림 맛을 기준으로 그룹화합니다.
  (GROUP BY I.INGREDIENT_TYPE)
• 상반기 아이스크림 총 주문량의 합을 위해 SUM 함수를 사용하고 총 주문량를 기준으로 오름차순 정렬합니다.

진료과별 총 예약 횟수 출력하기

SELECT
    MCDP_CD AS "진료과 코드", 
    COUNT(*) AS "5월예약건수"
FROM APPOINTMENT
WHERE TO_CHAR(APNT_YMD, 'YYYY-MM') = '2022-05'
GROUP BY MCDP_CD
ORDER BY "5월예약건수", "진료과 코드"

• 2022년 05월에 예약한 환자를 위해 테이블에 조건을 걸어줍니다.
  (WHERE TO_CHAR(APNT_YMD, 'YYYY-MM') = '2022-05')
• 진료과 별로 그룹화하기 위해 MCDP_CD를 기준으로 그룹화합니다.
  (GROUP BY MCDP_CD)
• 예약 수를 구하기 위해 COUNT 함수를 사용하고 예약 수와 진료과 코드를 기준으로 오름차순 정렬합니다.

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

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

• 완료된 중고거래에서 회원별 중고 물품을 판매한 총 금액이 70만원이 넘는 레코드를 조회하기 위해 서브 테이블 R을 생성합니다.

(
    SELECT
        WRITER_ID,
        SUM(PRICE) AS TOTAL_SALES
    FROM USED_GOODS_BOARD B
    WHERE B.STATUS = 'DONE'
    GROUP BY WRITER_ID
    HAVING SUM(PRICE) >= 700000
)R

• USED_GOODS_BOARD B와 R에 회원 아이디를 기준으로 JOIN합니다.
  (FROM USED_GOODS_USER U JOIN (...)R ON U.USER_ID = R.WRITER_ID)
• 총 판매액을 기준으로 오름차순 정렬합니다.

제가 공부한 내용을 정리하는 블로그입니다.
아직 많이 부족하고 배울게 너무나도 많습니다. 틀린내용이 있으면 언제나 가감없이 말씀해주시면 감사하겠습니다😁
Programmers SQL 고득점 Kit(SUM, MAX, MIN)입니다.

가장 비싼 상품 구하기

SELECT
    MAX(PRICE) AS MAX_PRICE
FROM PRODUCT

• MAX 함수를 사용하여 PRODUCT 테이블의 PRICE 중 가장 값이 비싼 가격을 조회합니다.

가격이 제일 비싼 식품의 정보 출력하기

-- 첫번째 방법
SELECT
    A.PRODUCT_ID,
    A.PRODUCT_NAME,
    A.PRODUCT_CD,
    A.CATEGORY,
    A.PRICE
FROM FOOD_PRODUCT A, (
    SELECT MAX(PRICE) AS MAX_PRICE
    FROM FOOD_PRODUCT
) B
WHERE A.PRICE = B.MAX_PRICE;

-- 두번째 방법
SELECT *
FROM FOOD_PRODUCT
ORDER BY PRICE DESC
FETCH NEXT 1 ROWS ONLY

• 첫번째 방법
  • 가장 비싼 값을 구하여 natural join 합니다.
    (FROM FOOD_PRODUCT A, (
        SELECT MAX(PRICE) AS MAX_PRICE
        FROM FOOD_PRODUCT) B)
  • 이후 WHERE 절을 통해 비교하여 가장 비싼 가격을 가진 상품의 정보를 출력합니다.
    (WHERE A.PRICE = B.MAX_PRICE;)
    
    
• 두번째 방법
  • PRICE를 기준으로 내림차순 정렬합니다.
    (ORDER BY PRICE DESC)
  • FETCH NEXT N ROWS 절을 이용하여 첫번째 레코드를 조회합니다.
    (FETCH NEXT 1 ROWS ONLY)

최대값 구하기

SELECT
    MAX(DATETIME) AS "시간"
FROM ANIMAL_INS;

• DATETIME도 MAX를 사용할 수 있습니다.
  가장 최근에 들어온 동물이 가장 큰 값이므로 MAX함수를 사용합니다.
  (MAX(DATETIME) AS "시간")

최솟값 구하기

SELECT
    MIN(DATETIME) AS "시간"
FROM ANIMAL_INS;

• DATETIME도 MIN를 사용할 수 있습니다.
  가장 먼저 들어온 동물이 가장 작은 값이므로 MIN함수를 사용합니다.
  (MIN(DATETIME) AS "시간")

동물 수 구하기

SELECT
    COUNT(*)
FROM ANIMAL_INS

• 동물 보호소에 들어온 동물의 수를 조회하기 위해 COUNT함수를 사용합니다.
  (SELECT COUNT(*))

중복 제거하기

SELECT
    COUNT(DISTINCT NAME)
FROM ANIMAL_INS

• 동물 보호소에 들어온 동물의 이름을 조회하기 위해 COUNT함수를 사용합니다.
  또 중복된 이름은 하나로 쳐야하므로 DISTINCT 키워드를 사용합니다.
  (SELECT COUNT(DISTINCT NAME))
• DISTINCT는 NULL을 세지 않습니다.

조건에 맞는 아이템들의 가격의 총합 구하기

SELECT
    SUM(PRICE) AS TOTAL_PRICE
FROM ITEM_INFO
WHERE RARITY = 'LEGEND'

• 희귀도가 LEGEND인 ITEM을 조회합니다.
  (WHERE RARITY = 'LEGEND')
• 해당 아이템의 가격의 총합을 구합니다.
  (SELECT SUM(PRICE) AS TOTAL_PRICE)

물고기 종류 별 대어찾기

SELECT 
    F1.ID,
    FNI.FISH_NAME,
    F1.LENGTH
FROM 
    FISH_INFO F1
JOIN 
    FISH_NAME_INFO FNI ON F1.FISH_TYPE = FNI.FISH_TYPE
JOIN 
    (SELECT FISH_TYPE, MAX(LENGTH) AS MAX_LENGTH
     FROM FISH_INFO
     WHERE LENGTH IS NOT NULL
     GROUP BY FISH_TYPE) AS F2
     ON F1.FISH_TYPE = F2.FISH_TYPE AND F1.LENGTH = F2.MAX_LENGTH
ORDER BY 
    F1.ID ASC;

• FISH_INFO와 FISH_NAME_INFO를 FISH_TYPE을 기준으로 조인합니다.
  (FROM FISH_INFO F1 JOIN FISH_NAME_INFO FNI ON F1.FISH_TYPE = FNI.FISH_TYPE)
• 물고기 종류 별로 묶어야 하기에 GROUP BY를 FISH_TYPE으로 지정한 후, FISH_TYPE에 따라 가장 큰 LENGTH를 조회합니다.
  이후 FISH_TYPE과 LENGTH를 통해 조인합니다.

JOIN (
    SELECT FISH_TYPE, MAX(LENGTH) AS MAX_LENGTH
     FROM FISH_INFO
     WHERE LENGTH IS NOT NULL
     GROUP BY FISH_TYPE
    ) AS F2 ON F1.FISH_TYPE = F2.FISH_TYPE AND F1.LENGTH = F2.MAX_LENGTH

• 물고기의 ID를 통해 오름차순 정렬합니다.

잡은 물고기 중 가장 큰 물고기의 길이 구하기

SELECT
    CONCAT(MAX(LENGTH), "cm") AS MAX_LENGTH
FROM FISH_INFO;

• 가장 큰 물고기의 길이를 구하기 위해 MAX 함수를 사용하고 문자열의 더하기를 위한 CONCAT 함수를 사용합니다.
  (SELECT CONCAT(MAX(LENGTH), "cm") AS MAX_LENGTH)

연도별 대장균 크기의 편차 구하기

SELECT
    Year(E.DIFFERENTIATION_DATE) AS YEAR,
    ABS(E.SIZE_OF_COLONY - R.MAX_SIZE) AS YEAR_DEV,
    E.ID
FROM
    ECOLI_DATA E
JOIN (
    SELECT
        Year(DIFFERENTIATION_DATE) AS YEAR,
        MAX(SIZE_OF_COLONY) AS MAX_SIZE
    FROM ECOLI_DATA
    GROUP BY YEAR
) R ON R.YEAR = Year(E.DIFFERENTIATION_DATE)
ORDER BY
    R.YEAR ASC,
    ABS(E.SIZE_OF_COLONY - R.MAX_SIZE) ASC;

• 연도를 구하기 위해 YEAR 함수를 사용하고 연도에 따른 가장 큰 대장균 크기를 구하기 위한 테이블 R을 구합니다.

(
    SELECT
        Year(DIFFERENTIATION_DATE) AS YEAR,
        MAX(SIZE_OF_COLONY) AS MAX_SIZE
    FROM ECOLI_DATA
    GROUP BY YEAR
) R

• 해당 년도의 가장 큰 대장균과의 편차를 구하기 위해 YEAR을 통해 JOIN을 합니다.
  (FROM ECOLI_DATA E JOIN (...) ON R.YEAR = Year(E.DIFFERENTIATION_DATE))
• 편차의 절댓값을 위해 ABS 함수를 사용합니다.
  (ABS(E.SIZE_OF_COLONY - R.MAX_SIZE) AS YEAR_DEV)
• 연도에 대해 오름차순 정렬하고 크기 편차에 대해 오름차순으로 정렬합니다.

제가 공부한 내용을 정리하는 블로그입니다.
아직 많이 부족하고 배울게 너무나도 많습니다. 틀린내용이 있으면 언제나 가감없이 말씀해주시면 감사하겠습니다😁
Programmers SQL 고득점 Kit입니다.

업그레이드 된 아이템 구하기

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

• ITEM_INFO 테이블에서 ITEM이 RARE인 ITEM의 ITEM_ID와 ITEM_TREE에서 PARRENT_ITEM_ID를 기준으로 조인합니다.

FROM ITEM_TREE T JOIN (
        SELECT
            *
        FROM ITEM_INFO
        WHERE RARITY = 'RARE'
    ) I
ON T.PARENT_ITEM_ID = I.ITEM_ID

• 다시 ITEM_INFO와 ITEM_ID를 기준으로 조인합니다. 자식 아이템의 RARITY를 확인하기 위함입니다.

FROM ITEM_INFO E JOIN (
    SELECT 
        T.ITEM_ID
    FROM ITEM_TREE T JOIN (
            SELECT
                *
            FROM ITEM_INFO
            WHERE RARITY = 'RARE'
        ) I
    ON T.PARENT_ITEM_ID = I.ITEM_ID
) R
ON E.ITEM_ID = R.ITEM_ID

• ITEM_ID를 기준으로 내림차순 정렬합니다.

Python 개발자 찾기

SELECT
    ID,
    EMAIL,
    FIRST_NAME,
    LAST_NAME
FROM DEVELOPER_INFOS
WHERE SKILL_1 = 'Python' OR SKILL_2 = 'Python' OR SKILL_3 = 'Python'
ORDER BY ID ASC

• SKILL이 Python인 개발자를 찾고 ID를 기준으로 오름차순 정렬합니다.
  (WHERE SKILL_1 = 'Python' OR SKILL_2 = 'Python' OR SKILL_3 = 'Python')

조건에 맞는 개발자 찾기

SELECT 
    DISTINCT D.ID, 
    D.EMAIL, 
    D.FIRST_NAME, 
    D.LAST_NAME
FROM DEVELOPERS D
JOIN SKILLCODES S ON (D.SKILL_CODE & S.CODE) > 0
WHERE S.NAME IN ('Python', 'C#')
ORDER BY D.ID ASC;

• SKILLCODES에 NAME이 Python과 C#인 레코드를 조회합니다.
  (WHERE S.NAME IN ('Python', 'C#'))
• DEVELOPERS의 SKILL_CODE와 S의 CODE를 비트연산(&)을 통해 공통된 비트가 있는 레코드를 조인합니다.
  (FROM DEVELOPERS D JOIN SKILLCODES S ON (D.SKILL_CODE & S.CODE) > 0)
• ID를 기준으로 오름차순 정렬합니다.

잔챙이 잡은 수 구하기

SELECT 
    COUNT(*) AS FISH_COUNT
FROM FISH_INFO
WHERE LENGTH is null;

• 10 cm 이하인 물고기를 조회합니다.
  (WHERE LENGTH is null;)

가장 큰 물고기 10마리 구하기

SELECT 
    ID, 
    IFNULL(LENGTH, 10) AS LENGTH
FROM (
    SELECT 
        ID, 
        LENGTH, 
        ROW_NUMBER() OVER (ORDER BY LENGTH DESC, ID ASC) AS ROWNUM
    FROM FISH_INFO
) AS temp
WHERE ROWNUM <= 10
ORDER BY LENGTH DESC, ID ASC;

• 생선의 길이와 아이디를 기준으로 ROW_NUMBER를 조회합니다.

SELECT 
        ID, 
        LENGTH, 
        ROW_NUMBER() OVER (ORDER BY LENGTH DESC, ID ASC) AS ROWNUM
    FROM FISH_INFO

• ROWNUM이 10보다 작은 레코드를 조회합니다.
  (WHERE ROWNUM <= 10)
• LENGTH가 null이면 10으로 치환합니다.
  (IFNULL(LENGTH, 10) AS LENGTH)
• 생선 길이(내림차순)와 아이디(오름차순)를 기준으로 정렬합니다.

특정 물고기를 잡은 총 수 구하기

SELECT
    COUNT(*) AS FISH_COUNT
FROM FISH_INFO AS I JOIN FISH_NAME_INFO AS N
ON I.FISH_TYPE = N.FISH_TYPE
WHERE N.FISH_NAME = 'BASS' OR N.FISH_NAME = 'SNAPPER';

• FISH_TYPE을 기준으로 조인합니다.
  (FROM FISH_INFO AS I JOIN FISH_NAME_INFO AS N ON I.FISH_TYPE = N.FISH_TYPE)
• 이름이 BASS 또는 SNAPPER인 생선을 조회합니다.
  (WHERE N.FISH_NAME = 'BASS' OR N.FISH_NAME = 'SNAPPER';)

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

SELECT 
    E1.ID, 
    IFNULL(COUNT(E2.PARENT_ID), 0) AS CHILD_COUNT
FROM ECOLI_DATA E1 LEFT JOIN ECOLI_DATA E2 
ON E1.ID = E2.PARENT_ID
GROUP BY E1.ID
ORDER BY E1.ID ASC;

• E1의 ID와 E2의 PARENT_ID를 기준으로 LEFT JOIN합니다.
  (FROM ECOLI_DATA E1 LEFT JOIN ECOLI_DATA E2 ON E1.ID = E2.PARENT_ID)
• E1.ID를 기준으로 GROUP BY합니다.
  (GROUP BY E1.ID)
• 만약 PARENT_ID가 없으면 0으로 처리합니다.
  (IFNULL(COUNT(E2.PARENT_ID), 0) AS CHILD_COUNT)
• ID를 기준으로 오름차순 정렬합니다.

대장균의 크기에 따라 분류하기 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 asc

• CASE WHEN 절을 이용하여 사이즈에 따라 분류합니다.
• ID를 기준으로 오름차순 정렬합니다.

특정 형질을 가지는 대장균 찾기

SELECT
    COUNT(*) AS COUNT
FROM ECOLI_DATA
WHERE GENOTYPE & 2 = 0 AND (GENOTYPE & 4 > 0 OR GENOTYPE & 1 > 0)

• 2번 형질을 보유하지 않으면서 1, 3번 형질을 보듀하고 있는 대장균의 갯수를 조회합니다.
  (WHERE GENOTYPE & 2 = 0 AND (GENOTYPE & 4 > 0 OR GENOTYPE & 1 > 0))

부모의 형질을 모두 가지는 대장균 찾기

SELECT 
    E1.ID,
    E1.GENOTYPE AS GENOTYPE,
    E2.GENOTYPE AS PARENT_GENOTYPE
FROM ECOLI_DATA E1 JOIN ECOLI_DATA E2 
ON E1.PARENT_ID = E2.ID
WHERE (E1.GENOTYPE & E2.GENOTYPE) = E2.GENOTYPE
ORDER BY E1.ID ASC;

• 자식과 부모를 연결합니다.
  (FROM ECOLI_DATA E1 JOIN ECOLI_DATA E2 ON E1.PARENT_ID = E2.ID)
• 자식의 유전자형이 부모의 유전자형을 포함하고 있는지 확인합니다.
  (WHERE (E1.GENOTYPE & E2.GENOTYPE) = E2.GENOTYPE)
• 자식의 ID를 기준으로 오름차순으로 정렬됩니다.

제가 공부한 내용을 정리하는 블로그입니다.
아직 많이 부족하고 배울게 너무나도 많습니다. 틀린내용이 있으면 언제나 가감없이 말씀해주시면 감사하겠습니다😁
Programmers SQL 고득점 Kit(SELECT)입니다.

재구매가 일어난 상품과 회원 리스트 구하기

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

• 동일한 회원, 동일한 상품을 조회하기 위해 USER_ID와 PRODUCT_ID를 기준으로 GROUP BY 해줍니다.
  (GROUP BY USER_ID, PRODUCT_ID)
• 그 중에서 두번 이상의 데이터가 있다는 것은 재구매가 일어났다는 것이니 HAVING을 걸어줍니다.
  (HAVING COUNT(*) >= 2)
• USER_ID의 오름차순, PRODUCT_ID를 내림차순으로 정렬해줍니다.
  (ORDER BY USER_ID ASC, PRODUCT_ID DESC)

모든 레코드 조회하기

SELECT * 
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

• ANIMAL_INS 테이블에서 ANIMAL_ID를 기준으로 오름차순 정렬한
  (ORDER BY ANIMAL_ID)
  데이터의 모든 레코드를 조회합니다.
  (SELECT *)

역순 정렬하기

SELECT
    NAME, 
    DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC

• ANIMAL_INS 테이블에서 ANIMAL_ID를 기준으로 내림차순 정렬한
  (ORDER BY ANIMAL_ID DESC)
  데이터의 NAME과 DATETIME을 조회합니다.

오프라인/온라인 판매 데이터 통합하기

SELECT 
    A.SALES_DATE,
    A.PRODUCT_ID,
    A.USER_ID,
    A.SALES_AMOUNT
FROM    (
    SELECT 
        TO_CHAR(SALES_DATE, 'YYYY-MM-DD') AS SALES_DATE,
        PRODUCT_ID,
        USER_ID,
        SALES_AMOUNT
    FROM ONLINE_SALE
    
    UNION
    
    SELECT 
        TO_CHAR(SALES_DATE, 'YYYY-MM-DD') AS SALES_DATE,
        PRODUCT_ID,
        NULL as USER_ID,
        SALES_AMOUNT
    FROM OFFLINE_SALE
) A
WHERE A.SALES_DATE like '2022-03%'
ORDER BY 
    A.SALES_DATE ASC, 
    A.PRODUCT_ID ASC, 
    A.USER_ID ASC

• 온라인 데이터 테이블(ONLINE_SALE)과 오프라인 데이터 테이블(OFFLINE_SALE)의 필드를 하나로 합치기 위해 필드를 합쳐주고 UNION 을 이용합니다.
• 오프라인에서 USER_ID가 없을 때에는 NULL처리합니다.
  (NULL as USER_ID)
• 그 중 판매일이 2022년 03월인 데이터만 출력합니다.
  (WHERE A.SALES_DATE like '2022-03%')
• 판매일을 기준으로 오름차순, 상품 ID 오름차순, 유저 ID를 오름차순으로 정렬합니다.
  (ORDER BY A.SALES_DATE ASC, A.PRODUCT_ID ASC, A.USER_ID ASC)

아픈 동물 찾기

SELECT 
    ANIMAL_ID,
    NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION = 'Sick'
ORDER BY ANIMAL_ID;

• 보호 시작시 상태가 Sick인 동물을 조회합니다.
  (WHERE INTAKE_CONDITION = 'Sick')
• ANIMAL_ID를 오름차순으로 정렬합니다.

어린 동물 찾기

SELECT 
    ANIMAL_ID,
    NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != 'Aged'
ORDER BY ANIMAL_ID;

• 보호 시작시 상태가 Aged가 아닌 동물을 조회합니다.
  (INTAKE_CONDITION != 'Aged')
• ANIMAL_ID를 오름차순으로 정렬합니다.

동물의 아이디와 이름

SELECT 
    ANIMAL_ID,
    NAME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

• 동물의 아이디와 이름을 아이디를 기준으로 오름차순 정렬합니다.

여러 기준으로 정렬하기

SELECT
    ANIMAL_ID,
    NAME,
    DATETIME
FROM ANIMAL_INS
ORDER BY NAME, DATETIME DESC

• 이름을 기준으로 오름차순, 동물 보호 시작일을 내림차순으로 정렬합니다.

상위 N개 레코드

SELECT
    NAME
FROM (
        SELECT
            NAME
        FROM ANIMAL_INS
        ORDER BY DATETIME asc
    )
WHERE ROWNUM = 1;

• 보호 시작일을 기준으로 오름차순으로 정렬하고 ROWNUM이 1인(가장 먼저 들어온) 데이터를 조회합니다.
  (WHERE ROWNUM = 1;)

조건에 맞는 회원수 구하기

SELECT
    COUNT(*)
FROM USER_INFO
WHERE TO_CHAR(JOINED, 'YYYY') = '2021' AND AGE BETWEEN 20 AND 29

• 2021년에 가입한 회원과 나이가 20세 이상 29세 이하인 회원을 조회하기 위해 BETWEEN을 사용합니다.
  (WHERE TO_CHAR(JOINED, 'YYYY') = '2021' AND AGE BETWEEN 20 AND 29)

제가 공부한 내용을 정리하는 블로그입니다.
아직 많이 부족하고 배울게 너무나도 많습니다. 틀린내용이 있으면 언제나 가감없이 말씀해주시면 감사하겠습니다😁
Programmers SQL 고득점 Kit(SELECT)입니다.

평균 일일 대여 요금 구하기

SELECT ROUND(AVG(DAILY_FEE), 0) AS AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = 'SUV';

• DAILY_FEE의 평균을 계산한 후 소수점 첫째 자리에서 반올림하여 정수로 반환합니다.
  (ROUND(AVG(DAILY_FEE), 0))
• CAR_TYPE이 'SUV'인 레코드만 필터링하여 평균을 계산합니다.
  (WHERE CAR_TYPE = 'SUV)

과일로 만든 아이스크림 고르기

SELECT 
    A.FLAVOR
FROM FIRST_HALF A JOIN ICECREAM_INFO B ON A.FLAVOR = B.FLAVOR
WHERE
    A.TOTAL_ORDER >= 3000 AND
    B.INGREDIENT_TYPE = 'fruit_based'
ORDER BY 
    A.TOTAL_ORDER DESC

• FIRST_HALF 테이블과 ICECREAM_INFO 테이블을 FLAVOR를 기준으로 조인합니다.
  (FROM FIRST_HALF A JOIN ICECREAM_INFO B ON A.FLAVOR = B.FLAVOR)
• TOTAL_ORDER가 3000이 넘고 아이스크림의 주 성분이 과일인 맛을 조회합니다.
  (WHERE A.TOTAL_ORDER >= 3000 AND B.INGREDIENT_TYPE = 'fruit_based')
• 총 주문량이 큰 순서대로 정렬합니다.
  (ORDER BY A.TOTAL_ORDER DESC)

3월에 태어난 여성 회원 목록 출력하기

SELECT 
	MEMBER_ID, 
    MEMBER_NAME, 
    GENDER, 
    TO_CHAR(DATE_OF_BIRTH, 'YYYY-MM-DD')
FROM MEMBER_PROFILE
WHERE TLNO IS NOT NULL AND TO_CHAR(DATE_OF_BIRTH, 'MM') = '03' AND GENDER = 'W'
ORDER BY MEMBER_ID;

• 타입을 맞춰주기 위해 TO_CHAR를 사용합니다.
  (TO_CHAR(DATE_OF_BIRTH, 'YYYY-MM-DD'))
• TLNO가 NULL이 아니며 생월이 3월이며 여성인 멤버를 조회합니다.
  (WHERE TLNO IS NOT NULL AND TO_CHAR(DATE_OF_BIRTH, 'MM') = '03' AND GENDER = 'W')
• 멤버 아이디를 기준으로 오름차순 정렬합니다.
  (ORDER BY MEMBER_ID;)

서울에 위치한 식당 목록 출력하기

SELECT
    I.REST_ID,
    I.REST_NAME,
    I.FOOD_TYPE,
    I.FAVORITES,
    I.ADDRESS,
    R.SCORE
FROM REST_INFO I JOIN (
    SELECT REST_ID, ROUND(AVG(REVIEW_SCORE), 2) AS SCORE
    FROM REST_REVIEW
    GROUP BY REST_ID
)R
ON I.REST_ID = R.REST_ID
WHERE I.ADDRESS LIKE '서울%'
ORDER BY score desc, I.FAVORITES DESC

• REST_REVIEW 테이블에서 REST_ID를 기준으로 GROUP BY를 하여 리뷰 평균점수를 구한 테이블을 R이라고합니다.
• REST_INFO 테이블과 위에서 구한 테이블 R과 REST_ID를 기준으로 조인합니다.
  (REST_INFO I JOIN (...)R ON I.REST_ID = R.REST_ID)
• 식당은 서울에 위치해야하므로 LIKE를 이용합니다.
  (WHERE I.ADDRESS LIKE '서울%')
• SCORE가 높은 순서대로, 즐겨찾기가 높은 순서대로 정렬해줍니다.
  (ORDER BY score desc, I.FAVORITES DESC)

흉부외과 또는 일반외과 의사 목록 출력하기

SELECT 
	DR_NAME, 
    DR_ID, 
    MCDP_CD, 
    TO_CHAR(HIRE_YMD, 'YYYY-MM-DD') AS HIRE_YMD
FROM DOCTOR
WHERE MCDP_CD = 'CS' or MCDP_CD = 'GS'
ORDER BY HIRE_YMD DESC, DR_NAME ASC;

• 흉부외과(CS), 일반외과(GS)인 의사의 정보를 조회합니다.
  (WHERE MCDP_CD = 'CS' or MCDP_CD = 'GS')
• 고용일자를 내림차순, 의사의 이름을 기준으로 오름차순으로 정렬합니다.
  (ORDER BY HIRE_YMD DESC, DR_NAME ASC;)

조건에 부합하는 중고거래 댓글 조회하기

SELECT 
    B.TITLE,
    B.BOARD_ID,
    R.REPLY_ID,
    R.WRITER_ID,
    R.CONTENTS,
    TO_CHAR(R.CREATED_DATE, 'YYYY-MM-DD')
FROM USED_GOODS_BOARD B JOIN USED_GOODS_REPLY R
ON B.BOARD_ID = R.BOARD_ID
WHERE TO_CHAR(B.CREATED_DATE, 'YYYY-MM') = '2022-10'
ORDER BY R.CREATED_DATE ASC, B.TITLE ASC

• USED_GOODS_BOARD 테이블과 USED_GOODS_REPLY를 BOARD_ID를 통해 조인합니다
  (USED_GOODS_BOARD B JOIN USED_GOODS_REPLY R ON B.BOARD_ID = R.BOARD_ID)
• 그 중 2022년 10월에 작성된 게시물을 찾기 위해 TO_CHAR를 통해 조건을 걸어줍니다.
  (WHERE TO_CHAR(B.CREATED_DATE, 'YYYY-MM') = '2022-10')
• 댓글 작성일을 기준으로 오름차순, 게시물 제목을 기준으로 오름차순 정렬해줍니다.
  (ORDER BY R.CREATED_DATE ASC, B.TITLE ASC)

인기있는 아이스크림

SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID ASC;

• 아이스크림의 맛을 총 주문량을 기준으로 내림차순 정렬하고 출하 번호를 기준으로 오름차순 정렬합니다.
  (ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID ASC;)

강원도에 위치한 생산공장 목록 출력하기

SELECT
    FACTORY_ID,
    FACTORY_NAME,
    ADDRESS
FROM FOOD_FACTORY
WHERE ADDRESS Like '강원%'
ORDER BY FACTORY_ID ASC

• FOOD_FACTORY 테이블에서 주소가 강원도인 데이터를 LIKE를 통해 조회합니다.
  (WHERE ADDRESS Like '강원%')
• FACTORY_ID를 기준으로 오름차순 정렬합니다.
  (ORDER BY FACTORY_ID ASC)

12세 이하인 여자 환자 목록 출력하기

SELECT 
    PT_NAME,
    PT_NO,
    GEND_CD,
    AGE,
    NVL(TLNO, 'NONE')
FROM PATIENT
WHERE GEND_CD = 'W' AND AGE <= 12
ORDER BY AGE DESC, PT_NAME ASC;

• PATIENT 테이블에서 12세 이하 여자 환자 데이터를 조회하기 위해 조건을 걸어줍니다.
  (WHERE GEND_CD = 'W' AND AGE <= 12)
• TLNO가 없을 때는 NONE으로 출력하기 위해 NVL을 사용합니다.
  (NVL(TLNO, 'NONE'))
• 나이가 많은 순, 이름을 기준으로 오름차순으로 정렬합니다.
  (ORDER BY AGE DESC, PT_NAME ASC;)

조건에 맞는 도서 리스트 출력하기

SELECT
    BOOK_ID,
    TO_CHAR(PUBLISHED_DATE, 'YYYY-MM-DD')
FROM BOOK
WHERE CATEGORY = '인문' AND TO_CHAR(PUBLISHED_DATE, 'YYYY') = '2021'
ORDER BY PUBLISHED_DATE ASC

• BOOK 테이블에서 카테고리가 인문이고 2021년에 출판된 책의 데이터를 조회합니다.
  (WHERE CATEGORY = '인문' AND TO_CHAR(PUBLISHED_DATE, 'YYYY') = '2021')
• 출판일을 기준으로 오름차순 정렬해줍니다.
  (ORDER BY PUBLISHED_DATE ASC)