Top 100 MySQL Query Interview Questions and Answers 2023

10. How do you add a column to a table in MySQL?

To add a column to a table in MySQL, use the ALTER TABLE command followed by the name of the table and the new column definition.

11. What is a foreign key in MySQL?

A foreign key is a field in one table that refers to the primary key of another table. It is used to establish a relationship between two tables.

12. How do you create a new MySQL table?

CREATE TABLE table_name (column1 datatype1, column2 datatype2, …);

13. What is a stored procedure in MySQL?

A stored procedure is a series of SQL statements that are kept in the database and can be run as a single entity.

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

15. How do you create a trigger in MySQL?

To construct a trigger in MySQL, use the CREATE TRIGGER command followed by the name of the trigger, the table it applies to, and the SQL statements that define it.

16. What is a cursor in MySQL?

A cursor in MySQL is a technique for iterating over the rows in a result set. It allows you to process each row in turn and perform operations on it.

17. How do you create a cursor in MySQL?

To create a cursor in MySQL, use the DECLARE CURSOR command followed by the name of the cursor and the SELECT statement that defines the result set.

Looking for a Data Scientist Job? Explore Here!

18. What is a transaction in MySQL?

The answer is that in MySQL, a transaction is a collection of SQL statements that are carried out as a single operation. It enables you to ensure that a series of changes to the database are all executed successfully or rolled back if any one of them fails. Moreover, it lets you to roll back individual changes independently.

19. What is normalization in MySQL?

Normalization in MySQL is the process of organizing data in a database to reduce redundancy and dependency

In MySQL, a DELETE statement removes rows from a table based on a condition, whereas a TRUNCATE statement removes all rows.

41. What is the difference between a UNION statement and a UNION ALL statement in MySQL?

In MySQL, a UNION statement combines the results of two or more SELECT queries into a single result set, removing duplicates. A UNION ALL statement also combines the results of two or more SELECT statements, but duplicates are not removed.

42. What is the difference between a primary key and a unique key in MySQL?

In MySQL, a primary key is a column or set of columns that identifies each row in a table in a way that is completely unique. In MySQL, a unique key is a column or set of columns that does not permit duplicate values to be stored in them. However, a unique key does not necessarily identify each row in a completely unique manner.

43. What is the difference between a CHAR data type and a VARCHAR data type in MySQL?

A CHAR data type in MySQL is a string of fixed length, while a VARCHAR data type is a string of variable length.

44. What is the difference between a FLOAT data type and a DOUBLE data type in MySQL?

In MySQL, the FLOAT data type represents a single-precision floating-point number, whereas the DOUBLE data type represents a double-precision floating-point number.

45. What is the difference between a TIMESTAMP data type and a DATETIME data type in MySQL?

In MySQL, the TIMESTAMP data type stores the number of seconds since the Unix epoch (January 1, 1970), whereas the DATETIME data type stores a date and time in a more human-readable format.

46. What is a database index?

An index in MySQL is a data structure that improves the speed of data retrieval operations on a table.

47. What are the benefits of database indexing?

The benefits of database indexing in MySQL include:

• Faster data retrieval: Indexing can greatly speed up data retrieval operations, particularly for large tables.
• Improved query performance: Queries that use indexed columns as filters can be executed much more quickly than those that do not.
• Reduced disk I/O: Indexing can reduce the amount of disk I/O required to retrieve data, which can improve overall system performance.
• Enhanced concurrency: Indexing can improve concurrency by lowering the amount of time that database locks are held during data retrieval processes.

48. What is denormalization in MySQL?

Denormalization is the technique of purposely injecting redundancy into a database in order to increase efficiency. This is generally done by adding unnecessary columns to a database or by duplicating data in different tables.

49. What is a self-join in MySQL?

A self-join in MySQL is a join that is executed on a single table. This is done by establishing two or more aliases for the same table and linking them together using different columns.

Get hands-on with our data science and machine learning course – sign up for a free demo!

50. What is the difference between a left join and a right join in MySQL?

In MySQL, a left join returns all rows from the left table along with rows that match from the right table. A right join brings back all of the rows from the right table as well as the rows that match from the left table.

51. What is the difference between a correlated subquery and a non-correlated subquery in MySQL?

In MySQL, a correlated subquery is a subquery that makes use of columns from the parent query. A non-correlated subquery does not make use of the outer query’s columns.

52. What is a compound key in MySQL?

A compound key in MySQL is a key that is made up of two or more columns. The combination of columns must be unique.

