Problem 1: With the given information, please do the following by writing it in SQL.

a) Finding laid back actors: List cast members (alphabetically by cast_name) with exactly 4 movie appearances. Format of each output row: cast_id, cast_name.

b) Find the movies with score >80 and has no cast members with name David. Output format: movie_id, score.

c) For each cast member, find the number of movies he or she played in, and the average movie scores. Filter out movies with score < 0. List those cast members in alphabetical order. Format of each output row: cast_id, cast_name, num_movies, average_score.

d) Find the names of movies with scores higher than that of some movies containing Chronicles. (Hint: consider using set comparison in subquery)

e) 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,

Hint: Self-Joins 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.

Hint: Please use the following information that I use in text files for guidance.

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;