Oracle

Oracle :- Oak Ridge Analytical Computer Logical Engine

Statements of Oracle:-

DDL – Data definition language

1)      Create – Used to make new database object

2)      Alter- To modify the structure of the table

3)      Add – Used to add new column

4)      Modify – Used to modify structure of existing columns

5)      Drop – Used to remove columns

DML – Data manipulation language

1)      Insert – New statement to be inserted

2)      Update – Used to modify the existing records

3)      Delete – Used to remove the records from the table

DCL – Data control language

1)      Grant – Used to give permissions

2)      Revoke – Used to cancel permissions for (insert, update, delete and select)

TCL – Transaction control language

1)      Commit – Used to save changes made by DML statements

2)      Roll back – It is used to cancel transactions which are not saved

3)      Save Point – It is used to mark the transaction here

DRL – Data retrieval language

1)      Select – Used to retrive data from tables it is for read only purpose

Set Operators:-

Union – Union operator returns all rows selected by either query. Use the union operator to return all rows from multiple tables and eliminate any duplicate rows.

Null values are not ignored during duplicate checking.

Union all – Combines the result of two select statements into one result set including the duplicates.

Minus – Minus operator returns rows from the first query that are not present in the second query.

Mathematical Functions:-

ABS – This function returns the absolute value of a number.

Syntax – ABS(number)

Ex- select ABS(-32) from dual; o/p – 32

ABS(-32.4)

o/p: 32.4

ABS(-32.45)

o/p: 32.4

ABS(32.67)

o/p: 32.67

ABS(32.67 * -1)

o/p: 32.67

CEIL - This function returns the smallest integer value that is greater than or equal to a number.

Syntax- CEIL( number )

Ex - CEIL(32.65)

o/p: 33

CEIL(32.1)

o/p: 33

CEIL(32)

o/p: 32

CEIL(-32.65)

o/p: -32

CEIL(-32)

o/p: -32

EXTRACT - This function extracts a value from a date or interval value.

You can only extract YEAR, MONTH, and DAY from a DATE.

You can only extract TIMEZONE_HOUR and TIMEZONE_MINUTE from a timestamp with a time zone datatype.

Syntax:

EXTRACT (

{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }

| { TIMEZONE_HOUR | TIMEZONE_MINUTE }

| { TIMEZONE_REGION | TIMEZONE_ABBR }

FROM { date_value | interval_value } )

Ex -

EXTRACT(YEAR FROM DATE '2003-08-22')

o/p: 2003

EXTRACT(MONTH FROM DATE '2003-08-22')

o/p: 8

EXTRACT(DAY FROM DATE '2003-08-22')

o/p: 22

FLOOR - This function returns the largest integer value that is equal to or less than a number.

Syntax- FLOOR( number )

Ex -

FLOOR(5.9)

o/p: 5

FLOOR(34.29)

o/p: 34

FLOOR(-5.9)

o/p: -6

ROUND - This function returns a number rounded to a certain number of decimal places.

Syntax (WITH NUMBERS): ROUND( number, [ decimal_places ] )

Ex-

ROUND(125.315)

o/p: 125

ROUND(125.315, 0)

o/p: 125

ROUND(125.315, 1)

o/p: 125.3

ROUND(125.315, 2)

o/p: 125.32

ROUND(125.315, 3)

o/p: 125.315

ROUND(-125.315, 2)

o/p: -125.32

TRUNC - This function returns a number truncated to a certain number of decimal places.

Syntax (WITH NUMBERS) : TRUNC( number, [ decimal_places ] )

Ex-

TRUNC(125.815)

o/p: 125

TRUNC(125.815, 0)

o/p: 125

TRUNC(125.815, 1)

o/p: 125.8

TRUNC(125.815, 2)

o/p: 125.81

TRUNC(125.815, 3)

o/p: 125.815

TRUNC(-125.815, 2)

o/p: -125.81

TRUNC(125.815, -1)

o/p: 120

TRUNC(125.815, -2)

o/p: 100

TRUNC(125.815, -3)

o/p: 0

TO_CHAR – This function converts a number or date to a string.

Syntax : TO_CHAR( value, [ format_mask ], [ nls_language ] )

Ex-

TO_CHAR(1210.73, '9999.9')

o/p: ' 1210.7'

TO_CHAR(-1210.73, '9999.9')

o/p: '-1210.7'

TO_CHAR(1210.73, '9,999.99')

o/p: ' 1,210.73'

TO_CHAR(1210.73, '$9,999.00')

o/p: ' $1,210.73'

TO_CHAR(21, '000099')

o/p: ' 000021'

TO_CHAR(sysdate, 'yyyy/mm/dd')

o/p: '2003/07/09'

TO_CHAR(sysdate, 'Month DD, YYYY')

o/p: 'July 09, 2003'

TO_CHAR(sysdate, 'FMMonth DD, YYYY')

o/p: 'July 9, 2003'

TO_CHAR(sysdate, 'MON DDth, YYYY')

o/p: 'JUL 09TH, 2003'

TO_CHAR(sysdate, 'FMMON DDth, YYYY')

o/p: 'JUL 9TH, 2003'

TO_CHAR(sysdate, 'FMMon ddth, YYYY')

o/p: 'Jul 9th, 2003'

TO_CHAR(sysdate, 'FMMonth DD, YYYY')

o/p: 'July 9, 2003'

TO_CHAR(sysdate, 'FMMON DDth, YYYY')

o/p: 'JUL 9TH, 2003'

TO_CHAR(sysdate, 'FMMon ddth, YYYY')

o/p: 'Jul 9th, 2003'

TO_DATE - This function converts a string to a date.

Syntax : TO_DATE( string1, [ format_mask ], [ nls_language ] )

Ex-

TO_DATE('2003/07/09', 'yyyy/mm/dd')

o/p: date value of July 9, 2003

TO_DATE('070903', 'MMDDYY')

o/p: date value of July 9, 2003

TO_DATE('20020315', 'yyyymmdd')

o/p: date value of Mar 15, 2002

TO_NUMBER - This function converts a string to a number.

Syntax : TO_NUMBER( string1, [ format_mask ], [ nls_language ] )

Ex-

TO_NUMBER('1210.73', '9999.99')

o/p: 1210.73

TO_NUMBER('546', '999')

o/p: 546

TO_NUMBER('23', '99')

o/p: 23

TO_NUMBER('1210.73')

o/p: 1210.73

TO_TIMESTAMP - This function converts a string to a timestamp.

Syntax : TO_TIMESTAMP( string1, [ format_mask ] [ 'nlsparam' ] )

Ex:

TO_TIMESTAMP('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS')

would return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.

TO_TIMESTAMP('2003/DEC/13 10:13:18', 'YYYY/MON/DD HH:MI:SS')

would also return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.

TO_TIMESTAMP_TZ - This function converts a string to a timestamp with time zone.

Syntax:TO_TIMESTAMP_TZ( string1 , [ format_mask ] [ 'nlsparam' ] )

Ex-

TO_TIMESTAMP_TZ('2003/12/13 10:13:18 -8:00', 'YYYY/MM/DD HH:MI:SS TZH:TZM')

would return '13-DEC-03 10.13.18.000000000 AM -08:00' as a timestamp with time zone value.

TO_TIMESTAMP_TZ('2003/DEC/13 10:13:18 -8:00', 'YYYY/MON/DD HH:MI:SS TZH:TZM')

would also return '13-DEC-03 10.13.18.000000000 AM -08:00' as a timestamp with time zone value.