Friday, October 28, 2011
SQL is divided into the following
Ø Data Definition Language (DDL)
Ø Data Manipulation Language (DML)
Ø Data Retrieval Language (DRL)
Ø Transaction Control Language (TCL)
Ø Data Control Language (DCL)
DDL -- create, alter, drop, truncate, rename
DML -- insert, update, delete
DRL -- select
TCL -- commit, rollback, savepoint
DCL -- grant, revoke
CREATE TABLE
SYNTAX
Create table
<table_name> (col1 datatype1, col2 datatype2 …coln
datatypen);
Ex:
SQL> create table student (no number (2), name varchar (10), marks
number (3));
INSERT
This will be
used to insert the records into table.
We have two
methods to insert.
Ø By value method
Ø By address method
a) USING VALUE METHOD
Syntax:
insert into <table_name) values (value1,
value2, value3 …. Valuen);
Ex:
SQL> insert into student values (1, ’sudha’, 100);
SQL> insert into student values (2,
’saketh’, 200);
To insert a new record again you have to
type entire insert command, if there are lot of
records this will be difficult.
This
will be avoided by using address method.
b) USING ADDRESS METHOD
Syntax:
insert into <table_name) values (&col1,
&col2, &col3 …. &coln);
This
will prompt you for the values but for every insert you have to use forward
slash.
Ex:
SQL> insert into student values (&no, '&name', &marks);
Enter value for no: 1
Enter value for name: Jagan
Enter value for marks: 300
old 1: insert into
student values(&no, '&name', &marks)
new 1: insert into
student values(1, 'Jagan', 300)
SQL> /
Enter value for no: 2
Enter value for name: Naren
Enter value for marks: 400
old 1: insert into
student values(&no, '&name', &marks)
new 1: insert into
student values(2, 'Naren', 400)
c) INSERTING DATA INTO SPECIFIED COLUMNS USING VALUE METHOD
Syntax:
insert into <table_name)(col1, col2, col3
… Coln) values (value1, value2,
value3 ….
Valuen);
Ex:
SQL> insert into student (no, name) values (3, ’Ramesh’);
SQL> insert into student (no, name)
values (4, ’Madhu’);
d) INSERTING DATA INTO SPECIFIED COLUMNS USING ADDRESS METHOD
Syntax:
insert into <table_name)(col1, col2, col3
… coln) values (&col1, &col2,
&col3 …. &coln);
This will prompt you for the values but
for every insert you have to use forward slash.
Ex:
SQL> insert into student (no, name) values (&no, '&name');
Enter value for no: 5
Enter value for name: Visu
old 1: insert into student (no, name)
values(&no, '&name')
new 1: insert into student (no, name) values(5, 'Visu')
SQL> /
Enter value for no: 6
Enter value for name: Rattu
old 1: insert into student (no, name)
values(&no, '&name')
new 1: insert into student (no, name) values(6, 'Rattu')
SELECTING DATA
Syntax:
Select * from <table_name>; -- here * indicates all columns
or
Select col1,
col2, … coln from <table_name>;
Ex:
SQL> select * from student;
NO NAME MARKS
--- ------ --------
1 Sudha 100
2 Saketh 200
1 Jagan 300
2 Naren 400
3 Ramesh
4 Madhu
5 Visu
6 Rattu
SQL> select no, name, marks from student;
NO NAME MARKS
--- ------ --------
1 Sudha 100
2 Saketh 200
1 Jagan 300
2 Naren 400
3 Ramesh
4 Madhu
5 Visu
6 Rattu
SQL> select no, name from student;
NO NAME
--- -------
1 Sudha
2 Saketh
1 Jagan
2 Naren
3 Ramesh
4 Madhu
5 Visu
6 Rattu
Friday, October 28, 2011 by Team search · 0
1)
To find the nth row of a table
SQL> Select *from
emp where rowid = (select max(rowid) from emp where rownum <= 4);
Or
SQL> Select *from emp where rownum
<= 4 minus select *from emp where rownum <= 3;
2)
To find duplicate rows
SQL> Select *from emp where rowid
in (select max(rowid) from emp group by empno,
ename, mgr, job,
hiredate, comm, deptno, sal);
Or
SQL> Select empno,ename,sal,job,hiredate,comm , count(*) from emp
group by
empno,ename,sal,job,hiredate,comm having count(*) >=1;
3)
To delete duplicate rows
SQL> Delete emp where rowid in (select max(rowid) from emp group by
empno,ename,mgr,job,hiredate,sal,comm,deptno);
4)
To find the count of duplicate
rows
SQL> Select ename, count(*) from emp group by ename having count(*)
>= 1;
5)
How to display alternative rows
in a table?
SQL> select
*from emp where (rowid,0) in (select rowid,mod(rownum,2) from emp);
6)
Getting employee details of each
department who is drawing maximum sal?
SQL> select *from emp where (deptno,sal) in
( select deptno,max(sal) from emp group by deptno);
7)
How to get number of employees in
each department , in which department is
having more than 2500 employees?
SQL> Select deptno,count(*) from emp group by deptno having count(*) >2500;
8)
To reset the time to the beginning of the day
SQL>
Select to_char(trunc(sysdate),’dd-mon-yyyy hh:mi:ss am’) from
dual;
9)
To find nth maximum sal
SQL> Select *from emp where sal in (select max(sal) from (select
*from emp order by sal)
where rownum
<= 5);
by Team search · 2
CONTIUATION....
INDEX-ORGANIZED TABLE
An index-organized table keeps its data sorted according to the
primary key column values for the table. Index-organized tables store their
data as if the entire table was stored in an index.
An index-organized table allows you to store the entire table’s
data in an index.
Ex:
SQL> create table student (sno number(2),sname
varchar(10),smarks number(3) constraint
pk
primary key(sno) organization index;
PARTITION INDEX
Similar to partitioning tables, oracle allows you to partition
indexes too. Like table partitions,
index partitions could be in different tablespaces.
LOCAL INDEXES
Ø Local keyword tells oracle to create a separte index for each
partition.
Ø In the local prefixed index the partition key is specified on the
left prefix. When the underlying table is partitioned baes on, say two columns
then the index can be prefixed on the first column specified.
Ø Local prefixed indexes can be unique or non unique.
Ø Local indexes may be easier to manage than global indexes.
Ex:
SQL> create index stud_index on student(sno) local;
GLOBAL INDEXES
Ø A global index may contain values from multiple partitions.
Ø An index is global prefixed if it is partitioned on the left
prefix of the index columns.
Ø The global clause allows you to create a non-partitioned index.
Ø Global indexes may perform uniqueness checks faster than local
(partitioned) indexes.
Ø You cannot create global indexes for hash partitions or
subpartitions.
Ex:
SQL> create index stud_index on student(sno) global;
Similar to table partitions, it is possible to move them from one
device to another. But unlike table partitions, movement of index partitions
requires individual reconstruction of the index or each partition (only in the
case of global index).
Ex:
SQL> alter
index stud_ind rebuild partition p2
Ø Index partitions cannot be dropped manually.
Ø They are dropped implicitly when the data they refer to is dropped
from the partitioned table.
MONITORING USE OF INDEXES
Once you turned on the monitoring the use of indexes, then we can
check whether the table is hitting the index or not.
To monitor the use of index use the follwing syntax.
Syntax:
alter index index_name
monitoring usage;
then check for the details in V$OBJECT_USAGE view.
If you want to stop monitoring use the following.
Syntax:
alter index index_name
nomonitoring usage;
DATA MODEL
Ø ALL_INDEXES
Ø DBA_INDEXES
Ø USER_INDEXES
Ø ALL_IND-COLUMNS
Ø DBA-IND_COLUMNS
Ø USER_IND_COLUMNS
Ø ALL_PART_INDEXES
Ø DBA_PART_INDEXES
Ø USER_PART_INDEXES
Ø V$OBJECT_USAGE
by Team search · 0
PART 1: http://www.shareoracleapps.com/2011/10/oracle-sql-tutorial-index-part-1.html
continuation.....
HOW A TEXT QEURY WORKS?
When a function such as CONTAINS or CATSEARCH is used in query,
the text portion of the query is processed by oracle text. The remainder of the
query is processed just like a regular query within the database. The result of
the text query processing and the regular query processing are merged to return
a single set of records to the user.
SEARCHING FOR AN EXACT MATCH OF A WORD
The following queries will search for a word called
‘prperty’ whose score is greater than zero.
SQL> select * from books where contains(info,
‘property’) > 0;
SQL> select * from books where catsearch(info,
‘property’, null) > 0;
Suppose if you want to know the score of the
‘property’ in each book, if score values for individual searches range from 0
to 10 for each occurrence of the string within the text then use the score
function.
SQL> select title, score(10) from books where
contains(info, ‘property’, 10) > 0;
SEARCHING FOR AN EXACT MATCH OF MULTIPLE WORDS
The following queries will search for two words.
SQL> select * from books where contains(info,
‘property AND harvests’) > 0;
SQL> select * from books where catsearch(info,
‘property AND harvests’, null) > 0;
Instead of using AND you could hae used an
ampersand(&). Before using this method, set define off so the &
character will not be seen as part of a variable name.
SQL> set define off
SQL> select * from books where contains(info,
‘property & harvests’) > 0;
SQL> select * from books where catsearch(info,
‘property harvests’, null) > 0;
The following queries will search for more than two
words.
SQL> select * from books where contains(info,
‘property AND harvests AND workers’) > 0;
SQL> select * from books where catsearch(info,
‘property harvests workers’, null) > 0;
The following queries will search for either of the two
words.
SQL> select * from books where contains(info,
‘property OR harvests’) > 0;
Instead of OR you can use a vertical line (|).
SQL> select * from books where contains(info,
‘property | harvests’) > 0;
SQL> select * from books where catsearch(info,
‘property | harvests’, null) > 0;
In the following queries the ACCUM(accumulate)
operator adds together the scores of the individual searches and compares the
accumulated score to the threshold value.
SQL> select * from books where contains(info,
‘property ACCUM harvests’) > 0;
SQL> select * from books where catsearch(info,
‘property ACCUM harvests’, null) > 0;
Instead of OR you can use a comma(,).
SQL> select * from books where contains(info,
‘property , harvests’) > 0;
SQL> select * from books where catsearch(info,
‘property , harvests’, null) > 0;
In the following queries the MINUS operator subtracts
the score of the second term’s search from the score of the first term’s
search.
SQL> select * from books where contains(info,
‘property MINUS harvests’) > 0;
SQL> select * from books where catsearch(info,
‘property NOT harvests’, null) > 0;
Instead of MINUS you can use – and instead of NOT
you can use ~.
SQL> select * from books where contains(info,
‘property - harvests’) > 0;
SQL> select * from books where catsearch(info,
‘property ~ harvests’, null) > 0;
SEARCHING FOR AN EXACT MATCH OF A PHRASE
The following queries will search for the phrase. If the search
phrase includes a reserved word within oracle text, the you must use curly
braces ({}) to enclose text.
SQL> select * from books where contains(info, ‘transactions
{and} finances’) > 0;
SQL> select * from books where catsearch(info, ‘transactions
{and} finances’, null) > 0;
You can enclose the entire phrase within curly braces, in which
case any reserved words within the phrase will be treated as part of the search
criteria.
SQL> select * from books where contains(info, ‘{transactions
and finances}’) > 0;
SQL> select * from books where catsearch(info, ‘{transactions
and finances}’, null) > 0;
SEARCHING FOR WORDS THAT ARE NEAR EACH OTHER
The following queries will search for the words that
are in between the search terms.
SQL> select * from books where contains(info, ‘workers
NEAR harvests’) > 0;
Instead of NEAR you can use ;.
SQL> select * from books where contains(info, ‘workers
; harvests’) > 0;
In CONTEXT index queries, you can specify the
maximum number of words between the search terms.
SQL> select * from books where contains(info, ‘NEAR((workers,
harvests),10)’ > 0;
USING WILDCARDS DURING SEARCHES
You can use wildcards to expand the list of valid search terms
used during your query. Just as in regular text-string wildcard processing, two
wildcards are available.
% - percent sign; multiple-character
wildcard
_ - underscore; single-character
wildcard
SQL> select * from books where contains(info, ‘worker%’)
> 0;
SQL> select * from books where contains(info, ‘work___’)
> 0;
SEARCHING FOR WORDS THAT SHARE THE SAME STEM
Rather than using wildcards, you can use stem-expansion
capabilities to expand the list of text strings. Given the ‘stem’ of a word,
oracle will expand the list of words to search for to include all words having
the same stem. Sample expansions are show here.
Play - plays playing played playful
SQL> select * from books where contains(info, ‘$manage’)
> 0;
SEARCHING FOR FUZZY MATCHES
A fuzzy match expands the specified search term to include words
that are spelled similarly but that do not necessarily have the same word stem.
Fuzzy matches are most helpful when the text contains misspellings. The
misspellings can be either in the searched text or in the search string
specified by the user during the query.
The following queries will not return anything because its search
does not contain the word ‘hardest’.
SQL> select * from books where contains(info, ‘hardest’)
> 0;
It does, however, contains the word ‘harvest’. A fuzzy match will
return the books containing the word ‘harvest’ even though ‘harvest’ has a
different word stem thant the word used as the search term.
To use a fuzzy match, precede the search term with a question
mark, with no space between the question mark and the beginning of the search
term.
SQL> select * from books where contains(info, ‘?hardest’)
> 0;
SEARCHING FOR WORDS THAT SOUND LIKE OTHER WORDS
SOUNDEX, expands search terms based on how the word sounds. The
SOUNDEX expansion method uses the same text-matching logic available via the
SOUNDEX function in SQL.
To use the SOUNDEX option, you must precede the search term with
an exclamation mark(!).
SQL> select * from books where contains(info, ‘!grate’)
> 0;
INDEX SYNCHRONIZATION
When using CONTEXT indexes, you need to manage the text index
contents; the text indexes are not updated when the base table is updated. When
the table was updated, its text index is out of sync with the base table. To
sync of the index, execute the SYNC_INDEX procedure of the CTX_DDL package.
SQL> exec CTX_DDL.SYNC_INDEX(‘book_index’);
INDEX SETS
Historically, problems with queries of text indexes have occurred
when other criteria are used alongside text searches as part of the where
clause. To improve the mixed query capability, oracle features index sets. The
indexes within the index set may be structured relational columns or on text
columns.
To create an index set, use the CTX_DDL package to create the
index set and add indexes to it. When you create a text index, you can then
specify the index set it belongs to.
SQL> exec CTX_DDL.CREATE_INDEX_SET(‘books_index_set’);
The add non-text indexes.
SQL> exec CTX_DDL.ADD_INDEX(‘books_index_set’,
‘title_index’);
Now create a CTXCAT text index. Specify
ctxsys.ctxcat as the index type, and list the index set in the parameters
clause.
SQL> create index book_index on books(info) indextype
is ctxsys.ctxcat parameters(‘index set books_index_set’);
by Team search · 0
Subscribe to:
Posts (Atom)
Disclaimer
The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.