CREATE TABLE \" ACTOR \" ( \"ID\" NUMBER(9,0) NOT NULL, \"NAME\" VARCHAR2(100) N
ID: 3703041 • 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 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;