Top 70+ MySQL Interview Questions and Answers

1. What is SQL Server?

SQL Server is one of the database management systems (DBMS) and is designed by
Microsoft. DBMS are computer software applications with the capability of interacting with
users, various other applications, and databases. The objective of SQL Server is capturing
and analyzing data and managing the definition, querying, creation, updating, and
administration of the database.

2. How and why use SQL Server?

SQL Server is free and anyone can download and use it. The application uses SQL
(Structured Query Language), and it is easy to use.

3. What is the default port for MySQL Server?

The default port for MySQL Server is 3306. Another standard default port is 1433
in TCP/IP for SQL Server.

4. What is the difference between CHAR and VARCHAR?

When a table is created, CHAR is used to define the fixed length of the table and columns.
The length value could be in the range of 1–255. The VARCHAR command is used to adjust
the column and table lengths as required.

5. How can a user get the current SQL version?

The syntax for getting the current version of MySQL:

1SELECT VERSION ();

6. What is the difference between primary key and unique key?

While both are used to enforce the uniqueness of the column defined, the primary key would
create a clustered index, whereas the unique key would create a non-clustered index on the
column. The primary key does not allow ‘NULL’, but the unique key does.

7. What are the differences between a primary key and a foreign key?
Primary Key Foreign Key
It helps in the unique identification of data It helps establish a link between
in a database tables
There can be only one primary key for a There can be more than one
table foreign key for a table
Primary key attributes cannot have Duplicate values are acceptable
duplicate values in a table for a foreign key
Null values are not acceptable Null values are acceptable
We can define primary key constraints for It cannot be defined for temporary
temporarily created tables tables
The primary key index is automatically The index is not created
created automatically

8. What is the use of ENUM in MySQL?

The use of ENUM will limit the values that can go into a table. For instance, a user can create
a table giving specific month values and other month values would not enter into the table.

9. What is the difference between LIKE and REGEXP operators in MySQL?

LIKE matches an entire column. If the text to be matched existed in the middle of a column
value, LIKE would not find it and the row would not be returned (unless wildcard characters
were used). LIKE is denoted using the ‘%’ sign.

For example:
1SELECT * FROM user WHERE user name LIKE “%NAME”

REGEXP, on the other hand, looks for matches within column values, and so if the text to be
matched existed in the middle of a column value, REGEXP would find it and the row would
be returned. The use of REGEXP is as follows:
1SELECT * FROM user WHERE username REGEXP “^NAME”;

10. What are the types of joins in MySQL?

There are four types of joins in MySQL.

Inner join returns rows if there is at least one match in both tables. Left join returns all the
rows from the left table even if there is no match in the right table. Right join returns all the
rows from the right table even if no matches exist in the left table. Full join would return
rows when there is at least one match in the tables.

11. What are the storage models of OLAP?

The storage models in OLAP are MOLAP, ROLAP, and HOLAP.

12. Can you use MySQL with Linux operating system?

Yes. The syntax for using MySQL with Linux operating system is as follows:

MySQL Intermediate Interview Questions

13. Compare MySQL vs SQL Server.

Criteria MySQL SQL Server
Developed by Oracle Microsoft
Programmed in C and C++ Mainly C++, but some parts in C
Platforms Supports many platforms Supports only Linux and Windows
Syntax Complex Syntax Simpler and easy-to-use syntax

14. What are the features of MySQL?

MySQL provides cross-platform support, a wide range of interfaces for application

programming, and has many stored procedures like triggers and cursors that help in managing
the database.

15. What are the advantages and disadvantages of using MySQL?

There are various advantages and disadvantages of using MySQL. Some of them are given
below:

Advantages
  MySQL helps in the secure management of databases. By using it, we can
securely execute database transactions.
 It is fast and efficient in comparison to other database management systems as it
supports varieties of storage engines.
 As its transaction processing is high, MySQL can execute millions of queries.

Besides, some of the features that make MySQL unique are deadlock identification,
execution of multiple transactions, efficient processing, and easy management.

Disadvantages

 Scalability in MySQL is a redundant task.

 MySQL serves good for large databases mostly.
 There are issues of the instability of software.

16. What is the Traditional Network Library for a system?

