Oracle 练手系列（一）
1．列出至少有一个员工的所有部门。第一种
select dname from dept
where exists (
select * from emp where emp.deptno = dept.deptno
);
第二种
select dname from dept where deptno in(select deptno from emp);
第三种
select dname
from dept
where deptno in
(select deptno from emp group by deptno having count(deptno) >= 1);
2．列出薪金比“SMITH”多的所有员工。
第一种
SELECT * FROM EMP WHERE SAL > (
select SAL from emp where ename='SMITH');
第二种
select * from emp
where exists (
select * from emp e2 where e2.sal < emp.sal and e2.ename = 'SMITH'
);
第三种
SELECT * FROM EMP WHERE SAL >any (
select SAL from emp where ename='SMITH');
--3．列出所有员工的姓名及其直接上级的姓名。
第一种
select emp.ename, boss.ename boss_name from emp
left join emp boss on boss.empno= emp.mgr;
第二种
select emp.ename, (select ename from emp boss where boss.empno = emp.mgr) as boss_name
from emp;
--------4．列出受雇日期早于其直接上级的所有员工。----------
第一种
select a.ename from emp a where a.hiredate1500;
第二种
select distinct emp.job from emp,
(
select distinct emp.job, min(emp.sal)over(partition by emp.job) as v from emp
) twhere t.job = emp.job and t.v >1500
8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。----------
第一种
select ename from emp where deptno=(select deptno from dept where dname='SALES');
第二种
select emp.ename from emp
left join dept on dept.deptno = emp.deptno
where dept.dname = 'SALES';
第三种
select ename from emp
where exists
(select deptno from dept where dept.deptno = emp.deptno and dept.dname='SALES');
页:
[1]