“21天好习惯”第一期－17

数据库查询训练

查找出employee表中住址相同的员工的姓名、性别、职称、薪水、住址。

select a.emp_name,a.sex,a.dept,a.salary,b.emp_name,b.sex,b.dept,b.salary
from employee a join employee b
on  (a.emp_no!=b.emp_no) and (a.emp_name>b.emp_name) and (a.addr=b.addr)

检索所有图书的销售明细（图书编号，书名，数量，实际销售单价）

select a.book_no 图书编号,book_name 书名,sum(isnull(qty,0)) 销售量, sum(isnull(qty,0)*isnull(unit_price,0)) 销售金额
from books a left outer join sale_item b
on a.book_no=b.book_no
group by a.book_no,book_name