Using this movies database: use master; go if db_id(\'movies\') is not null begi
ID: 3573612 • Letter: U
Question
Using this movies 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:
1. A relation that includes the last name and movie title of all actors who have movies listed in the movie table
Explanation / Answer
So basically, we need a table where we have two columns - last name and movie title.
This table should have only those last names and movie titles which are mentioned in the movie_actor table.
This movie_actor table tells us which actors have acted in which movies from the movie table.
Therefore, if we map the movie_id and actor_id from the movie_actor table to the movie and actor table respectively, we can get the attributes 'last_name' and 'title' for those actor and movie ids : :
SELECT actor.last_name, movie.title
FROM actor, movie
WHERE actor.actor_id = movie_actor.actor_id
AND movie.movie_id = movie_actor.movie_id;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.