# SQL Exercises, Practice, Solution - JOINS exercises on movie Database

## SQL [24 exercises with solution]

You may read our SQL Joins, SQL Left Join, SQL Right Join tutorial before solving the following exercises.

[An editor is available at the bottom of the page to write and execute the scripts. Go to the editor]

Sample Database:

1. From the following table, write a SQL query to find all reviewers whose ratings contain a NULL value. Return reviewer name.

Sample table: reviewer
``` rev_id |            rev_name
--------+--------------------------------
9001 | Righty Sock
9002 | Jack Malvern
9003 | Flagrant Baronessa
9004 | Alec Shaw
9005 |
9006 | Victor Woeltjen
9007 | Simon Wright
9008 | Neal Wruck
9009 | Paul Monks
9010 | Mike Salvati
9011 |
9012 | Wesley S. Walker
9013 | Sasha Goldshtein
9014 | Josh Cates
9015 | Krug Stillo
9016 | Scott LeBrun
9017 | Hannah Steele
9019 | Brandt Sponseller
```
Sample table: rating
``` mov_id | rev_id | rev_stars | num_o_ratings
--------+--------+-----------+---------------
901 |   9001 |      8.40 |        263575
902 |   9002 |      7.90 |         20207
903 |   9003 |      8.30 |        202778
906 |   9005 |      8.20 |        484746
924 |   9006 |      7.30 |
908 |   9007 |      8.60 |        779489
909 |   9008 |           |        227235
910 |   9009 |      3.00 |        195961
911 |   9010 |      8.10 |        203875
912 |   9011 |      8.40 |
914 |   9013 |      7.00 |        862618
915 |   9001 |      7.70 |        830095
916 |   9014 |      4.00 |        642132
925 |   9015 |      7.70 |         81328
918 |   9016 |           |        580301
920 |   9017 |      8.10 |        609451
921 |   9018 |      8.00 |        667758
922 |   9019 |      8.40 |        511613
923 |   9020 |      6.70 |         13091
```

Sample Output:

```            rev_name
--------------------------------
Neal Wruck
Scott LeBrun
(2 rows)
```

Click me to see the solution

2. From the following table, write a SQL query to find out who was cast in the movie 'Annie Hall'. Return actor first name, last name and role.

Sample table: actor
``` act_id |      act_fname       |      act_lname       | act_gender
--------+----------------------+----------------------+------------
101 | James                | Stewart              | M
102 | Deborah              | Kerr                 | F
103 | Peter                | OToole               | M
104 | Robert               | De Niro              | M
105 | F. Murray            | Abraham              | M
106 | Harrison             | Ford                 | M
107 | Nicole               | Kidman               | F
108 | Stephen              | Baldwin              | M
109 | Jack                 | Nicholson            | M
110 | Mark                 | Wahlberg             | M
111 | Woody                | Allen                | M
112 | Claire               | Danes                | F
113 | Tim                  | Robbins              | M
114 | Kevin                | Spacey               | M
115 | Kate                 | Winslet              | F
116 | Robin                | Williams             | M
117 | Jon                  | Voight               | M
118 | Ewan                 | McGregor             | M
119 | Christian            | Bale                 | M
120 | Maggie               | Gyllenhaal           | F
121 | Dev                  | Patel                | M
122 | Sigourney            | Weaver               | F
123 | David                | Aston                | M
124 | Ali                  | Astin                | F
```
Sample table: movie_cast
``` act_id | mov_id |              role
--------+--------+--------------------------------
101 |    901 | John Scottie Ferguson
102 |    902 | Miss Giddens
103 |    903 | T.E. Lawrence
104 |    904 | Michael
105 |    905 | Antonio Salieri
106 |    906 | Rick Deckard
107 |    907 | Alice Harford
108 |    908 | McManus
110 |    910 | Eddie Adams
111 |    911 | Alvy Singer
112 |    912 | San
113 |    913 | Andy Dufresne
114 |    914 | Lester Burnham
115 |    915 | Rose DeWitt Bukater
116 |    916 | Sean Maguire
117 |    917 | Ed
118 |    918 | Renton
120 |    920 | Elizabeth Darko
121 |    921 | Older Jamal
122 |    922 | Ripley
114 |    923 | Bobby Darin
109 |    909 | J.J. Gittes
119 |    919 | Alfred Borden
```
Sample table: movie
``` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
```

Sample Output:

```      act_fname       |      act_lname       |              role
----------------------+----------------------+--------------------------------
Woody                | Allen                | Alvy Singer
(1 row)
```

Click me to see the solution

3. From the following table, write a SQL query to find the director who directed a movie that featured a role in 'Eyes Wide Shut'. Return director first name, last name and movie title.

Sample table: director
``` dir_id |      dir_fname       |      dir_lname
--------+----------------------+----------------------
201 | Alfred               | Hitchcock
202 | Jack                 | Clayton
203 | David                | Lean
204 | Michael              | Cimino
205 | Milos                | Forman
206 | Ridley               | Scott
207 | Stanley              | Kubrick
208 | Bryan                | Singer
209 | Roman                | Polanski
210 | Paul                 | Thomas Anderson
211 | Woody                | Allen
212 | Hayao                | Miyazaki
213 | Frank                | Darabont
214 | Sam                  | Mendes
215 | James                | Cameron
216 | Gus                  | Van Sant
217 | John                 | Boorman
218 | Danny                | Boyle
219 | Christopher          | Nolan
220 | Richard              | Kelly
221 | Kevin                | Spacey
222 | Andrei               | Tarkovsky
223 | Peter                | Jackson
```
Sample table: movie_direction
``` dir_id | mov_id
--------+--------
201 |    901
202 |    902
203 |    903
204 |    904
205 |    905
206 |    906
207 |    907
208 |    908
209 |    909
210 |    910
211 |    911
212 |    912
213 |    913
214 |    914
215 |    915
216 |    916
217 |    917
218 |    918
219 |    919
220 |    920
218 |    921
215 |    922
221 |    923
```
Sample table: movie_cast
``` act_id | mov_id |              role
--------+--------+--------------------------------
101 |    901 | John Scottie Ferguson
102 |    902 | Miss Giddens
103 |    903 | T.E. Lawrence
104 |    904 | Michael
105 |    905 | Antonio Salieri
106 |    906 | Rick Deckard
107 |    907 | Alice Harford
108 |    908 | McManus
110 |    910 | Eddie Adams
111 |    911 | Alvy Singer
112 |    912 | San
113 |    913 | Andy Dufresne
114 |    914 | Lester Burnham
115 |    915 | Rose DeWitt Bukater
116 |    916 | Sean Maguire
117 |    917 | Ed
118 |    918 | Renton
120 |    920 | Elizabeth Darko
121 |    921 | Older Jamal
122 |    922 | Ripley
114 |    923 | Bobby Darin
109 |    909 | J.J. Gittes
119 |    919 | Alfred Borden
```
Sample table: movie
``` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
```

Sample Output:

```      dir_fname       |      dir_lname       |                     mov_title

----------------------+----------------------+----------------------------------
Stanley              | Kubrick              | Eyes Wide Shut
(1 row)
```

Click me to see the solution

4. From the following tables, write a SQL query to find the director of a movie that cast a role as Sean Maguire. Return director first name, last name and movie title.

Sample table: director
``` dir_id |      dir_fname       |      dir_lname
--------+----------------------+----------------------
201 | Alfred               | Hitchcock
202 | Jack                 | Clayton
203 | David                | Lean
204 | Michael              | Cimino
205 | Milos                | Forman
206 | Ridley               | Scott
207 | Stanley              | Kubrick
208 | Bryan                | Singer
209 | Roman                | Polanski
210 | Paul                 | Thomas Anderson
211 | Woody                | Allen
212 | Hayao                | Miyazaki
213 | Frank                | Darabont
214 | Sam                  | Mendes
215 | James                | Cameron
216 | Gus                  | Van Sant
217 | John                 | Boorman
218 | Danny                | Boyle
219 | Christopher          | Nolan
220 | Richard              | Kelly
221 | Kevin                | Spacey
222 | Andrei               | Tarkovsky
223 | Peter                | Jackson
```
Sample table: movie_direction
``` dir_id | mov_id
--------+--------
201 |    901
202 |    902
203 |    903
204 |    904
205 |    905
206 |    906
207 |    907
208 |    908
209 |    909
210 |    910
211 |    911
212 |    912
213 |    913
214 |    914
215 |    915
216 |    916
217 |    917
218 |    918
219 |    919
220 |    920
218 |    921
215 |    922
221 |    923
```
Sample table: movie_cast
``` act_id | mov_id |              role
--------+--------+--------------------------------
101 |    901 | John Scottie Ferguson
102 |    902 | Miss Giddens
103 |    903 | T.E. Lawrence
104 |    904 | Michael
105 |    905 | Antonio Salieri
106 |    906 | Rick Deckard
107 |    907 | Alice Harford
108 |    908 | McManus
110 |    910 | Eddie Adams
111 |    911 | Alvy Singer
112 |    912 | San
113 |    913 | Andy Dufresne
114 |    914 | Lester Burnham
115 |    915 | Rose DeWitt Bukater
116 |    916 | Sean Maguire
117 |    917 | Ed
118 |    918 | Renton
120 |    920 | Elizabeth Darko
121 |    921 | Older Jamal
122 |    922 | Ripley
114 |    923 | Bobby Darin
109 |    909 | J.J. Gittes
119 |    919 | Alfred Borden
```
Sample table: movie
``` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
```

Sample Output:

```      dir_fname       |      dir_lname       |                     mov_title
----------------------+----------------------+----------------------------------------------------
Gus                  | Van Sant             | Good Will Hunting
(1 row)
```

Click me to see the solution

5. From the following table, write a SQL query to find out which actors have not appeared in any movies between 1990 and 2000 (Begin and end values are included.). Return actor first name, last name, movie title and release year.

Sample table: actor
``` act_id |      act_fname       |      act_lname       | act_gender
--------+----------------------+----------------------+------------
101 | James                | Stewart              | M
102 | Deborah              | Kerr                 | F
103 | Peter                | OToole               | M
104 | Robert               | De Niro              | M
105 | F. Murray            | Abraham              | M
106 | Harrison             | Ford                 | M
107 | Nicole               | Kidman               | F
108 | Stephen              | Baldwin              | M
109 | Jack                 | Nicholson            | M
110 | Mark                 | Wahlberg             | M
111 | Woody                | Allen                | M
112 | Claire               | Danes                | F
113 | Tim                  | Robbins              | M
114 | Kevin                | Spacey               | M
115 | Kate                 | Winslet              | F
116 | Robin                | Williams             | M
117 | Jon                  | Voight               | M
118 | Ewan                 | McGregor             | M
119 | Christian            | Bale                 | M
120 | Maggie               | Gyllenhaal           | F
121 | Dev                  | Patel                | M
122 | Sigourney            | Weaver               | F
123 | David                | Aston                | M
124 | Ali                  | Astin                | F
```
Sample table: movie_cast
``` act_id | mov_id |              role
--------+--------+--------------------------------
101 |    901 | John Scottie Ferguson
102 |    902 | Miss Giddens
103 |    903 | T.E. Lawrence
104 |    904 | Michael
105 |    905 | Antonio Salieri
106 |    906 | Rick Deckard
107 |    907 | Alice Harford
108 |    908 | McManus
110 |    910 | Eddie Adams
111 |    911 | Alvy Singer
112 |    912 | San
113 |    913 | Andy Dufresne
114 |    914 | Lester Burnham
115 |    915 | Rose DeWitt Bukater
116 |    916 | Sean Maguire
117 |    917 | Ed
118 |    918 | Renton
120 |    920 | Elizabeth Darko
121 |    921 | Older Jamal
122 |    922 | Ripley
114 |    923 | Bobby Darin
109 |    909 | J.J. Gittes
119 |    919 | Alfred Borden
```
Sample table: movie
``` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
```

Sample Output:

```act_fname		act_lname		mov_title		mov_year
--------------------------------------------------------------------------------
James			Stewart			Vertigo			1958
Deborah			Kerr			The Innocents		1961
Peter			OToole			Lawrence of Arabia	1962
Robert			De Niro			The Deer Hunter		1978
.....
```

Click me to see the solution

6. From the following table, write a SQL query to find the directors who have directed films in a variety of genres. Group the result set on director first name, last name and generic title. Sort the result-set in ascending order by director first name and last name. Return director first name, last name and number of genres movies.

Sample table: director
``` dir_id |      dir_fname       |      dir_lname
--------+----------------------+----------------------
201 | Alfred               | Hitchcock
202 | Jack                 | Clayton
203 | David                | Lean
204 | Michael              | Cimino
205 | Milos                | Forman
206 | Ridley               | Scott
207 | Stanley              | Kubrick
208 | Bryan                | Singer
209 | Roman                | Polanski
210 | Paul                 | Thomas Anderson
211 | Woody                | Allen
212 | Hayao                | Miyazaki
213 | Frank                | Darabont
214 | Sam                  | Mendes
215 | James                | Cameron
216 | Gus                  | Van Sant
217 | John                 | Boorman
218 | Danny                | Boyle
219 | Christopher          | Nolan
220 | Richard              | Kelly
221 | Kevin                | Spacey
222 | Andrei               | Tarkovsky
223 | Peter                | Jackson
```
Sample table: movie_direction
``` dir_id | mov_id
--------+--------
201 |    901
202 |    902
203 |    903
204 |    904
205 |    905
206 |    906
207 |    907
208 |    908
209 |    909
210 |    910
211 |    911
212 |    912
213 |    913
214 |    914
215 |    915
216 |    916
217 |    917
218 |    918
219 |    919
220 |    920
218 |    921
215 |    922
221 |    923
```
Sample table: genres
``` gen_id |      gen_title
--------+----------------------
1001 | Action
1003 | Animation
1004 | Biography
1005 | Comedy
1006 | Crime
1007 | Drama
1008 | Horror
1009 | Music
1010 | Mystery
1011 | Romance
1012 | Thriller
1013 | War
```

Sample table: movie_genres

``` mov_id | gen_id
--------+--------
922 |   1001
917 |   1002
903 |   1002
912 |   1003
911 |   1005
908 |   1006
913 |   1006
926 |   1007
928 |   1007
918 |   1007
921 |   1007
902 |   1008
923 |   1009
907 |   1010
927 |   1010
901 |   1010
914 |   1011
906 |   1012
904 |   1013
```

Sample Output:

```      dir_fname       |      dir_lname       |      gen_title       | count
----------------------+----------------------+----------------------+-------
Alfred               | Hitchcock            | Mystery              |    1
Bryan                | Singer               | Crime                |    1
Danny                | Boyle                | Drama                |    2
David                | Lean                 | Adventure            |    1
.....
```

Click me to see the solution

7. From the following table, write a SQL query to find the movies with year and genres. Return movie title, movie year and generic title.

