Top 50 MySQL Interview Questions and Answers

Table of Contents

MySQL is a relational database management system. A database is a repository of all the data from any application. A relational database means the data is stored in separate tables. Where each table contains a set of rows and columns. In a relational database, there is a logical relationship between the tables.

MySQL is a combination of My and SQL. My is the name of its cofounder Michael Widenius’s daughter. And SQL stands for Structured Query Language. In this blog, we will discuss about 50 MySQL interview questions and answers. Whether you are a beginner or experienced in this field, this article will surely help you.

Top 50 MySQL Interview Questions and Answers

1. What is a cluster in MySQL

Ans. MySQL Cluster is a database cluster that uses commodity/data nodes for accessing, storing, and processing data. The clustering helps to achieve redundancy, availability, and scalability due to its shared-nothing and auto-sharding structure.

2. What are the different data types in MySQL

Ans. MySQL has three main data types as mentioned below,

a. String

b. Numeric

c. Date and Time

3. Explain the DDL, DML DCL, TCL, and DQL commands?

4. What is a binary log in MYSQL?

Ans. A binary log or BinLog contains the record of all the database changes. It can be used for data recovery, monitoring, and also for replication. Binary logs are written in binary forms. And these can only be analyzed using tools such as MySQL Binlog.

5. Difference between primary key and unique key

Ans. The primary key is a unique column value. The primary key never stores a duplicate or null value. Hence it is always unique. A unique key is one or more than one column value. Unique is similar to primary except that a null value is allowed in the unique key.

6. How do you delete data in MySQL in a table?

Ans. MySQL DELETE statement is used to delete records from the table. This deletes the complete row from the table, which cannot be recovered later.

Example: mysql> DELETE FROM Employees WHERE emp_id=107;

7. How many tables are there in MySQL?

Ans. MySQL can have an unlimited no. of tables. However, there is a limit to the number of files that represent tables. On the other hand, individual storage engines have their engine-specific limitations. InnoDB approves till 4 billion tables.

8. How can you view a database in MySQL?

Ans. You can use this command to view the database,

mysql> SHOW DATABASES;

9. How many decimal types are available in the MySQL database?

Ans. There are four decimal types in MySQL, as mentioned below,

Decimal
Numeric
Float
Double

10. What is the difference between FLOAT and DOUBLE?

11. What is the difference between BOLB and TEXT?

Ans. BOLB is Binary Large Objects and is used for storing binary data. At the same time, TEXT is used for storing large numbers of character strings. BOLB usually stores files for example images, videos, and executables.

12. How to add a column in MySQL?

Ans. ALTER TABLE syntax is used to add columns in MySQL

Example: ALTER TABLE table_name
ADD column_name datatype;

13. What is REGEXP in MySQL?

Ans. REGEXP stands for regular expression. This is an operator used to perform search operations for records with data values matching in some pre-defined patterns. It has powerful pattern matching. Which, in addition, helps to implement power search functions for the database. This also provides pattern matching with more flexibility and control by using several metacharacters.

14. How can you delete a column in MySQL?

Ans. DROP COLUMN command is used to delete a column. Because it modifies the structure of a table, it should be used in the ALTER TABLE query.

Example: ALTER TABLE <table_name>

DROP COLUMN <column_name>;

15. What is subquery in MySQL?

Ans. A subquery is a query that is nested into some other SQL query. This is also embedded with UPDATE, SELECT, INSERT, or DELETE statements along with the many operators.

16. What does correlated subquery mean?

Ans. A correlated subquery is a subquery that relies on the outer query. It uses the data from the outer query. The purpose of this is row-by-row processing. For every row in the outer query, each subquery is executed once.

17. What kind of joins are available in MySQL?

Ans. There are four types of joins available in MySQL mentioned below,

Inner Join
Left Join
Right Join
Cross Join

18. What is timestamp?

