前言

原作者@EricHu写的Oracle SQL经典查询联手系列文章已经很详细了,我根据Oracle 11g的实践过程重新做了梳理,之后会在扩展阅读中增加一部分Oracle官方培训教材供大家参考学习。

不懂装懂,永世饭桶


更新记录

2015年03月13日 - 撰写初稿

阅读原文 - https://wsgzao.github.io/post/oracle-sql/

扩展阅读


基础说明

set

1
2
3
4
5
--输出每页行数,缺省为24,为了避免分页,可设定为0
set pagesize 0

--输出一行字符个数,缺省为80
set linesize 300

column format

1
2
3
4
5
6
7
8
--如你的EMPNO,是整型的数据,输入以下命令,数字截为四位数显示
column EMPNO format 9999;

--如你的ENAME为字符型,输入以下命令,字符串截为10位显示
column ENAME format a10;

--重置为默认值
clear columns;

ORACLE SQL:经典查询练手第一篇

Oracle11g创建scott用户emp表

1
2
3
4
su - oracle
cd $ORACLE_HOME/rdbms/admin
sqlplus '/as sysdba'

1
2
3
4
@utlsampl.sql

conn scott/tiger


实例表结构与表的数据

scott.emp员工表结构

1
2
3
4
5
6
7
8
9
10
11
12
--提示:工资=薪金+佣金

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部门表

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

scott.emp表的现有数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- ---------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

scott.dept表的现有数据

1
2
3
4
5
6
7
8
SQL> select * from dept;

DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
--列出至少有一个员工的所有部门
SQL> select dname from dept where deptno in(select deptno from emp);

DNAME
----------------------------
ACCOUNTING
RESEARCH
SALES


--列出薪金比“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 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

13 rows selected.


--列出所有员工的姓名及其直接上级的姓名
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

14 rows selected.


--列出受雇日期早于其直接上级的所有员工
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.


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

DNAME EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO
---------- ---------- ---------- ---------- ---------- ------------ ---------- ----------
ACCOUNTING 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
ACCOUNTING 7839 KING PRESIDENT 17-NOV-81 5000 10
ACCOUNTING 7934 MILLER CLERK 7782 23-JAN-82 1300 10
RESEARCH 7566 JONES MANAGER 7839 02-APR-81 2975 20
RESEARCH 7902 FORD ANALYST 7566 03-DEC-81 3000 20
RESEARCH 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
RESEARCH 7369 SMITH CLERK 7902 17-DEC-80 800 20
RESEARCH 7788 SCOTT ANALYST 7566 19-APR-87 3000 20
SALES 7521 WARD SALESMAN 7698 22-FEB-81 1250 30
SALES 7844 TURNER SALESMAN 7698 08-SEP-81 1500 30
SALES 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 30
SALES 7900 JAMES CLERK 7698 03-DEC-81 950 30
SALES 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
SALES 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 30
OPERATIONS

15 rows selected.


--列出所有“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
ADAMS RESEARCH
JAMES SALES


--列出最低薪金大于1500的各种工作
SQL> select distinct job as HighSalJob from emp group by job having min(sal)>1500;

HIGHSALJOB
------------------
PRESIDENT
MANAGER
ANALYST


--列出在部门“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 where sal>(select avg(sal) from emp);

ENAME
----------
JONES
BLAKE
CLARK
SCOTT
KING
FORD

6 rows selected.


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

ENAME
----------
SCOTT
FORD


--列出薪金等于部门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


--列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金
SQL> select ename,sal from emp where sal>(select max(sal) from emp where deptno=30);

ENAME SAL
---------- ----------
JONES 2975
SCOTT 3000
KING 5000
FORD 3000


--列出在每个部门工作的员工数量、平均工资和平均服务期限
SQL> select (select b.dname from dept b where a.deptno=b.deptno) as deptname ,count(deptno) as deptcount,avg(sal) as deptavgsal from emp a group by deptno;

DEPTNAME DEPTCOUNT DEPTAVGSAL
---------------------------- ---------- ----------
SALES 6 1566.66667
RESEARCH 5 2175
ACCOUNTING 3 2916.66667


--列出所有员工的姓名、部门名称和工资
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
ALLEN SALES 1600
WARD SALES 1250
JONES RESEARCH 2975
MARTIN SALES 1250
BLAKE SALES 2850
CLARK ACCOUNTING 2450
SCOTT RESEARCH 3000
KING ACCOUNTING 5000
TURNER SALES 1500
ADAMS RESEARCH 1100
JAMES SALES 950
FORD RESEARCH 3000
MILLER ACCOUNTING 1300

14 rows selected.


--列出所有部门的详细信息和部门人数
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 3
20 RESEARCH DALLAS 5
30 SALES CHICAGO 6
40 OPERATIONS BOSTON


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

JOB AVG(SAL)
---------- ----------
CLERK 1037.5
SALESMAN 1400
PRESIDENT 5000
MANAGER 2758.33333
ANALYST 3000


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

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


--列出所有员工的年工资,按年薪从低到高排序
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 36000
KING 60000

14 rows selected.



ORACLE SQL:经典查询练手第二篇

实例表结构与表的数据

和第一篇相同


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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
--找出EMP表中的姓名(ENAME)第三个字母是A 的员工姓名
SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '__A%';

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


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

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


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

ENAME WAGE COMM
---------- ---------- ----------
TURNER 1500 0
WARD 1750 500
ALLEN 1900 300
MARTIN 2650 1400
SCOTT
KING
ADAMS
JAMES
FORD
MILLER
BLAKE
JONES
SMITH
CLARK

14 rows selected.


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

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


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

DEPTNO DNAME LOC
---------- ---------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
40 OPERATIONS BOSTON


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

ENAME WAGE
---------- ----------
MARTIN 2650
ALLEN 1900
WARD 1750


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

NAME JOB YEAR_SALARY
-------------------- ---------- -----------
TURNER SALESMAN 18000


/*----- 说明以下两条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;

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 SALGRADE;
SELECT * FROM BONUS;
SELECT * FROM EMP;
SELECT * FROM DEPT;


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

ENAME SAL
---------- ----------
ALLEN 1600
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
FORD 3000

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


ORACLE SQL:经典查询练手第三篇


ORACLE SQL:经典查询练手第四篇


ORACLE SQL:经典查询练手第五篇

文章目录
  1. 1. 前言
  2. 2. 更新记录
  3. 3. 基础说明
    1. 3.1. set
    2. 3.2. column format
  4. 4. ORACLE SQL:经典查询练手第一篇
    1. 4.1. Oracle11g创建scott用户emp表
    2. 4.2. 实例表结构与表的数据
    3. 4.3. SQL问题列表
    4. 4.4. 题目解答
  5. 5. ORACLE SQL:经典查询练手第二篇
    1. 5.1. 实例表结构与表的数据
    2. 5.2. SQL问题列表
    3. 5.3. 题目解答
  6. 6. ORACLE SQL:经典查询练手第三篇
  7. 7. ORACLE SQL:经典查询练手第四篇
  8. 8. ORACLE SQL:经典查询练手第五篇