Oracle Functions BY ANIL NAIN 9416077273

ANIL NAIN 9416077273

Functions are used to manipulate data items and return a result. Functions follow the format of: funtion_name (argument, argumnent2,…) An argument is a user-supplied variable or constant. The structure of functions is such that it accepts zero or more arguments.
Example
AVG Syntax AVG ([DISTINCT ALL]n)
Purpose Returns average value of n, ignoring null values.
Example SELECT AVG (sell_price ) “ Average”
FROM product_master,
Output Average
--------------
2012.3654
Note
In the above SELECT statement AVG function is used to calculate the average selling price of all products. The selected column is renamed as ‘Average’ in the output.
MIN Syntax MIN ([ISTINCT/ ALL] expr)
Purpose Returns minimum value of expr.
Example SELECT MIN(s_order_date) “Minimum Date”
FROM sales_order;
Output Minimum Date
----------------------
26- Jan-93
COUNT (expr) Syntax COUNT ([DISTINCT/ALL] expr)
Purpose Returns the number of rows where expr is not null.
Example SELECT COUNT (order_no) “No of Orders”
FROM sales_order
Output No of Orders
-------------------
5
COUNT(*) Syntax COUNT (*)
Purpose Returns the number of rows in the table, including duplicates and those with nulls.
Example SELECT COUNT(*) “Total” FROM client master;
Output Total
---------
15
MAX Syntax MAX ([DISTINCT/ALL]expr)
Purpose Returns maximum value of expr.
Example SELECT MAX (qty._ordered) “ Maximum”
FROM sale_order_details.
Output Maximum
---------------
5000.00
SUM Syntax SUM ([DISTINCT/ALL]n)
Purpose Returns sum of values of n.
Example SELECT SUM (qty_ordered)”Total Qty”
FROM sales_order_details
WHERE product_mo=’P00001’,
Output Total Qty
---------------
29025.00
ABS Syntax ABS (n)
Purpose Returns the absolute value of n.
Example SELECT ABX(-15) “Absolute” FROM dual;
Otuput Absolute
----------
15
POWER Syntax ABS(n)
Purpose Returns m raised to nth power, n must be an integer, else an error is returned.
Example SELECT POSWER (3,2) “Raised” FROM dual;
Otuput Raised
------------
9
ROUND Syntax ROUND (n[,m])
Purpose Returns n rounded to m places right of the decimal point.If m is omitted, to 0 places m can be negative to round off digits left of the decimal point m must be an integer.
Example SELECT ROUND (15, 19, 1)”Round” FROM dual;
Output Round
-----------
15.2
SQRT Syntax SQRT (n)
Purpose Returns square root of n; if n<0, name="’TURNER’" sel_price="sell_price+">