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.]
Sample Database:
1. From the following tables, write a SQL query to find the name of all reviewers who have rated their ratings with a NULL value. Return reviewer name. Go to the editor
Sample table: reviewer
Sample table: rating
Sample Output:
rev_name -------------------------------- Neal Wruck Scott LeBrun (2 rows)
2. From the following tables, write a SQL query to find the actors who were cast in the movie 'Annie Hall'. Return actor first name, last name and role. Go to the editor
Sample table: actor
Sample table: movie_cast
Sample table : movie
Sample Output:
act_fname | act_lname | role ----------------------+----------------------+-------------------------------- Woody | Allen | Alvy Singer (1 row)
3. From the following tables, write a SQL query to find the director who directed a movie that casted a role for 'Eyes Wide Shut'. Return director first name, last name and movie title. Go to the editor
Sample table: director
Sample table: movie_direction
Sample table: movie_cast
Sample table: movie
Sample Output:
dir_fname | dir_lname | mov_title ----------------------+----------------------+---------------------------------- Stanley | Kubrick | Eyes Wide Shut (1 row)
4. From the following tables, write a SQL query to find who directed a movie that casted a role as ‘Sean Maguire’. Return director first name, last name and movie title. Go to the editor
Sample table: director
Sample table: movie_direction
Sample table: movie_cast
Sample table: movie
Sample Output:
dir_fname | dir_lname | mov_title ----------------------+----------------------+---------------------------------------------------- Gus | Van Sant | Good Will Hunting (1 row)
5. From the following tables, write a SQL query to find the actors who have not acted in any movie between1990 and 2000 (Begin and end values are included.). Return actor first name, last name, movie title and release year. Go to the editor
Sample table: actor
Sample table: movie_cast
Sample table: movie
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 F. Murray Abraham Amadeus 1984 .....
6. From the following tables, write a SQL query to find the directors with number of genres movies. 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. Go to the editor
Sample table: director
Sample table: movie_direction
Sample table: genres
Sample table: movie_genres
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 .....
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. Go to the editor
Sample table: movie
Sample table: genres
Sample table: movie_genres
Sample Output:
mov_title | mov_year | gen_title ----------------------------------------------------+----------+---------------------- Aliens | 1986 | Action Deliverance | 1972 | Adventure Lawrence of Arabia | 1962 | Adventure Princess Mononoke | 1997 | Animation ....
8. From the following tables, write a SQL query to find all the movies with year, genres, and name of the director. Go to the editor
Sample table: movie
Sample table: genres
Sample table: movie_genres
Sample table: director
Sample table: movie_direction
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 --
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. Go to the editor
Sample table: movie
Sample table: director
Sample table: movie_direction
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 .....
10. From the following tables, write a SQL query to compute the average time and count number of movies for each genre. Return genre title, average time and number of movies for each genre. Go to the editor
Sample table: movie
Sample table: genres
Sample table: movie_genres
Sample Output:
gen_title | avg | count ----------------------+----------------------+------- Adventure | 162.5000000000000000 | 2 Comedy | 93.0000000000000000 | 1 Drama | 134.2500000000000000 | 4 Horror | 100.0000000000000000 | 1 .....
11. From the following tables, write a SQL query to find movies with the lowest duration. Return movie title, movie year, director first name, last name, actor first name, last name and role. Go to the editor
Sample table: movie
Sample table: actor
Sample table: director
Sample table: movie_direction
Sample table : movie_cast
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)
12. From the following tables, write a SQL query to find those years when a movie received a rating of 3 or 4. Sort the result in increasing order on movie year. Return move year. Go to the editor
Sample table: movie
Sample table: rating
Sample Output:
mov_year
----------
1997
(1 row)
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. Go to the editor
Sample table : movie
Sample table: rating
Sample table: reviewer
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 .....
14. From the following tables, write a SQL query to find those movies that have at least one rating and received highest number of stars. Sort the result-set on movie title. Return movie title and maximum review stars. Go to the editor
Sample table: movie
Sample table: rating
Sample Output:
mov_title | max ----------------------------------------------------+------ Aliens | 8.40 American Beauty | 7.00 Annie Hall | 8.10 Avatar | 7.30 .....
15. From the following tables, write a SQL query to find those movies, which have received ratings. Return movie title, director first name, director last name and review stars. Go to the editor
Sample table: movie
Sample table: rating
Sample table: movie_direction
Sample table: director
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 .....
16. Write a query in SQL to find the movie title, actor first and last name, and the role for those movies where one or more actors acted in two or more movies. Go to the editor
Sample table: movie
Sample table: movie_cast
Sample table: actor
Sample Output:
mov_title | act_fname | act_lname | role ----------------------------------------------------+----------------------+----------------------+-------------------------------- American Beauty | Kevin | Spacey | Lester Burnham Beyond the Sea | Kevin | Spacey | Bobby Darin (2 rows)
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. Go to the editor
Sample table: movie
Sample table: movie_cast
Sample table: actor
Sample table: director
Sample table: movie_direction
Sample Output:
dir_fname | dir_lname | mov_title | act_fname | act_lname | role ----------------------+----------------------+----------------------------------------------------+----------------------+----------------------+-------------------------------- Hayao | Miyazaki | Princess Mononoke | Claire | Danes | San (1 row)
18. From the following tables, write a SQL query to find those actors who have directed their movies. Return actor first name, last name, movie title and role. Go to the editor
Sample table: movie
Sample table: movie_cast
Sample table: actor
Sample table: director
Sample table: movie_direction
Sample Output:
act_fname | act_lname | mov_title | role ----------------------+----------------------+----------------------------------------------------+-------------------------------- Woody | Allen | Annie Hall | Alvy Singer Kevin | Spacey | Beyond the Sea | Bobby Darin (2 rows)
19. From the following tables, write a SQL query to find the cast list of the movie ‘Chinatown’. Return first name, last name. Go to the editor
Sample table: movie
Sample table: movie_cast
Sample table: actor
Sample Output:
act_fname | act_lname ----------------------+---------------------- Jack | Nicholson (1 row)
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. Go to the editor
Sample table: movie
Sample table: movie_cast
Sample table: actor
Sample Output:
mov_title ---------------------------------------------------- Blade Runner (1 row)
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. Go to the editor
Sample table : movie
Sample table : rating
Sample Output:
mov_title | mov_year | rev_stars | mov_rel_country ----------------------------------------------------+----------+-----------+----------------- The Usual Suspects | 1995 | 8.60 | UK (1 row)
22. From the following tables, write a SQL query to find the highest-rated ‘Mystery Movies’. Return the title, year, and rating. Go to the editor
Sample table: movie
Sample table: genres
Sample table: movie_genres
Sample table: rating
Sample Output:
mov_title | mov_year | rev_stars ----------------------------------------------------+----------+----------- Vertigo | 1958 | 8.40 (1 row)
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. Go to the editor
Sample table: movie
Sample table: genres
Sample table: movie_genres
Sample table: rating
Sample Output:
mov_year | gen_title | count | avg
----------+----------------------+-------+--------------------
1958 | Mystery | 1 | 8.4000000000000000
(1 row)
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. Go to the editor
Sample table: movie
Sample table: genres
Sample table: movie_genres
Sample table: rating
Sample table: actor
Sample table: director
Sample table: movie_direction
Sample table: movie_cast
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)
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.
SQL: Tips of the Day
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework