SQL definitions

Primary Key: A column in a table whose values uniquely identify the rows in the table. A primary key value cannot be NULL.
Unique Key: Unique Keys are used to uniquely identify each row in an Oracle table. There can be one and only one row for each unique key value.
Surrogate Key: A system generated key with no business value. Usually implemented with database generated sequences.
Foreign key (FK): A column or combination of columns that is used to establish and enforce a link between the data in two tables

Composite key consists of two or more columns, designated together as a table's primary key. Multiple-column primary keys can be defined only as table-level constraints:
Candidate key is a combination of attributes that can be uniquely used to identify a database record without any extraneous data. Each table may have one or more candidate keys. One of these candidate keys is selected as the table primary key.
Rownum is used to order the datas according to their ranks and it is used in inline views.
Level pseudocolumn is used in hierarchial retrival of database.....
An array is a container object that holds a fixed number of values of a single type. The length of an array is established when the array is created.

SQL statements

Data Retrieval
SELECT

Data Manipulation
INSERT
UPDATE
DELETE
MERGE

Data definition
CREATE
ALTER
DROP
RENAME
TRUNCATE

Transactional control
COMMIT
ROLLBACK
SAVEPOINT

Data Control
GRANT
REVOKE

Eliminating Duplicate rows : Use DISTINCT keyword in SELECT command
Limit rows : Use WHERE clause
Display rows in a range of values : Use BETWEEN clause
Test for values in a list :Use IN clause
Perform wildcard searches of valid search string values :Use LIKE clause
Test for nulls :Use IS NULL operator
Sort rows: Use ORDER BY clause ASC or DESC

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