.NET快速开发框架

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

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

公告

 [推荐]ORACLE SQL

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

 

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

 

 

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

 接上三篇:

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

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

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

本篇数据查询属于复杂业务,难度比较高,请继续努力,通过我为大家设立的这个系列,循序渐进,只要你对每一篇,每一个试题都实践测试,认真练习。我相信你对常用、经典的、复杂的SQL已能熟能生巧,信手拈来!

 


 

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

表名:REGIONS

序号

列名

数据类型

长度

小数位

标识

主键

允许空

默认值

说明

1

REGION_ID

NUMBER

 

 

 

 

 

2

REGION_NAME

VARCHAR2

25

 

 

 

 

 

 

表名:COUNTRIES

序号

列名

数据类型

长度

小数位

标识

主键

允许空

默认值

说明

1

COUNTRY_ID

CHAR

2

 

 

 

 

2

COUNTRY_NAME

VARCHAR2

40

 

 

 

 

 

3

REGION_ID

NUMBER

 

 

 

 

 

 

表名:LOCATIONS

序号

列名

数据类型

长度

小数位

标识

主键

允许空

默认值

说明

1

LOCATION_ID

NUMBER

4

0

 

 

 

2

STREET_ADDRESS

VARCHAR2

40

 

 

 

 

 

3

POSTAL_CODE

VARCHAR2

12

 

 

 

 

 

4

CITY

VARCHAR2

30

 

 

 

 

 

5

STATE_PROVINCE

VARCHAR2

25

 

 

 

 

 

6

COUNTRY_ID

CHAR

2

 

 

 

 

 

表名:DEPARTMENTS

序号

列名

数据类型

长度

小数位

标识

主键

允许空

默认值

说明

1

DEPARTMENT_ID

NUMBER

4

0

 

 

 

2

DEPARTMENT_NAME

VARCHAR2

30

 

 

 

 

 

3

MANAGER_ID

NUMBER

6

0

 

 

 

 

4

LOCATION_ID

NUMBER

4

0

 

 

 

 

表名:JOBS

序号

列名

数据类型

长度

小数位

标识

主键

允许空

默认值

说明

1

JOB_ID

VARCHAR2

10

 

 

 

 

2

JOB_TITLE

VARCHAR2

35

 

 

 

 

 

3

MIN_SALARY

NUMBER

6

0

 

 

 

 

4

MAX_SALARY

NUMBER

6

0

 

 

 

 

表名:EMPLOYEES

序号

列名

数据类型

长度

小数位

标识

主键

允许空

默认值

说明

1

EMPLOYEE_ID

NUMBER

6

0

 

 

 

2

FIRST_NAME

VARCHAR2

20

 

 

 

 

 

3

LAST_NAME

VARCHAR2

25

 

 

 

 

 

4

EMAIL

VARCHAR2

25

 

 

 

 

 

5

PHONE_NUMBER

VARCHAR2

20

 

 

 

 

 

6

HIRE_DATE

DATE

7

 

 

 

 

 

7

JOB_ID

VARCHAR2

10

 

 

 

 

 

8

SALARY

NUMBER

8

2

 

 

 

 

9

COMMISSION_PCT

NUMBER

2

2

 

 

 

 

10

MANAGER_ID

NUMBER

6

0

 

 

 

 

11

DEPARTMENT_ID

NUMBER

4

0

 

 

 

 

 

 

ER图:


 

 

SQL完成以下问题列表:

 

/*---------------------------------------------
1. 各个部门平均、最大、最小工资、人数,按照部门号升序排列。
2. 各个部门中工资大于5000的员工人数。
3. 各个部门平均工资和人数,按照部门名字升序排列。
4. 列出每个部门中有同样工资的员工的统计信息,列出他们的部门号,工资,人数。
5. 列出同部门中工资高于1000 的员工数量超过2 人的部门,显示部门名字、地区名称。
6. 哪些员工的工资,高于整个公司的平均工资,列出员工的名字和工资(降序)。
7. 哪些员工的工资,介于50号 和80号部门平均工资之间。
8. 所在部门平均工资高于5000 的员工名字。
9. 列出各个部门中工资最高的员工的信息:名字、部门号、工资。
10. 最高的部门平均工资是多少。
---------------------------------------------
*/

 

