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

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

21. What is a trigger in MySQL?

A trigger is a set of instructions that are automatically executed in response to a specific event, such as an INSERT, UPDATE, or DELETE statement being executed on a table. Triggers can be used to enforce business rules, validate data, or perform other tasks that need to be performed automatically.

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

A view is a virtual table that is created by selecting data from one or more existing tables in MySQL. Views are often used to simplify complex queries or to restrict access to sensitive data. The main difference between a view and a table is that a view does not contain any data of its own, but instead displays the data from the underlying tables.

23. What is a stored procedure in MySQL?

A stored procedure is a set of SQL statements that are stored in the database and can be executed by calling the procedure name. Stored procedures can be used to encapsulate complex database logic, improve performance by reducing the number of round-trips to the database, and enforce data validation and security rules.

24. What is a foreign key in MySQL?

A foreign key is a column or set of columns in one table that refers to the primary key of another table in MySQL. Foreign keys are used to establish a relationship between two tables and enforce referential integrity. When a foreign key is defined, MySQL ensures that the values in the foreign key column(s) match the values in the corresponding primary key column(s).

25. What is normalization in MySQL?

Normalization is the process of organizing data in a database in such a way that it minimizes redundancy and improves data integrity. The process involves breaking up a large table into smaller, more specialized tables, and defining relationships between them using foreign keys. Normalization helps to eliminate data inconsistencies and improve database performance.

26. What is a deadlock in MySQL?

A deadlock is a situation where two or more transactions are waiting for each other to release locks on resources that they need to access. Deadlocks can occur when transactions are executed concurrently and can lead to a complete halt in database activity if they are not resolved. MySQL provides mechanisms for detecting and resolving deadlocks automatically.

27. What is a database index in MySQL?

A database index is a data structure that is used to improve the performance of database queries by providing fast access to specific data. Indexes are created on one or more columns in a table and allow the database to quickly locate the rows that match a specific value or set of values. MySQL supports several types of indexes, including B-tree, hash, and full-text indexes.

28. What is the difference between a primary key and a foreign key in MySQL?

A primary key is a column or set of columns in a table that uniquely identifies each row in the table. A foreign key is a column or set of columns in one table that refers to the primary key of another table. The main difference between a primary key and a foreign key is that a primary key is used to uniquely identify rows in a table, while a foreign key is used to establish a relationship between two tables.

29. What is the purpose of the LIMIT clause in MySQL?

The LIMIT clause is used to limit the number of rows that are returned by a SELECT statement in MySQL. It can be used to retrieve a specific range of rows from a table or to limit the total number of rows returned by a query. The LIMIT clause is often used in conjunction with the ORDER BY clause to sort the results before limiting them.

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

CHAR and VARCHAR are both data types used to store character strings in MySQL. The main difference between them is that CHAR is a fixed-length data type, while VARCHAR is a variable-length data type. This means that CHAR columns always have a fixed length, while VARCHAR columns can have a variable length up to a maximum length specified during column creation.

32. What is the purpose of the GROUP BY clause in MySQL?

The GROUP BY clause is used to group rows that have the same values in one or more columns in MySQL. It is often used in conjunction with aggregate functions, such as COUNT, SUM, and AVG, to calculate summary statistics for each group of rows. The GROUP BY clause is also used to eliminate duplicate rows in the result set.

33. What is the purpose of the HAVING clause in MySQL?

The HAVING clause is used to filter the results of a GROUP BY query in MySQL. It is similar to the WHERE clause, but is used to filter groups of rows rather than individual rows. The HAVING clause is often used in conjunction with aggregate functions to filter out groups that do not meet certain criteria.

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

A left join and a right join are both types of outer joins in MySQL that return all rows from one table and matching rows from another table. The main difference between them is the direction of the join. In a left join, all rows from the left table are included in the result set, even if there are no matching rows in the right table. In a right join, all rows from the right table are included in the result set, even if there are no matching rows in the left table.

35. What is the purpose of the IFNULL function in MySQL?

The IFNULL function is used to replace NULL values with a specified value in MySQL. It takes two arguments: the first argument is the value to be checked, and the second argument is the value to be used if the first argument is NULL. The IFNULL function is often used in conjunction with aggregate functions to handle NULL values in the result set.

36. What is the purpose of the CONCAT function in MySQL?

The CONCAT function is used to concatenate two or more strings in MySQL. It takes two or more arguments, which can be column names, literal strings, or a combination of both. The CONCAT function is often used to combine multiple columns into a single column, or to create a custom string for display purposes.

37. What is the difference between a temporary table and a permanent table in MySQL?

A temporary table is a table that is created for the duration of a session in MySQL. Temporary tables are often used to store intermediate results during complex queries or to hold data for a specific task that is not needed after the session ends. A permanent table, on the other hand, is a table that is stored in the database permanently and is accessible to all users.

38. What is the purpose of the UNION operator in MySQL?

The UNION operator is used to combine the results of two or more SELECT statements in MySQL. It returns a single result set that includes all the rows from each SELECT statement, with duplicate rows removed. The UNION operator is often used to combine data from multiple tables or to create custom reports.

40. What is a transaction in MySQL?

A transaction in MySQL is a series of one or more database operations that are performed as a single unit of work. The purpose of a transaction is to ensure data consistency and integrity by either committing all changes or rolling back all changes if an error occurs.

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