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 provides specific examples on how to use arithmetic operators in queries.

Examples

Query the number and price of commodities purchased by customers who meet the specified conditions. The product of the number and the price is the total payment of each commodity. To do this, add a column t2.ol_quantity * t3.i_price with the alias item_sum_price to the SELECT list.

1. Create a table named cust.

   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 a table named ordr.

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

3. Create a table named item.

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

4. Insert data into 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 into 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 into 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 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