PLZ help with the following use oracle or oracle sql live problem is in imagethe databaseCREATE TABLE MEMBERSHIP( MEM_NUM CHAR(3) CONSTRAINT MEMBER_MEM_NUM_PK PRIMARY KEY,MEM_FNAME VARCHAR(30) NOT NULL,MEM_LNAME VARCHAR(30) NOT NULL,MEM_STREET VARCHAR(15),MEM_CITY VARCHAR(10),MEM_STATE CHAR(2),MEM_ZIP CHAR(5),MEM_BALANCE NUMBER (2));ALTER TABLE MEMBERSHIPMODIFY MEM_STREET VARCHAR(25);CREATE TABLE RENTAL( RENT_NUM CHAR(4) CONSTRAINT RENTAL_RENT_NUM_PK PRIMARY KEY,RENT_DATE DATE,MEM_NUM CHAR(3),CONSTRAINT RENTAL_MEM_NUM_FK FOREIGN KEY (MEM_NUM) REFERENCES MEMBERSHIP);CREATE TABLE PRICE(PRICE_CODE CHAR(1) CONSTRAINT PRICE_PRICE_CODE_PK PRIMARY KEY,PRICE_DESC VARCHAR(20),PRICE_RENTFEE NUMBER (3,1),PRICE_DAILYATFEE NUMBER(3,1));CREATE TABLE MOVIE(MOVIE_NUM CHAR(4) CONSTRAINT MOVIE_MOVIE_NUM_PK PRIMARY KEY,MOVIE_NAME VARCHAR(30) NOT NULL,MOVIE_YEAR CHAR(4),MOVIE_COST NUMBER(5,2),MOVIE_GENRE VARCHAR(15),PRICE_CODE CHAR(1),CONSTRAINT MOVIE_PRICE_CODE_FK FOREIGN KEY (PRICE_CODE) REFERENCES PRICE);CREATE TABLE VIDEO(VID_NUM CHAR(5) CONSTRAINT VIDEO_VIDEO_NUM_PK PRIMARY KEY,VID_INDATE DATE,MOVIE_NUM CHAR(4),CONSTRAINT VIDEO_MOVIE_NUM_FK FOREIGN KEY (MOVIE_NUM) REFERENCES MOVIE);CREATE TABLE DETAILRENTAL(RENT_NUM CHAR(4),VID_NUM CHAR(5),DETAIL_FEE NUMBER(3,1),DETAIL_DUEDATE DATE,DETAIL_RETURNDATE DATE,DETAIL_DALYLATEFEE NUMBER(2),CONSTRAINT DETAILRENTAL_RENT_VID_PK PRIMARY KEY(RENT_NUM, VID_NUM),CONSTRAINT DETAILRENTAL_RENT_FK FOREIGN KEY (RENT_NUM) REFERENCES RENTAL,CONSTRAINT DETAILRENTAL_VID_FK FOREIGN KEY (VID_NUM) REFERENCES VIDEO);INSERT INTO MEMBERSHIP VALUES('102', 'Tami', 'Dawson', '2632 Takli Circle', 'Norene', 'TN', '37136', 11);INSERT INTO MEMBERSHIP VALUES('103', 'Curt', 'Knight', '4025 Cornell Court', 'Flatgap', 'KY', '41219', 6);INSERT INTO MEMBERSHIP VALUES('104', 'Jamal', 'Melendez', '788 East 145th Avenue', 'Quebeck', 'TN', '38579', 0);INSERT INTO MEMBERSHIP VALUES('105', 'Iva', 'Mcclain', '6045 Musket Ball Circle', 'Summit', 'KY', '42783', 15);INSERT INTO MEMBERSHIP VALUES('106', 'Miranda', 'Parks', '4469 Maxwell Place', 'Germantown', 'TN', '38402', 5);INSERT INTO RENTAL VALUES('1001', TO_DATE('01-MAR-09', 'DD-MM-YY'), '103');INSERT INTO RENTAL VALUES('1002', TO_DATE('01-MAR-09', 'DD-MM-YY'), '105');INSERT INTO RENTAL VALUES('1003', TO_DATE('02-MAR-09', 'DD-MM-YY'), '102');INSERT INTO PRICE VALUES('1', 'Standard', 2.0, 1);INSERT INTO PRICE VALUES('2', 'New Release', 3.5, 3);INSERT INTO PRICE VALUES('3', 'Discount', 1.5, 1);INSERT INTO PRICE VALUES('4', 'Weekly Special', 1, 0.5);INSERT INTO MOVIE VALUES('1234', 'The Cesar Family Christmas', 2007, 39.95, 'FAMILY', '2');INSERT INTO MOVIE VALUES('1235', 'Smokey Mountain Wildlife', 2004, 59.95, 'ACTION', '3');INSERT INTO MOVIE VALUES('1236', 'Richard Goodhope', 2008, 59.95, 'DRAMA', '2');INSERT INTO MOVIE VALUES('1237', 'Beatnik Fever', 2007, 29.95, 'COMEDY', '2');INSERT INTO MOVIE VALUES('1238', 'Constant Companion', 2008, 89.95, 'DRAMA', '2');INSERT INTO MOVIE VALUES('1239', 'Where Hope Dies', 1998, 25.49, 'DRAMA', '1');INSERT INTO MOVIE VALUES('1245', 'Time to Burn', 2005, 45.49, 'ACTION', '3');INSERT INTO MOVIE VALUES('1246', 'What He Doesnt Know', 2006, 58.29, 'COMEDY', '1');INSERT INTO VIDEO VALUES('54321', TO_DATE('18-JUN-08', 'DD-MM-YY'), '1234');INSERT INTO VIDEO VALUES('54324', TO_DATE('18-JUN-08', 'DD-MM-YY'), '1234');INSERT INTO VIDEO VALUES('54325', TO_DATE('18-JUN-08', 'DD-MM-YY'), '1234');INSERT INTO VIDEO VALUES('34341', TO_DATE('22-JAN-07', 'DD-MM-YY'), '1235');INSERT INTO VIDEO VALUES('34342', TO_DATE('22-JAN-07', 'DD-MM-YY'), '1235');INSERT INTO VIDEO VALUES('34366', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');INSERT INTO VIDEO VALUES('34367', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');INSERT INTO VIDEO VALUES('34368', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');INSERT INTO VIDEO VALUES('34369', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');INSERT INTO VIDEO VALUES('44392', TO_DATE('21-OCT-08', 'DD-MM-YY'), '1237'); 4. Create a stored procedure to enter data about the return of videos that have been rented.The procedure should satisfy the following requirements.The video number will be provided as a parameter.a) Verify the video number exists in the VIDEO table. If it does not exist, display amessage that the video number provided was not found and do not write any data tothe database.b) If the video number does exist, then use a Count() function to ensure that the videohas only one record in DETAILRENTAL for which it does not have a return date. If morethan one row in DETAILRENTAL indicates that the video is rented but not returned,display an error message that the video has multiple outstanding rentals and do notwrite any data to the database.c) If the video does not have any outstanding rentals, then update the video status forthe video in the VIDEO table to "IN", and display a message that the video had nooutstanding rentals but it is now available for rental. If the video has only oneoutstanding rental, then update the return date to the current system date, andupdate the video status for that video in the VIDEO table to "IN". Then display amessage stating that the video was successfully returned

