oracle练习题

1、页眉内容oracle 练习题查询练习一-查询姓名首字母为“ A”或第二个字符为“A”的所有员工信息 SELECT *FROM empWHERE ename LIKE 'A%'OR ename LIKE '_A%' ;- - 查询部门 20 和 30 中的、岗位不是“ CLERK” 或 “ SALESMA” N 的所有员工信息SELECT *FROM empWHERE job != 'CLERK'AND job !='SALESMAN'AND deptnoIN ( 20 , 30);- - 查询出工资在2500-3500之间， 19

2、81 年入职的，没有奖金的所有员工信息SELECT *FROM empWHERE EXTRACT( YEAR FROM hiredate) =1981AND sal BETWEEN 2500 AND 3000AND comm IS NULL;- - 查询比平均员工工资高的员工信息SELECT deptno, dname, locFROM deptWHERE deptno IN (SELECT deptnoFROM empGROUP BY deptnoHAVING AVG(sal) >2000);- - 查询平均工资高于2000 的部门信息select deptno ,dname,locf

3、rom deptwhere deptno in ( select deptnofrom empgroup by deptnohaving avg (sal)> 2000 );- - 查询出 ward 的工作所在地SELECT locFROM deptWHERE deptno IN (SELECT deptnoFROM empWHERE ename = 'WARD');- - 查询出工资比ADAMS 高的所有人姓名、部门、所在地SELECT a.ename, b.dname, a.salFROM emp a, dept bWHERE a.deptno = b.deptnoA

