oracle查询语句【转载】

时间:2024-01-18 11:25:38

建立的表:

表名:REGIONS

序号

列名

数据类型

长度

小数位

标识

主键

允许空

默认值

说明

1

REGION_ID

NUMBER

2

REGION_NAME

VARCHAR2

25

 

表名:COUNTRIES

序号

列名

数据类型

长度

小数位

标识

主键

允许空

默认值

说明

1

COUNTRY_ID

CHAR

2

2

COUNTRY_NAME

VARCHAR2

40

3

REGION_ID

NUMBER

表名:LOCATIONS

序号

列名

数据类型

长度

小数位

标识

主键

允许空

默认值

说明

1

LOCATION_ID

NUMBER

4

0

2

STREET_ADDRESS

VARCHAR2

40

3

POSTAL_CODE

VARCHAR2

12

4

CITY

VARCHAR2

30

5

STATE_PROVINCE

VARCHAR2

25

6

COUNTRY_ID

CHAR

2

表名:DEPARTMENTS

序号

列名

数据类型

长度

小数位

标识

主键

允许空

默认值

说明

1

DEPARTMENT_ID

NUMBER

4

0

2

DEPARTMENT_NAME

VARCHAR2

30

3

MANAGER_ID

NUMBER

6

0

4

LOCATION_ID

NUMBER

4

0

表名:JOBS

序号

列名

数据类型

长度

小数位

标识

主键

允许空

默认值

说明

1

JOB_ID

VARCHAR2

10

2

JOB_TITLE

VARCHAR2

35

3

MIN_SALARY

NUMBER

6

0

4

MAX_SALARY

NUMBER

6

0

表名:EMPLOYEES

序号

列名

数据类型

长度

小数位

标识

主键

允许空

默认值

说明

1

EMPLOYEE_ID

NUMBER

6

0

2

FIRST_NAME

VARCHAR2

20

3

LAST_NAME

VARCHAR2

25

4

EMAIL

VARCHAR2

25

5

PHONE_NUMBER

VARCHAR2

20

6

HIRE_DATE

DATE

7

7

JOB_ID

VARCHAR2

10

8

SALARY

NUMBER

8

2

9

COMMISSION_PCT

NUMBER

2

2

10

MANAGER_ID

NUMBER

6

0

11

DEPARTMENT_ID

NUMBER

4

0

ER图:

用SQL完成以下问题列表:

1. 各个部门平均、最大、最小工资、人数,按照部门号升序排列。

2. 各个部门中工资大于5000的员工人数。

3. 各个部门平均工资和人数,按照部门名字升序排列。

4. 列出每个部门中有同样工资的员工的统计信息,列出他们的部门号,工资,人数。

5. 列出同部门中工资高于1000 的员工数量超过2 人的部门,显示部门名字、地区名称。

6. 哪些员工的工资,高于整个公司的平均工资,列出员工的名字和工资(降序)。

7. 哪些员工的工资,介于50号 和80号部门平均工资之间。

8. 所在部门平均工资高于5000 的员工名字。

9. 列出各个部门中工资最高的员工的信息:名字、部门号、工资。

10. 最高的部门平均工资是多少。

 

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

 

/*--------1、各个部门平均、最大、最小工资、人数,按照部门号升序排列。---------*/

SQL> SELECT DEPARTMENT_ID AS 部门号,AVG(SALARY) AS 平均工资

2         ,MAX(SALARY) AS 最高工资,MIN(SALARY)  AS 最低工资

3         ,COUNT(*) AS 人数

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID

ORDER BY DEPARTMENT_ID ASC;

部门号       平均工资       最高工资       最低工资         人数

------        ----------        ----------        ----------        ----------

10         4400           4400           4400               1

20         9500           13000          6000               2

30         4150           11000          2500               6

40         6500           6500           6500               1

50         3475.55555     8200              2100               45

60         5760           9000           4200               5

70         10000          10000          10000              1

80         8973.85294     14000          6100               34

90         21333.3333     24000             20000              3

100         8600           12000          6900               6

110         10150          12000          8300               2

7000           7000           7000               1

12 rows selected

/*--------2、各个部门中工资大于5000的员工人数。---------*/

SQL> SELECT DEPARTMENT_ID,COUNT(*) FROM EMPLOYEES

