Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
w3resource

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

Sample Database:

Movie database model

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)

Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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)

Click me to see the solution

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

Click me to see the solution

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)

Click me to see the solution

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

Click me to see the solution

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)

Click me to see the solution

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

Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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


Click me to see the solution

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

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