sql练习题.docx
《sql练习题.docx》由会员分享，可在线阅读，更多相关《sql练习题.docx（27页珍藏版）》请在冰豆网上搜索。
sql练习题
oracle_sql_练习
2009年12月08日星期二14:
32
（1）总结理解ORACLE软件安装步骤,注意事项
（2）总结理解建库步骤,注意事项
（3）注意区分一下几个概念:
ORACLESERVER-->amachinerunningoracle（almostonlyrunningoracle）
\--INSTANCE-->一段内存（是访问数据库的途径（媒介））
\--ORACLEDB-->oracledatabases
GLOBALDBNAME-->全局数据库名称（db_name,db_domain）,唯一标识数据库名字
\--db_name
\--db_domain
ORACLE_SID-->作为环境变量,指定实例名称
（4）把服务器和客户端2台机器安装配置好
[题01]请找出薪资大于2500的员工的信息且将结果倒序输出，要求显示员工姓名。
考点：
1>理解sal>2500执行过程
2>select列和orderby列的关系
3>orderby后面可接的3种方式
SQL>selectename,salfromemp
2wheresal>2500
3orderbysaldesc;
ENAMESAL
--------------------
BLAKE2850
JONES2975
FORD3000
SCOTT3000
KING5000
[题02]请找出所有员工的姓名并显示ul_xxx_ok字样.,显示形如ul_smith_ok..的字样.注意smith是小写
考点:
1>concat和lower函数的使用
2>理解基于函数时的SQL执行过程
3>理解concat和lower各执行的次数
4>理解函数永远返回值
5>函数的嵌套
SQL>selectconcat（lower（concat（'ul_',ename））,'_ok'）fromemp;
CONCAT（LOWER（CON
----------------
ul_smith_ok
ul_allen_ok
ul_ward_ok
或者:
SQL>select'ul_'||lower（ename）||'_ok'newnamefromemp;
NEWNAME
----------------
ul_allen_ok
ul_ward_ok
ul_jones_ok
[题03]请找出81年7月以后入职的人有哪些？
显示姓名，日期（用三种思想）
考点:
1>to_date和to_char灵活使用
2>日期和字符串的隐式转换
3>找出最好的语句
SQL>selectename,hiredatefromemp
2whereto_char（hiredate,'yyyy-mm-dd'）>'1981-07-01';
ENAMEHIREDATE
-----------------------------
MARTIN1981-09-2800:
00:
00
SCOTT1987-04-1900:
00:
00
KING1981-11-1700:
00:
00
TURNER1981-09-0800:
00:
00
ADAMS1987-05-2300:
00:
00
JAMES1981-12-0300:
00:
00
FORD1981-12-0300:
00:
00
MILLER1982-01-2300:
00:
00
8rowsselected.
[题04]清找出近三个月入职的人有哪些？
显示姓名，日期（用两种思想）
考点:
1>months_between的使用
2>add_months的使用
SQL>selectmax（hiredate）fromemp;
MAX（HIREDATE）
-------------------
1987-05-2300:
00:
00
SQL>selectename,hiredatefromemp
2wherehiredatebetween'1987-03-01'and'1987-05-23';
ENAMEHIREDATE
-----------------------------
SCOTT1987-04-1900:
00:
00
ADAMS1987-05-2300:
00:
00
[题05]请计算每个人入职年数，保留小数点1位（末尾数直接进数）.并且按照入职的年数倒序输出
考点:
1>months_between和ceil使用
2>函数的嵌入使用
3>有关计算年的思路（工作中常用）
SQL>selectround（（sysdate-hiredate）/365,1）aswork_years
2fromemp
3orderbywork_yearsdesc;
WORK_YEARS
----------
29
28.8
28.8
28.7
28.6
28.5
28.3
28.2
28.1
28
28
WORK_YEARS
----------
27.9
22.6
22.5
14rowsselected.
[题06]请根据每个人的job计算其调薪前和调薪后的情况。
显示姓名，调薪前、调薪后（调薪的标准如下）
PRESIDENT＝>原来的1.1倍MANAGER＝>原来的1.2倍ANALYST＝>原来的1.3倍其余=>原来的1.4倍
考点:
1>decode简单使用
SQL>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;####注意，这里的"increase"要用""号,不能用''号.
ENAMESALincrease
------------------------------
SMITH8001120
ALLEN16002240
WARD12501750
JONES29753570
MARTIN12501750
BLAKE28503420
[题07]请统计历史数据中每个季度入职人数（不考虑年数的不同），显示季度数，入职人数（如果某季度没有人数则不用显示）
考点:
1>decode思路扩展
2>分组的使用
3>groupby需要注意的几点
SQL>selectsum（decode（to_char（hiredate,'fmmm'）,1,1,2,1,3,1,0））season_1,
2sum（decode（to_char（hiredate,'fmmm'）,4,2,5,2,6,2,0））season_2,
3sum（decode（to_char（hiredate,'fmmm'）,7,3,8,3,9,3,0））season_3,
4sum（decode（to_char（hiredate,'fmmm'）,10,4,11,4,12,4,0））season_4fromemp;
SEASON_1SEASON_2SEASON_3SEASON_4
----------------------------------------
310616
[题08］请找出每个部门中薪资最高的人和薪资最低的人，显示部门名称，员工姓名，员工最高薪资|最低薪资
考点:
1>子查询
2>in
3>union|unionall
SQL>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
考点:
1>经典的行转列问题
2>分组和聚合函数
SQL>selectto_char（hiredate,'yyyy'）,count（*）fromemp
2groupbyto_char（hiredate,'yyyy'）;
TO_CCOUNT（*）
--------------
19872
1
19821
19818
SQL>selectsum（decode（to_char（hiredate,'yyyy'）,'1980',1,0））"1980",
2sum（decode（to_char（hiredate,'yyyy'）,'1981',1,0））"1981",
3sum（decode（to_char（hiredate,'yyyy'）,'1982',1,0））"1982",
4sum（decode（to_char（hiredate,'yyyy'）,'1987',1,0））"1987"
5fromemp;
1980198119821987
----------------------------------------
0812
[题10]请找出哪个部门的员工薪资大于2500，显示部门名称即可.（要求用两种思想）
1>深入理解exists的运行过程
SQL>selectd.dnamefromdeptd
2whereexists（select'x'fromempewheree.deptno=d.deptno
3ande.sal>2500）;
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
[题11]请找出被领导者的薪资高于（直接）领导者的薪资，显示其被领导者姓名和薪资（要求用两种思想分别实现）
考点:
1>自连接
2>深入理解exists的运行过程
1>自连接:
SQL>selecte.ename,e.salfromempem,empe
2whereem.empno=e.mgrande.sal>em.sal;
ENAMESAL
--------------------
SCOTT3000
FORD3000
查询不直接领导同事的员工的信息（即有上司无下属）,包括工号,名字,薪水和领导者:
SQL>selecte.empno,e.ename,e.sal,e.mgrfromempe
2wheree.empnonotin
3（selectdistinctmgrfromempwheremgrisnotnull）;
EMPNOENAMESALMGR
----------------------------------------
111
7844TURNER15007698
7934MILLER13007782
7902FORD30007566
7654MARTIN12507698
7876ADAMS11007788
7900JAMES9507698
7rowsselected.
[题12]请找出哪个部门未分配员工，只显示部门名称即可.（要求用三种思想分别实现）
请统计所有部门的人数,显示部门名称、部门人数.（注意是所有部门）
SQL>selectd.dnamefromdeptd
2wherenotexists
3（select'x'fromempewheree.deptno=d.deptno）;
DNAME
--------------
OPERATIONS
请统计所有部门的人数,显示部门名称、部门人数.（注意是所有部门）
SQL>selectd.dname,x.countfromdeptd,
2（selectdeptno,count（*）countfromempgroupbydeptno）x
3whered.deptno=x.deptno（+）;
DNAMECOUNT
------------------------
ACCOUNTING3
RESEARCH4
SALES4
OPERATIONS
[题13]请找出部门人数超过3人的部门里的人员名单，要求显示部门名称、显示姓名。
考点:
1>exists使用
SQL>selectd.dnamefromdeptd
2whereexists
3（selectdeptno,count（*）fromempgroupbydeptno
4havingcount（*）>3）;
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS
[题14]找出比本部门平均薪资高的员工姓名和薪资以及部门名称和部门的平均薪资
考点:
1>内部视图使用及相关注意事项
2>等值链接
SQL>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名，要求显示员工姓名，薪资
考点:
1>rownum的使用
2>内部视图的嵌入使用
3>模拟BS结构的分页SQL程序
4>不允许用分析函数实现
SQL>select*from
2（selectename,salfromemporderbysaldesc）x
3whererownum<6
4minus
5select*from
6（selectename,salfromemporderbysaldesc）x
7whererownum<4;
ENAMESAL
--------------------
FORD3000
JONES2975
[题16]显示每个人的姓名：
部门薪资，占部门薪资100％，总薪资，占总薪资的100％
考点:
1>分析函数的使用
2>分析函数运行过程
SQL>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>分析函数的使用
1.先得到如下格式（DNAME（部门名）COUNT_MAN（部门人数统计）AVGSAL（部门平均薪水）ENAME（员工名））的表,理解这个过程:
SQL>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
SQL>selectename,sal,deptno,rmfrom
2（selectx.*,row_number（）over（partitionbyx.deptnoorderbysal
3desc）rmfromempx）
4whererm<4;
ENAMESALDEPTNORM
----------------------------------------
KING5000101
CLARK2450102
MILLER1300103
SCOTT3000201
FORD3000202
JONES2975203
BLAKE2850301
TURNER1500302
MARTIN1250303
1
10rowsselected.
[题18]找出全公司薪资的前3名,显示ename,sal:
SQL>selectename,salfrom
2（selectename,salfromemporderbysaldesc）
3whererownum<=3;
ENAMESAL
--------------------
KING5000
SCOTT3000
FORD3000
排名中有相同值的问题处理:
SQL>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
EMPNOENAMERANKDENSE_RANKROW_NUMBER
--------------------------------------------------
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部门
升级会员 