SQL 语句小练习

emp表中还有ename（名字），sal（工资），deptno（部门号）； 

选择各个部门里面工资最高的人的姓名，工资，和部门号；代码如下：

select ename,sal,emp.DEPTNO
from emp join (select max(sal) max_sal,deptno
           from emp
      group by deptno) temp
on (emp.SAL = temp.max_sal and emp.DEPTNO = temp.deptno)
order by emp.DEPTNO; 

 

求各个部门工资的平均值属于哪个等级。

select t.deptno, t.avgs, grade
from (select deptno, avg(sal) avgs from emp group by deptno) t join salgrade g
on (t.avgs between g.losal and g.hisal);

 

求各个部门的等级的平均值

select deptno, avg(grade) from
(
 select deptno, grade
 from emp join salgrade
 on(emp.sal between salgrade.losal and salgrade.hisal)
) t
group by deptno;

 

求雇员中那些是经理

select ename
from emp e
where e.EMPNO in (select distinct m.mgr from emp m);

不用组函数，求薪水的最大值

select sal
from emp
where emp.sal not in
(
select distinct e.sal
from emp e join emp m
on e.sal < m.sal
);

 

求各部门工资平均值最大的部门编号

select t.deptno
from (
     select deptno, avg(sal) avgs
  from emp
  group by deptno
  ) t
where t.avgs =(  
    select max(m.avgs)
    from (select deptno, avg(sal) avgs
      from emp
      group by deptno
      ) m
   ); 

 

求部门工资平均值最大的部门名称：

select dname,deptno
from dept
where deptno in(

    select t.deptno
    from (
         select deptno, avg(sal) avgs
      from emp
      group by deptno
      ) t
    where t.avgs in (  
        select max(m.avgs)
        from (select deptno, avg(sal) avgs
          from emp
          group by deptno
          ) m
       )
);

 

求平均工资等级最低的部门名称

select dname
from dept
where dept.DEPTNO in(

select deptno
from(

 select deptno,avgsal, grade
  from(
   select deptno,avg(sal) avgsal
   from emp
   group by deptno
  ) t1 join salgrade g
  on (
     avgsal between g.losal and g.hisal
  )

) t2
where t2.grade=(

 select min(grade)
 from (
 
  select deptno,avgsal, grade
  from(
   select deptno,avg(sal) avgsal
   from emp
   group by deptno
  ) t1 join salgrade g
  on (
     avgsal between g.losal and g.hisal
  )
 
 )

)

);

也可以采用组函数嵌套，但是最多只能嵌套两层。

 

 

求比普通员工的最高工资还高的经理人的名称。

select ename from (
    select * from emp where emp.EMPNO in (select mgr from emp where mgr is not null)
)
where
sal > (

 select max(sal)
 from emp e1
 where e1.EMPNO not in (
    select e2.MGR from emp e2 where e2.MGR is not null
 )

);

 

 

select ename from emp
where
sal > (

 select max(sal)
 from emp e1
 where e1.EMPNO not in (
    select MGR from emp where MGR is not null
 )

)
and empno in(select distinct mgr from emp where mgr is not null );

 

效率比较：

select * from emp where deptno = 10 and ename like '%A%';  语句一

select * from emp where ename like '%A%' and deptno = 10;  语句二

语句一 > 语句二

 

rownum,orcale提供了一个伪字段rownum，但是rownum只可以与小于，小于等于联用，但不可以与大于，等于，大于等于联用。

 

select ename from emp where rownum <=5; (正确)

select ename from emp where rownum =5 ；（错误）

select ename from （select ename，rownum from emp）where rownum =5；（正确）

 

 

求工资最高的前五名员工的工资。

 

select sal from emp where rownum <=5 order by sal desc;（错误）

select sal from (select sal,rownum from emp order by sal desc) where rownum <=5 ;（正确）

 

求工资最高的第六名到第十名。

 

select sal from (select sal,rownum r from emp order by sal desc) where r>=6 and r<=10;（错误）

 

select ename,sal, rownum r from (
    select ename, sal from emp order by sal desc
) r <=10 and r>=6;（错误）

 

select ename,sal from(
 select ename,sal, rownum r from (
     select ename, sal from emp order by sal desc
 )
)where r >=6 and r<= 10;（正确）