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

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. Some

---

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.

---

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

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.

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:

<code class=" hljs sql">## <span class="hljs-operator"><span class="hljs-keyword">drop</span> the <span class="hljs-keyword">table</span> <span class="hljs-keyword">if</span> <span class="hljs-keyword">exists</span>
<span class="hljs-keyword">drop</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">if</span> <span class="hljs-keyword">exists</span> movies <span class="hljs-keyword">cascade</span>;</span>   
<span class="hljs-operator"><span class="hljs-keyword">drop</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">if</span> <span class="hljs-keyword">exists</span> play_in <span class="hljs-keyword">cascade</span>;</span>

## <span class="hljs-operator"><span class="hljs-keyword">create</span> tables movies <span class="hljs-keyword">and</span> play_in
<span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> movies (
mid <span class="hljs-keyword">integer</span> <span class="hljs-keyword">PRIMARY</span> <span class="hljs-keyword">KEY</span>, 
title <span class="hljs-keyword">varchar</span>(<span class="hljs-number">200</span>),
<span class="hljs-keyword">year</span> <span class="hljs-keyword">integer</span>,
num_ratings <span class="hljs-keyword">integer</span>, 
rating <span class="hljs-keyword">real</span>);</span>

<span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> play_in (
mid <span class="hljs-keyword">integer</span> <span class="hljs-keyword">references</span> movies, 
name <span class="hljs-keyword">varchar</span>(<span class="hljs-number">100</span>), 
cast_position <span class="hljs-keyword">integer</span>, 
<span class="hljs-keyword">PRIMARY</span> <span class="hljs-keyword">KEY</span>(mid, name));</span>

<span class="hljs-operator"><span class="hljs-keyword">create</span> index mid <span class="hljs-keyword">on</span> movies(mid);</span></code>

---

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

<code class=" hljs rust">## <span class="hljs-keyword">use</span> <span class="hljs-string">"copy"</span> in Postgres
\<span class="hljs-keyword">copy</span> movies from <span class="hljs-string">'~/data/movie_processed.dat'</span>;
\<span class="hljs-keyword">copy</span> play_in from <span class="hljs-string">'~/data/movie_actor_processed.dat'</span>;

## <span class="hljs-keyword">if</span> you <span class="hljs-keyword">use</span> other databases(mysql, sqlite), you can <span class="hljs-keyword">use</span>   the sql statement: <span class="hljs-string">"insert into ... valuse()"</span>
</code>

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

---

Solution 1

<code class=" hljs asciidoc">(a) SELECT name FROM play<span class="hljs-emphasis">_in p, movies m
WHERE p.mid = m.mid and m.title=’Quantum of Solace’
ORDER BY p.cast_</span>position;

## (a) Result just like this:

<span class="hljs-header">name
------------------------------</span>
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<span class="hljs-emphasis">_ratings>1;

</span>## (b) Result just like this:
<span class="hljs-header">title
---------------------------------------</span>
The Lord of the Rings: The Two Towers
Cidade de Deus
Mou gaan dou
(3 rows)
</code>

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

---

Solution 2

<code class=" hljs vbnet"><span class="hljs-keyword">SELECT</span> title <span class="hljs-keyword">from</span> movies m, play_in p
<span class="hljs-keyword">WHERE</span> m.<span class="hljs-keyword">mid</span> = p.<span class="hljs-keyword">mid</span> <span class="hljs-keyword">and</span> 
name = ’Sean Connery’ <span class="hljs-keyword">and</span> 
cast_position = <span class="hljs-number">1</span>
<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> title;

<span class="hljs-preprocessor">## Result just like this:</span>
title
---------------------------------------
Der Name der Rose
Diamonds Are Forever
Dr. No
Entrapment
Finding Forrester
First Knight
<span class="hljs-keyword">From</span> Russia <span class="hljs-keyword">with</span> Love
Goldfinger
Never Say Never Again
The Hunt <span class="hljs-keyword">for</span> Red October
The League <span class="hljs-keyword">of</span> Extraordinary Gentlemen
Thunderball
You Only Live Twice
(<span class="hljs-number">13</span> rows)</code>

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

---

Solution 3

<code class=" hljs sql">(a) <span class="hljs-operator"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">VIEW</span> <span class="hljs-keyword">IF</span> <span class="hljs-keyword">EXISTS</span> WeigthedRatings;</span>

<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">VIEW</span> WeightedRatings <span class="hljs-keyword">AS</span>
<span class="hljs-keyword">SELECT</span> name, <span class="hljs-aggregate">SUM</span>(rating*num_ratings)/<span class="hljs-aggregate">SUM</span>(num_ratings) <span class="hljs-keyword">AS</span> WeightedRating
<span class="hljs-keyword">FROM</span> movies m, play_in p <span class="hljs-keyword">WHERE</span> m.mid = p.mid <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span>(name);</span>

