SQL Exercises, Practice, Solution - SUBQUERIES exercises on movie Database
SQL [16 exercises with solution]
You may read our SQL Subqueries 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 the actors who played a role in the movie 'Annie Hall'. Return all the fields of actor table.
Sample table: actor
Sample table: movie_cast
Sample table: movie
Sample Output:
act_id | act_fname | act_lname | act_gender
--------+----------------------+----------------------+------------
111 | Woody | Allen | M
(1 row)
2. From the following tables, write a SQL query to find the director of a film that cast a role in 'Eyes Wide Shut'. Return director first name, last name.
Sample table: director
Sample table: movie_direction
Sample table: movie_cast
Sample table: movie
Sample Output:
dir_fname | dir_lname ----------------------+---------------------- Stanley | Kubrick (1 row)
3. From the following table, write a SQL query to find those movies that have been released in countries other than the United Kingdom. Return movie title, movie year, movie time, and date of release, releasing country.
Sample table: movie
Sample Output:
mov_title | mov_year | mov_time | date_of_release | releasing_country ----------------------------------------------------+----------+----------+-----------------+------------------- The Innocents | 1961 | 100 | 1962-02-19 | SW Annie Hall | 1977 | 93 | 1977-04-20 | USA Seven Samurai | 1954 | 207 | 1954-04-26 | JP (3 rows)
4. From the following tables, write a SQL query to find for movies whose reviewer is unknown. Return movie title, year, release date, director first name, last name, actor first name, last name.
Sample table: movie
Sample table: actor
Sample table: director
Sample table: movie_direction
Sample table: movie_cast
Sample table: reviewer
Sample table: rating
Sample Output:
mov_title | mov_year | mov_dt_rel | dir_fname | dir_lname | act_fname | act_lname ----------------------------------------------------+----------+------------+----------------------+----------------------+----------------------+---------------------- Blade Runner | 1982 | 1982-09-09 | Ridley | Scott | Harrison | Ford Princess Mononoke | 1997 | 2001-10-19 | Hayao | Miyazaki | Claire | Danes (2 rows)
5. From the following tables, write a SQL query to find those movies directed by the director whose first name is Woddy and last name is Allen. Return movie title.
Sample table: movie
Sample table: director
Sample table: movie_direction
Sample Output:
mov_title ---------------------------------------------------- Annie Hall (1 row)
6. From the following tables, write a SQL query to determine those years in which there was at least one movie that received a rating of at least three stars. Sort the result-set in ascending order by movie year. Return movie year.
Sample table: movie
Sample table: rating
Sample Output:
mov_year
----------
1958
1961
1962
1977
.....
7. From the following table, write a SQL query to search for movies that do not have any ratings. Return movie title.
Sample table: movie
Sample table: rating
Sample Output:
mov_title ---------------------------------------------------- Deliverance Amadeus Spirited Away The Prestige .....
8. From the following table, write a SQL query to find those reviewers who have not given a rating to certain films. Return reviewer name.
Sample table: reviewer
Sample table: rating
Sample Output:
rev_name -------------------------------- Neal Wruck Scott LeBrun (2 rows)
9. From the following tables, write a SQL query to find movies that have been reviewed by a reviewer and received a rating. Sort the result-set in ascending order by reviewer name, movie title, review Stars. Return reviewer name, movie title, review Stars.
Sample table: reviewer
Sample table: rating
Sample table: movie
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 .....
10. From the following table, write a SQL query to find movies that have been reviewed by a reviewer and received a rating. Group the result set on reviewer’s name, movie title. Return reviewer’s name, movie title.
Sample table: reviewer
Sample table: rating
Sample table : movie
Sample Output:
rev_name | mov_title --------------------------------+---------------------------------------------------- Righty Sock | Titanic Righty Sock | Vertigo (2 rows)
11. From the following tables, write a SQL query to find those movies, which have received highest number of stars. Group the result set on movie title and sorts the result-set in ascending order by movie title. Return movie title and maximum number of review stars.
Sample table: rating
Sample table: movie
Sample Output:
mov_title | max ----------------------------------------------------+------ Aliens | 8.40 American Beauty | 7.00 Annie Hall | 8.10 Avatar | 7.30 .....
12. From the following tables, write a SQL query to find all reviewers who rated the movie 'American Beauty'. Return reviewer name.
Sample table: reviewer
Sample table: rating
Sample table: movie
Sample Output:
rev_name -------------------------------- Sasha Goldshtein (1 row)
13. From the following table, write a SQL query to find the movies that have not been reviewed by any reviewer body other than 'Paul Monks'. Return movie title.
Sample table: reviewer
Sample table: rating
Sample table: movie
Sample Output:
mov_title ---------------------------------------------------- Avatar Lawrence of Arabia Donnie Darko Aliens .....
14. From the following table, write a SQL query to find the movies with the lowest ratings. Return reviewer name, movie title, and number of stars for those movies.
Sample table: reviewer
Sample table: rating
Sample table: movie
Sample Output:
rev_name | mov_title | rev_stars --------------------------------+----------------------------------------------------+----------- Paul Monks | Boogie Nights | 3.00 (1 row)
15. From the following tables, write a SQL query to find the movies directed by 'James Cameron'. Return movie title.
Sample table: director
Sample table: movie_direction
Sample table: movie
Sample Output:
mov_title ---------------------------------------------------- Titanic Aliens (2 rows)
16. Write a query in SQL to find the movies in which one or more actors appeared in more than one film.
Sample table: movie
Sample table: movie_cast
Sample table: actor
Keep Learning: SQL Subqueries, SQL Single Row Subqueries, SQL Multiple Row and Column Subqueries, SQL Correlated Subqueries, SQL Nested subqueries.
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
What is the most efficient/elegant way to parse a flat table into a tree?
WITH RECURSIVE MyTree AS (
SELECT * FROM MyTable WHERE ParentId IS NULL
UNION ALL
SELECT m.* FROM MyTABLE AS m JOIN MyTree AS t ON m.ParentId = t.Id
)
SELECT * FROM MyTree;
Ref: https://bit.ly/3FPYFFF
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook