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

-- Insert dummy rows
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;

------------------

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

-- Insert more dummy rows
insert into reserves (borrowerid, isbn) select borrowerid, '0' from cardholder where borrowerid NOT IN (select borrowerid from reserves where borrowerid != 0);
--insert into reserves (borrowerid, isbn) select 0, isbn from book where isbn NOT IN (select isbn from reserves where isbn != '0');

-- Find the cardholders who have reserved nothing
select b_name from cardholder ch, reserves r where ch.borrowerid=r.borrowerid AND r.isbn='0';

-- Find the books that are not being reserved
select author, title from book b, reserves r where b.isbn=r.isbn AND r.isbn='0';

-- Count the number of reservations of each book
select author, title, count(r_date) from book b, reserves r where b.isbn=r.isbn group by author, title;