Transcript Hard SQL Queries Explained Database Principles Query 1, Problem 1:  Find the books (author, title) which have not been borrowed by a cardholder.

Hard SQL Queries Explained
Database Principles
Query 1, Problem 1:

Find the books (author, title) which have not been borrowed by
a cardholder of 'senior' status but have been borrowed by
someone from Kingston.
select author, title from book k, copy c, borrows b, cardholder ch
where k.isbn = c.isbn and
c.accession_no = b.accession_no and
b.borrowerid = ch.borrowerid and
ch.b_addr = 'Kingston' and
ch.b_status NOT IN
(select b_status from cardholder where b_status = 'senior')
finds the books borrowed by people from Kingston whose status is not senior
Database Principles
Query 1, Problem 2:

Find the books (author, title) which have not been borrowed by
a cardholder of 'senior' status but have been borrowed by
someone from Kingston.
select author, title from book k, copy c, borrows b
where k.isbn = c.isbn and
c.accession_no = b.accession_no and
k.isbn NOT IN (select isbn from copy c, borrows b, cardholder ch
where c.accession_no = b.accession_no and
b.borrowerid = ch.borrowerid and
ch.b_status = 'senior' and
ch.b_addr != 'Kingston')
finds the borrowed books that are not borrowed by seniors from outside Kingston
Database Principles
Query 1, Problem 2:

Find the books (author, title) which have not been borrowed by
a cardholder of 'senior' status but have been borrowed by
someone from Kingston.
select author, title from book k, copy c, borrows b, cardholder ch
where ch.b_status != 'senior' and ch.b_addr = 'Kingston' and
b.borrowerid = ch.borrowerid and
c.accession_no = b.accession_no and
k.isbn = c.isbn
finds the books borrowed by non-seniors from Kingston
“not borrowed by seniors” not the same as “borrowed by non-senior”
Database Principles
Query 2, Problem 1:

Find the books that have not been borrowed.
select author, title from book k, copy c, borrows b
where k.isbn = c.isbn and
c.accession_no NOT IN
(select b.accession_no from borrows)
this finds books with at least one copy not borrowed; if a “book” has
“not been borrowed” then every copy of that book and not just a single
copy has not been borrowed
Database Principles
Query 2, Problem 2:

Find the books that have not been borrowed.
select author, title from book k, copy c
where c.accession_no NOT IN
(select b.accession_no from borrows)
no join of book and copy
this finds books with at least one copy of some other book is not borrowed
Database Principles
Query 3, Problem 1:

Find the books reserved only by people who have also
borrowed at least one book
select author, title from book k
where isbn IN (select k.isbn from book k, reserves r, borrows b
where k.isbn = r.isbn and
r.borrowerid = b.borrowerid)
this finds the books reserved by someone who has borrowed
some book; however if the book is also reserved by someone
who has not borrowed another book then the original book still
makes it into the answer set, which is wrong
Database Principles
Query 3, Problem 1:

Find the books reserved only by people who have also
borrowed at least one book
select author, title from book k, borrows b, reserves r, copy c
where r.borrowerid = b.borrowerid and r.isbn = k.isbn
this finds the books reserved by someone who has borrowed something;
it is also incorrect because it mentions copy in the from_clause but does
not use it.
Database Principles
Query 3, Problem 2:

Find the books reserved only by people who have also
borrowed at least one book
select author, title from book k, borrows b, reserves r, copy c
where k.isbn = r.isbn and
r.borrowerid NOT IN (select b.borrowerid from reserves r, borrows b
where r.borrowerid != b.borrowerid)
this finds the books reserved by someone who borrowed nothing
Database Principles
Query 3, Problem 3:

Find the books reserved only by people who have also
borrowed at least one book
select author, title from book k, reserves r
where k.isbn = r.isbn and
k.isbn NOT IN (select c.isbn from copy c, borrows b
where c.accession_no = b.accession_no and
b.borrowerid = r.borrowerid)
this finds the books reserved by someone and
not borrowed by the same person;
Database Principles
Query 3, Problem 4:

Find the books reserved only by people who have also
borrowed at least one book
(select author, title from book k, reserves r
where k.isbn = r.isbn )EXCEPT
(select author, title from reserves r, book k
where r.isbn = k.isbn and
r.borrowerid = ((select borrowerid from cardholder)
EXCEPT
(select borrowerid from borrows)))
this almost works. It's logic is correct except that
r.borrowerid = ((select borrowerid . . .))
fails if the subquery does not have exactly one row in its answer set.
It turns out that in our particular case this is true so the query “looks
like” it works but it would fail if we had several more cardholders, for example
Database Principles
Query 3, Problem 5:

Find the books reserved only by people who have also
borrowed at least one book
select author, title from book k, borrows b, reserves r, copy c
where k.isbn = r.isbn and
c.accession_no = b.accession_no
this finds reserved books where someone (anyone)
has borrowed something (anything)
Database Principles
Query 4, Problem 1:

Find the books (author, title) which have been reserved but not
borrowed by the same cardholder.
select author, title from book k, copy c, borrows b, cardholder ch
where k.isbn = r.isbn and
k.isbn = c.isbn and
c.accession_no = b.accession_no and
b.borrowerid != r.borrowerid
this finds books reserved by one cardholder and borrowed by
another; it does not exclude the possibility that a reserved book
has also been borrowed by the cardholder making the reservation
Database Principles
Query 4, Problem 2:

Find the books (author, title) which have been reserved but not
borrowed by the same cardholder.
select author, title from book k, copy c, borrows b, reserves r
where k.isbn = c.isbn and
c.accession_no = b.accession_no and
b.borrowerid NOT IN
(select b.accession_no
from book k, copy c, borrows b, reserves r , cardholder ch
where r.borrowerid = b.borrowerid)
this finds a book on loan
where someone else has reserved some other book
Database Principles
Query 5, Problem 1:

Find the books (author, title) published by Addison Wesley and
borrowed by all people from New Paltz.
select author, title from book k
where pub_name = 'AW' and NOT EXISTS
(select * from cardholder ch, borrows b, copy c
where ch.borrowerid = b.borrowerid and
b.accession_no = c.accession_no and c.isbn = k.isbn and
ch.b_addr != 'New Paltz'
this finds the AW books where there does not exist a cardholder who
has borrowed that book who is not from New Paltz; ie, only cardholders
from New Paltz have borrowed the book.
Database Principles
Query 5, Problem 2:

Find the books (author, title) published by Addison Wesley and
borrowed by all people from New Paltz.
select author, title from book k, copy c, borrows b, cardholder ch
where k.isbn = c.isbn and c.accession_no = b.accession_no and
b.borrowerid = ch.borrowerid and ch.b_addr = 'New Paltz' and
k.pub_name IN (select pub_name from book
where pub_name = 'AW')
this finds all books borrowed by people from New Paltz and
published by AW
Database Principles