The Traditional Network Library is a software framework that offers pre-built functions and
protocols for developing networked applications. It simplifies the complexities of socket
programming by providing higher-level abstractions, allowing developers to establish
connections, exchange data, and manage network protocols in a conventional manner.

17. What do DDL, DML, and DCL stand for?

DDL is the abbreviation for Data Definition Language dealing with database schemas, as
well as the description of how data resides in the database. An example of this is the
CREATE TABLE command. DML denotes Data Manipulation Language which includes
commands such as SELECT, INSERT, etc. DCL stands for Data Control Language and
includes commands like GRANT, REVOKE, etc.
18. What is a join in MySQL?

In MySQL, joins are used to query data from two or more tables. The query is made using
the relationship between certain columns existing in the table. There are four types of joins in
MySQL.

Inner join returns rows if there is at least one match in both tables. Left join returns all the
rows from the left table even if there is no match in the right table. Right join returns all the
rows from the right table even if no matches exist in the left table. Full join would return
rows when there is at least one match in the tables.
19. What are the common MySQL functions?

Common MySQL functions are as follows:

 ABS(): Returns the absolute value of a number. It removes the negative sign if
the number is negative.
 ROUND(): Rounds a number to a specified number of decimal places. It can
round to the nearest integer or a specific decimal position.
 CEIL(): Returns the smallest integer greater than or equal to a given number. It
rounds up the value to the nearest integer.
 FLOOR(): Returns the largest integer less than or equal to a given number. It
rounds down the value to the nearest integer.
 EXP(): Calculates the exponential value of a number. It returns the result of
raising the mathematical constant e to the power of the given number.
 LOG(): Calculates the natural logarithm of a number. It returns the logarithm
base e (natural logarithm) of the given number.
 NOWO: The function for returning the current date and time as a single value
 CURRDATEO: The function for returning the current date or time
 CONCAT (X, Y): The function to concatenate two string values creating a single
string output
 DATEDIFF (X, Y): The function to determine the difference between two dates

20. What are Heap Tables?

Basically, Heap tables are in-memory tables used for high-speed temporary storage. But,
TEXT or BLOB fields are not allowed within them. They also do not support AUTO
INCREMENT.

21. What is the syntax for concatenating tables in MySQL?

The syntax for concatenating tables in MySQL:

1CONCAT (string 1, string 2, string 3)

22. What is the limit of indexed columns that can be created for a table?
It depends on the storage engine used:

For the MyISAM storage engine, the limit is 64 while for the InnoDB storage engine, the
limit is 16.

23. What are the different types of strings used in database columns in MySQL?

In MySQL, the different types of strings that can be used for database columns are SET,
BLOB, VARCHAR, TEXT, ENUM, and CHAR.

24. Is there an object-oriented version of MySQL library functions?

Yes. MySQL is the object-oriented version of MySQL, and it interfaces in PHP.

25. What is the storage engine used for MySQL?

The storage engine used for MySQL refers to the component responsible for managing how
data is stored, organized, and accessed within the database system. The default storage engine
in MySQL is InnoDB, offering features like transaction support and referential integrity.

Other commonly used engines include MyISAM, known for its simplicity and performance,
and NDB Cluster, providing distributed storage for MySQL Cluster. The choice of engine
depends on specific application requirements such as data integrity, performance, and
scalability.

26. What is the difference between the primary key and the candidate key?

The primary key in MySQL is used to identify every row of a table in a unique manner. For
one table, there is only one primary key. The candidate keys can be used to reference the
foreign keys. One of the candidate keys is the primary key.
27. What are the different types of tables in MySQL?

 MyISAM is the default table that is based on the sequential access method.
 Heap is the table that is used for fast data access, but the data will be lost if the
table or the system crashes.
 InnoDB is the table that supports transactions using the COMMIT and
ROLLBACK commands.
 BDB can support transactions similar to InnoDB, but the execution is slower.

28. What are the TRIGGERS that can be used in MySQL tables?

Following TRIGGERS are allowed in MySQL:

  BEFORE INSERT
 AFTER INSERT
 BEFORE UPDATE
 AFTER UPDATE
 BEFORE DELETE
 AFTER DELETE

29. How to use the MySQL slow query log?

Information that is provided on the slow query log could be huge in size. The query could
also be listed over a thousand times. In order to summarize the slow query log in an
informative manner, one can use the third-party tool ‘pt-query-digest’.

