The solution of Databases Query problems(Fifth)

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

Preface

when there is rain neither sun nor moon, the people actually do not think so.

Perhaps there are seasonal climate not to be cold rain, let the sun or the cool side.

Has the rain the night is otherwise a moonlit night no flavor.

Sometimes not reminiscent of Li Shangyin “when he cut a total of west window candle, but then when the famous hope of reunion among friends”

In the rain, fom the sky come the silk notes; here, quiet and comfortable.

Databases-in-rain

Presentation

In this page, we will show the homework of the course of the Databases Technology in CMU. All of the questions we tested in the postgresql.

This section mainly is to practice the query operation of the SQL, include SQL view, index, etc. We also sure that all the sqls will pass in the mysql, sqlite… , this article also will provide the Chinese version in detail.

中文版

Question Details

In this homework you will have to write SQL queries to answer questions on a movie dataset, about movies and actors. The database contains two tables:

• movies(mid, title, year, num ratings, rating) , Primary key: mid
• play in(mid, name, cast position), Primary key: mid, name

The tables contain the obvious information: which actor played in what movie, at what position; for each movie, we have the title (eg., ’Gone with the wind’), year of production, count of rating reviews it received, and the average score of those ratings (a float in the range 0 to 10, with ’10’ meaning ’excellent’).

We will use Postgres, which is installed in the your own machines.

Question 1: Warm-up queries . . . . . . . . . . . . . . . . . . . . . . . . . [5 points]
(a) [2 points] Print all actors in the movie Quantum of Solace, sorted by cast position. Print only their names.
(b) [3 points] Print all movie titles that were released in 2002, with rating larger than 8 and with more than one rating (num ratings > 1).

Question 2: Find the star’s movies . . . . . . . . . . . . . . . . . . . . [5 points]
(a) [5 points] Print movie titles where Sean Connery was the star (i.e. he had position 1 in the cast). Sort the movie titles alphabetically.

Question 3: Popular actors . . . . . . . . . . . . . . . . . . . . . . . . . . [15 points]
(a) [8 points] We want to find the actors of the highest quality. We define their quality as the weighted average of the ratings of the movies they have played in (regardless of cast position), using the number of ratings for each movie as the
weight. In other words, we define the quality for a particular actor as

DB-Fifth-1

Print the names of the top 5 actors, according to the above metric. Break ties alphabetically.

(b) [7 points] Now we want to find the 5 most popular actors, in terms of number of ratings (regardless of positive or negative popularity). I.e, if actor ‘Smith’ played in 2 movies, with num ratings 10 and 15, then Smith’s popularity is 25 (=10+15). Print the top 5 actor names according to popularity. Again, break ties alphabetically.

Question 4: Most controversial actor . . . . . . . . . . . . . . . . [10 points]
(a) [10 points] We want to find the most controversial actor. As a measure of controversy, we define the maximum difference between the ratings of two movies that an actor has played in (regardless of cast position). That is, if actor ‘Smith’ played in a movie that got rating=1.2, and another that got rating=9.5, and all the other movies he played in, obtained scores within that range, then Smith’s contoversy score is 9.5-1.2= 8.3. Print the name of the top-most controversial actor - again, if there is a tie in first place, break it alphabetically.

Question 5: The minions . . . . . . . . . . . . . . . . . . . . . . . . . . . . [20 points]
(a) [20 points] Find the “minions” of Annette Nicole: Print the names of actors who only played in movies with her and never without her. The answer should not contain the name of Annette Nicole. Order the names alphabetically.

Question 6: High productivity . . . . . . . . . . . . . . . . . . . . . . . . [5 points]
(a) [5 points] Find the top 2 most productive years (by number of movies produced). Solve ties by preferring chronologically older years, and print only the years.

Question 7: Movies with similar cast . . . . . . . . . . . . . . . . [15 points]
(a) [8 points] Print the count of distinct pairs of movies that have at least one actor in common (ignoring cast position). Exclude self-pairs, and mirror-pairs.
(b) [7 points] Print the count of distinct pairs of moves that have at least two actors in common (again, ignoring cast position). Again, exclude self-pairs, and mirror pairs.

Question 8: Skyline query . . . . . . . . . . . . . . . . . . . . . . . . . . . [25 points]
(a) [25 points] We want to find a set of movies that have both high popularity (ie, high num ratings) as well as high quality (rating). No single movie may achieve both - in which case, we want the so-called Skyline query 2 . More specifically, we want all movies that are not “dominated” by any other movie:

Definition of domination : Movie “A” dominates movie “B” if movie “A” wins over movie “B”, on both criteria, or wins on one, and ties on the rest.

Figure 1 gives a pictorial example: the solid dots (’A’, ’D’, ’F’) are not dominated by any other dot, and thus form the skyline. All other dots are dominated by at least one other dot: e.g., dot ’B’ is dominated by dot ’A’, being inside the shaded rectangle that has ’A’ as the upper-right corner.

DB-Fifth-figure-1

Figure 1: Illustration of Skyline and domination : ’A’ dominates all points in the shaded rectangle; ’A’, ’D’ and ’F’ form the skyline of this cloud of points.

Given the above description, print the title of all the movies on the skyline, along with the rating and the number of ratings.

Answer

we give the Postgres version in detail, we will see you can tranfer it easily in mysql or sqlite.

Initialization:

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);

Insert Values

Insert into some values into the table movies and play_in,
you will find the datas just in the follow links in my 360 yunFiles:

https://yunpan.cn/cSfLzxQApRXSi password: f3ab

1
2
3
4
5
6
7
8
9
10
11
12
## use "copy" in Postgres
\copy movies from '~/data/movie_processed.dat';
\copy play_in from '~/data/movie_actor_processed.dat';

## use "load data local infile" in Mysql
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';

## if you find load infile errors in mysql just like this:
## ERROR 1148 (42000): The used command is not allowed with this MySQL version
## please grant FILE authority in Mysql just use this comman: "mysql -u root -p tablename --local-infile=1" to login databases;

The flowing image show the test infos in my ubuntu os:

DB-Fifth-ctreate-and-copy-datas1

DB-Fifth-ctreate-and-copy-datas2

Solution 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) Result just like this:

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) Result just like this:
title
---------------------------------------
The Lord of the Rings: The Two Towers
Cidade de Deus
Mou gaan dou
(3 rows)

Solution 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;

## Result just like this:
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)

The flowing image show the test solution 2 infos in my ubuntu os:

Solution 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) Result just like this:
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) Result just like this:
name
----------------------
Johnny Depp
Alan Rickman
Orlando Bloom
Helena Bonham Carter
Matt Damon
(5 rows)

Solution 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;

## Result just like this:
name
---------------
John Travolta
(1 row)

The flowing image show the test solution 4 infos in my ubuntu os:

Solution 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;

## Result just like this:
name
-----------------
Christian Perry
(1 row)

Solution 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;

## Result just like this:
year
------
2006
2007
(2 rows)

Solution 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) Result just like this:
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) Result just like this:
count
-------
6845
(1 row)

Solution 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);

Reference

[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%