/*--------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
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
