-- 1.查询部门平均工资在2500元以上的部门名称及平均工资SELECT d.DNAME,avg(sal)from emp e join dept d using(deptno)GROUPBY DEPTNO
havingavg(sal)>2500-- 2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资,并按平均工资降序排序。SELECT job,avg(sal)from emp
GROUPBY JOB
having job notlike'SA%'andavg(sal)>2500ORDERBYavg(sal)desc-- 4.查询岗位不为SALESMAN,工资和大于等于2500的岗位及每种岗位的工资和。SELECT job,SUM(sal)from emp
GROUPBY job
having job <>'SALESMAN'andSUM(sal)>=2500-- 5.显示经理号码和经理姓名,这个经理所管理员工的最低工资,没有经理的KING也要显示,不包括最低工资小于3000的,按最低工资由高到低排序。SELECT m.empno,m.ename,MIN(e.sal)from emp e leftjoin emp m
on e.mgr =m.empno
GROUPBY e.mgr
havingmin(e.sal)>=3000ORDERBYMIN(e.sal)desc-- 6.查询工资高于编号为7782的员工工资,并且和7369号员工从事相同工作的员工的编号、姓名及工资。SELECT empno,ename,sal
from emp
where sal >(SELECT sal from emp where empno =7782)and job =(SELECT job from emp where empno =7369)-- 7.查询工资最高的员工姓名和工资。 SELECT ename,sal
from emp
where sal =(SELECTMAX(sal)from emp )-- 8.查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。SELECT e.deptno,d.dname,MIN(sal)from emp e join dept d
on e.deptno = d.deptno
GROUPBY deptno
havingMIN(sal)>(SELECTmin(sal)from emp
GROUPBY deptno
having deptno =10)-- 9.查询员工工资为其部门最低工资的员工的编号和姓名及工资。SELECT e.empno,e.ename,e.sal
from emp e join(SELECT deptno,MIN(sal) minsal from emp GROUPBY deptno) a
on e.deptno =a.deptno
WHERE sal <= minsal
-- 10.显示经理是KING的员工姓名,工资。SELECT e.ename,e.sal
from emp e join emp m
on e.mgr = m.empno
where m.ename ='KING'-- 11.显示比员工SMITH参加工作时间晚的员工姓名,工资,参加工作时间。SELECT ename,sal,hiredate
from emp
where hiredate >(SELECT hiredate
from emp
where ename='SMITH')-- 12.使用子查询的方式查询哪些职员在NEW YORK工作。SELECT ename
from emp
WHERE ename =any(SELECT ename
from emp e join dept d using(deptno)where loc ='NEW YORK')-- 13.写一个查询显示和员工SMITH工作在同一个部门的员工姓名,雇用日期,查询结果中排除SMITH。SELECT ename,hiredate
from emp
where ename <>'SMITH'and deptno =any(SELECT deptno
from emp
where ename ='SMITH')-- 14.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。SELECT empno,ename
from emp
where sal >(SELECTavg(sal)from emp)-- 15. 显示部门名称和人数SELECT dname,COUNT(*)from emp e join dept d using(deptno)GROUPBY deptno
-- 16. 显示每个部门的最高工资的员工SELECT deptno,ename,sal
from emp
where sal =any(SELECTMAX(sal)from emp
GROUPBY deptno)-- 17. 显示出和员工号7369部门相同的员工姓名,工资SELECT ename,sal
from emp
where deptno =(select deptno
from emp
where empno=7369)-- 18. 显示出和姓名中包含“W”的员工相同部门的员工姓名select ename
from emp
where deptno =(SELECT deptno
from emp
where ename like'%W%')