Oracle SQL：经典查询练手四篇-技术开发专区

Oracle SQL：经典查询练手四篇

作者：EricHu 编辑：胡铭娅 2011-06-23 12:01 来源：EricHu�

　　【IT168 技术】本文与大家共同讨论与分享ORACLE SQL的一些常用经典查询，欢迎大家补充，同时你认为有那些经典的也可分享出来。在本文中，对每一个问题，你要是认为有什么更好的解决方法也欢迎你及时提出。交流与分享才能共同进步嘛，感谢!

　　经典查询练手第一篇

　　本文使用的实例表结构与表的数据如下：

　　scott.emp员工表结构如下：

　　Name Type Nullable Default Comments
　　-------- ------------ -------- ------- --------
　　EMPNO NUMBER(4) 员工号
　　ENAME VARCHAR2(10) Y 员工姓名
　　JOB VARCHAR2(9) Y 工作
　　MGR NUMBER(4) Y 上级编号
　　HIREDATE DATE Y 雇佣日期
　　SAL NUMBER(7,2) Y 薪金
　　COMM NUMBER(7,2) Y 佣金
　　DEPTNO NUMBER(2) Y 部门编号

　　scott.dept部门表

　　Name Type Nullable Default Comments
　　------ ------------ -------- ------- --------
　　DEPTNO NUMBER(2) 部门编号
　　DNAME VARCHAR2(14) Y 部门名称
　　LOC VARCHAR2(13) Y 地点

　　提示：工资=薪金+佣金

　　scott.emp表的现有数据如下：

SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH      CLERK      7902 1980-12-17     800.00               20
7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
7788 SCOTT      ANALYST    7566 1987-4-19     4000.00               20
7839 KING       PRESIDENT       1981-11-17    5000.00               10
7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
7900 JAMES      CLERK      7698 1981-12-3      950.00               30
7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
7934 MILLER     CLERK      7782 1982-1-23     1300.00               10
  102 EricHu     Developer  1455 2011-5-26 1   5500.00     14.00     10
  104 huyong     PM         1455 2011-5-26 1   5500.00     14.00     10
  105 WANGJING   Developer  1455 2011-5-26 1   5500.00     14.00     10

17 rows selected

　　Scott.dept表的现有数据如下：

SQL> select * from dept;

DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
    50 50abc          50def
    60 Developer      HaiKou

6 rows selected

　　用SQL完成以下问题列表：

1．列出至少有一个员工的所有部门。
2．列出薪金比“SMITH”多的所有员工。
3．列出所有员工的姓名及其直接上级的姓名。
4．列出受雇日期早于其直接上级的所有员工。
5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
6．列出所有“CLERK”（办事员）的姓名及其部门名称。
7．列出最低薪金大于1500的各种工作。
8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
9．列出薪金高于公司平均薪金的所有员工。
10．列出与“SCOTT”从事相同工作的所有员工。
11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
13．列出在每个部门工作的员工数量、平均工资和平均服务期限。
14．列出所有员工的姓名、部门名称和工资。
15．列出所有部门的详细信息和部门人数。
16．列出各种工作的最低工资。
17．列出各个部门的MANAGER（经理）的最低薪金。
18．列出所有员工的年工资,按年薪从低到高排序。

　　各答案如下，欢迎大家给出不出的解答方式。

--------1．列出至少有一个员工的所有部门。---------
SQL> select dname from dept where deptno in(select deptno from emp);
DNAME
--------------
RESEARCH
SALES
ACCOUNTING
--------或--------
SQL> select dname from dept where deptno in(select deptno from emp group by deptno having count(deptno) >=1);
DNAME
--------------
ACCOUNTING
RESEARCH
SALES

--------2．列出薪金比“SMITH”多的所有员工。----------
SQL> select * from emp where sal > (select sal from emp where ename = 'SMITH');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 4000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
102 EricHu Developer 1455 2011-5-26 1 5500.00 14.00 10
104 huyong PM 1455 2011-5-26 1 5500.00 14.00 10
105 WANGJING Developer 1455 2011-5-26 1 5500.00 14.00 10
16 rows selected

--------3．列出所有员工的姓名及其直接上级的姓名。----------
SQL> select a.ename,(select ename from emp b where b.empno=a.mgr) as boss_name from emp a;
ENAME BOSS_NAME
---------- ----------
SMITH FORD
ALLEN BLAKE
WARD BLAKE
JONES KING
MARTIN BLAKE
BLAKE KING
CLARK KING
SCOTT JONES
KING
TURNER BLAKE
ADAMS SCOTT
JAMES BLAKE
FORD JONES
MILLER CLARK
EricHu
huyong
WANGJING
17 rows selected

--------4．列出受雇日期早于其直接上级的所有员工。----------
SQL> select a.ename from emp a where a.hiredate<(select hiredate from emp b where b.empno=a.mgr);
ENAME
----------
SMITH
ALLEN
WARD
JONES
BLAKE
CLARK
6 rows selected

--------5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门----------
SQL> select a.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.deptno
2 from dept a left join emp b on a.deptno=b.deptno;

