ORACLE SQL 查询练手

SQL> conn scott/tiger@clonepdb_plug
Connected.
SQL> desc emp
Name Null? Type

---

EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> desc dept
Name Null? Type

---

DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
提示：工资＝SAL＋COMM
SQL> set line 100
SQL> select * from emp;

 EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

---

  7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
  7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
  7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
  7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
  7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
  7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
  7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
  7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
  7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
  7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
  7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20

 EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

---

  7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

12 rows selected.

SQL> select * from dept;

DEPTNO DNAME          LOC

---

    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO

1．列出至少有一个员工的所有部门。
SQL> select dname from dept where deptno in (select DEPTNO from emp);

DNAME

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

ENAME

ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
KING
TURNER
JAMES
FORD
MILLER

11 rows selected.
3．列出所有员工的姓名及其直接上级的姓名。
SQL> select a.ename,(select b.ename from emp b where a.mgr=b.empno) Boss from emp a;

ENAME BOSS

---

SMITH FORD
ALLEN BLAKE
WARD BLAKE
JONES KING
MARTIN BLAKE
BLAKE KING
CLARK KING
KING
TURNER BLAKE
JAMES BLAKE
FORD JONES

ENAME BOSS

---

MILLER CLARK

12 rows selected.
4．列出受雇日期早于其直接上级的所有员工。
SQL> select a.ename from emp a where a.HIREDATE>(select b.HIREDATE from emp b where a.mgr=b.empno);

ENAME

MARTIN
TURNER
JAMES
FORD
MILLER

5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SQL> select a.deptno,a.dname,b.ename,b.empno from dept a,emp b where a.deptno=b.deptno(+);

DEPTNO DNAME          ENAME           EMPNO

---

    10 ACCOUNTING     KING             7839
    10 ACCOUNTING     CLARK            7782
    10 ACCOUNTING     MILLER           7934
    20 RESEARCH       FORD             7902
    20 RESEARCH       SMITH            7369
    20 RESEARCH       JONES            7566
    30 SALES          JAMES            7900
    30 SALES          TURNER           7844
    30 SALES          MARTIN           7654
    30 SALES          WARD             7521
    30 SALES          ALLEN            7499

DEPTNO DNAME          ENAME           EMPNO

---

    30 SALES          BLAKE            7698

12 rows selected.
SQL> select a.deptno,a.dname,b.ename,b.empno from dept a left join emp b on a.deptno=b.deptno;

DEPTNO DNAME          ENAME           EMPNO

---

    10 ACCOUNTING     KING             7839
    10 ACCOUNTING     CLARK            7782
    10 ACCOUNTING     MILLER           7934
    20 RESEARCH       FORD             7902
    20 RESEARCH       SMITH            7369
    20 RESEARCH       JONES            7566
    30 SALES          JAMES            7900
    30 SALES          TURNER           7844
    30 SALES          MARTIN           7654
    30 SALES          WARD             7521
    30 SALES          ALLEN            7499

DEPTNO DNAME          ENAME           EMPNO

---

    30 SALES          BLAKE            7698

12 rows selected.
Oracle 外连接(OUTER JOIN)包括以下：

左外连接(左边的表不加限制)
右外连接(右边的表不加限制)
全外连接(左右两表都不加限制)
对应SQL：LEFT/RIGHT/FULL OUTER JOIN。 通常省略OUTER关键字， 写成：LEFT/RIGHT/FULL JOIN。

在左连接和右连接时都会以一张A表为基础表，该表的内容会全部显示，然后加上A表和B表匹配的内容。 如果A表的数据在B表中没有记录。 那么在相关联的结果集行中列显示为空值（NULL）。

对于外连接， 也可以使用“(+) ”来表示。 关于使用（+）的一些注意事项：

（+）操作符只能出现在WHERE子句中，并且不能与OUTER JOIN语法同时使用。
当使用（+）操作符执行外连接时，如果在WHERE子句中包含有多个条件，则必须在所有条件中都包含（+）操作符。
（+）操作符只适用于列，而不能用在表达式上。
（+）操作符不能与OR和IN操作符一起使用。
（+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。
LEFT JOIN是以左表的记录为基础的；用（+）来实现， 这个+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。如果加号写在右表，左表就是全部显示，所以是左连接。
全外连接（FULL OUTER JOIN/FULL JOIN）
左表和右表都不做限制，所有的记录都显示，两表不足的地方均为NULL。 全外连接不支持（+）写法。
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

---

MILLER ACCOUNTING
SMITH RESEARCH
JAMES SALES

SQL> select a.ename,b.dname from emp a ,dept b where a.deptno=b.deptno and a.job='CLERK';

ENAME DNAME

---

SMITH RESEARCH
JAMES SALES
MILLER ACCOUNTING
7．列出最低薪金大于1500的各种工作。
SQL> select distinct Job from emp group by job having min(sal)>1500 ;

JOB

PRESIDENT
MANAGER
ANALYST
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.
SQL> select ename from emp a join dept b on a.deptno=b.deptno and b.dname='SALES';

ENAME

JAMES
TURNER
MARTIN
WARD
ALLEN
BLAKE

6 rows selected.
SQL> select ename from emp a,dept b where a.deptno=b.deptno and b.dname='SALES';

ENAME

JAMES
TURNER
MARTIN
WARD
ALLEN
BLAKE

6 rows selected.
9．列出薪金高于公司平均薪金的所有员工。
SQL> select * from emp where sal >(select avg(sal) from emp);

 EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

---

  7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
  7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
  7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
  7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
  7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20

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

ENAME

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

no rows selected
12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
SQL> select a.ename,a.sal from emp a where a.sal > (select max(b.sal) from emp b where b.deptno=30) and a.deptno<>30;

ENAME SAL

---

JONES 2975
KING 5000
FORD 3000
13．列出在每个部门工作的员工数量、平均工资
SQL> select a.dname,(select count(*) from emp b where a.deptno=b.deptno) as DEPTCOUNT,(select avg(sal) from emp b where a.deptno=b.deptno) as deptavgsal from dept a;

DNAME DEPTCOUNT DEPTAVGSAL

---

ACCOUNTING 3 2916.66667
RESEARCH 3 2258.33333
SALES 6 1566.66667
SQL> select (select dname from dept b where a.deptno=b.deptno) as dname,count(a.deptno) as deptcount,avg(sal) as deptavgsal from emp a group by deptno
2 ;

DNAME DEPTCOUNT DEPTAVGSAL

---

SALES 6 1566.66667
RESEARCH 3 2258.33333
ACCOUNTING 3 2916.66667
14．列出所有员工的姓名、部门名称和工资。
SQL> select a.ename,b.dname,a.sal+NVL(a.comm,0) from emp a join dept b on a.deptno=b.deptno;

ENAME DNAME A.SAL+NVL(A.COMM,0)

---

KING ACCOUNTING 5000
CLARK ACCOUNTING 2450
MILLER ACCOUNTING 1300
FORD RESEARCH 3000
SMITH RESEARCH 800
JONES RESEARCH 2975
JAMES SALES 950
TURNER SALES 1500
MARTIN SALES 2650
WARD SALES 1750
ALLEN SALES 1900

ENAME DNAME A.SAL+NVL(A.COMM,0)

---

BLAKE SALES 2850
15．列出所有部门的详细信息和部门人数。
SQL> select a.dname,a.loc,(select count(*) from emp where deptno=a.deptno) from dept a;

DNAME LOC (SELECTCOUNT(*)FROMEMPWHEREDEPTNO=A.DEPTNO)

---

ACCOUNTING NEW YORK 3
RESEARCH DALLAS 3
SALES CHICAGO 6
16．列出各种工作的最低工资。
SQL> select job,avg(sal) from emp group by job;

JOB AVG(SAL)

---

CLERK 1016.66667
SALESMAN 1400
PRESIDENT 5000
MANAGER 2758.33333
ANALYST 3000
17．列出各个部门的MANAGER（经理）的最低薪金
SQL> select deptno,avg(sal) from emp where job='MANAGER' group by deptno;

