This url is well documented ..
Moving to Sqllite due to easy access into api
MySql Tutorial
PL/SQL Tutorial in MySQL
Moving to Sqllite due to easy access into api
SQLite Tutorial
Basic DataBase InfoMySql Tutorial
PL/SQL Tutorial in MySQL
PLSQL Tutorial
Online Interpreter
Online Interpreter
Stored object:
object that are stored in the data base as tables,index and constraints
View
emp table;
empno ename sal deptno
1 A 5000 1
2 B 6000 1
a view is a handle to a table it is like a pointer but c pointer are on the RAM this is
hard disk pointer
for indirect access to a table views are created.
The main reason of the creation of view is for security purpose specially in multi user enviroment
create view v1
as
select empno,ename from emp;
select *from v1;
if you want to share your data with other users than giving permission on the tables.
Create view and give privillages to access those view so that other users can only see
restriceted column.
A view does not contain any data.
select statement and view name is stored in a system table.
only definiation is stored and data is not stored known as stored query.
select statement of the view is stored in the db in the compiled format that is what the
exe of the select statement
view is going to speed up your application becuase select statement stored in the
compiled format
Being a stored object you are able to access view from anywhere
Advantage
security
when you say select *from tab where tab is not a table but its a view
352 system table are not tables actaually they are views
#insert a row into view
insert into v1 values (.....)
when you say insert its pass on the data to the table becuase its a pointer
DML operation can be performed on the view will affect the base table
constraints which are specified on the base will be enforced when you insert
through the view
create view v1
as
select *from emp where deptno =1;
select *from v1 will show only first three records
here you are restricting row access
view is only restrict column acccess on the row access
In the above the query following query is allowed insert into v1 values (6,'f',6000,2)
The select will resttrict for dept no 1 where as insert ,update delete are not allowed
if you want so you use with check option
create view v1
as
select *from emp where deptno=1 with check option
with check option - achieves if end if check constraints for diffirent user create
diffirent views with check option
Force view
allow this view to be created even though a table does not exist.
The idea is to allow to create a view first and table after words like in the situation
where you are creating a table at the runtime.
create force view v1
as
select empno,ename,sal,deptno from emp;
Force view are created and stored in the db in the uncompiled format once the
table is created the view still remain in the uncompiled format when you
fire a query then the view compiled and stored into the db
drop view v1
Instead of droppping and recreating the view we have
create or replace view view v1 as select
desc v1
view based on the computed filed expression is not allowed unless you specify an
alias for virtual column
create or replace view v1
as
select ename,sal*12 annual from emp;
can only select from the above view
DML operation is not allowed like above view
view based on computed filed expression function is not allowed unless you specify
an alias for virtual function
view based on join
create or replace view v1
as
select dname,ename from emp,dept where dept.deptno =emp.deptno
For a view based on a join can only select from above
DML operation are not allowed
oracle 10g allows if you have a view based on joins it allows DML operation
to be performed
oracle 10 g great computing
an alias for virtual function
view based on join
create or replace view v1
as
select dname,ename from emp,dept where dept.deptno =emp.deptno
For a view based on a join can only select from above
DML operation are not allowed
oracle 10g allows if you have a view based on joins it allows DML operation
to be performed
oracle 10 g great computing
ALTER view option used when you want to recompile a view
why recompile
if some changes are made in a base table to reflect those chnages
in the view recompile the view
alter view v1 compile
uses of views
complex query can be stored in your view definition
when you drop a table the view inside a database as a invalid view
stored object
objects that are stored in the database
Sequences
used for automatic code generation
create sequence abc;
insert into emp(abc.nextval,'a',5000);
insert into emp(abc.nextval,'b',6000)'
exit;
only numeric sequence
why recompile
if some changes are made in a base table to reflect those chnages
in the view recompile the view
alter view v1 compile
uses of views
complex query can be stored in your view definition
when you drop a table the view inside a database as a invalid view
stored object
objects that are stored in the database
Sequences
used for automatic code generation
create sequence abc;
insert into emp(abc.nextval,'a',5000);
insert into emp(abc.nextval,'b',6000)'
exit;
only numeric sequence
independent objects and it has nothing to with the table
create sequence abc
minvalue 4 -- start from 4
maxvalue 1000 will work upto 1000
after reaching 1000 if you ty to use sequence you can get error
To use the some sequence again
drop sequence abc;
create sequence abc
minvalue 1000
maxvalue 5000
instead of dropping and recreating
use alter
alter sequence abc
maxvalue 5000
select abc.cuurentval from dual;
return the current value of seq
create sequence abc
minvalue 4
maxvalue 1000
cycle
after reaching to the 1000 the sequence will again start from 4
create sequence abc
minvalue 1
maxvalue 1000 start with 4
cycle;
start with 4 reaches 1000 & again restart with 1
create sequence abc
minvalue 1
maxvalue 1000
increment by 2
sequence will increment by 2 default value is 1 this sequence will be valid upto only 999
create sequence abc
minvalue 1
maxvalue 1000
increment by -1
The sequence will be in the reverse order 1000,999,998
Rollback commit has not effect on the sequence
Whenever a client make a call to a sever ram for the sequence orcale brings 20 no
into a server RAM which speepds up the processing
if you are not satisfied with cache default value 20 you can specify it.
create sequence abc
minvalue 1
maxvalue 100000
cache 1000
when the first time client make a call to a server for a sequence 1000 no of sequence
are generated into the server RAM
create sequence abc
minvalue 1
maxvalue 10000
no cache
By doing no cache only on value is generated and transfered from the server RAM to
server hard disk at a time
select *from user_sequences
Anything that you do with create command in a stored object
Multi user enviroment
Multiple user can create tables with same name becuase tables are stored as username
Table name
ora1.emp
ora2.emp
due to this there is no such a thing filename.extension in oracle
Unlike os there is no such a thing like filename, path folder in a oracle
There is no such thing like the table you have created should go on c drive
it goes in the data base
Assume that ora1 has granted permission to ora2 if ora2 wants to work on the orcale
table the query is
select *from ora1-emp
Similiar for insert update delete & soon this concept is applied on all the stored objets
username objectname
common for all RDBMS
here ora1 and ora2 are user of same db
suppose oral is on diff db and ora2 is on diff db then you have to say that
dbname.username.objectname
Again here we are assuming that abses are on the same server
if the databases are on the two diffirent server dblinkname.dbname.username.object
by default when you create an object it is going to your schema if you want to have
object created in other schema you should have permission
Synonyms
same meaning
handle to the schema object
if multiuser are doing the same things but can not use the same program they must
have diffirent program to do the same task. a solution to this enviroment
Create synonym semp for scott.emp
select *from semp;
insert into semp values
update semp set
But the point is every user has to create its own synonym named semp including
scott then you can have a single program for all user provided all privilliges
has been granted.
synonyms are used in the multiuser enviroment
Instead of every user creating their own synonyms you can create a public
synonyms you can create a public synonyms which will be avaliable to all
user in the data base
The name of the public synonnymous in the data base has to be unique
and finally write a program for the public synonymous which will be created
by all the user in the multi user enviroment
create public synonym semp for scott.emp;
Now consider scott has created a sequence and jack wants to use that
a insert into semp values
This is the tedious job as above it is solution is to create a synonym.
create synonym sabc for scott.abc;
Insert into semp values sabc.nextval;
To find out all synons
create sequence abc
minvalue 4 -- start from 4
maxvalue 1000 will work upto 1000
after reaching 1000 if you ty to use sequence you can get error
To use the some sequence again
drop sequence abc;
create sequence abc
minvalue 1000
maxvalue 5000
instead of dropping and recreating
use alter
alter sequence abc
maxvalue 5000
select abc.cuurentval from dual;
return the current value of seq
create sequence abc
minvalue 4
maxvalue 1000
cycle
after reaching to the 1000 the sequence will again start from 4
create sequence abc
minvalue 1
maxvalue 1000 start with 4
cycle;
start with 4 reaches 1000 & again restart with 1
create sequence abc
minvalue 1
maxvalue 1000
increment by 2
sequence will increment by 2 default value is 1 this sequence will be valid upto only 999
create sequence abc
minvalue 1
maxvalue 1000
increment by -1
The sequence will be in the reverse order 1000,999,998
Rollback commit has not effect on the sequence
Whenever a client make a call to a sever ram for the sequence orcale brings 20 no
into a server RAM which speepds up the processing
if you are not satisfied with cache default value 20 you can specify it.
create sequence abc
minvalue 1
maxvalue 100000
cache 1000
when the first time client make a call to a server for a sequence 1000 no of sequence
are generated into the server RAM
create sequence abc
minvalue 1
maxvalue 10000
no cache
By doing no cache only on value is generated and transfered from the server RAM to
server hard disk at a time
select *from user_sequences
Anything that you do with create command in a stored object
Multi user enviroment
Multiple user can create tables with same name becuase tables are stored as username
Table name
ora1.emp
ora2.emp
due to this there is no such a thing filename.extension in oracle
Unlike os there is no such a thing like filename, path folder in a oracle
There is no such thing like the table you have created should go on c drive
it goes in the data base
Assume that ora1 has granted permission to ora2 if ora2 wants to work on the orcale
table the query is
select *from ora1-emp
Similiar for insert update delete & soon this concept is applied on all the stored objets
username objectname
common for all RDBMS
here ora1 and ora2 are user of same db
suppose oral is on diff db and ora2 is on diff db then you have to say that
dbname.username.objectname
Again here we are assuming that abses are on the same server
if the databases are on the two diffirent server dblinkname.dbname.username.object
by default when you create an object it is going to your schema if you want to have
object created in other schema you should have permission
Synonyms
same meaning
handle to the schema object
if multiuser are doing the same things but can not use the same program they must
have diffirent program to do the same task. a solution to this enviroment
Create synonym semp for scott.emp
select *from semp;
insert into semp values
update semp set
But the point is every user has to create its own synonym named semp including
scott then you can have a single program for all user provided all privilliges
has been granted.
synonyms are used in the multiuser enviroment
Instead of every user creating their own synonyms you can create a public
synonyms you can create a public synonyms which will be avaliable to all
user in the data base
The name of the public synonnymous in the data base has to be unique
and finally write a program for the public synonymous which will be created
by all the user in the multi user enviroment
create public synonym semp for scott.emp;
Now consider scott has created a sequence and jack wants to use that
a insert into semp values
This is the tedious job as above it is solution is to create a synonym.
create synonym sabc for scott.abc;
Insert into semp values sabc.nextval;
To find out all synons
select *frim user_synonyms;
this will show you all private synonyms and public those are available
to you.
When jack is creating synonym for view..v1
The oracle dos not check that whether the synonym is a valid or nt
at the time of creatin that is it does not check that schema object exist valid or not.
The idea is to create a public synonymms first start working on it and the create
user tables and views
You can make a synonym for synonym. There is no practical use of it but you can
create it.
Stored Procedures
this will show you all private synonyms and public those are available
to you.
When jack is creating synonym for view..v1
The oracle dos not check that whether the synonym is a valid or nt
at the time of creatin that is it does not check that schema object exist valid or not.
The idea is to create a public synonymms first start working on it and the create
user tables and views
You can make a synonym for synonym. There is no practical use of it but you can
create it.
Stored Procedures
These are global procedures
A procedure is a routine that needs a called and does not return any value
it is like a void function
stored in the db can call from any where like any program any slow any frontend which
is connected to a database can be called from sql*plus
stored in the database in the coomplied binary format it goes into the system table.
hiding code from user as they are in the complied format
Execution will be very fast.
you can convert your program in the executable format by writing your program
in the form of stored procedure.
when a make a call to a stored procedure to make oracle binaries
stored procedures abc into the server RAM so the execution of stored procedures
in the server RAM and after execution the server RAM gets freed. the result in less traffic
As the procedure executes in the server RAM its used for server data processing
In multi user enviroment for multiple clients only single copy of the
procedure is broguht into the server RAM and this copy is shared by
all the clients
Program code shared by all users
can not have two stored procedures with same same name
create or replace procedures abc
as
begin
intsert into emp values (1,'amit)
exception when
then
end.
Table
system table created by the oracle
oracle takes care of adding and removing table into tab table.
Total 352 system tables.
The complete set of 352 system table is known as data dictionary
These table store privilages,memory and requirement of other table and so on
Rows of the table are scattered all over the harddisk.They are fragement
Row id ---> Actual physical memory location on the harddisk where row is created
String of the 18 chracters which is understand by the oracle software
Rowid is in an encrypted format
No two rows of a table can have the same rowid
you can use rowid in the where clause of select statement
Ex select rowid,ename from emp where rowid='aaa ---upto 18'
Pratical use of rowid is to delete the duplicates as far as we are concerned
Delete *from emp where rowid=' '
Internally used by the oracle for managing the data
ex locking of the rows
rowid id is not the column of the table but can be used in select statement which
is why its known as Pseduo column
Select *frm emp where empn=1000
oracle goes to emp table server between it will go through all the record if the
oracle has found the request record but at this point oracle will not stop
it will scan all the records further and finally will return the result.
This is slow process
SQL indexes
Part of performance tuning query tuning to speed up
rows are scattered
to speed up search operation( for fater access)
index for empno
rowid empno
0001 1
0002 2
0003 3
0004 4
0005 5
To speed up a search operation you need to create an index based on the empno
Index is just like a table here it will store empno and corresponding rowid
Now here when you will fire a select query instead of searching table oracle
will search in index
When oracle finds requested data it will takes up a rowid and returns the results of the
query.
The data stored in the index in the form of ascending order so when oracle find the records belonging to empno=1 oracle will stop and will not scan further
To speed up select statement
indexes automatically invoked by oracle as and when required
Duplicates values will be stored in an index
indexes are automatically updated by oracle for all your DML operation
select *from emp where ename='B'
TO speed up select statement you need to create another index based on ename column
No upper limit on number of indexes per table
if you are having indexes on 50% of the column the 50% of data will be duplicated
Larger the number of indexes,slower be the DML operation that is the overhead only
select is faster.
You can not index long and long raw
select *from emp where empno =1 and deptno
employee no is department wise and you can have
1. two indexe one for empno and other for deptno
2. create one index having both the column empno and deptno is known as composite index
Rowid deptno empno
00001 1 1
00002 1 2
00003 1 3
00004 2 1
00005 2 2
Composite index
index based on the multiple columns
can combine upto 16 columns in a composite index
Index key
The column or set of the columns n whose basis the index has been created
The order of the columns in a composite index is important
The first column in a index is a priamry key index
The second column in a index is a secondary index key
The parent column should be first column and child column should be second column
null values are not started in a index
When/where to create an index
select statement with where clause you need to create an index
if your select statement retrives less than 25% of the table data then and only
then an index should be created otherwise not
Primary column is the best column for search/select hence the key columns should
always be indexed
3. Dept
Deptno Dname Loc
1 TRN Pune
2 Exp Delhi
3 MKTG calcutta
select dname,ename from emp,dept where dept.deptno=emp.deptno
-create two index
rowid deptno -derving index
1
2
3
common fields in join operation should always be indexed
-Create index abc on emp(empno);
-index name should be always unique
-once you create one index it will be permanet in your database
-Composite index
create index i_emp_dno_en on(deptno,empno)
-if you drop the table index will be automatically dropped
-if an index is based on some column and if you drop the column index will be automatically dropped
bydefault indexes are in ascending order but in progress indexes are in descending order
-To have descending index : create index i_emp_empno on emp(empno desc)
-In case of composite index you can have one column in ascending and other one in
descending order
-create unique index i_ep_empno on emp(empno);
-An unique index is same as normal index but it will not allow you to have duplicates
values in that column on which index is created
-if you table is having duplicates values for a certain column and you are trying to create
an unique index based on that column oracle gives an error message
-select *from user_indexes;
-user index is a system table in which all indexes are stored
-table contains when index is created
-type of index but does not tell you on which the index is based on
- select *frm user_indexes where table_name='emp';
-select *from user_ind_columns
-user_ind_columns contain name of the index and the name of the column on which index
is specified
-oracle does not allow you to create more than one index on the same column but you can
have multiple indexes participating in composite indexes have diff kind of column combination.
SQL Constraints
constraints defination:
limitation imposed on a table
Types of constraints
Primary key def
-it is column or set of columns that uniquely identifies a row
-Duplicates values are not allowed.
-Null values are also not allowed
-Primary key is help you to distingus between diffirent records
-Purpose of the Primary key is record uniqueness.
-it is recommended that every table should have a priamry key
-Rowid is a unique so why not to make rowid as a primary key
1.Rowid is not a column of a table but its a pseudo column
2.Rowid keeps on changing
-unique index is automatically created
-Long and long row can not be primary key
Composite primary key
-you can combine maximum upto 16 columns in a composite primary key
-you can have only one primary key cnstraint per table
Candidate key:
Any other column that can also serve the purpose of a primary key is a candidate
create table emp
{
empno char(4) primary key,
ename varchar2(25),
sal number(7),
deptno number(2)
}
all constraints are all at server level and you can perform DML operation through
any front end the constraints will be always be valid.
-constraint is an oracle created function which performs the validation.
-it returns logical true or false since it is an object function it has a name to find out these
-select *from user_constraints
-user_constraints is a system table which store all constraints and the all table names.
-select *from user_constraints where table_name='emp'
-To drop constraints
ALTER table emp
drop constraints sys18492
Give constraints name as
create table emp
( empn char(4) constraint pk_emp_emp primary key,
ename varchar2(25),
sal number(7,2),
deptno number(2)
);
Drop
- alter table emp drop constraints pk_emp_empno
- select *from user_indexes o/p pk_emp_empn
-here an index name is same as the constraint name
-oracle does not allow you to drop this index becuase an oracle has automatically created
it.however if you drop the constraint oracle will automatically drop that index
-composite primary key
-you will have to do it at the end of the structure
-create table emp
( empno char(4),
ename varchar2(25),
sal number(7,25),
deptn number(2)
constaint pk_emp_empno primary key(deptno,empno)
);
here you will get a composite and becuase its a composite constraints
Sequence of columns in a constraint will affect the index
-Constraints are of two types
1. Column level constraints
-giving constraints on a single column
2.Table level constraints
giving constraints on multiple columns
constraints have to given at the end of the structure
if you have already created a table and you want to add the constraint afterward.
Alter table emp
add constraint pk_emp_empno primary key (empno)
Not Null
-Null values are not allowed compulsory field
-Duplicate value are not allowed
-There is no such a thing as composite not null you have to give not null
constraint for a seprate single column
so its always columns level constraint
Create table emp
(
empno char(4),
ename varchar2(25) constraint,
nn_emp_ename not null,
sal number(7,2) constraint,
nn_emp_sal not null,
deptno number(2)
);
To add constraint after creation of a table.
Alter table emp
modify ename varchar2(25)
constraint nn_emp_ename not null;
why syntax is diffirent
NoT null oracle version 6 onwards
all other oracle version 7 onwards
To make a compability with the new version oracle does not chage syntax of not null
how do you make primary key constraint for version 6 or below or for candidate key
-Make Not NULL constraint on column
-Define unique index on column in a single table
Unique Constraint
-very similiar to primary key constraint
-duplicate values are not allowed null values allowed
-unique index is automatically created
-long and long raw can not be unique
-can combine upto 16 columns in a composite unique
-can have any no of unique constraint per table
Primary key from candiadate key:
Give unique constraint and not null constraint
create
( empno char(4)
ename varchar2
Column level unique --> mobile_n char(20) constraint
u_emp_mob_no unique(mobile no),
constraint u_emp_dno_eno,
unique(deptno,empno)
);
Special treatment is given to null.
Null is independent of datatypes thats why oracle allows duplicate null value
column level constraint can be written at table level which results in more readability.
But a table level constraint is composite uniue constraint can never be written at column
level.
create table emp
(
constraint u_emp_dno_eno,
unique(deptno,empno),
constraint u_emp_mob_no,
unique(mobile_no)
);
Foreign key
Emp table
empno ename sal deptno mgr
1 a 1 1
2 a 1 1
deptno column of emp is referring to deptno column of dept table
Foreign key definiation
Its a column of set of column that references a col or set of columns of some table.
To ensure that a valid value is entered in the deptno of emp which is present in
deptno of dept table.
Table column
Dept Deptno --Master column
Emp Dept no -- child column
Foreign key constraint is specified on the child table
child column/foreign key column may have duplicates
1.Make dept table with deptno as a primary key/unique key
2.create table emp
( empno char(4),
ename varchar2(3),
deptno number(2)
)
Foreign key constraint may be refer t column of some table
PL/SQL
SQL:
1. sql does not have procedural capabilites. sql does not do any programming stuff
2.sql statement are passed to engine in one at a time and excute a single sql statement
and if it get any error it can not handle
PL/SQL
1. PL/SQL is a development tool that not only support sql data but also provides
facilites of conditional checking
2. PL/SQL send an entire block of sql statment to the oracle engine all in one go
3. PL/SQL also permits dealing with error and handled it
4. PL/SQL allow declaration and use of variable in block of code
The Generic PL/SQL block
PL/SQL permits the creation of structure block of code that describe process.
A single PL/SQL code block consists of a set of sql statement and clubbed together
and passed to the oracle engine.
The section of PL/SQL block are
The Declare section
The master begin and end section that also contains an exception section
The declare section
code block start with a declaration section in which memory variable
and other oracle object can be declared and if required initialized
The Begin section
it consist of a set of sql and pl/sql statement which describe process that have to
be applied to table data.
The end section
this marks the end of a PL/SQL block
PL/SQL in the oracle engine
The pl/sql engine resides in the oracle engine the oracle engine can process not only
single sql statement but also entire pl/sql block
pl/sql
the character set
uppercase alphabets {A Z}
lowecase alphabets {a,z}
words used in PL/SQL block are called lexical unit
literals:
a literals is a numeric value or a character string used to represent itself
numeric literals
this can be either intergers of floats
string literals
these are represented by one or more legal characters
"hello world"
character literals
These are string literals consisting of a single characters
The default data type that can be declared in pl/sql are :
number,char,date,boolean
pl/sql can use the %TYPE attribute to declare variables based on the definition
of a column in a table.hence if a column attribute change the variable attribute
will changes as well.
Not null cause creation of a variable or a constant that can not be assigned to a null
value if anyone try to assign this value it will get exception
variables
variables in pl/sql block are named variables. A named variable name must begin
with a character and can be followed by maximum of 29 other character
reverse word can not assigned/used as variable names unless enclosed within double
quotes
Assigning values to variables
-> using the assignment operator :=
-> selecting or fecting table data values into variable
Constant
declaring a constant is similiar to declaring a variable except that the keyword constant
must be added to a variable name and a value assigned immediately.
Then after no further assignment are possible
Raw:
Raw types are used to store binary data.Character variable are automatically convert
character set by oracle
The maximum length of a raw variable is 32,767 bytes
The same way we have long raw which have maximum length is 2 gb
Rowid
this data type is same as the data base rowid pseudo column type. it can hold
rowid which can be considered as a unique key for every row in the data base.
rowid are stored internally as a fixed length whose fixed length varies depending
on the operating system
Lob
A lob dattype are used to store large objets. A large object can be either binary file
or a character file upto 4 GB in size.
Large object contain unstructured data which is accessed more effectively than long
long raw data
BLOB( Binary LOB) this stores unstructerd binary data upto 4 GB.
CLOB (Character LOB) This store single byte character upto 4 Gb in length
Storage of LOB
The area required to store LOB data can be specified at the time of creation
of the table that include LOB column
create tablename tablename( columnname data type size clob)
Control structure
The flow of control can be specify following ways.
1. conditional way
if <condition> then
<action>
else if <condition> then
<action>
else
<action>
end if
ex: write a pl/sql block that will accept an account number from wuser check if the
user balance is less than the minimum balance only then deduct 100 rs from the balance.
Duplicates values will be stored in an index
indexes are automatically updated by oracle for all your DML operation
select *from emp where ename='B'
TO speed up select statement you need to create another index based on ename column
No upper limit on number of indexes per table
if you are having indexes on 50% of the column the 50% of data will be duplicated
Larger the number of indexes,slower be the DML operation that is the overhead only
select is faster.
You can not index long and long raw
select *from emp where empno =1 and deptno
employee no is department wise and you can have
1. two indexe one for empno and other for deptno
2. create one index having both the column empno and deptno is known as composite index
Rowid deptno empno
00001 1 1
00002 1 2
00003 1 3
00004 2 1
00005 2 2
Composite index
index based on the multiple columns
can combine upto 16 columns in a composite index
Index key
The column or set of the columns n whose basis the index has been created
The order of the columns in a composite index is important
The first column in a index is a priamry key index
The second column in a index is a secondary index key
The parent column should be first column and child column should be second column
null values are not started in a index
When/where to create an index
select statement with where clause you need to create an index
if your select statement retrives less than 25% of the table data then and only
then an index should be created otherwise not
Primary column is the best column for search/select hence the key columns should
always be indexed
3. Dept
Deptno Dname Loc
1 TRN Pune
2 Exp Delhi
3 MKTG calcutta
select dname,ename from emp,dept where dept.deptno=emp.deptno
-create two index
rowid deptno -derving index
1
2
3
common fields in join operation should always be indexed
-Create index abc on emp(empno);
-index name should be always unique
-once you create one index it will be permanet in your database
-Composite index
create index i_emp_dno_en on(deptno,empno)
-if you drop the table index will be automatically dropped
-if an index is based on some column and if you drop the column index will be automatically dropped
bydefault indexes are in ascending order but in progress indexes are in descending order
-To have descending index : create index i_emp_empno on emp(empno desc)
-In case of composite index you can have one column in ascending and other one in
descending order
-create unique index i_ep_empno on emp(empno);
-An unique index is same as normal index but it will not allow you to have duplicates
values in that column on which index is created
-if you table is having duplicates values for a certain column and you are trying to create
an unique index based on that column oracle gives an error message
-select *from user_indexes;
-user index is a system table in which all indexes are stored
-table contains when index is created
-type of index but does not tell you on which the index is based on
- select *frm user_indexes where table_name='emp';
-select *from user_ind_columns
-user_ind_columns contain name of the index and the name of the column on which index
is specified
-oracle does not allow you to create more than one index on the same column but you can
have multiple indexes participating in composite indexes have diff kind of column combination.
SQL Constraints
constraints defination:
limitation imposed on a table
Types of constraints
Primary key def
-it is column or set of columns that uniquely identifies a row
-Duplicates values are not allowed.
-Null values are also not allowed
-Primary key is help you to distingus between diffirent records
-Purpose of the Primary key is record uniqueness.
-it is recommended that every table should have a priamry key
-Rowid is a unique so why not to make rowid as a primary key
1.Rowid is not a column of a table but its a pseudo column
2.Rowid keeps on changing
-unique index is automatically created
-Long and long row can not be primary key
Composite primary key
-you can combine maximum upto 16 columns in a composite primary key
-you can have only one primary key cnstraint per table
Candidate key:
Any other column that can also serve the purpose of a primary key is a candidate
create table emp
{
empno char(4) primary key,
ename varchar2(25),
sal number(7),
deptno number(2)
}
all constraints are all at server level and you can perform DML operation through
any front end the constraints will be always be valid.
-constraint is an oracle created function which performs the validation.
-it returns logical true or false since it is an object function it has a name to find out these
-select *from user_constraints
-user_constraints is a system table which store all constraints and the all table names.
-select *from user_constraints where table_name='emp'
-To drop constraints
ALTER table emp
drop constraints sys18492
Give constraints name as
create table emp
( empn char(4) constraint pk_emp_emp primary key,
ename varchar2(25),
sal number(7,2),
deptno number(2)
);
Drop
- alter table emp drop constraints pk_emp_empno
- select *from user_indexes o/p pk_emp_empn
-here an index name is same as the constraint name
-oracle does not allow you to drop this index becuase an oracle has automatically created
it.however if you drop the constraint oracle will automatically drop that index
-composite primary key
-you will have to do it at the end of the structure
-create table emp
( empno char(4),
ename varchar2(25),
sal number(7,25),
deptn number(2)
constaint pk_emp_empno primary key(deptno,empno)
);
here you will get a composite and becuase its a composite constraints
Sequence of columns in a constraint will affect the index
-Constraints are of two types
1. Column level constraints
-giving constraints on a single column
2.Table level constraints
giving constraints on multiple columns
constraints have to given at the end of the structure
if you have already created a table and you want to add the constraint afterward.
Alter table emp
add constraint pk_emp_empno primary key (empno)
Not Null
-Null values are not allowed compulsory field
-Duplicate value are not allowed
-There is no such a thing as composite not null you have to give not null
constraint for a seprate single column
so its always columns level constraint
Create table emp
(
empno char(4),
ename varchar2(25) constraint,
nn_emp_ename not null,
sal number(7,2) constraint,
nn_emp_sal not null,
deptno number(2)
);
To add constraint after creation of a table.
Alter table emp
modify ename varchar2(25)
constraint nn_emp_ename not null;
why syntax is diffirent
NoT null oracle version 6 onwards
all other oracle version 7 onwards
To make a compability with the new version oracle does not chage syntax of not null
how do you make primary key constraint for version 6 or below or for candidate key
-Make Not NULL constraint on column
-Define unique index on column in a single table
Unique Constraint
-very similiar to primary key constraint
-duplicate values are not allowed null values allowed
-unique index is automatically created
-long and long raw can not be unique
-can combine upto 16 columns in a composite unique
-can have any no of unique constraint per table
Primary key from candiadate key:
Give unique constraint and not null constraint
create
( empno char(4)
ename varchar2
Column level unique --> mobile_n char(20) constraint
u_emp_mob_no unique(mobile no),
constraint u_emp_dno_eno,
unique(deptno,empno)
);
Special treatment is given to null.
Null is independent of datatypes thats why oracle allows duplicate null value
column level constraint can be written at table level which results in more readability.
But a table level constraint is composite uniue constraint can never be written at column
level.
create table emp
(
constraint u_emp_dno_eno,
unique(deptno,empno),
constraint u_emp_mob_no,
unique(mobile_no)
);
Foreign key
Emp table
empno ename sal deptno mgr
1 a 1 1
2 a 1 1
deptno column of emp is referring to deptno column of dept table
Foreign key definiation
Its a column of set of column that references a col or set of columns of some table.
To ensure that a valid value is entered in the deptno of emp which is present in
deptno of dept table.
Table column
Dept Deptno --Master column
Emp Dept no -- child column
Foreign key constraint is specified on the child table
child column/foreign key column may have duplicates
1.Make dept table with deptno as a primary key/unique key
2.create table emp
( empno char(4),
ename varchar2(3),
deptno number(2)
)
Foreign key constraint may be refer t column of some table
PL/SQL
SQL:
1. sql does not have procedural capabilites. sql does not do any programming stuff
2.sql statement are passed to engine in one at a time and excute a single sql statement
and if it get any error it can not handle
PL/SQL
1. PL/SQL is a development tool that not only support sql data but also provides
facilites of conditional checking
2. PL/SQL send an entire block of sql statment to the oracle engine all in one go
3. PL/SQL also permits dealing with error and handled it
4. PL/SQL allow declaration and use of variable in block of code
The Generic PL/SQL block
PL/SQL permits the creation of structure block of code that describe process.
A single PL/SQL code block consists of a set of sql statement and clubbed together
and passed to the oracle engine.
The section of PL/SQL block are
The Declare section
The master begin and end section that also contains an exception section
The declare section
code block start with a declaration section in which memory variable
and other oracle object can be declared and if required initialized
The Begin section
it consist of a set of sql and pl/sql statement which describe process that have to
be applied to table data.
The end section
this marks the end of a PL/SQL block
PL/SQL in the oracle engine
The pl/sql engine resides in the oracle engine the oracle engine can process not only
single sql statement but also entire pl/sql block
pl/sql
the character set
uppercase alphabets {A Z}
lowecase alphabets {a,z}
words used in PL/SQL block are called lexical unit
literals:
a literals is a numeric value or a character string used to represent itself
numeric literals
this can be either intergers of floats
string literals
these are represented by one or more legal characters
"hello world"
character literals
These are string literals consisting of a single characters
The default data type that can be declared in pl/sql are :
number,char,date,boolean
pl/sql can use the %TYPE attribute to declare variables based on the definition
of a column in a table.hence if a column attribute change the variable attribute
will changes as well.
Not null cause creation of a variable or a constant that can not be assigned to a null
value if anyone try to assign this value it will get exception
variables
variables in pl/sql block are named variables. A named variable name must begin
with a character and can be followed by maximum of 29 other character
reverse word can not assigned/used as variable names unless enclosed within double
quotes
Assigning values to variables
-> using the assignment operator :=
-> selecting or fecting table data values into variable
Constant
declaring a constant is similiar to declaring a variable except that the keyword constant
must be added to a variable name and a value assigned immediately.
Then after no further assignment are possible
Raw:
Raw types are used to store binary data.Character variable are automatically convert
character set by oracle
The maximum length of a raw variable is 32,767 bytes
The same way we have long raw which have maximum length is 2 gb
Rowid
this data type is same as the data base rowid pseudo column type. it can hold
rowid which can be considered as a unique key for every row in the data base.
rowid are stored internally as a fixed length whose fixed length varies depending
on the operating system
Lob
A lob dattype are used to store large objets. A large object can be either binary file
or a character file upto 4 GB in size.
Large object contain unstructured data which is accessed more effectively than long
long raw data
BLOB( Binary LOB) this stores unstructerd binary data upto 4 GB.
CLOB (Character LOB) This store single byte character upto 4 Gb in length
Storage of LOB
The area required to store LOB data can be specified at the time of creation
of the table that include LOB column
create tablename tablename( columnname data type size clob)
Control structure
The flow of control can be specify following ways.
1. conditional way
if <condition> then
<action>
else if <condition> then
<action>
else
<action>
end if
ex: write a pl/sql block that will accept an account number from wuser check if the
user balance is less than the minimum balance only then deduct 100 rs from the balance.