<span class="hljs-operator"><span class="hljs-keyword">SELECT</span> name <span class="hljs-keyword">FROM</span> WeightedRatings
<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span>
WeightedRating <span class="hljs-keyword">DESC</span>, name <span class="hljs-keyword">ASC</span> LIMIT <span class="hljs-number">5</span>;</span>

## (a) Result just like this:
name
<span class="hljs-comment">-----------------------</span>
Adam Kalesperis
Aidan Feore
Aleksandr Kajdanovsky
Alexander Kaidanovsky
Alisa Frejndlikh
(5 rows)

(b) <span class="hljs-operator"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">VIEW</span> <span class="hljs-keyword">IF</span> <span class="hljs-keyword">EXISTS</span> ActorSumRatings;</span>

<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">VIEW</span> ActorSumRatings <span class="hljs-keyword">AS</span>
<span class="hljs-keyword">SELECT</span> name, <span class="hljs-aggregate">SUM</span>(num_ratings) <span class="hljs-keyword">as</span> popularity
<span class="hljs-keyword">FROM</span> play_in p, movies m
<span class="hljs-keyword">WHERE</span> p.mid = m.mid
<span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> name;</span>

<span class="hljs-operator"><span class="hljs-keyword">SELECT</span> name <span class="hljs-keyword">from</span> ActorSumRatings
<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> popularity <span class="hljs-keyword">DESC</span>, name <span class="hljs-keyword">ASC</span> LIMIT <span class="hljs-number">5</span>;</span>

## (b) Result just like this:
name
<span class="hljs-comment">----------------------</span>
Johnny Depp
Alan Rickman
Orlando Bloom
Helena Bonham Carter
Matt Damon
(5 rows)</code>

The flowing images show the test solution 3 infos in my ubuntu os:

---

Solution 4

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">VIEW</span> <span class="hljs-keyword">IF</span> <span class="hljs-keyword">EXISTS</span> RatingGap;</span>

<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">VIEW</span> RatingGap <span class="hljs-keyword">AS</span>
<span class="hljs-keyword">SELECT</span> p1.name, <span class="hljs-aggregate">MAX</span>(ABS(m1.rating-m2.rating)) <span class="hljs-keyword">as</span> Gap
<span class="hljs-keyword">FROM</span> play_in p1, play_in p2, movies m1, movies m2
<span class="hljs-keyword">WHERE</span> p1.mid = m1.mid <span class="hljs-keyword">and</span> 
p2.mid = m2.mid <span class="hljs-keyword">and</span> 
p1.name = p2.name
<span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span>(p1.name);</span>

<span class="hljs-operator"><span class="hljs-keyword">SELECT</span> name 
<span class="hljs-keyword">FROM</span> RatingGap 
<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span>(Gap) <span class="hljs-keyword">DESC</span> LIMIT <span class="hljs-number">1</span>;</span>

## Result just like this:
name
<span class="hljs-comment">---------------</span>
John Travolta
(1 row)</code>

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

---

Solution 5

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">VIEW</span> <span class="hljs-keyword">IF</span> <span class="hljs-keyword">EXISTS</span> MastersMovies <span class="hljs-keyword">CASCADE</span>;</span>

<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">VIEW</span> MastersMovies <span class="hljs-keyword">AS</span>
<span class="hljs-keyword">SELECT</span> m.mid,m.title <span class="hljs-keyword">FROM</span> movies m, play_in p
<span class="hljs-keyword">WHERE</span> m.mid = p.mid <span class="hljs-keyword">and</span> p.name = ’Annette Nicole’;</span>

<span class="hljs-operator"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">VIEW</span> <span class="hljs-keyword">IF</span> <span class="hljs-keyword">EXISTS</span> CoActors;</span>

<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">VIEW</span> CoActors <span class="hljs-keyword">AS</span>
<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">DISTINCT</span> name <span class="hljs-keyword">FROM</span> MastersMovies m , play_in p
<span class="hljs-keyword">WHERE</span> p.mid = m.mid;</span>

<span class="hljs-operator"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">VIEW</span> <span class="hljs-keyword">IF</span> <span class="hljs-keyword">EXISTS</span> Combinations;</span>

<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">VIEW</span> Combinations <span class="hljs-keyword">AS</span>
<span class="hljs-keyword">SELECT</span> name,mid <span class="hljs-keyword">FROM</span> MastersMovies , CoActors;</span>

<span class="hljs-operator"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">VIEW</span> <span class="hljs-keyword">IF</span> <span class="hljs-keyword">EXISTS</span> NonExistent;</span>

<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">VIEW</span> NonExistent <span class="hljs-keyword">AS</span>
<span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> Combinations
<span class="hljs-keyword">EXCEPT</span> (<span class="hljs-keyword">SELECT</span> name, mid <span class="hljs-keyword">FROM</span> play_in);</span>

<span class="hljs-operator"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">VIEW</span> <span class="hljs-keyword">IF</span> <span class="hljs-keyword">EXISTS</span> PotentialResults;</span>

