select查询例子总结

1、求每个部门的平均薪水
select avg(salary) from emp group by deptno;
2、求薪水最高的人的名字
select ename from emp where sal=
( select max(sal) from emp);

3、查询工资大于1200雇员,按部门编号进行分组,分组后平均薪水大于1500,按工薪倒充排列.

select ename

from emp

where sal>1200

group by deptno having avg(sal)>1500

order by avg(sal) desc;

4、那些人工资,在平均工资之上.

select ename,sal from emp

wheresal>(select avg(sal) from emp)

5、查找每个部门挣钱最多的那个人的名字.

select ename from emp join

(select max(sal) max_sal,deptno from emp group by deptno) t_max //t_max为每个部门最高工资表

on (emp.deptno=t_max.deptno and emp.sal=t_max.max_sal);

6、把某个人的名字以及他的经理人的名字求出来

select e.ename c_ename, d.ename d_ename from emp e,emp d

where e.mgr = d.empno

7.求部门平均薪水的等级。

select deptno, avg_sal, grade from salgrade sjoin

(select deptno,avg(salary) avg_salfrom emp group by deptno) t_avgsal

on t.avg_sal between s.losal and s.hisal;

8、那些人是经理

select ename from emp where empno in

(select distinct mgr from emp);

9、不用组函数，求薪水的最高值

select distinct sal from emp where

sal not in

(selectdistinct e1.sal t_sal from emp e1 join emp e2on e1.sal<e2.sal);

10、平均薪水最高的部门编号

select deptno,avg(sal) avg_sal from emp group by deptno

havingavg_sal =

(select max(avg(sal)) from emp group by deptno);

11、平均薪水最高的部门名称

 

select dname from dept

where deptno=

(select deptno =

(select deptno,avg(sal) avg_sal from emp group by deptno

havingavg_sal =

(select max(avg(sal)) from emp group by deptno)

)

);

12、比普通员工最高工资还要高的经理人（非老大）

select ename from emp where empno in

(select distinct deptno from emp where deptno is not null)

and sal>(

select max(sal) from emp where empno not in

(select distinct deptno from emp where deptno is not null)

)；