一起學習寫SQL

www_xylove

作為一名dba，會寫sql是一項基本功。
如果在做dba之前，從事過開發(fā)，可能寫sql就相對容易些，如果直接就上手dba，可能寫sql的能力會差一些。
本人也沒有從事過開發(fā)，寫sql也是在工作中慢慢的學會的。
不過，這也沒有關系，多練習練習，就會了。
下面總共有sql練習55道題，這55道題目是itpub網(wǎng)友提供的，但沒有提供作者的名字，這里先行謝謝了。
原版的55道題目，既有題也有答案，因為是學習寫sql，不是去看人家怎么寫的，我這里的練習的是自己這幾天寫出來的，
當然也參考了原作者的答案。原作者的文檔命名為《Oracle_SQL：經(jīng)典查詢練手四篇(全)》可以到itpub上下載。

下面的sql基本上是我自己寫出來的，因為我也是在不斷的學習寫sql中，有寫的不妥的地方，請網(wǎng)友們指出，謝謝。
好了，轉入正題，下面我們來一起寫sql：

SQL學習1-18：
1．列出至少有一個員工的所有部門。
2．列出薪金比“MARTIN”多的所有員工。
3．列出所有員工的姓名及其直接上級的姓名。
4．列出受雇日期早于其直接上級的所有員工。
5．列出部門名稱和這些部門的員工信息，同時列出那些沒有員工的部門
6．列出所有“CLERK”（辦事員）的姓名及其部門名稱。
7．列出最低薪金大于1500的各種工作。
8．列出在部門“SALES”（銷售部）工作的員工的姓名，假定不知道銷售部的部門編號。
9．列出薪金高于公司平均薪金的所有員工。
10．列出與“BLAKE”從事相同工作的所有員工。
11．列出薪金等于部門30中員工的薪金的所有員工的姓名和薪金。
12．列出薪金高于在部門30工作的所有員工的薪金的員工姓名和薪金。
13．列出在每個部門工作的員工數(shù)量、平均工資和平均服務期限。
14．列出所有員工的姓名、部門名稱和工資。
15．列出所有部門的詳細信息和部門人數(shù)。
16．列出各種工作的最低工資。
17．列出各個部門的MANAGER（經(jīng)理）的最低薪金。
18．列出所有員工的年工資,按年薪從低到高排序。

1．列出至少有一個員工的所有部門。
select dname from scott.dept a,(
select deptno,count(*) from scott.emp  where deptno is not null group by deptno order by count(*) desc
) b
where a.deptno = b.deptno

DNAME
ACCOUNTING
RESEARCH
SALES

2. 列出薪金比“MARTIN”多的所有員工。
select * from scott.emp b where sal>
(select sal from scott.emp where ename='MARTIN')

3. 列出所有員工的姓名及其直接上級的姓名。
select a.empno as "員工編號",
       a.ename as "員工姓名",
       b.ename as "員工的直接領導"
  from scott.emp a, scott.emp b
where a.mgr = b.empno(+)
員工編號        員工姓名        員工的直接領導
7902        FORD        JONES
7900        JAMES        BLAKE
7844        TURNER        BLAKE
7654        MARTIN        BLAKE
7521        WARD        BLAKE
7499        ALLEN        BLAKE
7934        MILLER        CLARK
7782        CLARK        KING
7698        BLAKE        KING
7566        JONES        KING
7369        SMITH        FORD
7839        KING       
4．列出受雇日期早于其直接上級的所有員工。
select a.empno as "員工編號",
       a.ename as "員工姓名",
       a.hiredate as "員工受雇日期",
       b.ename as "員工的直接領導",
       b.hiredate "員工的直接領導受雇日期"
  from scott.emp a, scott.emp b
where a.mgr = b.empno(+)
and a.hiredate<b.hiredate
員工編號        員工姓名        員工受雇日期        員工的直接領導        員工的直接領導受雇日期
7521        WARD        1981/2/22        BLAKE        1981/5/1
7499        ALLEN        1981/2/20        BLAKE        1981/5/1
7782        CLARK        1981/6/9        KING        1981/11/17
7566        JONES        1981/4/2        KING        1981/11/17
7698        BLAKE        1981/5/1        KING        1981/11/17
7369        SMITH        1980/12/17        FORD        1981/12/3
紅色標示的這些員工就是比直接領導受雇早的員工。

5．列出部門名稱和這些部門的員工信息，同時列出那些沒有員工的部門

select a.empno, a.ename, b.dname, b.deptno
  from scott.emp a, scott.dept b
where a.deptno(+) = b.deptno

EMPNO        ENAME        DNAME        DEPTNO
7782        CLARK        ACCOUNTING        10
7934        MILLER        ACCOUNTING        10
7902        FORD        RESEARCH        20
7369        SMITH        RESEARCH        20
7566        JONES        RESEARCH        20
7900        JAMES        SALES            30
7844        TURNER        SALES          30
7654        MARTIN        SALES          30
7521        WARD        SALES          30
7499        ALLEN        SALES          30
7698        BLAKE        SALES          30
                OPERATIONS              40


