Oraclep324查询练习及答案Word文件下载.doc
- 文档编号:1233541
- 上传时间:2023-04-30
- 格式:DOC
- 页数:12
- 大小:64KB
Oraclep324查询练习及答案Word文件下载.doc
《Oraclep324查询练习及答案Word文件下载.doc》由会员分享,可在线阅读,更多相关《Oraclep324查询练习及答案Word文件下载.doc(12页珍藏版)》请在冰点文库上搜索。
(28)查询所有员工及其部门信息,包括那些还不属于任何部门的员工;
(29)查询所有工种为CLERK的员工的姓名及其部门名称;
(30)查询最低工资大于2500的各种工作;
(31)查询平均工资低于2000的部门及其员工信息;
(32)查询在SALES部门工作的员工的姓名信息;
(33)查询工资高于公司平均工资的所有员工信息;
(34)查询与SMITH员工从事相同工作的所有员工信息;
(35)列出工资等于30号部门中某个员工工资的所有员工的姓名和工资;
(36)查询工资高于30号部门中工作的所有员工的工资的员工姓名和工资;
(37)查询每个部门中的员工数量、平均工资和平均工作年限;
(38)查询不同部门的同一种工作;
(39)查询各个部门的详细信息以及部门人数、部门平均工资;
(40)查询各种工作的最低工资;
(41)查询各个部门中不同工种的最高工资;
(42)查询10号部门员工及其领导的信息;
(43)查询各个部门的人数及平均工资;
(44)查询工资为某个部门平均工资的员工信息;
(45)查询工资高于本部门平均工资的员工信息;
(46)查询工资高于本部门平均工资的员工信息及其部门的平均工资;
(47)查询工资高于20号部门某个员工工资的员工的信息;
(48)统计各个工种的员工人数与平均工资;
(49)统计每个部门中各工种的人数与平均工资;
(50)查询工资、奖金与10号部门某员工工资、奖金都相同的员工信息;
(51)查询部门人数大于5的部门的员工信息;
(52)查询所有员工工资都大于2000的部门的信息;
(53)查询所有员工工资都大于2000的部门的信息及其员工的信息;
(54)查询所有员工工资都在2000~3000之间的部门的信息;
(55)查询所有工资在2000~3000之间的员工所在的部门的员工信息;
(56)查询每个员工的领导所在部门的信息;
(57)查询人数最多的部门信息;
(58)查询30号部门中工资排序前3名的员工信息;
(59)查询所有员工中工资排序在5~10名之间的员工信息;
(60)查询SMITH员工及其所有直接、间接下属员工的信息;
(61)查询SCOTT员工及其直接、间接上级员工的信息;
(62)以树状结构查询所有员工与领导之间的层次关系;
(63)向emp表中插入一条记录,员工号为1357,名字为oracle,工资为2050,部门号为20,入职日期为2002年5月10日;
(64)向emp表中插入一条记录,员工名为FAN,号为8000,其他信息与SMITH员工的信息相同;
(65)将各个部门员工的工资修改为该员工所在部门平均工资加1000;
1、select*fromempwheredeptno=20;
2、selectempno,ename,deptnofromempwherejob=‘CLERK’
3、select*fromempwherecomm>
sal;
4、select*fromempwherecomm.>
sal*0.2
5、select*fromempwherejob=’MANAGER’anddeptno=10orjob=’CLERK’anddeptno=20
6、select*fromempwheresal>
=2000
minus
select*fromempwherejob=’MANAGER’orjob=’CLERK’
select*frommepwherejob!
=’MANAGER’andjob!
=’CLERK’andsal>
7、selectdistinctjobfromempwherecommisnotnull
8、selectename,empnosal+nvl(comm.,0)fromemp;
9、select*fromempwherecommisnullorcomm<
100;
10、select*fromempwherehiredatein(selectlast_day(hiredate)-1fromemp);
11、selectempno,ename,hiredatefromempwhere(sysdate-hiredate)/365>
=10;
12、selectinitcap(ename),empno,salfromemp;
13、select*fromempwherelength(ename)=6;
14、select*fromempwhereenamenotlike‘%S%’;
15、select*fromempwhereenamelike‘_M%’;
16、selectsubstr(ename,1,3)frommep;
17、selectreplace(ename,’s’,’S’)fromemp;
18、selectename,hiredatefromemporderbyhiredate
19、selectename,job,sal,comm.Fromemporderbyjobdesc,sal
20、selectename,extract(monthfromhiredate)month,extract(yearfromhiredate)yearfromemporderbymonth,year;
selectename,to_char(hiredate,'
yyyy'
)year,to_char(hiredate,'
mm'
)monthfromemporderbymonth,year
21.select*fromempwhereextract(monthfromhiredate)=’2’;
22.selectfloor((sysdate-hiredate)/365)||'
年'
||floor(mod(sysdate-hiredate,365)/30)||'
月'
||floor(mod(mod(sysdate-hiredate,365),30))||'
日'
worktime
fromemp
23.selectdname,deptno,locfromdeptwheredeptnoin(selectdeptnofromempgroupbydeptnohavingcount(*)>
1);
24.select*fromempwheresal>
(selectsalformempwhereename=’SMITH’);
25.selecta.ename,b.enamefromempa,empb
wherea.mgr=b.empno
26.selecta.ename,a.hiredatefromempa
wherehiredate<
(selecthiredatefromempbwhereb.empno=a.mgr)
27.selectdept.deptno,dname,ename,empno,salfromdeptleftjoinempondept.deptno=emp.deptno;
28.selectempno,ename,sal,emp.deptno,dnamefromdeptrightjoinempondept.deptno=emp.deptno;
29.selectename,dnamefromemp,deptwherejob=’CLERK’andemp.deptno=dept.deptno;
30.selectjobfromempgroupbyjobhavingmin(sal)>
2500;
31.selectdeptno,empno,ename,salfromempwheredeptnoin(selectdeptnofromempgroupbydeptnohavingavg(sal)<
2000);
32.selectenamefromemp,deptwheredname=’SALES’anddept.deptno=emp.deptno;
33.select*fromempwheresal>
(selectavg(sal)fromemp);
34.select*fromempwherejob=(selectjobfromempwhereename=’SMITH’);
35.selectename,salfromempwheresalin(selectsalfromempwheredeptno=30);
36.selectename,salfromempwheresal>
all(selectsalfromempwheredeptno=30);
37.selectcount(*),avg(sal),avg((sysdate-hiredate)/365)fromempgroupbydeptno;
38.selectdistinctjob,deptnofromemp;
查询同部门同工种员工信息
selectename,emp.job,emp.deptnofromemp,(selectdeptno,jobfromempgroupby(deptno,job)havingcount(*)>
1)a
whereemp.deptno=a.deptnoandemp.job=a.job
orderbyename
39.selectdept.deptno,dname,d.amount,d.avgsalfromdept,(selectdeptno,count(*)amount,avg(sal)avgsalfromempgroupbydeptno)dwheredept.deptno=d.deptno;
40.selectjob,min(sal)fromempgroupbyjob;
41.selectdeptno,job,max(sal)fromempgroupbydeptno,job
42.selecta.ename,b.enamefromempa,empb
wherea.mgr=b.empnoanda.deptno=10;
43.selectdeptno,count(empno),avg(sal)fromempgroupbydeptno;
44.select*fromempwheresalin(selectavg(sal)fromempgroupbydeptno);
45.select*fromempewheresal>
(selectavg(sal)fromempwheredeptno=e.deptno);
46.selectename,empno,sald.avgsalfromempe,(selectempno,avg(sal)avgsalfromemp)dwheresal>
(selectavg(sal)fromempwheredeptno=e.deptno)andd.deptno=e.deptno
47.select*fromempwheresal>
any(selectsalfromempwheredeptno=20);
48.selectjob,count(empno),avg(sal)fromempgroupbyjob;
49.selectdeptno,job,count(empno),avg(sal)fromempgroupbydeptno,job;
50.select*fromempwhere(sal,comm)in(selectsal,commfromempwheredeptno=10);
51.select*fromempwheredeptnoin(selectdeptnofromempgroupbydeptnohavingcount(empno)>
5);
52.selectdeptno,dname,locfromdeptwheredeptnoin(selectdeptnofromempgroupbydeptnohavingmin(sal)>
53.selectemp.deptno,dname,ename,empno,salfromemp,deptwhereemp.deptnoin(selectdeptnofromempgroupbydeptnohavingmin(sal)>
2000)andemp.deptno=dept.deptno;
54.selectdname,deptno,locfromdept
wheredeptnonotin(selectdeptnofromempwheresal<
2000ORSAL>
3000)
55.select*fromempwheredeptnoin(selectdeptnofromempwheresaLBETWEEN2000AND3000)
56.selecta.deptno,dname,locfromdepta,empb,empcwherea.deptno=b.deptnoandb.mgr=c.empno
57.selectdeptno,dname,locfromdeptwheredeptnoin(selectdeptnofromempgroupbydeptnohavingcount(empno)>
=all(selectmax(count(*))fromempgroupbydeptno));
58.select*fromempwheredeptno=30andrownum<
=3orderbysal;
58.selectrownum,a.empno,a.salfrom(select*fromemporderbysaldesc)a
Whererownum<
=3anddeptno=30
59.selectrownum,a.empno,a.salfrom(select*fromemporderbysaldesc)awhererownum<
=10
minus
selectrownum,a.empno,a.salfrom(select*fromemporderbysaldesc)a
whererownum<
5
63.insertintoemp(empno,ename,sal,deptno,hiredate)values(1357,’oracle’,2050,20,to_date(‘2002-5-10’,’YYYY-MM-DD’));
64.insertintoempselect8000,’FAN’,job,mgr,hiredate,sal,comm.,deptnofromempwhereename=’SMITH’;
65.updateempsetsal=1000+(selectavg(sal)fromempewhereemp.deptno=e.deptnogroupbydeptno);
SQL>
SELECTEXTRACT(DAYFROMNUMTODSINTERVAL(SYSDATE-HIREDATE,’DAY’))DAY,
2EXTRACT(HOURFROMNUMTODSINTERVAL(SYSDATE-HIREDATE,’DAY’))HOUR,
3EXTRACT(MINUTEFROMNUMTODSINTERVAL(SYSDATE-HIREDATE,’DAY’))MINUTE,
4NUMTODSINTERVAL(SYSDATE-HIREDATE,’DAY’)DETAIL
5FROMEMP;
原意是给出两个日期,获取两个日期之间的间隔数值,返回形如格式:
x年x月x日,类似倒计时工具计时器。
这里权当作个记录,当时使用到了Oracle数据类型interval来做处理,因为参数传递的问题,没有奏效,最后使用内置函数解决,这里仅此作个记录,下次留用。
主要是要考虑到闰年、闰月、每个月的不同天数问题。
贴出函数脚本
函数代码
1.create
or
replace
function
fn_interval_ymd(d2
date,d1
date)
return
varchar2
2.is
3.year_
number;
4.month_
5.day_
6.temp_date
date;
7.date2
date:
=d2;
8.date1
=d1;
9.begin
10.if
date1-date2>
0
then
11.
temp_date:
=date2;
12.
date2:
=date1;
13.
date1:
=temp_date;
14.end
if;
15.year_
:
=floor(months_between(date2,date1)/12);
16.month_:
=floor(months_between(date2,(date1+NUMTOYMINTERVAL(year_,
'
year'
))));
17.day_
=date2-((date1+NUMTOYMINTERVAL(year_,
))+NUMTOYMINTERVAL(month_,
month'
));
18.return
lpad(year_,4,'
0'
)||'
||lpad(month_,2,'
||lpad(day_,2,'
;
19.exception
20.
when
others
21.
dbms_output.put_line('
Input
date
format
exception!
);
22.
23.end;
createorreplacefunctionfn_interval_ymd(d2date,d1date)returnvarchar2
is
year_number;
month_number;
day_number;
temp_datedate;
date2date:
date1date:
begin
ifdate1-date2>
0then
temp_date:
date2:
date1:
endif;
year_:
month_:
=floor(months_between(date2,(date1+NUMTOYMINTERVAL(year_,'
day_:
=date2-((date1+NUMTOYMINTERVAL(year_,'
))+NUMTOYMINTERVAL(month_,'
returnlpad(year_,4,'
exception
whenothersthen
dbms_output.put_line('
Inputdateformatexception!
return'
end;
测试效果:
Sql代码
1.select
fn_interval_ymd(sysdate,sysd
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oraclep324 查询 练习 答案