CREATE TABLE PRICE (
PRICE_CODE INTEGER NOT NULL UNIQUE,
PRICE_DESCRIPTION CHAR(15) NOT NULL,
PRICE_RENTFEE VARCHAR(3) NOT NULL,
PRICE_DAILYLATEFEE VARCHAR(3) NOT NULL,
PRIMARY KEY (PRICE_CODE));

CREATE TABLE MOVIE (
MOVIE_NUM INTEGER NOT NULL UNIQUE,
MOVIE_TITLE VARCHAR(35) NOT NULL,
MOVIE_YEAR INTEGER NOT NULL,
MOVIE_COST NUMBER NOT NULL,
MOVIE_GENRE CHAR(6) NOT NULL,
PRICE_CODE INTEGER,
PRIMARY KEY (MOVIE_NUM),
FOREIGN KEY (PRICE_CODE) references PRICE(PRICE_CODE)
);

CREATE TABLE VIDEO (
VID_NUM INTEGER NOT NULL UNIQUE,
VID_INDATE DATE NOT NULL,
MOVIE_NUM INTEGER NOT NULL,
PRIMARY KEY (VID_NUM),
FOREIGN KEY (MOVIE_NUM) references MOVIE(MOVIE_NUM)
);

CREATE TABLE DETAILRENTAL (
RENT_NUM INTEGER NOT NULL,
VID_NUM INTEGER NOT NULL,
DETAIL_FEE NUMBER NOT NULL,
DETAIL_DUEDATE DATE NOT NULL,
DETAIL_RETURNDATE DATE,
DETAIL_DAILYLATEFEE NUMBER NOT NULL,
PRIMARY KEY (RENT_NUM, VID_NUM),
FOREIGN KEY (RENT_NUM) references RENTAL(RENT_NUM),
FOREIGN KEY (VID_NUM) references VIDEO(VID_NUM),
CONSTRAINT VID_UI1 UNIQUE (RENT_NUM, VID_NUM)
);

CREATE TABLE RENTAL (
RENT_NUM INTEGER NOT NULL UNIQUE,
RENT_DATE DATE NOT NULL,
MEM_NUM INTEGER NOT NULL,
PRIMARY KEY (RENT_NUM),
FOREIGN KEY (MEM_NUM) references MEMBERSHIP(MEM_NUM)
);

CREATE TABLE MEMBERSHIP (
MEM_NUM INTEGER NOT NULL UNIQUE,
MEM_FNAME VARCHAR(15) NOT NULL,
MEM_LNAME VARCHAR(15) NOT NULL,
MEM_STREET VARCHAR(35) NOT NULL,
MEM_CITY CHAR(15) NOT NULL,
MEM_STATE CHAR(2) NOT NULL,
MEM_ZIP VARCHAR(5) NOT NULL,
MEM_BALANCE NUMBER,
PRIMARY KEY (MEM_NUM)
);







INSERT INTO PRICE VALUES('1', 'Standard', '2', '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', '.5');

INSERT INTO MOVIE VALUES('1234', 'The Ceasar Family Christmas', '2016', '39.95', 'FAMILY', '2');
INSERT INTO MOVIE VALUES('1235', 'Smokey Mountain Wildlife', '2013', '59.95', 'ACTION', '1');
INSERT INTO MOVIE VALUES('1236', 'Richard Goodhope', '2017', '59.95', 'DRAMA', '2');
INSERT INTO MOVIE VALUES('1237', 'Beatnik Fever', '2016', '29.95', 'COMEDY', '2');
INSERT INTO MOVIE VALUES('1238', 'Constant Companion', '2017', '89.95', 'DRAMA', ' ');
INSERT INTO MOVIE VALUES('1239', 'Where Hope Dies', '2007', '25.49', 'DRAMA', '3');
INSERT INTO MOVIE VALUES('1245', 'Time to Burn', '2014', '45.49', 'ACTION', '1');
INSERT INTO MOVIE VALUES('1246', 'What He Doesn`t Know', '2015', '58.29', 'COMEDY', '1');

INSERT INTO VIDEO VALUES('54321', '18-JUN-17', '1234');
INSERT INTO VIDEO VALUES('54324', '18-JUN-17', '1234');
INSERT INTO VIDEO VALUES('54325', '18-JUN-17', '1234');
INSERT INTO VIDEO VALUES('34341', '22-JAN-16', '1235');
INSERT INTO VIDEO VALUES('34342', '22-JAN-16', '1235');
INSERT INTO VIDEO VALUES('34366', 'O2-MAR-18', '1236');
INSERT INTO VIDEO VALUES('34367', 'O2-MAR-18', '1236');
INSERT INTO VIDEO VALUES('34368', 'O2-MAR-18', '1236');
INSERT INTO VIDEO VALUES('34369', 'O2-MAR-18', '1236');
INSERT INTO VIDEO VALUES('44392', '21-OCT-17', '1237');
INSERT INTO VIDEO VALUES('44397', '21-OCT-17', '1237');
INSERT INTO VIDEO VALUES('59237', '14-FEB-18', '1237');
INSERT INTO VIDEO VALUES('61388', '25-JAN-16', '1239');
INSERT INTO VIDEO VALUES('61353', '28-JAN-15', '1245');
INSERT INTO VIDEO VALUES('61354', '28-JAN-15', '1245');
INSERT INTO VIDEO VALUES('61367', '30-JUL-17', '1246');
INSERT INTO VIDEO VALUES('61369', '30-JUL-17', '1246');

