===> SQL Queries: Use & to Prompt Value
insert into departments
(
department_id,
department_name,
location_id
)
values
(&department_id,'&department_name',&locationid)
===> SQL Queries: Copying rows from another table
insert into sales_reps(id_name,salary,commission_pct)
select employee_id,last_name,salary,commission_pct
from employees
where job_id like '%REP%'
Monday, August 16, 2010
Oracle 11g: Creating A Table By Using SubQuery
===> SQL Queries
create table dept80
as
select employee_id,
last_name,
salary*12 ANNSAL,
hire_date
from employees
where department_id = 80;
select * from dept80;
Results:
create table succeeded.
EMPLOYEE_ID LAST_NAME ANNSAL HIRE_DATE
---------------------- ------------------------- ---------------
145 Russell 168000 01-OCT-96
146 Partners 162000 05-JAN-97
147 Errazuriz 144000 10-MAR-97
148 Cambrault 132000 15-OCT-99
149 Zlotkey 126000 29-JAN-00
150 Tucker 120000 30-JAN-97
151 Bernstein 114000 24-MAR-97
152 Hall 108000 20-AUG-97
153 Olsen 96000 30-MAR-98
154 Cambrault 90000 09-DEC-98
155 Tuvault 84000 23-NOV-99
156 King 120000 30-JAN-96
157 Sully 114000 04-MAR-96
158 McEwen 108000 01-AUG-96
159 Smith 96000 10-MAR-97
160 Doran 90000 15-DEC-97
161 Sewall 84000 03-NOV-98
162 Vishney 126000 11-NOV-97
163 Greene 114000 19-MAR-99
164 Marvins 86400 24-JAN-00
165 Lee 81600 23-FEB-00
166 Ande 76800 24-MAR-00
167 Banda 74400 21-APR-00
168 Ozer 138000 11-MAR-97
169 Bloom 120000 23-MAR-98
170 Fox 115200 24-JAN-98
171 Smith 88800 23-FEB-99
172 Bates 87600 24-MAR-99
173 Kumar 73200 21-APR-00
174 Abel 132000 11-MAY-96
175 Hutton 105600 19-MAR-97
176 Taylor 103200 24-MAR-98
177 Livingston 100800 23-APR-98
179 Johnson 74400 04-JAN-00
34 rows selected
create table dept80
as
select employee_id,
last_name,
salary*12 ANNSAL,
hire_date
from employees
where department_id = 80;
select * from dept80;
Results:
create table succeeded.
EMPLOYEE_ID LAST_NAME ANNSAL HIRE_DATE
---------------------- ------------------------- ---------------
145 Russell 168000 01-OCT-96
146 Partners 162000 05-JAN-97
147 Errazuriz 144000 10-MAR-97
148 Cambrault 132000 15-OCT-99
149 Zlotkey 126000 29-JAN-00
150 Tucker 120000 30-JAN-97
151 Bernstein 114000 24-MAR-97
152 Hall 108000 20-AUG-97
153 Olsen 96000 30-MAR-98
154 Cambrault 90000 09-DEC-98
155 Tuvault 84000 23-NOV-99
156 King 120000 30-JAN-96
157 Sully 114000 04-MAR-96
158 McEwen 108000 01-AUG-96
159 Smith 96000 10-MAR-97
160 Doran 90000 15-DEC-97
161 Sewall 84000 03-NOV-98
162 Vishney 126000 11-NOV-97
163 Greene 114000 19-MAR-99
164 Marvins 86400 24-JAN-00
165 Lee 81600 23-FEB-00
166 Ande 76800 24-MAR-00
167 Banda 74400 21-APR-00
168 Ozer 138000 11-MAR-97
169 Bloom 120000 23-MAR-98
170 Fox 115200 24-JAN-98
171 Smith 88800 23-FEB-99
172 Bates 87600 24-MAR-99
173 Kumar 73200 21-APR-00
174 Abel 132000 11-MAY-96
175 Hutton 105600 19-MAR-97
176 Taylor 103200 24-MAR-98
177 Livingston 100800 23-APR-98
179 Johnson 74400 04-JAN-00
34 rows selected
Oracle 11g: Creating Constraints
Types Of Constraints
===> SQL Queries: Column Level
create table employee_column_level
(
employee_id number(6) CONSTRAINT emp_emp_level_id_pk primary key,
first_name varchar2(20)
);
create table employee_table_level
(
employee_id number(6),
first_name varchar2(20),
CONSTRAINT emp_emp_table_id_pk primary key (employee_id)
);
- not null
- primary key
- foreign key
- check
- unique
===> SQL Queries: Column Level
create table employee_column_level
(
employee_id number(6) CONSTRAINT emp_emp_level_id_pk primary key,
first_name varchar2(20)
);
create table employee_table_level
(
employee_id number(6),
first_name varchar2(20),
CONSTRAINT emp_emp_table_id_pk primary key (employee_id)
);
Oracle 11g: Create Tables
Naming Rules
Select * from userA.employees
===> SQL Queries: Creating New Table & Use DEFAULT
create table dept
(
deptno number(2),
dname varchar2(14),
loc varchar2(13),
create_date DATE default sysdate
);
describe dept;
Results
create table succeeded.
describe dept;
Name Null Type
------------------------------ -------- ---------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
CREATE_DATE DATE
- MUST begin with a Character
- Length : 1-30 characters long
- Can only contains: A-Z, a-z, $,_,#,0-9
- CANNOT be duplicate
- CANNOT be oracle reserved words.
- CREATE TABLE privilege
- a storage area.
Select * from userA.employees
===> SQL Queries: Creating New Table & Use DEFAULT
create table dept
(
deptno number(2),
dname varchar2(14),
loc varchar2(13),
create_date DATE default sysdate
);
describe dept;
Results
create table succeeded.
describe dept;
Name Null Type
------------------------------ -------- ---------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
CREATE_DATE DATE
Oracle 11g: Synonyms
Why use Synonym?
create synonym d_sum
for dept_sum_vu;
Results
create synonym succeeded.
===> SQL Queries: Drop a Synonym
drop synonym d_sum;
Results
drop synonym d_sum succeeded.
- create easy reference
- shorten lengthy object name
create synonym d_sum
for dept_sum_vu;
Results
create synonym succeeded.
===> SQL Queries: Drop a Synonym
drop synonym d_sum;
Results
drop synonym d_sum succeeded.
Oracle 11g: Indexes
How Indexes Being Created?
===> SQL Queries: Create New Index
create index emp_last_name_idx
on employees(last_name);
Results
create index succeeded.
===> SQL Queries: Removing an Index
drop index emp_last_name_idx;
Results:
drop index emp_last_name_idx succeeded.
- Automatically
- When we created Primary Key
- Manually
- A column has wide range values
- A column has large number of null values
- One or more columns are frequently used together (Clause/Join Condition)
- Large Table
===> SQL Queries: Create New Index
create index emp_last_name_idx
on employees(last_name);
Results
create index succeeded.
===> SQL Queries: Removing an Index
drop index emp_last_name_idx;
Results:
drop index emp_last_name_idx succeeded.
Oracle 11g: Sequences
===> SQL Queries: Creating A Sequence
create sequence dept_deptid_seq
increment by 10
start with 120
maxvalue 9999
nocache
nocycle;
results
create sequence succeeded.
===> SQL Queries: Using A Sequence
values (dept_deptid_seq.NEXTVAL,'Support',2500);
select dept_deptid_seq.CURRVAL
from dual;
===> SQL Queries: Alter a Sequence
alter sequence dept_deptid_seq
increment by 20
maxvalue 999999
nocache
nocycle
Rules on modifying the sequence:
Results :
alter sequence dept_deptid_seq succeeded.
===> SQL Queries: Removing a Sequence
drop sequence dept_deptid_seq;
Results
drop sequence dept_deptid_seq succeeded.
create sequence dept_deptid_seq
increment by 10
start with 120
maxvalue 9999
nocache
nocycle;
results
create sequence succeeded.
===> SQL Queries: Using A Sequence
- NEXTVAL [nextvalue]
- CURRVAL[currentvalue]
- NEXTVAL must be issued first before CURRVAL has value
values (dept_deptid_seq.NEXTVAL,'Support',2500);
select dept_deptid_seq.CURRVAL
from dual;
===> SQL Queries: Alter a Sequence
alter sequence dept_deptid_seq
increment by 20
maxvalue 999999
nocache
nocycle
Rules on modifying the sequence:
- Must have the privilege to Alter
- Only future sequence nos are affected
- To restart the sequence no, the sequence must be dropped and recreated
Results :
alter sequence dept_deptid_seq succeeded.
===> SQL Queries: Removing a Sequence
drop sequence dept_deptid_seq;
Results
drop sequence dept_deptid_seq succeeded.
Oracle 11g: Clauses For View
===>SQL Queries: WITH CHECK OPTION[make sure that the DML operations performed stay in the domain of the view]
create or replace view empvu20
as
select *
from employees
where department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck;
select * from empvu20;
Results :
create or replace view succeeded.
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------------------- -------------------- --------------------
201 Michael Hartstein MHARTSTE 888.122 17-FEB-96 MK_MAN 3000 100 20
202 Pat Fay PFAY 603.123 17-AUG-97 MK_REP 6000 201 20
===>SQL Queries: WITH READ ONLY[No DML Operation can be done through the view.]
create or replace view empvu10
(employee_number,employee_name,job_title)
as
select employee_id,
last_name,
job_id
from employees
where department_id = 10
WITH READ ONLY;
SELECT * FROM empvu10;
Results:
create or replace view succeeded.
EMPLOYEE_NUMBER EMPLOYEE_NAME JOB_TITLE
---------------------- ------------------------- ----------
200 Whalen AD_ASST
1 rows selected
create or replace view empvu20
as
select *
from employees
where department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck;
select * from empvu20;
Results :
create or replace view succeeded.
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------------------- -------------------- --------------------
201 Michael Hartstein MHARTSTE 888.122 17-FEB-96 MK_MAN 3000 100 20
202 Pat Fay PFAY 603.123 17-AUG-97 MK_REP 6000 201 20
===>SQL Queries: WITH READ ONLY[No DML Operation can be done through the view.]
create or replace view empvu10
(employee_number,employee_name,job_title)
as
select employee_id,
last_name,
job_id
from employees
where department_id = 10
WITH READ ONLY;
SELECT * FROM empvu10;
Results:
create or replace view succeeded.
EMPLOYEE_NUMBER EMPLOYEE_NAME JOB_TITLE
---------------------- ------------------------- ----------
200 Whalen AD_ASST
1 rows selected
Oracle 11g: Rules For Performing DML Operations On a View
- can always perfrorm DML operations om simple views
- cannot remove a row, if view contains:
- Group Function
- Group By
- Distinct
- RowNum
- cannot add data thru view, if view contains:
- Group Function
- Group By
- Distinct
- RowNum
- Parent table that has columns - not null
- cannot modify data in view, if view contains
- Group Function
- Group By
- Distinct
- RowNum
- Expressions (case when, decode)
Oracle 11g: Views
===> SQL Queries: Creating a View
create view empvu80
as
select employee_id,last_name,salary
from employees
where department_id = 80;
describe empvu80
Results:
create view succeeded.
describe empvu80
Name Null Type
------------------------------ -------- ------------------------------------------------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
LAST_NAME NOT NULL VARCHAR2(25)
SALARY NUMBER(8,2)
===> SQL Queries: Creating a View using Aliases
create view salvu80
as
select employee_id ID_NUMBER,
last_name NAME,
salary*12 ANN_SALARY
from employees
where department_id = 50;
describe salvu80;
Results:
describe salvu80
Name Null Type
------------------------------ --------------------------------------------------
ID_NUMBER NOT NULL NUMBER(6)
NAME NOT NULL VARCHAR2(25)
ANN_SALARY
Retrieve data from view:
select * from salvu80
Results:
ID_NUMBER NAME ANN_SALARY
---------------------- ------------------------- ----------------------
120 Weiss 96000
121 Fripp 98400
122 Kaufling 94800
123 Vollman 78000
124 Mourgos 69600
125 Nayer 38400
126 Mikkilineni 32400
127 Landry 28800
128 Markle 26400
129 Bissot 39600
130 Atkinson 33600
131 Marlow 30000
132 Olson 25200
133 Mallin 39600
134 Rogers 34800
135 Gee 28800
136 Philtanker 26400
137 Ladwig 43200
138 Stiles 38400
139 Seo 32400
140 Patel 30000
141 Rajs 42000
142 Davies 37200
143 Matos 31200
144 Vargas 30000
180 Taylor 38400
181 Fleaur 37200
182 Sullivan 30000
183 Geoni 33600
184 Sarchand 50400
185 Bull 49200
186 Dellinger 40800
187 Cabrio 36000
188 Chung 45600
189 Dilly 43200
190 Gates 34800
191 Perkins 30000
192 Bell 48000
193 Everett 46800
194 McCain 38400
195 Jones 33600
196 Walsh 37200
197 Feeney 36000
198 OConnell 31200
199 Grant 31200
45 rows selected
===> SQL Queries: Modifying A View
create or replace view empvu80
(id_number,name,sal,department_id)
as
select employee_id,
first_name || '' || last_name,
salary,
department_id
from employees
where department_id = 80;
select * from empvu80
Results:
ID_NUMBER NAME SAL DEPARTMENT_ID
---------------------- --------------------------------------------- ---------------------- ----------------------
145 JohnRussell 14000 80
146 KarenPartners 13500 80
147 AlbertoErrazuriz 12000 80
148 GeraldCambrault 11000 80
149 EleniZlotkey 10500 80
150 PeterTucker 10000 80
151 DavidBernstein 9500 80
152 PeterHall 9000 80
153 ChristopherOlsen 8000 80
154 NanetteCambrault 7500 80
155 OliverTuvault 7000 80
156 JanetteKing 10000 80
157 PatrickSully 9500 80
158 AllanMcEwen 9000 80
159 LindseySmith 8000 80
160 LouiseDoran 7500 80
161 SarathSewall 7000 80
162 ClaraVishney 10500 80
163 DanielleGreene 9500 80
164 MatteaMarvins 7200 80
165 DavidLee 6800 80
166 SundarAnde 6400 80
167 AmitBanda 6200 80
168 LisaOzer 11500 80
169 HarrisonBloom 10000 80
170 TaylerFox 9600 80
171 WilliamSmith 7400 80
172 ElizabethBates 7300 80
173 SunditaKumar 6100 80
174 EllenAbel 11000 80
175 AlyssaHutton 8800 80
176 JonathonTaylor 8600 80
177 JackLivingston 8400 80
179 CharlesJohnson 6200 80
34 rows selected
===> SQL Queries: Creating a Complex View
create or replace view dept_sum_vu
(name,minsal,maxsal,avgsal)
as
select d.department_name,
min(e.salary),
max(e.salary),
avg(e.salary)
from employees e join departments d
on (e.department_id = d.department_id)
group by d.department_name;
select * from dept_sum_vu;
Results:
create or replace view succeeded.
NAME MINSAL MAXSAL AVGSAL
------------------------------ ---------------------- ---------------------- ----------------------
Administration 4400 4400 4400
Accounting 8300 12000 10150
Executive 17000 24000 333333333333333333
IT 4200 9000 576
Purchasing 2500 11000 4150
Human Resources 6500 6500 6500
Public Relations 10000 10000 10000
Shipping 2100 8200 3475.55555555555556
Finance 6900 12000 8600
Sales 6100 14000 8955.882352941176
Marketing 6000 13000 9500
===> SQL Queries: Creating Removing a View
select * from empvu80;
drop view empvu80;
select * from empvu80;
Results:
ID_NUMBER NAME SAL DEPARTMENT_ID
---------------------- --------------------------------------------- ---------------------- ----------------------
145 JohnRussell 14000 80
146 KarenPartners 13500 80
147 AlbertoErrazuriz 12000 80
148 GeraldCambrault 11000 80
149 EleniZlotkey 10500 80
150 PeterTucker 10000 80
151 DavidBernstein 9500 80
152 PeterHall 9000 80
153 ChristopherOlsen 8000 80
154 NanetteCambrault 7500 80
155 OliverTuvault 7000 80
156 JanetteKing 10000 80
157 PatrickSully 9500 80
158 AllanMcEwen 9000 80
159 LindseySmith 8000 80
160 LouiseDoran 7500 80
161 SarathSewall 7000 80
162 ClaraVishney 10500 80
163 DanielleGreene 9500 80
164 MatteaMarvins 7200 80
165 DavidLee 6800 80
166 SundarAnde 6400 80
167 AmitBanda 6200 80
168 LisaOzer 11500 80
169 HarrisonBloom 10000 80
170 TaylerFox 9600 80
171 WilliamSmith 7400 80
172 ElizabethBates 7300 80
173 SunditaKumar 6100 80
174 EllenAbel 11000 80
175 AlyssaHutton 8800 80
176 JonathonTaylor 8600 80
177 JackLivingston 8400 80
179 CharlesJohnson 6200 80
34 rows selected
drop view empvu80 succeeded.
Error starting at line 5 in command:
select * from empvu80
Error at Command Line:5 Column:15
Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
*Cause:
*Action:
create view empvu80
as
select employee_id,last_name,salary
from employees
where department_id = 80;
describe empvu80
Results:
create view succeeded.
describe empvu80
Name Null Type
------------------------------ -------- ------------------------------------------------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
LAST_NAME NOT NULL VARCHAR2(25)
SALARY NUMBER(8,2)
===> SQL Queries: Creating a View using Aliases
create view salvu80
as
select employee_id ID_NUMBER,
last_name NAME,
salary*12 ANN_SALARY
from employees
where department_id = 50;
describe salvu80;
Results:
describe salvu80
Name Null Type
------------------------------ --------------------------------------------------
ID_NUMBER NOT NULL NUMBER(6)
NAME NOT NULL VARCHAR2(25)
ANN_SALARY
Retrieve data from view:
select * from salvu80
Results:
ID_NUMBER NAME ANN_SALARY
---------------------- ------------------------- ----------------------
120 Weiss 96000
121 Fripp 98400
122 Kaufling 94800
123 Vollman 78000
124 Mourgos 69600
125 Nayer 38400
126 Mikkilineni 32400
127 Landry 28800
128 Markle 26400
129 Bissot 39600
130 Atkinson 33600
131 Marlow 30000
132 Olson 25200
133 Mallin 39600
134 Rogers 34800
135 Gee 28800
136 Philtanker 26400
137 Ladwig 43200
138 Stiles 38400
139 Seo 32400
140 Patel 30000
141 Rajs 42000
142 Davies 37200
143 Matos 31200
144 Vargas 30000
180 Taylor 38400
181 Fleaur 37200
182 Sullivan 30000
183 Geoni 33600
184 Sarchand 50400
185 Bull 49200
186 Dellinger 40800
187 Cabrio 36000
188 Chung 45600
189 Dilly 43200
190 Gates 34800
191 Perkins 30000
192 Bell 48000
193 Everett 46800
194 McCain 38400
195 Jones 33600
196 Walsh 37200
197 Feeney 36000
198 OConnell 31200
199 Grant 31200
45 rows selected
===> SQL Queries: Modifying A View
create or replace view empvu80
(id_number,name,sal,department_id)
as
select employee_id,
first_name || '' || last_name,
salary,
department_id
from employees
where department_id = 80;
select * from empvu80
Results:
ID_NUMBER NAME SAL DEPARTMENT_ID
---------------------- --------------------------------------------- ---------------------- ----------------------
145 JohnRussell 14000 80
146 KarenPartners 13500 80
147 AlbertoErrazuriz 12000 80
148 GeraldCambrault 11000 80
149 EleniZlotkey 10500 80
150 PeterTucker 10000 80
151 DavidBernstein 9500 80
152 PeterHall 9000 80
153 ChristopherOlsen 8000 80
154 NanetteCambrault 7500 80
155 OliverTuvault 7000 80
156 JanetteKing 10000 80
157 PatrickSully 9500 80
158 AllanMcEwen 9000 80
159 LindseySmith 8000 80
160 LouiseDoran 7500 80
161 SarathSewall 7000 80
162 ClaraVishney 10500 80
163 DanielleGreene 9500 80
164 MatteaMarvins 7200 80
165 DavidLee 6800 80
166 SundarAnde 6400 80
167 AmitBanda 6200 80
168 LisaOzer 11500 80
169 HarrisonBloom 10000 80
170 TaylerFox 9600 80
171 WilliamSmith 7400 80
172 ElizabethBates 7300 80
173 SunditaKumar 6100 80
174 EllenAbel 11000 80
175 AlyssaHutton 8800 80
176 JonathonTaylor 8600 80
177 JackLivingston 8400 80
179 CharlesJohnson 6200 80
34 rows selected
===> SQL Queries: Creating a Complex View
create or replace view dept_sum_vu
(name,minsal,maxsal,avgsal)
as
select d.department_name,
min(e.salary),
max(e.salary),
avg(e.salary)
from employees e join departments d
on (e.department_id = d.department_id)
group by d.department_name;
select * from dept_sum_vu;
Results:
create or replace view succeeded.
NAME MINSAL MAXSAL AVGSAL
------------------------------ ---------------------- ---------------------- ----------------------
Administration 4400 4400 4400
Accounting 8300 12000 10150
Executive 17000 24000 333333333333333333
IT 4200 9000 576
Purchasing 2500 11000 4150
Human Resources 6500 6500 6500
Public Relations 10000 10000 10000
Shipping 2100 8200 3475.55555555555556
Finance 6900 12000 8600
Sales 6100 14000 8955.882352941176
Marketing 6000 13000 9500
===> SQL Queries: Creating Removing a View
select * from empvu80;
drop view empvu80;
select * from empvu80;
Results:
ID_NUMBER NAME SAL DEPARTMENT_ID
---------------------- --------------------------------------------- ---------------------- ----------------------
145 JohnRussell 14000 80
146 KarenPartners 13500 80
147 AlbertoErrazuriz 12000 80
148 GeraldCambrault 11000 80
149 EleniZlotkey 10500 80
150 PeterTucker 10000 80
151 DavidBernstein 9500 80
152 PeterHall 9000 80
153 ChristopherOlsen 8000 80
154 NanetteCambrault 7500 80
155 OliverTuvault 7000 80
156 JanetteKing 10000 80
157 PatrickSully 9500 80
158 AllanMcEwen 9000 80
159 LindseySmith 8000 80
160 LouiseDoran 7500 80
161 SarathSewall 7000 80
162 ClaraVishney 10500 80
163 DanielleGreene 9500 80
164 MatteaMarvins 7200 80
165 DavidLee 6800 80
166 SundarAnde 6400 80
167 AmitBanda 6200 80
168 LisaOzer 11500 80
169 HarrisonBloom 10000 80
170 TaylerFox 9600 80
171 WilliamSmith 7400 80
172 ElizabethBates 7300 80
173 SunditaKumar 6100 80
174 EllenAbel 11000 80
175 AlyssaHutton 8800 80
176 JonathonTaylor 8600 80
177 JackLivingston 8400 80
179 CharlesJohnson 6200 80
34 rows selected
drop view empvu80 succeeded.
Error starting at line 5 in command:
select * from empvu80
Error at Command Line:5 Column:15
Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
*Cause:
*Action:
Oracle 11g: Conditional Expressions
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
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:
Posts (Atom)