Top 50 MySQL Interview Questions You Must Prepare In 2023

MySQL Interview Questions:

In the era of 2.5 Quintillion bytes of data being generated every day, data plays a crucial role in decision making for business operations. This quite quintessentially makes us handle data in databases and gives us the need to use database management systems.  With various kinds of Database Management System(DBMS) present in the market today, the Relational Database Management System(RDBMS) is one of the most popular systems available. This type of DBMS, uses a structure that allows the users to identify and access data in relation to another piece of data in the database, and MySQL is one such popular RDBMS. Well, knowing MYSQL opens the doors for you to become a Database Administrator. I believe that you are already aware of these facts and this has made you land on this MySQL Interview Questions article.

In this article on MySQL Interview Questions, I will be discussing the top questions related to MySQL asked in your interviews. These questions are collected after consulting with people having excellent skills in this field. For your better understanding, I have divided the article into the following sections:

1. MySQL DBA Interview Questions
2. PHP MySQL Interview Questions 
3. Complex MySQL Interview Questions
4. Tricky MySQL Interview Questions 

So, let’s get started guys.

MySQL DBA Interview Questions

Q1. Can you tell the difference between Mysql_connect And Mysql_pconnect?

Q2. What is the default port for MySQL server?

MySQL Server’s default port is 3306. Apart from this, another standard default port for the SQL Server in TCP/IP is 1433.

Q3. Can you tell what are the different set operations available in MySQL?

The various set operations available in MySQL are as follows:

• UNION – This operation returns all the distinct rows selected by a query
• UNION ALL – This operation returns all the rows selected by a query and also includes all duplicate rows.
• MINUS – This operation returns all the distinct rows selected by the first query but does not select the rows selected by the second query.
• INTERSECT – This operation returns all the distinct rows selected by both queries.

Refer to the below image:

Q4. Can you tell the order of SQL SELECT statement?

The order of SQL SELECT statement is as follows:

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.

Q5. What is Database White Box Testing?

The Database Whitebox Testing deals with the tables, data model, schema and referential integrity rules. It also deals with the triggers, logical views with database consistency and ACID properties.

Q6. What is Database Black Box Testing?

Database Black Box Testing deals with data mapping, data storing and retrieving. The Database Black Box Testing is used for techniques such as Equivalence Partitioning and Boundary Value Analysis. 

Q7. What is CTE?

An expression which consists of a temporary set of results defined in a SQL statement is said to be a Common Table Expression(CTE).

Q8. What are the different tables present in Mysql?

There are mainly five types of tables present in MySQL. Out of all these database engines, the default database engine used in MySQL is MyISAM. Refer below to know the five types of tables:

• MyISAM
• Heap
• Merge
• INNO DB
• ISAM

Q9. What is a Cursor?

Considered as a pointer to point to one row in a set of rows, a Cursor is nothing but a control which enables traversal over the records in the table. So, the cursor is used for performing traversing actions such as addition, retrieval, and removal of records in a database.

Q10. How can you test for NULL values in a database?

A NULL value is a field with no value present in that particular field. Since the NULL value cannot be compared to any other NULL values, you cannot use the comparison operators such as =, <, or <>. To compare the fields with NULL values, you have to use the IS NULL and IS NOT NULL operator.

Refer below for Syntax of IS NULL and IS NOT NULL.

SELECT column_names FROM table_name WHERE column_name IS NULL;
SELECT column_names FROM table_name WHERE column_name IS NOT NULL;

Q11. Can you elaborate on BLOB and TEXT in MySQL?

BLOB

BLOB(Binary Large Object) is used to hold a variable amount of data and holds up to 65,535 bytes of data. The following are the four types of BLOB.

• TINYBLOB
• BLOB
• MEDIUMBLOB
• LONGBLOB

TEXT

TEXT is used to store string values and holds up to a maximum length of 65,535 characters. The following are the four types of TEXT

• TINYTEXT
• TEXT
• MEDIUMTEXT
• LONGTEXT

Q12. Can you tell how can you display the Maximum salary in SQL?

To display the maximum salary in SQL, you can use the inbuilt function called MAX().

Q13. What is the difference between the NVL function, IFNULL function, and the ISNULL function?

The NVL function, IFNULL function, and the ISNULL function all of them are used to replace the NULL value with another value. The ORACLE users use the NVL function, MySQL users use the IFNULL function and the SQL servers use the ISNULL function

For example, let us say we have a column(column_3) which has NULL values.

So, if you run the below statement, the output you would get is a NULL value.

SELECT column_1 * (column_2 + column_3) FROM Example_Table

Now, to overcome this, you can use the above three functions as follows:

