CREATE TABLE actor( id INTEGER NOT NULL, name VARCHAR2(100), CONSTRAINT actor_PK PRIMARY KEY (id)); CREATE TABLE movie( id INTEGER NOT NULL, title VARCHAR2(100), yr NUMBER(4,0), score NUMBER, votes INTEGER, director VARCHAR2(100), CONSTRAINT movie_PK PRIMARY KEY (id)); CREATE TABLE casting( movie_id INTEGER NOT NULL, actor_id INTEGER NOT NULL, ord INTEGER, 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));

Q1. (10 pts) Harry Potter is a popular movie series. Find all the movies with title beginning with Harry Potter and list the year, lead actors name, and score of every movie in this series. Sort the results based on score in descending order.

Q2. (10 pts) Which movie receives the most votes in 2013? Show the title, lead actors name, and director of this movie. Do not use ORDER BY clause in your query. If multiple movies share the same highest number of votes in 2013, then your query must be able to find all of them and display the three columns listed above for each of these movies.

Q3. (10 pts) Find actors who played in at least 4 movies that have only a single word in the title, but never played in any movie with two or more words in the title. List the names of these actors.

Q4. (10 pts) Show the year in which Watson, Emma and Radcliffe, Daniel played in the same movie for the first time. Your output should contain only one row and one column called earliest_year.

Q5. (10 pts) The Oscar Awards nomination committee would like to ask you to nominate the best actors in the first 10 years of the 21st Century (2000-2009). You want to calculate an overall score for each actor and list 10 actors with the top 10 highest scores. The score is calculated as follows:

(1) An actor receives playing points for each movie he/she played. If playing as the lead actor (ord = 1) then 3 points. If ranked 2nd in the cast list (ord = 2) then 2 points, if ranked 3 rd in the cast list (ord = 3) then 1 point. Otherwise 0 points. 3

(2) For each movie played by an actor, the playing points is multiplied by the actual score of that movie to get a per movie score. For example, actor A played in movie M as the 2 nd actor (ord = 2). Movie M received a score of 8.0. Then the per movie score A receives by playing M is 2 x 8.0 = 16.0

(3) The overall score of an actor is calculated as the sum of the per movie scores he/she received from all the movie he/she played between 2000 and 2009. Exclude movies before 2000 or after 2009.

(4) Actors who played in no more than 5 movies between 2000 and 2009 are not eligible to be nominated and should be excluded from your calculation.

Show the names and overall scores of the actors with the top-10 highest overall scores. Your output should be in decreasing order of the overall score. Be aware that actor names are not unique. There might be different actors with the same name and your query must not confuse them.