DNAME EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO
-------------- ----- ---------- --------- ----- ----------- --------- ------
RESEARCH 7369 SMITH CLERK 7902 1980-12-17 800.00 20
SALES 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 30
SALES 7521 WARD SALESMAN 7698 1981-2-22 1250.00 30
RESEARCH 7566 JONES MANAGER 7839 1981-4-2 2975.00 20
SALES 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 30
SALES 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
ACCOUNTING 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
RESEARCH 7788 SCOTT ANALYST 7566 1987-4-19 4000.00 20
ACCOUNTING 7839 KING PRESIDENT 1981-11-17 5000.00 10
SALES 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 30
RESEARCH 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
SALES 7900 JAMES CLERK 7698 1981-12-3 950.00 30
RESEARCH 7902 FORD ANALYST 7566 1981-12-3 3000.00 20
ACCOUNTING 7934 MILLER CLERK 7782 1982-1-23 1300.00 10
ACCOUNTING 102 EricHu Developer 1455 2011-5-26 1 5500.00 10
ACCOUNTING 104 huyong PM 1455 2011-5-26 1 5500.00 10
ACCOUNTING 105 WANGJING Developer 1455 2011-5-26 1 5500.00 10
50abc
OPERATIONS
Developer

20 rows selected

--------6．列出所有“CLERK”（办事员）的姓名及其部门名称。----------
SQL> select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno and a.job='CLERK';
ENAME DNAME
---------- --------------
SMITH RESEARCH
ADAMS RESEARCH
JAMES SALES
MILLER ACCOUNTING

--------7．列出最低薪金大于1500的各种工作。----------
SQL> select distinct job as HighSalJob from emp group by job having min(sal)>1500;
HIGHSALJOB
----------
ANALYST
Developer
MANAGER
PM
PRESIDENT

--------8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。----------
SQL> select ename from emp where deptno=(select deptno from dept where dname='SALES');
ENAME
----------
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES
6 rows selected

--------9．列出薪金高于公司平均薪金的所有员工。----------
SQL> select ename from emp where sal>(select avg(sal) from emp);
ENAME
----------
JONES
BLAKE
SCOTT
KING
FORD
EricHu
huyong
WANGJING
8 rows selected

--------10．列出与“SCOTT”从事相同工作的所有员工。--------
SQL> select ename from emp where job=(select job from emp where ename='SCOTT');
ENAME
----------
SCOTT
FORD

--------11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。---------
SQL> select a.ename,a.sal from emp a where a.sal in (select b.sal
2 from emp b where b.deptno=30) and a.deptno<>30;
ENAME SAL
---------- ---------

--------12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。---------
SQL> select ename,sal from emp where sal>(select max(sal) from emp where deptno=30);
ENAME SAL
---------- ---------
JONES 2975.00
SCOTT 4000.00
KING 5000.00
FORD 3000.00
EricHu 5500.00
huyong 5500.00
WANGJING 5500.00
7 rows selected

--------13．列出在每个部门工作的员工数量、平均工资和平均服务期限。---------
SQL> select (select b.dname from dept b where a.deptno=b.deptno) as deptname ,count(deptno) as deptcount,avg(sal) as deptavgsal
2 from emp a group by deptno;
DEPTNAME DEPTCOUNT DEPTAVGSAL
-------------- ---------- ----------
ACCOUNTING 6 4208.33333
RESEARCH 5 2375
SALES 6 1566.66666

--------14．列出所有员工的姓名、部门名称和工资。---------
SQL> select a.ename,(select b.dname from dept b where b.deptno=a.deptno) as deptname,sal from emp a;

ENAME DEPTNAME SAL
---------- -------------- ---------
SMITH RESEARCH 800.00
ALLEN SALES 1600.00
WARD SALES 1250.00
JONES RESEARCH 2975.00
MARTIN SALES 1250.00
BLAKE SALES 2850.00
CLARK ACCOUNTING 2450.00
SCOTT RESEARCH 4000.00
KING ACCOUNTING 5000.00
TURNER SALES 1500.00
ADAMS RESEARCH 1100.00
JAMES SALES 950.00
FORD RESEARCH 3000.00
MILLER ACCOUNTING 1300.00
EricHu ACCOUNTING 5500.00
huyong ACCOUNTING 5500.00
WANGJING ACCOUNTING 5500.00

17 rows selected

--------15．列出所有部门的详细信息和部门人数。---------
SQL> select a.deptno,a.dname,a.loc,(select count(deptno) from emp b where b.deptno=a.deptno group by b.deptno) as deptcount from dept a;
DEPTNO DNAME LOC DEPTCOUNT
------ -------------- ------------- ----------
10 ACCOUNTING NEW YORK 6
20 RESEARCH DALLAS 5
30 SALES CHICAGO 6
40 OPERATIONS BOSTON
50 50abc 50def
60 Developer HaiKou

6 rows selected

--------16．列出各种工作的最低工资。---------
SQL> select job,avg(sal) from emp group by job;

JOB         AVG(SAL)
--------- ----------
ANALYST         3500
CLERK         1037.5
Developer       5500
MANAGER   2758.33333
PM              5500
PRESIDENT       5000
SALESMAN        1400

