Top 50 MySQL Interview Questions and Answers

IT professionals who are creative thinkers, passionate, and skilled have a bunch of opportunities to explore. A Person may get a job into the right field if he has enough knowledge of multi-tenant computer applications and database systems.

MySQL is one of the popular IT skills used by organizations worldwide for their critical business apps. This is the only database system that is specially developed for the online world. In this blog, let’s go through a few of the MySQL Questions which would help you in an interview and get the right opportunities in the database domain. Let us take a quiz first to know how much you are familiar with the MySQL database platform.

MySQL Interview Questions and Answers for Intermediates

Q1). How to compare MySQL and SQL?

• SQL means Structured Query Language that is used to interact with databases like MySQL. It is a database that is used to store various types of data and keeps it safe.
• SQL is a computer language, and MySQL is a software application or program.
• SQL is used for maintaining database management system whereas MySQL is used for storing, data handling, deleting, or modifying data.

Q2). What is MySQL?

MySQL is a multi-threaded or multi-user database management system having more than 11 million installations. It is the world’s second largest most popular and widely used open source database. Using MySQL is free of cost for developers, but enterprises have to buy the licensed version. It is supported by the Oracle and based on Structured Query Language. It supports a wide range of operating systems like Windows, Linux, and Unix, etc.

Q3). In which language MySQL is written?

MySQL is written in C, and C++ language, and its SQL parser are written in YACC language.

Q4). What are the technical specifications of MySQL?

MySQL is a client-server based application that includes:

• It a multithreaded SQL Server supports various client programs and libraries.
• It includes different backends.
• It includes a wide range of programming interfaces and administrative tools.

Q5). Why is MySQL so popular?

MySQL is a database server that is fast, reliable, and easy to use. It can be downloaded from the internet from a single click.

Q6). What do you understand by Heap Tables?

These are available in memory for high-speed storage temporarily. It does not allow BLOB or text fields; only comparison operators are allowed. Heap tables and indexes do not support an auto-increment function and should be NOT NULL.

Q7). Name the default port for the MySQL Server.

It is 3306.

Q8). How is MySQL better than Oracle?

These are the reasons what makes MySQL better than Oracle:

• It is an open source database system suitable for almost all organizations without any major costs involved in it.
• It is portable with a GUI interface and a command prompt.
• MySQL administration is supported by the MySQL query browser.

Q9). How to compare FLOAT and DOUBLE keywords in MySQL?

• To store float numbers, FLOAT keyword is used with four byes and eight bits accuracy.
• To store float number, the DOUBLE keyword is used with eight bytes and 18 bits accuracy.

Q10). How can you represent ENUMs and SETs internally?

They are used to represent the power of two because of storage optimizations.

Q11). How to compare Char-length and length keywords in MySQL?

Char-length is related to character count, whereas length keyword is related to the byte count.

Q12). Why is ENUM used in MySQL?

ENUM is a string object that is used to specify a predefined set of values and can be used during the table creation.

Q13). What is REGEX in MySQL?

It a regular pattern that matches a given pattern in the search value.

Q14). How to compare CHAR and VARCHAR in MySQL?

They are different in storage and retrieval. CHAR column length is fixed to length while VARCHAR length value varies from 1 to 255.

What is the possible number of string types available for a column?

Read: Comparative Study of SQL and NoSQL Databases

• SET
• BLOB
• ENUM
• CHAR
• TEXT
• VARCHAR

Q15). How to get the current MySQL version?

Here is the command to get the current MySQL version:

SELECT VERSION ();

Q16). Name a few drivers in MySQL.

Here is the list of drivers available in the MySQL:

• PHP Driver
• JDBC/ODBC Driver
• C WRAPPER
• PYTHON Driver
• PERL Driver
• RUBY Driver
• CAP11PHP Driver

Q17). What are storage engines and how data is stored in storage engines?

Storage engines are table types, and data is stored in the storage engine using various techniques given below:

• Indexing
• Locking levels
• Storage mechanism
• Functions and capabilities

Q18). How to compare a primary key and a candidate key?

Each row in a table is identified uniquely by the primary key. There is only a primary key within a table. A primary key can be used as the candidate key and vice versa based on requirements.

Q19). How to login to a MySQL account when using a UNIX shell?

Here is the command for logging to a MySQL account when using a UNIX shell:

#[mysql dir]/bin/mysql – hostname –u <UserName> -p <password>

Q20). What is the purpose of myisamchk in MySQL?

It is used to compress the myISAM tables that reduce the memory or disk usage.

Q21). What is the maximum size of a Heap Table?

There is no limit on the maximum size of a heap table, but it should be controlled by MySQL config variable named as:

Max_heap_table_size

Q22). How to differentiate the myISAM static and myISAM dynamic?

For myISAM static, all the fields have fixed length while myISAM dynamic can accommodate variables of different lengths.

Q23). What will happen when a column is set to auto increment and reaches its maximum value?

It will stop incrementing and produces errors for further inserts.

Q24). What will happen when a column within a table is defined as TIMESTAMP?

It gets the current timestamp whenever rows are altered.

Q25). What do you understand by federated tables?

They allow access to tables located on other databases of the server.

MySQL Interview Questions and Answers for Advanced Workforce

Q26). How can you see all the indexes for a table?

To see all the indexes for a table, you can use the following command:

SHOW INDEX FROM <tablename>;

Q27). How to find which auto increment operation was applied to the Last Insert?

LAST  INSERT_ID

This operator returns the last value assigned by the auto-increment operator and there is no need to specify the table name.

Read: SSRS Pie Chart - Having a Slice of the Pie

Q28). How to get the total number of rows affected by a query?

The total number of rows affected by a query can be given as:

SELECT COUNT (user_id) FROM users;

