SQL Inner join Movie Databases

--drop table Movies
--drop table Actors
--drop table Quotes
--drop table xrefMovieActor
--drop table xrefActorQuote
--drop table xrefMovieQuotes

use DragonsQuotes
go

create table Quotes
(
      QuoteID int identity (1,1) primary key
      , Quote varchar(100)
)

Insert into Quotes (Quote) values ('Hakuna Matata')
Insert into Quotes (Quote) values ('Dat-da-dah!  We''re dead.')
Insert into Quotes (Quote) values ('Well you shouldn''t have been trying to steal our Ozone.')
Insert into Quotes (Quote) values ('I am Iron Man.  The suit and I are one.')
Insert into Quotes (Quote) values ('This is some rescue. When you came in here, didn''t you have a plan for getting out?')

select *
from Quotes

create table xrefActorQuote
(
      xrefAQID int identity (1,1) primary key
      , ActorID int
      , QuoteID int
);

insert into xrefActorQuote (ActorID, QuoteID) values (1,1)
insert into xrefActorQuote (ActorID, QuoteID) values (2,2)
insert into xrefActorQuote (ActorID, QuoteID) values (3,3)
insert into xrefActorQuote (ActorID, QuoteID) values (4,4)
insert into xrefActorQuote (ActorID, QuoteID) values (5,5)

select *
from xrefActorQuote

create table Movies
(
      MovieID int identity(1,1) not null primary Key
      ,MovieName varchar(100)
)

Insert into Movies
(
      MovieName
)

values
(
      'The Lion King'
)

Insert into Movies
(
      MovieName
)

values
(
      'How to Train Your Dragon'
)

Insert into Movies
(
      MovieName
)

values
(
      'Men in Black 2'
)

Insert into Movies
(
      MovieName
)

values
(
      'Iron Man 2'
)

Insert into Movies
(
      MovieName
)

values
(
      'Star Wars'
)

Select * 

from Movies

create table xrefMovieQuotes
(
xrefMQID int identity(1,1) not null primary Key
,MovieID int
,QuotesID int
)

insert into xrefMovieQuotes(MovieID,QuotesID) values (1,1)
insert into xrefMovieQuotes(MovieID,QuotesID) values (2,2)
insert into xrefMovieQuotes(MovieID,QuotesID) values (3,3)
insert into xrefMovieQuotes(MovieID,QuotesID) values (4,4)
insert into xrefMovieQuotes(MovieID,QuotesID) values (5,5) 

select * from xrefMovieQuotes

create table Actor
(
      ActorID int identity (1,1) PRIMARY KEY,
      ActorName varchar (40)
);

insert into Actor
(ActorName)
Values
('Rafiki')

insert into Actor
(ActorName)
Values
('Hiccup')

insert into Actor
(ActorName)
Values
('Will Smith')

insert into Actor
(ActorName)
Values
('Robert Downey Jr.')

insert into Actor
(ActorName)
Values
('Carrie Fisher')

select *
from Actor

create table xrefMovieActor
(
      xrefMAID int identity (1,1) Primary Key,
      MovieID int,
      ActorID int
);

insert into xrefMovieActor
(MovieID, ActorID)
Values
('1', '1')

insert into xrefMovieActor
(MovieID, ActorID)
Values
('2', '2')

insert into xrefMovieActor
(MovieID, ActorID)
Values
('3', '3')

insert into xrefMovieActor
(MovieID, ActorID)
Values
('4', '4')
insert into xrefMovieActor
(MovieID, ActorID)
Values
('5', '5')

select *
from xrefMovieActor


select *
from xrefMovieActor


Select A.ActorName, M.MovieName
from xrefMovieActor xMA
inner join Actor A
      on A.ActorID = xMA.ActorID
inner join xrefActorQuote xAQ
      on A.ActorID = xAQ.ActorID
inner join Movies M
      on M.MovieID = xMA.MovieID
where MovieName = 'Men in Black 2'

select *
from Movies M
inner join xrefMovieActor xrefMA
      on M.MovieID = xrefMA.MovieID
inner join xrefMovieQuotes xrefMQ
      on xrefMA.MovieID = xrefMQ.MovieID
inner join Quotes Q
      on Q.QuoteID = xrefMQ.QuotesID
inner join Actor A
      on A.ActorID = xrefMA.ActorID
where QUOTE like '%suit%'

select a.ActorName,m.MovieName
from Actor a
inner join xrefMovieActor x on x.ActorID = a.ActorID
inner join Movies m on m.MovieID = x.MovieID
where a.ActorName = 'Will Smith'