■패스트캠퍼스 데이터 분석 부트캠프■ 9주차 #SQL 중급 문법/코딩테스트 연습

📌합집합
A UNION B - 동일한 값은 제외
A UNION ALL B - 동일한 값도 포함

(예시)
SELECT
FROM 

UNION

SELECT
FROM

ORDER BY가 붙으면 > (A쿼리에서 가져온 컬럼으로 사용)


📌교집합
교집합 - JOIN 으로 사용, AND로 ON뒤에 공통 컬럼 다 나열

📌차집합 - LEFT,RIGHT JOIN
AND로 모든 컬럼 나열 후
WHERE B.name IS NULL <-하면 A테이블에만 있는거 보여줌


📌★쿼리문 순서
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

*GROUP BY 제외하고 서브쿼리문 활용 가능
*서브쿼리는 반드시 괄호 안에 있어야함
*서브쿼리에는 ;을 붙이지 않아도 됨
*INSERT,UPDATE,DELETE문에도 사용가능

📌SELECT절의 서브쿼리
*SELECT절의 서브 쿼리는 반드시 결과값이 하나의 '값'이어야함

📌FROM절의 서브쿼리
*서브쿼리로 만든 테이블은 만드시 별명을 가져야함
*FROM절의 서브 쿼리는 반드시 결과값이 하나의 ;테이블'이어야함

📌WHERE절의 서브쿼리
*WHERE절의 서브 쿼리는 반드시 결과값이 하나의 '컬럼'이어야함
*하나의 컬럼에 여러개 값 존재할 수 있음
*연산자와 함께 사용
- 비교연산자 (=,!=,>,<)
- 주요 연산자 (IN, ALL,ANY,EXISTS)
**비교연산자만 사용시, WHERE절의 서브쿼리는 반드시 결과값이 '하나의값'이어야함
**주요연산자만 사용시, WHERE절의 서브쿼리는 반드시 결과값이 '하나의컬럼'이어야함
**EXISTS는 단독으로 사용하며 결과값이 여러 컬럼이어도 됨

 

---

 

📌코딩테스트 연습 (sakila 데이터 활용)

 

-- 연습문제1
-- category 테이블에서 "Comedy", "Sports", "Family" 카테고리의 category_id 알아내기 (category_id 와 카테고리
-- 명 출력하기)
SELECT name, category_id
FROM category
WHERE name IN('Comedy','Sports','Family');


-- 연습문제2
-- film_category 테이블에서 영화 아이디(film_id)가 2 인 영화의 카테고리 ID 알아내기
SELECT film_id, category_id
FROM film_category
WHERE film_id = 2;

-- 연습문제3 
-- film_category 테이블에서 카테고리 ID별 영화 수 알아내기
SELECT category_id, COUNT(*) 
FROM film_category
GROUP BY category_id; #시바 이건 왜 안되는데'''


-- 연습문제4
-- 카테고리가 Comedy 인 영화 수 알아내기 (JOIN, 서브쿼리 각각 작성)
-- (category 테이블에는 카테고리 이름과 category_id, film_category 테이블에는 category_id와 각 영화 id가 있음)

SELECT COUNT(*)
FROM film_category FA
JOIN category C
ON FA.category_id = C.category_id
WHERE C.name = 'Comedy';

SELECT COUNT(*)
FROM film_category F, category C
WHERE F.category_id = C.category_id
AND C.name = 'Comedy';

SELECT COUNT(*)
FROM film_category
WHERE category_id IN (
SELECT category_id FROM category WHERE name = 'Comedy'
);


-- 연습문제5
-- Comedy, Sports, Family 각각의 카테고리별 영화 수 알아내기 (JOIN 사용하기)
-- (category 테이블에는 카테고리 이름과 category_id, film_category 테이블에는 category_id와 각 영화 id가 있음)
SELECT name, COUNT(*)
FROM category C
JOIN film_category FA
ON C.category_id = FA.category_id
WHERE name IN ('Comedy', 'Sports', 'Family')
GROUP BY C.category_id;



-- 연습문제6 (HAVING 문법을 사용해서 작성해보세요!)
-- 카테고리에 포함되는 각각의 영화 수가 70 이상인 카테고리명을 출력하기
SELECT name, COUNT(*)
FROM category C
JOIN film_category FA
ON C.category_id = FA.category_id
GROUP BY C.category_id
HAVING COUNT(*) >=70;


-- 연습문제7
-- 각 카테고리에 포함된 영화들의 렌탈 횟수 구해보기 (각 카테고리별에 포함된 영화들의 총 렌탈 횟수와 각 카테고리명을
-- 출력하는 것이 최종 목표)
-- - rental 테이블에 렌탈 기록이 있음
-- - inventory 테이블에 물품현황과 해당 물품(DVD)에 들어 있는 영화 아이디가 있음
-- - film_category 테이블에 영화 아이디에 매칭되는 카테고리 아이디가 있음
-- - category 테이블에 카테고리 아이디에 매칭되는 카테고리명이 있음
SELECT name, COUNT(*)
FROM rental R
JOIN inventory I ON I.inventory_id = R.inventory_id
JOIN film_category FC ON FC.film_id = I.film_id
JOIN category C ON C.category_id = FC.category_id
GROUP BY C.category_id
ORDER BY COUNT(*) DESC;

-- 연습문제8
-- "Comedy", "Sports", "Family" 카테고리에 포함되는 영화들의 렌탈 횟수 출력하기 (카테코리명, 렌탈 횟수)
-- - rental 테이블에 렌탈 기록이 있음
-- - inventory 테이블에 물품현황과 해당 물품(DVD)에 들어 있는 영화 아이디가 있음
-- - film_category 테이블에 영화 아이디에 매칭되는 카테고리 아이디가 있음
-- - category 테이블에 카테고리 아이디에 매칭되는 카테고리명이 있음
SELECT name, COUNT(*)
FROM rental R
JOIN inventory I ON I.inventory_id = R.inventory_id
JOIN film_category FC ON FC.film_id = I.film_id
JOIN category C ON C.category_id = FC.category_id
WHERE name IN ("Comedy", "Sports", "Family")
GROUP BY C.category_id
ORDER BY COUNT(*) DESC;

-- 연습문제9
-- 카테고리가 Comedy 인 데이터의 렌탈 횟수 출력하기 (서브쿼리 문법으로 작성해보세요)
SELECT COUNT(*)
FROM rental R
WHERE inventory_id IN (
SELECT inventory_id FROM inventory I WHERE film_id IN (
SELECT film_id FROM film_category FA WHERE category_id IN (
SELECT category_id FROM category C WHERE name = 'Comedy'
            )
)
    );



-- 연습문제10
-- 카테고리가 Comedy 인 데이터의 영화 갯수 출력하기 (JOIN 문법으로 작성해보세요)
SELECT COUNT(*)
FROM film_category FA
JOIN category C ON FA.category_id = C.category_id
WHERE name = 'Comedy';


-- 연습문제11
-- 카테고리가 Comedy 인 데이터의 영화 갯수 출력하기 (서브쿼리 문법으로 작성해보세요)
SELECT COUNT(*)
FROM film_category
WHERE category_id IN (
SELECT category_id FROM category WHERE name = 'Comedy'
    );


-- 연습문제12
-- address 테이블에는 address_id 가 있지만, customer 테이블에는 없는 데이터의 갯수 출력하기 (RIGHT JOIN 문법
-- 으로 작성해보세요!)
SELECT COUNT(*)
FROM customer C
RIGHT JOIN address A ON C.address_id = A.address_id
WHERE C.address_id IS NULL; 

 

 

-- 연습문제13 (실전)
-- 캐나다 고객에게 이메일 마케팅 캠페인을 진행하고자 합니다. 캐나다 고객의 이름과, email 주소 리스트를 뽑아주세요
SELECT CONCAT(first_name, ' ', last_name), email
FROM customer
WHERE address_id IN (
SELECT address_id FROM address WHERE city_id IN (
SELECT city_id FROM city WHERE country_id IN (
SELECT country_id FROM country WHERE country = 'canada'
            )
)
);

SELECT first_name, last_name, email
FROM customer C
JOIN address A ON A.address_id = C.address_id
JOIN city CI ON CI.city_id = A.city_id
JOIN country CO ON CO.country_id = CI.country_id
WHERE CO.country = "Canada";

USE sakila;
-- 연습문제14 (실전)
-- 젊은 가족 사이에서 매출이 저조해서, 모든 가족 영화를 홍보 대상으로 삼으려고 합니다. 가족 영화로 분류된 모든 영화
-- 리스트를 뽑아주세요
SELECT title
FROM film F
JOIN film_category FC ON FC.film_id = F.film_id
JOIN category C ON C.category_id = FC.category_id
WHERE name = "Family";


-- 연습문제15 (실전) >> 너무 어렵다
-- 가장 자주 대여하는 영화 리스트를 참고로 보고 싶습니다. 가장 자주 대여하는 영화 순으로 100개만 뽑아주세요
-- title (영화제목) 과 Rentals (렌탈 횟수) 로 보고 싶습니다.

SELECT title, COUNT(*) AS Rentals
FROM rental R 
JOIN inventory I ON R.inventory_id = I.inventory_id
JOIN film F ON F.film_id = I.film_id
GROUP BY F.film_id
ORDER BY COUNT(*) DESC
LIMIT 100;


-- 연습문제16 (실전) <- 난 못했었어..
-- 각 스토어별로 매출을 확인하고 싶습니다. 관련 데이터를 출력해주세요
-- 스토어가 위치한 '도시, 국가' 를 Store 항목으로, 스토어 ID 를 Store ID 로, 각 스토어별 총 매출을 'Total Sales' 항목으
-- 로 출력해주세요
SELECT 
STO.store_id AS 'Store_ID',
    CONCAT(C.country,',', CT.city) AS 'Store',
    SUM(P.amount) AS 'Total Sales'
FROM payment P
JOIN staff STA ON STA.staff_id = P.staff_id
JOIN store STO ON STO.store_id = STA.store_id
JOIN address A ON STO.address_id = A.address_id
JOIN city CT ON CT.city_id = A.city_id
JOIN country C ON C.country_id = CT.country_id
GROUP BY STO.store_id;
# 순서가 달라도 되나?



-- 연습문제17 (실전)
-- 각 스토어의 스토어 ID, 도시, 및 국가를 알고싶습니다. 관련 데이터를 출력해주세요
-- store_id, city, country 로 보고 싶습니다.
SELECT S.store_id, C.country, CT.city
FROM store S
JOIN address A ON S.address_id = A.address_id
JOIN city CT ON CT.city_id = A.city_id
JOIN country C ON C.country_id = CT.country_id;


-- 연습문제18 (실전)
-- 가장 렌탈비용을 많이 지불한 상위 10명에게 선물을 배송하고자 합니다
-- 가장 렌탈비용을 많이 지불한 상위 10명의 주소(address)와 이메일, 그리고 각 고객당 총 지불 비용을 출력해주세요

SELECT A.address, C.email, SUM(P.amount)
FROM payment P 
JOIN customer C ON P.customer_id = C.customer_id
JOIN address A ON C. address_id = A.address_id
GROUP BY P.customer_id
ORDER BY SUM(amount) DESC
LIMIT 10;