my notes...

BETWEEN ... AND ....

IN(set)

LIKE

IS NULL

WHERE Salary BETWEEN 2500 AND 3500;

WHERE cust_id IN(101, 102, 105);

WHERE job LIKE '_sal%';

_ : single character

% : zero or more

WHERE mgr_id IS NULL;

AND

OR

NOT

WHERE job LIKE '_sal%'
AND mgr_id IS NULL

WHERE job LIKE '_sal%'
OR mgr_id IS NULL;

WHERE cust_id NOT IN(101, 102, 105);

1

Arithmatic operators

2

Concatenation operator

3

Comparison conditions

4

IS [NOT] NULL, LIKE, [NOT] IN

5

[NOT] BETWEEN

6

NOT logical condition

7

AND logical condition

8

OR logical condition

• MONTH_BETWEEN(date1, date2)

:

(+ve if date1>date2)

• ADD_MONTHS(date,n)

:

N= number of calender months

• NEXT_DAY(date,'char')

:

'char' : a number representing the day of week, or a char string.

• LAST_DAY(date)

:

Last date of the month containing date

• ROUND(date[,fmt])

:

If fmt omitted, date rounded to nearest day

• TRUNC(date[,fmt])

:

If fmt omitted, date truncated to nearest day

VARCHAR2 OR CHAR-> NUMBER
VARCHAR2 OR CHAR -> DATE
NUMBER -> VARCHAR2
DATE -> VARCHAR2

VARCHAR2 OR CHAR-> NUMBER
VARCHAR2 OR CHAR -> DATE

TO_CHAR(number|date,[fmt],[nlsparams])

:

Convert a number of date value to VARCHAR2 as per fmt.

nlsparams specifies decimal character, group separater, local currency symbol & international currency symbol.

If nlsparams or any other parameter is omitted, default session parameter values are used.

TO_DATE(char,[fmt],[nlsparams])

:

If fmt is omitted DD-MON-YY is used.

• YYYY

:

Full year in numbers

• YEAR

:

Year spelled out

• MM

:

Two digit value of month

• MONTH

:

Full name of the month

• MON

:

Three letter abbreviation of month

• DY

:

Three letter abbreviation of day-of-week

• DAY

:

Full name of week

• DD

:

Numeric day of month

• / . ,

• “of the”

• TH

• SP

• SPTH or THSP

:

:

:

:

:

Punctuation reproduced in the result

quoted string reproduced in the result

Ordinal Number (eg. DDTH for 4TH)

spelled out number (eg. DDSP for FOUR)

spelled out ordinal number (eg. DDSPTH for FOURTH)

Element

Desc

Example

Result

9

Numeric position

999999

1234

0

Leading zeros

099999

001234

$

Floating dollar sign

$999999

$1234

L

Floating local currency symbol

L999999

FF1234

.

Decimal point in position specified

999999.99

1234.00

,

Comma in position specified

999,999

1,234

MI

Minus sign to right (-ve values)

999999MI

1234-

PR

Parenthesize -ve numbers

999999PR

<1234>

EEEE

Scientific notation

99.999EEEE

1.234E+03

V

Multiply by 10 n times (n=no. of 9s after V)

9999V99

123400

B

Zero value as blank, no zero.

B9999.99

1234.00

NVL (expr1, expr2)

:

Converts a Null value to an actual value

example:

NVL(commission_pct,0);
NVL(hire_date,'02-FEB-98');
NVL(job_id,'Job not allocated');

NVL conversion:

NVL2 (expr1, expr2, expr3)

If expr1 <> NULL returns expr2
if expr1 = NULL returns expr3

example:

NVL2(commission_pct,'SAL+COMM','SAL');

Note :

• expr1 can have any data type.

• expr2,expr3 can have any data type except LONG.

• if data types of expr2 & expr3 are different, expr3 is converted to expr2 data type before comparison.

• Return data type is that of expr2.

• If expr2 is CHAR, then VARCHAR2 is returned.

NULLIF (expr1, expr2)

:

If expr1=expr2 , returns NULL

else return expr1

COALESCE (expr1, expr2,..., exprn)

:

Returns the 1^st non-null expression in the expression list.

Example:

COALESCE (commission_pct, salary,0);

[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr

CASE job_id WHEN 'SAL_CLK' THEN salary * 1.10

END “REVISED_SALARY

[search2, result2,....]
[, default]

DECODE (job_id, 'SAL_CLK', salary * 1.10,