Question

PLZ help with the following use oracle or oracle sql live problem is in imagethe databaseCREATE TABLE MEMBERSHIP( MEM_NUM CHAR(3) CONSTRAINT MEMBER_MEM_NUM_PK PRIMARY KEY,MEM_FNAME VARCHAR(30) NOT NULL,MEM_LNAME VARCHAR(30) NOT NULL,MEM_STREET VARCHAR(15),MEM_CITY VARCHAR(10),MEM_STATE CHAR(2),MEM_ZIP CHAR(5),MEM_BALANCE NUMBER (2));ALTER TABLE MEMBERSHIPMODIFY MEM_STREET VARCHAR(25);CREATE TABLE RENTAL( RENT_NUM CHAR(4) CONSTRAINT RENTAL_RENT_NUM_PK PRIMARY KEY,RENT_DATE DATE,MEM_NUM CHAR(3),CONSTRAINT RENTAL_MEM_NUM_FK FOREIGN KEY (MEM_NUM) REFERENCES MEMBERSHIP);CREATE TABLE PRICE(PRICE_CODE CHAR(1) CONSTRAINT PRICE_PRICE_CODE_PK PRIMARY KEY,PRICE_DESC VARCHAR(20),PRICE_RENTFEE NUMBER (3,1),PRICE_DAILYATFEE NUMBER(3,1));CREATE TABLE MOVIE(MOVIE_NUM CHAR(4) CONSTRAINT MOVIE_MOVIE_NUM_PK PRIMARY KEY,MOVIE_NAME VARCHAR(30) NOT NULL,MOVIE_YEAR CHAR(4),MOVIE_COST NUMBER(5,2),MOVIE_GENRE VARCHAR(15),PRICE_CODE CHAR(1),CONSTRAINT MOVIE_PRICE_CODE_FK FOREIGN KEY (PRICE_CODE) REFERENCES PRICE);CREATE TABLE VIDEO(VID_NUM CHAR(5) CONSTRAINT VIDEO_VIDEO_NUM_PK PRIMARY KEY,VID_INDATE DATE,MOVIE_NUM CHAR(4),CONSTRAINT VIDEO_MOVIE_NUM_FK FOREIGN KEY (MOVIE_NUM) REFERENCES MOVIE);CREATE TABLE DETAILRENTAL(RENT_NUM CHAR(4),VID_NUM CHAR(5),DETAIL_FEE NUMBER(3,1),DETAIL_DUEDATE DATE,DETAIL_RETURNDATE DATE,DETAIL_DALYLATEFEE NUMBER(2),CONSTRAINT DETAILRENTAL_RENT_VID_PK PRIMARY KEY(RENT_NUM, VID_NUM),CONSTRAINT DETAILRENTAL_RENT_FK FOREIGN KEY (RENT_NUM) REFERENCES RENTAL,CONSTRAINT DETAILRENTAL_VID_FK FOREIGN KEY (VID_NUM) REFERENCES VIDEO);INSERT INTO MEMBERSHIP VALUES('102', 'Tami', 'Dawson', '2632 Takli Circle', 'Norene', 'TN', '37136', 11);INSERT INTO MEMBERSHIP VALUES('103', 'Curt', 'Knight', '4025 Cornell Court', 'Flatgap', 'KY', '41219', 6);INSERT INTO MEMBERSHIP VALUES('104', 'Jamal', 'Melendez', '788 East 145th Avenue', 'Quebeck', 'TN', '38579', 0);INSERT INTO MEMBERSHIP VALUES('105', 'Iva', 'Mcclain', '6045 Musket Ball Circle', 'Summit', 'KY', '42783', 15);INSERT INTO MEMBERSHIP VALUES('106', 'Miranda', 'Parks', '4469 Maxwell Place', 'Germantown', 'TN', '38402', 5);INSERT INTO RENTAL VALUES('1001', TO_DATE('01-MAR-09', 'DD-MM-YY'), '103');INSERT INTO RENTAL VALUES('1002', TO_DATE('01-MAR-09', 'DD-MM-YY'), '105');INSERT INTO RENTAL VALUES('1003', TO_DATE('02-MAR-09', 'DD-MM-YY'), '102');INSERT INTO PRICE VALUES('1', 'Standard', 2.0, 1);INSERT INTO PRICE VALUES('2', 'New Release', 3.5, 3);INSERT INTO PRICE VALUES('3', 'Discount', 1.5, 1);INSERT INTO PRICE VALUES('4', 'Weekly Special', 1, 0.5);INSERT INTO MOVIE VALUES('1234', 'The Cesar Family Christmas', 2007, 39.95, 'FAMILY', '2');INSERT INTO MOVIE VALUES('1235', 'Smokey Mountain Wildlife', 2004, 59.95, 'ACTION', '3');INSERT INTO MOVIE VALUES('1236', 'Richard Goodhope', 2008, 59.95, 'DRAMA', '2');INSERT INTO MOVIE VALUES('1237', 'Beatnik Fever', 2007, 29.95, 'COMEDY', '2');INSERT INTO MOVIE VALUES('1238', 'Constant Companion', 2008, 89.95, 'DRAMA', '2');INSERT INTO MOVIE VALUES('1239', 'Where Hope Dies', 1998, 25.49, 'DRAMA', '1');INSERT INTO MOVIE VALUES('1245', 'Time to Burn', 2005, 45.49, 'ACTION', '3');INSERT INTO MOVIE VALUES('1246', 'What He Doesnt Know', 2006, 58.29, 'COMEDY', '1');INSERT INTO VIDEO VALUES('54321', TO_DATE('18-JUN-08', 'DD-MM-YY'), '1234');INSERT INTO VIDEO VALUES('54324', TO_DATE('18-JUN-08', 'DD-MM-YY'), '1234');INSERT INTO VIDEO VALUES('54325', TO_DATE('18-JUN-08', 'DD-MM-YY'), '1234');INSERT INTO VIDEO VALUES('34341', TO_DATE('22-JAN-07', 'DD-MM-YY'), '1235');INSERT INTO VIDEO VALUES('34342', TO_DATE('22-JAN-07', 'DD-MM-YY'), '1235');INSERT INTO VIDEO VALUES('34366', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');INSERT INTO VIDEO VALUES('34367', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');INSERT INTO VIDEO VALUES('34368', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');INSERT INTO VIDEO VALUES('34369', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');INSERT INTO VIDEO VALUES('44392', TO_DATE('21-OCT-08', 'DD-MM-YY'), '1237'); 4. Create a stored procedure to enter data about the return of videos that have been rented.The procedure should satisfy the following requirements.The video number will be provided as a parameter.a) Verify the video number exists in the VIDEO table. If it does not exist, display amessage that the video number provided was not found and do not write any data tothe database.b) If the video number does exist, then use a Count() function to ensure that the videohas only one record in DETAILRENTAL for which it does not have a return date. If morethan one row in DETAILRENTAL indicates that the video is rented but not returned,display an error message that the video has multiple outstanding rentals and do notwrite any data to the database.c) If the video does not have any outstanding rentals, then update the video status forthe video in the VIDEO table to &#34;IN&#34;, and display a message that the video had nooutstanding rentals but it is now available for rental. If the video has only oneoutstanding rental, then update the return date to the current system date, andupdate the video status for that video in the VIDEO table to &#34;IN&#34;. Then display amessage stating that the video was successfully returned

Accepted Answer

The complete answer is given below.