Difference between revisions of "Oracle:Functions"

From mi-linux
Jump to navigationJump to search
m (Protected "Oracle:Functions" ([Edit=Allow only administrators] (indefinite) [Move=Allow only administrators] (indefinite)))
 
(11 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
[[Main Page]] >> [[Oracle|Oracle and SQL]] >> [[Oracle_Workbook|Workbook]] >> Arithmetic Operators and Functions
 
[[Main Page]] >> [[Oracle|Oracle and SQL]] >> [[Oracle_Workbook|Workbook]] >> Arithmetic Operators and Functions
  
−
== Arithmetic Operators ==
+
This section covers the following topics:
  
−
Arithmetic expressions can be included in an SQL command. The following operators may be used in arithmetic expressions:
+
*[[Oracle:Arithmetic_Operators|Arithmetic operators]]
 +
*[[Oracle:Group_Functions|Group functions]]
 +
*[[Oracle:Subqueries|Subqueries]]
 +
*[[Oracle:Date_functions|Date functions]]
 +
*[[Oracle:Character_functions|Character functions]]
  
−
<code>
 
−
:+ plus
 
−
: - minus
 
−
: * multiply
 
−
:/ divide
 
−
</code>
 
  
−
Parentheses may be used in the arithmetic expressions.
+
Not all of the functions available have been mentioned in these pages. Each version of the Oracle DBMS generally introduces new features. See the [[http://oradb-srv.wlv.ac.uk/E50529_01/index.htm Oracle manual]] for the full set of functions available in the latest version of the DBMS.
  
−
== Some Example Queries Involving Arithmetic Expressions ==
 
  
−
To calculate total monthly compensation (salary plus commission) for every salesperson, ordered by compensation:
+
----
  
−
SELECT  ENAME,  SAL,  COMM,  SAL + COMM AS total_sal
+
Return to the [[Oracle_Workbook|Workbook]].
−
FROM  EMP 
 
−
WHERE  JOB  =  'SALESMAN' 
 
−
ORDER BY total_sal DESC;
 
−
 
 
−
Note, when creating new columns, i.e., the sum of SAL+COMM is creating a temporary new column, the column does not have an actual name. If you wish to refer to this column in an order by clause then you can either repeat the expression again, give the new column an alias as seen above or refer to the column's position in the select list, which in this case is the fourth one.
 
−
 
 
−
To list employees whose commission is greater than 25% of their salary:
 
−
 
−
SELECT  ENAME,  SAL,  COMM FROM  EMP
 
−
WHERE  COMM  >  0.25  *  SAL;
 
−
 
 
−
To calculate total annual compensation for all salespeople:
 
−
 
 
−
SELECT  ENAME,  SAL,  COMM,  12  *  (SAL  +  COMM) AS compensation
 
−
FROM  EMP 
 
−
WHERE  JOB  =  'SALESMAN';
 
−
 
 
−
 
 
−
== Arithmetic Functions ==
 
−
 
 
−
The following arithmetic functions can be used in SQL commands:
 
−
 
 
−
{| class="wikitable"
 
−
!Function!!Returns
 
−
|-
 
−
|ABS(X)||absolute value of X
 
−
|-
 
−
|CEILING(X)||smallest integer > X
 
−
|-
 
−
|FLOOR(X)||largest integer < X
 
−
|-
 
−
|MOD(X,N)||remainder of X divided by N
 
−
|-
 
−
|ROUND(X,N)||X rounded to N places
 
−
|-
 
−
|SIGN(X)||if X<0, -1; if X=0, 0; if X>0, 1
 
−
|-
 
−
|SQRT(X)||square root of X
 
−
|}
 
−
 
 
−
where X is an attribute name and N is an integer.
 
−
 
 
−
Note that when an expression or individual function refers to a column that contains a null value, the result is also null.
 
−
 
 
−
 
 
−
 
 
−
== Next Step ==
 
−
 
 
−
[[Oracle:Group_Functions|Group Functions]].
 

Latest revision as of 15:37, 4 March 2016

Main Page >> Oracle and SQL >> Workbook >> Arithmetic Operators and Functions

This section covers the following topics:


Not all of the functions available have been mentioned in these pages. Each version of the Oracle DBMS generally introduces new features. See the [Oracle manual] for the full set of functions available in the latest version of the DBMS.


Return to the Workbook.

Retrieved from "https://mi-linux.wlv.ac.uk/wiki/index.php?title=Oracle:Functions&oldid=8915"