4、ND a.sal > (SELECT salFROM empWHERE ename = 'ADAMS');- - 查询出工资排名第7 的员工信息SELECT *FROM (SELECT rank() OVER ( ORDER BY sal DESC) AS rk, emp.* FROM emp)WHERE rk =7;/*minue:两个结果值相减，uniou:两个结果集拼到一起(17 ) - ( 16 ) =排名第 7- /- - 查询与部门 20 岗位不同的员工工资SELECT sal, job,deptnoFROM empWHERE job NOT IN (SELE

5、CT jobFROM empWHERE deptno =20);- - 验证 -20 部门的岗位/*select jobfrom empwhere deptno=20;*/- - 查询与 smith 部门岗位完全相同的员工姓名、工作、工资SELECT *FROM empWHERE deptno IN (SELECT deptnoFROM empWHERE ename = 'SMITH')AND job IN (SELECT jobFROM empWHERE ename = 'SMITH'）;- - 查询 emp 表中的所有信息select *from emp;-

6、 - 查询 emp 表中的员工姓名和工资select ename,salfrom emp;大于 3000 的所有员工信息大于 3000 的所有员工信息- - 查询 emp 表中部门编号为 20 的并且 sal select *from empwhere deptno= 20 and sal> 3000 ;- - 查询 emp 表中部门编号为 20 的或者 sal select *from empwhere deptno= 20 or sal> 3000 ;- - 使用 between and 查询工资在 2000 到 4000 之间的员工 select *from empwhere

7、sal between2000 and 4000 ;- - 使用in 查询 部门编号 10 ， 20 的所有员工select *from empwhere deptno in ( 10 , 20 );- - 使用 like 查询所有名字中包括W 的员工信息select *from empwhere ename like '%W%' ;- - 使用 like 查询所有员工名字中的第二子字母为W 的员工信息select *from empwhere ename like '_W%' ;- - 查询所有员工信息并按照部门编号和工资进行排序select *from em

8、porder by deptno,sal ;- 显示员工共工资上浮20% 的结果select sal+sal* 0.2from emp;/* 最后一题的另一种思路minue: 两个结果值相减，uniou: 两个结果集拼到一起（ 17） - （ 16） =排名第7*/-11显示em味的员工姓名以及工资和奖金的和- -12 显示 dept 表的内容，使用别名将表头转换成中文显示- -13 查询员工姓名和工资，并按工资从小到大排序- -14 查询员工姓名和雇佣日期，并按雇佣日期排序，后雇佣的先显示- -15 查询员工信息，先按部门标号从小到大排序，再按雇佣日期的先后排序多表查询练习/* 多表查询练习

9、 */* 多表查询练习 */select*fromempwhere job = 'MANAGER' ;select*fromdept;select*fromsalgrade;- - 列出在部门sales 工作的员工的姓名selectenamefrom emp a, dept bwhere b.dname = 'SALES'and a.deptno = b.deptno;- - 列出所有员工的姓名，部门名称和工资select ename, dname, sal from emp a, dept b where a.deptno = b.deptno;- - 列出所

10、有部门的详细信息和部门人数select *from deptfull join ( select deptno, count (*) from emp group by deptno) b on dept.deptno =.deptno;- - 列出各个部门职位为 manager 的最低薪金select deptno, min (sal) 最低薪金 from empwhere job in 'MANAGER'group by deptno;- - 查询出部门人数至少是1 的部门名字selectdnamefrom deptcount (*) 人数 from emp group b

11、y deptno) bonfulljoin ( select deptno,dept.deptno =b.deptnowhere 人数 >= 1 ;- - 列出工资比smith 多的员工);select * from emp where sal > ( select sal from emp where ename = 'SMITH'- - 列出所有员工的对应领导的姓名select a.*, b.ename 领导 from emp a left join emp b on b.empno = a.mgr;- - 求出某个员工的领导，并要求这些领导的薪水高于或等于30

12、00select a.ename, a.salfrom emp a, emp bwhere a.empno = b.mgr and a.sal >=3000 ;- - 列出部门名称，和这些部门的员工信息select dname, a.* from emp a, dept b where a.deptno = b.deptno;- - 列出所有职位为 clerk 的员工姓名及其部门名称，部门的人数SELECT ename, dname, a.*, jobFROM emp, dept bFULL JOIN ( SELECT deptno, COUNT(*) AS 人数 FROM emp GR

13、OUP BY deptno) a ON b.deptno =a.deptnoWHERE emp.deptno = a.deptnoAND job = 'CLERK' ;- - 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级select e1.ename, dname, e2.ename领导 , e1.sal ， s.gradefrom emp e1, dept d, emp e2, salgrade swhere e1.mgr = e2.empno(+)and e1.deptno = d.deptnoand e1.sal > ( select av

14、g (sal) from emp)and e1.sal between s.losal and s.hisal;- - 列出与scott 从事相同工作的所有员工共及部门名称selectename, dnamefrom dept d, emp ewhere d.deptno = e.deptnoand e.job = ( select job from emp where ename = 'SCOTT' );- - 列出薪金大与部门 30 中的任意员工的薪金的所有员工的姓名和薪金 select ename, salfrom empwhere sal > ANY ( sele

15、ct sal from emp where deptno =30 );- - 列出薪金大雨部门 30 中的全部员工的信息的所有员工的姓名和薪金，部门名称 SELECT e.*, dnameFROM emp e, dept dWHERE sal > ALL ( SELECT sal FROM emp WHERE deptno =30 )AND d.deptno = e.deptno;- - 列出每个部门的员工数量，平均工资SELECT count (*) 人数 , avg (sal), deptno FROM emp GROUP BY deptno;- - 列出每个部门的员工数量，平均工资

16、和平均服务期限(月) select deptno,count (*) 员工数量 ,trunc(avg (sal + nvl(comm,0) 平均工资 ,to_char(to_date('0001-01', 'yyyy-mm' ) + avg ( sysdate - hiredate)- 366 - 31 ,'yy" 年 "mm" 月 ' ) 平均服务期限from empgroup by deptno;/*select sysdate sysdate+(sysdate-hiredate),from emp*/- - 列

17、出各种工作的最低工资及从事工资最低工资的雇员名称select e.ename, a.*from emp eright join ( select min (sal) 最低工资 , job from emp group by job) a on e.joba.jobwhere e.sal = a. 最低工资 ;- -求出部门名称带字符'S'的部门员工，工资合计，部门人数 select dname, a.*from dept dfull join ( select sum (sal) 工资合计 , deptno from emp group by deptno) a on d.de

18、ptno =a.deptnowhere dname like '%S%' ;- - 求出部门平均工资以及等级select d.dname, b.*, s.gradefrom salgrade s,dept d,( select avg (sal) 平均工资 , deptno from emp group by deptno) b where d.deptno(+) = b.deptnoand b. 平均工资 between s.losal and s.hisal;- - 不使用函数查询工资最高人的信息select * from emp where sal >= all (

19、 select sal from emp);- - 求出平均工资最高的部门名称select d.dname, b. 平均工资from dept d,( select avg (sal) 平均工资 , deptno from emp group by deptno) b where d.deptno = b.deptnoand b. 平均工资 >= all( select avg (sal) 平均工资 from emp group by deptno);- - 求平均工资的等级最低的部门名称 select dname, b.gradefrom dept,( select grade, de

20、ptnofrom salgrade,(select deptno, avg (sal) 平均工资 from emp e group bydeptno) awhere a. 平均工资between losal and hisal) bwhere b.grade =( select from(deptno) a wheremin (grade)salgrade, select deptno, avg (sal)a.平均工资between losal平均工资 from emp e groupand hisal)byand b.deptno = dept.deptno;-部门经理人中平均工资最低的部门

21、名称select dname, a.*from dept,( select avg (sal), rank() over(order by avg (sal) rk, deptnofrom empwhere job = 'MANAGER'group by deptno) awhere a.rk =1and dept.deptno = a.deptno;select sysdate,sysdate-hiredate, sysdate+(sysdate-hiredate)from empselect deptno,count(*) 员工数量,trunc(avg(sal+nvl(co

22、mm,2)平均工资，to_char(to_date('0001-01','yyyy-mm') +avg(sysdate-hiredate)-366-31,'yy"年"mm'月') 平均服务期限from emp group by deptno;to_char(to_date('0001-01','yyyy-mm') +avg(sysdate-hiredate)-366-31,'ddddd" 大')平均服务期限pl/sql 练习题-1.自定义输入任意员工编号，输出该

23、员工编号、姓名、工资、部门名称、所在地 declareempnointeger；enamevarchar2(10);salinteger；dnamevarchar2(20);locvarchar2(20);iinteger；beginselect empno, ename, sal, dname, loc into empno, ename, sal, dname, loc from emp,deptwhere emp.deptno = dept.deptno and empno = &i;dbms_output.put_line('姓名：'|ename|chr( 13

24、 )| '工工号不存在);资:'|sal|chr( 13)| '部门名称：|dname|chr( 13)| '所在地：'|loc); exception when no_data_found thendbms_output.put_line(end ;-2.自定义输入任意员工编号，如果该员工入职时间大于10年，则奖金加1W,如果该员工入职时间大于五年，奖金加5000 ，否则奖金不加，最终输出员工编号、姓名、入职时间、原奖金、现奖金- 【- 第六天 1.2 】declareempnointeger;enamevarchar2 ( 10 );hiredate

25、date ;comminteger;comm1integer;yearsint ;inumber ;begini := &i;selectempno, ename, hiredate, commintoempno, ename, hiredate, commfrom empwhere empno = i;select months_between(sysdateinto years from empwhere empno = i;, hiredate)dbms_output.put_line(' 员工编号：|' | empno| chr( 13) | ' 姓名：

26、chr(13 ) |原奖金' | comm |chr(13 );if years /12 > 10 then' 自职时间' | hiredate | chr(| ename13) |'selectcomm + 10000 intoelsif years /12 > 5 thenselectcomm + 5000 intoend if ;comm1 from emp where empno = i;comm1 from emp where empno = i;dbms_output.put_line(现奖金 ' | comm1);end ;-3

27、.自定义输自部门编号，查询出该部门编号下所有员工信息（姓名、工资、部门编号），并显示信息条数declareename emp.ename% type ;sal emp.sal% type ;deptno emp.deptno% type ; inumber := &i;cursor c1 isselectename, sal, deptnofrom emp where deptno = i;beginopen c1;loopfetch c1into ename, sal, deptno;if c1%found thendbms_output.put_line(chr(' 姓名

28、' |13) |ename | chr( 13 ) |' 工资 '' 部门编号： ' |deptno |chr(sal |13);elsedbms_output.put_line( dbms_output.put_line( exit ;' 查询完毕！' );' 共有 ' | c1% rowcount |' 条记录 ' );end if ; end loop ; close c1;end ;- -4. 自定义输入员工编号，若该员工工资低于5000 ，则加奖金 500l- - 若员工工资高于5000 ，则加奖

29、金100 ；- - 最 终输出员工编号、姓名、工资、原奖金、先奖金 declare姓名、工资、原奖金、先奖金empno emp.empno% ename emp.ename%type ;type ;sal emp.sal% comm m% comm1 m%type ;type ;type ;number := &i;beginselectintoempno, ename, comm, sal empno, ename, comm, salfromempwhereempno = i;dbms_output.put_line(|' 员工编号：' | empno| chr(

30、13)| ' 姓名：' |if sal <chr(5000 then13)|' 原奖金 :'| nvl(comm, 0) |chr(selectnvl(comm,0) +5001into comm1from emp where empno =ename13);i;elseselectnvl(comm,0) +100into comm1 from emp where empno = i;end if ;dbms_output.put_line(exceptionwhen no_data_found' 现奖金 '| comm1);then

31、9; 工号不存在 ' );dbms_output.put_line( end ;-5. 对每位员工的薪水进行判断，如果该员工薪水高于其所在部门的平均薪水，则将其薪水减50 ，更新前后的薪水，员工姓名，所在部门编号declaret_ename emp.ename% type ;t_salemp.sal%type;sal1emp.sal%type;t_deptno emp.deptno% type ;avsalemp.sal%type;cursor c_1 isselect ename, sal, deptnofrom emp;beginopen c_1;loopfetch c_1into t_ename, t_sal, t_deptno;exit when c_1% notfound ;select avg (sal) into avsalfrom emp where deptno