但行好事
莫论前程❤

Oracle数据库sql语句练习(4)

本篇数据查询属于复杂业务,难度比较高

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

imgimg

img

ER图:

img

用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
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
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
           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
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
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
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
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

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
赞(0) 打赏
未经允许不得转载:刘鹏博客 » Oracle数据库sql语句练习(4)
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

 

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