Top 50 MySQL Interview Questions and Answers (2024)

1. What are the features of MySQL?

MYSQL is open-source, has very rich documentation, a wide range of interfaces and functions for application programming, and has many stored procedures like triggers and cursors that help in managing the database. Also, the Query optimizer is very efficient in MySQL.

2. Name the benefits and drawbacks of MySQL.

There are various advantages and drawbacks of using MySQL. a number of them are given below:
Advantages:

MySQL helps with the secure management of databases. By using it, we will securely execute database transactions.
It is fast and efficient as compared to other management systems because it supports sorts of storage engines.
MySQL can execute many queries due to high transaction processing.
Besides, a number of the features that make MySQL unique are deadlock identification, execution of multiple transactions, efficient processing, and straightforward management.

Disadvantages:

Scalability in MySQL may be a redundant task.
MySQL is good for giant databases mostly.
There are problems with the instability of software.

3. 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.

4. In which language MySQL has been written?

MySQL is written in C and C++, and its SQL parser is written in yacc.

5. What is the difference between MySQL and SQL?

SQL is known as the standard query language. It is used to interact with the database like MySQL. MySQL is a database that stores various types of data and keeps it safe.
A PHP script is required to store and retrieve the values inside the database.
SQL is a computer language, whereas MySQL is a software or an application
SQL is used for the creation of database management systems whereas MySQL is used to enable data handling, storing, deleting, and modifying data.

6. How to get the current MySQL version?

We will use the below query to get the current version of MySQL

SELECT VERSION();

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

The following TRIGGERS are allowed in MySQL:

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

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

LIKE is denoted using the ‘%’ sign. For example:

SELECT * FROM user WHERE user name LIKE “%NAME”

On the other hand, the use of REGEXP is as follows:

SELECT * FROM user WHERE username REGEXP “^NAME”;

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

Basically, DELETE TABLE is a logged operation, and every row deleted is logged. Therefore, the process is usually slow. TRUNCATE TABLE also deletes rows in a table, but it will not log any of the rows deleted. The process is faster here in comparison. TRUNCATE TABLE can be rolled back and is functionally similar to the DELETE statement without a WHERE clause.

10. What are the two methods of doing INNER JOINs in MySQL?

The inner join is also called a natural join. Inner join adds the records to the resultset only if both the tables have the record with the matching condition. In other words, it checks for equality.

We can do an inner join using the keywords INNER JOIN in the from clause of a query. Whenever we have a query where we check the equality of two columns, we are essentially doing an Inner join.

For example,

Select * from actor inner join customer using(first_name);

Select * from film f INNER join film_category fc ON f.film_id  = fc.film_id;

11. What is meant by transaction?

A Transaction in SQL is a single logical unit of work.
Transactions can make multiple changes to the database like creating tables, deleting records, updating records etc in the same database transaction.
The transaction is successful when all the operations in it are complete and are committed which means that the changes are committed to memory i.e. made permanent.
A transaction fails if any of the operations in it fail and cause a rollback. A rollback means that the changes are discarded and are not permanent.
Commit or Rollback is either implicit or explicit.
In MySQL, transactions with DDL instructions like CREATE, ALTER, RENAME, DROP and TRUNCATE are implicitly committed. The changes once made are permanent and users cannot control this.
Users can switch off auto-commit for DML instructions like INSERT, UPDATE, DELETE and SELECT.
Transactions follow the ACID properties.

12. What are ACID properties?

All database systems have ACID properties. ACID stands for Atomicity, Consistency, Isolation, and Durability. They ensure that all operations on the data are processed correctly and the database has consistency and reliability.

13. What are the differences between a primary key and a foreign key?

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

14. How do we create a user in a database?

To create a database user we use the create user statement. The Syntax is,

CREATE USER [IF NOT EXISTS] account_name
IDENTIFIED BY ‘password’;

The account_name consists of two parts: username followed by the @ sign followed by the hostname. The hostname is optional.

15. How to drop a user in a database?

To drop a user we use the DROP USER statement. The Basic syntax for dropping a user is,

DROP USER [IF EXISTS] <account_name>;

IF the user has a hostname we have to include it while deleting the user.

16. What are the three main categories of datatypes MySQL supports?