各试题解答如下(欢迎大家指出不同的方法或建议!):

 

/*--------1、各个部门平均、最大、最小工资、人数,按照部门号升序排列。---------*/
SQL
> SELECT DEPARTMENT_ID AS 部门号,AVG(SALARY) AS 平均工资
  
2         ,MAX(SALARY) AS 最高工资,MIN(SALARY)  AS 最低工资
  
3         ,COUNT(*AS 人数
  
4  FROM EMPLOYEES
  
5  GROUP BY DEPARTMENT_ID
  
6  ORDER BY DEPARTMENT_ID ASC;
 
   部门号       平均工资       最高工资       最低工资         人数
------         ----------         ----------         ----------         ----------
       10           4400               4400               4400                 1
    
20          9500              13000           6000                  2
    
30           4150              11000           2500                  6
    
40           6500               6500               6500                  1
    
50             3475.55555     8200               2100                 45
    
60           5760               9000               4200                  5
    
70          10000              10000              10000                  1
    
80             8973.85294     14000           6100                 34
    
90             21333.3333     24000              20000                  3
   
100           8600              12000           6900                  6
   
110          10150              12000           8300                  2
                
7000               7000               7000                  1
 
12 rows selected

/*--------2、各个部门中工资大于5000的员工人数。---------*/
SQL
> SELECT DEPARTMENT_ID,COUNT(*FROM EMPLOYEES
   
2  WHERE SALARY > 5000
   
3  GROUP BY DEPARTMENT_ID;
 
DEPARTMENT_ID   
COUNT(*)
------------- ----------
           20          2
           
30          1
           
40          1
           
50          5
           
60          2
           
70          1
           
80         34
           
90          3
          
100          6
          
110          2
                       
1
 
11 rows selected

/*--------3、各个部门平均工资和人数,按照部门名字升序排列。---------*/

SQL
> SELECT DPTNAME,AVG(SALARY),COUNT(*FROM
  
2         (SELECT
  
3             (SELECT DEPT.DEPARTMENT_NAME FROM DEPARTMENTS DEPT
  
4             WHERE DEPT.DEPARTMENT_ID = EMP.DEPARTMENT_ID) DPTNAME,
  
5             EMP.SALARY
  
6  FROM EMPLOYEES EMP)
  
7  GROUP BY DPTNAME
  
8  ORDER BY DPTNAME;
 
DPTNAME                        
AVG(SALARY)   COUNT(*)
------------------------------ ----------- ----------
Accounting                           10150          2
Administration                        
4400          1
Executive                      
21333.33333          3
Finance                               
8600          6
Human Resources                       
6500          1
IT                                    
5760          5
Marketing                             
9500          2
Public Relations                     10000          1
Purchasing                            
4150          6
Sales                          
8973.852941         34
Shipping                       
3475.555555         45
                                      
7000          1 
12 rows selected

--或者--

SQL
> SELECT DEPT.DEPARTMENT_NAME,AVG(EMP.SALARY),COUNT(*)
   
2  FROM EMPLOYEES EMP,DEPARTMENTS DEPT
   
3  WHERE EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID
   
4  GROUP BY DEPT.DEPARTMENT_NAME
   
5  ORDER BY DEPT.DEPARTMENT_NAME;
 
DEPARTMENT_NAME                
AVG(EMP.SALARY)   COUNT(*)
------------------------------ --------------- ----------
Accounting                               10150          2
Administration                            
4400          1
Executive                      
21333.333333333          3
Finance                                   
8600          6
Human Resources                           
6500          1
IT                                        
5760          5
Marketing                                 
9500          2
Public Relations                         10000          1
Purchasing                                
4150          6
Sales                          
8973.8529411764         34
Shipping                       
3475.5555555555         45
 
11 rows selected
--可以看到,这种方式,对于部门号为空的没有统计出来

/*--------4、列出每个部门中有同样工资的员工的统计信息,
  列出他们的部门号,工资,人数。---------
*/

SQL
> SELECT EMP1.DEPARTMENT_ID,EMP1.SALARY,COUNT(*) CNT
   
2  FROM   EMPLOYEES EMP1,EMPLOYEES EMP2
   
3  WHERE  EMP1.DEPARTMENT_ID = EMP2.DEPARTMENT_ID AND
   
4          EMP1.SALARY = EMP2.SALARY
   
5          AND EMP1.EMPLOYEE_ID <> EMP2.EMPLOYEE_ID
   
6  GROUP BY EMP1.DEPARTMENT_ID,EMP1.SALARY;
 
DEPARTMENT_ID     SALARY        CNT
------------- ---------- ----------
           50    2200.00          2
           
50    2400.00          2
           
50    2500.00         20
           
50    2600.00          6
           
50    2700.00          2
           
50    2800.00          6
           
50    2900.00          2
           
50    3000.00          2
           
50    3100.00          6
           
50    3200.00         12
           
50    3300.00          2
           
50    3600.00          2
           
60    4800.00          2
           
80    7000.00          2
           
80    7500.00          2
           
80    8000.00          6
           
80    9000.00          2
           
80    9500.00          6
           
80   10000.00          6
           
80   10500.00          2 
           
80   11000.00          2
           
90   20000.00          2
 
22 rows selected

/*--------5、列出同部门中工资高于1000 的员工数量超过2 人的部门,
显示部门名字、地区名称。---------
*/

SQL
> SELECT D.DEPARTMENT_NAME,L.CITY,COUNT(*)
   
2  FROM EMPLOYEES E,DEPARTMENTS D,LOCATIONS L
   
3  WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND
   
4         D.LOCATION_ID   = L.LOCATION_ID    AND
   
5         E.SALARY > 1000
   
6  GROUP BY D.DEPARTMENT_NAME,L.CITY
   
7  HAVING COUNT(*> 2;
 
DEPARTMENT_NAME                CITY                             
COUNT(*)
------------------------------ ------------------------------ ----------
IT                             Southlake                               5
Sales                          Oxford                                 
34
Finance                        Seattle                                 
6
Shipping                       South San Francisco                    
45
Executive                      Seattle                                 
3
Purchasing                     Seattle                                 
6
 
6 rows selected

 

 

/*--------6、哪些员工的工资,高于整个公司的平均工资,
  列出员工的名字和工资(降序)。---------
*/

SQL
> SELECT FIRST_NAME || ' ' || LAST_NAME,SALARY
   
2  FROM EMPLOYEES
   
3  WHERE SALARY > (
   
4        SELECT AVG(SALARY)
   
5        FROM EMPLOYEES
   
6        )
   
7  ORDER BY SALARY DESC
FIRST_NAME
||''||LAST_NAME                          SALARY
---------------------------------------------- ----------
Steven King                                      24000.00
Neena Kochhar                                    
20000.00
Lex De Haan                                      
20000.00
John Russell                                     
14000.00
Karen Partners                                   
13500.00
Michael Hartstein                                
13000.00
Nancy Greenberg                                  
12000.00
Alberto Errazuriz                                
12000.00
Shelley Higgins                                  
12000.00
Lisa Ozer                                        
11500.00
Den Raphaely                                     
11000.00
Gerald Cambrault                                 
11000.00
Ellen Abel                                       
11000.00
Eleni Zlotkey                                    
10500.00
Clara Vishney                                    
10500.00
Peter Tucker                                     
10000.00
Janette King                                     
10000.00
Harrison Bloom                                   
10000.00
Hermann Baer                                     
10000.00
Tayler Fox                                        
9600.00
--共50条数据

/*--------7、哪些员工的工资,介于50号 和80号 部门平均工资之间。---------*/

SQL
> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY
  
2  FROM EMPLOYEES
  
3  WHERE SALARY
  
4  BETWEEN
  
5      (SELECT AVG(SALARY) FROM EMPLOYEES
  
6       WHERE DEPARTMENT_ID = 50)
  
7  AND (SELECT AVG(SALARY) FROM EMPLOYEES
  
8       WHERE DEPARTMENT_ID = 80);
 
NAME                                               SALARY
---------------------------------------------- ----------
Bruce Ernst                                       6000.00
David Austin                                      
4800.00
Valli Pataballa                                   
4800.00
Diana Lorentz                                     
4200.00
John Chen                                         
8200.00
Ismael Sciarra                                    
7700.00
Jose Manuel Urman                                 
7800.00
Luis Popp                                         
6900.00
Matthew Weiss                                     
8000.00
Adam Fripp                                        
8200.00
Payam Kaufling                                    
7900.00
Shanta Vollman                                    
6500.00
Kevin Mourgos                                     
5800.00
Renske Ladwig                                     
3600.00
Trenna Rajs                                       
3500.00
Christopher Olsen                                 
8000.00
Nanette Cambrault                                 
7500.00
Oliver Tuvault                                    
7000.00
Lindsey Smith                                     
8000.00
Louise Doran                                      
7500.00
Sarath Sewall                                     
7000.00
Mattea Marvins                                    
7200.00
David Lee                                         
6800.00
Sundar Ande                                       
6400.00
Amit Banda                                        
6200.00
William Smith                                     
7400.00
Elizabeth Bates                                   
7300.00
Sundita Kumar                                     
6100.00
Alyssa Hutton                                     
8800.00
Jonathon Taylor                                   
8600.00
Jack Livingston                                   
8000.00
Kimberely 
Grant                                   7000.00
Charles Johnson                                   
7211.00
Nandita Sarchand                                  
4200.00
Alexis Bull                                       
4100.00
Kelly Chung                                       
3800.00
Jennifer Dilly                                    
3600.00
Sarah Bell                                        
4000.00
Britney Everett                                   
3900.00
Jennifer Whalen                                   
4400.00
Pat Fay                                           
6000.00
Susan Mavris                                      
6500.00
William Gietz                                     
8300.00
 
43 rows selected

/*--------8、所在部门平均工资高于5000 的员工名字。---------*/

SQL
> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY
   
2  FROM EMPLOYEES
   
3  WHERE DEPARTMENT_ID IN
   
4        (SELECT DEPARTMENT_ID FROM EMPLOYEES
   
5         GROUP BY DEPARTMENT_ID
   
6         HAVING AVG(SALARY) > 5000);
 
NAME                                               SALARY
---------------------------------------------- ----------
Michael Hartstein                                13000.00
Pat Fay                                           
6000.00
Susan Mavris                                      
6500.00
Alexander Hunold                                  
9000.00
Bruce Ernst                                       
6000.00
David Austin                                      
4800.00
Valli Pataballa                                   
4800.00
Diana Lorentz                                     
4200.00
Hermann Baer                                     
10000.00
John Russell                                     
14000.00
Karen Partners                                   
13500.00
Alberto Errazuriz                                
12000.00
Gerald Cambrault                                 
11000.00
Eleni Zlotkey                                    
10500.00
Peter Tucker                                     
10000.00
David Bernstein                                   
9500.00
Peter Hall                                        
9000.00
Christopher Olsen                                 
8000.00
Nanette Cambrault                                 
7500.00
Oliver Tuvault                                    
7000.00
--等54行数据…

/*--------9、列出各个部门中工资最高的员工的信息:名字、部门号、工资。---------*/

SQL
> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME
   
2         ,SALARY,DEPARTMENT_ID
   
3  FROM EMPLOYEES
   
4  WHERE (DEPARTMENT_ID,SALARY) IN
   
5        (SELECT DEPARTMENT_ID,MAX(SALARY)
   
6         FROM EMPLOYEES
   
7         GROUP BY DEPARTMENT_ID); 

NAME                                               SALARY DEPARTMENT_ID
---------------------------------------------- ---------- -------------
Jennifer Whalen                                   4400.00            10
Michael Hartstein                                
13000.00            20
Den Raphaely                                     
11000.00            30
Susan Mavris                                      
6500.00            40
Adam Fripp                                        
8200.00            50
Alexander Hunold                                  
9000.00            60
Hermann Baer                                     
10000.00            70
John Russell                                     
14000.00            80
Steven King                                      
24000.00            90
Nancy Greenberg                                  
12000.00           100
Shelley Higgins                                  
12000.00           110
 
11 rows selected

/*--------10、最高的部门平均工资是多少。---------*/
SQL
> SELECT MAX(AVGSALARY)
  
2  FROM(SELECT DEPARTMENT_ID,AVG(SALARY) AVGSALARY
  
3    FROM EMPLOYEES
  
4    GROUP BY DEPARTMENT_ID);
 
MAX(AVGSALARY)
--------------
21333.33333333

 

 

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