Sample table: movie
``` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
```
Sample table: genres
``` gen_id |      gen_title
--------+----------------------
1001 | Action
1003 | Animation
1004 | Biography
1005 | Comedy
1006 | Crime
1007 | Drama
1008 | Horror
1009 | Music
1010 | Mystery
1011 | Romance
1012 | Thriller
1013 | War
```

Sample table: movie_genres

``` mov_id | gen_id
--------+--------
922 |   1001
917 |   1002
903 |   1002
912 |   1003
911 |   1005
908 |   1006
913 |   1006
926 |   1007
928 |   1007
918 |   1007
921 |   1007
902 |   1008
923 |   1009
907 |   1010
927 |   1010
901 |   1010
914 |   1011
906 |   1012
904 |   1013
```

Sample Output:

```                     mov_title                      | mov_year |      gen_title
----------------------------------------------------+----------+----------------------
Aliens                                             |     1986 | Action
Lawrence of Arabia                                 |     1962 | Adventure
Princess Mononoke                                  |     1997 | Animation
....
```

Click me to see the solution

8. From the following tables, write a SQL query to find all the movies with year, genres, and name of the director.

Sample table: movie
``` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
```
Sample table: genres
``` gen_id |      gen_title
--------+----------------------
1001 | Action
1003 | Animation
1004 | Biography
1005 | Comedy
1006 | Crime
1007 | Drama
1008 | Horror
1009 | Music
1010 | Mystery
1011 | Romance
1012 | Thriller
1013 | War
```

Sample table: movie_genres

``` mov_id | gen_id
--------+--------
922 |   1001
917 |   1002
903 |   1002
912 |   1003
911 |   1005
908 |   1006
913 |   1006
926 |   1007
928 |   1007
918 |   1007
921 |   1007
902 |   1008
923 |   1009
907 |   1010
927 |   1010
901 |   1010
914 |   1011
906 |   1012
904 |   1013
```
Sample table: director
``` dir_id |      dir_fname       |      dir_lname
--------+----------------------+----------------------
201 | Alfred               | Hitchcock
202 | Jack                 | Clayton
203 | David                | Lean
204 | Michael              | Cimino
205 | Milos                | Forman
206 | Ridley               | Scott
207 | Stanley              | Kubrick
208 | Bryan                | Singer
209 | Roman                | Polanski
210 | Paul                 | Thomas Anderson
211 | Woody                | Allen
212 | Hayao                | Miyazaki
213 | Frank                | Darabont
214 | Sam                  | Mendes
215 | James                | Cameron
216 | Gus                  | Van Sant
217 | John                 | Boorman
218 | Danny                | Boyle
219 | Christopher          | Nolan
220 | Richard              | Kelly
221 | Kevin                | Spacey
222 | Andrei               | Tarkovsky
223 | Peter                | Jackson
```
Sample table: movie_direction
``` dir_id | mov_id
--------+--------
201 |    901
202 |    902
203 |    903
204 |    904
205 |    905
206 |    906
207 |    907
208 |    908
209 |    909
210 |    910
211 |    911
212 |    912
213 |    913
214 |    914
215 |    915
216 |    916
217 |    917
218 |    918
219 |    919
220 |    920
218 |    921
215 |    922
221 |    923
```

Sample Output:

```                     mov_title                      | mov_year |      gen_title       |      dir_fname       |      dir_
----------------------------------------------------+----------+----------------------+----------------------+----------
Vertigo                                            |     1958 | Mystery              | Alfred               | Hitchcock
The Innocents                                      |     1961 | Horror               | Jack                 | Clayton
Lawrence of Arabia                                 |     1962 | Adventure            | David                | Lean
The Deer Hunter                                    |     1978 | War                  | Michael              | Cimino
-- More  --
```

Click me to see the solution

9. From the following tables, write a SQL query to find the movies released before 1st January 1989. Sort the result-set in descending order by date of release. Return movie title, release year, date of release, duration, and first and last name of the director.

Sample table: movie
``` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
```
Sample table: director
``` dir_id |      dir_fname       |      dir_lname
--------+----------------------+----------------------
201 | Alfred               | Hitchcock
202 | Jack                 | Clayton
203 | David                | Lean
204 | Michael              | Cimino
205 | Milos                | Forman
206 | Ridley               | Scott
207 | Stanley              | Kubrick
208 | Bryan                | Singer
209 | Roman                | Polanski
210 | Paul                 | Thomas Anderson
211 | Woody                | Allen
212 | Hayao                | Miyazaki
213 | Frank                | Darabont
214 | Sam                  | Mendes
215 | James                | Cameron
216 | Gus                  | Van Sant
217 | John                 | Boorman
218 | Danny                | Boyle
219 | Christopher          | Nolan
220 | Richard              | Kelly
221 | Kevin                | Spacey
222 | Andrei               | Tarkovsky
223 | Peter                | Jackson
```
Sample table: movie_direction
``` dir_id | mov_id
--------+--------
201 |    901
202 |    902
203 |    903
204 |    904
205 |    905
206 |    906
207 |    907
208 |    908
209 |    909
210 |    910
211 |    911
212 |    912
213 |    913
214 |    914
215 |    915
216 |    916
217 |    917
218 |    918
219 |    919
220 |    920
218 |    921
215 |    922
221 |    923
```

Sample Output:

```                     mov_title                      | mov_year | mov_dt_rel | mov_time |      dir_fname       |      dir_lname
----------------------------------------------------+----------+------------+----------+----------------------+----------------------
Aliens                                             |     1986 | 1986-08-29 |      137 | James                | Cameron
Amadeus                                            |     1984 | 1985-01-07 |      160 | Milos                | Forman
Deliverance                                        |     1972 | 1982-10-05 |      109 | John                 | Boorman
Blade Runner                                       |     1982 | 1982-09-09 |      117 | Ridley               | Scott
.....
```

Click me to see the solution

10. From the following table, write a SQL query to calculate the average movie length and count the number of movies in each genre. Return genre title, average time and number of movies for each genre.

Sample table: movie
``` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
```
Sample table: genres
``` gen_id |      gen_title
--------+----------------------
1001 | Action
1003 | Animation
1004 | Biography
1005 | Comedy
1006 | Crime
1007 | Drama
1008 | Horror
1009 | Music
1010 | Mystery
1011 | Romance
1012 | Thriller
1013 | War
```

Sample table: movie_genres

``` mov_id | gen_id
--------+--------
922 |   1001
917 |   1002
903 |   1002
912 |   1003
911 |   1005
908 |   1006
913 |   1006
926 |   1007
928 |   1007
918 |   1007
921 |   1007
902 |   1008
923 |   1009
907 |   1010
927 |   1010
901 |   1010
914 |   1011
906 |   1012
904 |   1013
```

Sample Output:

```      gen_title       |         avg          | count
----------------------+----------------------+-------
Comedy               |  93.0000000000000000 |     1
Drama                | 134.2500000000000000 |     4
Horror               | 100.0000000000000000 |     1
.....
```

Click me to see the solution

11. From the following table, write a SQL query to find movies with the shortest duration. Return movie title, movie year, director first name, last name, actor first name, last name and role.

