SQL has four statements which we discussed below
DDL (Data Definition Language) STATEMENTS
CREATE
CREATE TABLE
statement allows you to create and define a table
Syntax: CREATE TABLE
<tablename> (col1 datatype null/not null, col2 datatype null/not null,
... );
Each column must
have a datatype. The column should either be defined as "null" or
"not null" and if this value is left blank, the database assumes
"null" as the default - also we can specify database constraints
while defining table
You can also create
a table from an existing table by copying the existing table's columns.
It is important to
note that when creating a table in this way, the new table will be populated
with the records from the existing table (based on the SELECT Statement)
1- Copying all
columns from another table
2- Copying selected
columns from another table
3- Copying selected
columns from multiple tables
Question: How can I
create a table from another table without copying any values from the old
table?
Answer: To do this,
the basic syntax is: CREATE TABLE
<new_table> AS (SELECT * FROM
old_table WHERE 1=2); - here a false
condition is used
ALTER
ALTER TABLE
statement allows you to add, modify, drop, or rename a column from an existing
table
1- To add a column
to an existing table:
ALTER TABLE
<table_name> ADD <column_name
column-definition>
2- To add multiple
columns to an existing table:
ALTER TABLE
<table_name> ADD ( col1 coldef, col2 coldef, ...
coln col_def)
3- To modify a
column in an existing table:
ALTER TABLE
<table_name> MODIFY col_name col_type
4- To modify
multiple columns in an existing table:
ALTER TABLE
<table_name> MODIFY (col1 type, col2 type, ... coln type)
5- To drop a column
in an existing table:
ALTER TABLE
<table_name> DROP COLUMN column_name
6- To rename a
column in an existing table:
ALTER TABLE
<table_name> RENAME COLUMN <old_name> to <new_name>
7- The basic syntax
for renaming a table is:
ALTER TABLE
<table_name> RENAME TO <new_table_name>
TRUNCATE
TRUNCATE statement is
used to remove all rows from a table.
Removing rows with
the TRUNCATE TABLE statement can be faster than removing all rows with the
DELETE statement, especially if the table has numerous triggers, indexes, and
other dependencies. To truncate a table, the table must be in your schema or
you must have DROP ANY TABLE system privilege.
TRUNCATE TABLE
<tablename>;
DROP
DROP TABLE statement
allows you to remove a table from the database.
Syntax: DROP TABLE
<table_name>;
RENAME
RENAME statement
allows you to rename an existing table in the database.
Syntax: RENAME <old_name> to <new_name>;
DML (Data Manipulation Language) STATEMENTS
SELECT
(Select is also known as Data Retrieval Statement)
SELECT statement
allows you to retrieve records from one or more tables in your database.
1 - To retrieve all
columns from a table:
SELECT
* FROM <table_name> (* signifies that we wish to view all fields from
table)
2 - To retrieve
individual fields in the table:
SELECT
col1, col2 from <table_name>
3 - To retrieve
filtered rows in the table:
SELECT
col1, col2 from <table_name> where <condition>
4 - To retrieve
fields from multiple tables:
SELECT
tab1.col, tab2.col FROM <tab1>, <tab2> WHERE tab1.join_col =
tab2.join_col
INSERT
1 - INSERT statement
allows you to insert a single record or multiple records into a table
INSERT
INTO <table> (col1, col2, ... coln) VALUES (val1, val2, ... valn);
2- INSERT statement
allows you to insert data using sub-select
INSERT
INTO <tab1>(col11, col12) SELECT col21, col22 FROM <tab2> WHERE
<expr>
3 - INSERT ALL
INTO
suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
INTO
suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
INTO
products (product_id, product_name) VALUES (1001, 'Chair')
INTO
dept (deptno, dname, location) VALUES (10, 'sales','chicago') SELECT * FROM
dual;
UPDATE
1 - UPDATE statement
allows you to update a single record or multiple records in a table.
UPDATE
<tablename> SET <column> = <expression> WHERE
<condition>
2 - UPDATE on table
based on values in another table is done using sub-queries and EXISTS clause.
DELETE
DELETE statement
allows you to delete a single record or multiple records from a table.
1 - DELETE FROM
<table> WHERE <condition> - deletes records from table that satisfy
condition
2 - DELETE FROM
<table_name> - deletes all records from table
3 - DELETE can be
performed on one table based on values in another table using EXISTS/IN/ NOT
IN/ NOT EXISTS operator(s)
MERGE
MERGE (sometimes
referred to as "UPSERT") is a DML statement that enables us to
optionally update or insert data into a target table, depending on whether
matching records already exist.
MERGE INTO <target_table> t USING
<source_table> s
ON (
s.obj_id = t.obj_id )
WHEN MATCHED THEN
UPDATE SET t.obj_name = s.obj_name, t.obj_type =
s.obj_type
WHEN NOT
MATCHED THEN
INSERT (t.obj_id,
t.obj_name, t.obj_type) VALUES (s.obj_id, s.obj_name, s.obj_type );
MERGE : as
stated previously, this is DML statement in Oracle
INTO : this
is how we specify the target for the MERGE. The target must be either a table
or an updateable
view
USING : USING
clause represents the source dataset for the MERGE. This can be a single
table or an in-line view
ON () : here
we supply the join between the source dataset and target table. Join conditions
must be in parentheses;
WHEN MATCHED : this
clause is where we instruct Oracle on what to do when we
already have a matching
record in the target table. We obviously
want an UPDATE in this case
WHEN NOT MATCHED : this
clause is where we INSERT records for which there is no
current match.
TCL (Transaction Control Language) STATEMENTS
COMMIT
The COMMIT statement
makes permanent any changes made to the database during the current
transaction. A commit also makes the changes visible to other users. The COMMIT
statement releases all row and table locks, and erases any savepoints you
marked since the last commit or rollback
Until your changes
are committed:
- You can see the
changes when you query the tables you modified, but other users cannot see the
changes.
- If you change your
mind or need to correct a mistake, you can use the ROLLBACK statement to roll
back (undo) the changes
ROLLBACK
ROLLBACK statement
is the inverse of the COMMIT statement. It undoes some or all database changes
made during the current transaction.
All savepoints
marked after the savepoint to which you roll back are erased. The savepoint to
which you roll back is not erased. For example, if you mark savepoints A, B, C,
and D in that order, then roll back to savepoint B, only savepoints C and D are
erased.
SAVEPOINT
SAVEPOINT statement
names and marks the current point in the processing of a transaction. With the
ROLLBACK TO statement, savepoints undo parts of a transaction instead of the
whole transaction.
A simple rollback or
commit erases all savepoints. When you roll back to a savepoint, any savepoints
marked after that savepoint are erased. The savepoint to which you roll back
remains.
You can reuse
savepoint names within a transaction. The savepoint moves from its old position
to the current point in the transaction. An implicit savepoint is marked before
executing an INSERT, UPDATE, or DELETE statement. If the statement fails, a
rollback to the implicit savepoint is done. Normally, just the failed SQL
statement is rolled back, not the whole transaction
DCL (Data Control Language) STATEMENTS
GRANT
You can grant users
various privileges to tables. These privileges can be any combination of
select, insert, update & delete. Below is an explanation of what each
privilege means
Select - Ability to
query the table with a select statement.
Insert - Ability to
add new rows to the table with the insert statement.
Update - Ability to
update rows in the table with the update statement.
Delete - Ability to
delete rows from the table with the delete statement.
1 – GRANT select,
update, insert on EMP to XYZ;
to grant
select,update,insert privilege on EMP table to other user XYZ.
2 - GRANT
all on EMP to XYZ;
to grant all
privileges on EMP table to XYZ
3 - GRANT
select on EMP to public;
to grant
select privilege on EMP to all other users of the database
4 - GRANT
update (ename),insert (empno, ename) on
EMP to XYZ;
to grant update on ename column only
and insert on empno and ename columns only
5 - GRANT select on
EMP to XYZ WITH GRANT OPTION;
To grant select privilege on EMP table to XYZ and to
make XYZ be able further pass on this privilege
REVOKE
Once you have granted
privileges, you may need to revoke some or all of these privileges. To do this,
you can execute a revoke command. You can revoke any combination of select,
insert, update and delete.
1 -
revoke select, update, insert on EMP from XYZ;
to revoke select, update, insert privilege you have granted
to XYZ
2 -
revoke select on EMP from public;
To revoke select statement on EMP granted to public
3 -
revoke update, insert on EMP from XYZ;
To revoke update privilege on ename
column and insert privilege on empno and ename columns
Note :You cannot
take back column level privileges. Suppose you just want to take back insert privilege on ename column then you
have to first take back the whole insert privilege and then grant privilege on
empno column.
DIFFERENCE BETWEEN TRUNCATE, DELETE & DROP STATEMENTS
TRUNCATE
|
DELETE
|
DROP
|
DDL
|
DML
|
DDL
|
NO ROLLBACK
|
ROLLBACK CAN BE PERFORMED
|
NO ROLLBACK
|
NO CONDITION CAN BE SPECIFIED
|
CONDITION CAN BE SPECIFIED
|
NO CONDITION CAN BE SPECIFIED
|
FAST
|
SLOW
|
FAST
|
TRIGGER CANNOT BE FIRED
|
TRIGGER CAN BE FIRED
|
TRIGGER CANNOT BE FIRED
|
TCL NOT REQUIRED
|
TCL REQUIRED
|
TCL NOT REQUIRED
|
REDO LOGS NOT GENERATED
|
REDO LOGS GENERATED
|
REDO LOGS NOT GENERATED
|
REMOVES DATASPACE AND INDEXSPACE
|
REMOVES ONLY DATASPACE
|
REMOVES INDEXES AND PRIVILEGES
|
JOINS
When data from more
than one table in the database is required, a join condition is used. Rows in
one table can be joined to rows in another table according to common values
existing in corresponding columns, that is, usually primary and foreign key
columns.
- Write the join
condition in the WHERE clause.
- Prefix column name
with the table name when same column name appears in more than one table.
- To join n tables
together, you need a minimum of n-1 join conditions.
EQUI JOINS
An equijoin is a
join condition containing equality operator. To determine an employee’s
department name, you compare the value in the DEPTNO column in the EMP table
with the DEPTNO values in the DEPT table. The relationship between the EMP and
DEPT tables is an equijoin, that is, values in the DEPTNO column on both tables
must be equal. Equijoins are also called simple/inner join.
NON-EQUI JOINS
A non-equijoin is a
join condition containing something other than an equality operator. The relationship
between the EMP table and the SALGRADE table has an example of a non-equijoin.
A relationship between the two tables is that the SAL column in the EMP table
must be between the values in the LOSAL and HISAL columns of the SALGRADE
table. The relationship is obtained using an operator other than equals (=)
NATURAL JOIN
The NATURAL JOIN
clause is based on all columns in the two tables that have the same name.
- It selects rows
from the two tables that have equal values in all matched columns.
- The join can
happen only on columns having the same names and data types in both the tables
- If the columns
having the same names have different data types, an error is returned.
SELECT empno, ename,
dname FROM emp NATURAL JOIN dept
Creating Joins with
USING Clause:
If several columns
have the same names but the data types do not match, the NATURAL JOIN clause
can be modified with the USING clause to specify the columns that should be
used for an equijoin.
- Use the USING
clause to match only one column when more than one column matches.
- Do not use a table
name or alias in the referenced columns.
- The NATURAL JOIN
and USING clauses are mutually exclusive.
SELECT empno,
ename, dname FROM emp JOIN dept using (DEPTNO)
Creating Joins with
ON Clause
- The join condition
for the natural join is basically an equijoin of all columns with the same
name.
- To specify
arbitrary conditions or specify columns to join, the ON clause is used.
- The join condition
is separated from other search conditions.
- The ON clause
makes code easy to understand.
INNER JOIN
Join of two tables
returning only matched rows is an inner join.
OUTER JOIN
You use an outer
join to also see rows that do not meet the join condition also.
- The missing rows
can be returned if an outer join operator is used in the join condition. The
operator is a plus sign enclosed in parentheses (+), and it is placed on the
“side” of the join that is deficient in information. This operator has the
effect of creating one or more null rows, to which one or more rows from the
non-deficient table can be joined.
- The outer join
operator can appear on only one side of the expression—the side that has
information missing. It returns those rows from one table that have no direct
match in the other table.
- A condition
involving an outer join cannot use the IN operator or be linked to another
condition by the OR operator.
LEFT OUTER JOIN
A join between two
tables that returns the results of the inner join as well as unmatched rows
from right table is a left outer join (in short everyrow from left table and
matching rows from right table and null values for unmatched rows)
Ex: Select
dept.deptno, count(*) from dept LEFT OUTER JOIN emp on (dept.deptno =
emp.deptno)
RIGHT OUTER JOIN
A join between two
tables that returns the results of the inner join as well as unmatched rows
from left table is a right outer join (in short every row from right table and
matching rows from left table and null values for unmatched rows)
FULL OUTER JOIN
A join between two
tables that returns the results of an inner join as well as the results of a
left and right join is a full outer join.
CROSS JOIN / CARTESIAN PRODUCT
The CROSS JOIN
clause produces the cross-product of two tables.
This is the same as
a Cartesian product between the two tables.
SELF JOIN
A self join is a
join of a table to itself. This table appears twice in the FROM clause and is
followed by table aliases that qualify column names in the join condition. To
perform a self join, Oracle Database combines and returns rows of the table
that satisfy the join condition.
SINGLE ROW FUNCTIONS
ABS
returns the absolute
value of a number
eg: abs(-23) would
return 23; abs(-23.6) would return 23.6; abs(23.65) would return 23.65
CEIL
returns the smallest
integer value that is greater than or equal to a number
eg: ceil(32.65)
would return 33; ceil(32) would return 32; ceil(-32.65) would return -32;
ceil(-32) would return -32
EXP
returns e raised to
the nth power, where e = 2.71828183
eg: exp(3) would
return 20.0855369231877; exp(3.1) would return 22.1979512814416; exp(-3) would
return 0.0497870683678639
FLOOR
returns the largest
integer value that is equal to or less than a number
eg: floor(5.9) would
return 5; floor(34.29) would return 34; floor(-5.9) would return -6
MOD
returns the
remainder of m divided by n ( when used as mod( m, n ) )
mod(15, 4) would
return 3; mod(15, 0) would return 15; mod(11.6, 2) would return 1.6;
mod(11.6, 2.1) would
return 1.1; mod(-15, 4) would return -3; mod(-15, 0) would return -15
POWER
returns m raised to
the nth power ( m is base, n is exponent; If m is -ve, then n must be an
integer)
Usage: power( m, n )
eg: power(3, 2)
would return 9; power(5, 3) would return 125; power(-5, 3) would return -125;
power(6.2, 3) would
return 238.328; power(6.2, 3.5) would return 593.431934277892
ROUND
returns a number
rounded to a certain number of decimal places
Usage: round(
number, [ decimal_places ] )
number is the number
to round
decimal_places is
the number of decimal places rounded to. This value must be an integer. If this
parameter is omitted, the round function will round the number to 0 decimal
places.
eg: round(125.315)
would return 125; round(125.315, 0) would return 125;
round(125.315, 1)
would return 125.3; round(125.315, 2) would return 125.32;
round(125.315, 3)
would return 125.315; round(-125.315, 2) would return -125.32;
round(125.315,-1)
would return 130; round(125.315, -2) would return 100;
SIGN
returns a value
indicating the sign of a number
Usage: sign( number
)
number is the number
to test for its sign.
If number < 0,
then sign returns -1
If number = 0, then
sign returns 0
If number > 0,
then sign returns 1
eg: sign(-23) would
return -1; sign(-0.001) would return -1; sign(0) would return 0; sign(0.001)
would return 1; sign(23) would return 1; sign(23.601) would return 1
SQRT
returns the square
root of n
Usage: sqrt( n )
-> n is a positive number
eg: sqrt(9) would
return 3; sqrt(-5) would return argument -5 is out of range
TRUNC
number truncated to
a certain number of decimal places.
Usage: trunc(
number, [ decimal_places ] )
number is the number
to truncate.
decimal_places is
the number of decimal places to truncate to. This value must be an integer. If
this parameter is omitted, the trunc function will truncate the number to 0
decimal places.
eg: trunc(125.815)
would return 125; trunc(125.815, 0) would return 125;
trunc(125.815, 1)
would return 125.8; trunc(125.815, 2) would return 125.81;
trunc(125.815, -1)
would return 120; trunc(125.815, -2) would return 100; trunc(125.815, -3) would
return 0
ASCII
returns the NUMBER
code that represents the specified character.
Usage:
ascii(single_character)
single_character is
the specified character to retrieve the NUMBER code for. If more than one
character is entered, the function will return the value for the first
character and ignore all of the characters after the first.
eg: ascii('t') would
return 116; ascii('T') would return
84; ascii('T2') would also
return 84.
CHR
It returns the
character based on the NUMBER code.
Usage: chr(
number_code ) <number_code is the NUMBER code used to retrieve the
character.>
eg: chr(116) would
return 't'; chr(84)
would return 'T'
CONCAT
allows you to
concatenate two strings together.
Usage: concat(
string1, string2 ) <string1 is first string and string2 is second string to
concatenate>
eg: concat('IT', ' Usa') would return 'IT Usa'; concat('a', 'b') would
return 'ab'
INITCAP
sets the first
character in each word to uppercase and the rest to lowercase.
Usage:
initcap(string1)
string1 is
the string argument whose first character in each word will be converted to
uppercase and all remaining characters converted to lowercase.
eg: initcap('rhythm
of life') would return 'Rhythm Of Life'; initcap('IT USA') would return 'Usa'
LOWER
converts all letters
in the specified string to lowercase. All non letters in the string are
unaffected by this function.
Usage:
lower(string1) <string1 is the string to convert to lowercase>
eg: lower('IT Usa') returns 'it usa'; lower('RHYTHM
OF LIFE ') returns 'rhythm of life '
LPAD
pads the left-side
of a string with a specific set of characters (when string1 is not null).
Usage: lpad(
string1, padded_length, [ pad_string ] )
string1 is the string to pad to the left-hand side; padded_length is the number of
characters to return. If the padded_length is smaller than the original string,
the lpad function will truncate the string to the size of padded_length. pad_string
is optional. This is the string that will be padded to the left-hand side of
string1. If this parameter is omitted, the lpad function will pad spaces to the
left-side of string1.
eg: lpad('abcd', 7)
returns ' abcd'; lpad('abcd',
2) returns 'ab'; lpad('abcd', 8,
'0') would return '0000abcd';
LTRIM
removes all
specified characters from the left-hand side of a string.
Usage: ltrim(
string1, [ trim_string ]) <string1 is the string to trim the
characters from the left-hand side; trim_string
is the string that will be removed from the left-hand side of string1. If this
parameter is omitted, the ltrim function will remove all leading spaces from
string1>
eg: ltrim(' abcd'); would return 'abcd'; ltrim(' abcd', ' '); would return abcd'
ltrim('000123',
'0'); would return '123'; ltrim('123123abcd123',
'123') would return 'abcd123';
ltrim('123123abcd',
'123'); would return 'abcd' ltrim('6372Tech',
'0123456789'); would return 'Tech'
ltrim('xyxzyyyabcd',
'xyz'); would return 'abcd' < removes individual occurrences of 'x', 'y',
and 'z'>
RTRIM
removes all
specified characters from the right-hand side of a string
Usage: rtrim(
string1, [ trim_string ] )
string1 is
the string to trim the characters from the right-hand side.
trim_string is
the string that will be removed from the right-hand side of string1. If this
parameter is omitted, the rtrim function will remove all trailing spaces from
string1.
eg: rtrim('abcd '); would return 'abcd' rtrim('abcd ', ' '); would return 'abcd'
rtrim('123000',
'0'); would return '123' rtrim('abcd123123',
'123'); would return 'abcd'
rtrim('123abcd123',
'123'); would return '123abcd' rtrim('abcdxyxzyyy',
'xyz'); would return 'abcd'
rtrim('abcd6372',
'0123456789'); would return 'abcd'
REPLACE
replaces a sequence
of characters in a string with another set of characters.
Usage:
replace( string1, string_to_replace, [ replacement_string ] )
string1 is the string to replace a sequence of characters with
another set of characters; string_to_replace is the string that
will be searched for in string1. replacement_string is optional. All
occurrences of string_to_replace will be replaced with replacement_string in
string1. If the replacement_string parameter is omitted, the replace function
simply removes all occurrences of string_to_replace, and returns the resulting
string.
eg:
replace('123123abcd', '123'); would return 'abcd'; replace('123abcd123', '123'); would return 'abcd';
replace('222abcd',
'2', '3'); would return '333abcd'; replace('0000123',
'0'); would return '123';
RPAD
pads the right-side
of a string with a specific set of characters (when string1 is not null)
Usage: rpad(
string1, padded_length, [ pad_string ] )
string1 is the string to pad characters to (the right-hand
side); padded_length is the number of characters to return. If the
padded_length is smaller than the original string, the rpad function will
truncate the string to the size of padded_length; pad_string is optional. This is the
string that will be padded to the right-hand side of string1. If this parameter
is omitted, the rpad function will pad spaces to the right-side of string1.
eg: rpad('abcd', 7);
would return 'abcd ' rpad('abcd', 2);
would return 'ab'
rpad('abcd', 8,
'0'); would return 'abcd0000' rpad('abcd',
4, 'z'); would return 'abcd'
UPPER
converts all letters
in the specified string to uppercase. If there are characters in the string
that are not letters, they are unaffected by this function. Usage: upper(string1) < string1
is the string to convert to uppercase>
eg: upper('Oracle
Char functions'); would return 'ORACLE CHAR FUNCTIONS';
upper('hello
oracle'); would return 'HELLO ORACLE'
LENGTH
returns the length
of the specified string. Usage: length( string1 )
string1 is the string to return the length for. If string1 is
NULL, then the function returns NULL.
eg: length(NULL)
would return NULL; length('') would return NULL; length('Sample text') would
return 11;
SUBSTR
allows you to
extract a substring from a string.
Usage:
substr( string, start_position, [ length ] )
string is the source string; start_position is the
position for extraction. The first position in the string is always 1.
length is the number of characters to extract. If this
parameter is omitted, substr will return the entire string.
Note:
- If start_position
is 0, then substr treats start_position as 1 (ie: the first position in the
string).
- If start_position
is a positive number, then substr starts from the beginning of the string.
- If start_position
is a negative number, then substr starts from the end of the string and counts
backwards.
- If length is a
negative number, then substr will return a NULL value.
eg: substr('This is
a test', 6, 2) would return 'is'; substr('This
is a test', 6) would return 'is a test'
substr('ThisIsATest',
1, 4) would return 'This'; substr('ThisIsATest',
-3, 3) would return 'est'
substr('ThisIsATest',
-6, 3) would return 'sAT'; substr('ThisIsATest',
-8, 2) would return 'sI'
TRIM
removes all
specified characters either from the beginning or the ending of a string.
Usage: trim(
[ leading | trailing | both [
trim_character ] ] string1 )
leading - remove trim_string from the front of string1; trailing
- remove trim_string from the end of string1.
both - remove trim_string from the front and end of
string1; If none of these are chosen
(ie: leading, trailing, both), the trim function will remove trim_string from
both the front and end of string1.
trim_character is the character that will be removed from string1.
If this parameter is omitted, the trim function will remove all leading and
trailing spaces from string1; string1
is the string to trim.
eg: trim(' test
') would return 'test'; trim('
' from
' test ') would return 'test'
trim(leading '0'
from '000123') would return '123'; trim(trailing
'1' from 'Test1') would return 'Test'
trim(both '1' from
'123Test111') would return '23Test';
LENGTHB
Returns the number
of bytes in string (use for double-byte char sets)
Usage:
lengthb(string1) <string1 is the string to return the
length in bytes for>
Eg: in sample string
– assume one character is double-byte character, and then lengthb will be
length+1
TRANSLATE
replaces a sequence
of characters in a string with another set of characters. However, it replaces
a single character at a time. For example, it will replace the 1st character in
the string_to_replace with the 1st character in the replacement_string. Then it
will replace the 2nd character in the string_to_replace with the 2nd character
in the replacement_string, and so on.
Usage:
translate( string1, string_to_replace, replacement_string )
string1 is the string to replace a sequence of characters
with another set of characters;
string_to_replace is the string that will be searched for in string1.
replacement_string - All characters in the string_to_replace will be
replaced with the corresponding character in the replacement_string.
eg:
translate('1test23', '123', '456'); would return '4test56';
translate('222test', '2es', '3il'); would return '333tilt'
INSTR
returns the location
of a substring in a string.
Usage:
instr( string1, string2 [, start_position [, nth_appearance ] ] )
string1 is the string to search.
string2 is the substring to search for in string1.
start_position is the position in string1 where the search will
start. This argument is optional. If omitted, it defaults to 1. The first
position in the string is 1. If the start_position is negative, the function
counts back start_position number of characters from the end of string1 and
then searches towards the beginning of string1.
nth_appearance is the nth appearance of string2. This is optional.
If omitted, it defaults to 1.
- If string2 is not
found in string1, then the instr Oracle function will return 0.
eg: instr('This is
sample text', 'i') would return 3; 1st
occurrence of 'i';
instr('This is
sample text', 'i', 1, 2) would return 6; 2nd occurrence of 'i' starting from
1st position;
instr('This is
sample text', 'i', 4, 1) would return 6; 1st occurrence of 'i' starting from
4th position;
instr('This is
sample text', 'e', -1, 2) would return 14; 2nd occurance of 'e' from reverse
order starting from 1st position from end;
ADD_MONTHS
add_months function
returns a date plus n months
Usage:
add_months(date1,n); date1-> is the starting date; n-> is the number of
months to add to date1
add_months('01-Aug-03',
3) would return '01-Nov-03'; add_months('01-Aug-03', -3) would return
'01-May-03'
CURRENT_DATE
current_date
function returns the current date (in the time zone of the current SQL session)
Usage: current_date;
SELECT CURRENT_DATE FROM DUAL;
<output format
5/3/2011 12:50:17 PM>
CURRENT_TIMESTAMP
returns current date
and time in the time zone of the current SQL session. It returns a timestamp
with time zone.
Usage:
current_timestamp; SELECT CURRENT_TIMESTAMP FROM DUAL;
<output format
5/3/2011 12:51:23.098000 PM -04:00>
LOCALTIMESTAMP
returns the current
date and time in the time zone of the current SQL session. It returns a
TIMESTAMP value.
Usage:
localtimestamp; SELECT LOCALTIMESTAMP FROM DUAL;
<output format:
5/3/2011 12:51:23.098000 PM>
NEW_TIME
returns a date in
time zone1 to a date in time zone2.
Usage: new_time(
date, zone1, zone2 )
zone1 and zone2 can
be any of the following values:
CST - Central
Standard Time; CDT - Central Daylight Time
EST - Eastern
Standard Time; EDT - Eastern Daylight Time
MST - Mountain
Standard Time; MDT - Mountain Daylight Time
PST - Pacific
Standard Time; PDT - Pacific Daylight Time
GMT - Greenwich Mean
Time
The following
new_time example converts an Eastern Standard Time into a Pacific Standard
Time:
new_time (to_date
('2003/11/01 11:45', 'yyyy/mm/dd HH24:MI'), 'EST', 'PST')
This example would
return '2003/11/01 08:45:00'.
NEXT_DAY
returns the first
weekday that is greater than a date.
Usage:
next_day(date, weekday)
date -> is used
to find the next weekday.
weekday -> is a
day of the week (ie: SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY,
SATURDAY)
eg:
next_day('01-Aug-03', 'TUESDAY') would return '05-Aug-03';
next_day('06-Aug-03', 'WEDNESDAY') would return '13-Aug-03';
next_day('06-Aug-03',
'SUNDAY') would return '10-Aug-03'
<if you give
current weekday - will return next occurance of that weekday>
ROUND(DATE)
round function
returns a date rounded to a specific unit of measure.
Usage:
round(date,[format])
date is the date to
round; format is the unit of measure to apply for rounding. If the format
parameter is omitted, the round function will round to the nearest day.
Valid format
parameters are: year, month, dd, dy, hh, mi, ww, w etc.,
round(sysdate,'dy')
- displays nearest sunday
round(sysdate,'dd')
or round(sysdate) - displays nearest day
round(sysdate,'MONTH')
- displays nearest 1st day of month
round(sysdate,'YEAR')
- displays nearest year (January 1st of nearest year)
round(sysdate,'mi')
- displays sysdate with nearest minute
round(sysdate,'hh')
- displays sysdate with nearest hour
round(sysdate,'ww')
- displays same day of the week as the first day of the year
round(sysdate,'w') -
displays same day of the week as the first day of the month
EXTRACT
EXTRACT(MONTH FROM
CURRENT_TIMESTAMP) D_MONTH
, EXTRACT(DAY FROM
CURRENT_TIMESTAMP) D_DAY
, EXTRACT(YEAR FROM
CURRENT_TIMESTAMP) D_YEAR
, EXTRACT(HOUR FROM
CURRENT_TIMESTAMP) D_HOUR
, EXTRACT(MINUTE
FROM CURRENT_TIMESTAMP) D_MIN
, EXTRACT(SECOND
FROM CURRENT_TIMESTAMP) D_SECOND
,
EXTRACT(TIMEZONE_HOUR FROM CURRENT_TIMESTAMP) D_HOUR_TZ
, EXTRACT(TIMEZONE_REGION
FROM CURRENT_TIMESTAMP) D_REGION
,
EXTRACT(TIMEZONE_ABBR FROM CURRENT_TIMESTAMP) D_ABBR
LAST_DAY
MONTHS_BETWEEN
NVL
NVL2
NULLIF
COALESCE
SESSIONTIMEZONE
SYSDATE
SYSTIMESTAMP
TO_TIMESTAMP
TRUNC(DATE)
TO_CHAR
TO_NUMBER
TO_DATE
GREATEST
LEAST
DECODE
DBTIMEZONE
DATA MART
A data mart is a
subset of an organizational data store, usually oriented to a specific purpose
or major data subject, that may be distributed to support business needs.[1]
Data marts are analytical data stores designed to focus on specific business
functions for a specific community within an organization. Data marts are often
derived from subsets of data in a data warehouse, though in the bottom-up data
warehouse design methodology the data warehouse is created from the union of
organizational data marts
DISTINCT
1- The DISTINCT
clause allows removing duplicates from the result set. The DISTINCT clause can
only be used with select statements.
SELECT DISTINCT
<column(s)> FROM <table(s)> WHERE <condition>
SELECT DISTINCT
deptno FROM emp -> would return all unique deptno from emp table.
2- The DISTINCT
clause can be used with more than one field.
SELECT DISTINCT
deptno, job FROM emp; -> would return each unique deptno and job
combination. In this case, the distinct applies to each field listed after the
DISTINCT keyword
SUB QUERY
Query inside a query
is called subquery - where inner query is executed first and then outer query
is executed based on inner query output.
These subqueries can
reside in the WHERE clause, the FROM clause, or the SELECT clause
SUB QUERY IN WHERE
CLAUSE
IN / NOT IN – used
when inner query returns more than one rows
EXISTS
NOT EXISTS
<, >, =, ANY,
ALL
Note: when using NOT
IN
VIEWS
A view is, in
essence, a virtual table. It does not physically exist. Rather, it is created
by a query joining one or more tables
CREATE VIEW
view_name AS SELECT column(s)/expression(s) FROM <table(s)> WHERE <expression/join(s)>
You can update a
VIEW without dropping - CREATE OR REPLACE VIEW view_name AS SELECT
column(s)/expression(s) FROM <table(s)> WHERE <expression/join(s)>
DROP VIEW
<Viewname>
Question: Can you update the data in a view?
Answer: A view is created by joining one or more
tables. When you update record(s) in a view, it updates the records in the
underlying tables that make up the view.
So, yes, you can
update the data in a view providing you have the proper privileges to the
underlying tables.
Question: Does the
view exist if the table is dropped from the database?
Answer: Yes, in
Oracle, the view continues to exist even after one of the tables (that the view
is based on) is dropped from the database. However, if you try to query the
view after the table has been dropped, you will receive a message indicating
that the view has errors.
If you recreate the
table (that you had dropped), the view will again be fine.
REMAINDER
returns the
remainder of m divided by n (when used as remainder( m, n ) )
- The remainder
function uses the round function in its formula
eg: remainder(15, 6)
would return 3; remainder(15, 5) returns 0; remainder(15, 4) would return -1;
remainder(11.6, 2)
would return -0.4; remainder(11.6, 2.1) returns -1;remainder(-15, 4) returns 1
Very good job please keep posted
ReplyDelete