SELECT column_1 * (column_2 + NVL(column_3,0)) FROM Example_Table
SELECT column_1 * (column_2 + IFNULL(column_3,0)) FROM Example_Table
SELECT column_1 * (column_2 + ISNULL(column_3,0)) FROM Example_Table

Q14. What is the difference between GUI Testing and Database Testing?

Q15. How To Display Nth Highest Salary From A Table In A Mysql Query?

Consider the table named “Employee”.

Now, to find the Nth salary consider the below statement.

SELECT DISTINCT(salary) FROM employee ORDER BY salary DESC LIMIT n-1,1

So, if you want to find out the 7th largest salary, consider the below query.

SELECT DISTINCT(salary) FROM employee ORDER BY salary DESC LIMIT 6,1 

Now, let’s move on to the next set of questions, which is the PHP MySQL Interview Questions.

PHP MySQL Interview Questions

Q1. What is the command used to create a database using PHP and MySQL?

The command used to create a database using both PHP and MySQL is mysql_create_db(“Database Name”).

Q2. Can you tell the  Difference Between Mysql_fetch_object And Mysql_fetch_array?

Both of them are similar but vary with a single difference. Mysql_fetch_object return as object and Mysql_fetch_array returns an array. This means that you cannot access the data by their offsets but can only access through its fields names.

Q3: What are the ways in which you can retrieve data in the result set of MySQL using PHP?

The different ways in which you can retrieve data in the result set of MySQL using PHP are as follows:

• mysql_fetch_object: This constant fetches a result row as an object.
• mysql_fetch_array: This constant fetches a result row as an associative array, numeric array or as both.
• mysql_fetch_row: This constant gives us a result row as an enumerated array.
• mysql_fetch_assoc: This constant gives us a result row as an associative array.

Q4.  Can you tell how many values can Set the function of MySQL to consider?

MySQL’s Set function can take a maximum of 64 values, but can also consider 0 values.

Q5. Can you tell the reasons for selecting Lamp(Linux, Apache, MySQL, PHP) instead of any other combination of software programs, servers, and operating system?

The reason behind selecting Lamp stack is very simple. Linux, Apache, MySQL, PHP are open source software. The security of the Linux operating system is much more than Windows.  The Apache server is a better server than others in the perspective of functionalities and security. MySQL is one of the most popular open source databases is used with PHP to perform various functionalities.

Q6. Can you tell a way to know the number of days between the two given dates in PHP?

You can simply declare the two dates, and then use the strtotime function to subtract both the dates and find the differences between the days in seconds.

Consider the below example.

date1 =’2018-09-15′;
date2 = ‘2018-10-15’;
days = (strtotime($date1) – strtotime($date2)) / (60 * 60 * 24);

Q7. Can you tell how to find the number of rows in a resultset using PHP?

You can use the mysql_num_rows function to find the number of rows in a resultset.

Consider the below example.

output = mysql_query(sql, database_name);
number_of_rows = mysql_num_rows(output);
echo "The number of forws found are equal to: $number_of_rows";

Q8. What are the functions used to encrypt and decrypt the data present in MySQL?

The function used to encrypt the data is AES_ENCRYPT() and the function used to decrypt the data is AES_DECRYPT().

Q9. If you wish to encrypt the username and password using PHP, how will you do that? 

You can encrypt the username and password using the following functions respectively:

SET USERNAME=USERNAME("Username");
SET PASSWORD=PASSWORD(”Password”);

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

The SELECT statement is used to select data from a database and the data returned is stored in a result table, called the result-set. The SELECT statement can be either individually used or can be used with other statements such as ORDER BY, GROUP BY, and HAVING clause.

To increase the performance of a MySQL SELECT query, you can use the LIMIT clause to limit MySQL from further search in a table, after collecting the required number of records. Apart from this, we can also use the LEFT JOIN or the RIGHT JOIN to retrieve data from two or more tables.

Q11. Can you tell the difference between $message and $$message?

$message and $$message are both PHP variables. $message is used to store the variable data and $$message is used to store the variable of a variable. So basically, data is stored in $message and $$message is used to store the data that can be changed dynamically.

Q12.  Write a program using the SELECT statement, While Loop.

You can write a program to select the students details from the student table and use the loop to just print the name of students.

example_query = mysql_query("SELECT * FROM 'students' WHERE  'student_id' = '1';");
while(output = mysql_fetch_array(example_query))
{
echo output['Students_Name'];
}

Q13. How can you take the backup and restore a MySQL database using PHP? 

MySQL comes with a utility mysqldump to provide the database backup and restore. The command you can use for backup and restore are as follows respectively.

//To take the backup of database
mysqldump database > backup -file.sql;
//To restore the database
mysqldump database < backup -file.sql;