Sample table: movie
``` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
```
Sample table: actor
``` act_id |      act_fname       |      act_lname       | act_gender
--------+----------------------+----------------------+------------
101 | James                | Stewart              | M
102 | Deborah              | Kerr                 | F
103 | Peter                | OToole               | M
104 | Robert               | De Niro              | M
105 | F. Murray            | Abraham              | M
106 | Harrison             | Ford                 | M
107 | Nicole               | Kidman               | F
108 | Stephen              | Baldwin              | M
109 | Jack                 | Nicholson            | M
110 | Mark                 | Wahlberg             | M
111 | Woody                | Allen                | M
112 | Claire               | Danes                | F
113 | Tim                  | Robbins              | M
114 | Kevin                | Spacey               | M
115 | Kate                 | Winslet              | F
116 | Robin                | Williams             | M
117 | Jon                  | Voight               | M
118 | Ewan                 | McGregor             | M
119 | Christian            | Bale                 | M
120 | Maggie               | Gyllenhaal           | F
121 | Dev                  | Patel                | M
122 | Sigourney            | Weaver               | F
123 | David                | Aston                | M
124 | Ali                  | Astin                | F
```
Sample table: director
``` dir_id |      dir_fname       |      dir_lname
--------+----------------------+----------------------
201 | Alfred               | Hitchcock
202 | Jack                 | Clayton
203 | David                | Lean
204 | Michael              | Cimino
205 | Milos                | Forman
206 | Ridley               | Scott
207 | Stanley              | Kubrick
208 | Bryan                | Singer
209 | Roman                | Polanski
210 | Paul                 | Thomas Anderson
211 | Woody                | Allen
212 | Hayao                | Miyazaki
213 | Frank                | Darabont
214 | Sam                  | Mendes
215 | James                | Cameron
216 | Gus                  | Van Sant
217 | John                 | Boorman
218 | Danny                | Boyle
219 | Christopher          | Nolan
220 | Richard              | Kelly
221 | Kevin                | Spacey
222 | Andrei               | Tarkovsky
223 | Peter                | Jackson
```
Sample table: movie_direction
``` dir_id | mov_id
--------+--------
201 |    901
202 |    902
203 |    903
204 |    904
205 |    905
206 |    906
207 |    907
208 |    908
209 |    909
210 |    910
211 |    911
212 |    912
213 |    913
214 |    914
215 |    915
216 |    916
217 |    917
218 |    918
219 |    919
220 |    920
218 |    921
215 |    922
221 |    923
```
Sample table: movie_cast
``` act_id | mov_id |              role
--------+--------+--------------------------------
101 |    901 | John Scottie Ferguson
102 |    902 | Miss Giddens
103 |    903 | T.E. Lawrence
104 |    904 | Michael
105 |    905 | Antonio Salieri
106 |    906 | Rick Deckard
107 |    907 | Alice Harford
108 |    908 | McManus
110 |    910 | Eddie Adams
111 |    911 | Alvy Singer
112 |    912 | San
113 |    913 | Andy Dufresne
114 |    914 | Lester Burnham
115 |    915 | Rose DeWitt Bukater
116 |    916 | Sean Maguire
117 |    917 | Ed
118 |    918 | Renton
120 |    920 | Elizabeth Darko
121 |    921 | Older Jamal
122 |    922 | Ripley
114 |    923 | Bobby Darin
109 |    909 | J.J. Gittes
119 |    919 | Alfred Borden
```

Sample Output:

```                     mov_title                      | mov_year |      dir_fname       |      dir_lname       |      act_fname       |      act_lname       |              role
----------------------------------------------------+----------+----------------------+----------------------+----------------------+----------------------+-------------------------------
Annie Hall                                         |     1977 | Woody                | Allen                | Woody                | Allen                | Alvy Singer
(1 rows)

```

Click me to see the solution

12. From the following table, write a SQL query to find the years in which a movie received a rating of 3 or 4. Sort the result in increasing order on movie year.

Sample table: movie
``` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
```
Sample table: rating
``` mov_id | rev_id | rev_stars | num_o_ratings
--------+--------+-----------+---------------
901 |   9001 |      8.40 |        263575
902 |   9002 |      7.90 |         20207
903 |   9003 |      8.30 |        202778
906 |   9005 |      8.20 |        484746
924 |   9006 |      7.30 |
908 |   9007 |      8.60 |        779489
909 |   9008 |           |        227235
910 |   9009 |      3.00 |        195961
911 |   9010 |      8.10 |        203875
912 |   9011 |      8.40 |
914 |   9013 |      7.00 |        862618
915 |   9001 |      7.70 |        830095
916 |   9014 |      4.00 |        642132
925 |   9015 |      7.70 |         81328
918 |   9016 |           |        580301
920 |   9017 |      8.10 |        609451
921 |   9018 |      8.00 |        667758
922 |   9019 |      8.40 |        511613
923 |   9020 |      6.70 |         13091
```

Sample Output:

``` mov_year
----------
1997
(1 row)
```

Click me to see the solution

13. From the following tables, write a SQL query to get the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars.

Sample table: movie
``` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
```
Sample table: rating
``` mov_id | rev_id | rev_stars | num_o_ratings
--------+--------+-----------+---------------
901 |   9001 |      8.40 |        263575
902 |   9002 |      7.90 |         20207
903 |   9003 |      8.30 |        202778
906 |   9005 |      8.20 |        484746
924 |   9006 |      7.30 |
908 |   9007 |      8.60 |        779489
909 |   9008 |           |        227235
910 |   9009 |      3.00 |        195961
911 |   9010 |      8.10 |        203875
912 |   9011 |      8.40 |
914 |   9013 |      7.00 |        862618
915 |   9001 |      7.70 |        830095
916 |   9014 |      4.00 |        642132
925 |   9015 |      7.70 |         81328
918 |   9016 |           |        580301
920 |   9017 |      8.10 |        609451
921 |   9018 |      8.00 |        667758
922 |   9019 |      8.40 |        511613
923 |   9020 |      6.70 |         13091
```
Sample table: reviewer
``` rev_id |            rev_name
--------+--------------------------------
9001 | Righty Sock
9002 | Jack Malvern
9003 | Flagrant Baronessa
9004 | Alec Shaw
9005 |
9006 | Victor Woeltjen
9007 | Simon Wright
9008 | Neal Wruck
9009 | Paul Monks
9010 | Mike Salvati
9011 |
9012 | Wesley S. Walker
9013 | Sasha Goldshtein
9014 | Josh Cates
9015 | Krug Stillo
9016 | Scott LeBrun
9017 | Hannah Steele
9019 | Brandt Sponseller
```

Sample Output:

```            rev_name            |                     mov_title                      | rev_stars
--------------------------------+----------------------------------------------------+-----------
Brandt Sponseller              | Aliens                                             |      8.40
Flagrant Baronessa             | Lawrence of Arabia                                 |      8.30
Hannah Steele                  | Donnie Darko                                       |      8.10
Jack Malvern                   | The Innocents                                      |      7.90
.....
```

Click me to see the solution

14. From the following table, write a SQL query to find those movies that have at least one rating and received the most stars. Sort the result-set on movie title. Return movie title and maximum review stars.

Sample table: movie
``` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
```
Sample table: rating
``` mov_id | rev_id | rev_stars | num_o_ratings
--------+--------+-----------+---------------
901 |   9001 |      8.40 |        263575
902 |   9002 |      7.90 |         20207
903 |   9003 |      8.30 |        202778
906 |   9005 |      8.20 |        484746
924 |   9006 |      7.30 |
908 |   9007 |      8.60 |        779489
909 |   9008 |           |        227235
910 |   9009 |      3.00 |        195961
911 |   9010 |      8.10 |        203875
912 |   9011 |      8.40 |
914 |   9013 |      7.00 |        862618
915 |   9001 |      7.70 |        830095
916 |   9014 |      4.00 |        642132
925 |   9015 |      7.70 |         81328
918 |   9016 |           |        580301
920 |   9017 |      8.10 |        609451
921 |   9018 |      8.00 |        667758
922 |   9019 |      8.40 |        511613
923 |   9020 |      6.70 |         13091
```

