地区名称。
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(*)
------------------------------ ------------------------------ ----------