[推荐]ORACLE SQL：经典查询练手第三篇(不懂装懂，永世饭桶！)

[推荐]ORACLE SQL：

经典查询练手第三篇(不懂装懂，永世饭桶！)

——通过知识共享树立个人品牌。

本文与大家共同讨论与分享ORACLE SQL的一些常用经典查询，欢迎大家补充，同时你认为有那些经典的也可分享出来。在本文中，对每一个问题，你要是认为有什么更好的解决方法也欢迎你及时提出。交流与分享才能共同进步嘛，感谢！

接上两篇:

[推荐]ORACLE SQL：经典查询练手第一篇

[推荐]ORACLE SQL：经典查询练手第二篇

本篇相对上两篇来说难度有所增加，继续努力，通过我为大家设立的这个系列，只要你对每一篇，每一个试题都实践测试，认真练习。我相信你对常用、经典的SQL已能熟能生巧，信手拈来！

本文使用ORACLE自带的人力资源（HR）实例数据，本文所用表结构如下：

HR.EMPLOYEES员工表结构如下：

HR.DEPARTMENTS表结构如下：

HR.REGIONS表结构如下：

SQL>DESC HR.REGIONS;
NameTypeNullableDefault Comments
----------------------------------------------
REGION_IDNUMBER
REGION_NAMEVARCHAR2(25)Y

用SQL完成以下问题列表：

1 .让SELECTTO_CHAR(SALARY,'L99,999.99')FROMHR.EMPLOYEESWHEREROWNUM<5 输出结果的货币单位是￥和$。

2.列出前五位每个员工的名字，工资、涨薪后的的工资（涨幅为8% ），以“元”为单位进行四舍五入。

3 .找出谁是最高领导，将名字按大写形式显示。

4 .找出First_Name为David，Last_Name为Austin的直接领导名字。

5 .First_Name为Alexander，Last_Name为Hunold领导谁。（谁向David报告）。

6 .哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资。

7 .哪些员工和Chen(LAST_NAME)同部门。

8 .哪些员工跟DeHaan(LAST_NAME)做一样职位。

9 .哪些员工跟Hall(LAST_NAME)不在同一个部门。

10 .哪些员工跟William（FIRST_NAME）、Smith(LAST_NAME)做不一样的职位。

11 .显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称。

12 .显示Executive部门有哪些职位。

13 .整个公司中，最高工资和最低工资相差多少。

14 .提成大于0的人数。

15 .显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。

16 .整个公司有多少个领导。

17.列出在同一部门入职日期晚但工资高于其他同事的员工：名字、工资、入职日期。

各试题解答如下（欢迎大家指出不同的方法或建议！）：

/*--------1、改变NLS_LANG的值，让SELECTTO_CHAR(SALARY,'L99,999.99')FROMHR.EMPLOYEESWHEREROWNUM<5输出结果的货币单位是￥和$。---------*/
-----在没有设置NLS_LANG的情况下：

SQL >SELECTTO_CHAR(SALARY,'L99,999.99' )
2FROM HR.EMPLOYEES
3WHEREROWNUM<5 ;

TO_CHAR(SALARY,'L99,999.99' )
----------------------------
￥24,000.00
￥ 20 ,000.00
￥ 20 ,000.00
￥ 9 ,000.00

SQL >SELECTTO_CHAR(SALARY,'$99,999.99' )
2FROM HR.EMPLOYEES
3WHEREROWNUM<5 ;

TO_CHAR(SALARY,'$99,999.99' )
----------------------------
$24,000.00
$ 20 ,000.00
$ 20 ,000.00
$ 9 ,000.00

/* --说明：对于'$99,999.99'格式符：
L：表示强制显示当地货币符号
$:表示显示美元符号
9:表示一个数字
0:表示强制0显示
.:表示一个小数点
,:表示一个千位分隔符
--------------*/

/*--------2、列出前五位每个员工的名字，工资、涨薪后的的工资（涨幅为8%），以“元”为单位进行四舍五入。---------*/

SQL >SELECTFIRST_NAME,SALARY,ROUND(SALARY*1.08)FROM HR.EMPLOYEES
2WHEREROWNUM<=5 ;

FIRST_NAMESALARYROUND(SALARY*1.08 )
------------------------------------------------
Steven24000.0025920
Neena 20000.00 21600
Lex 20000.00 21600
Alexander 9000.00 9720
Bruce 6000.00 6480

