w3resource

SQL exercises on movie Database: Find the actors with all information who played a role in the movie 'Annie Hall'

SQL movie Database: Subquery Exercise-1 with Solution

1. From the following tables, write a SQL query to find the actors who played a role in the movie 'Annie Hall'. Return all the fields of actor table.

Sample table: actor


Sample table: movie_cast


Sample table: movie


Sample Solution:

SELECT * 
FROM actor 
WHERE act_id IN(
SELECT act_id 
FROM movie_cast 
WHERE mov_id IN (
SELECT mov_id 
FROM movie 
WHERE mov_title='Annie Hall'
));

Sample Output:

 act_id |      act_fname       |      act_lname       | act_gender
--------+----------------------+----------------------+------------
    111 | Woody                | Allen                | M
(1 row)

Code Explanation:

The query in SQL that retrieves all information on actors who appeared in the movie 'Annie Hall'. The query uses a subquery to find the mov_id of the movie 'Annie Hall', and then searches the movie_cast table to find all actors who appeared in that movie by matching the mov_id to the inner subquery.
The outer query returns all information on the actors found in the previous step, by matching their act_id values to those in the actor table.

Relational Algebra Expression:

Relational Algebra Expression: Find the actors with all information who played a role in the movie 'Annie Hall'.

Relational Algebra Tree:

Relational Algebra Tree: Find the actors with all information who played a role in the movie 'Annie Hall'.

Practice Online


Movie database model

Query Visualization:

Duration:

Query visualization of Find the actors with all information who played a role in the movie 'Annie Hall' - Duration

Rows:

Query visualization of Find the actors with all information who played a role in the movie 'Annie Hall' - Rows

Cost:

Query visualization of Find the actors with all information who played a role in the movie 'Annie Hall' - Cost

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: SQL SUBQUERIES Exercises on movie Database
Next: From the following tables, write a SQL query to find the director who directed a movie that casted a role for 'Eyes Wide Shut'. Return director first name, last name.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

What is the best way to paginate results in SQL Server?

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

Database: SQL Server

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

 





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