Sample Output:

```                     mov_title                      | max
----------------------------------------------------+------
Aliens                                             | 8.40
American Beauty                                    | 7.00
Annie Hall                                         | 8.10
Avatar                                             | 7.30
.....
```

Click me to see the solution

15. From the following table, write a SQL query to find out which movies have received ratings. Return movie title, director first name, director last name and review stars.

Sample table: movie
``` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
```
Sample table: rating
``` mov_id | rev_id | rev_stars | num_o_ratings
--------+--------+-----------+---------------
901 |   9001 |      8.40 |        263575
902 |   9002 |      7.90 |         20207
903 |   9003 |      8.30 |        202778
906 |   9005 |      8.20 |        484746
924 |   9006 |      7.30 |
908 |   9007 |      8.60 |        779489
909 |   9008 |           |        227235
910 |   9009 |      3.00 |        195961
911 |   9010 |      8.10 |        203875
912 |   9011 |      8.40 |
914 |   9013 |      7.00 |        862618
915 |   9001 |      7.70 |        830095
916 |   9014 |      4.00 |        642132
925 |   9015 |      7.70 |         81328
918 |   9016 |           |        580301
920 |   9017 |      8.10 |        609451
921 |   9018 |      8.00 |        667758
922 |   9019 |      8.40 |        511613
923 |   9020 |      6.70 |         13091
```
Sample table: movie_direction
``` dir_id | mov_id
--------+--------
201 |    901
202 |    902
203 |    903
204 |    904
205 |    905
206 |    906
207 |    907
208 |    908
209 |    909
210 |    910
211 |    911
212 |    912
213 |    913
214 |    914
215 |    915
216 |    916
217 |    917
218 |    918
219 |    919
220 |    920
218 |    921
215 |    922
221 |    923
```
Sample table: director
``` dir_id |      dir_fname       |      dir_lname
--------+----------------------+----------------------
201 | Alfred               | Hitchcock
202 | Jack                 | Clayton
203 | David                | Lean
204 | Michael              | Cimino
205 | Milos                | Forman
206 | Ridley               | Scott
207 | Stanley              | Kubrick
208 | Bryan                | Singer
209 | Roman                | Polanski
210 | Paul                 | Thomas Anderson
211 | Woody                | Allen
212 | Hayao                | Miyazaki
213 | Frank                | Darabont
214 | Sam                  | Mendes
215 | James                | Cameron
216 | Gus                  | Van Sant
217 | John                 | Boorman
218 | Danny                | Boyle
219 | Christopher          | Nolan
220 | Richard              | Kelly
221 | Kevin                | Spacey
222 | Andrei               | Tarkovsky
223 | Peter                | Jackson
```

Sample Output:

```                     mov_title                      |      dir_fname       |      dir_lname       | rev_stars
----------------------------------------------------+----------------------+----------------------+-----------
Vertigo                                            | Alfred               | Hitchcock            |      8.40
The Innocents                                      | Jack                 | Clayton              |      7.90
Lawrence of Arabia                                 | David                | Lean                 |      8.30
Blade Runner                                       | Ridley               | Scott                |      8.20
.....
```

Click me to see the solution

16. From the following table, write a SQL query to find movies in which one or more actors have acted in more than one film. Return movie title, actor first and last name, and the role.

Sample table: movie
``` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
```
Sample table: movie_cast
``` act_id | mov_id |              role
--------+--------+--------------------------------
101 |    901 | John Scottie Ferguson
102 |    902 | Miss Giddens
103 |    903 | T.E. Lawrence
104 |    904 | Michael
105 |    905 | Antonio Salieri
106 |    906 | Rick Deckard
107 |    907 | Alice Harford
108 |    908 | McManus
110 |    910 | Eddie Adams
111 |    911 | Alvy Singer
112 |    912 | San
113 |    913 | Andy Dufresne
114 |    914 | Lester Burnham
115 |    915 | Rose DeWitt Bukater
116 |    916 | Sean Maguire
117 |    917 | Ed
118 |    918 | Renton
120 |    920 | Elizabeth Darko
121 |    921 | Older Jamal
122 |    922 | Ripley
114 |    923 | Bobby Darin
109 |    909 | J.J. Gittes
119 |    919 | Alfred Borden
```
Sample table: actor
``` act_id |      act_fname       |      act_lname       | act_gender
--------+----------------------+----------------------+------------
101 | James                | Stewart              | M
102 | Deborah              | Kerr                 | F
103 | Peter                | OToole               | M
104 | Robert               | De Niro              | M
105 | F. Murray            | Abraham              | M
106 | Harrison             | Ford                 | M
107 | Nicole               | Kidman               | F
108 | Stephen              | Baldwin              | M
109 | Jack                 | Nicholson            | M
110 | Mark                 | Wahlberg             | M
111 | Woody                | Allen                | M
112 | Claire               | Danes                | F
113 | Tim                  | Robbins              | M
114 | Kevin                | Spacey               | M
115 | Kate                 | Winslet              | F
116 | Robin                | Williams             | M
117 | Jon                  | Voight               | M
118 | Ewan                 | McGregor             | M
119 | Christian            | Bale                 | M
120 | Maggie               | Gyllenhaal           | F
121 | Dev                  | Patel                | M
122 | Sigourney            | Weaver               | F
123 | David                | Aston                | M
124 | Ali                  | Astin                | F
```

Sample Output:

```                     mov_title                      |      act_fname       |      act_lname       |              role
----------------------------------------------------+----------------------+----------------------+--------------------------------
American Beauty                                    | Kevin                | Spacey               | Lester Burnham
Beyond the Sea                                     | Kevin                | Spacey               | Bobby Darin
(2 rows)
```

Click me to see the solution

17. From the following tables, write a SQL query to find the actor whose first name is 'Claire' and last name is 'Danes'. Return director first name, last name, movie title, actor first name and last name, role.

