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

"TITLE" VARCHAR2(100) not null "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)) ;

1. Find the movie(s) that has the maximum number of actors in this dataset. Show all the attributes of the movie(s) you find. [Hint: use a subquery in the HAVING clause; need to use 2 nested subqueries]

2. Find the top 0.2% movies in score (from high to low). Show all the attributes of these movies.

3. Find the top 5 most popular words as the first word in the title of a movie. Use the string manipulation functions to extract the first word in the movie titles. For each word calculate the total number of movies with this word as the first word in the title. Since there are a lot of movie titles starting with The or A, exclude these two words from the ranking. Show the top 10 most frequent words and their corresponding number of movies. You may ignore movie titles with only one word.

4. Count in each decade how many movies have the substring Love or Rose in the title. Your output should contain two columns, the 1st column being the decade (four-digit integer ending with 0, like 1990, 2000, 2010, etc.), and the 2nd column being the corresponding number of movies with Love or Rosein thetitle.Sorttheresultsinchronologicalorder(fromearliertolaterdecades).Labelthefirst column DECADE, and the second column NUM_OF_MOVIES. [Hint: Use the FLOOR function to generate the decades].

5. Calculate the correlation coefficient between Votes and Score using (1) all the movies directed by Jackson, Peter, and (2) all the movies in the MOVIE table, respectively. Your output should contain two columns. The 1st column (with a column heading of Dataset) hastwo entries, one is Movies_By_JP and the other is All_Movies; the 2nd column (with a column heading of Votes_vs_Score) contains the corresponding correlation coefficients. [Hint: Use the set operator UNION].

6. Write a query that categorizes all the movies into 3 score levels as follows: 1) score >= 8High; 2) 5 <= score < 8Medium; 3) score < 5Low, and then count the number of movies in each score levels. Your output should contain two columns. The 1st column (with a column heading of SCORE_LEVEL) has 3 entries: High, Medium, and Low; the 2nd column (with a column heading of NUM_OF_MOVIES) contains the number of movies in the corresponding score level. You do not have to sort the output.

Answer as many as possible please, Thank you