This project will utilize SQL to create user views for the provided set of queri

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

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

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

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

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

Explanation / Answer

1) SQL> select b.book_id,b.book_title,p.patron_id,p.first_name,p.last_name,br.branch_name

from waitlist w,patron p,book b,branch br where w.patron_id = p.patron_id

and w.branch_id = br.branch_id and w.book_id = b.book_id;

2) SQL> select b.book_id,book_title,review,first_name,last_name,review_date

from book b,review r,reviewer rvr where b.book_id = r.book_id and r.reviewer_id = rvr.reviewer_id;

3) SQL> select b.book_id,book_title,bc.category_id,category_name

from book b,book_category bc,category c where b.book_id = bc.book_id and bc.category_id = c.category_id;

4) SQL> select c.category_id,category_name,bc.book_id,book_title

from category c,book_category bc,book b where c.category_id = bc.category_id and bc.book_id = b.book_id;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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