Given a database for a movie production company. The database includes information about actors, directors, movies, movie cast, and ratings. Write a query to retrieve a list of actors who have participated in movies either before the year 2005 or after the year 2015. The data is stored in a prepopulated database with tables for actors, directors, movies, movie casts, and ratings
Question
Given a database for a movie production company. The database includes information about actors, directors, movies, movie cast, and ratings. Write a query to retrieve a list of actors who have participated in movies either before the year 2005 or after the year 2015. The data is stored in a prepopulated database with tables for actors, directors, movies, movie casts, and ratings
Solution
Sure, here is a SQL query that would retrieve the list of actors who have participated in movies either before the year 2005 or after the year 2015.
SELECT DISTINCT a.actor_name
FROM actors a
JOIN movie_cast mc ON a.actor_id = mc.actor_id
JOIN movies m ON mc.movie_id = m.movie_id
WHERE m.year < 2005 OR m.year > 2015;
Here is a step by step explanation of the query:
-
SELECT DISTINCT a.actor_name: This line is selecting the unique names of the actors from the actors table. -
FROM actors a: This line is specifying the actors table as the source of the data. -
`JOIN movie_cast mc
Similar Questions
Given a database for a movie production company. The database includes information about actors, directors, movies, movie cast, and ratings. Write a query to retrieve the titles of all movies directed by 'Martin Scorsese' from the Movie Database.Your task is to identify and display the movies associated with the director with the name 'Martin Scorsese.'The following table is already created, and the records are inserted into the table.
You are managing a database for a movie production company. The database includes information about actors, directors, movies, movie cast, and ratings.Write a query for updating the ratings of all movies directed by 'Lankesh' to a new rating of 3 in a database that contains information about actors, directors, movies, movie casts, and ratings.The following table is already created, and the records are inserted into the table.
Write SQL query considering below schema of databaseFilm(film_id, title, legth, rental_rate), Actor(film_id, actor_id, first_name, last_name)Category(film_id, rating, language, release_year)Create tables with Primary Key, foreign key constraints in given schemas.i.Give the name of actors whose actor id is 23.ii.Give the title of films whose id is between 25 and 40.iii.Give the name of actor whose last name contains Kapoor.iv.Give the title of film which was released in 2022.v.Give the name of actors played who have role in film title ‘Chhello divas’
AssignmentComplete & ContinueEnable fullscreen1.Write a SQL query to find the actors who were cast in the movie 'Annie Hall'. Return actor first name, last name and role.2.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 and movie title.3.Write a SQL query to find who directed a movie that casted a role as ‘Sean Maguire’. Return director first name, last name and movie title.4.Write a SQL query to find the actors who have not acted in any movie between 1990 and 2000 (Begin and end values are included.). Return actor first name, last name, movie title and release year.
(d) A database, FILMS, stores information about films and actors. Part of the databaseis shown:ACTOR (ActorID, FirstName, LastName, DateOfBirth)FILM_FACT (FilmID, FilmTitle, ReleaseDate, Category)FILM_ACTOR (ActorID, FilmID)(i) A composite primary key consists of two or more attributes that together form theprimary key. Explain why the table FILM_ACTOR has a composite primary key.
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.