SQL复杂查询联系汇总（牵扯多表链…

USE [study] 

go

--1.查询雇员姓名,所在部门编号和名称

select EMP.ename,EMP.deptno,DEPT.dname from EMP,DEPT whereemp.deptno=dept.deptno;

--2.查询雇员姓名,工作,领导的姓名

select e1.ename,e1.job,e2.ename from EMP e1 inner join EMP e2on e1.mgr=e2.empno;--自链接

--3.查询雇员姓名,工作,领导姓名及部门名称

select e1.ename,e1.job,e2.ename,(select dept.dname fromDEPT 

where e1.deptno=dept.deptno) from EMP e1 inner join EMP e2 one1.mgr=e2.empno;

--4.查询雇员姓名,工作,工资及工资等级

select EMP.ename,EMP.job,emp.sal,grade 

from EMP inner join SALGRADE on emp.sal>=SALGRADE.losal andemp.sal<=SALGRADE.hisal;

select * from EMP;

--5.查询雇员姓名,工作,工资及工资等级,要求工资等级显示为A B C D E [case]

select EMP.ename,EMP.job,EMP.sal,'工资等级'=

case

when SALGRADE.losal>=700 and SALGRADE.hisal<=1200then'E'

when SALGRADE.losal>=1201 and SALGRADE.hisal<=1400then'D'

when SALGRADE.losal>=1401 and SALGRADE.hisal<=2000then'C'

when SALGRADE.losal>=2001 and SALGRADE.hisal<=3000then'B'

when SALGRADE.losal>=3001 and SALGRADE.hisal<=9999then'A'

else '可以回家了'

end

from EMP inner join SALGRADE 

on emp.sal>=SALGRADE.losal andemp.sal<=SALGRADE.hisal;

--6.查询雇员姓名,工作,领导的姓名 (左连接 =(+) :以左表为主)

select e1.ename,e1.job,e2.ename from EMP e1 left join EMP e2on e1.mgr=e2.empno;

--7.查询雇员姓名,年薪,按年薪从高到低排序

select ename,(SAL*12) as 年薪 from EMP order by 年薪 desc;

--8.查询每个部门中工资最高的雇员姓名,工作,工资,部门名称,最后按工资从高到低排序????????

--selectemp.ename,emp.job,emp.sal,dept.dname 

--from emp,dept where emp.deptno=dept.deptnoand 

--emp.sal=(select max(sal) from EMP inner joinDEPT 

--on emp.deptno=dept.deptno group by dept.deptno) order byemp.sal desc;

selectemp.ename,emp.job,emp.sal,dept.dname 

from emp,dept,(select max(sal) maxsal from EMP inner joinDEPT 

on emp.deptno=dept.deptno group by dept.deptno) t

where emp.deptno=dept.deptno and emp.sal=t.maxsal order byemp.sal desc;

--9.查询每个部门的雇员数量

select deptno,COUNT(empno) from EMP group by deptno;

--10.求出每个部门的平均工资

select deptno,AVG(sal) from EMP group by deptno;--显示部门编号

select DEPT.dname,AVG(sal) from EMP inner joinDEPT 

on emp.deptno=dept.deptno group by dept.dname;--显示部门名称

--11.按部门分组，并显示部门的名称，以及每个部门的员工数

select DEPT.dname,COUNT(emp.empno) fromEMP,DEPT 

where emp.deptno=dept.deptno group by dept.dname;

--12.要求显示平均工资大于2000的部门编号和平均工资

select dept.deptno,AVG(sal) fromDEPT,EMP 

where dept.deptno=emp.deptno group by dept.deptno havingAVG(sal)>2000;

--13.显示非销售人员工作名称以及从事同一工作雇员的月工资的总和 ,并且要满足从事同一工作的雇员

--的月工资大于$1500，输出结果按月工资的合计升序排列??????????

select t.job,sum(t.sal) sumsal

from (select job,SAL from EMP where job!='SALESMAN') t

where t.sal>1500 group by t.job order by sumsal asc;

--14.求出平均工资最高的部门

select top 1 deptno,AVG(sal) from EMP group by deptno order byAVG(sal) desc;

--15.要求查询出比7654工资要高的全部雇员的信息

select * from EMP where sal>(select sal from EMP whereempno=7654)

--16.要求查询工资比7654高，与7788从事相同工作的全部雇员信息

select * from EMP where sal>(select sal from EMP whereempno=7654) 

and job=(select job  from EMP whereempno=7788)

--17.查询出工资最低的雇员姓名，工作，工资

select ename,job,SAL from EMP where sal=(select MIN(sal) fromEMP );

--18.要求查询出部门名称，部门的员工数，部门的平均工资，部门的最低收入雇员姓名????????????????

select DEPT.dname 部门名称,COUNT(emp.ename) 员工数,AVG(sal) 平均工资,emp.ename 最低收入雇员

from EMP,DEPT,(select MIN(sal) minsal from EMP inner joinDEPT 

on emp.deptno=dept.deptno group by dept.deptno) t

where emp.deptno=dept.deptno and emp.sal=t.minsal group by dept.dname;