oracle基本语句练习Word文档下载推荐.docx

oracle基本语句练习Word文档下载推荐.docx

《oracle基本语句练习Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《oracle基本语句练习Word文档下载推荐.docx（13页珍藏版）》请在冰点文库上搜索。

　　select*fromempwherenvl（comm,0）<

100;

　　--9、找出各月最后一天受雇的所有雇员

　　select*fromempwherehiredate=last_day（hiredate）;

　　--10、找出早于25年之前受雇的雇员

　　select*fromempwheremonths_between（sysdate,hiredate）/12>

25;

　　select*fromempwherehiredate

　　--11、显示只有首字母大写的所有雇员的姓名

　　selectenamefromempwhereename=initcap（ename）;

　　--12、显示正好为6个字符的雇员姓名

　　selectenamefromempwherelength（ename）=6

　　--13、显示不带有'

R'

的雇员姓名

　　Selectenamefromempwhereenamenotlike‘%R%’;

　　Selectenamefromempwhereinstr（ename,’R’）=0;

　　--14、显示所有雇员的姓名的前三个字符

　　selectsubstr（ename,1,3）fromemp

　　--15、显示所有雇员的姓名，用a替换所有'

A'

　　Selectreplace（ename,’A’,’a’）fromemp

　　--16、显示所有雇员的姓名以及满10年服务年限的日期

　　Selectename,add_months（hiredate,12*10）‘服务年限的日期’fromemp

　　--17、显示雇员的详细资料，按姓名排序

　　Select*fromemporderbyename

　　--18、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面

　　Selectenamefromemporderbyhiredate

　　--19、显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序

　　Selectename,job,salfromemporderbyjobdesc,salasc

　　--20、显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，将最早年份的项目排在最前面

　　selectename,to_char（hiredate,'

yyyy'

）,to_char（hiredate,'

mm'

）fromemporderbyhiredateasc

　　--21、显示在一个月为30天的情况下所有雇员的日薪金

　　selectename,sal/30fromemp;

　　--22、找出在（任何年份的）2月受聘的所有雇员

　　select*fromempwhereto_char（hiredate,'

）='

02'

;

　　--23、对于每个雇员，显示其加入公司的天数

　　selectename,sysdate-hiredatefromemp

　　--24、显示姓名字段的任何位置，包含"

A"

的所有雇员的姓名

　　selectenamefromempwhereenamelike'

%A%'

　　selectenamefromempwhereinstr（ename,’A’,1）>

　　--25、以年、月和日显示所有雇员的服务年限

　　Selectmonths_between（sysdate,hiredate）/12as“年”,months_between（sysdate,hiredate）as“月”,sysdate-hiredateas“日”fromemp

empdept是oracle中的两张默认表

每天都花上十分钟写一写，保证自己不会忘记啊~

--连接到scott用户

connectscott/tiger;

--查看当前用户下的所有表

select*fromtabwheretabType='

TABLE'

--查看表结构

descdept;

descemp;

--1列出emp表中各部门的部门号，最高工资，最低工资

selectdeptnoas部门号,max（sal）最高工资,min（sal）as"

最低工资"

fromempgroupbydeptno;

--2列出emp表中各部门job为'

CLERK'

的员工的最低工资，最高工资

fromempwherejob='

groupbydeptno;

--3对于emp中最低工资小于1000的部门，列出job为'

的员工的部门号，最低工资，最高工资

--查询出最低工资小于1000的部门号及最低工资值

--selectdeptno,min（sal）fromempgroupbydeptnohavingmin（sal）<

1000

--查询出最低工资小于1000的部门号

--selectdeptnofromempgroupbydeptnohavingmin（sal）<

1000;

--子查询

anddeptnoin（selectdeptnofromempgroupbydeptnohavingmin（sal）<

1000）

--4写出对上题的另一解决方法

--where+groupby+having

groupbydeptnohavingmin（sal）<

--子查询和父查询关联起来,做联接查询

select 

deptnoas部门号,max（sal）最高工资,min（sal）as"

fromempawherejob='

and1000>

（selectmin（sal）fromempbwherea.deptno=b.deptno）

--5根据部门号由高而低，工资由低而高列出每个员工的姓名，部门号，工资

selectename姓名,deptno部门号,sal工资fromemporderbydeptnodesc,salasc;

--6列出'

张三'

所在部门中每个员工的姓名与部门号

selectename,deptnofromempwhere 

deptnoin（selectdeptnofromempwhereename='

）;

--selectename,deptnofromempwheredeptno=20ordeptno=30;

--selectename,deptnofromempwheredeptnoin（20,30）;

--7列出每个员工的姓名，工作，部门号，部门名

--内联接:

返回两个表匹配的数据

--1.

selecte.ename,e.job,d.deptno,d.dnamefromempe

innerjoin 

deptd

one.deptno=d.deptno;

--2.

selecte.ename,e.job,d.deptno,d.dnamefromempe,deptdwheree.deptno=d.deptno;

--8列出emp中工作为'

的员工的姓名，工作，部门号，部门名

selecte.ename,e.job,d.deptno,d.dnamefromdeptd

empe

one.deptno=d.deptno

wheree.job='

selecte.ename,e.job,d.deptno,d.dnamefromempe,deptd 

wheree.deptno=d.deptnoande.job='