53. What is a temporary table in MySQL?

In MySQL, a temporary table is a table that is generated and used only once for a single query or session. After a session or query ends, temporary tables are automatically deleted.

54. How do you delete data from a MySQL table?

DELETE FROM table_name WHERE condition;

55. What is the difference between an inner join and an outer join in MySQL?

An inner join in MySQL returns only rows with matching values between the two tables. An outside join returns all rows from both tables, with null values for columns that have no matching value.

56. What is a full outer join in MySQL?

With MySQL, performing a full outer join will return all rows from both tables, and it will include null values for any columns that do not have a matching value.

57. What is a cross join in MySQL?

The Cartesian product of the two tables that are being joined is what is returned when doing a cross join in MySQL. What this indicates is that each row in the first table is connected with each row in the second table.

58. How do you select all data from a MySQL table?

SELECT * FROM table_name;

59. What is a clustered index in MySQL?

In MySQL, an index that determines the physical order in which data is stored in a table is referred to as a clustered index. Clustered indexes are limited to a single instance per table.

60. What is a non-clustered index in MySQL?

In MySQL, an index is considered to be non-clustered if it does not determine the physical order of the data in the database it is associated with. It is possible for tables to have several non-clustered indexes.

61. How do you select specific columns from a MySQL table?

SELECT column1, column2, … FROM table_name;

62. What are the Numeric Data Types in MySQL?

63. What is the difference between the CHAR and VARCHAR data types in MySQL?

The CHAR data type in MySQL is utilized for the storage of character strings with a predetermined length. Strings of characters with different lengths can be stored using the VARCHAR data type. For inserting or updating data, using VARCHAR normally requires less processing time than CHAR does, but it can use up to twice as much space.

64. How do you select data from a MySQL table based on a condition?

SELECT * FROM table_name WHERE condition;

65. How do you join two MySQL tables?

SELECT * FROM table1 JOIN table2 ON table1.column = table2.column;

66. How do you group data in a MySQL query?

SELECT column1, SUM(column2) FROM table_name GROUP BY column1;

67. What is a foreign key constraint in MySQL?

A foreign key constraint in MySQL is a rule that enforces a relationship between two tables. The foreign key column in one table references the primary key column in another table.

68. What is the difference between a primary key constraint and a unique key constraint in MySQL?

A primary key constraint in MySQL is used to enforce the uniqueness of a column or set of columns, and to identify each row in the table. A unique key constraint is used to enforce the uniqueness of a column or set of columns, but does not necessarily identify each row in the table.

69. How do you order data in a MySQL query?

SELECT * FROM table_name ORDER BY column1 ASC/DESC;

70. How do you limit the number of rows returned in a MySQL query?

SELECT * FROM table_name LIMIT 10;

71. What is the purpose of the EXPLAIN statement in MySQL?

MySQL uses the EXPLAIN statement to evaluate and enhance the efficiency of SQL statements. The statement can be used to find potential performance problems as it provides information on how MySQL processes the statement.

Experience the power of our web development course with a free demo – enroll now!

72. What is a database index in MySQL?

In MySQL, a database index is a type of data structure used to boost the speed of database queries. In order to speed up searches, joins, and other operations, indexes can be established on one or more columns in a table.

73. How do you calculate the average value of a column in a MySQL table?

SELECT AVG(column_name) FROM table_name;

74. How do you count the number of rows in a MySQL table?

SELECT COUNT(*) FROM table_name;

75. What is a natural join in MySQL?

In MySQL, a join that utilizes columns with the same name in both tables is known as a natural join. There is no explicit join condition.

76. What is a self-referential foreign key in MySQL?

In MySQL, a foreign key that makes use of the same table is known as a self-referential foreign key. In a single table, this is frequently used to establish hierarchical relationships.

77. How do you find the maximum value in a column in a MySQL table?

SELECT MAX(column_name) FROM table_name;

78. How do you find the minimum value in a column in a MySQL table?

SELECT MIN(column_name) FROM table_name;

79. What is a right join in MySQL?

All the rows from the right table and the matching rows from the left table are returned by a right join in MySQL. The outcome will have NULL values for those columns if the left table does not contain any matching rows.

80. What is the difference between the MyISAM and InnoDB storage engines in MySQL?

MySQL’s MyISAM storage engine is a straightforward, quick storage engine that works well for read-intensive applications. A more sophisticated storage engine that supports transactions and is appropriate for write-intensive applications is InnoDB.