7 rows selected

--------17．列出各个部门的MANAGER（经理）的最低薪金。--------
SQL> select deptno,min(sal) from emp where job='MANAGER' group by deptno;

DEPTNO   MIN(SAL)
------ ----------
    10       2450
    20       2975
30       2850

--------18．列出所有员工的年工资,按年薪从低到高排序。---------
SQL> select ename,(sal+nvl(comm,0))*12 as salpersal from emp order by salpersal;

ENAME       SALPERSAL
---------- ----------
SMITH            9600
JAMES           11400
ADAMS           13200
MILLER          15600
TURNER          18000
WARD            21000
ALLEN           22800
CLARK           29400
MARTIN          31800
BLAKE           34200
JONES           35700
FORD            36000
SCOTT           48000
KING            60000
EricHu          66168
huyong          66168
WANGJING        66168

17 rows selected

　　经典查询练手第二篇

　　本篇相对上篇来说比较简单，如果你对本篇的各测试做得不称心如意的话，我想你是时候给自己充下电了!

　　本文使用的实例表结构与表的数据如下：

　　scott.emp员工表结构如下：

SQL> DESC SCOTT.EMP;
Name     Type         Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO    NUMBER(4)                     员工编号
ENAME    VARCHAR2(10) Y                员工姓名
JOB      VARCHAR2(9)  Y                职位
MGR      NUMBER(4)    Y                上级编号
HIREDATE DATE         Y                雇佣日期
SAL      NUMBER(7,2)  Y                薪金
COMM     NUMBER(7,2)  Y                佣金
DEPTNO   NUMBER(2)    Y                所在部门编号
--提示：工资＝薪金＋佣金

　　scott.dept部门表：

SQL> DESC SCOTT.DEPT;
Name   Type         Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(3)                     部门编号
DNAME  VARCHAR2(14) Y                部门名称
LOC    VARCHAR2(13) Y                地点

　　scott.emp表的现有数据如下：

SQL> SELECT * FROM SCOTT.EMP;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 4000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
102 EricHu Developer 1455 2011-5-26 1 5500.00 14.00 10
104 huyong PM 1455 2011-5-26 1 5500.00 14.00 10
105 WANGJING Developer 1455 2011-5-26 1 5500.00 14.00 10

17 rows selected

　　Scott.dept表的现有数据如下：

SQL> SELECT * FROM SCOTT.DEPT;

DEPTNO DNAME LOC
------ -------------- -------------
110 信息科海口
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 50abc 50def
60 Developer HaiKou

7 rows selected

　　用SQL完成以下问题列表：

1. 找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名。
2. 找出EMP表员工名字中含有A 和N的员工姓名。
3. 找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。
4. 列出部门编号为20的所有职位。
5. 列出不属于SALES 的部门。
6. 显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序。
7. 显示职位为MANAGER 和SALESMAN，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪。
8. 说明以下两条SQL语句的输出结果：
SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;
9. 让SELECT 语句的输出结果为
SELECT * FROM SALGRADE;
SELECT * FROM BONUS;
SELECT * FROM EMP;
SELECT * FROM DEPT;
……
列出当前用户有多少张数据表，结果集中存在多少条记录。
10. 判断SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否抱错，为什么？

　　各试题解答如下(欢迎大家指出不同的方法或建议!)：

--------1. 找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名。---------
SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '__A%';

ENAME
----------
ADAMS
BLAKE
CLARK

-------2. 找出EMP表员工名字中含有A 和N的员工姓名。----------
SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '%A%' AND ENAME LIKE '%N%';

ENAME
----------
ALLEN
MARTIN
WANGJING
--------或--------
SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '%A%N%';

ENAME
----------
ALLEN
MARTIN
WANGJING

/*--------3. 找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，
佣金从大到小。----------*/
SQL> SELECT ENAME,SAL + COMM AS WAGE,COMM
2 FROM SCOTT.EMP
3 ORDER BY WAGE,COMM DESC;

ENAME WAGE COMM
---------- ---------- ---------
TURNER 1500 0.00
WARD 1750 500.00
ALLEN 1900 300.00
MARTIN 2650 1400.00
EricHu 5514 14.00
WANGJING 5514 14.00
huyong 5514 14.00
SMITH
JONES
JAMES
MILLER
FORD
ADAMS
BLAKE
CLARK
SCOTT
KING

17 rows selected

-------4. 列出部门编号为20的所有职位。----------
SQL> SELECT DISTINCT JOB FROM EMP WHERE DEPTNO = 20;

JOB
---------
ANALYST
CLERK
MANAGER

-------5. 列出不属于SALES 的部门。----------
SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME <> 'SALES';

DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
40 OPERATIONS BOSTON
50 50abc 50def
60 Developer HaiKou
110 信息科海口

6 rows selected

--或者：
SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME != 'SALES';
SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME NOT IN('SALES');
SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME NOT LIKE 'SALES';

