Create the following views using the Where clause; Do not use joins! 16. List al

ID: 3836897 • Letter: C

Question

Create the following views using the Where clause; Do not use joins!

16. List all books that patrons are waiting for with the book number, title, patron number, name, and branchname.

17. Show all the reviews for each book with the book number, title, review, reviewer name, and date of thereview.

18. Show all the categories for each book with the book number, title, category number, and category name.

19. Show all the books for each category with the category number, category name, book number, and book title.

COUNTY LIBRARY SYSTEM Use the following relations for the SQL queries for this project. D, LIBRARY NAME, STREETLADDRESS, CITY, STATE, ZIPCODE, PHONE, LIBRARY LIBRARY MANAGER NAME) BRANCH BRANCH ID, BRANCH NAME, STREET ADDRESS, CITY, STATE, ZIPCODE, PHONE, BRANCH MANAGER, LIBRARYLID) FK L BRARY ID LIBRARY (PUBLISHER ID, PUBLISHER NAME, STREETLADDRESS, STATE ZIPCODE, PHONE) CITY, PUBLISHER BOOK BOOK D, BOOK TITLE, PUBLICATION DATE, PUBLISHER ID, PRECIS) FK PUBLISHER ID PUBLISHER (AUTHORID, FIRST NAME, LAST NAME, DATE oF BIRTH, DATE OF DEATH) AUTHOR BOOK AUTHOR BOOK D. AUTHOR ID FK BOOK ID BOOK FK AUTHOR ID AUTHOR COPY ID, BOOK D, COST, BRANCH ID) COPY FK BOOK ID BOOK FK BRANCH ID BRANCH CATEGORY CATEGORY ID, CATEGORY NAME

Explanation / Answer

Sql queries for view creations are given below:

Question 16. List all books that patrons are waiting for with the book number, title, patron number, name, and branchname

CREATE VIEW book_patron_view AS (
SELECT BOOK_ID, BOOK_TITLE FROM BOOK
UNION
SELECT PATRON_ID, FIRST_NAME, LAST_NAME FROM PATRON
UNION
SELECT BRANCH_NAME FROM BRANCH
)

Question 17. 17. Show all the reviews for each book with the book number, title, review, reviewer name, and date of thereview.

CREATE VIEW book_review_view AS (
SELECT BOOK_ID, BOOK_TITLE FROM BOOK WHERE BOOK_ID IN (SELECT BOOK_ID FROM REVIEW)
UNION
SELECT REVIEW_ID, REVIEW_DATE
UNION
SELECT REVIEWER_ID, FIRST_NAME, LAST_NAME FROM REVIEWER WHERE REVIEWER_ID IN (SELECT REVIEWER_ID FROM REVIEW)
)

Navigate

Create the following views using the Where clause; Do not use joins! 12. List th

Create the following views using the Where clause; Do not use joins! 16. List al

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

Create the following views using the Where clause; Do not use joins! 16. List al

Question

Explanation / Answer

Related Questions

Navigate