The ceil functions allows you to round up any number to the next integer. For example: ceil(6.02) would return 7 ceil(7.59) would return 8 See Round to round numbers to a particular decimal place See Floor to round a number
Dense_rank – Oracle SQL Function
DENSE_RANK returns a NUMBER representing the rank of a row within a group of rows. Syntax: DENSE_RANK(expression1,…) WITHIN GROUP (ORDER BY) Example: SELECT DENSE_RANK(5000,103) WITHIN GROUP (ORDER BY SALARY, MGR_ID) as RESULT FROM EMP; RESULT ———— 43
Round – Oracle SQL Function
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