write the SQL statement and run against the tables in the book database

/*** WITH ALTER ***/ ------------------------------------------------ -- Drop all the schema. ----------------------------------------------- CREATE OR REPLACE FUNCTION DROP_ALL_SCHEMA_OBJECTS RETURN NUMBER AS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN FOR OBJECT_REC IN (SELECT OBJECT_TYPE,'"'||OBJECT_NAME||'"'||DECODE(OBJECT_TYPE,'TABLE' ,' CASCADE CONSTRAINTS',NULL) OBJ_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE IN ('TABLE','VIEW','PACKAGE','SEQUENCE','SYNONYM', 'MATERIALIZED VIEW') ORDER BY OBJECT_TYPE) LOOP EXECUTE IMMEDIATE ('DROP '||OBJECT_REC.OBJECT_TYPE||' ' ||OBJECT_REC.OBJ_NAME); END LOOP; FOR OBJECT_REC IN (SELECT OBJECT_TYPE, '"'||OBJECT_NAME||'"' OBJ_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE NOT IN ('TYPE') AND OBJECT_NAME'DROP_ALL_SCHEMA_OBJECTS') LOOP EXECUTE IMMEDIATE ('DROP '||OBJECT_REC.OBJECT_TYPE||' ' ||OBJECT_REC.OBJ_NAME); END LOOP; FOR OBJECT_REC IN (SELECT OBJECT_TYPE, '"'||OBJECT_NAME||'"' OBJ_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE IN ('TYPE')) LOOP EXECUTE IMMEDIATE ('DROP '||OBJECT_REC.OBJECT_TYPE||' ' ||OBJECT_REC.OBJ_NAME || ' FORCE'); END LOOP; RETURN 0; END DROP_ALL_SCHEMA_OBJECTS; / SELECT DROP_ALL_SCHEMA_OBJECTS FROM DUAL; CREATE TABLE AUTHOR (AUTHOR_NUM DECIMAL(2,0) PRIMARY KEY, AUTHOR_LAST CHAR(12), AUTHOR_FIRST CHAR(10) ); CREATE TABLE PUBLISHER (PUBLISHER_CODE CHAR(3) PRIMARY KEY, PUBLISHER_NAME CHAR(25), CITY CHAR(20) ); 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 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 INVENTORY (BOOK_CODE CHAR(4) NOT NULL, BRANCH_NUM DECIMAL(2,0) NOT NULL, ON_HAND DECIMAL(2,0), PRIMARY KEY (BOOK_CODE, BRANCH_NUM)); CREATE TABLE WROTE (BOOK_CODE CHAR(4), AUTHOR_NUM DECIMAL(2,0) NOT NULL, SEQUENCE DECIMAL(1,0), PRIMARY KEY (BOOK_CODE, AUTHOR_NUM)); ----------------------------------------------------------------------- --Refrential Integrity -- Alter tables after inputing the values alter table BOOK add constraint BOOK_PUBLISHER_CODE_FK foreign key (PUBLISHER_CODE) references PUBLISHER (PUBLISHER_CODE); alter table WROTE add constraint WROTE_AUTHOR_NUM_FK foreign key (AUTHOR_NUM) references AUTHOR (AUTHOR_NUM); alter table WROTE add constraint WROTE_BOOK_CODE_FK foreign key (BOOK_CODE) references BOOK (BOOK_CODE); alter table INVENTORY add constraint INVENTORY_BRANCH_NUM_FK foreign key (BRANCH_NUM) references BRANCH (BRANCH_NUM); alter table INVENTORY add constraint INVENTORY_BOOK_CODE_FK foreign key (BOOK_CODE) references BOOK (BOOK_CODE); --Author table 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'); -- Publisher 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'); --Branch 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); -- Book 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'); --Inventory 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); -- --Wrote 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); 

Query 16: By wsing JOIN, list each author's last name, and namber of books they've written. Query 17: By using JOIN, list the book code and book titie for each book located in branch number 2 and written by author number 20 . Query 18: By using JOIN, list the author's number, last name, and number of hocks they've written. Query 19: By using Left Outer Join, list all book code awaialable in branch 3 as wells book codes that are not avaliable in branch 3. Query 20: List all book codes with more than one autohors