SQL 고득점 킷 JOIN

 

1. 주문량이 많은 아이스크림 구하기 

 

- 내 코드 

-- -- 코드를 입력하세요
-- 토탈 오더 더한 거 TOP -N 구해야함
-- FIRST_HALF 기본기 : 맛 , 외래키 SHIPMENT_ID
-- JULY 기본기 SHIPMENT_ID 외래키 맛
-- => JULY를 맛 별로 그룹해서 일단 맛별 토탈 오더 구하고
-- > FIRST_HALF와 맛별로 조인 + 토탈 오더를 더한다. 이를 정렬한다.
-- > 여기서 ROWNUM 3까지 구함

WITH
JY as (SELECT SUM(TOTAL_ORDER) TOTAL_ORDER ,FLAVOR FROM JULY
 GROUP BY FLAVOR)

  SELECT FLAVOR
  FROM (SELECT  jy.FLAVOR, jy.TOTAL_ORDER + fh.TOTAL_ORDER as TOTAL
      FROM FIRST_HALF fh JOIN JY jy ON (fh.FLAVOR = jy.FLAVOR)
      ORDER BY TOTAL
 WHERE ROWNUM <= 3;

 -> WITH절에 중첩 안됨 연속으로 미리 만들어둔 서브쿼리 다음 서브쿼리에서 사용안됨

 -> as 키워드는 SELECT 절에만 사용가능 FROM은 바로 별칭 

 

SELECT T.FLAVOR
FROM ( SELECT FH.FLAVOR, SUM(FH.TOTAL_ORDER) + SUM(J.TOTAL_ORDER) as TOTAL
       FROM FIRST_HALF FH INNER JOIN JULY J ON (FH.FLAVOR = J. FLAVOR)
       GROUP BY FH.FLAVOR
       ORDER BY TOTAL DESC) T
WHERE ROWNUM <= 3;

 

 

2. 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 

 

SELECT ccr.CAR_ID, ccr.CAR_TYPE, ccr.DAILY_FEE * 30 * (1-(ccdp.DISCOUNT_RATE/100)) AS FEE
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN ccdp JOIN CAR_RENTAL_COMPANY_CAR ccr ON (ccdp.CAR_TYPE = ccr.CAR_TYPE)
WHERE ccr.CAR_ID NOT IN (SELECT ch.CAR_ID 
                         FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY ch
                         WHERE TO_CHAR(END_DATE,'YYYYMMDD') >'20221101' 
                         AND TO_CHAR(START_DATE,'YYYYMMDD') <'20221201')
AND ccdp.DURATION_TYPE ='30일 이상'                         
AND ccr.CAR_TYPE IN ('세단','SUV')
AND ccr.DAILY_FEE * 30 * (1-(ccdp.DISCOUNT_RATE/100)) BETWEEN 500000 AND 2000000
ORDER BY FEE DESC, ccr.CAR_TYPE, ccr.CAR_ID DESC;

 - 굉장히 어려웠다.

 

* 오라클 날짜자료형 비교 TO_CHAR 이용하는 법 

SELECT ch.CAR_ID 
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY ch
WHERE TO_CHAR(END_DATE,'YYYYMMDD') >'20221101' 
AND TO_CHAR(START_DATE,'YYYYMMDD') <'20221201')

 - 모든 데이터 중에 시작날짜는 12월 1일보다 작으면서, 렌탈 종료날 짜는 11월 1일보다 큰 애들 제외

 - 위 쿼리 결과로 CAR_ID 해당하는 CAR_ID가 나옴 -> 이를 비교하며 제외

 

* HISTORY 컬럼임 -> ID별로 대여에 모든 데이터들이 나옴 

 

 

3. 오랜기간 보호한 동물 

SELECT ai.NAME,ai.DATETIME
FROM (SELECT ai.NAME, ai.DATETIME
FROM ANIMAL_INS ai LEFT OUTER JOIN ANIMAL_OUTS ao
ON(ai.ANIMAL_ID = ao.ANIMAL_ID)
WHERE ao.ANIMAL_ID IS NULL
ORDER BY ai.DATETIME) ai
WHERE ROWNUM <= 3;

- TOP-N만 사용할 수 있으면 비교적 쉽게 풀 수 있다.

 

 

4. 없어진 기록 

SELECT 
ao.ANIMAL_ID, ao.NAME
FROM ANIMAL_OUTS ao LEFT OUTER JOIN ANIMAL_INS ai ON (ao.ANIMAL_ID = ai.ANIMAL_ID)
WHERE ai.ANIMAL_ID IS NULL
ORDER BY ao.ANIMAL_ID;

 

5. 있었는데요, 없었습니다.

SELECT ai.ANIMAL_ID, ai.NAME FROM ANIMAL_INS ai INNER JOIN ANIMAL_OUTS ao
ON(ai.ANIMAL_ID = ao.ANIMAL_ID)
WHERE ai.DATETIME > ao.DATETIME
ORDER BY ai.DATETIME;

1. 있었는데요 없었습

6. 그룹별 조건에 맞는 식당 목록 출력하기 

 

- 내 풀이 

SELECT mp.MEMBER_NAME, rs.REVIEW_TEXT, TO_CHAR(rs.REVIEW_DATE,'YYYY-MM-DD') as REVIEW_DATE
 FROM MEMBER_PROFILE mp 
 JOIN REST_REVIEW rs 
 ON (mp.MEMBER_ID = rs.MEMBER_ID)
 WHERE mp.MEMBER_ID IN (
  SELECT rr.MEMBER_ID
 FROM REST_REVIEW rr
 GROUP BY rr.MEMBER_ID
 HAVING COUNT(*) = ( SELECT MAX(cnt) 
 FROM (SELECT COUNT(*) cnt
 FROM REST_REVIEW
 GROUP BY MEMBER_ID))
 )
 ORDER BY REVIEW_DATE,REVIEW_TEXT;

 - 중첩이 많다..

 

7. 5월 식품들의 총매출 조회하기 

 SELECT fp.PRODUCT_ID, fp.PRODUCT_NAME, (fo.AMOUNT * fp.PRICE) as TOTAL_SALES
 FROM 
 (SELECT fo.PRODUCT_ID,SUM(fo.AMOUNT) as AMOUNT 
  FROM FOOD_ORDER fo
  WHERE TO_CHAR(fo.PRODUCE_DATE,'YYYYMM') = 202205
 GROUP BY fo.PRODUCT_ID
 ) fo INNER JOIN FOOD_PRODUCT fp ON (fo.PRODUCT_ID = fp.PRODUCT_ID)
 ORDER BY TOTAL_SALES DESC, fp.PRODUCT_ID;

 

8. 보호소에서 중성화한 동물 

SELECT ai.ANIMAL_ID, ai.ANIMAL_TYPE, ai.NAME

FROM ANIMAL_INS ai 
WHERE ai.SEX_UPON_INTAKE LIKE '%Intact%'
AND ai.ANIMAL_ID IN (
    SELECT animal_id FROM ANIMAL_OUTS ao
     WHERE ao.SEX_UPON_OUTCOME LIKE 'Neutered%' 
     OR ao.SEX_UPON_OUTCOME LIKE 'Spayed%');