---6. 显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序。---------
SQL> SELECT ENAME,SAL + COMM AS WAGE FROM SCOTT.EMP
2 WHERE SAL + COMM NOT BETWEEN 1000 AND 1500
3 ORDER BY WAGE DESC;

ENAME WAGE
---------- ----------
EricHu 5514
huyong 5514
WANGJING 5514
MARTIN 2650
ALLEN 1900
WARD 1750

6 rows selected
--或者
SQL> SELECT ENAME,SAL + COMM AS WAGE FROM SCOTT.EMP
2 WHERE SAL + COMM < 1000 OR SAL + COMM > 1500
3 ORDER BY WAGE DESC;

ENAME WAGE
---------- ----------
EricHu 5514
huyong 5514
WANGJING 5514
MARTIN 2650
ALLEN 1900
WARD 1750

6 rows selected

/*----- 7. 显示职位为MANAGER 和SALESMAN，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪。----------*/
SQL> SELECT ENAME 姓名,JOB 职位,(SAL + COMM) * 12 AS 年薪
2 FROM SCOTT.EMP
3 WHERE (SAL + COMM) * 12 BETWEEN 15000 AND 20000
4 AND JOB IN('MANAGER','SALESMAN');

姓名职位年薪
---------- --------- ----------
TURNER SALESMAN 18000

/*----- 8. 说明以下两条SQL语句的输出结果：
SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;
----------*/
SQL> SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;

EMPNO COMM
----- ---------
7369
7566
7698
7782
7788
7839
7876
7900
7902
7934

10 rows selected

---------------------------------------------------------------
SQL> SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;

EMPNO COMM
----- ---------

--说明：IS NULL是判断某个字段是否为空，为空并不等价于为空字符串或为数字0；
--而 =NULL 是判断某个值是否等于 NULL，NULL = NULL和NULL <> NULL都为 FALSE。

/*-----9. 让SELECT 语句的输出结果为
SELECT * FROM SALGRADE;
SELECT * FROM BONUS;
SELECT * FROM EMP;
SELECT * FROM DEPT;
……
列出当前用户有多少张数据表，结果集中存在多少条记录。
----------*/

SQL> SELECT 'SELECT * FROM '||TABLE_NAME||';' FROM USER_TABLES;

'SELECT*FROM'||TABLE_NAME||';'
---------------------------------------------
SELECT * FROM BONUS;
SELECT * FROM EMP;
SELECT * FROM DEPT;
--......等等，在此不列出。

---10. 语句SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否抱错？---------
SQL> SELECT ENAME,SAL FROM EMP WHERE SAL > '1500';

ENAME SAL
---------- ---------
ALLEN 1600.00
JONES 2975.00
BLAKE 2850.00
CLARK 2450.00
SCOTT 4000.00
KING 5000.00
FORD 3000.00
EricHu 5500.00
huyong 5500.00
WANGJING 5500.00

10 rows selected

SQL> SELECT ENAME,SAL FROM EMP WHERE SAL > 1500;

ENAME SAL
---------- ---------
ALLEN 1600.00
JONES 2975.00
BLAKE 2850.00
CLARK 2450.00
SCOTT 4000.00
KING 5000.00
FORD 3000.00
EricHu 5500.00
huyong 5500.00
WANGJING 5500.00

10 rows selected
--说明不会抱错，这儿存在隐式数据类型的。

　　经典查询练手第三篇

　　本篇相对上两篇来说难度有所增加，继续努力，通过我为大家设立的这个系列，只要你对每一篇，每一个试题都实践测试，认真练习。我相信你对常用、经典的SQL已能熟能生巧，信手拈来!

　　本文使用ORACLE自带的人力资源(HR)实例数据，本文所用表结构如下：

　　HR.EMPLOYEES员工表结构如下：

　　HR.DEPARTMENTS表结构如下：

　　HR.REGIONS表结构如下：

SQL> DESC HR.REGIONS;
Name Type Nullable Default Comments
----------- ------------ -------- ------- --------
REGION_ID NUMBER
REGION_NAME VARCHAR2(25) Y

　　用SQL完成以下问题列表：

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、改变NLS_LANG 的值，让SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE ROWNUM < 5 输出结果的货币单位是￥和$。---------*/
-----在没有设置NLS_LANG的情况下：

SQL> SELECT TO_CHAR(SALARY,'L99,999.99')
   2  FROM HR.EMPLOYEES
   3  WHERE ROWNUM < 5;

TO_CHAR(SALARY,'L99,999.99')
----------------------------
         ￥24,000.00
         ￥20,000.00
         ￥20,000.00
          ￥9,000.00

SQL> SELECT TO_CHAR(SALARY,'$99,999.99')
   2  FROM HR.EMPLOYEES
   3  WHERE ROWNUM < 5;

TO_CHAR(SALARY,'$99,999.99')
----------------------------
$24,000.00
$20,000.00
$20,000.00
  $9,000.00

/*--说明：对于'$99,999.99'格式符：
L：表示强制显示当地货币符号
$: 表示显示美元符号
9: 表示一个数字
0: 表示强制0显示
.: 表示一个小数点
,: 表示一个千位分隔符
--------------*/

