sql练习题Word文档格式.docx

1、题02 请找出所有员工的姓名并显示ul_xxx_ok字样. ,显示形如ul_smith_ok.的字样.注意smith是小写考点:concat和lower函数的使用理解基于函数时的SQL执行过程理解concat和lower各执行的次数4理解函数永远返回值5函数的嵌套 select concat（lower（concat（ul_,ename）,_ok） from emp;CONCAT（LOWER（CON-ul_smith_okul_allen_okul_ward_ok或者: select |lower（ename）| newname from emp;NEWNAMEul_jones_ok题03 请

2、找出81年7月以后入职的人有哪些？显示姓名，日期（用三种思想）to_date和to_char灵活使用日期和字符串的隐式转换找出最好的语句 select ename,hiredate from emp2 where to_char（hiredate,yyyy-mm-dd）1981-07-01;ENAME HIREDATE- -MARTIN 1981-09-28 00:00:00SCOTT 1987-04-19 00:KING 1981-11-17 00:TURNER 1981-09-08 00:ADAMS 1987-05-23 00:JAMES 1981-12-03 00:FORD 1981-1

3、2-03 00:MILLER 1982-01-23 00:8 rows selected.题04 清找出近三个月入职的人有哪些？显示姓名，日期（用两种思想）months_between的使用add_months的使用 select max（hiredate） from emp;MAX（HIREDATE）-1987-05-23 00:2 where hiredate between 1987-03-01 and 1987-05-23题05 请计算每个人入职年数，保留小数点1位（末尾数直接进数）.并且按照入职的年数倒序输出months_between和ceil使用函数的嵌入使用有关计算年的思路（工

4、作中常用） select round（sysdate-hiredate）/365,1） as work_years2 from emp3 order by work_years desc;WORK_YEARS-2928.828.728.628.528.328.228.12827.922.622.514 rows selected.题06 请根据每个人的job计算其调薪前和调薪后的情况。显示姓名，调薪前、调薪后（调薪的标准如下）PRESIDENT原来的1.1倍 MANAGER原来的1.2倍 ANALYST原来的1.3倍 其余=原来的1.4倍decode简单使用 select ename,sal,

5、decode（2 job,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）7 as increase from emp; #注意，这里的要用号,不能用号.ENAME SAL increase- - -SMITH 800 1120ALLEN 1600 2240WARD 1250 1750JONES 2975 3570MARTIN 1250 1750BLAKE 2850 3420题07 请统计历史数据中每个季度入职人数（不考虑年数的不同），显示季度数，入职人数（

6、如果某季度没有人数则不用显示）decode思路扩展分组的使用group by需要注意的几点 select sum（decode（to_char（hiredate,fmmm）,1,1,2,1,3,1,0） season_1,2 sum（decode（to_char（hiredate,）,4,2,5,2,6,2,0） season_2,3 sum（decode（to_char（hiredate,）,7,3,8,3,9,3,0） season_3,4 sum（decode（to_char（hiredate,）,10,4,11,4,12,4,0） season_4 from emp;SEASON_1

7、SEASON_2 SEASON_3 SEASON_4- - - -3 10 6 16题08请找出每个部门中薪资最高的人和薪资最低的人，显示部门名称，员工姓名，员工最高薪资|最低薪资子查询inunion|union all select deptno,ename,sal from emp2 where sal in （select max（sal） from emp group by deptno） or sal in （select min（sal） from emp group by deptno）3 order by deptno;DEPTNO ENAME SAL10 KING 50001

8、0 MILLER 130020 SCOTT 300020 FORD 300020 SMITH 80030 JAMES 95030 BLAKE 28507 rows selected.题09 请分别用两种格式显示1980 1983年之间的每年的入职人数。-1980 x1981 y1982 z1980 1981 1982 x y z 经典的行转列问题分组和聚合函数 select to_char（hiredate,yyyy）,count（*） from emp2 group by to_char（hiredate,）;TO_C COUNT（*）- -1987 211982 11981 8）,1980

9、,1,0） 1980,1981198119821982198719875 from emp;1980 1981 1982 19870 8 1 2题10 请找出哪个部门的员工薪资大于2500，显示部门名称即可.（要求用两种思想）深入理解exists的运行过程 select d.dname from dept d2 where exists （select x from emp e where e.deptno=d.deptno3 and e.sal2500）;DNAME-ACCOUNTINGRESEARCHSALES题11 请找出被领导者的薪资高于（直接）领导者的薪资，显示其被领导者姓名和薪资（

10、要求用两种思想分别实现）自连接自连接: select e.ename,e.sal from emp em,emp e2 where em.empno=e.mgr and e.salem.sal;查询不直接领导同事的员工的信息（即有上司无下属）,包括工号,名字,薪水和领导者: select e.empno,e.ename,e.sal,e.mgr from emp e2 where e.empno not in3 （select distinct mgr from emp where mgr is not null）;EMPNO ENAME SAL MGR1117844 TURNER 1500 7

