SQL Functions

SQL FUNCTIONS
Perform calculations on data
Modify individual data items
Manipulate output for groups of rows
Format dates and numbers for display
Convert column data types

SINGLE ROW FUNCTIONS
Character functions
1.Case manipulation
2.Character manipulation
Number functions
Date functions

Conversion functions
Implicit data type
Explicit data type

General functions
NVL(expr1,expr2)
NVL2(expr1,expr2,expr3)
NULLIF(expr1,expr2)
COALSECE(expr1,expr2,..exprn)
CASE DECODE

MULTIPLE ROW FUNCTIONS
Functions can manipulate groups of rows to give one result per group of rows. These functions are known as group functions
CARTESIAN PRODUCT When a join condition is invalid or omitted completely, the result is a Cartesian product, in which allcombinations of rows are displayed. All rows in the first table are joined to all rows in the secondtable.

JOINS and prior Equijoin - The relationship between the EMPLOYEES and DEPARTMENTS tables is an equijoin—that is, valuesin the DEPARTMENT_ID column on both tables must be equal. Frequently, this type of join involvesprimary and foreign key complements
Non -equijoin - A non-equijoin is a join condition containing something other than an equality operator.
Outer join (+) - If a row does not satisfy a join condition, the row will not appear in the query result. For example, in the equijoin condition of EMPLOYEES and DEPARTMENTS tables, employee Grant does not appear because there is no department ID recorded for her in the EMPLOYEES table. Instead of seeing 20employees in the result set, you see 19 records.
Self join - Sometimes you need to join a table to itself. To find the name of each employee’s manager, you need to join the EMPLOYEES table to itself, or perform a self join.
Cross join - The CROSS JOIN clause produces the cross product of two tables. This is the same as a Cartesian product between the two tables.
Natural joins - 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.
Using - If several columns have the same names but thedata types do not match, the NATURAL JOINclause can be modified with the USING clause tospecify the columns that should be used for an equijoin.
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.
Left outer join - The result of a left outer join for tables A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B).
Right outer join - A right outer join closely resembles a left outer join, except with the tables reversed. Every record from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in A.
Full outer join - A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.
Group Functions - Unlike single-row functions, group functions operate on sets of rows to give one result per group.These sets may be the whole table or the table split into groups.
Types of G.F - AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE
Having - You use the HAVING clause to restrict groups.• You cannot use group functions in the WHERE clause.
PL/SQL - It is the procedural extension to SQL with design features of programming languages.Data manipulation and query statements of SQL are included within procedural units of code.
Benefits of pl sql
It plays a central role in both the oracle server and oracle development tools.
Improved Performance
Moduralized program development
Portability
Identifiers
Benefits of sub programs Integration :
Easy maintenance
Improved data security and integration
Improved performance
Improved code clarity

PL SQL BLOCK
DECLARE - optional Variables, cursors, user defined exceptions
BEGIN - mandatory-
sql statements-
pl sql statements
EXCEPTION -optional Actions to perform when errors occur
END; - mandatory

VARIABLES
Temporary storage of data
Manipulation of stored values
Reusability
Ease of maintenance

Types of variables
PL SQL
Scalar – char, varchar2, long, long raw, number, binary integer, pls_integer, Boolean, date, timestamp, timestamp with time zone, timestamp with local time zone, interval year to month, interval day to second.
Composite – Table, record, nested table, and varray types
Reference
LOB
NON-PL SQL
Bind
Host