WHERE SALARY > 5000

GROUP BY DEPARTMENT_ID;

DEPARTMENT_ID   COUNT(*)

------------- ----------

20          2

30          1

40          1

50          5

60          2

70          1

80         34

90          3

100          6

110          2

1

11 rows selected

/*--------3、各个部门平均工资和人数,按照部门名字升序排列。---------*/

SQL> SELECT DPTNAME,AVG(SALARY),COUNT(*) FROM

  2         (SELECT

  3             (SELECT DEPT.DEPARTMENT_NAME FROM DEPARTMENTS DEPT

4             WHERE DEPT.DEPARTMENT_ID = EMP.DEPARTMENT_ID) DPTNAME,

5             EMP.SALARY

FROM EMPLOYEES EMP)

GROUP BY DPTNAME

ORDER BY DPTNAME;

DPTNAME                        AVG(SALARY)   COUNT(*)

------------------------------ ----------- ----------

Accounting                           10150          2

Administration                        4400          1

Executive                      21333.33333          3

Finance                               8600          6

Human Resources                       6500          1

IT                                    5760          5

Marketing                             9500          2

Public Relations                     10000          1

Purchasing                            4150          6

Sales                          8973.852941         34

Shipping                       3475.555555         45

7000          1

12 rows selected

--或者--

SQL> SELECT DEPT.DEPARTMENT_NAME,AVG(EMP.SALARY),COUNT(*)

FROM EMPLOYEES EMP,DEPARTMENTS DEPT

WHERE EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID

GROUP BY DEPT.DEPARTMENT_NAME

ORDER BY DEPT.DEPARTMENT_NAME;

DEPARTMENT_NAME                AVG(EMP.SALARY)   COUNT(*)

------------------------------ --------------- ----------

Accounting                               10150          2

Administration                            4400          1

Executive                      21333.333333333          3

Finance                                   8600          6

Human Resources                           6500          1

IT                                        5760          5

Marketing                                 9500          2

Public Relations                         10000          1

Purchasing                                4150          6

Sales                          8973.8529411764         34

Shipping                       3475.5555555555         45

11 rows selected

--可以看到,这种方式,对于部门号为空的没有统计出来

/*--------4、列出每个部门中有同样工资的员工的统计信息,

列出他们的部门号,工资,人数。---------*/

SQL> SELECT EMP1.DEPARTMENT_ID,EMP1.SALARY,COUNT(*) CNT

FROM   EMPLOYEES EMP1,EMPLOYEES EMP2

WHERE  EMP1.DEPARTMENT_ID = EMP2.DEPARTMENT_ID AND

   4          EMP1.SALARY = EMP2.SALARY

5          AND EMP1.EMPLOYEE_ID <> EMP2.EMPLOYEE_ID

GROUP BY EMP1.DEPARTMENT_ID,EMP1.SALARY;

DEPARTMENT_ID     SALARY        CNT

------------- ---------- ----------

50    2200.00          2

50    2400.00          2

50    2500.00         20

50    2600.00          6

50    2700.00          2

50    2800.00          6

50    2900.00          2

50    3000.00          2

50    3100.00          6

50    3200.00         12

50    3300.00          2

50    3600.00          2

60    4800.00          2

80    7000.00          2

80    7500.00          2

80    8000.00          6

80    9000.00          2

80    9500.00          6

80   10000.00          6

80   10500.00          2

80   11000.00          2

90   20000.00          2

22 rows selected

/*--------5、列出同部门中工资高于1000 的员工数量超过2 人的部门,

显示部门名字、地区名称。---------*/

SQL> SELECT D.DEPARTMENT_NAME,L.CITY,COUNT(*)

FROM EMPLOYEES E,DEPARTMENTS D,LOCATIONS L

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND

   4         D.LOCATION_ID   = L.LOCATION_ID    AND

   5         E.SALARY > 1000

GROUP BY D.DEPARTMENT_NAME,L.CITY

HAVING COUNT(*) > 2;

DEPARTMENT_NAME                CITY                             COUNT(*)

------------------------------ ------------------------------ ----------

IT                             Southlake                               5

Sales                          Oxford                                 34

Finance                        Seattle                                 6

Shipping                       South San Francisco                    45

Executive                      Seattle                                 3

Purchasing                     Seattle                                 6

6 rows selected

/*--------6、哪些员工的工资,高于整个公司的平均工资,

列出员工的名字和工资(降序)。---------*/

SQL> SELECT FIRST_NAME || ' ' || LAST_NAME,SALARY

FROM EMPLOYEES

WHERE SALARY > (

4        SELECT AVG(SALARY)

5        FROM EMPLOYEES

6        )

ORDER BY SALARY DESC;

FIRST_NAME||''||LAST_NAME                          SALARY

---------------------------------------------- ----------

Steven King                                      24000.00

Neena Kochhar                                    20000.00

Lex De Haan                                      20000.00

John Russell                                     14000.00

Karen Partners                                   13500.00

Michael Hartstein                                13000.00

Nancy Greenberg                                  12000.00

Alberto Errazuriz                                12000.00

Shelley Higgins                                  12000.00

Lisa Ozer                                        11500.00

Den Raphaely                                     11000.00

Gerald Cambrault                                 11000.00

Ellen Abel                                       11000.00

Eleni Zlotkey                                    10500.00

Clara Vishney                                    10500.00

Peter Tucker                                     10000.00

Janette King                                     10000.00

Harrison Bloom                                   10000.00

Hermann Baer                                     10000.00

Tayler Fox                                        9600.00

--共50条数据

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

SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY

FROM EMPLOYEES

WHERE SALARY

BETWEEN

  5      (SELECT AVG(SALARY) FROM EMPLOYEES

6       WHERE DEPARTMENT_ID = 50)

7  AND (SELECT AVG(SALARY) FROM EMPLOYEES

8       WHERE DEPARTMENT_ID = 80);

NAME                                               SALARY

---------------------------------------------- ----------

Bruce Ernst                                       6000.00

David Austin                                      4800.00

Valli Pataballa                                   4800.00

Diana Lorentz                                     4200.00

John Chen                                         8200.00

Ismael Sciarra                                    7700.00

Jose Manuel Urman                                 7800.00

Luis Popp                                         6900.00

Matthew Weiss                                     8000.00

Adam Fripp                                        8200.00

Payam Kaufling                                    7900.00

Shanta Vollman                                    6500.00

Kevin Mourgos                                     5800.00

Renske Ladwig                                     3600.00

Trenna Rajs                                       3500.00

Christopher Olsen                                 8000.00

Nanette Cambrault                                 7500.00

Oliver Tuvault                                    7000.00

Lindsey Smith                                     8000.00

Louise Doran                                      7500.00

Sarath Sewall                                     7000.00

Mattea Marvins                                    7200.00

David Lee                                         6800.00

Sundar Ande                                       6400.00

Amit Banda                                        6200.00

William Smith                                     7400.00

Elizabeth Bates                                   7300.00

Sundita Kumar                                     6100.00

Alyssa Hutton                                     8800.00

Jonathon Taylor                                   8600.00

Jack Livingston                                   8000.00

Kimberely Grant                                   7000.00

Charles Johnson                                   7211.00

Nandita Sarchand                                  4200.00

Alexis Bull                                       4100.00

Kelly Chung                                       3800.00

Jennifer Dilly                                    3600.00

Sarah Bell                                        4000.00

Britney Everett                                   3900.00

Jennifer Whalen                                   4400.00

Pat Fay                                           6000.00

Susan Mavris                                      6500.00

William Gietz                                     8300.00

43 rows selected

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

SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY

FROM EMPLOYEES

WHERE DEPARTMENT_ID IN

   4        (SELECT DEPARTMENT_ID FROM EMPLOYEES

5         GROUP BY DEPARTMENT_ID

6         HAVING AVG(SALARY) > 5000);

NAME                                               SALARY

---------------------------------------------- ----------

Michael Hartstein                                13000.00

Pat Fay                                           6000.00

Susan Mavris                                      6500.00

Alexander Hunold                                  9000.00

Bruce Ernst                                       6000.00

David Austin                                      4800.00

Valli Pataballa                                   4800.00

Diana Lorentz                                     4200.00

Hermann Baer                                     10000.00

John Russell                                     14000.00

Karen Partners                                   13500.00

Alberto Errazuriz                                12000.00

Gerald Cambrault                                 11000.00

Eleni Zlotkey                                    10500.00

