Playing as a guest? to earn XP and track your progress.
📚 MySQL Functions Quiz
📚 MySQL Functions
What is the return value of SELECT ROUND(15.193, 1)?
15.1
15.2
15.19
15
ROUND(15.193, 1) rounds to 1 decimal place. Since the second decimal (9) is >= 5, the first decimal (1) increments to 2.
Which function returns the position of the first occurrence of a substring?
SUBSTR()
FIND()
INSTR()
LOCATE()
INSTR(string, substring) returns the index of the first occurrence. Returns 0 if not found.
What is the output of SELECT SUBSTR('Computer', 4, 3)?
put
mpt
mpu
com
Starts at position 4 ('p') and extracts 3 characters ('p', 'u', 't').
Which function returns the remainder of a division?
DIV()
REM()
MOD()
POW()
MOD(N, M) or N % M returns the remainder.
Identify the output of SELECT TRUNCATE(123.456, 1).
123.4
123.5
123.45
123.0
TRUNCATE removes digits after the specified position without rounding.
What does SELECT DAYOFWEEK('2023-12-03') return if the date is a Sunday?
0
1
7
Sun
By default in MySQL, 1 = Sunday, 2 = Monday, ..., 7 = Saturday.
Output of SELECT CONCAT('Hi', NULL, 'There')?
HiThere
Hi NULL There
NULL
Error
If any argument in CONCAT is NULL, the result is NULL.
Which function removes both leading and trailing spaces?
LTRIM()
RTRIM()
TRIM()
STRIP()
TRIM() removes whitespace from both ends of the string.
Result of SELECT ROUND(256.78, -2)?
200
300
250
256
Rounding with -2 operates on the hundreds place. 256 is closer to 300.
Which function works similarly to SUBSTR?
MID()
TEXT()
SLICE()
PART()
MID(), SUBSTR(), and SUBSTRING() are synonyms in MySQL.
What will SELECT SIGN(-15) return?
15
-15
-1
0
SIGN returns -1 for negative numbers, 1 for positive, and 0 for zero.
Output of SELECT POWER(2, 3)?
6
8
9
5
Calculates 2 raised to the power of 3 (222 = 8).
What does SELECT NOW() return?
Date only
Time only
Date and Time
Timestamp integer
NOW() returns the current date and time.
Output of SELECT LENGTH('Data')?
4
5
3
8
Returns the length of the string in bytes (4 characters = 4 bytes for standard ASCII).
What is the output of SELECT LCASE('MySql')?
MYSQL
mysql
Mysql
Error
LCASE (or LOWER) converts all characters to lowercase.
Output of SELECT LEFT('Informatics', 2)?
In
cs
fo
Info
Extracts the first 2 characters from the left.
What does SELECT YEAR('2023-05-10') return?
2023-05
05
10
2023
Extracts the year part from the date.
Result of SELECT ROUND(1.5)?
1
2
1.5
NULL
When no decimal count is provided, ROUND rounds to the nearest integer. 1.5 rounds up to 2.
Output of SELECT MOD(10, 3)?
3.33
3
1
0
10 divided by 3 is 3 with a remainder of 1.
What does SELECT SQRT(25) return?
5
25
625
12.5
Returns the square root of 25.
Output of SELECT RIGHT('Science', 3)?
Sci
nce
ence
Ice
Extracts the last 3 characters from the right ('n', 'c', 'e').
Which function is used to return the current date only?
NOW()
CURDATE()
SYSDATE()
TIME()
CURDATE() returns the date portion (YYYY-MM-DD).
Output of SELECT INSTR('Hello', 'z')?
0
1
NULL
Error
Returns 0 because 'z' is not found in 'Hello'.
What is SELECT DAYNAME('2023-01-01')'s return type?
Number
String
Date
Boolean
DAYNAME returns the name of the day (e.g., 'Sunday') which is a string.
Output of SELECT TRUNCATE(15.79, 0)?
15
16
15.7
15.0
Truncates to 0 decimal places (removes decimals), so 15.79 becomes 15.
Which function converts ASCII value to Character?
ASCII()
CHAR()
ORD()
VAL()
CHAR() converts integer ASCII codes into their corresponding characters.
Result of SELECT ROUND(125.55, -1)?
120
130
125.5
125.6
Rounds to the nearest ten. 125.55 rounds up to 130.
What does SELECT MONTHNAME('2023-03-15') return?
3
March
Mar
03
Returns the full name of the month.
Output of SELECT SUBSTR('ABCDEFG', -3)?
ABC
EFG
CDE
G
Negative index counts from the right. Starts at 3rd from last ('E') and takes the rest.
Result of SELECT LTRIM(' Text')?
'Text'
'Text '
' Text'
'Te xt'
Removes leading spaces from the left.
Which represents 'Square Root' in SQL?
SQR()
SQRT()
ROOT()
POWER(x, 0.5)
SQRT is the dedicated function, though POWER(x, 0.5) also works mathematically, SQRT is the standard function name requested.
Output of SELECT LENGTH(TRIM(' A '))?
5
3
1
0
TRIM removes 2 leading and 2 trailing spaces, leaving 'A' (length 1).
What does SELECT DAYOFMONTH('2023-02-14') return?
2
14
Feb
2023
Returns the day number of the month (14).
Output of SELECT CHAR(65)?
65
A
a
Char
ASCII 65 corresponds to 'A'.
Result of SELECT SIGN(0)?
1
-1
0
NULL
SIGN of zero is 0.
Output of SELECT TRUNCATE(1234.56, -2)?
1200
1234
1300
1230
Truncates at the hundreds place (-2). Digits 3 and 4 become 0. No rounding.
Output of SELECT CONCAT('Key', 'Board')?
Key Board
KeyBoard
Key+Board
Error
Joins strings without any separator.
Function to get the day of the year (1-366)?
DAY()
YEARLYDAY()
DAYOFYEAR()
JULIAN()
DAYOFYEAR returns the running day count of the year.
Output of SELECT ROUND(3.14159, 3)?
3.14
3.141
3.142
3.15
Rounds to 3 decimals. Next digit is 5, so 3.141 becomes 3.142.
Which function converts 'abc' to 'ABC'?
LOWER()
UPPER()
CAPS()
TITLE()
UPPER() (or UCASE) converts to uppercase.
Output of SELECT MID('CloudComputing', 6, 4)?
Comp
mput
Cloud
Com
Starts at 6th char ('C' of Computing) and takes 4 chars: 'Comp'.
Result of SELECT MOD(20, 6)?
2
3
3.33
6
20 divided by 6 is 3 with a remainder of 2.
Output of SELECT SUBSTR('Hello World', 7)?
Hello
World
Wor
World!
If length is omitted, it takes everything from the start position (7) to the end.
Output of SELECT LENGTH(12345)?
5
Number
Error
12345
MySQL implicitly converts numbers to strings for LENGTH. '12345' has 5 chars.
Result of SELECT ROUND(-1.23)?
-1
-2
-1.2
-1.3
-1.23 is closer to -1 than -2.
Which statement is true about SYSDATE() vs NOW()?
They are always identical
NOW returns time of query execution; SYSDATE returns live time
SYSDATE returns Date only
NOW returns Date only
NOW() returns the constant time at which the statement began execution; SYSDATE() returns the exact time the function executes (important in long queries).
Output of SELECT INSTR('banana', 'a')?
1
2
3
0
Finds the first occurrence of 'a', which is at index 2.
Output of SELECT POWER(4, 0.5)?
2
16
8
4.5
Power of 0.5 is equivalent to Square Root. Sqrt(4) = 2.
What is the index of the first character in a MySQL string?
0
1
-1
Depends on OS
SQL strings are 1-indexed.
Result of SELECT RIGHT('2023', 2)?
20
23
3
02
Takes the last 2 characters '23'.