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

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.

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.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Difference between natural join and inner join

One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned-

Consider:

TableA                           TableB
+------------+----------+        +--------------------+    
|Column1     | Column2  |        |Column1  |  Column3 |
+-----------------------+        +--------------------+
| 1          |  2       |        | 1       |   3      |
+------------+----------+        +---------+----------+

The INNER JOIN of TableA and TableB on Column1 will return

SELECT * FROM TableA AS a INNER JOIN TableB AS b USING (Column1);
SELECT * FROM TableA AS a INNER JOIN TableB AS b ON a.Column1 = b.Column1;
+------------+-----------+---------------------+    
| a.Column1  | a.Column2 | b.Column1| b.Column3|
+------------------------+---------------------+
| 1          |  2        | 1        |   3      |
+------------+-----------+----------+----------+

The NATURAL JOIN of TableA and TableB on Column1 will return:

SELECT * FROM TableA NATURAL JOIN TableB
+------------+----------+----------+    
|Column1     | Column2  | Column3  |
+-----------------------+----------+
| 1          |  2       |   3      |
+------------+----------+----------+

Ref: https://bit.ly/3AG5CId

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook