Databases & SQL · Class 12 · Query Practice
MySQLfunctions⏱️ 12 min read
Math, Text & Date FunctionsOptional for Computer Science
These come up in a shape of their own: write the output of the following. There is no table to read and no query to design — just a function, an argument, and a value you either know or you do not.
Informatics Practices only
The single-row function families are on the Informatics Practices syllabus. Computer Science students will not be asked for them, so treat this page as optional — though
round() is worth knowing whatever you take.1Math functions
A select with no from is perfectly legal — it just evaluates whatever you give it and hands back one row.
MySQL command line client
mysql> select mod(17, 5), power(2, 5), sqrt(144);
+------------+-------------+-----------+
| mod(17, 5) | power(2, 5) | sqrt(144) |
+------------+-------------+-----------+
| 2 | 32 | 12 |
+------------+-------------+-----------+
1 row in set (0.00 sec)
round() and truncate() are the pair examiners like, because they look similar and are not:
MySQL command line client
mysql> select round(1234.567, 2), round(1234.567, -2), truncate(1234.567, 2);
+--------------------+---------------------+-----------------------+
| round(1234.567, 2) | round(1234.567, -2) | truncate(1234.567, 2) |
+--------------------+---------------------+-----------------------+
| 1234.57 | 1200 | 1234.56 |
+--------------------+---------------------+-----------------------+
1 row in set (0.00 sec)
Three things in one line
round(…, 2) rounds to two decimals — 1234.57. A negative second argument rounds to the left of the point: round(…, -2) gives 1200. And truncate() does not round at all, it chops — 1234.56, even though the next digit is a 7.MySQL command line client
mysql> select sign(-15), sign(0), sign(42);
+-----------+---------+----------+
| sign(-15) | sign(0) | sign(42) |
+-----------+---------+----------+
| -1 | 0 | 1 |
+-----------+---------+----------+
1 row in set (0.00 sec)
2Text functions
MySQL command line client
mysql> select ucase('lambdalab'), lcase('LambdaLab'), length('LambdaLab');
+--------------------+--------------------+---------------------+
| ucase('lambdalab') | lcase('LambdaLab') | length('LambdaLab') |
+--------------------+--------------------+---------------------+
| LAMBDALAB | lambdalab | 9 |
+--------------------+--------------------+---------------------+
1 row in set (0.00 sec)
1
Write the output of
3 marksselect left('LambdaLab', 6), right('LambdaLab', 3), mid('LambdaLab', 4, 4);▶Show the answer
MySQL command line client
mysql> select left('LambdaLab', 6), right('LambdaLab', 3), mid('LambdaLab', 4, 4);
+----------------------+-----------------------+------------------------+
| left('LambdaLab', 6) | right('LambdaLab', 3) | mid('LambdaLab', 4, 4) |
+----------------------+-----------------------+------------------------+
| Lambda | Lab | bdaL |
+----------------------+-----------------------+------------------------+
1 row in set (0.00 sec)
mid(str, 4, 4) starts at the fourth character and takes four of them. SQL counts from 1, not from 0 as Python does — position 4 of LambdaLab is b, so the answer is bdaL. Getting this off by one is the commonest way to lose the mark.
MySQL command line client
mysql> select instr('LambdaLab', 'Lab'), concat('Lambda', 'Lab');
+---------------------------+-------------------------+
| instr('LambdaLab', 'Lab') | concat('Lambda', 'Lab') |
+---------------------------+-------------------------+
| 7 | LambdaLab |
+---------------------------+-------------------------+
1 row in set (0.00 sec)
instr() returns 7, not 1. The text does begin with Lam, but the search is for Lab exactly, and the first place that appears is position 7. A function returns 0 when the text is not found at all.
MySQL command line client
mysql> select ltrim(' Lab'), rtrim('Lab '), trim(' Lab ');
+-----------------+-----------------+-------------------+
| ltrim(' Lab') | rtrim('Lab ') | trim(' Lab ') |
+-----------------+-----------------+-------------------+
| Lab | Lab | Lab |
+-----------------+-----------------+-------------------+
1 row in set (0.00 sec)
3The same functions on a column
2
For department
2 marks30, display each name, the name in capitals, and its length.▶Show the answer
MySQL command line client
mysql> select name, ucase(name), length(name) from emp where deptno = 30;
+--------------+--------------+--------------+
| name | ucase(name) | length(name) |
+--------------+--------------+--------------+
| Deepa Nair | DEEPA NAIR | 10 |
| Gopal Das | GOPAL DAS | 9 |
| Jyoti Tamang | JYOTI TAMANG | 12 |
+--------------+--------------+--------------+
3 rows in set (0.00 sec)
The space counts: Deepa Nair is ten characters, not nine.
4Date functions
3
For department
3 marks20, display the name, joining date, and the year, month and day of joining separately.▶Show the answer
MySQL command line client
mysql> select name, doj, year(doj), month(doj), day(doj) from emp where deptno = 20;
+--------------+------------+-----------+------------+----------+
| name | doj | year(doj) | month(doj) | day(doj) |
+--------------+------------+-----------+------------+----------+
| Bina Sharma | 2019-07-01 | 2019 | 7 | 1 |
| Emil Lepcha | 2020-02-17 | 2020 | 2 | 17 |
| Hema Subba | 2014-04-08 | 2014 | 4 | 8 |
| Karan Bhutia | 2012-05-25 | 2012 | 5 | 25 |
+--------------+------------+-----------+------------+----------+
4 rows in set (0.00 sec)
The parts come back as plain numbers, so month() gives 7, not 07 and not July.
MySQL command line client
mysql> select dayname('2024-01-26'), monthname('2024-01-26'), dayofmonth('2024-01-26');
+-----------------------+-------------------------+--------------------------+
| dayname('2024-01-26') | monthname('2024-01-26') | dayofmonth('2024-01-26') |
+-----------------------+-------------------------+--------------------------+
| Friday | January | 26 |
+-----------------------+-------------------------+--------------------------+
1 row in set (0.00 sec)
dayname() and monthname() give words; dayofmonth() gives the number, exactly as day() does. Republic Day 2024 really was a Friday — the server worked that out, it was not looked up.
curdate() and now()
curdate() gives today’s date and now() the date and time. Their output is deliberately not printed here, because it would be wrong tomorrow. In an exam, write the format — YYYY-MM-DD and YYYY-MM-DD HH:MM:SS — rather than a specific value. Quick Check
What is the output of mid('LambdaLab', 4, 4)?
Quick Check
What does round(1234.567, -2) return?
Quick Check
What is the difference between round(1234.567, 2) and truncate(1234.567, 2)?