Oracle查询语句

今天没有加班，总感觉有东西悬着。。。感觉还有好多技能没掌握，于是顺着书签找到了昨天的sql语句，把剩下的过了一遍，在这个mark一下吧。

–81、求出每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
–82、按部门分组，并显示部门的名称，及每个部门的员工数
select d.dname,count(e.empno) from emp e, dept d where e.deptno = d.deptno
group by d.dname

select d.deptno,d.dname,temp.c from
(
select deptno,count(e.empno) c from emp e group by e.deptno
)
temp,dept d where temp.deptno = d.deptno;
–83、要求显示平均工资大于2000的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
–84、显示非销售人员工作名称以及从事同一工作雇员的月工资的总和
–并且要满足从事同一工作的雇员的月工资合计大于5000，输出结果按照月工资的合计升序排序
select job,sum(sal) su from emp where job<>’SALESMAN’ group by job
having sum(sal) > 5000 order by su

select temp.job,sum(temp.sal) s from
(
select job,sal from emp e where job <> ‘SALESMAN’
)
temp
group by temp.job having sum(temp.sal)>5000
order by s;

–85、求出平均工资最高的部门工资
select max(avg(sal)) from emp group by deptno;
–86、要求查询出比雇员编号为7654工资高的所有雇员信息
select * from emp where sal >
(select sal from emp where empno = 7654);
–87、要求查询出工资比7654高，同时与7788从事相同工作的全部雇员信息
select * from emp where sal >
(
select sal from emp where empno = 7654
)
and job =
(
select job from emp where empno = 7788
)
–88、要求查询出工资最低的雇员姓名，工作，工资
select ename,job,sal from emp where sal =
(
select min(sal) from emp where empno = 7788
)
–89、要求查询出：部门名称，部门的员工数，部门的平均工资，部门的最低
–收入雇员的姓名
select d.dname,temp.c,temp.a,e.ename from dept d,
(
select deptno,count(empno) c,avg(sal) a,min(sal) m from emp group by deptno
) temp ,emp e
where d.deptno = temp.deptno and e.sal = temp.m;
select d.deptno,temp.dname,temp.c,temp.a,e.ename,e.sal from
(select d.dname,count(e.empno) c,avg(e.sal) a,min(e.sal) m from emp e,
dept d where e.deptno = d.deptno
group by d.dname) temp ,
emp e,dept d
where temp.m= e.sal and temp.dname = d.dname;
–90、求出每个部门的最低工资的雇员信息
select * from emp where sal in
(select min(sal) from emp group by deptno)

select * from emp where sal = any(select min(sal) from emp group by deptno)

select * from (
select min(sal) m from emp group by deptno
) temp ,emp e where e.sal = temp.m;

–91、90范式中，比子查询条件中最低的工资要大的雇员信息
select * from emp where sal > any(
select min(sal) from emp group by deptno
);
select * from emp where sal > (
select min(min(sal)) from emp group by deptno
);
–92、90范式中，比子查询条件中最高的工资要小的雇员信息
select * from emp where sal < any(
select min(sal) from emp group by deptno
);
select * from emp where sal <(
select max(min(sal)) from emp group by deptno
)
–93、90范式中，比子查询条件中最高工资要大的雇员信息
select * from emp where sal < all(
select min(sal) from emp group by deptno
);
select * from emp where sal > (
select max(min(sal)) from emp group by deptno
)
–94、90范式中，鼻子查询条件中最小的工资要小的雇员信息
select * from emp where sal < all(
select min(sal) from emp group by deptno
)
select * from emp where sal <(
select min(min(sal)) from emp group by deptno
)
–95、查找出20部门中没有奖金的雇员信息
select * from emp where(sal,nvl(comm,-1)) in (
select sal,nvl(comm,-1) from emp where deptno = 20)

select * from emp where deptno = 20 and comm is null
–96、union操作符返回两个查询选定的所有不重复的行
select deptno from emp
union
select deptno from dept
–97、union all操作符返回两个查询选定的所有行，包括重复的行
select deptno from emp
union all
select deptno from dept
–98、intersect操作符只返回两个查询都有的行
select deptno from emp
intersect
select deptno from dept
–99、minus只返回由第一个查询选定但是没有被第二个查询选定的行
–也就是第一个查询结果中排除第二个查询结果中出现的行
select deptno from emp
minus
select deptno from emp;