2018-12-24作业

1.查询部门平均工资在2500元以上的部门名称及平均工资。

select dname,avg(sal)
from emp e join dept d
on e.deptno = d.deptno
GROUP BY DNAME
HAVING avg(sal) > 2500;

2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。

SELECT job,avg(sal)
from emp
where job not like ‘SA%’
GROUP BY JOB
HAVING avg(sal) > 2500
ORDER BY avg(sal) desc;

3.查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。

select dname,ROUND(min(sal)),ROUND(max(sal))
from emp e join dept d
on e.deptno = d.deptno
GROUP BY DNAME
HAVING count(*) > 2;

4.查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。

SELECT job,sum(sal)
from emp
where job <> ‘SALESMAN’
GROUP BY job
HAVING sum(sal) > 2500

5.显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。

SELECT e.mgr,m.ename,min(e.sal)
from emp e LEFT JOIN emp m
on e.mgr = m.empno
GROUP BY e.mgr
HAVING min(e.sal) >= 3000
ORDER BY min(e.sal) desc;

6.查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。

select empno,ename,sal,job
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 = (
select max(sal)
from emp
);

8.查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。

SELECT e.deptno,dname,min(sal)
from emp e join dept d
on e.deptno = d.deptno
GROUP BY e.deptno,dname
HAVING min(sal) > (
select min(sal)
from emp
where deptno = 10
);

9.查询员工工资为其部门最低工资的员工的编号和姓名及工资。

SELECT empno,ename,sal
from emp
where (deptno,sal) in (
select deptno,min(sal)
from emp
group by deptno
);

10.显示经理是KING的员工姓名，工资。

SELECT ename,sal
from emp
where mgr = (
select EMPNO
from emp
where ename = ‘KING’
);

11.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。

SELECT ename,sal,hiredate
from emp
where HIREDATE > (
select HIREDATE
from emp
where ename = ‘SMITH’
);

12.使用子查询的方式查询哪些职员在NEW YORK工作。

SELECT *
from emp
where deptno = (
select DEPTNO
from dept
where loc = ‘NEW YORK’
);

13.写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期，查询结果中排除SMITH。

SELECT ename,hiredate
from emp
where deptno = (
select DEPTNO
from emp
where ename = ‘SMITH’
) and ename <> ‘SMITH’;

14.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。

SELECT empno,ename
from emp
where sal > (
select avg(sal)
from emp
);

15.显示部门名称和人数

SELECT dname,count(*)
from emp e natural join dept d
GROUP BY dname;

16.显示每个部门的最高工资的员工

SELECT *
from emp
where (deptno,sal) in (
select deptno,max(sal)
from emp
GROUP BY 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 in (
select deptno
from emp
where ename like ‘%W%’
);