.NET快速开发框架

RDIFramework.NET,基于.NET的快速信息化系统开发、整合框架,给用户和开发者最佳的.Net框架部署方案。请关注官方博客:http://blog.rdiframework.net/

   :: 首页 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理

公告

 

[推荐]ORACLE SQL

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

 

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

 

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

 接上两篇:

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

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

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

 


 

本文使用ORACLE自带的人力资源(HR)实例数据,本文所用表结构如下:

HR.EMPLOYEES员工表结构如下:

 

HR.DEPARTMENTS表结构如下:

 

HR.REGIONS表结构如下:

 

SQL> DESC HR.REGIONS;
Name        Type         Nullable 
Default Comments 
----------- ------------ -------- ------- -------- 
REGION_ID   NUMBER                                 
REGION_NAME 
VARCHAR2(25) Y

 

 


SQL完成以下问题列表:

 

1. 让SELECT TO_CHAR(SALARY,'L99,999.99'FROM HR.EMPLOYEES WHERE  ROWNUM < 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. 哪些员工跟De Haan(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 的值,让SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE ROWNUM < 5 输出结果的货币单位是¥和$。---------*/
-----在没有设置NLS_LANG的情况下:

SQL
> SELECT TO_CHAR(SALARY,'L99,999.99')
   
2  FROM HR.EMPLOYEES
   
3  WHERE ROWNUM < 5;

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

SQL
> SELECT TO_CHAR(SALARY,'$99,999.99')
   
2  FROM HR.EMPLOYEES
   
3  WHERE ROWNUM < 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
> SELECT FIRST_NAME,SALARY,ROUND(SALARY * 1.08FROM HR.EMPLOYEES
   
2  WHERE ROWNUM <=5;
 
FIRST_NAME               SALARY 
ROUND(SALARY*1.08)
-------------------- ---------- ------------------
Steven                 24000.00              25920
Neena                  
20000.00              21600
Lex                    
20000.00              21600
Alexander               
9000.00               9720
Bruce                   
6000.00               6480

/*--------3、找出谁是最高领导,将名字按大写形式显示。---------*/
SQL
> SELECT UPPER(FIRST_NAME || ' ' || LAST_NAME) AS NAME
   
2  FROM HR.EMPLOYEES
   
3  WHERE MANAGER_ID IS NULL;
 
NAME
----------------------------------------------
STEVEN KING

/*--------4、找出David 的直接领导的名字。---------*/
SQL
> SELECT UPPER(FIRST_NAME ||' ' || LAST_NAME) AS NAME
   
2  FROM HR.EMPLOYEES
   
3  WHERE EMPLOYEE_ID IN(
   
4  SELECT MANAGER_ID FROM HR.EMPLOYEES
   
5  WHERE FIRST_NAME = 'David' AND LAST_NAME = 'Austin');
 
NAME
----------------------------------------------
ALEXANDER HUNOLD

--或采用以下方法

SQL
> SELECT UPPER( EMP1.FIRST_NAME ||' ' ||  EMP1.LAST_NAME) AS NAME
   
2  FROM HR.EMPLOYEES EMP1,HR.EMPLOYEES EMP2
   
3  WHERE EMP1.EMPLOYEE_ID = EMP2.MANAGER_ID
   
4  AND EMP2.FIRST_NAME = 'David' AND EMP2.LAST_NAME =  'Austin'

NAME
----------------------------------------------
ALEXANDER HUNOLD
 
/*--------5、First_Name 为Alexander,LAST_NAME为Hunold领导谁。(谁向David 报告)。---------*/
SQL
> SELECT UPPER(FIRST_NAME ||' ' || LAST_NAME) AS NAME
   
2  FROM HR.EMPLOYEES
   
3  WHERE MANAGER_ID IN(
   
4  SELECT EMPLOYEE_ID FROM HR.EMPLOYEES
   
5  WHERE FIRST_NAME = 'Alexander' AND LAST_NAME = 'Hunold');
 
NAME
----------------------------------------------
BRUCE ERNST
DAVID AUSTIN
VALLI PATABALLA
DIANA LORENTZ

--或采用以下方法

SQL
> SELECT UPPER( EMP1.FIRST_NAME || ' ' ||  EMP1.LAST_NAME) AS NAME
  
2  FROM HR.EMPLOYEES EMP1,HR.EMPLOYEES EMP2
  
3  WHERE EMP1.MANAGER_ID = EMP2.EMPLOYEE_ID
  
4  AND EMP2.FIRST_NAME = 'Alexander' AND EMP2.LAST_NAME =  'Hunold';
 
NAME
----------------------------------------------
BRUCE ERNST
DAVID AUSTIN
VALLI PATABALLA
DIANA LORENTZ

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

SQL
> SELECT E.FIRST_NAME,E.SALARY,M.FIRST_NAME,M.SALARY
  
2  FROM EMPLOYEES E,EMPLOYEES M
  
3  WHERE E.MANAGER_ID = M.EMPLOYEE_ID AND E.SALARY > M.SALARY;
 
FIRST_NAME               SALARY FIRST_NAME               SALARY
-------------------- ---------- -------------------- ----------
Lisa                   11500.00 Gerald                 11000.00
Ellen                  
11000.00 Eleni                  10500.00

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

SQL
> SELECT E.FIRST_NAME,E.SALARY
  
2  FROM EMPLOYEES E WHERE E.SALARY >
  
3 (SELECT M.SALARY FROM EMPLOYEES M 
  
4  WHERE E.MANAGER_ID = M.EMPLOYEE_ID);
 
FIRST_NAME               SALARY
-------------------- ----------
Lisa                   11500.00
Ellen                  
11000.00

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

SQL
> SELECT FIRST_NAME FROM EMPLOYEES
  
2  WHERE DEPARTMENT_ID IN
  
3  (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME = 'Chen')
  
4  AND LAST_NAME <> 'Chen';
 
FIRST_NAME
--------------------
Nancy
Daniel
Ismael
Jose Manuel
Luis
--或者--

SQL
> SELECT E1.FIRST_NAME FROM EMPLOYEES E1,EMPLOYEES E2
  
2  WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID
  
3  AND E2.LAST_NAME = 'Chen' AND E1.LAST_NAME <> 'Chen';
 
FIRST_NAME
--------------------
Nancy
Daniel
Ismael
Jose Manuel
Luis

/*--------8、哪些员工跟De Haan(LAST_NAME)做一样职位。---------*/
SQL
> SELECT FIRST_NAME FROM EMPLOYEES
  
2  WHERE JOB_ID IN
  
3  (SELECT JOB_ID FROM EMPLOYEES
  
4  WHERE LAST_NAME = 'De Haan')
  
5  AND LAST_NAME <> 'De Haan';
 
FIRST_NAME
--------------------
Neena

--或者--

SQL
> SELECT E1.FIRST_NAME FROM EMPLOYEES E1,EMPLOYEES E2
  
2  WHERE E1.JOB_ID = E2.JOB_ID 
  
3  AND E2.LAST_NAME = 'De Haan' AND E1.LAST_NAME <> 'De Haan';
 
FIRST_NAME
--------------------
Neena

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

SQL
> SELECT FIRST_NAME || ' ' || LAST_NAME FROM HR.EMPLOYEES
  
2  WHERE DEPARTMENT_ID NOT IN(
  
3  SELECT DEPARTMENT_ID FROM HR.EMPLOYEES
  
4  WHERE LAST_NAME = 'Hall');
 
FIRST_NAME
||''||LAST_NAME
----------------------------------------------
Steven King
Neena Kochhar
Lex De Haan
Alexander Hunold
Bruce Ernst
David Austin
Valli Pataballa
Diana Lorentz
Nancy Greenberg
--...初始有72条数据

--或者:

SQL
> SELECT e1.FIRST_NAME FROM EMPLOYEES e1,EMPLOYEES e2
  
2  WHERE e1.DEPARTMENT_ID = e2.DEPARTMENT_ID(+)
  
3  and e2.LAST_NAME(+= 'Hall'
  
4  and e2.LAST_NAME IS NULL;

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

SQL
> SELECT FIRST_NAME || ' ' || LAST_NAME FROM HR.EMPLOYEES
  
2  WHERE JOB_ID <> (SELECT DISTINCT JOB_ID FROM EMPLOYEES
  
3  WHERE FIRST_NAME = 'William' AND LAST_NAME = 'Smith'); 

FIRST_NAME
||''||LAST_NAME
----------------------------------------------
Steven King
Neena Kochhar
Lex De Haan
Alexander Hunold
----...初始有77条数据

 


 

 

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

SQL
> SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS NAME,
  
2  E.COMMISSION_PCT,D.DEPARTMENT_NAME,L.CITY
  
3  FROM HR.EMPLOYEES E,HR.DEPARTMENTS D,HR.LOCATIONS L
  
4  WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
  
5  AND D.LOCATION_ID = L.LOCATION_ID
  
6  AND E.COMMISSION_PCT IS NOT NULL;

/*--------12、显示Executive部门有哪些职位。---------*/
SQL
> SELECT DISTINCT E.JOB_ID FROM HR.EMPLOYEES E,HR.DEPARTMENTS D
  
2  WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
  
3  AND D.DEPARTMENT_NAME = 'Executive';
 
JOB_ID
----------
AD_PRES
AD_VP

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

/*--------14、提成大于0 的人数。---------*/
SQL
> SELECT COUNT(*AS 提成大小0的人数 FROM HR.EMPLOYEES
  
2  WHERE COMMISSION_PCT > 0;
 
       提成大小0的人数
---------------
             35
--或者

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

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

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

      最高工资       最低工资       工资总和       平均工资
      
----------    ----------      ----------     ---------
      24000          2100           698011         6523

/*--------16、整个公司有多少个领导。---------*/
SQL
> SELECT COUNT(DISTINCT(MANAGER_ID))  FROM HR.EMPLOYEES
   
2  WHERE MANAGER_ID IS NOT NULL;
 
COUNT(DISTINCT(MANAGER_ID))
---------------------------
                         18

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

SQL
> SELECT DISTINCT E1.FIRST_NAME || ' ' || E1.LAST_NAME AS 姓名,
  
2         E1.SALARY AS 工资,E1.HIRE_DATE AS 入职日期
  
3  FROM HR.EMPLOYEES E1,HR.EMPLOYEES E2
  
4  WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID
  
5  AND E1.HIRE_DATE > E2.HIRE_DATE
  
6  AND E1.SALARY > E2.SALARY
  
7  ORDER BY 工资 DESC;

姓名                                                   工资 入职日期
---------------------------------------------- ---------- -----------
John Russell                                     14000.00 1996-10-1
Karen Partners                                   
13500.00 1997-1-5
Alberto Errazuriz                                
12000.00 1997-3-10
Nancy Greenberg                                  
12000.00 1994-8-17
Lisa Ozer                                        
11500.00 1997-3-11
Ellen Abel                                       
11000.00 1996-5-11
Gerald Cambrault                                 
11000.00 1999-10-15
Clara Vishney                                    
10500.00 1997-11-11
Eleni Zlotkey                                    
10500.00 2000-1-29
Harrison Bloom                                   
10000.00 1998-3-23
Peter Tucker                                     
10000.00 1997-1-30
Tayler Fox                                        
9600.00 1998-1-24
Danielle Greene                                   
9500.00 1999-3-19
David Bernstein                                   
9500.00 1997-3-24
Peter Hall                                        
9000.00 1997-8-20
Alyssa Hutton                                     
8800.00 1997-3-19
Jonathon Taylor                                   
8600.00 1998-3-24
Adam Fripp                                        
8200.00 1997-4-10
Christopher Olsen                                 
8000.00 1998-3-30
Jack Livingston                                   
8000.00 1998-4-23 
Matthew Weiss                                     
8000.00 1996-7-18
Jose Manuel Urman                                 
7800.00 1998-3-7
Nanette Cambrault                                 
7500.00 1998-12-9
William Smith                                     
7400.00 1999-2-23
Elizabeth Bates                                   
7300.00 1999-3-24
Charles Johnson                                   
7211.00 2000-1-4
Mattea Marvins                                    
7200.00 2000-1-24
Shanta Vollman                                    
6500.00 1997-10-10
Kevin Mourgos                                     
5800.00 1999-11-16
Nandita Sarchand                                  
4200.00 1996-1-27
Alexis Bull                                       
4100.00 1997-2-20
Sarah Bell                                        
4000.00 1996-2-4
Britney Everett                                   
3900.00 1997-3-3
Kelly Chung                                       
3800.00 1997-6-14
Jennifer Dilly                                    
3600.00 1997-8-13
Julia Dellinger                                   
3400.00 1998-6-24
Laura Bissot                                      
3300.00 1997-8-20
Julia Nayer                                       
3200.00 1997-7-16
Samuel McCain                                     
3200.00 1998-7-1
Stephen Stiles                                    
3200.00 1997-10-26
Winston Taylor                                    
3200.00 1998-1-24 
Alana Walsh                                       
3100.00 1998-4-24
Jean Fleaur                                       
3100.00 1998-2-23
Anthony Cabrio                                    
3000.00 1999-2-7
Kevin Feeney                                      
3000.00 1998-5-23
Michael Rogers                                    
2900.00 1998-8-26
Shelli Baida                                      
2900.00 1997-12-24
Timothy Gates                                     
2900.00 1998-7-11
Girard Geoni                                      
2800.00 2000-2-3
Mozhe Atkinson                                    
2800.00 1997-10-30
Vance Jones                                       
2800.00 1999-3-17
Irene Mikkilineni                                 
2700.00 1998-9-28
John Seo                                          
2700.00 1998-2-12
Donald OConnell                                   
2600.00 1999-6-21
Douglas 
Grant                                     2600.00 2000-1-13
Randall Matos                                     
2600.00 1998-3-15
Martha Sullivan                                   
2500.00 1999-6-21
Randall Perkins                                   
2500.00 1999-12-19
Ki Gee                                            
2400.00 1999-12-12
Hazel Philtanker                                  
2200.00 2000-2-6
Steven Markle                                     
2200.00 2000-3-8
 
61 rows selected

 

posted on 2011-06-20 12:52 yonghu86 阅读(...) 评论(...) 编辑 收藏
我要啦免费统计