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 one environment variable to prompt the user to input the ID of a movie. Then show on the screen the movie’s title (<MovieTitle>) and the comment based on its score.

If its score is greater than or equal to 8.0, then display the comment as follows --

If its score is greater than or equal to 5.0 but less than 8.0, then the comment reads as follows --

If its score is less than 5.0, then the comment reads as follows --

Test your code with three movies (one in each category).

Explanation / Answer

Below is the PL/SQL code for getting the movie name from user and based on that movie title, its score is fetched. Score of the movie is then compared in if, else statement to decide weather the movie is high score, medium score or low score. Output is displayed in each if else result.

PL/SQL Code-

accept mt VARCHAR(100) prompt 'Please enter movie title: '

CREATE OR REPLACE Function Rating

DECLARE

m_title VARCHAR(100);

BEGIN

m_title := &mt;

SELECT TITLE, SCORE

FROM MOVIE

WHERE TITLE = m_title;

IF (SCORE >= 8.0) THEN

dbms_output.PUT_LINE(TITLE || ' is a high score movie');

ELSIF (SCORE > = 5.0 AND SCORE < 8.0) THEN

dbms_output.PUT_LINE(TITLE || ' is a medium score movie');

ELSIF (SCORE < 5.0) THEN

dbms_output.PUT_LINE(TITLE || ' is a low score movie');

END;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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