SQL Mathematical Operators and String Operators

All of them can be directly used in any SELECT or WHERE clause.

Mathematical Operators

Operator	Meaning	Example	Result
+	addition	SELECT 10+2	12
-	subtraction	SELECT 10-2	8
*	multipication	SELECT 10*2	20
/	division	SELECT 10/2	5
%	modulo (remainder)	SELECT 10%3	1
^	exponentiation (associates left to right)	SELECT 2^3	8
| /	square root	| / 25.0	5
| |/	cube root	|| / 27.0	3
!	factorial	5 !	120

SELECT 1050 * (1 + 0.10) AS "price incl GST"

Output:

Mathematical functions

mod(a, b)
Computes the remainder of a / b.
round(n, d)
Rounds n to d decimal places.
trunc(n, d)
Truncates n to d decimal places.
ceil(n)
Computes the smallest integer value not less than n.
floor(n)
Computes the largest integer value not greater than n.
abs(n)
Computes the absolute value of n.
pi()
“π” constant

SELECT pi();

SELECT mod(15, 4), round(15.67, 1),
       trunc(15.67, 1), ceil(15.67),
       floor(15.67), abs(-121.4)

Output:

String Operators

Item	Meaning	Example	Result
||	String concatenation	SELECT 'Hello, ’ || ‘world!’	Hello, world!
lower(s)	Convert string s to lower case	SELECT lower(‘Hello, world!’)	hello, world!
upper(s)	Convert string s to upper case	SELECT upper(‘Hello, world!’)	HELLO, WORLD!

Example:
write an SQL query to list the actor_id and fullname of every Actor whose last_name begins with ‘A’.

SELECT actor_id, last_name||', '||first_name as fullname
FROM Actor
WHERE last_name LIKE 'A%'

Output:

Reference:
Mathematical Operators