This project will utilize SQL to create user views for the provided set of queri
ID: 3800057 • Letter: T
Question
This project will utilize SQL to create user views for the provided set of queries.
EXAMPLES:
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
2. Retrieve the name, city, and grade point average of students with a high GPA) greater than or equal to 3.7)
SELECT stu_lname, stu-fname, stu_GPA FROM student
WHERE stu_GPA >= 3.7 ORDER BY stu_lname, stu_fname;
3. List the name and hiring date of faculty hired in 1994 or 1995
SELECT fac_lname, fac_fname, fac_hire_date FROM faculty
WHERE fac_hire_date BETWEEN ‘1-JAN-1994’ AND ’31-DEC-1995’
ORDER BY fac_lname, fac_fname;
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:
11. List the books written by each author with the author number, name, book number, title, and publication date.
12. List the books that each patron currently has checked out with the patron number, patron name, telephone number, book number, and title.
13. Same as #12 except with the addition of the branch number and name.
14. Same as #12 except that the list should be for any book the patron has ever checked out.
15. Same as #12 except for all books that are past due along with the past due fee.
16. List the current total of past due fees for each library for all books that are still out.
Explanation / Answer
11) Select AUTHOR_ID, FIRST_NAME,BOOK_ID, BOOK_TITLE, PUBLICATION_DATE,
From BOOK,AUTHOR,BOOKAUTHOR
where BOOK.BOOK_ID=BOOKAUTHOR.BOOK_ID AND AUTHOR.AUTHOR_ID=BOOKAUTHOR.AUTHOR_ID
12) Select PATRON_ID,FIRST_NAME,PHONE,BOOK_ID,BOOK_TITLE
From PATRON,CHECKOUT,COPY,BOOK
where PATRON.PATRON_ID=CHECKOUT.PATRON_ID AND CHECKOUT.COPY_ID=COPY.COPY_ID AND COPY.BOOK_ID=BOOK.BOOK_ID
As per the chegg guidelines i am supposed to answer first question due to time limit consideration. Hence please post rest of the questions a seperate query.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.