11、6987934 MILLER 1300 77827902 FORD 3000 75667654 MARTIN 1250 76987876 ADAMS 1100 77887900 JAMES 950 7698题12 请找出哪个部门未分配员工，只显示部门名称即可.（要求用三种思想分别实现）请统计所有部门的人数,显示部门名称、部门人数.（注意是所有部门）2 where not exists3 （select from emp e where e.deptno=d.deptno）;OPERATIONS select d.dname,x.count from dept d,2 （select deptn

12、o,count（*） count from emp group by deptno） x3 where d.deptno=x.deptno（+）;DNAME COUNT- -ACCOUNTING 3RESEARCH 4SALES 4题13 请找出部门人数超过3人的部门里的人员名单，要求显示部门名称、显示姓名。exists使用2 where exists3 （select deptno,count（*） from emp group by deptno4 having count（*）3）;题14 找出比本部门平均薪资高的员工姓名和薪资以及部门名称和部门的平均薪资内部视图使用及相关注意事项等值链

13、接 select e.ename,e.sal,x.dname,x.avgsal from2 （select d.dname,d.deptno,t.avgsal from dept d,3 （select deptno,avg（sal） avgsal from emp group by deptno） t4 where d.deptno=t.deptno） x,emp e5 where x.deptno=e.deptno6 and e.salx.avgsal;ENAME SAL DNAME AVGSAL- - - -JONES 2975 RESEARCH 2518.75BLAKE 2850 SA

14、LES 1637.5SCOTT 3000 RESEARCH 2518.75KING 5000 ACCOUNTING 2916.66667FORD 3000 RESEARCH 2518.75题15 请找出公司级薪资的45名，要求显示员工姓名，薪资rownum的使用内部视图的嵌入使用模拟BS结构的分页SQL程序不允许用分析函数实现 select * from2 （select ename,sal from emp order by sal desc） x3 where rownum64 minus5 select * from6 （select ename,sal from emp order b

15、y sal desc） x7 where rownum4;题16 显示每个人的姓名：部门薪资，占部门薪资100，总薪资，占总薪资的100分析函数的使用分析函数运行过程 select e.ename,d.sum_dept_sal,e.sal/d.sum_dept_sal,2 a.sum_all_sal,e.sal/a.sum_all_sal from emp e,3 （select deptno,sum（sal） sum_dept_sal from emp4 group by deptno） d,5 （select sum（sal） sum_all_sal from emp） a6 where

16、e.deptno=d.deptno;ENAME SUM_DEPT_SAL E.SAL/D.SUM_DEPT_SAL SUM_ALL_SAL E.SAL/A.SUM_ALL_SAL- - - - -JONES 10075 .2953 25375 .1172MARTIN 6550 .1908 25375 .0493BLAKE 6550 .4351 25375 .1123CLARK 8750 .2800 25375 .0966SCOTT 10075 .2978 25375 .1182KING 8750 .5714 25375 .1970TURNER 6550 .2290 25375 .0591ADA

17、MS 10075 .1092 25375 .0433JAMES 6550 .1450 25375 .0374FORD 10075 .2978 25375 .1182MILLER 8750 .1486 25375 .051211 rows selected.题17 请找出每个部门的薪资最后3名。显示部门名称，员工姓名，薪资，部门排名1.先得到如下格式（DNAME（部门名） COUNT_MAN（部门人数统计） AVGSAL（部门平均薪水） ENAME（员工名）的表,理解这个过程: select d.dname,x.count_man,x.avgsal,e.ename from2 dept d,3

18、（select deptno,count（ename） count_man,avg（sal） avgsal,min（sal） minsal4 from emp group by deptno） x,5 emp e6 where e.deptno=d.deptno and e.deptno=x.deptno and e.sal=x.minsal;DNAME COUNT_MAN AVGSAL ENAME- - - -RESEARCH 4 2518.75 ADAMSSALES 4 1637.5 JAMESACCOUNTING 3 2916.66667 MILLER select ename,sal,

19、deptno,rm from2 （select x.*,row_number（） over （partition by x.deptno order by sal3 desc） rm from emp x）4 where rm 1 2 3 3 4 5 .）rank:返回一个唯一的值,所有相同的排名都是一样，但是最后一条相同记录和下一条排名之间空出排名（譬如排在第3的有2个,则第4将不存在,下一个直接到第5 - 1 2 3 3 5 6 .） 本文来自CSDN博客，转载请标明出处：oracle pl/sql实例练习第一部分：oracle pl/sql实例练习（1）一、使用scott/tiger用户下的emp表和dept表完成下列练习，表的结构说明如下emp员工表（empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号）dept部门表（deptno部门