Oracle例题（三）

备注：以下例题均基于Oracle的scott账户中的表

1、使用in查询部门编号为10,20的所有员工

select * from emp where deptno in(10,20);

2、使用like查询所有名字中包括w的员工信息

select * from emp where ename like '%W%';

3、使用like查询所有员工名字中第二个子字母为w的信息

select * from emp where ename like '_M%';

4、查询所有员工信息并按部门编号和工资进行排序

select * from emp  order by deptno,sal ASC;

select * from emp  order by 1,2 ASC;

5、显示员工工资上浮20%的结果

select ename, sal 原工资 , sal*1.2 上浮后的工资 from emp;

6、显示emp表的员工姓名以及工资和奖金的和

select ename, sal+nvl(comm,0) from emp;

7、显示dept表的内容，使用别名叫表头转换成中文显示

select deptno 部门编号, dname 部门名字, loc 部门地点 from dept;

8、查询员工姓名和工资，并按工资从小到大排序

select ename, sal from emp order by sal ASC;

9、查询员工姓名和雇佣日期，并按雇佣日期排序，后雇佣的先显示

select ename, hiredate from emp order by hiredate DESC;

10、查询员工信息，先按部门标号从小到大排序，再按雇佣时间的先后排序

select * from emp order by deptno,hiredate asc;

11、列出在部门SALES工作的员工姓名

select ename
  from emp
 where deptno in (select deptno from dept where dname = 'SALES');

 select emp.ename
   from emp, dept
  where emp.deptno = dept.deptno
    and dept.dname = 'SALES';

12、列出所有员工的姓名，部门和工资

select emp.ename, dept.dname, emp.sal
  from emp, dept
 where emp.deptno = dept.deptno;

13、列出所有部门的详细信息和部门人数

select dept.*, count(ename)
  from emp, dept
 where emp.deptno(+) = dept.deptno
 group by dept.deptno, dept.dname, dept.loc;

14、列出各个部门职位为Manager的最低薪金

 select deptno, min(sal) from emp where job = 'MANAGER' group by deptno;

15、查询部门人数至少是1的部门名字

select dept.dname
  from emp, dept
 where emp.deptno = dept.deptno
 group by dept.dname
having count(ename) >= 1;

16、列出工资比SMITH多的员工

select ename
  from emp
 where sal > (select sal from emp where ename = 'SMITH');

17、列出所有员工的对应领导的姓名

select e1.ename, e2.ename 领导 from emp e1 left join emp e2 on e1.mgr = e2.empno;

18、求出某个员工的领导，并要求这些领导的薪水高于或等于3000

select distinct e.e2mgr
  from (select e1.ename, e2.ename e2mgr, e2.sal e2sal
          from emp e1
          left join emp e2 on e1.mgr = e2.empno) e
 where e.e2sal >= 3000

19、列出部门名称，和这些部门的员工信息

select dept.dname,
       dept.deptno,
       emp.empno,
       emp.ename,
       emp.job,
       emp.mgr,
       emp.hiredate,
       emp.sal,
       emp.comm
  from dept
  left join emp on emp.deptno = dept.deptno;

20、列出所有职位为CLERK的员工姓名及部门名称，部门人数

select ename, dname, enum, job
  from emp
  left join (select dept.deptno, dname, count(ename) as enum
               from emp, dept
              where emp.deptno = dept.deptno
              group by dept.deptno, dname) a on emp.deptno = a.deptno
 where job = 'CLERK';