특정 단어가 포함된 경우
SELECT CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%네비게이션%'
ORDER BY CAR_ID DESC
컬럼을 문자열로 연결하기
SELECT TO_CHAR('/home/grep/src/' || A.BOARD_ID || '/' || FILE_ID || FILE_NAME || FILE_EXT ) as "FILE_PATH"
FROM USED_GOODS_BOARD A JOIN USED_GOODS_FILE B
ON A.BOARD_ID = B.BOARD_ID
WHERE A.VIEWS = (SELECT MAX(VIEWS) FROM USED_GOODS_BOARD)
ORDER BY B.FILE_ID DESC
날짜의 기간에 따라 value 설정
SELECT B.HISTORY_ID,
B.CAR_ID,
TO_CHAR(B.START_DATE, 'YYYY-MM-DD') AS START_DATE,
TO_CHAR(B.END_DATE, 'YYYY-MM-DD') AS END_DATE,
CASE
WHEN A.DATE_DIFF >= 30 THEN '장기 대여'
ELSE '단기 대여'
END AS RENT_TYPE
FROM
(
SELECT HISTORY_ID, (END_DATE - START_DATE + 1) AS DATE_DIFF
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE EXTRACT(YEAR FROM START_DATE) = 2022
AND EXTRACT(MONTH FROM START_DATE) = 9
) A LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B
ON A.HISTORY_ID = B.HISTORY_ID
ORDER BY 1 DESC
날짜 범위에 따라 value 설정
SELECT ORDER_ID, PRODUCT_ID, TO_CHAR(OUT_DATE, 'YYYY-MM-DD' ) AS "OUT_DATE",
CASE
WHEN OUT_DATE <= TO_DATE('2022-05-01', 'YYYY-MM-DD') THEN '출고완료'
WHEN OUT_DATE > TO_DATE('2022-05-01', 'YYYY-MM-DD') THEN '출고대기'
ELSE '출고미정'
END AS 출고여부
FROM FOOD_ORDER
ORDER BY 1
월별 구하기
SELECT EXTRACT(MONTH FROM START_DATE) MONTH, CAR_ID, COUNT(*) RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (
SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE EXTRACT(MONTH FROM START_DATE) IN (8, 9, 10)
GROUP BY CAR_ID
HAVING COUNT(*) > 4
)
AND 8 <= EXTRACT(MONTH FROM START_DATE) AND EXTRACT(MONTH FROM START_DATE) < 11
GROUP BY EXTRACT(MONTH FROM START_DATE), CAR_ID
HAVING COUNT(*) > 0
ORDER BY MONTH, CAR_ID DESC
상위 특정 개수의 행 출력하기
SELECT B.NAME, B.DATETIME
FROM (
SELECT A.ANIMAL_ID
FROM (
SELECT A.ANIMAL_ID
FROM ANIMAL_INS A
LEFT JOIN ANIMAL_OUTS B
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.ANIMAL_ID IS NULL
ORDER BY A.DATETIME
) A
WHERE ROWNUM <= 3
) A JOIN ANIMAL_INS B
ON A.ANIMAL_ID = B.ANIMAL_ID
ORDER BY B.DATETIME ASC
pk는 다르지만 같은 값이 2개인 경우 + 상위 3개 구하기
SELECT FLAVOR
FROM (
SELECT A.FLAVOR, (A.SUM + B.SUM) AS SUM
FROM (
SELECT FLAVOR, SUM(TOTAL_ORDER) AS SUM
FROM FIRST_HALF
GROUP BY FLAVOR
) A JOIN
(
SELECT FLAVOR, SUM(TOTAL_ORDER) AS SUM
FROM JULY
GROUP BY FLAVOR
) B
ON A.FLAVOR = B.FLAVOR
ORDER BY SUM DESC
)
WHERE ROWNUM <= 3
SELECT A.PRODUCT_ID, B.PRODUCT_NAME, (A.AMOUNT * B.PRICE) AS TOTAL_SALES
FROM (
SELECT PRODUCT_ID, SUM(AMOUNT) AS AMOUNT
FROM FOOD_ORDER
WHERE EXTRACT(YEAR FROM PRODUCE_DATE) = 2022
AND EXTRACT(MONTH FROM PRODUCE_DATE) = 5
GROUP BY PRODUCT_ID
) A JOIN FOOD_PRODUCT B
ON A.PRODUCT_ID = B.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, A.PRODUCT_ID
자리수별 문자 정보 가져오기
SELECT SUBSTR(PRODUCT_CODE, 1, 2) AS CODE, COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY SUBSTR(PRODUCT_CODE, 1, 2)
ORDER BY 1
문제에서 나오지는 않지만 타입 체크 잘하기, 문자열 특정 단어 포함되는 값 찾기
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE LOWER(NAME) LIKE '%el%'
AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME
번호에 하이픈 넣기
SELECT B.USER_ID,
B.NICKNAME, TO_CHAR(B.CITY || ' ' || B.STREET_ADDRESS1 || ' ' || B.STREET_ADDRESS2 ) AS "전체주소",
SUBSTR(B.TLNO, 1, 3) || '-' || SUBSTR(B.TLNO, 4, 4) || '-' || SUBSTR(B.TLNO, 8) AS "전화번호"
FROM
(
SELECT A.WRITER_ID, COUNT(*) AS COUNT
FROM USED_GOODS_BOARD A JOIN USED_GOODS_USER B
ON A.WRITER_ID = B.USER_ID
GROUP BY A.WRITER_ID
HAVING COUNT(*) >= 3
) A LEFT JOIN
USED_GOODS_USER B
ON A.WRITER_ID = B.USER_ID
ORDER BY 1 DESC
시간대별 개수 구하기
SELECT TO_NUMBER(TO_CHAR(DATETIME, 'HH24')) AS HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS
WHERE TO_CHAR(DATETIME, 'HH24') BETWEEN 9 AND 19
GROUP BY TO_CHAR(DATETIME, 'HH24')
ORDER BY HOUR
기간사이의 일 수 구하기
SELECT CAR_ID,
ROUND(SUM(DATEDIFF(END_DATE, START_DATE) + 1) / COUNT(CAR_ID), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC
2개의 테이블을 하나로 합치기 UNION, UNION ALL
- UNION
- UNION ALL
- 모든 컬럼값이 같은 ROW도 결과로 보여준다. 중복제거 하지 않는다.
