23.12.20

[오늘 할 일]

1. 코드카타 5문제 풀기

2. SQLD 자격증 대비반 내용 복습 (1,2,3,4주차)

+) 문득 생각난 마크다운 (기초) 학습

 

[내용 정리]

1. SQL 코드카타 문제 (5문제)

= 조인 & 서브쿼리
SELECT A.REST_ID, A.REST_NAME, A.FOOD_TYPE, A.FAVORITES, A.ADDRESS, B.SCORE
FROM REST_INFO A, (SELECT REST_ID, ROUND(AVG(REVIEW_SCORE),2) SCORE
                   FROM REST_REVIEW
                   GROUP BY REST_ID) B
WHERE 1=1
      AND A.REST_ID = B.REST_ID
      AND A.ADDRESS LIKE '서울%'
ORDER BY B.SCORE DESC, A.FAVORITES DESC

-- (ORACLE)
-- 테이블 조회할 때, 새로운 칼럼 추가할 때 기존 칼럼에는 테이블 ALIAS를 붙여줘야한다(?)
SELECT HISTORY_ID,
        CAR_ID, 
        TO_CHAR(START_DATE,'YYYY-MM-DD') START_DATE,
        TO_CHAR(END_DATE,'YYYY-MM-DD') END_DATE,
        CASE WHEN DIFF_DATE >= 30 THEN '장기 대여' ELSE '단기 대여' END RENT_TYPE
FROM (SELECT A.*, A.END_DATE - A.START_DATE + 1 AS DIFF_DATE
      FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY A
      WHERE TO_CHAR(START_DATE,'YYYYMM') = '202209'
      ) 
ORDER BY HISTORY_ID DESC
-- GROUP과 HAVING 사용 이해
SELECT CAR_ID, ROUND(AVG((A.END_DATE - A.START_DATE + 1)),1) AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY A
GROUP BY CAR_ID
HAVING AVG((A.END_DATE - A.START_DATE + 1)) >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC

-- 서브쿼리 활용
SELECT A.*
FROM PLACES A, (SELECT HOST_ID, COUNT(HOST_ID) CT_HOST
                FROM PLACES
                GROUP BY HOST_ID
               HAVING COUNT(HOST_ID) >=2) B
WHERE 1=1
      AND A.HOST_ID = B.HOST_ID
-- 서브쿼리(WHERE 절 활용)을 배우고
SELECT *
FROM PLACES
WHERE HOST_ID IN (SELECT HOST_ID
                 FROM PLACES
                 GROUP BY HOST_ID
                 HAVING COUNT(*) > 1)
ORDER BY ID

-- 55번 문제)
-- 1) 서브쿼리를 활용해서 푼 문제
SELECT A.CART_ID
FROM (SELECT CART_ID, NAME
      FROM CART_PRODUCTS
     WHERE NAME = 'Milk') A,
      (SELECT CART_ID, NAME
       FROM CART_PRODUCTS
       WHERE NAME = 'Yogurt') B
WHERE A.CART_ID = B.CART_ID
ORDER BY A.CART_ID

-- 2) LISTAGG(oracle) 활용해서 푼 문제
SELECT CART_ID
FROM CART_PRODUCTS
GROUP BY CART_ID
HAVING LISTAGG(NAME, '') WITHIN GROUP(ORDER BY name) LIKE '%Milk%Yogurt%'
ORDER BY CART_ID

-- 3) COUNT 활용해서 푼 문제
SELECT CART_ID
FROM CART_PRODUCTS
WHERE 1=1
AND NAME in ('Yogurt', 'Milk')
GROUP BY CART_ID
HAVING COUNT(DISTINCT NAME) >= 2
ORDER BY CART_ID