PostgreSQL Exercises
작성자: 김영찬
// postgres 계정(수퍼유저)에서 사용자 생성(movie)
- sudo -u postgres psql // postgres 계정으로 psql 실행
- 또는 아래 방식으로
su - postgres
psql // Linux postgres 계정에서 psql을 실행하면 수퍼유저로 로그인
- postgres=#CREATE USER movie PASSWORD 'futuresoft#00'; // 수퍼유저에서 사용자 계정 movie 생성
- postgres=#ALTER ROLE movie superuser createdb createrole; // ALTER ROLE로 사용자에 권한 부여
- postgres=#\du
// Database 생성(postgres 계정 혹은 createdb 권한이 있는 사용자 계정)
- CREATE DATABASE movielens WITH OWNER movie; // 수퍼유저에서 movie를 소유주 movielenst 데이터베이스 생성
// 새로 생성한 계정(movie)과 데이터베이스(movielens)로 다시 접속
- psql -h localhost -p 5432 -U movie -d movielens
// Movielens Table Schema and Sample Data Load
- movielens-postgres.sql // 영화 및 평점관련 테이블 생성 후 샘플 데이터 insert
- export PGPASSWORD=password; // hadoop 계정에 대한 암호를 환경변수에 설정
- psql -h localhost -p 5432 -U hadoop -d hadooptest < movielens-postgres.sql
- (혹은 psql 로그인후 postgres=#\i movielens-postgres.sql 로 sql 파일 실행)
- genre(영화분류)
- movie(영화정보)
- muser(고객정보)
- occupation(직업정보)
- movierating(영화평점)
- moviegenre(영화장르)
// Schema와 Sample Data에 일부 내용 보완
ALTER TABLE user RENAME TO muser; // user 테이블명을 muser로 변경
ALTER TABLE muser ADD COLUMN name VARCHAR(40) NOT NULL DEFAULT ' '; // 이름 필드 추가
UPDATE muser SET name = '고객' || text(id); // 이름 값 설정
UPDATE muser SET age = 17 WHERE age < 18; // 나이 설정 안된 회원 정보 임의 설정
UPDATE movie SET year = 2001 WHERE year = 0; // 상영년도 설정 안된 데이터 값 임의 설정
COMMENT ON TABLE genre IS '영화분류';
COMMENT ON TABLE movie IS '영화정보';
COMMENT ON TABLE moviegenre IS '영화장르';
COMMENT ON TABLE movierating IS '영화평점';
COMMENT ON TABLE muser IS '고객정보';
COMMENT ON TABLE occupation IS '직업분류';
// 기본 Query 예제
SELECT * FROM genre; // 결과에 모든 필드를 표시, Default 정렬 순서는 PK 순서
SELECT COUNT(*) FROM genre; // 전체 레코드의 갯수 출력
SELECT COUNT(name) FROM genre; // 특정 필드값이 NULL이 아닌 레코드 갯수 출력
SELECT id AS 분류ID, name AS 분류명 FROM genre; // 필드명을 임으로 변경해서 표시(default는 필드정의 명칭)
SELECT name FROM genre; // 결과에 name 필드만 표시
SELECT substr(name, 1, 8) FROM genre;
SELECT substr(name, 3) FROM genre;
SELECT name FROM genre WHERE substr(name, 1, 2) = 'Th'; // 문자의 일부를 완전일치 검색
SELECT name FROM genre WHERE substr(name, 1, 2) LIKE 'Th%'; // Th 로 시작하는 레코드 출력
SELECT id, name FROM genre ORDER BY name ASC; // Ascending Order
SELECT id, name FROM genre ORDER BY name DESC; // Descending Order
SELECT id, name FROM genre WHERE id =1;
SELECT id, name FROM genre WHERE id =1 AND name = 'Action'; // 모든 조건이 만족하는 레코드 출력
SELECT id, name FROM genre WHERE id =1 OR name = 'Comedy'; // 어느 조건이건 만족하는 레코드 출력
SELECT id, name FROM genre WHERE id =1 AND name = 'Comedy';
SELECT id, name FROM genre WHERE name LIKE 'A%' ORDER BY name ASC; // A로 시작하는 레코드 출력
SELECT id, name FROM genre WHERE name LIKE '%e' ORDER BY name ASC; // e로 끝나는 레코드 출력
SELECT id, name FROM genre WHERE name LIKE '%me%' ORDER BY name ASC; // me가 포함된 레코드 출력
SELECT id, name, year FROM movie LIMIT 10; // 결과중 10건만 출력
SELECT id, name, year FROM movie WHERE year = 2001; // 숫자 필드 질의
SELECT id, name, year FROM movie WHERE name = 'Jumanji'; // 문자 필드 질의
SELECT id, name, year FROM movie WHERE year BETWEEN 1995 AND 1998; // 특정값 사이 결과 출력
SELECT id, name, year FROM movie WHERE year >= 2000; // 특정 값 이상 결과 출력
SELECT DISTINCT year FROM movie ORDER BY year DESC; // 특정 필드에서 고유한 값의 목록 출력
SELECT COUNT(DISTINCT year) FROM movie; // 중복 함수 결과 - 건수 표시
SELECT movieid, genreid FROM moviegenre;
SELECT id, name, gender, age FROM muser;
SELECT id, name, gender, age FROM muser WHERE id <= 10 ORDER BY id ASC;
SELECT id, name, gender, age FROM muser WHERE id > 100 ORDER BY id ASC;
SELECT * FROM muser WHERE id IN (1, 100, 1000);
SELECT * FROM muser WHERE id IN (SELECT userid FROM movierating WHERE rating >= 4); // Subquery for 고객정보
SELECT * FROM movie WHERE id IN (SELECT movieid FROM movierating WHERE rating < 3); // Subquery for 영화정보
SELECT userid, movieid, rating FROM movierating;
SELECT SUM(rating), MIN(rating), MAX(rating), AVG(rating), STDDEV(rating) FROM movierating;
SELECT userid, movieid, rating FROM movierating WHERE userid=6 AND movieid=1806;
SELECT movieid, ROUND(AVG(rating), 2) FROM movierating GROUP BY movieid; // 여러 결과를 묶어서 하나로 표시(연산 함수와 함께)
SELECT movieid, MAX(rating), MIN(rating), ROUND(AVG(rating), 2) AS avr FROM movierating GROUP BY movieid; // 수치연산 함수 적용
SELECT movieid, rating FROM movierating ORDER BY rating ASC , movieid ASC; // 복수 필드로 정렬
SELECT movieid, ROUND(AVG(rating), 2) AS average FROM movierating GROUP BY movieid ORDER BY average DESC LIMIT 10; // 숫자 표시형식 지정
SELECT movieid, ROUND(AVG(rating), 2) AS average FROM movierating GROUP BY movieid HAVING AVG(rating) > 4.50 ORDER BY average DESC; // 영화별
SELECT userid, ROUND(AVG(rating), 2) AS average FROM movierating GROUP BY userid HAVING AVG(rating) > 4.68 ORDER BY average DESC; // 고객별
SELECT id, name FROM occupation;
SELECT name FROM muser WHERE birthdate ='2020-10-20' // 날짜형에 대한 일치 검색
// SELECT name FROM muser WHERE birthdate BETWEEN '1980-01-01' AND '2000-12-31' // 날짜형에 대한 구간 검색
// JOIN Query 예제
- 상영된 영화의 년도별 상영 건수를 최신 년도 순으로 보여라
SELECT
year AS 상영연도,
count(*) AS 영화편수
FROM movie
GROUP BY year
ORDER BY year DESC;
- 년도별 상영된 영화를 최신 년도순으로 장르명 함께 보여라(영화기준)
SELECT
a.year AS 상영연도,
a.id AS 영화ID,
b.id AS 장르ID,
SUBSTR(a.name,1, 30) AS 영화제목,
b.name AS 영화분류
FROM movie a
LEFT JOIN (
SELECT c.movieid,
g.name,
g.id
FROM moviegenre c
LEFT JOIN genre g
ON c.genreid = g.id) b
ON a.id = b.movieid
ORDER BY a.year DESC, a.name ASC;
// movie를 기준으로 moviegenre와 LEFT JOIN하여 모든 데이터가 나옴
- 년도별 상영된 영화를 최신 년도순으로 장르명 함께 보여라(영화평가기준)
SELECT
a.year AS 상영연도,
a.id AS 영화ID,
b.id AS 장르ID,
SUBSTR(a.name,1, 30) AS 영화제목,
b.name AS 영화분류
FROM movie a
RIGHT JOIN (
SELECT
c.movieid,
g.name,
g.id
FROM moviegenre c
LEFT JOIN genre g
ON c.genreid =g.id) b
ON a.id = b.movieid
ORDER BY a.year DESC, a.name ASC;
// moviegenre를 기준으로 moviegenre에 RIGHT JOIN함. moviegenre에 movieid 3건이 movie에 없음
SELECT *
FROM moviegenre
WHERE movieid NOT IN
(
SELECT id FROM movie
);
SELECT * FROM movie WHERE id = 2821;
- 영화정보와 영화별 장르정보에 모두 포함된 레코드만 보여라.(아래 2개의 결과건수와 movie 기준 LEFT JOIN 건수가 동일)
SELECT *
FROM movie a
INNER JOIN moviegenre g
ON a.id = g.movieid;
SELECT *
FROM moviegenre g
INNER JOIN movie a
ON a.id = g.movieid;
- 상영된 영화를 년도별 장르별 건수를 년도와 장르순으로 보여라
SELECT
a.year AS 상영연도,
b.name AS 영화분류,
count(*) AS 상영건수
FROM movie a
LEFT JOIN (
SELECT c.movieid,
g.name
FROM moviegenre c
LEFT JOIN genre g
ON c.genreid = g.id) b
ON a.id = b.movieid
GROUP BY a.year, b.name
ORDER BY a.year DESC, b.name ASC;
- 고객들 명단을 직업과 함께 표시하라
SELECT
a.name AS 고객명,
a.gender AS 성별,
a.age AS 나이,
b.name AS 직업
FROM muser a
LEFT JOIN (
SELECT id,
name
FROM occupation) b
ON a.occupationid = b.id
ORDER BY a.name ASC, a.age ASC;
- 고객들의 연령별 인원수를 집계하라.
\pset numericlocale // psql에서 표시되는 숫자를 1000단위 마다 ,로 구분
SELECT b.agerange AS 연령대,
COUNT(*) AS 인원수
FROM (
SELECT
CASE
WHEN age < 20 THEN '20 이하'
WHEN age < 30 THEN '20~29'
WHEN age < 40 THEN '30~39'
WHEN age < 50 THEN '40~49'
WHEN age >= 50 THEN '50 이상'
END AS agerange
FROM muser) b
GROUP BY b.agerange
ORDER BY b.agerange ASC;
- 연령대별 평점 평균을 계산하라
SELECT
a.agerange AS 연령대,
COUNT(*) AS 인원수,
ROUND(AVG(a.rating),2) AS 평점
FROM (
SELECT
CASE
WHEN d.age < 20 THEN '20 이하'
WHEN d.age < 30 THEN '20~29'
WHEN d.age < 40 THEN '30~39'
WHEN d.age < 50 THEN '40~49'
WHEN d.age >= 50 THEN '50 이상'
END AS agerange,
d.rating
FROM (
SELECT b.userid,
c.age,
c.name,
b.movieid,
b.rating
FROM movierating b
LEFT JOIN muser c
ON b.userid = c.id) d
) a
GROUP BY a.agerange
ORDER BY a.agerange ASC;
- 성별 평점 평균을 계산하라
SELECT
a. gender AS 성별,
COUNT(*) AS 인원수,
ROUND(AVG(a.rating), 2) AS 평점
FROM (
SELECT
CASE d.gender
WHEN 'M' THEN '남성'
WHEN 'F' THEN '여성'
END AS gender,
d.rating
FROM (
SELECT b.userid,
c.gender,
b.movieid,
b.rating
FROM movierating b
LEFT JOIN muser c ON
b.userid = c.id) d
) a
GROUP BY a.gender
ORDER BY a.gender ASC;
- 영화별 평가가 100건 이상인 영화에 대한 평점 평균을 계산하고 평점이 높은 순 10건을 표시하라
SELECT
a.name AS 영화제목,
COUNT(*) AS 평가수,
ROUND(AVG(a.rating),2) AS 평점
FROM (
SELECT b.movieid,
c.name,
b.rating
FROM movierating b
LEFT JOIN movie c ON
b.movieid = c.id) a
GROUP BY a.name
HAVING COUNT(*) > 100
ORDER BY AVG(a.rating) DESC LIMIT 10;