Answered: Write the SQL command to change the…

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

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(30),

MEM_CITY VARCHAR(10),

MEM_STATE CHAR(2),

MEM_ZIP CHAR(5),

MEM_BALANCE NUMBER (2));

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

INSERT INTO DETAILRENTAL VALUES('1001', '34342', 2, TO_DATE('04-MAR-09', 'DD-MM-YY'), TO_DATE('02-MAR-09', 'DD-MM-YY'), 1);

INSERT INTO DETAILRENTAL VALUES('1001', '54321', 2, TO_DATE('04-MAR-09', 'DD-MM-YY'), TO_DATE('03-MAR-09', 'DD-MM-YY'), 1);

INSERT INTO DETAILRENTAL VALUES('1002', '54321', 3.5, TO_DATE('04-MAR-09', 'DD-MM-YY'), TO_DATE('04-MAR-09', 'DD-MM-YY'), 3);

INSERT INTO DETAILRENTAL VALUES('1003', '54325', 3.5, TO_DATE('04-MAR-09', 'DD-MM-YY'), TO_DATE('09-MAR-09', 'DD-MM-YY'), 3);

INSERT INTO DETAILRENTAL VALUES('1003', '54321', 2, TO_DATE('06-MAR-09', 'DD-MM-YY'), TO_DATE('09-MAR-09', 'DD-MM-YY'), 1);

INSERT INTO DETAILRENTAL VALUES('1003', '44392', 3.5, TO_DATE('05-MAR-09', 'DD-MM-YY'), TO_DATE('07-MAR-09', 'DD-MM-YY'), 3);

INSERT INTO DETAILRENTAL VALUES('1003', '34367', 3.5, TO_DATE('05-MAR-09', 'DD-MM-YY'), TO_DATE('07-MAR-09', 'DD-MM-YY'), 3);

INSERT INTO DETAILRENTAL VALUES('1003', '34341', 2, TO_DATE('07-MAR-09', 'DD-MM-YY'), TO_DATE('07-MAR-09', 'DD-MM-YY'), 1);

INSERT INTO DETAILRENTAL VALUES('1002', '44392', 3.5, TO_DATE('05-MAR-09', 'DD-MM-YY'), TO_DATE('05-MAR-09', 'DD-MM-YY'), 3);

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

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 3 steps with 4 images

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

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