MySQL Interview Questions

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:

Basic MySQL Interview Questions 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. 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.

Watch this video on MySQL Interview Questions and Answers:

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

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

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

The syntax for getting the current version of MySQL:

SELECT VERSION ();

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

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

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

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

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

11. What are the storage models of OLAP?

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

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

MySQL Intermediate Interview Questions

13. Compare MySQL vs SQL Server.

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

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

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

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

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

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

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

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

The syntax for concatenating tables in MySQL:

CONCAT (string 1, string 2, string 3)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

35. How to create a user-defined function in MySQL?

User-defined functions are created using the ‘CREATE FUNCTION’ syntax. It allows you to define your function that can be used in SQL statements. They come in handy when you perform calculations or operations frequently.

36. What are subqueries in MySQL?

When we write queries inside a query or nested queries are called subqueries. They allow complex operations and can be used in SELECT, INSERT, UPDATE, or DELETE statements. It can return single values, row sets, or tables as well as an output. They are often used for comparison and aggregation.

37. How does MySQL handle transactions?

MySQL handles transactions using START TRANSACTION, COMMIT, and ROLLBACK. Transactions ensure that a series of database operations will either all succeed or fail, maintaining data integrity.

38. How to implement full-text search in MySQL?

Full-text search in MySQL can be implemented using FULLTEXT indexes. It allows natural language searching of text-based columns and is particularly efficient for searching large texts within the VARCHAR and TEXT columns.

39. What is known as the covering index in MySQL?

A covering index is an index that includes all the columns needed to answer a query, allowing the database to retrieve query results directly from the index without accessing the table data.

40. What is the use of MySQL in web development?

MySQL is a cornerstone for web development, offering a reliable and efficient database management system for storing and retrieving data for websites and supporting dynamic content generation using the CRUD (Create, Read, Update, Delete) operations performed in MySQL.

41. How to handle large datasets in MySQL?

Handling large datasets in MySQL efficiently requires partitioning tables, using indexing strategically, optimizing queries, and possibly leveraging MySQL’s advanced storage engines like InnoDB for better performance and reliability.

CTA

Advanced MySQL Interview Questions and Answers For Experienced

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

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

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

DELETE TABLE

1. Syntax and Usage: Typically, you would use DELETE FROM table_name [WHERE condition];. “DELETE TABLE” is not a standard SQL statement.
2. Logged Operation: DELETE is indeed a logged operation, and each row deletion is logged.
3. Where Clause: It allows for conditionally deleting data, i.e., you can specify a WHERE clause to delete specific data.
4. Triggers: DELETE will activate any triggers associated with the table.
5. Speed: It is generally slower than TRUNCATE especially for deleting all rows.
6. Space Reclaim: Space used by the table is not reclaimed (unless using DELETE with no WHERE clause in some MySQL storage engines like InnoDB).

TRUNCATE TABLE

1. Syntax and Usage: The correct syntax is TRUNCATE TABLE table_name;.
2. Logged Operation: TRUNCATE is also a logged operation, but it typically logs fewer transactions because it logs the deallocation of the data pages in which the data exists, not the individual row deletions.
3. Where Clause: It does not allow for a WHERE clause. It will remove all rows.
4. Triggers: TRUNCATE will not activate triggers.
5. Speed: It is usually faster for deleting all rows in a table because it does not log individual row deletions.
6. Space Reclaim: Space used by the table is reclaimed, and the table is reset to its empty state, often times also resetting the auto-increment value to zero (or the starting value).

Rollback

Both DELETE and TRUNCATE operations can be rolled back if used within a transaction that is not yet committed. However, it’s essential to note that TRUNCATE is a data definition language (DDL) statement, and in some database systems, it might auto-commit the transaction, making the rollback impossible for the previous transactions within the same transaction block.

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

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

CTA

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

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

49. How can you optimize MySQL queries for faster data retrieval?

To optimize MySQL queries for faster data retrieval, indexing on columns can be used in WHERE clauses; avoid SELECT *; and refine queries with EXPLAIN to analyze performance. Implementing query caching can also significantly reduce load times for frequently requested data.

50. Explain the GRANT command in MySQL.

The GRANT command is used to give permission or certain privileges to perform various database operations whenever a new user is created.

51. What is the use of the DELIMITER command in MySQL?

The DELIMITER command is used to change the default delimiter used by MySQL, which is a semicolon (;). DELEIMITER is used while writing TRIGGER and STORED PROCEDURES in MySQL.

MySQL Query Interview Questions

52. Write an SQL query to find the second maximum salary.

SELECT MAX(salary) from table_name WHERE salary < (SELECT MAX(salary) from table_name)

53. Write a solution to swap all 'f' and 'm' values (i.e., change all 'f' values to 'm' and vice versa) with a single update statement and no intermediate temporary tables.

UPDATE salary
SET sex=CASE 
WHEN sex='f' THEN 'm'
WHEN sex='m' THEN 'f'
END
WHERE sex IN ('m', 'f')

54. Write a query to find the largest single number. A single number is a number that appears only once in the table.

SELECT MAX(Number) AS LargestSingleNumber
FROM (
    SELECT Number
    FROM numbers
    GROUP BY Number
    HAVING COUNT(Number) = 1
) AS SingleNumbers

55. Write a query to create a table using MySQL.

