Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Beginner Level MySQL Interview Questions
2.1.
1. What is MySQL?
2.2.
2. Why is MySQL used?
2.3.
3. What does SQL in MySQL stand for?
2.4.
4. What are MySQL database queries?
2.5.
5. What does a MySQL database contain?
2.6.
6. How do you create a database in MySQL?
2.7.
7. How do you remove a column from a database?
2.8.
8. What are the String datatypes in MySQL?
2.9.
9. What do you mean by databases?
2.10.
10. How can we interact with MySQL?
2.11.
11. What is BLOB in MySQL?
3.
Intermediate Level MySQL Interview Questions
3.1.
12. What are MySQL Triggers?
3.2.
13. How many triggers are possible in MySQL?
3.3.
14. What are the types of relationships used in MySQL?
3.4.
15. Define Joins in MySQL. Mention its types.
3.5.
16. What is the difference between CHAR and VARCHAR?
3.6.
17. What does a TIMESTAMP do on the UPDATE CURRENT_TIMESTAMP data type?
3.7.
18. What are MySQL ”Views”?
3.8.
19. What objects can be created using CREATE statement?
3.9.
20. What is the difference between the LIKE and REGEXP operators?
3.10.
21. How do you create and execute views in MySQL?
3.11.
22. What are all the Common SQL Functions?
4.
Advanced Level MySQL Interview Questions
4.1.
23. What is the difference between mysql_fetch_array and mysql_fetch_object?
4.2.
24. What is Scaling in MySQL?
4.3.
25. What is Sharding ?
4.4.
26. What are the MySQL clients and utilities?
4.5.
27. What are Heap tables?
4.6.
28. Explain Access Control Lists.
4.7.
29. Where will the MyISAM table be stored? Give their storage formats.
4.8.
30. What are the different tables present in MySQL?
4.9.
31. What are Transaction Storage Engines in MySQL?
4.10.
32. Explain the ACID properties in the context of database transactions. 
4.11.
33. What are stored procedures in MySQL, and when would you use them in database design? Provide an example of a stored procedure.
5.
Frequently Asked Questions
5.1.
What is SQL and MySQL Interview Questions?
5.2.
What are the questions asked in MySQL interview?
5.3.
Why to use MySQL?
5.4.
In which language MySQL is written?
6.
Conclusion
Last Updated: Mar 27, 2024
Medium

Top 30+ MySQL Interview Questions (2023)

Author Rinki Deka
0 upvote
gp-icon
Interview guide for product based companies
Free guided path
12 chapters
99+ problems
gp-badge
Earn badges and level up

Introduction

MySQL is a relational database management system in which data is stored structurally in rows and columns, generally called Tables. This database uses SQL(Structured Query Language) to extract, delete or manipulate data in the databases.

mysql interview questions

It is an open-source database that can be used freely by any individual or organization without paying for it and can make modifications in it. In this article, we will discuss 30+ commonly asked MySQL interview questions (2023). So, let's get started.

Beginner Level MySQL Interview Questions

Given below are some beginner-level MySQL interview questions:

1. What is MySQL?

MySQL is a database management system that organizes and manages data in a tabular format and uses structured query language to access, delete or manipulate data.

2. Why is MySQL used?

The reasons for using MySQL database are

  • High availability and security.
     
  • Easy to use.
     
  • Fast and reliable.
     
  • Supports large communities.

3. What does SQL in MySQL stand for?

SQL stands for Structured Query Language; it manipulates data by sending query requests to the MySQL server. One can use the command to send requests from the database.

In this query CustomerName, City is the column name, and Customers is a table name. Whereas SELECT and FROM are the commands in SQL.

Customer Table:

Customer table

Query -

SELECT CustomerName, City FROM Customers;

Output - 

Output

4. What are MySQL database queries?

A query is a way to interact with a database. One can send a query to the database for specific information and get a record out of it based on the condition of the requests.

5. What does a MySQL database contain?

MySQL database contains multiple tables containing rows and columns as a record. The column represents the data fields, and the rows represent a data count in a particular table. 

6. How do you create a database in MySQL?

There are two ways to create a database in MySQL. One can use the commands given below to create a database.

CREATE DATABASE “Colors”;
CREATE DATABASE IF NOT EXISTS “Colors”;


It is better to use the second query; it prevents errors as if any database with the same designation already exists in the MySQL server.

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

You can remove a column from the database by using the DROP keyword. One can use the below command to remove a column.  

DROP DATABASE “database_name”;

8. What are the String datatypes in MySQL?

There are various string datatypes in MySQL.

Data type Meaning
CHAR Fixed-length non-binary string.
VARCHAR Variable length non-binary string.
BINARY Fixed-length binary string.
VARBINARY Variable length binary string.
ENUM Each column is assigned one enumeration number.
TEXT Small non-binary string.

9. What do you mean by databases?

A database is a collection of information in a systematic and organized form that is stored in the system to be accessed and searched quickly.

10. How can we interact with MySQL?

There are three ways to interact with MySQL

  • By using a programming language.
     
  • Using a command line interface.
     
  • Via a web interface.