Peter Tucker                                     10000.00

David Bernstein                                   9500.00

Peter Hall                                        9000.00

Christopher Olsen                                 8000.00

Nanette Cambrault                                 7500.00

Oliver Tuvault                                    7000.00

--等54行数据…

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

SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME

2         ,SALARY,DEPARTMENT_ID

FROM EMPLOYEES

WHERE (DEPARTMENT_ID,SALARY) IN

   5        (SELECT DEPARTMENT_ID,MAX(SALARY)

6         FROM EMPLOYEES

7         GROUP BY DEPARTMENT_ID);

NAME                                               SALARY DEPARTMENT_ID

---------------------------------------------- ---------- -------------

Jennifer Whalen                                   4400.00            10

Michael Hartstein                                13000.00            20

Den Raphaely                                     11000.00            30

Susan Mavris                                      6500.00            40

Adam Fripp                                        8200.00            50

Alexander Hunold                                  9000.00            60

Hermann Baer                                     10000.00            70

John Russell                                     14000.00            80

Steven King                                      24000.00            90

Nancy Greenberg                                  12000.00           100

Shelley Higgins                                  12000.00           110

11 rows selected

/*--------10、最高的部门平均工资是多少。---------*/

SQL> SELECT MAX(AVGSALARY)

FROM(SELECT DEPARTMENT_ID,AVG(SALARY) AVGSALARY

3    FROM EMPLOYEES

4    GROUP BY DEPARTMENT_ID);

MAX(AVGSALARY)

--------------

21333.33333333

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

scott.emp员工表结构如下:

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

scott.dept部门表

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

提示:工资=薪金+佣金

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

 

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

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

 

 
SQL> select * from dept;   DEPTNO DNAME          LOC ------ -------------- -------------     10 ACCOUNTING     NEW YORK     20 RESEARCH       DALLAS     30 SALES          CHICAGO     40 OPERATIONS     BOSTON     50 50abc          50def     60 Developer      HaiKou   6 rows selected
 

