• 김영찬

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;

조회수 3회댓글 0개

최근 게시물

전체 보기