//找出在此数据库中没有出版记录的作者（name，affiliation）
select name,affiliation from authors
where name not in (select distinct(author) from bookauthors);
===================================================================
select * from authors where not exists
(select name from bookauthors
where author=authors.name);


//找出在2006年11月14日过期的书名
select distinct(a.title),b.available
from books a,bookcopies b
where a.isbn=b.isbn and b.due_date<='10-14-2006'; 

//找出在California地区（出版社在California）出版的书籍（列出isbn，title）

select isbn,title from books b,publishers p
where b.publisher=p.name and p.address like '%CA';

//列出所有作者和他们出版书的数量（列出name,affiliation,num）有的同学把未出过书的作者

select name,affiliation,count(*) as num
from authors,bookauthors
where name=author
group by name,affiliation
union
select name,affiliation,0 as num
from authors where name not in
(select author from bookauthors)
order by num;

//查询超过10美元并且由两个以上作者编写的书名（列出title，publisher）

select title,publisher
from bookauthors ba,books b
where ba.isbn=b.isbn and price>10
group by ba.isbn,title,publisher having count(*)>2;

或者用嵌套查询
select title,publisher
from books where price>10 and isbn in 
(select isbn from bookauthors group by isbn having count(*)>2);


//找出那些没有出版过超过200页书的作者

select author from bookauthors
minus
select author from bookauthors ba,books b
where ba.isbn=b.isbn and pageno>=200;

或者嵌套查询

select distinct author from bookauthors where author not in
(select author from bookauthors ba,books b
where ba.isbn=b.isbn and pageno>=200);

//z找出具有最高售价书的作者（列出author，price）

select distinct author,price
from bookauthors ba,books b
where ba.isbn=b.isbn and price=(select max(price) from books);


//列出所有书的名称，出版社，拷贝的总数
select title,publisher,count(*) as numofcopies
from books b, bookcopies bc
where b.isbn=bc.isbn
group by b.isbn,title,publisher;



//创建视图1，要求列出有3本以上拷贝的书（包括isbn，title，numOfcopies）
create view book_view(isbn,title,numofcopies) as
select books.isbn,title,count(*) from bookcopies,books where
bookcopies.isbn=books.isbn group by books.isbn,title having(count(copy)>3);


//创建视图2，要求列出目前图书馆中可以外借的所有图书（包括isbn，title，可借的数量）

create view book_avail(isbn,title,availnum) as
select books.isbn,title,count(*) from books,bookcopies where
books.isbn=bookcopies.isbn and available='Y'
group by books.isbn,title; 


create view multiauthors
as
select ba.isbn,title
from bookauthors ba,books b
where ba.isbn=b.isbn 
group by ba.isbn,title having count(*)>2;

update books
set price=price*1.05
where isbn in (select books.isbn from books, bookauthors where books.isbn=bookauthors.isbn and bookauthors.author='C.J. Date');