CREATE TABLE \" ACTOR \" ( \"ID\" NUMBER(9,0) NOT NULL, \"NAME\" VARCHAR2(100) N
ID: 3699398 • 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)) ;
You're 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
DECLARE
m_id MOVIE.ID%type;
m_name MOVIE.TITle%type
m_score MOVIE.SCORE%type
BEGIN
m_id:=&Please enter movie id;
select title,title from movie where id=m_id;
if(m_score>=8.0) THEN
dbms_output_line(m_name || 'is a high-score movie');
ELSIF ( m_score>=5.0 and m_score<8.0) THEN
dbms_output_line(m_name || 'is a medium-score movie');
ELSE
dbms_output_line(m_name || 'is a low-score movie' );
END IF;
END;