数据库题目整理及详解中文版（五）

Posted on | In
Symbols count in article: 30k | Reading time ≈ 27 mins.

前言

有雨的时候既没有太阳也没有月亮，人们却多不以为许。

有雨的夜晚则另有一番月夜所没有的韵味。

有时不由让人想起李商隐“何当共剪西窗烛，却话巴山夜雨时”的名句

雨中, 从对面天空中传来丝丝音符; 这里, 静谧而又舒畅.

Databases-in-rain

说明

本文算作一篇译文吧, 题目来自于卡内基.梅隆大学的数据库技术课程的Homework, 在数据库postgresql中亲测通过; 各版本的SQL环境差别不大, 看看下文就知道, 在其他版本的数据库, 如mysql | sqlite等都能通过.

这部分主要是使用SQL语句进行查询相关操作, 涉及到sql的视图, 索引等, 本文提供英文原版和中文版.

英文版

题目详述

在本页面中, 你需要使用SQL的查询语句来回答下面的问题, 下文中给出的是一个关于电影的数据库, 该数据库中包含电影影片和演员的信息, 数据表如下所示:

  • movies(mid, title, year, num ratings, rating) , 主键: mid
  • play in(mid, name, cast_position), 主键: mid, name

这两张表包含如下信息: 哪个演员出演哪部电影, 以及在电影中位置; 对于每一部电影, 都包含title(标题), year(上映时间), num ratings(收到的评分数目), rating(以及这些评分的平均值, 是一个float型的数字, 在0~10之间, 10代表’’excellent’').

请在你的机器上使用PostgreSQL, 根据以上信息回答如下问题:

Question 1: 热身练习 . . . . . . . . . . . . . . . . . . . . . . . . . [5 points]
(a) [2 分] 打印出所有在电影"Quantum of Solace"中的所有演员, 按照他们 在影片中的位置(cast position)排列, 且只打印出他们的名字(name);
(b) [3 分] 打印2002年上映的所有影片的标题, 约束rating评分大于8, 且num_ratings > 1.

Question 2: 找出相关明星的电影. . . . . . . . . . . . . . . . . . . . [5 points]
(a) [5 分] 打印有关电影明星"Sean Connery"主演(即, 他在演员表位于第一位: cast_position=1)的影片的标题, 电影标题按字母排序.

Question 3: 最优秀的演员 . . . . . . . . . . . . . . . . . . . . . . . . . . [15 points]
(a) [8 分] 我们想要查找最优秀的演员. 关于最优秀的定义如下计算公式:

DB-Fifth-1

请打印最优秀的5个演员, 先后顺序与演员名字字母顺序无关.

(b) [7 分] 现在, 我们想要找到5个最优秀的演员, 仅就他们获得评分的数量而言. 如, 如果演员‘Smith’在两部电影中出演, 且两部影片的评分分别是10和15, 则Smith’s 流行度为25 (=10+15).

请根据演员的流行度打印出最流行的5个演员. 同样, 先后顺序与演员名字字母顺序无关.

Question 4: 最受争议的演员 . . . . . . . . . . . . . . . . [10 points]
(a) [10 分] 我们想要找出最受争议的演员.最受争议的演员的计算方式如下: 通过演员在两部影片中获得评分的最大差异来评判(与演员在演员表的位置无关). 如, 演员‘Smith’在一部电影中获得的评分rating=1.2, 在另一部电影中获得的评分rating=9.5, 那么他所获得的争议得分为9.5-1.2= 8.3.

请打印出最具争议性的演员的姓名, 同样, 先后顺序与演员名字字母顺序无关.

Question 5: 仆从人员 . . . . . . . . . . . . . . . . . . . . . . . . . . . . [20 points]
(a) [20 分] 查找演员Annette Nicole作为仆从的演员: 查找电影中演员Annette Nicole作为仆从或没有作为仆从的演员的名字. 答案中不包含Annette Nicole, 且名字按字母排序.

Question 6: 高产出 . . . . . . . . . . . . . . . . . . . . . . . . [5 points]
(a) [5 分] 查找产出影片数量最高的2个年份(根据年份中产出的影片数量). 结果按照时间顺序打印, 且只打印出年份.

Question 7: 相似演员表的电影 . . . . . . . . . . . . . . . . [15 points]
(a) [8 分] 打印电影中演员不同派对关系的影片的数量, 电影中至少有1个演员相同(忽略他们在演员表中的位置).
(b) [7 分] 打印电影中演员不同派对关系的影片的数量, 电影中至少有2个演员相同(忽略他们在演员表中的位置).

Question 8: 轮廓查询 . . . . . . . . . . . . . . . . . . . . . . . . . . . [25 points]
(a) [25 分] 我们想找出一个关于电影影片的集合, 这个集合都具有高人气(即, 高num_ratings)以及高质量(rating)。如果没有一个这样的电影存在 - 在这种情况下, 我们进行所谓的轮廓查询。更具体地说, 我们希望所有的电影都不是由任何其他电影“主导”:

主导的定义 : 电影 “A” 主导 电影 “B”, 如果电影 “A” 胜于 “B”, 即A在这两个标准(高 num_ratings 和 高 rating)都胜于B, 或者只要有其中一个标准中胜过B. 我们则称电影 “A” 主导 电影 “B”.

图 1 给出了一个形象的例子: 实点 (’A’, ’D’, ’F’)并不由任何其他点所主导,从而形成了一个轮廓线。所有其他点都至少被一个其他点所主导: 例如,点 “B” 被点 “A” 所主导, 如图阴影矩形框的右上角有一个点 “A” 。

DB-Fifth-figure-1

图 1: 关于轮廓和主导概念的插图 : ’A’ 主导了所有在阴影框中点; ’A’, ’D’ 和 ’F’ 形成了这些点集合的轮廓.

鉴于以上的描述, 请根据评分, 评分的数量打印所有在轮廓上的电影.

解答

本文给出了所有在Postgres中测试过的答案, 相信大家可以很容易地在Mysql 或 Sqlite中进行转化.

初始化:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
## drop the table if exists
drop table if exists movies cascade;   
drop table if exists play_in cascade;

## create tables movies and play_in
create table movies (
mid integer PRIMARY KEY, 
title varchar(200),
year integer,
num_ratings integer, 
rating real);

create table play_in (
mid integer references movies, 
name varchar(100), 
cast_position integer, 
PRIMARY KEY(mid, name));

create index mid on movies(mid);

导入数据

这里提供了供进行测试的数据源, 请从我的360云盘地址中下载:
https://yunpan.cn/cSfLzxQApRXSi 访问密码: f3ab

1
2
3
4
5
6
7
8
9
10
11
12
## 在Postgres中, 请使用copy命令导入数据
\copy movies from '~/data/movie_processed.dat';
\copy play_in from '~/data/movie_actor_processed.dat';

## 在Mysql下请使用 "load data local infile"命令导入文件数据, 如:
load data local infile "~/data/movie_processed.dat" into table movies FIELDS TERMINATED BY '\t';
load data local infile "~/data/movie_actor_processed.dat" into table play_in FIELDS TERMINATED BY '\t';

## 如果在mysql "load infile" 出现错误信息如下时, 
## ERROR 1148 (42000): The used command is not allowed with this MySQL version
## 请授予相应数据库的FILE权限, 在登录数据库时赋予权限, 如:
## mysql -u root -p tablename --local-infile=1

下图为我在ubuntu系统下的测试结果:

DB-Fifth-ctreate-and-copy-datas1

DB-Fifth-ctreate-and-copy-datas2

解答 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
(a) SELECT name FROM play_in p, movies m
WHERE p.mid = m.mid and m.title=’Quantum of Solace’
ORDER BY p.cast_position;

## (a) 运行结果如下所示:

name
------------------------------
Daniel Craig
Olga Kurylenko
Mathieu Amalric
Judi Dench
Giancarlo Giannini
Gemma Arterton
Jeffrey Wright
David Harbour
Jesper Christensen
Anatole Taubman
Rory Kinnear
Tim Pigott-Smith
Fernando Guillen-Cuervo
Jesus Ochoa
Glenn Foster
Paul Ritter
Simon Kassianides
Stana Katic
Lucrezia Lante della Rove...
Neil Jackson
Oona Chaplin
(21 rows)

(b) SELECT title FROM movies
WHERE year = 2002 and rating>8 and num_ratings>1;

## (b) 运行结果如下所示:
title
---------------------------------------
The Lord of the Rings: The Two Towers
Cidade de Deus
Mou gaan dou
(3 rows)

解答 2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT title from movies m, play_in p
WHERE m.mid = p.mid and 
name = ’Sean Connery’ and 
cast_position = 1
ORDER BY title;

## 运行结果如下所示:
title
---------------------------------------
Der Name der Rose
Diamonds Are Forever
Dr. No
Entrapment
Finding Forrester
First Knight
From Russia with Love
Goldfinger
Never Say Never Again
The Hunt for Red October
The League of Extraordinary Gentlemen
Thunderball
You Only Live Twice
(13 rows)

解答 3

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
(a) DROP VIEW IF EXISTS WeigthedRatings;

CREATE VIEW WeightedRatings AS
SELECT name, SUM(rating*num_ratings)/SUM(num_ratings) AS WeightedRating
FROM movies m, play_in p WHERE m.mid = p.mid GROUP BY(name);

SELECT name FROM WeightedRatings
ORDER BY
WeightedRating DESC, name ASC LIMIT 5;

## (a) 运行结果如下所示:
name
-----------------------
Adam Kalesperis
Aidan Feore
Aleksandr Kajdanovsky
Alexander Kaidanovsky
Alisa Frejndlikh
(5 rows)

(b) DROP VIEW IF EXISTS ActorSumRatings;

CREATE VIEW ActorSumRatings AS
SELECT name, SUM(num_ratings) as popularity
FROM play_in p, movies m
WHERE p.mid = m.mid
GROUP BY name;

SELECT name from ActorSumRatings
ORDER BY popularity DESC, name ASC LIMIT 5;

## (b) 运行结果如下所示:
name
----------------------
Johnny Depp
Alan Rickman
Orlando Bloom
Helena Bonham Carter
Matt Damon
(5 rows)

解答 4

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DROP VIEW IF EXISTS RatingGap;

CREATE VIEW RatingGap AS
SELECT p1.name, MAX(ABS(m1.rating-m2.rating)) as Gap
FROM play_in p1, play_in p2, movies m1, movies m2
WHERE p1.mid = m1.mid and 
p2.mid = m2.mid and 
p1.name = p2.name
GROUP BY(p1.name);

SELECT name 
FROM RatingGap 
ORDER BY(Gap) DESC LIMIT 1;

## 运行结果如下所示:
name
---------------
John Travolta
(1 row)

解答 5

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
DROP VIEW IF EXISTS MastersMovies CASCADE;

CREATE VIEW MastersMovies AS
SELECT m.mid,m.title FROM movies m, play_in p
WHERE m.mid = p.mid and p.name = ’Annette Nicole’;

DROP VIEW IF EXISTS CoActors;

CREATE VIEW CoActors AS
SELECT DISTINCT name FROM MastersMovies m , play_in p
WHERE p.mid = m.mid;

DROP VIEW IF EXISTS Combinations;

CREATE VIEW Combinations AS
SELECT name,mid FROM MastersMovies , CoActors;

DROP VIEW IF EXISTS NonExistent;

CREATE VIEW NonExistent AS
SELECT * FROM Combinations
EXCEPT (SELECT name, mid FROM play_in);

DROP VIEW IF EXISTS PotentialResults;

CREATE VIEW PotentialResults AS
SELECT * from CoActors
EXCEPT (SELECT distinct(name) FROM NonExistent);

DROP VIEW IF EXISTS NotMastersMovies;

CREATE VIEW NotMastersMovies AS
SELECT m.mid FROM movies m
EXCEPT (SELECT mid FROM MastersMovies);

SELECT * from PotentialResults
WHERE name not in 
(SELECT name
FROM play_in p, NotMastersMovies m
WHERE m.mid = p.mid
UNION SELECT ’Annette Nicole’
) ORDER BY name;

## 运行结果如下所示:
name
-----------------
Christian Perry
(1 row)

解答 6

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DROP VIEW IF EXISTS MoviesPerYear;

CREATE VIEW MoviesPerYear AS
SELECT year, COUNT(title) num_movies
FROM MOVIES GROUP BY(year);

SELECT year from MoviesPerYear 
ORDER BY num_movies DESC LIMIT 2;

## 运行结果如下所示:
year
------
2006
2007
(2 rows)

解答 7

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
(a) SELECT COUNT(*) FROM 
(SELECT DISTINCT m1.mid, m2.mid
 FROM movies m1, movies m2, play_in p1, play_in p2
 WHERE m1.mid > m2.mid and
 m1.mid = p1.mid and
 m2.mid = p2.mid and 
 p1.name = p2.name) 
AS count;

## (a) 运行结果如下所示:
count
--------
104846
(1 row)

(b) SELECT COUNT(*) FROM 
(SELECT DISTINCT m1.mid, m2.mid
FROM movies m1, movies m2, play_in p1,
play_in p2, play_in p3, play_in p4
WHERE m1.mid > m2.mid and 
m1.mid = p1.mid and 
m2.mid = p2.mid and
m1.mid = p3.mid and 
m2.mid = p4.mid and 
p2.name <> p4.name and 
p1.name = p2.name and 
p3.name = p4.name) 
AS count;

## (b) 运行结果如下所示:
count
-------
6845
(1 row)

解答 8

1
2
3
4
5
6
7
8
9
10
11
DROP VIEW IF EXISTS Dominated;

CREATE VIEW Dominated AS
SELECT DISTINCT m2.mid, m2.title,m2.num_ratings, m2.rating
FROM movies m1, movies m2
WHERE m2.rating<=m1.rating and m2.num_ratings<=m1.num_ratings and 
NOT (m2.rating = m1.rating and m2.num_ratings=m1.num_ratings);

SELECT title,num_ratings,rating 
FROM movies
EXCEPT (SELECT title,num_ratings,rating FROM Dominated);

参考资料

[1] http://www.ruanyifeng.com/blog/2013/12/getting_started_with_postgresql.html
[2] http://www.postgresql.org/docs/
[3] http://www.cs.cmu.edu/~epapalex/15415S14/PostgreSQLReadme.htm
[4] http://www.cs.cmu.edu/~christos/courses/
[5] http://blog.chinaunix.net/uid-20685819-id-4267454.html

0%