There are three main categories of datatypes that MySQL allows. They are:

Number/Numeric datatype
Date datatype
String/Text/Character datatype

17. If a database with the name testdb exists and we try to re-create it. What happens?

If we try to create a database that already exists, MySQL throws an error ”Cannot create Database <dbname>; database exists”.

To reduce the severity of the error to a warning, we can use the command

CREATE DATABASE [IF NOT EXISTS] <db-name>

18. What are the privileges needed to create a table?

We need CREATE Privileges to create a table in MySQL.

19. What is the difference between the unique and primary key constraints?

Primary Key	Unique Key
Unique identifier for rows of a table	Unique identifier for rows of a table when the primary key is not present
Cannot be NULL	Can be NULL
Only one primary key can be present in a table	Multiple Unique Keys can be present in a table
present in a table	present in a table
Selection using the primary key creates a clustered index	Selection using a unique key creates a non-clustered index

20. IS the primary key automatically indexed in MySQL?

Yes, the primary key is automatically indexed in MySQL because the primary key, index, etc get stored into B-trees. All engines including InnoDB as well as MyISAM automatically support the primary key to be indexed.

The primary key is implicitly indexed in InnoDB, MyISAM, and other engines.

21. How can I define a column of a MySQL table PRIMARY KEY without using the PRIMARY KEY keyword?

As we know that a PRIMARY KEY column must have unique values and cannot have null values hence if we define a column with UNIQUE and NOT NULL constraints both then MySQL considers that column as PRIMARY KEY by default.

22. Can we remove a primary key from a table? IF yes, how?

Yes, we can remove a primary key constraint from a table. We will have to use the Alter table ..Drop the statement

Syntax:

ALTER TABLE <table-name> drop primary key;

23. How to add a column to an existing table?

We can use ALTER TABLE and ADD COLUMN command to add a column in an existing table as shown below,

ALTER TABLE table_name     
ADD COLUMN column_name column_definition [FIRST|AFTER existing_column];

24. How does multiple-column sorting work?

When sorting records using Multiple columns, the records are sorted on the basis of the first column first.

In case there is a conflict i.e. there are more than one record with the same value then the second column is considered. The conflict records are then sorted using the second column sort and so on.

Example: consider the records from a customer table. We are sorting on the basis of the following columns

SELECT * FROM CUSTOMER ORDER BY LAST_NAME DESC,FIRST_NAME ASC, address_id desc;

customer_id	store_id	first_name	last_name	Email	address_id
28	1	CYNTHIA	YOUNG	[email protected]	32
413	2	MARVIN	YEE	[email protected]	418
402	1	LUIS	YANEZ	[email protected]	407
318	1	BRIAN	WYMAN	[email protected]	323
31	2	BRENDA	WRIGHT	[email protected]	35
496	2	TYLER	WREN	[email protected]	501
107	1	LORI	WOOD	[email protected]	111
78	2	LORI	WOOD	[email protected]	82
581	1	VIRGIL	WOFFORD	[email protected]	587
541	2	DARREN	WINDHAM	[email protected]	547
8	2	SUSAN	WILSON	[email protected]	12

In the 2 highlighted records, The first name and the last name are the same, so there is a conflict, so the records will be sorted on the basis of descending address_id.

25. How to delete columns in MySQL?

We can remove, drop, or delete one or more columns in an existing table using the ALTER TABLE statement as follows:

ALTER TABLE table_name DROP COLUMN column_name1, column_name2....;

26. What is the best way to insert 10,000 records from one table to another?

If we want to keep the structure the same we can simply do an insert - Select *

Insert into <table1> select * from <table2>;

27. What are the differences between CHAR and VARCHAR data types?

CHAR and VARCHAR data types store string data.

Char:

Stores fixed-length data
Storage size = maximum size of the char datatype
Stores small data
Is faster

Varchar:

Stores variable-length data.
Storage size = length of the string inserted
Stores larger data
Is slower.

28. Which MySQL function is used to concatenate strings?

CONCAT() function can used to combine two or more string data.

Example:

The following SELECT query with CONCAT() function will combine five words, ‘Welcome’,’to’,’the’,’techieclues’,’website’.

Select concat(‘Welcome’,’to’,’the’,’techieclues’,’website’)

