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)) ;

Youre using PL/SQL:

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

The PL/SQL procedure for copying the records with more than 50000 vvotes from the movie table to the New_Movie table is given below. The procedure has two parameters one is the table name from which the records are copied and another is the table to which the records will be copied.

Store procedure definition-

CREATE OR REPLACE PROCEDURE movie_copy(
p_tab_from varchar2,
p_tab_to varchar2)
IS
BEGIN
execute immediate 'insert into '||p_tab_to||' (select * from '||p_tab_from||' VOTES > 50000)';
dbms_output.PUT_LINE('Inserted' || sql%Rowcount || 'movies in New_Movie table');
END;

Calling the procedure-

movie_copy (Movie, New_Movie)

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects