WITH문의 활용
WITH문을 활용함으로써 SELECT문의 결과를 임시 집합으로 저장해두고 SQL문에서 마치 테이블 처럼 해당 집합을 불러올수 있다.
WITH문 실습
SELECT f.film_id
, f.title
, (CASE
WHEN LENGTH < 30 THEN 'SHORT'
WHEN LENGTH >= 30 AND LENGTH < 90 THEN 'MEDIUM'
WHEN LENGTH > 90 THEN 'LONG'
END) LENGTH
FROM film f;
영화의 상영시간별로 SHORT, MEDIUN, LONG으로 나누고 있다.
이 결과를 가지고 하나의 테이블처럼 사용할 수는 없을까?
그러한 기능이 WITH문이다.
WITH TMP1 AS (
SELECT f.film_id
, f.title
, (CASE
WHEN LENGTH < 30 THEN 'SHORT'
WHEN LENGTH >= 30 AND LENGTH < 90 THEN 'MEDIUM'
WHEN LENGTH > 90 THEN 'LONG'
END) LENGTH
FROM film f
)
SELECT * FROM TMP1;
그럼 이걸 실사용에서는 어떻게 사용하는지 실습 해보자.
실습
길이가 LONG인 자료들을 보여주어라
WITH TMP1 AS (
SELECT f.film_id
, f.title
, (CASE
WHEN LENGTH < 30 THEN 'SHORT'
WHEN LENGTH >= 30 AND LENGTH < 90 THEN 'MEDIUM'
WHEN LENGTH > 90 THEN 'LONG'
END) LENGTH
FROM film f
)
SELECT * FROM TMP1 WHERE LENGTH = 'LONG';
with문을 이용해서 해당 집합을 TMP1으로 지정하고 아래 SELECT문에서 TMP1을 조회하였다.
TMP1 집합에서 상영시간 구분이 LONG인 집합을 출력하였다.
생각대로 SQL - 9
문제1번) dvd 대여를 제일 많이한 고객 이름은? (with 문 활용)
WITH MC AS(
SELECT r.customer_id
FROM rental r
GROUP BY customer_id
ORDER BY count(*) DESC
LIMIT 1
)
SELECT c.first_name, c.last_name
FROM customer c, MC
WHERE c.customer_id = MC.customer_id문제2번) 영화 시간 유형 (length_type)에 대한 영화 수를 구하세요.
영화 상영 시간 유형의 정의는 다음과 같습니다.
영화 길이 (length) 은 60분 이하 short , 61분 이상 120분 이하 middle , 121 분이상 long 으로 한다.
WITH FLT AS (
SELECT (CASE
WHEN f.length <=60 THEN 'SHORT'
WHEN f.length >=61 AND f.length <=120 THEN 'MIDDLE'
WHEN f.length >=121 THEN 'LONG'
END) FL
FROM film f
)
SELECT FL, count(*)
FROM FLT
GROUP BY FL;
문제3번) 약어로 표현되어 있는 영화등급(rating) 을 영문명, 한글명과 같이 표현해 주세요. (with 문 활용)
G ? General Audiences (모든 연령대 시청가능)
PG ? Parental Guidance Suggested. (모든 연령대 시청가능하나, 부모의 지도가 필요)
PG-13 ? Parents Strongly Cautioned (13세 미만의 아동에게 부적절 할 수 있으며, 부모의 주의를 요함)
R ? Restricted. (17세 또는 그이상의 성인)
NC-17 ? No One 17 and Under Admitted. (17세 이하 시청 불가)
WITH RATING AS (
SELECT DISTINCT rating
FROM film f
)SELECT rating
,(CASE
WHEN rating = 'G' THEN 'General Audiences (모든 연령대 시청가능)'
WHEN rating = 'PG' THEN 'Parental Guidance Suggested. (모든 연령대 시청가능하나, 부모의 지도가 필요)'
WHEN rating = 'PG-13' THEN 'Parents Strongly Cautioned (13세 미만의 아동에게 부적절 할 수 있으며, 부모의 주의를 요함)'
WHEN rating = 'R' THEN 'Restricted. (17세 또는 그이상의 성인)'
WHEN rating = 'NC-17' THEN 'No One 17 and Under Admitted. (17세 이하 시청 불가)'
END) AS rating_description
FROM rating
문제4번) 고객 등급별 고객 수를 구하세요. (대여 횟수에 따라 고객 등급을 나누고 조건은 아래와 같습니다.)
A 등급은 31회 이상
B 등급은 21회 이상 30회 이하
C 등급은 11회 이상 20회 이하
D 등급은 10회 이하
WITH cus_rating AS (
SELECT c.customer_id, (
CASE
WHEN count(*) < 10 THEN 'D'
WHEN count(*) <= 20 THEN 'C'
WHEN count(*) <= 30 THEN 'B'
ELSE 'A'
END) rating
FROM rental r
INNER JOIN customer c ON r.customer_id = c.customer_id
GROUP BY c.customer_id
)
SELECT rating, count(*)
FROM cus_rating
GROUP BY rating
이렇게 해놨는데 나는 D가 0임을 표현하고 싶다.
이럴 경우는 어떻게 하는가? 궁금하다.
문제5번) 고객 이름 별로 , flag 를 붙여서 보여주세요.
- 고객의 first_name 이름의 첫번째 글자가, A, B,C 에 해당 하는 사람은 각 A,B,C 로 flag 를 붙여주시고
A,B,C 에 해당하지 않는 인원에 대해서는 Others 라는 flag 로 붙여주세요.
SELECT c.first_name, c.last_name
, CASE
WHEN first_name LIKE 'A%' THEN 'A'
WHEN first_name LIKE 'B%' THEN 'B'
WHEN first_name LIKE 'C%' THEN 'C'
ELSE 'Others'
END AS flag
FROM customer c 
문제6번) payment 테이블을 기준으로, 2007년 1월~ 3월 까지의 결제일에 해당하며, staff2번 인원에게 결제를 진행한 결제건에 대해서는, Y 로
그 외에 대해서는 N 으로 표기해주세요. with 절을 이용해주세요.
WITH Ycon as(
SELECT p.payment_id AS yp
FROM payment p
WHERE p.payment_date BETWEEN '2007-01-01' AND '2007-03-31'
AND staff_id = 2
)
SELECT CASE WHEN yp ISNULL THEN 'N'
ELSE 'Y'
END
, p2.payment_date
, p2.staff_id
FROM payment p2
LEFT JOIN Ycon ON p2.payment_id = Ycon.yp
문제7번) Payement 테이블을 기준으로, 결제에 대한 Quarter 분기를 함께 표기해주세요.
with 절을 활용해서 풀이해주세요.
1~월의 경우 Q1
4~6월 의 경우 Q2
7~9월의 경우 Q3
10~12월의 경우 Q4
SELECT p.*
, (CASE
WHEN EXTRACT (MONTH FROM p.payment_date) BETWEEN 1 AND 3 THEN 'Q1'
WHEN EXTRACT (MONTH FROM p.payment_date) BETWEEN 4 AND 6 THEN 'Q2'
WHEN EXTRACT (MONTH FROM p.payment_date) BETWEEN 7 AND 9 THEN 'Q3'
WHEN EXTRACT (MONTH FROM p.payment_date) BETWEEN 10 AND 12 THEN 'Q4'
END) QUARTER
FROM payment p 
문제8번) Rental 테이블을 기준으로, 회수일자에 대한 Quater 분기를 함께 표기해주세요.
with 절을 활용해서 풀이해주세요.
1~월의 경우 Q1
4~6월 의 경우 Q2
7~9월의 경우 Q3
10~12월의 경우 Q4 로 함께 보여주세요.
WITH RQ AS (
SELECT r.rental_date , CASE WHEN EXTRACT (MONTH FROM r.return_date) < 4 THEN 'Q1'
WHEN EXTRACT (MONTH FROM r.return_date) < 7 THEN 'Q2'
WHEN EXTRACT (MONTH FROM r.return_date) < 10 THEN 'Q3'
else 'Q4'
END AS qu
FROM Rental r
)
SELECT *
FROM RQ9번) 직원이이 월별 대여를 진행 한 대여 갯수가 어떻게 되는 지 알려주세요.
대여 수량이 아래에 해당 하는 경우에 대해서, 각 flag 를 알려주세요 .
0~ 500개 의 경우 under_500
501~ 3000 개의 경우 under_3000
3001 ~ 99999 개의 경우 over_3001
WITH SPC as(
SELECT r.staff_id, count(*) AS cn
FROM rental r
GROUP BY r.staff_id
)
SELECT SPC.staff_id , cn
, CASE WHEN SPC.cn <= 500 THEN 'under_500'
WHEN SPC.cn <= 3000 THEN 'under_3000'
ELSE 'over_3001'
end
FROM SPC 
10번) 직원의 현재 패스워드에 대해서, 새로이 패스워드를 지정하려고 합니다.
직원1의 새로운 패스워드는 12345 , 직원2의 새로운 패스워드는 54321입니다.
해당의 경우, 직원별로 과거 패스워드와 현재 새로이 업데이트할 패스워드를
함께 보여주세요.
with 절을 활용하여 새로운 패스워드 정보를 저장 후 , 알려주세요.
WITH SP as(
SELECT s.staff_id
, CASE WHEN s.staff_id = 1 THEN '12345'
WHEN s.staff_id = 2 THEN '54321'
END AS new_password
, "password" AS prev_password
FROM staff s
)
SELECT *
FROM SP
