Consider the following relational database schema that records information about
ID: 3762546 • Letter: C
Question
Consider the following relational database schema that records information about borrowers and their books. The primary key for each relation is underlined.
Books(bID: integer, title: string, isbn: string, publisherName: string)
Authors(aID: integer, fName: string, mName: string, lName: string)
Write(bID: integer, aID: integer)
BookCopies(bID: integer, branchID: string, numOfCopies: interger)
LibraryBranches(branchID: string, branchName: string, address: string, telphone: string)
Borrowers(cardNo: string, fName: string, mName: string, lName: string, , address: string, telphone: string)
BookLoans(bID: integer, branchID: string, cardNo: string, checkOutDate: date, checkInDate: date)
Write the following queries in SQL. Note that you have to write exactly one SQL statement for each question. Also, the query answers must not contain duplicates, but you should use the SQL keyword distinct only when necessary.
1. Find the names of all borrowers who have checked out both books “Fundamentals of Database Systems” and “Information Storage and Retrieval” at the same time, more than once in the past. Note that “Fundamentals of Database Systems” and “Information Storage and Retrieval” are book titles.
2. Print the titles of books that have never been checkout.
Explanation / Answer
For Question 1:
select distinct fname, mname, lname from Borrowers as bo
inner join BookLoans as bl on bl.cardno=bo.cardno
inner join Books as b on b.id=bl.bID
where b.title in ('Fundamentals of Database Systems', 'Information Storage and Retrieval')
For Question 2:
select title from Books where bID not in (select bID from BookLoans)