Performing Arithmetic

Oracle allows you to perform arithmetic in SQL statements using arithmetic expressions, including addition, subtraction, multiplication, and division. Arithmetic expressions consist of two operands ” numbers or dates ”and an arithmetic operator . The four arithmetic operators are shown in the following table.

Operator	Description
+	Addition
-	Subtraction
*	Multiplication
/	Division

The following example shows how to use the multiplication operator (*) to calculate 2 multiplied by 6 (the numbers 2 and 6 are the operands):

  SELECT 2*6   FROM dual;  2*6 ----------  12 

As you can see from this example, the correct result of 12 is displayed. The use of 2*6 in this SQL statement is an example of an expression . An expression may contain a combination of columns , literal values, and operators.

The dual Table

You'll notice the use of the dual table in the previous example. I mentioned the dual table in the previous chapter ” dual is a table that contains a single row. The following output from the DESCRIBE command shows the structure of the dual table, along with a SELECT statement that selects the row in the dual table:

  DESCRIBE dual;  Name                                      Null?    Type  ----------------------------------------- -------- -----------  DUMMY                                              VARCHAR2(1)  SELECT *   FROM dual;  D - X 

Notice the dual table has one VARCHAR2 column named dummy , and contains a single row with the value X.

 

Using Date Arithmetic

You can use the addition and subtraction operators with dates. You can add a number ”representing a number of days ”to a date. The following example adds 2 days to July 31, 2003, and displays the resulting date:

  SELECT TO_DATE('31-JUL-2003') + 2   FROM dual;  TO_DATE(--------- 02-AUG-03 

Note  

TO_DATE() is a function that converts a string to a date. You ll learn more about TO_DATE() in Chapter 4.

The next example subtracts two days from August 2, 2003:

  SELECT TO_DATE('02-AUG-2003') - 2   FROM dual;  TO_DATE(' --------- 31-JUL-03 

You can also subtract one date from another, yielding the number of days between the two dates. The following example subtracts July 31, 2003, from August 2, 2003:

  SELECT TO_DATE('02-AUG-2003') - TO_DATE('31-JUL-2003')   FROM dual;  TO_DATE('02-AUG-2003')-TO_DATE('31-JUL-2003') ---------------------------------------------  2 

Using Columns in Arithmetic

Operands do not have to be literal numbers or dates, they may also be columns from a table. In the following example, the name and price columns are selected from the products table; notice that 2 is added to the value in the price column using the addition operator ( + ) to form the expression price + 2 :

  SELECT name, price + 2   FROM products;  NAME PRICE+2 ------------------------------ ---------- Modern Science 21.95 Chemistry 32 Supernova 27.99 Tank War 15.95 Z Files 51.99 2412: The Return 16.95 Space Force 9 15.49 From Another Planet 14.99 Classical Music 12.99 Pop 3 17.99 Creative Yell 16.99 My Front Line 15.49 

You can also combine more than one operator in an expression. In the following example, the price column is multiplied by 3, and then 1 is added to the resulting value:

  SELECT name, price * 3 + 1   FROM products;  NAME PRICE*3+1 ------------------------------ ---------- Modern Science 60.85 Chemistry 91 Supernova 78.97 Tank War 42.85 Z Files 150.97 2412: The Return 45.85 Space Force 9 41.47 From Another Planet 39.97 Classical Music 33.97 Pop 3 48.97 Creative Yell 45.97 My Front Line 41.47 

The normal rules of arithmetic operator precedence apply in SQL: multiplication and division are performed first, followed by addition and subtraction. If operators of the same precedence are used, they are performed from left to right. For example, if you were to use the following expression: 10*12/3 “1 , 10 multiplied by 12 would be calculated first, yielding a result of 120; 120 would then be divided by 3, yielding 40; finally, 1 would be subtracted from 40, yielding 39:

  SELECT 10 * 12 / 3  1   FROM dual;  10*12/3-1 ----------  39 

You can also use parentheses () to specify the order of execution for the operators. For example:

  SELECT 10 * (12 / 3  1)   FROM dual;  10*(12/3-1) -----------  30 

In this example, the parentheses are used to calculate 12/3-1 first, the result of which is then multiplied by 10 ”yielding 30 as the final answer.