Transcript www.cs.newpaltz.edu

Removing Outer Joins Homework
LOJ and NOT IN Removal
When Do We Need NOT IN and LOJs?

Remember the Library database
borrows
Cardholder

(0,1) Copy
(0,n)
Having minimum participation numbers of zero means we need
to use NOT IN queries and left outer join (LOJ) queries.
-- Find the cardholders who have not borrowed any books.
select b_name from cardholder where borrowerid NOT IN
(select borrowerid from borrows)
-- Count the number of books borrowed by each cardholder
select b_name, count(l_date)
from cardholder ch left outer join borrows b on ch.borrowerid = b.borrowerid
group by b_name
LOJ and NOT IN Removal
Remedy:

Suppose that for each cardholder who has not borrowed any
books we add a row to the borrows table with a “dummy”
borrowed copy and a null loan date.
insert into cardholder (borrowerid) values (0);
insert into book (isbn) values ('0');
insert into copy (accession_no,isbn) values ('0','0');
insert into borrows (borrowerid, accession_no)
select borrowerid, '0' from cardholder where
borrowerid NOT IN
(select borrowerid from borrows where borrowerid != 0);
insert into borrows (borrowerid, accession_no)
select 0,accession_no from copy
where accession_no NOT IN
(select accession_no from borrows where accession_no != '0'
-- Find the cardholders who have not borrowed any books.
select b_name from cardholder ch, borrows b
where ch.borrowerid = b.borrowerid and
b.accession_no = '0';
-- Count the number of books borrowed by each cardholder
select b_name, count(l_date)
from cardholder ch, borrows b
where ch.borrowerid = b.borrowerid
group by b_name;
LOJ and NOT IN Removal
Homework:
•
Add additional rows to the Library database and rewrite the
following queries without using NOT IN or LOJ:
-- Find the cardholders who have reserved nothing
select b_name from cardholder where borrowerid NOT IN
(select borrowerid from reserves);
-- Find the books that are not being reserved.
select author, title from book where
isbn NOT IN (select isbn from reserves);
-- Count the number of reservations of each book
select author, title, count(r_date)
from book k left outer join reserves r on k.isbn = r.isbn
group by author, title;
•
Place all the SQL, SQL that executes original queries, SQL to
create dummy rows and SQL to execute new versions of the
queries in a single file and execute from that file.
LOJ and NOT IN Removal
Homework (continued):
•
Also, when adding the dummy rows show how to answer the
following questions:
Find the cardholders who have borrowed at least one book
Find the books (author, title) that have at least one copy not on loan
Find the books (author, title) with no copies on loan
When adding rows to the borrows table why did we need to
add rows to the book table too?
•
NOTE: Later we will see how to keep the Library database
LOJ and NOT IN Removal