w3resource

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:

Movie database model

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