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
复杂SQL语句练习【III】
最新推荐文章于 2024-04-07 23:56:42 发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)