2018.12.24作业
1.查询部门平均工资在2500元以上的部门名称及平均工资。
select dname,avg(sal)
from emp natural join dept
group by dname
having avg(sal) > 2500
2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资,并按平均工资降序排序。
select job,avg(sal)
from emp
group by job
having avg(sal) > 2500
and job not like 'sa%'
order by avg(sal) desc
3.查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。
select dname,min(sal),max(sal)
from emp natural join dept
group by dname
having count(*) >2
4.查询岗位不为SALESMAN,工资和大于等于2500的岗位及每种岗位的工资和。
select job,sum(sal)
from emp
group by job
having job <> 'salesman' and sum(sal) > 2500
5.显示经理号码和经理姓名,这个经理所管理员工的最低工资,没有经理的KING也要显示,不包括最低工资小于3000的,按最低工资由高到低排序。
select e.mgr,d.ename,min(e.sal)
from emp e left join emp d
on e.mgr = d.empno
group by e.mgr,d.ename
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
group by empno,ename,sal
having sal in(select min(sal) from emp group by deptno)
10.显示经理是KING的员工姓名,工资。
select a.ename,a.sal
from emp a join emp b
on a.mgr = b.empno
where b.ename = 'king'
11.显示比员工SMITH参加工作时间晚的员工姓名,工资,参加工作时间。
select ename,sal,hiredate
from emp
group by ename,sal,hiredate
having hiredate > (select hiredate from emp where ename = 'smith')
12.使用子查询的方式查询哪些职员在NEW YORK工作。
select ename
from emp
where ename in (select ename from emp natural join 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)
- 显示部门名称和人数
select dname,count(*)
from emp natural join dept
group by deptno
- 显示每个部门的最高工资的员工
select ename
from emp
where sal in(select max(sal) from emp group by deptno)
- 显示出和员工号7369部门相同的员工姓名,工资
select ename,sal
from emp
where deptno = (select deptno from emp where empno = 7369)
- 显示出和姓名中包含“W”的员工相同部门的员工姓名
select ename
from emp
where deptno in(select deptno from emp where ename like '%W%')