General Note: Here we have pulled in the data from a sample database. You can download it from the following link and use it for practicing Click Here For Sample Database

In this tutorial, you will learn the available arithmetic operators and how to use them in SQLite.

Arithmetic Operators

1. Add(+)

2. Subtract(-)

3. Multiply(*)

4. Divide( / )

5. Modulo( % )

Query to add 300 INR to mobile price is as follows


Select mobile_name,price,price+300 from mobile_sales_details

mobile_name             price       price+300 
----------------------  ----------  ----------
Moto X (16 GB) (Black)  23999       24299     
Moto G (Black  with 16  13999       14299     
Moto E (Black)          6999        7299      
Asus Zenfone 4 A400CG   5999        6299      
Asus Zenfone 5 A501CG   9999        10299     
Asus Zenfone 5 A501CG   12999       13299     
Asus Zenfone 6 A600CG   16999       17299     
Alcatel Onetouch Idol   16999       17299     
Samsung Galaxy S Duos   8206        8506      
Samsung Galaxy Star Pr  7599        7899      
Samsung Galaxy S Duos   8214        8514      
Samsung Galaxy Core I8  11795       12095     
Samsung Galaxy Grand 2  18990       19290     
Samsung Galaxy Trend S  9610        9910      
Samsung Galaxy Grand N  14010       14310     
Samsung I8552 - Galaxy  15999       16299     
Samsung Galaxy S4 Mini  24730       25030     
Samsung GT 1200 R/I/M   1299        1599      
Samsung Guru 1200 (Bla  1399        1699      
Samsung Galaxy S5 (Cha  54300       54600     
Samsung Galaxy S4 I950  28071       28371

The old price of moto x black is 23,999(INR) . Now after adding 300(INR) the price is now 24,299. This query affects all the value present in the price column


Select mobile_name,price,price-300 from mobile_sales_details

mobile_name             price       price-300 
----------------------  ----------  ----------
Moto X (16 GB) (Black)  23999       23699     
Moto G (Black  with 16  13999       13699     
Moto E (Black)          6999        6699      
Asus Zenfone 4 A400CG   5999        5699      
Asus Zenfone 5 A501CG   9999        9699      
Asus Zenfone 5 A501CG   12999       12699     
Asus Zenfone 6 A600CG   16999       16699     
Alcatel Onetouch Idol   16999       16699     
Samsung Galaxy S Duos   8206        7906      
Samsung Galaxy Star Pr  7599        7299      
Samsung Galaxy S Duos   8214        7914      
Samsung Galaxy Core I8  11795       11495     
Samsung Galaxy Grand 2  18990       18690     
Samsung Galaxy Trend S  9610        9310      
Samsung Galaxy Grand N  14010       13710     
Samsung I8552 - Galaxy  15999       15699     
Samsung Galaxy S4 Mini  24730       24430     
Samsung GT 1200 R/I/M   1299        999       
Samsung Guru 1200 (Bla  1399        1099      
Samsung Galaxy S5 (Cha  54300       54000

All mobile prices are reduced by 300. This will affect all the price row in the table.

If you want to increase the price of mobile by multiplying with 10 the query as below


Select mobile_name,price,price*10 from mobile_sales_details

mobile_name             price       price*10  
----------------------  ----------  ----------
Moto X (16 GB) (Black)  23999       239990    
Moto G (Black  with 16  13999       139990    
Moto E (Black)          6999        69990     
Asus Zenfone 4 A400CG   5999        59990     
Asus Zenfone 5 A501CG   9999        99990     
Asus Zenfone 5 A501CG   12999       129990
Asus Zenfone 6 A600CG   16999       169990
Alcatel Onetouch Idol   16999       169990    
Samsung Galaxy S Duos   8206        82060     
Samsung Galaxy Star Pr  7599        75990     
Samsung Galaxy S Duos   8214        82140     
Samsung Galaxy Core I8  11795       117950    
Samsung Galaxy Grand 2  18990       189900    
Samsung Galaxy Trend S  9610        96100     
Samsung Galaxy Grand N  14010       140100    
Samsung I8552 - Galaxy  15999       159990    
Samsung Galaxy S4 Mini  24730       247300    
Samsung GT 1200 R/I/M   1299        12990     
Samsung Guru 1200 (Bla  1399        13990     
Samsung Galaxy S5 (Cha  54300       543000    
Samsung Galaxy S4 I950  28071       280710

Price column showing the actual price of mobile and the price*10 column showing the price which is 10 times the original price.

The below query is used to divide the price by 10


Select mobile_name,price,price/10 from mobile_sales_details

mobile_name             price       price/10  
----------------------  ----------  ----------
Moto X (16 GB) (Black)  23999       2399      
Moto G (Black  with 16  13999       1399      
Moto E (Black)          6999        699       
Asus Zenfone 4 A400CG   5999        599       
Asus Zenfone 5 A501CG   9999        999       
Asus Zenfone 5 A501CG   12999       1299      
Asus Zenfone 6 A600CG   16999       1699      
Alcatel Onetouch Idol   16999       1699      
Samsung Galaxy S Duos   8206        820       
Samsung Galaxy Star Pr  7599        759       
Samsung Galaxy S Duos   8214        821       
Samsung Galaxy Core I8  11795       1179      
Samsung Galaxy Grand 2  18990       1899      
Samsung Galaxy Trend S  9610        961       
Samsung Galaxy Grand N  14010       1401      
Samsung I8552 - Galaxy  15999       1599      
Samsung Galaxy S4 Mini  24730       2473      
Samsung GT 1200 R/I/M   1299        129

If you observe the result, the values are truncated. Say for example take the 1st row, moto x price is 23,999 and if you divide it with 10, the result should be 2399.9. but here the value is truncated in sqlite editor.

Operator Priority

If you use all arithmetic operator in single column, they behave something different from our basic mathematics. Say for example if you have a scenario as mentioned below price+2*5 (take price=100).

The result will be 110 (In SQLite). But as per the basic mathematics, the expected result is 1010. So in programming language the arithmetic operators have some priority. They are * / + -


Select mobile_name,price,price+2*5 from mobile_sales_details

mobile_name             price       price+2*5 
----------------------  ----------  ----------
Moto X (16 GB) (Black)  23999       24009     
Moto G (Black  with 16  13999       14009     
Moto E (Black)          6999        7009      
Asus Zenfone 4 A400CG   5999        6009      
Asus Zenfone 5 A501CG   9999        10009     
Asus Zenfone 5 A501CG   12999       13009     
Asus Zenfone 6 A600CG   16999       17009     
Alcatel Onetouch Idol   16999       17009     
Samsung Galaxy S Duos   8206        8216      
Samsung Galaxy Star Pr  7599        7609      
Samsung Galaxy S Duos   8214        8224      
Samsung Galaxy Core I8  11795       11805     
Samsung Galaxy Grand 2  18990       19000     
Samsung Galaxy Trend S  9610        9620      
Samsung Galaxy Grand N  14010       14020     
Samsung I8552 - Galaxy  15999       16009     
Samsung Galaxy S4 Mini  24730       24740     
Samsung GT 1200 R/I/M   1299        1309      
Samsung Guru 1200 (Bla  1399        1409      
Samsung Galaxy S5 (Cha  54300       54310     
Samsung Galaxy S4 I950  28071       28081

In the above query 2*5 will be executed first after that it will be added to the price.

If you want to add 2 to price then multiply with 5, you have to use parenthesis control as mentioned in the below query


Select mobile_name,price,(price+2)*5 from mobile_sales_details

 


mobile_name             price       (price+2)*5
----------------------  ----------  -----------
Moto X (16 GB) (Black)  23999       120005     
Moto G (Black  with 16  13999       70005      
Moto E (Black)          6999        35005      
Asus Zenfone 4 A400CG   5999        30005      
Asus Zenfone 5 A501CG   9999        50005      
Asus Zenfone 5 A501CG   12999       65005      
Asus Zenfone 6 A600CG   16999       85005      
Alcatel Onetouch Idol   16999       85005      
Samsung Galaxy S Duos   8206        41040      
Samsung Galaxy Star Pr  7599        38005      
Samsung Galaxy S Duos   8214        41080      
Samsung Galaxy Core I8  11795       58985      
Samsung Galaxy Grand 2  18990       94960      
Samsung Galaxy Trend S  9610        48060      
Samsung Galaxy Grand N  14010       70060      
Samsung I8552 - Galaxy  15999       80005      
Samsung Galaxy S4 Mini  24730       123660     
Samsung GT 1200 R/I/M   1299        6505       
Samsung Guru 1200 (Bla  1399        7005       
Samsung Galaxy S5 (Cha  54300       271510     
Samsung Galaxy S4 I950  28071       140365     
Samsung Guru E1207T (B  1450        7260