等同于：
select a.empno, a.ename, b.dname, b.deptno
  from scott.emp a
right join scott.dept b
    on a.deptno = b.deptno

6．列出所有“CLERK”（辦事員）的姓名及其部門名稱。
select c.job,c.ename,b.dname from scott.dept b,
(select a.ename,a.deptno,a.job from scott.emp a where job ='CLERK') c
where c.deptno = b.deptno

JOB            ENAME            DNAME
CLERK        SMITH        RESEARCH
CLERK        JAMES        SALES
CLERK        MILLER        ACCOUNTING

7. 列出最低薪金大于1500的各種工作。
select a.job,min(a.sal) from scott.emp a group by a.job having min(sal) >1500;

JOB                 MIN(A.SAL)
PRESIDENT        5000
MANAGER        2450
ANALYST        3000

8．列出在部門“SALES”（銷售部）工作的員工的姓名，假定不知道銷售部的部門編號。
select a.ename from scott.emp a ,(
select b.deptno from scott.dept b where dname ='SALES') c where
c.deptno = a.deptno
或
select ename from scott.emp where deptno = (select deptno from scott.dept where dname ='SALES');

ENAME
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES

9．列出薪金高于公司平均薪金的所有員工。
select ename from scott.emp where sal > (
select round(avg(sal)) from scott.emp )
ENAME
JONES
BLAKE
CLARK
KING
FORD

10．列出與“BLAKE”從事相同工作的所有員工。
select b.ename,b.job from scott.emp b,(
select a.job from scott.emp a where ename='BLAKE') c
where b.job = c.job
或
select ename,job from scott.emp b where job = (select a.job from scott.emp a where ename='BLAKE')

ENAME        JOB
JONES        MANAGER
BLAKE        MANAGER
CLARK        MANAGER

11．列出薪金等于部門30中員工的薪金的所有員工的姓名和薪金。
select a.ename, a.sal
  from scott.emp a
where a.sal in (select b.sal from scott.emp b where b.deptno = 30)
   and a.deptno <> 30;
12．列出薪金高于在部門30工作的所有員工的薪金的員工姓名和薪金。
select ename,sal from scott.emp where sal> all (
select sal from scott.emp where deptno=30)
或
select ename,sal from scott.emp where sal>(select max(sal) from scott.emp where deptno=30)
ENAME        SAL
JONES        2975.00
KING        5000.00
FORD        3000.00

13．列出在每個部門工作的員工數(shù)量、平均工資和平均服務期限。
   select b."部門平均工資","部門員工數(shù)量","平均服務年限" from scott.dept a,
(select deptno,
       round(avg(sal)) as "部門平均工資",
       count(*) as "部門員工數(shù)量",
       round(avg(to_number(to_char(hiredate, 'yyyymmdd')))) as "平均服務年限"
  from scott.emp
where deptno is not null
group by deptno) b
where a.deptno = b.deptno

部門平均工資        部門員工數(shù)量        平均服務年限
1567        6        19810664
2258        3        19807607
1875        2        19815366

14．列出所有員工的姓名、部門名稱和工資。
select ename, dname, sal
  from scott.dept a, (select * from scott.emp b) b
where a.deptno = b.deptno
ENAME        DNAME        SAL
CLARK        ACCOUNTING        2450.00
MILLER        ACCOUNTING        1300.00
FORD        RESEARCH        3000.00
SMITH        RESEARCH        800.00
JONES        RESEARCH        2975.00
JAMES        SALES        950.00
TURNER        SALES        1500.00
MARTIN        SALES        1250.00
WARD        SALES        1250.00
ALLEN        SALES        1600.00
BLAKE        SALES        2850.00

15．列出所有部門的詳細信息和部門人數(shù)。
select b.deptno, b.dname, b.loc, count(ename)
  from scott.emp a, (select * from scott.dept) b
where a.deptno(+) = b.deptno
group by b.deptno, b.dname, b.loc;
或
select a.deptno,
       a.dname,
       a.loc,
       (select  count(*)
          from scott.emp b
         where b.deptno = a.deptno
         group by b.deptno) as deptcount
  from scott.dept a;

20        RESEARCH        DALLAS        3
40        OPERATIONS        BOSTON        0
10        ACCOUNTING        NEW YORK        2
30        SALES        CHICAGO        6

16．列出各種工作的最低工資。
select job,min(sal) from scott.emp group by job

JOB        MIN(SAL)
CLERK        800
SALESMAN        1250
PRESIDENT        5000
MANAGER        2450
ANALYST        3000

