General Function

 

General Function :

Aim	Functions
Conditional Functions	CASE, DECODE, COALESCE
Handle NULL values	NVL, NVL2, COALESCE, NULLIF
Compare multiple values and return the highest/lowest	GREATEST, LEAST
Retrieve system/user-related information	USER, SYSDATE, SYSTIMESTAMP
Convert data types	TO_CHAR, TO_NUMBER, TO_DATE
Perform numeric operations	ROUND, TRUNC, CEIL, FLOOR, MOD

Conditional Functions :

CASE Statement

The CASE function evaluates multiple conditions and returns different values.

SELECT name, salary,

       CASE

           WHEN salary >= 9000 THEN 'High'

           WHEN salary BETWEEN 6000 AND 8999 THEN 'Medium'

                            ELSE 'Low'

    END AS salary_category

FROM employees;

Output

name	salary	salary_category
Harry	10000	High
Tom	9000	High
Alice	8000	Medium
Mary	7000	Medium
Bob	7000	Medium
John	5000	Low

 

DECODE Function

The DECODE function provides IF-THEN-ELSE logic but in a simpler syntax than CASE.

SELECT name, salary,

       DECODE(

           TRUNC(salary/1000),

           10, 'High',

           9, 'High',

           8, 'Medium',

           7, 'Medium',

           5, 'Low',

           'Unknown'

       ) AS salary_category

FROM employees;

 

 

 

Output

name	salary	salary_category
Harry	10000	High
Tom	9000	High
Alice	8000	Medium
Mary	7000	Medium
Bob	7000	Medium
John	5000	Low

 

 

Feature	DECODE	CASE
SQL Standard?	Oracle-specific (Works only in Oracle DB)	Standard SQL (Works in all databases)
Condition Types	Only equality (=)	Supports =, >, <, BETWEEN, LIKE, etc.
Multiple Conditions?	❌ No	✅ Yes
Supports Complex Expressions?	❌ No	✅ Yes
Nested Conditions?	❌ No	✅ Yes
Performance	✅ Faster for simple cases	⚠️ Slightly slower but more flexible
Works Outside Oracle?	❌ No	✅ Yes (ANSI SQL Standard)
Usage	Used for simple conditional checks	Used for complex and multi-condition logic
Syntax Simplicity	✅ Simpler for basic conditions	⚠️ Slightly more complex but powerful
Example Use Case	Replace values based on exact match	Handle range-based conditions, multiple conditions

 

 

COALESCE Function

The COALESCE function returns the first non-null value from a list of expressions.

SELECT name, COALESCE(bonus, salary * 0.1, 1000) AS final_bonus

FROM employees;

Output

name	bonus	final_bonus
Harry	NULL	1000 (salary * 0.1 used)
Tom	2000	2000
Alice	NULL	1000
Mary	NULL	1000

 

 

 

 

 

Handle NULL Values :

NVL()

Replaces NULL with a default value

SELECT name, salary, NVL(commission, 0) AS final_commission FROM employees;

name	salary	commission	final_commission
Harry	10000	NULL	0
Tom	9000	500	500

 

NVL2()

Returns one value if NULL, and another if NOT NULL.

SELECT name, salary, NVL2(commission, 'Has Bonus', 'No Bonus') AS bonus_status

FROM employees;

Output

name	salary	commission	bonus_status
Harry	10000	NULL	No Bonus
Tom	9000	500	Has Bonus

 

Returns "Has Bonus" if commission is NOT NULL, otherwise "No Bonus".

 

 

 

 

NULLIF Function

Returns NULL if two values are equal, otherwise returns the first value.

SELECT name, salary, NULLIF(salary, 7000) AS new_salary

FROM employees;

Output

name	salary	new_salary
Mary	7000	NULL
Bob	7000	NULL
John	5000	5000

 

Returns NULL if salary is 7000, otherwise returns salary.

 

 

 

 

 

 

GREATEST Function

Returns the largest value from a list.

SELECT name, GREATEST(salary, 5000, 7000) AS max_value FROM employees;

Output

name	salary	max_value
Harry	10000	10000
Tom	9000	9000
Alice	8000	8000
John	5000	7000

Returns the maximum value among salary, 5000, and 7000.

 

LEAST Function

Returns the smallest value from a list.

SELECT name, LEAST(salary, 5000, 7000) AS min_value FROM employees;

Output

name	salary	min_value
Harry	10000	5000
Tom	9000	5000
Alice	8000	5000
John	5000	5000

 Returns the smallest value among salary, 5000, and 7000.