select*from emp where sal =(selectmax(sal)from emp);
最低工资的员工信息
select*from emp where sal =(selectmin(sal)from emp);
分组查询
每个部门的平均工资
select deptno,avg(sal)from emp groupby deptno;
子查询
单行子查询
查询出高于10号部门的平均工资的员工信息
select*from emp where sal >(selectavg(sal)from emp where deptno=10);
多行子查询(in not in any all) >any >all
查询出比10号部门任何员工薪资高的员工信息
select*from emp where sal >any(select sal from emp where deptno=10);
多列子查询(实际使用较少) in
和10号部门同名同工作的员工信息
select*from emp where(ename,job)in(select ename,job from emp where deptno=10);
Select接子查询
获取员工的名字和部门的名字
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
from后面接子查询
查询emp表中经理信息
select*from emp where job='MANAGER';
where 接子查询
薪资高于10号部门平均工资的所有员工信息
select*from emp where sal >(selectavg(sal)from emp where deptno=10);
having后面接子查询
有哪些部门的平均工资高于30号部门的平均工资
select deptno from emp groupby deptno
havingavg(sal)>(selectavg(sal)from emp where deptno =30);
工资>JONES工资
select*from emp where sal >(select sal from emp where ename ='JONES');
查询与SCOTT同一个部门的员工
select*from emp where deptno=(select deptno from emp where ename ='SCOTT');
工资高于30号部门所有人的员工信息
select*from emp where sal >(selectmax(sal)from emp where deptno=30);
查询工作和工资与MARTIN完全相同的员工信息
select*from emp where(job,sal)in(select job,sal from emp where ename ="MARTIN");
有两个以上直接下属的员工信息
select*from emp groupby mgr havingcount(empno)>2;
查询员工编号为7788的员工名称,员工工资,部门名称,部门地址
select e.ename,e.sal,d.dname,d.loc from emp e , dept d
where e.deptno=d.deptno and e.empno =7788;
SQL查询的综合案例
查询出高于本部门平均工资的员工信息
select*from emp e where sal >any(selectavg(sal)from emp ee groupby deptno having e.deptno=ee.deptno);
列出达拉斯加工作的人中,比纽约平均工资高的人
SELECT*FROM emp WHERE deptno =(SELECT deptno FROM dept WHERE loc ='DALLAS')AND
sal >(SELECTAVG(sal)FROM emp WHERE deptno =(SELECT deptno FROM dept WHERE loc ='NEW YORK'));
查询7369员工编号,姓名,经理编号和经理姓名
select e.empno,e.ename,d.empno,d.ename mgr from emp e,emp d
where e.mgr=d.empno and e.empno=7369;
查询出各个部门薪水最高的员工所有信息
- 方式一
select*from emp where(sal,deptno)in(selectmax(sal),deptno from emp groupby deptno);- 方式二
select*from emp e,(selectmax(sal) max,deptno from emp groupby deptno) s
where e.deptno=s.deptno and e.sal = s.max;