Top MySQL Interview Questions and Answers

CTA

Today, large volumes of data are generated in companies on a daily basis. This data plays a very significant role in company growth. Storing the data hence becomes extremely important for business use, and MySQL offers a platform for this purpose. It opens doors to numerous job opportunities in the field of database administration and management. With the help of these MySQL interview questions, you can crack your job interview easily. Here is a glance at some of the commonly asked questions in MySQL and other related domains:

Frequently Asked MySQL Interview Questions

Q1. What is SQL Server?
Q2. Compare MySQL vs SQL Server.
Q3. How and why use SQL Server?
Q4. What are the features of MySQL?
Q5. What is the Traditional Network Library for a system?
Q6. What is the default port for MySQL Server?
Q7. What do DDL, DML, and DCL stand for?
Q8. What is a join in MySQL?
Q9. What are the common MySQL functions?
Q10. What is the difference between CHAR and VARCHAR?

These MySQL interview questions can be classified majorly into the following three categories:

1. Basic MySQL Interview Questions for Freshers

2. Intermediate MySQL Interview Questions

3. Advanced MySQL Interview Questions for Experienced

Watch this video on MySQL Interview Questions and Answers:

Basic MySQL Interview Questions and Answers for Freshers

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. Compare MySQL vs SQL Server.

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

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

Also, check out the blog on differences between MySQL and PostgreSQL.

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

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

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

Learn all about SQL through this SQL Certification Course!

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

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

Master SQL concepts in depth from this SQL Tutorial now!

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

Check out Intellipaat’s blog to get a fair understanding of SQL Optimization Techniques!

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

Intermediate MySQL Interview Questions and Answers

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

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

The syntax for concatenating tables in MySQL:

CONCAT (string 1, string 2, string 3)

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

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

CTA

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

The syntax for getting the current version of MySQL:

SELECT VERSION ();

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

 

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

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

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

Check out our comprehensive MySQL tutorial now and master the most widely used open-source relational database management system.

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

 

Want to get certified in SQL? Go through our blog on SQL Server Certification and be informed!

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

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

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

Thinking of opting for one of the Database Courses? Intellipaat is the right choice for you!

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

25. 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:

SELECT * 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:

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

26. 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’.

CTA

Watch this SQL Tutorial for Beginners video:

Advanced MySQL Interview Questions and Answers for Experienced

27. How can you change the root password if it is lost?

In such cases when the password is lost, the user should start the DB with skip-grants-table and then change the password. Thereafter, with the new password, the user should restart the DB in normal mode.

Queries to be used:
a) To start DB with skip-grant-table: –skip-grant-tables
b) Connect to the MySQL server as the root user: mysql -u root
c) Use the following command to change the root password: UPDATE mysql.user SET authentication_string=PASSWORD(‘new_password’) WHERE User=’root’;
d) Flush the privileges: FLUSH PRIVILEGES;
e) Exit MySQL: Exit;
f) Restart MySQL in normal DB mode.

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

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

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

31. What are the storage models of OLAP?

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

32. How to define the testing of network layers in MySQL?

For this, it is necessary to review the layered architecture and determine hardware and software configuration dependencies with respect to the application put to test.

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

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

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

35. How can one restart SQL Server in the single user or the minimal configuration modes?

The command line SQLSERVER.EXE used with ‘–m’ will restart SQL Server in the single-user mode and the same with ‘–f’ will restart it in the minimal configuration mode.

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

CTA

37. Can you use MySQL with Linux operating system?

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

etc/init.d/mysqlstart

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

39. 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:

mysqldump [options] db_name [tables]

For multiple databases:

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

For all databases:

mysqldump [options] –all-databases

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

If you have any doubts or queries related to SQL, get them clarified from SQL experts on our SQL Community!

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