MySQL Functions

WHAT IS A QUERY?
Selective retrieval of data from one or more tables generally based on a condition is called
query. The SELECT command is used to perform query.
WHAT IS MEANT BY NULL VALUE?
Sometimes, you do not know the value for a column. In a table, you can store these
unknown values as NULL. NULL means a value that is unavailable, unassigned, unknown
or inapplicable. NULL is not the same as zero or a space or any other character. In a table,
NULL is searched using IS NULL keywords. For example
SELECT * FROM EMPL WHERE SAL IS NULL;
MySQl Functions
A function performs a single well-defined task. A function accepts zero or more values
(called parameters or arguments), performs some operations on these values and returns
a result. The arguments are enclosed within parenthesis (simple brackets).
MySQL provides two types of functions:
1. Single row functions
Single row functions are further divided into the following categories:
a. Numeric functions
b. String functions
c. Date time functions
2. Multiple row functions

Single row functions Multiple row functions

1. They work with a single row at a They work with multiple rows or groups of
time rows at a time.
2. They work on a single value and They work on multiple values and return a
return a single value for each row single value for each group of rows on
of the table on which the function which the function is applied.
is applied.
3. They can be used in the Select They can be used in the select clause and
clause, where clause and order by having clause.
clause.
By Nimmi Sam 1
 4. They are also called scalar They are also called group functions or
functions. aggregate functions.
5. For example round(), ucase(), For example sum(), max(), min(), avg() and
month(), dayname() etc. count().
6. They can accept one or more They can accept only one argument.
arguments.

Single Row functions

1) Numeric functions – They perform operations on numeric values.
a) SQRT(N) – returns the square root of a number N.
SELECT SQRT(2);
OUTPUT:
1.414
b) POWER(X,Y) or POW(X,Y) – returns the value of X raised to the power of Y i.e.
(XY).
SELECT POWER(8,2);
OUTPUT:
64
c) MOD(X,Y) – divides X by Y and returns the remainder.
SELECT MOD(24,2);
OUTPUT:
0
SELECT MOD(25,2);
OUTPUT:
1
d) ROUND(N,D) – rounds a number to a specified number of decimal places.
Syntax
ROUND(n,d)
n – number to be rounded
d – number of decimal places to which the number has to be rounded
 Here, the number of decimal places (d) is optional.

By Nimmi Sam 2
 If (d) is not specified, the number is rounded off to zero decimal places.
EXAMPLE 1
mysql> select round(56.348,2);
+-----------------------+
| round(56.348,2) |
+-----------------------+
| 56.35 |
+-----------------------+
The number is rounded off to two decimal places. The next digit after two
decimal places i.e. ‘8’ will be considered for rounding the number. If this digit
is ≥ 5 then the previous digit will increase by 1.
EXAMPLE 2
mysql> select round(56.343,2);
+-----------------------+
| round(56.343,2) |
+-----------------------+
| 56.34 |
+-----------------------+
The number is rounded off to two decimal places. The next digit after two
decimal places i.e. ‘3’ will be considered for rounding the number. Since this
digit is not ≥ 5, the previous digit ‘4’ will not change.
EXAMPLE 3
mysql> select round(16.74);
+-------------------+
| round(16.74) |
+-------------------+
| 17 |
+-------------------+
The number is rounded off to zero decimal places. If the number is positive, it
is rounded up to the next integer.
EXAMPLE 4

By Nimmi Sam 3
mysql> select round(-16.74);

+-------------------+
| round(-16.74) |
+-------------------+
| -17 |
+-------------------+
If the number is negative, it is rounded down to the nearest integer.
EXAMPLE 5
mysql> select round(5278.345,-2);
+---------------------------+
| round(5278.345,-2) |
+---------------------------+
| 5300 |
+---------------------------+
In the above example, the second argument passed to the ROUND() function
is -2. So the second digit to the left of decimal point which is 7 will be
considered for rounding off.
If this digit is ≥ 5 then the digit preceding 7 will increase by 1. Hence the digit
2 will become 3 and two digits to the left of decimal point will become zero.
EXAMPLE 6
mysql> select round(5248.345,-2);
+---------------------------+
| round(5248.345,-2) |
+---------------------------+
| 5200 |
+---------------------------+
In this example also, the second argument passed to ROUND() function is -2.
So the second digit to the left of decimal point which is 4 will be considered
for rounding off.
Since this digit is not ≥ 5, the digit preceding 4 will not change. However, two
digits to the left of decimal point will become zero.

By Nimmi Sam 4
NOTE: If the number of decimal places (d) is negative then ‘d’ digits to the left
of the decimal point become zero and the ‘d’th digit is considered for rounding
the number. (Refer to examples 5 and 6)
EXAMPLE 7
These functions can be used with the columns (fields/attributes) of a table
besides constants.
mysql> select name, perMarks, round(perMarks,1) from student;
+-----------+--------------+-----------------------------+
| name | perMarks | round(permarks,1) |
+-----------+--------------+-----------------------------+
| Mukul | 95.25 | 95.3 |
| Kritika | 45.17 | 45.2 |
| Naveen | 96.64 | 96.6 |
| Uday | 82.39 | 82.4 |
+-----------+--------------+-----------------------------+
In the above example, the percentage of marks stored in perMarks field is
rounded off to 1 decimal place.
EXAMPLE 8
mysql> select name, perMarks, round(perMarks,0) from student;
+-----------+--------------+-----------------------------+
| name | perMarks | round(permarks,0) |
+-----------+--------------+-----------------------------+
| Mukul | 95.25 | 95 |
| Kritika | 45.17 | 45 |
| Naveen | 96.64 | 97 |
| Uday | 82.39 | 82 |
+-----------+--------------+-----------------------------+
In the above example, the percentage of marks is rounded off to zero decimal
places.

By Nimmi Sam 5
 ASSIGNMENTS ANSWERS
1) Select ROUND(128.683,1); 128.7
2) Select ROUND(125.638,1); 125.6
3) Select ROUND(48.392,-1); 50
4) Select ROUND(83.18,-1); 80
5) Select ROUND(28.36); 28
6) Select ROUND(58.63,-2); 100
7) Select ROUND(326,-2); 300
8) Select ROUND(4235.18,-3); 4000
Explanation of Q6.
Select ROUND(58.63,-2);
-2 means that the second digit to the left of decimal point (that is 5) will
be considered for rounding the number. Since this number is ≥ 5, the
previous digit will increase by 1. There is no previous digit to 5 in this
example. So, the previous digit will be taken as zero which will then be
increased by 1. So 0+1=1.
The two digits, to the left of decimal point will become zero as the
second argument is -2.
So the final answer is 100.

By Nimmi Sam 6