11. What is BLOB in MySQL?

BLOB stands for a binary large object. It holds a large amount of data, such as documents, images, videos, etc.

There are four types of BLOB:

  • TINYBLOB
     
  • BLOB
     
  • MEDIUMBLOB
     
  • LARGEBLOB
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Intermediate Level MySQL Interview Questions

Given below are some intermediate-level MySQL interview questions:

12. What are MySQL Triggers?

In MySQL, the trigger is a stored program that invokes automatically in response to an event such as insert, delete, or update occurring on a table.

The SQL standard defines two types of triggers such as row-level and statement-level.

  • Row-level trigger activates after affecting each row in a table.
     
  • Statement-level trigger activates after every transaction, regardless of how many rows are affected.

13. How many triggers are possible in MySQL?

There are six triggers used in MySQL

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

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

There are three types of relationships in MySQL.

  • One-to-One - In a one-to-one relationship, a record in one table has a relationship with exactly one record in another.
     
  • One-to-Many - In a one-to-many relationship, a record in one table is related to more than one record in another table.
     
  • Many-to-Many - In many-to-many relationships, many rows in one table are linked with multiple records in another table.

15. Define Joins in MySQL. Mention its types.

A join is a clause in MySQL that combines rows from two or more tables based on a common column between them.

There are different types of joins in MySQL, such as:

  • INNER Join - The INNER join is used to select common records from both tables.
     
  • LEFT Join - The LEFT keyword selects all the records from the left table and the common records from both tables.
     
  • RIGHT Join - The RIGHT keyword selects all the records from the right table and the common records from both tables.
     
  • FULL Join - The FULL Join keyword return all the records when there is a match in left or right table records. 

16. What is the difference between CHAR and VARCHAR?

Some of the differences between CHAR and VARCHAR are given below.

CHAR VARCHAR
CHAR datatype is used to store a string of fixed length.  VARCHAR datatype is used to store a string of variable length.
The CHAR datatype's storage size is equal to n bytes. The storage size in VARCHAR datatype is the equal actual length of the entered string in bytes.
Takes 1 byte for each character.   It takes 1 byte for each character and some extra bytes for holding length information.

17. What does a TIMESTAMP do on the UPDATE CURRENT_TIMESTAMP data type?

The TIMESTAMP column gets updated with zero when the table is created. Whenever a change occurs in the field, the UPDATED CURRENT_TIMESTAMP modifier updates the time to the current time.

18. What are MySQL ”Views”?

MySQL views consist of rows and columns that are returned after the execution of a particular query. We can retrieve the data separately with the help of views by using the alias keyword.

There are some advantages to views.

  • Security
     
  • Maintainability
     
  • Simplicity

19. What objects can be created using CREATE statement?

Below are some objects that can be created using CREATE statement

  • EVENT
     
  • FUNCTION
     
  • DATABASE
     
  • PROCEDURE
     
  • INDEX
     
  • TABLE
     
  • TRIGGER
     
  • VIEW
     
  • USER

20. What is the difference between the LIKE and REGEXP operators?

LIKE and REGEXP operators are used to express with “^” and “%”. One can use the LIKE and REGEXP operators in the following way.

Customer Table:

Customer table
SELECT * FROM Customers WHERE CustomerName LIKE 'a%';

 

Output - 

Output
SELECT * FROM Customer WHERE City REGEXP 'co';

 

Output -

Output

In the above commands, SELECT is used for selecting the data. FROM is used for the table. WHERE is a conditional keyword. LIKE and REGEXP is used with the conditional operator. 

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

We can create MySQL views with CREATE VIEW statement. By following the below command, you can create a view.

Customer Table:

Customer table
CREATE VIEW Mexico_Customers AS SELECT CustomerName, ContactName
FROM Customers WHERE Country = 'Mexico'; 

 

In the above command, CREATE VIEW syntax is used for creating the view by selecting a different table based on a given condition.

Output -

Output

22. What are all the Common SQL Functions?

The common SQL functions are:

  • CONCAT - Combines two string input values to create a single string output. 
     
  • CURRDATE, CURRTIME - Returns the current date and time. 
     
  • FORMAT - Formats the function X to D significant digits.
     
  • SUBTIMES - Determines the difference between two times. 
     
  • DATEDIFF - Determines the difference between two dates.
     
  • NOW - Returns the current date and time as one value.
     
  • HOUR(), MINUTE(), SECOND() – Extracts the given data from a time value.
     
  • FROMDAYS(INT) – Converts an integer number of days into a date value. 
     

Also see, Power Electronics Interview Questions

Advanced Level MySQL Interview Questions

Given below are some advanced-level MySQL interview questions:

23. What is the difference between mysql_fetch_array and mysql_fetch_object?

Below are some differentiating points listed:

 mysql_fetch_array      mysql_fetch_object 
It returns a result row as an associative array and numeric array.  It returns a row of the result set.
It takes one parameter, which is the result.  It takes two parameters: a class name and an array of parameters.
Returns value in an array of strings.  Returns an object with string properties for the accessed row.

