MySQL interview questions and answers
MySQL is a widely used relational database. According to db-engines.com, it ranks as second in usage, only losing to Oracle Database, and not by far. That’s why we prepare the following list of MySQL interview questions for you to use in your next interview.
So, in this article, you will see from basic to more advanced questions related to MySQL.
Question: Suppose you have a table called “Person” in your database, like the one below. How would you replace all the null values in the “Firstname” collumn, during retrieval time, with the value “Anonymous”?
Answer: The most basic way would be to use the function “ifnull” in your select statement, like the example below:
SELECT ifnull(firstname,’Anonymous’) AS ‘Name’ FROM Person
Another more flexible option is the use of the COALESCE function, which accepts two or more parameters and returns the first non-null value:
SELECT coalesce(firstname,’Anonymous’) AS ‘Name’ FROM Person
How would you restrict the number of rows returned in a resultset?
Answer: You would most likely use the LIMIT keyword in your SELECT statement, see the example:
SELECT firstName FROM person LIMIT 100 ;
To have further information, check Limit Optimization on dev.mysql.com.
What query would you use to get the current date?
Answer: You execute this query:
The parenthesis is optional.
To have further information, check Date and Time Functions on dev.mysql.com.
How would you retrieve the total number of records returned by a particular SQL command?
Answer: There is a specific function for this purpose. It is the COUNT function, which you would use in your SQL statement like the example below:
SELECT COUNT(*) FROM TableName;
To have further information, check Counting rows on dev.mysql.com.
Explain the COUNT function?
Answer: This function returns the number of items found in a particular command.
To have further information, check Counting rows on dev.mysql.com.
What are JOIN statements? Please, name the different types of join expressions possible in MySQL and explain their purposes and differences?
Answer: JOINs are instructions to combine data from different sets of data. The following are the different types of join expressions available in MySQL:
- INNER JOIN: this will generate a set of records with the tables involved only for the tuples that attend the “ON” statement equality. In the below example, it will only bring tuples/records where a person has an address.
SELECT * FROM person p INNER JOIN address a ON p.id = a.person_id
- LEFT JOIN: Left join is similar to INNER JOIN but all the data in the left table will be selected, independent if they have any relation to the table on the right or not. So the result from the following query might bring records of persons with no address.
SELECT * FROM person p LEFT JOIN address a ON p.id = a.person_id
- RIGHT JOIN: Right join is identical to LEFT JOIN, with the difference that all the data in the right column will be selected regardless of whether the data in the right column does or does not match the data in the left column.
SELECT * FROM person p RIGHT JOIN address a ON p.id = a.person_id
- STRAIGHT JOIN: Straight join tells MySQL to always read the left table before the right table. There are few cases where the normal join can put the tables in the wrong order.
- CROSS JOIN: Cross join is the type of join in which for each record of the first table, all records of the second table will be related, i.e. all records of the first tables are queried and is created a Cartesian product of the records from the first table with the records of the second table.
- NATURAL JOIN: Similar to INNER JOIN but it is not required to identify which columns will be compared, as it will make the comparison between fields with the same name.
Could you please explain what are primary and foreign keys used for?
Answer: A primary key is used to identify an entity. It allows the object to be uniquely identified. For this, when a field is marked as PK, the database will not allow duplicate values to be stored.
A foreign key, on the other hand, is used to identify an object that is being used or referenced in another table. This is the principle of a relational database. A foreign key can only be foreign if it is primary in another table. The database manages this relationship, including preventing a primary key from being changed or removed if it is being referenced by a foreign key.
Could you please explain what are Stored Procedures?
Answer: Stored procedures are SQL statements that can be written to the database and called anytime.
They are useful when:
- an SQL statement is used too often;
- for standardizing access to the database;
- the query needs to be accessed by an external application.
Could you please explain what are views?
Answer: Views are virtual tables, or even better, stored queries in the database that will produce a result. In a view we can combine data from one or more tables, enter data or do other DML operations. A view does not store the data itself, it always depends on the query that is stored to generate its results.
CREATE VIEW example as
SELECT * FROM websites WHERE size=’big’
Check dev.mysql.com page about creating views to learn more.
Please explain happens when a column that is set to AUTO_INCREMENT reach its maximum value?
Answer: Basically no new record can be stored in this table. What happens is that the auto increment stops working, or incrementing, which means any further inserts will produce an error, since the key has been used already.
Check dev.mysql.com page about AUTO_INCREMENT.
How to get the MySQL version using a SQL command?
Answer: You can execute the command:
SELECT VERSION() ;
In this case, the parenthesis is required.
Learn more about select function and other information functions on dev.mysql.com.
Can you name some available types for storing text data?
Answer: There are eight data type for string formats:
- CHAR, VARCHAR
These two types are very similar: CHAR columns can set a maximum length limit from 0 up to 255 chars. VARCHAR, on the other hand, has a VARiable length. The maximum size can be set from 0 up to 65,535.
- BINARY, VARBINARY
Similar to CHAR and VARCHAR, with the main difference that they don’t store strings with a sequence of characters, but as a sequence of bytes.
- BLOB, TEXT
BLOB, or Big Large Object can hold a variable amount of data in binary format. TEXT is the blob equivalent for character strings.
Enum stores strings, but they limit the values to a fixed set of options. Example: taste ENUM(‘sweet’, ‘sour’, ‘salty’, ‘bitter’)
The advantage is that MySQL can optimize the storage of the information.
Similar to ENUM, but allows the storage of several values from a list of possible choices.
What is the difference between DATE, DATETIME, TIMESTAMP and TIME?
- DATE: It is used for values with a date part but no time part. MySQL retrieves and displays DATE values in ‘YYYY-MM-DD’ format. The supported range is ‘1000-01-01’ to ‘9999-12-31’.
- DATETIME: It is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format. The supported range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.
- TIMESTAMP: It is used for values that contain both date and time parts. TIMESTAMP has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.
- TIME: It saves values in ‘HH:MM:SS’ format (or ‘HHH:MM:SS’ format for large hours values). TIME values may range from ‘-838:59:59’ to ‘838:59:59’. The hours part may be so large because the TIME type can be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).
What is the difference between CHAR data type and VARCHAR Data Type?
- CHAR type is used to define a fixed length of a column. The length value could be in the range of 0-255.
- VARCHAR length is adjustable up to 65,535.
|Value||Char (4)||Storage Required||VarChar(4)||Storage Required|
|”||‘ ‘||4 bytes||”||1 bytes|
|‘ab’||‘ab ‘||4 bytes||‘ab’||3 bytes|
|‘abcd’||‘abccd’||4 bytes||‘abcd’||5 bytes|
|‘abcdefg’||‘abccd’||4 bytes||‘abcd’||5 bytes|
What is an Index?
Answer: Index make it easier to find content, so MySQL can find content easier than searching it page by page. It always depends on whether the indexes are well defined.
- They are able to reduce the amount of data the server would have to examine.
- They are able to help the server to sort (eg ORDER BY) and avoid creation of temporary tables.
When should you create a new index?
Answer: You should configure indexes for the fields that you use in WHERE. You should first find the fields that are taking time and resources from the server and then create the index.
About the MySQL Query Cache, what are the rules for a query to be cached or not?
Answer: The quick answer is that the queries must be identical for the cache to store properly and then give a hit for already cached queries. Although this is a simple answer, it is unfortunately not completely true, because even in this case the cache might give a “miss”:
- If the query contains specific functions;
- If It contains user-defined functions (UDFs) or stored functions.
- If It contains user variables or local stored program variables.
- If It contains tables in the MySQL, INFORMATION_SCHEMA, or performance_schema database.
- If It contains any partitioned tables.
To have a complete list of cases where the cache will not work, check the query cache operation, from dev.mysql.com.
What are triggers and what types of triggers are in Mysql?
Answer: Triggers are SQL commands that can be specified to be run automatically when some event is issued. There are 6 opportunities for you to trigger your code automatically:
- BEFORE INSERT
- AFTER INSERT
- BEFORE UPDATE
- AFTER UPDATE
- BEFORE DELETE
- AFTER DELETE
Learn more about the Trigger syntax on dev.mysql.com
How to create a new user with a SQL command:
Answer: CREATE USER ‘newuser’@’localhost’ IDENTIFIED BY ‘password’;
Describe the SQL command you would use to set permissions to specific existing user?
Answer: A GRANT statement would be the way to go.
GRANT ALL PRIVILEGES ON database.table TO ‘newuser’@’localhost’;
See more about the GRANT syntax on dev.mysql.com.
What is the default port for MySQL Server?
What is MariaDB and how does it relate to MySQL?
About the question: I would ask this question to assess how much is the candidate aware of the developments related to MySQL.
Answer: MariaDB is a fork of the MySQL project made by the original developers once Oracle acquired MySQL. They are very much compatible with each other.
Check what is MariaDB to know more on mariadb.com.
What is the difference between InnoDB and MyISAM? When should you prefer one over the other?
Answer: Both InnoDB and MyISAM are storage engines for MySQL.
The main difference lies in the fact that MyISAM does not provide support for transactions nor foreign keys. The question is why would one choose to use MyISAM?
If your needs are much more about reading than write, that it is a good option because it has a better performance. Especially updates and deletes are problematic in terms of performance and fragmentation of the rows. Use OPTIMIZE TABLE command to defragment the rows.
Since version 5.5, the default engine in MySQL is InnoDB. In most normal use cases it has a better performance than MyISAM and provides the more important features, special support for ACID-compliant transactions and foreign keys.
That’s why, if you want to use MyISAM engine in any specific table, it is necessary to set the ENGINE, as shown in the below example:
CREATE TABLE mylog (i INT) ENGINE = MYISAM;