Quiz: SQL Functions in MySQL: String, Numeric and Date Functions
Study the lesson first: SQL Functions in MySQL: String, Numeric and Date Functions
Total: 7 questions
1. What are SQL functions, and how do single-row functions differ from aggregate functions?
What are SQL functions, and how do single-row functions differ from aggregate functions?
SQL functions are built-in database operations that take arguments (columns, constants, expressions) and return a value. Single-row (scalar) functions are evaluated for each row separately and return one value per row; they are used in SELECT, WHERE, ORDER BY, as well as in INSERT and UPDATE. Aggregate functions (COUNT, SUM) collapse many rows into one value and work together with GROUP BY.
The main groups of MySQL single-row functions:
- string —
CONCAT,SUBSTRING,CHAR_LENGTH,REPLACE,TRIM; - numeric —
ROUND,TRUNCATE,CEIL,FLOOR,MOD; - date and time —
NOW,DATE_FORMAT,DATE_ADD,DATEDIFF,TIMESTAMPDIFF.
The conditional expressions CASE/IF and the NULL functions (COALESCE, IFNULL, NULLIF) are single-row too. Almost every function follows one rule: if an argument is NULL, the result is NULL too — ROUND(price * 0.93, 2) returns NULL for the grapes without a price, and CONCAT with any NULL argument loses the whole string. Functions in SELECT change only the query result, not the table data; to store a transformed value, put the function into UPDATE ... SET product = TRIM(product). You can also try a function without a table: SELECT UPPER('Java');.
2. Which string functions does MySQL have, and what is the difference between CHAR_LENGTH and LENGTH?
Which string functions does MySQL have, and what is the difference between CHAR_LENGTH and LENGTH?
MySQL string functions change, cut, join and search text: CONCAT joins strings, SUBSTRING, LEFT and RIGHT extract part of a string, UPPER and LOWER change the case, TRIM removes spaces, REPLACE swaps a substring, LPAD/RPAD pad to a given length, INSTR and LOCATE find a position, and STRCMP compares strings.
CHAR_LENGTH counts characters, LENGTH counts bytes. For plain ASCII text they match (grapes: 6 and 6), but in utf8mb4 an accented letter such as é takes 2 bytes and an emoji takes 4, so “café beans” has 10 characters and 11 bytes. Validate input length with CHAR_LENGTH: VARCHAR(50) limits characters.
Things to remember:
- positions start at 1:
INSTR('MySQL', 'SQL')= 3 (Java’sindexOfreturns 2), “not found” is 0, not -1;SUBSTRING('MySQL', 0, 2)silently returns an empty string; INSTR(str, substr)andLOCATE(substr, str)take their arguments in opposite order, and a swapped order returns 0 without an error;- a negative position in
SUBSTRINGcounts from the end:SUBSTRING('MySQL', -3)=SQL; LPADpads and also truncates:LPAD('JavaScript', 4, '.')=Java;STRCMPcompares according to the collation, so in MySQL 8STRCMP('java', 'JAVA')= 0;CONCATreturnsNULLif any argument isNULL.
3. How does ROUND differ from TRUNCATE, FLOOR and CEIL in MySQL, and how do DIV and MOD work?
How does ROUND differ from TRUNCATE, FLOOR and CEIL in MySQL, and how do DIV and MOD work?
ROUND(x, d)rounds to the nearest value:ROUND(92.55665, 2)= 92.56; a negativedrounds to tens and hundreds:ROUND(1234.5, -2)= 1200.TRUNCATE(x, d)drops the extra decimal places without rounding:TRUNCATE(92.55665, 2)= 92.55. On a discounted price the difference is money:1.50 * 0.93= 1.3950,ROUND(..., 2)= 1.40,TRUNCATE(..., 2)= 1.39.FLOORandCEIL(CEILING) round down and up to an integer: 92 and 93 for 92.55665.x DIV yis integer division,MOD(x, y)orx % ythe remainder, with the sign of the dividend:MOD(-10, 3)= -1. Together withCEILthey solve a packing problem: for 34 pounds and 15-pound crates,CEIL(34 / 15)= 3 crates,34 DIV 15= 2 full ones,MOD(34, 15)= 4 pounds left over.
Differences from Java: the / operator in MySQL is always fractional (7 / 2 = 3.5000, while in Java between two ints it is 3). For exact-value numbers (DECIMAL, the literal 2.5) MySQL rounds half away from zero: ROUND(-2.5) = -3, whereas Math.round(-2.5) returns -2. For approximate values (DOUBLE, 2.5e0) the result depends on the C library and usually rounds to even: ROUND(2.5e0) = 2. If an argument is NULL, any of these functions returns NULL.
4. How do you format a date in MySQL with DATE_FORMAT and parse a string into a date with STR_TO_DATE?
How do you format a date in MySQL with DATE_FORMAT and parse a string into a date with STR_TO_DATE?
DATE_FORMAT(date, format) turns a date into a string, STR_TO_DATE(string, format) turns a string into a date. The format is built from specifiers that start with %; all other characters are printed as is: DATE_FORMAT(sold_at, '%m/%d/%Y %h:%i %p') → 12/31/2025 11:59 PM.
- Main specifiers:
%Y— 4-digit year,%m— month 01–12,%d— day,%H— hour 00–23,%i— minutes,%s— seconds,%Mand%W— month and weekday names. - Minutes are
%i, not%M. There is no error:'%Y-%m-%d %H:%M'returns2025-12-31 23:December. Names are printed in thelc_time_nameslanguage (en_USby default); afterSET lc_time_names = 'es_ES';they come out in Spanish. - Java patterns are different:
'%m/%d/%Y %H:%i'corresponds toDateTimeFormatter.ofPattern("MM/dd/yyyy HH:mm"); in JavaMMis the month,mmthe minutes. - The
'%Y-%m'format is a handy month key for grouping and sorts correctly even as a string;'%m/%Y'would put01/2025before12/2024. Never sort by a formatted date —ORDER BY sold_at, with the format only inSELECT.
If a string does not match the format, STR_TO_DATE in a SELECT returns NULL with the warning Incorrect datetime value: STR_TO_DATE('31/12/2025', '%m/%d/%Y') = NULL, because there is no month 31. In an INSERT under strict mode the same string raises an error, so bad dates are caught before an import with WHERE STR_TO_DATE(...) IS NULL.
5. How do you add an interval to a date and calculate the difference between dates in MySQL: DATE_ADD, DATEDIFF and TIMESTAMPDIFF?
How do you add an interval to a date and calculate the difference between dates in MySQL: DATE_ADD, DATEDIFF and TIMESTAMPDIFF?
- The current moment.
NOW()returns aDATETIME,CURDATE()aDATE,CURTIME()aTIME, all in the session time zone.NOW()captures the time the statement started,SYSDATE()the time of the actual call. - Adding a period.
DATE_ADD(date, INTERVAL 7 DAY),DATE_SUBor the short formdate + INTERVAL 1 MONTH. A day that does not exist is clamped to the end of the month: January 31 + 1 month = February 28 (in the leap year 2024 — the 29th), just likeLocalDate.plusMonths(1)in Java. The day number is kept, though: February 28 + 1 month = March 28; for “the end of next month” writeLAST_DAY(date + INTERVAL 1 MONTH). - Date difference.
DATEDIFF(end, start)counts calendar days and ignores the time.TIMESTAMPDIFF(unit, start, end)takes the time into account and drops incomplete units: from 23:59 on December 31 to January 10,DATEDIFFgives 10 andTIMESTAMPDIFF(DAY)gives 9. Their argument order is opposite, and a swappedDATEDIFFsilently returns a negative number. - Age and whole periods.
TIMESTAMPDIFF(YEAR, birth_date, CURDATE())counts whole years; theDATEDIFF(...) / 365variant goes wrong around birthdays because of leap years. For the same reasonTIMESTAMPDIFF(MONTH, '2025-01-31', '2025-02-28')= 0.
For “how many days overdue” you usually want DATEDIFF; for “have 24 hours passed” — TIMESTAMPDIFF(HOUR).
6. Why does a function on a column in WHERE prevent index use, and how do you rewrite WHERE YEAR(sold_at) = 2025?
Why does a function on a column in WHERE prevent index use, and how do you rewrite WHERE YEAR(sold_at) = 2025?
An index stores the sold_at values sorted, but it holds no YEAR(sold_at) values. So for WHERE YEAR(sold_at) = 2025 MySQL cannot use a regular index on sold_at and evaluates the function for every row: in EXPLAIN, possible_keys is NULL and type is ALL. The result is still correct — only speed suffers.
Rewrite the condition so the column stays “bare” and the computation moves to the constant side:
YEAR(sold_at) = 2025→sold_at >= '2025-01-01' AND sold_at < '2026-01-01'(the upper bound is excluded, so the sale on December 31 at 23:59 is not lost);DATE(sold_at) = '2025-01-31'→sold_at >= '2025-01-31' AND sold_at < '2025-02-01';DATEDIFF(NOW(), sold_at) <= 30→sold_at >= CURDATE() - INTERVAL 30 DAY;LEFT(product, 3) = 'app'→product LIKE 'app%';lbs * 2 > 40→lbs > 20.
If you often need to filter by an expression, MySQL 8.0.13 and later can build a functional index: CREATE INDEX idx_sales_year ON sales ((YEAR(sold_at))); (the double parentheses are required). It only helps queries with exactly the same expression. Functions in the SELECT list have no effect on index use.
7. How do you pass a date range into an SQL query from Java via JDBC and read a date back as a LocalDate?
How do you pass a date range into an SQL query from Java via JDBC and read a date back as a LocalDate?
Compute the period bounds in Java and pass them as parameters, keeping a range condition on the bare column in SQL — the index works and you never glue a date into the query string:
String sql = "SELECT id, DATE(sold_at) AS sold_day FROM sales WHERE sold_at >= ? AND sold_at < ?";
LocalDate from = LocalDate.of(2025, 1, 1);
LocalDate to = from.plusMonths(1); // 2025-02-01, upper bound excluded
ps.setObject(1, from.atStartOfDay()); // LocalDateTime for DATETIME
ps.setObject(2, to.atStartOfDay());
LocalDate day = rs.getObject("sold_day", LocalDate.class);
String text = day.format(DateTimeFormatter.ofPattern("MMM d, yyyy", Locale.US)); // Jan 15, 2025, Jan 31, 2025
Since JDBC 4.2 (Connector/J 8), setObject and getObject(..., LocalDate.class) work with java.time types directly, without java.sql.Date; a full DATETIME column is read with getObject("sold_at", LocalDateTime.class). Format dates for the user in Java with DateTimeFormatter rather than with DATE_FORMAT: you get the right locale, and the ResultSet carries dates, not strings.
If the time comes from NOW(), remember that MySQL returns it in the session time zone (time_zone), while LocalDateTime.now() uses the JVM time zone: with the server in UTC and the application in America/New_York the clocks differ by 4 or 5 hours. Fixes: UTC on both the server and the JVM, the Connector/J property connectionTimeZone, or passing the time from Java as a parameter.