You can also use the phpMyAdmin user interface to backup your database. If you wish to backup, the database you just have to click on the “export” link on the phpMyAdmin main page.

Q14. Can you tell the difference between ereg_replace() and eregi_replace()?

ereg_replace and eregi_repalce() are regular expressions used to replace the matching characters. The only difference between these functions are  eregi_replace() function ignores the case distinction when it matches alphabetic characters. 

Q15. How to copy data from one server to another using PHP?

You can use the following three options:

Option 1: You can use the PHP Copy to move files from server to server. Refer to the syntax below:

/* Copy the file from source url to server */
$copy = copy( $remote_file_url, $local_file );

Option 2:  You can use the PHP FTP to move files from server to server. Refer to the syntax below.

/* Download $remote_file and save to $local_file */
ftp_get($connect_it,$local_file,$remote_file,FTP_BINARY)

Option 3: You can use the ZIP and UNZIP Files option in PHP.

Now, let’s move on to the next set of questions, which is the Complex MySQL Interview Questions.

Complex MySQL Interview Questions

Q1. Can you tell few best practices to be followed for optimization in SQL?

The best practices to be followed for SQL optimizations depend on the individual to individual, but the following list consists of the most popular practices that are advised to follow. Refer below.

• Try avoiding prefixing your stored procedure names with “sp_”.
• It is recommended to use the column list in INSERT statements.
• Preferably use the ANSI-Standard JOIN Clauses rather than the Old style clauses.
• While using SELECT statement, avoid using * queries.
•  Do not use double quotes in T-SQL code.
• Try avoiding to use column numbers in the ORDER BY clause.
• Try using table aliases if your SQL statement involves more than a single source.

Q2. Can you tell what are various ways to create an index?

The various options to create an index are as follows:

• You can create an index using the T-SQL statements.
• You can use the SQL Server Management Studio. In this, you can browse to the table you need to create an index and then right click on the Indexes node. Over here you have to choose the New Index option.
• You can indirectly identify the index by defining the PRIMARY KEY and the UNIQUE constraint within the CREATE TABLE or ALTER TABLE statement.

Q3. What is the difference between a Heap table and Temporary table?

Q4. Why do you think it is advised to not to use GUID and CHARACTER columns as Clustered Index arrays?

GUID columns affect the clustered index sorting performance as the nature of the random GUID value generated is larger than the integer data types.

CHARACTER columns affect the sorting performance of the character data types, larger-size values, non-increasing values, and non-static values which often tend to change. These values cannot be compared as binary values, as the characters comparison mechanism depends on the used collection.

Q5. How can you handle the –secure-file-priv in MySQL?

–secure-file-priv option limits the MySQL Server from loading the directories using the LOAD DATA INFILE.

If you wish to see the directory that has been configured then you may use the SHOW VARIABLES LIKE “secure_file_priv”; 

You have mainly two options to tackle:

• Either move your file to the directory specified by the secure-file-priv.
• Or you can disable secure-file-priv.  You cannot disable this later on, and you have to remove it from the start itself.

Q6. What is the difference between B-Tree and Hash Indexes?

Q7. Where is the MyISAM table stored?

Each and every MyISAM Table is stored on disk in the following three files:

• .frm file – Stores the table definition.
• .MYD file – A data file has an MYData extension.
• .MYI  index file – The index file has an MYIndex extension.

Q8.State the differences between MongoDB and MySQL.

Q9. Identify what is wrong in the below query.

SELECT EmployeeID, AVG(Salary)

FROM EmployeeDetails

WHERE AVG(Salary) > 75

GROUP BY EmployeeID

The answer is quite simple. You cannot use the WHERE clause to restrict the groups. Instead of this, you have to use the HAVING clause.

Your query should be as follows:

SELECT EmployeeID, AVG(Salary)
FROM EmployeeDetails
HAVING AVG(Salary) > 75
GROUP BY EmployeeID

Q10. What is Normalization and list the different types of normalization?

Normalization is the process of organizing data to avoid duplication and redundancy. There are many successive levels of normalization. These are called normal forms. Each consecutive normal form depends on the previous one. The first three normal forms are usually adequate.

• First Normal Form (1NF) – No repeating groups within rows
• Second Normal Form (2NF) – Every non-key (supporting) column value is dependent on the whole primary key.
• Third Normal Form (3NF) – Dependent solely on the primary key and no other non-key (supporting) column value.

Now, let us move on to the next set of questions which is the Tricky MySQL Interview Questions.

Tricky MySQL Interview Questions 

Q1. Consider you have a composite index of three columns. Now, you have to provide the value of two columns in the WHERE clause of a SELECT query. Do you think Index can be used for the operation?

