SQL server混合查询

表的创建：

create table DEPT(deptno int not null primary key,dname varchar(14)  null,loc varchar(13) null)create table EMP(empno int not null primary key,ename varchar(10) null,job varchar(9) null,mgr int null,hiredate date null,sal float null,comm float null,deptno int null foreign key references DEPT(deptno),)create table SALGRADE(grade int null,losal int null,hisal int null,)

--查询部分：--1.查询雇员姓名,所在部门编号和名称SELECT emp.ename,dbo.DEPT.deptno,dept.dname FROM emp,dbo.DEPT WHERE emp.deptno=dbo.DEPT.deptno--2.查询雇员姓名,工作,领导的姓名，自链接SELECT e1.ename,e1.job,e2.ename FROM dbo.EMP e1 INNER JOIN dbo.EMP e2 ON e1.mgr=e2.empno --3.查询雇员姓名,工作,领导姓名及部门名称SELECT e1.ename,e1.job,e2.ename,(SELECT dname FROM dbo.DEPT WHERE e1.deptno=deptno) FROM dbo.EMP e1 INNER JOIN dbo.EMP e2 ON e1.mgr=e2.empno --4.查询雇员姓名,工作,工资及工资等级SELECT dbo.EMP.ename,dbo.EMP.job,dbo.EMP.sal,dbo.SALGRADE.grade FROM  dbo.EMP INNER JOIN dbo.SALGRADE ON dbo.EMP.sal>=dbo.SALGRADE.losal AND dbo.EMP.sal<=dbo.SALGRADE.hisal--5.查询雇员姓名,工作,工资及工资等级,要求工资等级显示为--A B C D ESELECT '等级'= CASEWHEN SALGRADE.grade=1 THEN 'A'WHEN SALGRADE.grade=2 THEN 'B'WHEN SALGRADE.grade=3 THEN 'C'WHEN SALGRADE.grade=4 THEN 'D'WHEN SALGRADE.grade=5 THEN 'E'END, dbo.EMP.ename,dbo.EMP.job,dbo.EMP.sal,dbo.SALGRADE.grade FROM dbo.EMP , dbo.SALGRADE WHERE  emp.sal BETWEEN dbo.SALGRADE.losal AND dbo.SALGRADE.hisal--6.查询雇员姓名,工作,领导的姓名 (左连接 =(+) :以左表为主)SELECT e1.ename,e1.job,e2.ename AS 领导 FROM dbo.EMP e1 LEFT JOIN dbo.EMP e2 ON e1.mgr=e2.empno --7.查询雇员姓名,年薪,按年薪从高到低排序SELECT ename,sal*12+ISNULL(comm,0)*12 AS 年薪 FROM dbo.EMP ORDER BY sal*12+ISNULL(comm,0)*12--8.查询每个部门中工资最高的雇员姓名,工作,工资,部门名称,最后按工资从高到低排序SELECT e.ename ,e.job ,e.sal ,d.dname FROM dbo.EMP e,dbo.DEPT d,(SELECT deptno,MAX(sal) m FROM dbo.EMP GROUP BY deptno) fWHERE e.deptno=d.deptno AND d.deptno=f.deptno AND e.sal=f.m ORDER BY sal DESC --9.查询每个部门的雇员数量SELECT COUNT(deptno) FROM dbo.EMP GROUP BY deptno--10.求出每个部门的平均工资SELECT AVG(sal) FROM dbo.EMP GROUP BY deptno--11.按部门分组，并显示部门的名称，以及每个部门的员工数SELECT dbo.DEPT.dname,COUNT(dbo.EMP.deptno) FROM dbo.DEPT INNER JOIN dbo.EMP ON dbo.DEPT.deptno = dbo.EMP.deptno GROUP BY dbo.DEPT.dname--要求显示平均工资大于2000的部门编号和平均工资SELECT dbo.EMP.deptno, AVG(sal) FROM dbo.EMP  GROUP BY deptno having avg(sal)>2000;--显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员--的月工资大于$1500，输出结果按月工资的合计升序排列select job,SUM(sal) s from emp where job <>'SALESMAN' group by job HAVING SUM(sal) > 5000 order by s--求出平均工资最高的部门SELECT dname FROM dept d,(SELECT deptno,avg(sal) avgsal, max(avg(sal)) over() maxavgsal from emp GROUP BY deptno) dasWHERE avgsal=maxavgsal AND d.deptno=das.deptno--要求查询出比7654工资要高的全部雇员的信息SELECT * FROM dbo.EMP WHERE sal>(SELECT sal FROM dbo.EMP WHERE empno=7654)--要求查询工资比7654高，与7788从事相同工作的全部雇员信息SELECT * FROM dbo.EMP WHERE sal>(SELECT sal FROM dbo.EMP WHERE empno=7654) AND job=(SELECT job FROM dbo.EMP WHERE empno=7788)--查询出工资最低的雇员姓名，工作，工资SELECT ename,job,sal FROM dbo.EMP WHERE sal=(SELECT MIN(sal) FROM dbo.EMP)--要求查询出部门名称，部门的员工数，部门的平均工资，部门的最低收入雇员姓名SELECT d.dname'部门',ed.c'员工数',ed.a'平均工资',e.ename'最低收入雇员'FROM dept d,emp e,(    SELECT deptno,COUNT(empno) c,AVG(sal) a,MIN(sal) m    FROM emp    GROUP BY deptno) edWHERE d.deptno=ed.deptno AND d.deptno=e.deptno AND  e.sal=ed.m