select last_name,
job_id,
salary
from employees
where Salary < 7000
Results:
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------------------
King AD_PRES 24000
Kochhar AD_VP 17000
De Haan AD_VP 17000
Hunold IT_PROG 9000
Ernst IT_PROG 6000
Austin IT_PROG 4800
Pataballa IT_PROG 4800
Lorentz IT_PROG 4200
Greenberg FI_MGR 12000
Faviet FI_ACCOUNT 9000
Chen FI_ACCOUNT 8200
Sciarra FI_ACCOUNT 7700
Urman FI_ACCOUNT 7800
Popp FI_ACCOUNT 6900
Raphaely PU_MAN 11000
Khoo PU_CLERK 3100
Baida PU_CLERK 2900
Tobias PU_CLERK 2800
Himuro PU_CLERK 2600
Colmenares PU_CLERK 2500
Weiss ST_MAN 8000
Fripp ST_MAN 8200
Kaufling ST_MAN 7900
Vollman ST_MAN 6500
Mourgos ST_MAN 5800
Nayer ST_CLERK 3200
Mikkilineni ST_CLERK 2700
Landry ST_CLERK 2400
Markle ST_CLERK 2200
Bissot ST_CLERK 3300
Atkinson ST_CLERK 2800
Marlow ST_CLERK 2500
Olson ST_CLERK 2100
Mallin ST_CLERK 3300
Rogers ST_CLERK 2900
Gee ST_CLERK 2400
Philtanker ST_CLERK 2200
Ladwig ST_CLERK 3600
Stiles ST_CLERK 3200
Seo ST_CLERK 2700
Patel ST_CLERK 2500
Rajs ST_CLERK 3500
Davies ST_CLERK 3100
Matos ST_CLERK 2600
Vargas ST_CLERK 2500
Russell SA_MAN 14000
Partners SA_MAN 13500
Errazuriz SA_MAN 12000
Cambrault SA_MAN 11000
Zlotkey SA_MAN 10500
Tucker SA_REP 10000
Bernstein SA_REP 9500
Hall SA_REP 9000
Olsen SA_REP 8000
Cambrault SA_REP 7500
Tuvault SA_REP 7000
King SA_REP 10000
Sully SA_REP 9500
McEwen SA_REP 9000
Smith SA_REP 8000
Doran SA_REP 7500
Sewall SA_REP 7000
Vishney SA_REP 10500
Greene SA_REP 9500
Marvins SA_REP 7200
Lee SA_REP 6800
Ande SA_REP 6400
Banda SA_REP 6200
Ozer SA_REP 11500
Bloom SA_REP 10000
Fox SA_REP 9600
Smith SA_REP 7400
Bates SA_REP 7300
Kumar SA_REP 6100
Abel SA_REP 11000
Hutton SA_REP 8800
Taylor SA_REP 8600
Livingston SA_REP 8400
Grant SA_REP 7000
Johnson SA_REP 6200
Taylor SH_CLERK 3200
Fleaur SH_CLERK 3100
Sullivan SH_CLERK 2500
Geoni SH_CLERK 2800
Sarchand SH_CLERK 4200
Bull SH_CLERK 4100
Dellinger SH_CLERK 3400
Cabrio SH_CLERK 3000
Chung SH_CLERK 3800
Dilly SH_CLERK 3600
Gates SH_CLERK 2900
Perkins SH_CLERK 2500
Bell SH_CLERK 4000
Everett SH_CLERK 3900
McCain SH_CLERK 3200
Jones SH_CLERK 2800
Walsh SH_CLERK 3100
Feeney SH_CLERK 3000
OConnell SH_CLERK 2600
Grant SH_CLERK 2600
Whalen AD_ASST 4400
Hartstein MK_MAN 13000
Fay MK_REP 6000
Mavris HR_REP 6500
Baer PR_REP 10000
Higgins AC_MGR 12000
Gietz AC_ACCOUNT 8300
107 rows selected
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------------------
Colmenares PU_CLERK 2500
Marlow ST_CLERK 2500
Patel ST_CLERK 2500
Vargas ST_CLERK 2500
Sullivan SH_CLERK 2500
Cabrio SH_CLERK 3000
Perkins SH_CLERK 2500
Feeney SH_CLERK 3000
8 rows selected
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------------------
Cabrio SH_CLERK 3000
Feeney SH_CLERK 3000
2 rows selected
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------------------
King AD_PRES 24000
Kochhar AD_VP 17000
De Haan AD_VP 17000
Hunold IT_PROG 9000
Ernst IT_PROG 6000
Austin IT_PROG 4800
Pataballa IT_PROG 4800
Lorentz IT_PROG 4200
Greenberg FI_MGR 12000
Faviet FI_ACCOUNT 9000
Chen FI_ACCOUNT 8200
Sciarra FI_ACCOUNT 7700
Urman FI_ACCOUNT 7800
Popp FI_ACCOUNT 6900
Raphaely PU_MAN 11000
Khoo PU_CLERK 3100
Baida PU_CLERK 2900
Tobias PU_CLERK 2800
Himuro PU_CLERK 2600
Colmenares PU_CLERK 2500
Weiss ST_MAN 8000
Fripp ST_MAN 8200
Kaufling ST_MAN 7900
Vollman ST_MAN 6500
Mourgos ST_MAN 5800
Nayer ST_CLERK 3200
Mikkilineni ST_CLERK 2700
Landry ST_CLERK 2400
Markle ST_CLERK 2200
Bissot ST_CLERK 3300
Atkinson ST_CLERK 2800
Marlow ST_CLERK 2500
Olson ST_CLERK 2100
Mallin ST_CLERK 3300
Rogers ST_CLERK 2900
Gee ST_CLERK 2400
Philtanker ST_CLERK 2200
Ladwig ST_CLERK 3600
Stiles ST_CLERK 3200
Seo ST_CLERK 2700
Patel ST_CLERK 2500
Rajs ST_CLERK 3500
Davies ST_CLERK 3100
Matos ST_CLERK 2600
Vargas ST_CLERK 2500
Russell SA_MAN 14000
Partners SA_MAN 13500
Errazuriz SA_MAN 12000
Cambrault SA_MAN 11000
Zlotkey SA_MAN 10500
Tucker SA_REP 10000
Bernstein SA_REP 9500
Hall SA_REP 9000
Olsen SA_REP 8000
Cambrault SA_REP 7500
Tuvault SA_REP 7000
King SA_REP 10000
Sully SA_REP 9500
McEwen SA_REP 9000
Smith SA_REP 8000
Doran SA_REP 7500
Sewall SA_REP 7000
Vishney SA_REP 10500
Greene SA_REP 9500
Marvins SA_REP 7200
Lee SA_REP 6800
Ande SA_REP 6400
Banda SA_REP 6200
Ozer SA_REP 11500
Bloom SA_REP 10000
Fox SA_REP 9600
Smith SA_REP 7400
Bates SA_REP 7300
Kumar SA_REP 6100
Abel SA_REP 11000
Hutton SA_REP 8800
Taylor SA_REP 8600
Livingston SA_REP 8400
Grant SA_REP 7000
Johnson SA_REP 6200
Taylor SH_CLERK 3200
Fleaur SH_CLERK 3100
Sullivan SH_CLERK 2500
Geoni SH_CLERK 2800
Sarchand SH_CLERK 4200
Bull SH_CLERK 4100
Dellinger SH_CLERK 3400
Cabrio SH_CLERK 3000
Chung SH_CLERK 3800
Dilly SH_CLERK 3600
Gates SH_CLERK 2900
Perkins SH_CLERK 2500
Bell SH_CLERK 4000
Everett SH_CLERK 3900
McCain SH_CLERK 3200
Jones SH_CLERK 2800
Walsh SH_CLERK 3100
Feeney SH_CLERK 3000
OConnell SH_CLERK 2600
Grant SH_CLERK 2600
Whalen AD_ASST 4400
Hartstein MK_MAN 13000
Fay MK_REP 6000
Mavris HR_REP 6500
Baer PR_REP 10000
Higgins AC_MGR 12000
Gietz AC_ACCOUNT 8300
107 rows selected
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------------------
King AD_PRES 24000
Kochhar AD_VP 17000
De Haan AD_VP 17000
Hunold IT_PROG 9000
Ernst IT_PROG 6000
Austin IT_PROG 4800
Pataballa IT_PROG 4800
Lorentz IT_PROG 4200
Greenberg FI_MGR 12000
Faviet FI_ACCOUNT 9000
Chen FI_ACCOUNT 8200
Sciarra FI_ACCOUNT 7700
Urman FI_ACCOUNT 7800
Popp FI_ACCOUNT 6900
Raphaely PU_MAN 11000
Khoo PU_CLERK 3100
Baida PU_CLERK 2900
Tobias PU_CLERK 2800
Himuro PU_CLERK 2600
Weiss ST_MAN 8000
Fripp ST_MAN 8200
Kaufling ST_MAN 7900
Vollman ST_MAN 6500
Mourgos ST_MAN 5800
Nayer ST_CLERK 3200
Mikkilineni ST_CLERK 2700
Bissot ST_CLERK 3300
Atkinson ST_CLERK 2800
Mallin ST_CLERK 3300
Rogers ST_CLERK 2900
Ladwig ST_CLERK 3600
Stiles ST_CLERK 3200
Seo ST_CLERK 2700
Rajs ST_CLERK 3500
Davies ST_CLERK 3100
Matos ST_CLERK 2600
Russell SA_MAN 14000
Partners SA_MAN 13500
Errazuriz SA_MAN 12000
Cambrault SA_MAN 11000
Zlotkey SA_MAN 10500
Tucker SA_REP 10000
Bernstein SA_REP 9500
Hall SA_REP 9000
Olsen SA_REP 8000
Cambrault SA_REP 7500
Tuvault SA_REP 7000
King SA_REP 10000
Sully SA_REP 9500
McEwen SA_REP 9000
Smith SA_REP 8000
Doran SA_REP 7500
Sewall SA_REP 7000
Vishney SA_REP 10500
Greene SA_REP 9500
Marvins SA_REP 7200
Lee SA_REP 6800
Ande SA_REP 6400
Banda SA_REP 6200
Ozer SA_REP 11500
Bloom SA_REP 10000
Fox SA_REP 9600
Smith SA_REP 7400
Bates SA_REP 7300
Kumar SA_REP 6100
Abel SA_REP 11000
Hutton SA_REP 8800
Taylor SA_REP 8600
Livingston SA_REP 8400
Grant SA_REP 7000
Johnson SA_REP 6200
Taylor SH_CLERK 3200
Fleaur SH_CLERK 3100
Geoni SH_CLERK 2800
Sarchand SH_CLERK 4200
Bull SH_CLERK 4100
Dellinger SH_CLERK 3400
Cabrio SH_CLERK 3000
Chung SH_CLERK 3800
Dilly SH_CLERK 3600
Gates SH_CLERK 2900
Bell SH_CLERK 4000
Everett SH_CLERK 3900
McCain SH_CLERK 3200
Jones SH_CLERK 2800
Walsh SH_CLERK 3100
Feeney SH_CLERK 3000
OConnell SH_CLERK 2600
Grant SH_CLERK 2600
Whalen AD_ASST 4400
Hartstein MK_MAN 13000
Fay MK_REP 6000
Mavris HR_REP 6500
Baer PR_REP 10000
Higgins AC_MGR 12000
Gietz AC_ACCOUNT 8300
96 rows selected
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------------------
Austin IT_PROG 4800
Pataballa IT_PROG 4800
Lorentz IT_PROG 4200
Khoo PU_CLERK 3100
Baida PU_CLERK 2900
Tobias PU_CLERK 2800
Himuro PU_CLERK 2600
Colmenares PU_CLERK 2500
Nayer ST_CLERK 3200
Mikkilineni ST_CLERK 2700
Landry ST_CLERK 2400
Markle ST_CLERK 2200
Bissot ST_CLERK 3300
Atkinson ST_CLERK 2800
Marlow ST_CLERK 2500
Olson ST_CLERK 2100
Mallin ST_CLERK 3300
Rogers ST_CLERK 2900
Gee ST_CLERK 2400
Philtanker ST_CLERK 2200
Ladwig ST_CLERK 3600
Stiles ST_CLERK 3200
Seo ST_CLERK 2700
Patel ST_CLERK 2500
Rajs ST_CLERK 3500
Davies ST_CLERK 3100
Matos ST_CLERK 2600
Vargas ST_CLERK 2500
Taylor SH_CLERK 3200
Fleaur SH_CLERK 3100
Sullivan SH_CLERK 2500
Geoni SH_CLERK 2800
Sarchand SH_CLERK 4200
Bull SH_CLERK 4100
Dellinger SH_CLERK 3400
Cabrio SH_CLERK 3000
Chung SH_CLERK 3800
Dilly SH_CLERK 3600
Gates SH_CLERK 2900
Perkins SH_CLERK 2500
Bell SH_CLERK 4000
Everett SH_CLERK 3900
McCain SH_CLERK 3200
Jones SH_CLERK 2800
Walsh SH_CLERK 3100
Feeney SH_CLERK 3000
OConnell SH_CLERK 2600
Grant SH_CLERK 2600
Whalen AD_ASST 4400
49 rows selected
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------------------
Ernst IT_PROG 6000
Austin IT_PROG 4800
Pataballa IT_PROG 4800
Lorentz IT_PROG 4200
Popp FI_ACCOUNT 6900
Khoo PU_CLERK 3100
Baida PU_CLERK 2900
Tobias PU_CLERK 2800
Himuro PU_CLERK 2600
Colmenares PU_CLERK 2500
Vollman ST_MAN 6500
Mourgos ST_MAN 5800
Nayer ST_CLERK 3200
Mikkilineni ST_CLERK 2700
Landry ST_CLERK 2400
Markle ST_CLERK 2200
Bissot ST_CLERK 3300
Atkinson ST_CLERK 2800
Marlow ST_CLERK 2500
Olson ST_CLERK 2100
Mallin ST_CLERK 3300
Rogers ST_CLERK 2900
Gee ST_CLERK 2400
Philtanker ST_CLERK 2200
Ladwig ST_CLERK 3600
Stiles ST_CLERK 3200
Seo ST_CLERK 2700
Patel ST_CLERK 2500
Rajs ST_CLERK 3500
Davies ST_CLERK 3100
Matos ST_CLERK 2600
Vargas ST_CLERK 2500
Lee SA_REP 6800
Ande SA_REP 6400
Banda SA_REP 6200
Kumar SA_REP 6100
Johnson SA_REP 6200
Taylor SH_CLERK 3200
Fleaur SH_CLERK 3100
Sullivan SH_CLERK 2500
Geoni SH_CLERK 2800
Sarchand SH_CLERK 4200
Bull SH_CLERK 4100
Dellinger SH_CLERK 3400
Cabrio SH_CLERK 3000
Chung SH_CLERK 3800
Dilly SH_CLERK 3600
Gates SH_CLERK 2900
Perkins SH_CLERK 2500
Bell SH_CLERK 4000
Everett SH_CLERK 3900
McCain SH_CLERK 3200
Jones SH_CLERK 2800
Walsh SH_CLERK 3100
Feeney SH_CLERK 3000
OConnell SH_CLERK 2600
Grant SH_CLERK 2600
Whalen AD_ASST 4400
Fay MK_REP 6000
Mavris HR_REP 6500
60 rows selected
===> SQL Queries: CASE expression
select last_name,
job_id,
salary,
case job_id when 'IT_PROG' then 1.10*salary
when 'ST_CLERK' then 1.15*salary
when 'ST_REP' then 1.20*salary
else salary end "Revised_Salary"
from employees
where Salary < 7000;
LAST_NAME JOB_ID SALARY Revised_Salary
------------------------- ---------- ---------------------- -------
Ernst IT_PROG 6000 6600
Austin IT_PROG 4800 5280
Pataballa IT_PROG 4800 5280
Lorentz IT_PROG 4200 4620
Popp FI_ACCOUNT 6900 6900
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
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
Lee SA_REP 6800 6800
Ande SA_REP 6400 6400
Banda SA_REP 6200 6200
Kumar SA_REP 6100 6100
Johnson SA_REP 6200 6200
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
Fay MK_REP 6000 6000
Mavris HR_REP 6500 6500
60 rows selected
===> SQL Queries DECODE [decode function has the functionality of an IF-THEN-ELSE statement.]
select last_name,
job_id,
salary,
decode (job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'ST_REP',1.20*salary)
Revised_salary
from employees
where Salary < 7000;
Results:
LAST_NAME JOB_ID SALARY REVISED_SALARY
------------------------- ---------- ---------------------- ----------------------
Ernst IT_PROG 6000 6600
Austin IT_PROG 4800 5280
Pataballa IT_PROG 4800 5280
Lorentz IT_PROG 4200 4620
Popp FI_ACCOUNT 6900
Khoo PU_CLERK 3100
Baida PU_CLERK 2900
Tobias PU_CLERK 2800
Himuro PU_CLERK 2600
Colmenares PU_CLERK 2500
Vollman ST_MAN 6500
Mourgos ST_MAN 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
Lee SA_REP 6800
Ande SA_REP 6400
Banda SA_REP 6200
Kumar SA_REP 6100
Johnson SA_REP 6200
Taylor SH_CLERK 3200
Fleaur SH_CLERK 3100
Sullivan SH_CLERK 2500
Geoni SH_CLERK 2800
Sarchand SH_CLERK 4200
Bull SH_CLERK 4100
Dellinger SH_CLERK 3400
Cabrio SH_CLERK 3000
Chung SH_CLERK 3800
Dilly SH_CLERK 3600
Gates SH_CLERK 2900
Perkins SH_CLERK 2500
Bell SH_CLERK 4000
Everett SH_CLERK 3900
McCain SH_CLERK 3200
Jones SH_CLERK 2800
Walsh SH_CLERK 3100
Feeney SH_CLERK 3000
OConnell SH_CLERK 2600
Grant SH_CLERK 2600
Whalen AD_ASST 4400
Fay MK_REP 6000
Mavris HR_REP 6500
60 rows selected
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment