列出所有员工的姓名及其直接上级的姓名。 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;