HTTP/1.1은 매번 TCP 연결을 하는 것이 아닌 한번의 연결 이후 여러개의 파일을 송수신 할 수 있는 프로토콜입니다. 핸드셰이크가 없어져 오버헤드가 적어지지만 리소스가 많아질 수록 대기시간이 길어지는 단점이 있습니다.
HTTP/2는 HTTP/1.x보다 지연시간을 줄이고 응답시간을 더 빠르게 하는 프로토콜로 멀티 플렉싱, 헤더 압축 등 요청의 우선순위를 처리하는 프로토콜입니다. 장점으로는 멀티 플렉싱과 서버 푸시가 있습니다. 멀티 플렉싱은 여러개의 스트림을 사용하여 송수신하고 서버 푸시란 클라이언트의 요청 없이 서버가 클라이언트로 데이터를 먼저 줄 수 있습니다.
제가 공부한 내용을 정리하는 블로그입니다. 아직 많이 부족하고 배울게 너무나도 많습니다. 틀린내용이 있으면 언제나 가감없이 말씀해주시면 감사하겠습니다😁 Programmers SQL 고득점 Kit(String, Date)입니다.
루시와 엘라 찾기
SELECT
ANIMAL_ID,
NAME,
SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
ORDER BY ANIMAL_ID
이름이 'Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty'인 동물을 조회하기 위해 조건을 걸어줍니다. WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
동물 ID를 기준으로 정렬합니다.
이름에 el이 들어가는 동물 찾기
SELECT
ANIMAL_ID,
NAME
FROM ANIMAL_INS
WHERE UPPER(NAME) LIKE '%EL%' AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME;
이름에 EL이 들어가는 개를 찾기 위해 UPPER(또는 LOWER)를 사용하여 Like절을 통해 조회합니다. WHERE UPPER(NAME) LIKE '%EL%' AND ANIMAL_TYPE = 'Dog'
이름을 통해 정렬합니다.
중성화 여부 파악하기
SELECT
ANIMAL_ID,
NAME,
CASE
WHEN SEX_UPON_INTAKE LIKE 'Neutered%' Then 'O'
WHEN SEX_UPON_INTAKE LIKE 'Spayed%' Then 'O'
WHEN SEX_UPON_INTAKE LIKE 'Intact%' THEN 'X'
END AS "중성화"
FROM ANIMAL_INS
ORDER BY ANIMAL_ID
중성화 여부를 위한 CASE 절을 이용합니다. CASE WHEN SEX_UPON_INTAKE LIKE 'Neutered%' Then 'O' WHEN SEX_UPON_INTAKE LIKE 'Spayed%' Then 'O' WHEN SEX_UPON_INTAKE LIKE 'Intact%' THEN 'X' END AS "중성화"
동물 ID를 통해 정렬합니다.
오랜기간 보호한 동물(2)
SELECT ANIMAL_ID, NAME
FROM (
SELECT
INS.ANIMAL_ID,
INS.NAME,
(OUTS.DATETIME - INS.DATETIME) AS PROTECTION_DAYS
FROM
ANIMAL_INS INS
JOIN ANIMAL_OUTS OUTS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
ORDER BY
PROTECTION_DAYS DESC
)
WHERE ROWNUM <= 2;
입양간 동물을 구하기 위해 JOIN 합니다. FROM ANIMAL_INS INS JOIN ANIMAL_OUTS OUTS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
보호기간을 구하기 위해 PROTECTION_DAYS 컬럼을 생성하고 해당 컬럼을 통해 내림차순 정렬합니다. (OUTS.DATETIME - INS.DATETIME) AS PROTECTION_DAYS
상위 두개의 레코드를 조회하기 위해 WHERE절을 이용합니다. WHERE ROWNUM <= 2;
카테고리 별 상품 개수 구하기
SELECT
SUBSTR(PRODUCT_CODE, 0, 2) AS CATEGORY,
COUNT(*)
FROM PRODUCT
GROUP BY SUBSTR(PRODUCT_CODE, 0, 2)
ORDER BY CATEGORY
상품 카테고리 앞 두자리를 기준으로 그룹화를 진행합니다. GROUP BY SUBSTR(PRODUCT_CODE, 0, 2)
카테고리를 기준으로 오름차순 정렬합니다.
DATETIME에서 DATE로 형변환
SELECT
ANIMAL_ID,
NAME,
TO_CHAR(DATETIME, 'YYYY-MM-DD') AS "날짜"
FROM ANIMAL_INS
ORDER BY ANIMAL_ID
DATETIME을 YYYY-MM-DD로 형변환합니다.
동물 ID를 기준으로 오름차순 정렬합니다.
연도 별 평균 미세먼지 농도 조회하기
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
ORDER BY YEAR;
지역이 수원인 동네에 연도별 미세먼지 농도를 구하기 위해 GROUP BY를 사용합니다. WHERE LOCATION2 = '수원' GROUP BY YEAR
연도를 기준으로 오름차순 정렬합니다.
한 해에 잡은 물고기 수 구하기
SELECT
COUNT(*) AS FISH_COUNT
FROM FISH_INFO
WHERE YEAR(TIME) = 2021
2021년에 잡은 물고기 수를 출력하기 위해 조건을 걸어줍니다. WHERE YEAR(TIME) = 2021
분기별 분화된 대장균의 개체수 구하기
SELECT
E.QUARTER,
COUNT(*) AS ECOLI_COUNT
FROM (
SELECT
ID,
CASE
WHEN MONTH(DIFFERENTIATION_DATE) <= 3 THEN "1Q"
WHEN MONTH(DIFFERENTIATION_DATE) <= 6 THEN "2Q"
WHEN MONTH(DIFFERENTIATION_DATE) <= 9 THEN "3Q"
WHEN MONTH(DIFFERENTIATION_DATE) <= 12 THEN "4Q"
END AS QUARTER
FROM ECOLI_DATA
) E
GROUP BY QUARTER
ORDER BY E.QUARTER
월을 기준으로 분기를 구하기 위해 CASE 절을 이용한 서브 테이블 E를 생성합니다. ( SELECT ID, CASE WHEN MONTH(DIFFERENTIATION_DATE) <= 3 THEN "1Q" WHEN MONTH(DIFFERENTIATION_DATE) <= 6 THEN "2Q" WHEN MONTH(DIFFERENTIATION_DATE) <= 9 THEN "3Q" WHEN MONTH(DIFFERENTIATION_DATE) <= 12 THEN "4Q" END AS QUARTER FROM ECOLI_DATA ) E
QUARTER를 기준으로 그룹화와 정렬을 진행합니다 GROUP BY QUARTER ORDER BY E.QUARTER
제가 공부한 내용을 정리하는 블로그입니다. 아직 많이 부족하고 배울게 너무나도 많습니다. 틀린내용이 있으면 언제나 가감없이 말씀해주시면 감사하겠습니다😁 Programmers SQL 고득점 Kit(String, Date)입니다.
자동차 평균 대여 기간 구하기
SELECT
CAR_ID,
TO_CHAR(ROUND(AVG(END_DATE - START_DATE + 1), 1), 'FM9990.0') AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVG(END_DATE - START_DATE + 1) >= 7
ORDER BY ROUND(AVG(END_DATE - START_DATE + 1), 1) DESC, CAR_ID DESC
자동차 평균 대여 기간을 구하기 위해서 자동차 ID를 기준으로 그룹화를 진행합니다. GROUP BY CAR_ID
평균 대여 기간이 7일 이상인 레코드를 조회하기 위해 HAVING 절을 이용합니다. HAVING AVG(END_DATE - START_DATE + 1) >= 7
소수점을 표현하기 위해 FM9990.0의 데이터 형식으로 표현해줍니다. TO_CHAR(ROUND(AVG(END_DATE - START_DATE + 1), 1), 'FM9990.0') AS AVERAGE_DURATION
자동차 대여 기간, 자동차 ID를 기준으로 내림차순 정렬합니다.
조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기
SELECT
CONCAT(CONCAT(
CONCAT(CONCAT(CONCAT('/home/grep/src/', B.BOARD_ID),'/'),file_id),
FILE_NAME),file_ext) AS FILE_PATH
FROM (
SELECT
BOARD_ID
FROM USED_GOODS_BOARD
ORDER BY VIEWS DESC
FETCH NEXT 1 ROWS ONLY
) B JOIN USED_GOODS_FILE F ON B.BOARD_ID = F.BOARD_ID
ORDER BY F.FILE_ID DESC
조회수가 가장 높은 레코드를 조회하기 위해 서브 테이블 B를 생성합니다. 조회수를 기준으로 내림차순 정렬하고 첫번째 레코드를 조회합니다.
(
SELECT
BOARD_ID
FROM USED_GOODS_BOARD
ORDER BY VIEWS DESC
FETCH NEXT 1 ROWS ONLY
) B
B 테이블과 USED_GOODS_FILE 테이블과 BOARD_ID를 기준으로 JOIN 합니다. FROM(...) B JOIN USED_GOODS_FILE F ON B.BOARD_ID = F.BOARD_ID
첨부파일의 경로를 표시하기 위해 여러 컬럼을 더해주기 위해 CONCAT을 여러번 이용합니다. CONCAT(CONCAT(CONCAT(CONCAT(CONCAT('/home/grep/src/', B.BOARD_ID),'/'),file_id), FILE_NAME),file_ext) AS FILE_PATH
파일 ID를 기준으로 내림차순 정렬합니다.
자동차 대여기록 별 대여 금액 구하기
SELECT
H.HISTORY_ID,
C.DAILY_FEE * H.RENTAL_DATE * NVL(P.DISCOUNT_RATE, 1) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C JOIN (
SELECT
HISTORY_ID,
CAR_ID,
END_DATE - START_DATE + 1 AS RENTAL_DATE,
CASE WHEN END_DATE - START_DATE + 1 >= 90 THEN '90일 이상'
WHEN END_DATE - START_DATE + 1 >= 30 THEN '30일 이상'
WHEN END_DATE - START_DATE + 1 >= 7 THEN '7일 이상'
ELSE NULL
END AS DURATION_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
)H ON C.CAR_ID = H.CAR_ID LEFT JOIN (
SELECT
DURATION_TYPE,
(1 - (DISCOUNT_RATE / 100)) AS DISCOUNT_RATE
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
WHERE CAR_TYPE = '트럭'
)P ON H.DURATION_TYPE = P.DURATION_TYPE
WHERE C.CAR_TYPE = '트럭'
ORDER BY FEE DESC, H.HISTORY_ID DESC
가격 정책과 조인하기 위해 서브 테이블 H을 생성합니다. 대여 기간 컬럼(RENTAL_DATE)와 조인을 위한 컬럼(DURATION_TYPE) 컬럼을 추가해줍니다.
(
SELECT
HISTORY_ID,
CAR_ID,
END_DATE - START_DATE + 1 AS RENTAL_DATE,
CASE WHEN END_DATE - START_DATE + 1 >= 90 THEN '90일 이상'
WHEN END_DATE - START_DATE + 1 >= 30 THEN '30일 이상'
WHEN END_DATE - START_DATE + 1 >= 7 THEN '7일 이상'
ELSE NULL
END AS DURATION_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
)H
자동차 ID를 기준으로 JOIN합니다. FROM CAR_RENTAL_COMPANY_CAR C JOIN (...) H ON C.CAR_ID = H.CAR_ID
트럭에 대한 자동차 대여 기록별 대여 금액을 구하기 위해 서브테이블 P를 생성하여 대여 기간을 기준으로 LEFT JOIN 합니다. LEFT JOIN을 하는 이유는 7일 이하로 렌트하는 레코드를 조회하기 위함입니다.
LEFT JOIN (
SELECT
DURATION_TYPE,
(1 - (DISCOUNT_RATE / 100)) AS DISCOUNT_RATE
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
WHERE CAR_TYPE = '트럭'
)P ON H.DURATION_TYPE = P.DURATION_TYPE
자동차 타입이 트럭인 레코드를 조회하기 위한 조건을 걸어줍니다. WHERE C.CAR_TYPE = '트럭'
대여 금액을 구하기 위해 해당 수식의 컬럼을 추가합니다. null인 것은 할인 대상이 아니기에 * 1을 해줍니다. C.DAILY_FEE * H.RENTAL_DATE * NVL(P.DISCOUNT_RATE, 1) AS FEE
대여 금액과 대여 기록 ID를 기준으로 내림차순 조회합니다.
조건에 부합하는 중고거래 상태 조회하기
SELECT
BOARD_ID,
WRITER_ID,
TITLE,
PRICE,
CASE WHEN STATUS = 'SALE' THEN '판매중'
WHEN STATUS = 'RESERVED' THEN '예약중'
WHEN STATUS = 'DONE' THEN '거래완료'
END
FROM USED_GOODS_BOARD
WHERE TO_CHAR(CREATED_DATE, 'YYYY-MM-DD') = '2022-10-05'
ORDER BY BOARD_ID DESC;
2022년 10월 5일에 등록된 레코드를 조회하기 위해 조건을 걸어줍니다. WHERE TO_CHAR(CREATED_DATE, 'YYYY-MM-DD') = '2022-10-05'
STATUS를 통해 판매중/예약중/거래완료를 처리하기 위해 CASE 절을 사용합니다. CASE WHEN STATUS = 'SALE' THEN '판매중' WHEN STATUS = 'RESERVED' THEN '예약중' WHEN STATUS = 'DONE' THEN '거래완료' END
게시물 ID를 기준으로 내림차순 정렬합니다.
특정 옵션이 포함된 자동차 리스트 구하기
SELECT
CAR_ID,
CAR_TYPE,
DAILY_FEE,
OPTIONS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%네비게이션%'
ORDER BY CAR_ID DESC
네비게이션 옵션이 있는 레코드를 조회하기 위해 like 절을 사용합니다. WHERE OPTIONS LIKE '%네비게이션%'
자동차 ID를 기준으로 내림차순 정렬합니다.
자동차 대여 기록에서 장기/단기 대여 구분하기
SELECT
HISTORY_ID,
CAR_ID,
TO_CHAR(START_DATE, 'YYYY-MM-DD') AS START_DATE,
TO_CHAR(END_DATE, 'YYYY-MM-DD') AS END_DATE,
CASE WHEN END_DATE - START_DATE + 1 >= 30 THEN '장기 대여'
ELSE '단기 대여'
END AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE EXTRACT(MONTH FROM START_DATE) = 9
ORDER BY HISTORY_ID DESC
대여 시작일이 9월인 레코드를 조회하기 위해 조건을 걸어줍니다. DATETIME 형식에서 월만 추출하기 위해 EXTRACT 절을 사용합니다. WHERE EXTRACT(MONTH FROM START_DATE) = 9
30일 이상 대여하는 레코드에 대해서는 장기 대여 아닌 것에 대해서는 단기 대여를 위해 CASE 절을 사용합니다. CASE WHEN END_DATE - START_DATE + 1 >= 30 THEN '장기 대여' ELSE '단기 대여' END AS RENT_TYPE
예약 ID를 기준으로 내림차순 정렬합니다.
조건별로 분류하여 주문상태 출력하기
SELECT
ORDER_ID,
PRODUCT_ID,
TO_CHAR(OUT_DATE, 'YYYY-MM-DD') AS OUT_DATE,
CASE WHEN TO_CHAR(OUT_DATE, 'YYYYMMDD') <= '20220501' THEN '출고완료'
WHEN TO_CHAR(OUT_DATE, 'YYYYMMDD') > '20220501' THEN '출고대기'
ELSE '출고미정'
END AS 출고여부
FROM FOOD_ORDER
ORDER BY ORDER_ID
2022년 5월 1일을 기준으로 출고 여부를 조회하기 위해 CASE 절을 사용합니다. CASE WHEN TO_CHAR(OUT_DATE, 'YYYYMMDD') <= '20220501' THEN '출고완료' WHEN TO_CHAR(OUT_DATE, 'YYYYMMDD') > '20220501' THEN '출고대기' ELSE '출고미정' END AS 출고여부
주문 ID를 기준으로 오름차순 정렬합니다.
대여 기록이 존재하는 자동차 리스트 구하기
SELECT
H.CAR_ID
FROM (
SELECT
DISTINCT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE EXTRACT(MONTH FROM START_DATE) = 10
) H JOIN (
SELECT
CAR_ID
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = '세단'
) C ON H.CAR_ID = C.CAR_ID
ORDER BY H.CAR_ID DESC
10월에 대여 시작한 테이블을 조회하기 위해 서브 테이블 H를 생성합니다. 중복을 제거하기 위해 DISTINCT 키워드를 사용합니다.
(
SELECT
DISTINCT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE EXTRACT(MONTH FROM START_DATE) = 10
) H
자동차 종류가 세단인 자동차 데이터를 조회하기 위해 서브 테이블 C를 생성합니다.
(
SELECT
CAR_ID
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = '세단'
) C
자동차 ID를 기준으로 H 테이블과 C 테이블을 JOIN합니다. FROM (...) H JOIN (...) C ON H.CAR_ID = C.CAR_ID
자동차 ID를 기준으로 내림차순 정렬합니다.
조건에 맞는 사용자 정보 조회하기
SELECT
U.USER_ID,
U.NICKNAME,
CONCAT(CONCAT(CONCAT(CONCAT(CITY, ' '), STREET_ADDRESS1), ' '), STREET_ADDRESS2) AS 전체주소,
CONCAT(CONCAT(CONCAT(SUBSTR(TLNO,0, 3), '-'), CONCAT(SUBSTR(TLNO, 4, 4), '-')), SUBSTR(TLNO, 8)) AS 전화번호
FROM USED_GOODS_USER U JOIN (
SELECT
WRITER_ID
FROM USED_GOODS_BOARD
GROUP BY WRITER_ID
HAVING COUNT(*) >= 3
) B ON U.USER_ID = B.WRITER_ID
ORDER BY U.USER_ID DESC
중고 거래 게시물을 작성한 갯수를 구하기 위해 3건 이상 작성한 작성자 ID를 기준으로 그룹화한 서브 테이블 B를 생성합니다.
(
SELECT
WRITER_ID
FROM USED_GOODS_BOARD
GROUP BY WRITER_ID
HAVING COUNT(*) >= 3
) B
USED_GOODS_USER 테이블과 USER_ID를 B.WRITER_ID를 기준으로 JOIN합니다. FROM USED_GOODS_USER U JOIN (...) B ON U.USER_ID = B.WRITER_ID
전체 주소를 조회하기 위해 CITY와 도로명 주소 사이에 CONCAT 함수를 이용하여 띄어쓰기를 넣어줍니다. CONCAT(CONCAT(CONCAT(CONCAT(CITY, ' '), STREET_ADDRESS1), ' '), STREET_ADDRESS2) AS 전체주소,
전화번호 11자 사이에 대쉬(-)를 넣어주기 위해 SUBSTR과 CONCAT 함수를 이용합니다. SUBSTR(A, B, C) => A를 B부터 C자리까지 자름 SUBSTR(A, B) => A를 B이후 문자열 CONCAT(CONCAT(CONCAT(SUBSTR(TLNO,0, 3), '-'), CONCAT(SUBSTR(TLNO, 4, 4), '-')), SUBSTR(TLNO, 8)) AS 전화번호
USER_ID를 기준으로 내림차순 정렬합니다.
취소되지 않은 진료 예약 조회하기
SELECT
A.APNT_NO,
P.PT_NAME,
A.PT_NO,
A.MCDP_CD,
D.DR_NAME,
A.APNT_YMD
FROM DOCTOR D JOIN (
SELECT
APNT_YMD,
APNT_NO,
PT_NO,
MCDP_CD,
MDDR_ID
FROM APPOINTMENT
WHERE APNT_CNCL_YN = 'N' AND TO_CHAR(APNT_YMD, 'YYYY-MM-DD') = '2022-04-13'
) A ON D.DR_ID = A.MDDR_ID JOIN PATIENT P ON A.PT_NO = P.PT_NO
WHERE A.MCDP_CD = 'CS'
ORDER BY A.APNT_YMD ASC
2022년 4월 13일에 취소되지 않은 진료예약을 구하기 위해 서브테이블 A를 생성합니다.
(
SELECT
APNT_YMD,
APNT_NO,
PT_NO,
MCDP_CD,
MDDR_ID
FROM APPOINTMENT
WHERE APNT_CNCL_YN = 'N' AND TO_CHAR(APNT_YMD, 'YYYY-MM-DD') = '2022-04-13'
) A
의사 ID와 환자 ID를 기준으로 JOIN합니다. FROM DOCTOR D JOIN (...) A ON D.DR_ID = A.MDDR_ID JOIN PATIENT P ON A.PT_NO = P.PT_NO
흉부외과 데이터를 조회하기 위해 조건을 걸어줍니다. WHERE A.MCDP_CD = 'CS'
제가 공부한 내용을 정리하는 블로그입니다. 아직 많이 부족하고 배울게 너무나도 많습니다. 틀린내용이 있으면 언제나 가감없이 말씀해주시면 감사하겠습니다😁 Programmers SQL 고득점 Kit(JOIN)입니다.
특정 기간동안 대여 가능한 자동차들의 대여 비용 구하기
SELECT
H.CAR_ID,
C.CAR_TYPE,
C.DAILY_FEE * 30 * (1 - (DISCOUNT_RATE / 100)) AS FEE
FROM (
SELECT DISTINCT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
GROUP BY CAR_ID
HAVING MAX(TO_CHAR(END_DATE, 'YYYY-MM')) < '2022-11'
) H JOIN (
SELECT CAR_ID, CAR_TYPE, DAILY_FEE
FROM CAR_RENTAL_COMPANY_CAR C
WHERE CAR_TYPE = '세단' OR CAR_TYPE = 'SUV'
) C ON H.CAR_ID = C.CAR_ID JOIN (
SELECT CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
WHERE (CAR_TYPE = '세단' OR CAR_TYPE = 'SUV') AND (DURATION_TYPE = '30일 이상')
) P ON C.CAR_TYPE = P.CAR_TYPE
WHERE C.DAILY_FEE * 30 * (1 - (DISCOUNT_RATE / 100)) BETWEEN 500000 AND 2000000
ORDER BY FEE DESC, C.CAR_TYPE ASC, H.CAR_ID DESC
2022년 11월 대여가 가능한 차를 찾아야 하기에 CAR_RENTAL_COMPANY_RENTAL_HISTORY에서 반납일이 11월보다 작은 차를 조회하기 위한 서브 테이블 H를 생성합니다.
(
SELECT DISTINCT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
GROUP BY CAR_ID
HAVING MAX(TO_CHAR(END_DATE, 'YYYY-MM')) < '2022-11'
) H
자동차의 종류가 세단이나 SUV여야 하므로 서브테이블 C를 생성합니다.
(
SELECT CAR_ID, CAR_TYPE, DAILY_FEE
FROM CAR_RENTAL_COMPANY_CAR C
WHERE CAR_TYPE = '세단' OR CAR_TYPE = 'SUV'
) C
H테이블과 C 테이블을 CAR_ID를 기준으로 조인합니다 FROM (...) H JOIN (...)C ON H.CAR_ID = C.CAR_ID
세단과 SUV의 30일 이상 대여할 시의 할인율을 구하기 위한 서브 테이블 P를 생성합니다.
(
SELECT CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
WHERE (CAR_TYPE = '세단' OR CAR_TYPE = 'SUV') AND (DURATION_TYPE = '30일 이상')
) P
P 테이블과 C테이블의 CAR_TYPE을 기준으로 조인합니다. C.CAR_TYPE = P.CAR_TYPE
30일 간의 대여 금액이 50만원 이상 200만원 미만인 자동차를 구하기 위해 BETWEEN 함수를 이용하빈다. DISCOUNT_RATE가 퍼센트로 표시되어있기에 해당 계산을 이용합니다. WHERE C.DAILY_FEE * 30 * (1 - (DISCOUNT_RATE / 100)) BETWEEN 500000 AND 2000000
자동차 종류를 기준으로 오름차순 정렬, 자동차 ID를 기준으로 내림차순 정렬합니다.
5월 식품들의 총 매출 조회하기
SELECT
P.PRODUCT_ID,
P.PRODUCT_NAME,
P.PRICE * O.AMOUNT AS TOTAL_SALES
FROM FOOD_PRODUCT P JOIN (
SELECT PRODUCT_ID, SUM(AMOUNT) AS AMOUNT
FROM FOOD_ORDER O
WHERE TO_CHAR(PRODUCE_DATE, 'YYYY-MM') = '2022-05'
GROUP BY PRODUCT_ID
) O ON P.PRODUCT_ID = O.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, P.PRODUCT_ID ASC
2022년 5월에 생산된 식품을 조회하기 위한 서브 테이블 O를 생성합니다. 총 매출(TOTAL_ORDER * PRICE)을 구해야하기 때문에식품ID를 기준으로 그룹화를 하고 SUM 함수를 사용합니다.
(
SELECT PRODUCT_ID, SUM(AMOUNT) AS AMOUNT
FROM FOOD_ORDER O
WHERE TO_CHAR(PRODUCE_DATE, 'YYYY-MM') = '2022-05'
GROUP BY PRODUCT_ID
) O
FOOD_PRODUCT 테이블과 PRODUCT_ID를 기준으로 JOIN합니다. FROM FOOD_PRODUCT P JOIN (...) O ON P.PRODUCT_ID = O.PRODUCT_ID
총 매출을 구하기 위해 다음과 같이 TOTAL_SALES 컬럼을 구성합니다. P.PRICE * O.AMOUNT AS TOTAL_SALES
총 매출을 기준으로 내림차순 정렬하고, 식품 ID를 기준으로 오름차순 정렬합니다.
주문량이 많은 아이스크림들 조회하기
SELECT
H.FLAVOR
FROM (
SELECT FLAVOR, TOTAL_ORDER
FROM FIRST_HALF
) H JOIN (
SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM JULY
GROUP BY FLAVOR
) J ON H.FLAVOR = J.FLAVOR
ORDER BY H.TOTAL_ORDER + J.TOTAL_ORDER DESC
FETCH NEXT 3 ROWS ONLY
상반기 아이스크림의 총 주문량을 구하기 위한 서브 테이블 H를 생성합니다.
(
SELECT FLAVOR, TOTAL_ORDER
FROM FIRST_HALF
) H
7월에는 주문량이 많아 다른 두 공장에서 같은 맛을 출하를 진행하였으므로 FLAVOR를 기준으로 그룹화를 진행하고 총 주문량을 조회하는 서브테이르 J를 생성합니다.
(
SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM JULY
GROUP BY FLAVOR
) J
H 테이블과 J 테이블을 FLAVOR을 기준으로 JOIN 합니다. FROM (...) H JOIN (...) J ON H.FLAVOR = J.FLAVOR
총 주문량에 대해 내림차순 정렬 후 상위 3개의 레코드를 조회합니다. ORDER BY H.TOTAL_ORDER + J.TOTAL_ORDER DESC FETCH NEXT 3 ROWS ONLY
조건에 맞는 도서와 저자 리스트 출력하기
SELECT
B.BOOK_ID,
A.AUTHOR_NAME,
TO_CHAR(B.PUBLISHED_DATE, 'YYYY-MM-DD') AS PUBLISHED_DATE
FROM BOOK B JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE B.CATEGORY = '경제'
ORDER BY B.PUBLISHED_DATE
도서 테이블과 저자 테이블을 JOIN하기 위해 AUTHOR_ID를 기준으로 JOIN합니다. FROM BOOK B JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
경제 카테고리에 해당하는 도서를 찾기 위해 조건을 걸어줍니다. WHERE B.CATEGORY = '경제'
출판일을 기준으로 오름차순 정렬합니다.
그룹별 조건에 맞는 식당 목록 출력하기
WITH MemberReviewCount AS (
SELECT
MEMBER_ID,
COUNT(*) AS REVIEW_COUNT
FROM
REST_REVIEW
GROUP BY
MEMBER_ID
),
MaxReviewMember AS (
SELECT
MEMBER_ID
FROM
MemberReviewCount
WHERE
REVIEW_COUNT = (SELECT MAX(REVIEW_COUNT) FROM MemberReviewCount)
)
SELECT
MP.MEMBER_NAME,
RR.REVIEW_TEXT,
TO_CHAR(RR.REVIEW_DATE, 'YYYY-MM-DD')
FROM
REST_REVIEW RR
JOIN MEMBER_PROFILE MP ON RR.MEMBER_ID = MP.MEMBER_ID
JOIN MaxReviewMember MRM ON RR.MEMBER_ID = MRM.MEMBER_ID
ORDER BY
TO_CHAR(RR.REVIEW_DATE, 'YYYY-MM-DD') ASC,
RR.REVIEW_TEXT ASC;
WITH 키워드를 이용하여 테이블을 미리 생성합니다. 첫번째 테이블 MemberReviewCount는 MEMBER_ID를 기준으로 리뷰 횟수를 조회하는 테이블을 생성합니다.
WITH MemberReviewCount AS (
SELECT
MEMBER_ID,
COUNT(*) AS REVIEW_COUNT
FROM
REST_REVIEW
GROUP BY
MEMBER_ID
),
두번째 테이블은 리뷰를 가장 많이 작성한 회원 ID를 조회하기 위한 테이블을 생성합니다
MaxReviewMember AS (
SELECT
MEMBER_ID
FROM
MemberReviewCount
WHERE
REVIEW_COUNT = (SELECT MAX(REVIEW_COUNT) FROM MemberReviewCount)
)
레스토랑 리뷰 테이블과 멤버 정보 테이블, MaxReviewMember 테이블을 Member_ID를 기준으로 JOIN 합니다. FROM REST_REVIEW RR JOIN MEMBER_PROFILE MP ON RR.MEMBER_ID = MP.MEMBER_ID JOIN MaxReviewMember MRM ON RR.MEMBER_ID = MRM.MEMBER_ID
리뷰 작성일을 기준으로 오름차순, 리뷰 텍스트를 기준으로 오름차순 정렬합니다.
없어진 기록 찾기
SELECT
O.ANIMAL_ID,
O.NAME
FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID(+) = O.ANIMAL_ID
WHERE I.ANIMAL_ID IS NULL
ORDER BY O.ANIMAL_ID, O.NAME;
동물 보호소에 들어온 동물과 입양 보낸 동물들을 ANIMAL_ID를 기준으로 LEFT JOIN합니다. LEFT JOIN하는 이유는 입양 들어온 기록은 존재하는데 입양 보낸 기록이 없을 때를 찾기 위함입니다. FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID(+) = O.ANIMAL_ID
없어진 동물의 기록을 찾아야하기 때문에 I.ANIMAL_ID가 NULL인 데이터를 조회합니다. WHERE I.ANIMAL_ID IS NULL
동물 ID와 이름을 기준으로 오름차순 정렬합니다.
있었는데요 없었습니다
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
동물 보호소에 들어온 동물과 입양 보낸 동물들을 ANIMAL_ID를 기준으로 JOIN합니다. FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
보호 시작일이 입양일보다 더 늦은 레코드를 조회하기 위해 조건을 걸어줍니다. WHERE I.DATETIME > O.DATETIME
보호 시작일이 빠른 순으로 조회합니다.(오름차순으로 정렬합니다)
오랜기간 보호한 동물(1)
SELECT
I.NAME,
I.DATETIME
FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID(+)
WHERE O.ANIMAL_ID IS NULL
ORDER BY I.DATETIME ASC
FETCH NEXT 3 ROWS ONLY
동물 보호소에 들어온 동물과 입양 보낸 동물들을 ANIMAL_ID를 기준으로 RIGHT JOIN합니다. RIGHT JOIN하는 이유는 입양을 아직 못간 동물을 찾기 위합니다. FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID(+)
입양을 못간 동물을 조회하기 위해 조건을 걸어줍니다. WHERE O.ANIMAL_ID IS NULL
보호 시작일을 기준으로 오름차순으로 정렬하고 상위 3개의 레코드를 조회하기 위해 FETCH NEXT 절을 사용합니다. ORDER BY I.DATETIME ASC FETCH NEXT 3 ROWS ONLY
보호소에서 중성화한 동물
SELECT
I.ANIMAL_ID,
I.ANIMAL_TYPE,
I.NAME
FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.SEX_UPON_INTAKE LIKE 'Intact%'
AND (O.SEX_UPON_OUTCOME LIKE 'Spayed%' OR O.SEX_UPON_OUTCOME LIKE 'Neutered%')
ORDER BY I.ANIMAL_ID
동물 보호소에 들어온 동물과 입양 보낸 동물들을 ANIMAL_ID를 기준으로 JOIN합니다. FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
보호소에서 중성화한 동물을 찾기 위해 조건을 걸어줍니다. 들어왔을 때는 아니였지만 나갈때 중성화가 되어있으면 보호소에서 중성화 한 것입니다. WHERE I.SEX_UPON_INTAKE LIKE 'Intact%' AND (O.SEX_UPON_OUTCOME LIKE 'Spayed%' OR O.SEX_UPON_OUTCOME LIKE 'Neutered%')
동물 ID를 기준으로 오름차순 정렬합니다.
상품 별 오프라인 매출 구하기
SELECT
P.PRODUCT_CODE,
P.PRICE * S.TOTAL_SALES AS SALES
FROM PRODUCT P JOIN (
SELECT PRODUCT_ID, SUM(SALES_AMOUNT) AS TOTAL_SALES
FROM OFFLINE_SALE
GROUP BY PRODUCT_ID
) S ON P.PRODUCT_ID = S.PRODUCT_ID
ORDER BY SALES DESC, P.PRODUCT_CODE ASC
오프라인에서 상품별 판매량을 구하기 위해 상품 ID를 기준으로 그룹화한 테이블을 S로 생성합니다.
(
SELECT PRODUCT_ID, SUM(SALES_AMOUNT) AS TOTAL_SALES
FROM OFFLINE_SALE
GROUP BY PRODUCT_ID
) S
상품 테이블과 S를 PRODUCT_ID를 기준으로 JOIN합니다. FROM PRODUCT P JOIN (...) S ON P.PRODUCT_ID = S.PRODUCT_ID
매출을 구하기 위해 다음과 같이 필드를 생성합니다. P.PRICE * S.TOTAL_SALES AS SALES
총 매출량을 기준으로 내림차순, 상품 코드를 기준으로 오름차순 정렬합니다.
상품을 구매한 회원 비율 구하기
SELECT
TO_CHAR(O.SALES_DATE, 'YYYY') AS YEAR,
EXTRACT(MONTH FROM O.SALES_DATE) AS MONTH,
COUNT(DISTINCT U.USER_ID) AS PUCHASED_USERS,
ROUND(COUNT(DISTINCT U.USER_ID)/(SELECT COUNT(*) FROM USER_INFO WHERE TO_CHAR(joined, 'YYYY') = '2021'), 1) AS PUCHASED_RATIO
FROM USER_INFO U JOIN ONLINE_SALE O ON U.USER_ID = O.USER_ID
WHERE TO_CHAR(U.JOINED, 'YYYY') = '2021'
GROUP BY TO_CHAR(O.SALES_DATE, 'YYYY'), EXTRACT(MONTH FROM O.SALES_DATE)
ORDER BY TO_CHAR(O.SALES_DATE, 'YYYY'), EXTRACT(MONTH FROM O.SALES_DATE)
상품을 구매한 회원을 찾기 위해 USER_INFO 테이블과 ONLINE_SALE 테이블을 JOIN 합니다. FROM USER_INFO U JOIN ONLINE_SALE O ON U.USER_ID = O.USER_ID
그 중 2021년에 가입한 회원을 구하기 위해 조건을 걸어줍니다. WHERE TO_CHAR(U.JOINED, 'YYYY') = '2021'
연, 월별로 (구매 회원수 / 가입 회원) 을 구해야하기에 연, 월을 기준으로 그룹화를 진행합니다. GROUP BY TO_CHAR(O.SALES_DATE, 'YYYY'), EXTRACT(MONTH FROM O.SALES_DATE)
상품을 구매한 회원의 비율을 소수점 두번째 자리에서 반올림해야 하기에 다음과 같이 컬럼을 구성합니다. 2021년에 가입한 회원의 전체 수를 구하기 위해 서브 쿼리를 이용합니다. ROUND(COUNT(DISTINCT U.USER_ID)/(SELECT COUNT(*) FROM USER_INFO WHERE TO_CHAR(joined, 'YYYY') = '2021'), 1) AS PUCHASED_RATIO
연과 월을 기준으로 오름차순 정렬합니다.
FrontEnd 개발자 찾기
SELECT
D.ID,
D.EMAIL,
D.FIRST_NAME,
D.LAST_NAME
FROM
DEVELOPERS D
WHERE
EXISTS (
SELECT 1
FROM SKILLCODES S
WHERE S.CATEGORY = 'FRONT END'
AND (D.SKILL_CODE & S.CODE) <> 0
)
ORDER BY
D.ID ASC;
서브 쿼리를 사용합니다. SKILL이 FRONT END이고 DEVELOPERS의 SKILL_CODE와 비트 연산을 통해 0보다 큰(공통 비트가 존재하는) 컬럼을 조회합니다.
FROM
DEVELOPERS D
WHERE
EXISTS (
SELECT 1
FROM SKILLCODES S
WHERE S.CATEGORY = 'FRONT END'
AND (D.SKILL_CODE & S.CODE) <> 0
)
제가 공부한 내용을 정리하는 블로그입니다. 아직 많이 부족하고 배울게 너무나도 많습니다. 틀린내용이 있으면 언제나 가감없이 말씀해주시면 감사하겠습니다😁 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
제가 공부한 내용을 정리하는 블로그입니다. 아직 많이 부족하고 배울게 너무나도 많습니다. 틀린내용이 있으면 언제나 가감없이 말씀해주시면 감사하겠습니다😁 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 JOINHR_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)
