SQL Exercises, Practice, Solution - BASIC exercises on movie Database

SQL [10 exercises with solution]

[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 name and year of the movies. Return movie title, movie release year.

Sample table: movie

Sample Output:

                     mov_title                      | mov_year
----------------------------------------------------+----------
 Vertigo                                            |     1958
 The Innocents                                      |     1961
 Lawrence of Arabia                                 |     1962
 The Deer Hunter                                    |     1978
 .....
 

Click me to see the solution

2. From the following table, write a SQL query to find when the movie 'American Beauty' released. Return movie release year.

Sample table: movie

Sample Output:

 mov_year
----------
     1999
(1 row)

Click me to see the solution

3. From the following table, write a SQL query to find the movie that was released in 1999. Return movie title.

Sample table: movie

Sample Output:

                     mov_title
----------------------------------------------------
 Eyes Wide Shut
 American Beauty
(2 rows)

Click me to see the solution

4. From the following table, write a SQL query to find those movies, which were released before 1998. Return movie title.

Sample table: movie

Sample Output:

                     mov_title
----------------------------------------------------
 Vertigo
 The Innocents
 Lawrence of Arabia
 The Deer Hunter
 Amadeus
 .....
 

Click me to see the solution

5. From the following tables, write a SQL query to find the name of all reviewers and movies together in a single list.

Sample table: movie

Sample table: reviewer

Sample Output:

                      rev_name
----------------------------------------------------

 Hannah Steele
 Annie Hall
 The Usual Suspects
 Aliens
 Wesley S. Walker
 .....
 

Click me to see the solution

6. From the following table, write a SQL query to find all reviewers who have rated seven or more stars to their rating. Return reviewer name.

Sample table: reviewer

Sample table: rating

Sample Output:

            rev_name
--------------------------------
Righty Sock
Jack Malvern
Flagrant Baronessa
Victor Woeltjen
Simon Wright
....

Click me to see the solution

7. From the following tables, write a SQL query to find the movies without any rating. Return movie title.

Sample table: movie

Sample table: rating

Sample Output:

                     mov_title
----------------------------------------------------
 The Deer Hunter
 Amadeus
 Eyes Wide Shut
 The Shawshank Redemption
 .....
 

Click me to see the solution

8. From the following table, write a SQL query to find the movies with ID 905 or 907 or 917. Return movie title.

Sample table: movie

Sample Output:

                     mov_title
----------------------------------------------------
 Amadeus
 Eyes Wide Shut
 Deliverance
(3 rows)

Click me to see the solution

9. From the following table, write a SQL query to find the movie titles that contain the word 'Boogie Nights'. Sort the result-set in ascending order by movie year. Return movie ID, movie title and movie release year.

Sample table: movie

Sample Output:

 mov_id |                     mov_title                      | mov_year
--------+----------------------------------------------------+----------
    910 | Boogie Nights                                      |     1997
(1 row)

Click me to see the solution

10. From the following table, write a SQL query to find those actors with the first name 'Woody' and the last name 'Allen'. Return actor ID.

Sample table: actor

Sample Output:

 act_id
--------
    111
(1 row)

Click me to see the solution

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.