Quiz: 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?

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?

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’s indexOf returns 2), “not found” is 0, not -1; SUBSTRING('MySQL', 0, 2) silently returns an empty string;
  • INSTR(str, substr) and LOCATE(substr, str) take their arguments in opposite order, and a swapped order returns 0 without an error;
  • a negative position in SUBSTRING counts from the end: SUBSTRING('MySQL', -3) = SQL;
  • LPAD pads and also truncates: LPAD('JavaScript', 4, '.') = Java;
  • STRCMP compares according to the collation, so in MySQL 8 STRCMP('java', 'JAVA') = 0;
  • CONCAT returns NULL if any argument is NULL.
3. 

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 negative d rounds 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.
  • FLOOR and CEIL (CEILING) round down and up to an integer: 92 and 93 for 92.55665.
  • x DIV y is integer division, MOD(x, y) or x % y the remainder, with the sign of the dividend: MOD(-10, 3) = -1. Together with CEIL they 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?

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, %M and %W — month and weekday names.
  • Minutes are %i, not %M. There is no error: '%Y-%m-%d %H:%M' returns 2025-12-31 23:December. Names are printed in the lc_time_names language (en_US by default); after SET lc_time_names = 'es_ES'; they come out in Spanish.
  • Java patterns are different: '%m/%d/%Y %H:%i' corresponds to DateTimeFormatter.ofPattern("MM/dd/yyyy HH:mm"); in Java MM is the month, mm the minutes.
  • The '%Y-%m' format is a handy month key for grouping and sorts correctly even as a string; '%m/%Y' would put 01/2025 before 12/2024. Never sort by a formatted date — ORDER BY sold_at, with the format only in SELECT.

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?

  • The current moment. NOW() returns a DATETIME, CURDATE() a DATE, CURTIME() a TIME, 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_SUB or the short form date + 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 like LocalDate.plusMonths(1) in Java. The day number is kept, though: February 28 + 1 month = March 28; for “the end of next month” write LAST_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, DATEDIFF gives 10 and TIMESTAMPDIFF(DAY) gives 9. Their argument order is opposite, and a swapped DATEDIFF silently returns a negative number.
  • Age and whole periods. TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) counts whole years; the DATEDIFF(...) / 365 variant goes wrong around birthdays because of leap years. For the same reason TIMESTAMPDIFF(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?

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?

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.

Page 1 of 1