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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- for each book list the book code book title publisher
- maharshi dayanand university
- content analysis
- pandas groupby in action assumption university
- laboratory manual for computer programming with python
- edexcel gcse in computer science scheme of work for year
- python class room diary be easy in my python class
Related searches
- how to type a book title correctly
- book title punctuation
- book title ideas
- book list for 10th grade
- book list for 9th graders
- book title generator
- description of each book of the bible
- book list for middle school
- book list for 4th graders
- book list for 2nd graders
- summary of each book in the bible
- for each in a linked list java