oracle练习题

..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)=1981ANDsalBETWEEN2500AND3000ANDcommISNULL;--查询比平均员工工资高的员工信息SELECTdeptno,dname,locFROMdeptWHEREdeptnoIN(SELECTdeptnoFROMempGROUPBYdeptnoHAVINGAVG(sal)2000);--查询平均工资高于2000的部门信息selectdeptno,dname,locfromdeptwheredeptnoin(selectdeptnofromempgroupbydeptnohavingavg(sal)2000);--查询出ward的工作所在地SELECTlocFROMdeptWHEREdeptnoIN(SELECTdeptno..FROMempWHEREename='WARD');--查询出工资比ADAMS高的所有人、部门、所在地SELECTa.ename,b.dname,a.salFROMempa,deptbWHEREa.deptno=b.deptnoANDa.sal(SELECTsalFROMempWHEREename='ADAMS');--查询出工资排名第7的员工信息SELECT*FROM(SELECTrank()OVER(ORDERBYsalDESC)ASrk,emp.*FROMemp)WHERErk=7;/*minue:两个结果值相减，uniou:两个结果集拼到一起（1~7）-（1~6）=排名第7*/--查询与部门20岗位不同的员工工资SELECTsal,job,deptnoFROMempWHEREjobNOTIN(SELECTjobFROMempWHEREdeptno=20);--验证--20部门的岗位/*selectjobfromempwheredeptno=20;*/--查询与smith部门岗位完全相同的员工、工作、工资SELECT*FROMempWHEREdeptnoIN(SELECTdeptnoFROMempWHEREename='SMITH')ANDjobIN(..SELECTjobFROMempWHEREename='SMITH');--查询emp表中的所有信息select*fromemp;--查询emp表中的员工和工资selectename,salfromemp;--查询emp表中部门编号为20的并且sal大于3000的所有员工信息select*fromempwheredeptno=20andsal3000;--查询emp表中部门编号为20的或者sal大于3000的所有员工信息select*fromempwheredeptno=20orsal3000;--使用betweenand查询工资在2000到4000之间的员工select*fromempwheresalbetween2000and4000;--使用in查询部门编号10，20的所有员工select*fromempwheredeptnoin(10,20);--使用like查询所有名字中包括W的员工信息select*fromempwhereenamelike'%W%';--使用like查询所有员工名字中的第二子字母为W的员工信息select*fromempwhereenamelike'_W%';--查询所有员工信息并按照部门编号和工资进行排序select*fromemporderbydeptno,sal;--显示员工共工资上浮20%的结果selectsal+sal*0.2fromemp;../*最后一题的另一种思路minue:两个结果值相减，uniou:两个结果集拼到一起（1~7）-（1~6）=排名第7*/--11显示emp表的员工以及工资和奖金的和--12显示dept表的容，使用别名将表头转换成中文显示--13查询员工和工资，并按工资从小到大排序--14查询员工和雇佣日期，并按雇佣日期排序，后雇佣的先显示--15查询员工信息，先按部门标号从小到大排序，再按雇佣日期的先后排序多表查询练习/*多表查询练习*//*多表查询练习*/select*fromempwherejob='MANAGER';select*fromdept;select*fromsalgrade;--列出在部门sales工作的员工的selectenamefromempa,deptbwhereb.dname='SALES'anda.deptno=b.deptno;--列出所有员工的，部门名称和工资selectename,dname,salfromempa,deptbwherea.deptno=b.deptno;--列出所有部门的详细信息和部门人数select*fromdeptfulljoin(selectdeptno,count(*)fromempgroupbydeptno)bondept.deptno=b.deptno;--列出各个部门职位为manager的最低薪金selectdeptno,min(sal)最低薪金fromempwherejobin'MANAGER'groupbydeptno;..--查询出部门人数至少是1的部门名字selectdnamefromdeptfulljoin(selectdeptno,count(*)人数fromempgroupbydeptno)bondept.deptno=b.deptnowhere人数=1;--列出工资比smith多的员工select*fromempwheresal(selectsalfromempwhereename='SMITH');--列出所有员工的对应领导的selecta.*,b.ename领导fromempaleftjoinempbonb.empno=a.mgr;--求出某个员工的领导，并要求这些领导的薪水高于或等于3000selecta.ename,a.salfromempa,empbwherea.empno=b.mgranda.sal=3000;--列出部门名称，和这些部门的员工信息selectdname,a.*fromempa,deptbwherea.deptno=b.deptno;--列出所有职位为clerk的员工及其部门名称，部门的人数SELECTename,dname,a.*,jobFROMemp,deptbFULLJOIN(SELECTdeptno,COUNT(*)AS人数FROMempGROUPBYdeptno)aONb.deptno=a.deptnoWHEREemp.deptno=a.deptnoANDjob='CLERK';--列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级selecte1.ename,dname,e2.ename领导,e1.sal，s.gradefromempe1,deptd,empe2,salgradeswheree1.mgr=e2.empno(+)ande1.deptno=d.deptnoande1.sal(selectavg(sal)fromemp)ande1.salbetweens.losalands.hisal;--列出与scott从事相同工作的所有员工共及部门名称selectename,dnamefromdeptd,empewhered.deptno=e.deptnoande.job=(selectjobfromempwhereename='SCOTT');--列出薪金大与部门30中的任意员工的薪金的所有员工的和薪金selectename,salfromempwheresalANY(selectsalfromempwheredeptno=30);..--列出薪金大雨部门30中的全部员工的信息的所有员工的和薪金，部门名称SELECTe.*,dnameFROMempe,deptdWHEREsalALL(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月')平均服务期限fromempgroupbydeptno;/*selectsysdatesysdate+(sysdate-hiredate),fromemp*/--列出各种工作的最低工资及从事工资最低工资的雇员名称selecte.ename,a.*fromemperightjoin(selectmin(sal)最低工资,jobfromempgroupbyjob)aone.job=a.jobwheree.sal=a.最低工资;--求出部门名称带字符‘S’的部门员工，工资合计，部门人数selectdname,a.*fromdeptdfulljoin(selectsum(sal)工资合计,deptnofromempgroupbydeptno)aond.deptno=a.deptnowherednamelike'%S%';--求出部门平均工资以及等级selectd.dname,b.*,s.gradefromsalgrades,deptd,(selectavg(sal)平均工资,deptnofromempgroupbydeptno)bwhered.deptno(+)=b.deptnoandb.平均工资betweens.losalands.hisal;--不使用函数查询工资最高人的信息select*fromempwheresal=all(selectsalfromemp);--求出平均工资最高的部门名称selectd.dname,b.平均工资fromdeptd,..(selectavg(sal)平均工资,deptnofromempgroupbydeptno)bwhered.deptno=b.deptnoandb.平均工资=all(selectavg(sal)平均工资fromempgroupbydeptno);--求平均工资的等级最低的部门名称selectdname,b.gradefromdept,(selectgrade,deptnofromsalgrade,(selectdeptno,avg(sal)平均工资fromempegroupbydeptno)awherea.平均工资betweenlosalandhisal)bwhereb.grade=(selectmin(grade)fromsalgrade,(selectdeptno,avg(sal)平均工资fromempegroupbydeptno)awherea.平均工资betweenlosalandhisal)andb.deptno=dept.deptno;--部门经理人中平均工资最低的部门名称selectdname,a.*fromdept,(selectavg(sal),rank()over(orderbyavg(sal))rk,de