Movie (MovieID, Title, Year, Score, Votes) Actor(ActorID, Name) Casting (MovieID

Question

Movie (MovieID, Title, Year, Score, Votes)

Actor(ActorID, Name)

Casting (MovieID, ActorID, Ordinal)

You need to create a le called views.sql, which is a text le containing your

views for each of the questions given below. Each view should be created

using a CREATE VIEW statement.

1. (15 points) The

KevinBacon

view is a table of the name of each actor

that has been cast in a movie with

Kevin Bacon

, along with a count

of the number of times they have been cast with Kevin Bacon.

3

2. (15 points) The

Popularity

view is a table of the ID, name, and

composite score of each actor. The composite score is the sum of the

score * votes for every movie the actor has been cast

Explanation / Answer

--View Popularity
CREATE VIEW Popularity_View AS
SELECT A.ActorID,A.Name,(SUM(M.score)*SUM(M.votes)) AS CompositeScore
FROM Actor A INNER JOIN Casting C ON A.ActorID=C.ActorID
INNER JOIN Movie M ON C.MovieID=M.MovieID
GROUP BY A.ActorID,A.Name,M.Score,M.votes;

--View Kavin Bacon
CREATE VIEW Kavin_Bacon_VIEW AS
SELECT A.ActorID,A.Name,COUNT(A.ActorID) AS NumberTimesActed
FROM Actor A INNER JOIN Casting C ON A.ActorID=C.ActorID
WHERE A.Name<>'KevinBacon' AND C.MovieID IN
(SELECT MovieID FROM Casting Where ActorID in
(SELECT ActorID FROM Actor where Name='KevinBacon'))
GROUP BY A.ActorID;

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

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