Using this movie database: use master; go if db_id(\'movies\') is not null begin

Question

Using this movie database: use master; go if db_id('movies') is not null begin alter database movies set single_user with rollback immediate; drop database movies; end go create database movies; go /* END SQL SERVER ONLY SECTION */ use movies; create table actor( actor_id int identity not null, first_name varchar(20), last_name varchar(20), constraint pk_actor primary key (actor_id) ); create table movie( movie_id int identity not null, title varchar(50), release_year int, constraint pk_movie primary key (movie_id) ); create table movie_actor( movie_id int not null, actor_id int not null, constraint pk_movie_actor primary key (movie_id,actor_id), constraint fk_ma_movie foreign key (movie_id) references movie(movie_id) on delete cascade on update cascade, constraint fk_ma_actor foreign key (actor_id) references actor(actor_id) on delete cascade on update cascade ); insert into actor (first_name, last_name) values ('Adam','Sandler'), ('Drew','Barrymore'), ('Matthew','McConaughey'), ('Kate','Hudson'), ('Julia','Roberts'), ('Richard','Gere'), ('Angelina','Jolie'), ('Brad','Pitt'), ('Kate','Winslet'), ('Leonardo','DiCaprio'), ('Tom','Hanks'), ('Meg','Ryan'), ('Bradley','Cooper'), ('Jennifer','Lawrence'), ('Morgan','Freeman'), ('Dwayne','Johnson'); insert into movie (title, release_year) values ('The Wedding Singer',1997), ('50 First Dates',2004), ('Fool''s Gold',2008), ('How to Lose a Guy in 10 Days',2003), ('Pretty Woman',1990), ('Runaway Bride',1999), ('Mr. and Mrs. Smith',2005), ('By the Sea',2015), ('Titanic',1997), ('Revolutionary Road',2008), ('Joe Versus the Volcano',1990), ('Sleepless in Seattle',1993), ('You''ve Got Mail',1998), ('Silver Linings Playbook',2012), ('American Hustle',2013), ('Zootopia',2016), ('Kubo and the Two Strings',2016); insert into movie_actor(movie_id, actor_id) values (1,1), (1,2), (2,1), (2,2), (3,3), (3,4), (4,3), (4,4), (5,5), (5,6), (6,5), (6,6), (7,7), (7,8), (8,7), (8,8), (9,9), (9,10), (10,9), (10,10), (11,11), (11,12), (12,11), (12,12), (13,11), (13,12), (14,13), (14,14), (15,13), (15,14); How would you answer this question. Create SQL SELECT statements to produce the following results: 2.A relation that includes the first name, last name, movie title, and year of all actors listed in the actor table, regardless of whether they have a movie in the movie table.

Explanation / Answer

Answer : -

There are three tables in database:-

1) Actor with three columns

2) Movie with three columns

3) Movie_actor with two columns

Note :- In Movie_actor table :-

a) Movie_id and Actor_id both are primary key .

b) Movie_id of this table is the foreign key to Movie_id of Movie table.

c) Actor_id is the foreign key to Actor_id of Actor table.

Now the problem is to create the select query that includes the first name, last name, movie title, and year of all actors listed in the actor table, regardless of whether they have a movie in the movie table.

Select query :-

SELECT A.FIRST_NAME, A.LAST_NAME, M.TITLE, M.RELEASE_YEAR FROM ACTOR A, MOVIE M, MOVIE_ACTOR MA WHERE MA.ACTOR_ID IN (SELECT A1.ACTOR_ID FROM ACTOR A1) AND M.MOVIE_ID = MA.MOVIE_ID ;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.