1. 인기있는 아이스크림
SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID
2. 12세 이하인 여자 환자 목록 출력하기
SELECT PT_NAME, PT_NO, GEND_CD, AGE, NVL(TLNO, 'NONE')
FROM PATIENT
WHERE AGE <= 12
AND GEND_CD = 'W'
ORDER BY AGE DESC, PT_NAME
3. 재구매가 일어난 상품과 회원 리스트 구하기
SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(USER_ID) > 1
AND COUNT(PRODUCT_ID) > 1
ORDER BY USER_ID, PRODUCT_ID DESC
4. 오프라인/온라인 판매 데이터 통합하기
WITH SALE_DATA AS (
SELECT TO_CHAR(SALES_DATE, 'YYYY-MM-DD') AS SALES_DATE
, PRODUCT_ID
, USER_ID
, SALES_AMOUNT
FROM ONLINE_SALE
WHERE TO_CHAR(SALES_DATE, 'YYYYMM') = '202203'
UNION
SELECT TO_CHAR(SALES_DATE, 'YYYY-MM-DD') AS SALES_DATE
, PRODUCT_ID
, NULL AS USER_ID
, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE TO_CHAR(SALES_DATE, 'YYYYMM') = '202203'
)
SELECT SALES_DATE
, PRODUCT_ID
, USER_ID
, SALES_AMOUNT
FROM SALE_DATA
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID
SELECT TO_CHAR(A.SALES_DATE, 'YYYY-MM-DD'), A.PRODUCT_ID, A.USER_ID, A.SALES_AMOUNT
FROM(
SELECT USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE
FROM ONLINE_SALE
WHERE TO_CHAR(SALES_DATE, 'MM') = '03'
UNION
SELECT NULL AS USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE
FROM OFFLINE_SALE
WHERE TO_CHAR(SALES_DATE, 'MM') = '03'
) A
ORDER BY 1,2,3;
5. 평균 일일 대여 요금 구하기
SELECT ROUND(AVG(DAILY_FEE)) AS AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = 'SUV'
6. 아픈 동물 찾기, 어린 동물 찾기
SELECT ANIMAL_ID
, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION = 'Sick'
ORDER BY ANIMAL_ID
SELECT ANIMAL_ID
, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != 'Aged'
ORDER BY ANIMAL_ID
7. 상위 N개 레코드
SELECT NAME
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY DATETIME) AS NUM
, NAME
FROM ANIMAL_INS
)
WHERE NUM = 1
SELECT NAME
FROM (
SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
)
WHERE ROWNUM = 1
8. 조건에 맞는 회원수 구하기
SELECT COUNT(USER_ID)
FROM USER_INFO
WHERE TO_CHAR(JOINED, 'YYYY') = '2021'
AND AGE BETWEEN 20 AND 29
9. 가격이 제일 비싼 식품의 정보 출력하기
SELECT *
FROM (
SELECT PRODUCT_ID
, PRODUCT_NAME
, PRODUCT_CD
, CATEGORY
, PRICE
FROM FOOD_PRODUCT
ORDER BY PRICE DESC
)
WHERE ROWNUM = 1
10. 중복 제거하기
SELECT COUNT(*)
FROM (
SELECT NAME
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
)
SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS
SELECT COUNT(NAME)
FROM (
SELECT DISTINCT NAME
FROM ANIMAL_INS
)