sql练习,题目来源于网络

练习使用账号 scott (个人喜欢使用子查询来解题,,所以解题答案基本都是子查询的,,,)

–列出至少有三个员工的所有部门和部门信息

select * from dept where deptno in (select deptno from emp group by deptno having count(*) > 3);

–列出受雇日期早于直接上级的所有员工的编号，姓名，部门名称

select * from emp e1,emp e2 where e1.hiredate > e2.hiredate and e1.mgr = e2.empno;

–.列出职位为“CLERK”的姓名和部门名称，部门人数

select e.ename,
       d.dname,
       (select count(*)
          from emp emp1
         group by deptno
        having emp1.deptno = e.deptno)
  from emp e, dept d
 where job = 'CLERK'
   and e.deptno = d.deptno;

–.列出和“SCOTT”从事相同工作的所有员工及部门名称：

select emp1.*,
       (select dname from dept d where d.deptno = emp1.deptno) 部门名称
  from emp emp1
 where job = (select job from emp e where e.ename = 'SCOTT');

–.列出每个部门工作的员工数量、平均工资和平均服务期限（单位为年）

我们先算出服务月份
select round(avg(to_char(sysdate, 'yyyy') - to_char(hiredate, 'yyyy'))) 服务月份
  from emp
 group by deptno;


最后在添加其它要求
select deptno 部门编号,count(*) 人数,avg(sal) 平均工资,round(avg(to_char(sysdate, 'yyyy') - to_char(hiredate, 'yyyy'))) 服务月份
  from emp
 group by deptno;

–、列出各个部门的MANAGER 的最低薪金：

这题主要的坑就是如下, 分组了就不能判断了,这里相关的知识点就是  having 后面只能跟聚合函数,  结论, 如果分组要加聚合之外的条件,那么得拆成俩步,先用子查询查一部分,
select min(sal) from emp e group by deptno having job = 'Manager';

以下是正确答案 
select min(sal), deptno
  from (select * from emp e where e.job = 'MANAGER')
 group by deptno;

–、给任职日期超过10年的人加薪10%；

select ename, sal * 1.1 加薪后
  from emp
 where round(to_char(sysdate, 'yyyy') - to_char(hiredate, 'yyyy')) > 10;


update emp
   set sal = sal * 1.1
 where round(to_char(sysdate, 'yyyy') - to_char(hiredate, 'yyyy')) > 10;

–,查询出和SCOTT工资一样的员工信息

select *
  from emp e
 where sal = (select sal from emp where ename = 'SCOTT')
   and ename <> 'SCOTT';

–,查询出比SCOTT工资高的员工信息

select *
  from emp e
 where sal >(select sal from emp where ename = 'SCOTT');

–查询出不是领导的员工

需要注意的是,这里不能使用not in , 因为老板的mgr 是没有的,in 的相关语法规定不能比较null值,因为任何一个数跟 null 比较都会变成null,

还需要注意的是,我们还需要排除掉mgr是空的那个人,因为是老板
select *
  from emp
 where empno <> any (select distinct mgr from emp)
   and mgr is not null;   

–查询出平均工资高于2000的部门编号和该部门平均工资

select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000; 

–查询出有员工的部门【数据量大的时候用exists效率非常高】

select * from dept d where exists(select * from emp e where d.deptno = e.deptno );

–找到员工表中薪水大于本部门平均工资的员工。

select *
  from (select avg(sal) avgSal, deptno from emp group by deptno) a, emp e
 where e.deptno = a.deptno
   and sal > avgSal;

–统计每年入职的员工个数

select to_char(hiredate, 'yyyy'), count(*)
  from emp
 group by to_char(hiredate, 'yyyy');

–查询出emp表中工资在第六和第十之间的数据oracle中的分页查询

select *
  from (select rownum line, e.* from emp e) e
 where e.line > 6
   and e.line < 10;

–统计薪资大于薪资最高的员工所在部门的平均工资和薪资最低的员工所在部门的平均工资的平均工资的员工信息

这题主要玩的是文字游戏,我们只要把题拆成几步就很好做了
统计薪资大于 薪资最高的员工所在部门的平均工资 和 薪资最低的员工所在部门的平均工资的 平均工资 的员工信息

--查询薪资最高的员工所在部门,
select deptno from emp where sal = (select max(sal) from emp); 

--查询薪资最高的员工所在部门的平均工资
select avg(sal)
  from emp
 group by deptno
having deptno = (select deptno
                   from emp
                  where sal = (select max(sal) from emp))

薪资最低的员工所在部门
select deptno from emp where sal = (select min(sal) from emp); 

薪资最低的员工所在部门的平均工资
select avg(sal)
  from emp
 group by deptno
having deptno = (select deptno
                   from emp
                  where sal = (select min(sal) from emp))

薪资最高的员工所在部门的平均工资 和 薪资最低的员工所在部门的平均工资的 平均工资,也就是 俩个部门的平均工资和/2
select * from emp where sal >           
(select avg(sal)
  from emp
 group by deptno
having deptno = (select deptno
                   from emp
                  where sal = (select max(sal) from emp)))
                  +
(select avg(sal)
  from emp
 group by deptno
having deptno = (select deptno
                   from emp
                  where sal = (select min(sal) from emp)))/2

纠正一下

having与where的唯一区别是 当查询语句中有 聚合函数 的时候 就不能用where 了 只能用having

本章就讲那么多啦,