Ans. MySQL TIMESTAMP function returns a current date and time value against a DateTime or date value expression.

19. How to save images in MySQL?

Ans. Images in MySQL can be stored in BLOB type. Because images are binary data, binary data such as images and multimedia can only be stored in binary large object types.

20. What types of TRIGGERS are available in MySQL?

Ans. A trigger is stored program in MySQL. A trigger is invoked automatically in response to events like INSERT, DELETE, or UPDATE. There are two types of triggers, the first row-level trigger and the second statement-level trigger.

21. What is an Access Control List?

Ans. Access Control List or ACL is a list of Rules. These rules define which users or system processes are denied or granted permission to access the system resource

22. What are the different types of normalization?

Ans. There are three different types of normalization in the database listed below,

1NF – First Normal Form
2NF – Second Normal Form
3NF – Third Normal Form

23. How to create an Index?

Ans. The index is created in MySQL with the CREATE INDEX command.

Example: CREATE INDEX index_name ON table_name (column_name);

24. How can you create user-defined functions?

Ans. MySQL functions help to solve complex calculations and data manipulation easily. There are two types of functions,

· System Defined Functions

· User Defined Functions

CREATE FUNCTION statement is used for creating functions.

25. Is it possible to use MySQL with Linux?

Ans. Yes, you can use the MySQL database with Linux.

26. How many indexed columns can be created in a table?

Ans. Most storage engines allow up to 16 columns. And also 256 bytes of total index length.

27. Which storage engine is used in MySQL?

Ans. InnoDB is the default database engine for MySQL version 5.5 and above. Other engines used for different purposes as listed below,

ISAM
MyISAM
MERGE
MEMORY (HEAP)
ARCHIVE
BDB
CSV
FEDERATED

28. How to take an incremental backup in MySQL?

Ans. MySQLDUMP and the binary log are used to perform incremental backups.

29. Function of mysqldump

Ans. MySQLDUMP is used to dump one or more than one MySQL database for backup /transfer to another SQL server.

30. How can you handle transactions in MySQL?

Ans. Blow mentioned statements are used to handle the transactions,

START TRANSACTION /BEGIN: To start a new transaction.
COMMIT: To commit the current transaction and make the permanent changes.
ROLLBACK: To roll back the current transaction and also to cancel the changes.
SET auto-commit: To enable/disable the default auto-commit mode.

31. What is a foreign key in MySQL?

Ans. A foreign key is a column in one database that refers to the primary key in other tables.

32. How does MySQL support web development?

Ans. The speed of your site and how swiftly you can access the data depends on the MySQL database. Therefore the performance of the website depends on it.

33. What is the covering index in MySQL?

Ans. A covering index provides the data for a query. And this is without accessing the actual table.

34. What are the steps to implementing a full-text search in MySQL?

Ans. You can follow the below steps to implement a full-text search,

Creating full-text index
Performing full-text searches with MATCH() and AGAINST() functions
Natural Language Full-Text Searches
Boolean Full-Text Searches
Query Expansion
Using MySQL ngram Full-Text Parser
Optimizing full-text search performance

35. How can you solve the “the table is full” issue?

Ans. Reasons for this error,

The disk reaches the maximum limit:

Firstly, if you are continuously adding new data and not deleting the old data. The disc will reach its limit. Secondly, if you have too many columns in a table, this can also fill up the space.

The disk is full of large files: Many large files can also fill up the disc.

You can delete some data or increase the size of the disc to resolve these issues.

If you are using the InnoDB engine, you can change the maximum value of the key

innodb_data_file_path=ibdata1:25M:autoextend:max:512M

sudo service mysql stop

sudo service mysql start

In the case of MyISAM, then you can increase the size up to 65,536TB.

36. How MySQL is different from PostgreSQL?

Ans. Here is the list of some differences between MySQL and PostgreSQL,

37. Use of GRANT command in MySQL

Ans. You can use the GRANT command to grant new privileges to a user account.