81. How do you create an index on a MySQL table?

CREATE INDEX index_name ON table_name(column_name);

82. What are the common MySQL functions?

Common MySQL functions are as follows:

The command to return the current date and time as a single value is NOWO.
The CURRDATEO function provides the current date or time.
BEGIN (X, Y): Concatenating two string values into a single output string is possible using this function.
(X, Y) DATEDIFF The ability to compare two dates and determine their differences.

83. What is replication in MySQL?

The act of replicating data from one database to another is known as replication in MySQL. Data can be spread over several servers using this technique for fault tolerance or load balancing.

84. How do you create a stored procedure in MySQL?

CREATE PROCEDURE procedure_name(parameters)
BEGIN
— code goes here
END;

85. How do you create a view in MySQL?

CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;

86. How do you grant privileges to a user in MySQL?

GRANT privilege_name ON database_name.table_name TO ‘username’@’localhost’;

87. Explain the logical architecture of MySQL

Connection management, security, and authentication are a few of the fundamental services provided by MySQL’s first layer that are necessary for client/server applications and servers to function.
Most of the intelligence of the database management system is found in MySQL’s second layer. It contains all of the built-in functions as well as the code for analysis, parsing, query optimization, and caching.
The storage engines of MySQL’s third layer are in charge of storing and obtaining data from the database.

88. What is a view in MySQL?

In MySQL, a view is a fictitious table that is constructed from the output of a SELECT statement. In most SQL statements, views can be used in place of tables to simplify complex queries.

89. What is a trigger in MySQL?

In MySQL, a trigger is a group of procedures that are carried out automatically whenever a predetermined event takes place. Business rules or restrictions relating to data integrity can be enforced via triggers.

90. What are the TRIGGERS that can be used in MySQL tables?

Below are TRIGGERS that are allowed in MySQL:

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
•  AFTER UPDATE
•  BEFORE DELETE
•  AFTER DELETE

91. What is a deadlock in MySQL?

In MySQL, a deadlock occurs when multiple transactions are awaiting one another’s release of resource locks. This can result in a situation where none of the transactions can proceed.

Experience the power of our web development course with a free demo – Enroll Now!

92. What is the difference between the UPDATE and DELETE commands in MySQL?

In MySQL, the UPDATE command is used to change the data that already exists in a table. To delete data from a table, use the DELETE command.

93. What is the difference between the IS NULL and IS NOT NULL operators in MySQL?

In MySQL, the IS NULL operator is used to determine whether a column has a null value. To determine whether a column does not have a null value, use the IS NOT NULL operator.

94. What is the difference between the COUNT(*) and COUNT(column_name) functions in MySQL?

Counting the number of rows in a table in MySQL uses the COUNT(*) function, which works regardless of whether a particular column has a null value. The amount of non-null values in a given column is counted using the COUNT(column name) function.

95. What is an access control list?

Companies create a set of permissions linked to various data items in order to prevent data loss and guarantee secure access. An access control list is the term used to describe this set of permissions (ACL).
The ACL is the cornerstone of the server’s security and can assist users in resolving connection issues. These lists, which MySQL caches, are also known as grant tables. When a user issues a command, MySQL authenticates the user and grants permissions in a specific order.

96. What is the difference between the INNER JOIN and OUTER JOIN clauses in MySQL?

In MySQL, the INNER JOIN clause is used to only return rows with matching values across the two tables being connected. All data from one table and just the matched rows from the other table being connected are returned using the OUTER JOIN clause.

97. What is the difference between a LEFT JOIN and a RIGHT JOIN in MySQL?

Only the matching rows from the joined right table are returned by an LEFT JOIN in MySQL, which returns all the rows from the left table. Just the matching rows from the connected left table are returned by a RIGHT JOIN, which returns all the rows from the right table.

98. What is the difference between a UNION and a UNION ALL operation in MySQL?

In MySQL, the results of two or more SELECT operations are combined into a single result set using the UNION procedure. Similar operations, such as UNION ALL, also include duplicate rows in the result set.

99. What is the difference between a clustered index and a non-clustered index in MySQL?

The actual order of the rows in a table is determined by an index called a clustered index in MySQL. The physical order of the rows in a table is unaffected by a non-clustered index.

100. What is a database constraint in MySQL?

In MySQL, a constraint is a rule that the database upholds to maintain data integrity. To make sure that data is distinct, non-null, or falls inside a certain range of values, constraints might be utilized.