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

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 basic MySQL architecture?

The logical architecture of MySQL is made of ‘connection manager’, ‘query optimizer’, and ‘pluggable engines’.

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

In either Windows or POSIX systems, the named pipes provide ways of inter-process communications to connect different processes running on the same machine. It dispenses with the necessity of using the network stack, and data can be sent without affecting the performance. Servers set up named pipes to listen to requests. The client process needs to know the specific pipe name to send the request.

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

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

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

11. What are the common MySQL functions?

Common MySQL functions are as follows:

• 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!

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

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

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

The syntax for concatenating tables in MySQL:

CONCAT (string 1, string 2, string 3)

15. What is the limit of indexed columns that can be created for a table?

The maximum limit of indexed columns that can be created for any table is 16.

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

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

The syntax for getting the current version of MySQL:

SELECT VERSION ();

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

 

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

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

20. What is the storage engine used for MySQL?

Storage tables are named table types. The data is stored in the files using multiple techniques such as indexing, locking levels, capabilities, and functions.

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

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

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

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

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

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

26. 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”;

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

28. 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 a normal mode.

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

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

31. What are the types of joins in MySQL?

There are four types of joins in MySQL. Inner join returns the rows if there is at least one match in two 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.

32. What are the storage models of OLAP?

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

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

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

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

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

A transaction is a logical unit of work where either all or none of the steps should be performed. ACID is the abbreviation for Atomicity, Consistency, Isolation, and Durability that are properties of any transaction.

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

37. What is the difference between BLOB and TEXT?

BLOBs are binary large objects holding huge data. Four types of BLOBs are TINYBLOB, BLOB, MEDIBLOB, and LONGBLOB. TEXT is a case-sensitive BLOB. Four types of TEXT are TINY TEXT, TEXT, MEDIUMTEXT, and LONG TEXT.

CTA

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

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

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

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

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