[SQL Kit] SELECT

Yeojin·2023년 12월 5일
0

programmers

목록 보기
1/5

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
        )
profile
"Ever tried. Ever failed. No matter. Try Again. Fail again. Fail better."

0개의 댓글

관련 채용 정보