地区名称。

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 830

0 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(*)

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