oraclesql练习Word文件下载.docx
《oraclesql练习Word文件下载.docx》由会员分享，可在线阅读，更多相关《oraclesql练习Word文件下载.docx（11页珍藏版）》请在冰点文库上搜索。
KING
5000
[题02]请找出所有员工的姓名并显示ul_xxx_ok字样.
显示形如ul_smith_ok..的字样.注意smith是小写
考点:
concat和lower函数的使用
理解基于函数时的SQL执行过程
理解concat和lower各执行的次数
4>
理解函数永远返回值
5>
函数的嵌套
selectconcat（lower（concat（'
ul_'
ename））,'
_ok'
）fromemp;
CONCAT（LOWER（CON
----------------
ul_smith_ok
ul_allen_ok
ul_ward_ok
或者:
select'
||lower（ename）||'
newnamefromemp;
NEWNAME
ul_jones_ok
[题03]请找出81年7月以后入职的人有哪些？
显示姓名，日期（用三种思想）
to_date和to_char灵活使用
日期和字符串的隐式转换
找出最好的语句
selectename,hiredatefromemp
2whereto_char（hiredate,'
yyyy-mm-dd'
）>
'
1981-07-01'
;
HIREDATE
-----------------------------
MARTIN
1981-09-2800:
00:
00
1987-04-1900:
1981-11-1700:
TURNER
1981-09-0800:
ADAMS
1987-05-2300:
JAMES
1981-12-0300:
MILLER
1982-01-2300:
8rowsselected.
[题04]清找出近三个月入职的人有哪些？
显示姓名，日期（用两种思想）
months_between的使用
add_months的使用
selectmax（hiredate）fromemp;
MAX（HIREDATE）
-------------------
1987-05-2300:
2wherehiredatebetween'
1987-03-01'
and'
1987-05-23'
[题05]请计算每个人入职年数，保留小数点1位（末尾数直接进数）.并且按照入职的年数倒序输出
months_between和ceil使用
函数的嵌入使用
有关计算年的思路（工作中常用）
selectround（（sysdate-hiredate）/365,1）aswork_years
2fromemp
3orderbywork_yearsdesc;
WORK_YEARS
----------
29
28.8
28.7
28.6
28.5
28.3
28.2
28.1
28
27.9
22.6
22.5
14rowsselected.
[题06]请根据每个人的job计算其调薪前和调薪后的情况。
显示姓名，调薪前、调薪后（调薪的标准如下）
PRESIDENT＝>
原来的1.1倍
MANAGER＝>
原来的1.2倍
ANALYST＝>
原来的1.3倍其余=>
原来的1.4倍
decode简单使用
selectename,sal,decode（
2job,'
PRESIDENT'
sal*1.1,
3'
MANAGER'
sal*1.2,
4'
ANALYST'
sal*1.3,
5'
SALESMAN'
SAL*1.4,
6'
CLERK'
sal*1.4,sal）
7as"
increase"
fromemp;
####注意，这里的"
要用"
"
号,不能用'
号.
SAL
increase
------------------------------
SMITH
800
1120
ALLEN
1600
2240
WARD
1250
1750
2975
3570
2850
3420
[题07]请统计历史数据中每个季度入职人数（不考虑年数的不同），显示季度数，入职人数（如果某季度没有人数则不用显示）
decode思路扩展
分组的使用
groupby需要注意的几点
selectsum（decode（to_char（hiredate,'
fmmm'
）,1,1,2,1,3,1,0））season_1,
2sum（decode（to_char（hiredate,'
）,4,2,5,2,6,2,0））season_2,
3sum（decode（to_char（hiredate,'
）,7,3,8,3,9,3,0））season_3,
4sum（decode（to_char（hiredate,'
）,10,4,11,4,12,4,0））season_4fromemp;
SEASON_1
SEASON_2
SEASON_3
SEASON_4
----------------------------------------
3
10
6
16
[题08］请找出每个部门中薪资最高的人和薪资最低的人，显示部门名称，员工姓名，员工最高薪资|最低薪资
子查询
in
union|unionall
selectdeptno,ename,salfromemp
2wheresalin（selectmax（sal）fromempgroupbydeptno）orsalin（selectmin（sal）fromempgroupbydeptno）
3orderbydeptno;
DEPTNOENAME
10KING
10MILLER
1300
20SCOTT
20FORD
20SMITH
800
30JAMES
950
30BLAKE
7rowsselected.
[题09]请分别用两种格式显示1980～1983年之间的每年的入职人数。
-----------------------------
1980
x
1981
y
1982
z
19801981
1982
x
y
z
经典的行转列问题
分组和聚合函数
selectto_char（hiredate,'
yyyy'
）,count（*）fromemp
2groupbyto_char（hiredate,'
）;
TO_C
COUNT（*）
--------------
1987
2
1
1
8
）,'
1980'
1,0））"
1980"
1981'
1981"
1982'
1982"
1987'
1987"
5fromemp;
1981
1982
1987
0
8
1
[题10]请找出哪个部门的员工薪资大于2500，显示部门名称即可.（要求用两种思想）
深入理解exists的运行过程
selectd.dnamefromdeptd
2whereexists（select'
x'
fromempewheree.deptno=d.deptno
3ande.sal>
2500）;
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
[题11]请找出被领导者的薪资高于（直接）领导者的薪资，显示其被领导者姓名和薪资（要求用两种思想分别实现）
自连接
自连接:
selecte.ename,e.salfromempem,empe
2whereem.empno=e.mgrande.sal>
em.sal;
查询不直接领导同事的员工的信息（即有上司无下属）,包括工号,名字,薪水和领导者:
selecte.empno,e.ename,e.sal,e.mgrfromempe
2wheree.empnonotin
3（selectdistinctmgrfromempwheremgrisnotnull）;
EMPNOENAME
MGR
111
7844TURNER
1500
7698
7934MILLER
1300
7782
7902FORD
3000
7566
7654MARTIN
7876ADAMS
1100
7788
7900JAMES
950
[题12]请找出哪个部门未分配员工，只显示部门名称即可.（要求用三种思想分别实现）
请统计所有部门的人数,显示部门名称、部门人数.（注意是所有部门）
2wherenotexists
3（select'
fromempewheree.deptno=d.deptno）;
OPERATIONS
selectd.dname,x.countfromdeptd,
2（selectdeptno,count（*）countfromempgroupbydeptno）x
3whered.deptno=x.deptno（+）;
DNAME
COUNT
------------------------
ACCOUNTING
3
RESEARCH
4
SALES
[题13]请找出部门人数超过3人的部门里的人员名单，要求显示部门名称、显示姓名。
exists使用
2whereexists
3（selectdeptno,count（*）fromempgroupbydeptno
4havingcount（*）>
3）;
[题14]找出比本部门平均薪资高的员工姓名和薪资以及部门名称和部门的平均薪资
内部视图使用及相关注意事项
等值链接
selecte.ename,e.sal,x.dname,x.avgsalfrom
2（selectd.dname,d.deptno,t.avgsalfromdeptd,
3（selectdeptno,avg（sal）avgsalfromempgroupbydeptno）t
4whered.deptno=t.deptno）x,empe
5wherex.deptno=e.deptno
6ande.sal>
x.avgsal;
SALDNAME
AVGSAL
--------------------------------------------
2975RESEARCH
2518.75
2850SALES
1637.5
3000RESEARCH
5000ACCOUNTING
2916.66667
[题15]请找出公司级薪资的4～5名，要求显示员工姓名，薪资
rownum的使用
内部视图的嵌入使用
模拟BS结构的分页SQL程序
不允许用分析函数实现
select*from
2（selectename,salfromemporderbysaldesc）x
3whererownum<
6
4minus
5select*from
6（selectename,salfromemporderbysaldesc）x
7whererownum<
4;
[题16]显示每个人的姓名：
部门薪资，占部门薪资100％，总薪资，占总薪资的100％
分析函数的使用
分析函数运行过程
selecte.ename,d.sum_dept_sal,e.sal/d.sum_dept_sal,
2a.sum_all_sal,e.sal/a.sum_all_salfromempe,
3（selectdeptno,sum（sal）sum_dept_salfromemp
4groupbydeptno）d,
5（selectsum（sal）sum_all_salfromemp）a
6wheree.deptno=d.deptno;
SUM_DEPT_SALE.SAL/D.SUM_DEPT_SALSUM_ALL_SALE.SAL/A.SUM_ALL_SAL
------------------------------------------------------------------------
10075
.2953
25375
.1172
6550
.1908
.0493
.4351
.1123
CLARK
8750
.2800
.0966
.2978
.1182
.5714
.1970
.2290
.0591
.1092
.0433
.1450
.0374
.1486
.0512
11rowsselected.
[题17]请找出每个部门的薪资最后3名。
显示部门名称，员工姓名，薪资，部门排名
1.先得到如下格式（DNAME（部门名）
COUNT_MAN（部门人数统计）AVGSAL（部门平均薪水）ENAME（员工名））的表,理解这个过程:
selectd.dname,x.count_man,x.avgsal,e.enamefrom
2deptd,
3（selectdeptno,count（ename）count_man,avg（sal）avgsal,min（sal）minsal
4fromempgroupbydeptno）x,
5empe
6wheree.deptno=d.deptnoande.deptno=x.deptnoande.sal=x.minsal;
COUNT_MAN
AVGSALENAME
--------------------------------------------
4
2518.75ADAMS
1637.5JAMES
32916.66667MILLER
selectename,sal,deptno,rmfrom
2（selectx.*,row_number（）over（partitionbyx.deptnoorderbysal
3desc）rmfromempx）
4
whererm<
DEPTNO
RM
---------------
升级会员 