MySQL Interview Questions (original) (raw)

Last Updated : 24 Sep, 2025

MySQL is an open-source, relational database management system (RDBMS) that uses Structured Query Language (SQL) to manage and manipulate data. It is one of the most popular database systems used in web applications, known for its speed, reliability, and ease of use. MySQL is commonly used in conjunction with programming languages such as PHP, Java, and Python to build dynamic websites and applications.

MySQL Basic Interview Questions

1. How to create a database in MySQL?

To create a database in MySQL, we can use the CREATE DATABASE statement followed by the name we want to give to our database. For example:

CREATE DATABASE mydatabase;

2. What are different String Data Types in MySQL?

Here’s a one-line summary for each MySQL string type:

3. 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 'username'@'host' IDENTIFIED BY 'password';

4. Explain the differences between SQL and MySQL?

5. What is the MySQL server's default port?

The default port for MySQL server is 3306. This is the port number used by MySQL to communicate with clients and other services by default, unless it is configured to use a different port in the MySQL configuration file (my.cnf or my.ini).

6. How can we learn batch mode in MySQL?

Batch mode in MySQL is used to execute a series of SQL queries from a file (often referred to as a batch file). The file contains multiple SQL statements that are executed in sequence. Here's the correct way to run MySQL in batch mode:

mysql -u username -p database_name < batch-file.sql

7. How many different tables are present in MySQL?

There are several storage engines in MySQL, and the table types depend on which storage engine is used. The common storage engines are:

8. What arethe differences between CHAR and VARCHAR data types in MySQL?

9. What is Difference between CHAR_LENGTH and LENGTH?

**Example:

SELECT LENGTH('Hello'); -- Returns 5 (1 byte per character in ASCII) SELECT CHAR_LENGTH('Hello'); -- Returns 5 (5 characters)

10. What do you understand by % and _ in the like statement?

In the LIKE statement, the % and _ symbols are used for pattern matching:

**Example:

SELECT * FROM Employees WHERE Name LIKE 'J%n';
SELECT * FROM Employees WHERE Name LIKE 'J_n';

11. How many index columns can be created in a table?

There are 16 indexed columns can be created in a table.

12. What are string types available for columns?

There are six string types available for the column.

13. Explain the main difference between FLOAT and DOUBLE?

**Example:

CREATE TABLE example ( float_value FLOAT, double_value DOUBLE );

14. Explain the differences between BLOB and TEXT.

**BLOB: Binary Large Object is used to store large amounts of binary data, such as images or files. The sorting and comparison of BLOB values are **case-sensitive and done in binary format. Types:

**TEXT: Used to store large amounts of text data. Unlike BLOB, TEXT data is **case-insensitive and sorted lexicographically.

15. Explain the difference between having and where clause in MySQL.

16. Explain REGEXP?

REGEXP (Regular Expression) is used in MySQL to perform pattern matching with string values. It allows you to use regular expressions for string searches within a query. The pattern can match anywhere in the string and is case-insensitive by default.

Syntax:

SELECT * FROM table_name WHERE column_name REGEXP 'pattern';

17. How can we add a column in MySQL?

A column is a series of table cells that store a value for table's each row. we can add column by using ALTER TABLE statement.

ALTER TABLE tab_name

ADD COLUMN col_name col_definition [FIRST|AFTER exist_col];

18. How to delete columns in MySQL?

We can remove columns in MySQL by using ALTER TABLE statement.

**Syntax:

**ALTER **TABLE table_name **DROP **COLUMN column1, column2....;

19. How to delete a table in MySQL?

We can delete a table by using DROP TABLE statement. This statement deletes complete data of table.

DROP TABLE table-name;

20. How are mysql_fetch_array() and mysql_fetch_object() different from each another?

mysql_fetch_array() Gets a result row as a related array or a regular array from database. mysql_fetch_object gets a result row as an object from the database.

21. How to get the top 10 rows?

The following query will be used to get top 10 rows.

SELECT * FROM table_name LIMIT 0,10;

22. How does NOW() differ from CURRENT_DATE()?

current year, month, and date with hours, minutes, and seconds is shown by using NOW() command while CURRENT_DATE shows current year current month, and current date.

**Syntax:

SELECT NOW();

SELECT CURRENT_DATE();

23. What is the use of the ‘DISTINCT’ keyword in MySQL?

The DISTINCT keyword is used to remove duplicate rows from the result set, returning only unique values for the specified columns. The DISTINCT keyword is used with the SELECT statement.

**Syntax:

SELECT DISTINCT colu1, colum2..

FROM table_name;

24. Which storage engines are used in MySQL?

Storage engines are also called table types. Data is stored in a file using multiple techniques.

Below are some techniques.

25. How to create a table in MySQL?

To create a table in MySQL, the CREATE TABLE statement is used. Here's the syntax:

**Syntax:

CREATE TABLE Employee (

Employee_Name VARCHAR(128),

Employee_ID VARCHAR(128),

Employee_Salary VARCHAR(16),

Designation CHAR(4)

);

26. How to insert data in MySQL table?

We can add data to a table using the INSERT INTO statement.

**Syntax:

**INSERT **INTO table_name ( field1, field2, field3 )

**VALUES ( value1, value2, value3 );

27. Write a statement to find duplicate rows In the MySQL table?

The below statement is used to find duplicate rows.

SELECT Table_Name, Category

FROM Product

