oracle练习5

1. 自连接查询（显示某员工的上级信息）
   select * from emp where empno=(select mgr from emp where ename=’SMITH’);
   
2. 列出薪金比“SMITH”多的所有员工。(经典的自连接查询)
   select e.ename,e.sal from emp e where sal> (select sal from emp where ename=’SMITH’);
   

3. 列出所有员工的姓名及其直接上级的姓名。
   select e.ename as “员工姓名”,m.ename as “上级姓名” from emp e,emp m where e.mgr=m.empno(+);
   

4. 列出受雇日期早于其直接上级的所有员工。
   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;
   

5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门(以emp表为主，左连接查询)
   select e.ename as “员工姓名”,d.dname “部门名称” from dept d left join emp e on d.deptno=e.deptno;
   
6. 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。(经典的两个表连接)
   select e.ename as “员工姓名”,d.dname as “部门名称” from emp e,dept d where e.deptno=d.deptno and d.dname=’SALES’;
   
7. 查询员工编号,员工名,上级编号,上级名称
   select e.empno as “员工编号”,e.ename as “员工姓名”,m.empno as “上级编号”,m.ename as “上级姓名”
   from emp e,emp m where e.mgr=m.empno(+);
   
8. 查询员工编号,员工名,所在部门名,上级编号,上级名称
   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;
   
9. 列出所有雇员的姓名及其直接上级的姓名
   select e.ename as “员工姓名”,m.ename as “上级姓名” from emp e,emp m where e.mgr=m.empno(+);
   
10. 列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门
    select e.ename as “员工姓名”,d.dname “部门名称” from dept d left join emp e on d.deptno=e.deptno;
    
11. 显示所有部门在”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’;
    
12. 显示员工”SMITH”的姓名，部门名称
    select e.ename as “员工姓名”,d.dname as “部门名称”
    from emp e,dept d where e.deptno=d.deptno and e.ename=’SMITH’;
    
13. 显示员工姓名，部门名称，工资，工资级别(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;
    
14. 显示员工”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’;
    
15. 显示员工名，参加工作时间，经理名，参加工作时间：参加工作时间比他的经理早
    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;