1. 单行子查询
- 查询入职日期最早的员工姓名,入职日期
select ename, hiredate
from emp
where hiredate = (select min(hiredate) from emp );
- 查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名,工资,部门名称
select ename, sal, dname
from emp, dept
where emp.deptno = dept.deptno
and sal > (select sal from emp where ename = 'SMITH')
and loc = 'CHICAGO';
- 查询入职日期比20部门入职日期最早的员工还要早的员工姓名,入职日期
select ename, hiredate
from emp
where hiredate < ( select min( hiredate )
from emp
where deptpno = 20 );
- 查询部门人数大于所有部门平均人数的的部门编号,部门名称,部门人数
select deptno, dname, count(*)
from dept, emp
where emp.deptno = dept.deptno
group by emp.deptno, dname
having count(*) > ( select count( empno ) / count( distinct deptno )
from emp
where deptno is not null ) ;
2. 多行子查询
- 查询入职日期比10部门任意一个员工晚的员工姓名、入职日期,不包括 10部门员工
select ename, hiredate
from emp
where deptno != 10
and hiredate > any (select hiredate
from emp
where deptno = 10);
- 查询入职日期比10部门所有员工晚的员工姓名、入职日期,不包括10部门员工
select ename, hiredate
from emp
where deptno != 10
and hiredate > all ( select hiredate
from emp
where deptno = 10 );
- 查询职位和10部门任意一个员工职位相同的员工姓名,职位职位,不包括10部门员工
select ename, job
from emp
where deptno != 10
and job in ( select job
from emp
where deptno = 10 );
3. 多列子查询
- 查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名,职位职位,不包括10部门员工
select ename, job
from emp
where deptno != 10
and ( job, mgr ) in ( select job, mgr
from emp
where deptno = 10);
- 查询职位及经理和10部门任意一个员工职位或经理相同的员工姓名,职位,不包括10部门员工
select ename, job
from emp
where deptno != 10
and ( job in (select job from emp where deptno = 10)
or
mgr in (select mgr from emp where deptno = 10)
);
4. form中使用子查询
- 查询比自己部门职位平均工资高的员工姓名、职位,部门名称,职位平均工资
select ename, job, dname, sal, avg_sal
from emp,
( select emp.deptno, dname, avg( sal ) as avg_sal
from dept, emp
where dept.deptno = emp.deptno
group by emp.deptno, dname) a
where emp.deptno = a.deptno
and sal > avg_sal;
- 查询比自己职位平均工资高的员工姓名、职位,部门名称,职位平均工资
select ename, job, dname, sal, avg_sal
from emp,dept,
(select job, avg(sal) as avg_sal from emp group by job ) a
where emp.deptno = dept.deptno
and emp.job = a.job
and sal > avg_sal ;
- 查询职位和经理同员工SCOTT或BLAKE完全相同的员工姓名、职位,不包括SCOOT和BLAKE本人
select ename, emp.job
from emp,
( select job, mgr
from emp
where ename in ('SCOTT', 'BLAKE') ) a
where ename not in ('SCOTT', 'BLAKE')
and emp.job = a.job
and emp.mgr = a.mgr;
- 查询不是经理的员工姓名
select ename
from emp,
(select distinct mgr
from emp
where mgr is not null ) emp2
where emp.empno = emp2.mgr( + ) and emp2.mgr is null;
伪列Rownum
- 查询入职日期最早的前5名员工姓名,入职日期
select ename, hiredate
from (select * from emp order by hiredate desc)
where rownum < 6;
- 查询工作在CHICAGO并且入职日期最早的前2名员工姓名,入职日期
select ename, hiredate
from ( select *
from emp
where deptno = (select deptno from dept where loc = 'CHICAGO')
order by hiredate asc )
where rownum < 3;
分页-Rownum
- 按照每页显示5条记录,分别查询第1页,第2页,第3页信息,要求显示员工姓名、入职日期、部门名称。
select ename,
hiredate,
( select dname from dept where b.deptno = dept.deptno )
from (select rownum rn, a.*
from ( select * from emp order by hiredate desc ) a ) b
where rn between 1 and 5; -- 第一页
-- where rn between 6 and 10; 第二页
-- where rn between 11 and 15; 第三页
- 按照每页显示5条记录,分别查询工资最高的第1页,第2页,第3页信息,要求显示员工姓名、入职日期、部门名称、工资。
--- 第一页的最高工资
select ename,
hiredate,
sal,
( select dname from dept where b.deptno = dept.deptno )
from (select rownum rn, a.*
from ( select * from emp order by hiredate desc ) a ) b
where rn between 1 and 5
and sal = ( select max(sal)
from (select rownum rn, a.*
from ( select * from emp order by hiredate desc ) a )
where rn between 1 and 5 );
--- 第二页、三页的最高工资只要更改上述代码的的 rn between and 就可以了
5. 总练习
- 查询工资高于编号为7782的员工工资,并且和7369号员工从事相同工作的员工的编号、姓名及工资
select empno, ename, sal
from emp
where sal > ( select sal from emp where empno = 7782)
and job = ( select job from emp where empno = 7369 );
- 查询工资最高的员工姓名和工资
select ename, sal
from emp
where sal = ( select max(sal) from emp );
- 查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。
select emp.deptno, dname, min(sal)
from emp,dept
where emp.deptno = dept. deptno
group emp.deptno, dname
having min(sal) > ( select min(sal) from emp where deptno = 10 )
- 查询员工工资为其部门最低工资的员工的编号和姓名及工资
--- 普通子查询
select empno, ename, sal
from emp,
( select deptno, min(sal) min_sal from emp group by deptno ) dept_min
where emp.deptno = dept_min.deptno
and sal = dept_min.min_sal;
--- 相关子查询
select empno, ename, sal
from emp e
where sal = ( select min(sal) from emp where deptno = e.deptno )
- 显示经理是KING的员工姓名,工资
select ename, sal
from emp
where mgr = ( select empno
from emp
where ename = 'KING');
- 显示比员工SMITH参加工作时间晚的员工姓名,工资,参加工作时间
select ename, sal, hiredate
from emp
where hiredate > (select hiredate
from emp
where ename = 'SMITH');
- 使用子查询的方式查询哪些职员在NEW YORK工作
select *
from emp
where deptno = ( select deptno
from dept
where loc = 'NEW YORK' );
- 写一个查询显示和员工SMITH工作在同一个部门的员工姓名,雇用日期,查询结果中排除SMITH
select ename, hiredate
from emp
where deptno in ( select deptno
from emp
where ename = 'SMITH')
and ename != 'SMITH';
- 写一个查询显示其工资比全体职员平均工资高的员工编号、姓名
select empno, ename
from emp
where sal > ( select avg(sal) from emp );
- 写一个查询显示其上级领导是KING的员工姓名、工资
select ename, sal
from emp
where mgr = ( select empno from emp where ename = 'KING' );
- 显示所有工作在RESEARCH部门的员工姓名,职位
select ename, job
from emp
where deptno = ( select deptno from dept where dname = 'RESEARCH' );
- 查询每个部门的部门编号、平均工资,要求部门的平均工资高于部门20的平均工资
select deptno, avg(sal)
from emp
where deptno is not null
group by deptno
having avg(sal) > ( select avg(sal)
from emp
where deptno = 20);
- 查询大于自己部门平均工资的员工姓名,工资,所在部门平均工资,高于部门平均工资的额度
select ename, sal, avg_sal, sal - avg_sal
from emp,
(select deptno, avg(sal) as avg_sal
from emp
group by deptno) a
where emp.deptno = a.deptno
and sal > avg_sal;
- 列出至少有一个雇员的所有部门
select dept.deptno
from emp, dept
where emp.deptno = dept.deptno
group by dept.deptno
having count(*) > 0;
- 列出薪金比"SMITH"多的所有雇员
select emp.*
from emp
where sal > ( select sal
from emp
where ename = 'SMITH' );
- 列出入职日期早于其直接上级的所有雇员
select emp1.*
from emp emp1, emp emp2
where emp1.mgr = emp2.empno
and emp1.hiredate < emp2.hiredate
- 找员工姓名和直接上级的名字
select emp1.ename, emp2.ename
from emp emp1, emp emp2
where emp1.mgr = emp2.empno
- 显示部门名称和人数
select dname, count(*)
from emp, dept
where emp.deptno = dept.deptno
group by dname;
- 显示每个部门的最高工资的员工
select emp.*
from emp,
( select deptno, max(sal) max_sal
from emp
group by deptno) a
where emp.deptno = a.deptno
and sal = max_sal;
- 显示出和员工号7369部门相同的员工姓名,工资
select ename, sal
from emp
where deptno = ( select deptno
from emp
where empno = 7369 );
- 显示出和姓名中包含"W"的员工相同部门的员工姓名
select ename
from emp
where deptno in ( select deptno
from emp
where ename like '%W%' );
- 显示出工资大于平均工资的员工姓名,工资
select ename, sal
from emp
where sal > ( select avg(sal) from emp );
- 显示出工资大于本部门平均工资的员工姓名,工资
select ename, sal
from emp,
( select deptno, avg(sal) avg_sal
from emp
group by deptno ) a
where emp.deptno = a.deptno
and sal > avg_sal;
- 显示每位经理管理员工的最低工资,及最低工资者的姓名
select mgr_id, ename, min_sal
from emp,
( select emp2.empno mgr_id, min( emp1.sal ) min_sal
from emp emp1, emp emp2
where emp1.mgr = emp2.empno
group by emp1.mgr, emp2.empno )
where emp.mgr = mgr_id
and sal = min_sal
order by mgr_id;
- 显示比工资最高的员工参加工作时间晚的员工姓名,参加工作时间
select ename, hiredate
from emp
where hiredate > all ( select hiredate
from emp
where sal = ( select max(sal) from emp )
and hiredate is not null );
- 显示出平均工资最高的的部门平均工资及部门名称
select dname
from emp, dept
where emp.deptno = dept.deptno
group by deptno
having avg(sal) = ( select max( avg(sal) )
from emp
where deptno is not null
group by deptno );