Share
Explore BrainMass

SQL Statements for Library Database

E. Given the database schema on the relations as follows:

LIBRARY(LNO, LNAME, LOCATION)
AUTHOR(ANO, ANAME)
BOOK(BNO, TITLE, ANO)
COPY(CNO, LNO, BNO)
PATRON(PNO, PNAME, ADDRESS)
LOAN(LOANNO, PNO, CNO)

LNO - Library Number
LNAME - Library Name
ANO - Author Number
BNO - Book Number
CNO - Copy Number
PNO - Patron Number
LOAN - Loan Number

Write the following queries in SQL:
(You are not supposed to use any aggregation functions such as: count(), sum(), etc.)

1. Find all pairs of different book titles that have copies in the same library.
(Your SQL statement should exclude pairs of the form (x, x) from the answer set and to provide one of the pairs (x, y) or (y, x) when both qualify.)
2. Find all triplets of libraries that are collocated.
(Your SQL statement should include the library name in the form of (a, b, c), exclude the triplets of the form (x, x, x), (x, x, y), (x, y, x), and (y, x, x) from the answer set, and just list one of the six permutations of (x, y, z) when triplets qualify.)
3. Find the titles of books with copies in all Fort Lauderdale libraries.
4. Find the titles of books with copies in all Fort Lauderdale libraries that features copies of all books written by Maier.

Solution Summary

Word attachment shows how to find pairs or book copies, triplets of collocated libraries and titles within a certain library using SQL.

$2.19