用SQL完成以下问题列表:

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

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

 
--------1.列出至少有一个员工的所有部门。--------- SQL> select dname from dept where deptno in(select deptno from emp);  DNAME -------------- RESEARCH SALES ACCOUNTING --------或-------- SQL> select dname from dept where deptno in(select deptno from emp group by deptno having count(deptno) >=1);  DNAME -------------- ACCOUNTING RESEARCH SALES
--------2.列出薪金比“SMITH”多的所有员工。---------- SQL> select * from emp where sal > (select sal from emp where ename = 'SMITH');   EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------  7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30  7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30  7566 JONES      MANAGER    7839 1981-4-2      2975.00               20  7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30  7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30  7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10  7788 SCOTT      ANALYST    7566 1987-4-19     4000.00               20  7839 KING       PRESIDENT       1981-11-17    5000.00               10  7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30  7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20  7900 JAMES      CLERK      7698 1981-12-3      950.00               30  7902 FORD       ANALYST    7566 1981-12-3     3000.00               20  7934 MILLER     CLERK      7782 1982-1-23     1300.00               10   102 EricHu     Developer  1455 2011-5-26 1   5500.00     14.00     10   104 huyong     PM         1455 2011-5-26 1   5500.00     14.00     10   105 WANGJING   Developer  1455 2011-5-26 1   5500.00     14.00     10  16 rows selected
--------3.列出所有员工的姓名及其直接上级的姓名。---------- SQL> select a.ename,(select ename from emp b where b.empno=a.mgr) as boss_name from emp a;  ENAME      BOSS_NAME ---------- ---------- SMITH      FORD ALLEN      BLAKE WARD       BLAKE JONES      KING MARTIN     BLAKE BLAKE      KING CLARK      KING SCOTT      JONES KING        TURNER     BLAKE ADAMS      SCOTT JAMES      BLAKE FORD       JONES MILLER     CLARK EricHu      huyong      WANGJING     17 rows selected
--------4.列出受雇日期早于其直接上级的所有员工。---------- SQL> select a.ename from emp a where a.hiredate<(select hiredate from emp b where b.empno=a.mgr);  ENAME ---------- SMITH ALLEN WARD JONES BLAKE CLARK  6 rows selected
--------5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门---------- SQL> select a.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.deptno   2  from dept a left join emp b on a.deptno=b.deptno;   DNAME          EMPNO ENAME      JOB         MGR HIREDATE          SAL DEPTNO -------------- ----- ---------- --------- ----- ----------- --------- ------ RESEARCH        7369 SMITH      CLERK      7902 1980-12-17     800.00     20 SALES           7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00     30 SALES           7521 WARD       SALESMAN   7698 1981-2-22     1250.00     30 RESEARCH        7566 JONES      MANAGER    7839 1981-4-2      2975.00     20 SALES           7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00     30 SALES           7698 BLAKE      MANAGER    7839 1981-5-1      2850.00     30 ACCOUNTING      7782 CLARK      MANAGER    7839 1981-6-9      2450.00     10 RESEARCH        7788 SCOTT      ANALYST    7566 1987-4-19     4000.00     20 ACCOUNTING      7839 KING       PRESIDENT       1981-11-17    5000.00     10 SALES           7844 TURNER     SALESMAN   7698 1981-9-8      1500.00     30 RESEARCH        7876 ADAMS      CLERK      7788 1987-5-23     1100.00     20 SALES           7900 JAMES      CLERK      7698 1981-12-3      950.00     30 RESEARCH        7902 FORD       ANALYST    7566 1981-12-3     3000.00     20 ACCOUNTING      7934 MILLER     CLERK      7782 1982-1-23     1300.00     10 ACCOUNTING       102 EricHu     Developer  1455 2011-5-26 1   5500.00     10 ACCOUNTING       104 huyong     PM         1455 2011-5-26 1   5500.00     10 ACCOUNTING       105 WANGJING   Developer  1455 2011-5-26 1   5500.00     10 50abc                                                                  OPERATIONS                                                             Developer                                                             20 rows selected
--------6.列出所有“CLERK”(办事员)的姓名及其部门名称。---------- SQL> select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno and a.job='CLERK';  ENAME      DNAME ---------- -------------- SMITH      RESEARCH ADAMS      RESEARCH JAMES      SALES MILLER     ACCOUNTING
--------7.列出最低薪金大于1500的各种工作。---------- SQL> select distinct job as HighSalJob from emp group by job having min(sal)>1500;  HIGHSALJOB ---------- ANALYST Developer MANAGER PM PRESIDENT
--------8.列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。---------- SQL> select ename from emp where deptno=(select deptno from dept where dname='SALES');  ENAME ---------- ALLEN WARD MARTIN BLAKE TURNER JAMES  6 rows selected
--------9.列出薪金高于公司平均薪金的所有员工。---------- SQL> select ename from emp where sal>(select avg(sal) from emp);  ENAME ---------- JONES BLAKE SCOTT KING FORD EricHu huyong WANGJING  8 rows selected
--------10.列出与“SCOTT”从事相同工作的所有员工。-------- SQL> select ename from emp where job=(select job from emp where ename='SCOTT');  ENAME ---------- SCOTT FORD
--------11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。--------- SQL> select a.ename,a.sal from emp a where a.sal in (select b.sal   2  from emp b where b.deptno=30) and a.deptno<>30;  ENAME            SAL ---------- --------- --------12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。--------- SQL> select ename,sal from emp where sal>(select max(sal) from emp where deptno=30);  ENAME            SAL ---------- --------- JONES        2975.00 SCOTT        4000.00 KING         5000.00 FORD         3000.00 EricHu       5500.00 huyong       5500.00 WANGJING     5500.00  7 rows selected
--------13.列出在每个部门工作的员工数量、平均工资和平均服务期限。--------- SQL> select (select b.dname from dept b where a.deptno=b.deptno) as deptname ,count(deptno) as deptcount,avg(sal) as deptavgsal   2  from emp a group by deptno;  DEPTNAME        DEPTCOUNT DEPTAVGSAL -------------- ---------- ---------- ACCOUNTING              6 4208.33333 RESEARCH                5       2375 SALES                   6 1566.66666
--------14.列出所有员工的姓名、部门名称和工资。--------- SQL> select a.ename,(select b.dname from dept b where b.deptno=a.deptno) as deptname,sal from emp a;    ENAME      DEPTNAME             SAL ---------- -------------- --------- SMITH      RESEARCH          800.00 ALLEN      SALES            1600.00 WARD       SALES            1250.00 JONES      RESEARCH         2975.00 MARTIN     SALES            1250.00 BLAKE      SALES            2850.00 CLARK      ACCOUNTING       2450.00 SCOTT      RESEARCH         4000.00 KING       ACCOUNTING       5000.00 TURNER     SALES            1500.00 ADAMS      RESEARCH         1100.00 JAMES      SALES             950.00 FORD       RESEARCH         3000.00 MILLER     ACCOUNTING       1300.00 EricHu     ACCOUNTING       5500.00 huyong     ACCOUNTING       5500.00 WANGJING   ACCOUNTING       5500.00   17 rows selected
--------15.列出所有部门的详细信息和部门人数。--------- SQL> select a.deptno,a.dname,a.loc,(select count(deptno) from emp b where b.deptno=a.deptno group by b.deptno) as deptcount from dept a;  DEPTNO DNAME          LOC            DEPTCOUNT ------ -------------- ------------- ----------     10 ACCOUNTING     NEW YORK               6     20 RESEARCH       DALLAS                 5     30 SALES          CHICAGO                6     40 OPERATIONS     BOSTON             50 50abc          50def              60 Developer      HaiKou        6 rows selected
--------16.列出各种工作的最低工资。--------- SQL> select job,avg(sal) from emp group by job;   JOB         AVG(SAL) --------- ---------- ANALYST         3500 CLERK         1037.5 Developer       5500 MANAGER   2758.33333 PM              5500 PRESIDENT       5000 SALESMAN        1400   7 rows selected
--------17.列出各个部门的MANAGER(经理)的最低薪金。-------- SQL> select deptno,min(sal) from emp where job='MANAGER' group by deptno;   DEPTNO   MIN(SAL) ------ ----------     10       2450     20       2975 30       2850
--------18.列出所有员工的年工资,按年薪从低到高排序。--------- SQL> select ename,(sal+nvl(comm,0))*12 as salpersal from emp order by salpersal;   ENAME       SALPERSAL ---------- ---------- SMITH            9600 JAMES           11400 ADAMS           13200 MILLER          15600 TURNER          18000 WARD            21000 ALLEN           22800 CLARK           29400 MARTIN          31800 BLAKE           34200 JONES           35700 FORD            36000 SCOTT           48000 KING            60000 EricHu          66168 huyong          66168 WANGJING        66168   17 rows selected
 

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

