oracle练习题.docx
《oracle练习题.docx》由会员分享，可在线阅读，更多相关《oracle练习题.docx（18页珍藏版）》请在冰豆网上搜索。
oracle练习题
oracle练习题
查询练习一
--查询姓名首字母为“A”或第二个字符为“A”的所有员工信息
SELECT*
FROMemp
WHEREenameLIKE'A%'
ORenameLIKE'_A%';
--查询部门20和30中的、岗位不是“CLERK”或“SALESMAN”的所有员工信息
SELECT*
FROMemp
WHEREjob!
='CLERK'
ANDjob!
='SALESMAN'
ANDdeptnoIN（20,30）;
--查询出工资在2500-3500之间，1981年入职的，没有奖金的所有员工信息
SELECT*
FROMemp
WHEREEXTRACT（YEARFROMhiredate）=1981
ANDsalBETWEEN2500AND3000
ANDcommISNULL;
--查询比平均员工工资高的员工信息
SELECTdeptno,dname,loc
FROMdept
WHEREdeptnoIN（
SELECTdeptno
FROMemp
GROUPBYdeptno
HAVINGAVG（sal）>2000
）;
--查询平均工资高于2000的部门信息
selectdeptno,dname,loc
fromdept
wheredeptnoin（selectdeptno
fromemp
groupbydeptno
havingavg（sal）>2000）;
--查询出ward的工作所在地
SELECTloc
FROMdept
WHEREdeptnoIN（
SELECTdeptno
FROMemp
WHEREename='WARD'
）;
--查询出工资比ADAMS高的所有人姓名、部门、所在地
SELECTa.ename,b.dname,a.sal
FROMempa,deptb
WHEREa.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,deptno
FROMemp
WHEREjobNOTIN（
SELECTjob
FROMemp
WHEREdeptno=20
）;
--验证--20部门的岗位
/*selectjob
fromemp
wheredeptno=20;*/
--查询与smith部门岗位完全相同的员工姓名、工作、工资
SELECT*
FROMemp
WHEREdeptnoIN（
SELECTdeptno
FROMemp
WHEREename='SMITH'
）
ANDjobIN（
SELECTjob
FROMemp
WHEREename='SMITH'
）;
--查询emp表中的所有信息
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;
--求出某个员工的领导，并要求这些领导的薪水高于或等于3000
selecta.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）
fromemp
selectdeptno,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 m%type;
comm1m%type;
i number:
=&i;
begin
selectempno,ename,comm,sal
intoempno,ename,comm,sal
升级会员 