/*--------2、列出前五位每个员工的名字，工资、涨薪后的的工资（涨幅为8%），以“元”为单位进行四舍五入。---------*/

SQL> SELECT FIRST_NAME,SALARY,ROUND(SALARY * 1.08) FROM HR.EMPLOYEES
   2  WHERE ROWNUM <=5;

FIRST_NAME               SALARY ROUND(SALARY*1.08)
-------------------- ---------- ------------------
Steven                 24000.00              25920
Neena                  20000.00              21600
Lex                    20000.00              21600
Alexander               9000.00               9720
Bruce                   6000.00               6480

/*--------3、找出谁是最高领导，将名字按大写形式显示。---------*/
SQL> SELECT UPPER(FIRST_NAME || ' ' || LAST_NAME) AS NAME
   2  FROM HR.EMPLOYEES
   3  WHERE MANAGER_ID IS NULL;

NAME
----------------------------------------------
STEVEN KING

/*--------4、找出David 的直接领导的名字。---------*/
SQL> SELECT UPPER(FIRST_NAME ||' ' || LAST_NAME) AS NAME
   2  FROM HR.EMPLOYEES
   3  WHERE EMPLOYEE_ID IN(
   4  SELECT MANAGER_ID FROM HR.EMPLOYEES
   5  WHERE FIRST_NAME = 'David' AND LAST_NAME = 'Austin');

NAME
----------------------------------------------
ALEXANDER HUNOLD

--或采用以下方法

SQL> SELECT UPPER( EMP1.FIRST_NAME ||' ' ||  EMP1.LAST_NAME) AS NAME
   2  FROM HR.EMPLOYEES EMP1,HR.EMPLOYEES EMP2
   3  WHERE EMP1.EMPLOYEE_ID = EMP2.MANAGER_ID
   4  AND EMP2.FIRST_NAME = 'David' AND EMP2.LAST_NAME =  'Austin';

NAME
----------------------------------------------
ALEXANDER HUNOLD

/*--------5、First_Name 为Alexander，LAST_NAME为Hunold领导谁。（谁向David 报告）。---------*/
SQL> SELECT UPPER(FIRST_NAME ||' ' || LAST_NAME) AS NAME
   2  FROM HR.EMPLOYEES
   3  WHERE MANAGER_ID IN(
   4  SELECT EMPLOYEE_ID FROM HR.EMPLOYEES
   5  WHERE FIRST_NAME = 'Alexander' AND LAST_NAME = 'Hunold');

NAME
----------------------------------------------
BRUCE ERNST
DAVID AUSTIN
VALLI PATABALLA
DIANA LORENTZ

--或采用以下方法

SQL> SELECT UPPER( EMP1.FIRST_NAME || ' ' ||  EMP1.LAST_NAME) AS NAME
  2  FROM HR.EMPLOYEES EMP1,HR.EMPLOYEES EMP2
  3  WHERE EMP1.MANAGER_ID = EMP2.EMPLOYEE_ID
  4  AND EMP2.FIRST_NAME = 'Alexander' AND EMP2.LAST_NAME =  'Hunold';

NAME
----------------------------------------------
BRUCE ERNST
DAVID AUSTIN
VALLI PATABALLA
DIANA LORENTZ

/*--------6、哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资。---------*/

SQL> SELECT E.FIRST_NAME,E.SALARY,M.FIRST_NAME,M.SALARY
  2  FROM EMPLOYEES E,EMPLOYEES M
  3  WHERE E.MANAGER_ID = M.EMPLOYEE_ID AND E.SALARY > M.SALARY;

FIRST_NAME               SALARY FIRST_NAME               SALARY
-------------------- ---------- -------------------- ----------
Lisa                   11500.00 Gerald                 11000.00
Ellen                  11000.00 Eleni                  10500.00

--要是只列出员工的名字与工资的话，还可以这样：

SQL> SELECT E.FIRST_NAME,E.SALARY
  2  FROM EMPLOYEES E WHERE E.SALARY >
  3 (SELECT M.SALARY FROM EMPLOYEES M
  4  WHERE E.MANAGER_ID = M.EMPLOYEE_ID);

FIRST_NAME               SALARY
-------------------- ----------
Lisa                   11500.00
Ellen                  11000.00

/*--------7、哪些员工和Chen(LAST_NAME)同部门。---------*/

SQL> SELECT FIRST_NAME FROM EMPLOYEES
  2  WHERE DEPARTMENT_ID IN
  3  (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME = 'Chen')
  4  AND LAST_NAME <> 'Chen';

FIRST_NAME
--------------------
Nancy
Daniel
Ismael
Jose Manuel
Luis
--或者--

SQL> SELECT E1.FIRST_NAME FROM EMPLOYEES E1,EMPLOYEES E2
  2  WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID
  3  AND E2.LAST_NAME = 'Chen' AND E1.LAST_NAME <> 'Chen';

FIRST_NAME
--------------------
Nancy
Daniel
Ismael
Jose Manuel
Luis

