SQL string and date function interview questions, with answers

The questions, with answers

1. 1.How do you find the length of a string, and why does MySQL have two functions for it?

   CHAR_LENGTH(str) counts characters; LENGTH(str) in MySQL counts bytes. For plain ASCII they agree, but in a UTF-8 column a Devanagari or emoji character occupies several bytes, so LENGTH('नमस्ते') is 18 while CHAR_LENGTH is 6. Use CHAR_LENGTH when you mean characters. PostgreSQL's LENGTH and SQL Server's LEN both count characters, and SQL Server's DATALENGTH counts bytes; SQL Server's LEN also ignores trailing spaces, which is its own trap. The interview point is not the function name but the fact that "length" is ambiguous once you leave ASCII — say which one you mean.

2. 2.How do you concatenate strings, and what happens with NULL?

   Standard SQL uses the || operator, supported by PostgreSQL, Oracle and SQLite; SQL Server uses +; MySQL uses the CONCAT function (its || is a logical OR unless PIPES_AS_CONCAT is set, a classic trap). NULL handling is where dialects part company: || and + yield NULL if any operand is NULL, and so does MySQL's CONCAT, while CONCAT_WS(', ', a, b) skips NULLs and PostgreSQL's CONCAT function ignores them. So a full-name column built as first_name || ' ' || middle_name || ' ' || last_name goes NULL for everyone without a middle name; use CONCAT_WS or COALESCE(middle_name, '') to survive it. Say the portable answer — COALESCE inside the concatenation — and name the dialect shortcuts.

   -- MySQL
   SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name FROM Employees;
   
   -- PostgreSQL / standard
   SELECT first_name || ' ' || COALESCE(middle_name || ' ', '') || last_name AS full_name FROM Employees;

3. 3.How do you extract part of a string, such as the domain of an email address?

   SUBSTRING(str, start, length) — positions start at 1 in SQL, not 0 — takes a slice by position, and LEFT and RIGHT take the ends. When the slice depends on a delimiter, find the delimiter's position first: in MySQL, SUBSTRING(email, LOCATE('@', email) + 1) returns everything after the @, and SUBSTRING_INDEX(email, '@', -1) does the same in one call (a positive count takes the part before the nth delimiter, a negative count the part after). PostgreSQL uses POSITION('@' IN email) or SPLIT_PART(email, '@', 2); SQL Server uses CHARINDEX and SUBSTRING. The follow-up is what happens when the delimiter is absent — LOCATE returns 0, so the slice starts at 1 and returns the whole string — which is worth guarding with a CASE.

   -- MySQL: the part before the @ (-1 instead of 1 gives the part after it)
   SELECT email, SUBSTRING_INDEX(email, '@', 1) AS local_part FROM Users;
   
   -- PostgreSQL
   SELECT email, SPLIT_PART(email, '@', 1) AS local_part FROM Users;

4. 4.What do UPPER, LOWER, TRIM and REPLACE do, and where do they show up in real queries?

   UPPER and LOWER change case; TRIM removes leading and trailing spaces (LTRIM and RTRIM one side each, and TRIM can strip other characters — TRIM('-' FROM code)); REPLACE(str, from, to) substitutes every occurrence. They show up in data cleaning — matching emails regardless of case, stripping the spaces a form submitted, normalising phone numbers by replacing dashes — and in one interview trap: comparing a cleaned column costs the index. WHERE LOWER(email) = 'a@b.com' has to compute LOWER for every row, because the index stores the original values. Store the normalised form (lower-case emails at insert time), use a case-insensitive collation, or create an index on the expression itself, which PostgreSQL, Oracle and MySQL 8 support.

5. 5.How do you find rows from a particular year, and what is wrong with the obvious way?

   The obvious way is WHERE YEAR(joining_date) = 2022 — MySQL syntax; EXTRACT(YEAR FROM joining_date) in PostgreSQL and standard SQL, DATEPART(year, joining_date) in SQL Server. It is correct and it is what most interviews accept. What is wrong with it is performance: the function is applied to every row, so an index on joining_date is useless. The better form is a range, WHERE joining_date >= '2022-01-01' AND joining_date < '2023-01-01', which the index can seek into — and note the half-open interval with < on the upper bound, which handles DATETIME columns with a time part correctly where BETWEEN ... '2022-12-31' would miss the last day's later rows. Give the function first, then the range as the improvement.

   -- fine for correctness, ignores the index
   SELECT name FROM Employees WHERE YEAR(joining_date) = 2022;
   
   -- index-friendly, and safe for DATETIME
   SELECT name FROM Employees
   WHERE joining_date >= '2022-01-01' AND joining_date < '2023-01-01';

6. 6.How do you do date arithmetic, such as orders from the last 30 days or a person's age?

   Add or subtract an interval. MySQL: order_date >= CURDATE() - INTERVAL 30 DAY, or DATE_SUB(CURDATE(), INTERVAL 30 DAY); PostgreSQL: order_date >= CURRENT_DATE - INTERVAL '30 days'; SQL Server: order_date >= DATEADD(day, -30, CAST(GETDATE() AS date)). The difference between two dates is DATEDIFF(later, earlier) in MySQL (days; SQL Server's DATEDIFF takes a unit first), and simply later - earlier in PostgreSQL. Age is the classic follow-up because dividing days by 365 drifts: MySQL's TIMESTAMPDIFF(YEAR, dob, CURDATE()) and PostgreSQL's AGE(dob) count whole years correctly, leap years included. Two edge cases to mention: whether "last 30 days" includes today, and that CURDATE() is a date while NOW() carries a time.

   -- MySQL: orders in the last 7 days, today included
   SELECT * FROM Orders WHERE order_date >= CURDATE() - INTERVAL 7 DAY;
   
   -- MySQL: age in whole years
   SELECT name, TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS age FROM Employees;

7. 7.What is the difference between DATE, DATETIME and TIMESTAMP, and how do time zones get involved?

   DATE holds a calendar date only; DATETIME holds a date and a time of day with no notion of zone — it stores exactly the digits you gave it; TIMESTAMP in MySQL is converted from the session's time zone to UTC on storage and back on retrieval, so the same stored instant displays differently to sessions in different zones, and it only covers 1970 to 2038. PostgreSQL's equivalents are timestamp (no zone) and timestamptz (an absolute instant, displayed in the session zone), and timestamptz is the one to use for events. The interview answer: store instants as UTC timestamps, store dates as DATE, never store a date in a VARCHAR — a string '10/11/2023' cannot be compared, sorted or indexed as a date, and half of the date-function questions exist to fix data that was stored that way.

8. 8.How do you format a date for display, and why should you do it last?

   DATE_FORMAT(order_date, '%d %b %Y') in MySQL, TO_CHAR(order_date, 'DD Mon YYYY') in PostgreSQL and Oracle, FORMAT(order_date, 'dd MMM yyyy') or CONVERT with a style code in SQL Server. Formatting turns a date into a string, and the string has none of a date's properties — it sorts alphabetically ('01 Feb' before '01 Jan'), it cannot take part in date arithmetic, and comparing it defeats the index — so it belongs in the SELECT list of the final query, or in the application, never in WHERE or ORDER BY. The mirror image is parsing: STR_TO_DATE in MySQL, TO_DATE in PostgreSQL and Oracle, turn input strings into real dates once, at the boundary. Interviewers ask this to see whether you keep dates as dates until the last moment.

How the diagnostic asks it

One question from the SQL bank, exactly as a sitting would show it. The bank has 5 on string & date functions and 60 across SQL.