人力资源(HR)实例数据,本文所用表结构如下:
表名:REGIONS
序号
|
列名
|
数据类型
|
长度
|
小数位
|
标识
|
主键
|
允许空
|
默认值
|
说明
|
1
|
REGION_ID
|
NUMBER
|
是
|
否
| |||||
2
|
REGION_NAME
|
VARCHAR2
|
25
|
是
|
表名:COUNTRIES
序号
|
列名
|
数据类型
|
长度
|
小数位
|
标识
|
主键
|
允许空
|
默认值
|
说明
|
1
|
COUNTRY_ID
|
CHAR
|
2
|
是
|
否
| ||||
2
|
COUNTRY_NAME
|
VARCHAR2
|
40
|
是
| |||||
3
|
REGION_ID
|
NUMBER
|
是
|
表名:LOCATIONS
序号
|
列名
|
数据类型
|
长度
|
小数位
|
标识
|
主键
|
允许空
|
默认值
|
说明
|
1
|
LOCATION_ID
|
NUMBER
|
4
|
0
|
是
|
否
| |||
2
|
STREET_ADDRESS
|
VARCHAR2
|
40
|
是
| |||||
3
|
POSTAL_CODE
|
VARCHAR2
|
12
|
是
| |||||
4
|
CITY
|
VARCHAR2
|
30
|
否
| |||||
5
|
STATE_PROVINCE
|
VARCHAR2
|
25
|
是
| |||||
6
|
COUNTRY_ID
|
CHAR
|
2
|
是
|
表名:DEPARTMENTS
序号
|
列名
|
数据类型
|
长度
|
小数位
|
标识
|
主键
|
允许空
|
默认值
|
说明
|
1
|
DEPARTMENT_ID
|
NUMBER
|
4
|
0
|
是
|
否
| |||
2
|
DEPARTMENT_NAME
|
VARCHAR2
|
30
|
否
| |||||
3
|
MANAGER_ID
|
NUMBER
|
6
|
0
|
是
| ||||
4
|
LOCATION_ID
|
NUMBER
|
4
|
0
|
是
|
表名:JOBS
序号
|
列名
|
数据类型
|
长度
|
小数位
|
标识
|
主键
|
允许空
|
默认值
|
说明
|
1
|
JOB_ID
|
VARCHAR2
|
10
|
是
|
否
| ||||
2
|
JOB_TITLE
|
VARCHAR2
|
35
|
否
| |||||
3
|
MIN_SALARY
|
NUMBER
|
6
|
0
|
是
| ||||
4
|
MAX_SALARY
|
NUMBER
|
6
|
0
|
是
|
表名:EMPLOYEES
序号
|
列名
|
数据类型
|
长度
|
小数位
|
标识
|
主键
|
允许空
|
默认值
|
说明
|
1
|
EMPLOYEE_ID
|
NUMBER
|
6
|
0
|
是
|
否
| |||
2
|
FIRST_NAME
|
VARCHAR2
|
20
|
是
| |||||
3
|
LAST_NAME
|
VARCHAR2
|
25
|
否
| |||||
4
|
EMAIL
|
VARCHAR2
|
25
|
否
| |||||
5
|
PHONE_NUMBER
|
VARCHAR2
|
20
|
是
| |||||
6
|
HIRE_DATE
|
DATE
|
7
|
否
| |||||
7
|
JOB_ID
|
VARCHAR2
|
10
|
否
| |||||
8
|
SALARY
|
NUMBER
|
8
|
2
|
是
| ||||
9
|
COMMISSION_PCT
|
NUMBER
|
2
|
2
|
是
| ||||
10
|
MANAGER_ID
|
NUMBER
|
6
|
0
|
是
| ||||
11
|
DEPARTMENT_ID
|
NUMBER
|
4
|
0
|
是
|
ER图:
创建表的语句:
--创建表:REGIONS
CREATE TABLE REGIONS(
REGION_ID NUMBER PRIMARY KEY,
REGION_NAME VARCHAR2(25)
);
COMMIT;
--创建表:COUNTRIES
CREATE TABLE COUNTRIES(
COUNTRY_ID CHAR(2) PRIMARY KEY,
COUNTRY_NAME VARCHAR2(40),
REGION_ID NUMBER
);
COMMIT;
--创建表:--创建表:COUNTRIES
CREATE TABLE LOCATIONS(
LOCATION_ID NUMBER(4) PRIMARY KEY,
STREET_ADDRESS VARCHAR2(40),
POSTAL_CODE VARCHAR2(12),
CITY VARCHAR2(30) NOT NULL,
STATE_PROVINCE VARCHAR2(25),
COUNTRY_ID CHAR(2)
);
COMMIT;
--创建表:DEPARTMENTS
CREATE TABLE DEPARTMENTS(
DEPARTMENT_ID NUMBER(4) PRIMARY KEY,
DEPARTMENT_NAME VARCHAR2(30) NOT NULL,
MANAGER_ID NUMBER(6),
LOCATION_ID NUMBER(4)
);
COMMIT;
--创建表:JOBS
CREATE TABLE JOBS(
JOB_ID VARCHAR2(10) PRIMARY KEY,
JOB_TITLE VARCHAR2(35) NOT NULL,
MIN_SALARY NUMBER(6),
MAX_SALARY NUMBER(6)
);
COMMIT;
--创建表:EMPLOYEES
CREATE TABLE EMPLOYEES(
EMPLOYEE_ID NUMBER(6) PRIMARY KEY,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25) NOT NULL,
EMAIL VARCHAR2(25) NOT NULL,
PHONE_NUMBER VARCHAR2(20),
HIREDATE DATE NOT NULL,
JOB_ID VARCHAR2(10) NOT NULL,
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4)
);
COMMIT;
查询练习题目
1. 让SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE ROWNUM < 5 输出结果的货币单位是¥和$。
2. 列出前五位每个员工的名字,工资、涨薪后的的工资(涨幅为8%),以“元”为单位进行四舍五入。
3. 找出谁是最高领导,将名字按大写形式显示。
4. 找出First_Name 为David,Last_Name为Austin 的直接领导名字。
5. First_Name 为Alexander,Last_Name为Hunold领导谁。(谁向David 报告)。
6. 哪些员工的工资高于他直接上司的工资,列出员工的名字和工资,上司的名字和工资。
7. 哪些员工和Chen(LAST_NAME)同部门。
8. 哪些员工跟De Haan(LAST_NAME)做一样职位。
9. 哪些员工跟Hall(LAST_NAME)不在同一个部门。
10. 哪些员工跟William(FIRST_NAME)、Smith(LAST_NAME)做不一样的职位。
11. 显示有提成的员工的信息:名字、提成、所在部门名称、所在地区的名称。
12. 显示Executive部门有哪些职位。
13. 整个公司中,最高工资和最低工资相差多少。
14. 提成大于0 的人数。
15. 显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。
16. 整个公司有多少个领导。
17. 列出在同一部门入职日期晚但工资高于其他同事的员工:名字、工资、入职日期。
参考解析答案:
/*--------1、各个部门平均、最大、最小工资、人数,按照部门号升序排列。---------*/ SELECT DEPARTMENT_ID AS 部门号,AVG(SALARY) AS 平均工资 ,MAX(SALARY) AS 最高工资,MIN(SALARY) AS 最低工资 ,COUNT(*) AS 人数 FROM EMPLOYEES GROUP BY DEPARTMENT_ID ORDER BY DEPARTMENT_ID ASC;
/*--------2、各个部门中工资大于5000的员工人数。---------*/ SELECT DEPARTMENT_ID,COUNT(*) FROM EMPLOYEES WHERE SALARY > 5000 GROUP BY DEPARTMENT_ID;
/*--------3、各个部门平均工资和人数,按照部门名字升序排列。---------*/ SELECT DPTNAME,AVG(SALARY),COUNT(*) FROM (SELECT (SELECT DEPT.DEPARTMENT_NAME FROM DEPARTMENTS DEPT WHERE DEPT.DEPARTMENT_ID = EMP.DEPARTMENT_ID) DPTNAME, EMP.SALARY FROM EMPLOYEES EMP) GROUP BY DPTNAME ORDER BY DPTNAME;/*待完善......*/