For each book, list the book code, book title, publisher ...



Using only the Tables from the Henry database (that is, you CANNOT use the Henry View), write Select statements that do the following (1 Point Each):

1. For each book, list the book code, book title, publisher code, and publisher name. Order the results by publisher name.

SELECT BOOK_CODE, TITLE, B.PUBLISHER_CODE, PUBLISHER_NAME

FROM BOOK B

JOIN PUBLISHER P on P.PUBLISHER_CODE = B.PUBLISHER_CODE

ORDER BY PUBLISHER_NAME

2. List the book title, book code, and price of each book published by Scribner that has a book price of at least $14.

SELECT TITLE, B.BOOK_CODE, PRICE

FROM BOOK B

JOIN PUBLISHER P ON P.PUBLISHER_CODE = B.PUBLISHER_CODE

WHERE B.PUBLISHER_CODE = 'SC'

AND PRICE >= 14

3. List the book code, book title, and units on hand for each book in branch number 3.

SELECT I.BOOK_CODE, TITLE, ON_HAND

FROM BOOK B

JOIN INVENTORY I ON I.BOOK_CODE = B.BOOK_CODE

WHERE BRANCH_NUM = 3

4. Find the book title for each book written by author number 18. Use the EXISTS operator in your query.

SELECT TITLE

FROM BOOK B

WHERE EXISTS (SELECT *

FROM WROTE W

WHERE B.BOOK_CODE = W.BOOK_CODE

AND W.AUTHOR_NUM = 18)

5. List all author codes and author names that have no books at any Henry Bookstore.

SELECT AUTHOR_NUM, AUTHOR_FIRST, AUTHOR_LAST

FROM AUTHOR

WHERE AUTHOR_NUM NOT IN (

SELECT A.AUTHOR_NUM

FROM AUTHOR A

JOIN WROTE W on A.AUTHOR_NUM = W.AUTHOR_NUM

JOIN BOOK B ON W.BOOK_CODE = B.BOOK_CODE

JOIN INVENTORY I ON B.BOOK_CODE = I.BOOK_CODE)

Some did it this way:

SELECT Author.AUTHOR_NUM, AUTHOR_FIRST, AUTHOR_LAST

FROM AUTHOR LEFT OUTER JOIN WROTE

ON AUTHOR.AUTHOR_NUM = WROTE.AUTHOR_NUM

LEFT OUTER JOIN BOOK ON WROTE.BOOK_CODE = BOOK.BOOK_CODE

LEFT OUTER JOIN INVENTORY ON BOOK.BOOK_CODE = INVENTORY.BOOK_CODE

WHERE ISNULL(INVENTORY.ON_HAND,0) = 0

6. List the book codes for each pair of books that have the same price. (For example, one such pair would be book 0200 and book 7559, because the price of both books is $8.00.) The first book code listed should be the major sort key, and the second book code should be the minor sort key.

SELECT B1.BOOK_CODE, B2.BOOK_CODE

FROM BOOK B1

JOIN BOOK B2 ON B1.PRICE = B2.PRICE

GROUP BY B1.BOOK_CODE, B2.BOOK_CODE

HAVING B1.BOOK_CODE < B2.BOOK_CODE

ORDER BY B1.BOOK_CODE, B2.BOOK_CODE

7. Find the book title, publisher name, branch number and units on hand for each paperback book in branch number 4 that is also in branch number 2.

SELECT TITLE, PUBLISHER_NAME, I1.BRANCH_NUM, I1.ON_HAND, I2.BRANCH_NUM, I2.ON_HAND

FROM BOOK B

JOIN PUBLISHER P ON B.PUBLISHER_CODE = P.PUBLISHER_CODE

JOIN INVENTORY I1 ON B.BOOK_CODE = I1.BOOK_CODE

JOIN INVENTORY I2 ON I1.BOOK_CODE = I2.BOOK_CODE

WHERE I1.BRANCH_NUM = 4

AND PAPERBACK = 'Y'

AND I2.BRANCH_NUM = 2

OR

SELECT TITLE, PUBLISHER_NAME, BRANCH_NUM, ON_HAND

FROM BOOK B

JOIN PUBLISHER P ON B.PUBLISHER_CODE = P.PUBLISHER_CODE

JOIN INVENTORY I ON B.BOOK_CODE = I.BOOK_CODE

WHERE BRANCH_NUM IN (2,4)

AND B.BOOK_CODE IN

(SELECT BOOK.BOOK_CODE FROM BOOK JOIN INVENTORY

ON BOOK.BOOK_CODE = INVENTORY.BOOK_CODE

WHERE INVENTORY.BRANCH_NUM = 4 AND PAPERBACK = 'Y'

INTERSECT

SELECT BOOK.BOOK_CODE FROM BOOK JOIN INVENTORY

ON BOOK.BOOK_CODE = INVENTORY.BOOK_CODE

WHERE INVENTORY.BRANCH_NUM = 2 AND PAPERBACK = 'Y')

OR

SELECT TITLE, PUBLISHER_NAME, BRANCH_NUM, ON_HAND

FROM BOOK B

JOIN PUBLISHER P ON B.PUBLISHER_CODE = P.PUBLISHER_CODE

JOIN INVENTORY I ON B.BOOK_CODE = I.BOOK_CODE

WHERE BRANCH_NUM IN (2,4) AND PAPERBACK = 'Y'

AND B.BOOK_CODE IN

(SELECT BOOK_CODE FROM INVENTORY

WHERE INVENTORY.BRANCH_NUM = 4

AND BOOK_CODE IN

(SELECT BOOK_CODE FROM INVENTORY

WHERE INVENTORY.BRANCH_NUM = 2 ))

8. Find the book code and book title for each book whose price is more than $10 or that was published in Boston.

SELECT BOOK_CODE, TITLE

FROM BOOK B

JOIN PUBLISHER P ON P.PUBLISHER_CODE = B.PUBLISHER_CODE

WHERE B.PRICE > 10 P.CITY = 'BOSTON'

9. Find the book code and book title for each book whose price is more than $10 but that was not published in Boston.

SELECT BOOK_CODE, TITLE

FROM BOOK

WHERE PRICE > 10

EXCEPT

SELECT BOOK_CODE, TITLE

FROM BOOK B

JOIN PUBLISHER P ON B.PUBLISHER_CODE = P.PUBLISHER_CODE

WHERE P.CITY = 'BOSTON'

This also works in this case:

SELECT BOOK_CODE, TITLE

FROM BOOK B

JOIN PUBLISHER P ON B.PUBLISHER_CODE = P.PUBLISHER_CODE

WHERE PRICE > 10 AND P.CITY != 'BOSTON'

10. Find the book code, book title and publisher name for each book whose price is greater than the book price of every book that has the type MYS.

SELECT BOOK_CODE, TITLE, PUBLISHER_NAME

FROM BOOK B

JOIN PUBLISHER P ON B.PUBLISHER_CODE = P.PUBLISHER_CODE

WHERE PRICE > (SELECT MAX(PRICE)

FROM BOOK

WHERE TYPE = 'MYS')

Extra Credit.

11. List the book code, book title, and units on hand for each book in branch number 2. Be sure each book is included, regardless of whether there are any copies of the book currently on hand in branch 2. Order the output by book code.

SELECT B.BOOK_CODE, TITLE, ISNULL(ON_HAND,0)

FROM BOOK B

LEFT OUTER JOIN INVENTORY I ON B.BOOK_CODE = I.BOOK_CODE

AND BRANCH_NUM = 2

ORDER BY B.BOOK_CODE

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download