Question

are Each question is worth 10 points. apply 1. List the book code and book title of each book that has the type HOR 2. List the book code and book title of each book that has the type HOR or is published by the publisher with the publisher code SC 3. List the book code and book title of each book that has the type MYS and a price of less than $20 4. List the book code, book title, price and increase the price of each book by 20% Label the calculated f INCREASED PRICE 5. List the books whose type code is FIC, SFI or HOR Write the code using IN and then write the code without the IN (must have both to get full credit for this question). 6. List all the books whose Publisher code is NOT JP 7. List all the books that cost greater than or equal to $10 or less than or equal to $20. Write the code using BETWEEN and them write the code without the BETWEEN (mast have both to get full credit for this question). 8. List the book title of any book that has the word and' anywhere in their name. 9. List the publisher code, publisher name and city. Sort the output by publisher code (a to z order) within city (z to a order) 10. List the book title, publisher code, type for all paperback books. Sort the output by publisher code (a to z order) BRANCH BRANCH NUM BRANCH NAME BRANCH_LOCATION NUM EMPLOYEES 1 Henry Downtown 16 Riverview 10 2 Henry On The Hil 1289 Bedford 3 Henry Brentwood Brentwood Mall 4 Henry Eastshore 15 Eastshore Mall PUBLISHER PUBLISHER CODE PUBLISHER NAME CITY AH Sauk City W Arkham lHosne New York Arcade Publishing AP Rasic Rooks Boulder CO BA Berkley Publishing BE Boston New York BY Hack Ray Books CT Counse Technology Boston Fawoett Books New York FA New York Farrar Straus and Garous New York HC HarperCollins Publishers New York Jove Publications JP Los Angelos Jeremy P Tarcher New York Lb Books LB Kingston Melhorscn and Co MP Penguin USA New York PE New York Pome New Tork Putnam Publishing Group- New York Random Houses RH New York SIL Schoken Booko Seribner New York Sc Simon and Sohuster New Vork Scholastic Trale New York NT Newtown CT Tauntn Pro TM Tr Bks New York TH New York Thames and Hudo TH Westpot T Touchtone Bock TO New Vorks CST1204 D10 Su-pa CST1204 Summerdoc Henry Books Tables.odf ea1 204 Summer w AUTHOR AUTHOR_NUM AUTHOR_LASTAUTHOR_FIRST 1 Morrison Toni 2 Solotaroff Paul 3 Vintage Vernor 4 Francis Dick 5 Straub Peter Stephen Philip 6 King 7 Pratt Truddi 8 Chase 9 Collins Bradley Joseph 10 Heller 11 Wills Gary 12 Hofstadter Douglas R 13 Lee Нагреr 14 Ambrose Stephen E 15 Rowling J.K 16 Salinger J.D. 17 Heaney Seamus 18 Camus Albert 19 Collins, Jr Bradley CST1204 Summerdoc CST1204 0910 Su pdf Henry Books Tables pdf CSTI204 Summer /Downloads/Henry%20Books%20Tables %20(1) pdf 18 Camus Albert Bradley 19 Collins, Jr 20 Steinbeck John 21 Castelman Riva 22 Owen Barbara 23 O'Rourke Randy 24 Kidder Tracy 25 Schleining Lon BOOK BOOK CODE TTLE PUBLISHER TYPE PRICE PAPERBACK CODE T ST $7.19 $7.99Y $8.00 0150 A Deepess in the Sky Mage Terror 0189 FA BOR 0200 The Stranger VB FIC 0378 Venice $24 50 N ART Sepond Wind $24.95 N 079x MYS The Edge S699 Y MYS Dreamoatchen A Nvel HOR $19.60 N 1351 1382 Treune Cots ART $24 46 N Belowod $12.95 Y 1383 FIC 2226 Harry Potter and the Priscncer od Aakaban ST SE1 $1396N ART $21 00 N 2281 Van Gogh and Gpin $6.95Y 2766 Of Mioe and Mon FIC Henry Books Tables pdf CST1204 Summerdoc CST1204 D910 Su-pa cu204 Summe CST/Downloads/Henry % 20Books %20Tables% 20( 1) pdf Van Gogh and Gag 2281 WP $21.00 N ART 2766 Of Mice and Men PE PIC $6.95 Y 2908 Electrie Light PS POE $14.00 N 3350 Group: Stx People in Search of a BP Life PSY $10.40 Y Nine Stories 3743 LB FIC $5.99 Y 3906 The Soul of a New Machine BY SCI $11.16 Y 5163 Travels with Charley PE TRA $7.95 Y Catch-22 5790 $12.00 Y FIC 6128 Jaza FIC $12.95 Y 6328 Rand of Brothers TO HIS $960 Y 669x A Guide to SQ CMP $37.95 Y CT Franny and Zooey 6908 LB FIC $5.99 Y 7405 East of Eden FIC $12.95 Y PE 7443 llarry Potter and the Goblet of Pire SFT $18.16 N ST 7559 The Fall FIC $8.00 Y VB 8092 Godel, Escher, Rach BA $14.00 Y PHI 8720 When Ralbet Bowls JP PSY S6.29 Y Black House 9611 BOR $1881 N 9627 Song of Solomon The Grapes of Wrath PL. $1400 Y FIC 9701 FIC PE $13.00 T 9882 $6.99 Y Slay Ride MYS JP 9883 The Catcher in the Rye FIC $5.99 Y 9931 IHC To Kill Mockindind $1800 N FIC WROTE INVENTORY BOOK CODE AUTHOR NUM SEQUENCE BOOK CODE ANCH NUM ON HANDE o1s0 osso Henry Bocks Tables po cST1204 Summer dee CST1204 D910 Su par Do euhor Summer. w WROTE INVENTORY BOOK CODE AUTHOR NUM SEQUENCE BOOK CODE BRANCH NUM ON HAND 0180 0180 2 0189 1 0189 2 0200 18 0200 1 e078 1 0200 2 3 079x 1 0378 2 079x 1 1351 6 1 079x 1382 23 2 079x 1382 25 1 2 138x 1351 2226 15 1351 2281 1382 2 2281 19 1383 20 2766 2226 2008 2226 1 1 2226 3350 4 1 16 3743 2281 1 CST1204 0930 Su pa cSTL204 Summer...doc Decum Henry Books Tablespa CuP04 Summer WROTE INVENTORY BOOK CODE AUTHOR NUM SEQUENCE B0OK CODE BRANCH NUM ON HAND 3906 24 2766 2 5163 20 2908 5790 2908 6128 3350 6328 14 1 3743 2 669x 7 1 3906 2 6908 3906 1 2 7405 20 5163 7443 15 1 5790 4 2 7559 18 1 6128 4 s092 13 3 1 6128 6328 8720 669x 9611 2 6008 25 1 9611 7405 9627 1 9701 7443 2 9882 1 2559 2 4 1 s092 9583 16 S720 9931 13 1 9611 9627 9627 4 2 9701 2 1 9701 9708 3 2 970 3 2 2 9931 Document CST1204 Summerdo CST1204 D91o Su par Henry Books Tables p Can204 Summer