30. How can one take an incremental backup in MySQL?

A user can take an incremental backup in MySQL using Percona XtraBackup.

31. What is meant by transaction? What are ACID properties?

In the context of databases, a transaction refers to a logical unit of work that consists of one
or more database operations. These operations are treated as a single, indivisible unit,
meaning they either all succeed or all fail. Transactions are used to ensure data consistency
and integrity within a database system.

Transactions are commonly used in scenarios where multiple database operations need to be
executed as a cohesive unit. For example, consider a banking application where a transfer of
funds involves deducting an amount from one account and adding it to another account. In
this case, the deducting and adding operations should be performed together to maintain data
consistency. If one operation succeeds but the other fails, it could lead to an inconsistent state
in the database.

ACID properties are a set of fundamental principles that ensure reliability and consistency in
database transactions. ACID stands for Atomicity, Consistency, Isolation, and Durability.
32. What is the difference between BLOB and TEXT?

BLOBs are binary large objects holding huge data. The 4 types of BLOB are:

TINYBLOB: This data type can store up to 255 bytes of binary data.

BLOB: This data type can store up to 65,535 bytes of binary data.

MEDIUMBLOB: This data type can store up to 16,777,215 bytes of binary data.

LONGBLOB: This data type can store up to 4 GB of binary data.

TEXT is a case-sensitive BLOB. Four types of TEXT are TINY TEXT, TEXT,
MEDIUMTEXT, and LONG TEXT.

33. What is the TIMESTAMP data type?

Timestamp in SQL Server helps in row versioning. Row versioning is a type of concurrency
that allows retaining the value until it is committed in the database. It shows the instant time
of any event. It consists of both the date and time of the event. Also, timestamp helps in
backing up data during the failure of a transaction.

While we insert, update, or delete a record, the date and time automatically get inserted.

 Format of timestamp: YYYY-MM-DD HH:MM: SS

 Range of timestamp: “1970-01-01 00:00:01” UTC to “2038-01-19 03:14:07”
UTC

34. What is the function of mysqldump?

As the name suggests, mysqldump is used to dump one or more created databases. It
performs backups for data or transfers the data from SQL Server to another. Also, it helps in
producing the initial database schema by logical backups. Moreover, unlike triggers,
mysqldump does not backup the stored procedures or functions by default.

Syntaxes
For a single database:
1mysqldump [options] db_name [tables]

For multiple databases:

1mysqldump [options] –databases db1 [db2 db3...]

For all databases:

1mysqldump [options] –all-databases

35. How to create a user-defined function in MySQL?

User-defined functions are created using the ‘CREATE FUNCTION’ syntax. It allows you to
define your function that can be used in SQL statements. They come in handy when you
perform calculations or operations frequently.

36. What are subqueries in MySQL?

When we write queries inside a query or nested queries are called subqueries. They allow
complex operations and can be used in SELECT, INSERT, UPDATE, or DELETE
statements. It can return single values, row sets, or tables as well as an output. They are often
used for comparison and aggregation.

37. How does MySQL handle transactions?

MySQL handles transactions using START TRANSACTION, COMMIT, and ROLLBACK.

Transactions ensure that a series of database operations will either all succeed or fail,
maintaining data integrity.

38. How to implement full-text search in MySQL?

Full-text search in MySQL can be implemented using FULLTEXT indexes. It allows natural
language searching of text-based columns and is particularly efficient for searching large
texts within the VARCHAR and TEXT columns.

39. What is known as the covering index in MySQL?

A covering index is an index that includes all the columns needed to answer a query,
allowing the database to retrieve query results directly from the index without accessing the
table data.

40. What is the use of MySQL in web development?

MySQL is a cornerstone for web development, offering a reliable and efficient database
management system for storing and retrieving data for websites and supporting dynamic
content generation using the CRUD (Create, Read, Update, Delete) operations performed in
MySQL.

41. How to handle large datasets in MySQL?

Handling large datasets in MySQL efficiently requires partitioning tables, using indexing
strategically, optimizing queries, and possibly leveraging MySQL’s advanced storage engines
like InnoDB for better performance and reliability.

43. How to resolve the problem of the data disk that is full?

When the data disk is full and overloaded, the way out is to create and soft link and move
the .frm and the .idb files into that link location.

