SQL--PUBS

 

在示例数据库Pubs中执行数据查询，要求如下：

1使用内联接查询出authors和publishers表中位于同一个城市的作者和出版社信息。
2 查询出作者号以1~5开头的所有作者，并使用右外联接在查询的结果集中列出和作者在同一个城市的出版社名。（authors是作者表，publishers是出版社表）
3 使用自联接查找居住在 Oakland 相同邮码区域中的作者。（zip是邮码）
4 请查询所有员工的编号、姓、名以及他们所从事工作的名称。(jobs表是员工职位表，其中job_desc为职位描述，即职位名称)
5 请查询出与作者在同一个州的书店的名称、地址和城市。（stores表为书店表，其中stor_name是书店名称,stor_address是地址,city是城市）
6 请查询出所销售书籍的名称、总销售数量以及总销售额。(qty是销售数量, 销售额 =单价price * 销售数量qty)
7 请查询出书籍的名称、类型、价格、撰写这本书的作者的姓、名、联系地址、电话以及出版该书的出版社名称。（authors是作者表，publishers是出版社表）

1.
  select au_fname,address,pub_name,publishers.city
  from authors inner join publishers
  on authors.city=publishers.city
2.
  select au_id,au_fname,pub_name,publishers.city
  from authors right join publishers
  on authors.au_id like'[1-5]%' and authors.city=publishers.city

3
select a.au_fname,a.zip
from authors as a inner join authors as b
on (a.city='Oakland' and a.zip=b.zip)
4.
  select a.emp_id,a.fname,b.job_desc
  from  employee as a inner join jobs as b
  on  a.job_id=b.job_id
5.
  select  distinct a.stor_name,a.stor_address,a.city,a.state
  from stores as a inner join authors as b
  on a.state=b.state
6.(取样汇总，可用建新表(临时表)的方法)
  select a.title,a.price,b.qty into #newtable
  from titles as a right join sales as b
  on a.title_id=b.title_id

  select *from #newtable

  select title as '姓名',sum(price*qty) as '总销售金额',sum(qty) as '总销售数量'
  from #newtable
  group by title
7.(多表联接，inner join on 和where 表达式可以达到同样的效果)
  
  select *from titles
  select *from authors
  select *from titleauthor
  select *from publishers

(1 )
 select t.title,t.type,t.price,a.au_lname,a.au_fname,a.address,a.phone,p.pub_name
  from titles as t inner join titleauthor as b
   on  t.title_id=b.title_id
   inner join authors as a
   on a.au_id=b.au_id
   inner join publishers as p
   on t.pub_id=p.pub_id

(2)
  select c.title,c.type,c.price,
       a.au_lname,a.au_fname,a.address,a.phone,
       d.pub_name
  from authors a ,titleauthor b,
     titles  c, publishers d
  where a.au_id = b.au_id
   and b.title_id = c.title_id
   and c.pub_id = d.pub_id

 

 

 

 

11. 查询Titles表，返回所有版税royalty列非空的数据行；
12. 查询Titles表，返回预付款advance列值大于7000的行数；
13. 查询Titles表，按照出版物类型type列进行分组，显示每一组中type值、Price的平均值；
14. 查询TitleAuthor表中，按照Title_ID进行分组查询，并显示每一组中版权费（royaltyper）的最大值；
15. 查询作者表，限制条件的要求是：au_id的值必须不以”8”开头，但必须包含字符”8”；
16. 查询Sales表，要求返回定单日期（ord_date）在1993年到1994年之间，查询结果按照title_id降序的方式进行显示；
17. 查询Sales表，只返回前40％的行；
18. 编写一个查询，找出现有图书的各个类别（不能有重复值）
19. 编写一个查询，找出各个作者所著图书的数量（提示：作者所著图书在titleauthor表）
20. 编写一个查询，显示各个作者的版权费(royaltyper)的总和
21. 编写一个查询，显示sales表中各本书（title_id 书的编号）的数量（qty）的平均值

11.
   select *from titles
   where royalty is not null
12.
   select count(0)
   from   titles
   where advance>7000
13.
   select type,avg(price)
   from   titles
   group by type
14.
   select max(royaltyper) as 最大值,title_id
   from  titleauthor
   group by title_id
15.
  
   select au_id from authors
   where au_id like '[^8]%[8]%'

16.
   select *from sales
   where ord_date between '1993'and'1994'
   order by title_id desc
17.
   select top 40 percent *      /*注意percent的写法*/
   from sales
18.
   select distinct type as 类别 from titles  /*distinct关键字可从select查询中去掉重复的行 */
   group by type
19.
   select au_id,count(title_id)   /*也可以是count(0)*/
   from   titleauthor
   group by au_id
20.
   select au_id,sum(royaltyper)
   from  titleauthor
   group by au_id
21.
   select title_id,avg(qty) as 平均值
   from sales
   group by title_id