CREATE TABLE \" ACTOR \" ( \"ID\" NUMBER(9,0) NOT NULL, \"NAME\" VARCHAR2(100) N

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 “Cameron, James”, you should display on the screen that --

Director James Cameron 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

create or replace PROCEDURE DIRECTORDetails

(first_name VARCHAR2(50),last_name VARCHAR2(50))

AS

BEGIN

FOR aRow IN (SELECT DIRECTOR, COUNT(ID) AS CountMovies, AVG(SCORE) AS AvgScore

FROM MOVIE

WHERE DIRECTOR = (SELECT first_name || ',' || last_name from dual)

GROUP BY DIRECTOR)

LOOP

--need to add additinal colummns i=based on your customer table

DBMS_OUTPUT.PUT_LINE(first_name || ',' || last_name || 'had directed' || aRow.CountMovies || ' movies with an average score of ' || aRow.AvgScore);

END LOOP;

--if nodata found below details will display

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('Director ' first_name || ',' || last_name ||' does not exist in the movie table!');

END DIRECTORDetails;

/

--sample example

SET SERVEROUTPUT ON

accept first_name PROMPT "James "

accept last_name PROMPT "Cameron"

EXEC DIRECTORDetails (:first_name, :last_name)

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.