MySQL (DBA) Basic Interview Question with Answer - Part 3
MySQL (DBA) Basic Interview Question with Answer - Part 1
MySQL (DBA) Basic Interview Question with Answer - Part 2
41. What is a stored procedure in
MySQL?
A stored procedure in MySQL is a precompiled
collection of SQL statements that are stored in the database server and can be
called by name from client applications or other stored procedures. Stored
procedures are often used to encapsulate complex logic or to provide a layer of
abstraction between the application and the database.
42. What is a trigger in MySQL?
A trigger in MySQL is a special type of stored
procedure that is automatically executed when a certain event occurs, such as a
row being inserted, updated, or deleted in a table. Triggers are often used to
enforce business rules, to log changes to the database, or to perform complex
calculations or data transformations.
43. What is the purpose of the IN
operator in MySQL?
The IN operator in MySQL is used to specify a list
of values to be matched against a column. It is often used in conjunction with
the WHERE clause to filter rows based on a specific set of values.
44. What is the purpose of the LIKE
operator in MySQL?
The LIKE operator in MySQL is used to search for a
pattern in a column. It is often used in conjunction with the WHERE clause to
filter rows based on a specific pattern, such as a substring or a regular
expression.
45. What is the difference between
the TRUNCATE and DELETE statements in MySQL?
The TRUNCATE and DELETE statements are both used to
remove data from a table in MySQL, but they have different effects. TRUNCATE is
a DDL (Data Definition Language) statement that removes all data from a table
and resets the auto-increment counter to zero, whereas DELETE is a DML (Data
Manipulation Language) statement that removes specific rows from a table.
46. What is the purpose of the MAX
function in MySQL?
The MAX function in MySQL is used to return the
highest value in a column. It is often used in conjunction with the GROUP BY
clause to find the maximum value for each group of rows.
47. What is the purpose of the MIN
function in MySQL?
The MIN function in MySQL is used to return the
lowest value in a column. It is often used in conjunction with the GROUP BY clause
to find the minimum value for each group of rows.
48. What is the purpose of the AVG
function in MySQL?
The AVG function in MySQL is used to return the
average value of a column. It is often used in conjunction with the GROUP BY
clause to find the average value for each group of rows.
49. What is the purpose of the SUM
function in MySQL?
The SUM function in MySQL is used to return the sum
of a column. It is often used in conjunction with the GROUP BY clause to find
the total sum for each group of rows.
50. What is the purpose of the
DISTINCT keyword in MySQL?
The DISTINCT keyword in MySQL is used to return
only unique values from a column. It is often used in conjunction with the
SELECT statement to eliminate duplicate rows from the result set.
51. What is a join in MySQL?
A join in MySQL is used to combine data from two or
more tables based on a related column between them. There are several types of
joins in MySQL, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER
JOIN.
52. 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. A primary key is used to
enforce data integrity and to ensure that no two rows in a table have the same
key value.
53. What is a foreign key in MySQL?
A foreign key in MySQL is a column or a set of
columns that references the primary key of another table. A foreign key is used
to establish a relationship between two tables and to enforce referential
integrity between them.
54. What is normalization in MySQL?
Normalization in MySQL is a process of organizing a
database in such a way that it reduces redundancy and dependency of data. The
aim of normalization is to eliminate duplicate data and to ensure that each
piece of data is stored in only one place.
55. What is denormalization in MySQL?
Denormalization in MySQL is a process of
intentionally adding redundancy to a database in order to improve performance.
The aim of denormalization is to reduce the number of joins needed to retrieve
data, at the expense of increased storage space and potential data
inconsistencies.
56. What is a view in MySQL?
A view in MySQL is a virtual table that is derived
from one or more tables in a database. A view can be used to simplify complex
queries, to provide a layer of abstraction between the application and the database,
or to restrict access to certain columns or rows of a table.
57. What is an index in MySQL?
An index in MySQL is a data structure that is used
to speed up queries on a table. An index is created on one or more columns of a
table and allows the database to quickly find rows that match a certain
condition.
58. What is a cursor in MySQL?
A cursor in MySQL is a database object that is used
to traverse the result set of a query one row at a time. A cursor can be used
to perform complex operations on a row-by-row basis, such as updating or
deleting data based on certain conditions.
59. What is a temporary table in
MySQL?
A temporary table in MySQL is a table that is
created and used for a specific session or transaction, and is automatically
dropped when the session or transaction ends. Temporary tables can be used to
store intermediate results or to perform complex calculations.
60. What is the purpose of the
EXPLAIN statement in MySQL?
The EXPLAIN statement in MySQL is used to show the
execution plan of a query. It can be used to optimize queries by identifying
slow queries, missing indexes, or other performance issues.
Post a Comment