3 Essential MySQL Interview Questions *
Toptal sourced essential questions that the best MySQL developers and engineers can answer. Driven from our community, we encourage experts to submit questions and offer feedback.
Hire a Top MySQL Developer NowInterview Questions
Consider the following table definition in a MySQL database:
CREATE TABLE example (
id INT NOT NULL,
text1 VARCHAR(32) NOT NULL,
text2 VARCHAR(32) NOT NULL DEFAULT 'foo'
);
What will be the result of the two INSERT
statements below if strict SQL mode is enabled? How will the result differ if strict SQL mode is not enabled? Explain your answer.
INSERT INTO example (id) VALUES(1);
INSERT INTO example (text1) VALUES('test');
Since the text1
and text2
columns are marked as NOT NULL
and have no explicit DEFAULT
clauses, attempts to insert implicit NULL
values into these columns should presumably fail (note that you can use the SHOW CREATE TABLE
statement to see which columns have an explicit DEFAULT
clause). So accordingly, these two INSERT
statements should fail since they don’t specify a value for one or more of these columns.
If strict SQL mode is enabled, an error will indeed occur and the statements will be rolled back. (Note: In the case of a multi-row statement on a non-transactional table, an error occurs, but if this happens for the second or subsequent row of the statement, the preceding rows will have been inserted.)
However, if strict SQL mode is *not enabled,* MySQL sets the column to the implicit default value for the column data type rather than failing.
In MySQL, implicit defaults are determined as follows:
- For numeric types, the default is typically zero.
- For date and time types, the default is the appropriate “zero” value for the type. (This actually a bit more complicated for fields with the type
TIMESTAMP
.) - For string types (other than
ENUM
), the default value is the empty string. ForENUM
, the default is the first enumeration value.
Therefore, after the above two INSERT
statements are executed, the contents of the table will be as follows:
mysql> SELECT * FROM example;
+----+-------+-------+
| id | text1 | text2 |
+----+-------+-------+
| 1 | | foo |
| 0 | test | foo |
+----+-------+-------+
As shown, MySQL has inserted an empty string into column text1
on the first row, and zero into column id
on the second row, even though each column is defined as NOT NULL
with no DEFAULT
clause.
If we create a table and insert a row as follows:
CREATE TABLE game (team1 VARCHAR(32), team2 VARCHAR(32), start TIMESTAMP, end TIMESTAMP);
INSERT INTO game VALUES ("argentina", "brazil", now(), now());
The resulting table will be as follows:
+-----------+--------+---------------------+---------------------+
| team1 | team2 | start | end |
+-----------+--------+---------------------+---------------------+
| argentina | brazil | 2014-07-17 20:44:35 | 2014-07-17 20:44:35 |
+-----------+--------+---------------------+---------------------+
Given the above table definition, what will be the effect of the following SQL:
UPDATE game SET team1 = "uruguay" WHERE team1 = "argentina";
The updated table will then be as follows:
+-----------+--------+---------------------+---------------------+
| team1 | team2 | start | end |
+-----------+--------+---------------------+---------------------+
| uruguay | brazil | 2014-07-17 20:50:10 | 2014-07-17 20:44:35 |
+-----------+--------+---------------------+---------------------+
Note that, in addition to the team1
column being modified, the start
column was automatically updated as well, but the end
field remained unchanged. Why?
The reason the start
column was automatically updated is that its type is TIMESTAMP
. Fields of type TIMESTAMP
have the behavior that when a record in the table is updated, the TIMESTAMP
field (i.e., the start
field in this example) gets updated to reflect the then-current time.
But surprisingly, if we have multiple columns of type TIMESTAMP
, only the first TIMESTAMP
column has this behavior but the others do not. That is why the end
field remains unchanged in this example.
How are Users and Grants different in MySQL than in other databases?
Creating a grant in MySQL can effectively create the user as well. MySQL users are implemented in a very rudimentary fashion. The biggest misunderstanding in this area surrounds the idea of a user. In most databases a username is unique by itself. In MySQL, however, it is the combination of user and hostname that must be unique. So, for example, if I create user john@localhost, john@server2 and john@server3, they are actually three distinct users, which can have distinct passwords and privileges. It can be very confusing that “john” logging in from the local command line has different privileges or password than john logging in from server2 and server3.
Apply to Join Toptal's Development Network
and enjoy reliable, steady, remote Freelance MySQL Developer Jobs
There is more to interviewing than tricky technical questions, so these are intended merely as a guide. Not every “A” candidate worth hiring will be able to answer them all, nor does answering them all guarantee an “A” candidate. At the end of the day, hiring remains an art, a science — and a lot of work.
Why Toptal
Submit an interview question
Submitted questions and answers are subject to review and editing, and may or may not be selected for posting, at the sole discretion of Toptal, LLC.
Looking for MySQL Developers?
Looking for MySQL Developers? Check out Toptal’s MySQL developers.
Nicholas Wakeman
Freelance MySQL Developer
Nick's been developing, architecting, and leading software projects for well over a decade. In addition to a rich full-stack skill set, his business acumen and ability to communicate effectively with clients and stakeholders regardless of their technical knowledge set him apart from the rest. Though focused on tech leadership for the last few years, Nick still enjoys developing early-stage prototypes and products.
Show MoreAbhimanyu Veer Aditya
Freelance MySQL Developer
Abhimanyu is a machine learning expert with 15 years of experience creating predictive solutions for business and scientific applications. He’s a cross-functional technology leader, experienced in building teams and working with C-level executives. Abhimanyu has a proven technical background in computer science and software engineering with expertise in high-performance computing, big data, algorithms, databases, and distributed systems.
Show MoreDaniel Ramteke
Freelance MySQL Developer
Daniel is a full-stack developer with 15+ years of professional experience in iOS, Android, and server-side development. He developed several apps and published them on the App Store. Daniel's an experienced mentor who can explain technical concepts and support non-technical people in making informed decisions. He'd love to help his clients build apps from scratch and reboot older projects, as he excels at organizing projects, hiring new roles, and managing the development process.
Show MoreToptal Connects the Top 3% of Freelance Talent All Over The World.
Join the Toptal community.