44. What is the difference between the DELETE TABLE and TRUNCATE TABLE

commands in MySQL?

DELETE TABLE

1. Syntax and Usage: Typically, you would use DELETE FROM table_name
[WHERE condition];. “DELETE TABLE” is not a standard SQL statement.
2. Logged Operation: DELETE is indeed a logged operation, and each row
deletion is logged.
 3. Where Clause: It allows for conditionally deleting data, i.e., you can specify
a WHERE clause to delete specific data.
4. Triggers: DELETE will activate any triggers associated with the table.
5. Speed: It is generally slower than TRUNCATE especially for deleting all rows.
6. Space Reclaim: Space used by the table is not reclaimed (unless
using DELETE with no WHERE clause in some MySQL storage engines like
InnoDB).

TRUNCATE TABLE

1. Syntax and Usage: The correct syntax is TRUNCATE TABLE table_name;.

2. Logged Operation: TRUNCATE is also a logged operation, but it typically logs
fewer transactions because it logs the deallocation of the data pages in which the
data exists, not the individual row deletions.
3. Where Clause: It does not allow for a WHERE clause. It will remove all rows.
4. Triggers: TRUNCATE will not activate triggers.
5. Speed: It is usually faster for deleting all rows in a table because it does not log
individual row deletions.
6. Space Reclaim: Space used by the table is reclaimed, and the table is reset to its
empty state, often times also resetting the auto-increment value to zero (or the
starting value).

Rollback

Both DELETE and TRUNCATE operations can be rolled back if used within a transaction
that is not yet committed. However, it’s essential to note that TRUNCATE is a data definition
language (DDL) statement, and in some database systems, it might auto-commit the
transaction, making the rollback impossible for the previous transactions within the same
transaction block.

47. What is an access control list?

Every organization has some crucial data specific to its business. This data needs secure
access so that any consequence due to data loss can be avoided. For this, organizations create
a sequence of permissions that are linked to various data objects. These lists are known as the
access control list (ACL).

ACL serves as the basis for the server’s security that helps troubleshoot the connection
problems for users. These are also known as grant tables that are cached by MySQL. MySQL
verifies a user for authentication and grants permissions in a sequence when the user executes
a command.

48. What is the main difference between MySQL and PostgreSQL?

The basic difference between MySQL and PostgreSQL are:

My SQL is purely a relational database whereas PostgreSQL is an object-relational database.

PostgreSQL is more complex and slower than MySQL. In MYSQL, troubleshooting is easy
but it is difficult to troubleshoot PostgreSQL. MySQL does not support materialized view
whereas PostgreSQL support materialized view.

49. How can you optimize MySQL queries for faster data retrieval?

To optimize MySQL queries for faster data retrieval, indexing on columns can be used in
WHERE clauses; avoid SELECT *; and refine queries with EXPLAIN to analyze
performance. Implementing query caching can also significantly reduce load times for
frequently requested data.

50. Explain the GRANT command in MySQL.

The GRANT command is used to give permission or certain privileges to perform various
database operations whenever a new user is created.

51. What is the use of the DELIMITER command in MySQL?

The DELIMITER command is used to change the default delimiter used by MySQL, which is
a semicolon (;). DELEIMITER is used while writing TRIGGER and STORED
PROCEDURES in MySQL.

MySQL Query Interview Questions

52. Write an SQL query to find the second maximum salary.