/*--------8、哪些员工跟De Haan(LAST_NAME)做一样职位。---------*/
SQL> SELECT FIRST_NAME FROM EMPLOYEES
  2  WHERE JOB_ID IN
  3  (SELECT JOB_ID FROM EMPLOYEES
  4  WHERE LAST_NAME = 'De Haan')
  5  AND LAST_NAME <> 'De Haan';

FIRST_NAME
--------------------
Neena

--或者--

SQL> SELECT E1.FIRST_NAME FROM EMPLOYEES E1,EMPLOYEES E2
  2  WHERE E1.JOB_ID = E2.JOB_ID
  3  AND E2.LAST_NAME = 'De Haan' AND E1.LAST_NAME <> 'De Haan';

FIRST_NAME
--------------------
Neena

/*--------9、哪些员工跟Hall(LAST_NAME)不在同一个部门。---------*/

SQL> SELECT FIRST_NAME || ' ' || LAST_NAME FROM HR.EMPLOYEES
  2  WHERE DEPARTMENT_ID NOT IN(
  3  SELECT DEPARTMENT_ID FROM HR.EMPLOYEES
  4  WHERE LAST_NAME = 'Hall');

FIRST_NAME||''||LAST_NAME
----------------------------------------------
Steven King
Neena Kochhar
Lex De Haan
Alexander Hunold
Bruce Ernst
David Austin
Valli Pataballa
Diana Lorentz
Nancy Greenberg
--...初始有72条数据

--或者：

SQL> SELECT e1.FIRST_NAME FROM EMPLOYEES e1,EMPLOYEES e2
  2  WHERE e1.DEPARTMENT_ID = e2.DEPARTMENT_ID(+)
  3  and e2.LAST_NAME(+) = 'Hall'
  4  and e2.LAST_NAME IS NULL;

/*-------10、哪些员工跟William（FIRST_NAME）、Smith(LAST_NAME)做不一样的职位。--------*/

SQL> SELECT FIRST_NAME || ' ' || LAST_NAME FROM HR.EMPLOYEES
  2  WHERE JOB_ID <> (SELECT DISTINCT JOB_ID FROM EMPLOYEES
  3  WHERE FIRST_NAME = 'William' AND LAST_NAME = 'Smith');

FIRST_NAME||''||LAST_NAME
----------------------------------------------
Steven King
Neena Kochhar
Lex De Haan
Alexander Hunold
----...初始有77条数据

/*--------11、显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称。---------*/

SQL> SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS NAME,
  2  E.COMMISSION_PCT,D.DEPARTMENT_NAME,L.CITY
  3  FROM HR.EMPLOYEES E,HR.DEPARTMENTS D,HR.LOCATIONS L
  4  WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
  5  AND D.LOCATION_ID = L.LOCATION_ID
  6  AND E.COMMISSION_PCT IS NOT NULL;

/*--------12、显示Executive部门有哪些职位。---------*/
SQL> SELECT DISTINCT E.JOB_ID FROM HR.EMPLOYEES E,HR.DEPARTMENTS D
  2  WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
  3  AND D.DEPARTMENT_NAME = 'Executive';

JOB_ID
----------
AD_PRES
AD_VP

/*--------13、整个公司中，最高工资和最低工资相差多少。---------*/
SQL> SELECT MAX(SALARY) - MIN(SALARY) FROM HR.EMPLOYEES;

MAX(SALARY)-MIN(SALARY)
-----------------------
                  21900

/*--------14、提成大于0 的人数。---------*/
SQL> SELECT COUNT(*) AS 提成大小0的人数 FROM HR.EMPLOYEES
  2  WHERE COMMISSION_PCT > 0;

       提成大小0的人数
---------------
             35
--或者

SQL> SELECT COUNT(COMMISSION_PCT) AS 提成大小0的人数
  2   FROM HR.EMPLOYEES
  3   WHERE COMMISSION_PCT > 0;
       提成大小0的人数
---------------
             35

/*--------15、显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。---------*/

SQL> SELECT MAX(NVL(SALARY,0)) AS 最高工资,
  2           MIN(NVL(SALARY,0)) AS 最低工资,
  3           SUM(NVL(SALARY,0)) AS 工资总和,
  4           ROUND(AVG(NVL(SALARY,0))) AS 平均工资
  5  FROM HR.EMPLOYEES;

      最高工资       最低工资       工资总和       平均工资
      ----------    ----------      ----------     ---------
      24000          2100           698011         6523

/*--------16、整个公司有多少个领导。---------*/
SQL> SELECT COUNT(DISTINCT(MANAGER_ID))  FROM HR.EMPLOYEES
   2  WHERE MANAGER_ID IS NOT NULL;

COUNT(DISTINCT(MANAGER_ID))
---------------------------
                         18

/*--------17、列出在同一部门入职日期晚但工资高于其他同事的员工：
名字、工资、入职日期。---------*/

SQL> SELECT DISTINCT E1.FIRST_NAME || ' ' || E1.LAST_NAME AS 姓名,
  2         E1.SALARY AS 工资,E1.HIRE_DATE AS 入职日期
  3  FROM HR.EMPLOYEES E1,HR.EMPLOYEES E2
  4  WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID
  5  AND E1.HIRE_DATE > E2.HIRE_DATE
  6  AND E1.SALARY > E2.SALARY
  7  ORDER BY 工资 DESC;

