--================================
-- ylb:表的高级查询-子查询
--    pubs库的练习
-- 12/12/2011
--================================
use pubs
go
select * from authors
select * from titleauthor
select * from titles
select * from publishers
select * from stores
go
--1. 查找和出版商同一州的作者姓名。
select * from authors a
where state in(select state from publishers where state=a.state)
go
select * from authors a
where exists(select * from publishers where state=a.state)
go
--2. 查找和商店同一州的作者姓名
select * from authors a
where state in(select state from stores where state=a.state)
go
--3. 查找和商店同一城市的出版社名称
select * from publishers p
where city in (select city from stores where city=p.city)
go
--4. 查找写商业书的作者名
select * from authors
select * from titleauthor
select * from titles
go
--4_1,
select title_id from titles
where type='business'
go
--4_2,
select au_id from titleauthor
where title_id in('BU1032','BU1111','BU2075','BU7832')
go
--4_3,
select * from authors
where au_id in('213-46-8915','267-41-2394')
go
--4,结论
select * from authors
where au_id in(select au_id from titleauthor
where title_id in(select title_id from titles
where type='business'))
go
--5. 查找美国出版社出版的所有书
select * from publishers
select * from titles
go
--5_1,
select pub_id from publishers
where country='USA'
go
--5_2,
select * from titles
where pub_id in('0877','0736')
go
--5结论
select * from titles
where pub_id in(select pub_id from publishers
where country='USA')
go
--6. 查找美国出版社出版书的作者姓名
--6_1,
select pub_id from publishers
where country='USA'
go
--6_2,
select title_id from titles
where pub_id in('0877','0736')
go
--6_3,
select au_id from titleauthor
where title_id in('BU2075','MC2222')
go
--6_4,
select * from authors
where au_id in('213-46-8915','712-45-1867')
go
--6总结
select * from authors
where au_id in(select au_id from titleauthor
where title_id in(select title_id from titles
where pub_id in(select pub_id from publishers
where country='USA')))
go
--7. 查找在CA州出版社所出版的商业书作者姓名
--7-1,
select pub_id from publishers
where state='CA'
go
--7-2,
select title_id from  titles
where pub_id in('1389')
and [type]='business'
go
--7-3,
select au_id from titleauthor
where title_id in('BU1032','BU1111')
go
--7-4,
select * from authors
where au_id in('213-46-8915','409-56-7008')
go
--7总结
select * from authors
where au_id in(select au_id from titleauthor
where title_id in(select title_id from  titles
where pub_id in(select pub_id from publishers
where state='CA')
and [type]='business'))
go
--P:8. 查找和出版社在同一州的作者所写的书名
--8_1,
select au_id from authors a
where state in(select state from publishers where state=a.state)
go
--8-2,
select title_id from titleauthor
where au_id in(select au_id from authors a
where state in(select state from publishers where state=a.state))
go
--8-3,
select * from titles
where title_id in(select title_id from titleauthor)
go

--8 结论
select * from titles
where title_id in(select title_id from titleauthor
where au_id in(select au_id from authors a
where state in(select state from publishers where state=a.state)))
go
--9. 查找和作者在同一城市的出版社名称
select * from publishers p
where city in(select city from authors where city=p.city)
go
--10. 查找单价大于所有商业书的书，它的作者姓名

--方法一、
--10-1,
select MAX(price) from titles where type='business'
go
--10-2a,
select title_id from titles
where price >(select MAX(price) from titles where type='business')
go
--10-2b,
select title_id from titles
where price > all(select price from titles where type='business')
go
--10-3,
select au_id from titleauthor
where title_id in(select title_id from titles
where price >(select MAX(price) from titles where type='business'))
go
--10总结
select * from authors
where au_id in(select au_id from titleauthor
where title_id in(select title_id from titles
where price >(select MAX(price) from titles where type='business')))
go

--11.   查找（Algodata Infosystems）出版社所在州，出过商业书的作者姓名
--11_1,
select pub_id from publishers
where pub_name='Algodata Infosystems'
go
--11-2,
select title_id from titles
where type='business' and pub_id =(select pub_id from publishers
where pub_name='Algodata Infosystems')
go
--11-3,
select au_id from titleauthor
where title_id in(select title_id from titles
where pub_id =(select pub_id from publishers
where pub_name='Algodata Infosystems'))
go
--11-4,
select * from authors
where au_id in(select au_id from titleauthor
where title_id in(select title_id from titles
where type='business' and pub_id =(select pub_id from publishers
where pub_name='Algodata Infosystems')))