limited time offer

MySQL Functions Interview Questions

Showing 1 - 6 of 6 results

What is the purpose of using the IFNULL() function in MySQL?

MySQL IFNULL() takes two expressions and if the first expression is not NULL, it returns the first expression. Otherwise, it returns the second expression.

Depending on the context in which it is used, it returns either numeric or string value.

Example

mysql> SELECT IFNULL(1,2);

+-------------+
| IFNULL(1,2) |
+-------------+
|           1 |
+-------------+

1 row in set (0.00 sec)
mysql> SELECT IFNULL(NULL,2);

+----------------+
| IFNULL(NULL,2) |
+----------------+
|              2 |
+----------------+

1 row in set (0.00 sec)

What is the difference between UNIX TIMESTAMP and MySQL TIMESTAMP?

Both UNIX TIMESTAMP and MySQL TIMESTAMP are used to represent the date and time value. The main difference between these values is that UNIX TIMESTAMP represents the value by using 32-bits integers and MySQL TIMESTAMP represents the value in the human-readable format.

Example: FROM_UNIXTIME

mysql> SELECT FROM_UNIXTIME (1596222320) AS MySQLTIMESTAMP;

+---------------------+
| MySQLTIMESTAMP      |
+---------------------+
| 2020-08-01 00:35:20 |
+---------------------+
1 row in set (0.00 sec)

UNIX_TIMESTAMP

mysql> SELECT UNIX_TIMESTAMP ('2018-12-25 09:45:40') AS UNIXTIMESTAMP;
+---------------+
| UNIXTIMESTAMP |
+---------------+
|    1545711340 |
+---------------+
1 row in set (0.00 sec)

How can you retrieve a portion of any column value by using a select query in MySQL?

SUBSTR() function is used to retrieve the portion of any column. 

Example: To get first five character of Contact Number use below command:

SELECT SUBSTR(ContactNo,1,5) FROM Shopping.Customer;

+-----------------------+
| SUBSTR(ContactNo,1,5) |
+-----------------------+
| 99990                 |
| 96504                 |
| 11111                 |
| 12345                 |
+-----------------------+
4 rows in set (0.00 sec)

Which MySQL function is used to concatenate string?

CONCAT() function is used to combine two or more string data. The use of this function is here with an example.

mysql> SELECT * FROM Shopping.Customer;

+------------+--------------+------------+------------+--------+------------+-----------+
| CustomerID | CustomerName | ContactNo  | Address    | CityID | PostalCode | CountryID |
+------------+--------------+------------+------------+--------+------------+-----------+
|          1 | Tarun        | 9999075499 | Madan Puri |    124 |     122001 |        91 |
|          2 | Ram          | 9650423377 | A-487      |     11 |     110085 |        91 |
|          3 | Sham         | 1111111111 | A-485      |     11 |     110085 |        91 |
|          4 | Mohan        | 1234567890 | 454        |    124 |     122002 |        91 |
+------------+--------------+------------+------------+--------+------------+-----------+
4 rows in set (0.00 sec)

Combine CustomerName and ContactNo

mysql> SELECT CONCAT(CustomerName,": ",ContactNo) FROM Shopping.Customer;

+-------------------------------------+
| CONCAT(CustomerName,": ",ContactNo) |
+-------------------------------------+
| Tarun: 9999075499                   |
| Ram: 9650423377                     |
| Sham: 1111111111                    |
| Mohan: 1234567890                   |
+-------------------------------------+
4 rows in set (0.00 sec)

 

Which statement is used in a select query for partial matching in MySQL?

REGEXP and LIKE statements can be used in a select query for partial matching. REGEXP is used to search records based on the pattern and LIKE is used to search any record by matching any string at the beginning or end or middle of a particular field value.

Example:

Using REGEXP to get customer whose Contact Number starts 9:

mysql> SELECT * FROM Shopping.Customer WHERE ContactNo REGEXP "^9";

+------------+--------------+------------+------------+--------+------------+-----------+
| CustomerID | CustomerName | ContactNo  | Address    | CityID | PostalCode | CountryID |
+------------+--------------+------------+------------+--------+------------+-----------+
|          1 | Tarun        | 9999075499 | Madan Puri |    124 |     122001 |        91 |
|          2 | Ram          | 9650423377 | A-487      |     11 |     110085 |        91 |
+------------+--------------+------------+------------+--------+------------+-----------+

 Using LIKE to get customer whose Contact Number starts 9:

mysql> SELECT * FROM Shopping.Customer WHERE ContactNo LIKE "9%";

+------------+--------------+------------+------------+--------+------------+-----------+
| CustomerID | CustomerName | ContactNo  | Address    | CityID | PostalCode | CountryID |
+------------+--------------+------------+------------+--------+------------+-----------+
|          1 | Tarun        | 9999075499 | Madan Puri |    124 |     122001 |        91 |
|          2 | Ram          | 9650423377 | A-487      |     11 |     110085 |        91 |
+------------+--------------+------------+------------+--------+------------+-----------+
2 rows in set (0.00 sec)

What is the difference between NOW() and CURRENT_DATE() in MySQL?

Both NOW() and CURRENT_DATE() are built-in MySQL methods. NOW() is used to show the current date and time of the server and CURRENT_DATE() is used to show only the date of the server.

Example

Now()

mysql> SELECT now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-28 21:41:28 |
+---------------------+
1 row in set (0.01 sec)

 CURRENT_DATE()

mysql> SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2019-12-28     |
+----------------+
1 row in set (0.00 sec)
Subscribe to MySQL Functions Interview Questions