Oracle SQL：经典查询练习（3）

人力资源（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;/*待完善......*/