“21天好习惯”第一期-13

数据库——连接查询

1. (其它)查找出employee表中住址相同的员工的姓名、性别、职称、薪水、住址。

select a.[emp_name],a.[sex],a.[title],a.[salary],a.[addr],b.[emp_name],b.[sex],b.[title],b.[salary],b.[addr]

from [dbo].[employee] a,[dbo].[employee] b

where (a.[emp_name]>b.[emp_name]) and (a.[addr]=b.[addr])

2. (其它)查找出employee表中部门相同且住址相同的女员工的姓名、性别、职称、薪水、住址。

select a.[emp_name],a.[sex],a.[title],a.[salary],a.[addr],b.[emp_name],b.[sex],b.[title],b.[salary],b.[addr]

from [dbo].[employee] a,[dbo].[employee] b

where (a.[emp_no]!=b.[emp_no]) and (a.[emp_name]>b.[emp_name]) and (a.[addr]=b.[addr]) and (a.[dept]=b.[dept]) and a.[sex]='女' and b.[sex]='女'

3. (其它)统计所有图书的销售量和销售金额，并按销售金额的大小降序排序，显示信息包括（图书编号，书名，销售量，销售金额）。

select a.[book_no],a.[book_name],sum([qty]),sum([qty]*[unit_price])

from [dbo].[books] a,[dbo].[sale_item] b

where a.[book_no]=b.[book_no]

group by a.[book_no],a.[book_name]

4. (其它)检索所有图书的销售明细（图书编号，书名，数量，销售金额）提示：使用外连接，将没有售出的书也要显示出来。

select a.[book_no],a.[book_name],sum(isnull([qty],0)),sum(isnull([qty],0)*isnull([unit_price],0))

from [dbo].[books] a left outer join [dbo].[sale_item] b

on a.[book_no]=b.[book_no]

group by a.[book_no],a.[book_name]

5. (其它)检索实际单价高于50元的图书信息（图书编号、书名、数量、实际销售单价）。

select a.[book_no],[book_name],sum([qty]),sum([qty]*[unit_price])/sum([qty])

from [dbo].[books] a join [dbo].[sale_item] b

on a.[book_no]=b.[book_no]

group by a.[book_no],[book_name]

having sum([qty]*[unit_price])/sum([qty])>50

 6. (其它)查询在订单中订购金额超过100元的顾客姓名及收件人。

 

select a.[cust_name],[receiver]

from [dbo].[customer] a,[dbo].[sales] b

where a.[cust_name]=b.[cust_name] and b.[total_amt]>100

7. (其它)查找有同时购买了“数据库系统概论”和“java编程思想”的订单号。

select a.[order_no]

from [dbo].[sale_item] a,[dbo].[sale_item] b,[dbo].[books]c,[dbo].[books] d

where a.[order_no]=b.[order_no] and a.[book_no]=c.[book_no] and b.[book_no]=d.[book_no]

 and c.[book_name]='数据库系统概论' and d.[book_name]='java编程思想'

 8. (其它)统计所有用户的消费金额，并按金额的大小降序排序。

select a.[cust_name],sum(isnull([total_amt],0))

from [dbo].[customer] a left outer join [dbo].[sales] b

on a.[cust_name]=b.[cust_name]

group by a.[cust_name]

order by sum([total_amt]) desc;

 9. (其它)（开放题）该公司现在想做促销活动，想将“数据库系统概论”和其他的图书进行套餐搭配，请你帮忙设计一种方案，并说明理由。

select distinct d.[book_no],d.[book_name]

from [dbo].[sale_item] a,[dbo].[books] b,[dbo].[sale_item] c,[dbo].[books] d

where b.[book_name]='数据库系统概论' and a.[book_no]=b.[book_no]

and d.book_name<>'数据库系统概论'  and  c.book_no=d.book_no

and a.order_no=c.order_no

 10. (其它)（开放题）该公司想要进一批新书，根据现有数据库信息，你能否设计一种比较合理的方案来给该公司提供一些建议，应该进哪些书？

select b.[book_no],b.[book_name],sum([qty])

from [dbo].[sale_item] a,[dbo].[books] b

where a.[book_no]=b.[book_no]

group by b.[book_no],b.[book_name]

order by sum([qty]) desc