CREATE TABLE \" ACTOR \" ( \"ID\" NUMBER(9,0) NOT NULL, \"NAME\" VARCHAR2(100) N
ID: 3699410 • 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)) ;
Youre using PL/SQL:
[Use TRIGGER for this question] When a new movie is inserted into the movie table, if the movie score is not specified, assign a score based on the following rules:
if the movie is earlier than year 2000, then assign 7.0 to its score; otherwise, assign 8.0 as the score.
After you implement the trigger, write three SQL queries to test it. The first two queries insert two movies into the movie table with NULL for score (you can make up two fake movies – one before 2000 and the other after 2000), respectively. Then write the third SQL query to list these two newly inserted movies to validate that your trigger accomplishes the task successfully. Include both your trigger and the three queries (as well as the output of the third query) in your solution.
Explanation / Answer
When a new movie is inserted into the movie table,
if the movie score is not specified, assign a score based on
the following rules:
if the movie is earlier than year 2000,
then assign 7.0 to its score; otherwise, assign 8.0 as the score.
CREATE TRIGGER TRIGGER_MOVIE
ON MOVIE
FOR INSERT
AS
IF( SELECT SCORE FROM INSERTED WHERE YR<2000) IS NULL
BEGIN
UPDATE MOVIE
SET SCORE=7.0
END
ELSE
BEGIN
UPDATE MOVIE
SET SCORE=8.0
END
1.INSERT INTO MOVIE VALUES(9,'OYE',2005,NULL,6,'MURGADOS');
2.INSERT INTO MOVIE VALUES(5,'RANI',1985,NULL,8,'RAJA');
3.SELECT * FROM MOVIE;