Computer Science Knowledge Storage

제가 공부한 내용을 정리하는 블로그입니다.
아직 많이 부족하고 배울게 너무나도 많습니다. 틀린내용이 있으면 언제나 가감없이 말씀해주시면 감사하겠습니다😁
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)