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
.....
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)
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)
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
.....
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
.....
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
....
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
.....
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)
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)
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)
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
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
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
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