SQL Practice: Henry Books Case

1. SQL Practice:Henry Books Case Yong Choi BPA CSUB

2. SQL 1 Q 1 SELECT BookCode, Title FROM Book; Q 2 SELECT * FROM Publisher; Q 3 SELECT PublisherName FROM Publisher WHERE City='New York';

3. SQL 2 Q 4 SELECT PublisherName FROM Publisher WHERE NOT City='New York'; Q 5 SELECT BranchName FROM Branch WHERE NumEmployees >= 10; Q 6 SELECT BookCode, Title FROM Book WHERE Type='HOR';

4. SQL 3 Q 7 SELECT BookCode, Title FROM Book WHERE Type='HOR‘ AND Paperback; Q 8 SELECT BookCode, Title FROM Book WHERE Type='HOR‘ OR PublisherCode='PE'; Q 9 SELECT BookCode, Title, Price FROM Book WHERE Price BETWEEN 10 and 20;

5. SQL 4 Q 10 SELECT BookCode, Title FROM Book WHERE Type='MYS‘ AND Price<20; Q 11 SELECT BookCode, Title, Price*0.85 AS DiscountedPrice FROM Book; Q 12 SELECT BookCode, Title FROM Book WHERE Type IN ('FIC', 'MYS', 'ART');

6. SQL 5 Q 13 SELECT BookCode, Title, PublisherCode FROM Book ORDER BY PublisherCode, Title; Q 14 SELECT COUNT(*) FROM BOOK WHERE Type='MYS'; Q 15 SELECT AVG(Price), Type FROM Book GROUP BY Type;

7. SQL 6 Q 16 SELECT BookCode, Title, Book.PublisherCode, PublisherName FROM Book, Publisher WHERE Book.PublisherCode=Publisher.PublisherCode; Q 17 SELECT Title, Price FROM Book, Publisher WHERE Book.PublisherCode=Publisher.PublisherCode AND PublisherName='Lb Books'; Q 18 SELECT Title, BookCode FROM Book, Publisher WHERE Book.PublisherCode=Publisher.PublisherCode AND PublisherName='Scribner‘ AND Price>10;

8. SQL 7 Q 19 SELECT BookCode, Title, PublisherCode, Price INTO Fiction FROM Book WHERE Type='FIC'; Q 20 UPDATE Fiction SET Price = 8.5 WHERE Price=8; Q 21 DELETE * FROM Fiction WHERE PublisherCode='PE';