ORACLE SQL 查詢練手

發布時間：2020-09-02 09:03:41 來源：網絡閱讀：482 作者：whshurk 欄目：關系型數據庫

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

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

ENAME

ALLEN
MARTIN

找出所有有傭金的員工，列出姓名、工資、傭金，顯示結果按工資從小到大，
傭金從大到小。
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

顯示工資不在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.

顯示職位為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

說明以下兩條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

讓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;

語句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,

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);

FIRST_NAME		''		LAST SALARY BOSS_NAME BOSS_SALARY

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;

A.FIRST_NAME		''		A. SALARY B.FIRST_NAME		''		B.LAST_NAME 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;

FIRST_NAME		''		LAST COMMISSION_PCT

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

FIRST_NAME		''		LAST COMMISSION_PCT

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

FIRST_NAME		''		LAST COMMISSION_PCT

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

FIRST_NAME		''		LAST COMMISSION_PCT

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;

FIRST_NAME		''		LAST COMMISSION_PCT

FIRST_NAME		''		LAST COMMISSION_PCT

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

FIRST_NAME		''		LAST COMMISSION_PCT

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

FIRST_NAME		''		LAST COMMISSION_PCT

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)

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

COUNT(*)

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')))

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

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;

FIRST_NAME		''		LAST SALARY

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

FIRST_NAME		''		LAST SALARY

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

FIRST_NAME		''		LAST SALARY

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

FIRST_NAME		''		LAST SALARY

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

FIRST_NAME		''		LAST SALARY

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);

FIRST_NAME		''		LAST SALARY

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

FIRST_NAME		''		LAST SALARY

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

FIRST_NAME		''		LAST SALARY

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

FIRST_NAME		''		LAST SALARY

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);

FIRST_NAME		''		LAST SALARY

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

FIRST_NAME		''		LAST SALARY

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

FIRST_NAME		''		LAST SALARY

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

FIRST_NAME		''		LAST SALARY

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

FIRST_NAME		''		LAST SALARY

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);

FIRST_NAME		''		LAST SALARY

FIRST_NAME		''		LAST SALARY

FIRST_NAME		''		LAST SALARY

FIRST_NAME		''		LAST SALARY

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

FIRST_NAME		''		LAST SALARY

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);

FIRST_NAME		''		LAST DEPARTMENT_ID SALARY

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);

FIRST_NAME		''		LAST 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);

FIRST_NAME		''		LAST SALARY HIRE_DATE

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

FIRST_NAME		''		LAST SALARY HIRE_DATE

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

FIRST_NAME		''		LAST SALARY HIRE_DATE

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

FIRST_NAME		''		LAST SALARY HIRE_DATE

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

FIRST_NAME		''		LAST SALARY HIRE_DATE

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

FIRST_NAME		''		LAST SALARY HIRE_DATE

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

向AI問一下細節

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

ORACLE SQL 查詢練手

DNAME

ENAME

ENAME

JOB

ENAME

ENAME

ENAME

ENAME

ENAME

ENAME

ENAME

JOB

DNAME

DNAME

'SELECT*FROM'||TABLE_NAME||';'

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

TO_CHAR(SALARY,'L99,

UPPER(FIRST_NAME||''||LAST_NAME)

FIRST_NAME||''||LAST_NAME

FIRST_NAME||''||LAST_NAME

JOB_ID

MAX(SALARY)-MIN(SALARY)

COUNT(*)

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

MAX(AVGSALARY)

MAX(AVG(SALARY))

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

FIRST_NAME||''||LAST

JOB_ID

JOB_ID

JOB_ID

猜你喜歡

最新資訊

相關推薦