Usage of index completely depends on if you consider the primary index or not.  Consider you have a student table. Now, suppose if an Index is present on StudentID, StudentFirstName, and StudentLastName then you can consider a query as follows:

SELECT * FROM StudentDetails WHERE StudentID=3 and StudentFirstName='Jatin'

Now, if you consider that the above two columns in the query are the secondary index columns, then the index will not be invoked. Else, if the above two columns contain the first column while creating an index(i.e. the primary index), then the index will definitely be invoked.

In the above scenario, I have considered that StudentID and the StudentFirstName as primary columns, so an Index will be used in this case.

Q2. Suppose you have to collect the first name, middle name and the last name of students from the below table. But, you observe that there few missing values either in the first name, middle name and the last name columns. How will you return the first non-null values?

You can use the COALESCE function to return the first non-null value from the table. Consider the below query.

SELECT StudentID, COALESCE(FirstName, MiddleName, LastName) as Name FROM StudentDetails;

Q3. Consider a scenario where you have two to three tables with thousand tuples in each of them. Now, if you have to perform a JOIN operation between them will you choose to perform filtering of rows or transforming of rows first. 

The answer to this question is quite logical. If you have three tables with thousands of tuples in each of them, then you are first supposed to filter the rows in those tables and then transform the table. This would be beneficiary as if you transform the table, then the number of columns may increase reducing the performance. Due to such performance issues, a lot of memory will be used and the output will appear on your screen after quite a long wait of time.

Q4. How can you validate emails using a single query?

To validate emails you can use the regular expressions function (REGEXP_LIKE). Consider the below query.

SELECT
Email
FROM
Employee
where NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}’, ‘i’);

Q5. Consider a scenario where you have to send an email to a client from the SQL database. How do you think you can achieve this task?

To send an email from the database, you can use the stored procedures. Follow the below procedure to send the emails:

• Configure your SQL Server Mail account.
• Enable the database mail.
• Write a script to send an email. Refer below for the script.

USE [YourDB]
EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]; [email protected];[email protected]’
@body = ' Sample Body Text',
@subject = 'Example Email' ;
GO

Q6. Consider you have the following three tables which have to be linked together. 

Department(Ssn, EmployeeName, EmployeeAge..)

EmployeeContactDetails(Ssn, DepartmentID,desc,Ord)

EmployeeAddress(Ssn,DepartmentID, desc, Ord)

The problem statement is to select all the departments from the Department table, with the “desc” field from the EmployeeContactDetails and EmployeeAddress where Ord=1.  Now, you have to solve this problem statement with a single query.

To solve this problem statement you can use the JOINS concept. You simply have to perform a JOIN on the Department.Ssn and the  DepartmentID in the other tables.

Now, if you are sure that the Ssn exists in all the three considered tables, then you can use the INNER JOIN. Also, if you are not sure that you have matching rows, then you can use the LEFT JOIN. Consider the below query.

SELECT d.Ssn,
d.EmployeeName,
c.desc ContactDetailsDesc,
a.desc AddressDetailsDesc
from Department d
inner join EmployeeContactDetails c
on d.id = c.DepartmentID
inner join address a
on d.id = a.DepartmentID
where d.EmployeeName = 'abc'
and c.ord = 1
and a.ord = 1

Q7. If you are assigned a task, to find the information of PROCEDURES. What are the basic commands that you will use to do so?

To check the procedures, you can consider the following query.

SELECT * FROM SampleSource
WHERE Type=’PROCEDURE’
AND NAME IN (‘SP_CONNECTED_AGG’,’SP_UNCONNECTED_AGG’);

To find the procedures columns information, you can consider the following query.

SELECT OWNER, OBJECT_NAME, ARGUMENT_NAME, DATA_TYPE, IN_OUT from ALL_ARGUMENTS order by OWNER, OBJECT_NAME, SEQUENCE;

Q8.  Can you tell which of the following WHERE clauses is faster?

WHERE col * 4 < 16

WHERE col < 16 / 4

If we compare both the statements, then the second WHERE clause would be comparatively faster than the first one. That is because, for the first statement, MYSQL would retrieve the value of ‘col’ for each and every row, multiplied by four. After that, it would compare the result to 16. Also, in the first case no Index can be used, and hence it makes it further slow.

Q9.  What is the main difference between ‘BETWEEN’ and ‘IN’ condition operators?

BETWEEN operator is used to display rows based on a range of values in a row whereas the IN condition operator is used to check for values contained in a specific set of values.

Example of BETWEEN:

SELECT * FROM Students where ROLL_NO BETWEEN 10 AND 50;

SELECT * FROM students where ROLL_NO IN (8,15,25);