scott.emp员工表结构如下:

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

scott.dept部门表

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

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

 

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

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

 
SQL> SELECT * FROM SCOTT.DEPT;   DEPTNO DNAME          LOC ------ -------------- -------------    110 信息科         海口     10 ACCOUNTING     NEW YORK     20 RESEARCH       DALLAS     30 SALES          CHICAGO     40 OPERATIONS     BOSTON     50 50abc          50def     60 Developer      HaiKou   7 rows selected
 

用SQL完成以下问题列表:

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

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

 
--------1. 找出EMP表中的姓名(ENAME)第三个字母是A 的员工姓名。--------- SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '__A%';   ENAME ---------- ADAMS BLAKE CLARK
-------2. 找出EMP表员工名字中含有A 和N的员工姓名。---------- SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '%A%' AND ENAME LIKE '%N%';   ENAME ---------- ALLEN MARTIN WANGJING --------或-------- SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '%A%N%';   ENAME ---------- ALLEN MARTIN WANGJING
/*--------3. 找出所有有佣金的员工,列出姓名、工资、佣金,显示结果按工资从小到大, 佣金从大到小。----------*/ SQL> SELECT ENAME,SAL + COMM AS WAGE,COMM   2  FROM SCOTT.EMP   3  ORDER BY WAGE,COMM DESC;   ENAME            WAGE      COMM ---------- ---------- --------- TURNER           1500      0.00 WARD             1750    500.00 ALLEN            1900    300.00 MARTIN           2650   1400.00 EricHu           5514     14.00 WANGJING         5514     14.00 huyong           5514     14.00 SMITH                  JONES                  JAMES                  MILLER                 FORD                   ADAMS                  BLAKE                  CLARK                  SCOTT                  KING                     17 rows selected
-------4. 列出部门编号为20的所有职位。---------- SQL> SELECT DISTINCT JOB FROM EMP WHERE DEPTNO = 20;   JOB --------- ANALYST CLERK MANAGER
-------5. 列出不属于SALES 的部门。---------- SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME <> 'SALES';   DEPTNO DNAME          LOC ------ -------------- -------------     10 ACCOUNTING     NEW YORK     20 RESEARCH       DALLAS     40 OPERATIONS     BOSTON     50 50abc          50def     60 Developer      HaiKou    110 信息科         海口   6 rows selected
--或者: SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME != 'SALES'; SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME NOT IN('SALES'); SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME NOT LIKE 'SALES';
---6. 显示工资不在1000 到1500 之间的员工信息:名字、工资,按工资从大到小排序。--------- SQL> SELECT ENAME,SAL + COMM AS WAGE FROM SCOTT.EMP   2  WHERE SAL + COMM NOT BETWEEN 1000 AND 1500   3  ORDER BY WAGE DESC;   ENAME            WAGE ---------- ---------- EricHu           5514 huyong           5514 WANGJING         5514 MARTIN           2650 ALLEN            1900 WARD             1750   6 rows selected --或者 SQL> SELECT ENAME,SAL + COMM AS WAGE FROM SCOTT.EMP   2  WHERE SAL + COMM < 1000 OR SAL + COMM > 1500   3  ORDER BY WAGE DESC;   ENAME            WAGE ---------- ---------- EricHu           5514 huyong           5514 WANGJING         5514 MARTIN           2650 ALLEN            1900 WARD             1750   6 rows selected
/*----- 7. 显示职位为MANAGER 和SALESMAN,年薪在15000 和20000 之间的员工的信息:名字、职位、年薪。----------*/ SQL> SELECT ENAME 姓名,JOB 职位,(SAL + COMM) * 12 AS 年薪   2  FROM SCOTT.EMP   3  WHERE (SAL + COMM) * 12 BETWEEN 15000 AND 20000   4  AND JOB IN('MANAGER','SALESMAN');   姓名       职位              年薪 ---------- --------- ---------- TURNER     SALESMAN       18000
/*----- 8. 说明以下两条SQL语句的输出结果:     SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;     SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL; ----------*/ SQL> SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;   EMPNO      COMM ----- ---------  7369   7566   7698   7782   7788   7839   7876   7900   7902   7934    10 rows selected
--------------------------------------------------------------- SQL> SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;   EMPNO      COMM ----- --------- --说明:IS NULL是判断某个字段是否为空,为空并不等价于为空字符串或为数字0; --而 =NULL 是判断某个值是否等于 NULL,NULL = NULL和NULL <> NULL都为 FALSE。 /*-----9. 让SELECT 语句的输出结果为     SELECT * FROM SALGRADE; SELECT * FROM BONUS; SELECT * FROM EMP; SELECT * FROM DEPT; …… 列出当前用户有多少张数据表,结果集中存在多少条记录。 ----------*/
SQL> SELECT 'SELECT * FROM '||TABLE_NAME||';' FROM USER_TABLES;   'SELECT*FROM'||TABLE_NAME||';' --------------------------------------------- SELECT * FROM BONUS; SELECT * FROM EMP; SELECT * FROM DEPT; --......等等,在此不列出。 ---10. 语句SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否抱错?--------- SQL> SELECT ENAME,SAL FROM EMP WHERE SAL > '1500';   ENAME            SAL ---------- --------- ALLEN        1600.00 JONES        2975.00 BLAKE        2850.00 CLARK        2450.00 SCOTT        4000.00 KING         5000.00 FORD         3000.00 EricHu       5500.00 huyong       5500.00 WANGJING     5500.00   10 rows selected   SQL> SELECT ENAME,SAL FROM EMP WHERE SAL > 1500;   ENAME            SAL ---------- --------- ALLEN        1600.00 JONES        2975.00 BLAKE        2850.00 CLARK        2450.00 SCOTT        4000.00 KING         5000.00 FORD         3000.00 EricHu       5500.00 huyong       5500.00 WANGJING     5500.00   10 rows selected --说明不会抱错,这儿存在隐式数据类型的。
 

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

scott.emp员工表结构如下:

scott.dept部门表

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

 

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

用SQL完成以下问题列表:

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

© 2011  EricHu