MySQL (DBA) Basic Interview Question with Answer - Part 5

 

MySQL (DBA) Basic Interview Question with Answer - Part 1
MySQL (DBA) Basic Interview Question with Answer - Part 2
MySQL (DBA) Basic Interview Question with Answer - Part 3
MySQL (DBA) Basic Interview Question with Answer - Part 4

81. What is a trigger in MySQL?

A trigger in MySQL is a stored program that is automatically executed in response to a specific event, such as an INSERT, UPDATE, or DELETE operation on a table. Triggers are used to enforce business rules, to update related tables, or to log changes to a table.

82. What is a subquery in MySQL?

A subquery in MySQL is a SELECT statement that is nested inside another SELECT statement, and is used to retrieve data that will be used as part of the main query. Subqueries are used to simplify complex queries, to filter data based on a condition, or to retrieve data from related tables.

83. What is a transaction in MySQL?

A transaction in MySQL is a set of SQL statements that are executed as a single, atomic unit, and either succeed or fail as a whole. Transactions are used to ensure data consistency, to prevent data loss, and to enforce the ACID properties of a database.

84. What are the ACID properties of a database?

The ACID properties of a database refer to a set of characteristics that ensure the reliability and consistency of transactions. ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity means that transactions are treated as a single, indivisible unit. Consistency means that transactions ensure the integrity and accuracy of data. Isolation means that transactions are executed independently of each other. Durability means that transactions are permanently saved and cannot be undone.

85. What is a primary key in MySQL?

A primary key in MySQL is a column or a set of columns that uniquely identifies each row in a table. It is used to enforce data integrity, to ensure data uniqueness, and to provide a fast way to access data in a table. Primary keys are typically created on columns that contain unique or non-null values.

86. What is a foreign key constraint in MySQL?

A foreign key constraint in MySQL is a relationship between two tables that is based on a column or a set of columns in one table that references the primary key of another table. Foreign key constraints are used to enforce data integrity, to maintain referential integrity between related tables, and to ensure that data is consistent and accurate.

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

A left join in MySQL returns all the rows from the left table and the matching rows from the right table. If there is no match, the result will contain NULL values for the columns from the right table. A right join, on the other hand, returns all the rows from the right table and the matching rows from the left table. If there is no match, the result will contain NULL values for the columns from the left table.

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

The CHAR and VARCHAR data types in MySQL are used to store character strings. The main difference between them is that CHAR has a fixed length, while VARCHAR has a variable length. CHAR is typically used for columns with a fixed length, such as postal codes or phone numbers, while VARCHAR is used for columns with variable lengths, such as names or addresses.

89. What is the difference between the GROUP BY and HAVING clauses in MySQL?

The GROUP BY clause in MySQL is used to group the result set by one or more columns, and to perform aggregate functions on the groups, such as COUNT, SUM, AVG, or MAX. The HAVING clause, on the other hand, is used to filter the groups based on a condition, such as HAVING COUNT(*) > 10.

91. What is the difference between the IN and EXISTS operators in MySQL?

The IN operator in MySQL is used to test whether a value exists in a list of values or a subquery. The EXISTS operator, on the other hand, is used to test whether a subquery returns any rows. While both operators can be used to achieve similar results, the EXISTS operator is typically faster and more efficient for large data sets.

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

In MySQL, a clustered index is an index that determines the physical order of data in a table. It is created on the primary key column of a table, and is used to sort and group data based on that column. A non-clustered index, on the other hand, is an index that does not affect the physical order of data in a table, and can be created on any column or combination of columns.

93. What is the difference between a view and a table in MySQL?

In MySQL, a view is a virtual table that is based on the result set of a SELECT statement, and can be used as a substitute for a table in queries. Views are used to simplify complex queries, to restrict access to sensitive data, or to provide a customized view of data to different users. A table, on the other hand, is a physical storage structure that contains data, and can be modified through INSERT, UPDATE, or DELETE operations.

94. What is normalization in MySQL?

Normalization in MySQL is the process of organizing data in a database in such a way that it reduces redundancy, improves data integrity, and avoids anomalies. Normalization involves breaking down a large table into smaller, more manageable tables, and creating relationships between them based on common attributes. There are several levels of normalization, each with a set of rules that must be followed to ensure data consistency and accuracy.

95. What is a cursor in MySQL?

A cursor in MySQL is a database object that allows you to traverse the rows of a result set one at a time. Cursors are typically used in stored procedures or triggers to perform complex operations on a large set of data, and provide a way to navigate the data in a controlled and efficient manner.

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

A left outer join in MySQL returns all the rows from the left table and the matching rows from the right table. If there is no match, the result will contain NULL values for the columns from the right table. A right outer join, on the other hand, returns all the rows from the right table and the matching rows from the left table. If there is no match, the result will contain NULL values for the columns from the left table.

97. What is a database index in MySQL?

A database index in MySQL is a data structure that is used to improve the performance of queries by providing a fast way to access data in a table. Indexes are created on one or more columns in a table, and are used to sort, group, or filter data based on those columns. Indexes can significantly improve the speed of queries, but can also increase the size of the database and the time required to update data.

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

In MySQL, a primary key is a column or a set of columns that uniquely identifies each row in a table, and is used to enforce data integrity and ensure data uniqueness. A unique key, on the other hand, is a column or a set of columns that also ensures data uniqueness, but does not necessarily enforce data integrity.

100. What is a subquery in MySQL?

A subquery in MySQL is a query that is nested within another query, and is used to retrieve data from one or more tables based on certain conditions. Subqueries can be used in a variety of contexts, such as SELECT, INSERT, UPDATE, and DELETE statements, and can be used to filter, sort, or group data based on specific criteria.

MySQL (DBA) Basic Interview Question with Answer - Part 6