MySQL Interview Questions and Answers

Embark on a journey into the world of relational databases with 'MySQL Interview Questions and Answers.' This blog is your indispensable resource for preparing for MySQL-related interviews, featuring a diverse collection of questions and detailed answers. Whether you're a database administrator, a software developer, or a MySQL enthusiast, our guide covers MySQL fundamentals, SQL queries, performance optimization, and best practices. Prepare with confidence and explore the realm of efficient data management with MySQL.

1. What is MySQL?

MySQL is an open-source relational database management system (RDBMS) that uses SQL (Structured Query Language) for managing and manipulating data in databases.

2. Explain the difference between INNER JOIN and LEFT JOIN in MySQL.

INNER JOIN retrieves rows where there is a match in both tables, while LEFT JOIN retrieves all rows from the left table and the matched rows from the right table.

3. What do you mean by ‘databases’?

A database is a structured collection of data stored in a computer system and organized in a way to be quickly searched. With databases, information can be rapidly retrieved.

4. What does a MySQL database contain?

A MySQL database contains one or more tables, each of which contains records or rows. Within these rows are various columns or fields that contain the data itself.

5. How can you interact with MySQL?

There are three main ways you can interact with MySQL: 

  • using a command line
  • via a web interface
  • through a programming language

6. What are MySQL Database Queries?

A query is a specific request or a question. One can query a database for specific information and have a record returned.

7. What are some of the common MySQL commands?

Command Action
ALTER To alter a database or table
BACKUP To back-up a table
\\c To cancel Input
CREATE To create a database
DELETE To delete a row from a table
DESCRIBE To describe a table's columns
DROP To delete a database or table
EXIT(ctrl+c) To exit
GRANT To change user privileges
HELP (\\h, \\?) Display help
INSERT Insert data
LOCK Lock table(s)
QUIT(\\q) Same as EXIT
RENAME Rename a Table
SHOW List details about an object
SOURCE Execute a file
STATUS (\\s) Display the current status
TRUNCATE Empty a table
UNLOCK Unlock table(s)
UPDATE Update an existing record
USE Use a database

8. How do you create a database in MySQL?

Use the following command to create a new database called ‘books’:


9. How do you create a table using MySQL?

Use the following to create a table using MySQL:

CREATE TABLE history (author VARCHAR(128),title VARCHAR(128),type VARCHAR(16),year CHAR(4)) ENGINE InnoDB;

10. How do you Insert Data Into MySQL?

The INSERT INTO statement is used to add new records to a MySQL table:

INSERT INTO table_name (column1, column2, column3,...)VALUES (value1, value2, value3,...)

If we want to add values for all the columns of the table, we do not need to specify the column names in the SQL query. However, the order of the values should be in the same order as the columns in the table. The INSERT INTO syntax would be as follows:

INSERT INTO table_nameVALUES (value1, value2, value3, ...);

11. How do you remove a column from a database?

You can remove a column by using the DROP keyword:

ALTER TABLE classics DROP pages;

12. How to create an Index in MySQL?

In MySQL, there are different index types, such as a regular INDEX, a PRIMARY KEY, or a FULLTEXT index. You can achieve fast searches with the help of an index. Indexes speed up performance by either ordering the data on disk so it's quicker to find your result or, telling the SQL engine where to go to find your data.

Example: Adding indexes to the history table:

ALTER TABLE history ADD INDEX(author(10));ALTER TABLE history ADD INDEX(title(10));ALTER TABLE history ADD INDEX(category(5));ALTER TABLE history ADD INDEX(year);DESCRIBE history;

13. How to Delete Data From a MySQL Table?

In MySQL, the DELETE statement is used to delete records from a table:

DELETE FROM table_nameWHERE column_name = value_name

14. How do you view a database in MySQL?

One can view all the databases on the MySQL server host using the following command:


15. What are the Numeric Data Types in MySQL?

MySQL has numeric data types for integer, fixed-point, floating-point, and bit values, as shown in the table below. Numeric types can be signed or unsigned, except BIT. A special attribute enables the automatic generation of sequential integer or floating-point column values, which is useful for applications that require a series of unique identification numbers.

Type Name Meaning
TINYINT Very Small Integer
SMALLINT Small Integer
MEDIUMINT Medium-sized Integer
INT Standard Integer
BIGINT Large Integer
DECIMAL Fixed-point number
FLOAT Single-precision floating-point number
DOUBLE Double-precision floating-point number
BIT Bit-field

16. What are the String Data Types in MySQL?

