51. Movie only actor

easy

Instruction
  • Write a query to return the first name and last name of actors who only appeared in movies.
  • Actor appeared in tv should not be included .
  • The order of your results doesn't matter.

Table 1: actor_movie

Actors who appeared in a movie.


  col_name  | col_type
------------+-------------------
 actor_id   | integer
 first_name | character varying
 last_name  | character varying

Table 2: actor_tv

Actors who appeared in a TV show.


  col_name  | col_type
------------+-------------------
 actor_id   | integer
 first_name | character varying
 last_name  | character varying

Sample results


 first_name  |  last_name
-------------+-------------
 ED          | CHASE
 ZERO        | CAGE
 CUBA        | OLIVIER

Solution postgres

SELECT M.first_name, M.last_name
FROM actor_movie M
LEFT JOIN actor_tv T
ON M.actor_id = T.actor_id
WHERE T.actor_id IS NULL;
    

Explanation

This PostgreSQL query selects the first and last names of actors who have only appeared in movies but not in TV shows. It does this by joining two tables, actor_movie and actor_tv, on their actor_id columns. The LEFT JOIN keyword is used to include all rows from the actor_movie table and only matching rows from the actor_tv table. The WHERE clause filters out any rows where the actor_id in the actor_tv table is null, indicating that the actor has not appeared in any TV shows.

Last Submission postgres

Expected results



More OUTER JOIN questions

ID Title Level FTPR
194 Daily active users report for new market snap easy
20%
191 Number of replies by group twitter medium
18%
172 Comments distribution facebook hard
13%
168 Average number of streams in the US tiktok medium
13%
140 Daily sales of restaurant 100011 doordash hard
8%
118 Daily bookings in the US airbnb hard
5%
100 Advertiser ROI facebook medium
8%
56 Total number of actors easy
30%
55 Busy days and slow days medium
10%
54 Customer groups by movie rental spend medium
20%
53 Movie groups by rental income hard
11%
52 Movies cast by movie only actors easy
11%
45 Movie inventory optimization hard
9%
44 In-demand vs not-in-demand movies medium
6%
43 Customers who rented vs. those who did not medium
8%
42 Films that are in stock vs not in stock medium
12%
41 Productive actors vs less-productive actors hard
7%