Sample table: movie
``` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
```
Sample table: movie_cast
``` act_id | mov_id |              role
--------+--------+--------------------------------
101 |    901 | John Scottie Ferguson
102 |    902 | Miss Giddens
103 |    903 | T.E. Lawrence
104 |    904 | Michael
105 |    905 | Antonio Salieri
106 |    906 | Rick Deckard
107 |    907 | Alice Harford
108 |    908 | McManus
110 |    910 | Eddie Adams
111 |    911 | Alvy Singer
112 |    912 | San
113 |    913 | Andy Dufresne
114 |    914 | Lester Burnham
115 |    915 | Rose DeWitt Bukater
116 |    916 | Sean Maguire
117 |    917 | Ed
118 |    918 | Renton
120 |    920 | Elizabeth Darko
121 |    921 | Older Jamal
122 |    922 | Ripley
114 |    923 | Bobby Darin
109 |    909 | J.J. Gittes
119 |    919 | Alfred Borden
```
Sample table: actor
``` act_id |      act_fname       |      act_lname       | act_gender
--------+----------------------+----------------------+------------
101 | James                | Stewart              | M
102 | Deborah              | Kerr                 | F
103 | Peter                | OToole               | M
104 | Robert               | De Niro              | M
105 | F. Murray            | Abraham              | M
106 | Harrison             | Ford                 | M
107 | Nicole               | Kidman               | F
108 | Stephen              | Baldwin              | M
109 | Jack                 | Nicholson            | M
110 | Mark                 | Wahlberg             | M
111 | Woody                | Allen                | M
112 | Claire               | Danes                | F
113 | Tim                  | Robbins              | M
114 | Kevin                | Spacey               | M
115 | Kate                 | Winslet              | F
116 | Robin                | Williams             | M
117 | Jon                  | Voight               | M
118 | Ewan                 | McGregor             | M
119 | Christian            | Bale                 | M
120 | Maggie               | Gyllenhaal           | F
121 | Dev                  | Patel                | M
122 | Sigourney            | Weaver               | F
123 | David                | Aston                | M
124 | Ali                  | Astin                | F
```
Sample table: director
``` dir_id |      dir_fname       |      dir_lname
--------+----------------------+----------------------
201 | Alfred               | Hitchcock
202 | Jack                 | Clayton
203 | David                | Lean
204 | Michael              | Cimino
205 | Milos                | Forman
206 | Ridley               | Scott
207 | Stanley              | Kubrick
208 | Bryan                | Singer
209 | Roman                | Polanski
210 | Paul                 | Thomas Anderson
211 | Woody                | Allen
212 | Hayao                | Miyazaki
213 | Frank                | Darabont
214 | Sam                  | Mendes
215 | James                | Cameron
216 | Gus                  | Van Sant
217 | John                 | Boorman
218 | Danny                | Boyle
219 | Christopher          | Nolan
220 | Richard              | Kelly
221 | Kevin                | Spacey
222 | Andrei               | Tarkovsky
223 | Peter                | Jackson
```
Sample table: movie_direction
``` dir_id | mov_id
--------+--------
201 |    901
202 |    902
203 |    903
204 |    904
205 |    905
206 |    906
207 |    907
208 |    908
209 |    909
210 |    910
211 |    911
212 |    912
213 |    913
214 |    914
215 |    915
216 |    916
217 |    917
218 |    918
219 |    919
220 |    920
218 |    921
215 |    922
221 |    923
```

Sample Output:

```      dir_fname       |      dir_lname       |                     mov_title                      |      act_fname       |      act_lname       |              role
----------------------+----------------------+----------------------------------------------------+----------------------+----------------------+--------------------------------
Hayao                | Miyazaki             | Princess Mononoke                                  | Claire               | Danes                | San
(1 row)
```

Click me to see the solution

18. From the following table, write a SQL query to find for actors whose films have been directed by them. Return actor first name, last name, movie title and role.

Sample table: movie
``` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
```
Sample table: movie_cast
``` act_id | mov_id |              role
--------+--------+--------------------------------
101 |    901 | John Scottie Ferguson
102 |    902 | Miss Giddens
103 |    903 | T.E. Lawrence
104 |    904 | Michael
105 |    905 | Antonio Salieri
106 |    906 | Rick Deckard
107 |    907 | Alice Harford
108 |    908 | McManus
110 |    910 | Eddie Adams
111 |    911 | Alvy Singer
112 |    912 | San
113 |    913 | Andy Dufresne
114 |    914 | Lester Burnham
115 |    915 | Rose DeWitt Bukater
116 |    916 | Sean Maguire
117 |    917 | Ed
118 |    918 | Renton
120 |    920 | Elizabeth Darko
121 |    921 | Older Jamal
122 |    922 | Ripley
114 |    923 | Bobby Darin
109 |    909 | J.J. Gittes
119 |    919 | Alfred Borden
```
Sample table: actor
``` act_id |      act_fname       |      act_lname       | act_gender
--------+----------------------+----------------------+------------
101 | James                | Stewart              | M
102 | Deborah              | Kerr                 | F
103 | Peter                | OToole               | M
104 | Robert               | De Niro              | M
105 | F. Murray            | Abraham              | M
106 | Harrison             | Ford                 | M
107 | Nicole               | Kidman               | F
108 | Stephen              | Baldwin              | M
109 | Jack                 | Nicholson            | M
110 | Mark                 | Wahlberg             | M
111 | Woody                | Allen                | M
112 | Claire               | Danes                | F
113 | Tim                  | Robbins              | M
114 | Kevin                | Spacey               | M
115 | Kate                 | Winslet              | F
116 | Robin                | Williams             | M
117 | Jon                  | Voight               | M
118 | Ewan                 | McGregor             | M
119 | Christian            | Bale                 | M
120 | Maggie               | Gyllenhaal           | F
121 | Dev                  | Patel                | M
122 | Sigourney            | Weaver               | F
123 | David                | Aston                | M
124 | Ali                  | Astin                | F
```
Sample table: director
``` dir_id |      dir_fname       |      dir_lname
--------+----------------------+----------------------
201 | Alfred               | Hitchcock
202 | Jack                 | Clayton
203 | David                | Lean
204 | Michael              | Cimino
205 | Milos                | Forman
206 | Ridley               | Scott
207 | Stanley              | Kubrick
208 | Bryan                | Singer
209 | Roman                | Polanski
210 | Paul                 | Thomas Anderson
211 | Woody                | Allen
212 | Hayao                | Miyazaki
213 | Frank                | Darabont
214 | Sam                  | Mendes
215 | James                | Cameron
216 | Gus                  | Van Sant
217 | John                 | Boorman
218 | Danny                | Boyle
219 | Christopher          | Nolan
220 | Richard              | Kelly
221 | Kevin                | Spacey
222 | Andrei               | Tarkovsky
223 | Peter                | Jackson
```
Sample table: movie_direction
``` dir_id | mov_id
--------+--------
201 |    901
202 |    902
203 |    903
204 |    904
205 |    905
206 |    906
207 |    907
208 |    908
209 |    909
210 |    910
211 |    911
212 |    912
213 |    913
214 |    914
215 |    915
216 |    916
217 |    917
218 |    918
219 |    919
220 |    920
218 |    921
215 |    922
221 |    923
```

Sample Output:

```      act_fname       |      act_lname       |                     mov_title                      |              role
----------------------+----------------------+----------------------------------------------------+--------------------------------
Woody                | Allen                | Annie Hall                                         | Alvy Singer
Kevin                | Spacey               | Beyond the Sea                                     | Bobby Darin
(2 rows)
```

Click me to see the solution

19. From the following tables, write a SQL query to find the cast list of the movie â€˜Chinatownâ€™. Return first name, last name.

Sample table: movie
``` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
```
Sample table: movie_cast
``` act_id | mov_id |              role
--------+--------+--------------------------------
101 |    901 | John Scottie Ferguson
102 |    902 | Miss Giddens
103 |    903 | T.E. Lawrence
104 |    904 | Michael
105 |    905 | Antonio Salieri
106 |    906 | Rick Deckard
107 |    907 | Alice Harford
108 |    908 | McManus
110 |    910 | Eddie Adams
111 |    911 | Alvy Singer
112 |    912 | San
113 |    913 | Andy Dufresne
114 |    914 | Lester Burnham
115 |    915 | Rose DeWitt Bukater
116 |    916 | Sean Maguire
117 |    917 | Ed
118 |    918 | Renton
120 |    920 | Elizabeth Darko
121 |    921 | Older Jamal
122 |    922 | Ripley
114 |    923 | Bobby Darin
109 |    909 | J.J. Gittes
119 |    919 | Alfred Borden
```
Sample table: actor
``` act_id |      act_fname       |      act_lname       | act_gender
--------+----------------------+----------------------+------------
101 | James                | Stewart              | M
102 | Deborah              | Kerr                 | F
103 | Peter                | OToole               | M
104 | Robert               | De Niro              | M
105 | F. Murray            | Abraham              | M
106 | Harrison             | Ford                 | M
107 | Nicole               | Kidman               | F
108 | Stephen              | Baldwin              | M
109 | Jack                 | Nicholson            | M
110 | Mark                 | Wahlberg             | M
111 | Woody                | Allen                | M
112 | Claire               | Danes                | F
113 | Tim                  | Robbins              | M
114 | Kevin                | Spacey               | M
115 | Kate                 | Winslet              | F
116 | Robin                | Williams             | M
117 | Jon                  | Voight               | M
118 | Ewan                 | McGregor             | M
119 | Christian            | Bale                 | M
120 | Maggie               | Gyllenhaal           | F
121 | Dev                  | Patel                | M
122 | Sigourney            | Weaver               | F
123 | David                | Aston                | M
124 | Ali                  | Astin                | F
```