姓名                                                   工资入职日期
---------------------------------------------- ---------- -----------
John Russell                                     14000.00 1996-10-1
Karen Partners                                   13500.00 1997-1-5
Alberto Errazuriz                                12000.00 1997-3-10
Nancy Greenberg                                  12000.00 1994-8-17
Lisa Ozer                                        11500.00 1997-3-11
Ellen Abel                                       11000.00 1996-5-11
Gerald Cambrault                                 11000.00 1999-10-15
Clara Vishney                                    10500.00 1997-11-11
Eleni Zlotkey                                    10500.00 2000-1-29
Harrison Bloom                                   10000.00 1998-3-23
Peter Tucker                                     10000.00 1997-1-30
Tayler Fox                                        9600.00 1998-1-24
Danielle Greene                                   9500.00 1999-3-19
David Bernstein                                   9500.00 1997-3-24
Peter Hall                                        9000.00 1997-8-20
Alyssa Hutton                                     8800.00 1997-3-19
Jonathon Taylor                                   8600.00 1998-3-24
Adam Fripp                                        8200.00 1997-4-10
Christopher Olsen                                 8000.00 1998-3-30
Jack Livingston                                   8000.00 1998-4-23
Matthew Weiss                                     8000.00 1996-7-18
Jose Manuel Urman                                 7800.00 1998-3-7
Nanette Cambrault                                 7500.00 1998-12-9
William Smith                                     7400.00 1999-2-23
Elizabeth Bates                                   7300.00 1999-3-24
Charles Johnson                                   7211.00 2000-1-4
Mattea Marvins                                    7200.00 2000-1-24
Shanta Vollman                                    6500.00 1997-10-10
Kevin Mourgos                                     5800.00 1999-11-16
Nandita Sarchand                                  4200.00 1996-1-27
Alexis Bull                                       4100.00 1997-2-20
Sarah Bell                                        4000.00 1996-2-4
Britney Everett                                   3900.00 1997-3-3
Kelly Chung                                       3800.00 1997-6-14
Jennifer Dilly                                    3600.00 1997-8-13
Julia Dellinger                                   3400.00 1998-6-24
Laura Bissot                                      3300.00 1997-8-20
Julia Nayer                                       3200.00 1997-7-16
Samuel McCain                                     3200.00 1998-7-1
Stephen Stiles                                    3200.00 1997-10-26
Winston Taylor                                    3200.00 1998-1-24
Alana Walsh                                       3100.00 1998-4-24
Jean Fleaur                                       3100.00 1998-2-23
Anthony Cabrio                                    3000.00 1999-2-7
Kevin Feeney                                      3000.00 1998-5-23
Michael Rogers                                    2900.00 1998-8-26
Shelli Baida                                      2900.00 1997-12-24
Timothy Gates                                     2900.00 1998-7-11
Girard Geoni                                      2800.00 2000-2-3
Mozhe Atkinson                                    2800.00 1997-10-30
Vance Jones                                       2800.00 1999-3-17
Irene Mikkilineni                                 2700.00 1998-9-28
John Seo                                          2700.00 1998-2-12
Donald OConnell                                   2600.00 1999-6-21
Douglas Grant                                     2600.00 2000-1-13
Randall Matos                                     2600.00 1998-3-15
Martha Sullivan                                   2500.00 1999-6-21
Randall Perkins                                   2500.00 1999-12-19
Ki Gee                                            2400.00 1999-12-12
Hazel Philtanker                                  2200.00 2000-2-6
Steven Markle                                     2200.00 2000-3-8

61 rows selected

　　经典查询练手第四篇

　　本篇数据查询属于复杂业务，难度比较高，请继续努力，通过我为大家设立的这个系列，循序渐进，只要你对每一篇，每一个试题都实践测试，认真练习。我相信你对常用、经典的、复杂的SQL已能熟能生巧，信手拈来!

