--================================
-- ylb:表的高级查询-  多表连接和子查询
--    pubs库的练习
-- 17:18 2011/12/13
--================================
use pubs
go
select * from authors
select * from titles
--select * from titleauthor
select * from publishers
--1,查看出版社名称，书名称
go
--2,查看出版社名称,出版社出书预付款总额
select pub_name,SUM(advance) '预付款总额' from publishers p
inner join titles t on p.pub_id=t.pub_id
group by pub_name
go
--3,查看出版社名称，出版社出书预付款最大值，单价最小值
select pub_name,max(advance) '付款最大值',MIN(price) '单价最小值' from publishers p
inner join titles t on p.pub_id=t.pub_id
group by pub_name
go
--4,查看出版社编号，出版社名称，书名称，书单价，作者编号
select * from publishers p
inner join titles t on p.pub_id=t.pub_id
inner join titleauthor ta on t.title_id=ta.title_id
go
--5,查看出版社编号，书总价，书最高价格，作者总数
---5分析
--这本书(TC7777)有两个作者（472-27-2349,672-71-3249）
--这个作者（486-29-1786） 出了两本书（PC9999,PS7777）

--5_1,错的
select p.pub_id,SUM(price),MAX(price),count(title_id) from publishers p
inner join titles t on p.pub_id=t.pub_id
group by p.pub_id
go
--5_2,正确的
select p.pub_id,SUM(price),MAX(price),count(distinct au_id) from publishers p
inner join titles t on p.pub_id=t.pub_id
inner join titleauthor ta on t.title_id=ta.title_id
group by p.pub_id
go

--6,查看出版社编号，书总价，书最高价格，要求作者总数大于9的信息
select pub_id,SUM(price),MAX(price) from titles t 
inner join titleauthor ta on t.title_id=ta.title_id
group by pub_id
having count(distinct au_id)>9
go

--7,查找书名称，书编号，作者编号
 
 go
--8,查找书名称，作者数量
select title,COUNT(au_id) '作者数量' from titles t
inner join titleauthor ta on t.title_id=ta.title_id
group by title
go
--9,查找商业书的所有书名称，作者数量, 
select title,COUNT(au_id) '作者数量' from titles t
inner join titleauthor ta on t.title_id=ta.title_id
where type='business'
group by title
go
--10,查找商业书的所有作者姓名，作者编号，作者城市
select * from titles t
inner join titleauthor ta on t.title_id=ta.title_id
inner join authors a on ta.au_id=a.au_id
where type='business'
go
--11,在CA州的作者出的书，书名，书单价总和，
--书最高预付款，书的最低价格。
select title,SUM(price),MAX(advance),MIN(price) from titles t
inner join titleauthor ta on t.title_id=ta.title_id
inner join authors a on ta.au_id=a.au_id
where state='CA'
group by title
go
--12,和商店的同一州作者出的书，书名，书单价总和，
--书最高预付款，书的最低价格。
--12_1,
select au_id from authors a
where state in(select state from stores where state=a.state)
go
--12_2,
select distinct title_id from titleauthor ta
where au_id in(select au_id from authors a
where state in(select state from stores where state=a.state))
go
--12_3,
select * from titles t
where title_id in(select distinct title_id from titleauthor ta
where au_id in(select au_id from authors a
where state in(select state from stores where state=a.state)))
go
--12_4,结论
select title,SUM(price),MAX(advance),MIN(price) from titles t
where title_id in(select distinct title_id from titleauthor ta
where au_id in(select au_id from authors a
where state in(select state from stores where state=a.state)))
group by title
go
--13,和商店的同一州作者出的书，书名，书单价总和，
--书最高预付款，书的最低价格,要
--求书必须大于所有商业书的价格。
--13_1,
select * from titles 
where price>(select max(price) from titles where type='business')
go
--13_2,总结
select title,SUM(price),MAX(advance),MIN(price) from titles t
where title_id in(select distinct title_id from titleauthor ta
where au_id in(select au_id from authors a
where state in(select state from stores where state=a.state)))
and price>(select max(price) from titles where type='business')
group by title
go
--SQL脚本总结
--1，如果有条件“先做条件”
--2，多列先链表，再做条件
--3, 先做条件后排序
--3，分组常和聚合函数在一起。
--4，
--5，子查询，如果有已知条件用嵌套子查询，否则就是相关子查询。

--前（where 列条件）group by 后（having 组条件）

select * from titles
where type='business'
order by title_id desc