GROUP BY Name, Category

HAVING COUNT(id) > 1;

28. What types of relationships are used in MySQL?

There are three types of relationships used in MySQL.

29. How to insert Date in MySQL?

We can use INSERT statement to insert date in MySQL table. MySQL default date format is YYYY-MM-DD. Automatic MySQL consist many data types to store dates.

**Syntax:

**INSERT **INTO table_name (column_name, column_date) **VALUES ('DATE: Manual Date', '2023-5-20');

30. What is join? Tell different join in MySQL.

JOINs in MySQL are used to combine rows from two or more tables based on a related column. The most common types of joins are:

**1. INNER JOIN: Returns records that have matching values in both tables.

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

**2. LEFT JOIN (or **LEFT OUTER JOIN): Returns all records from the left table and matched records from the right table. If there’s no match, NULL values are returned for the right table.

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

**3. RIGHT JOIN (or **RIGHT OUTER JOIN): Returns all records from the right table and matched records from the left table. If there’s no match, NULL values are returned for the left table.

SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;

4. **FULL JOIN (MySQL does **not support FULL JOIN natively): Returns all records when there is a match in either left (table1) or right (table2).

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id UNION SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;

31. What is a primary key? How to drop the primary key in MySQL?

A primary key in MySQL is a single field or a group of fields that are used to uniquely identify each record in a table. A primary key cannot be null or empty. ALTER TABLE statement is used to delete a primary key from a table.

**Syntax:

ALTER TABLE table_name DROP PRIMARY KEY;

32. What is a heap table in MySQL?

A heap table is usually used for temporary and fast temporary storage.

33. What is the main difference between the primary key and the candidate key?

The primary key uniquely identified each row of a table. only one primary key is available for a table.

34. What is the difference between DELETE and TRUNCATE commands in MySQL?

**DELETE Command is used to delete rows from the table depending on given the condition. TRUNCATE command is used to DELETE all rows from the table. DELETE command is a Data Manipulation Language command. TRUNCATE command is a Data Definition Language command.

35. What is InnoDB?

A SQL storage database is called InnoDB database. The InnoDB offers ACID transactions, row-level locking, and foreign key support. InnoDB is owned by Oracle Corporation.

36. What is the difference between UNION and UNION ALL in MySQL?

During combining the results of more than one SELECT statement, the UNION operator deletes duplicate rows between the various SELECT statements. The UNION ALL also combines the result set of more than one SELECT statement, but it does not delete duplicate rows.

**37. What is a ‘timestamp’ in MySQL?

In MySQL, When a row is added to or updated in a table, a data type "timestamp" automatically records the time.

38. What is the use of ENUMs in MySQL?

ENUM is a string object that can be used when creating tables to specify a set of predefined values.

CREATE table size(name ENUM('Small', 'Medium', 'Large');

39. How can you control max size of heap in MySQL?

MySQL config variable _max_heap_table_size can be used to control the max size of heap.

**Syntax:

SET max_heap_table_size = M

40. What is a view? How to create a view?

A database object that has no value is called view. Rows and columns exist in a view. A view is virtual table. it is created by combining one or more tables. The difference of a view and a table is that views are definition that build on other tables. If the underlying table changes, the View will also reflect those same changes.

**Syntax:

CREATE VIEW view_name AS

SELECT columns

FROM tables

[WHERE conditions];

41. Where MyISAM table will be stored and also give MyISAM formats of storage?

Every MyISAM table is stored on disk.

There are three storage formats can be used .

42. How can we save images in MySQL?

In MySQL, Blobs can be used to store images. All database images are first converted into blobs then saved and then they will be added to the database, and finally, it will later be stored on the disk.

43. What are trigger and how many TRIGGERS are available in MySQL table?

A trigger is a procedural code in a database. Triggers are automatically triggered when specific events occur on a particular table. During column updating triggers are invoked automatically.

SIX triggers are available in MySQL table.

44. What are the different characteristics of MySQL MyISAM Static and MyISAM Dynamic?

MySQL Advanced Interview Questions

45. What are Access Control Lists?

A list of permissions known as an Access Control List is connected to an object. It is MySQL server security model helps in troubleshooting issues like users being unable to connect. MySQL holds the ACL's cached in memory. ACL's also called grant tables. MySQL verifies the authentication data and permissions against the ACLs. It predetermined order whenever a user tries to log in or execute a command.

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

Normalization is used to avoid duplication and redundancy. it is a process of organizing data. There are many normal forms of normalization. which are also called successive levels. The first three regular forms are sufficient.

47. What are various ways to create an index?

There are many options to create an index as below:

48. What are a clustered index and a non clustered index?

**Cluster Index: An index type used to arrange data in a table is called a clustered index. The table's data are stored in a specific order based on the clustered index.

**Non Cluster Index: A non-clustered index is also a type of index used to organize data in a table. The table's data are not stored in a specific order based on the non clustered index.

49. How tovalidate emails using a single query?

We can use the regular expressions function (REGEXP_LIKE) to validate emails. Below is the example of validate emails using a single query.

**SELECT Email

**FROM Vehicle

**where NOT REGEXP_LIKE(Email, '[A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}', 'i');

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

The MySQL Server is restricted from loading directories using the LOAD DATA INFILE command by the -secure-file-priv option. Use the SHOW VARIABLES LIKE "secure_file_priv" command to view the directory that has been configured.

There are two options to handle as below.