Type Name Meaning
CHAR fixed-length nonbinary(character) string
VARCHAR variable-length nonbinary string
BINARY fixed-length binary string
VARBINARY variable-length binary string
TINYBLOB Very small BLOB(binary large object)
TINYTEXT A very small nonbinary string
TEXT Small nonbinary string
MEDIUMTEXT Medium-sized nonbinary string
LONGTEXT Large nonbinary string
ENUM An enumeration; each column value is assigned, one enumeration member
SET A set; each column value is assigned zero or more set members
NULL NULL in SQL is the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank. This value is different than a zero value or a field that contains spaces.

17. What are the Temporal Data Types in MySQL?

Type Name Meaning
DATE A date value, in ' CCYY-MM-DD ' Format
TIME A Time value, in ' hh : mm :ss ' format
DATETIME Date and time value, in ' CCYY-MM-DD hh : mm :ss ' format
TIMESTAMP A timestamp value, in ' CCYY-MM-DD hh : mm :ss ' format
YEAR A year value, in CCYY or YY format

Example: To select the records with an Order Date of "2018-11-11" from a table:

SELECT * FROM Orders WHERE OrderDate='2018-11-11'

18. What is BLOB in MySQL?

BLOB is an acronym that stands for a binary large object. It is used to hold a variable amount of data.
There are four types of BLOB:

  • BLOB

A BLOB can hold a very large amount of data. For example - documents, images, and even videos. You could store your complete novel as a file in a BLOB if needed.

19. How to add users in MySQL?

You can add a User by using the CREATE command and specifying the necessary credentials. For example:

CREATE USER ‘testuser’ IDENTIFIED BY ‘sample password’;


20. What are MySQL “Views”?

In MySQL, a view consists of a set of rows that is returned if a particular query is executed. This is also known as a ‘virtual table’. Views make it easy to retrieve the way of making the query available via an alias. 
The advantages of views are:

  • Simplicity
  • Security
  • Maintainability

21. How do you create and execute views in MySQL?

Creating a view is accomplished with the CREATE VIEW statement. As an example:


22. What are MySQL Triggers?

A trigger is a task that executes in response to some predefined database event, such as after a new row is added to a particular table. Specifically, this event involves inserting, modifying, or deleting table data, and the task can occur either prior to or immediately following any such event. 
Triggers have many purposes, including:

  • Audit Trails
  • Validation
  • Referential integrity enforcement

23. How many Triggers are possible in MySQL?

There are six Triggers allowed to use in the MySQL database:

  • Before Insert
  • After Insert
  • Before Update
  • After Update
  • Before Delete
  • After Delete

24. What is the MySQL server?

The server, mysqld, is the hub of a MySQL installation; it performs all manipulation of databases and tables.

25. What are the MySQL clients and utilities?

Several MySQL programs are available to help you communicate with the server. For administrative tasks, some of the most important ones are listed here:

mysql—An interactive program that enables you to send SQL statements to the server and to view the results. You can also use mysql to execute batch scripts (text files containing SQL statements).

mysqladmin—An administrative program for performing tasks such as shutting down the server, checking its configuration, or monitoring its status if it appears not to be functioning properly.

mysqldump—A tool for backing up your databases or copying databases to another server.

mysqlcheck and myisamchk—Programs that help you perform table checking, analysis, and optimization, as well as repairs if tables become damaged. mysqlcheck works with MyISAM tables and to some extent with tables for other storage engines. myisamchk is for use only with MyISAM tables.

26. What are the types of relationships used in MySQL?

There are three categories of relationships in MySQL:

  • One-to-One: Usually, when two items have a one-to-one relationship, you just include them as columns in the same table.
  • One-to-Many: One-to-many (or many-to-one) relationships occur when one row in one table is linked to many rows in another table.
  • Many-to-Many: In a many-to-many relationship, many rows in one table are linked to many rows in another table. To create this relationship, add a third table containing the same key column from each of the other tables

27. What is Scaling in MySQL?

In MySQL, scaling capacity is actually the ability to handle the load, and it’s useful to think of load from several different angles such as:

  • Quantity of data
  • Number of users
  • User activity
  • Size of related datasets

28. What is Sharding in SQL?

The process of breaking up large tables into smaller chunks (called shards) that are spread across multiple servers is called Sharding. 
The advantage of Sharding is that since the sharded database is generally much smaller than the original; queries, maintenance, and all other tasks are much faster.

29. What are Transaction Storage Engines in MySQL?

To be able to use MySQL’s transaction facility, you have to be using MySQL’s InnoDB storage engine (which is the default from version 5.5 onward). If you are not sure which version of MySQL your code will be running on, rather than assuming InnoDB is the default engine you can force its use when creating a table, as follows.

Published On: 2024-01-17