DEPTNO   AVG(SAL)

---

    30       2850
    20       2975
    10       2450

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

ENAME (SAL+NVL(COMM,0))*12

---

SMITH 9600
JAMES 11400
MILLER 15600
TURNER 18000
WARD 21000
ALLEN 22800
CLARK 29400
MARTIN 31800
BLAKE 34200
JONES 35700
FORD 36000

ENAME (SAL+NVL(COMM,0))*12

---

KING 60000
SQL> select ename,(sal+nvl(comm,0))*12 as salpersal from emp order by salpersal ;

ENAME SALPERSAL

---

SMITH 9600
JAMES 11400
MILLER 15600
TURNER 18000
WARD 21000
ALLEN 22800
CLARK 29400
MARTIN 31800
BLAKE 34200
JONES 35700
FORD 36000

ENAME SALPERSAL

---

KING 60000

12 rows selected.
19．找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名。
SQL> select ename from emp where substr(ename,3,1)='A';

ENAME

BLAKE
CLARK
SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '__A%';

ENAME

BLAKE
CLARK

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

ENAME

ALLEN
MARTIN

21. 找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，
    佣金从大到小。
    SQL> select ename,sal+nvl(comm,0) as total ,comm from emp order by total, comm desc nulls last;

ENAME TOTAL COMM

---

SMITH 800
JAMES 950
MILLER 1300
TURNER 1500 0
WARD 1750 500
ALLEN 1900 300
CLARK 2450
MARTIN 2650 1400
BLAKE 2850
JONES 2975
FORD 3000

ENAME TOTAL COMM

---

KING 5000

12 rows selected.

22.列出部门编号为20的所有职位
SQL> select job from emp where deptno=20;

JOB

CLERK
MANAGER
ANALYST

23.列出不属于SALES 的部门
SQL> select dname from dept where dname!='SALES'
2 ;

DNAME

ACCOUNTING
RESEARCH

SQL> select dname from dept where dname<>'SALES';

DNAME

ACCOUNTING
RESEARCH

24. 显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序。
    SQL> select ename,sal+nvl(comm,0) as total from emp where sal+nvl(comm,0) not between 1000 and 1500 order by total desc;

ENAME TOTAL

---

KING 5000
FORD 3000
JONES 2975
BLAKE 2850
MARTIN 2650
CLARK 2450
ALLEN 1900
WARD 1750
JAMES 950
SMITH 800

10 rows selected.

25. 显示职位为MANAGER 和SALESMAN，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪。
    SQL> select ename,job,(sal+nvl(comm,0))12 as total from emp where (sal+nvl(comm,0))12 between 15000 and 20000;

ENAME JOB TOTAL

---

TURNER SALESMAN 18000
MILLER CLERK 15600

26. 说明以下两条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
    7839
    7900
    7902
    7934

8 rows selected.

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

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

27. 让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 DEPT;
SELECT FROM EMP;
SELECT FROM BONUS;
SELECT FROM SALGRADE;
SQL> SELECT concat(concat('select * from ',table_name),';') FROM user_tables;

CONCAT(CONCAT('SELECT*FROM',TABLE_NAME),';')

select from DEPT;
select from EMP;
select from BONUS;
select from SALGRADE;

28. 语句SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否报错？
    SQL> SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'
    2 ;

ENAME SAL

---

ALLEN 1600
JONES 2975
BLAKE 2850
CLARK 2450
KING 5000
FORD 3000

6 rows selected.
不会报错，这儿存在隐式数据类型的。
SQL> conn hr/hr@pdbtest
Connected.
SQL> desc employees;
Name Null? Type

---

EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)

SQL> desc DEPARTMENTS
Name Null? Type

---

DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)

SQL> desc REGIONS
Name Null? Type

---

REGION_ID NOT NULL NUMBER
REGION_NAME VARCHAR2(25)
29.让SELECT TO_CHAR(SALARY,'L99,999.99') FROM EMPLOYEES WHERE ROWNUM < 5 输出结果的货币单位是￥和$。
SQL> SELECT TO_CHAR(SALARY,'L99,999.99') FROM EMPLOYEES WHERE ROWNUM < 5;