INSERT INTO DETAILRENTAL VALUES('1001', '34342', '2', '04-MAR-18', 'O2-MAR-18', ' ');
INSERT INTO DETAILRENTAL VALUES('1001', '61353', '2', '04-MAR-18', 'O3-MAR-18', '1');
INSERT INTO DETAILRENTAL VALUES('1002', '59237', '3.5', '04-MAR-18', 'O4-MAR-18', '3');
INSERT INTO DETAILRENTAL VALUES('1003', '54325', '3.5', '04-MAR-18', 'O9-MAR-18', '3');
INSERT INTO DETAILRENTAL VALUES('1003', '61369', '2', '06-MAR-18', 'O9-MAR-18', '1');
INSERT INTO DETAILRENTAL VALUES('1003', '61388', '0', '06-MAR-18', 'O9-MAR-18', '1');
INSERT INTO DETAILRENTAL VALUES('1004', '44392', '3.5', '05-MAR-18', 'O7-MAR-18', '3');
INSERT INTO DETAILRENTAL VALUES('1004', '34367', '3.5', '05-MAR-18', 'O7-MAR-18', '3');
INSERT INTO DETAILRENTAL VALUES('1004', '34341', '2', '07-MAR-18', 'O7-MAR-18', '1');
INSERT INTO DETAILRENTAL VALUES('1005', '34342', '2', '07-MAR-18', 'O5-MAR-18', '1');
INSERT INTO DETAILRENTAL VALUES('1005', '44397', '3.5', '05-MAR-18', 'O5-MAR-18', '3');
INSERT INTO DETAILRENTAL VALUES('1006', '34366', '3.5', '05-MAR-18', 'O4-MAR-18', '3');
INSERT INTO DETAILRENTAL VALUES('1006', '61367', '2', '07-MAR-18', ' ', '1');
INSERT INTO DETAILRENTAL VALUES('1007', '34368', '3.5', '05-MAR-18', ' ', '3');
INSERT INTO DETAILRENTAL VALUES('1008', '34369', '3.5', '05-MAR-18', 'O5-MAR-18', '3');
INSERT INTO DETAILRENTAL VALUES('1009', '54324', '3.5', '05-MAR-18', ' ', '3');
INSERT INTO DETAILRENTAL VALUES('1001', '34366', '3.5', '04-MAR-18', 'O2-MAR-18', '3');

INSERT INTO RENTAL VALUES('1001', '01-MAR-18', '103');
INSERT INTO RENTAL VALUES('1002', '01-MAR-18', '105');
INSERT INTO RENTAL VALUES('1003', '02-MAR-18', '102');
INSERT INTO RENTAL VALUES('1004', '02-MAR-18', '110');
INSERT INTO RENTAL VALUES('1005', '02-MAR-18', '111');
INSERT INTO RENTAL VALUES('1006', '02-MAR-18', '107');
INSERT INTO RENTAL VALUES('1007', '02-MAR-18', '104');
INSERT INTO RENTAL VALUES('1008', '03-MAR-18', '105');
INSERT INTO RENTAL VALUES('1009', '03-MAR-18', '111');

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', '38183', '0');
INSERT INTO MEMBERSHIP VALUES('107', 'Rosario', 'Elliot', '7578 Danner Avenue', 'Columbia', 'TN', '38402', '5');
INSERT INTO MEMBERSHIP VALUES('108', 'Mattie', 'Guy', '4390 Evergreen Street', 'Lily', 'KY', '40740', '0');
INSERT INTO MEMBERSHIP VALUES('109', 'Clint', 'Ochoa', '1711 Elm Street', 'Greeneville', 'TN', '37745', '10');
INSERT INTO MEMBERSHIP VALUES('110', 'Lewis', 'Rosales', '4524 Southwind Circle', 'Counce', 'TN', '38326', '0');
INSERT INTO MEMBERSHIP VALUES('111', 'Stacy', 'Mann', '2789 East Cook Avenue', 'Murfreesboro', 'TN', '37132', '8');
INSERT INTO MEMBERSHIP VALUES('112', 'Luis', 'Trujillo', '7267 Melvin Avenue', 'Heiskell', 'TN', '37754', '3');
INSERT INTO MEMBERSHIP VALUES('113', 'Minnie', 'Gonzales', '6430 Vasili Drive', 'Williston', 'TN', '38076', '0');









-- Using the tables that were created in Case 1 and the data that was loaded into those tables in Case 2, alter the DETAILRENTAL table to include a derived attribute named DETAIL_DAYSLATE to store integers of up to three digits. The attribute should accept null values.
ALTER TABLE DETAILRENTAL  
ADD DETAIL_DAYSLATE NUMBER(3,0);