[SQL] PostgreSQL 학습하기(+DVD Rental): 1

이전 내용

[SQL] 샘플 데이터로 PostgreSQL 연습하기

---

PostgreSQL 학습하기 - 기초 (feat. Chat GPT)

학습을 위한 샘플 데이터도 다운 받았겠다. 이제 실질적인 학습을 해보려 한다.
챗 GPT에게 dvdrental 데이터베이스에 기반한 PostgreSQL 학습용 질문 20개를 요청했다.

I downloaded DVDRENTAL data. Based on the data, please give me  20 tests for SQL for POSTGRESQL beginners.

해보겠습니다 선생님!

★ 자주 쓰는 SQL 문법 키워드 요약

기능	SQL 문법 예시
정렬	ORDER BY column DESC
조건 검색	WHERE column = 'value'
집계 함수	COUNT(), SUM(), AVG()
그룹화	GROUP BY column
조인	JOIN, LEFT JOIN, ON
상위 몇 개	LIMIT 5
날짜 조건	WHERE date BETWEEN '...

 

📅 주요 테이블 요약

테이블명	설명
customer	고객 정보
film	영화 정보
rental	대여 정보
inventory	영화 재고
payment	결제 정보
staff	직원 정보
store	지점 정보

 

📌 전체 문제

✅ PART 1. SQL 기본 (SELECT, WHERE, ORDER BY)
1. 모든 고객의 이름과 이메일을 출력하시오.
2. 20달러 이상의 결제를 한 payment 정보를 출력하시오.
3. 영화(film) 제목과 렌트 가격을 렌트 가격이 높은 순서로 정렬하시오.
4. 성(last_name)이 'Smith'인 고객의 이름과 이메일을 조회하시오.
5. 영화 제목이 'ACADEMY DINOSAUR'인 영화의 전체 정보를 조회하시오.

✅ PART 2. 집계함수 & GROUP BY
6. 고객(customer) 수는 총 몇 명인가요?
7. 고객을 도시(city)별로 나누고, 도시별 고객 수를 계산하시오.
8. 가장 많이 결제한 고객의 ID와 총 결제 금액을 구하시오.
9. 스태프(staff)별로 처리한 결제 금액의 합계를 구하시오.
10. 가장 비싼 영화 5편의 제목과 가격을 출력하시오.

✅ PART 3. JOIN 연습 문제
11. 고객 이름과 함께 그 사람이 소속된 도시 이름(city)을 출력하시오.
12. 각 고객이 대여(rental)한 영화 제목(film.title)을 모두 출력하시오.
13. 대여 횟수가 10번 이상인 고객의 ID와 이름을 출력하시오.
14. 가장 최근에 대여된 영화 제목과 대여 날짜를 출력하시오.
15. 2005년 7월에 결제(payment)된 내역을 출력하시오.

✅ PART 4. 응용 문제 (실무 감각)
16. 한 번도 결제를 하지 않은 고객의 이름을 조회하시오.
17. 고객별로 평균 결제 금액을 구하고, 가장 많이 결제한 고객부터 정렬하시오.
18. 영화별로 평균 렌트 요금을 구하고, 3달러 이상인 영화만 출력하시오.
19. 카테고리별(category) 영화 수를 출력하시오.
20. 두 명 이상의 직원이 같은 매장에서 근무하는 매장 번호(store_id)를 구하시오.

---

✅ PART 1. SQL 기본 (SELECT, WHERE, ORDER BY)

1. 모든 고객의 이름과 이메일을 출력하시오.

먼저, 고객 데이터는 customer 테이블에 있으니까, customer 테이블의 컬럼을 조회해본다.

\d customer;

 
이중 필요한 컬럼은

• first_name: 고객의 성
• last_name: 고객 이름
• email: 이메일 주소

이므로, 아래와 같은 구문을 입력하면

SELECT first_name, last_name, email FROM customer;

고객 (customer) 테이블의 이름과 이메일 정보만 출력된다.

---

2. 20달러 이상의 결제를 한 payment 정보를 출력하시오.

이번에는 결제 관련 정보가 있는 payment 테이블의 컬럼을 확인해 본다.

\d payment

 
해당 테이블의 정보도 확인해 본다.

SELECT * FROM payment;

 
내가 생각한 정답은 이러한데,

SELECT * FROM payment WHERE amount >= 20;

 
쿼리 결과문에 아무 정보도 안 뜬다.

 
혹시 잘못된 쿼리인가 싶어 10 달러 이상의 결제 정보를 조회해보니,

