w3resource

SQL exercises on movie Database: Find the ID number for the actor whose first name is 'Woody' and the last name is 'Allen'

SQL movie Database: Basic Exercise-10 with 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
 act_id |      act_fname       |      act_lname       | act_gender
--------+----------------------+----------------------+------------
    101 | James                | Stewart              | M
    102 | Deborah              | Kerr                 | F
    103 | Peter                | OToole               | M
    104 | Robert               | De Niro              | M
    105 | F. Murray            | Abraham              | M
    106 | Harrison             | Ford                 | M
    107 | Nicole               | Kidman               | F
    108 | Stephen              | Baldwin              | M
    109 | Jack                 | Nicholson            | M
    110 | Mark                 | Wahlberg             | M
    111 | Woody                | Allen                | M
    112 | Claire               | Danes                | F
    113 | Tim                  | Robbins              | M
    114 | Kevin                | Spacey               | M
    115 | Kate                 | Winslet              | F
    116 | Robin                | Williams             | M
    117 | Jon                  | Voight               | M
    118 | Ewan                 | McGregor             | M
    119 | Christian            | Bale                 | M
    120 | Maggie               | Gyllenhaal           | F
    121 | Dev                  | Patel                | M
    122 | Sigourney            | Weaver               | F
    123 | David                | Aston                | M
    124 | Ali                  | Astin                | F

Sample Solution:

-- Selecting 'act_id' from the 'actor' table
-- Filtering results where 'act_fname' is 'Woody' and 'act_lname' is 'Allen'
SELECT act_id
FROM actor 
WHERE act_fname='Woody' 
AND act_lname='Allen';

Sample Output:

 act_id
--------
    111
(1 row)

Code Explanation:

The said query in SQL that select the act_id of an actor with the first name 'Woody' and the last name 'Allen' from a table named 'actor'. The query filters the results using the WHERE clause to match only the rows that meet the said criteria.

Relational Algebra Expression:

Relational Algebra Expression: Find the ID number for the actor whose first name is 'Woody' and the last name is 'Allen'.

Relational Algebra Tree:

Relational Algebra Tree: Find the ID number for the actor whose first name is 'Woody' and the last name is 'Allen'.

Practice Online


Movie database model

Query Visualization:

Duration:

Query visualization of Find the ID number for the actor whose first name is 'Woody' and the last name is 'Allen' - Duration

Rows:

Query visualization of Find the ID number for the actor whose first name is 'Woody' and the last name is 'Allen' - Rows

Cost:

Query visualization of Find the ID number for the actor whose first name is 'Woody' and the last name is 'Allen' - Cost

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

Previous: 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.
Next: SQL SUBQUERIES Exercises on movie Database

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.