===> 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:
Subscribe to:
Posts (Atom)
