The round functions allows you to round up any number to a certain number of decimal places. For example: round(6.02,1) would return 6.0 round(6.789,2) would return 6.79 See Ceil to round numbers up to the nearest integer. See Floor to
Group_id – Oracle SQL Function
GROUP_ID assigns a number to each group defined in a GROUP BY clause, GROUP_ID can be used to easily see duplicated groups in query results. Example: select avg(salary), mgr_id, group_id() gid from EMP group by mgr_id; AVG(SALARY) MGR_ID GID ———————-
Replace – Oracle SQL Function
The replace functions allows you to replace all occurences of one string with another. The syntax for the replace function is: replace ( string , search_string, change_to) string is the source string. search_string is the character or set of characters
Max – Oracle SQL Function
MAX returns the maximum value of the expression argument. Syntax: MAX(expression) Example: SELECT max(salary) as max_salary from JOBS; max_salary ——————- 40000
Substr – Oracle SQL Function
The substr functions allows you to extract a substring from a string. The syntax for the substr function is: substr ( string , start_position , [ length ]) string is the source string. start_position is the position for extraction. The
Median – Oracle SQL Function
MEDIAN returns the median value of the expression argument. Syntax: MEDIAN(expression) Example: SELECT MEDIAN(max_salary) as med FROM JOBS; MED —————- 10000
Nvl – Oracle SQL Function
The NVL function lets you substitutes a non-value when a null value is encountered. The syntax for the NVL function is: NVL ( string1, replace_with ) string1 is the string to test for a null value. replace_with is the value
Rank – Oracle SQL Function
RANK determines the rank of one or more of the expression exp within a result set. Syntax: RANK(expression…) WITHIN GROUP (ORDER BY expression) Example: SELECT RANK(10000) WITHIN GROUP (ORDER BY salary DESC) as RESULT FROM EMP; RESULT ——————- 16
Avg – Oracle SQL Function
AVG returns the average value of a column. Syntax: AVG(column name) e.g. SELECT AVG(SALARY) FROM EMPLOYEE
Stddev – Oracle SQL Function
STDDEV returns the sample standard deviation of the expression. STDDEV will return zero when it has only one row of input data. Syntax: STDDEV(expression) Example: SELECT STDDEV(COMMISSION_PCT) FROM EMP; STDDEV(COMMISSION_PCT) ——————————————– .0857432345