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

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. Go to the editor

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.  Go to the editor

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, which was made in the year 1999. Return movie title.  Go to the editor

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 was made before 1998. Return movie title.  Go to the editor

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.  Go to the editor

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 tables, write a SQL query to find all reviewers who have rated 7 or more stars to their rating. Return reviewer name.  Go to the editor

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.  Go to the editor

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.  Go to the editor

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 those movie titles, which include the words 'Boogie Nights'. Sort the result-set in ascending order by movie year. Return movie ID, movie title and movie release year.  Go to the editor

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 whose first name is 'Woody' and the last name is 'Allen'. Return actor ID  Go to the editor

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.