/*--------3、找出谁是最高领导，将名字按大写形式显示。---------*/
SQL >SELECTUPPER(FIRST_NAME||''||LAST_NAME)AS NAME
2FROM HR.EMPLOYEES
3WHEREMANAGER_IDISNULL ;

NAME
----------------------------------------------
STEVENKING

/*--------4、找出David的直接领导的名字。---------*/
SQL >SELECTUPPER(FIRST_NAME||''||LAST_NAME)AS NAME
2FROM HR.EMPLOYEES
3WHEREEMPLOYEE_IDIN (
4SELECTMANAGER_IDFROM HR.EMPLOYEES
5WHEREFIRST_NAME='David'ANDLAST_NAME='Austin' );

NAME
----------------------------------------------
ALEXANDERHUNOLD

--或采用以下方法

SQL >SELECTUPPER(EMP1.FIRST_NAME||''||EMP1.LAST_NAME)AS NAME
2FROM HR.EMPLOYEESEMP1,HR.EMPLOYEESEMP2
3WHEREEMP1.EMPLOYEE_ID= EMP2.MANAGER_ID
4ANDEMP2.FIRST_NAME='David'ANDEMP2.LAST_NAME='Austin' ;

NAME
----------------------------------------------
ALEXANDERHUNOLD

/*--------5、First_Name为Alexander，LAST_NAME为Hunold领导谁。（谁向David报告）。---------*/
SQL >SELECTUPPER(FIRST_NAME||''||LAST_NAME)AS NAME
2FROM HR.EMPLOYEES
3WHEREMANAGER_IDIN (
4SELECTEMPLOYEE_IDFROM HR.EMPLOYEES
5WHEREFIRST_NAME='Alexander'ANDLAST_NAME='Hunold' );

NAME
----------------------------------------------
BRUCEERNST
DAVIDAUSTIN
VALLIPATABALLA
DIANALORENTZ

--或采用以下方法

SQL >SELECTUPPER(EMP1.FIRST_NAME||''||EMP1.LAST_NAME)AS NAME
2FROM HR.EMPLOYEESEMP1,HR.EMPLOYEESEMP2
3WHEREEMP1.MANAGER_ID= EMP2.EMPLOYEE_ID
4ANDEMP2.FIRST_NAME='Alexander'ANDEMP2.LAST_NAME='Hunold' ;

NAME
----------------------------------------------
BRUCEERNST
DAVIDAUSTIN
VALLIPATABALLA
DIANALORENTZ

/*--------6、哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资。---------*/

SQL >SELECT E.FIRST_NAME,E.SALARY,M.FIRST_NAME,M.SALARY
2FROM EMPLOYEESE,EMPLOYEESM
3WHEREE.MANAGER_ID=M.EMPLOYEE_IDANDE.SALARY> M.SALARY;

FIRST_NAMESALARYFIRST_NAMESALARY
------------------------------------------------------------
Lisa11500.00Gerald11000.00
Ellen 11000.00 Eleni10500.00

--要是只列出员工的名字与工资的话，还可以这样：

SQL >SELECT E.FIRST_NAME,E.SALARY
2FROMEMPLOYEESEWHEREE.SALARY>
3 (SELECTM.SALARYFROM EMPLOYEESM
4WHEREE.MANAGER_ID= M.EMPLOYEE_ID);

FIRST_NAMESALARY
------------------------------
Lisa11500.00
Ellen 11000.00

/*--------7、哪些员工和Chen(LAST_NAME)同部门。---------*/

SQL >SELECTFIRST_NAMEFROM EMPLOYEES
2WHEREDEPARTMENT_IDIN
3 (SELECTDEPARTMENT_IDFROMEMPLOYEESWHERELAST_NAME='Chen' )
4ANDLAST_NAME<>'Chen' ;

FIRST_NAME
--------------------
Nancy
Daniel
Ismael
JoseManuel
Luis
--或者--

SQL >SELECTE1.FIRST_NAMEFROM EMPLOYEESE1,EMPLOYEESE2
2WHEREE1.DEPARTMENT_ID= E2.DEPARTMENT_ID
3ANDE2.LAST_NAME='Chen'ANDE1.LAST_NAME<>'Chen' ;

FIRST_NAME
--------------------
Nancy
Daniel
Ismael
JoseManuel
Luis

/*--------8、哪些员工跟DeHaan(LAST_NAME)做一样职位。---------*/
SQL >SELECTFIRST_NAMEFROM EMPLOYEES
2WHEREJOB_IDIN
3 (SELECTJOB_IDFROM EMPLOYEES
4WHERELAST_NAME='DeHaan' )
5ANDLAST_NAME<>'DeHaan' ;