CREATE TABLE table_name (fname VARCAHR(56), name VARCHAR(56), gender VARCHAR(56), dob DATE)

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

57. Write an SQL query to find the maximum, minimum, and average salary of the employees.

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

58. Write an SQL query to find the employee ID whose salary lies in the range of 60000 and 120000

SELECT EmpId, Salary
FROM EmployeeSalary
WHERE Salary BETWEEN 9 = 60000 AND 120000

59. Write an SQL query to fetch the employees whose names begin with any two characters, are followed by the text “ng” and end with any sequence of characters.

SELECT FullName
FROM EmployeeDetails
WHERE FullName LIKE ‘__ng%’;

60. Write an SQL query to fetch employee names having a salary greater than or equal to 45000 and less than or equal to 89000.

SELECT FullName 
FROM EmployeeDetails 
WHERE EmpId IN 
(SELECT EmpId FROM EmployeeSalary 
WHERE Salary BETWEEN 45000 AND 89000);

61. Write an SQL query to fetch all the employees who are also managers from the EmployeeDetails table.

SELECT DISTINCT e.fullname
FROM Employee e
INNER JOIN Employee m
ON e.EmpID = m.ManagerID

62. Write an SQL query to remove duplicates from a table without using a temporary table.

DELETE e1 FROM Employee e1
INNER JOIN Employee e2 
WHERE e1.EmpId > e2.EmpId 
AND e1.FullName = e2.FullName 
AND e1.ManagerId = e2.ManagerId
AND e1.DOJ = e2.DOJ

63. Write an SQL query to fetch only even rows from the table.

SELECT * FROM EmployeeDetails 
WHERE MOD (EmpId, 2) = 0;

MySQL Database Interview Questions

64. How do you search exactly as you type in MySQL?

To search exactly as typed, add BINARY before your search term. For instance, SELECT * FROM table_name WHERE BINARY column_name = ‘value’; checks for exact matches, including letter cases.

65. Why use the HAVING clause in MySQL?

Use HAVING to filter data after grouping it with GROUP BY. It works like WHERE but applies to groups, not individual rows.

66. How do you see all shortcuts for finding data in a table?

To see all data-finding shortcuts, type SHOW INDEX FROM table_name; It shows shortcuts’ names, types, and which columns they use.

67. How do you find the difference in time between the two dates?

Use TIMESTAMPDIFF to calculate the time gap. For example, SELECT TIMESTAMPDIFF(DAY, ‘2020-01-01’, ‘2020-01-31’); tells you the days between dates.

68. What does a VIEW do in MySQL?

A VIEW acts like a pretend table made from other table data. It makes complex data simple, limits access for safety, and shows parts of data.

69. How do you copy how a table is set up but not its data?

To copy a table’s setup, use CREATE TABLE new_table LIKE original_table;. This copies how the table is set up without the data.

70. What's the difference between CHAR_LENGTH and LENGTH in MySQL?

CHAR_LENGTH counts how many characters are in a string, seeing all as single characters. LENGTH counts how many bytes the string is, which changes with different characters.

71. How do you change a table's name?

Change a table’s name with RENAME TABLE old_table_name TO new_table_name;. This updates the table’s name in your database.

72. When is ENUM used in MySQL?

Use ENUM when a column only accepts specific values. ENUM lets you pick from a list you set when you make the table.

73. How do you check your MySQL version?

To see your MySQL version, run SELECT VERSION();. This shows the version and details of your MySQL server.

74. Why is AUTO_INCREMENT used with primary keys?

AUTO_INCREMENT gives each new row a unique number, making it perfect for primary keys. It makes adding records easy without manually setting the key.

75. How do you save your MySQL database as a file?

To save your database, use mysqldump -u username -p database_name > export_name.sql;. This command makes a .sql file with your database’s structure and data.

MySQL Salary Trends

CTA

MySQL Job Trends

According to the Bureau of Labor Statistics US, there will be growth in the employment of MySQL employment of Database Administrators (DBA) in the US by 9% between 2021 and 2031.

With 6926 open jobs on LinkedIn in the United States alone for jobs related to MySQL, the tally moves to 137000+ for the related roles.

MySQL Roles and Responsibilities

According to a job posted by Newfold Digital on LinkedIn:

Role: Database Administrator

1. Responsibilities:

• Work with different tech stacks like Oracle, MySQL, MS SQL Server, and PostgreSQL.
• Manage databases hosted on internal servers and public cloud platforms like AWS, GCP, Azure, and Oracle Cloud.
• Set up monitoring and health check measures and steps for both production and non-production systems.
• Collaborate with various teams across different time zones to understand the risks and issues.
• Document important technical practices to create a central knowledge base for the teams to refer.

2. Skills Required:

• Hands-on experience in optimizing databases, implementation covering configurations, and  Infrastructure Layout
• Experience working with MySQL, PostgreSQL, Mongo, Cassandra, and similar tools
• Good communication skills. Open to changes and adaptive to a dynamic environment
• It is good to have DevOps experience for automation purposes.

I hope this set of MySQL Interview Questions will help you prepare for your interviews. Best of luck!

Looking to start your career or even elevate your skills in the field of MySQL? You can enroll in our MySQL Training Course or enroll in Intellipaat’s Master’s Program and get certified today.