We are living in a data-driven world. It is estimated that everyday businesses generate 2.5 quintillion bytes of digital data for various purposes. From business decisions to operations, everything relies on data. Therefore, storing this data has become crucial for enterprises today.

MySQL database is one such open-source Relational Database Management System for storing, accessing and managing data. It also opens a plethora of job opportunities in the field of database administration and management. If you want to explore a career in the MySQL database, we have compiled a list of frequently asked MySQL interview questions for you.

Basic MySQL Interview Questions and Answers

1. What are Heap tables and what is their max size?

Heap is a table created with a hashed index in the memory storage engine and is used temporarily for high-speed data storage.

The data in the heap table is stored without any specific order. Hence, while retrieving from the heap, it is accessed in order of data pages and not in the order in which it was inserted.

The size of the heap table is controlled by a variable: max_heap_table_size which has a default value of 16MB. You can set the max_heap_table_size based on the maximum size permitted for the system’s internal MEMORY tables.

2. How to add new columns, and change column names in MySQL?

Syntax to add new columns in MySQL: 

ALTER TABLE table_name ADD COLUMN column_name data_type 

Syntax to change column names: 

ALTER TABLE table_name 

RENAME COLUMN old_column_name TO new_column_name;

3. How to join tables in MySQL?

MySQL JOIN command helps in retrieving data from multiple tables into a single table based on a related column in both of them.

There are six types of MySQL JOINs:

MySQL Joins tables commands — Image credit: educba.com

Self Join: Table joins with itself
Inner Join: Retrieves records that match in both table
Left Join: Retrieves matching records from the right table and all records from the left table
Right Join: Returns matching records from the left table and all records from the right table
Cross Join: Provides cartesian product of joining tables, i.e. returns all possible combinations of records.
Full-outer join: Retrieves all records from two tables that have a common column.

An example MySQL query to perform inner join:

The two tables are Customers and Orders, with CustomerID being the common column in both.

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate 
FROM Orders 
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

4. How to drop the primary key in MySQL?

A primary key is a single or a combination field identifier for each record in a table.

Syntax to drop a primary key:

ALTER TABLE table_name   

DROP PRIMARY KEY;

5. How do you create a Stored Procedure in MySQL?

Syntax to create a Stored Procedure:

CREATE_PROCEDURE procedure_name[(parameter datatype[, parameter datatype])] 

BEGIN 

     Body_section of SQL Statements 

END;

To execute the Stored Procedure:

You use the CALL query to retrieve the saved Stored procedure.

CALL stored_procedure_name(argument_list);

Learn more about Stored Procedures here.

6. Differentiate between TRUNCATE and DELETE in MySQL.

TRUNCATE	DELETE
It is a Data Definition Language (DDL) command which deletes all rows without removing table structure from the database	It is a Data Manipulation Language (DML) command which also deletes the columns while keeping the table structure intact
It does not reinitialize the table, so on entering the new rows, the AUTO_INCREMENT number starts after the last inserted row	It re-initializes the table and on entering the new rows, the AUTO_INCREMENT number starts from 1
Cannot use the WHERE clause in TRUNCATE	Can use WHERE clause in a DELETE command
You can’t use it with indexed views	Can use with indexed views
It is a complex command that deletes data permanently	It is an easy command and deletes data from the table which is retrievable later

7. What are ‘triggers’? What are the types of triggers in MySQL?

Triggers are special stored procedures that automatically invoke responses to an event. A trigger only invokes when the data changes in a table, unlike Stored Procedures which you can invoke explicitly.

There are 2 types of triggers in MySQL:

Row-level trigger: Fired when there are INSERT, UPDATE query in MySQL or DELETE statements on any rows.

Statement level trigger: Fired on a table regardless of how many rows are changed.

Interview Questions on MySQL Data Types

8. Differentiate between CHAR and VARCHAR.

CHAR	VARCHAR
Contains non-binary strings and column length is fixed	Contains non-binary strings but column length can vary
Max no of characters – 255	Maximum no of characters –4000
Uses static memory allocation	Uses dynamic memory allocation
Ideal to use when the data sizes for the column is consistent	Ideal to use when the size of data entries vary significantly. When the data length exceeds 8000 bytes, we use VARCHAR(MAX)

9. What is the usage and advantage of ENUMs in MySQL?

ENUMS are string objects that allow limiting the possible values of input data. For example, the following ENUM captures gender data:

ENUM (‘male’, ‘female’, ‘other’)

Hence, any input that is not among the values defined in the ENUM will be rejected.

Advantages of ENUM data type:

Compact data storage for a column with a limited set of possible values. The string values in ENUM are automatically used as a numeric index.
Allows readable queries as it can translate the numbers back to the corresponding string.
Accepts various data types like string, integer, floating-point, and decimal.

10. Differentiate CHAR_LENGTH() and LENGTH()?

Both CHAR_LENGTH() and LENGTH() return the length of a string. But a significant difference is that CHAR_LENGTH() returns the string length as the character count of a variable whereas LENGTH() returns the string length as a byte count.

Example input:

SELECT  
CHAR_LENGTH('HackerTrail'),  
LENGTH('HackerTrail');

Example output: 
+--------------------+---------------+ 

| CHAR_LENGTH('HackerTrail') | LENGTH('HackerTrail') | 

+--------------------+---------------+ 

|                  11 |             11 | 

+--------------------+---------------+

11. How many string types are available in MySQL?

The string types available for columns are:

SET
BLOB
ENUM
CHAR
TEXT
VARCHAR
BINARY
VARBINARY

12. Differences between MySQL Datetime and Timestamp Data Types

DateTime

Timestamp

Stores date-time information