Q29). Is MySQL case-sensitive or not?

No, it is not case-sensitive. All commands given below have the same meaning.

SELECT VERSION(), CURRENT_DATE;
select version(), current_date;
seleCT vErSiOn(), Current_DATE;

Q30). What are the comparison operators that can be used for a column in MySQL?

These are AND, OR LIKE operators to use with SELECT statement.

Q31). How to convert between UNIX and MySQL stamps?

The following command can be used to convert from MySQL timestamp to UNIX timestamp.

UNIX_TIMESTAMP

The following command can be used to convert from UNIX timestamp to MySQL timestamp:

FROM_UNIXTIME

Q32). What is the significance of “%” and “_” operators in the LIKE statement?

“%” corresponds to zero or more characters and “_” operator exactly represents only one character.

Q33). How to compare “LIKE” and “REGEXP” operators in MySQL?

These two operators are used to express with % and ^: 

SELECT * FROM employee WHERE em_name REGEXP "^b";
SELECT * FROM employee WHERE em_name LIKE "%b";

Q34). Write a few significant differences between TEXT and BLOB.

A BLOB is a large binary object that has the capability of holding a voluminous amount of data. There are a total of four types of BLOB in MySQL.

• TINYBLOB
• BLOB
• MEDIUMBLOB
• LONGBLO

BLOB and TEXT are different in terms of maximum length of values they can hold. TEXT is a BLOB that is case-sensitive in nature and divided into four categories. These are:

• TINYTEXT
• TEXT
• MEDIUMTEXT
• LONGTEXT

TEXT is usually case-sensitive, and BLOB are not case-sensitive for TEXT values.

Q35). How to compare mysql_fetch_array and mysql_fetch_object?

Here are the notable differences among mysql_fetch_array and mysql_fetch_object:

• mysql_fetch_array returns a result row as a regular array or associated array from the database.
• mysql_fetch_object returns a result row as an object from the database.

Q36). How to run batch mode in MySQL?

Here is the command to run match mode in MySQL: 

mysql ;
mysql mysql.out

Q37). How to store myISAM in different formats?

Each myISAM table is stored in three formats:

• The'.frm' file stores the table definnition
• the data file has a '.MYD' (MYData) Extension
• The index file has a '.MYI'(MYIndex) extension

Q38). What are the different types of tables available in MySQL?

“MYISAM” is the default storage engine in MySQL. Here is the list of different types of tables available in MySQL:

MYISAM, Heap, Merge, INNO DB, ISAM.

Q39). What do you understand by the term ISAM in MySQL?

ISAM means Indexed Sequential Access Method. It is used to store and retrieve data on secondary devices like tapes.

Read: What is SQL Subquery? Types of Subqueries in SQL

Q40). Do you know about InnoDB in MySQL?

It is a transaction-safe storage engine proposed by the Innobase which is an Oracle Corporation now.

Q41). How to optimize the DISTINCT operator in MySQL?

DISTINCT is first converted to GROUP BY clause then combined with ORDER BY clause.

Q42). How many columns can be used to create indexes?

A total of 16 indexed columns can be used to create indexes.

Q43). What is the command to display the top 50 rows in MySQL?

Here is the command for displaying top 50 rows in MySQL:

SELECT * FROM
LIMIT 0,50;

it is not easy learning these commands theoretically but you should practice them practically. For this purpose, join online training program or check the demo class first to make sure that you have chosen the right institute.

Q44). How to enter characters as HEX numbers?

To enter characters as HEX number, you should enter HEX numbers with single quotes and prefix (x).

Q45). What is an Access Control List in MySQL?

An Access Control List (ACL) is a set of permission associated with an object. It is the basis of the server’s security model and helps in troubleshooting problems like users are not able to connect, not able to access data, etc. MYSQL stores the ACL in the cache memory. Every time a user tries to run or authenticate a command, MySQL checks the permissions or authentication information against the Access Control List (ACL) in a specific order.

Q46). How will you define joins in MySQL?

MySQL uses joins to query data from two or more tables. There is a total of four types of joins in MySQL. These are Inner join, Full join, Right join, and left join. Inner join return rows even if there is one single match in both the tables.

LEFT join returns all rows from the LEFT table even if there are no matches for the RIGHT table. RIGHT join returns all rows from the RIGHT table even if there are no matches for the LEFT table. Full join returns rows where there is at least one match in both the tables.

Q47). What do you understand by DDL, DCL, and DML in MySQL?

DDL means Data Definition Language that deals with database schemas as well as database description of how data can be stored in a database. One of the common examples of DDL is “CREATE TABLE” command. DML means Data Manipulation Language that includes commands like SELECT, INSERT, etc. DCL means Data Control Language that includes commands like GRANT, REVOKE, etc.

Q48). How to reset the password if the root password is lost?

You can choose the following command if the root password is lost and you can start the database in normal mode again. –skip-grants-table

Q49). How to compare transaction and ACID properties?

A transaction is a logical unit of work where all or none of the steps are performed. ACID means Atomicity, Consistency, Isolation, and Durability that can be defined as properties of a transaction.

Q50). What is the basic MySQL architecture?

The logical MySQL architecture composed of query optimizer, connection manager, and pluggable engines, etc.

Final Words:

These top MySQL interview questions and answers are selected after deep research and are suitable for every candidate looking for a job in the database space. They should be beneficial for intermediates, and advanced workforce who want to enhance their career with little or more experience.

When you are at an intermediate level, it is expected that you have gone through all MySQL basic concepts and practiced something worth at the workplace. Usually, every job interview discussion starts with the basics and moves to the advanced level. To give new heights to your career, add a certification to your resume by joining SQL certification program at JanBask Training. We wish you luck for a progressive career in the near future.