Sample Output:

```      act_fname       |      act_lname
----------------------+----------------------
Jack                 | Nicholson
(1 row)
```

Click me to see the solution

20. From the following tables, write a SQL query to find those movies where actorâ€™s first name is 'Harrison' and last name is 'Ford'. Return movie title.

Sample table: movie
``` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
```
Sample table: movie_cast
``` act_id | mov_id |              role
--------+--------+--------------------------------
101 |    901 | John Scottie Ferguson
102 |    902 | Miss Giddens
103 |    903 | T.E. Lawrence
104 |    904 | Michael
105 |    905 | Antonio Salieri
106 |    906 | Rick Deckard
107 |    907 | Alice Harford
108 |    908 | McManus
110 |    910 | Eddie Adams
111 |    911 | Alvy Singer
112 |    912 | San
113 |    913 | Andy Dufresne
114 |    914 | Lester Burnham
115 |    915 | Rose DeWitt Bukater
116 |    916 | Sean Maguire
117 |    917 | Ed
118 |    918 | Renton
120 |    920 | Elizabeth Darko
121 |    921 | Older Jamal
122 |    922 | Ripley
114 |    923 | Bobby Darin
109 |    909 | J.J. Gittes
119 |    919 | Alfred Borden
```
Sample table: actor
``` act_id |      act_fname       |      act_lname       | act_gender
--------+----------------------+----------------------+------------
101 | James                | Stewart              | M
102 | Deborah              | Kerr                 | F
103 | Peter                | OToole               | M
104 | Robert               | De Niro              | M
105 | F. Murray            | Abraham              | M
106 | Harrison             | Ford                 | M
107 | Nicole               | Kidman               | F
108 | Stephen              | Baldwin              | M
109 | Jack                 | Nicholson            | M
110 | Mark                 | Wahlberg             | M
111 | Woody                | Allen                | M
112 | Claire               | Danes                | F
113 | Tim                  | Robbins              | M
114 | Kevin                | Spacey               | M
115 | Kate                 | Winslet              | F
116 | Robin                | Williams             | M
117 | Jon                  | Voight               | M
118 | Ewan                 | McGregor             | M
119 | Christian            | Bale                 | M
120 | Maggie               | Gyllenhaal           | F
121 | Dev                  | Patel                | M
122 | Sigourney            | Weaver               | F
123 | David                | Aston                | M
124 | Ali                  | Astin                | F
```

Sample Output:

```                     mov_title
----------------------------------------------------
(1 row)
```

Click me to see the solution

21. From the following tables, write a SQL query to find the highest-rated movies. Return movie title, movie year, review stars and releasing country.

Sample table: movie
``` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
```
Sample table: rating
``` mov_id | rev_id | rev_stars | num_o_ratings
--------+--------+-----------+---------------
901 |   9001 |      8.40 |        263575
902 |   9002 |      7.90 |         20207
903 |   9003 |      8.30 |        202778
906 |   9005 |      8.20 |        484746
924 |   9006 |      7.30 |
908 |   9007 |      8.60 |        779489
909 |   9008 |           |        227235
910 |   9009 |      3.00 |        195961
911 |   9010 |      8.10 |        203875
912 |   9011 |      8.40 |
914 |   9013 |      7.00 |        862618
915 |   9001 |      7.70 |        830095
916 |   9014 |      4.00 |        642132
925 |   9015 |      7.70 |         81328
918 |   9016 |           |        580301
920 |   9017 |      8.10 |        609451
921 |   9018 |      8.00 |        667758
922 |   9019 |      8.40 |        511613
923 |   9020 |      6.70 |         13091
```

Sample Output:

```                     mov_title                      | mov_year | rev_stars | mov_rel_country
----------------------------------------------------+----------+-----------+-----------------
The Usual Suspects                                 |     1995 |      8.60 | UK
(1 row)
```

Click me to see the solution

22. From the following tables, write a SQL query to find the highest-rated â€˜Mystery Moviesâ€™. Return the title, year, and rating.

Sample table: movie
``` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
```
Sample table: genres
``` gen_id |      gen_title
--------+----------------------
1001 | Action
1003 | Animation
1004 | Biography
1005 | Comedy
1006 | Crime
1007 | Drama
1008 | Horror
1009 | Music
1010 | Mystery
1011 | Romance
1012 | Thriller
1013 | War
```

Sample table: movie_genres

``` mov_id | gen_id
--------+--------
922 |   1001
917 |   1002
903 |   1002
912 |   1003
911 |   1005
908 |   1006
913 |   1006
926 |   1007
928 |   1007
918 |   1007
921 |   1007
902 |   1008
923 |   1009
907 |   1010
927 |   1010
901 |   1010
914 |   1011
906 |   1012
904 |   1013
```
Sample table: rating
``` mov_id | rev_id | rev_stars | num_o_ratings
--------+--------+-----------+---------------
901 |   9001 |      8.40 |        263575
902 |   9002 |      7.90 |         20207
903 |   9003 |      8.30 |        202778
906 |   9005 |      8.20 |        484746
924 |   9006 |      7.30 |
908 |   9007 |      8.60 |        779489
909 |   9008 |           |        227235
910 |   9009 |      3.00 |        195961
911 |   9010 |      8.10 |        203875
912 |   9011 |      8.40 |
914 |   9013 |      7.00 |        862618
915 |   9001 |      7.70 |        830095
916 |   9014 |      4.00 |        642132
925 |   9015 |      7.70 |         81328
918 |   9016 |           |        580301
920 |   9017 |      8.10 |        609451
921 |   9018 |      8.00 |        667758
922 |   9019 |      8.40 |        511613
923 |   9020 |      6.70 |         13091
```

Sample Output:

```                     mov_title                      | mov_year | rev_stars
----------------------------------------------------+----------+-----------
Vertigo                                            |     1958 |      8.40
(1 row)
```

Click me to see the solution

23. From the following tables, write a SQL query to find the years when most of the â€˜Mystery Moviesâ€™ produced. Count the number of generic title and compute their average rating. Group the result set on movie release year, generic title. Return movie year, generic title, number of generic title and average rating.

Sample table: movie
``` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
```
Sample table: genres
``` gen_id |      gen_title
--------+----------------------
1001 | Action
1003 | Animation
1004 | Biography
1005 | Comedy
1006 | Crime
1007 | Drama
1008 | Horror
1009 | Music
1010 | Mystery
1011 | Romance
1012 | Thriller
1013 | War
```

Sample table: movie_genres