This also stores date time but first converts the date time to UTC during storage and converts back from UTC to the current time zone during retrieval

Range 1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

Range 1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

MySQL Interview Questions on Keys

13. What are concatenated primary keys?

Concatenated primary keys are composite identifiers that are made from the combination of two or more columns in a table.

Eg. Consider the following Product table:

PRODUCT
OrderID	ProdID	Quantity	SellingPrice	Discount	Shipped

Here no single column can be used as a primary key because a single order can have multiple products and the same product can be in multiple orders. Therefore, the possible solution is to create a composite or concatenated key on ‘OrderID’ and ‘ProdID’.

14. How to use “ORDER BY” on a primary key?

The ‘Order by’ clause can be used only if you are selecting by the Primary Key of the table or an Index that sorts the rows of the table as required.

MySQL Query syntax:

SELECT * from Table_name ORDER BY primary_key;

15. List down the differences between the primary key and the candidate key.

The differences between the primary key and candidate key are:

Primary Key	Candidate Key
A unique and non-null identifier for a record in a table	A unique key for a record in a table however a table can have multiple candidate keys
Value cannot be null	Value can be null
It is a crucial part of a table or a relation	It signifies which key can be a primary key
The primary key is always a candidate key	Candidate key might or might not be a primary key

16. Can we create a MySQL foreign key without a primary key?

Yes, you can create a MySQL foreign key without a primary key, however, it must be a column with a unique constraint on it.

MySQL DBA questions

17. What are the different set operations available in MySQL?

UNION - This operation returns all distinct rows from the query.
UNION ALL - This operation returns all the distinct and duplicate rows from the query.
MINUS - This operation uses two select statements. It returns all the rows from the first select statement that were not found in the second select.
INTERSECT - This operation returns all rows selected from both queries.

18. What is database black box and white box testing?

Black Box testing:

A testing approach to test functionality through interfaces based on given specifications or test cases. Black box test does not require to have in-depth knowledge of the technology used to implement the database. It includes data-mapping, verification of input and output data, and various functions like boundary value analysis and equivalence partitioning.

White box testing:

A testing approach to test the implementation part of a database. It is used for module testing of database functions and checking of referential integrity rules. In-depth technical knowledge about the internal structure is a must as well as knowing how it works. It involves testing of database tables, schema, data model, triggers, SQL views, etc.

To further illustrate this:

CAR is the subject under test.
The driver is the black box tester.
The mechanic is the white box tester.
The driver will test the car based on the driving experience while a mechanic would be able to test on more sophisticated details internally which may not be visible or exposed to the driver.

19. How to test for null values in a database?

Use the ISNULL operator in the SQL statement to check if a field has any null values.

SELECT column_names FROM table_name WHERE column_name IS NULL;

20. How to display the highest salary from a SQL table?

Use the SQL function MAX to display the highest salary. Here is the MySQL query for the same:

SELECT MAX(salary) from EMPLOYEES;

21. How can you increase the performance of the MySQL SELECT query?

Use the ‘LIMIT clause to limit the search in a table.

Syntax: 

SELECT {fieldname(s) | *} FROM tableName(s) [WHERE condition] LIMIT  N;

Use LEFT or RIGHT MySQL JOIN to retrieve selective data from the tables but avoid OUTER JOIN.

Syntax for LEFT JOIN:  

SELECT column_name(s) 

FROM table1 

LEFT JOIN table2 

ON table1.column_name = table2.column_name;

Syntax for RIGHT JOIN: 

SELECT column_name(s) 

FROM table1 

RIGHT JOIN table2 

ON table1.column_name = table2.column_name;

Use the WHERE clause to filter data in your SELECT statement.

Syntax:  

SELECT field1, field2,...fieldN table_name1, table_name2… 

[WHERE condition1 [AND [OR]] condition2..... 

Avoid using the ‘*’ in Select statements. 

SELECT * FROM tablename;

Advanced MySQL Interview Questions and Answers

22. What are the various ways to create indexes?

There are two ways to create an index in MySQL:

1. At the time of table creation using CREATE TABLE

SYNTAX:

CREATE TABLE table_name 

(  

  column1 datatype [ NULL | NOT NULL ], 

  column2 datatype [ NULL | NOT NULL ], 

  ... 

  column_n datatype [ NULL | NOT NULL ], 

 

  INDEX index_name [ USING BTREE | HASH ] 

    (index_col1 [(length)] [ASC | DESC],  

     index_col2 [(length)] [ASC | DESC], 

     ... 

     index_col_n [(length)] [ASC | DESC]) 

);

2. Using CREATE INDEX to add indexes to existing tables

SYNTAX: 

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name 

  [ USING BTREE | HASH ] 

  ON table_name 

    (index_col1 [(length)] [ASC | DESC],  

     index_col2 [(length)] [ASC | DESC], 

     ... 

     index_col_n [(length)] [ASC | DESC]);

23. Difference between B-tree and Hash Indexes.

B-Tree	Hash Index
Used for comparisons between columns like =, >, < , >=, <=, or BETWEEN operators	Used only for equality comparisons like =, <=, >=
Searches next entry in the order	Can’t search for the next entry in the order
Any leftmost prefix key can fetch rows.	Only whole keys can fetch rows.

24. How can you filter the duplicate data while retrieving records from the table?

The keyword DISTINCT filters duplicate data while retrieving data from the table.

Here is the syntax:

SELECT DISTINCT field_name from table_name;

The digital world is building a culture that takes action based on data. This also creates a need gap for experts who can store, access, and operate this data. If you wish to gain database expertise, our list of MySQL Interview questions is the right place for you to start. Moreover, as a part of your interview preparations, you can further read the SQL interview questions list.