sql练习题Word文档格式.docx

sql练习题Word文档格式.docx

《sql练习题Word文档格式.docx》由会员分享，可在线阅读，更多相关《sql练习题Word文档格式.docx（27页珍藏版）》请在冰豆网上搜索。

[题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'

;

ENAMEHIREDATE

-----------------------------

MARTIN1981-09-2800:

00:

00

SCOTT1987-04-1900:

KING1981-11-1700:

TURNER1981-09-0800:

ADAMS1987-05-2300:

JAMES1981-12-0300:

FORD1981-12-0300:

MILLER1982-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;

####注意，这里的"

要用"

"

号,不能用'

号.

ENAMESALincrease

------------------------------

SMITH8001120

ALLEN16002240

WARD12501750

JONES29753570

MARTIN12501750

BLAKE28503420

[题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_1SEASON_2SEASON_3SEASON_4

----------------------------------------

310616

[题08］请找出每个部门中薪资最高的人和薪资最低的人，显示部门名称，员工姓名，员工最高薪资|最低薪资

子查询

in

union|unionall

selectdeptno,ename,salfromemp

2wheresalin（selectmax（sal）fromempgroupbydeptno）orsalin（selectmin（sal）fromempgroupbydeptno）

3orderbydeptno;

DEPTNOENAMESAL

10KING5000

10MILLER1300

20SCOTT3000

20FORD3000

20SMITH800

30JAMES950

30BLAKE2850

7rowsselected.

[题09]请分别用两种格式显示1980～1983年之间的每年的入职人数。

-----------------------------

1980x

1981y

1982z

198019811982

xyz

经典的行转列问题

分组和聚合函数

selectto_char（hiredate,'

yyyy'

）,count（*）fromemp

2groupbyto_char（hiredate,'

）;

TO_CCOUNT（*）

--------------

19872

1

19821

19818

）,'

1980'

1,0））"

1980"

1981'

1981"

1982'

1982"

1987'

1987"

5fromemp;

1980198119821987

0812

[题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）;

EMPNOENAMESALMGR

111

7844TURNER15007698

7934MILLER13007782

7902FORD30007566

7654MARTIN12507698

7876ADAMS11007788

7900JAMES9507698

[题12]请找出哪个部门未分配员工，只显示部门名称即可.（要求用三种思想分别实现）

请统计所有部门的人数,显示部门名称、部门人数.（注意是所有部门）

2wherenotexists

3（select'

fromempewheree.deptno=d.deptno）;

OPERATIONS

selectd.dname,x.countfromdeptd,

2（selectdeptno,count（*）countfromempgroupbydeptno）x

3whered.deptno=x.deptno（+）;

DNAMECOUNT

------------------------

ACCOUNTING3

RESEARCH4

SALES4

[题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;

ENAMESALDNAMEAVGSAL

--------------------------------------------

JONES2975RESEARCH2518.75

BLAKE2850SALES1637.5

SCOTT3000RESEARCH2518.75

KING5000ACCOUNTING2916.66667

FORD3000RESEARCH2518.75

[题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;

ENAMESUM_DEPT_SALE.SAL/D.SUM_DEPT_SALSUM_ALL_SALE.SAL/A.SUM_ALL_SAL

------------------------------------------------------------------------

JONES10075.295325375.1172

MARTIN6550.190825375.0493

BLAKE6550.435125375.1123

CLARK8750.280025375.0966

SCOTT10075.297825375.1182

KING8750.571425375.1970

TURNER6550.229025375.0591

ADAMS10075.109225375.0433

JAMES6550.145025375.0374

FORD10075.297825375.1182

MILLER8750.148625375.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;

DNAMECOUNT_MANAVGSALENAME

--------------------------------------------

RESEARCH42518.75ADAMS

SALES41637.5JAMES

ACCOUNTING32916.66667MILLER

selectename,sal,deptno,rmfrom

2（selectx.*,row_number（）over（partitionbyx.deptnoorderbysal

3desc）rmfromempx）

4whererm<

ENAMESALDEPTNORM

KING5000101

CLARK2450102

MILLER1300103

SCOTT3000201

FORD3000202

JONES2975203

BLAKE2850301

TURNER1500302

MARTIN1250303

10rowsselected.

[题18]找出全公司薪资的前3名,显示ename,sal:

selectename,salfrom

2（selectename,salfromemporderbysaldesc）

=3;

排名中有相同值的问题处理:

selectempno,ename,

2rank（）over（orderbysum（sal）desc）rank,

3dense_rank（）over（orderbysum（sal）desc）dense_rank,

4row_number（）over（orderbysum（sal）desc）row_number

5fromemp

6groupbyempno,ename;

EMPNOENAMERANKDENSE_RANKROW_NUMBER

--------------------------------------------------

111111

7839KING222

7788SCOTT333

7902FORD334

7566JONES545

7698BLAKE656

7782CLARK767

7844TURNER878

7934MILLER989

7654MARTIN10910

7876ADAMS111011

7900JAMES121112

12rowsselected.

row_number:

返回一个唯一的值,当碰到相同数据,排名按照记录集中的顺序依此递增

（忽略相同的数据,（譬如SCOTT'

SSAL=3000,FORD'

SSAL=3000,排名则可能是SCOTT=3,FORD=4））

dense_rank:

返回一个唯一的值,所有相同的排名待遇一样（譬如排在第3的有2个-->

123345...）

rank:

返回一个唯一的值,所有相同的排名都是一样，但是最后一条相同记录和下一条排名之间空出排名

（譬如排在第3的有2个,则第4将不存在,下一个直接到第5-->

123356...）

本文来自CSDN博客，转载请标明出处：

oraclepl/sql实例练习

第一部分：

oraclepl/sql实例练习

（1）

一、使用scott/tiger用户下的emp表和dept表完成下列练习，表的结构说明如下

emp员工表（empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号）

dept部门表（deptno部门