weite the SQL statement (query) and run against the Book database.

Q1. List the book code for books without a title

Q2. List the count of distinct book types.

Q3. List the distinct book type and average book price for that type, further for only books in paperback, grouped by book type. 

/*** 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);