Drop table author;
drop table branch;
drop table wrote;
drop table inventory;
drop table publisher;
drop table author;

CREATE TABLE AUTHOR 
(AUTHOR_NUM DECIMAL(2,0) PRIMARY KEY,
AUTHOR_LAST CHAR(12),
AUTHOR_FIRST CHAR(10) );
CREATE TABLE BOOK 
(BOOK_CODE CHAR(4) PRIMARY KEY,
TITLE CHAR(40),
PUBLISHER_CODE CHAR(3),
TYPE CHAR(3),
PRICE DECIMAL(4,2),
PAPERBACK CHAR(1) );
CREATE TABLE BRANCH 
(BRANCH_NUM DECIMAL(2,0) PRIMARY KEY,
BRANCH_NAME CHAR(50),
BRANCH_LOCATION CHAR(50),
NUM_EMPLOYEES DECIMAL(2,0) );
CREATE TABLE INVENTORY 
(BOOK_CODE CHAR(4),
BRANCH_NUM DECIMAL(2,0) NOT NULL,
ON_HAND DECIMAL(2,0),
PRIMARY KEY (BOOK_CODE, BRANCH_NUM) );
CREATE TABLE PUBLISHER 
(PUBLISHER_CODE CHAR(3) PRIMARY KEY,
PUBLISHER_NAME CHAR(25),
CITY CHAR(20) );
CREATE TABLE WROTE 
(BOOK_CODE CHAR(4),
AUTHOR_NUM DECIMAL(2,0) NOT NULL,
SEQUENCE DECIMAL(1,0),
PRIMARY KEY (BOOK_CODE, AUTHOR_NUM) );
INSERT INTO AUTHOR
VALUES
(1,'Morrison','Toni');
INSERT INTO AUTHOR
VALUES
(2,'Solotaroff','Paul');
INSERT INTO AUTHOR
VALUES
(3,'Vintage','Vernor');
INSERT INTO AUTHOR
VALUES
(4,'Francis','Dick');
INSERT INTO AUTHOR
VALUES
(5,'Straub','Peter');
INSERT INTO AUTHOR
VALUES
(6,'King','Stephen');
INSERT INTO AUTHOR
VALUES
(7,'Pratt','Philip');
INSERT INTO AUTHOR
VALUES
(8,'Chase','Truddi');
INSERT INTO AUTHOR
VALUES
(9,'Collins','Bradley');
INSERT INTO AUTHOR
VALUES
(10,'Heller','Joseph');
INSERT INTO AUTHOR
VALUES
(11,'Wills','Gary');
INSERT INTO AUTHOR
VALUES
(12,'Hofstadter','Douglas R.');
INSERT INTO AUTHOR
VALUES
(13,'Lee','Harper');
INSERT INTO AUTHOR
VALUES
(14,'Ambrose','Stephen E.');
INSERT INTO AUTHOR
VALUES
(15,'Rowling','J.K.');
INSERT INTO AUTHOR
VALUES
(16,'Salinger','J.D.');
INSERT INTO AUTHOR
VALUES
(17,'Heaney','Seamus');
INSERT INTO AUTHOR
VALUES
(18,'Camus','Albert');
INSERT INTO AUTHOR
VALUES
(19,'Collins, Jr.','Bradley');
INSERT INTO AUTHOR
VALUES
(20,'Steinbeck','John');
INSERT INTO AUTHOR
VALUES
(21,'Castelman','Riva');
INSERT INTO AUTHOR
VALUES
(22,'Owen','Barbara');
INSERT INTO AUTHOR
VALUES
(23,'O''Rourke','Randy');
INSERT INTO AUTHOR
VALUES
(24,'Kidder','Tracy');
INSERT INTO AUTHOR
VALUES
(25,'Schleining','Lon');
INSERT INTO BOOK
VALUES
('0180','A Deepness in the Sky','TB','SFI',7.19,'Y');
INSERT INTO BOOK
VALUES
('0189','Magic Terror','FA','HOR',7.99,'Y');
INSERT INTO BOOK
VALUES
('0200','The Stranger','VB','FIC',8.00,'Y');
INSERT INTO BOOK
VALUES
('0378','Venice','SS','ART',24.50,'N');
INSERT INTO BOOK
VALUES
('079X','Second Wind','PU','MYS',24.95,'N');
INSERT INTO BOOK
VALUES
('0808','The Edge','JP','MYS',6.99,'Y');
INSERT INTO BOOK
VALUES
('1351','Dreamcatcher: A Novel','SC','HOR',19.60,'N');
INSERT INTO BOOK
VALUES
('1382','Treasure Chests','TA','ART',24.46,'N');
INSERT INTO BOOK
VALUES
('138X','Beloved','PL','FIC',12.95,'Y');
INSERT INTO BOOK
VALUES
('2226','Harry Potter and the Prisoner of Azkaban','ST','SFI',13.96,'N');
INSERT INTO BOOK
VALUES
('2281','Van Gogh and Gauguin','WP','ART',21.00,'N');
INSERT INTO BOOK
VALUES
('2766','Of Mice and Men','PE','FIC',6.95,'Y');
INSERT INTO BOOK
VALUES
('2908','Electric Light','FS','POE',14.00,'N');
INSERT INTO BOOK
VALUES
('3350','Group: Six People in Search of a Life','BP','PSY',10.40,'Y');
INSERT INTO BOOK
VALUES
('3743','Nine Stories','LB','FIC',5.99,'Y');
INSERT INTO BOOK
VALUES
('3906','The Soul of a New Machine','BY','SCI',11.16,'Y');
INSERT INTO BOOK
VALUES
('5163','Travels with Charley','PE','TRA',7.95,'Y');
INSERT INTO BOOK
VALUES
('5790','Catch-22','SC','FIC',12.00,'Y');
INSERT INTO BOOK
VALUES
('6128','Jazz','PL','FIC',12.95,'Y');
INSERT INTO BOOK
VALUES
('6328','Band of Brothers','TO','HIS',9.60,'Y');
INSERT INTO BOOK
VALUES
('669X','A Guide to SQL','CT','CMP',37.95,'Y');
INSERT INTO BOOK
VALUES
('6908','Franny and Zooey','LB','FIC',5.99,'Y');
INSERT INTO BOOK
VALUES
('7405','East of Eden','PE','FIC',12.95,'Y');
INSERT INTO BOOK
VALUES
('7443','Harry Potter and the Goblet of Fire','ST','SFI',18.16,'N');
INSERT INTO BOOK
VALUES
('7559','The Fall','VB','FIC',8.00,'Y');
INSERT INTO BOOK
VALUES
('8092','Godel, Escher, Bach','BA','PHI',14.00,'Y');
INSERT INTO BOOK
VALUES
('8720','When Rabbit Howls','JP','PSY',6.29,'Y');
INSERT INTO BOOK
VALUES
('9611','Black House','RH','HOR',18.81,'N');
INSERT INTO BOOK
VALUES
('9627','Song of Solomon','PL','FIC',14.00,'Y');
INSERT INTO BOOK
VALUES
('9701','The Grapes of Wrath','PE','FIC',13.00,'Y');
INSERT INTO BOOK
VALUES
('9882','Slay Ride','JP','MYS',6.99,'Y');
INSERT INTO BOOK
VALUES
('9883','The Catcher in the Rye','LB','FIC',5.99,'Y');
INSERT INTO BOOK
VALUES
('9931','To Kill a Mockingbird','HC','FIC',18.00,'N');
INSERT INTO BRANCH
VALUES
(1,'Henry Downtown','16 Riverview',10);
INSERT INTO BRANCH
VALUES
(2,'Henry On The Hill','1289 Bedford',6);
INSERT INTO BRANCH
VALUES
(3,'Henry Brentwood','Brentwood Mall',15);
INSERT INTO BRANCH
VALUES
(4,'Henry Eastshore','Eastshore Mall',9);
INSERT INTO INVENTORY
VALUES
('0180',1,2);
INSERT INTO INVENTORY
VALUES
('0189',2,2);
INSERT INTO INVENTORY
VALUES
('0200',1,1);
INSERT INTO INVENTORY
VALUES
('0200',2,3);
INSERT INTO INVENTORY
VALUES
('0378',3,2);
INSERT INTO INVENTORY
VALUES
('079X',2,1);
INSERT INTO INVENTORY
VALUES
('079X',3,2);
INSERT INTO INVENTORY
VALUES
('079X',4,3);
INSERT INTO INVENTORY
VALUES
('0808',2,1);
INSERT INTO INVENTORY
VALUES
('1351',2,4);
INSERT INTO INVENTORY
VALUES
('1351',3,2);
INSERT INTO INVENTORY
VALUES
('1382',2,1);
INSERT INTO INVENTORY
VALUES
('138X',2,3);
INSERT INTO INVENTORY
VALUES
('2226',1,3);
INSERT INTO INVENTORY
VALUES
('2226',3,2);
INSERT INTO INVENTORY
VALUES
('2226',4,1);
INSERT INTO INVENTORY
VALUES
('2281',4,3);
INSERT INTO INVENTORY
VALUES
('2766',3,2);
INSERT INTO INVENTORY
VALUES
('2908',1,3);
INSERT INTO INVENTORY
VALUES
('2908',4,1);
INSERT INTO INVENTORY
VALUES
('3350',1,2);
INSERT INTO INVENTORY
VALUES
('3743',2,1);
INSERT INTO INVENTORY
VALUES
('3906',2,1);
INSERT INTO INVENTORY
VALUES
('3906',3,2);
INSERT INTO INVENTORY
VALUES
('5163',1,1);
INSERT INTO INVENTORY
VALUES
('5790',4,2);
INSERT INTO INVENTORY
VALUES
('6128',2,4);
INSERT INTO INVENTORY
VALUES
('6128',3,3);
INSERT INTO INVENTORY
VALUES
('6328',2,2);
INSERT INTO INVENTORY
VALUES
('669X',1,1);
INSERT INTO INVENTORY
VALUES
('6908',2,2);
INSERT INTO INVENTORY
VALUES
('7405',3,2);
INSERT INTO INVENTORY
VALUES
('7443',4,1);
INSERT INTO INVENTORY
VALUES
('7559',2,2);
INSERT INTO INVENTORY
VALUES
('8092',3,1);
INSERT INTO INVENTORY
VALUES
('8720',1,3);
INSERT INTO INVENTORY
VALUES
('9611',1,2);
INSERT INTO INVENTORY
VALUES
('9627',3,5);
INSERT INTO INVENTORY
VALUES
('9627',4,2);
INSERT INTO INVENTORY
VALUES
('9701',1,2);
INSERT INTO INVENTORY
VALUES
('9701',2,1);
INSERT INTO INVENTORY
VALUES
('9701',3,3);
INSERT INTO INVENTORY
VALUES
('9701',4,2);
INSERT INTO INVENTORY
VALUES
('9882',3,3);
INSERT INTO INVENTORY
VALUES
('9883',2,3);
INSERT INTO INVENTORY
VALUES
('9883',4,2);
INSERT INTO INVENTORY
VALUES
('9931',1,2);
INSERT INTO PUBLISHER
VALUES
('AH','Arkham House','Sauk City WI');
INSERT INTO PUBLISHER
VALUES
('AP','Arcade Publishing','New York');
INSERT INTO PUBLISHER
VALUES
('BA','Basic Books','Boulder CO');
INSERT INTO PUBLISHER
VALUES
('BP','Berkley Publishing','Boston');
INSERT INTO PUBLISHER
VALUES
('BY','Back Bay Books','New York');
INSERT INTO PUBLISHER
VALUES
('CT','Course Technology','Boston');
INSERT INTO PUBLISHER
VALUES
('FA','Fawcett Books','New York');
INSERT INTO PUBLISHER
VALUES
('FS','Farrar Straus and Giroux','New York');
INSERT INTO PUBLISHER
VALUES
('HC','HarperCollins Publishers','New York');
INSERT INTO PUBLISHER
VALUES
('JP','Jove Publications','New York');
INSERT INTO PUBLISHER
VALUES
('JT','Jeremy P. Tarcher','Los Angeles');
INSERT INTO PUBLISHER
VALUES
('LB','Lb Books','New York');
INSERT INTO PUBLISHER
VALUES
('MP','McPherson and Co.','Kingston');
INSERT INTO PUBLISHER
VALUES
('PE','Penguin USA','New York');
INSERT INTO PUBLISHER
VALUES
('PL','Plume','New York');
INSERT INTO PUBLISHER
VALUES
('PU','Putnam Publishing Group','New York');
INSERT INTO PUBLISHER
VALUES
('RH','Random House','New York');
INSERT INTO PUBLISHER
VALUES
('SB','Schoken Books','New York');
INSERT INTO PUBLISHER
VALUES
('SC','Scribner','New York');
INSERT INTO PUBLISHER
VALUES
('SS','Simon and Schuster','New York');
INSERT INTO PUBLISHER
VALUES
('ST','Scholastic Trade','New York');
INSERT INTO PUBLISHER
VALUES
('TA','Taunton Press','Newtown CT');
INSERT INTO PUBLISHER
VALUES
('TB','Tor Books','New York');
INSERT INTO PUBLISHER
VALUES
('TH','Thames and Hudson','New York');
INSERT INTO PUBLISHER
VALUES
('TO','Touchstone Books','Westport CT');
INSERT INTO PUBLISHER
VALUES
('VB','Vintage Books','New York');
INSERT INTO PUBLISHER
VALUES
('WN','W.W. Norton','New York');
INSERT INTO PUBLISHER
VALUES
('WP','Westview Press','Boulder CO');
INSERT INTO WROTE
VALUES
('0180',3,1);
INSERT INTO WROTE
VALUES
('0189',5,1);
INSERT INTO WROTE
VALUES
('0200',18,1);
INSERT INTO WROTE
VALUES
('0378',11,1);
INSERT INTO WROTE
VALUES
('079X',4,1);
INSERT INTO WROTE
VALUES
('0808',4,1);
INSERT INTO WROTE
VALUES
('1351',6,1);
INSERT INTO WROTE
VALUES
('1382',23,2);
INSERT INTO WROTE
VALUES
('1382',25,1);
INSERT INTO WROTE
VALUES
('138X',1,1);
INSERT INTO WROTE
VALUES
('2226',15,1);
INSERT INTO WROTE
VALUES
('2281',9,2);
INSERT INTO WROTE
VALUES
('2281',19,1);
INSERT INTO WROTE
VALUES
('2766',20,1);
INSERT INTO WROTE
VALUES
('2908',17,1);
INSERT INTO WROTE
VALUES
('3350',2,1);
INSERT INTO WROTE
VALUES
('3743',16,1);
INSERT INTO WROTE
VALUES
('3906',24,1);
INSERT INTO WROTE
VALUES
('5163',20,1);
INSERT INTO WROTE
VALUES
('5790',10,1);
INSERT INTO WROTE
VALUES
('6128',1,1);
INSERT INTO WROTE
VALUES
('6328',14,1);
INSERT INTO WROTE
VALUES
('669X',7,1);
INSERT INTO WROTE
VALUES
('6908',16,1);
INSERT INTO WROTE
VALUES
('7405',20,1);
INSERT INTO WROTE
VALUES
('7443',15,1);
INSERT INTO WROTE
VALUES
('7559',18,1);
INSERT INTO WROTE
VALUES
('8092',12,1);
INSERT INTO WROTE
VALUES
('8720',8,1);
INSERT INTO WROTE
VALUES
('9611',5,2);
INSERT INTO WROTE
VALUES
('9611',6,1);
INSERT INTO WROTE
VALUES
('9627',1,1);
INSERT INTO WROTE
VALUES
('9701',20,1);
INSERT INTO WROTE
VALUES
('9882',4,1);
INSERT INTO WROTE
VALUES
('9883',16,1);
INSERT INTO WROTE
VALUES
('9931',13,1);