Oracle之再展身手 练习题2（多表）

1
2
select e.ename ename,m.ename mname from emp e join emp m on e.mgr=m.empno;

1
2
select d.deptno,e.* from dept d left outer join emp e on e.deptno=d.deptno;

1
2
3
select ename from emp where deptno=
		(select deptno from dept where loc='NEW YORK');

1
2
3
select ename from dept d inner join emp e 
					on e.deptno=d.deptno where loc='NEW YORK';

1
2
3
select e.ename,d.dname from emp e inner join dept d 
	on e.deptno=d.deptno where ename='SMITH';

1
2
3
4
5
6
select e.ename,d.dname,e.sal,s.grade from emp e inner join dept d 
		on e.deptno=d.deptno 
			left outer join salgrade s 
				on e.sal between s.losal and s.hisal 
		where s.grade>4;

1
2
3
select m.ename,e.ename from emp e join emp m 
	on e.mgr=m.empno where m.ename in('KING','FORD');

1
2
3
select e.ename,e.hiredate,m.ename,m.hiredate from emp e join emp m
	 on e.mgr=m.empno where e.hiredate<m.hiredate;

1
2
3
4
5
select sum(sal),count(*)
from emp e where e.deptno in
	(select d.deptno from dept d where d.dname like '%S%')
group by e.deptno;

1
2
select e.*,sal+nvl(comm,0)*12 nx from emp e order by  nx;

1
2
3
4
select e.ename,e.sal,e.job
from emp e,(select min(sal) losal,job from emp group by job) m
where e.sal=m.losal and e.job=m.job;

1
2
3
4
5
select d.*,sump
from dept d left outer join
(select count(*) sump,deptno from emp group by deptno)m
on d.deptno=m.deptno;

1
2
select e.ename,d.dname,e.sal from emp e join dept d on e.deptno=d.deptno;

1
2
3
select ename,(select ename from emp where mgr is null) boss
from emp;

1
2
3
4
select e.empno,e.ename,e.sal,d.dname,s.grade 
from emp e inner join dept d on e.deptno=d.deptno 
inner join salgrade s on e.sal between s.losal and s.hisal;

1
2
select max(sal),min(sal),floor(avg(sal)) from emp  group by job;

1
2
3
select * from dept d where d.deptno in
	(select deptno from emp group by deptno having avg(sal)>2000)