If we specify the negative value of the second argument then the digits before the decimal point would be deleted and rounded off. The number of digits to be deleted depends upon the value of the negative second argument. Following examples will demonstrate the change, depending upon the negative value of the second argument, in the output of ROUND() function.mysql> Select ROUND(1789.456, -1); +--------------------+ | ROUND(1789.456, -1) | +--------------------+ |               1790 | +--------------------+ 1 row in set (0.00 sec)  The query above returns 1790 because the first digit (which is to be deleted ... Read More

The TRUNCATE() function is used to return the value of X truncated to D number of decimal places. If D is 0, then the decimal point is removed. If D is negative, then D number of values in the integer part of the value is truncated. Consider the following example –mysql> Select TRUNCATE(7.536432, 2); +----------------------+ | TRUNCATE(7.536432, 2) | +----------------------+ |                 7.53 | +----------------------+ 1 row in set (0.00 sec)The ROUND() function returns X rounded to the nearest integer. If a second argument, D, is supplied, then the function returns X rounded ... Read More

For fetching the values of REPLACE() function in our choice column name, we need to use the keyword ‘AS’ with REPLACE() function. Example mysql> Select Name, REPLACE(Name, 'G','S') AS Name_Changed from student Where Subject = 'Computers'; +--------+--------------+ | Name | Name_Changed | +--------+--------------+ | Gaurav | Saurav | | Gaurav | Saurav | +--------+--------------+ 2 rows in set (0.00 sec) The query above will give the result set of REPLACE() function in column name of our choice ‘Name_Changed’ which is given after keyword ‘AS’.

The CEILING() function returns the smallest integer value that is not smaller than X. Consider the following example –mysql> Select CEILING(3.46); +---------------+ | CEILING(3.46) | +---------------+ |             4 | +---------------+ 1 row in set (0.00 sec)   mysql> Select CEILING(-6.43); +----------------+ | CEILING(-6.43) | +----------------+ |             -6 | +----------------+ 1 row in set (0.02 sec)The FLOOR() function returns the largest integer value that is not greater than X. Consider the following example –mysql> Select FLOOR(-6.43); +--------------+ | FLOOR(-6.43) | +--------------+ |           -7 ... Read More

MySQL LENGTH() function measures the string length in ‘bytes’ which means that it is not multibyte safe. The difference of the result between multi-byte safe functions, like CHAR_LENGTH() or CHARACTER_LENGTH(), and LENGTH() function especially relevant for Unicode, in which most of the characters are encoded in two bytes or relevant for UTF-8 where the number of bytes varies. For example, if a string contains four 2-bytes characters then LENGTH() function will return 8, whereas CHAR_LENGTH() or CHARACTER_LENGTH() function will return 4. It is demonstrated in the example below −Examplemysql> Select LENGTH('tutorialspoint'); +--------------------------+ | LENGTH('tutorialspoint') | +--------------------------+ |       ... Read More

When we use RAND() function along with both ORDER BY and LIMIT clause in a query, MySQL returns the different set of rows or values each time. To understand it considers a table ‘Employee’ having the following records −mysql> Select * from Employee; +----+--------+--------+ | ID | Name   | Salary | +----+--------+--------+ | 1  | Gaurav | 50000  | | 2  | Rahul  | 20000  | | 3  | Advik  | 25000  | | 4  | Aarav  | 65000  | | 5  | Ram    | 20000  | | 6  | Mohan  | 30000  | | 7  | Aryan ... Read More

When invoked with an integer argument, RAND( ) uses that value to seed the random number generator. Each time you seed the generator with a given value, RAND( ) will produce the same sequence of random numbers. Following example will demonstrate it −Examplemysql> Select RAND(1), RAND(1), Rand(1); +---------------------+---------------------+---------------------+ | RAND(1)             | RAND(1)             | Rand(1)             | +---------------------+---------------------+---------------------+ | 0.40540353712197724 | 0.40540353712197724 | 0.40540353712197724 | +---------------------+---------------------+---------------------+ 1 row in set (0.00 sec)

When we use MySQL ORDER BY clause with RAND() function then the result set would have the shuffled set of rows. In other words, the result set would be in a random order. To understand it considers a table ‘Employee’ having the following records −mysql> Select * from employee; +----+--------+--------+ | ID | Name   | Salary | +----+--------+--------+ | 1  | Gaurav | 50000  | | 2  | Rahul  | 20000  | | 3  | Advik  | 25000  | | 4  | Aarav  | 65000  | | 5  | Ram    | 20000  | | 6  | Mohan  | ... Read More

We know that MySQL RAND() returns a random floating point value between the range of 0 and 1. It will generate two different random numbers if we will call the RAND() function, without seed, two times in the same query. Following example will make it clearer − Example mysql> Select RAND(), RAND(), Rand(); +--------------------+-------------------+--------------------+ | RAND() | RAND() | Rand() | +--------------------+-------------------+--------------------+ | 0.9402844448949066 | 0.911499003797303 | 0.7366417150354402 | +--------------------+-------------------+--------------------+ 1 row in set (0.00 sec) The above result set shows that RAND() function will generate different random number every time we call it.

With the help of IS NULL operator, we can check for NULL in a MySQL query. We cannot use = (comparison operator) because as we know that NULL is not a value. Following example using the data from ‘employee’ table will exhibit it −Examplemysql> Select * from Employee WHERE Salary IS NULL; +----+-------+--------+ | ID | Name  | Salary | +----+-------+--------+ | 7  | Aryan | NULL   | | 8  | Vinay | NULL   | +----+-------+--------+ 2 rows in set (0.00 sec)The query above use IS NULL operator and produces the output where salary column is having NULL.mysql> ... Read More