[24주차_SQL] SQL 풀이 모음

[jmxx219]

고다혜:
멸종위기의 대장균 찾기

배수빈:
특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

백제완:
주문량이 많은 아이스크림들 조회하기

손지민:
상품을 구매한 회원 비율 구하기

이지영:
연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기

## 문제: [문제명](링크)


### 설명


### 코드


### 아이디어

[yeongleej]

문제: 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기

설명

• HR_DEPARTMENT : 회사의 부서 정보
• HR_EMPLOYEES : 사원정보
• HR_GRADE : 2022년 사원의 평가정보

기준 점수	평가 등급	성과금(연봉 기준)
96 이상	S	20%
90 이상	A	15%
80 이상	B	10%
이외	C	0%

=> 각 사원의 (사번, 성명, 평가등급, 성과금) 조회
=> 사번 오름차순으로 정렬

코드

SELECT
    E.EMP_NO, E.EMP_NAME,
    CASE 
        WHEN AVG(G.SCORE) >= 96 THEN 'S'
        WHEN AVG(G.SCORE) >= 90 THEN 'A'
        WHEN AVG(G.SCORE) >= 80 THEN 'B'
        ELSE 'C'
    END AS GRADE,
    CASE
        WHEN AVG(G.SCORE) >=96 THEN E.SAL*0.2
        WHEN AVG(G.SCORE) >=90 THEN E.SAL*0.15
        WHEN AVG(G.SCORE) >=80 THEN E.SAL*0.1
        ELSE 0
    END AS BONUS
FROM HR_EMPLOYEES E
LEFT JOIN HR_GRADE G
ON E.EMP_NO = G.EMP_NO
GROUP BY E.EMP_NO
ORDER BY E.EMP_NO

아이디어

• 각 사원의 총 평가점수는 1분기+2분기의 평균으로 계산
  • AVG(SCORE)
• CASE문 을 활용하여 평가표에 맞는 평가 등급과 성과금 구하기

[baexxbin]

문제: 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

설명

• 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중
• 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고
• 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서
• 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성
• 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬

코드

SELECT A.CAR_ID, A.CAR_TYPE, 
       ROUND(A.DAILY_FEE*((100-C.DISCOUNT_RATE)/100)*30) AS FEE
FROM CAR_RENTAL_COMPANY_CAR A
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B
    ON A.CAR_ID = B.CAR_ID
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN C 
    ON A.CAR_TYPE = C.CAR_TYPE 
WHERE A.CAR_TYPE IN ('세단', 'SUV')
AND A.CAR_ID NOT IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE END_DATE > '2022-11-01' AND START_DATE < '2022-12-01'
)
AND C.DURATION_TYPE = '30일 이상'
GROUP BY A.CAR_ID
HAVING FEE >= 500000 AND FEE < 2000000
ORDER BY FEE DESC, A.CAR_TYPE ASC, CAR_ID DESC

아이디어

• 문제 조건을 설정하기 위해 3개의 테이블 조인
• WHERE을 통해 조건 만족
  • 자동차 종류 : WHERE A.CAR_TYPE IN ('세단', 'SUV')
  • 대여 날짜 는 NOT IN을 통해 11월에 대여하는 자동차를 빼줌
  • 대여 기간 : C.DURATION_TYPE = '30일 이상'
• 할인을 적용한 대여 금액 계산
  • 기존 비용 * 할인된 비용 * 대여 일
• 정렬
  • 금액이 50만원 이상 200만원 미만인 자동차에 대해 정렬해야하므로, GROUP BY를 통해 자동차별로 금액을 묶어주고 그 후 정렬하는 것이기에 HAVING 이용

[KodaHye]

문제: 멸종위기의 대장균 찾기

설명

• ECOLI_DATA(ID, PARENT_ID, SIZE_OF_COLONY, DIFFERENTIATION_DATE, GENOTYPE)
• 각 세대별 자식이 없는 개체의 수와 세대 출력

코드

WITH recursive CTE AS (
    SELECT id, parent_id, 1 as GENERATION
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL
    
    UNION ALL
    
    SELECT b.id, b.parent_id, a.generation + 1
    FROM CTE a
    JOIN ECOLI_DATA b ON a.id = b.parent_id 
)

SELECT COUNT(a.ID) as COUNT, a.GENERATION
FROM CTE a
WHERE ID NOT IN (
    SELECT DISTINCT PARENT_ID
    FROM CTE
    WHERE PARENT_ID IS NOT NULL
)
GROUP BY a.GENERATION
ORDER BY a.GENERATION

아이디어

• RECURSIVE를 이용하여 PARENT_ID가 NULL인 대장균을 1세대로 기준을 둔 뒤, 각 대장균들에 대한 세대 구하기
• 자식이 없다는 것은 PARNET_ID에 없는 ID를 찾으라는 것과 같음 → WHERE ID NOT IN ...
• 세대를 기준으로 GROUP BY를 하고, 요구하는대로 결과 값 출력하기

복잡한 쿼리문은 아직도 어렵네요 ㅎㅁㅎ;,!!

[jewan100]

문제: 주문량이 많은 아이스크림들 조회하기

설명

• 7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문을 작성

코드

WITH TOTAL AS (
    SELECT
        *
    FROM
        JULY 
    UNION ALL
    SELECT
        *
    FROM
        FIRST_HALF
)

SELECT
    FLAVOR
FROM
    TOTAL
GROUP BY
    FLAVOR
ORDER BY
    SUM(TOTAL_ORDER) DESC
LIMIT 3

아이디어

• 7월의 아이스크림 총 주문량과 상반기 아이스크림 총 주문량 테이블을 UNION ALL
  • 중복된 값이 있을 수도 있으므로 UNION ALL을 사용해야 함
• 상위 3개만 출력하기 위해 LIIMIT 3을 사용

[jmxx219]

문제 : 상품을 구매한 회원 비율 구하기

설명

• 2021년에 가입한 전체 회원들 중 상품을 구매한 회원의 비율을 년, 월 별로 출력
  • 비율 = 2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수
  • 비율 출력은 소수점 두번째 자리에서 반올림
• 전체 결과는 년, 월을 기준으로 오름차순 정렬

코드

SELECT 
    YEAR(o.SALES_DATE) AS YEAR,
    MONTH(o.SALES_DATE) AS MONTH,
    COUNT(DISTINCT o.USER_ID) AS PURCHASED_USERS,
    ROUND(
        COUNT(DISTINCT o.USER_ID)/(SELECT COUNT(*) FROM USER_INFO WHERE YEAR(JOINED) = 2021)
        , 1) AS PUCHASED_RATIO
FROM ONLINE_SALE o
WHERE o.USER_ID IN 
    (
        SELECT u.USER_ID
        FROM USER_INFO u
        WHERE YEAR(u.JOINED) = 2021
    )
GROUP BY YEAR(o.SALES_DATE), MONTH(o.SALES_DATE)
ORDER BY YEAR, MONTH;

아이디어

• 서브쿼리로 2021년에 가입한 회원들의 USER_ID를 조회해오고, 여기서 포함되는 ONLINE_SALE 데이터만 조회
• 년, 월별로 출력을 위해 GROUP BY로 년, 월로 그룹화하고 정렬
• 비율 구하기
  • 2021년에 가입한 회원 중 상품을 구매한 회원수 = 비율을 구하기 위해 현재 테이블에서 USER_ID를 DISTINCT로 중복을 없애고 COUNT로 회원 수 조회
  • 2021년에 가입한 전체 회원 수 = 서브쿼리로 2021년에 가입한 회원 수 조회
  • ROUND 함수로 소수점 첫번째 자리까지 출력