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

 

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
MySQL (DBA) Basic Interview Question with Answer - Part 5

101. What is a trigger in MySQL?

A trigger in MySQL is a set of instructions that are automatically executed by the database when certain events occur, such as INSERT, UPDATE, or DELETE operations on a table. Triggers can be used to enforce business rules, perform complex calculations, or perform other operations on the data.

102. What is a foreign key in MySQL?

A foreign key in MySQL is a column or a set of columns in a table that references the primary key of another table. Foreign keys are used to enforce referential integrity between tables, and ensure that data remains consistent and accurate. When a foreign key is defined in a table, it creates a relationship between the two tables, and ensures that any data modifications are propagated to the related tables.

103. What is a transaction in MySQL?

A transaction in MySQL is a set of SQL statements that are executed as a single unit of work, and are either all completed or all rolled back in the event of an error. Transactions are used to ensure data consistency and integrity, and provide a way to group multiple SQL statements into a single logical operation.

104. What is a stored procedure in MySQL?

A stored procedure in MySQL is a set of SQL statements that are stored in the database and can be executed as a single unit of work. Stored procedures are typically used to perform complex operations on a database, such as updating multiple tables or performing calculations on large data sets. They can be called from within other SQL statements, or from within an application.

105. What is a stored function in MySQL?

A stored function in MySQL is a type of stored procedure that returns a single value. Stored functions can be used in SQL statements or in other stored procedures, and can be used to perform calculations or other operations on data.

106. What is the difference between a function and a procedure in MySQL?

In MySQL, a function is a stored program that returns a single value, while a procedure is a stored program that does not return a value. Functions are typically used to perform calculations or other operations on data, while procedures are used to perform complex operations on a database, such as updating multiple tables or performing calculations on large data sets.

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

In MySQL, the CHAR data type is a fixed-length string that can contain up to 255 characters, while the VARCHAR data type is a variable-length string that can contain up to 65,535 characters. CHAR columns are typically used for columns that have a fixed length, such as telephone numbers or postal codes, while VARCHAR columns are used for columns that have a variable length, such as email addresses or comments.

108. What is a UNION statement in MySQL?

A UNION statement in MySQL is used to combine the result sets of two or more SELECT statements into a single result set. The UNION statement removes duplicate rows from the result set, and orders the result set based on the order of the columns in the first SELECT statement.

110. What is a view in MySQL?

A view in MySQL is a virtual table that is based on the result of a SELECT statement. Views are used to simplify complex queries, provide security, and improve performance by precomputing complex queries. Views can be queried like a table, but do not store any data themselves.

111. What is normalization in MySQL?

Normalization in MySQL is the process of organizing data in a database to reduce redundancy and improve data consistency. There are different levels of normalization, called normal forms, with each level addressing a specific type of data redundancy. The most common normal forms are first normal form (1NF), second normal form (2NF), and third normal form (3NF).

112. What is denormalization in MySQL?

Denormalization in MySQL is the process of intentionally introducing redundancy into a database to improve query performance. This is often done by adding redundant data to a table that is frequently queried, so that the query can be executed more quickly. However, denormalization can also make it more difficult to maintain data consistency and integrity.

113. What is a primary key in MySQL?

A primary key in MySQL is a column or a set of columns in a table that uniquely identifies each row in the table. A primary key is used to enforce data integrity and ensure that each row in the table is unique. Primary keys are also used as the basis for defining relationships between tables using foreign keys.

114. What is an index in MySQL?

An index in MySQL is a data structure that is used to improve the performance of SELECT, UPDATE, and DELETE statements on a table. Indexes are created on one or more columns in a table, and are used to quickly locate rows that meet specific criteria. MySQL supports different types of indexes, such as B-tree indexes, hash indexes, and full-text indexes.

115. What is a full-text search in MySQL?

A full-text search in MySQL is a type of search that allows users to search for words or phrases in a text column in a table. Full-text searches use a special type of index called a full-text index, which allows for fast searching of large amounts of text data. Full-text searches can be used to find specific words or phrases, or to perform complex searches using Boolean operators.

116. What is a stored view in MySQL?

A stored view in MySQL is a view that is stored as a table in the database. Stored views are used to improve performance by precomputing complex queries and reducing the amount of data that needs to be retrieved from the database. Stored views are created using the CREATE VIEW statement, and can be queried like a table.

117. What is a temporary table in MySQL?

A temporary table in MySQL is a table that is created for a specific session or connection, and is deleted when the session or connection is closed. Temporary tables can be used to store intermediate results, or to perform complex calculations on data. Temporary tables are created using the CREATE TEMPORARY TABLE statement.

118. What is a binary data type in MySQL?

A binary data type in MySQL is a data type that is used to store binary data, such as images, videos, or sound files. Binary data types are stored as a sequence of bytes, and can be up to 65,535 bytes in size. MySQL supports different types of binary data types, such as BLOB and LONGBLOB.