1SELECT MAX(salary) from table_name WHERE salary < (SELECT MAX(salary) from table_nam

53. Write a solution to swap all 'f' and 'm' values (i.e., change all 'f' values to 'm' and
vice versa) with a single update statement and no intermediate temporary tables.
id Name Sex Salary
1 A m 2500
2 B f 1500
3 C m 5500
4 D f 500
UPDATE salary
1
2SET sex=CASE
3WHEN sex='f' THEN 'm'
4WHEN sex='m' THEN 'f'
5END
6WHERE sex IN ('m', 'f')
54. Write a query to find the largest single number. A single number is a number that

appears only once in the table.

Number
8
8
3
3
1
4
5
6
SELECT MAX(Number) AS LargestSingleNumber
1
2FROM (
3 SELECT Number
4 FROM numbers
5 GROUP BY Number
6 HAVING COUNT(Number) = 1
7) AS SingleNumbers

55. Write a query to create a table using MySQL.

CREATE TABLE table_name (fname VARCAHR(56), name VARCHAR(56), gender VARCHAR(56)
1DATE)

56. Write an SQL query to fetch the EmpId and FullName of all the employees working

under the Manager with id – 453.

1SELECT EmpId, FullName
2FROM EmployeeDetails
3WHERE ManagerId = 453;

57. Write an SQL query to find the maximum, minimum, and average salary of the

employees.
1SELECT Max(Salary),
2Min(Salary),
3AVG(Salary)
4FROM EmployeeSalary;

58. Write an SQL query to find the employee ID whose salary lies in the range of 60000

and 120000
1SELECT EmpId, Salary
2FROM EmployeeSalary
3WHERE Salary BETWEEN 9 = 60000 AND 120000

59. Write an SQL query to fetch the employees whose names begin with any two

characters, are followed by the text “ng” and end with any sequence of characters.
1SELECT FullName
2FROM EmployeeDetails
3WHERE FullName LIKE ‘__ng%’;

60. Write an SQL query to fetch employee names having a salary greater than or equal

to 45000 and less than or equal to 89000.

1SELECT FullName
2FROM EmployeeDetails
3WHERE EmpId IN
4(SELECT EmpId FROM EmployeeSalary
5WHERE Salary BETWEEN 45000 AND 89000);

61. Write an SQL query to fetch all the employees who are also managers from the

EmployeeDetails table.
1SELECT DISTINCT e.fullname
2FROM Employee e
3INNER JOIN Employee m
4ON e.EmpID = m.ManagerID
62. Write an SQL query to remove duplicates from a table without using a temporary

table.
1DELETE e1 FROM Employee e1
2INNER JOIN Employee e2
3WHERE e1.EmpId > e2.EmpId
4AND e1.FullName = e2.FullName
5AND e1.ManagerId = e2.ManagerId
6AND e1.DOJ = e2.DOJ

63. Write an SQL query to fetch only even rows from the table.
1SELECT * FROM EmployeeDetails
2WHERE MOD (EmpId, 2) = 0;

MySQL Database Interview Questions

64. How do you search exactly as you type in MySQL?

To search exactly as typed, add BINARY before your search term. For instance, SELECT *
FROM table_name WHERE BINARY column_name = ‘value’; checks for exact matches,
including letter cases.

65. Why use the HAVING clause in MySQL?

Use HAVING to filter data after grouping it with GROUP BY. It works like WHERE but
applies to groups, not individual rows.

66. How do you see all shortcuts for finding data in a table?

To see all data-finding shortcuts, type SHOW INDEX FROM table_name; It shows
shortcuts’ names, types, and which columns they use.

67. How do you find the difference in time between the two dates?
Use TIMESTAMPDIFF to calculate the time gap. For example, SELECT
TIMESTAMPDIFF(DAY, ‘2020-01-01’, ‘2020-01-31’); tells you the days between dates.

68. What does a VIEW do in MySQL?

A VIEW acts like a pretend table made from other table data. It makes complex data simple,
limits access for safety, and shows parts of data.

69. How do you copy how a table is set up but not its data?

To copy a table’s setup, use CREATE TABLE new_table LIKE original_table;. This copies
how the table is set up without the data.

70. What's the difference between CHAR_LENGTH and LENGTH in MySQL?

CHAR_LENGTH counts how many characters are in a string, seeing all as single characters.
LENGTH counts how many bytes the string is, which changes with different characters.

71. How do you change a table's name?

Change a table’s name with RENAME TABLE old_table_name TO new_table_name;. This

updates the table’s name in your database.

72. When is ENUM used in MySQL?

Use ENUM when a column only accepts specific values. ENUM lets you pick from a list you
set when you make the table.

73. How do you check your MySQL version?

To see your MySQL version, run SELECT VERSION();. This shows the version and details
of your MySQL server.

74. Why is AUTO_INCREMENT used with primary keys?

AUTO_INCREMENT gives each new row a unique number, making it perfect for primary
keys. It makes adding records easy without manually setting the key.

75. How do you save your MySQL database as a file?

To save your database, use mysqldump -u username -p database_name > export_name.sql;.

This command makes a .sql file with your database’s structure and data.