MySQL
Functions
Recap
• Functions perform some operations and return a value.
• Single row functions operate on a single value to return a single value.
• Multiple Row functions operate on a set of rows to return a single value.
• String functions operate on character type data. They return either character or
numeric values.
• String Functions are as follows:
UCASE ()/UPPER (), LCASE ()/LOWER (), MID ()/SUBSTRING ()/SUBSTR (),
LENGTH (), LEFT (), RIGHT (), INSTR (), LTRIM (), RTRIM (), TRIM ().
Types of Function
• Single Row Functions: Single row functions operate on a single value to return a single value. They can
accept one or more arguments but return only one result per row. When applied on a table, they return
a single result for every row of the queried table. They are further categorized into:
String/Text functions
Math functions
Date and Time functions
• Multiple Row/Aggregate Functions: Multiple row functions operate on a set of rows to return a
single value. Examples include SUM(), AVG() , MAX(), MIN() and COUNT().
Table: Employee
Math Functions
• MySQL math functions perform operations on numeric values and return
numeric values. The following table tells us about the numeric functions of
MySQL and what they do.
Math Functions are as follows:
POWER (), ROUND (), MOD (),SQRT()
Math Functions…
• POWER(X,Y) / POW(X,Y):
Returns the value X raised to the power Y.
For example:
mysql> SELECT POW(2,4); Result: 16
mysql> SELECT POW(2,-2); Result: 0.25
mysql> SELECT POW(-2,3); Result:-8
mysql> SELECT id, salary, POWER(salary,2) FROM employee;
Result: ->
Math Functions…
• MOD( X, Y):
returns the remainder of a number divided by another number. This function also works on
fractional values and returns the exact remainder. The function returns NULL when the value of
divisor is 0.
For example:
mysql> SELECT MOD(18,4); Result: 2
mysql> SELECT MOD(18.5,4); Result: 2.5
mysql> SELECT MOD(18.5,4.5); Result: 0 .5
mysql> SELECT MOD(18,4.5); Result: 0.0
mysql> SELECT MOD(18.5,0); Result: NULL
Math Functions…
• ROUND(X,D) / ROUND(X):
Rounds the argument X to D decimal places.
If number of decimal places is not specified or is zero, the number rounds to the nearest
integer OR (0 decimal places).
If negative value is specified for precision, it counts off that value left from the decimal
point.
If positive value is specified for precision, it counts off that value right from the decimal
point.
Math Functions…
Round( )….
For example:
mysql> SELECT ROUND(-1.23); Result: -1
mysql> SELECT ROUND(-1.58); Result: -2
mysql> SELECT ROUND(1.43); Result: 1
mysql> SELECT ROUND(6.298, 1); Result: 6.3
mysql> SELECT ROUND(6.235, 0); Result: 6
mysql> SELECT ROUND(56.235, -1); Result: 60
mysql> SELECT id, ROUND(salary,0) FROM employee;
Result: ->
Exercise
Table: Charity
I. Display all first names in lowercase.
II. Display all last names of people of Mumbai city in uppercase .
Exercise
Table: Charity
III. Display Person Id along with First 3 characters of his/her name.
IV. Display first name concatenated with last name for all the employees.
V. Display length of address along with Person Id
VI. Display last 2 characters of City and Person ID.
VII. Display Last Names and First names of people who have "at" in the second or third position in their first names.
VIII. Display the position of 'a' in Last name in every row.
IX. Display Last Name and First name of people who have "a" as the last character in their First names.
X. Display the first name and last name concatenated after removing the leading and trailing blanks. Display Person Id,
last names and contribution rounded to the nearest rupee of all the persons.
XI. Display Person Id, last name and contribution with decimal digits truncated of all the persons.
XII. Display Last name, contribution and a third column which has contribution divided by 10. Round it to two decimal
points.