#Functions
1) Numeric Functions:Numeric functions are used to perform operations on numbers. They accept numeric values as input and return numeric values as output. Few of the Numeric functions are:
ABS (x) - Absolute value of the number 'x'- ABS (1) - output 1
CEIL (x) - Integer value that is Greater than or equal to the number 'x'- CEIL (2.83) and CEIL (2.49) output is 3
FLOOR (x) - Integer value that is Less than or equal to the number 'x' -
Ex: FLOOR (2.83) and FLOOR (2.49) outout is 2
TRUNC (x, y) - Truncates value of number 'x' up to 'y' decimal places .
Ex:TRUNC (140.234, 2) output 140.23, TRUNC (-54, 1) output 54,TRUNC (5.7) output 5.
ROUND (x, y) Rounded off value of the number 'x' up to the number 'y' decimal places.
Ex:ROUND (125.456, 1) output 125.4,ROUND (124.456, -1) output 120
2) Character or Text Functions: Character or text functions are used to manipulate text strings. They accept strings or characters as input and can return both character and number values as output.
LOWER(string_value) LOWER('Good Morning') good morning
UPPER(string_value) UPPER('Good Morning') GOOD MORNING
INITCAP(string_value) INITCAP('GOOD MORNING') Good Morning
LTRIM(string_value, trim_text) LTRIM ('Good Morning', 'Good) Morning
RTRIM (string_value, trim_text) RTRIM ('Good Morning', ' Morning') Good
TRIM (trim_text FROM string_value) TRIM ('o' FROM 'Good Morning') Gd Mrning
SUBSTR (string_value, m, n) SUBSTR ('Good Morning', 6, 7) Morning
LENGTH (string_value) LENGTH ('Good Morning') 12
LPAD (string_value, n, pad_value) LPAD ('Good', 6, '*') **Good
RPAD (string_value, n, pad_value) RPAD ('Good', 6, '*') Good**
3) Date Functions:These are functions that take values that are of datatype DATE as input and return values of datatypes DATE, except for the MONTHS_BETWEEN function, which returns a number as output.
Few date functions are as given below.
ADD_MONTHS (date, n) Returns a date value after adding 'n' months to the date 'x'.- Ex:ADD_MONTHS ('16-Sep-81', 3) output is 16-Dec-81
MONTHS_BETWEEN (x1, x2) Returns the number of months between dates x1 and x2.- Ex:MONTHS_BETWEEN ('16-Sep-81', '16-Dec-81') output is 3
ROUND (x, date_format) Returns the date 'x' rounded off to the nearest century, year, month, date, hour, minute, or second as specified by the 'date_format'.
TRUNC (x, date_format) Returns the date 'x' lesser than or equal to the nearest century, year, month, date, hour, minute, or second as specified by the 'date_format'.
NEXT_DAY (x, week_day) Returns the next date of the 'week_day' on or after the date 'x' occurs. Ex:NEXT_DAY ('01-Jun-08', 'Wednesday') output is 04-JUN-08.
LAST_DAY (x) It is used to determine the number of days remaining in a month from the date 'x' specified.- Ex: LAST_DAY ('01-Jun-08') output is 30-Jun-08.
SYSDATE :Returns the systems current date and time.
NEW_TIME ('01-Jun-08', 'IST', 'EST') output is 31-May-08
NEW_TIME (x, zone1, zone2) Returns the date and time in zone2 if date 'x' represents the time in zone1.
4) Conversion Functions:
These are functions that help us to convert a value in one form to another form. For Ex: a null value into an actual value, or a value from one datatype to another datatype like NVL, TO_CHAR, TO_NUMBER, TO_DATE.
Few of the conversion functions available in oracle are:
TO_CHAR (x [,y]) Converts Numeric and Date values to a character string value. It cannot be used for calculations since it is a string value.
Ex:TO_CHAR (3000, '$9999').
TO_CHAR (SYSDATE, 'Day, Month YYYY') output is $3000 Monday, June 2008.
TO_DATE (x [, date_format])Converts a valid Numeric and Character values to a Date value. Date is formatted to the format specified by 'date_format'.
Ex:TO_DATE ('01-Jun-08') output 01-Jun-08.
NVL (x, y) If 'x' is NULL, replace it with 'y'. 'x' and 'y' must be of the same datatype.
Ex: NVL (null, 1) output 1
DECODE (a, b, c, d, e, default_value) Checks the value of 'a', if a = b, then returns 'c'. If a = d, then returns 'e'. Else, returns default_value.
5)Analytic Functions:
RANK : RANK gives you the ranking within your ordered partition.RANK is an Analytical function as well as Aggregate function in Oracle. It returns the rank of the row within the group.
Ex: Agrregate Function:Find the rank of the employee whose employee number is 18 and salary is 7700.
select rank(7700,18) WITHIN GROUP (ORDER BY salary,empno) as sp from emp;
SP
———-
7
Ex: As an Analytical Function:Find the highest salary holder in each department.
select * from
(
select empno,salary,
rank() over(partition by deptno order by salary desc) as sal_rank from emp
)
where sal_rank = 1;
EMPNO SALARY SAL_RANK
———- ———- ———-
13 9000 1
17 9500 1
19 8500 1
23 7800 1
10 12000 1
DENSE_RANK : DENSE_RANK computes the rank of a row in an ordered group of rows.
LEAD - The LEAD function is used to return data from the next row.LEAD has the ability to compute an expression on the next rows (rows which are going to come after the current row) and return the value to the current row.
Syntax:LEAD (, , ) OVER ()
is the expression to compute from the leading row.
is the index of the leading row relative to the current row.
is a positive integer with default 1.
is the value to return if the points to a row outside the partition range.
Example:
SELECT deptno, empno, sal,
LEAD(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) NEXT_LOWER_SAL,
LAG(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) PREV_HIGHER_SAL
FROM emp
WHERE deptno IN (10, 20)
ORDER BY deptno, sal DESC;
DEPTNO EMPNO SAL NEXT_LOWER_SAL PREV_HIGHER_SAL
------- ------ ----- -------------- ---------------
10 7839 5000 2450 0
10 7782 2450 1300 5000
10 7934 1300 0 2450
20 7788 3000 3000 0
20 7902 3000 2975 3000
20 7566 2975 1100 3000
20 7876 1100 800 2975
20 7369 800 0 1100
LAG - The LAG function is used to access data from a previous row.
The syntax of LAG is similar except that the offset for LAG goes into the previous rows.
FAQs: What is the difference Between RANK and DENSE_RANK
Ans:RANK and DENSE_RANK both provide rank to the records based on some column value or expression. In case of a tie of 2 records at position N, RANK declares 2 positions N and skips position N+1 and gives position N+2 to the next record. While DENSE_RANK declares 2 positions N but does not skip position N+1.
Example:
SELECT empno, deptno, sal,
RANK() OVER (PARTITION BY deptno
ORDER BY sal DESC NULLS LAST) RANK,
DENSE_RANK() OVER (PARTITION BY
deptno ORDER BY sal DESC NULLS
LAST) DENSE_RANK
FROM emp
WHERE deptno IN (10, 20)
ORDER BY 2, RANK;
EMPNO DEPTNO SAL RANK DENSE_RANK
------ ------- ----- ----- ----------
7839 10 5000 1 1
7782 10 2450 2 2
7934 10 1300 3 3
7788 20 3000 1 1
7902 20 3000 1 1
7566 20 2975 3 2
7876 20 1100 4 3
7369 20 800 5 4
FIRST_VALUE and LAST_VALUE :The FIRST_VALUE analytic function picks the first record from the partition after doing the ORDER BY. The is computed on the
columns of this first record and results are returned. The LAST_VALUE function is used in similar context except that it acts on the last record of the partition.
SELECT empno, deptno, hiredate ? FIRST_VALUE(hiredate)
OVER (PARTITION BY deptno ORDER BY hiredate) DAY_GAP
FROM emp
WHERE deptno IN (20, 30)
ORDER BY deptno, DAY_GAP;
EMPNO DEPTNO DAY_GAP
---------- ---------- ----------
7369 20 0
7566 20 106
7902 20 351
7788 20 722
7876 20 756
7499 30 0
7521 30 2
7698 30 70
7844 30 200
7654 30 220
7900 30 286
11 rows selected.
FIRST and LAST function
The FIRST function (or more properly KEEP FIRST function) is used in a very special situation. Suppose we rank a group of record and found several records in the first
rank. Now we want to apply an aggregate function on the records of the first rank. KEEP FIRST enables that.
The general syntax is:
Function( ) KEEP (DENSE_RANK FIRST ORDER BY) OVER ().
#Sub Queries:A subquery is best defined as a query within a query. Subqueries enable you to write queries that select data rows for criteria that are actually developed
while the query is executing at run time.
Types of Subqueries.
Single Row Sub Query: Sub query which returns single row output. They mark the usage of single row comparison operators, when used in WHERE conditions.
Multiple row sub query: Sub query returning multiple row output. They make use of multiple row comparison operators like IN, ANY, ALL. There can be sub queries
returning multiple columns also.
Correlated Sub Query: Correlated subqueries depend on data provided by the outer query.This type of subquery also includes subqueries that use the EXISTS operator to
test the existence of data rows satisfying specified criteria.
#Views:Views are known as logical tables. They represent the data of one of more tables. A view derives its data from the tables on which it is based. These tables are called base tables. Views can be based on actual tables or another view also.
Whatever DML operations you performed on a view they actually affect the base table of the view. You can treat views same as any other table. You can Query, Insert,
Update and delete from views, just as any other table.
Views are very powerful and handy since they can be treated just like any other table but do not occupy the space of a table.
We can Create,Replace and Drop views.
#Materialized Views:A materialized view, or snapshot as they were previously known, is a table segment whose contents are periodically refreshed based on a query, either against a local or remote table. Using materialized views against remote tables is the simplest way to achieve replication of data between sites.
Basic Syntax:
CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
[ON PREBUILT TABLE]
AS
SELECT ...;
The BUILD clause options are shown below.
IMMEDIATE : The materialized view is populated immediately.
DEFERRED : The materialized view is populated on the first requested refresh.
The following refresh types are available.
FAST : A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails.
COMPLETE : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.
FORCE : A fast refresh is attempted. If one is not possible a complete refresh is performed.
A refresh can be triggered in one of two ways.
ON COMMIT : The refresh is triggered by a committed data change in one of the dependent tables.
ON DEMAND : The refresh is initiated by a manual request or a scheduled task.
Create Materialized View Logs:Materialized view logs can be created to capture all changes to the base table since the last refresh.
Refresh Materialized Views
If a materialized view is configured to refresh on commit, you should never need to manually refresh it, unless a rebuild is necessary. Remember, refreshing on commit is a very intensive operation for volatile base tables. It makes sense to use fast refreshes where possible.
For on demand refreshes, you can choose to manually refresh the materialized view or refresh it as part of a refresh group.
#Synonyms:A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.
You generally use synonyms when you are granting access to an object from another schema and you don't want the users to have to worry about knowing which schema owns the object.
If you create it as PUBLIC means that the synonym is a public synonym and is accessible to all users
We can create,replace and drop synonyms.
1) Numeric Functions:Numeric functions are used to perform operations on numbers. They accept numeric values as input and return numeric values as output. Few of the Numeric functions are:
ABS (x) - Absolute value of the number 'x'- ABS (1) - output 1
CEIL (x) - Integer value that is Greater than or equal to the number 'x'- CEIL (2.83) and CEIL (2.49) output is 3
FLOOR (x) - Integer value that is Less than or equal to the number 'x' -
Ex: FLOOR (2.83) and FLOOR (2.49) outout is 2
TRUNC (x, y) - Truncates value of number 'x' up to 'y' decimal places .
Ex:TRUNC (140.234, 2) output 140.23, TRUNC (-54, 1) output 54,TRUNC (5.7) output 5.
ROUND (x, y) Rounded off value of the number 'x' up to the number 'y' decimal places.
Ex:ROUND (125.456, 1) output 125.4,ROUND (124.456, -1) output 120
2) Character or Text Functions: Character or text functions are used to manipulate text strings. They accept strings or characters as input and can return both character and number values as output.
LOWER(string_value) LOWER('Good Morning') good morning
UPPER(string_value) UPPER('Good Morning') GOOD MORNING
INITCAP(string_value) INITCAP('GOOD MORNING') Good Morning
LTRIM(string_value, trim_text) LTRIM ('Good Morning', 'Good) Morning
RTRIM (string_value, trim_text) RTRIM ('Good Morning', ' Morning') Good
TRIM (trim_text FROM string_value) TRIM ('o' FROM 'Good Morning') Gd Mrning
SUBSTR (string_value, m, n) SUBSTR ('Good Morning', 6, 7) Morning
LENGTH (string_value) LENGTH ('Good Morning') 12
LPAD (string_value, n, pad_value) LPAD ('Good', 6, '*') **Good
RPAD (string_value, n, pad_value) RPAD ('Good', 6, '*') Good**
3) Date Functions:These are functions that take values that are of datatype DATE as input and return values of datatypes DATE, except for the MONTHS_BETWEEN function, which returns a number as output.
Few date functions are as given below.
ADD_MONTHS (date, n) Returns a date value after adding 'n' months to the date 'x'.- Ex:ADD_MONTHS ('16-Sep-81', 3) output is 16-Dec-81
MONTHS_BETWEEN (x1, x2) Returns the number of months between dates x1 and x2.- Ex:MONTHS_BETWEEN ('16-Sep-81', '16-Dec-81') output is 3
ROUND (x, date_format) Returns the date 'x' rounded off to the nearest century, year, month, date, hour, minute, or second as specified by the 'date_format'.
TRUNC (x, date_format) Returns the date 'x' lesser than or equal to the nearest century, year, month, date, hour, minute, or second as specified by the 'date_format'.
NEXT_DAY (x, week_day) Returns the next date of the 'week_day' on or after the date 'x' occurs. Ex:NEXT_DAY ('01-Jun-08', 'Wednesday') output is 04-JUN-08.
LAST_DAY (x) It is used to determine the number of days remaining in a month from the date 'x' specified.- Ex: LAST_DAY ('01-Jun-08') output is 30-Jun-08.
SYSDATE :Returns the systems current date and time.
NEW_TIME ('01-Jun-08', 'IST', 'EST') output is 31-May-08
NEW_TIME (x, zone1, zone2) Returns the date and time in zone2 if date 'x' represents the time in zone1.
4) Conversion Functions:
These are functions that help us to convert a value in one form to another form. For Ex: a null value into an actual value, or a value from one datatype to another datatype like NVL, TO_CHAR, TO_NUMBER, TO_DATE.
Few of the conversion functions available in oracle are:
TO_CHAR (x [,y]) Converts Numeric and Date values to a character string value. It cannot be used for calculations since it is a string value.
Ex:TO_CHAR (3000, '$9999').
TO_CHAR (SYSDATE, 'Day, Month YYYY') output is $3000 Monday, June 2008.
TO_DATE (x [, date_format])Converts a valid Numeric and Character values to a Date value. Date is formatted to the format specified by 'date_format'.
Ex:TO_DATE ('01-Jun-08') output 01-Jun-08.
NVL (x, y) If 'x' is NULL, replace it with 'y'. 'x' and 'y' must be of the same datatype.
Ex: NVL (null, 1) output 1
DECODE (a, b, c, d, e, default_value) Checks the value of 'a', if a = b, then returns 'c'. If a = d, then returns 'e'. Else, returns default_value.
5)Analytic Functions:
RANK : RANK gives you the ranking within your ordered partition.RANK is an Analytical function as well as Aggregate function in Oracle. It returns the rank of the row within the group.
Ex: Agrregate Function:Find the rank of the employee whose employee number is 18 and salary is 7700.
select rank(7700,18) WITHIN GROUP (ORDER BY salary,empno) as sp from emp;
SP
———-
7
Ex: As an Analytical Function:Find the highest salary holder in each department.
select * from
(
select empno,salary,
rank() over(partition by deptno order by salary desc) as sal_rank from emp
)
where sal_rank = 1;
EMPNO SALARY SAL_RANK
———- ———- ———-
13 9000 1
17 9500 1
19 8500 1
23 7800 1
10 12000 1
DENSE_RANK : DENSE_RANK computes the rank of a row in an ordered group of rows.
LEAD - The LEAD function is used to return data from the next row.LEAD has the ability to compute an expression on the next rows (rows which are going to come after the current row) and return the value to the current row.
Syntax:LEAD (
Example:
SELECT deptno, empno, sal,
LEAD(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) NEXT_LOWER_SAL,
LAG(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) PREV_HIGHER_SAL
FROM emp
WHERE deptno IN (10, 20)
ORDER BY deptno, sal DESC;
DEPTNO EMPNO SAL NEXT_LOWER_SAL PREV_HIGHER_SAL
------- ------ ----- -------------- ---------------
10 7839 5000 2450 0
10 7782 2450 1300 5000
10 7934 1300 0 2450
20 7788 3000 3000 0
20 7902 3000 2975 3000
20 7566 2975 1100 3000
20 7876 1100 800 2975
20 7369 800 0 1100
LAG - The LAG function is used to access data from a previous row.
The syntax of LAG is similar except that the offset for LAG goes into the previous rows.
FAQs: What is the difference Between RANK and DENSE_RANK
Ans:RANK and DENSE_RANK both provide rank to the records based on some column value or expression. In case of a tie of 2 records at position N, RANK declares 2 positions N and skips position N+1 and gives position N+2 to the next record. While DENSE_RANK declares 2 positions N but does not skip position N+1.
Example:
SELECT empno, deptno, sal,
RANK() OVER (PARTITION BY deptno
ORDER BY sal DESC NULLS LAST) RANK,
DENSE_RANK() OVER (PARTITION BY
deptno ORDER BY sal DESC NULLS
LAST) DENSE_RANK
FROM emp
WHERE deptno IN (10, 20)
ORDER BY 2, RANK;
EMPNO DEPTNO SAL RANK DENSE_RANK
------ ------- ----- ----- ----------
7839 10 5000 1 1
7782 10 2450 2 2
7934 10 1300 3 3
7788 20 3000 1 1
7902 20 3000 1 1
7566 20 2975 3 2
7876 20 1100 4 3
7369 20 800 5 4
FIRST_VALUE and LAST_VALUE :The FIRST_VALUE analytic function picks the first record from the partition after doing the ORDER BY. The
columns of this first record and results are returned. The LAST_VALUE function is used in similar context except that it acts on the last record of the partition.
SELECT empno, deptno, hiredate ? FIRST_VALUE(hiredate)
OVER (PARTITION BY deptno ORDER BY hiredate) DAY_GAP
FROM emp
WHERE deptno IN (20, 30)
ORDER BY deptno, DAY_GAP;
EMPNO DEPTNO DAY_GAP
---------- ---------- ----------
7369 20 0
7566 20 106
7902 20 351
7788 20 722
7876 20 756
7499 30 0
7521 30 2
7698 30 70
7844 30 200
7654 30 220
7900 30 286
11 rows selected.
FIRST and LAST function
The FIRST function (or more properly KEEP FIRST function) is used in a very special situation. Suppose we rank a group of record and found several records in the first
rank. Now we want to apply an aggregate function on the records of the first rank. KEEP FIRST enables that.
The general syntax is:
Function( ) KEEP (DENSE_RANK FIRST ORDER BY
#Sub Queries:A subquery is best defined as a query within a query. Subqueries enable you to write queries that select data rows for criteria that are actually developed
while the query is executing at run time.
Types of Subqueries.
Single Row Sub Query: Sub query which returns single row output. They mark the usage of single row comparison operators, when used in WHERE conditions.
Multiple row sub query: Sub query returning multiple row output. They make use of multiple row comparison operators like IN, ANY, ALL. There can be sub queries
returning multiple columns also.
Correlated Sub Query: Correlated subqueries depend on data provided by the outer query.This type of subquery also includes subqueries that use the EXISTS operator to
test the existence of data rows satisfying specified criteria.
#Views:Views are known as logical tables. They represent the data of one of more tables. A view derives its data from the tables on which it is based. These tables are called base tables. Views can be based on actual tables or another view also.
Whatever DML operations you performed on a view they actually affect the base table of the view. You can treat views same as any other table. You can Query, Insert,
Update and delete from views, just as any other table.
Views are very powerful and handy since they can be treated just like any other table but do not occupy the space of a table.
We can Create,Replace and Drop views.
#Materialized Views:A materialized view, or snapshot as they were previously known, is a table segment whose contents are periodically refreshed based on a query, either against a local or remote table. Using materialized views against remote tables is the simplest way to achieve replication of data between sites.
Basic Syntax:
CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
[ON PREBUILT TABLE]
AS
SELECT ...;
The BUILD clause options are shown below.
IMMEDIATE : The materialized view is populated immediately.
DEFERRED : The materialized view is populated on the first requested refresh.
The following refresh types are available.
FAST : A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails.
COMPLETE : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.
FORCE : A fast refresh is attempted. If one is not possible a complete refresh is performed.
A refresh can be triggered in one of two ways.
ON COMMIT : The refresh is triggered by a committed data change in one of the dependent tables.
ON DEMAND : The refresh is initiated by a manual request or a scheduled task.
Create Materialized View Logs:Materialized view logs can be created to capture all changes to the base table since the last refresh.
Refresh Materialized Views
If a materialized view is configured to refresh on commit, you should never need to manually refresh it, unless a rebuild is necessary. Remember, refreshing on commit is a very intensive operation for volatile base tables. It makes sense to use fast refreshes where possible.
For on demand refreshes, you can choose to manually refresh the materialized view or refresh it as part of a refresh group.
#Synonyms:A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.
You generally use synonyms when you are granting access to an object from another schema and you don't want the users to have to worry about knowing which schema owns the object.
If you create it as PUBLIC means that the synonym is a public synonym and is accessible to all users
We can create,replace and drop synonyms.
No comments:
Post a Comment