🛢️ Unit 2 – Database Query using SQL · Lesson 01
SQL Math Functions: POWER, ROUND, MOD
What is an SQL function?
A function is a ready-made routine that takes values (arguments), does a job and returns a result. MySQL has many built-in functions so we do not have to calculate by hand.
Single-row vs multi-row
Single-row (scalar) functions
- Work on one row at a time
- Return one result per row
- Math, text and date functions
- Can be used in SELECT, WHERE, ORDER BY
Multi-row (aggregate) functions
- Work on a group of rows
- Return one result per group
- MAX, MIN, SUM, AVG, COUNT
- Often used with GROUP BY
POWER()
POWER(x, y) or POW(x, y) returns x raised to the power y (xy). A power of 0.5 gives the square root.
SQL
SELECT POWER(2, 5), POWER(5, 2), POWER(9, 0.5), POW(10, -2);
Output
+-------------+-------------+---------------+-------------+ | POWER(2, 5) | POWER(5, 2) | POWER(9, 0.5) | POW(10, -2) | +-------------+-------------+---------------+-------------+ | 32 | 25 | 3 | 0.01 | +-------------+-------------+---------------+-------------+
ROUND()
ROUND(x, d) rounds x to d decimal places. If d is missing it rounds to a whole number; a negative d rounds to tens (-1), hundreds (-2) and so on. A digit 5 or more rounds up.
SQL
SELECT ROUND(1249.5678), ROUND(1249.5678, 2), ROUND(1249.5678, 1), ROUND(1249.5678, -1), ROUND(1249.5678, -2);
Output
+------------------+---------------------+---------------------+----------------------+----------------------+ | ROUND(1249.5678) | ROUND(1249.5678, 2) | ROUND(1249.5678, 1) | ROUND(1249.5678, -1) | ROUND(1249.5678, -2) | +------------------+---------------------+---------------------+----------------------+----------------------+ | 1250 | 1249.57 | 1249.6 | 1250 | 1200 | +------------------+---------------------+---------------------+----------------------+----------------------+
SQL
SELECT ROUND(-7.5), ROUND(7.45, 1), ROUND(2.5), ROUND(3.5);
Output
+-------------+----------------+------------+------------+ | ROUND(-7.5) | ROUND(7.45, 1) | ROUND(2.5) | ROUND(3.5) | +-------------+----------------+------------+------------+ | -8 | 7.5 | 3 | 4 | +-------------+----------------+------------+------------+
MOD()
MOD(a, b) returns the remainder when a is divided by b – the same as a % b. It is handy for checking even/odd numbers or leftovers.
SQL
SELECT MOD(17, 5), MOD(20, 4), MOD(-17, 5), 17 % 5;
Output
+------------+------------+-------------+--------+ | MOD(17, 5) | MOD(20, 4) | MOD(-17, 5) | 17 % 5 | +------------+------------+-------------+--------+ | 2 | 0 | -2 | 2 | +------------+------------+-------------+--------+
Using math functions on a table
SQL
SELECT * FROM ITEM;
Output
+-------+--------------+---------+------+----------+ | ICode | IName | Price | Qty | Discount | +-------+--------------+---------+------+----------+ | I01 | Notebook | 45.75 | 120 | 5.0 | | I02 | Gel Pen | 12.40 | 350 | 0.0 | | I03 | Geometry Box | 189.99 | 40 | 10.0 | | I04 | School Bag | 1249.50 | 15 | 12.5 | | I05 | Water Bottle | 349.00 | 60 | 7.5 | +-------+--------------+---------+------+----------+
SQL
SELECT IName, Price, ROUND(Price) AS Rounded FROM ITEM;
Output
+--------------+---------+---------+ | IName | Price | Rounded | +--------------+---------+---------+ | Notebook | 45.75 | 46 | | Gel Pen | 12.40 | 12 | | Geometry Box | 189.99 | 190 | | School Bag | 1249.50 | 1250 | | Water Bottle | 349.00 | 349 | +--------------+---------+---------+
SQL
SELECT IName, Price * Qty AS StockValue, ROUND(Price * Qty, -2) AS Approx FROM ITEM;
Output
+--------------+------------+--------+ | IName | StockValue | Approx | +--------------+------------+--------+ | Notebook | 5490.00 | 5500 | | Gel Pen | 4340.00 | 4300 | | Geometry Box | 7599.60 | 7600 | | School Bag | 18742.50 | 18700 | | Water Bottle | 20940.00 | 20900 | +--------------+------------+--------+
SQL
SELECT IName, Price, Discount, ROUND(Price - Price * Discount / 100, 2) AS NetPrice FROM ITEM;
Output
+--------------+---------+----------+----------+ | IName | Price | Discount | NetPrice | +--------------+---------+----------+----------+ | Notebook | 45.75 | 5.0 | 43.46 | | Gel Pen | 12.40 | 0.0 | 12.40 | | Geometry Box | 189.99 | 10.0 | 170.99 | | School Bag | 1249.50 | 12.5 | 1093.31 | | Water Bottle | 349.00 | 7.5 | 322.83 | +--------------+---------+----------+----------+
SQL
SELECT IName, Qty, MOD(Qty, 12) AS LooseItems FROM ITEM WHERE MOD(Qty, 2) = 0;
Output
+--------------+------+------------+ | IName | Qty | LooseItems | +--------------+------+------------+ | Notebook | 120 | 0 | | Gel Pen | 350 | 2 | | Geometry Box | 40 | 4 | | Water Bottle | 60 | 0 | +--------------+------+------------+
SQL
SELECT IName, POWER(Qty, 2) AS QtySquare FROM ITEM WHERE Qty < 50;
Output
+--------------+-----------+ | IName | QtySquare | +--------------+-----------+ | Geometry Box | 1600 | | School Bag | 225 | +--------------+-----------+
Practice
- SELECT ROUND(5678.926, 1), ROUND(5678.926, -3); Answer: 5678.9 and 6000
- SELECT MOD(45, 7), POWER(3, 4); Answer: 3 and 81
- Display IName and price rounded to the nearest ten. SELECT IName, ROUND(Price, -1) FROM ITEM;
Frequently Asked Questions
What does ROUND(x, -1) do?
A negative second argument rounds to the left of the decimal point: -1 rounds to the nearest ten, -2 to the nearest hundred. ROUND(1249.56, -2) gives 1200.
What is the difference between POWER and POW?
None. POW() is just another name for POWER() in MySQL.
What is MOD(-17, 5) in MySQL?
-2. The sign of the result follows the first number (the dividend).
