This project will utilize SQL to create user views for the provided set of queri
ID: 3799629 • Letter: T
Question
This project will utilize SQL to create user views for the provided set of queries.
EXAMPLE:
1. List all libraries with the library name, address, and telephone number.
CREATE VIEW LibraryNames (Number, Name, Address, Phone) AS
SELECT Library#, LibraryName, Address, Phone
FROM LIBRARY
COUNTY LIBRARY SYSTEM
Use the following relations for the SQL queries for this project.
LIBRARY (LIBRARY_ID, LIBRARY_NAME, STREET_ADDRESS, CITY, STATE, ZIPCODE, PHONE, MANAGER_NAME)
BRANCH (BRANCH_ID, BRANCH_NAME, STREET_ADDRESS, CITY, STATE, ZIPCODE, PHONE, BRANCH_MANAGER, LIBRARY_ID)
FK LIBRARY_ID -> LIBRARY
PUBLISHER (PUBLISHER_ID, PUBLISHER_NAME, STREET_ADDRESS, CITY, STATE, ZIPCODE, PHONE)
BOOK (BOOK_ID, BOOK_TITLE, PUBLICATION_DATE, PUBLISHER_ID, PRECIS)
FK PUBLISHER_ID -> PUBLISHER
AUTHOR (AUTHOR_ID, FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, DATE_OF_DEATH)
BOOKAUTHOR (BOOK_ID, AUTHOR_ID)
FK BOOK_ID -> BOOK
FK AUTHOR_ID -> AUTHOR
COPY (COPY_ID, BOOK_ID, COST, BRANCH_ID)
FK BOOK_ID -> BOOK
FK BRANCH_ID -> BRANCH
CATEGORY (CATEGORY_ID, CATEGORY_NAME)
BOOK_CATEGORY (BOOK_ID, CATEGORY_ID)
FK BOOK_ID -> BOOK
FK CATEGORY_ID -> CATEGORY
REVIEWER (REVIEWER_ID, FIRST_NAME, LAST_NAME)
REVIEW (REVIEW_ID, BOOK_ID, REVIEWER_ID, REVIEW_DATE, REVIEW)
FK BOOK_ID -> BOOK
FK REVIEWER_ID -> REVIEWER
PATRON (PATRON_ID, FIRST_NAME, LAST_NAME, STREET_ADDRESS, CITY, STATE, ZIPCODE, PHONE, FEE_BALANCE)
CHECKOUT (CHECKOUT_ID, COPY_ID, PATRON_ID, OUT_DATE, DUE_DATE, RETURN_DATE)
FK COPY_ID -> COPY
FK PATRON_ID -> PATRON
WAITLIST (BOOK_ID, PATRON_ID, BRANCH_ID, ON_DATE, OFF_DATE,)
FK BOOK_ID -> BOOK
FK PATRON_ID -> PATRON
FK BRANCH_ID -> BRANCH
QUERIES REQUIRED
6. List all books along with their publishers by book number, title, publisher number, name, and address.
7. List all books from each publisher with the publisher number, name, book number, title, and publication date.
8. List the branches associated with each city library with the library number, name, branch number, branch name, and branch address.
9. List the books owned by each branch with the library number, name, book number, and title.
10. List the books owned by each city library with the library number, name, book number, and title.
Explanation / Answer
(6) SELECT book.num
, book.title
,publisher.num
,publisher.name
,publisher.address
FROM book
INNER
JOIN publisher
ON publisher.id = book.publisher_id
(7) SELECT publisher.number
,publisher.name
,book.number
,book.title
,book.publdate
FROM book
INNER
JOIN publisher
ON publisher_id =book.publisher_id
GROUP BY publisher.name
(8) SELECT branch.number
,branch.name
,branch.address
,library.number
,library.name
FROM branch
INNER
JOIN library
ON library_id = branch.library_id
GROUP BY branch.city
(9) SELECT book.name
,book.title
,library.name
,library.number
FROM library
INNER
JOIN book
on book.id = library.book_id
GROUP BY branch.city
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.