38. Explain the use of the DELIMITER command in MySQL.

Ans. DELIMITER command is a semicolon, which is used for stored procedures and also to create triggers.

39. How to change a table’s name in MySQL?

Ans. You can change the table’s name using ALTER TABLE and RENAME TABLE syntax.

40. How to check your current MySQL version?

Ans. You can check the current version with the Commands, SELECT VERSION (), CURRENT_DATE,

41. What is the use of VIEW in MySQL

Ans. VIEW shows the view just like a real table. A view has rows and columns. You can use the CREATE VIEW statement to create a view.

42. How to join the tables in MySQL?

Ans. You can join the tables using different JOIN clauses, as mentioned below,

Inner Join

Left Join

Right Join

Cross Join

43. What is the SQL query for finding the maximum, minimum, and average salary of the employees?

SELECT Max(Salary),
Min(Salary),
AVG(Salary)
FROM EmployeeSalary;

44. What is an SQL query to find the employee ID whose salary lies in the range of 50000 and 100000

SELECT Empld, Salary

FROM EmployeeSalary

WHERE Salary BETWEEN 9 = 50000 AND 100000

45. Write an SQL query to fetch the EmpId and FullName of all the employees working under the Manager with id – 453.

SELECT EmpId, FullName

FROM EmployeeDetails

WHERE ManagerId = 453;

46. What is a workbench in MySQL

Ans. A workbench is a tool for database designing. Database administrators, architects, and developers use this tool. Oracle, developed as well as maintained MySQL workbench.

47. Explain the difference between Oracle and MySQL.

Ans. A lot of organizations use Oracle and MySQL relational databases. However, there are some key differences between both databases.

48. What is the purpose of SAVEPOINT in MySQL?

Ans. In MySQL SAVEPOINT is used to save transactions for some time. A transaction can be rolled back till SAVEPOINT to its previous state. Rollback is not possible after the COMMIT statement.

49. Explain the difference between Microsoft SQL and MySQL.

Ans. Below listed are the differences between Microsoft SQL and MySQL Types of relationships in MySQL

50. Types of relationships in MySQL

Ans. In MySQL, three types of database relationships are available mentioned below,

One-to-one

One-to-many

Many-to-many

Henry Harvin SQL Course:

You can acquaint yourself with the concept of SQL and databases. Learn the basics of SQL with Henry Harvin. Join the SQL developer course and learn programming for databases. The program focuses on 100 % practical training. This course will surely help you to prepare for MySQL Interview Questions. Henry Harvin’s courses are designed by subject matter experts in the field. This program comes with 1 year of gold membership. And the plus point is you can attend multiple sessions with multiple trainers.

Conclusion:

There is a growing demand for database developers in the industry. Most of the large enterprises are using MySQL as a database. Hope the above MySQL interview questions and answers will help you to prepare.

FAQs

Q.1 What is the basic qualification for a MySQL developer?

Ans. There are no such criteria. However, the basic knowledge of structured query language (SQL) will surely help. You can get a professional certification in MySQL from a well-known institute like Henry Harvin. As they provide hands-on experience training. This will also help you with the MySQL interview questions and answers.

Q.2 How is MySQL better than other Databases?

Ans. Advantages of using MySQL over other database

Data Protection
Scalability on Demand
High Efficiency
24/7 Uptime
Outstanding Transactional Support
Excellent Workflow Control
Lower Total Ownership Cost

Q.3 How to prepare for a MySQL interview?

Ans. Brush up your coding skills. As well as prepare all the possible MySQL interview questions and answers. And also remember to research the organization before the interview.

Q.4 Can I become a MySQL developer if I know other databases?

Ans. The basic understanding of any database is a plus point. However, you still need to learn syntax, command, and MySQL architecture to become a MySQL developer. A good training and certification course will surely help you to achieve that and prepare you for MySQL interview questions.