不说废话,大牛初始亦菜鸟,成功之路贵执行,马上行动!如果想获得更优质的显示效果,请在Chrome、Firefox等现代浏览器浏览本站。

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

:: 数据库精典 EricHu 811℃ 0评论

 

[推荐]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.99FROM 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 1996101
Karen Partners                                   
13500.00 199715
Alberto Errazuriz                                
12000.00 1997310
Nancy Greenberg                                  
12000.00 1994817
Lisa Ozer                                        
11500.00 1997311
Ellen Abel                                       
11000.00 1996511
Gerald Cambrault                                 
11000.00 19991015
Clara Vishney                                    
10500.00 19971111
Eleni Zlotkey                                    
10500.00 2000129
Harrison Bloom                                   
10000.00 1998323
Peter Tucker                                     
10000.00 1997130
Tayler Fox                                        
9600.00 1998124
Danielle Greene                                   
9500.00 1999319
David Bernstein                                   
9500.00 1997324
Peter Hall                                        
9000.00 1997820
Alyssa Hutton                                     
8800.00 1997319
Jonathon Taylor                                   
8600.00 1998324
Adam Fripp                                        
8200.00 1997410
Christopher Olsen                                 
8000.00 1998330
Jack Livingston                                   
8000.00 1998423 
Matthew Weiss                                     
8000.00 1996718
Jose Manuel Urman                                 
7800.00 199837
Nanette Cambrault                                 
7500.00 1998129
William Smith                                     
7400.00 1999223
Elizabeth Bates                                   
7300.00 1999324
Charles Johnson                                   
7211.00 200014
Mattea Marvins                                    
7200.00 2000124
Shanta Vollman                                    
6500.00 19971010
Kevin Mourgos                                     
5800.00 19991116
Nandita Sarchand                                  
4200.00 1996127
Alexis Bull                                       
4100.00 1997220
Sarah Bell                                        
4000.00 199624
Britney Everett                                   
3900.00 199733
Kelly Chung                                       
3800.00 1997614
Jennifer Dilly                                    
3600.00 1997813
Julia Dellinger                                   
3400.00 1998624
Laura Bissot                                      
3300.00 1997820
Julia Nayer                                       
3200.00 1997716
Samuel McCain                                     
3200.00 199871
Stephen Stiles                                    
3200.00 19971026
Winston Taylor                                    
3200.00 1998124 
Alana Walsh                                       
3100.00 1998424
Jean Fleaur                                       
3100.00 1998223
Anthony Cabrio                                    
3000.00 199927
Kevin Feeney                                      
3000.00 1998523
Michael Rogers                                    
2900.00 1998826
Shelli Baida                                      
2900.00 19971224
Timothy Gates                                     
2900.00 1998711
Girard Geoni                                      
2800.00 200023
Mozhe Atkinson                                    
2800.00 19971030
Vance Jones                                       
2800.00 1999317
Irene Mikkilineni                                 
2700.00 1998928
John Seo                                          
2700.00 1998212
Donald OConnell                                   
2600.00 1999621
Douglas 
Grant                                     2600.00 2000113
Randall Matos                                     
2600.00 1998315
Martha Sullivan                                   
2500.00 1999621
Randall Perkins                                   
2500.00 19991219
Ki Gee                                            
2400.00 19991212
Hazel Philtanker                                  
2200.00 200026
Steven Markle                                     
2200.00 200038
 
61 rows selected

 

转载请注明:RDIFramework.NET » [推荐]ORACLE SQL:经典查询练手第三篇(不懂装懂,永世饭桶!)

喜欢 (1)or分享 (0)
发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址