CREATE TABLE MEMBERSHIP (MEM_NUM CHAR(5) CONSTRAINT MEM_MEM_NUM_PK PRIMARY KEY, MEM_FNAME VARCHAR(20) NOT NULL, MEM_LNAME VARCHAR(20)NOT NULL, MEM_STREET VARCHAR(30), MEM_CITY VARCHAR(20), MEM_STATE VARCHAR(20), MEM_ZIP CHAR(5), MEM_BALANCE NUMBER(3));

INSERT INTO MEMBERSHIP VALUES('102','TAMI','DAWSON','2632 TAKLI CIRCLE','NORENE','TN','37136',11.11); INSERT INTO MEMBERSHIP VALUES('103','CURT','NULL','4025 CORNELL COURT','FLAGAP','KY','41219',666.11);

UPDATE MEMBERSHIP SET MEM_BALANCE = 6 WHERE MEM_NUM = '103';

SELECT * FROM MEMBERSHIP;

ALTER TABLE MEMBERSHIP MODIFY MEM_STATE CHAR(2);

CREATE TABLE RENTAL (RENT_NUM CHAR(4) CONSTRAINT RENTAL_RENT_NUM_PK PRIMARY KEY, RENT_DATE DATE, MEM_NUM CHAR(5)NOT NULL, CONSTRAINT RENTAL_MEM_NUM_FK FOREIGN KEY (MEM_NUM)REFERENCES MEMBERSHIP);

INSERT INTO RENTAL VALUES ('1001', TO_DATE('01-03-1990','DD-MM-YYYY'),'102');

INSERT INTO RENTAL VALUES ('1002', TO_DATE('01-03-1990','DD-MM-YYYY'),'103');

CREATE TABLE PRICE ( PRICE_CODE CHAR(1) PRIMARY KEY, PRICE_DESC VARCHAR(25) NOT NULL, PRICE_RENTFEE NUMBER(3,1), PRICE_DAILYLATEFEE NUMBER(3,1));

CREATE TABLE MOVIE (MOVIE_NUM CHAR(6) CONSTRAINT MOVIE_NUM_PK PRIMARY KEY, MOVIE_NAME VARCHAR(25) 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(6) PRIMARY KEY, VID_INDATE DATE, MOVIE_NUM CHAR(6)NOT NULL, CONSTRAINT VIDEO_MOVIE_NUM_FK FOREIGN KEY(MOVIE_NUM)REFERENCES MOVIE);

CREATE TABLE DETAILRENTAL (RENT_NUM CHAR(4), VID_NUM CHAR(6), DETAIL_FEE NUMBER(3,1), DETAIL_DUEDATE DATE, DETAIL_RETURNDATE DATE, DETAIL_DAILYLATEFEE NUMBER(2), CONSTRAINT DETAIL_RENT_NUM_VID_NUM_PK PRIMARY KEY (RENT_NUM, VID_NUM), CONSTRAINT DETAIL_RENT_NUM_FK FOREIGN KEY(RENT_NUM) REFERENCES RENTAL, CONSTRAINT DETAIL_VIDEO_NUM_FK FOREIGN KEY(VID_NUM) REFERENCES VIDEO);

QUESTIONS:

1. Write the SQL command to change the price code for all Action movies to price code 3. Write the SQL command to save the changes made to the table. 2. Write a single SQL command to increase all price rental fee values by $0.50. 3. Write the SQL command to change the movie year for movie number 1245 to 2014 4. Alter the PRICE table to include an attribute named PRICE_RENTDAYS to store integers up to 2 digits. The attribute should not accept null values, and should have a CHECK constraint less than 10. 5. Issue a ROLLBACK command and then check the table structure. Did anything change? Why or why not? 6. Alter the VIDEO table to include an attribute named VID_STATUS to store character data up to 4 characters long. The attribute should have a constraint to enforce the values (IN, OUT, and LOST)