--9对于emp中有管理者的员工，列出姓名，管理者姓名（mgr列是管理者员工号）

--内联接的特列:

自联接

selecta.ename,b.enamefromempa,empbwherea.mgr=b.empno;

--10对于dept表中，列出所有部门名，部门号，同时列出各部门工作为'

的员工名与工作

--外联接:

左外联:

返回左表所有数据和右表匹配的数据

--1.通用的左外连接写法

selectd.dname,d.deptno,e.ename,e.jobfromdeptd

leftjoin 

（select*fromempwhereemp.job='

）e

ond.deptno=e.deptno;

--2.oracle支持的左外联接写法

selectd.dname,d.deptno,e.ename,e.jobfromdeptd,

whered.deptno=e.deptno（+）;

--11对于工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序

selectdeptno,ename,salfromempa

wherea.sal>

（selectavg（sal）fromempbwherea.deptno=b.deptno）

orderbydeptno;

--12对于emp，列出各个部门中平均工资高于本部门平均水平的员工数和部门号，按部门号排序

selecta.deptno,a.ename,a.sal,a1.dnamefromempa,depta1wherea.deptno=a1.deptnoanda.sal>

（selectavg（sal）fromempbwherea.deptno=b.deptno）orderbydeptno;

--13对于emp中工资高于本部门平均水平，人数多与1人的，列出部门号，人数，按部门号排序

selectdeptno,count（*）fromempa

groupbydeptnohavingcount（*）>

1orderbydeptno;

--14对于emp中低于自己工资至少5人的员工，列出其部门号，姓名，工资，

--以及工资少于自己的人数

selectdeptno,ename,sal,

（selectcount（empno）fromempbwhereb.sal<

a.sal）asnum

fromempawhere（selectcount（empno）fromempbwhereb.sal<

a.sal）>

=5;

针对DEPT和EMP表，查询出SMITH所在部门的部门名称，部门工资平均值

selecta1.dname,a2.mysalfromdepta1,（selectdeptno,avg（sal）mysalfromempgroupbydeptno）a2wherea1.deptno=a2.deptnoanda2.deptno=（selectdeptnofromempwhereename='

SMITH'

按年限长工资

selectename,hiredate,sal,sal*（1+round（months_between（sysdate,hiredate）/100.0））asnewsal

fromemp;

selecta1.deptno,a2.dname,a1.ename,a1.salfromempa1,depta2wherea1.deptno=a2.deptnoanda1.deptno=（

selectdeptnofromempwheresal>

（selectavg（sal）fromemp）groupbydeptnohavingmax（count（deptno）））;

oracle经典题目

Oracle工作 

--01按以下格式显示下面的信息，条件是工资大于1500的。

-- 

部门名称 

姓名 

工资

selectdname,ename,sal

fromdept,emp

wheredept.deptno=emp.deptnoandsal>

1500

--2按以下格式显示下面信息，条件是此人工资在所有人中最高。

部门 

wheredept.deptno=emp.deptno

andemp.sal=（selectmax（sal）fromemp）

--3按以下格式显示下面信息

某人为某人工作

selecte.ename||'

为'

||m.ename||'

工作'

as描述

fromempe,empm

wheree.mgr=m.empno

--4为所有人长工资，标准是：

10部门长10%；

20部门长15%；

30部门长20%其他部门长18%（要求用DECODE函数）

selectename,deptno,sal,sal*（1+nvl（decode（deptno,10,0.1,20,0.15,30,0.2）,0.18））asnewsal

fromemp

--5

--根据工作年限长工资，标准是：

为公司工作了几个月就长几个百分点。

--6查询出king所在部门的部门号\部门名称\部门人数

--ex1

selectd.deptno,d.dname,count（*）

fromdeptd,empe,empm

whered.deptno=e.deptno

ande.deptno=m.deptno

andm.ename='

KING'

groupbyd.deptno,d.dname

--ex2

fromdeptd,empe

ande.deptno=（selectdeptnofromempwhereename='

）

--7查询出king所在部门的工作年限最大的员工名字

selectename,hiredate

where（deptno,hiredate）in（selectdeptno,min（hiredate）

fromemp

wheredeptnoin（selectdeptno

whereename='

groupbydeptno）

--8查询出管理员工人数最多的人的名字和他管理的人的名字

withmas

（selectempno,ename

whereempnoin（selectmgr

groupbymgr

havingcount（*）>

=all（selectcount（*）

groupbymgr）

）

） 

selectename,'

astypefromm

union

selecte.ename,'

emp'

astypefromm,empewheree.mgr=m.empno

--9查询出工资成本最高的部门的部门号和部门名称

selectd.deptno,d.dname

havingsum（e.sal）>

=all（selectsum（sal） 

--10查询出工资不超过2500的人数最多的部门名称

ande.sal<

=2500

havingcount（*）>

=all（selectcount（*）

wheresal<

--11查询出没有下属员工的人的名字和他的职位

selectename,job

whereempnonotin（selectdistinctnvl（mgr,0）

fromemp）

--12查询出人数最多的那个部门的部门编号和部门名称

--13查询出没有员工的那个部门的部门编号和部门名称（要求用两种方法,其中一种要用集合运算）

selectdeptno,dname

fromdept

wheredeptnonotin（selectdeptnofromemp）

wherenotexists

（selectdeptnodeptno

wheredept.deptno=emp.deptno）

--ex3

select