CREATE TABLE \" ACTOR \" ( \"ID\" NUMBER(9,0) NOT NULL, \"NAME\" VARCHAR2(100) N
ID: 3703038 • Letter: C
Question
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" NUMBER(9,0) NOT NULL
"ACTOR_ID" NUMBER(9,0) NOT NULL
"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)) ;
Using Pl/Sql
Use two environment variables to prompt the user to input the first name and the last name of a director, respectively. Then show on the screen the number of movies (<N>) directed by that director, and the average score (<S>) of those movies that he/she directed. Your output should contain one line “<director> had directed <N> movies with an average score of <S>”, where <director> is the full name of the director in the form of “FirstName LastName”. For example, if the input director is “Cameron, James”, you should display on the screen that --
James Cameron had directed 4 movies with an average score of 7.85
In case the name of the director (given by the user) does not exist in the movie table, you should catch the runtime error (NO_DATA_FOUND) in your exception-handling section. For example, if the input director is “Wang, Wenjun”, you should display on the screen that --
Director Wenjun Wang does not exist in the movie table!”.
Test your code with at least two directors (one exists in the movie table and the other does not).
Explanation / Answer
Answer:
Here wrotye the select * from movie where id in(select movie_id from(select movie_id,count(actor_id) as cnt, rank() over (order by count(actor_id) desc) as rnk from casting group by movie_id) where rnk=1)
Selects the actors who starred together with count more than 4 using self join . Here in having clause actor1 < actor2 is used to remove reverse duplicates like a,b is considered but not b,a pair of actors.
SELECT A.actor_id AS actor1, B.actor_id AS actor2, COUNT(*) as num_of_movies
FROM casting A, casting B
WHERE A.movie_id = B.movie_id
GROUP BY actor1, actor2
HAVING actor1 <> actor2 and num_of_movies > 4 and actor1 < actor2
ORDER BY num_of_movies DESC
--tq--