FIRST_NAME
--------------------
Neena

--或者--

SQL >SELECTE1.FIRST_NAMEFROM EMPLOYEESE1,EMPLOYEESE2
2WHEREE1.JOB_ID= E2.JOB_ID
3ANDE2.LAST_NAME='DeHaan'ANDE1.LAST_NAME<>'DeHaan' ;

FIRST_NAME
--------------------
Neena

/*--------9、哪些员工跟Hall(LAST_NAME)不在同一个部门。---------*/

SQL >SELECTFIRST_NAME||''||LAST_NAMEFROM HR.EMPLOYEES
2WHEREDEPARTMENT_IDNOTIN (
3SELECTDEPARTMENT_IDFROM HR.EMPLOYEES
4WHERELAST_NAME='Hall' );

FIRST_NAME||''|| LAST_NAME
----------------------------------------------
StevenKing
NeenaKochhar
LexDeHaan
AlexanderHunold
BruceErnst
DavidAustin
ValliPataballa
DianaLorentz
NancyGreenberg
--...初始有72条数据

--或者：

SQL >SELECTe1.FIRST_NAMEFROM EMPLOYEESe1,EMPLOYEESe2
2WHEREe1.DEPARTMENT_ID=e2.DEPARTMENT_ID(+ )
3ande2.LAST_NAME(+)='Hall'
4 ande2.LAST_NAMEISNULL ;

/*-------10、哪些员工跟William（FIRST_NAME）、Smith(LAST_NAME)做不一样的职位。--------*/

SQL >SELECTFIRST_NAME||''||LAST_NAMEFROM HR.EMPLOYEES
2WHEREJOB_ID<>(SELECTDISTINCTJOB_IDFROM EMPLOYEES
3WHEREFIRST_NAME='William'ANDLAST_NAME='Smith' );

FIRST_NAME||''|| LAST_NAME
----------------------------------------------
StevenKing
NeenaKochhar
LexDeHaan
AlexanderHunold
----...初始有77条数据

/*--------11、显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称。---------*/

SQL >SELECTE.FIRST_NAME||''||E.LAST_NAMEAS NAME,
2 E.COMMISSION_PCT,D.DEPARTMENT_NAME,L.CITY
3FROM HR.EMPLOYEESE,HR.DEPARTMENTSD,HR.LOCATIONSL
4WHEREE.DEPARTMENT_ID= D.DEPARTMENT_ID
5ANDD.LOCATION_ID= L.LOCATION_ID
6ANDE.COMMISSION_PCTISNOTNULL ;

/*--------12、显示Executive部门有哪些职位。---------*/
SQL >SELECTDISTINCTE.JOB_IDFROM HR.EMPLOYEESE,HR.DEPARTMENTSD
2WHERED.DEPARTMENT_ID= E.DEPARTMENT_ID
3ANDD.DEPARTMENT_NAME='Executive' ;

JOB_ID
----------
AD_PRES
AD_VP

/*--------13、整个公司中，最高工资和最低工资相差多少。---------*/
SQL >SELECTMAX(SALARY)-MIN(SALARY)FROM HR.EMPLOYEES;

MAX(SALARY)-MIN (SALARY)
-----------------------
21900

/*--------14、提成大于0的人数。---------*/
SQL >SELECTCOUNT(*)AS提成大小0的人数FROM HR.EMPLOYEES
2WHERECOMMISSION_PCT>0 ;

提成大小0的人数
---------------
35
--或者

SQL >SELECTCOUNT(COMMISSION_PCT)AS 提成大小0的人数
2FROM HR.EMPLOYEES
3WHERECOMMISSION_PCT>0 ;
提成大小0的人数
---------------
35

/*--------15、显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。---------*/

SQL >SELECTMAX(NVL(SALARY,0))AS 最高工资,
2MIN(NVL(SALARY,0))AS 最低工资,
3SUM(NVL(SALARY,0))AS 工资总和,
4ROUND(AVG(NVL(SALARY,0)))AS 平均工资
5FROM HR.EMPLOYEES;

最高工资最低工资工资总和平均工资
---------------------------------------
2400021006980116523

/*--------16、整个公司有多少个领导。---------*/
SQL >SELECTCOUNT(DISTINCT(MANAGER_ID))FROM HR.EMPLOYEES
2WHEREMANAGER_IDISNOTNULL ;