TO_CHAR(SALARY,'L99,

      $24,000.00
      $17,000.00
      $17,000.00
       $9,000.00

30.列出前五位每个员工的名字，工资、涨薪后的的工资（涨幅为8%），以“元”为单位进行四舍五入。
SQL> select FIRST_NAME,SALARY,round(SALARY*1.08) from EMPLOYEES where rownum <6;

FIRST_NAME SALARY ROUND(SALARY*1.08)

---

Steven 24000 25920
Neena 17000 18360
Lex 17000 18360
Alexander 9000 9720
Bruce 6000 6480
31.、找出谁是最高领导，将名字按大写形式显示。
SQL> select upper(FIRST_NAME||' '||LAST_NAME) from EMPLOYEES where MANAGER_ID is null;

UPPER(FIRST_NAME||''||LAST_NAME)

STEVEN KING
32.找出First_Name 为David，Last_Name为Austin 的直接领导名字。
SQL> select FIRST_NAME||' '||LAST_NAME from EMPLOYEES where EMPLOYEE_ID=(select MANAGER_ID from EMPLOYEES where FIRST_NAME='David' and LAST_NAME='Austin');

FIRST_NAME||''||LAST_NAME

Alexander Hunold
33.First_Name 为Alexander，LAST_NAME为Hunold领导谁
SQL> select FIRST_NAME||' '||LAST_NAME from EMPLOYEES where MANAGER_ID=(select EMPLOYEE_ID from EMPLOYEES where FIRST_NAME='Alexander' and LAST_NAME='Hunold');

FIRST_NAME||''||LAST_NAME

Bruce Ernst
David Austin
Valli Pataballa
Diana Lorentz
34.哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资。
SQL> select FIRST_NAME||' '||LAST_NAME,salary,(select FIRST_NAME||' '||LAST_NAME from EMPLOYEES where EMPLOYEE_id=a.manager_id) as boss_name,(select salary from EMPLOYEES where EMPLOYEE_id=a.manager_id ) as boss_salary from EMPLOYEES a where a.salary>(select salary from EMPLOYEES where EMPLOYEE_id=a.manager_id);

Lisa Ozer 11500 Gerald Cambrault 11000
Ellen Abel 11000 Eleni Zlotkey 10500
SQL> select a.FIRST_NAME||' '||a.LAST_NAME,a.salary,b.FIRST_NAME||' '||b.LAST_NAME,b.salary from EMPLOYEES a join EMPLOYEES b on b.EMPLOYEE_id=a.manager_id and a.salary>b.salary;

Lisa Ozer 11500 Gerald Cambrault 11000
Ellen Abel 11000 Eleni Zlotkey 10500
35.哪些员工和Chen(LAST_NAME)同部门
SQL> select EMPLOYEE_ID,FIRST_NAME from EMPLOYEES where DEPARTMENT_ID=(select DEPARTMENT_ID from EMPLOYEES where LAST_NAME='Chen') and LAST_NAME!='Chen';

EMPLOYEE_ID FIRST_NAME

---

    108 Nancy
    109 Daniel
    111 Ismael
    112 Jose Manuel
    113 Luis

SQL> select EMPLOYEE_ID,FIRST_NAME from EMPLOYEES a join (select DEPARTMENT_ID from EMPLOYEES where LAST_NAME='Chen') b using(DEPARTMENT_ID) where LAST_NAME!='Chen';

EMPLOYEE_ID FIRST_NAME

---

    108 Nancy
    109 Daniel
    111 Ismael
    112 Jose Manuel
    113 Luis                

36.哪些员工跟De Haan(LAST_NAME)做一样职位
SQL> select EMPLOYEE_ID,FIRST_NAME from EMPLOYEES a join (select job_id from EMPLOYEES where LAST_NAME='De Haan') b using (job_id) where LAST_NAME!='De Haan'
2 ;

EMPLOYEE_ID FIRST_NAME

---

    101 Neena

SQL> select EMPLOYEE_ID,FIRST_NAME from EMPLOYEES a join (select job_id from EMPLOYEES where LAST_NAME='De Haan') b on a.job_id=b.job_id and LAST_NAME!='De Haan'
2 ;

EMPLOYEE_ID FIRST_NAME

---

    101 Neena

37.哪些员工跟Hall(LAST_NAME)不在同一个部门。
SQL> select EMPLOYEE_ID,FIRST_NAME from EMPLOYEES a join (select DEPARTMENT_ID from EMPLOYEES where LAST_NAME='Hall') b on a.DEPARTMENT_ID!=b.DEPARTMENT_ID ;

EMPLOYEE_ID FIRST_NAME

---

    100 Steven
    101 Neena
    102 Lex
    103 Alexander
    104 Bruce
    105 David
    106 Valli
    107 Diana
    108 Nancy
    109 Daniel
    110 John

EMPLOYEE_ID FIRST_NAME

---

    111 Ismael
    112 Jose Manuel
    113 Luis
    114 Den
    115 Alexander
    116 Shelli
    117 Sigal
    118 Guy
    119 Karen
    120 Matthew
    121 Adam

EMPLOYEE_ID FIRST_NAME

---

    122 Payam
    123 Shanta
    124 Kevin
    125 Julia
    126 Irene
    127 James
    128 Steven
    129 Laura
    130 Mozhe
    131 James
    132 TJ

EMPLOYEE_ID FIRST_NAME

---

    133 Jason
    134 Michael
    135 Ki
    136 Hazel
    137 Renske
    138 Stephen
    139 John
    140 Joshua
    141 Trenna
    142 Curtis
    143 Randall

EMPLOYEE_ID FIRST_NAME

---

    144 Peter
    180 Winston
    181 Jean
    182 Martha
    183 Girard
    184 Nandita
    185 Alexis
    186 Julia
    187 Anthony
    188 Kelly
    189 Jennifer

EMPLOYEE_ID FIRST_NAME

---

    190 Timothy
    191 Randall
    192 Sarah
    193 Britney
    194 Samuel
    195 Vance
    196 Alana
    197 Kevin
    198 Donald
    199 Douglas
    200 Jennifer

EMPLOYEE_ID FIRST_NAME

---

    201 Michael
    202 Pat
    203 Susan
    204 Hermann
    205 Shelley
    206 William

72 rows selected.
38.哪些员工跟William（FIRST_NAME）、Smith(LAST_NAME)做不一样的职位。
SQL> select EMPLOYEE_ID,FIRST_NAME from EMPLOYEES a join (select job_ID from EMPLOYEES where FIRST_NAME='William'and LAST_NAME='Smith') b on a.job_ID!=b.job_ID ;

EMPLOYEE_ID FIRST_NAME

---

    100 Steven
    101 Neena
    102 Lex
    103 Alexander
    104 Bruce
    105 David
    106 Valli
    107 Diana
    108 Nancy
    109 Daniel
    110 John

EMPLOYEE_ID FIRST_NAME

---

    111 Ismael
    112 Jose Manuel
    113 Luis
    114 Den
    115 Alexander
    116 Shelli
    117 Sigal
    118 Guy
    119 Karen
    120 Matthew
    121 Adam

EMPLOYEE_ID FIRST_NAME

---

    122 Payam
    123 Shanta
    124 Kevin
    125 Julia
    126 Irene
    127 James
    128 Steven
    129 Laura
    130 Mozhe
    131 James
    132 TJ

EMPLOYEE_ID FIRST_NAME

---

    133 Jason
    134 Michael
    135 Ki
    136 Hazel
    137 Renske
    138 Stephen
    139 John
    140 Joshua
    141 Trenna
    142 Curtis
    143 Randall

EMPLOYEE_ID FIRST_NAME

---

    144 Peter
    145 John
    146 Karen
    147 Alberto
    148 Gerald
    149 Eleni
    180 Winston
    181 Jean
    182 Martha
    183 Girard
    184 Nandita

EMPLOYEE_ID FIRST_NAME

---

    185 Alexis
    186 Julia
    187 Anthony
    188 Kelly
    189 Jennifer
    190 Timothy
    191 Randall
    192 Sarah
    193 Britney
    194 Samuel
    195 Vance

EMPLOYEE_ID FIRST_NAME

---

    196 Alana
    197 Kevin
    198 Donald
    199 Douglas
    200 Jennifer
    201 Michael
    202 Pat
    203 Susan
    204 Hermann
    205 Shelley
    206 William

77 rows selected.
39.显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称。
SQL> select FIRST_NAME||' '||LAST_NAME, COMMISSION_PCT from EMPLOYEES a join (DEPARTMENTS b join LOCATIONS c using(LOCATION_ID)) using(DEPARTMENT_ID) where COMMISSION_PCT is not null;

John Russell .4
Karen Partners .3
Alberto Errazuriz .3
Gerald Cambrault .3
Eleni Zlotkey .2
Peter Tucker .3
David Bernstein .25
Peter Hall .25
Christopher Olsen .2
Nanette Cambrault .2
Oliver Tuvault .15

Janette King .35
Patrick Sully .35
Allan McEwen .35
Lindsey Smith .3
Louise Doran .3
Sarath Sewall .25
Clara Vishney .25
Danielle Greene .15
Mattea Marvins .1
David Lee .1
Sundar Ande .1

Amit Banda .1
Lisa Ozer .25
Harrison Bloom .2
Tayler Fox .2
William Smith .15
Elizabeth Bates .15
Sundita Kumar .1
Ellen Abel .3
Alyssa Hutton .25
Jonathon Taylor .2
Jack Livingston .2

Charles Johnson .1

34 rows selected.
SQL> select FIRST_NAME||' '||LAST_NAME, COMMISSION_PCT from EMPLOYEES a ,DEPARTMENTS b,HR.LOCATIONS c where a.DEPARTMENT_ID = b.DEPARTMENT_ID and a.COMMISSION_PCT is not null and b.LOCATION_ID = c.LOCATION_ID;

John Russell .4
Karen Partners .3
Alberto Errazuriz .3
Gerald Cambrault .3
Eleni Zlotkey .2
Peter Tucker .3
David Bernstein .25
Peter Hall .25
Christopher Olsen .2
Nanette Cambrault .2
Oliver Tuvault .15

Janette King .35
Patrick Sully .35
Allan McEwen .35
Lindsey Smith .3
Louise Doran .3
Sarath Sewall .25
Clara Vishney .25
Danielle Greene .15
Mattea Marvins .1
David Lee .1
Sundar Ande .1

Amit Banda .1
Lisa Ozer .25
Harrison Bloom .2
Tayler Fox .2
William Smith .15
Elizabeth Bates .15
Sundita Kumar .1
Ellen Abel .3
Alyssa Hutton .25
Jonathon Taylor .2
Jack Livingston .2

Charles Johnson .1

34 rows selected.
40.示Executive部门有哪些职位
SQL> SELECT DISTINCT JOB_ID FROM EMPLOYEES a join DEPARTMENTS b using(DEPARTMENT_ID) where b.DEPARTMENT_NAME = 'Executive';

JOB_ID

AD_VP
AD_PRES
41.整个公司中，最高工资和最低工资相差多少。
SQL> SELECT MAX(SALARY) - MIN(SALARY) FROM EMPLOYEES;

MAX(SALARY)-MIN(SALARY)

              21900

42.提成大于0 的人数。
SQL> SELECT count(*) FROM EMPLOYEES where COMMISSION_PCT>0;

COUNT(*)

    35

43.显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。
SQL> SELECT MAX(SALARY),MIN(SALARY),sum(SALARY),avg(SALARY) FROM EMPLOYEES;

MAX(SALARY) MIN(SALARY) SUM(SALARY) AVG(SALARY)

---

  24000        2100      691416  6461.83178

44.整个公司有多少个领导。
SQL> SELECT count(DISTINCT(NVL(manager_id,'1'))) FROM employees e;

COUNT(DISTINCT(NVL(MANAGER_ID,'1')))

                              19

45.列出在同一部门入职日期晚但工资高于其他同事的员工：
名字、工资、入职日期。
SQL> select distinct a.FIRST_NAME,a.SALARY,a.HIRE_DATE from employees a join employees b on a.DEPARTMENT_ID=b.DEPARTMENT_ID and a.SALARY>b.SALARY and a.HIRE_DATE>b.HIRE_DATE order by a.salary desc;

FIRST_NAME SALARY HIRE_DATE

---

Steven 24000 2003-06-17 00:00:00
John 14000 2004-10-01 00:00:00
Karen 13500 2005-01-05 00:00:00
Nancy 12008 2002-08-17 00:00:00
Alberto 12000 2005-03-10 00:00:00
Lisa 11500 2005-03-11 00:00:00
Ellen 11000 2004-05-11 00:00:00
Gerald 11000 2007-10-15 00:00:00
Clara 10500 2005-11-11 00:00:00
Eleni 10500 2008-01-29 00:00:00
Harrison 10000 2006-03-23 00:00:00

FIRST_NAME SALARY HIRE_DATE

---

Peter 10000 2005-01-30 00:00:00
Tayler 9600 2006-01-24 00:00:00
Danielle 9500 2007-03-19 00:00:00
David 9500 2005-03-24 00:00:00
Alexander 9000 2006-01-03 00:00:00
Peter 9000 2005-08-20 00:00:00
Alyssa 8800 2005-03-19 00:00:00
Jonathon 8600 2006-03-24 00:00:00
Jack 8400 2006-04-23 00:00:00
Adam 8200 2005-04-10 00:00:00
Christopher 8000 2006-03-30 00:00:00

FIRST_NAME SALARY HIRE_DATE

---

Matthew 8000 2004-07-18 00:00:00
Jose Manuel 7800 2006-03-07 00:00:00
Nanette 7500 2006-12-09 00:00:00
William 7400 2007-02-23 00:00:00
Elizabeth 7300 2007-03-24 00:00:00
Mattea 7200 2008-01-24 00:00:00
David 6800 2008-02-23 00:00:00
Shanta 6500 2005-10-10 00:00:00
Sundar 6400 2008-03-24 00:00:00
Bruce 6000 2007-05-21 00:00:00
Kevin 5800 2007-11-16 00:00:00

FIRST_NAME SALARY HIRE_DATE

---

Nandita 4200 2004-01-27 00:00:00
Alexis 4100 2005-02-20 00:00:00
Sarah 4000 2004-02-04 00:00:00
Britney 3900 2005-03-03 00:00:00
Kelly 3800 2005-06-14 00:00:00
Jennifer 3600 2005-08-13 00:00:00
Julia 3400 2006-06-24 00:00:00
Laura 3300 2005-08-20 00:00:00
Julia 3200 2005-07-16 00:00:00
Samuel 3200 2006-07-01 00:00:00
Stephen 3200 2005-10-26 00:00:00

FIRST_NAME SALARY HIRE_DATE

---

Winston 3200 2006-01-24 00:00:00
Alana 3100 2006-04-24 00:00:00
Jean 3100 2006-02-23 00:00:00
Anthony 3000 2007-02-07 00:00:00
Kevin 3000 2006-05-23 00:00:00
Michael 2900 2006-08-26 00:00:00
Shelli 2900 2005-12-24 00:00:00
Timothy 2900 2006-07-11 00:00:00
Girard 2800 2008-02-03 00:00:00
Mozhe 2800 2005-10-30 00:00:00
Vance 2800 2007-03-17 00:00:00

FIRST_NAME SALARY HIRE_DATE

---

Irene 2700 2006-09-28 00:00:00
John 2700 2006-02-12 00:00:00
Donald 2600 2007-06-21 00:00:00
Douglas 2600 2008-01-13 00:00:00
Randall 2600 2006-03-15 00:00:00
Martha 2500 2007-06-21 00:00:00
Randall 2500 2007-12-19 00:00:00
Ki 2400 2007-12-12 00:00:00
Hazel 2200 2008-02-06 00:00:00
Steven 2200 2008-03-08 00:00:00

65 rows selected.
SQL> select distinct a.FIRST_NAME,a.SALARY,a.HIRE_DATE from employees a join employees b using(DEPARTMENT_ID) where a.SALARY>b.SALARY and a.HIRE_DATE>b.HIRE_DATE order by SALARY desc;

FIRST_NAME SALARY HIRE_DATE

---

Steven 24000 2003-06-17 00:00:00
John 14000 2004-10-01 00:00:00
Karen 13500 2005-01-05 00:00:00
Nancy 12008 2002-08-17 00:00:00
Alberto 12000 2005-03-10 00:00:00
Lisa 11500 2005-03-11 00:00:00
Ellen 11000 2004-05-11 00:00:00
Gerald 11000 2007-10-15 00:00:00
Clara 10500 2005-11-11 00:00:00
Eleni 10500 2008-01-29 00:00:00
Harrison 10000 2006-03-23 00:00:00

FIRST_NAME SALARY HIRE_DATE

---

Peter 10000 2005-01-30 00:00:00
Tayler 9600 2006-01-24 00:00:00
Danielle 9500 2007-03-19 00:00:00
David 9500 2005-03-24 00:00:00
Alexander 9000 2006-01-03 00:00:00
Peter 9000 2005-08-20 00:00:00
Alyssa 8800 2005-03-19 00:00:00
Jonathon 8600 2006-03-24 00:00:00
Jack 8400 2006-04-23 00:00:00
Adam 8200 2005-04-10 00:00:00
Christopher 8000 2006-03-30 00:00:00

FIRST_NAME SALARY HIRE_DATE

---

Matthew 8000 2004-07-18 00:00:00
Jose Manuel 7800 2006-03-07 00:00:00
Nanette 7500 2006-12-09 00:00:00
William 7400 2007-02-23 00:00:00
Elizabeth 7300 2007-03-24 00:00:00
Mattea 7200 2008-01-24 00:00:00
David 6800 2008-02-23 00:00:00
Shanta 6500 2005-10-10 00:00:00
Sundar 6400 2008-03-24 00:00:00
Bruce 6000 2007-05-21 00:00:00
Kevin 5800 2007-11-16 00:00:00

FIRST_NAME SALARY HIRE_DATE

---

Nandita 4200 2004-01-27 00:00:00
Alexis 4100 2005-02-20 00:00:00
Sarah 4000 2004-02-04 00:00:00
Britney 3900 2005-03-03 00:00:00
Kelly 3800 2005-06-14 00:00:00
Jennifer 3600 2005-08-13 00:00:00
Julia 3400 2006-06-24 00:00:00
Laura 3300 2005-08-20 00:00:00
Julia 3200 2005-07-16 00:00:00
Samuel 3200 2006-07-01 00:00:00
Stephen 3200 2005-10-26 00:00:00

FIRST_NAME SALARY HIRE_DATE

---

Winston 3200 2006-01-24 00:00:00
Alana 3100 2006-04-24 00:00:00
Jean 3100 2006-02-23 00:00:00
Anthony 3000 2007-02-07 00:00:00
Kevin 3000 2006-05-23 00:00:00
Michael 2900 2006-08-26 00:00:00
Shelli 2900 2005-12-24 00:00:00
Timothy 2900 2006-07-11 00:00:00
Girard 2800 2008-02-03 00:00:00
Mozhe 2800 2005-10-30 00:00:00
Vance 2800 2007-03-17 00:00:00

FIRST_NAME SALARY HIRE_DATE

---

Irene 2700 2006-09-28 00:00:00
John 2700 2006-02-12 00:00:00
Donald 2600 2007-06-21 00:00:00
Douglas 2600 2008-01-13 00:00:00
Randall 2600 2006-03-15 00:00:00
Martha 2500 2007-06-21 00:00:00
Randall 2500 2007-12-19 00:00:00
Ki 2400 2007-12-12 00:00:00
Hazel 2200 2008-02-06 00:00:00
Steven 2200 2008-03-08 00:00:00

65 rows selected.
46.各个部门平均、最大、最小工资、人数，按照部门号升序排列。
47.SQL> select distinct DEPARTMENT_ID,max(SALARY),min(salary),count(*) from employees group by DEPARTMENT_ID order by DEPARTMENT_ID;

DEPARTMENT_ID MAX(SALARY) MIN(SALARY) COUNT(*)

---

       10        4400        4400          1
       20       13000        6000          2
       30       11000        2500          6
       40        6500        6500          1
       50        8200        2100         45
       60        9000        4200          5
       70       10000       10000          1
       80       14000        6100         34
       90       24000       17000          3
      100       12008        6900          6
      110       12008        8300          2

DEPARTMENT_ID MAX(SALARY) MIN(SALARY) COUNT(*)

---

                 7000        7000          1

12 rows selected.
47.各个部门中工资大于5000的员工人数
SQL> select distinct DEPARTMENT_ID,count(*) from employees where salary>5000 group by DEPARTMENT_ID ;

DEPARTMENT_ID COUNT(*)

---

      100          6
       30          1
                   1
       90          3
       20          2
       70          1
      110          2
       50          5
       80         34
       40          1
       60          2

11 rows selected.
48.各个部门平均工资和人数，按照部门名字升序排列
SQL> select DEPARTMENT_name,avg(a.salary),count(*) from employees a left join DEPARTMENTS b using (DEPARTMENT_ID) group by DEPARTMENT_NAME order by b.DEPARTMENT_NAME ;

DEPARTMENT_NAME AVG(A.SALARY) COUNT(*)

---

Accounting 10154 2
Administration 4400 1
Executive 19333.3333 3
Finance 8601.33333 6
Human Resources 6500 1
IT 5760 5
Marketing 9500 2
Public Relations 10000 1
Purchasing 4150 6
Sales 8955.88235 34
Shipping 3475.55556 45

DEPARTMENT_NAME AVG(A.SALARY) COUNT(*)

---

                                    7000          1

12 rows selected.

SQL> select avg(a.salary),count(*) from employees a left join DEPARTMENTS b on a.DEPARTMENT_ID=b.DEPARTMENT_id group by DEPARTMENT_NAME order by b.DEPARTMENT_NAME;

AVG(A.SALARY) COUNT(*)

---

    10154          2
     4400          1

19333.3333 3
8601.33333 6
6500 1
5760 5
9500 2
10000 1
4150 6
8955.88235 34
3475.55556 45

AVG(A.SALARY) COUNT(*)

---

     7000          1

49.列出每个部门中有同样工资的员工的统计信息，
列出他们的部门号，工资，人数。
SQL> select DEPARTMENT_id,salary,count() from employees group by DEPARTMENT_id, salary having count()>1;

DEPARTMENT_ID SALARY COUNT(*)

---

       90      17000          2
       50       3200          4
       50       2200          2
       50       3600          2
       80      10500          2
       80       9000          2
       50       2700          2
       50       3100          3
       80      10000          3
       50       3000          2
       60       4800          2

DEPARTMENT_ID SALARY COUNT(*)

---

       50       3300          2
       80       6200          2
       50       2800          3
       50       2500          5
       50       2600          3
       50       2400          2
       80       9500          3
       80       7500          2
       80      11000          2
       80       7000          2
       50       2900          2

DEPARTMENT_ID SALARY COUNT(*)

---

       80       8000          2

23 rows selected.
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

---

       90      17000          2
       50       3200         12
       50       2200          2
       50       3600          2
       80      10500          2
       80       9000          2
       50       2700          2
       50       3100          6
       80      10000          6
       50       3000          2
       60       4800          2

DEPARTMENT_ID SALARY CNT

---

       50       3300          2
       80       6200          2
       50       2800          6
       50       2500         20
       50       2600          6
       50       2400          2
       80       9500          6
       80       7500          2
       80      11000          2
       80       7000          2
       50       2900          2

DEPARTMENT_ID SALARY CNT

---

       80       8000          2

23 rows selected.
50.列出同部门中工资高于1000 的员工数量超过2 人的部门，
显示部门名字、地区名称。
SQL> select b.DEPARTMENT_NAME,c.CITY,count() from EMPLOYEES a join (DEPARTMENTS b join LOCATIONS c using(LOCATION_ID)) using(DEPARTMENT_ID) where a.SALARY > 1000 GROUP BY b.DEPARTMENT_NAME,c.CITY having count()>2;

DEPARTMENT_NAME CITY COUNT(*)

---

IT Southlake 5
Sales Oxford 34
Shipping South San Francisco 45
Purchasing Seattle 6
Executive Seattle 3
Finance Seattle 6

6 rows selected.
51.哪些员工的工资，高于整个公司的平均工资，
列出员工的名字和工资（降序）
SQL> select first_name||' '||last_name,salary from EMPLOYEES where salary>(select avg(salary) from EMPLOYEES) order by salary desc;

Steven King 24000
Neena Kochhar 17000
Lex De Haan 17000
John Russell 14000
Karen Partners 13500
Michael Hartstein 13000
Nancy Greenberg 12008
Shelley Higgins 12008
Alberto Errazuriz 12000
Lisa Ozer 11500
Ellen Abel 11000

Den Raphaely 11000
Gerald Cambrault 11000
Clara Vishney 10500
Eleni Zlotkey 10500
Peter Tucker 10000
Harrison Bloom 10000
Janette King 10000
Hermann Baer 10000
Tayler Fox 9600
David Bernstein 9500
Danielle Greene 9500

Patrick Sully 9500
Daniel Faviet 9000
Alexander Hunold 9000
Peter Hall 9000
Allan McEwen 9000
Alyssa Hutton 8800
Jonathon Taylor 8600
Jack Livingston 8400
William Gietz 8300
Adam Fripp 8200
John Chen 8200

Christopher Olsen 8000
Matthew Weiss 8000
Lindsey Smith 8000
Payam Kaufling 7900
Jose Manuel Urman 7800
Ismael Sciarra 7700
Louise Doran 7500
Nanette Cambrault 7500
William Smith 7400
Elizabeth Bates 7300
Mattea Marvins 7200

Oliver Tuvault 7000
Kimberely Grant 7000
Sarath Sewall 7000
Luis Popp 6900
David Lee 6800
Susan Mavris 6500
Shanta Vollman 6500

51 rows selected.
52.哪些员工的工资，介于50号 和80号 部门平均工资之间
SQL> select first_name||' '||last_name,salary from EMPLOYEES where salary between (select avg(salary) from EMPLOYEES where DEPARTMENT_ID = 50) and (SELECT AVG(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = 80);

Bruce Ernst 6000
David Austin 4800
Valli Pataballa 4800
Diana Lorentz 4200
John Chen 8200
Ismael Sciarra 7700
Jose Manuel Urman 7800
Luis Popp 6900
Matthew Weiss 8000
Adam Fripp 8200
Payam Kaufling 7900

Shanta Vollman 6500
Kevin Mourgos 5800
Renske Ladwig 3600
Trenna Rajs 3500
Christopher Olsen 8000
Nanette Cambrault 7500
Oliver Tuvault 7000
Lindsey Smith 8000
Louise Doran 7500
Sarath Sewall 7000
Mattea Marvins 7200

David Lee 6800
Sundar Ande 6400
Amit Banda 6200
William Smith 7400
Elizabeth Bates 7300
Sundita Kumar 6100
Alyssa Hutton 8800
Jonathon Taylor 8600
Jack Livingston 8400
Kimberely Grant 7000
Charles Johnson 6200

Nandita Sarchand 4200
Alexis Bull 4100
Kelly Chung 3800
Jennifer Dilly 3600
Sarah Bell 4000
Britney Everett 3900
Jennifer Whalen 4400
Pat Fay 6000
Susan Mavris 6500
William Gietz 8300

43 rows selected.
53.所在部门平均工资高于5000 的员工名字。
SQL> select first_name||' '||last_name,salary from EMPLOYEES where DEPARTMENT_ID IN (select distinct DEPARTMENT_ID from EMPLOYEES group by DEPARTMENT_ID having avg(salary)>5000);

Steven King 24000
Neena Kochhar 17000
Lex De Haan 17000
Alexander Hunold 9000
Bruce Ernst 6000
David Austin 4800
Valli Pataballa 4800
Diana Lorentz 4200
Nancy Greenberg 12008
Daniel Faviet 9000
John Chen 8200

Ismael Sciarra 7700
Jose Manuel Urman 7800
Luis Popp 6900
John Russell 14000
Karen Partners 13500
Alberto Errazuriz 12000
Gerald Cambrault 11000
Eleni Zlotkey 10500
Peter Tucker 10000
David Bernstein 9500
Peter Hall 9000

Christopher Olsen 8000
Nanette Cambrault 7500
Oliver Tuvault 7000
Janette King 10000
Patrick Sully 9500
Allan McEwen 9000
Lindsey Smith 8000
Louise Doran 7500
Sarath Sewall 7000
Clara Vishney 10500
Danielle Greene 9500

Mattea Marvins 7200
David Lee 6800
Sundar Ande 6400
Amit Banda 6200
Lisa Ozer 11500
Harrison Bloom 10000
Tayler Fox 9600
William Smith 7400
Elizabeth Bates 7300
Sundita Kumar 6100
Ellen Abel 11000

Alyssa Hutton 8800
Jonathon Taylor 8600
Jack Livingston 8400
Charles Johnson 6200
Michael Hartstein 13000
Pat Fay 6000
Susan Mavris 6500
Hermann Baer 10000
Shelley Higgins 12008
William Gietz 8300
SQL> select first_name||' '||last_name,salary from EMPLOYEES join (select distinct DEPARTMENT_ID from EMPLOYEES group by DEPARTMENT_ID having avg(salary)>5000) using(DEPARTMENT_ID);

Steven King 24000
Neena Kochhar 17000
Lex De Haan 17000
Alexander Hunold 9000
Bruce Ernst 6000
David Austin 4800
Valli Pataballa 4800
Diana Lorentz 4200
Nancy Greenberg 12008
Daniel Faviet 9000
John Chen 8200

Ismael Sciarra 7700
Jose Manuel Urman 7800
Luis Popp 6900
John Russell 14000
Karen Partners 13500
Alberto Errazuriz 12000
Gerald Cambrault 11000
Eleni Zlotkey 10500
Peter Tucker 10000
David Bernstein 9500
Peter Hall 9000

Christopher Olsen 8000
Nanette Cambrault 7500
Oliver Tuvault 7000
Janette King 10000
Patrick Sully 9500
Allan McEwen 9000
Lindsey Smith 8000
Louise Doran 7500
Sarath Sewall 7000
Clara Vishney 10500
Danielle Greene 9500

Mattea Marvins 7200
David Lee 6800
Sundar Ande 6400
Amit Banda 6200
Lisa Ozer 11500
Harrison Bloom 10000
Tayler Fox 9600
William Smith 7400
Elizabeth Bates 7300
Sundita Kumar 6100
Ellen Abel 11000

Alyssa Hutton 8800
Jonathon Taylor 8600
Jack Livingston 8400
Charles Johnson 6200
Michael Hartstein 13000
Pat Fay 6000
Susan Mavris 6500
Hermann Baer 10000
Shelley Higgins 12008
William Gietz 8300

54 rows selected.
54.列出各个部门中工资最高的员工的信息：名字、部门号、工资。
55.SQL> select first_name||' '||last_name,DEPARTMENT_ID,salary from EMPLOYEES where (DEPARTMENT_ID,salary) in (SELECT DEPARTMENT_ID,MAX(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID);

Nancy Greenberg 100 12008
Den Raphaely 30 11000
Steven King 90 24000
Michael Hartstein 20 13000
Hermann Baer 70 10000
Shelley Higgins 110 12008
Adam Fripp 50 8200
John Russell 80 14000
Susan Mavris 40 6500
Alexander Hunold 60 9000
Jennifer Whalen 10 4400

11 rows selected.
SQL> select first_name||' '||last_name,DEPARTMENT_ID,salary from EMPLOYEES join (SELECT DEPARTMENT_ID,MAX(SALARY) as salary FROM EMPLOYEES GROUP BY DEPARTMENT_ID) using (DEPARTMENT_ID,salary);

Steven King 90 24000
Alexander Hunold 60 9000
Nancy Greenberg 100 12008
Den Raphaely 30 11000
Adam Fripp 50 8200
John Russell 80 14000
Jennifer Whalen 10 4400
Michael Hartstein 20 13000
Susan Mavris 40 6500
Hermann Baer 70 10000
Shelley Higgins 110 12008

11 rows selected.
55.最高的部门平均工资是多少。
SQL> select max(AVGSALARY) from (SELECT DEPARTMENT_ID,AVG(SALARY) AVGSALARY FROM EMPLOYEES GROUP BY DEPARTMENT_ID);

MAX(AVGSALARY)

19333.3333
19334.SQL> select max(avg(salary))

2 from employees
3 group by department_id;

MAX(AVG(SALARY))

  19333.3333

56.哪些部门的人数比90 号部门的人数多。
SQL> select department_id, count() from employees group by department_id having count()>(select count(*) from employees where department_id=90);

DEPARTMENT_ID COUNT(*)

---

      100          6
       30          6
       50         45
       80         34
       60          5

57.Den(FIRST_NAME)、Raphaely(LAST_NAME)的领导是谁（非关联子查询）
SQL> select first_name || ' '||last_name from employees where employee_ID = (select manager_ID from employees where FIRST_NAME='Den' and LAST_NAME='Raphaely');

FIRST_NAME||''||LAST

Steven King

SQL> select first_name || ' '||last_name from employees where employee_ID in (select manager_ID from employees where FIRST_NAME='Den' and LAST_NAME='Raphaely');

FIRST_NAME||''||LAST

Steven King
58.Den(FIRST_NAME)、Raphaely(LAST_NAME) 领导谁（非关联子查询）。
SQL> select first_name || ' '||last_name from employees where MANAGER_ID in (select EMPLOYEE_ID from employees where FIRST_NAME='Den' and LAST_NAME='Raphaely');

FIRST_NAME||''||LAST

Alexander Khoo
Shelli Baida
Sigal Tobias
Guy Himuro
Karen Colmenares
SQL> select first_name || ' '||last_name from employees where MANAGER_ID = (select EMPLOYEE_ID from employees where FIRST_NAME='Den' and LAST_NAME='Raphaely');

FIRST_NAME||''||LAST

Alexander Khoo
Shelli Baida
Sigal Tobias
Guy Himuro
Karen Colmenares
59.Den(FIRST_NAME)、Raphaely(LAST_NAME) 的领导是谁（关联子查询）。
SQL> SELECT FIRST_NAME || ' ' || LAST_NAME
2 FROM EMPLOYEES EMP1
3 WHERE EXISTS (
4 SELECT 1 FROM EMPLOYEES EMP2
5 WHERE FIRST_NAME = 'Den'
6 AND LAST_NAME = 'Raphaely'
7 AND EMP1.EMPLOYEE_ID = EMP2.MANAGER_ID);

FIRST_NAME||''||LAST

Steven King
60.Den(FIRST_NAME)、Raphaely(LAST_NAME) 领导谁（关联子查询）
SQL> SELECT FIRST_NAME || ' ' || LAST_NAME
2 FROM EMPLOYEES EMP1
3 WHERE EXISTS (
4 SELECT 1 FROM EMPLOYEES EMP2
5 WHERE FIRST_NAME = 'Den'
6 AND LAST_NAME = 'Raphaely'
7 AND EMP2.EMPLOYEE_ID = EMP1.MANAGER_ID);

FIRST_NAME||''||LAST

Alexander Khoo
Shelli Baida
Sigal Tobias
Guy Himuro
Karen Colmenares。
61.列出在同一部门共事，入职日期晚但工资高于其他同事的员工：
名字、工资、入职日期（关联子查询）。
SQL> SELECT FIRST_NAME || ' ' || LAST_NAME,salary,HIRE_DATE
2 FROM EMPLOYEES EMP1
3 WHERE EXISTS (
4 SELECT 1 FROM EMPLOYEES EMP2
5 WHERE EMP1.HIRE_DATE>EMP2.HIRE_DATE and EMP1.salary>EMP2.salary
6 AND EMP1.DEPARTMENT_ID = EMP2.DEPARTMENT_ID);

Steven King 24000 2003-06-17 00:00:00
Alexander Hunold 9000 2006-01-03 00:00:00
Bruce Ernst 6000 2007-05-21 00:00:00
Nancy Greenberg 12008 2002-08-17 00:00:00
Jose Manuel Urman 7800 2006-03-07 00:00:00
Shelli Baida 2900 2005-12-24 00:00:00
Adam Fripp 8200 2005-04-10 00:00:00
Matthew Weiss 8000 2004-07-18 00:00:00
Shanta Vollman 6500 2005-10-10 00:00:00
Kevin Mourgos 5800 2007-11-16 00:00:00
Laura Bissot 3300 2005-08-20 00:00:00

Julia Dellinger 3400 2006-06-24 00:00:00
Jennifer Dilly 3600 2005-08-13 00:00:00
Girard Geoni 2800 2008-02-03 00:00:00
Anthony Cabrio 3000 2007-02-07 00:00:00
Vance Jones 2800 2007-03-17 00:00:00
Martha Sullivan 2500 2007-06-21 00:00:00
Randall Perkins 2500 2007-12-19 00:00:00
Donald OConnell 2600 2007-06-21 00:00:00
Douglas Grant 2600 2008-01-13 00:00:00
Michael Rogers 2900 2006-08-26 00:00:00
Winston Taylor 3200 2006-01-24 00:00:00

Jean Fleaur 3100 2006-02-23 00:00:00
Timothy Gates 2900 2006-07-11 00:00:00
Samuel McCain 3200 2006-07-01 00:00:00
Alana Walsh 3100 2006-04-24 00:00:00
Kevin Feeney 3000 2006-05-23 00:00:00
Julia Nayer 3200 2005-07-16 00:00:00
Irene Mikkilineni 2700 2006-09-28 00:00:00
Mozhe Atkinson 2800 2005-10-30 00:00:00
Stephen Stiles 3200 2005-10-26 00:00:00
John Seo 2700 2006-02-12 00:00:00
Randall Matos 2600 2006-03-15 00:00:00

Alexis Bull 4100 2005-02-20 00:00:00
Kelly Chung 3800 2005-06-14 00:00:00
Britney Everett 3900 2005-03-03 00:00:00
Steven Markle 2200 2008-03-08 00:00:00
Ki Gee 2400 2007-12-12 00:00:00
Hazel Philtanker 2200 2008-02-06 00:00:00
Nandita Sarchand 4200 2004-01-27 00:00:00
Sarah Bell 4000 2004-02-04 00:00:00
Alberto Errazuriz 12000 2005-03-10 00:00:00
Gerald Cambrault 11000 2007-10-15 00:00:00
Eleni Zlotkey 10500 2008-01-29 00:00:00

Clara Vishney 10500 2005-11-11 00:00:00
Lisa Ozer 11500 2005-03-11 00:00:00
Harrison Bloom 10000 2006-03-23 00:00:00
Tayler Fox 9600 2006-01-24 00:00:00
Danielle Greene 9500 2007-03-19 00:00:00
Jack Livingston 8400 2006-04-23 00:00:00
Mattea Marvins 7200 2008-01-24 00:00:00
John Russell 14000 2004-10-01 00:00:00
Karen Partners 13500 2005-01-05 00:00:00
Ellen Abel 11000 2004-05-11 00:00:00
Peter Tucker 10000 2005-01-30 00:00:00

David Bernstein 9500 2005-03-24 00:00:00
Peter Hall 9000 2005-08-20 00:00:00
Alyssa Hutton 8800 2005-03-19 00:00:00
Jonathon Taylor 8600 2006-03-24 00:00:00
Christopher Olsen 8000 2006-03-30 00:00:00
Nanette Cambrault 7500 2006-12-09 00:00:00
William Smith 7400 2007-02-23 00:00:00
Elizabeth Bates 7300 2007-03-24 00:00:00
David Lee 6800 2008-02-23 00:00:00
Sundar Ande 6400 2008-03-24 00:00:00

65 rows selected.
62.哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)不在同一个部门（非关联子查询）。
SQL> select first_name || ' '||last_name from employees where DEPARTMENT_ID != (select DEPARTMENT_ID from employees where FIRST_NAME='Den' and LAST_NAME='Raphaely');

FIRST_NAME||''||LAST

Ellen Abel
Sundar Ande
Mozhe Atkinson
David Austin
Hermann Baer
Amit Banda
Elizabeth Bates
Sarah Bell
David Bernstein
Laura Bissot
Harrison Bloom

FIRST_NAME||''||LAST

Alexis Bull
Anthony Cabrio
Gerald Cambrault
Nanette Cambrault
John Chen
Kelly Chung
Curtis Davies
Lex De Haan
Julia Dellinger
Jennifer Dilly
Louise Doran

FIRST_NAME||''||LAST

Bruce Ernst
Alberto Errazuriz
Britney Everett
Daniel Faviet
Pat Fay
Kevin Feeney
Jean Fleaur
Tayler Fox
Adam Fripp
Timothy Gates
Ki Gee

FIRST_NAME||''||LAST

Girard Geoni
William Gietz
Douglas Grant
Nancy Greenberg
Danielle Greene
Peter Hall
Michael Hartstein
Shelley Higgins
Alexander Hunold
Alyssa Hutton
Charles Johnson

FIRST_NAME||''||LAST

Vance Jones
Payam Kaufling
Janette King
Steven King
Neena Kochhar
Sundita Kumar
Renske Ladwig
James Landry
David Lee
Jack Livingston
Diana Lorentz

FIRST_NAME||''||LAST

Jason Mallin
Steven Markle
James Marlow
Mattea Marvins
Randall Matos
Susan Mavris
Samuel McCain
Allan McEwen
Irene Mikkilineni
Kevin Mourgos
Julia Nayer

FIRST_NAME||''||LAST

Donald OConnell
Christopher Olsen
TJ Olson
Lisa Ozer
Karen Partners
Valli Pataballa
Joshua Patel
Randall Perkins
Hazel Philtanker
Luis Popp
Trenna Rajs

FIRST_NAME||''||LAST

Michael Rogers
John Russell
Nandita Sarchand
Ismael Sciarra
John Seo
Sarath Sewall
Lindsey Smith
William Smith
Stephen Stiles
Martha Sullivan
Patrick Sully

FIRST_NAME||''||LAST

Jonathon Taylor
Winston Taylor
Peter Tucker
Oliver Tuvault
Jose Manuel Urman
Peter Vargas
Clara Vishney
Shanta Vollman
Alana Walsh
Matthew Weiss
Jennifer Whalen

FIRST_NAME||''||LAST

Eleni Zlotkey

100 rows selected.
SQL> select first_name || ' '||last_name from employees where DEPARTMENT_ID not in (select DEPARTMENT_ID from employees where FIRST_NAME='Den' and LAST_NAME='Raphaely');

FIRST_NAME||''||LAST

Matthew Weiss
Adam Fripp
Payam Kaufling
Shanta Vollman
Kevin Mourgos
Julia Nayer
Irene Mikkilineni
James Landry
Steven Markle
Laura Bissot
Mozhe Atkinson

FIRST_NAME||''||LAST

James Marlow
TJ Olson
Jason Mallin
Michael Rogers
Ki Gee
Hazel Philtanker
Renske Ladwig
Stephen Stiles
John Seo
Joshua Patel
Trenna Rajs

FIRST_NAME||''||LAST

Curtis Davies
Randall Matos
Peter Vargas
Winston Taylor
Jean Fleaur
Martha Sullivan
Girard Geoni
Nandita Sarchand
Alexis Bull
Julia Dellinger
Anthony Cabrio

FIRST_NAME||''||LAST

Kelly Chung
Jennifer Dilly
Timothy Gates
Randall Perkins
Sarah Bell
Britney Everett
Samuel McCain
Vance Jones
Alana Walsh
Kevin Feeney
Donald OConnell

FIRST_NAME||''||LAST

Douglas Grant
Susan Mavris
Shelley Higgins
William Gietz
Steven King
Neena Kochhar
Lex De Haan
Hermann Baer
Jennifer Whalen
Michael Hartstein
Pat Fay

FIRST_NAME||''||LAST

Alexander Hunold
Bruce Ernst
David Austin
Valli Pataballa
Diana Lorentz
Nancy Greenberg
Daniel Faviet
John Chen
Ismael Sciarra
Jose Manuel Urman
Luis Popp

FIRST_NAME||''||LAST

John Russell
Karen Partners
Alberto Errazuriz
Gerald Cambrault
Eleni Zlotkey
Peter Tucker
David Bernstein
Peter Hall
Christopher Olsen
Nanette Cambrault
Oliver Tuvault

FIRST_NAME||''||LAST

Janette King
Patrick Sully
Allan McEwen
Lindsey Smith
Louise Doran
Sarath Sewall
Clara Vishney
Danielle Greene
Mattea Marvins
David Lee
Sundar Ande

FIRST_NAME||''||LAST

Amit Banda
Lisa Ozer
Harrison Bloom
Tayler Fox
William Smith
Elizabeth Bates
Sundita Kumar
Ellen Abel
Alyssa Hutton
Jonathon Taylor
Jack Livingston

FIRST_NAME||''||LAST

Charles Johnson

100 rows selected.
63.哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)不在同一个部门（关联子查询）。
SQL> select first_name || ' '||last_name from employees emp1 where not exists (select 1 from employees emp2 where FIRST_NAME='Den' and LAST_NAME='Raphaely'and emp1.DEPARTMENT_ID =emp2.DEPARTMENT_ID );

FIRST_NAME||''||LAST

Matthew Weiss
Adam Fripp
Payam Kaufling
Shanta Vollman
Kevin Mourgos
Julia Nayer
Irene Mikkilineni
James Landry
Steven Markle
Laura Bissot
Mozhe Atkinson

FIRST_NAME||''||LAST

James Marlow
TJ Olson
Jason Mallin
Michael Rogers
Ki Gee
Hazel Philtanker
Renske Ladwig
Stephen Stiles
John Seo
Joshua Patel
Trenna Rajs

FIRST_NAME||''||LAST

Curtis Davies
Randall Matos
Peter Vargas
Winston Taylor
Jean Fleaur
Martha Sullivan
Girard Geoni
Nandita Sarchand
Alexis Bull
Julia Dellinger
Anthony Cabrio

FIRST_NAME||''||LAST

Kelly Chung
Jennifer Dilly
Timothy Gates
Randall Perkins
Sarah Bell
Britney Everett
Samuel McCain
Vance Jones
Alana Walsh
Kevin Feeney
Donald OConnell

FIRST_NAME||''||LAST

Douglas Grant
Susan Mavris
Shelley Higgins
William Gietz
Steven King
Neena Kochhar
Lex De Haan
Hermann Baer
Jennifer Whalen
Kimberely Grant
Michael Hartstein

FIRST_NAME||''||LAST

Pat Fay
Alexander Hunold
Bruce Ernst
David Austin
Valli Pataballa
Diana Lorentz
Nancy Greenberg
Daniel Faviet
John Chen
Ismael Sciarra
Jose Manuel Urman

FIRST_NAME||''||LAST

Luis Popp
John Russell
Karen Partners
Alberto Errazuriz
Gerald Cambrault
Eleni Zlotkey
Peter Tucker
David Bernstein
Peter Hall
Christopher Olsen
Nanette Cambrault

FIRST_NAME||''||LAST

Oliver Tuvault
Janette King
Patrick Sully
Allan McEwen
Lindsey Smith
Louise Doran
Sarath Sewall
Clara Vishney
Danielle Greene
Mattea Marvins
David Lee

FIRST_NAME||''||LAST

Sundar Ande
Amit Banda
Lisa Ozer
Harrison Bloom
Tayler Fox
William Smith
Elizabeth Bates
Sundita Kumar
Ellen Abel
Alyssa Hutton
Jonathon Taylor

FIRST_NAME||''||LAST

Jack Livingston
Charles Johnson

101 rows selected.
发现上面一题错误
SQL> select first_name || ' '||last_name from employees where nvl(DEPARTMENT_ID,1) not in (select DEPARTMENT_ID from employees where FIRST_NAME='Den' and LAST_NAME='Raphaely');

FIRST_NAME||''||LAST

Matthew Weiss
Adam Fripp
Payam Kaufling
Shanta Vollman
Kevin Mourgos
Julia Nayer
Irene Mikkilineni
James Landry
Steven Markle
Laura Bissot
Mozhe Atkinson

FIRST_NAME||''||LAST

James Marlow
TJ Olson
Jason Mallin
Michael Rogers
Ki Gee
Hazel Philtanker
Renske Ladwig
Stephen Stiles
John Seo
Joshua Patel
Trenna Rajs

FIRST_NAME||''||LAST

Curtis Davies
Randall Matos
Peter Vargas
Winston Taylor
Jean Fleaur
Martha Sullivan
Girard Geoni
Nandita Sarchand
Alexis Bull
Julia Dellinger
Anthony Cabrio

FIRST_NAME||''||LAST

Kelly Chung
Jennifer Dilly
Timothy Gates
Randall Perkins
Sarah Bell
Britney Everett
Samuel McCain
Vance Jones
Alana Walsh
Kevin Feeney
Donald OConnell

FIRST_NAME||''||LAST

Douglas Grant
Susan Mavris
Kimberely Grant
Shelley Higgins
William Gietz
Steven King
Neena Kochhar
Lex De Haan
Hermann Baer
Jennifer Whalen
Michael Hartstein

FIRST_NAME||''||LAST

Pat Fay
Alexander Hunold
Bruce Ernst
David Austin
Valli Pataballa
Diana Lorentz
Nancy Greenberg
Daniel Faviet
John Chen
Ismael Sciarra
Jose Manuel Urman

FIRST_NAME||''||LAST

Luis Popp
John Russell
Karen Partners
Alberto Errazuriz
Gerald Cambrault
Eleni Zlotkey
Peter Tucker
David Bernstein
Peter Hall
Christopher Olsen
Nanette Cambrault

FIRST_NAME||''||LAST

Oliver Tuvault
Janette King
Patrick Sully
Allan McEwen
Lindsey Smith
Louise Doran
Sarath Sewall
Clara Vishney
Danielle Greene
Mattea Marvins
David Lee

FIRST_NAME||''||LAST

Sundar Ande
Amit Banda
Lisa Ozer
Harrison Bloom
Tayler Fox
William Smith
Elizabeth Bates
Sundita Kumar
Ellen Abel
Alyssa Hutton
Jonathon Taylor

FIRST_NAME||''||LAST

Jack Livingston
Charles Johnson

101 rows selected.
not in <>忽略null
64.Finance部门有哪些职位（非关联子查询）。
SQL> SELECT DISTINCT JOB_ID FROM EMPLOYEES where DEPARTMENT_ID = (select DEPARTMENT_ID from DEPARTMENTS where DEPARTMENT_NAME = 'Finance');

JOB_ID

FI_ACCOUNT
FI_MGR

SQL> SELECT DISTINCT JOB_ID FROM EMPLOYEES where DEPARTMENT_ID in (select DEPARTMENT_ID from DEPARTMENTS where DEPARTMENT_NAME = 'Finance');

JOB_ID

FI_ACCOUNT
FI_MGR
65.Finance部门有哪些职位（关联子查询）。
SQL> SELECT DISTINCT JOB_ID FROM EMPLOYEES a where exists (select DEPARTMENT_ID from DEPARTMENTS b where a.DEPARTMENT_ID=b.DEPARTMENT_ID and DEPARTMENT_NAME = 'Finance');

JOB_ID

FI_ACCOUNT
FI_MGR