[강의명]
패스트캠퍼스 SQL 100제 + 자격증 완성 온라인 완주반
[수강목록]
생각대로 SQL - 3
[강의내용]
생각대로 SQL - 3
문제10번) country가 china가 아닌 지역에 사는, 고객의 이름(first_name, last_name)과 , email, phonenumber, country, city 를 알려주세요
country 테이블을 추가로 조인하여 where절에서 not china를 설정해야 한다.
SELECT c.first_name, c.last_name, c.email, a.phone, co.country, ct.city
FROM customer c
JOIN address a ON c.address_id = a.address_id
JOIN city ct ON a.city_id = ct.city_id
JOIN country co ON ct.country_id = co.country_id
WHERE co.country <> 'China';
문제12번) Music 장르이면서, 영화길이가 60~180분 사이에 해당하는 영화의 title, description, length 를 알려주세요.
- 영화 길이가 짧은 순으로 정렬해서 알려주세요.
title, description, length 컬럼은 film테이블에 있다. category 테이블에서 category값이 music인 정보를 찾아야하는데, category 테이블에 도달하기 위해서는 중계 역할을 하는 film_category 테이블을 거쳐야 한다.
SELECT f.title, f.description, f.length
FROM film f
JOIN film_category fc ON f.film_id = fc.film_id
JOIN category c ON fc.category_id = c.category_id
WHERE c.name = 'Music'
AND f.length BETWEEN 60 AND 180
ORDER BY f.length;
문제13번) actor 테이블을 이용하여, 배우의 ID, 이름, 성 컬럼에 추가로 'Angels Life' 영화에 나온 영화 배우 여부를 Y , N 으로 컬럼을 추가 표기해주세요. 해당 컬럼은 angelslife_flag로 만들어주세요.
어려웠던 문제이므로 자세하게 들여다본다.
내가 작성한 답안은 (Y값이 잘 나오는지 검사를 위해 맨 밑에 ORDER BY절을 추가했음)
SELECT a.actor_id, a.first_name, a.last_name,
CASE WHEN f.title IN ('Angels Life') THEN 'Y'
ELSE 'N'
END AS angelslife_flag
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
JOIN film f ON fa.film_id = f.film_id
ORDER BY angelslife_flag desc;
중복된 값이 등장하는 오류가 발생했다.
이를 수정하기 위해 DISTINCT를 걸었으나...
Y값에 해당하는 배우들은 N값을 달고 또 등장하는 오류가 있다.
이러한 배경에는 배우들은 여러 작품을 촬영하므로, film 테이블을 조인하면서 여러 배우들이 테이블에 등장하게 되는 것에 있다.
그래서 모범답안을 참조했다.
SELECT a.actor_id, a.first_name, a.last_name,
CASE WHEN angels_actor.actor_id IS NOT NULL THEN 'Y'
ELSE 'N'
END AS angelslife_flag
FROM actor a
LEFT JOIN (SELECT f.film_id, f.title, fa.actor_id
FROM film f
JOIN film_actor fa ON f.film_id = fa.film_id
WHERE title = 'Angels Life'
) AS angels_actor ON a.actor_id = angels_actor.actor_id;
우선, angels_actor라고 이름붙인 서브쿼리를 통해 Angels Life에 출연한 배우들의 정보를 따로 추출하여 서브쿼리(angels_actor 테이블)에 넣었다.
여기에 나타나는 actor_id값을 통해 angelslife_flag에서 Y/N을 가려주면 된다.
LEFT JOIN을 사용하여 Angels Life에 출연하지 않은 배우들을 따로 NULL값으로 출력할 수 있다. NULL인 값은 CASE절을 통해 angelslife_flag로 나타내면 된다.
문제14번) 대여일자가 2005-06-01~ 14일에 해당하는 주문 중에서 , 직원의 이름(이름 성) = 'Mike Hillyer' 이거나
고객의 이름이 (이름 성) ='Gloria Cook' 에 해당 하는 rental 의 모든 정보를 알려주세요.
- 추가로 직원이름과, 고객이름에 대해서도 fullname 으로 구성해서 알려주세요.
rental 테이블에 staff 테이블과 customer 테이블을 조인하면 된다. rental_date는 년월일 + 시각까지 나와있기 때문에
DATE함수를 통해 년월일만으로 바꿔준다.
또한, '2005-06-01' 처럼 따옴표를 붙여줘야함에 주의 한다.
그리고, 직원의 이름(이름 성) = 'Mike Hillyer' 이거나 고객의 이름이 (이름 성) ='Gloria Cook' 이어야 하므로, A OR B를 괄호로 감싸주어야 한다.
SELECT r.*, s.first_name || ' ' || s.last_name AS fullname_staff,
c.first_name || ' ' || c.last_name AS fullname_customer
FROM rental r
JOIN staff s ON r.staff_id = s.staff_id
JOIN customer c ON r.customer_id = c.customer_id
WHERE date(r.rental_date) BETWEEN '2005-06-01' AND '2005-06-14'
and (s.first_name || ' ' || s.last_name = 'Mike Hillyer'
OR c.first_name || ' ' || c.last_name = 'Gloria Cook');
