|
Page 1 of 7
This Week we will be learning "Restricting and Sorting Data" covers the following topics. WHERE Clause - Comparison Operators; Logical Operator; Using the Logical Operator, AND; Using the logical Operator, OR; Using the Logical Operator, NOT; Nesting Logical Operators; Other Comparison Keywords; Using the Keyword, LIKE; Using the Keywords, BETWEEN...AND; Using the Keyword, IN; Using the Keyword, IS NULL ORDER BY Clause - Using the ORDER BY Clause
Expressions in a SELECT statement: Arithmetic operations can be performed using SELECT statement as shown below.
SQL> SELECT 10+5 FROM DUAL;
10+5
----------
15
SQL> SELECT 10-5 FROM DUAL;
10-5
----------
5
SQL> SELECT 10*5 FROM DUAL;
10*5
----------
50
SQL> SELECT 10/5 FROM DUAL;
10/5
----------
2
Numeric Functions: These functions operate on Numeric data hence is the name.
Note: Argument “num” in the following functions is any float-valued number.
ABS(num): Returns absolute value of the given number.(i.e. Always positive value)
SQL> SELECT ABS(10) FROM DUAL;
ABS(10)
----------
10
SQL> SELECT ABS(-10) FROM DUAL;
ABS(-10)
----------
10
CEIL(num): It returns the smallest integer greater than the given number.
SQL> SELECT CEIL(123.456) FROM DUAL;
CEIL(123.456)
-------------
124
FLOOR(num): It returns the largest integer smaller than the given value.
SQL> SELECT FLOOR(123.456) FROM DUAL;
FLOOR(123.456)
--------------
123
LN(num) : It returns natural logarithm value of “num” .
SQL> SELECT LN(10) FROM DUAL;
LN(10)
----------
2.30258509
LOG(m, n): It returns logarithm of “n” with base “m”.
SQL> SELECT LOG(100,10) FROM DUAL;
LOG(100,10)
-----------
.5
SQL> SELECT LOG(10,100) FROM DUAL;
LOG(10,100)
-----------
2
MOD(m, n) : It returns remainder of “m” divided by “n”.
SQL> SELECT MOD(10,3) FROM DUAL;
MOD(10,3)
----------
1
POWER(m, n): It returns value equal to “m” raised by “n”.
SQL> SELECT POWER(10,2) FROM DUAL;
POWER(10,2)
-----------
100
ROUND(m, n): It rounds the given float-valued number “m” to the “n” places after the decimal
SQL> SELECT ROUND(1.23456) FROM DUAL;
ROUND(1.23456)
--------------
1
SQL> SELECT ROUND(1.23456,3) FROM DUAL;
ROUND(1.23456,3)
----------------
1.235
SQRT(m): It calculates square root value of number “m”
SQL> SELECT SQRT(9) FROM DUAL;
SQRT(9)
----------
3
TRUNC(m, n): It truncates given float-valued number “m” to “n” places after the decimal.
SQL> SELECT TRUNC(1.23456) FROM DUAL;
TRUNC(1.23456)
--------------
1
SQL> SELECT TRUNC(1.23456,3) FROM DUAL;
TRUNC(1.23456,3)
----------------
1.234
GREATEST(expr1, expr2, …) : It finds the greatest value among the given expressions.
SQL> SELECT GREATEST(4,7,3,5,9,2) FROM DUAL;
GREATEST(4,7,3,5,9,2)
---------------------
9
LEAST(expr1, expr2, …): It finds the Lowest value among the given expressions.
SQL> SELECT LEAST(4,7,3,5,9,2) FROM DUAL;
LEAST(4,7,3,5,9,2)
------------------
2
|