COUNT(DISTINCT (MANAGER_ID))
---------------------------
18

/* --------17、列出在同一部门入职日期晚但工资高于其他同事的员工：
名字、工资、入职日期。---------*/

SQL >SELECTDISTINCTE1.FIRST_NAME||''||E1.LAST_NAMEAS 姓名,
2E1.SALARYAS工资,E1.HIRE_DATEAS 入职日期
3FROM HR.EMPLOYEESE1,HR.EMPLOYEESE2
4WHEREE1.DEPARTMENT_ID= E2.DEPARTMENT_ID
5ANDE1.HIRE_DATE> E2.HIRE_DATE
6ANDE1.SALARY> E2.SALARY
7ORDERBY工资DESC ;

姓名工资入职日期
-------------------------------------------------------------------
JohnRussell14000.001996-10-1
KarenPartners 13500.00 1997-1-5
AlbertoErrazuriz 12000.00 1997-3-10
NancyGreenberg 12000.00 1994-8-17
LisaOzer 11500.00 1997-3-11
EllenAbel 11000.00 1996-5-11
GeraldCambrault 11000.00 1999-10-15
ClaraVishney 10500.00 1997-11-11
EleniZlotkey 10500.00 2000-1-29
HarrisonBloom 10000.00 1998-3-23
PeterTucker 10000.00 1997-1-30
TaylerFox 9600.00 1998-1-24
DanielleGreene 9500.00 1999-3-19
DavidBernstein 9500.00 1997-3-24
PeterHall 9000.00 1997-8-20
AlyssaHutton 8800.00 1997-3-19
JonathonTaylor 8600.00 1998-3-24
AdamFripp 8200.00 1997-4-10
ChristopherOlsen 8000.00 1998-3-30
JackLivingston 8000.00 1998-4-23
MatthewWeiss8000.001996-7-18
JoseManuelUrman 7800.00 1998-3-7
NanetteCambrault 7500.00 1998-12-9
WilliamSmith 7400.00 1999-2-23
ElizabethBates 7300.00 1999-3-24
CharlesJohnson 7211.00 2000-1-4
MatteaMarvins 7200.00 2000-1-24
ShantaVollman 6500.00 1997-10-10
KevinMourgos 5800.00 1999-11-16
NanditaSarchand 4200.00 1996-1-27
AlexisBull 4100.00 1997-2-20
SarahBell 4000.00 1996-2-4
BritneyEverett 3900.00 1997-3-3
KellyChung 3800.00 1997-6-14
JenniferDilly 3600.00 1997-8-13
JuliaDellinger 3400.00 1998-6-24
LauraBissot 3300.00 1997-8-20
JuliaNayer 3200.00 1997-7-16
SamuelMcCain 3200.00 1998-7-1
StephenStiles 3200.00 1997-10-26
WinstonTaylor 3200.00 1998-1-24
AlanaWalsh3100.001998-4-24
JeanFleaur 3100.00 1998-2-23
AnthonyCabrio 3000.00 1999-2-7
KevinFeeney 3000.00 1998-5-23
MichaelRogers 2900.00 1998-8-26
ShelliBaida 2900.00 1997-12-24
TimothyGates 2900.00 1998-7-11
GirardGeoni 2800.00 2000-2-3
MozheAtkinson 2800.00 1997-10-30
VanceJones 2800.00 1999-3-17
IreneMikkilineni 2700.00 1998-9-28
JohnSeo 2700.00 1998-2-12
DonaldOConnell 2600.00 1999-6-21
Douglas Grant2600.002000-1-13
RandallMatos 2600.00 1998-3-15
MarthaSullivan 2500.00 1999-6-21
RandallPerkins 2500.00 1999-12-19
KiGee 2400.00 1999-12-12
HazelPhiltanker 2200.00 2000-2-6
StevenMarkle 2200.00 2000-3-8

61 rowsselected

© 2011 EricHu

原创作品，转贴请注明作者和出处，留此信息。

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

cnBlobs:http://www.cnblogs.com/huyong/
优快云：http://blog.youkuaiyun.com/chinahuyong

作者：EricHu（DB、C/S、B/S、WebService、WCF、PM等）
出处：http://www.cnblogs.com/huyong/

Q Q：80368704 E-Mail: 80368704@qq.com
本博文欢迎大家浏览和转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，在『参考』的文章中，我会表明参考的文章来源，尊重他人版权。若您发现我侵犯了您的版权，请及时与我联系。
更多文章请看[置顶]索引贴——（不断更新中）