Output:

Welcome to the techieclues website

29. What is the difference between delete, drop, and truncate?

TRUNCATE

It removes all rows from a table.
It does not require a WHERE clause.
Truncate cannot be used with indexed views.
It is performance-wise faster.

DELETE

It removes Some or All rows from a table.
A WHERE clause is used to remove particular rows according to the matched condition. All rows will be deleted when we do not use the Where condition in Query.
It removes rows one at a time.
It can be used with indexed views.

DROP

It removes a table from the database.
All table rows, indexes, and privileges will also be removed when we use this command.
The operation cannot be rolled back.

30. Write a query to fetch duplicate records from a table using MySQL.

SELECT EmpId, Project, Salary, COUNT(*) FROM EmployeeSalary GROUP BY EmpId, Project, Salary HAVING COUNT(*) > 1;

31. How do I import the database through the command-line client?

MySQL -u [username] -p [database_name] < [database_dump_file].sql

Where username: Any MySQL user. For example, root,

Database_name: Name of the database in MySQL. This should exist,

database_dump_file: This is the dump file that has all the data, the structure of tables, etc for the database.

32. What are TRIGGERS and how it can be used in MySQL?

In Mysql, a trigger is a database object that is directly associated with a table. It will be activated when a defined action is executed for the table. It can be performed when you run one of the following MySQL like INSERT, UPDATE and DELETE occurred in a table. Its activation time can be BEFORE or AFTER

33. What is the difference between NOW and CURRENT_DATE in MySQL?

NOW	CURRENT_DATE
NOW() give you the current date-time in the format 'YYYY-MM_DD HH:MM: SS'	CURRENT_DATE() will only give you the current date in format "YYYY-MM_DD"

34. What is a regex for searching for a string with exactly one character between n and t?

Select regexp_like('NOT','n.t');

35. What is the difference between BETWEEN and IN operators in Mysql?

Between operator is used to select a range of data between two values. It can be texts, numbers, and dates, etc.

Syntax:

Select * from TABLENAME where FIELDNAME between VALUE1 and VALUE2

IN operator is used to check for a value in the given set of values.

Syntax:

Select * from TABLENAME where FIELDNAME IN ('VALUE1','VALUE2',...)

36. What are aggregate functions in MySQL?

The MySQL aggregate function performs a calculation on a set of values, and it returns a single value as output. It ignores NULL values when it performs calculations except for the COUNT function.

MySQL provides many aggregate functions that are listed below.

Sum
Count
Max
Min
Group_concat
First
Last

37. What does a group_concat() do?

The Group_concat function virtually groups and combines multiple rows into a single row. For example, the below Query to get all the actors for documentary movies.

SELECT F.TITLE,F.DESCRIPTION,F.LENGTH,F.RATING,GROUP_CONCAT(CONCAT(A.FIRST_NAME,A.LAST_NAME) SEPARATOR ', ') AS `ACTORS`
FROM FILM_ACTOR FA, FILM F ,ACTOR A
WHERE F.FILM_ID = FA.FILM_ID AND A.ACTOR_ID = FA.ACTOR_ID
AND F.FILM_ID IN (
   SELECT FILM_ID FROM FILM_CATEGORY, CATEGORY
   WHERE CATEGORY.CATEGORY_ID = FILM_CATEGORY.CATEGORY_ID
   AND CATEGORY.CATEGORY_ID = 6)
GROUP BY F.FILM_ID;

38. What is the difference between function and procedure in MySQL?

Function	Procedure
The function returns a single value which is mandatory.	The procedure returns zero or N values.
They only work with a statement: Select	They can work with multiple statements: Insert, Update, Delete, Select.
In function, transactions are not possible	In the procedure, transactions are possible
Error handling is not possible	With try-catch, error handling is possible.

39. What do we do if we want to join two tables and allow duplicates too?

We can use a union all operator,

Select <column-names> from <table1>
UNION ALL
Select <column-names> from <table2>

40. How does COALESCE work?

The COALESCE function is sort of like an either-or. In COALESCE, we give three arguments. IF the First Argument is NULL, then MySQL checks the second argument and returns it if not null. If the second argument is also null, then the third value is returned.

