This page needs a newer browser to run its tools. You can still read everything here.

MySQL's SQL: types, modes, text and time interview questions

Interview questions on “MySQL's SQL: types, modes, text and time”: warm-ups, then what each harder question tests and how a good answer starts.

7 questions: 3 warm-ups and 4 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

Fresher

MySQL 8.4 runs in strict mode by default. What does strict mode change, and what does it not cover?

Answered in the lesson Data types, strict mode and SQL modes in MySQL
Fresher to mid

Why keep amounts of money in DECIMAL or in whole paise rather than in DOUBLE?

Answered in the lesson Data types, strict mode and SQL modes in MySQL
Fresher to mid

A sign-up form accepts "Ramesh" and "ramesh" as two different usernames, but the database reports a duplicate key error. What is happening and how do you fix it?

Answered in the lesson Character sets, collations and time zones

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

Mid to senior

A reporting service moved from MariaDB to MySQL 8.4, and that night a third of its queries fail with ERROR 1055. A colleague wants to remove ONLY_FULL_GROUP_BY from the global sql_mode so the reports run by morning. What do you do instead, and how do you find every affected query?
What it tests

Knowing what ONLY_FULL_GROUP_BY protects, containing an incident without changing every application's results, finding the failing statements from the server itself, and choosing the right fix for each query.

First step

Leave the global mode alone: it would hide the error for every application and let the server pick values silently again. If the reports must run by morning, change only the reporting service's own sessions, with a date to undo it. Then list the failing statements from performance_schema's statement digests and fix each one with an aggregate, ANY_VALUE() or a grouped key.
Follow-ups
- How do you find the failing queries without waiting for the next night's run?
- Were the numbers the reports showed on MariaDB right, and how would you check?
- Which fix do you choose for a column that is the same in every row of its group, and for one that is not?
- What happens to stored procedures and views that hold such queries?
From the lesson Data types, strict mode and SQL modes in MySQL
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45
One payment; nothing renews unless you choose to. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I have a licence key
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 5 minutes
Mid to senior

An order service and a billing service both keep amounts in DECIMAL(12,2) and charge 18 % tax. On about one invoice in fifty their totals differ by one paisa, and neither uses FLOAT or DOUBLE. What could cause it, how do you tell which cause it is, and how do you make them agree?
What it tests

Knowing that exact types still round, where rounding can happen (per line or per invoice, when MySQL stores a value or in code) and which rule each layer uses, and settling on one written rule.

First step

Take one invoice that differs and recompute it by hand both ways: tax rounded on every line and then summed, and tax worked out on the invoice total and rounded once. Then find each layer's rounding rule: MySQL rounds half away from zero when it stores into DECIMAL, while a service's language may round half to even unless told otherwise.
Follow-ups
- Which of the two totals is right?
- Where should the rounding rule live so that every service applies the same one?
- Would whole paise in a BIGINT column have prevented the mismatch?
From the lesson Data types, strict mode and SQL modes in MySQL
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 4 minutes
Mid to senior

A sign-up form accepts "amit" and "amit " (with a trailing space) as two different usernames, and both rows are inserted. Later, a login check with WHERE username = 'amit' returns both rows, and the application errors with "more than one user found". What collation behaviour caused this, and how do you prevent it?
What it tests

Separating storage from comparison under PAD SPACE, knowing which way a unique index decides in each pad attribute, and choosing between a trim on write, a generated column and a collation change.

First step

Identify that the table uses a PAD SPACE collation (utf8mb4_general_ci or utf8mb4_bin), which stores the trailing space but ignores it in comparisons, and that the column carries no unique index. Say why both rows were stored and why the login query matches both. Then propose fixes.
Follow-ups
- What would a unique index on this column have done, under PAD SPACE and under NO PAD?
- How would you migrate the existing data to fix the duplicates?
- Should you trim on insert, use a NO PAD collation, or add a generated column?
From the lesson Character sets, collations and time zones
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 4 minutes
Mid to staff

An e-commerce site moved its MySQL server from a data centre in Mumbai to one in London. After the move, appointment times in the TIMESTAMP columns shifted by 5 hours and 30 minutes, and customer complaints came in. What caused this, and how do you prevent it on future moves?
What it tests

Understanding that TIMESTAMP values are stored in UTC but displayed through the session time zone, and that a server's system_time_zone can affect the default session time zone.

First step

Explain that TIMESTAMP columns store UTC internally but convert through the session time_zone when reading. If the old server used time_zone = SYSTEM with Asia/Kolkata, and the new server uses SYSTEM with Europe/London, the same UTC instant displays differently. Then describe the fix: set time_zone explicitly.
Follow-ups
- Would this problem happen with DATETIME columns instead?
- How do you check what time_zone a running server is using?
- Should you store all times in UTC and convert in the application?
From the lesson Character sets, collations and time zones
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 4 minutes

All MySQL & MariaDB in Depth interview questions · The MySQL & MariaDB in Depth track

Warm-up questions

MySQL 8.4 runs in strict mode by default. What does strict mode change, and what does it not cover?

Why keep amounts of money in DECIMAL or in whole paise rather than in DOUBLE?

A sign-up form accepts "Ramesh" and "ramesh" as two different usernames, but the database reports a duplicate key error. What is happening and how do you fix it?

Questions with model answers

A reporting service moved from MariaDB to MySQL 8.4, and that night a third of its queries fail with ERROR 1055. A colleague wants to remove ONLY_FULL_GROUP_BY from the global sql_mode so the reports run by morning. What do you do instead, and how do you find every affected query?

An order service and a billing service both keep amounts in DECIMAL(12,2) and charge 18 % tax. On about one invoice in fifty their totals differ by one paisa, and neither uses FLOAT or DOUBLE. What could cause it, how do you tell which cause it is, and how do you make them agree?

An e-commerce site moved its MySQL server from a data centre in Mumbai to one in London. After the move, appointment times in the TIMESTAMP columns shifted by 5 hours and 30 minutes, and customer complaints came in. What caused this, and how do you prevent it on future moves?