31일차 - SQL 실습(sakila, world data)

데이터 분석 학습일지🐣

31일차 - SQL 실습(sakila, world data)

boiled egg 2023. 8. 2. 15:29

Date : 2023-08-01

Topic : sakila, world data SQL 실습

Note

1. Sakila 데이터 실습 (DVD 가게 데이터!)

실제 현업에서 사용하는 데이터베이스에는 다양한 테이블이 있지만,
각 테이블에 대한 매우 친절하고, 상세한 설명이 없는 경우도 많습니다. 데이터를 보며 유추하기도 합니다.

👉테이블과 데이터 사이 관계를 보며 추측해보는 습관!

사용한 SELECT 문법
- LIMIT : 결과 중 일부만 데이터 가져오기
- COUNT : 결과 수 세기 (데이터 행의 수 세기)
- DISTINCT : 특정 컬럼값 출력시 중복된 값을 출력하지 않음
- SUM, AVG, MAX, MIN : 집계 함수, 보통 GROUP BY와 함께 사용
- GROUP BY : 말 그대로 그룹핑, 문제에서 보통 '~별(로)', '~ 종류에 따른' 이런 문장 구조
- ORDER BY : 데이터 정렬, DESC (내림차순) | ASC (오름차순, default값)
- AS : 별칭 부여, count, round 등으로 복잡해진 컬럼명을 가독성 좋게 변경
  - ex) COUNT(*) AS total → count all을 total로 표시
⭐특정 테이블의 컬럼과 컬럼값 확인을 위해 LIMIT 1 을 많이 사용함

SELECT * FROM city LIMIT 1

/* 영화 테이블(film) 에서 영화 등급 종류 알아내기
도메인 : 각 컬럼값 이해를 위한 배경 지식 ex) 각 영화의 영화 등급은 rating 컬럼에 들어 있음 */
SELECT distinct rating FROM film;

/* 영화 렌탈 테이블(rental) 에서 10개 데이터만 출력하기
rental 테이블은 DVD 를 언제, 누가 빌려갔고, 반환했는지에 대한 정보 */
SELECT * FROM rental LIMIT 10;

/* 영화 렌탈 테이블(rental) 에서 inventory_id 가 367 이고, staff_id가 1인 로우(Row) 전체 출력
inventory_id 는 빌려간 DVD 의 ID 를 의미 */
SELECT * FROM rental
WHERE inventory_id=367 AND staff_id=1;

/* payment 테이블에서 렌탈비용 합계, 평균, 최대값, 최소값 구하기
payment 테이블은 렌탈 비용을 포함한 정보 */
SELECT sum(amount) AS SUM_price, 
	   avg(amount) AS AVG_price, 
       max(amount) AS MAX_price, 
       min(amount) AS MIN_price
FROM payment;

SELECT rating, COUNT(rating) AS cnt_rating FROM film
WHERE rating='PG' or rating='G'
# WHERE rating IN ('PG', 'G') 더 짧게 작성 가능
GROUP BY rating;

# 영화(film table)에서 영화가 G 등급인 영화 제목을 출력
SELECT title FROM film
WHERE rating='G';

# 영화(film table)에서 release 연도가 2006 또는 2007 연도이고, 영화가 PG 또는 G 등급인 영화 제목을 출력
SELECT * FROM film
WHERE (release_year=2006 OR release_year=2007)
AND (rating='PG' OR rating='G');

/* SELECT title FROM film
WHERE release_year IN (2006, 2007)
AND rating IN ('PG', 'G'); */

/* film테이블에서 rating (등급)으로 그룹을 묶어서, 각 등급별 영화 갯수와 각 등급별 평균 렌탈 비용 출력.
단, 평균 렌탈비용이 높은 순으로 출력 (등급과 각 등급별 갯수, 각 등급별 평균 렌탈 비용 출력하기) */
SELECT rating,
	   COUNT(*) AS total_films, 
       round(AVG(rental_rate), 2) AS avg_rental_rate
FROM film
GROUP BY rating
ORDER BY avg_rental_rate DESC;

# 각 등급별 영화 길이가 130분 이상인 영화의 갯수와 등급을 출력
SELECT rating,
       COUNT(*) AS total_films
FROM film
WHERE length>=130
GROUP BY rating;

테이블과 테이블 간의 관계
inventory_id, customer_id 와 같이 다른 테이블에 상세 정보가 있고, 각 정보를 구별할 수 있는 ID 값으로 연결되어
있는 경우가 많음
👉하나의 테이블에 모든 정보를 담지 않는다!

2. world 데이터 실습

sakila data와 똑같은 문법 순서대로 쿼리를 작성해본다.

(중복되는 문제는 작성해보고 출력이 정확히 되는지만 확인하고 일지에 적지 않았다.)

# city 테이블에서 국가코드 수 추출
SELECT COUNT(DISTINCT CountryCode) AS cnt_code FROM city;
# DISTINCT 키워드를 통해 중복값을 제외한 국가코드의 수를 카운트한다.

# countrylanguage 테이블에서 전체 언어 수 추출
SELECT COUNT(DISTINCT language) AS tot_lang FROM countrylanguage;
# 중복값을 제외한 언어만 카운트해야 한다.

# 각 국가별 도시 중 최대 Population과 해당 국가코드를 출력
SELECT CountryCode, MAX(population) AS max_pop
FROM city
GROUP BY CountryCode;

# 각 Continent 별 총 SurfaceArea 과 해당 Continent 를 출력 (country 테이블 기반)
SELECT Continent, SUM(SurfaceArea) AS tot_SA
FROM country
GROUP BY Continent;

# 각 Region 별 평균 GNP 와 최소 IndepYear, 그리고 해당 Region 를 출력
SELECT Region,
	ROUND(AVG(GNP), 2) AS avg_gnp,
	MIN(IndepYear) AS min_indepyear
FROM country
GROUP BY Region;

# 각 Region별, IndepYear가 1900 이상인 국가만 평균 GNP가 낮은 순으로 5개의 Region만 평균 GNP를 출력
SELECT Region, ROUND(AVG(GNP), 2) AS AvgGNP
FROM country
WHERE IndepYear >= 1900
GROUP BY Region
ORDER BY AvgGNP
LIMIT 5;

문제가 길어지면 쉬운 쿼리도 복잡하고 헷갈리는 경우가 많았는데 하나의 큰 문제를 단계별로 쪼개서 생각하고 작성한 뒤 합쳐보니 구조나 데이터 관계도 이해하기 쉬워졌다. 예전에는 문장이 늘어나는 게 싫어서 최소한으로 개행하고 들여쓰기를 했는데, 쿼리가 길어지면 엔터와 탭을 잘 써야 readability가 좋아진다 하셨다. 아직은 연습문제라 쿼리가 그렇게 길지 않지만 나중에 코딩테스트와 실무를 대비해 일찍 습관을 들이도록 해야겠다.

(+ 또한 가독성이 좋게 키워드는 대문자, 컬럼명이나 테이블명은 소문자로 쓰는 습관&별칭은 되도록 키워드와 겹치지 않도록 _ (under bar)나 축약하여 대소문자 구분으로 확실하게 표기(ex: avg_gnp, AvgGNP 등등) )

저작자표시 (새창열림)