oracle练习5
- 自连接查询（显示某员工的上级信息）
select * from emp where empno=(select mgr from emp where ename=’SMITH’); - 列出薪金比“SMITH”多的所有员工。(经典的自连接查询)
select e.ename,e.sal from emp e where sal> (select sal from emp where ename=’SMITH’); 列出所有员工的姓名及其直接上级的姓名。
select e.ename as “员工姓名”,m.ename as “上级姓名” from emp e,emp m where e.mgr=m.empno(+);列出受雇日期早于其直接上级的所有员工。
select e.ename as “员工姓名”,e.hiredate as “员工受雇日期”
,m.ename as “上级姓名”,m.hiredate as “上司受雇日期”
from emp e,emp m where e.mgr=m.empno(+) and (e.hiredate-m.hiredate) <0;- 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门(以emp表为主，左连接查询)
select e.ename as “员工姓名”,d.dname “部门名称” from dept d left join emp e on d.deptno=e.deptno; - 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。(经典的两个表连接)
select e.ename as “员工姓名”,d.dname as “部门名称” from emp e,dept d where e.deptno=d.deptno and d.dname=’SALES’; - 查询员工编号,员工名,上级编号,上级名称
select e.empno as “员工编号”,e.ename as “员工姓名”,m.empno as “上级编号”,m.ename as “上级姓名”
from emp e,emp m where e.mgr=m.empno(+); - 查询员工编号,员工名,所在部门名,上级编号,上级名称
select e.empno as “员工编号”,e.ename as “员工姓名”,d.dname as “部门名称” ,
m.empno as “上级编号”,m.ename as “上级姓名”
from emp e,emp m,dept d where e.mgr=m.empno(+) and e.deptno=d.deptno; - 列出所有雇员的姓名及其直接上级的姓名
select e.ename as “员工姓名”,m.ename as “上级姓名” from emp e,emp m where e.mgr=m.empno(+); - 列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门
select e.ename as “员工姓名”,d.dname “部门名称” from dept d left join emp e on d.deptno=e.deptno; - 显示所有部门在”NEW YORK”(dept表 loc字段)的员工姓名
select e.ename as “员工姓名”,d.loc as “部门位置”
from emp e,dept d where e.deptno=d.deptno and d.loc=’NEW YORK’; - 显示员工”SMITH”的姓名，部门名称
select e.ename as “员工姓名”,d.dname as “部门名称”
from emp e,dept d where e.deptno=d.deptno and e.ename=’SMITH’; - 显示员工姓名，部门名称，工资，工资级别(salgrade表 grade字段),要求工资级别大于4级
select e.ename as “员工姓名”,d.dname as “部门名称” ,e.sal as “工资”,s.grade as “工资等级”
from emp e,dept d,salgrade s where e.deptno=d.deptno and e.sal between losal and hisal
and s.grade>4; - 显示员工”KING”和”FORD”管理的员工姓名及其经理姓名
select t.mename as “员工姓名”,t.eename as “管理的员工姓名”,n.ename as “经理姓名”
from
(select m.ename as mename,e.ename as eename,m.deptno as mdeptno from emp m,emp e where
m.empno=e.mgr and m.ename in(‘KING’,’FORD’)) t,emp n
where t.mdeptno=n.deptno and n.job=’MANAGER’; - 显示员工名，参加工作时间，经理名，参加工作时间：参加工作时间比他的经理早
select t.eename as “员工姓名”,t.ehiredate as “参加工作时间”,
m.ename as “经理姓名”，m.hiredate as “参加工作时间” from
(select e.ename as eename,e.hiredate as ehiredate,e.deptno as edeptno from emp e) t,emp m
where t.edeptno=m.deptno and m.job=’MANAGER’ and (t.ehiredate-m.hiredate)<0;