DROP TABLE CUSTOMERS; DROP TABLE ORDERS; DROP TABLE PUBLISHER; DROP TABLE AUTHOR; DROP TABLE BOOKS; DROP TABLE ORDERITEMS; DROP TABLE BOOKAUTHOR; DROP TABLE PROMOTION; CREATE TABLE Customers (Customer# NUMBER(4) PRIMARY KEY, LastName VARCHAR2(10), FirstName VARCHAR2(10), Address VARCHAR2(20), City VARCHAR2(12), State VARCHAR2(2), Zip VARCHAR2(5), Referred NUMBER(4)); CREATE TABLE Orders (Order# NUMBER(4) PRIMARY KEY, Customer# NUMBER(4) REFERENCES Customers(customer#), OrderDate DATE, ShipDate DATE, ShipStreet VARCHAR2(18), ShipCity VARCHAR2(15), ShipState VARCHAR2(2), ShipZip VARCHAR2(5)); CREATE TABLE Publisher (PubID NUMBER(2) PRIMARY KEY, Name VarCHAR2(23), Contact VARCHAR2(15), Phone VARCHAR2(12)); CREATE TABLE Author (AuthorID Varchar2(4) PRIMARY KEY, Lname VARCHAR2(10), Fname VARCHAR2(10)); CREATE TABLE Books (ISBN VARCHAR2(10) PRIMARY KEY, Title VARCHAR2(30), PubDate DATE, PubID NUMBER(2) REFERENCES Publisher(pubid), Cost NUMBER(5,2), Retail NUMBER(5,2), Category VARCHAR2(12)); CREATE TABLE Orderitems (Order# NUMBER(4) NOT NULL REFERENCES Orders(order#), Item# NUMBER(2) NOT NULL, ISBN VARCHAR2(10) REFERENCES Books(ISBN), Quantity NUMBER(3) ); CREATE TABLE Bookauthor (ISBN VARCHAR2(10) REFERENCES Books(ISBN), AuthorID VARCHAR2(4) REFERENCES Author(authorid), CONSTRAINT bookauthor_pk PRIMARY KEY (isbn,authorid)); CREATE TABLE Promotion (Gift varchar2(15), Minretail number(5,2), Maxretail number(5,2)); INSERT INTO CUSTOMERS VALUES (1001, 'MORALES', 'BONITA', 'P.O. BOX 651', 'EASTPOINT', 'FL', '32328', NULL); INSERT INTO CUSTOMERS VALUES (1002, 'THOMPSON', 'RYAN', 'P.O. BOX 9835', 'SANTA MONICA', 'CA', '90404', NULL); INSERT INTO CUSTOMERS VALUES (1003, 'SMITH', 'LEILA', 'P.O. BOX 66', 'TALLAHASSEE', 'FL', '32306', NULL); INSERT INTO CUSTOMERS VALUES (1004, 'PIERSON', 'THOMAS', '69821 SOUTH AVENUE', 'BOISE', 'ID', '83707', NULL); INSERT INTO CUSTOMERS VALUES (1005, 'GIRARD', 'CINDY', 'P.O. BOX 851', 'SEATTLE', 'WA', '98115', NULL); INSERT INTO CUSTOMERS VALUES (1006, 'CRUZ', 'MESHIA', '82 DIRT ROAD', 'ALBANY', 'NY', '12211', NULL); INSERT INTO CUSTOMERS VALUES (1007, 'GIANA', 'TAMMY', '9153 MAIN STREET', 'AUSTIN', 'TX', '78710', 1003); INSERT INTO CUSTOMERS VALUES (1008, 'JONES', 'KENNETH', 'P.O. BOX 137', 'CHEYENNE', 'WY', '82003', NULL); INSERT INTO CUSTOMERS VALUES (1009, 'PEREZ', 'JORGE', 'P.O. BOX 8564', 'BURBANK', 'CA', '91510', 1003); INSERT INTO CUSTOMERS VALUES (1010, 'LUCAS', 'JAKE', '114 EAST SAVANNAH', 'ATLANTA', 'GA', '30314', NULL); INSERT INTO CUSTOMERS VALUES (1011, 'MCGOVERN', 'REESE', 'P.O. BOX 18', 'CHICAGO', 'IL', '60606', NULL); INSERT INTO CUSTOMERS VALUES (1012, 'MCKENZIE', 'WILLIAM', 'P.O. BOX 971', 'BOSTON', 'MA', '02110', NULL); INSERT INTO CUSTOMERS VALUES (1013, 'NGUYEN', 'NICHOLAS', '357 WHITE EAGLE AVE.', 'CLERMONT', 'FL', '34711', 1006); INSERT INTO CUSTOMERS VALUES (1014, 'LEE', 'JASMINE', 'P.O. BOX 2947', 'CODY', 'WY', '82414', NULL); INSERT INTO CUSTOMERS VALUES (1015, 'SCHELL', 'STEVE', 'P.O. BOX 677', 'MIAMI', 'FL', '33111', NULL); INSERT INTO CUSTOMERS VALUES (1016, 'DAUM', 'MICHELL', '9851231 LONG ROAD', 'BURBANK', 'CA', '91508', 1010); INSERT INTO CUSTOMERS VALUES (1017, 'NELSON', 'BECCA', 'P.O. BOX 563', 'KALMAZOO', 'MI', '49006', NULL); INSERT INTO CUSTOMERS VALUES (1018, 'MONTIASA', 'GREG', '1008 GRAND AVENUE', 'MACON', 'GA', '31206', NULL); INSERT INTO CUSTOMERS VALUES (1019, 'SMITH', 'JENNIFER', 'P.O. BOX 1151', 'MORRISTOWN', 'NJ', '07962', 1003); INSERT INTO CUSTOMERS VALUES (1020, 'FALAH', 'KENNETH', 'P.O. BOX 335', 'TRENTON', 'NJ', '08607', NULL); INSERT INTO ORDERS VALUES (1000,1005,TO_DATE('31-03-05','DD-MM-YY'),TO_DATE('02-04-05','DD-MM-YY'),'1201 ORANGE AVE', 'SEATTLE', 'WA', '98114'); INSERT INTO ORDERS VALUES (1001,1010,TO_DATE('31-03-05', 'DD-MM-YY'),TO_DATE('01-04-05','DD-MM-YY'), '114 EAST SAVANNAH', 'ATLANTA', 'GA', '30314'); INSERT INTO ORDERS VALUES (1002,1011,TO_DATE('31-03-05','DD-MM-YY'), TO_DATE('01-04-05','DD-MM-YY'),'58 TILA CIRCLE', 'CHICAGO', 'IL', '60605'); INSERT INTO ORDERS VALUES (1003,1001,TO_DATE('01-04-05','DD-MM-YY'),TO_DATE('01-04-05','DD-MM-YY'),'958 MAGNOLIA LANE', 'EASTPOINT', 'FL', '32328'); INSERT INTO ORDERS VALUES (1004,1020,TO_DATE('01-04-05','DD-MM-YY'),TO_DATE('05-04-05','DD-MM-YY'),'561 ROUNDABOUT WAY', 'TRENTON', 'NJ', '08601'); INSERT INTO ORDERS VALUES (1005,1018,TO_DATE('01-04-05','DD-MM-YY'),TO_DATE('02-04-05','DD-MM-YY'), '1008 GRAND AVENUE', 'MACON', 'GA', '31206'); INSERT INTO ORDERS VALUES (1006,1003,TO_DATE('01-04-05','DD-MM-YY'),TO_DATE('02-04-05','DD-MM-YY'),'558A CAPITOL HWY.', 'TALLAHASSEE', 'FL', '32307'); INSERT INTO ORDERS VALUES (1007,1007,TO_DATE('02-04-05','DD-MM-YY'),TO_DATE('04-04-05','DD-MM-YY'), '9153 MAIN STREET', 'AUSTIN', 'TX', '78710'); INSERT INTO ORDERS VALUES (1008,1004,TO_DATE('02-04-05','DD-MM-YY'),TO_DATE('03-04-05','DD-MM-YY'), '69821 SOUTH AVENUE', 'BOISE', 'ID', '83707'); INSERT INTO ORDERS VALUES (1009,1005,TO_DATE('03-04-05','DD-MM-YY'),TO_DATE('05-04-05','DD-MM-YY'),'9 LIGHTENING RD.', 'SEATTLE', 'WA', '98110'); INSERT INTO ORDERS VALUES (1010,1019,TO_DATE('03-04-05','DD-MM-YY'),TO_DATE('04-04-05','DD-MM-YY'),'384 WRONG WAY HOME', 'MORRISTOWN', 'NJ', '07960'); INSERT INTO ORDERS VALUES (1011,1010,TO_DATE('03-04-05','DD-MM-YY'),TO_DATE('05-04-05','DD-MM-YY'), '102 WEST LAFAYETTE', 'ATLANTA', 'GA', '30311'); INSERT INTO ORDERS VALUES (1012,1017,TO_DATE('03-04-05','DD-MM-YY'),NULL,'1295 WINDY AVENUE', 'KALMAZOO', 'MI', '49002'); INSERT INTO ORDERS VALUES (1013,1014,TO_DATE('03-04-05','DD-MM-YY'),TO_DATE('04-04-05','DD-MM-YY'),'7618 MOUNTAIN RD.', 'CODY', 'WY', '82414'); INSERT INTO ORDERS VALUES (1014,1007,TO_DATE('04-04-05','DD-MM-YY'),TO_DATE('05-04-05','DD-MM-YY'), '9153 MAIN STREET', 'AUSTIN', 'TX', '78710'); INSERT INTO ORDERS VALUES (1015,1020,TO_DATE('04-04-05','DD-MM-YY'),NULL,'557 GLITTER ST.', 'TRENTON', 'NJ', '08606'); INSERT INTO ORDERS VALUES (1016,1003,TO_DATE('04-04-05','DD-MM-YY'),NULL,'9901 SEMINOLE WAY', 'TALLAHASSEE', 'FL', '32307'); INSERT INTO ORDERS VALUES (1017,1015,TO_DATE('04-04-05','DD-MM-YY'),TO_DATE('05-04-05','DD-MM-YY'),'887 HOT ASPHALT ST', 'MIAMI', 'FL', '33112'); INSERT INTO ORDERS VALUES (1018,1001,TO_DATE('05-04-05','DD-MM-YY'),NULL,'95812 HIGHWAY 98', 'EASTPOINT', 'FL', '32328'); INSERT INTO ORDERS VALUES (1019,1018,TO_DATE('05-04-05','DD-MM-YY'),NULL, '1008 GRAND AVENUE', 'MACON', 'GA', '31206'); INSERT INTO ORDERS VALUES (1020,1008,TO_DATE('05-04-05','DD-MM-YY'),NULL,'195 JAMISON LANE', 'CHEYENNE', 'WY', '82003'); INSERT INTO ORDERS VALUES (1021,1008,TO_DATE('05-05-05','DD-MM-YY'),NULL,'195 JAMISON LANE', 'CHEYENNE', 'WY', '82003'); INSERT INTO ORDERS VALUES (1022,1008,TO_DATE('20-05-05','DD-MM-YY'),NULL,'195 JAMISON LANE', 'CHEYENNE', 'WY', '82003'); INSERT INTO ORDERS VALUES (1023,1010,TO_DATE('31-05-05', 'DD-MM-YY'),TO_DATE('01-06-05','DD-MM-YY'), '114 EAST SAVANNAH', 'ATLANTA', 'GA', '30314'); INSERT INTO ORDERS VALUES (1024,1008,TO_DATE('31-03-05','DD-MM-YY'), TO_DATE('01-04-05','DD-MM-YY'),'58 TILA CIRCLE', 'CHICAGO', 'IL', '60605'); INSERT INTO ORDERS VALUES (1025,1001,TO_DATE('01-05-05','DD-MM-YY'),TO_DATE('15-05-05','DD-MM-YY'),'958 MAGNOLIA LANE', 'EASTPOINT', 'FL', '32328'); INSERT INTO ORDERS VALUES (1026,1010,TO_DATE('01-05-05','DD-MM-YY'),TO_DATE('25-05-05','DD-MM-YY'),'561 ROUNDABOUT WAY', 'TRENTON', 'NJ', '08601'); INSERT INTO ORDERS VALUES (1027,1018,TO_DATE('01-06-05','DD-MM-YY'),TO_DATE('06-06-05','DD-MM-YY'), '1008 GRAND AVENUE', 'MACON', 'GA', '31206'); INSERT INTO ORDERS VALUES (1028,1006,TO_DATE('01-06-05','DD-MM-YY'),TO_DATE('07-06-05','DD-MM-YY'),'558A CAPITOL HWY.', 'TALLAHASSEE', 'FL', '32307'); INSERT INTO ORDERS VALUES (1029,1007,TO_DATE('05-06-05','DD-MM-YY'),TO_DATE('09-06-05','DD-MM-YY'),'561 ROUNDABOUT WAY', 'TRENTON', 'NJ', '08601'); INSERT INTO ORDERS VALUES (1030,1018,TO_DATE('10-06-05','DD-MM-YY'),TO_DATE('20-06-05','DD-MM-YY'), '1008 GRAND AVENUE', 'MACON', 'GA', '31206'); INSERT INTO PUBLISHER VALUES(1,'PRINTING IS US','TOMMIE SEYMOUR','000-714-8321'); INSERT INTO PUBLISHER VALUES(2,'PUBLISH OUR WAY','JANE TOMLIN','010-410-0010'); INSERT INTO PUBLISHER VALUES(3,'AMERICAN PUBLISHING','DAVID DAVIDSON','800-555-1211'); INSERT INTO PUBLISHER VALUES(4,'READING MATERIALS INC.','RENEE SMITH','800-555-9743'); INSERT INTO PUBLISHER VALUES(5,'REED-N-RITE','SEBASTIAN JONES','800-555-8284'); INSERT INTO AUTHOR VALUES ('S100','SMITH', 'SAM'); INSERT INTO AUTHOR VALUES ('J100','JONES','JANICE'); INSERT INTO AUTHOR VALUES ('A100','AUSTIN','JAMES'); INSERT INTO AUTHOR VALUES ('M100','MARTINEZ','SHEILA'); INSERT INTO AUTHOR VALUES ('K100','KZOCHSKY','TAMARA'); INSERT INTO AUTHOR VALUES ('P100','PORTER','LISA'); INSERT INTO AUTHOR VALUES ('A105','ADAMS','JUAN'); INSERT INTO AUTHOR VALUES ('B100','BAKER','JACK'); INSERT INTO AUTHOR VALUES ('P105','PETERSON','TINA'); INSERT INTO AUTHOR VALUES ('W100','WHITE','WILLIAM'); INSERT INTO AUTHOR VALUES ('W105','WHITE','LISA'); INSERT INTO AUTHOR VALUES ('R100','ROBINSON','ROBERT'); INSERT INTO AUTHOR VALUES ('F100','FIELDS','OSCAR'); INSERT INTO AUTHOR VALUES ('W110','WILKINSON','ANTHONY'); INSERT INTO BOOKS VALUES ('1059831198','BODYBUILD IN 10 MINUTES A DAY',TO_DATE('21-01-01','DD-MM-YY'),4,18.75,30.95, 'FITNESS'); INSERT INTO BOOKS VALUES ('0401140733','REVENGE OF MICKEY',TO_DATE('14-12-01','DD-MM-YY'),1,14.20,22.00, 'FAMILY LIFE'); INSERT INTO BOOKS VALUES ('4981341710','BUILDING A CAR WITH TOOTHPICKS',TO_DATE('18-03-02','DD-MM-YY'),2,37.80,59.95, 'CHILDREN'); INSERT INTO BOOKS VALUES ('8843172113','DATABASE IMPLEMENTATION',TO_DATE('04-06-99','DD-MM-YY'),3,31.40,55.95, 'COMPUTER'); INSERT INTO BOOKS VALUES ('3437212490','COOKING WITH MUSHROOMS',TO_DATE('28-02-00','DD-MM-YY'),4,12.50,19.95, 'COOKING'); INSERT INTO BOOKS VALUES ('3957136468','HOLY GRAIL OF ORACLE',TO_DATE('31-12-01','DD-MM-YY'),3,47.25,75.95, 'COMPUTER'); INSERT INTO BOOKS VALUES ('1915762492','HANDCRANKED COMPUTERS',TO_DATE('21-01-01','DD-MM-YY'),3,21.80,25.00, 'COMPUTER'); INSERT INTO BOOKS VALUES ('9959789321','E-BUSINESS THE EASY WAY',TO_DATE('01-03-02','DD-MM-YY'),2,37.90,54.50, 'COMPUTER'); INSERT INTO BOOKS VALUES ('2491748320','PAINLESS CHILD-REARING',TO_DATE('17-07-00','DD-MM-YY'),5,48.00,89.95, 'FAMILY LIFE'); INSERT INTO BOOKS VALUES ('0299282519','THE WOK WAY TO COOK',TO_DATE('11-09-00','DD-MM-YY'), 4,19.00,28.75, 'COOKING'); INSERT INTO BOOKS VALUES ('8117949391','BIG BEAR AND LITTLE DOVE',TO_DATE('08-11-01','DD-MM-YY'),5,5.32,8.95, 'CHILDREN'); INSERT INTO BOOKS VALUES ('0132149871','HOW TO GET FASTER PIZZA',TO_DATE('11-11-02','DD-MM-YY'),4,17.85,29.95, 'SELF HELP'); INSERT INTO BOOKS VALUES ('9247381001','HOW TO MANAGE THE MANAGER',TO_DATE('09-05-99','DD-MM-YY'),1,15.40,31.95, 'BUSINESS'); INSERT INTO BOOKS VALUES ('2147428890','SHORTEST POEMS',TO_DATE('01-05-01','DD-MM-YY'),5,21.85,39.95, 'LITERATURE'); INSERT INTO ORDERITEMS VALUES (1000,1,'3437212490',1); INSERT INTO ORDERITEMS VALUES (1001,1,'9247381001',1); INSERT INTO ORDERITEMS VALUES (1001,2,'2491748320',1); INSERT INTO ORDERITEMS VALUES (1002,1,'8843172113',2); INSERT INTO ORDERITEMS VALUES (1003,1,'8843172113',1); INSERT INTO ORDERITEMS VALUES (1003,2,'1059831198',1); INSERT INTO ORDERITEMS VALUES (1003,3,'3437212490',1); INSERT INTO ORDERITEMS VALUES (1004,1,'2491748320',2); INSERT INTO ORDERITEMS VALUES (1005,1,'2147428890',1); INSERT INTO ORDERITEMS VALUES (1006,1,'9959789321',1); INSERT INTO ORDERITEMS VALUES (1007,1,'3957136468',3); INSERT INTO ORDERITEMS VALUES (1007,2,'9959789321',1); INSERT INTO ORDERITEMS VALUES (1007,3,'8117949391',1); INSERT INTO ORDERITEMS VALUES (1007,4,'8843172113',1); INSERT INTO ORDERITEMS VALUES (1008,1,'3437212490',2); INSERT INTO ORDERITEMS VALUES (1009,1,'3437212490',1); INSERT INTO ORDERITEMS VALUES (1009,2,'0401140733',1); INSERT INTO ORDERITEMS VALUES (1010,1,'8843172113',1); INSERT INTO ORDERITEMS VALUES (1011,1,'2491748320',1); INSERT INTO ORDERITEMS VALUES (1012,1,'8117949391',1); INSERT INTO ORDERITEMS VALUES (1012,2,'1915762492',2); INSERT INTO ORDERITEMS VALUES (1012,3,'2491748320',1); INSERT INTO ORDERITEMS VALUES (1012,4,'0401140733',1); INSERT INTO ORDERITEMS VALUES (1013,1,'8843172113',1); INSERT INTO ORDERITEMS VALUES (1014,1,'0401140733',2); INSERT INTO ORDERITEMS VALUES (1015,1,'3437212490',1); INSERT INTO ORDERITEMS VALUES (1016,1,'2491748320',1); INSERT INTO ORDERITEMS VALUES (1017,1,'8117949391',2); INSERT INTO ORDERITEMS VALUES (1018,1,'3437212490',1); INSERT INTO ORDERITEMS VALUES (1018,2,'8843172113',1); INSERT INTO ORDERITEMS VALUES (1019,1,'0401140733',1); INSERT INTO ORDERITEMS VALUES (1020,1,'3437212490',1); INSERT INTO ORDERITEMS VALUES (1021,1,'3437212490',1); INSERT INTO ORDERITEMS VALUES (1022,1,'9247381001',1); INSERT INTO ORDERITEMS VALUES (1022,2,'2491748320',1); INSERT INTO ORDERITEMS VALUES (1023,1,'8843172113',1); INSERT INTO ORDERITEMS VALUES (1023,2,'1059831198',1); INSERT INTO ORDERITEMS VALUES (1023,3,'3437212490',1); INSERT INTO ORDERITEMS VALUES (1024,1,'2491748320',2); INSERT INTO ORDERITEMS VALUES (1025,1,'2147428890',1); INSERT INTO ORDERITEMS VALUES (1026,1,'9959789321',1); INSERT INTO ORDERITEMS VALUES (1027,1,'3957136468',3); INSERT INTO ORDERITEMS VALUES (1027,2,'9959789321',1); INSERT INTO ORDERITEMS VALUES (1027,3,'8117949391',1); INSERT INTO ORDERITEMS VALUES (1027,4,'8843172113',1); INSERT INTO ORDERITEMS VALUES (1028,1,'3437212490',2); INSERT INTO ORDERITEMS VALUES (1029,1,'3437212490',1); INSERT INTO ORDERITEMS VALUES (1029,2,'0401140733',1); INSERT INTO ORDERITEMS VALUES (1030,1,'8843172113',1); INSERT INTO BOOKAUTHOR VALUES ('1059831198','S100'); INSERT INTO BOOKAUTHOR VALUES ('1059831198','P100'); INSERT INTO BOOKAUTHOR VALUES ('0401140733','J100'); INSERT INTO BOOKAUTHOR VALUES ('4981341710','K100'); INSERT INTO BOOKAUTHOR VALUES ('8843172113','P105'); INSERT INTO BOOKAUTHOR VALUES ('8843172113','A100'); INSERT INTO BOOKAUTHOR VALUES ('8843172113','A105'); INSERT INTO BOOKAUTHOR VALUES ('3437212490','B100'); INSERT INTO BOOKAUTHOR VALUES ('3957136468','A100'); INSERT INTO BOOKAUTHOR VALUES ('1915762492','W100'); INSERT INTO BOOKAUTHOR VALUES ('1915762492','W105'); INSERT INTO BOOKAUTHOR VALUES ('9959789321','J100'); INSERT INTO BOOKAUTHOR VALUES ('2491748320','R100'); INSERT INTO BOOKAUTHOR VALUES ('2491748320','F100'); INSERT INTO BOOKAUTHOR VALUES ('2491748320','B100'); INSERT INTO BOOKAUTHOR VALUES ('0299282519','S100'); INSERT INTO BOOKAUTHOR VALUES ('8117949391','R100'); INSERT INTO BOOKAUTHOR VALUES ('0132149871','S100'); INSERT INTO BOOKAUTHOR VALUES ('9247381001','W100'); INSERT INTO BOOKAUTHOR VALUES ('2147428890','W105'); insert into promotion values ('BOOKMARKER', 0, 12); insert into promotion values ('BOOK LABELS', 12.01, 25); insert into promotion values ('BOOK COVER', 25.01, 56); insert into promotion values ('FREE SHIPPING', 56.01, 999.99); Commit;