[강의명]
패스트캠퍼스 SQL 100제 + 자격증 완성 온라인 완주반
[수강목록]
3주차 과제 풀이 (SQL 39~53번 문제)
[강의내용]
문제39번) 고객의 기본정보인 고객id, 이름, 성, 이메일과 함께 고객의 address, district, postal_code, phone을 함께 보여주세요.
고객id, 이름, 성, 이메일 컬럼은 customer 테이블에 존재한다. 그리고 address를 비롯한 나머지 컬럼들은 address 테이블에 존재한다.
다른 테이블을 한 쿼리에서 사용하기 위해 FROM뒤에 JOIN을 써서 추가할 테이블을 입력해준다.
그리고 ON 뒤에 두 테이블간의 공통 컬럼을 적어준다.
SELECT c.customer_id, c.first_name, c.last_name, a.address, a.district, a.postal_code, a.phone
FROM customer c
JOIN address a ON c.address_id = a.address_id;
이종 테이블의 정보가 함께 출력되는 것을 확인할 수 있다.
문제40번) 고객의 기본 정보인, 고객 id, 이름, 성, 이메일과 함께 고객의 address, district, postal_code, phone , city를 함께 알려주세요.
이번에는 총 3개의 테이블을 JOIN해본다. city 컬럼은 city 테이블에 있기 때문에 customer - address - city 순으로 JOIN해주면 된다.
customer - address : address_id컬럼으로 JOIN
address - city : city_id컬럼으로 JOIN
SELECT c.customer_id, c.first_name, c.last_name, a.address, a.district, a.postal_code, a.phone, c2.city
FROM customer c
JOIN address a ON c.address_id = a.address_id
JOIN city c2 ON a.city_id = c2.city_id;
문제41번) Lima City에 사는 고객의 이름과, 성, 이메일, phonenumber에 대해서 알려주세요.
문제2번에서 만든 DB에서 city값이 'Lima'인 항목을 출력하면 된다.
SELECT c.customer_id, c.first_name, c.last_name, c.email, a.phone
FROM customer c
JOIN address a ON c.address_id = a.address_id
JOIN city c2 ON a.city_id = c2.city_id
WHERE c2.city in ('Lima');
문제42번) rental 정보에 추가로, 고객의 이름과, 직원의 이름을 함께 보여주세요.
- 고객의 이름, 직원 이름은 이름과 성을 fullname 컬럼으로만들어서 직원이름/고객이름 2개의 컬럼으로 확인해주세요.
총 3개 테이블의 JOIN + 텍스트 결합을 묻는 문제이다.
참고로 SELECT절은 쿼리 상 마지막 단계 (HAVING 뒤)에서 실행되기 때문에, Alias를 사용할 수 있다.
그래서 r.*는 rental 테이블의 모든내용(* : asterisk) 을 나타낸다.
SELECT r.*, c.first_name || ' ' || c.last_name AS cust_fullname,
s.first_name || ' ' || s.last_name AS staff_fullname
FROM rental r
JOIN staff s ON r.staff_id = s.staff_id
JOIN customer c ON r.customer_id = c.customer_id;
문제43번) seth.hannon@sakilacustomer.org 이메일 주소를 가진 고객의 address, address2, postal_code, phone, city를 알려주세요.
문제3번에서 city = 'Lima'인 값을 조회한 것처럼, email = seth.hannon@sakilacustomer.org 인 값을 출력하면 된다.
SELECT a.address, a.address2, a.postal_code, a.phone, c2.city
FROM customer c
JOIN address a ON c.address_id = a.address_id
JOIN city c2 ON a.city_id = c2.city_id
WHERE c.email = 'seth.hannon@sakilacustomer.org';
문제44번) Jon Stephens 직원을 통해 dvd대여를 한 payment 기록 정보를 확인하려고 합니다.
- payment_id, 고객이름과 성, rental_id, amount, staff 이름과 성을 알려주세요.
5번과 마찬가지로, 테이블 JOIN을 통한 DB준비 + WHERE절 조건 입력을 통해 해결할 수 있다.
SELECT p.payment_id, c.customer_id, c.first_name, r.rental_id, p.amount, s.first_name, s.last_name
FROM rental r
JOIN staff s ON r.staff_id = s.staff_id
JOIN customer c ON r.customer_id = c.customer_id
JOIN payment p ON c.customer_id = p.customer_id
WHERE s.first_name = 'Jon';
문제45번) 배우가 출연하지 않는 영화의 film_id, title, release_year, rental_rate, length 를 알려주세요.
배우가 출연하지 않는 영화는 무엇을 의미할까?
film_id와 actor_id가 각각 매핑된 film_actor 테이블에 존재하지 않는 film이라고 할 수 있다.
아직 actor 정보가 film_actor 테이블에 업로드되지 않은 것이라 생각할 수 있다.
모든 영화들이 film_actor 테이블에 존재하는 것이 아니기 때문에, INNER JOIN이 아닌 LEFT OUTER JOIN을 사용한다.
(INNER 와 OUTER 는 생략가능하다)
SELECT f.film_id, f.title, f.release_year, f.rental_rate, f.length
FROM film f
LEFT JOIN film_actor fa ON f.film_id = fa.film_id
WHERE fa.actor_id IS NULL;
이를 통해 공통항목이 없는 데이터도 불러올 수 있다.
(INNER JOIN에서는 공통항목이 없는 데이터는 불러오지 않는다)
해당 영화들은 film_actor 테이블에 존재하지 않는 영화들임을 알 수 있다.
문제46번) store 상점 id별 주소 (address, address2, district) 와 해당 상점이 위치한 city 주소를 알려주세요.
store_id 별 주소 이므로, SELECT절에서 맨 앞에 store_id를 써주는 것을 유의하면 된다.
SELECT s.store_id, address, address2, district, city
FROM store s
JOIN address a ON s.address_id = a.address_id
JOIN city c ON a.city_id = c.city_id;