Read more, wc command in linux

24. What is Scaling in MySQL?

In MySQL, scaling is the ability of the MySQL server to handle the load. The load here can be seen from different angles, such as

  • Number of users.
     
  • User Activity,
     
  • Size of the related dataset,
     
  • Quantity of data.

25. What is Sharding ?

Sharding refers to breaking up the tables into smaller parts called shards. After breaking up, spread them across multiple servers. The main advantage of sharding is that since shards are generally much smaller, it is very easy to maintain and handle all the tasks much faster.

26. What are the MySQL clients and utilities?

There are several MySQL programs are available to communicate with the server. Some of them are listed here:

  • mysql - This program enables you to send requests to the MySQL server and get the result. It can also be used to execute batch scripts.
     
  • mysqladmin - This program is used for performing the tasks such as shutting down the server, checking its configuration, or monitoring its status if it appears not functioning properly.
     
  • mysqldump- This tool is used for backing up your database by storing its copy on another server.
     
  • mysqlcheck and myisamchk - These programs help you perform table checking, analysis, and optimization and repair them if they 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.

27. What are Heap tables?

Heap tables are present in the memory and used for high-speed storage on a temporary basis. Some of the properties of heap tables are listed here:

  • Auto-increment is not supported by heap tables.
     
  • Indexes should not be null.
     
  • Only comparison operators can be used =, >, <, =>, =<.
     
  • BLOB or TEXT fields are not allowed.

28. Explain Access Control Lists.

An access control list is a permission that is associated with an object. It is a basis for MySQL server’s security model, and it also helps in troubleshooting problems.

MySQL server keeps this list in cached memory. When a user tries to authenticate or run a command, MySQL checks the authentication information and takes permission against the ACLs in the given order.

29. Where will the MyISAM table be stored? Give their storage formats.

MyISAM table is stored on disk. It is stored in three formats.

  • “.frm’” file stores the table definition.
     
  • “.MYD” (MYData) extension stores a data file.
     
  • “.MYI” (MYIndex) extension stores an index file.

30. What are the different tables present in MySQL?

There is a total of five types of tables present in MySQL:

  • Heap
     
  • Merge
     
  • MyISAM
     
  • INNODB
     
  • ISAM

31. What are Transaction Storage Engines in MySQL?

A storage engine is a software module that a database management system uses to create, read, and update data from a database. One must use MySQL’s Inno DB storage engine to use MySQL transaction facility. The default storage engine prior to MySQL version 5.5 is MyISAM.

32. Explain the ACID properties in the context of database transactions. 

ACID stands for Atomicity, Consistency, Isolation, and Durability. MySQL ensures transaction consistency by following these principles. Atomicity ensures that a transaction is treated as a single, indivisible unit. Consistency ensures that a transaction brings the database from one consistent state to another. Isolation ensures that concurrent transactions do not interfere with each other. Durability guarantees that once a transaction is committed, its changes are permanent.

33. What are stored procedures in MySQL, and when would you use them in database design? Provide an example of a stored procedure.

Stored procedures are precompiled SQL statements that can be stored and executed in the database. They are used to encapsulate a series of SQL operations into a single callable routine. For example, here's a simple MySQL stored procedure that retrieves customer information:

DELIMITER //
CREATE PROCEDURE GetCustomerInfo(IN customerId INT)
BEGIN
   SELECT * FROM Customers WHERE CustomerID = customerId;
END //
DELIMITER ;

Frequently Asked Questions

What is SQL and MySQL Interview Questions?

SQL and MySQL interview questions pertain to commonly asked questions and topics during interviews related to the MySQL relational database management system. Before attending an SQL and MySQL interview we should read these questions and their answers.

What are the questions asked in MySQL interview?

MySQL interview questions cover topics such as database design, SQL queries, indexing, normalization, and more to assess a candidate's knowledge of MySQL.

Why to use MySQL?

MySQL is favored for its open-source nature, speed, reliability, and scalability. It's widely used in web applications, data-driven websites, and various software projects.

In which language MySQL is written?

MySQL is primarily coded in C and C++. These languages were chosen for their top-notch performance, system resource control, portability across various operating systems, and ease of integration with other languages and libraries. This combination ensures that MySQL is both high-performing and versatile, making it a popular and powerful database system.

Conclusion

In this article, we have covered the topic of 30+ commonly asked MySQL interview questions (2023). In this, we have discussed all the critical interview questions related to MySQL from beginner to advanced level, which are asked in the interviews. I hope you all found these MySQL interview questions interesting and helpful. 

We hope that this blog has helped you enhance your knowledge regarding MySQL Interview questions, and if you would like to learn more, check out our other articles -

You can also consider our Interview Preparation Course to give your career an edge over others.

Do upvote our blog to help other ninjas grow.

Previous article
Top PL/SQL Interview Questions and Answers (2023)
Next article
Top 40 Oracle DBA Interview Questions & Answers (2023)
Guided path
Free
gridgp-icon
Interview guide for product based companies
12 chapters
123+ Problems
gp-badge
Earn badges and level up
Live masterclass