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 Trigger. First manually create a new table (named New_Movie) in APEX. This table should have the same schema as the movie table. You don’t need to show the code for creating this table. Then write PL/SQL to do the following: find movies with at least 50,000 votes. Insert all of these movies (all columns) into the New_Movie table. If the number of movies inserted is <N>, you should display on the screen as follows –

Inserted <N> movies into the New_Movie table

Explanation / Answer

PL/SQL CODE for your question. I add the if statement in the PL/SQL Code. If you dont want just remove the if to end if and simple write dbms_output.put_line('Inserted '||c ||' movies into the New_Movie table');

I write the program in PL/SQL anonymous block, if you require procedure then mention in comments. If you require in trigger then tell me when you fire the trigger , I will write the code for you.

PL/SQL CODE

declare

c number;

begin

----inserted records into new_movie table

insert into new_movie select * from movie where votes>=50000;

----count the records and store in c

select count(*) into c from movie where votes>=50000;

-----Display the output

if c>=1

then

dbms_output.put_line('Inserted '||c ||' movies into the New_Movie table');

else

dbms_output.put_line('No movies having 50000 votes');

end if;

end;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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