Oracle 常用语法练习

用的是自带的emp,salgrade,dept表,Oracle11G下测试通过。

-- 1.列出所有员工及直接上级的姓名
select  e.ename,nvl(m.ename, ' 无 ' )
from  emp e,emp m
where  e.mgr  =  m.empno( + );

-- 2.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
select  e.empno,e.ename,d.dname
from  emp e,emp m,dept d
where  e.mgr  =  m.empno( + )  and  e.deptno  =  d.deptno  and  e.HIREDATE  <  m.hiredate;

-- 3.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.
select  d.dname,nvl(e.ename, ' 无 ' )
from  emp e,dept d
where  e.deptno( + )  =  d.deptno;

-- 4.列出至少有一个员工的所有部门
select  d.dname, count (e.empno)
from  emp e,dept d
where  e.deptno  =  d.deptno  group   by  d.dname  having   count (e.empno)  >   0 ;

-- 5.列出薪金比"SMITH"多的所有员工信息.
select   *   from  emp  where  sal  >
( select  sal  from  emp  where  ename  =   ' SMITH ' );

-- 6.列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.
select  e.ename,d.dname,ed.cc
from  emp e,dept d,( select  deptno, count (empno) cc  from  emp  group   by  deptno) ed
where  e.deptno  =  d.deptno  and  d.deptno  =  ed.deptno  and  e.job  =   ' CLERK ' ;

select   *   from  emp  where   JOB  =   ' CLERK '  ;
-- 7.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数.
select  e.job, count (e.empno)
from  emp e  where  e.job  in
( select  job
from  emp  group   by  job  having   min (sal) > 1500 )
group   by  e.job;

-- 8.列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号.
select  ename  from  emp  where  deptno  =  
( select  deptno  from  dept  where  dname  =   ' SALES ' );

-- 9.列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.
select  ename 姓名,d.dname 部门名称,nvl(( select  m.ename  from  emp m  where  e.mgr  =  m.empno), ' 无 ' ) 上级领导,
( select  grade  from  salgrade  where  e.sal  between  losal  and  hisal) 工资等级 
from  emp e,dept d
where  e.deptno  =  d.deptno  and  e.sal  >
( select   avg (sal)  from  emp);

-- 或
select  e.ename 姓名,d.dname 部门名称,nvl(m.ename, ' 无 ' ) 上级领导,s.grade 工资等级
from  emp e,dept d,emp m,salgrade s
where  e.deptno  =  d.deptno  and  e.sal  >
( select   avg (sal)  from  emp)  and  e.deptno  =  d.deptno  and  e.mgr  =  m.empno( + )
and  e.sal  between  losal  and  hisal;

-- 10.列出与"SCOTT"从事相同工作的所有员工及部门名称.
select  e.ename,d.dname  from  emp e,dept d
where  e.deptno  =  d.deptno  and  e.job  =
( select  job  from  emp  where  ename  =   ' SCOTT ' )  and  e.ename  <>   ' SCOTT ' ;

-- 11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金.
select  ename,sal  from  emp  where  sal  in
( select  sal  from  emp  where  deptno  =   30 )  and  deptno  <>   30 ;

-- 12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称.
select  e.ename,e.sal,d.dname 
from  emp e,dept d
where  e.deptno  =  d.deptno  and  e.sal  >   all  ( select  sal  from  emp  where  deptno  =   30 )  and  e.deptno  <>   30 ;

-- 13.列出在每个部门工作的员工数量,平均工资和平均服务期限.
select  d.dname 部门名称,ed.c 员工数量, round (ed.a, 2 ) 平均工资, round (ed.h, 0 ) 服务期限
from  dept d
,( select  deptno, count (empno) c, avg (sal) a, avg (months_between(sysdate,hiredate) / 12 ) h  from  emp  group   by  deptno) ed
where  d.deptno  =  ed.deptno;

-- 或
select  d.dname 部门名称, count (e.empno) 员工数量, round ( avg (e.sal), 2 ) 平均工资, round ( avg (months_between(sysdate,hiredate) / 12 ), 0 ) 服务期限
from  emp e,dept d
where  e.deptno  =  d.deptno  group   by  d.dname;

-- 14.列出所有员工的姓名、部门名称和工资。
select  e.ename,d.dname,e.sal
from  emp e,dept d
where  e.deptno  =  d.deptno;

-- 15.列出所有部门的详细信息和人数
select  d. * ,( select   count (e.empno)  from  emp e  where  e.deptno  =  d.deptno) 人数
from  dept d;
-- 或
select  d. * ,nvl(ed.c, 0 ) 人数
from  dept d,( select  deptno, count (empno) c  from  emp  group   by  deptno) ed
where  d.deptno  =  ed.deptno( + );

-- 16.列出各种工作的最低工资及从事此工作的雇员姓名
select   *   from  emp  where  sal  in
( select   min (sal) sal  from  emp  group   by  job)

-- 17.列出各个部门的MANAGER(经理)的最低薪金
select  deptno, min (sal)  from  emp  where  job  =   ' MANAGER '   group   by  deptno;

-- 18.列出所有员工的年工资,按年薪从低到高排序
select  ename, (sal  +  nvl(comm, 0 ))  *   12  income  from  emp  order   by  income;

-- 19.查出某个员工的上级主管，并要求出这些主管中的薪水超过3000.
select   distinct  m.ename 主管, m.sal 主管工资
from  emp e,emp m
where  e.mgr  =  m.empno  and  m.sal  >   3000 ;

-- 20.求出部门名称中,带'S'字符的部门员工的工资合计、部门人数.
select  d.dname 部门, count (e.sal) 工资合计, count (e.empno) 部门人数
from  emp e,dept d
where  e.deptno( + )  =  d.deptno  and  d.dname  like   ' %S% '   group   by  d.dname;

-- 21.给任职日期超过10年的员工加薪10%.
update  emp  set  sal  =  sal  +  sal  *   0.1   where  (months_between(sysdate,hiredate) / 12 )  >   10 ;

々上善若水々 2009-04-08 16:38 发表评论