이렇게 10달러 이상의 결제 정보만 잘 뜬다.
혹시 모르니 해당 테이블에서 가격이 가장 높은 순으로 정렬하여 가장 큰 amount 값을 확인해 본다.

SELECT * FROM payment ORDER BY amount DESC;

 
11.99 달러가 가장 큰 값으로 20달러 이상의 결제 정보는 출력이 안 되는게 맞다.

---

3. 영화(film) 제목과 렌트 가격을 렌트 가격이 높은 순서로 정렬하시오.

이번에는 렌트 관련 문제라 rental 테이블에 있는 데이터인가 싶어 테이블 내의 컬럼을 확인해봤는데,

\d rental;

 
아래와 같이 영화 제목에 대한 정보가 없어 보인다.

 
따라서, 렌트 및 영화 전반적인 정보가 담긴 film 테이블을 조회해본다.

\d film;

GPT의 출제 의도에 의하면, 이 컬럼 중에 rental_rate 가 렌트 가격을 의미한다고 한다.

 
따라서 정답은

SELECT title, rental_rate FROM film ORDER BY rental_rate DESC;

---

4. 성(last_name)이 'Smith'인 고객의 이름과 이메일을 조회하시오.

고객 정보이기 때문에 customer 테이블에서 조회하면 된다.
단, last_name 이 'Smith'여야 하는데, 'Smith' 인건지(==), 'Smith'를 포함하는 건지 확인이 필요해 보인다.
 
필자가 처음에 작성한 답 (오답)은 아래와 같은데,

SELECT last_name, email FROM customer WHERE last_name == 'Smith';

 
이 답이 잘못된 이유는

대부분의 SQL 데이터베이스 시스템에서 두 값이 같은지 비교할 때는 등호(=) 하나만 사용한다. ==는 SQL 표준이 아니며, 주로 다른 프로그래밍 언어(예: Python, JavaScript, Java 등)에서 같음 비교에 사용되는 연산자이다.

 
따라서, '==' 에서 '='를 하나만 빼면

SELECT last_name, email FROM customer WHERE last_name = 'Smith';

 

★ 특정 문자를 포함하는 결과 출력하기 (와일드카드 문자)

SQL에서 특정 문자를 포함하는 값을 조회하려면, LIKE 연산자와 와일드카드 문자를 사용해야 한다.
가장 흔히 사용되는 와일드카드 문자는 다음과 같다:

• % (퍼센트): 0개 이상의 모든 문자 시퀀스를 나타낸다.
• _ (언더스코어): 정확히 1개의 모든 문자를 나타낸다.

LIKE 연산자 사용 예시
last_name 컬럼에 'Smith'라는 문자를 포함하는 모든 고객을 찾으려면 다음과 같이 쿼리를 작성한다.

SELECT last_name, email FROM customer WHERE last_name LIKE '%Smith%';

 
하지만 여전히 한 개만 출력되는 것을 보니, Smith라는 이름의 고객은 한 명 뿐 인듯 하다.

---

5. 영화 제목이 'ACADEMY DINOSAUR'인 영화의 전체 정보를 조회하시오.

이번에도 역시 film 테이블에서 조회하면 될 거 같다.

SELECT * FROM film WHERE title = 'ACADEMY DINOSAUR';

 
그런데 해당 영화가 없는지 하나도 안 뜬다.

 
혹시 몰라 각 단어의 첫글자만 대문자로 하고, 나머지 글자들은 소문자로 바꿔서 조회해보니

SELECT * FROM film WHERE title = 'Academy Dinosaur';

 
데이터가 잘 조회된다.

 

★ 대소문자 구분 없이 조회하기 (세가지 방법)

1. LOWER() 사용하기 (SQL 표준)
title 컬럼의 모든 값을 소문자로 변환한 다음, 소문자 'academy dinosaur'와 비교

SELECT * FROM film WHERE LOWER(title) = 'academy dinosaur';

 
2. UPPER() 사용하기 (SQL 표준)
title 컬럼의 모든 값을 대문자로 변환한 다음, 'ACADEMY DINOSAUR' 와 비교

 
3. ILIKE 사용 (PostgreSQL 전용)
PostgreSQL은 대소문자를 구분하지 않는 LIKE 연산자인 ILIKE를 제공

SELECT * FROM film WHERE UPPER(title) ILIKE 'ACADEMY DINOSAUR';

 

SQLD 2과목 SQL 기본 및 활용 - WHERE 절

---

다음 내용

[SQL] PostgreSQL 학습하기(+DVD Rental): 2