Begin for c in (select table_name from user_tables) loop execute immediate ('drop table '||c.table_name||' cascade constraints'); end loop; End; /

CREATE 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 MEMBERSHIP MODIFY 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));

ALTER TABLE PRICE ADD (PRICE_RENTDAYS NUMBER(2, 0) DEFAULT 3 NOT NULL);

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

VID_STATUS

ALTER TABLE VIDEO ADD (VID_STATUS VARCHAR2(4) DEFAULT 'IN' NOT NULL) ADD (CONSTRAINT video_ck_vid_status CHECK(VID_STATUS = 'IN' OR VID_STATUS = 'OUT' OR VID_STATUS = 'LOST' ));

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

UPDATE MOVIE SET MOVIE_YEAR = 1245 TO 2014;

UPDATE MOVIE SET PRICE_CODE = 3 WHERE MOVIE_GENRE = 'Action';

UPDATE PRICE SET PRICE_CODE = PRICE_CODE + '0.50';

SAVE PRICE

1. Use your Tiny Viedio database that we created in Lab_03 to answer the following using a subquery: a. Find all movies with a cost less than or equal to the average movies cost b. Write a query to display all the movies that have video copies c. Write a query to display the membership number, first name, last name, and balance of the memberships that have a rental on March-2009. d. Create a New Member table with only using the membership name (Fname and Lname) and membership city. e. List movie title and movie year for movies that have a movie cost that is greater than all individual movie costs for movies with price description "weekly Special