Question

Hi Please help me ASAP need SQL commands only for MS Access

The following tables provide a very small portion of the data that will be kept in the database. The data needs to be inserted into the database for testing purposes. Write the INSERT commands necessary to place the following data in the tables that were created in Problem 47. (If required by your DBMS, be certain to save the rows permanently.)

only for MS Access!!!

Answer 1

Create table MEMBERSHIP
(MEM_NUM CHAR(3) CONSTRAINT PK_MEMNUM PRIMARY KEY,
MEM_FNAME VARCHAR2(15),
MEM_LNAME VARCHAR2(15),
MEM_STREET VARCHAR2(40),
MEM_CITY VARCHAR2(15),
MEM_STATE VARCHAR2(2),
MEM_ZIP CHAR(5),
MEM_BALANCE CHAR(2));

Create table RENTAL
(RENT_NUM CHAR(4) CONSTRAINT PK_RENTNUM PRIMARY KEY,
RENT_DATE DATE,
MEM_NUM CHAR(3) ,CONSTRAINT FK_MEMNUM FOREIGN KEY(MEM_NUM) REFERENCES MEMBERSHIP(MEM_NUM));

Create table PRICE
(PRICE_CODE CHAR(1) CONSTRAINT PK_PRICECODE PRIMARY KEY,
PRICE_DESCRIPTION VARCHAR2(15),
PRICE_RENTFEE NUMBER(2,1),
PRICE_DAILYLATEFEE NUMBER(2,1));

Create table MOVIE
(MOVIE_NUM CHAR(4) CONSTRAINT PK_MOVIENUM PRIMARY KEY,
MOVIE_TITLE VARCHAR2(40),
MOVIE_YEAR CHAR(4),
MOVIE_COST NUMBER(4,2),
MOVIE_GENRE VARCHAR2(15),
PRICE_CODE CHAR(1),CONSTRAINT FK_PRCCODE FOREIGN KEY(PRICE_CODE) REFERENCES PRICE(PRICE_CODE));

Create table VIDEO
(VID_NUM CHAR(5) CONSTRAINT PK_VIDEONUM PRIMARY KEY,
VID_INDATE DATE,
MOVIE_NUM CHAR(4),CONSTRAINT FK_MOVIENUM FOREIGN KEY(MOVIE_NUM) REFERENCES MOVIE(MOVIE_NUM));

Create table DETAILRENTAL
(RENT_NUM CHAR(4),
VID_NUM CHAR(5),
DETAIL_FEE NUMBER(2,1),
DETAIL_DUEDATE DATE ,
DETAIL_RETURNDATE DATE ,
DETAIL_DAILYLATEFEE CHAR(1),
CONSTRAINT DETLRENT_PK PRIMARY KEY (RENT_NUM,VID_NUM),
CONSTRAINT FK_RENTNUM FOREIGN KEY(RENT_NUM) REFERENCES RENTAL(RENT_NUM),
CONSTRAINT FK_VIDNUM FOREIGN KEY(VID_NUM) REFERENCES VIDEO(VID_NUM));

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

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

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

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' ,1);
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'   ,3);
INSERT INTO MOVIE VALUES (1245, 'Time to Burn'                    ,2005, 45.49 ,'ACTION' ,1);
INSERT INTO MOVIE VALUES (1246, 'What He Doesnt Know '           ,2006, 58.29 ,'COMEDY' ,1);

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

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

COMMIT;

UPDATE MOVIE SET MOVIE_YEAR = '2006' WHERE MOVIE_NUM = 1245 ;

i have answered the above , hope this helps