|
CREATE TABLE
|
Create table <table_name> (col1
datatype1, col2 datatype2 …coln datatypen);
|
|
insert
|
insert into <table_name) values (value1, value2, value3
…. Valuen);
|
|
|
insert into <table_name)
values (&col1, &col2, &col3 …. &coln);
|
|
|
insert into <table_name)(col1,
col2, col3 … Coln) values (value1, value2, value3 …. Valuen);
|
|
|
insert into <table_name)(col1,
col2, col3 … coln) values (&col1, &col2 ….&coln);
|
|
SELECTING
|
Select * from <table_name>; -- here * indicates all columns (or) Select col1, col2, … coln from
<table_name>;
|
|
WHERE
|
select * from <table_name> where <condition>;
|
|
USING AND
|
select * from <table_name>
where <condition1> and <condition2> and .. <conditionn>;
|
|
USING OR
|
Select * from <table_name>
where <condition1> and <condition2> or.. <condition>;
|
|
USING BETWEEN
|
select * from <table_name>
where <col> between <lower bound> and <upper
bound>;
|
|
USING NOT BETWEEN
|
select * from <table_name>
where <col> not between <lower bound> and <upper
bound>;
|
|
USING IN
|
select * from <table_name>
where <col> in ( value1, value2, value3 … valuen);
|
|
USING NOT IN
|
select * from <table_name>
where <col> not in ( value1, value2, value3 … valuen);
|
|
USING NULL
|
select * from <table_name>
where <col> is null;
|
|
USING NOT NULL
|
select * from <table_name>
where <col> is not null;
|
|
USING LIKE
|
select * from <table_name>
where <col> like <pattern>;
|
|
USING ORDER BY
|
Select * from <table_name>
order by <col> desc;
By default oracle will use ascending
order.
If you want output in descending
order you have to use desc keyword after the column.
|
|
|
DML
|
|
USING UPDATE
|
Update <table_name> set <col1> = value1,
<col2> = value2 where <condition>;
|
|
USING DELETE
|
Delete <table_name> where <condition>
|
|
USING ALTER
|
This can be used to add or remove
columns and to modify the precision of the datatype.
|
|
ADDING COLUMN
|
alter table <table_name>
add <col datatype>;
|
|
REMOVING COLUMN
|
alter table <table_name>
drop <col datatype>;
|
|
INCREASING OR DECREASING PRECISION
OF A COLUMN
|
alter table <table_name>
modify <col datatype>;
|
|
MAKING COLUMN UNUSED
|
alter table <table_name>
set unused column <col>;
|
|
DROPPING UNUSED COLUMNS
|
alter table <table_name>
drop unused columns;
|
|
RENAMING COLUMN
|
alter table <table_name>
rename column <old_col_name> to <new_col_name>;
|
|
USING TRUNCATE
|
This can be used to delete the
entire table data permanently.
truncate table <table_name>;
|
|
USING DROP
|
This will be used to drop the
database object;
Drop table <table_name>;
|
|
USING RENAME
|
This will be used to rename the
database object;
rename <old_table_name>
to <new_table_name>;
|
|
|
TCL
|
|
Commit
|
Commit or commit work;
|
|
Rollback
|
Roll
or roll work;
Or
Rollback
or rollback work;
|
|
USING SAVEPOINT
|
You can use savepoints to rollback
portions of your current set of transactions.
Savepoint <savepoint_name>;
|
|
|
DCl
|
|
USING GRANT
|
This is used to grant the
privileges to other users.
Grant <privileges> on <object_name> to <user_name>
[with grant option];
|
|
USING REVOKE
|
This is used to revoke the
privileges from the users to which you granted the privileges.
Revoke <privileges> on
<object_name> from <user_name>;
|
|
USING ALIASES
|
CREATE WITH SELECT
We can create a table using
existing table [along with data].
Create table <new_table_name> [col1,
col2, col3 ... coln] as select * from
<old_table_name>;
|
|
|
INSERT WITH SELECT
Using this we can insert existing
table data to a another table in a single trip. But the table structure
should be same.
Insert into <table1> select * from <table2>;
|
|
COLUMN ALIASES
|
Select <orginal_col>
<alias_name> from <table_name>;
|
|
TABLE ALIASES
|
If you are using table aliases you
can use dot method to the columns.
Select <alias_name>.<col1>, <alias_name>.<col2>
… <alias_name>.<coln> from <table_name> <alias_name>;
|
|
USING MERGE
|
You can use merge command to perform insert and update
in a single command.
Ex:SQL> Merge into student1 s1
Using (select *From student2) s2
On(s1.no=s2.no)
When matched then
Update set marks = s2.marks
When not matched then
Insert (s1.no,s1.name,s1.marks)
Values(s2.no,s2.name,s2.marks);
|
|
MULTIPLE INSERTS
|
MULTI
INSERT WITH ALL FIELDS
Insert
all
Into student values(1,’a’,100)
Into student values(2,’b’,200)
Into student values(3,’c’,300)
MULTI INSERT WITH SPECIFIED FIELDS
insert
all
Into student (no,name)
values(4,’d’)
Into student(name,marks)
values(’e’,400)
Into student values(3,’c’,300)
Select
*from dept where deptno=10;
MULTI INSERT WITH DUPLICATE ROWS
insert
all
Into student values(1,’a’,100)
Into student values(2,’b’,200)
Into student values(3,’c’,300)
Select
*from dept where deptno > 10;
MULTI INSERT WITH CONDITIONS BASED
Insert
all
When deptno > 10 then
Into student1 values(1,’a’,100)
When dname = ‘SALES’ then
Into student2 values(2,’b’,200)
When loc = ‘NEW YORK’ then
Into student3 values(3,’c’,300)
Select *from dept where
deptno>10;
MULTI
INSERT WITH CONDITIONS BASED AND ELSE
Insert
all
When deptno > 100 then
Into student1 values(1,’a’,100)
When dname = ‘S’ then
Into student2 values(2,’b’,200)
When loc = ‘NEW YORK’ then
Into student3 values(3,’c’,300)
Else
Into student values(4,’d’,400)
Select *from dept where
deptno>10;
MULTI
INSERT WITH CONDITIONS BASED AND FIRST
Insert
first
When deptno = 20 then
Into student1 values(1,’a’,100)
When dname = ‘RESEARCH’ then
Into student2 values(2,’b’,200)
When loc = ‘NEW YORK’ then
Into student3 values(3,’c’,300)
Select *from dept where
deptno=20;
MULTI
INSERT WITH CONDITIONS BASED, FIRST AND ELSE
>
Insert first
When deptno = 30 then
Into student1 values(1,’a’,100)
When dname = ‘R’ then
Into student2 values(2,’b’,200)
When loc = ‘NEW YORK’ then
Into student3 values(3,’c’,300)
Else
Into student values(4,’d’,400)
Select *from dept where
deptno=20;
MULTI
INSERT WITH MULTIBLE TABLES
>
Insert all
Into student1 values(1,’a’,100)
Into student2 values(2,’b’,200)
Into student3 values(3,’c’,300)
Select *from dept where
deptno=10;
|
|
|
FUNCTIONS
|
|
ABS
|
Absolute value is the measure of the magnitude of value.
Absolute value is always a positive number.
Syntax: abs (value)
|
|
SIGN
|
Sign gives the sign of a value.
Syntax: sign (value)
|
|
SQRT
|
This will give the square root of the given value.
Syntax: sqrt (value) -- here value must be positive.
|
|
MOD
|
This will give the remainder.
Syntax: mod (value, divisor)
|
|
NVL
|
This will substitutes the specified
value in the place of null values.
Syntax: nvl (null_col, replacement_value)
|
|
POWER
|
Power is the ability to raise a
value to a given exponent.
Syntax: power (value, exponent)
|
|
EXP
|
This will raise e value to the give
power.
Syntax: exp (value)
|
|
LN
|
This is based on natural or base e logarithm.
Syntax: ln (value) --
here value must be greater than zero which is positive only.
|
|
LOG
|
This is based on 10 based
logarithm.
Syntax: log (10, value) --
here value must be greater than zero which is positive only.
|
|
CEIL
|
This will produce a whole number
that is greater than or equal to the specified value.
Syntax: ceil (value)
|
|
FLOOR
|
This will produce a whole number that is less than or equal to the
specified value.
Syntax: floor (value)
|
|
ROUND
|
This will rounds numbers to a given
number of digits of precision.
Syntax: round (value, precision)
|
|
TRUNC
|
This will truncates or chops off digits
of precision from a number.
Syntax: trunc (value, precision)
|
|
BITAND
|
This will perform bitwise and
operation.
Syntax: bitand (value1, value2)
|
|
GREATEST
|
This will give the greatest number.
Syntax: greatest (value1, value2, value3 … valuen)
|
|
LEAST
|
This will give the least number.
Syntax: least (value1, value2, value3 … valuen)
|
|
COALESCE
|
This will return first non-null
value.
Syntax: coalesce (value1, value2, value3 … valuen)
|
|
|
STRING FUNCTIONS
|
|
INITCAP
|
This will capitalize the initial letter of the string.
Syntax: initcap (string)
|
|
UPPER
|
This will convert the string into
uppercase.
Syntax: upper (string)
|
|
LOWER
|
This will convert the string into
lowercase.
Syntax: lower (string)
|
|
LENGTH
|
This will give length of the
string.
Syntax: length (string)
|
|
RPAD
|
This will allows you to pad the
right side of a column with any set of characters.
Syntax: rpad (string, length [, padding_char])
|
|
LPAD
|
This will allows you to pad the left side of a column with any set of
characters.
Syntax: lpad (string, length [, padding_char])
|
|
LTRIM
|
This will trim off unwanted
characters from the left end of string.
Syntax: ltrim (string
[,unwanted_chars])
|
|
RTRIM
|
This will trim off unwanted characters from the right end of string.
Syntax: rtrim (string [, unwanted_chars])
|
|
TRIM
|
This will trim off unwanted
characters from the both sides of string.
Syntax: trim (unwanted_chars from string)
|
|
TRANSLATE
|
This will replace the set of
characters, character by character.
Syntax: translate (string, old_chars, new_chars)
|
|
REPLACE
|
This will replace the set of
characters, string by string.
Syntax: replace (string, old_chars [, new_chars])
|
|
SOUNDEX
|
This will be used to find words
that sound like other words, exclusively used in where clause.
Syntax: soundex (string)
|
|
CONCAT
|
This will be used to combine two
strings only.
Syntax: concat (string1, string2)
|
|
ASCII
|
This will return the decimal
representation in the database character set of the first character of the string.
Syntax: ascii (string)
|
|
CHR
|
This will return the character
having the binary equivalent to the string in either the database character
set or the national character set.
Syntax: chr (number)
|
|
SUBSTR
|
This will be used to extract substrings.
Syntax: substr (string, start_chr_count [, no_of_chars])
Note:
1 If no_of_chars parameter is
negative then it will display nothing.
2 If both parameters except string
are null or zeros then it will display nothing.
3 If no_of_chars parameter is
greater than the length of the string then it ignores and calculates based on
the orginal string length.
4 If start_chr_count is
negative then it will extract the substring from right end.
|
|
INSTR
|
This will allows you for searching through a string for set of
characters.
Syntax: instr (string, search_str [, start_chr_count [, occurrence]
])
Note:
1
If you are not specifying start_chr_count and occurrence
then it will start
search from
the beginning and finds first occurrence only.
2
If both parameters start_chr_count and occurrence
are null, it will display nothing.
|
|
DECODE
|
Decode will act as value by value
substitution.
For every value of field, it will checks for a match in a series of
if/then tests.
Syntax: decode (value, if1, then1, if2, then2, ……. else);
|
|
GREATEST
|
This will give the greatest string.
Syntax: greatest (strng1, string2, string3 … stringn)
Note:
1.If all the parameters are nulls
then it will display nothing.
2.
If any of the
parameters is null it will display nothing.
|
|
LEAST
|
This will give the least string.
Syntax: greatest (strng1, string2, string3 … stringn)
Note:
1.If all the parameters are nulls
then it will display nothing.
2.If any of the parameters is null
it will display nothing.
|
|
COALESCE
|
This will gives the first non-null
string.
Syntax: coalesce (strng1, string2, string3 … stringn)
|
|
|
DATE FUNCTIONS
|
|
SYSDATE
|
This will give the current date and time.
Ex: SQL> select sysdate from dual;
|
|
CURRENT_DATE
|
This will returns the current date in the session’s timezone.
Ex: SQL> select current_date from
dual;
|
|
CURRENT_TIMESTAMP
|
This will returns the current
timestamp with the active time zone information.
Ex:
SQL> select current_timestamp from dual;
|
|
SYSTIMESTAMP
|
This will returns the system date,
including fractional seconds and time zone of the database.
Ex:
SQL> select systimestamp from dual;
|
|
LOCALTIMESTAMP
|
This will returns local timestamp
in the active time zone information, with no time zone information shown.
Ex: SQL>
select localtimestamp from dual;
|
|
DBTIMEZONE
|
This will returns the current
database time zone in UTC format. (Coordinated Universal Time)
Ex: SQL> select dbtimezone
from dual;
|
|
SESSIONTIMEZONE
|
This will returns the value of the current session’s time zone.
Ex: SQL> select
sessiontimezone from dual;
|
|
TO_CHAR
|
This will be used to extract various date formats.
The available date formats as follows.
Syntax: to_char (date, format)
|
|
TO_DATE
|
This will be used to convert the
string into data format.
Syntax: to_date (date)
|
|
ADD_MONTHS
|
This will add the specified months
to the given date.
Syntax: add_months (date, no_of_months)
|
|
MONTHS_BETWEEN
|
This will give difference of months
between two dates.
Syntax: months_between (date1, date2)
|
|
NEXT_DAY
|
This will produce next day of the given day from the specified date.
Syntax: next_day (date, day)
|
|
LAST_DAY
|
This will produce last day of the given date.
Syntax: last_day (date)
|
|
EXTRACT
|
This is used to extract a portion
of the date value.
Syntax: extract ((year | month | day | hour | minute | second), date)
|
|
GREATEST
|
This will give the greatest date.
Syntax: greatest (date1, date2, date3 … daten)
|
|
LEAST
|
This will give the least date.
Syntax: least (date1, date2, date3 … daten)
|
|
ROUND
|
Round will rounds the date to which
it was equal to or greater than the given date.
Syntax: round (date, (day | month | year))
|
|
TRUNC
|
Trunc will chops off the date to
which it was equal to or less than the given date.
Syntax: trunc (date, (day | month | year))
|
|
NEW_TIME
|
This will give the desired timezone’s date and time.
Syntax: new_time (date,
current_timezone, desired_timezone)
|
|
COALESCE
|
This will give the first non-null date.
Syntax: coalesce (date1, date2, date3 … daten)
|
|
|
MISCELLANEOUS FUNCTIONS
|
|
UID
|
This will returns the integer value
corresponding to the user currently logged in.
Ex: SQL> select uid
from dual;
|
|
USER
|
This will returns the login’s user name.
Ex: SQL> select user from dual;
|
|
VSIZE
|
This will returns the number of bytes in the expression.
Ex: SQL> select
vsize(123), vsize('computer'), vsize('12-jan-90') from dual;
|
|
RANK
|
This will give the non-sequential
ranking.
Ex: SQL> select rownum,sal from (select
sal from emp order by sal desc);
|
|
DENSE_RANK
|
This will give the sequential ranking.
Ex: SQL> select
dense_rank(2975) within group(order by sal desc) from emp;
|
|
|
CONVERSION FUNCTIONS
|
|
BIN_TO_NUM
|
This will convert the binary
value to its numerical equivalent.
Syntax: bin_to_num( binary_bits)
|
|
CHARTOROWID
|
This will convert a character string to act like an internal oracle
row identifier or rowid.
|
|
ROWIDTOCHAR
|
This will convert an internal
oracle row identifier or rowid to character string.
|
|
TO_NUMBER
|
This will convert a char or varchar
to number.
|
|
TO_CHAR
|
This will convert a number or date
to character string.
|
|
TO_DATE
|
This will convert a number, char or
varchar to a date.
|
|
|
GROUP FUNCTIONS
|
|
SUM
|
This will give the sum of the values of the specified column.
Syntax: sum (column)
|
|
AVG
|
This will give the average of the values of the specified column.
Syntax: avg (column)
|
|
MAX
|
This will give the maximum of the values of the specified column.
Syntax: max (column)
|
|
MIN
|
This will give the minimum of the
values of the specified column.
Syntax: min (column)
|
|
COUNT
|
This will give the count of the
values of the specified column.
Syntax: count (column)
|
|
|
CONSTRAINTS
|
|
NOT NULL
|
This is used to avoid null values.
We can add this constraint in
column level only.
SQL> create table student(no
number(2) not null, name varchar(10), marks
number(3));
|
|
CHECK
|
This is used to insert the values
based on specified condition.
We can add this constraint in all
three levels.
COLUMN LEVEL:
SQL> create table student(no number(2) , name varchar(10), marks
number(3) check
(marks > 300));
TABLE LEVEL:
SQL> create table student(no
number(2) , name varchar(10), marks number(3), check
(marks > 300));
ALTER LEVEL:
SQL> alter table student add check(marks>300);
|
|
UNIQUE
|
This is used to avoid duplicates
but it allow nulls.
We can add this constraint in all
three levels.
COLUMN LEVEL:
SQL> create table student(no number(2) unique, name varchar(10),
marks
number(3));
TABLE LEVEL:
SQL> create table student(no
number(2) , name varchar(10), marks number(3),
unique(no));
ALTER LEVEL:
SQL> alter table student add
unique(no);
|
|
PRIMARY KEY
|
1 This is used to avoid duplicates
and nulls. This will work as combination of unique and not null.
2 Primary key always attached to the
parent table.
3 We can add this constraint in all
three levels.
COLUMN LEVEL:
SQL> create table student(no number(2) primary key, name
varchar(10), marks
number(3));
TABLE LEVEL:
SQL> create table student(no
number(2) , name varchar(10), marks number(3),
primary key(no));
ALTER LEVEL:
SQL> alter table student add
primary key(no);
|
|
FOREIGN KEY
|
1 This is used to reference the
parent table primary key column which allows duplicates.
2 Foreign key always attached to the
child table.
3 We can add this constraint in table
and alter levels only.
TABLE LEVEL:
SQL> create table emp(empno
number(2), ename varchar(10), deptno number(2),
primary key(empno), foreign
key(deptno) references dept(deptno));
ALTER LEVEL:
SQL> alter table emp add foreign
key(deptno) references dept(deptno);
Note:
Once the primary key and foreign
key relationship has been created then you can not remove any parent record
if the dependent childs exists.
|
|
USING ON DELTE CASCADE
|
By using this clause you can remove
the parent record even it childs exists.
Because
when ever you remove parent record oracle automatically removes all its
dependent records from child table, if this clause is present while creating
foreign key constraint.
TABLE LEVEL:
SQL>
create table emp(empno number(2), ename varchar(10), deptno number(2),
primary key(empno), foreign
key(deptno) references dept(deptno) on delete
cascade);
ALTER LEVEL:
SQL> alter table emp add foreign
key(deptno) references dept(deptno) on delete
cascade;
|
|
COMPOSITE KEYS
|
A composite key can be defined on a
combination of columns.
We can define composite keys on
entity integrity and referential integrity constraints.
Composite key can be defined in
table and alter levels only.
UNIQUE (TABLE LEVEL):
SQL> create table student(no number(2)
, name varchar(10), marks number(3),
unique(no,name));
UNIQUE (ALTER LEVEL):
SQL> alter table student add
unique(no,name);
PRIMARY KEY (TABLE LEVEL):
SQL> create table student(no
number(2) , name varchar(10), marks number(3),
primary key(no,name));
PRIMARY KEY (ALTER LEVEL):
SQL> alter table student add
primary key(no,anme);
FOREIGN KEY (TABLE LEVEL):
SQL> create table emp(empno
number(2), ename varchar(10), deptno number(2),
dname varchar(10), primary
key(empno), foreign key(deptno,dname) references
dept(deptno,dname));
FOREIGN KEY (ALTER LEVEL):
SQL> alter table emp add foreign
key(deptno,dname) references dept(deptno,dname);
|
|
ENABLE constraint
|
This will enable the constraint.
Before enable, the constraint will check the existing data.
Ex: SQL> alter table student enable
constraint un;
|
|
DISABLE constraint
|
This will disable the constraint.
Ex: SQL> alter table student enable
constraint un;
|
|
ENFORCE constraint
|
This will enforce the constraint
rather than enable for future inserts or updates.
This will not check for existing
data while enforcing data.
Ex: SQL> alter table student enforce
constraint un;
|
|
DROP
constraint
|
This will remove the constraint.
Ex: SQL> alter table student drop
constraint un;
Once the table is dropped, constraints automatically will drop.
|
|
|
CASE AND DEFAULT
|
|
CASE
|
Case
is similar to decode but easier to understand while going through coding
Select
sal,
Case sal
When 500 then ‘low’
When 5000 then ‘high’
Else ‘medium’
End case
From emp;
|
|
DEFAULT
|
Default can be considered as a substitute
behavior of not null constraint when applied to new rows being entered
into the table.
SQL> create table student(no
number(2) default 11,name varchar(2));
Note:
-- Default can not override nulls.
|
|
|
ABSTRACT DATA TYPES
|
|
|
Some times you may want type which
holds all types of data including numbers, chars and special characters
something like this.
> create type addr as object(hno
number(3),city varchar(10));
>create table student(no
number(2),name varchar(2),address addr);
SQL> insert into student
values(1,'a',addr(111,'hyd'));
select no,name,s.address.hno,s.address.city
from student s;
UPDATE WITH ADT TABLES:
update student s set s.address.city
= 'bombay' where s.address.hno = 333;
DELETE WITH ADT TABLES:
delete student s where
s.address.hno = 111;
DROPPING ADT:
SQL> drop type addr;
|
|
|
OBJECT
VIEWS AND METHODS
|
|
|
|
|
|
VARRAYS
AND NESTED TABLES
|
|
|
|
|
|
FLASHBACK QUERY
|
|
|
EXTERNAL
TABLES
|
|
|
REF DEREF VALUE
|
|
|
|
|
|
OBJECT VIEWS WITH REFERENCES
|
|
|
|
|
|
PARTITIONS
|
|
|
|
|
|
|
|
GROUP BY AND HAVING
|
Using group by, we can create
groups of related information.
Columns used in select must be used
with group by, otherwise it was not a group by expression
select
deptno, sum(sal) from emp group by deptno;
HAVING:
This will work as where clause
which can be used only with group by
because of absence of where clause in group by.
select deptno,job,sum(sal) tsal
from emp group by deptno,job having sum(sal) >
3000;
|
|
ROLLUP GROUPING CUBE
|
USING ROLLUP:
This will give the salaries in each
department in each job category along wih the total salary for individual
departments and the total salary of all the departments.
SQL> Select deptno,job,sum(sal)
from emp group by rollup(deptno,job);
USING GROUPING:
Select
decode(grouping(deptno),1,'All
Depts',deptno),decode(grouping(job),1,'All
jobs',job),sum(sal) from emp group by
rollup(deptno,job);
USING CUBE:
select
decode(grouping(deptno),1,’All
Depts’,deptno),decode(grouping(job),1,’All
Jobs’,job),sum(sal) from emp group by
cube(deptno,job);
|
|
|
SET OPERATORS
|
|
UNION
|
This
will combine the records of multiple tables having the same structure.
Ex: SQL> select * from student1 union
select * from student2;
|
|
UNION
ALL
|
This
will combine the records of multiple tables having the same structure but
including duplicates.
Ex: SQL> select * from student1 union all
select * from student2;
|
|
INTERSECT
|
This
will give the common records of multiple tables having the same structure.
Ex: SQL> select * from student1 intersect
select * from student2;
|
|
MINUS
|
This
will give the records of a table whose records are not in other tables having
the same structure.
Ex: SQL> select * from student1 minus
select * from student2;
|
|
|
VIEWS
|
|
VIEWS
|
TYPES
1 Simple view
2 Complex view
VIEWS WITHOUT DML(Read only view,View with group by,View with aggregate
functions,
View with rownum,Partition
view,View with distinct):
SQL> Create view dept_v as select
*from dept with read only;
SQL> Create view dept_v as select
deptno, sum(sal) t_sal from emp group by deptno;
SQL> Create view stud as select
rownum no, name, marks from student;
SQL> Create view student as select
*from student1 union select *from student2;
SQL> Create view stud as select
distinct no,name from student;
VIEWS WITH DML:
1
View with not null column --
insert with out not null column not possible
-- update not null column to
null is not possible
-- delete possible
2
View with out not null column which was in base table --
insert not possible
-- update, delete possible
3
View with expression -- insert , update not possible
-- delete possible
4
View with
functions (except aggregate) -- insert, update not possible
-- delete possible
5
View was created but the underlying table was dropped then
we will get the message like “ view has errors ”.
6
View was created but the base table has been altered but
still the view was with the initial definition, we have to replace the view
to affect the changes.
7
Complex view (view with more than one table) -- insert
not possible
-- update, delete
possible (not always)
CREATING VIEW WITHOUT HAVING THE
BASE TABLE
SQL> Create force view stud as
select *From student;
-- Once the base table was created
then the view is validated.
DROPPING VIEWS
SQL>
drop view dept_v;
|
|
|
SYNONYM AND SYNONYM
|
|
SEQUENCE
|
A synonym is a database object,
which is used as an alias for a table, view or sequence.
TYPES
3 Private
4 Public
Private synonym is available to the
particular user who creates.
Public synonym is created by DBA
which is available to all the users.
ADVANTAGES
1
Hide the name and owner of the object.
2
Provides location transparency for remote objects of a
distributed database.
CREATE AND DROP
SQL> create synonym s1 for emp;
SQL> drop synonym s1;
SEQUENCE:
Syntax:Create sequence <seq_name>
[increment bty n] [start with n] [maxvalue n]
[minvalue n] [cycle/nocycle]
[cache/nocache];
By defalult the sequence starts
with 1, increments by 1 with minvalue of 1 and with nocycle, nocache.
Cache option pre-alloocates a set
of sequence numbers and retains them in memory for faster access.
Ex: SQL> create sequence s;
SQL> create sequence s increment by 10 start with 100 minvalue 5
maxvalue 200 cycle cache 20;
ALTERING SEQUENCE
We can alter the sequence to
perform the following.
1
Set or eliminate minvalue or maxvalue.
2
Change the increment value.
3
Change the number of cached sequence numbers.
Ex: SQL> alter sequence s minvalue 5;
SQL> alter sequence s increment by 2;
SQL> alter sequence s cache 10;
DROPPING SEQUENCE
SQL> drop sequence s;
|
|
|
JOINS
|
|
EQUI JOIN
|
A join which contains an ‘=’
operator in the joins condition.
select empno,ename,job,dname,loc
from emp e,dept d where e.deptno=d.deptno;
USING CLAUSE
SQL> select empno,ename,job
,dname,loc from emp e join dept d using(deptno);
ON CLAUSE
SQL> select empno,ename,job,dname,loc from emp e
join dept d on(e.deptno=d.deptno);
|
|
NON-EQUI JOIN
|
A join which contains an operator
other than ‘=’ in the joins condition.
Ex:
SQL> select empno,ename,job,dname,loc from emp e,dept d where
e.deptno = d.deptno;
|
|
SELF JOIN
|
Joining the table itself is called
self join.
Ex: SQL> select
e1.empno,e2.ename,e1.job,e2.deptno from emp e1,emp e2 where e1.empno=e2.mgr;
|
|
NATURAL JOIN
|
Natural join compares all the
common columns.
Ex: SQL> select empno,ename,job,dname,loc
from emp natural join dept;
|
|
CROSS JOIN
|
This will gives the cross product.
Ex: SQL> select empno,ename,job,dname,loc
from emp cross join dept;
|
|
OUTER JOIN
|
Outer join gives the non-matching
records along with matching records.
LEFT OUTER JOIN:
This will display the all matching
records and the records which are in left hand side table those that are not
in right hand side table.
Ex: SQL> select
empno,ename,job,dname,loc from emp e left outer join dept d
on(e.deptno=d.deptno);Or SQL>
select empno,ename,job,dname,loc from emp e,dept d where e.deptno=d.deptno(+);
RIGHT OUTER JOIN
This will display the all matching
records and the records which are in right hand side table those that are not
in left hand side table.
Ex:
SQL> select empno,ename,job,dname,loc from emp e right outer join
dept d
on(e.deptno=d.deptno);Or SQL>
select empno,ename,job,dname,loc from emp e,dept d where e.deptno(+) = d.deptno;
FULL OUTER JOIN
This will display the all matching
records and the non-matching records from both tables.
Ex: SQL> select empno,ename,job,dname,loc
from emp e full outer join dept d
on(e.deptno=d.deptno);
|
|
INNER JOIN
|
This will display all the records
that have matched.
Ex: SQL> select empno,ename,job,dname,loc
from emp inner join dept using(deptno);
|
|
|
SUBQUERIES AND EXISTS
|
|
SINGLE
ROW SUBQUERIES
|
In single row subquery, it will
return one value.
Ex: SQL> select * from emp where sal >
(select sal from emp where empno = 7566);
|
|
MULTI
ROW SUBQUERIES
|
In multi row subquery, it will
return more than one value. In such cases we should include operators like
any, all, in or not in between the comparision operator and the subquery.
Ex: SQL> select * from emp where sal >
any (select sal from emp where sal between 2500 and 4000);
|
|
MULTIPLE
SUBQUERIES
|
There is no limit on the number of
subqueries included in a where clause. It allows nesting of a query within a
subquery.
Ex: SQL> select * from emp where sal =
(select max(sal) from emp where sal < (select max(sal) from emp));
|
|
CORRELATED
SUBQUERIES
|
A subquery is evaluated once for
the entire parent statement where as a correlated subquery is evaluated once
for every row processed by the parent statement.
Ex: SQL> select distinct deptno from emp
e where 5 <= (select count(ename) from emp where e.deptno = deptno);
|
|
EXISTS
|
Exists
function is a test for existence. This is a logical test for the return of
rows from a query.
Ex: Suppose we want to display the
department numbers which has more than 4
employees.
SQL> select deptno,count(*) from emp
group by deptno having count(*) > 4;
|
|
NOT
EXISTS
|
SQL>
select deptno,ename from emp e1 where not exists (select * from emp e2
where e1.deptno=e2.deptno group by
e2.deptno having count(e2.ename) > 4) order by deptno,ename;
|
|
|
WALKUP TREES AND INLINE VIEW
Using hierarchical queries, you can
retrieve data based on a natural hierarchical relationship between rows in a
table. However, where a hierarchical relationship exists between the rows of
a table, a process called tree walking enables the hierarchy to be
constructed.
SQL> select ename || '==>' || prior ename, level from emp start with
ename = 'KING'
connect by prior empno=mgr;
|
|
INLINE
VIEW OR TOP-N ANALYSIS
|
In
the select statement instead of table name, replacing the select statement is
known as inline view.
SQL> Select ename, sal, rownum rank
from (select *from emp order by sal);
|
|
|
LOCKS
|
|
ROW
LEVEL LOCKS
|
In the row level lock a row is
locked exclusively so that other cannot modify the row until the transaction
holding the lock is committed or rolled back. This can be done by using
select..for update clause.
Ex: SQL> select * from emp where sal >
3000 for update of comm.;
|
|
TABLE
LEVEL LOCKS
|
A table level lock will protect
table data thereby guaranteeing data integrity when data is being accessed
concurrently by multiple users. A table lock can be held in several modes.
1 Share lock
2 Share update lock
3 Exclusive lock
SHARE
LOCK:A share lock locks the table allowing other users to only query but not
insert, update or delete rows in a table. Multiple users can place share
locks on the same resource at the same time.
Ex: SQL> lock table emp in share mode;
SHARE
UPDATE LOCK:It locks rows that are to be updated in a table. It permits other
users to concurrently query, insert , update or even lock other rows in the
same table. It prevents the other users from updating the row that has been
locked.
Ex: SQL> lock table emp in share update
mode;
EXCLUSIVE
LOCK:Exclusive lock is the most restrictive of tables locks. When issued by
any user, it allows the other user to only query. It is similar to share lock
but only one user can place exclusive lock on a table at a time.
Ex: SQL> lock table emp in share
exclusive mode;
|
|
NOWAIT
|
If one user locked the table
without nowait then another user trying to lock the same table then he has to
wait until the user who has initially locked the table issues a commit or
rollback statement. This delay could be avoided by appending a nowait clause
in the lock table command.
Ex: SQL> lock table emp in exclusive mode
nowait.
|
|
DEADLOCK
|
A deadlock occurs when two users
have a lock each on separate object, and they want to acquire a lock on the
each other’s object. When this happens, the first user has to wait for the
second user to release the lock, but the second user will not release it
until the lock on the first user’s object is freed. In such a case, oracle
detects the deadlock automatically and solves the problem by aborting one of
the two transactions.
|
|
|
INDEXES
|
|
WHY INDEXES?
|
Indexes are most useful on larger
tables, on columns that are likely to appear in where clauses as simple
equality.
|
|
TYPES
|
1
Unique index
2
Non-unique index
3
Btree index
4
Bitmap index
5
Composite index
6
Reverse key index
7
Function-based index
8
Descending index
9
Domain index
10 Object index
11 Cluster index
12 Text index
13 Index organized table
14 Partition index
Local index
Local prefixed
Local non-prefixed
15
Global index
Global prefixed
Global non-prefixed
|
|
UNIQUE INDEX
|
Unique indexes guarantee that no
two rows of a table have duplicate values in the columns that define the
index. Unique index is automatically created when primary key or unique
constraint is created.
Ex: SQL> create unique index stud_ind on
student(sno);
|
|
NON-UNIQUE INDEX
|
Non-Unique indexes do not impose
the above restriction on the column values.
Ex: SQL> create index stud_ind on
student(sno);
|
|
BTREE INDEX or ASCENDING INDEX
|
The default type of index used in
an oracle database is the btree index. A btree index is designed to provide
both rapid access to individual rows and quick access to groups of rows
within a range. The btree index does this by performing a succession of value
comparisons. Each comparison eliminates many of the rows.
SQL> create index stud_ind on student(sno);
|
|
BITMAP INDEX
|
This can be used for low
cardinality columns: that is columns in which the number of distinct values
is small when compared to the number of the rows in the table.
SQL> create bitmap index stud_ind on student(sex);
|
|
COMPOSITE INDEX
|
A composite index also called a
concatenated index is an index created on multiple columns of a table.
Columns in a composite index can appear in any order and need not be adjacent
columns of the table.
SQL> create bitmap index stud_ind on student(sno, sname);
|
|
REVERSE KEY INDEX
|
A reverse key index when compared
to standard index, reverses each byte of the column being indexed while
keeping the column order.
SQL> create index stud_ind on
student(sno, reverse);
|
|
FUNCTION BASED INDEX
|
This will use result of the
function as key instead of using column as the value for the key.
Ex: SQL> create index stud_ind on
student(upper(sname));
|
|
DESCENDING INDEX
|
The order used by B-tree indexes
has been ascending order. You can categorize data in B-tree index in
descending order as well.
SQL> create index stud_ind on
student(sno desc);
|
|
TEXT INDEX
|
Querying text is different from
querying data because words have shades of meaning, relationships to other
words, and opposites.
|
|
|
SQL*PLUS
COMMNANDS
|
|
BREAK
|
This
will be used to breakup the data depending on the grouping.
Syntax: Break or bre [on <column_name>
on report]
|
|
COMPUTE
|
This
will be used to perform group functions on the data.
Syntax: Compute or comp [group_function of column_name
on breaking_column_name or
report]
|
|
TTITLE
|
This
will give the top title for your report. You can on or off the ttitle.
Syntax: Ttitle or ttit [left | center | right] title_name skip n other_characters
Ttitle or ttit [on or off]
|
|
BTITLE
|
This
will give the bottom title for your report. You can on or off the btitle.
Syntax: Btitle or btit [left | center | right] title_name skip n other_characters
Btitle or btit [on or off]
SQL> bre on deptno skip 1 on
report
SQL> comp sum of sal on deptno
SQL> comp sum of sal on report
SQL> ttitle center 'EMPLOYEE
DETAILS' skip1 center '----------------'
SQL> btitle center '** THANKQ
**'
SQL> select * from emp order by
deptno;
|
|
CLEAR
|
This
will clear the existing buffers or break or computations or columns
formatting.
Syntax: Clear or cle buffer | bre | comp | col;
|
|
CHANGE
|
This
will be used to replace any strings in SQL statements.
Syntax:
Change or c/old_string/new_string
|
|
COLUMN
|
This
will be used to increase or decrease the width of the table columns.
Syntax: Column or col <column_name>
format <num_format|text_format>
|
|
SAVE
|
This
will be used to save your current SQL statement as SQL Script file.
Syntax: Save or sav <file_name>.[extension]
replace or rep
|
|
EXECUTE
|
This
will be used to execute stored subprograms or packaged subprograms.
Syntax: Execute or exec <subprogram_name>
Ex: SQL> exec sample_proc
|
|
SPOOL
|
This
will record the data when you spool on, upto when you say spool off. By
default it will give lst as extension.
Syntax: Spool on | off | out | <file_name>.[Extension]
Ex: SQL> spool on
SQL> select * from dept;
|
|
LIST
|
This
will give the current SQL statement.
Syntax: List or li [start_line_number] [end_line_number]
|
|
INPUT
|
This
will insert the new line to the current SQL statement.
Syntax: Input or in <string>
Ex: SQL> select *
SQL>
list
1* select *
SQL> input from dept
SQL> list
1 select *
2* from dept
|
|
APPEND
|
This
will adds a new string to the existing string in the SQL statement without
any space.
Syntax: Append or app <string>
Ex: SQL> select *
SQL> list
1* select *
SQL> append from dept
1* select * from dept
SQL> list
1* select * from dept
|
|
DELETE
|
This
will delete the current SQL statement lines.
Syntax: Delete or del <start_line_number>
[<end_line_number>]
Ex: SQL> select
2 *
3 from
4 dept
5 where
6 deptno
7 >10;
SQL> list
1 select
2 *
3 from
4 dept
5 where
6 deptno
7* >10
SQL> del 1
SQL> list
1 *
2 from
3 dept
4 where
5 deptno
6* >10
SQL> del 2
SQL> list
1 *
2 dept
3 where
4 deptno
5* >10
SQL> del 2 4
SQL> list
1 *
2* >10
SQL> del
SQL> list
1 *
|
|
VARIABLE
|
This
will be used to declare a variable.
Syntax: Variable or var <variable_name>
<variable_type>
Ex: SQL> var dept_name varchar(15)
|
|
PRINT
|
This
will be used to print the output of the variables that will be declared at
SQL level.
Syntax: Print <variable_name>
|
|
START
|
This
will be used to execute SQL scripts.
Syntax: start <filename_name>.sql
|
|
HOST
|
This
will be used to interact with the OS level from SQL.
Syntax: Host [operation]
|
|
SHOW
|
Using
this, you can see several commands that use the set command and status.
Syntax: Show all | <set_command>
|
|
RUN
|
This
will runs the command in the buffer.
Syntax:Run
| /
|
|
STORE
|
This
will save all the set command statuses in a file.
Syntax:Store
set <filename>.[extension] [create] | [replace] | [append]
|
|
FOLD_AFTER
|
This
will fold the columns one after the other.
Syntax:Column
<column_name> fold_after [no_of_lines]
|
|
FOLD_BEFORE
|
This
will fold the columns one before the other.
Syntax:Column
<column_name> fold_before [no_of_lines]
|
|
DEFINE
|
This
will give the list of all the variables currently defined.
Syntax:Define
[variable_name]
|
|
|
SET COMMANDS
|
|
LINESIZE
|
This
will be used to set the linesize. Default linesize is 80.
Syntax: Set linesize <value>
|
|
PAGESIZE
|
This
will be used to set the pagesize. Default pagesize is 14.
Syntax: Set pagesize <value>
|
|
DESCRIBE
|
This
will be used to see the object’s structure.
Syntax: Describe or desc <object_name>
|
|
PAUSE
|
Syntax: Set pause on | off
Ex: SQL> set pause on
|
|
FEEDBACK
|
This will give the information
regarding howmany rows you selected the object.
Syntax: Set feedback <value>
|
|
HEADING
|
If
you want to display data without headings, then you can achieve with this. By
default heading is on.
Syntax: Set heading on | off
|
|
SERVEROUTPUT
|
This
will be used to display the output of the PL/SQL programs. By default this
will be off.
Syntax: Set serveroutput on | off
|
|
TIME
|
This
will be used to display the time. By default this will be off.
Syntax: Set time on | off
|
|
TIMING
|
This
will give the time taken to execute the current SQL statement. By default
this will be off.
Syntax: Set timing on | off
|
|
SQLPROMPT
|
This
will be used to change the SQL prompt.
Syntax: Set sqlprompt <prompt>
|
|
SQLCASE
|
This
will be used to change the case of the SQL statements. By default the case is
mixed.
Syntax: Set sqlcase upper | mixed | lower
|
|
SQLTERMINATOR
|
This
will be used to change the terminator of the SQL statements. By default the
terminator is ;.
Syntax:Set
sqlterminator <termination_character>
|
|
DEFINE
|
By default if the & character
finds then it will treat as bind variable and ask for the input
Syntax: Set define on | off
|
|
NEWPAGE
|
This
will shows how many blank lines will be left before the report. By default it
will leave one blank line.
Syntax: Set newpage <value>
|
|
HEADSEP
|
This
allow you to indicate where you want to break a page title or a column
heading that runs longer than one line. The default heading separator is
vertical bar (|).
Syntax: Set headsep <separation_char>
|
|
ECHO
|
When
using a bind variable, the SQL statement is maintained by echo. By default
this is off.
Syntax: Set echo on | off
|
|
VERIFY
|
When
using a bind variable, the old and new statements will be maintained by
verify. By default this is on.
Syntax: Set verify on | off
|
