Oracle SQL：经典查询练手第四篇(1)(2)
各试题解答如下(欢迎大家指出不同的方法或建议!)： /* --------1、各个部门平均、最大、最小工资、人数，按照部门号升序排列。---------*/ SQL SELECT DEPARTME
各试题解答如下(欢迎大家指出不同的方法或建议!)：
- /*--------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
精彩图集
精彩文章