SQL Exercises, Practice, Solution - SUBQUERIES exercises on movie Database
SQL [16 exercises with solution]
[An editor is available at the bottom of the page to write and execute the scripts.]
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. Go to the editor
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 who directed a movie that casted a role for 'Eyes Wide Shut'. Return director first name, last name. Go to the editor
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, which released in the country besides UK. Return movie title, movie year, movie time, date of release, releasing country. Go to the editor
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 those movies where reviewer is unknown. Return movie title, year, release date, director first name, last name, actor first name, last name. Go to the editor
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. Go to the editor
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 find those years, which produced at least one movie and that, received a rating of more than three stars. Sort the result-set in ascending order by movie year. Return movie year. Go to the editor
Sample table: movie
Sample table: rating
Sample Output:
mov_year
----------
1958
1961
1962
1977
.....
7. From the following table, write a SQL query to find those movies, which have no ratings. Return movie title. Go to the editor
Sample table: movie
Sample table: rating
Sample Output:
mov_title ---------------------------------------------------- Deliverance Amadeus Spirited Away The Prestige .....
8. From the following tables, write a SQL query to find those reviewers who have rated nothing for some movies. Return reviewer name. Go to the editor
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 those movies, which reviewed by a reviewer and got a rating. Sort the result-set in ascending order by reviewer name, movie title, review Stars. Return reviewer name, movie title, review Stars. Go to the editor
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 tables, write a SQL query to find those reviewers who rated more than one movie. Group the result set on reviewer’s name, movie title. Return reviewer’s name, movie title. Go to the editor
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. Go to the editor
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. Go to the editor
Sample table: reviewer
Sample table: rating
Sample table: movie
Sample Output:
rev_name -------------------------------- Sasha Goldshtein (1 row)
13. From the following tables, write a SQL query to find the movies, which have reviewed by any reviewer body except by 'Paul Monks'. Return movie title. Go to the editor
Sample table: reviewer
Sample table: rating
Sample table: movie
Sample Output:
mov_title ---------------------------------------------------- Avatar Lawrence of Arabia Donnie Darko Aliens .....
14. From the following tables, write a SQL query to find the lowest rated movies. Return reviewer name, movie title, and number of stars for those movies. Go to the editor
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. Go to the editor
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 name of 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
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.
- New Content published on w3resource:
- 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
- React - JavaScript Library
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework