[강의명]
패스트캠퍼스 SQL 100제 + 자격증 완성 온라인 완주반
[수강목록]
조건 연산자, WITH문, 트랜잭션 - 05. WITH문의 활용
생각대로 SQL - 9
[강의내용]
조건 연산자, WITH문, 트랜잭션 - 05. WITH문의 활용
WITH문을 활용함으로써 SELECT문의 결과를 임시집합으로 저장해놓고
SQL문에서 마치 테이블처럼 해당 집합을 불러올 수 있다.
[실습]
WITH문으로 영화시간에 대한 구간을 나눠 영화를 구분해보자
WITH TMP1 AS (
SELECT film_id, title,
CASE WHEN length <30 THEN 'SHORT'
WHEN length >= 30 AND length < 90 THEN 'MEDIUM'
WHEN length >= 90 THEN 'LONG'
END length
FROM film
)
SELECT *
FROM TMP1
WHERE length = 'LONG';
생각대로 SQL - 9
문제2번) 영화 시간 유형 (length_type)에 대한 영화 수를 구하세요.
영화 상영 시간 유형의 정의는 다음과 같습니다.
영화 길이 (length) 은 60분 이하 short , 61분 이상 120분 이하 middle , 121 분이상 long 으로 한다.
film 테이블의 모든 영화에 대해 상영시간에 따른 등급을 지정하고, 각 등급의 수를 센다.
[내 풀이]
WITH문 대신 CASE WHEN을 사용하면 간결하게 나타낼 수 있다.
SELECT t1.length_type, count(*)
FROM (
SELECT film_id,
CASE WHEN length >= 121 THEN 'long'
WHEN length BETWEEN 61 AND 120 THEN 'middle'
WHEN length <= 60 THEN 'short'
ELSE '' END AS length_type
FROM film
) AS t1
GROUP BY t1.length_type;
[모범답안]
WITH문을 활용하면 아래와 같이 나타낼 수 있다.
WITH tbl AS (
SELECT 0 AS chk1, 60 AS chk2, 'short' AS length_flag UNION ALL
SELECT 61 AS chk1, 120 AS chk2, 'middle' AS length_flag UNION ALL
SELECT 121 AS chk1, 999 AS chk2, 'long' AS length_flag
)
SELECT length_flag, count(film_id)
FROM (
SELECT f.film_id, f.length, tbl.length_flag
FROM film f
LEFT JOIN tbl ON f.length BETWEEN tbl.chk1 AND tbl.chk2
) AS db
GROUP BY length_flag;
WITH문에 작성한 tbl 테이블을 JOIN할때 ON절에 BETWEEN을 사용할 수 있다.
f.length가 tbl.chk1 ~ tbl.chk2 구간에 걸리는 값이 존재하면 JOIN된다.
결과는 동일하다.
문제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문을 사용했지만, 일반적인 SELECT문을 사용할 수도 있다.
WITH tmp1 AS (
SELECT film_id, title,
CASE WHEN rating = 'G' THEN 'General Audiences'
WHEN rating = 'PG' THEN 'Parental Guidance Suggested'
WHEN rating = 'PG-13' THEN 'Parents Strongly Cautioned'
WHEN rating = 'R' THEN 'Restricted'
WHEN rating = 'NC-17' THEN 'No One 17 and Under Admitted'
ELSE '' END AS rating_detail_en,
CASE WHEN rating = 'G' THEN '모든 연령대 시청가능'
WHEN rating = 'PG' THEN '모든 연령대 시청가능하나, 부모의 지도가 필요'
WHEN rating = 'PG-13' THEN '13세 미만의 아동에게 부적절 할 수 있으며, 부모의 주의를 요함'
WHEN rating = 'R' THEN '17세 또는 그이상의 성인'
WHEN rating = 'NC-17' THEN '17세 이하 시청 불가'
ELSE '' END AS rating_detail_kr
FROM film
)
SELECT *
FROM tmp1;
[모범답안]
WITH tbl AS (
SELECT 'G' AS rating, 'General Audiences' AS eng_text, '모든 연령대 시청가능' AS kor_text UNION ALL
SELECT 'PG' AS rating, 'Parental Guidance Suggested' AS eng_text, '모든 연령대 시청가능하나, 부모의 지도가 필요' AS kor_text UNION ALL
SELECT 'PG-13' AS rating, 'Parents Strongly Cautioned' AS eng_text, '13세 미만의 아동에게 부적절 할 수 있으며, 부모의 주의를 요함' AS kor_text UNION ALL
SELECT 'R' AS rating, 'Restricted' AS eng_text, '17세 또는 그이상의 성인' AS kor_text UNION ALL
SELECT 'NC-17' AS rating, 'No One 17 and Under Admitted' AS eng_text, '17세 이하 시청 불가' AS kor_text
)
SELECT f.film_id, f.rating, tbl.eng_text, tbl.kor_text
FROM film f
LEFT JOIN tbl ON CAST(f.rating AS varchar) = tbl.rating;
f.rating에 CAST( AS VARCHAR)를 걸어주지 않으면 에러가 발생한다.
tbl.rating은 VARCHAR인데 f.rating은 그냥 CHAR이기 때문으로 추측된다.
결과는 동일하다.
문제5번) 고객이름별로, flag를 붙여서 보여주세요.
- 고객의 first_name 이름의 첫번째 글자가, A, B, C 에 해당하는 사람은 각 A, B, C로 flag를 붙여주시고
A, B, C에 해당하지 않는 인원에 대해서는 Others라는 flag로 붙여주세요.
[내 풀이]
LEFT 함수를 통해 문자열 데이터의 일정 부분만 추출할 수 있다.
LEFT(컬럼명, 1)은 컬럼에서 왼쪽에서 첫번째 문자열만 추출한다.
마찬가지로 RIGHT()도 존재한다.
SELECT first_name, last_name,
CASE WHEN LEFT(first_name, 1) = 'A' THEN 'A'
WHEN LEFT(first_name, 1) = 'B' THEN 'B'
WHEN LEFT(first_name, 1) = 'C' THEN 'C'
ELSE 'Others' END AS name_flag
FROM customer;
[모범답안]
모범답안에서는 SUBSTRING 함수를 사용한다.
SUBSTRING(컬럼명, 시작할 지점, 종료할 지점)을 입력하면, 문자열 데이터의 일부분을 추출할 수 있다.
아래의 쿼리에서는 substring(c.first_name, 1, 1)을 통해 A, B, C 등을 추출한다.
또한 COALESCE함수를 통해 NULL값에 Others를 넣어주는 역할을 수행한다.
COALESCE(컬럼1, 컬럼1이 NULL이면 대신 넣을 단어)
WITH tbl AS (
SELECT 'A' chk1, 'A' flag UNION ALL
SELECT 'B' chk1, 'B' flag UNION ALL
SELECT 'C' chk1, 'C' flag
)
SELECT customer_id, first_name, COALESCE(tbl.flag, 'Others') flag
FROM customer c
LEFT JOIN tbl ON substring(c.first_name, 1, 1) = tbl.chk1
결과는 동일하다.
문제8번) Rental 테이블을 기준으로, 회수일자에 대한 Quater 분기를 함께 표기해주세요.
예를 들어, 1~3월은 Q1로 표시한다.
EXTRACT함수를 통해 DATE타입 데이터에서 특정 시간을 추출한다.
YEAR , MONTH , DAY , HOUR , MINUTE , SECOND 등의 조건을 사용할 수 있으며 조건 뒤에 FROM을 명시해야 한다.
WITH tmp1 AS (
SELECT rental_id, return_date,
CASE WHEN EXTRACT(MONTH FROM date(return_date)) BETWEEN 1 AND 3 THEN 'Q1'
WHEN EXTRACT(MONTH FROM date(return_date)) BETWEEN 4 AND 6 THEN 'Q2'
WHEN EXTRACT(MONTH FROM date(return_date)) BETWEEN 7 AND 9 THEN 'Q3'
WHEN EXTRACT(MONTH FROM date(return_date)) BETWEEN 10 AND 12 THEN 'Q4'
ELSE '' END AS quarter_type
FROM rental
)
SELECT *
FROM tmp1;
문제10번) 직원의 현재 패스워드에 대해서, 새로이 패스워드를 지정하려고 합니다.
직원1의 새로운 패스워드는 12345, 직원2의 새로운 패스워드는 54321입니다.
해당의 경우, 직원별로 과거 패스워드와 현재 새로이 업데이트할 패스워드를
함께 보여주세요.
WITH문에 SELECT만 기재해도 새로운 테이블을 만드는 것처럼 사용할 수 있다.
WITH new_pass AS (
SELECT 1 AS staff_id, '12345' AS new_pwd UNION ALL
SELECT 2 AS staff_id, '54321' AS new_pwd
)
SELECT s.staff_id, s.PASSWORD AS origin_pwd, n.new_pwd
FROM staff s
JOIN new_pass n ON s.staff_id = n.staff_id
