oracle练习题(同名34003)

oracle练习题(同名24002)oracle练习题查询练习一--查询姓名首字母为“A”或第二个字符为“A”的所有员工信息SELECT*FROMempWHEREenameLIKE'A%'      ORenameLIKE'_A%';--查询部门20和30中的、岗位不是“CLERK”或“SALESMAN”的所有员工信息SELECT*FROMempWHEREjob!='CLERK'      ANDjob!='SALESMAN'      ANDdeptnoIN(20,30);--查询出工资在2500-3500之间，1981年入职的，没有奖金的所有员工信息SELECT*FROMempWHEREEXTRACT(YEARFROMhiredate)=1981      ANDsalBETWEEN2500AND3000      ANDcommISNULL;--查询比平均员工工资高的员工信息SELECTdeptno,dname,locFROMdeptWHEREdeptnoIN(      SELECTdeptno      FROMemp      GROUPBYdeptno      HAVINGAVG(sal)>2000);--查询平均工资高于2000的部门信息selectdeptno,dname,locfromdeptwheredeptnoin(selectdeptno               fromemp               groupbydeptno               havingavg(sal)>2000);--查询出ward的工作所在地SELECTlocFROMdeptWHEREdeptnoIN(      SELECTdeptno      FROMemp      WHEREename='WARD');--查询出工资比ADAMS高的所有人姓名、部门、所在地SELECTa.ename,b.dname,a.salFROMempa,deptbWHEREa.deptno=b.deptno      ANDa.sal>(            SELECTsal            FROMemp            WHEREename='ADAMS'      );--查询出工资排名第7的员工信息SELECT*FROM(      SELECTrank()OVER(ORDERBYsalDESC)ASrk,emp.*      FROMemp)WHERErk=7;/*minue:两个结果值相减， uniou:两个结果集拼到一起 （1~7）-（1~6）=排名第7*/--查询与部门20岗位不同的员工工资SELECTsal,job,deptnoFROMempWHEREjobNOTIN(      SELECTjob      FROMemp      WHEREdeptno=20);--验证--20部门的岗位/*selectjobfromempwheredeptno=20;*/--查询与smith部门岗位完全相同的员工姓名、工作、工资SELECT*FROMempWHEREdeptnoIN(            SELECTdeptno            FROMemp            WHEREename='SMITH'      )      ANDjobIN(            SELECTjob            FROMemp            WHEREename='SMITH'      ); --查询emp 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 中的所有信息 select* fromemp; --查询emp表中的员工姓名和工资 selectename,sal fromemp; --查询emp表中部门编号为20的并且sal大于3000的所有员工信息 select* fromemp wheredeptno=20andsal>3000;  --查询emp表中部门编号为20的或者sal大于3000的所有员工信息  select* fromemp wheredeptno=20orsal>3000; --使用betweenand查询工资在2000到4000之间的员工 select* fromemp wheresalbetween2000and4000; --使用in查询部门编号10，20的所有员工 select* fromemp wheredeptnoin(10,20); --使用like查询所有名字中包括W的员工信息 select* fromemp whereenamelike'%W%'; --使用like查询所有员工名字中的第二子字母为W的员工信息 select* fromemp whereenamelike'_W%'; --查询所有员工信息并按照部门编号和工资进行排序 select* fromemp orderbydeptno,sal; --显示员工共工资上浮20%的结果 selectsal+sal*0.2 fromemp; /*最后一题的另一种思路  minue:两个结果值相减， uniou:两个结果集拼到一起 （1~7）-（1~6）=排名第7*/--11显示emp表的员工姓名以及工资和奖金的和--12显示dept表的内容，使用别名将表头转换成中文显示--13查询员工姓名和工资，并按工资从小到大排序--14查询员工姓名和雇佣日期，并按雇佣日期排序，后雇佣的先显示--15查询员工信息，先按部门标号从小到大排序，再按雇佣日期的先后排序多表查询练习/*多表查询练习*//*多表查询练习*/select*fromempwherejob='MANAGER';select*fromdept;select*fromsalgrade;--列出在部门sales工作的员工的姓名selectename fromempa,deptb whereb.dname='SALES'  anda.deptno=b.deptno;--列出所有员工的姓名，部门名称和工资selectename,dname,salfromempa,deptbwherea.deptno=b.deptno;--列出所有部门的详细信息和部门人数select* fromdept fulljoin(selectdeptno,count(*)fromempgroupbydeptno)bondept.deptno=                                                                   b.deptno;--列出各个部门职位为manager的最低薪金selectdeptno,min(sal)最低薪金 fromemp wherejobin'MANAGER' groupbydeptno;--查询出部门人数至少是1的部门名字selectdname fromdept fulljoin(selectdeptno,count(*)人数fromempgroupbydeptno)bondept.deptno=                                                                      b.deptno where人数>=1;--列出工资比smith多的员工select*fromempwheresal>(selectsalfromempwhereename='SMITH');--列出所有员工的对应领导的姓名selecta.*,b.ename领导fromempaleftjoinempbonb.empno=a.mgr;--求出某个员工的领导，并要求这些领导的薪水高于或等于3000selecta.ename,a.sal fromempa,empb wherea.empno=b.mgr  anda.sal>=3000;--列出部门名称，和这些部门的员工信息selectdname,a.*fromempa,deptbwherea.deptno=b.deptno;--列出所有职位为clerk的员工姓名及其部门名称，部门的人数SELECTename,dname,a.*,job FROMemp,deptb FULLJOIN(SELECTdeptno,COUNT(*)AS人数FROMempGROUPBYdeptno)aONb.deptno=                                                                         a.deptno WHEREemp.deptno=a.deptno  ANDjob='CLERK';--列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级@selecte1.ename,dname,e2.ename领导,e1.sal，s.grade fromempe1,deptd,empe2,salgrades wheree1.mgr=e2.empno(+)  ande1.deptno=d.deptno  ande1.sal>(selectavg(sal)fromemp)  ande1.salbetweens.losalands.hisal;--列出与scott从事相同工作的所有员工共及部门名称selectename,dname fromdeptd,empe whered.deptno=e.deptno  ande.job=(selectjobfromempwhereename='SCOTT');--列出薪金大与部门30中的任意员工的薪金的所有员工的姓名和薪金selectename,sal fromemp wheresal>ANY(selectsalfromempwheredeptno=30);--列出薪金大雨部门30中的全部员工的信息的所有员工的姓名和薪金，部门名称SELECTe.*,dname FROMempe,deptd WHEREsal>ALL(SELECTsalFROMempWHEREdeptno=30)  ANDd.deptno=e.deptno;--列出每个部门的员工数量，平均工资SELECTcount(*)人数,avg(sal),deptnoFROMempGROUPBYdeptno;--列出每个部门的员工数量，平均工资和平均服务期限（月）@selectdeptno,      count(*)员工数量,      trunc(avg(sal+nvl(comm,0)))平均工资,      to_char(to_date('0001-01','yyyy-mm')+avg(sysdate-hiredate)-366-31,              'yy"年"mm"月')平均服务期限 fromemp groupbydeptno;/*selectsysdatesysdate+(sysdate-hiredate),fromemp*/--列出各种工作的最低工资及从事工资最低工资的雇员名称@selecte.ename,a.* fromempe rightjoin(selectmin(sal)最低工资,jobfromempgroupbyjob)aone.job=                                                                  a.job wheree.sal=a.最低工资;--求出部门名称带字符‘S’的部门员工，工资合计，部门人数@selectdname,a.* fromdeptd fulljoin(selectsum(sal)工资合计,deptnofromempgroupbydeptno)aond.deptno=                                                                        a.deptno wherednamelike'%S%';--求出部门平均工资以及等级selectd.dname,b.*,s.grade fromsalgrades,      deptd,      (selectavg(sal)平均工资,deptnofromempgroupbydeptno)b whered.deptno(+)=b.deptno  andb.平均工资betweens.losalands.hisal;--不使用函数查询工资最高人的信息select*fromempwheresal>=all(selectsalfromemp);--求出平均工资最高的部门名称selectd.dname,b.平均工资 fromdeptd,      (selectavg(sal)平均工资,deptnofromempgroupbydeptno)b whered.deptno=b.deptno  andb.平均工资>=all (selectavg(sal)平均工资fromempgroupbydeptno);--求平均工资的等级最低的部门名称@selectdname,b.grade fromdept,      (selectgrade,deptno         fromsalgrade,              (selectdeptno,avg(sal)平均工资fromempegroupbydeptno)a        wherea.平均工资betweenlosalandhisal)b whereb.grade=      (selectmin(grade)         fromsalgrade,              (selectdeptno,avg(sal)平均工资fromempegroupbydeptno)a        wherea.平均工资betweenlosalandhisal)  andb.deptno=dept.deptno;--部门经理人中平均工资最低的部门名称selectdname,a.* fromdept,      (selectavg(sal),rank()over(orderbyavg(sal))rk,deptno         fromemp        wherejob='MANAGER'        groupbydeptno)a wherea.rk=1  anddept.deptno=a.deptno;selectsysdate,sysdate-hiredate,sysdate+(sysdate-hiredate)fromempselectdeptno,count(*)员工数量,trunc(avg(sal+nvl(comm,2)))平均工资,to_char(to_date('0001-01','yyyy-mm')+avg(sysdate-hiredate)-366-31,'yy"年"mm"月')平均服务期限fromempgroupbydeptno;to_char(to_date('0001-01','yyyy-mm')+avg(sysdate-hiredate)-366-31,'ddddd"天')平均服务期限pl/sql练习题--1.自定义输入任意员工编号，输出该员工编号、姓名、工资、部门名称、所在地declare empnointeger; enamevarchar2(10); sal  integer; dnamevarchar2(20); loc  varchar2(20); i    integer;begin selectempno,ename,sal,dname,loc   intoempno,ename,sal,dname,loc   fromemp,dept  whereemp.deptno=dept.deptno    andempno=&i;    dbms_output.put_line('姓名：'||ename||chr(13)||'工资:'||sal||chr(13)||'部门名称：'||dname||chr(13)||'所在地:'||loc); exception whenno_data_foundthen dbms_output.put_line('工号不存在'); end; --2.自定义输入任意员工编号，如果该员工入职时间大于10年，则奖金加1W，如果该员工入职时间大于五年，奖金加5000，否则奖金不加，最终输出员工编号、姓名、入职时间、原奖金、现奖金--【--第六天1.2】declare empno   integer; ename   varchar2(10); hiredatedate; comm    integer; comm1   integer; years   int; i       number;begin i:=&i; selectempno,ename,hiredate,comm   intoempno,ename,hiredate,comm   fromemp  whereempno=i; selectmonths_between(sysdate,hiredate)   intoyears   fromemp  whereempno=i; dbms_output.put_line('员工编号：'||empno||chr(13)||'姓名：'||ename||                      chr(13)||'入职时间'||hiredate||chr(13)||'原奖金'||comm||                      chr(13)); ifyears/12>10then   selectcomm+10000intocomm1fromempwhereempno=i; elsifyears/12>5then   selectcomm+5000intocomm1fromempwhereempno=i;  endif; dbms_output.put_line('现奖金'||comm1);end;--3.自定义输入部门编号，查询出该部门编号下所有员工信息（姓名、工资、部门编号），并显示信息条数declare ename emp.ename%type; sal   emp.sal%type; deptnoemp.deptno%type; i     number:=&i; cursorc1is   selectename,sal,deptnofromempwheredeptno=i;begin openc1; loop   fetchc1     intoename,sal,deptno;   ifc1%foundthen     dbms_output.put_line('姓名'||ename||chr(13)||'工资'||sal||                          chr(13)||'部门编号：'||deptno||chr(13));   else     dbms_output.put_line('查询完毕！');     dbms_output.put_line('共有'||c1%rowcount||'条 记录 混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载 ');     exit;   endif; endloop; closec1;end;--4.自定义输入员工编号，若该员工工资低于5000，则加奖金500l；--若员工工资高于5000，则加奖金100；--最终输出员工编号、姓名、工资、原奖金、先奖金declare empnoemp.empno%type; enameemp.ename%type; sal  emp.sal%type; comm emp.comm%type; comm1emp.comm%type; i    number:=&i;begin selectempno,ename,comm,sal   intoempno,ename,comm,sal   fromemp  whereempno=i; dbms_output.put_line('员工编号：'||empno||chr(13)||'姓名：'||ename||                      chr(13)||'原奖金:'||nvl(comm,0)||chr(13)); ifsal<5000then   selectnvl(comm,0)+5001intocomm1fromempwhereempno=i; else   selectnvl(comm,0)+100intocomm1fromempwhereempno=i;  endif; dbms_output.put_line('现奖金'||comm1);exception whenno_data_foundthen   dbms_output.put_line('工号不存在');end;--5.对每位员工的薪水进行判断，如果该员工薪水高于其所在部门的平均薪水，则将其薪水减50，更新前后的薪水，员工姓名，所在部门编号declare t_ename emp.ename%type; t_sal   emp.sal%type; sal1    emp.sal%type; t_deptnoemp.deptno%type; avsal   emp.sal%type; cursorc_1is   selectename,sal,deptnofromemp;begin openc_1; loop   fetchc_1     intot_ename,t_sal,t_deptno;   exitwhenc_1%notfound;   selectavg(sal)intoavsalfromempwheredeptno=t_deptno;   ift_sal>avsalthen     sal1:=t_sal-50;   else     sal1:=t_sal;   endif;   dbms_output.put_line('员工姓名：'||t_ename||chr(13)||'部门编号'||                        t_deptno||chr(13)||'原薪水：'||t_sal||chr(13)||                        '现薪水'||sal1||chr(13)); endloop; closec_1;end;--6.创建一个存储过程，实现：通过输入员工编号查看员工姓名、工资、奖金：--1.1如果输入的编号不存在，进行异常处理；--1.2如果工资高于4000，进行异常处理提示；--1.3如果奖金没有或为0，进行异常处理提示createorreplaceprocedureproc(t_empnoinemp.empno%type)as t_commemp.comm%type; t_sal emp.sal%type; t_nameemp.ename%type; i     number;begin selectcount(*)intoifromempwhereempno=t_empno; ifi=0then   RAISE_application_error(-20114,'编号不存在'); else   selectcomm,sal,ename     intot_comm,t_sal,t_name     fromemp    whereempno=t_empno; endif; ift_sal>4000then   RAISE_application_error(-20112,'工资高于4000'); elsift_comm=0ort_commisnullthen   RAISE_application_error(-20113,'奖金为空'); else   dbms_output.put_line('姓名：'||t_name||chr(13)||'工资：'||t_sal||                        chr(13)||'奖金：'||t_comm); endif;endproc;callproc(654);