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

 

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

61. What is the difference between CHAR and VARCHAR in MySQL?

CHAR is a fixed-length string data type in MySQL, which means that it always stores a fixed number of characters, regardless of whether the actual string is shorter or longer than the specified length. VARCHAR, on the other hand, is a variable-length string data type in MySQL, which means that it can store strings of varying lengths.

62. What is a trigger in MySQL?

A trigger in MySQL is a database object that is used to automatically execute a set of actions in response to certain database events, such as INSERT, UPDATE, or DELETE statements.

63. What is a stored procedure in MySQL?

A stored procedure in MySQL is a precompiled set of SQL statements that is stored in the database and can be executed by calling its name. Stored procedures can be used to encapsulate complex business logic, to improve performance by reducing the number of round-trips between the application and the database, and to enforce security by restricting direct access to database objects.

64. 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 committed or all rolled back if an error occurs. Transactions are used to ensure data integrity and consistency in the database, especially in multi-user environments.

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

A LEFT JOIN in MySQL returns all rows from the left table and the matching rows from the right table, and fills in NULL values for the non-matching rows. A RIGHT JOIN, on the other hand, returns all rows from the right table and the matching rows from the left table, and fills in NULL values for the non-matching rows.

66. What is a subquery in MySQL?

A subquery in MySQL is a query that is nested inside another query, and is used to retrieve data that will be used as a condition for the outer query. Subqueries can be used in WHERE, HAVING, or FROM clauses of a SQL statement.

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

A primary key in MySQL is a column or a set of columns that uniquely identifies each row in a table, and is used as a reference for foreign keys in other tables. A unique key, on the other hand, is a constraint that ensures that each value in a column or a set of columns is unique, but does not necessarily enforce a unique identifier for each row.

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

The IN operator in MySQL is used to compare a value with a list of values returned by a subquery, while the EXISTS operator is used to check if a subquery returns any rows. The main difference between the two is that the EXISTS operator stops evaluating the subquery as soon as it finds the first matching row, while the IN operator evaluates the entire subquery before comparing the values.

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

A clustered index in MySQL is an index that is physically stored in the same order as the data in the table, while a non-clustered index is stored separately from the table data. The main difference between the two is that a clustered index is faster for retrieving large ranges of data, while a non-clustered index is faster for retrieving small ranges of data or for sorting operations.

71. What is a view in MySQL?

A view in MySQL is a virtual table that is based on the result set of a SELECT statement. Views are used to simplify complex queries, to restrict access to sensitive data, and to provide a customized perspective of the data for different users.

72. What is normalization in MySQL?

Normalization in MySQL is the process of organizing data in a database so that it is efficient, accurate, and consistent. It involves dividing large tables into smaller, more manageable tables, and establishing relationships between them to eliminate redundancy and data inconsistencies.

73. What is the difference between the UNION and UNION ALL operators in MySQL?

The UNION operator in MySQL is used to combine the result sets of two or more SELECT statements, and removes any duplicates. The UNION ALL operator, on the other hand, also combines the result sets of two or more SELECT statements, but does not remove duplicates.

74. What is the difference between the LIKE and REGEXP operators in MySQL?

The LIKE operator in MySQL is used to match a string pattern against a column value using wildcard characters, such as % or _. The REGEXP operator, on the other hand, is used to match a string pattern against a column value using regular expressions.

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

A full-text search in MySQL is a feature that allows you to search for words or phrases within text columns in a table. It is optimized for large amounts of text data, and supports advanced search features, such as proximity searching, phrase searching, and Boolean searching.

76. 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 fast access to data in a table. It is created on one or more columns of a table, and stores a sorted copy of the data, which allows the database to search for specific values or ranges of values more efficiently.

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

A function in MySQL is a reusable block of code that returns a single value or a table of values, and can be used in SQL expressions or queries. A stored procedure, on the other hand, is a set of SQL statements that are stored in the database and can be executed by calling its name, but does not necessarily return a value.

78. What is a deadlock in MySQL?

A deadlock in MySQL is a situation where two or more transactions are waiting for each other to release resources, and neither can proceed. It can occur when two transactions are trying to access the same resources in a different order, or when there is a circular dependency between two or more transactions.

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

The LIMIT clause in MySQL is used to limit the number of rows returned by a SELECT statement. It is often used with an ORDER BY clause to return the top or bottom N rows, or with an OFFSET clause to return a subset of rows starting from a specific position.

80. What is the difference between a composite key and a foreign key in MySQL?

A composite key in MySQL is a primary key that is made up of two or more columns, and is used to uniquely identify each row in a table. A foreign key, on the other hand, is a column or a set of columns that references the primary key of another table, and is used to establish a relationship between the two tables.

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