1 / 5

Removing Outer Joins Homework

Removing Outer Joins Homework. When Do We Need NOT IN and LOJs?. Remember the Library database Having minimum participation numbers of zero means we need to use NOT IN queries and left outer join (LOJ) queries. borrows. Copy. Cardholder. (0,n). (0,1).

dotty
Download Presentation

Removing Outer Joins Homework

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. LOJ and NOT IN Removal Removing Outer Joins Homework

  2. LOJ and NOT IN Removal When Do We Need NOT IN and LOJs? • Remember the Library database • Having minimum participation numbers of zero means we need to use NOT IN queries and left outer join (LOJ) queries. borrows Copy Cardholder (0,n) (0,1) -- 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

  3. 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;

  4. LOJ and NOT IN Removal Homework: • Add additional rows to the Library database and rewrite the following queries without using NOT IN or LOJ: • 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. -- 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;

  5. LOJ and NOT IN Removal Homework (continued): • Also, when adding the dummy rows show how to answer the following questions: • 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 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

More Related