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;

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

Below is a segment of my SQL code (from prog3_sql.txt) that is currently a work in progress and needs to be modified. I have it to where it is working, but I am struggling on the part to where I have to find the average movie score. If you can help me find some way to solve this problem, then it would be very appreciative.

prog3_sql.txt

#SELECT * FROM good_collaboration

#ORDER BY tot_score;

drop view good_score;

create view good_score AS

select movie_id, name, score

from movies

where score >=75;

#select movie_id,cast_id, cast_name, from cast

#where cast.movie_id in (select good_score.movie_id from good_score);

select cast.cast_id, matched_cast.cast_id, count(*) as matched_count

from cast

left join

(select cast_id, movie_id from cast) as matched_cast

on matched_cast.movie_id=cast.movie_id

and matched_cast.cast_id <> cast.cast_id

and cast.movie_id in (select movie_id from good_score)

and matched_cast.movie_id in (select movie_id from good_score)

group by cast.cast_id,matched_cast.cast_id

having count(*)>=4 and matched_cast.cast_id is not null

order by count(*),cast.cast_id,matched_cast.cast_id;