``` mov_id | gen_id
--------+--------
922 |   1001
917 |   1002
903 |   1002
912 |   1003
911 |   1005
908 |   1006
913 |   1006
926 |   1007
928 |   1007
918 |   1007
921 |   1007
902 |   1008
923 |   1009
907 |   1010
927 |   1010
901 |   1010
914 |   1011
906 |   1012
904 |   1013
```
Sample table: rating
``` mov_id | rev_id | rev_stars | num_o_ratings
--------+--------+-----------+---------------
901 |   9001 |      8.40 |        263575
902 |   9002 |      7.90 |         20207
903 |   9003 |      8.30 |        202778
906 |   9005 |      8.20 |        484746
924 |   9006 |      7.30 |
908 |   9007 |      8.60 |        779489
909 |   9008 |           |        227235
910 |   9009 |      3.00 |        195961
911 |   9010 |      8.10 |        203875
912 |   9011 |      8.40 |
914 |   9013 |      7.00 |        862618
915 |   9001 |      7.70 |        830095
916 |   9014 |      4.00 |        642132
925 |   9015 |      7.70 |         81328
918 |   9016 |           |        580301
920 |   9017 |      8.10 |        609451
921 |   9018 |      8.00 |        667758
922 |   9019 |      8.40 |        511613
923 |   9020 |      6.70 |         13091
```

Sample Output:

``` mov_year |      gen_title       | count |        avg
----------+----------------------+-------+--------------------
1958 | Mystery              |     1 | 8.4000000000000000
(1 row)
```

Click me to see the solution

24. From the following tables, write a query in SQL to generate a report, which contain the fields movie title, name of the female actor, year of the movie, role, movie genres, the director, date of release, and rating of that movie.

Sample table: movie
``` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
```
Sample table: genres
``` gen_id |      gen_title
--------+----------------------
1001 | Action
1003 | Animation
1004 | Biography
1005 | Comedy
1006 | Crime
1007 | Drama
1008 | Horror
1009 | Music
1010 | Mystery
1011 | Romance
1012 | Thriller
1013 | War
```

Sample table: movie_genres

``` mov_id | gen_id
--------+--------
922 |   1001
917 |   1002
903 |   1002
912 |   1003
911 |   1005
908 |   1006
913 |   1006
926 |   1007
928 |   1007
918 |   1007
921 |   1007
902 |   1008
923 |   1009
907 |   1010
927 |   1010
901 |   1010
914 |   1011
906 |   1012
904 |   1013
```
Sample table: rating
``` mov_id | rev_id | rev_stars | num_o_ratings
--------+--------+-----------+---------------
901 |   9001 |      8.40 |        263575
902 |   9002 |      7.90 |         20207
903 |   9003 |      8.30 |        202778
906 |   9005 |      8.20 |        484746
924 |   9006 |      7.30 |
908 |   9007 |      8.60 |        779489
909 |   9008 |           |        227235
910 |   9009 |      3.00 |        195961
911 |   9010 |      8.10 |        203875
912 |   9011 |      8.40 |
914 |   9013 |      7.00 |        862618
915 |   9001 |      7.70 |        830095
916 |   9014 |      4.00 |        642132
925 |   9015 |      7.70 |         81328
918 |   9016 |           |        580301
920 |   9017 |      8.10 |        609451
921 |   9018 |      8.00 |        667758
922 |   9019 |      8.40 |        511613
923 |   9020 |      6.70 |         13091
```
Sample table: actor
``` act_id |      act_fname       |      act_lname       | act_gender
--------+----------------------+----------------------+------------
101 | James                | Stewart              | M
102 | Deborah              | Kerr                 | F
103 | Peter                | OToole               | M
104 | Robert               | De Niro              | M
105 | F. Murray            | Abraham              | M
106 | Harrison             | Ford                 | M
107 | Nicole               | Kidman               | F
108 | Stephen              | Baldwin              | M
109 | Jack                 | Nicholson            | M
110 | Mark                 | Wahlberg             | M
111 | Woody                | Allen                | M
112 | Claire               | Danes                | F
113 | Tim                  | Robbins              | M
114 | Kevin                | Spacey               | M
115 | Kate                 | Winslet              | F
116 | Robin                | Williams             | M
117 | Jon                  | Voight               | M
118 | Ewan                 | McGregor             | M
119 | Christian            | Bale                 | M
120 | Maggie               | Gyllenhaal           | F
121 | Dev                  | Patel                | M
122 | Sigourney            | Weaver               | F
123 | David                | Aston                | M
124 | Ali                  | Astin                | F
```
Sample table: director
``` dir_id |      dir_fname       |      dir_lname
--------+----------------------+----------------------
201 | Alfred               | Hitchcock
202 | Jack                 | Clayton
203 | David                | Lean
204 | Michael              | Cimino
205 | Milos                | Forman
206 | Ridley               | Scott
207 | Stanley              | Kubrick
208 | Bryan                | Singer
209 | Roman                | Polanski
210 | Paul                 | Thomas Anderson
211 | Woody                | Allen
212 | Hayao                | Miyazaki
213 | Frank                | Darabont
214 | Sam                  | Mendes
215 | James                | Cameron
216 | Gus                  | Van Sant
217 | John                 | Boorman
218 | Danny                | Boyle
219 | Christopher          | Nolan
220 | Richard              | Kelly
221 | Kevin                | Spacey
222 | Andrei               | Tarkovsky
223 | Peter                | Jackson
```
Sample table: movie_direction
``` dir_id | mov_id
--------+--------
201 |    901
202 |    902
203 |    903
204 |    904
205 |    905
206 |    906
207 |    907
208 |    908
209 |    909
210 |    910
211 |    911
212 |    912
213 |    913
214 |    914
215 |    915
216 |    916
217 |    917
218 |    918
219 |    919
220 |    920
218 |    921
215 |    922
221 |    923
```
Sample table: movie_cast
``` act_id | mov_id |              role
--------+--------+--------------------------------
101 |    901 | John Scottie Ferguson
102 |    902 | Miss Giddens
103 |    903 | T.E. Lawrence
104 |    904 | Michael
105 |    905 | Antonio Salieri
106 |    906 | Rick Deckard
107 |    907 | Alice Harford
108 |    908 | McManus
110 |    910 | Eddie Adams
111 |    911 | Alvy Singer
112 |    912 | San
113 |    913 | Andy Dufresne
114 |    914 | Lester Burnham
115 |    915 | Rose DeWitt Bukater
116 |    916 | Sean Maguire
117 |    917 | Ed
118 |    918 | Renton
120 |    920 | Elizabeth Darko
121 |    921 | Older Jamal
122 |    922 | Ripley
114 |    923 | Bobby Darin
109 |    909 | J.J. Gittes
119 |    919 | Alfred Borden
```

Sample Output:

```                     mov_title                      |      act_fname       |      act_lname       | mov_year |              role              |      gen_title       |      dir_fname       |      dir_lname       | mov_dt_rel | rev_stars
----------------------------------------------------+----------------------+----------------------+----------+--------------------------------+----------------------+----------------------+----------------------+------------+-----------
The Innocents                                      | Deborah              | Kerr                 |     1961 | Miss Giddens                   | Horror               | Jack                 | Clayton              | 1962-02-19 |      7.90
Princess Mononoke                                  | Claire               | Danes                |     1997 | San                            | Animation            | Hayao                | Miyazaki             | 2001-10-19 |      8.40
Aliens                                             | Sigourney            | Weaver               |     1986 | Ripley                         | Action               | James                | Cameron              | 1986-08-29 |      8.40
(3 rows)
```

Click me to see the solution

Keep Learning: SQL Joins, SQL Left Join, SQL Right Join, SQL Equi Join, SQL Non Equi Join, SQL Inner Join, SQL Natural Join, SQL Cross Join, SQL Outer Join, SQL Full Outer Join, SQL Self Join.

## Practice Online

More to Come !

Query visualizations are generated using Postgres Explain Visualizer (pev)

Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.

ï»¿