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, write the SQL command to change the movie year for movie number 1245 to 2014.

UPDATE MOVIE 
SET MOVIE_YEAR = 2010 
WHERE  MOVIE_NUM = 1245;

-- check to see if it worked

select MOVIE_YEAR
FROM MOVIE
WHERE MOVIE_NUM = 1245;