17．列出各個部門的MANAGER（經(jīng)理）的最低薪金。
   select b.ename as "部門經(jīng)理",
          (select c.dname as "部門"
             from scott.dept c
            where c.deptno = a.deptno) as "部門",
          min(a.sal) as "部門經(jīng)理最低工資"
     from scott.emp a, scott.emp b
    where a.mgr = b.empno(+)
      and b.ename <> 'KING'
    group by b.ename, a.deptno
    order by a.deptno desc

部門經(jīng)理        部門        部門經(jīng)理最低工資
BLAKE        SALES            950
FORD        RESEARCH        800
JONES        RESEARCH        3000
CLARK        ACCOUNTING        1300

18．列出所有員工的年工資,按年薪從低到高排序。
select ename, (sal + nvl(comm, 0)) * 12 as "年薪"
  from scott.emp
order by "年薪";

www_xylove

SQL學習2-10:
1. 找出EMP表中的姓名（ENAME）第三個字母是A 的員工姓名。
2. 找出EMP表員工名字中含有A 和N的員工姓名。
3. 找出所有有傭金的員工，列出姓名、工資、傭金，顯示結果按工資從小到大，傭金從大到小。
4. 列出部門編號為20的所有職位。
5. 列出不屬于SALES 的部門。
6. 顯示工資不在1000 到1500 之間的員工信息：名字、工資，按工資從大到小排序。
7. 顯示職位為MANAGER 和SALESMAN，年薪在15000 和20000 之間的員工的信息：名字、職位、年薪。
8. 說明以下兩條SQL語句的輸出結果：
SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;
9. 讓SELECT 語句的輸出結果為
SELECT * FROM SALGRADE;
SELECT * FROM BONUS;
SELECT * FROM EMP;
SELECT * FROM DEPT;
……
列出當前用戶有多少張數(shù)據(jù)表，結果集中存在多少條記錄。
10. 判斷SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否報錯，為什么？

1. 找出EMP表中的姓名（ENAME）第三個字母是A 的員工姓名。
select ename from scott.emp where ename like '__A%'
ENAME
BLAKE
CLARK
2. 找出EMP表員工名字中含有A 和N的員工姓名。
select ename
  from scott.emp
where ename like '%A%'
   and ename like '%N%';

ENAME
ALLEN
MARTIN
3. 找出所有有傭金的員工，列出姓名、工資、傭金，顯示結果按工資從小到大，傭金從大到小。
select ename, sal, comm
  from scott.emp
where comm is not null
order by sal,comm desc
ENAME           SAL             COMM
MARTIN        1250.00        1400.00
WARD        1250.00        500.00
TURNER        1500.00        0.00
ALLEN        1600.00        300.00

4. 列出部門編號為20的所有職位。
select job from scott.emp where deptno = 20
5. 列出不屬于SALES 的部門。
select dname from scott.dept where dname <>'SALES'
6. 顯示工資不在1000 到1500 之間的員工信息：名字、工資，按工資從大到小排序。
select ename,sal  from scott.emp where sal >1500 or sal<1000 order by sal desc
ENAME        SAL
KING        5000.00
FORD        3000.00
JONES        2975.00
BLAKE        2850.00
CLARK        2450.00
ALLEN        1600.00
JAMES        950.00
SMITH        800.00

7. 顯示職位為MANAGER 和SALESMAN，年薪在15000 和20000 之間的員工的信息：名字、職位、年薪
select ename, job, (sal * 12 + nvl(comm, 0)) as "年薪"
  from scott.emp
where job in ('MANAGER', 'SALESMAN')
   and ((sal * 12 + nvl(comm, 0)) >= 15000 and
       (sal * 12 + nvl(comm, 0)) <= 20000)
order by "年薪" desc

ENAME        JOB        年薪
ALLEN        SALESMAN        19500
TURNER        SALESMAN        18000
MARTIN        SALESMAN        16400
WARD        SALESMAN        15500

8. 說明以下兩條SQL語句的輸出結果：
SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
EMPNO        COMM
7369       
7566       
7698       
7782       
7839       
7900       
7902       
7934       
SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;
沒有選擇行

9. 讓SELECT 語句的輸出結果為
SELECT * FROM SALGRADE;
SELECT * FROM BONUS;
SELECT * FROM EMP;
SELECT * FROM DEPT;
……
列出當前用戶有多少張數(shù)據(jù)表，結果集中存在多少條記錄。
（這個過于簡單，就不示例了.）

10. 判斷SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否報錯，為什么？
ENAME        SAL
ALLEN        1600.00
JONES        2975.00
BLAKE        2850.00
CLARK        2450.00
KING        5000.00
FORD        3000.00

不會報錯：將自動轉換（隱式轉換）為字符類型的數(shù)字。

www_xylove

SQL學習3-17:
1. 讓SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE  ROWNUM < 5 輸出結果的貨幣單位是￥和$。
2. 列出前五位每個員工的名字，工資、漲薪后的的工資（漲幅為8%），以“元”為單位進行四舍五入。
3. 找出誰是最高領導，將名字按大寫形式顯示。
4. 找出First_Name 為David，Last_Name為Austin 的直接領導名字。
5. First_Name 為Alexander，Last_Name為Hunold領導誰。（誰向David 報告）。
6. 哪些員工的工資高于他直接上司的工資，列出員工的名字和工資，上司的名字和工資。
7. 哪些員工和Chen(LAST_NAME)同部門。
8. 哪些員工跟De Haan(LAST_NAME)做一樣職位。
9. 哪些員工跟Hall(LAST_NAME)不在同一個部門。
10. 哪些員工跟William（FIRST_NAME）、Smith(LAST_NAME)做不一樣的職位。
11. 顯示有提成的員工的信息：名字、提成、所在部門名稱、所在地區(qū)的名稱。
12. 顯示Executive部門有哪些職位。
13. 整個公司中，最高工資和最低工資相差多少。
14. 提成大于0 的人數(shù)。
15. 顯示整個公司的最高工資、最低工資、工資總和、平均工資保留到整數(shù)位。
16. 整個公司有多少個領導。
17. 列出在同一部門入職日期晚但工資高于其他同事的員工：名字、工資、入職日期。


1. 讓SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE  ROWNUM < 5 輸  出結果的貨幣單位是￥和$。
SELECT
     TO_CHAR(SALARY, 'L99,999.99'),
     TO_CHAR(SALARY, '$99,999.99')
  FROM HR.EMPLOYEES
WHERE ROWNUM < 5

TO_CHAR(SALARY,'L99,999.99')        TO_CHAR(SALARY,'$99,999.99')
         ￥24,000.00         $24,000.00
         ￥17,000.00         $17,000.00
         ￥17,000.00         $17,000.00
         ￥9,000.00              $9,000.00