Answer 1

SQL Queries

1. List book code, book title whose type is HOR

SELECT book_code, title FROM Book WHERE type='HOR';

2. List book code, book title whose type is HOR or with publisher code SC

SELECT book_code, title FROM Book WHERE type='HOR' OR publisher_code = 'SC';

3. List book code, book title whose type is MYS and price < 20

SELECT book_code, title FROM Book WHERE type='MYS' AND price < 20;

4. List book code, book title, price and increase the price by 20%.

=> Alter the table and add a column which will store the updated price.

ALTER TABLE Book ADD COLUMN Increased_price float(5,2);

UPDATE Book SET Increased_price = price + price*0.2;

SELECT book_code, title, price, Increased_price FROM Book

5. List books whose type is FIC, SFI or HOR

SELECT * FROM Book WHERE type IN ( 'FIC', 'SFI', 'HOR'); ----- using IN

SELECT * FROM Book WHERE type = 'FIC' or type = 'SFI' or type = 'HOR'); ----- without IN

6. List books whose publisher code not JP

SELECT * FROM Book WHERE publisher_code NOT IN ('JP');

7. List books whose price greater than or equal to 10, lesser than or equal to 20

SELECT * FROM Book WHERE price BETWEEN 10 AND 20; -------------- using BETWEEN

SELECT * FROM Book WHERE (price = 10 OR price > 10) AND (price = 20 OR price < 10); ---- without BETWEEN

8. List book title which has 'and'

SELECT title FROM Book WHERE title LIKE '%and%';

9. List publisher code, name, city and sort them by publisher code within city

SELECT * FROM Publisher GROUP BY City ORDER BY Publisher_code;

10. List book title, publisher code, type of paperback book and sort them on publisher code

SELECT title, publisher_code, type FROM Book WHERE paperback='Y' ORDER BY Publisher_code;