homework 2 ddl and dml

create table PUBLISHER
(
Name varchar(16),
Address varchar(16),
Phone varchar(16),
primary key(Name)
);

create table BOOK
(
  BookId integer,
  Title varchar(16),
 PublisherName varchar(16),
 primary key (BookId),
 foreign key (PublisherName) references PUBLISHER(Name)
);

create table BOOK_AUTHORS
(
BookId integer,
AuthorName varchar(16),
primary key(BookId, AuthorName),
foreign key (BookId) references BOOK(BookId)
);

create table LIBRARY_BRANCH
(
BranchId integer,
BranchName varchar(16),
Address varchar(16),
primary key(BranchId)
);

create table BOOK_COPIES
(
BookId integer,
BranchId integer,
No_Of_Copies integer,
primary key(BookId, BranchId),
foreign key(BookId) references BOOK(BookId),
foreign key(BranchId) references LIBRARY_BRANCH(BranchId)
);

create table BORROWER
(
CardNo integer,
Name varchar(16),
Address varchar(16),
Phone varchar(16),
primary key(CardNo)
);

create table BOOK_LOANS
(
BookId integer,
BranchId integer,
CardNo integer,
DateOut date,
DueDate date,
primary key(BookId, BranchId, CardNo),
foreign key (BookId) references BOOK(BookId),
foreign key (BranchId) references LIBRARY_BRANCH(BranchId),
foreign key(CardNo) references BORROWER(CardNo)
);

insert into borrower values(1, 'wai', 'coc104b', '123');
insert into borrower values(2, 'wai', 'coc104b', '123');
insert into publisher values('AW', 'AW123', '123');
insert into book values(1000, 'The Lost Tribe', 'AW');
insert into library_branch values(100, 'Central', 'Central123');
insert into book_loans values (1000, 100, 1, to_date('10/10/99', 'mm/dd/yy'), to_date('10/20/99', 'mm/dd/yy'));
insert into book_loans values (1000, 100, 2, to_date('10/10/99', 'mm/dd/yy'), to_date('10/20/99', 'mm/dd/yy));
insert into library_branch values(101, 'Central', 'Central123');
insert into library_branch values(102, 'Central', 'Central123');
insert into library_branch values(103, 'Central', 'Central123');

select branchname, count(*)
from library_branch
group by branchname, branchid;

insert into borrower values(3, 'akshai', 'rocky mt', '456');

;retrieve names of borrowers who do not have any books checked out.
select name from borrower where not exists (select * from book_loans where book_loans.cardno = borrower.cardno);
select br.name from borrower br where br.cardno not in (select bl.cardno from book_loans bl);
select name from borrower where cardno in ((select distinct cardno from borrower) minus (select distinct cardno from book_loans));
select title, name, address from book b, borrower r where (b.bookid, r.cardno) in (select distinct bookid, cardno from book_loans bl, library_branch lb where lb.branchid=bl.branchid);