2. 列出前五位每個員工的名字，工資、漲薪后的的工資（漲幅為8%），以“元”為單位進行四舍五入。
select first_name,round(to_char(salary)),round(to_char(salary+salary*0.0) from (
(select rownum as rn,a.* from hr.employees a order by salary desc) b )
where rn <6

FIRST_NAME        ROUND(TO_CHAR(SALARY))        ROUND(TO_CHAR(SALARY+SALARY*0.
Steven                              24000                    25920
Neena                              17000                    18360
Lex                                  17000                    18360
Alexander                          9000                    9720
Bruce                              6000                    6480

3. 找出誰是最高領導，將名字按大寫形式顯示。
select upper(first_name || ' ' || last_name) as "最高領導人"
  from hr.employees
where manager_id is null;

最高領導人
STEVEN KING


4. 找出First_Name 為David，Last_Name為Austin 的直接領導名字。
select employee_id, first_name, last_name
  from hr.employees b
where b.employee_id = (select a.manager_id
                          from hr.employees a
                         where a.first_name = 'David'
                           and a.last_name = 'Austin')

EMPLOYEE_ID        FIRST_NAME          LAST_NAME
103                     Alexander            Hunold

5. First_Name 為Alexander，Last_Name為Hunold領導誰。（誰向David 報告）。
select c.first_name || ' ' || c.last_name as "David Austin 的直接上司"
  from hr.employees c
where c.employee_id =
       (select b.manager_id
          from hr.employees b
         where b.employee_id =
               (select a.manager_id
                  from hr.employees a
                 where a.first_name = 'David'
                   and a.last_name = 'Austin'))

David Austin 的直接上司

Lex De Haan

6. 哪些員工的工資高于他直接上司的工資，列出員工的名字和工資，上司的名字和工資。
select c.employee_id as "員工ID",
       c.first_name || ' ' || c.last_name as "員工姓名",
       c.salary as "員工薪資",
       a.employee_id as "員工直接上級ID",
       a.first_name || ' ' || a.last_name as "員工直接上級姓名",
       a.salary as "員工直接上級薪資"
  from hr.employees a, hr.employees c
where a.employee_id(+) = c.manager_id
   and c.salary > a.salary
order by 員工薪資 desc

員工ID        員工姓名        員工薪資        員工直接上級ID        員工直接上級姓名        員工直接上級薪資
168        Lisa Ozer        11500.00        148        Gerald Cambrault        11000.00
174        Ellen Abel        11000.00        149        Eleni Zlotkey            10500.00

7. 哪些員工和Chen(LAST_NAME)同部門。
select b.last_name, b.department_id
  from hr.employees b,
       (select a.department_id from hr.employees a where last_name = 'Chen') c
where b.department_id = c.department_id

LAST_NAME        DEPARTMENT_ID
Greenberg        100
Faviet        100
Chen        100
Sciarra        100
Urman        100
Popp        100

8. 哪些員工跟De Haan(LAST_NAME)做一樣職位。
select b.last_name,b.job_id
  from hr.employees b,
       (select a.job_id from hr.employees a where last_name = 'De Haan') c
where b.job_id = c.job_id

LAST_NAME        JOB_ID
Kochhar        AD_VP
De Haan        AD_VP

9. 哪些員工跟Hall(LAST_NAME)不在同一個部門。

select b.last_name, b.department_id
  from hr.employees b,
       (select a.department_id from hr.employees a where last_name = 'Chen') c
where b.department_id <> c.department_id

10. 哪些員工跟William（FIRST_NAME）、Smith(LAST_NAME)做不一樣的職位。
select * from hr.employees a,(
select b.job_id from hr.employees b where b.first_name='William' and b.last_name='Smith' and b.job_id='SA_REP') c
where a.job_id <> c.job_id;

11. 顯示有提成的員工的信息：名字、提成、所在部門名稱、所在地區(qū)的名稱。
select g.first_name || ' ' || g.last_name as "員工",
       g.commission_pct as "提成",
       g.department_id as "部門",
       x.region_name as "地區(qū)"
  from hr.employees g,
       (select f.region_name
          from hr.regions f
         where f.region_id in
               (select e.region_id
                  from hr.countries e
                 where e.country_id in
                       (select d.country_id
                          from hr.locations d
                         where d.location_id in
                               (select distinct b.location_id
                                  from hr.departments b,
                                       (select a.department_id
                                          from hr.employees a
                                         where a.commission_pct is not null
                                           and a.department_id is not null) c
                                 where b.department_id = c.department_id)))) x
where g.commission_pct is not null;

員工                    提成           部門            地區(qū)
John Russell        0.40        80        Europe
Karen Partners        0.30        80        Europe
Alberto Errazuriz        0.30        80        Europe
Gerald Cambrault        0.30        80        Europe
Eleni Zlotkey        0.20        80        Europe
Peter Tucker        0.30        80        Europe
David Bernstein        0.25        80        Europe
Peter Hall        0.25        80        Europe
Christopher Olsen        0.20        80        Europe
Nanette Cambrault        0.20        80        Europe
Oliver Tuvault        0.15        80        Europe
Janette King        0.35        80        Europe
Patrick Sully        0.35        80        Europe
Allan McEwen        0.35        80        Europe
Lindsey Smith        0.30        80        Europe
Louise Doran        0.30        80        Europe
Sarath Sewall        0.25        80        Europe
Clara Vishney        0.25        80        Europe
Danielle Greene        0.15        80        Europe
Mattea Marvins        0.10        80        Europe
David Lee        0.10        80        Europe
Sundar Ande        0.10        80        Europe
Amit Banda        0.10        80        Europe
Lisa Ozer        0.25        80        Europe
Harrison Bloom        0.20        80        Europe
Tayler Fox        0.20        80        Europe
William Smith        0.15        80        Europe
Elizabeth Bates        0.15        80        Europe
Sundita Kumar        0.10        80        Europe
Ellen Abel        0.30        80        Europe
Alyssa Hutton        0.25        80        Europe
Jonathon Taylor        0.20        80        Europe
Jack Livingston        0.20        80        Europe
Kimberely Grant        0.15                Europe
Charles Johnson        0.10        80        Europe

12. 顯示Executive部門有哪些職位。
select b.job_id,count(*) from hr.employees b where b.department_id in (
select a.department_id from hr.departments a where a.department_name ='Executive')
group by b.job_id

JOB_ID        COUNT(*)
AD_VP            2
AD_PRES        1

13. 整個公司中，最高工資和最低工資相差多少。
select max(salary) - min(salary) from hr.employees;

14. 提成大于0 的人數(shù)。
select * from hr.employees a where a.commission_pct is not null;

15. 顯示整個公司的最高工資、最低工資、工資總和、平均工資保留到整數(shù)位。
select max(nvl(salary, 0)),
       min(nvl(salary, 0)),
       sum(nvl(salary, 0)),
       round(avg(nvl(salary, 0)))
  from hr.employees;

16. 整個公司有多少個領導。
select distinct a.first_name || ' ' || a.last_name as "公司領導"
  from hr.employees a, hr.employees c
where a.employee_id(+) = c.manager_id

公司領導
Lex De Haan
Nancy Greenberg
Shanta Vollman
Alberto Errazuriz
Shelley Higgins
Payam Kaufling
John Russell
Eleni Zlotkey
Adam Fripp
Gerald Cambrault
Michael Hartstein
Steven King
Alexander Hunold
Den Raphaely
Matthew Weiss
Kevin Mourgos
Neena Kochhar
Karen Partners

17. 列出在同一部門入職日期晚但工資高于其他同事的員工：名字、工資、入職日期。
select distinct a.first_name || ' ' || a.last_name,
                a.hire_date,
                a.salary,
                a.department_id
  from hr.employees a, hr.employees b
where a.department_id = b.department_id
   and a.hire_date > b.hire_date
   and a.salary > b.salary
order by a.department_id
A.FIRST_NAME||''||A.LAST_NAME        HIRE_DATE        SALARY        DEPARTMENT_ID
Shelli Baida        1997/12/24        2900.00        30
Nandita Sarchand        1996/1/27        4200.00        50
Sarah Bell        1996/2/4        4000.00        50
Matthew Weiss        1996/7/18        8000.00        50
Alexis Bull        1997/2/20        4100.00        50
Britney Everett        1997/3/3        3900.00        50
Adam Fripp        1997/4/10        8200.00        50
Kelly Chung        1997/6/14        3800.00        50
Julia Nayer        1997/7/16        3200.00        50
Jennifer Dilly        1997/8/13        3600.00        50
Laura Bissot        1997/8/20        3300.00        50
Shanta Vollman        1997/10/10        6500.00        50
Stephen Stiles        1997/10/26        3200.00        50
Mozhe Atkinson        1997/10/30        2800.00        50
Winston Taylor        1998/1/24        3200.00        50
John Seo        1998/2/12        2700.00        50
Jean Fleaur        1998/2/23        3100.00        50
Randall Matos        1998/3/15        2600.00        50
Alana Walsh        1998/4/24        3100.00        50
Kevin Feeney        1998/5/23        3000.00        50
Julia Dellinger        1998/6/24        3400.00        50
Samuel McCain        1998/7/1        3200.00        50
Timothy Gates        1998/7/11        2900.00        50
Michael Rogers        1998/8/26        2900.00        50
Irene Mikkilineni        1998/9/28        2700.00        50
Anthony Cabrio        1999/2/7        3000.00        50
Vance Jones        1999/3/17        2800.00        50
Martha Sullivan        1999/6/21        2500.00        50
Donald OConnell        1999/6/21        2600.00        50
Kevin Mourgos        1999/11/16        5800.00        50
Ki Gee        1999/12/12        2400.00        50
Randall Perkins        1999/12/19        2500.00        50
Douglas Grant        2000/1/13        2600.00        50
Girard Geoni        2000/2/3        2800.00        50
Hazel Philtanker        2000/2/6        2200.00        50
Steven Markle        2000/3/8        2200.00        50
Ellen Abel        1996/5/11        11000.00        80
John Russell        1996/10/1        14000.00        80
Karen Partners        1997/1/5        13500.00        80
Peter Tucker        1997/1/30        10000.00        80
Alberto Errazuriz        1997/3/10        12000.00        80
Lisa Ozer        1997/3/11        11500.00        80
Alyssa Hutton        1997/3/19        8800.00        80
David Bernstein        1997/3/24        9500.00        80
Peter Hall        1997/8/20        9000.00        80
Clara Vishney        1997/11/11        10500.00        80
Tayler Fox        1998/1/24        9600.00        80
Harrison Bloom        1998/3/23        10000.00        80
Jonathon Taylor        1998/3/24        8600.00        80
Christopher Olsen        1998/3/30        8000.00        80
Jack Livingston        1998/4/23        8400.00        80
Nanette Cambrault        1998/12/9        7500.00        80
William Smith        1999/2/23        7400.00        80
Danielle Greene        1999/3/19        9500.00        80
Elizabeth Bates        1999/3/24        7300.00        80
Gerald Cambrault        1999/10/15        11000.00        80
Mattea Marvins        2000/1/24        7200.00        80
Eleni Zlotkey        2000/1/29        10500.00        80
David Lee        2000/2/23        6800.00        80
Sundar Ande        2000/3/24        6400.00        80
Nancy Greenberg        1994/8/17        12000.00        100
Jose Manuel Urman        1998/3/7        7800.00        100

www_xylove

SQL學習4-10:
1. 各個部門平均、最大、最小工資、人數(shù)，按照部門號升序排列。
2. 各個部門中工資大于5000的員工人數(shù)。
3. 各個部門平均工資和人數(shù)，按照部門名字升序排列。
4. 列出每個部門中有同樣工資的員工的統(tǒng)計信息，列出他們的部門號，工資，人數(shù)。
5. 列出同部門中工資高于10000 的員工數(shù)量超過2 人的部門，顯示部門名字、地區(qū)名稱。
6. 哪些員工的工資，高于整個公司的平均工資，列出員工的名字和工資（降序）。
7. 哪些員工的工資，介于50號 和80號部門平均工資之間。
8. 所在部門平均工資高于5000 的員工名字。
9. 列出各個部門中工資最高的員工的信息：名字、部門號、工資。
10. 最高的部門平均工資是多少。

1. 各個部門平均、最大、最小工資、人數(shù)，按照部門號升序排列。
select a.department_id as "部門",
       count(a.employee_id) as "部門人數(shù)"，
       trunc(avg(a.salary)) as "平均工資",
       max(a.salary) as "最高工資",
       min(a.salary) as "最低工資"
  from hr.employees a
where a.department_id is not null
group by a.department_id
order by a.department_id

部門        部門人數(shù)        平均工資        最高工資        最低工資
10        1        4400        4400        4400
20        2        9500        13000        6000
30        6        4150        11000        2500
40        1        6500        6500        6500
50        45        3475        8200        2100
60        5        5760        9000        4200
70        1        10000        10000        10000
80        34        8955        14000        6100
90        3        19333        24000        17000
100        6        8600        12000        6900
110        2        10150        12000        8300

2. 各個部門中工資大于5000的員工人數(shù)。
select a.department_id, a.salary, count(a.employee_id)
  from hr.employees a
where a.salary > 5000
and a.department_id is not null
group by a.department_id, a.salary
order by a.department_id;

DEPARTMENT_ID        SALARY        COUNT(A.EMPLOYEE_ID)
20        6000.00        1
20        13000.00        1
30        11000.00        1
40        6500.00        1
50        5800.00        1
50        6500.00        1
50        7900.00        1
50        8000.00        1
50        8200.00        1
60        6000.00        1
60        9000.00        1
70        10000.00        1
80        6100.00        1
80        6200.00        2
80        6400.00        1
80        6800.00        1
80        7000.00        2
80        7200.00        1
80        7300.00        1
80        7400.00        1
80        7500.00        2
80        8000.00        2
80        8400.00        1
80        8600.00        1
80        8800.00        1
80        9000.00        2
80        9500.00        3
80        9600.00        1
80        10000.00        3
80        10500.00        2
80        11000.00        2
80        11500.00        1
80        12000.00        1
80        13500.00        1
80        14000.00        1
90        17000.00        2
90        24000.00        1
100        6900.00        1
100        7700.00        1
100        7800.00        1
100        8200.00        1
100        9000.00        1
100        12000.00        1
110        8300.00        1
110        12000.00        1

3. 各個部門平均工資和人數(shù)，按照部門名字升序排列。
select a.department_id as "部門",
       b.department_name as "部門名稱",
       count(a.employee_id) as "部門人數(shù)",
       trunc(avg(a.salary)) as "平均工資"
  from hr.employees a, hr.departments b
where a.department_id is not null
   and a.department_id = b.department_id
group by a.department_id,b.department_name
order by a.department_id

部門        部門名稱        部門人數(shù)        平均工資
10        Administration        1        4400
20        Marketing        2        9500
30        Purchasing        6        4150
40        Human Resources        1        6500
50        Shipping        45        3475
60        IT        5        5760
70        Public Relations        1        10000
80        Sales        34        8955
90        Executive        3        19333
100        Finance        6        8600
110        Accounting        2        10150

4. 列出每個部門中有同樣工資的員工的統(tǒng)計信息，列出他們的部門號，工資，人數(shù)。
select b.department_id as "部門", b.salary as "工資", count(b.employee_id) as "員工數(shù)量"
  from hr.employees b,
       (select a.salary, count(*)
          from hr.employees a
         group by a.salary
        having count(*) > 1) x
where b.salary = x.salary
   and b.department_id is not null
group by b.department_id, b.salary
order by count(b.employee_id) desc

部門          工資           員工數(shù)量
50        2500.00        5
50        3200.00        4
50        3100.00        3
50        2800.00        3
50        2600.00        3
80        9500.00        3
80        10000.00        3
80        9000.00        2
80        10500.00        2
50        3600.00        2
50        2200.00        2
90        17000.00        2
50        3300.00        2
80        8000.00        2
50        2900.00        2
80        7000.00        2
80        11000.00        2
80        7500.00        2
50        2400.00        2
80        6200.00        2
60        4800.00        2
50        3000.00        2
50        2700.00        2
60        6000.00        1
60        9000.00        1
30        2900.00        1
30        11000.00        1
100        8200.00        1
80        12000.00        1
110        12000.00        1
30        3100.00        1
30        2800.00        1
100        12000.00        1
40        6500.00        1
70        10000.00        1
50        4200.00        1
50        8200.00        1
50        6500.00        1
60        4200.00        1
30        2500.00        1
20        6000.00        1
100        9000.00        1
50        8000.00        1
30        2600.00        1

驗證一下，部門50，工資都是2500的員工數(shù)量是不是5名員工。

select count(*) from hr.employees a where a.department_id =50  and salary = 2500
COUNT(*)
5

或者 5名員工的詳細信息
select a.employee_id, a.first_name, a.last_name, a.salary, a.department_id
  from hr.employees a
where a.department_id = 50
   and salary = 2500

EMPLOYEE_ID        FIRST_NAME        LAST_NAME        SALARY        DEPARTMENT_ID
131        James        Marlow        2500.00        50
140        Joshua        Patel        2500.00        50
144        Peter        Vargas        2500.00        50
182        Martha        Sullivan        2500.00        50
191        Randall        Perkins        2500.00        50

驗證沒有問題，的確是5名員工。

5. 列出同部門中工資高于10000 的員工數(shù)量超過2 人的部門，顯示部門名字、地區(qū)名稱。
select b.department_name, c.city
  from hr.employees a, hr.departments b, hr.locations c
where a.department_id in
       (select d.department_id
          from hr.departments d
         where d.department_id is not null)
   and a.salary > 10000
   and a.department_id = b.department_id
   and b.location_id = c.location_id
group by a.department_id, b.department_name, c.city
having count(a.employee_id) > 2

DEPARTMENT_NAME        CITY
Sales                     Oxford
Executive                 Seattle

6. 哪些員工的工資，高于整個公司的平均工資，列出員工的名字和工資（降序）。
select b.first_name ||' '|| b.last_name ,b.salary
  from hr.employees b
where b.salary > (select round(avg(salary)) from hr.employees a)
  order by b.salary desc

7. 哪些員工的工資，介于50號 和80號部門平均工資之間。   
select x.first_name || ' ' || x.last_name as "在50#和80#平均工資之間的員工",
       x.salary as "員工的工資",
       (select distinct round(avg(a.salary))
          from hr.employees a
         where a.department_id = 50) as "50#部門的平均工資",
       (select distinct round(avg(b.salary))
          from hr.employees b
         where b.department_id = 80) as "80#部門的平均工資"
  from hr.employees x
where x.salary between (select distinct round(avg(a.salary))
                           from hr.employees a
                          where a.department_id = 50) and
       (select distinct round(avg(b.salary))
          from hr.employees b
         where b.department_id = 80)
order by x.salary desc

我把50#部門的平均工資和80#部門的平均工資也列出來，目的是通過比較，結果集更加清晰。

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

8. 所在部門平均工資高于5000 的員工名字。
   select b.first_name || ' ' || b.last_name as "部門員工",
       b.salary as "員工工資",
       c.department_id as "部門",
       c."部門平均工資"
  from hr.employees b,
       (select a.department_id, round(avg(a.salary)) as "部門平均工資"
          from hr.employees a
         where a.department_id is not null
         group by a.department_id) c
where b.department_id = c.department_id
   and c."部門平均工資" > 5000
   and b.salary > c."部門平均工資"

部門員工        員工工資        部門        部門平均工資
Steven King        24000.00        90        19333
Alexander Hunold        9000.00        60        5760
Bruce Ernst        6000.00        60        5760
Nancy Greenberg        12000.00        100        8600
Daniel Faviet        9000.00        100        8600
John Russell        14000.00        80        8956
Karen Partners        13500.00        80        8956
Alberto Errazuriz        12000.00        80        8956
Gerald Cambrault        11000.00        80        8956
Eleni Zlotkey        10500.00        80        8956
Peter Tucker        10000.00        80        8956
David Bernstein        9500.00        80        8956
Peter Hall        9000.00        80        8956
Janette King        10000.00        80        8956
Patrick Sully        9500.00        80        8956
Allan McEwen        9000.00        80        8956
Clara Vishney        10500.00        80        8956
Danielle Greene        9500.00        80        8956
Lisa Ozer        11500.00        80        8956
Harrison Bloom        10000.00        80        8956
Tayler Fox        9600.00        80        8956
Ellen Abel        11000.00        80        8956
Michael Hartstein        13000.00        20        9500
Shelley Higgins        12000.00        110        10150

9. 列出各個部門中工資最高的員工的信息：名字、部門號、工資。
select b.department_id, b.first_name || ' ' || b.last_name, b.salary
  from hr.employees b,
       (select a.department_id, max(a.salary) as maxsal
          from hr.employees a
         where a.department_id is not null
         group by a.department_id
         order by a.department_id asc) c
where b.department_id = c.department_id
   and b.salary = c.maxsal
order by b.salary desc;
或者
SELECT b.FIRST_NAME || ' ' || b.LAST_NAME AS NAME,
       b.SALARY,
       b.DEPARTMENT_ID
  FROM HR.EMPLOYEES b
WHERE (b.DEPARTMENT_ID, b.SALARY) IN
       (SELECT a.DEPARTMENT_ID, MAX(a.SALARY)
          FROM HR.EMPLOYEES a
         GROUP BY a.DEPARTMENT_ID);

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

10. 最高的部門平均工資是多少。
select max(round(avg(a.salary)))
  from hr.employees a
where a.department_id is not null
group by a.department_id

或者
SELECT MAX(AVGSALARY)
FROM(
SELECT DEPARTMENT_ID,ROUND(AVG(SALARY)) AVGSALARY
  FROM hr.EMPLOYEES
    GROUP BY DEPARTMENT_ID
    );

MAX(ROUND(AVG(A.SALARY)))
19333

jackson198574

回復 1# www_xylove


    好帖子！頂！謝謝LZ分享！

開著Q7去西藏

謝謝lz分享，

wgzlong

謝謝樓主分享，講的很好理解

dingning239

版主這個帖子很好，適合大家學習提高SQL編寫能力

CxHeny

樓主，我sql學的很差，感謝了

dzminglong

   感謝分享，學習學習