复杂SQL语句练习【III】

select e1.ename,e2.ename from emp e1, emp e2 where e1.mgr = e2.empno

select e1.ename,e2.ename
from emp e1
inner join emp e2
on e1.mgr = e2.empno
--1.列出所有雇员的姓名及其直接上级的姓名
  select e1.ename,e2.ename from emp e1
  inner join emp e2
  on e1.mgr=e2.empno
  
  select d.dname,e.ename from emp e,dept d where e.deptno = d.deptno(+)
  
  select d.dname,e.ename
  from emp e
  left outer join dept d
  on e.deptno = d.deptno
--2.列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门;
  select * from emp e
  right join dept d
  on e.deptno=d.deptno
  
  select e.ename from emp e
  inner join dept d
  on e.deptno = d.deptno
  where d.loc = 'NEW YORK'
  
  select e.ename from emp e ,dept d where e.deptno = d.deptno and d.loc='NEW YORK'
--3.显示所有部门在"洛阳"(dept表 loc字段)的员工姓名
3.显示所有部门在"NEW YORK"(dept表 loc字段)的员工姓名
  select * from emp e
  inner join dept d
  on e.deptno=d.deptno
  inner join city c
  on d.loc=c.cid
  where c.cname='洛阳'
  
  
  select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno and e.ename = 'WARD'
  
  select e.ename ,d.dname 
  from emp e
  inner join dept d
  on e.deptno = d.deptno
  where ename = 'WARD'
--4.显示员工"WARD"的姓名，部门名称
select e.ename,d.dname from emp e
inner join dept  d
on e.deptno=d.deptno
where e.ename='WARD'
select e.ename,d.dname,e.sal,s.grade from emp e,dept d,salgrade s where e.deptno = d.deptno and e.sal> s.losal and e.sal < s.hisal and s.grade >'4'
select e.ename,d.dname,e.sal,s.grade from emp e
inner join dept d
on e.dep