Use arithmetic operators in queries

Common arithmetic operators used in queries include addition (+), subtraction (-), multiplication (*), division (/), negation (-), and modulus (MOD). These operators can be used on numeric columns. This topic describes how to use arithmetic operators in queries and provides examples.

Examples

Query the number and unit price of commodities purchased by customers who meet the specified conditions. The product of the number of commodities and the unit price is the total payment of the commodities. You can add t2.ol_quantity * t3.i_price column to the select_list statement and rename the table as item_sum_price.

1. Create the cust table.

   obclient> CREATE TABLE cust (
               c_id        NUMBER,
               c_first     VARCHAR2(20),
               c_last      VARCHAR2(20),
               c_credit    NUMBER(10, 2)
         );
   Query OK, 0 rows affected
   

2. Create the ordr table.

   obclient> CREATE TABLE ordr (
               o_id         NUMBER,
               o_ol_cnt     NUMBER,
               o_entry_d    DATE
             );
   Query OK, 0 rows affected
   

3. Create the item table.

   obclient> CREATE TABLE item (
               i_id           NUMBER,
               i_name       VARCHAR2(20),
               i_price       NUMBER(10, 2)
               );
   Query OK, 0 rows affected
   

4. Insert data to the cust table.

   obclient> INSERT INTO cust VALUES(101,'Ann','Smith',16.10),
             (102,'Madeleine','Johnson',23.00),
             (103,'Michael','Brown',9.05);
   Query OK, 3 rows affected
   Records: 3  Duplicates: 0  Warnings: 0
   

5. Insert data to the ordr table.

   obclient> INSERT INTO ordr VALUES(102,400,date'2020-01-23'),
             (103,400,date'2020-01-23'),(104,300,date'2020-09-18'),
             (105,100,date'2020-07-01');
   Query OK, 4 rows affected
   Records: 4  Duplicates: 0  Warnings: 0
   

6. Insert data to the item table.

   obclient> INSERT INTO item VALUES(102,'Apple','12.9'),
             (103,'Banana','10.2'),(105,'Pear','9.8');
   Query OK, 3 rows affected
   Records: 3  Duplicates: 0  Warnings: 0
   

7. Query the number and unit price of commodities purchased by customers who meet the specified conditions.

   obclient> SELECT t1.c_first, t1.c_last, t1.c_credit, t2.o_ol_cnt * t3.i_price item_sum_price, t2.o_entry_d, t3.i_name, t3.i_price
             FROM cust t1
             JOIN ordr t2 ON (t1.c_id=t2.o_id)
             JOIN item t3 ON (t2.o_id=t3.i_id)
             ORDER BY t1.c_id, t2.o_id;
   +-----------+---------+----------+----------------+-----------+--------+---------+
   | C_FIRST   | C_LAST  | C_CREDIT | ITEM_SUM_PRICE | O_ENTRY_D | I_NAME | I_PRICE |
   +-----------+---------+----------+----------------+-----------+--------+---------+
   | Madeleine | Johnson |       23 |           5160 | 23-JAN-20 | Apple  |    12.9 |
   | Michael   | Brown   |     9.05 |           4080 | 23-JAN-20 | Banana |    10.2 |
   +-----------+---------+----------+----------------+-----------+--------+---------+
   2 rows in set