SQL堂上作业三
字符串相关
小写字母转换
在一些数据查询的场景中，我们要查询一些同时包含大小写字母的值，但是一般情况下无法实现大小写模糊匹配。
SELECT employee_id, last_name, department_id
FROM employees
WHERE last_name = 'higgins';
未选定行
我们发现，上文就没有输出
我们将last_name 改为lower(last_name)，发现sql就可以实现大小写模糊匹配了
SELECT employee_id, last_name, department_id
FROM employees
WHERE LOWER(last_name) = 'higgins';
输出如下
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID
----------- -------------------------------------------------- -------------
205 Higgins 110
一些字符串处理函数
| 用途 | FUNCTION | RESULT |
|---|---|---|
| 连接两个字符串 | CONCAT('Hello', 'World') | HelloWorld |
| 截取子串 | SUBSTR('HelloWorld',1,5) | Hello |
| 求字符串长度 | LENGTH('HelloWorld') | 10 |
| 求串1中首次出现串2的位置 | INSTR('HelloWorld', 'W') | 6 |
| 左填充* | LPAD(salary,10,'*') | \(*****24000\) |
| 右填充* | RPAD(salary, 10, '*') | \(24000*****\) |
| 翻转字符串 | TRIM('H' FROM 'HelloWorld') | elloWorld |
字符串处理函数综合应用
SELECT employee_id, CONCAT(first_name, last_name) NAME,
job_id, LENGTH (last_name),
INSTR(last_name, 'a') "Contains 'a'?"
FROM employees
WHERE SUBSTR(job_id, 4) = 'REP';
这个语句的意思是：输出（职工ID，姓+名，职业ID，姓的长度，姓中首次出现a的位置）
输出如下
EMPLOYEE_ID NAME JOB_ID LENGTH(LAST_NAME) Contains 'a'?
----------- ------------------------------------------------------------------------------------------ -------------------- ----------------- -------------
150 PeterTucker SA_REP 6 0
151 DavidBernstein SA_REP 9 0
152 PeterHall SA_REP 4 2
153 ChristopherOlsen SA_REP 5 0
154 NanetteCambrault SA_REP 9 2
155 OliverTuvault SA_REP 7 4
156 JanetteKing SA_REP 4 0
157 PatrickSully SA_REP 5 0
158 AllanMcEwen SA_REP 6 0
159 LindseySmith SA_REP 5 0
160 LouiseDoran SA_REP 5 4
161 SarathSewall SA_REP 6 4
162 ClaraVishney SA_REP 7 0
163 DanielleGreene SA_REP 6 0
164 MatteaMarvins SA_REP 7 2
165 DavidLee SA_REP 3 0
166 SundarAnde SA_REP 4 0
167 AmitBanda SA_REP 5 2
168 LisaOzer SA_REP 4 0
169 HarrisonBloom SA_REP 5 0
170 TaylerFox SA_REP 3 0
171 WilliamSmith SA_REP 5 0
172 ElizabethBates SA_REP 5 2
173 SunditaKumar SA_REP 5 4
174 EllenAbel SA_REP 4 0
175 AlyssaHutton SA_REP 6 0
176 JonathonTaylor SA_REP 6 2
177 JackLivingston SA_REP 10 0
178 KimberelyGrant SA_REP 5 3
179 CharlesJohnson SA_REP 7 0
202 PatFay MK_REP 3 2
203 SusanMavris HR_REP 6 2
204 HermannBaer PR_REP 4 2
已选择33行。
运算相关
有这一些函数可以使用
ROUND(a,b)：四舍五入函数，将数a保留b位小数后输出，其中第b+1位四舍五入
TRUNC(a,b)：保留b位函数，第b+1位直接舍弃
MOD(a,b)： 输出a%b，取模函数
ROUND
SELECT ROUND(45.923,2), ROUND(45.923,0),
ROUND(45.923,-1)
FROM DUAL;
输出如下
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
--------------- --------------- ----------------
45.92 46 50
TRUNC
SELECT TRUNC(45.923,2), TRUNC(45.923),
TRUNC(45.923,-2)
FROM DUAL;
输出如下
TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-2)
--------------- ------------- ----------------
45.92 45 0
MOD
SELECT last_name, salary, MOD(salary, 5000)
FROM employees
WHERE job_id = 'SA_REP';
输出如下
LAST_NAME SALARY MOD(SALARY,5000)
-------------------------------------------------- ---------- ----------------
Tucker 10000 0
Bernstein 9500 4500
Hall 9000 4000
Olsen 8000 3000
Cambrault 7500 2500
Tuvault 7000 2000
King 10000 0
Sully 9500 4500
McEwen 9000 4000
Smith 8000 3000
Doran 7500 2500
Sewall 7000 2000
Vishney 10500 500
Greene 9500 4500
Marvins 7200 2200
Lee 6800 1800
Ande 6400 1400
Banda 6200 1200
Ozer 11500 1500
Bloom 10000 0
Fox 9600 4600
Smith 7400 2400
Bates 7300 2300
Kumar 6100 1100
Abel 11000 1000
Hutton 8800 3800
Taylor 8600 3600
Livingston 8400 3400
Grant 7000 2000
Johnson 6200 1200
已选择30行。
日期相关
数据库中可以存储日期，时间等数据
注意：日期，时间等数据是可以运算的
默认情况
默认情况下，输出将按照DD-MM-YYYY格式进行
SELECT last_name, hire_date
FROM employees
WHERE last_name like 'G%';
输出如下
LAST_NAME HIRE_DATE
-------------------------------------------------- --------------
Gates 11-7月 -98
Gee 12-12月-99
Geoni 03-2月 -00
Gietz 07-6月 -94
Grant 13-1月 -00
Grant 24-5月 -99
Greenberg 17-8月 -94
Greene 19-3月 -99
已选择8行。
SYSDATE
SYSDATE用于表示当前的日期和时间
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees
WHERE department_id = 90;
输出如下
LAST_NAME WEEKS
-------------------------------------------------- ----------
King 1843.48921
Kochhar 1725.34635
De Haan 1552.48921
日期运算符
常用的日期运算符如下所示：
| 功能 | Function | Output |
|---|---|---|
| 输出两个日期间隔了几个月 | MONTHS_BETWEEN ('01-SEP-95','11-JAN-94') | 19.6774194 |
| 在某个日期上加上若干个月 | ADD_MONTHS ('11-JAN-94',6) | '11-JUL-94' |
| 找到符合条件的下一天 | NEXT_DAY ('01-SEP-95','FRIDAY') | '08-SEP-95' |
| 找到符合条件的上一天 | LAST_DAY('01-FEB-95') | '28-FEB- |
| 找到符合条件的前一个日期 | ROUND | |
| 找到符合条件的后一个日期 | TRUNC |
SELECT last_name,
TO_CHAR(hire_date, 'fmDD Month YYYY')
AS HIREDATE
FROM employees;
输出如下：
LAST_NAME HIREDATE
-------------------------------------------------- --------------------------------
King 17 6月 1987
Kochhar 21 9月 1989
De Haan 13 1月 1993
Hunold 3 1月 1990
Ernst 21 5月 1991
Austin 25 6月 1997
Pataballa 5 2月 1998
Lorentz 7 2月 1999
Greenberg 17 8月 1994
Faviet 16 8月 1994
Chen 28 9月 1997
Sciarra 30 9月 1997
Urman 7 3月 1998
Popp 7 12月 1999
Raphaely 7 12月 1994
Khoo 18 5月 1995
Baida 24 12月 1997
Tobias 24 7月 1997
Himuro 15 11月 1998
Colmenares 10 8月 1999
Weiss 18 7月 1996
Fripp 10 4月 1997
Kaufling 1 5月 1995
Vollman 10 10月 1997
Mourgos 16 11月 1999
Nayer 16 7月 1997
Mikkilineni 28 9月 1998
Landry 14 1月 1999
Markle 8 3月 2000
Bissot 20 8月 1997
Atkinson 30 10月 1997
Marlow 16 2月 1997
Olson 10 4月 1999
Mallin 14 6月 1996
Rogers 26 8月 1998
Gee 12 12月 1999
Philtanker 6 2月 2000
Ladwig 14 7月 1995
Stiles 26 10月 1997
Seo 12 2月 1998
Patel 6 4月 1998
Rajs 17 10月 1995
Davies 29 1月 1997
Matos 15 3月 1998
Vargas 9 7月 1998
Russell 1 10月 1996
Partners 5 1月 1997
Errazuriz 10 3月 1997
Cambrault 15 10月 1999
Zlotkey 29 1月 2000
Tucker 30 1月 1997
Bernstein 24 3月 1997
Hall 20 8月 1997
Olsen 30 3月 1998
Cambrault 9 12月 1998
Tuvault 23 11月 1999
King 30 1月 1996
Sully 4 3月 1996
McEwen 1 8月 1996
Smith 10 3月 1997
Doran 15 12月 1997
Sewall 3 11月 1998
Vishney 11 11月 1997
Greene 19 3月 1999
Marvins 24 1月 2000
Lee 23 2月 2000
Ande 24 3月 2000
Banda 21 4月 2000
Ozer 11 3月 1997
Bloom 23 3月 1998
Fox 24 1月 1998
Smith 23 2月 1999
Bates 24 3月 1999
Kumar 21 4月 2000
Abel 11 5月 1996
Hutton 19 3月 1997
Taylor 24 3月 1998
Livingston 23 4月 1998
Grant 24 5月 1999
Johnson 4 1月 2000
Taylor 24 1月 1998
Fleaur 23 2月 1998
Sullivan 21 6月 1999
Geoni 3 2月 2000
Sarchand 27 1月 1996
Bull 20 2月 1997
Dellinger 24 6月 1998
Cabrio 7 2月 1999
Chung 14 6月 1997
Dilly 13 8月 1997
Gates 11 7月 1998
Perkins 19 12月 1999
Bell 4 2月 1996
Everett 3 3月 1997
McCain 1 7月 1998
Jones 17 3月 1999
Walsh 24 4月 1998
Feeney 23 5月 1998
OConnell 21 6月 1999
Grant 13 1月 2000
Whalen 17 9月 1987
Hartstein 17 2月 1996
Fay 17 8月 1997
Mavris 7 6月 1994
Baer 7 6月 1994
Higgins 7 6月 1994
Gietz 7 6月 1994
已选择107行。
TO_XXX函数
TO_CHAR
我们可以用TO_CHAR函数，将数值以特定的格式转化为字符串
SELECT TO_CHAR(salary, '$99,999.00') SALARY
FROM employees
WHERE last_name = 'Ernst';
这条语句是将salary按照美元，保留两位小数，保留,输出
SALARY
----------------------
$6,000.00
TO_DATE
我们可以用TO_DATE函数生成一个日期
SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY')
FROM employees
WHERE hire_date < TO_DATE('01-Jan-90', 'DD-Mon-RR');
直接输入可能会出现“无效的月份”的提示，这是因为01-Jan-90的格式属于英文的日期，不属于中文的格式
我们可以将回话语言改为英文，命令如下
ALTER SESSION SET nls_date_language='american';
输出如下
LAST_NAME TO_CHAR(HIRE_DATE,'DD-MON-YYYY')
-------------------------------------------------- ----------------------------------------
King 17-Jun-1987
Kochhar 21-Sep-1989
Whalen 17-Sep-1987
通用函数
和大多数编程语言一样，SQL支持嵌套函数
SELECT last_name,
NVL(TO_CHAR(manager_id), 'No Manager')
FROM employees
WHERE manager_id IS NULL;
NVL(x,y)的含义是，如果x为空，则赋值为y，否则为x
LAST_NAME NVL(TO_CHAR(MANAGER_ID),'NOMANAGER')
-------------------------------------------------- --------------------------------------------------------------------------------
King No Manager
NVL的另一个例子
SELECT last_name, salary, NVL(commission_pct, 0),
(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM employees;
输出如下
LAST_NAME SALARY NVL(COMMISSION_PCT,0) AN_SAL
-------------------------------------------------- ---------- --------------------- ----------
King 24000 0 288000
Kochhar 17000 0 204000
De Haan 17000 0 204000
Hunold 9000 0 108000
Ernst 6000 0 72000
Austin 4800 0 57600
Pataballa 4800 0 57600
Lorentz 4200 0 50400
Greenberg 12000 0 144000
Faviet 9000 0 108000
Chen 8200 0 98400
Sciarra 7700 0 92400
Urman 7800 0 93600
Popp 6900 0 82800
Raphaely 11000 0 132000
Khoo 3100 0 37200
Baida 2900 0 34800
Tobias 2800 0 33600
Himuro 2600 0 31200
Colmenares 2500 0 30000
Weiss 8000 0 96000
Fripp 8200 0 98400
Kaufling 7900 0 94800
Vollman 6500 0 78000
Mourgos 5800 0 69600
Nayer 3200 0 38400
Mikkilineni 2700 0 32400
Landry 2400 0 28800
Markle 2200 0 26400
Bissot 3300 0 39600
Atkinson 2800 0 33600
Marlow 2500 0 30000
Olson 2100 0 25200
Mallin 3300 0 39600
Rogers 2900 0 34800
Gee 2400 0 28800
Philtanker 2200 0 26400
Ladwig 3600 0 43200
Stiles 3200 0 38400
Seo 2700 0 32400
Patel 2500 0 30000
Rajs 3500 0 42000
Davies 3100 0 37200
Matos 2600 0 31200
Vargas 2500 0 30000
Russell 14000 .4 235200
Partners 13500 .3 210600
Errazuriz 12000 .3 187200
Cambrault 11000 .3 171600
Zlotkey 10500 .2 151200
Tucker 10000 .3 156000
Bernstein 9500 .25 142500
Hall 9000 .25 135000
Olsen 8000 .2 115200
Cambrault 7500 .2 108000
Tuvault 7000 .15 96600
King 10000 .35 162000
Sully 9500 .35 153900
McEwen 9000 .35 145800
Smith 8000 .3 124800
Doran 7500 .3 117000
Sewall 7000 .25 105000
Vishney 10500 .25 157500
Greene 9500 .15 131100
Marvins 7200 .1 95040
Lee 6800 .1 89760
Ande 6400 .1 84480
Banda 6200 .1 81840
Ozer 11500 .25 172500
Bloom 10000 .2 144000
Fox 9600 .2 138240
Smith 7400 .15 102120
Bates 7300 .15 100740
Kumar 6100 .1 80520
Abel 11000 .3 171600
Hutton 8800 .25 132000
Taylor 8600 .2 123840
Livingston 8400 .2 120960
Grant 7000 .15 96600
Johnson 6200 .1 81840
Taylor 3200 0 38400
Fleaur 3100 0 37200
Sullivan 2500 0 30000
Geoni 2800 0 33600
Sarchand 4200 0 50400
Bull 4100 0 49200
Dellinger 3400 0 40800
Cabrio 3000 0 36000
Chung 3800 0 45600
Dilly 3600 0 43200
Gates 2900 0 34800
Perkins 2500 0 30000
Bell 4000 0 48000
Everett 3900 0 46800
McCain 3200 0 38400
Jones 2800 0 33600
Walsh 3100 0 37200
Feeney 3000 0 36000
OConnell 2600 0 31200
Grant 2600 0 31200
Whalen 4400 0 52800
Hartstein 13000 0 156000
Fay 6000 0 72000
Mavris 6500 0 78000
Baer 10000 0 120000
Higgins 12000 0 144000
Gietz 8300 0 99600
已选择107行。
NVL2
NVL2(x,y,z)的含义是，如果x为NULL，则输出z，否则输出y
SELECT last_name, salary, commission_pct,
NVL2(commission_pct,
'SAL+COMM', 'SAL') income
FROM employees WHERE department_id IN (50, 80);
输出如下
LAST_NAME SALARY COMMISSION_PCT INCOME
-------------------------------------------------- ---------- -------------- ----------------
Weiss 8000 SAL
Fripp 8200 SAL
Kaufling 7900 SAL
Vollman 6500 SAL
Mourgos 5800 SAL
Nayer 3200 SAL
Mikkilineni 2700 SAL
Landry 2400 SAL
Markle 2200 SAL
Bissot 3300 SAL
Atkinson 2800 SAL
Marlow 2500 SAL
Olson 2100 SAL
Mallin 3300 SAL
Rogers 2900 SAL
Gee 2400 SAL
Philtanker 2200 SAL
Ladwig 3600 SAL
Stiles 3200 SAL
Seo 2700 SAL
Patel 2500 SAL
Rajs 3500 SAL
Davies 3100 SAL
Matos 2600 SAL
Vargas 2500 SAL
Russell 14000 .4 SAL+COMM
Partners 13500 .3 SAL+COMM
Errazuriz 12000 .3 SAL+COMM
Cambrault 11000 .3 SAL+COMM
Zlotkey 10500 .2 SAL+COMM
Tucker 10000 .3 SAL+COMM
Bernstein 9500 .25 SAL+COMM
Hall 9000 .25 SAL+COMM
Olsen 8000 .2 SAL+COMM
Cambrault 7500 .2 SAL+COMM
Tuvault 7000 .15 SAL+COMM
King 10000 .35 SAL+COMM
Sully 9500 .35 SAL+COMM
McEwen 9000 .35 SAL+COMM
Smith 8000 .3 SAL+COMM
Doran 7500 .3 SAL+COMM
Sewall 7000 .25 SAL+COMM
Vishney 10500 .25 SAL+COMM
Greene 9500 .15 SAL+COMM
Marvins 7200 .1 SAL+COMM
Lee 6800 .1 SAL+COMM
Ande 6400 .1 SAL+COMM
Banda 6200 .1 SAL+COMM
Ozer 11500 .25 SAL+COMM
Bloom 10000 .2 SAL+COMM
Fox 9600 .2 SAL+COMM
Smith 7400 .15 SAL+COMM
Bates 7300 .15 SAL+COMM
Kumar 6100 .1 SAL+COMM
Abel 11000 .3 SAL+COMM
Hutton 8800 .25 SAL+COMM
Taylor 8600 .2 SAL+COMM
Livingston 8400 .2 SAL+COMM
Johnson 6200 .1 SAL+COMM
Taylor 3200 SAL
Fleaur 3100 SAL
Sullivan 2500 SAL
Geoni 2800 SAL
Sarchand 4200 SAL
Bull 4100 SAL
Dellinger 3400 SAL
Cabrio 3000 SAL
Chung 3800 SAL
Dilly 3600 SAL
Gates 2900 SAL
Perkins 2500 SAL
Bell 4000 SAL
Everett 3900 SAL
McCain 3200 SAL
Jones 2800 SAL
Walsh 3100 SAL
Feeney 3000 SAL
OConnell 2600 SAL
Grant 2600 SAL
已选择79行。
NULLIF
格式为NULLIF(x,y)
如果两个表达式不相等，NULLIF 返回第一个 expression1 的值。
如果两个表达式相等，NULLIF 返回NULL。
SELECT first_name, LENGTH(first_name) "expr1",
last_name, LENGTH(last_name) "expr2",
NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM employees;
输出如下：
FIRST_NAME expr1 LAST_NAME expr2 RESULT
---------------------------------------- ---------- -------------------------------------------------- ---------- ----------
Ellen 5 Abel 4 5
Sundar 6 Ande 4 6
Mozhe 5 Atkinson 8 5
David 5 Austin 6 5
Hermann 7 Baer 4 7
Shelli 6 Baida 5 6
Amit 4 Banda 5 4
Elizabeth 9 Bates 5 9
Sarah 5 Bell 4 5
David 5 Bernstein 9 5
Laura 5 Bissot 6 5
Harrison 8 Bloom 5 8
Alexis 6 Bull 4 6
Anthony 7 Cabrio 6 7
Gerald 6 Cambrault 9 6
Nanette 7 Cambrault 9 7
John 4 Chen 4
Kelly 5 Chung 5
Karen 5 Colmenares 10 5
Curtis 6 Davies 6
Lex 3 De Haan 7 3
Julia 5 Dellinger 9 5
Jennifer 8 Dilly 5 8
Louise 6 Doran 5 6
Bruce 5 Ernst 5
Alberto 7 Errazuriz 9 7
Britney 7 Everett 7
Daniel 6 Faviet 6
Pat 3 Fay 3
Kevin 5 Feeney 6 5
Jean 4 Fleaur 6 4
Tayler 6 Fox 3 6
Adam 4 Fripp 5 4
Timothy 7 Gates 5 7
Ki 2 Gee 3 2
Girard 6 Geoni 5 6
William 7 Gietz 5 7
Douglas 7 Grant 5 7
Kimberely 9 Grant 5 9
Nancy 5 Greenberg 9 5
Danielle 8 Greene 6 8
Peter 5 Hall 4 5
Michael 7 Hartstein 9 7
Shelley 7 Higgins 7
Guy 3 Himuro 6 3
Alexander 9 Hunold 6 9
Alyssa 6 Hutton 6
Charles 7 Johnson 7
Vance 5 Jones 5
Payam 5 Kaufling 8 5
Alexander 9 Khoo 4 9
Janette 7 King 4 7
Steven 6 King 4 6
Neena 5 Kochhar 7 5
Sundita 7 Kumar 5 7
Renske 6 Ladwig 6
James 5 Landry 6 5
David 5 Lee 3 5
Jack 4 Livingston 10 4
Diana 5 Lorentz 7 5
Jason 5 Mallin 6 5
Steven 6 Markle 6
James 5 Marlow 6 5
Mattea 6 Marvins 7 6
Randall 7 Matos 5 7
Susan 5 Mavris 6 5
Samuel 6 McCain 6
Allan 5 McEwen 6 5
Irene 5 Mikkilineni 11 5
Kevin 5 Mourgos 7 5
Julia 5 Nayer 5
Donald 6 OConnell 8 6
Christopher 11 Olsen 5 11
TJ 2 Olson 5 2
Lisa 4 Ozer 4
Karen 5 Partners 8 5
Valli 5 Pataballa 9 5
Joshua 6 Patel 5 6
Randall 7 Perkins 7
Hazel 5 Philtanker 10 5
Luis 4 Popp 4
Trenna 6 Rajs 4 6
Den 3 Raphaely 8 3
Michael 7 Rogers 6 7
John 4 Russell 7 4
Nandita 7 Sarchand 8 7
Ismael 6 Sciarra 7 6
John 4 Seo 3 4
Sarath 6 Sewall 6
Lindsey 7 Smith 5 7
William 7 Smith 5 7
Stephen 7 Stiles 6 7
Martha 6 Sullivan 8 6
Patrick 7 Sully 5 7
Jonathon 8 Taylor 6 8
Winston 7 Taylor 6 7
Sigal 5 Tobias 6 5
Peter 5 Tucker 6 5
Oliver 6 Tuvault 7 6
Jose Manuel 11 Urman 5 11
Peter 5 Vargas 6 5
Clara 5 Vishney 7 5
Shanta 6 Vollman 7 6
Alana 5 Walsh 5
Matthew 7 Weiss 5 7
Jennifer 8 Whalen 6 8
Eleni 5 Zlotkey 7 5
已选择107行。
COALESCE
COALESCE函数可以理解为支持多个参数的NVL函数
SELECT last_name,
COALESCE(commission_pct, salary, 10) comm
FROM employees
ORDER BY commission_pct;
输出如下
LAST_NAME COMM
-------------------------------------------------- ----------
Lee .1
Johnson .1
Marvins .1
Banda .1
Kumar .1
Ande .1
Greene .15
Grant .15
Tuvault .15
Bates .15
Smith .15
Taylor .2
Bloom .2
Fox .2
Cambrault .2
Livingston .2
Zlotkey .2
Olsen .2
Sewall .25
Hall .25
Bernstein .25
Vishney .25
Hutton .25
Ozer .25
Abel .3
Smith .3
Partners .3
Errazuriz .3
Tucker .3
Cambrault .3
Doran .3
King .35
Sully .35
McEwen .35
Russell .4
King 24000
Kochhar 17000
De Haan 17000
Hunold 9000
Ernst 6000
Austin 4800
Pataballa 4800
Lorentz 4200
Greenberg 12000
Faviet 9000
Chen 8200
Sciarra 7700
Urman 7800
Popp 6900
Raphaely 11000
Khoo 3100
Baida 2900
Tobias 2800
Himuro 2600
Colmenares 2500
Weiss 8000
Fripp 8200
Kaufling 7900
Vollman 6500
Mourgos 5800
Nayer 3200
Mikkilineni 2700
Landry 2400
Markle 2200
Bissot 3300
Atkinson 2800
Marlow 2500
Olson 2100
Mallin 3300
Rogers 2900
Gee 2400
Philtanker 2200
Ladwig 3600
Stiles 3200
Seo 2700
Patel 2500
Rajs 3500
Davies 3100
Matos 2600
Vargas 2500
Taylor 3200
Fleaur 3100
Sullivan 2500
Geoni 2800
Sarchand 4200
Bull 4100
Dellinger 3400
Cabrio 3000
Chung 3800
Dilly 3600
Gates 2900
Perkins 2500
Bell 4000
Everett 3900
McCain 3200
Jones 2800
Walsh 3100
Feeney 3000
OConnell 2600
Grant 2600
Whalen 4400
Hartstein 13000
Fay 6000
Mavris 6500
Baer 10000
Higgins 12000
Gietz 8300
已选择107行。
条件表达式
CASE
和一般程序中的CASE语句类似
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees;
输出如下
LAST_NAME JOB_ID SALARY REVISED_SALARY
-------------------------------------------------- -------------------- ---------- --------------
King AD_PRES 24000 24000
Kochhar AD_VP 17000 17000
De Haan AD_VP 17000 17000
Hunold IT_PROG 9000 9900
Ernst IT_PROG 6000 6600
Austin IT_PROG 4800 5280
Pataballa IT_PROG 4800 5280
Lorentz IT_PROG 4200 4620
Greenberg FI_MGR 12000 12000
Faviet FI_ACCOUNT 9000 9000
Chen FI_ACCOUNT 8200 8200
Sciarra FI_ACCOUNT 7700 7700
Urman FI_ACCOUNT 7800 7800
Popp FI_ACCOUNT 6900 6900
Raphaely PU_MAN 11000 11000
Khoo PU_CLERK 3100 3100
Baida PU_CLERK 2900 2900
Tobias PU_CLERK 2800 2800
Himuro PU_CLERK 2600 2600
Colmenares PU_CLERK 2500 2500
Weiss ST_MAN 8000 8000
Fripp ST_MAN 8200 8200
Kaufling ST_MAN 7900 7900
Vollman ST_MAN 6500 6500
Mourgos ST_MAN 5800 5800
Nayer ST_CLERK 3200 3680
Mikkilineni ST_CLERK 2700 3105
Landry ST_CLERK 2400 2760
Markle ST_CLERK 2200 2530
Bissot ST_CLERK 3300 3795
Atkinson ST_CLERK 2800 3220
Marlow ST_CLERK 2500 2875
Olson ST_CLERK 2100 2415
Mallin ST_CLERK 3300 3795
Rogers ST_CLERK 2900 3335
Gee ST_CLERK 2400 2760
Philtanker ST_CLERK 2200 2530
Ladwig ST_CLERK 3600 4140
Stiles ST_CLERK 3200 3680
Seo ST_CLERK 2700 3105
Patel ST_CLERK 2500 2875
Rajs ST_CLERK 3500 4025
Davies ST_CLERK 3100 3565
Matos ST_CLERK 2600 2990
Vargas ST_CLERK 2500 2875
Russell SA_MAN 14000 14000
Partners SA_MAN 13500 13500
Errazuriz SA_MAN 12000 12000
Cambrault SA_MAN 11000 11000
Zlotkey SA_MAN 10500 10500
Tucker SA_REP 10000 12000
Bernstein SA_REP 9500 11400
Hall SA_REP 9000 10800
Olsen SA_REP 8000 9600
Cambrault SA_REP 7500 9000
Tuvault SA_REP 7000 8400
King SA_REP 10000 12000
Sully SA_REP 9500 11400
McEwen SA_REP 9000 10800
Smith SA_REP 8000 9600
Doran SA_REP 7500 9000
Sewall SA_REP 7000 8400
Vishney SA_REP 10500 12600
Greene SA_REP 9500 11400
Marvins SA_REP 7200 8640
Lee SA_REP 6800 8160
Ande SA_REP 6400 7680
Banda SA_REP 6200 7440
Ozer SA_REP 11500 13800
Bloom SA_REP 10000 12000
Fox SA_REP 9600 11520
Smith SA_REP 7400 8880
Bates SA_REP 7300 8760
Kumar SA_REP 6100 7320
Abel SA_REP 11000 13200
Hutton SA_REP 8800 10560
Taylor SA_REP 8600 10320
Livingston SA_REP 8400 10080
Grant SA_REP 7000 8400
Johnson SA_REP 6200 7440
Taylor SH_CLERK 3200 3200
Fleaur SH_CLERK 3100 3100
Sullivan SH_CLERK 2500 2500
Geoni SH_CLERK 2800 2800
Sarchand SH_CLERK 4200 4200
Bull SH_CLERK 4100 4100
Dellinger SH_CLERK 3400 3400
Cabrio SH_CLERK 3000 3000
Chung SH_CLERK 3800 3800
Dilly SH_CLERK 3600 3600
Gates SH_CLERK 2900 2900
Perkins SH_CLERK 2500 2500
Bell SH_CLERK 4000 4000
Everett SH_CLERK 3900 3900
McCain SH_CLERK 3200 3200
Jones SH_CLERK 2800 2800
Walsh SH_CLERK 3100 3100
Feeney SH_CLERK 3000 3000
OConnell SH_CLERK 2600 2600
Grant SH_CLERK 2600 2600
Whalen AD_ASST 4400 4400
Hartstein MK_MAN 13000 13000
Fay MK_REP 6000 6000
Mavris HR_REP 6500 6500
Baer PR_REP 10000 10000
Higgins AC_MGR 12000 12000
Gietz AC_ACCOUNT 8300 8300
已选择107行。
DECODE
和CASE有一定的相似性
SELECT last_name, job_id, salary,
DECODE(job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
salary)
REVISED_SALARY
FROM employees;
输出如下
LAST_NAME JOB_ID SALARY REVISED_SALARY
-------------------------------------------------- -------------------- ---------- --------------
King AD_PRES 24000 24000
Kochhar AD_VP 17000 17000
De Haan AD_VP 17000 17000
Hunold IT_PROG 9000 9900
Ernst IT_PROG 6000 6600
Austin IT_PROG 4800 5280
Pataballa IT_PROG 4800 5280
Lorentz IT_PROG 4200 4620
Greenberg FI_MGR 12000 12000
Faviet FI_ACCOUNT 9000 9000
Chen FI_ACCOUNT 8200 8200
Sciarra FI_ACCOUNT 7700 7700
Urman FI_ACCOUNT 7800 7800
Popp FI_ACCOUNT 6900 6900
Raphaely PU_MAN 11000 11000
Khoo PU_CLERK 3100 3100
Baida PU_CLERK 2900 2900
Tobias PU_CLERK 2800 2800
Himuro PU_CLERK 2600 2600
Colmenares PU_CLERK 2500 2500
Weiss ST_MAN 8000 8000
Fripp ST_MAN 8200 8200
Kaufling ST_MAN 7900 7900
Vollman ST_MAN 6500 6500
Mourgos ST_MAN 5800 5800
Nayer ST_CLERK 3200 3680
Mikkilineni ST_CLERK 2700 3105
Landry ST_CLERK 2400 2760
Markle ST_CLERK 2200 2530
Bissot ST_CLERK 3300 3795
Atkinson ST_CLERK 2800 3220
Marlow ST_CLERK 2500 2875
Olson ST_CLERK 2100 2415
Mallin ST_CLERK 3300 3795
Rogers ST_CLERK 2900 3335
Gee ST_CLERK 2400 2760
Philtanker ST_CLERK 2200 2530
Ladwig ST_CLERK 3600 4140
Stiles ST_CLERK 3200 3680
Seo ST_CLERK 2700 3105
Patel ST_CLERK 2500 2875
Rajs ST_CLERK 3500 4025
Davies ST_CLERK 3100 3565
Matos ST_CLERK 2600 2990
Vargas ST_CLERK 2500 2875
Russell SA_MAN 14000 14000
Partners SA_MAN 13500 13500
Errazuriz SA_MAN 12000 12000
Cambrault SA_MAN 11000 11000
Zlotkey SA_MAN 10500 10500
Tucker SA_REP 10000 12000
Bernstein SA_REP 9500 11400
Hall SA_REP 9000 10800
Olsen SA_REP 8000 9600
Cambrault SA_REP 7500 9000
Tuvault SA_REP 7000 8400
King SA_REP 10000 12000
Sully SA_REP 9500 11400
McEwen SA_REP 9000 10800
Smith SA_REP 8000 9600
Doran SA_REP 7500 9000
Sewall SA_REP 7000 8400
Vishney SA_REP 10500 12600
Greene SA_REP 9500 11400
Marvins SA_REP 7200 8640
Lee SA_REP 6800 8160
Ande SA_REP 6400 7680
Banda SA_REP 6200 7440
Ozer SA_REP 11500 13800
Bloom SA_REP 10000 12000
Fox SA_REP 9600 11520
Smith SA_REP 7400 8880
Bates SA_REP 7300 8760
Kumar SA_REP 6100 7320
Abel SA_REP 11000 13200
Hutton SA_REP 8800 10560
Taylor SA_REP 8600 10320
Livingston SA_REP 8400 10080
Grant SA_REP 7000 8400
Johnson SA_REP 6200 7440
Taylor SH_CLERK 3200 3200
Fleaur SH_CLERK 3100 3100
Sullivan SH_CLERK 2500 2500
Geoni SH_CLERK 2800 2800
Sarchand SH_CLERK 4200 4200
Bull SH_CLERK 4100 4100
Dellinger SH_CLERK 3400 3400
Cabrio SH_CLERK 3000 3000
Chung SH_CLERK 3800 3800
Dilly SH_CLERK 3600 3600
Gates SH_CLERK 2900 2900
Perkins SH_CLERK 2500 2500
Bell SH_CLERK 4000 4000
Everett SH_CLERK 3900 3900
McCain SH_CLERK 3200 3200
Jones SH_CLERK 2800 2800
Walsh SH_CLERK 3100 3100
Feeney SH_CLERK 3000 3000
OConnell SH_CLERK 2600 2600
Grant SH_CLERK 2600 2600
Whalen AD_ASST 4400 4400
Hartstein MK_MAN 13000 13000
Fay MK_REP 6000 6000
Mavris HR_REP 6500 6500
Baer PR_REP 10000 10000
Higgins AC_MGR 12000 12000
Gietz AC_ACCOUNT 8300 8300
已选择107行。
