USING MYSQL

CREATE TABLE "ACTOR" ( "ID" NUMBER(9,0) NOT NULL, "NAME" VARCHAR2(100) NOT NULL, CONSTRAINT ACTOR_PK PRIMARY KEY (ID));

CREATE TABLE "MOVIE" ( "ID" NUMBER(9,0)

NOT NULL, NOT NULL,

"TITLE" VARCHAR2(100) "YR" NUMBER(4,0), "SCORE" NUMBER(3,1), "VOTES" NUMBER(9,0), "DIRECTOR" VARCHAR2(100), CONSTRAINT MOVIE_PK PRIMARY KEY (ID));

CREATE TABLE "CASTING" (

"MOVIE_ID" "ACTOR_ID" "ORD" CONSTRAINT CASTING_PK Primary Key (Movie_ID, ACTOR_ID), CONSTRAINT CASTING_FK1 FOREIGN KEY (MOVIE_ID) REFERENCES MOVIE(ID), CONSTRAINT CASTING_FK2 FOREIGN KEY (ACTOR_ID) REFERENCES ACTOR(ID)) ;

Some Notes:

1. 1) In this dataset, actors include both male and female personnel. In the following questions, we

   simply use actors to refer to all the actors and actresses in the dataset;

2. 2) In the MOVIE table, YR represents the year in which a movie is released (4 digits); Director

   contains the name of the director of the corresponding movie;

3. 3) ORD in the CASTING table represents the order of an actor in a movies cast. ORD = 1 means

   this actor is the lead actor;

4. 4) All the names for both actors and directors are in the following format: Last, First. There is a

   whitespace before the first name.

NUMBER(9,0) NOT NULL, NUMBER(9,0) NOT NULL, NUMBER(3,0),

Write SQL queries to answer/perform the following questions/tasks. For EACH question/task, you need to use ONLY the information given in the question/task and write a SINGLE SQL command. You can assume there is no NULL value in these tables. Show your SQL query and your query result in terms of a screenshot or an Excel file. Put everything in one single file and submit it through Canvas.(30 points).

1. (1) (4points)ListthemoviesthatwereplayedbyactorSchwarzenegger,ArnoldanddirectedbyCameron, James?Show the id, title and year of these movies.

2. (2) (4 points) Show the title and the lead actors name of the movie with ID = 2579806. Label the tile of the movie Movie_Title, and the lead actors name Lead_Actor.

3. (3) (4 points) Calculate the average score of all the movies starring actor DiCaprio, Leonardo. Round the average score to two decimal places.

4. (4) (4points)Listtheactorswhoplayedinatleast30movies.ShowtheID,nameandthenumberof movies in which he/she played (Label this number Num_of_Movies). Sort the results in descending order of the Num_of_Movies.

5. (5) (4points)ShowallthemoviesthatweredirectedbythedirectorofthemovieAvatar.

6. (6) (5points)Whichmoviehasthehighestscore?Showthetitleaswellasthescoreofthis movie.Do NOTusetheOrderByclause.Ifmultiplemoviessharethesamehighestscore,showallof these movies.

7. (7) (5points)FindthemoviesthatplayedbybothChan,JackieandLi,Jet.Showalltheattributesof these movies. You can use either subquery or a set operator.