CREATE TABLE actor( id INTEGER NOT NULL, name VARCHAR2(100), CONSTRAINT actor_PK
ID: 3703127 • Letter: C
Question
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));
In the “casting” table, “ord” refers to the order of an actor in a movie’s cast list (ord = 1 refers to the leading actor). In this database, actors include both male and female. In this homework, we simply use ‘Actors’ to represent all the actors and actresses in the data. In the “movie” table, “yr” is the year in which the movie is released. “score” is the average rating of a movie between 0 and 10 (inclusive). “votes” is the total number of people who rated the movie. 2 Run the given scripts in the following order: actor.sql, movie.sql, casting1.sql, casting2.sql. If you don’t follow this order, you may encounter errors. For each question below, write only ONE SQL query to generate the correct results. You cannot use any information not given in the question.
Q2. (10 pts) Find directors who directed at least 15 movies. Sort the results on the total number of movies directed in descending order. Your output should have two columns: director, total_movies.
Q3. (10 pts). Show the top 10 movies with the highest votes. List the title, yr, and votes of these movies and sort the results by votes in descending order.
Explanation / Answer
Solution:
2)
SELECT director, SUM(movie.id) AS total_movies FROM movie WHERE total_movies >=15 GROUP BY director ORDER BY total_movies DESC
3)
SELECT votes, title, year FROM movie LIMIT 10.
I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)