　　本文使用ORACLE自带的人力资源(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 图：

　　用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
          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

/*--------6、哪些员工的工资，高于整个公司的平均工资，
  列出员工的名字和工资（降序）。---------*/

SQL> SELECT FIRST_NAME || ' ' || LAST_NAME,SALARY
   2  FROM EMPLOYEES
   3  WHERE SALARY > (
   4        SELECT AVG(SALARY)
   5        FROM EMPLOYEES
   6        )
   7  ORDER BY SALARY DESC;
FIRST_NAME||''||LAST_NAME                          SALARY
---------------------------------------------- ----------
Steven King                                      24000.00
Neena Kochhar                                    20000.00
Lex De Haan                                      20000.00
John Russell                                     14000.00
Karen Partners                                   13500.00
Michael Hartstein                                13000.00
Nancy Greenberg                                  12000.00
Alberto Errazuriz                                12000.00
Shelley Higgins                                  12000.00
Lisa Ozer                                        11500.00
Den Raphaely                                     11000.00
Gerald Cambrault                                 11000.00
Ellen Abel                                       11000.00
Eleni Zlotkey                                    10500.00
Clara Vishney                                    10500.00
Peter Tucker                                     10000.00
Janette King                                     10000.00
Harrison Bloom                                   10000.00
Hermann Baer                                     10000.00
Tayler Fox                                        9600.00
--共50条数据

/*--------7、哪些员工的工资，介于50号和80号部门平均工资之间。---------*/

SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY
  2  FROM EMPLOYEES
  3  WHERE SALARY
  4  BETWEEN
  5      (SELECT AVG(SALARY) FROM EMPLOYEES
  6       WHERE DEPARTMENT_ID = 50)
  7  AND (SELECT AVG(SALARY) FROM EMPLOYEES
  8       WHERE DEPARTMENT_ID = 80);

NAME                                               SALARY
---------------------------------------------- ----------
Bruce Ernst                                       6000.00
David Austin                                      4800.00
Valli Pataballa                                   4800.00
Diana Lorentz                                     4200.00
John Chen                                         8200.00
Ismael Sciarra                                    7700.00
Jose Manuel Urman                                 7800.00
Luis Popp                                         6900.00
Matthew Weiss                                     8000.00
Adam Fripp                                        8200.00
Payam Kaufling                                    7900.00
Shanta Vollman                                    6500.00
Kevin Mourgos                                     5800.00
Renske Ladwig                                     3600.00
Trenna Rajs                                       3500.00
Christopher Olsen                                 8000.00
Nanette Cambrault                                 7500.00
Oliver Tuvault                                    7000.00
Lindsey Smith                                     8000.00
Louise Doran                                      7500.00
Sarath Sewall                                     7000.00
Mattea Marvins                                    7200.00
David Lee                                         6800.00
Sundar Ande                                       6400.00
Amit Banda                                        6200.00
William Smith                                     7400.00
Elizabeth Bates                                   7300.00
Sundita Kumar                                     6100.00
Alyssa Hutton                                     8800.00
Jonathon Taylor                                   8600.00
Jack Livingston                                   8000.00
Kimberely Grant                                   7000.00
Charles Johnson                                   7211.00
Nandita Sarchand                                  4200.00
Alexis Bull                                       4100.00
Kelly Chung                                       3800.00
Jennifer Dilly                                    3600.00
Sarah Bell                                        4000.00
Britney Everett                                   3900.00
Jennifer Whalen                                   4400.00
Pat Fay                                           6000.00
Susan Mavris                                      6500.00
William Gietz                                     8300.00

43 rows selected

/*--------8、所在部门平均工资高于5000 的员工名字。---------*/

SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY
   2  FROM EMPLOYEES
   3  WHERE DEPARTMENT_ID IN
   4        (SELECT DEPARTMENT_ID FROM EMPLOYEES
   5         GROUP BY DEPARTMENT_ID
   6         HAVING AVG(SALARY) > 5000);

NAME                                               SALARY
---------------------------------------------- ----------
Michael Hartstein                                13000.00
Pat Fay                                           6000.00
Susan Mavris                                      6500.00
Alexander Hunold                                  9000.00
Bruce Ernst                                       6000.00
David Austin                                      4800.00
Valli Pataballa                                   4800.00
Diana Lorentz                                     4200.00
Hermann Baer                                     10000.00
John Russell                                     14000.00
Karen Partners                                   13500.00
Alberto Errazuriz                                12000.00
Gerald Cambrault                                 11000.00
Eleni Zlotkey                                    10500.00
Peter Tucker                                     10000.00
David Bernstein                                   9500.00
Peter Hall                                        9000.00
Christopher Olsen                                 8000.00
Nanette Cambrault                                 7500.00
Oliver Tuvault                                    7000.00
--等54行数据…

/*--------9、列出各个部门中工资最高的员工的信息：名字、部门号、工资。---------*/

SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME
   2         ,SALARY,DEPARTMENT_ID
   3  FROM EMPLOYEES
   4  WHERE (DEPARTMENT_ID,SALARY) IN
   5        (SELECT DEPARTMENT_ID,MAX(SALARY)
   6         FROM EMPLOYEES
   7         GROUP BY DEPARTMENT_ID);

NAME                                               SALARY DEPARTMENT_ID
---------------------------------------------- ---------- -------------
Jennifer Whalen                                   4400.00            10
Michael Hartstein                                13000.00            20
Den Raphaely                                     11000.00            30
Susan Mavris                                      6500.00            40
Adam Fripp                                        8200.00            50
Alexander Hunold                                  9000.00            60
Hermann Baer                                     10000.00            70
John Russell                                     14000.00            80
Steven King                                      24000.00            90
Nancy Greenberg                                  12000.00           100
Shelley Higgins                                  12000.00           110

11 rows selected

/*--------10、最高的部门平均工资是多少。---------*/
SQL> SELECT MAX(AVGSALARY)
  2  FROM(SELECT DEPARTMENT_ID,AVG(SALARY) AVGSALARY
  3    FROM EMPLOYEES
  4    GROUP BY DEPARTMENT_ID);

MAX(AVGSALARY)
--------------
21333.33333333

关注我们