SQL server 数据库练习2----单表查询,子查询,连接查询

/*SELECT语句的基本使用（单表查询）：*/
use YGGL
go

--1)查询每个雇员的地址和电话。
select employeeid,phonenumber,address 
from employees

--2)查询员工编号为“100001”的姓名与地址
select name,address
from employees 
where employeeid='100001'

--3)查询女雇员的姓名与电话，并加标题(三种方法)。
select name "姓名",phonenumber "电话"
from employees 
where sex='女'

select name AS "姓名",phonenumber AS "电话"
from employees 
where sex='女'

select "姓名"=name,"电话"=phonenumber 
from employees 
where sex='女'

--4)查询员工来自几个不同的部门.(distinct)
select distinct departmentid
from employees

--5)计算每个员工的实际收入
select (income-outcome) AS "实际收入"
from salary

--6)找出所有姓王的员工的部门编号。(distinct)
select departmentid
from employees
where name like '王%'

--7)找出收入在2000~3000之间的员工的编号。(between ..and)
select employeeid
from salary
where income between 2000 and 3000

--8)找出所有在部门’101’或’103’或’105’工作的雇员编号.(使用in)
select employeeid
from employees
where departmentid in ('101','103','105')

--9)找出所有地址中含有’大学’的雇员的编号及部门号.（可使用like）
select employeeid,departmentid
from employees
where address like '%大学%'

/*子查询的使用：*/
--1)查找在财务部工作的雇员的情况。
select *
from employees 
where departmentid in 
(select departmentid
from departments
where departmentname='财务部'
)

--2)查询和’刘明’在同一个部门工作的雇员情况,但不包括’刘明’在内.
select *
from employees 
where departmentid in 
(select departmentid
from employees
where name='李明') and  name!='刘明'

--3)查询最早出生的所有职工的信息.
select *
from employees
where birthday =(select min(birthday)from employees)

--4)查找和李丽在同一部门的员工姓名与电话.
select name,phonenumber
from employees
where departmentid =(select departmentid
from employees
where name='李丽') 

--5)查询所有收入在2500元以下的员工的姓名,性别与地址。
select name,sex,address
from employees
where employeeid in (
select employeeid
from salary
where income<=2500
)

--6)查找财务部年龄不低于研发部雇员年龄的雇员的姓名。
select name 
from employees
where
(select birthday 
from employees
where departmentid in(select departmentid from departments where departmentname='财务部'))
>
(select birthday 
from employees
where departmentid in(select departmentid from departments where departmentname='研发部'))

--7)查找研发部比所有财务部收入都高的雇员的姓名。
select name 
from employees
where
(select max(income)
from salary
where departmentid in(select departmentid from departments where departmentname='财务部'))
<
(select income
from salary
where departmentid in (select departmentid from departments where departmentname='研发部'))

--8)查找比雇员平均收入还低的雇员的名单.
select name
from employees
where employeeid in(
select employeeid
from salary
where income<= (select avg(income)from salary) )

--9)查找比所有财务部的雇员收入都高的雇员的姓名.
select name 
from employees
where
(select max(income)
from salary
where departmentid in(select departmentid from departments where departmentname='财务部'))
<
(select income
from salary
where departmentid in (select departmentid from departments where  not departmentname='财务部'))


/*连接查询的使用：*/
--1)查询每个员工的情况及薪水的情况。
select * from employees
inner join salary on employees.employeeid=salary.employeeid

--2)查找市场部收入在2200元以上的雇员姓名及薪水详情。
select name,income,outcome
from employees,salary
where income>2200 and departmentid =
(select departmentid from departments where departmentname='市场部')

--3)查询每个雇员的情况及其工作部门的情况.
select  *
from employees e
full join departments d
on e.departmentid=d.departmentid

--4)查询研发部在1968年以前出生的雇员姓名及收入。
select name,income
from employees,salary
where birthday<'1968-1-1' and departmentid =
(select departmentid from departments where departmentname='研发部')


--5)查询姓名中含有”陈”的雇员的姓名,部门名称与实际收入.
select name,departmentname,income
from employees e,departments d,salary s
where name like '%陈%' and e.departmentid=d.departmentid and e.employeeid=s.employeeid

--6)查询收入低于员工平均收入的员工的员工编号,姓名，部门名称与收入。
select e.employeeid,name,departmentname,income
from employees e,departments d,salary s
where income<(select avg(income)from salary) and e.departmentid=d.departmentid and e.employeeid=s.employeeid




/*数据汇总*/
--1)求财务部雇员的平均收入，并将结果存入表temp中，并查看该表内容。
select * into temp 
from 
(	select avg(income) 平均收入 from salary 
	where employeeid in (
	select employeeid
	from employees
	where departmentid =(select departmentid from departments where departmentname='财务部')
	)
) e


--2)查询经理办公室雇员的最高和最低收入。
select max(income) '最高收入',min(income) '最低收入'
from salary
where employeeid in
(select employeeid from departments where departmentname='经理办公室')

--3)查询财务部雇员的平均实际收入。
select avg(income) '平均实际收入'
from salary
where employeeid in
(select employeeid from departments where departmentname='财务部')

--4)求市场部雇员的总人数。
select count(1) 总人数
from departments
where departmentname='市场部'

--5)统计财务部收入在2500元以上的员工人数。
select count(1) 总人数
from employees
where departmentid in 
(select departmentid from departments where departmentname='财务部')
and employeeid in 
(select employeeid from salary where income>2500)

--6)统计各部门收入在2000元以上的雇员的人数。
select count(*) 人数,departmentid
from employees
where employeeid in(select employeeid from salary where income>2000 )
group by departmentid

--7)将各雇员的情况按出生时间先后排列。
select *
from employees
order by birthday asc

--8)查询实际最高收入前6名雇员的姓名与实际收入.
select top 6 name,income
from employees e
inner join salary s
on e.employeeid=s.employeeid
order by income

--9)查询部门人数在4个以下的部门名称与部门人数.
select d.departmentname,count(employeeid) '人数'
from employees e
inner join departments d
on e.departmentid=d.departmentid  
group by  d.departmentname
having count(employeeid)<4

--10)查询每个部门的部门名称与平均实际收入,并按其降序排列.
select d.departmentname,avg(income) 平均收入
from employees e, salary s ,departments d
where e.departmentid=d.departmentid and e.employeeid=s.employeeid
group by e.departmentid,d.departmentname
order by avg(income) desc

--11)查询女员工人数超过3个的部门名称。
select departmentname
from departments 
where departmentid in
(	select departmentid
	from employees
	group by departmentid
	having count(employeeid)>3
)



--14)查询员工编号以‘6’开头的员工编号、姓名和电话，并产生一个员工总人数行。
select employeeid,name,phonenumber
from employees
where employeeid like '6%'
select count(1)
from (select employeeid,name,phonenumber
from employees
where employeeid like '6%') q

--15)查询部门平均收入在3000元以下的部门名称与部门平均收入。
select departmentname,avg(income)
from departments d,salary s,employees e
where e.employeeid= s.employeeid and e.departmentid= d.departmentid 
group by d.departmentname
having avg(income)<3000