This project will utilize SQL to create user views for the p

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

1. List all libraries with the library name, address, and telephone number.

2. List all branches with the branch number, name, address, and name of the manager.

3. List all the books in the library system with the number, title, publication date, and précis.

4. List all the patrons with their number, name, and address.

5. List all authors who are still living with their number, name, and birth date.

Solution

1) Ans: CREATE VIEW library_details(name,address,telephone_number) AS SELECT LIBRARY_NAME,STREET_ADDRESS,PHONE FROM LIBRARY

2) Ans: CREATE VIEW branch_details(branch_number,name,address,name_of_the_mangaer) AS SELECT BRANCH_ID,BRANCH_NAME,STREET_ADDRESS,BRANCH_MANAGER FROM BRANCH

3) Ans: CREATE VIEW book_details(number,title,publication_date,precis) AS SELECT BOOK_ID,BOOK_TITLE,PUBLICATION_DATE,PRECIS FROM BOOK

4) Ans: CREATE VIEW patron_details(number,name,address) AS SELECT PATRON_ID,FIRST_NAME + LAST_NAME,STREET_ADDRESS FROM PATRON

5) Ans: CREATE VIEW author_details(number,name,birth_date) AS SELECT AUTHOR_ID,FIRST_NAME + LAST_NAME,DATE_OF_BIRTH FROM AUTHOR WHERE DATE_OF_DEATH<\'28-FEB-2017\'.