Finding good collaborators: Create a view (virtual table) called good collaboration that lists pairs of stars who appeared in movies. Each row in the table describes one pair of stars who have appeared in at least 4 movies together AND each of the movie has score >= 75. The view should have the format: collaboration (cast_member_id1, cast_member_id2, num_movies, avg_movie_score). Exclude self pairs: (cast_member_id1 == cast_member_id2). Keep symmetrical or mirror pairs. For example, keep both (A, B) and (B,A).

Hint: SelfXJoins will likely be a necessary. After creating a view, list (cast_member_id1, cast_member_id2, num_movies, avg_movie_score) sorted by average movie scores from the view.

Movie-cast.txt

9,162652153,"Hayden Christensen"

9,162652152,"Ewan McGregor"

9,418638213,"Kenny Baker"

9,548155708,"Graeme Blundell"

9,358317901,"Jeremy Bulloch"

9,178810494,"Anthony Daniels"

9,770726713,"Oliver Ford Davies"

9,162652156,"Samuel L. Jackson"

9,162655731,"James Earl Jones"

9,284442167,"Claudia Karvan"

9,162652385,"Christopher Lee"

9,425838884,"Peter Mayhew"

9,162652155,"Ian McDiarmid"

9,196103011,"Temuera Morrison"

9,770711854,"Trisha Noble"

9,444129912,"Wayne Pygram"

9,162691723,"Jimmy Smits"

9,364660718,"Bruce Spence"

9,162656296,"Frank Oz"

9,162714169,"Ling Bai"

9,770961398,"Warren Owens"

.

.

.

770876554,770916051,"Alec Wilson"

770876554,770773491,"Edmund Pegge"

770876554,770925843,"Noel Travarthen"

770972512,335716545,"Noam Chomsky"

movie-name_score.txt

9,"Star Wars: Episode III - Revenge of the Sith 3D",80

24214,"The Chronicles of Narnia: The Lion, The Witch and The Wardrobe",76

1789,"War of the Worlds",74

10009,"Star Wars: Episode II - Attack of the Clones 3D",67

771238285,"Warm Bodies",-1

770785616,"World War Z",-1

771303871,"War Witch",89

771323601,"War of the Worlds the True Story",-1

771243843,"Safe Haven: The Underground Railroad During The Vietnam War",-1

770784043,"Bride Wars",11

11292,"Star Wars: Episode IV - A New Hope",94

11366,"Star Wars: Episode VI - Return of the Jedi",79

.

.

.

770894512,"Nazis, The - Nazi War Crimes",-1

770916696,"WCW Fall Brawl 1995: War Games",-1

770949969,"Colors of War - Europe",-1

770972512,"Plan Colombia: Cashing In On the Drug War Failure",-1

prog3_createTable_sql.txt

#Create Table movies

create table movies

(

movie_id integer,

name varchar(1000),

score integer

);

#Load Data

load data local infile '~/prog3/movie-name_score.txt' into table movies fields terminated by ',';

#Create Table Cast

create table cast

(

movie_id integer,

cast_id integer,

cast_name varchar(1000)

);

#Load Data

load data local infile '~/prog3/movie-cast.txt' into table cast fields terminated by ',';

select count(*) from movies;

select count(*) from cast;

prog3_sql.txt

select count(*) from movies;

select count(*) from cast;

Below is a segment of my SQL code (from prog3_sql.txt) that is currently a work in progress. Please focus on the code that is underneath the comment that says #Finding actors that have collaborated on at least 4 moives each of which scored >= 75

-----------------------------------------------

#Finding actors that have collaborated on at least 4 moives each of which scored >= 75

#Drop view Good_Collaboration;

#create view Good_Collaboration AS

#select movie_id,name,score

#from movies

#where score >=75;

#create view Cast_Collaboration AS

#select cast_id,movie_id,cast_name

#from cast

#where cast.movie_id=movies.movie_id;

#select C1.cast_name, C2.cast_name

#from cast C1

#inner join cast C2 on C1.Movie_Id=C2.Movie_ID

#where C1.cast_name <> C2.cast_name;

#select movie_id,cast_name, cast_id

#from Cast_Collaboration;

#Second attempt at finding actors who collaborated 4 times on films of score >=75

drop view good_collaboration;

create view good_collaboration as

select cast.cast_id, cast.cast_name, cast.movie_id

from cast

where cast.movie_id in (select movies.movie_id from movies where movies.score >=75)

order by cast.movie_id;

select c1.cast_name, c2.cast_name

from good_collaboration c1

inner join good_collaboration c2 on c1.movie_id=c2.movie_id

where c1.cast_name <> c2.cast_name and count(c1.cast_name)>4

#group by c1.cast_name, c2.cast_name

#order by c1.cast_name,c2.cast_name;