create database MovieQuote
GO
CREATE table actor(
actorId int Primary Key Identity(1,1),
actorName varchar(50)
)
CREATE table quote(
quoteId int Primary Key Identity(1,1),
quoteName varchar(50)
)
CREATE table movie(
movieId int Primary Key Identity(1,1),
movieName varchar(50),
xrefActorxQuote int
)
CREATE table xrefActorxQuote(
xrefActorxQuoteId int Primary Key Identity(1,1),
actorId int,
quoteId int
)
Go
INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (1, 1, 1)
INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (2, 7, 1)
INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (3, 8, 8)
INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (4, 9, 9)
INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (5, 10, 1)
INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (6, 41, 1)
INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (7, 42, 10)
INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (8, 43, 11)
INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (9, 44, 12)
INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (10, 45, 12)
INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (11, 46, 17)
INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (12, 47, 13)
INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (13, 48, 14)
INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (14, 49, 14)
INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (15, 50, 15)
INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (1, 14, 2)
INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (2, 15, 2)
INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (3, 12, 4)
INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (4, 16, 4)
INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (5, 17, 5)
INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (6, 10, 1)
INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (7, 1, 3)
INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (8, 8, 3)
INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (9, 9, 3)
INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (10, 11, 4)
INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (11, 13, 5)
INSERT quote (quoteId, quote) VALUES (1, 'Here''s looking at you, kid.')
INSERT quote (quoteId, quote) VALUES (7, 'endship.')
INSERT quote (quoteId, quote) VALUES (8, 'Play it, Sam. Play ''As Time Goes By.')
INSERT quote (quoteId, quote) VALUES (9, 'Round up the usual suspects.')
INSERT quote (quoteId, quote) VALUES (10, 'We''ll always have Paris.')
INSERT quote (quoteId, quote) VALUES (41, 'Of all the gin joints in all the towns in all the world, she walks into mine.')
INSERT quote (quoteId, quote) VALUES (42, 'I''m going to make him an offer he can''t refuse.')
INSERT quote (quoteId, quote) VALUES (43, 'Frankly, my dear, I don''t give a damn.')
INSERT quote (quoteId, quote) VALUES (44, 'After all, tomorrow is another day!')
INSERT quote (quoteId, quote) VALUES (45, 'As God is my witness, I''ll never be hungry again.')
INSERT quote (quoteId, quote) VALUES (46, 'Plastics.')
INSERT quote (quoteId, quote) VALUES (47, 'Mrs. Robinson, you''re trying to seduce me... Aren''t you?')
INSERT quote (quoteId, quote) VALUES (48, 'Toto, I''ve got a feeling we''re not in Kansas anymore')
INSERT quote (quoteId, quote) VALUES (49, 'There''s no place like home.')
INSERT quote (quoteId, quote) VALUES (50, 'I''ll get you, my pretty, and your little dog too!')
INSERT movie (movieId, movieName) VALUES (1, 'The Godfather')
INSERT movie (movieId, movieName) VALUES (2, 'The Wizard of Oz')
INSERT movie (movieId, movieName) VALUES (3, 'Casablanca')
INSERT movie (movieId, movieName) VALUES (4, 'Gone with the Wind')
INSERT movie (movieId, movieName) VALUES (5, 'The Graduate')
INSERT actor (actorId, actorName) VALUES (1, 'Humphrey Bogart')
INSERT actor (actorId, actorName) VALUES (8, 'Ingrid Bergman')
INSERT actor (actorId, actorName) VALUES (9, 'Claude Rains')
INSERT actor (actorId, actorName) VALUES (10, 'Marlon Brando')
INSERT actor (actorId, actorName) VALUES (11, 'Clark Gable')
INSERT actor (actorId, actorName) VALUES (12, 'Vivien Leigh')
INSERT actor (actorId, actorName) VALUES (13, 'Dustin Hoffman')
INSERT actor (actorId, actorName) VALUES (14, 'Judy Garland')
INSERT actor (actorId, actorName) VALUES (15, 'Margaret Hamilton')
INSERT actor (actorId, actorName) VALUES (16, 'Rhett Butler')
INSERT actor (actorId, actorName) VALUES (17, 'Mr. Maguire')
That thing people do...
select movieName, actorName, quote
From movie m
inner join xrefActorxMovie xam
on m.movieId = xam.movieId
inner join actor a
on xam.actorId = a.actorId
inner join xrefQuotexActor xqa
on a.actorId = xqa.actorId
inner join quote q
on xqa.quoteId = q.quoteId
Where actorName = 'Humphrey Bogart'
Another thing people do...
Select movieName, actorName, quote
From dbo.movie m
inner join dbo.xrefActorxMovie xAM
on m.movieId = xAM.movieId
inner join dbo.actor a
on xAM.actorId = a.actorId
inner join dbo.xrefQuotexActor xQA
on a.actorId = xQA.actorId
inner join dbo.quote q
on xQA.quoteId = q.quoteId
where movieName = 'Casablanca'
The Last thing people do...
Select actorName, quote, movieName
From actor a
inner join xrefQuotexActor xqa
on a.actorId = xqa.actorId
inner join quote q
on xqa.quoteId = q.quoteId
inner join xrefActorxMovie xam
on a.actorId = xam.actorId
inner join movie m
on xam.movieId = m.movieId
Where quote = 'Plastics.'
More to come, CA. Sometime.
No comments:
Post a Comment