<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">VIEW</span> PotentialResults <span class="hljs-keyword">AS</span>
<span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">from</span> CoActors
<span class="hljs-keyword">EXCEPT</span> (<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">distinct</span>(name) <span class="hljs-keyword">FROM</span> NonExistent);</span>

<span class="hljs-operator"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">VIEW</span> <span class="hljs-keyword">IF</span> <span class="hljs-keyword">EXISTS</span> NotMastersMovies;</span>

<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">VIEW</span> NotMastersMovies <span class="hljs-keyword">AS</span>
<span class="hljs-keyword">SELECT</span> m.mid <span class="hljs-keyword">FROM</span> movies m
<span class="hljs-keyword">EXCEPT</span> (<span class="hljs-keyword">SELECT</span> mid <span class="hljs-keyword">FROM</span> MastersMovies);</span>

<span class="hljs-operator"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">from</span> PotentialResults
<span class="hljs-keyword">WHERE</span> name <span class="hljs-keyword">not</span> <span class="hljs-keyword">in</span> 
(<span class="hljs-keyword">SELECT</span> name
<span class="hljs-keyword">FROM</span> play_in p, NotMastersMovies m
<span class="hljs-keyword">WHERE</span> m.mid = p.mid
<span class="hljs-keyword">UNION</span> <span class="hljs-keyword">SELECT</span> ’Annette Nicole’
) <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> name;</span>

## Result just like this:
name
<span class="hljs-comment">-----------------</span>
Christian Perry
(1 row)
</code>

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

---

Solution 6

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">VIEW</span> <span class="hljs-keyword">IF</span> <span class="hljs-keyword">EXISTS</span> MoviesPerYear;</span>

<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">VIEW</span> MoviesPerYear <span class="hljs-keyword">AS</span>
<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">year</span>, <span class="hljs-aggregate">COUNT</span>(title) num_movies
<span class="hljs-keyword">FROM</span> MOVIES <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span>(<span class="hljs-keyword">year</span>);</span>

<span class="hljs-operator"><span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">year</span> <span class="hljs-keyword">from</span> MoviesPerYear 
<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> num_movies <span class="hljs-keyword">DESC</span> LIMIT <span class="hljs-number">2</span>;</span>

## Result just like this:
year
<span class="hljs-comment">------</span>
2006
2007
(2 rows)</code>

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

---

Solution 7

<code class=" hljs vbnet">(a) <span class="hljs-keyword">SELECT</span> COUNT(*) <span class="hljs-keyword">FROM</span> 
(<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">DISTINCT</span> m1.<span class="hljs-keyword">mid</span>, m2.<span class="hljs-keyword">mid</span>
 <span class="hljs-keyword">FROM</span> movies m1, movies m2, play_in p1, play_in p2
 <span class="hljs-keyword">WHERE</span> m1.<span class="hljs-keyword">mid</span> > m2.<span class="hljs-keyword">mid</span> <span class="hljs-keyword">and</span>
 m1.<span class="hljs-keyword">mid</span> = p1.<span class="hljs-keyword">mid</span> <span class="hljs-keyword">and</span>
 m2.<span class="hljs-keyword">mid</span> = p2.<span class="hljs-keyword">mid</span> <span class="hljs-keyword">and</span> 
 p1.name = p2.name) 
<span class="hljs-keyword">AS</span> count;

<span class="hljs-preprocessor">## (a) Result just like this:</span>
count
--------
<span class="hljs-number">104846</span>
(<span class="hljs-number">1</span> row)

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

<span class="hljs-preprocessor">## (b) Result just like this:</span>
count
-------
<span class="hljs-number">6845</span>
(<span class="hljs-number">1</span> row)</code>

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

---

Solution 8

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">VIEW</span> <span class="hljs-keyword">IF</span> <span class="hljs-keyword">EXISTS</span> Dominated;</span>

<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">VIEW</span> Dominated <span class="hljs-keyword">AS</span>
<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">DISTINCT</span> m2.mid, m2.title,m2.num_ratings, m2.rating
<span class="hljs-keyword">FROM</span> movies m1, movies m2
<span class="hljs-keyword">WHERE</span> m2.rating<=m1.rating <span class="hljs-keyword">and</span> m2.num_ratings<=m1.num_ratings <span class="hljs-keyword">and</span> 
<span class="hljs-keyword">NOT</span> (m2.rating = m1.rating <span class="hljs-keyword">and</span> m2.num_ratings=m1.num_ratings);</span>

<span class="hljs-operator"><span class="hljs-keyword">SELECT</span> title,num_ratings,rating 
<span class="hljs-keyword">FROM</span> movies
<span class="hljs-keyword">EXCEPT</span> (<span class="hljs-keyword">SELECT</span> title,num_ratings,rating <span class="hljs-keyword">FROM</span> Dominated);</span>
</code>

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

---

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/