Select first_name ,rating, salary, COALESCE(rating,salary,'NA')
From employees;

41. Name a few Control flow in-built functions in MySQL?

Function	Description
`CASE <value> WHEN <value1> THEN <result -value>`	Return the corresponding result in the THEN branch if the condition in the WHEN branch is satisfied, otherwise, return the result in the ELSE branch.
`IF(<condition>,<true-condition>,<false-condition>)`	If checks the condition is given. If true then true-expression is executed else the false condition is executed.
`IFNULL`	It is similar to If but it checks the IF NULL condition specifically.
`NULLIF`	It checks the equality between two arguments and returns NULL if true and returns the first argument if False.

42. How to get all indexes on a table?

We can use the below query to get all indexes on a table.

SHOW INDEXES FROM <table_name>;

43. What are Invisible Indexes? Can a Primary Key be an invisible index?

An index on a column can be visible or invisible. To create an invisible index, we use the same create index syntax with the word Invisible added. Primary Key indexes cannot be Invisible, MySQL does not allow it. Invisible indexes make it possible to test the effect of removing an index on query performance, without making a destructive change that must be undone should the index turn out to be required.

44. How to find the second-highest salary in MySQL?

MySQL uses the LIMIT keyword, which can be used to limit the result set. It will allow us to get the first few rows, the last few rows, or a range of rows. It can also be used to find the second, third, or nth highest salary. It ensures that you have to use order by clause to sort the result set first and then print the output that provides accurate results. The following query is used to get the second-highest salary in MySQL:

SELECT salary   
FROM (SELECT salary FROM employees ORDER BY salary DESC LIMIT 2) AS Emp ORDER BY salary LIMIT 1;

There are some other ways to find the second-highest salary in MySQL, which are given below:

This statement uses subquery and IN clause to get the second highest salary:

SELECT MAX(salary)   
FROM employees   
WHERE salary NOT IN ( SELECT Max(salary) FROM employees);

This query uses subquery and < operator to return the second highest salary:

SELECT MAX(salary) From employees   
WHERE salary < ( SELECT Max(salary) FROM employees);

45. What is the difference between NOW() and CURRENT_DATE()?

NOW() command is used to show the current year, month, and date with hours, minutes, and seconds while CURRENT_DATE() shows the current year with month and date only.

46. How do we Pivot a table in MySQL?

Pivot is not supported by MySQL directly but we can emulate it by using the CASE statement.

An example from the Sakila database is,

select title, description,rental_rate,
	CASE rating
	WHEN 'G' THEN 'ALL'
	WHEN 'PG' THEN 'Parental Guidance'
	WHEN 'PG-13' THEN 'TEEN Movies'
	WHEN 'R' THEN 'ADULTS'
	WHEN 'NC-17' THEN 'ADULTS'
	END AS 'Audience'
From film;

46. What is MySQL Injection?

MySQL injection is a security problem in MySQL. It is one of the most common hacking techniques for any database.

An SQL injection happens when we are trying to receive some sort of user input from the user interface, say for example a username, and instead of the username, the user sends a SQL statement. If we execute this SQL statement into our database without the proper checks, it can destroy the database.

Example of SQL injections:

MySQL Injection based on 1=1
MySQL injection based on OR “”=””

47. What is a Cursor?

A Cursor is a data structure using which we can traverse over records in a table. It is like a pointer we use to traverse a list of records in a single direction.

48. Explain BLOB and TEXT in MySQL.

BLOB:

BLOB stands for Binary Large Object. It holds variable amounts of data. The MAX amount of data a BLOB can hold is 65,535 bytes. The types of BLOBs are:

TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB

TEXT:

TEXT stores string values and can hold up to a maximum of 65,535 characters. The types are:

TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT

49. What is meant by a decimal (5,2)?

A decimal data type is used in MySQL to store the fractional data.

The decimal (5,2) means that the total length of the fractional value is 5. The field can contain 3 digits before the decimal point and 2 digits after the decimal point. If a user adds any value larger than the defined length then it will insert 999.99 in the field.

The use of this data type is explained in the following example.

50. What is the function of mysqldump?

MySQL provides a dump facility (mysqldump) to export and import databases. It is a useful utility tool that is used to dump one or more or all databases from the server for backup or transfer to another database server.