COMPLETE
ORACLE SQL BLOG FOR DEVELOPERS AND
ADMINISTRATORS
In this Blog i am going to explain Complete Oracle SQL Blog for Developers and Administrators, short for Structured Query Language is pronounced Ess Queue el and is a simple non procedural language that lets you store and retrieve data in a relational database.
Data types:
Data type means the format in which we have to store a particular
value in a field.
The main data types in oracle are
1.
char(n):
Fixed-length character data (string), n characters long. The maximum
size for n is 255 bytes(2000 in Oracle8). Note that a string of type char is
always padded on right with blanks to full length of n. (+ can be memory
consuming).
Example: char(40)
2.
varchar2
(n):
Variable-length character string. The maximum size for n is 2000.
Only the bytes used
for a string require storage.
Example: varchar2 (80)
3. number (o, d):
Numeric data type for integers and real. o =
overall number of digits,
d= number of digits to the right of the decimal point.
Maximum values: o =38, d= −84 to +127.
Examples: number (8),
number (5, 2)
Note that, e.g., number (5, 2) cannot contain anything larger than
999.99
without resulting in an error. Data types derived from number are
integer, decimal, small int and real.
4. Date:
Date data type for storing date and time. The default format for
date is: DD-MMM-YY.
Examples: ’13-OCT-94’,
’07-JAN-98’
Languages in SQL:
SQL consists of 5 languages.
1. Data Definition Language.
2. Data Manipulation Language.
3. Data Control Language.
4. Data Retrieval Language.
5. Transaction Control Language.
1.
Data
Definition Language:
Data Definition Language (DDL) that is used to define the
structural
characteristics of your databases. The following statements create
or remove databases and tables or modify the structure of tables:
· create database creates a new database.
· drop database removes a database and any tables
it contains.
· create table creates a new table.
· drop table removes a table and any data it
contains.
· alter table modifies the structure of an
existing table.
Creating a table:
create table <tablename>(attribute-name datatype (size), attribute-name datatype (size),“ “
);
To view the structure of a table:
Syn: desc <table name>;
To see all the table names in a particular database:
Syn: select * from tab;
Dropping a table:
Syn: drop table <table name>;
2.
Data
Manipulation Language:
After a table has been created using the create table command,
tuples can be inserted into the table, or tuples can be deleted or
modified
a. Insert:-
The most simple way to insert a tuple into a table is to use the
insert statement
Syn: insert into <table> [(<column i. . . column j>)]
Values (<value i. . . value j>);
Ex: insert into project
(pno, pname, persons, budget, pstart) values (313, ’dbs’, 4,
150000.42, ’10-oct-94’);
or
insert into project values (313, ’dbs’, 7411, null, 150000.42,
’10-oct-94’, null);
b. Update:
For modifying attribute values of (some) tuples in a table, we use
the update statement.
Syn:
Update <table> set <column i> = <expression i>.
. . <column j> = <expression j>[where
<condition>];
Ex:
update emp set job = ’manager’, deptno = 20, sal = sal +1000 where
ename = ‘chaitanya’;
c. Delete:
All or selected tuples can be deleted from a table using the
delete command:
Syn:
delete from <table> [where <condition>];
Ex: delete from PROJECT where PEND < sysdate>;
3.
Data
Control Language:
A Data Control Language (DCL) is a computer language and a subset
of
SQL, used to control access to
data in a database.
Examples of DCL commands include:
grant to allow specified users to perform
specified tasks.
revoke to cancel previously granted or denied
permissions.
4.
Data
Retrieval Language:
This language is used to retrieve the data from the table in the
database .The command
under this category is “select”.
Syn:
Select * from <table name>;
5.
Transaction
Control Language:
A Transaction Control Language (TCL) is a compute language and a
subset of SQL, used
to control transactional processing in a database. Examples of TCL
commands include:
commit to apply the transaction.
rollback to undo all changes of a transaction.
savepoint to divide the transaction into smaller
sections.
CONSTRAINTS
Constraint:
Constraints are mainly used to restrict the table under certain
conditions.
Several types of Oracle constraints can be applied to Oracle
tables to enforce
data integrity, including:
· Oracle "Check" Constraint:
This constraint validates incoming columns at row insert time.
For example, rather than having an application verify that all
occurrences of region are North,South, East, or West, an Oracle check
constraint can be added to the table definition to ensure the validity of the
region column.
· Not Null Constraint:
This Oracle constraint is used to specify that a column may never
contain
a NULL value. This is enforced at SQL insert and update time.
Syntax for creating a not null constraint at the time of creation
Of a table:
Create table <table name> (attribute name data type(size)
not null);
Syntax for creating a not null constraint after creation of a
table:
Alter table <table name> modify (<attribute name> data
type (size) not null);
DROPPING A CONSTRAINT:
Alter table <table name> drop constraint <constraint
name>;
DISABLE A CONSTRAINT:
Alter table <table name> disable constraint <constraint
name>;
ENABLE A CONSTRAINT:
Alter table <table name> enable constraint <constraint
name>;
· Primary Key Constraint:
This Oracle constraint is used to identify the primary key for a
table.
This operation requires that the primary columns are unique, and
this Oracle constraint will create a unique index on the target primary key.
Syntax for creating a primary key constraint at the time of
creation Of a table:
Create table <table name> (attribute name data type (size)
primary key);
Syntax for creating a primary key constraint after creation of a
table:
Alter table <table name> add primary key (attribute name1,
attribute name2);
DROPPING A CONSTRAINT:
Alter table <table name> drop constraint <constraint
name>;
DISABLE A CONSTRAINT:
Alter table <table name> disable constraint <constraint
name>;
ENABLE A CONSTRAINT:
Alter table <table name> enable constraint <constraint
name>;
References Constraint:
This is the foreign key constraint as implemented by Oracle. A
references constraint is only applied at SQL INSERT and DELETE times. At SQL
DELETE time, the references Oracle constraint can be used to ensure that an
employee is not deleted, if rows still exist in the DEPENDENT table.
Syntax for creating a Foreign key constraint after creation of a table:
alter table (table name) add constraint (foreign key constraint
name) foreign key ( field name )
references primary_table_name (primary_table_primary_index_field);
DROPPING A CONSTRAINT:
Alter table <table name> drop constraint <constraint
name>;
DISABLE A CONSTRAINT:
Alter table <table name> disable constraint <constraint
name>;
ENABLE A CONSTRAINT:
Alter table <table name> enable constraint <constraint
name>;
· Unique Constraint:
This Oracle constraint is used to ensure that all column values
within a
table never contain a duplicate entry.
Syntax for creating a unique constraint at the time of creation Of
a table:
Create table <table name> (attribute name data type (size)
unique);
Syntax for creating a unique constraint after creation of a table:
Alter table <table name> add unique (attribute name1,
attribute name2);
DROPPING A CONSTRAINT:
Alter table <table name> drop constraint <constraint
name>;
DISABLE A CONSTRAINT:
Alter table <table name> disable constraint <constraint
name>;
ENABLE A CONSTRAINT:
Alter table <table name> enable constraint <constraint
name>;
DEFAULT:--
This command is used to set default values for an attribute i.e,
whenever the user enters any value into that default attribute then the value
which is entered will be there in the table. If the user doesn’t enter any
value into that default attribute.., then the default value will be present in
the table.
Syntax for creating a table using default
option…
create table <table name>(<att. name> data type(size)
default ‘value’);
eg. create table student(sno number(5),sname varchar2(20),gender char(10)
default ‘male’, or name varchar2(20) default ‘CHAITANYA’);
The value should be in uppercase letters only…
Syntax using alter after creation creation
of table…
Alter table <tablename> modify(<att.name>
datatype(size) default ‘VALUE’, <att.name>
datatype(size) default ‘VALUE’);
Alter table student modify(gender char(10) default ‘MALE’, orname
varchar2(20) default ‘CHAITANYA’);
It wont disturb the present value in the table,It sets the default
value where the value is null in the table.
TO CHANGE THE DEFAULT VALUE,
Alter table student modify(sno number(5) default 10);
TO REMOVE DEFAULT VALUE,
Alter table student modify(sno number(5) default ‘’);
FLASH BACK:
This command is used to bring the dropped tables again into the
data base. All the table which are
dropped in the database will be stored in a oracle predefined
table called as “recycle bin”. We can get back the dropped table unless and
until that table is present in the recycle bin.. oracle allows the users to
clear the dropped tables from the recycle bin.
Syntax for bringing the dropped table from the database
flashback table <tablename> to before drop;
flashback table student to before drop;
Syntax for viewing all the dropped tables in the
database
show recylebin or
select * from recyclebin;
Syntax for removing all the tables from the recyclebin
purge recyclebin;
Syntax for removing particular tables from the
recyclebin
purge table <tablename>
purge table student;
Syntax for renaming a table at the time of bringing
from recyclebin
flashback table <tablename> to before drop rename to <new
tablename>;
flashback table student to before drop rename to student1;
Syntax for removing a table permanently
from the database without going into the recyclebin
drop table <tablename> purge;
drop table student purge;
COUNT:
This command is used to display the total count of numbers of
records present in a table.
select count(*) from <tablename>;
select count(sno) from <tablename>;
select count(*),count(sno) from student;
ROWNUMBER:
Every record inserted into a table will be having a rownumber.By
using this row number,
we can display particular range of records in a table.the
operators which are applicable for rownumbers is <,<= and =(only for
first row)
The operators which are not applicable for rownumber are >,>=
and =(other than first row)
q) want to display 1st five records in a table??
select * from <tablename> where rownum<=5;
ROWID:
Rowid means whenever the user inserts one record into a table,then
oracle by default
creates one unique rowid for that row…this rowid is 16 digit hexa
value.a user can differentiate “n” no.of records by using the rowid….
Syntax to see the rowid value for a
particular record
selelct rowid <att. name> from <tablename>;
Syntax to delete record for a particular
table
delete from <tablename> where rowid-“_______________”;
DISTINCT:
This command is used to display non repeated values in a table.
select distinct(sname) from student;
select distinct(att.name) from <tablename>;
select distinct sno,sname from student;
ORDER BY:
This command is used to display the records of a particular table
in either ascending order or in descending order of particular attribute…By
default,It will display in ascending order…
q) want to display all the records of student table in ascending
order of their marks??
Select * from student order by marks;
q) want to display all the records of student table in descending
order of their marks??
Select * from student order by marks desc;
OPERATORS:
logical and,or,not
select * from emp where salary=5000 and ename=’CHAITANTA’;
select * from emp where salary =5000 or ename=’CHAITANYA’;
select * from emp where not salary=5000;
ARITHEMATIC +,-,*,/
update emp set
salary=salary+5000;
update emp set
salary=salary-5000;
MISCLENEOUS
between,not
between,is,is not,in,not in,like,not like between/not between
select * from emp where salary between 2000 and 5000;
select * from emp where salary not between 2000 and 5000;
IS/IS NOT
select * from student where sno is null;
select * from student where sname is null;
select * from student where sno = ‘ ’;
select * from student where sno is not null;
select * from student where sno< >’’;
IN/NOT IN
select * from student where sno in(1,2,4);
select * from student where sno not in(1,2,4);
LIKE/NOT LIKE
select * from tab where tname like ‘n%’;
it display the tables which starts with n
select * from tab where tname like ‘%n’;
it display the tables which ends with n
select * from tab where tname like ‘_%n’;
it display the tables which sum characters ends
with n
select * from tab where tname like ‘_ _ _’;
it display the tables which are having 3 characters
select * from tab where tname not like ‘n%’;
it display the tables which not starting with n
select * from tab where tname like ‘%abc%’;
it display the tables which starts with some character and ends with
other and having abc in the middle.
ALIAS:
This command is used to give alias names for a particular
attribute…
select sname as “chaitanya” from student;
It display the column names with alias name but not stores with
that alias name.
We can also write as follows
select sname “chaitanya” from student;
select sname chaitanya from student;
CONCATENATION:
This command is used to append two strings together. the symbol
used to concatenate is ||
select ‘my name is ‘||ename||’ with eno ‘||eno||’ earning a salary
of ‘||salary from emp;
DECODE:
Decode is used to differentiate between any two types of things
and display the appropriate one. It works as an if condition in C language.
select decode(gender,’m’,’mr’,’miss’) from emp;
It will display MR in decode if the gender is M otherwise it will
show MISS
CASE:
This operator is used to call dynamically a particular value in
that attribute and display
another name.
select sname,case when ‘abc’ then ‘name staring with a’
when ‘pqr’ then ‘name staring with p’
else ‘name not staring with a and p’
end from emp;
ALL & ANY:
ALL:
IT display the records of a table which satisfies all the
conditions in the given query.
select * from student where sno>all(3,4);
ANY:
It displays the records of a particular table which satisfies
anyone condition in the given
query.
select * from student where sno>any(3,4);
FUNCTIONS
FUNCTIONS are classified into 4 types.
1.date functions
2.string functions
3.mathematical functions
4.aggregate functions
Date Functions:
Dual:-- Dual is a dummy table which will be created in the database when
oracle is loaded.
q) how to see today’s date??
select sysdate from dual;
q) how to display today’s date along with time??
select systimestamp from dual;
add_months:
This function is used to add ‘n’ no. of months to the current date
or any date. This function is also used to subtract n no.of months to the given
date.
select add_months(jdate,6) from emp;
select add_months(sysdate,6) from dual;
select add_months(sysdate,-6) from dual;
months_between:
This function is used to display the difference between n no. of
months between two dates.
select months_between(sysdate,jdate) from emp;
select round(months_between(sysdate,jdate) from emp;
last_day:
This function is used to display the last day of the given months.
select last_day(sysdate) from dual;
select last_day(’01-feb-10’) from dual;
next_day:
This function is used to display the next week’s date.
select next_day(sysdate,’mon’) from dual;
this will display the first upcoming Monday
select next_day(sysdate+3,’mon’) from dual;
This will display the next week Monday date…
formats in date functions
d - no. of day in a week
dd - no. of day in a month
ddd - no. of day in a year
day - full name of the day in a week
dy - it will show 3 letters of day in a week
mon - 3 letters of the month in a year
month - full name of month in a year
mm - no of month in a year
q - no of quarter in which the month is existing
y - last digit of the year
yy - last 2 digits of the year
yyy - last 3 digits of the year
yyyy - last 4 digits of the year
hh - no of hours in 12 digit format
hh24 - no of hours in 24 digit format
mi - no. of minites
ss - no of seconds
sp - spelling of given month no,date no,year no
th - suffix of date
spth - spelling with suffix
w - no of week in the month
ww - no of week in the year
the 2 date conversion functions which are used in oracle are
to_char and to_date.
To_char:
this function is used to convert the standard date format values
into the user defined date format
select to_char(jdate,’dd/mm/yy’) from emp;
select to_char(‘jdate,’d’) from emp;
select to_char(‘jdate,’dd’) from emp;
select to_char(‘jdate,’ddd’) from emp;
select to_char(‘jdate,’day’) from emp;
select to_char(‘jdate,’mm’) from emp;
select to_char(‘jdate,’mon’) from emp;
select to_char(‘jdate,’q’) from emp;
select to_char(‘jdate,’y’) from emp;
select to_char(‘jdate,’yy’) from emp;
select to_char(‘jdate,’yyy’) from emp;
select to_char(‘jdate,’yyyy’) from emp;
select to_char(‘jdate,’hh’) from emp;
select to_char(‘jdate,’hh24’) from emp;
select to_char(‘jdate,’mi’) from emp;
select to_char(‘jdate,’ss’) from emp;
select to_char(‘jdate,’w’) from emp;
select to_char(‘jdate,’ww’) from emp;
select to_char(‘jdate,’ddsp’) from emp;
select to_char(‘jdate,’ddth’) from emp;
TO_DATE:
This function is used to convert the user defined date into system
defined date and it again converts the system defined date into the user
defined date format..
select to_char(to_date(’31-dec-10’),’dd/mm/yy’) from dual;
GREATEST AND LEAST
GREATEST:
This function is used to display the greatest date among the given
dates.
select greatest(to_date(’16-jan-09’),to_date(’17-jul-09’)) from
dual;
o/p:- 17-jul-09
select greatest(’16-jan-09’,’17-jul-09’) from dual;
o/p:- 17-jul-09
LEAST:
This function is used to display the least date among the given
dates.
select least(to_date(’16-jan-09’),to_date(’17-jul-09’)) from dual;
o/p:- 16-jan-09
select least(’16-jan-09’,’17-jul-09’) from dual;
o/p:- 16-jan-09
STRING FUNCTIONS:
LENGTH:
This function is used to display the total length of a given
string.
select length(‘chaitanya’) from dual;
select length(sname),sname from student;
select length(fname||sname) from student;
INITCAP:
This function is used to display the first letter in the word as a
capital letter
select initcap(‘chaitanya chakravarthy divakala’) from dual;
O/P : Chaitanya Chakravarthy Divakala
UPPER:
This function is used to convert the lower case letters of the
given string into upper case
letters.
select upper(‘chaitanya’) from dual;
o/p: CHAITANYA
LOWER:
This function is used to convert the upper case letters of given
string into lower case letters
select lower(‘CHAITANYA’) from dual;
o/p : Chaitanya
ASCII:
This function is used to convert the given character into
corresponding ascii codes.
select ascii(‘a’) from dual;
o/p: 65
CHR:
This function is used to convert the given ascii codes into
corresponding character.
select chr(65) from dual;
o/p : a
LPAD:
This function is used to add a string to left side ‘n’ no. of
times to the given length of the
string.
select lpad(sname,10,”*”) from student;
select lpad(‘chaitanya’,10,”*”) from dual;
select lpad(sname,10,”*$”) from student;
RPAD:
This function is used to add a string to right side ‘n’ no. of times
to the given length of the string.
select rpad(sname,10,”*”) from student;
select rpad(‘chaitanya’,10,”*”) from dual;
select rpad(sname,10,”*$”) from student;
SOUNDEX:
This function is used to display all the records
which will sound with the similar name.
select * from student where soundex(sname)=soundex(‘chaitanya’);
TRIM:
Trim consists of 3 types
1.ltrim
2.rtrim
3.trim
1.LTRIM:
This function is used to remove the required characters from the
left side.
select ltrim(‘chaitanya’,’c’) from dual;
o/p: haitanya
select ltrim(‘chaitanya’.’cha’) from dual;
o/p: itanya
select ltrim(‘chaitanya’,’ch’) from dual;
o/p :aitanya
2.RTRIM:
This function is used to remove the required characters from the
right side.
select rtrim(‘chaitanya’,’c’) from dual;
o/p: chaitanya
select rtrim(‘chaitanya’.’nya’) from dual;
o/p:chaita
select rtrim(‘chaitanya’,’tanya’) from dual;
o/p:chai
TRIM:
Both ltrim and rtrim will trim by character wise but not by string
wise…….
It consists of 3 types
1.leading
2.trailing
3.both
1.LEADING:
This function is used to remove a character from left side
select trim(leading ‘c’ from ‘chaitanya’) from dual;
o/p: haitanya
2.TRAILING:
This function is used to remove a character from
right side
select trim(trailing ‘a’ from ‘chaitanya’) from dual;
o/p: chaitany
3.BOTH:
This function is used to remove a character from both sides
select trim(both ‘m’ from ‘manasa’) from dual;
o/p: anasa
select trim( ‘m’ from ‘manasa’) from dual;
o/p: anasa
TRANSLATE:
This function is used to translate the letters
in the given string with the user defined
letters. this function is letter based.
select translate(‘dccvizag’,’dvg’,’abc’) from dual;
o/p: accbizac
select translate('yayay','y','k') from dual;
o/p: kakak
REPLACE:
This function is used to replace a particular
word in the given string with the
User defined word. this function is strictly a word based.
select replace(‘dccvizag’,’dcc’,’hyd’)from dual;
o/p:hydvizag
NVL:
This function is used to display the null values
in an attribute with the userdefined data..
select nvl(sname,'dcc') from student;
NVL2:
This function is used to display the names in an attribute with
one userdefined name and the null values in the same attribute with another
user defined name.
select nvl2(sname,'vsp','hyd') from student;
INSTR:
This function is used to return the position of
a particular character in a string.
select instr('visakhapatnam','a') from dual;
o/p: 4
it also used to return the position of a character not only from
the beginning...,
select instr('visakhapatnam','a',5) from dual;
o/p: 7
SUBSTR:
This function is used to return the string from
any postion upto end of the string.
select substr('viskahapatnam',4) from dual;
o/p: akhapatnam
This function is also used to display the string from mth position
to nth position
select substr('visakhapatnam',4,7) from dual;
o/p: akhapat
ARTHEMATIC FUNCTIONS
ABS:
This function is used to convert all the
negative numbers into positive numbers and display on the screen
select abs(15) from dual; 15
select abs(-15) from dual; 15
CEIL:
This function is used to display the next value
select ceil(12.2) from dual; 13
select ceil(12.7) from dual; 13
select ceil(12.275) from dual; 13
FLOOR:
This function is used to display the same value
select floor(12.2) from dual; 12
select floor(12.7) from dual; 12
ROUND:
This function is used to round the value to 'n'
no.of positions
select round(12.257,2) from dual; 12.26
select round(12.72,1) from dual; 12.7
select round(12.247,1) from dual; 12.2
TRUNC:
This function is used to cut the given number to some user defined
number.
select trunc(12.757,2) from dual; 12.75
select trunc(12.72,1) from dual; 12.7
MOD:
This function is used to display the remainder
between two numbers.
select mod(5,2) from dual; 1
select mod(2,5) from dual; 2
POWER:
This function is used to display the power of a given number.
select power(5,2) from dual; 25
SQRT:
This function is used to display the square root
of a given number.
select sqrt(81) from dual; 9
AGGREGATE FUNCTIONS
MAX:
This function is used to display the maximum
salary or the highest value of a particular
attribute.
select max(sal) from emp;
MIN:
This function is used to display the minimum
salary or the lowest value of a particular attribute.
select min(sal) from emp;
AVG:
This function is used to display the average
salary
select avg(sal) from emp;
SUM:
This function is used to display the total
salary
select sum(sal) from emp;
GROUPBY:
guidelines for using group functions
• distinct makes the function consider only nonduplicate values;
all makes it consider every
value including duplicates. the default is all and therefore does
not need to be specified.
• the data types for the functions with an expr argument may be
char, varchar2, number, or date.
• all group functions ignore null values. to substitute a value
for null values, use the nvl, nvl2, or coalesce functions.
• the oracle server implicitly sorts the result set in ascending
order when using a group by clause.
to override this default ordering, desc can be used in an order by
clause.
HAVING:
The having clause to
specify which groups are to be displayed, and thus, you further restrict the groups
on the basis of aggregate information.
in the syntax:
group_condition restricts the groups of rows returned to those
groups for which
specified condition is true the oracle server performs the
following steps when you use the having clause:
1. rows are grouped.
2. the group function is applied to the group.
3. the groups that match the criteria in the having clause are
displayed.
the having clause can precede the group by clause, but it is
recommended that you place the groupby
clause first because that is more logical. groups are formed and
group functions are calculated before
the having clause is applied to the groups in the select list.
VIEWS:
A view is a virtual table defined by a query. it provides a
mechanism to create alternate
ways of working with the data in a database. a view acts much like
a table. we can query it with a select, and some views even allow insert,
update,and delete.
however, a view doesn’t have any data. all of its data are ultimately
derived from tables all of its data are ultimately derived from tables like
those we created in views are similar to derived tables, except that views are
de.ned once and can be used in many queries.
syntax: create a view using the create view command.
create view <view name> [(<column list>)] as <select statement>
this creates a view named <view name>. the column names/types and data for the view
are determined by the result table derived by executing <select statement>.
let’s create a view showing the ingredients (ingredient id,
inventory, and inventory value) supplied to us by veggies_r_us.
example:
create view vrs as select ingredientid, name, inventory, inventory
* unitprice as value from ingredients
i, vendors v where i.vendorid = v.vendorid and companyname =
'veggies_r_us';
Note: this views may contain expressions and even simple literals.
display: vrs
ingredientid name inventory value
letus lettuce 200 2.00
pickl pickle 800 32.00
tomto tomato 15 0.45
Note : A view’s select statement
may refer to other views. because the view is just a virtual table, any changes
to the base tables are instantly reflected in the view data.
example:update the tomato inventory
update ingredients
set inventory = inventory * 2
where ingredientid = 'tomto';
why to use a view ?
views have several uses
1)usability
2)security
3)reduced dependency
updating views:
standard for a view to be updatable, not contain distinct
1)not reference the same column twice in the select clause
2)not have a group by or having clause
3)not contain union, except, or intersect
4)contain attributes from only one table
5)have exactly one row in a base table that corresponds to each
row in the view
updating through views :
update vrs set inventory = inventory * 2;
inserting through view:
insert into vrs(ingredientid, name, inventory) values 'newin','new
ingredient',100);
drop view
drop view <view name> [cascade | restrict]
Advantages of views
• restrict database access
• simplify queries
• provide data independence
• provide multiple views of the same data
• can be removed without affecting the underlying data
view options
• can be a simple view, based on one table
• can be a complex view based on more than one table or view can
contain groups of functions
• can replace other views with the same name
• can contain a check constraint
• can be read-only
SYNONYMS:
Synonyms are used to create a duplicate copy of a table.while creating
a synonyms there is no possibility to use a where condition.
it cannot capture the result of any sql query in the synonyms
which can be possible in views.
create a synonyms:
syn: create synonyms <synonym name> for <table name>;
eg: create synonym emp_syn for emp;
drop a synonyms:
syn:drop synonym <synonym name>;
eg: drop synonym emp_syn;
SEQUENCES:
Sequence is an independent object and used with in a table that
required for generating
numbers in either assending or desending order. oracle provides an
object as sequence and providing intervels between the numbers.In a table maximum
information required for generating a number.simillarly minimum information
required for generating numbers using sequencemust be a starting number.increment
value for generating the next number.
Creating a sequence:
create sequence <sequence name>
start with <value>
increment by <value>
max value<value>
min value <value>
cycle
order
cache;
eg: create sequence chaitanya_seq start with 200
increment by 2
max value 450
min value 200
cycle;
sequence created
confirming a sequence:verify your sequence values in the
user_sequences data dictionary table.
Note : verify a sequence value in user_sequence
the last_number column displays the next available sequence number
if nocache is specified.
Rules for using n extval and currval:
The select list of a subquery in an insert statement
2)select list of a select statement that is not part of a subquery
1)the select list of a view
2)a select statement with group by, having, or order by clauses
and with the distinctkeyword
views the current value for sequence:
select dept_deptid_seq.currval from dual;
o/p:- currval
---------
120
Caching sequence values:
Cache sequences in memory to provide faster access to those sequence
values,cache is populated the first cache is populated the first each request
for the next sequence value is retrieved from the cached
viewing the next available sequence value without incrementing it if
the sequence was created with nocache, it is possible to view the next
available sequence value
without incrementing it by querying the user_sequences table.
Altering a sequence:
modify it by using the alter sequence statement.
syntax:
alter sequence sequence
increment by n
{maxvalue n | nomaxvalue}
{minvalue n | nominvalue}
{cycle | nocycle}
{cache n | nocache};
eg:
alter sequence dept_deptid_seq
increment by 20
maxvalue 90
nocache
nocycle;
Removing a sequence:-
to remove a sequence from the data dictionary, use the drop
sequence statement.
syntax: drop sequence <sequence name>;
INDEXES:
An oracle server index is a schema object that can speed up the
retrieval of rows by using a pointer.indexes can be created explicitly or
automatically. an index provides direct
and fast access to rows in a table. its purpose is to reduce the necessity of
disk i/oby using an indexed path to locate data quickly. the index is used and
maintained
automatically by the oracle server. once an index is created, no
direct activity is required by the user.
indexes are logically and physically independent of the table they
index. It is created or dropped at any time and have no effect on the base
tables or other indexes.
how are index created a unique index is created automatically when
you define
a primary key or unique constraint in a table definition.oracle
allows us the creation of two types
1) duplicate index
2) unique index
creation of duplicate index : an index created on single attibute
of a table is called as simple index.it
accepts duplicate values for the indexed column.
(simple )
syn: create index <index name> on <table name>
<column-name>
eg: create index emp_ind on emp(sno);
Composite index:
An index created on more than one attibute of a table is called as
composite index.it accepts
duplicate values for the indexed column.
syn: create index <index name> on <table name>
<column name>.<column name>............;
eg: create index emp_index on emp(sno,sname...);
Creating of simple unique:
syn : create unique index <index name> on <table name>
(<column name>);
eg: create unique index emp_index on emp (sno);
Creating of composite unique index:-
syn:
create unique index <index name> on <tablename>(<colunn
name><column name>........);
eg:create unique index emp_index on emp(sno,sname.........);
Drop a index:-
syn :drop index <index name>;
ex: drop index emp_index;
View the contents of the index:-
1)by using user_index we can view the index name and table
name,but it cannot view the
column names on which the index is placed
2)by using user_ ind_ columns we can view the columns on which the
index is crated.
Disable the index:-
syn : alter index <indexname> unuable;
ex: alter index emp_ind unusable;
Enabling the index:-
syn: alter index <index name> enable;
ex: alter index emp_ind enable;
