Date : 2023-08-04
Topic : SQL 함수, 코딩테스트 실습, 집합연산, SQL VIEW
Note
SQL 함수
문자열 함수
SELECT title, LENGTH(title) AS title_leng
FROM film LIMIT 10;
SELECT title, LOWER(title)
FROM film LIMIT 10;
SELECT CONCAT(first_name, ' ', last_name)
FROM actor LIMIT 10;
SELECT CONCAT(first_name, last_name, 'name', 'dave') -- comma로 연결 가능
FROM actor LIMIT 10;
SELECT SUBSTRING(description, 3, 10) -- 3번째부터 10글자씩 '부분 문자열' 추출
FROM film LIMIT 10;날짜/시간 함수
SELECT NOW() AS current_date_time; -- 현재 날짜와 시간 반환
SELECT CURDATE() AS 'current_date'; -- 현재 날짜 반환
SELECT CURTIME() AS 'current_time'; -- 현재 시간 반환
# 날짜에 간격을 추가 (시간 단위 변경 가능)
SELECT
rental_date,
DATE_ADD(rental_date, INTERVAL 7 DAY) -- rental_date에서 7일이 추가된
FROM rental LIMIT 10;
# 날짜에서 간격을 뺀 (시간 단위 변경 가능)
SELECT
rental_date,
DATE_SUB(rental_date, INTERVAL 7 MINUTE) -- rental_date에서 7분 뺀
FROM rental LIMIT 10;숫자 함수
SELECT ABS(-amount) FROM payment LIMIT 10; -- 절대값 예시 위해 (-) 붙임
SELECT amount, CEIL(amount) FROM payment LIMIT 10; -- 크거나 같은 가장 작은 정수값 (반대는 floor)
SELECT amount, ROUND(amount, 1) FROM payment LIMIT 10; -- 소수점 n자리까지 반올림
SELECT rental_duration, SQRT(rental_duration) FROM film LIMIT 10; -- 제곱근기본 연습문제
# film 테이블에서 영화 제목( title )의 길이가 15자인 영화들을 찾아주세요.
SELECT title, LENGTH(title) FROM film
WHERE LENGTH(title) = 15;
# actor 테이블에서 첫 번째 이름( first_name )이 소문자로 'john'인 배우들의 전체 이름을 대문자로 출력해주세요.
SELECT UPPER(CONCAT(first_name, ' ',last_name)) AS name -- 함수는 중첩 가능
FROM actor
WHERE lower(first_name) = 'john';
# film 테이블에서 description 의 3-6번째 'Action'인 영화의 제목을 찾아주세요.
SELECT title, SUBSTRING(description, 3, 6) AS desc_action
FROM film
WHERE SUBSTRING(description, 3, 6)='Action';
/* rental 테이블에서 대여 시작 날짜( rental_date )가 2023년 1월 1일 이후인 모든 대여에 대해,
예상 반납 날짜를 대여 날짜로부터 5일 뒤로 설정해주세요. */
SELECT
rental_date, -- 대여일
DATE_ADD(rental_date, INTERVAL 5 DAY) AS return_date5 -- 5일 뒤 반납
FROM rental
WHERE rental_date >= '2006-01-01';
# 현재 날짜가 2006년 2월 15일일 때, rental 테이블에서 7일 이내에 반납해야 하는 모든 대여 정보를 찾아주세요.
SELECT *
FROM rental
WHERE DATE_SUB('2006-02-15', INTERVAL 7 DAY) <= rental_date; -- 헷갈리니까 다시 풀고 해설 달기
# payment 테이블에서 결제 금액( amount )이 -10 이하인 모든 결제에 대해, 절대값을 계산하여 출력해주세요.
SELECT ABS(amount) AS abs_amount FROM payment
WHERE abs_amount <= -10;
# film 테이블에서 영화 길이( length )가 120분 이상인 모든 영화에 대해, 영화 길이의 제곱근을 계산해주세요.
SELECT SQRT(length) FROM film -- 제곱근 SQRT (square root)
WHERE length >= 120;
# payment 테이블에서 결제 금액( amount )을 소수점 첫 번째 자리에서 반올림하여 출력해주세요.
SELECT ROUND(amount, 0) FROM payment; -- n자리에서 반올림하면 n+1까지 구해야함SubQuery 중급
예제
# 평균 결제 금액보다 많은 결제를 한 고객을 찾아봅시다
SELECT first_name, last_name -- 조건에 맞는 고객 이름 출력
FROM customer
WHERE customer_id IN (
SELECT customer_id -- 보다 많은 고객(ID)
FROM payment
WHERE amount > (SELECT AVG(amount) FROM payment) -- 고객 평균 결제액 (4.201356)
);
# 평균 결제 횟수보다 많은 결제를 한 고객을 찾아봅시다
SELECT first_name, last_name FROM customer -- 4. 이름 출력
WHERE customer_id IN (
SELECT customer_id FROM payment -- 3. 평균 결제 횟수보다 보다 많은 고객
GROUP BY customer_id
HAVING COUNT(*) > (
SELECT AVG(cnt_payment) -- 2. 고객별 결제 횟수의 평균
FROM (
SELECT COUNT(*) AS cnt_payment -- 1. 고객별 결제 횟수
FROM payment
GROUP BY customer_id
) a -- 파생된 테이블은 반드시 별칭을 가져야함
)
);
# 가장 많은 결제를 한 고객을 찾아봅시다
SELECT first_name, last_name -- 가장 많이 주문한 고객 이름
FROM customer
WHERE customer_id = (
SELECT customer_id -- 4.최다 주문 고객ID
FROM (
SELECT customer_id, COUNT(*) AS cnt_payment -- 1.고객별 고객ID와 카운트
FROM payment
GROUP BY customer_id
) AS payment_counts
ORDER BY cnt_payment DESC -- 2.많이 주문한 고객순 정렬
LIMIT 1 -- 3.가장 많이 주문한 고객
)
/* 각 고객에 대해 자신이 결제한 평균 금액보다 큰 결제를 한 경우의 결제 정보를 찾아봅시다
상관 서브쿼리(Correlated Subquery) : 서브쿼리가 외부 쿼리의 변수를 참조 */
SELECT P.customer_id, P.amount, P.payment_date
FROM payment P
WHERE P.amount > ( -- 2.보다 큰 금액 결제한 경우
SELECT AVG(amount) -- 1.평균 결제액
FROM payment
WHERE customer_id = P.customer_id
);기본 연습문제
# film 테이블에서 평균 영화 길이( length )보다 상영시간이 긴 영화들의 제목( title )을 찾아주세요.
SELECT title FROM film
WHERE length > (SELECT AVG(length) FROM film);
# rental 테이블에서 평균 대여 횟수보다 많은 대여를 한 고객들의 이름( first_name , last_name )을 찾아주세요.
SELECT first_name, last_name FROM customer
WHERE customer_id IN (
SELECT customer_id FROM rental -- 4.고객ID
GROUP BY customer_id
HAVING COUNT(*) > ( -- 3.평균보다 많은 대여 횟수를 가진
SELECT AVG(rental_count) -- 2.렌트 횟수 평균
FROM (
SELECT COUNT(*) AS rental_count -- 1. 고객별 렌트 횟수
FROM rental
GROUP BY customer_id
) AS rental_counts
)
);
# 가장 많은 영화를 대여한 고객의 이름(first_name, last_name)을 찾아주세요.
SELECT c.first_name, c.last_name -- 5.고객 이름 출력
FROM customer c
JOIN (
SELECT customer_id, COUNT(*) AS rent_cnt -- 1.고객별 렌트 횟수 서브쿼리
FROM rental
GROUP BY customer_id
) r
ON c.customer_id = r.customer_id -- 2.파생 테이블 r과 customer 테이블 조인
ORDER BY r.rent_cnt DESC -- 3.렌트 횟수 내림차순
LIMIT 1; -- 4. 가장 렌트를 많이 한 고객
# 각 고객에 대해 자신이 대여한 평균 영화 길이( length )보다 긴 영화들의 제목( title )을 찾아주세요.
SELECT C.first_name, C.last_name, F.title -- 4.영화 제목 출력
FROM customer C
JOIN rental R ON R.customer_id = C.customer_id
JOIN inventory I ON I.inventory_id = R.inventory_id
JOIN film F ON F.film_id = I.film_id
WHERE F.length > ( -- 3.보다 긴 영화
SELECT AVG(F.length) -- 2.평균 영화 길이
FROM film F
JOIN inventory I ON I.film_id = F.film_id
JOIN rental R ON R.inventory_id = I.inventory_id
WHERE R.customer_id = C.customer_id -- 1.렌트한 고객과 고객ID가 같을 때
)복합 연습 문제
/* rental 과 inventory 테이블을 JOIN하고, film 테이블에 있는 replacement_cost 가 $20 이상인 영화를
대여한 고객의 이름을 찾으세요. 고객 이름은 소문자로 출력해주세요.*/
SELECT DISTINCT LOWER(CONCAT(first_name, ' ', last_name)) AS customer_name -- DISTINCT로 고객명 중복 제거
FROM rental r
JOIN inventory i ON i.inventory_id = r.inventory_id
JOIN film f ON f.film_id = i.film_id
JOIN customer c ON c.customer_id = r.customer_id
WHERE replacement_cost >= 20집합 연산
: 두 개 이상의 SELECT문의 결과를 결합하거나 비교 [UNION, UNION ALL, INTERSECT, EXCEPT]
- 집합 연산을 사용할 때는 SELECT 문이 동일한 수의 열을 선택
- 열은 동일한 순서로 있어야 함
- 해당 열은 호환 가능한 데이터 유형을 가져야 함
- 모든 데이터베이스 시스템이 INTERSECT 와 EXCEPT 를 지원하는 것은 아님
UNION: 두 개 이상 SELECT문 결합, ⭐중복행 제거, 열 순서가 같아야 함
SELECT film_id FROM film
UNION
SELECT film_id FROM film_category;
# film 또는 film_category 테이블에 있는 중복행을 제거하고 고유한 film_id 리스트를 반환UNION ALL: UNION과 같은 기능이지만 ⭐중복된 행을 포함
SELECT film_id FROM film
UNION ALL
SELECT film_id FROM film_category;
# film 및 film_category 테이블의 모든 film_id 리스트를 반환하며, 중복 값도 포함INTERSECT: 두 개 이상 SELECT문의 교집합을 반환(=모든 SELECT문의 공통적으로 있는 행)
SELECT film_id FROM film
INTERSECT
SELECT film_id FROM film_category;
# film 및 film_category 테이블 모두에 있는 film_id를 반환EXCEPT: 두 개 이상 SELECT문의 차집합을 반환(=첫 번째 결과 집합엔 있지만 두 번째 결과 집합엔 없는 행)
SELECT film_id FROM film
EXCEPT
SELECT film_id FROM film_category;
# film에는 있지만 film_category에는 없는 film_id를 반환SQL VIEW
: 실제 테이블을 기반으로 한 가상 테이블. 뷰를 사용하면 복잡한 쿼리를 단순화하고 데이터 특정 부분에만 접근 가능
VIEW 생성
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;VIEW 수정
CREATE OR REPLACE VIEW view_name AS -- OR REPLACE(뷰를 생성하거나 이미 존재하면 교체)
SELECT column1, column2, ...
FROM table_name
WHERE condition;VIEW 삭제
DROP VIEW view_name;예제
/* 1. ActorInfo라는 VIEW를 만드세요.
(actor 테이블에서 first_name 과 last_name 컬럼을 포함해야합니다. actor_id 가 50 미만인 배우만 포함) */
CREATE VIEW ActorInfo AS
SELECT first_name, last_name FROM actor
WHERE actor_id < 50;
/* 2. film 테이블에서 렌탈 비용이 $2.00보다 높은 영화에 대한 VIEW를 만들어 봅니다.
이 VIEW의 이름은 ExpensiveFilms 이고, title 과 rental_rate 컬럼만을 포함해야 합니다. */
CREATE VIEW ExpensiveFilms AS
SELECT title, rental_rate FROM film
WHERE rental_rate > 2.00;
/* 3. 이미 만든 VIEW인 ActorInfo 를 수정하여 actor_id 가 100 미만인 배우만 포함하도록 바꾸려면 어떻게 해야 할까요? */
CREATE OR REPLACE VIEW ActorInfo AS
SELECT first_name, last_name FROM actor
WHERE actor_id < 100;
/* 4. ExpensiveFilms VIEW를 삭제하려면 어떤 SQL 명령을 사용해야 할까요? */
DROP VIEW ExpensiveFilms;문자열, 숫자 함수는 자주 쓰이는 게 특정되는데 날짜 함수는 파생 문법도 많고 형태가 헷갈리고 종류가 너무 다양해서 익숙해지려면 많이 보고 자주 사용해봐야 겠다. (사실 현업에서 DATETYPE 컬럼은 자주 사용되지 않는다는 말씀을 하셨지만 그래도 사용해야 할 경우가 언제 생길지 모르니 항상 숙지하고 있을 것!) TIMESTAMPDIFF()는 모든 시간 형태를 사용할 수 있으니 꼭 기억해두기⭐ 그동안 JOIN 공부한다고 잠깐 서브쿼리 문제는 손에서 놨더니 조금만 난이도가 올라가면 금방 헷갈려진다. 서브쿼리 구조 자체는 이해할 수 있는데 코딩테스트처럼 문제를 보고 쿼리 작성하는 시간이 너무 오래 걸리니 연습 문제 꾸준히 복습하기✍️
'데이터 분석 학습일지🐣' 카테고리의 다른 글
| 37일차 - SQL 코딩테스트 연습 (0) | 2023.08.10 |
|---|---|
| 36일차 - SQL 코딩테스트 연습(sakila data) (0) | 2023.08.10 |
| 33일차 - SQL index, FOREIGN KEY, INNER JOIN, SubQuery (0) | 2023.08.03 |
| 31일차 - SQL 실습(sakila, world data) (0) | 2023.08.02 |
| 29일차 - SQL 실습 (0) | 2023.07.28 |
