Oracle - sql经典查询实例四

本文使用ORACLE自带的人力资源（HR）实例数据，本文所用表结构如下： 表名：REGIONS 序号 1 2 列名 REGION_ID 数据类型 NUMBER 长度 25 小数位 标识 主键 是 允许空 否 是 默认值 说明 REGION_NAME VARCHAR2 表名：COUNTRIES 序号 列名 数据类型 长度 小数位 1 2 3 COUNTRY_ID CHAR 2 40 是 标识 主键 允许空 否 是 是 默认值 说明 COUNTRY_NAME VARCHAR2 REGION_ID NUMBER 表名：LOCATIONS 序号 1 2 3 4 5 6 列名 LOCATION_ID 数据类型 NUMBER 长度 小数位 4 40 12 30 25 2 0 标识 主键 是 允许空 默认值 否 是 是 否 是 是 说明 STREET_ADDRESS VARCHAR2 POSTAL_CODE CITY VARCHAR2 VARCHAR2 STATE_PROVINCE VARCHAR2 COUNTRY_ID CHAR 表名：DEPARTMENTS 序号 列名 数据类型 长度 小数位 1 2 3 4 DEPARTMENT_ID NUMBER 4 30 6 4 0 0 0 是 标识 主键 允许空 否 否 是 是 默认值 说明 DEPARTMENT_NAME VARCHAR2 MANAGER_ID LOCATION_ID NUMBER NUMBER 表名：JOBS 序号 1 2 3 4 列名 JOB_ID JOB_TITLE 数据类型 VARCHAR2 VARCHAR2 长度 10 35 6 6 小数位 0 0 标识 主键 是 允许空 否 否 是 是 默认值 说明 MIN_SALARY NUMBER MAX_SALARY NUMBER 表名：EMPLOYEES 序号 1 2 3 4 5 6 列名 EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE 数据类型 NUMBER VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 DATE 长度 小数位 6 20 25 25 20 7 0 标识 主键 是 允许空 默认值 否 是 否 否 是 否 说明 7 8 9 10 11 JOB_ID SALARY VARCHAR2 NUMBER 10 8 2 6 4 2 2 0 0 否 是 是 是 是 COMMISSION_PCT NUMBER MANAGER_ID DEPARTMENT_ID NUMBER NUMBER ER图： 用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

本文来源：https://www.bwwdw.com/article/uqnf.html