1.

Triggers

Q1: What is a trigger in MySQL?

• Answer: A trigger is a set of SQL statements that automatically executes when a specified
event occurs on a table. Triggers can be defined to run before or after an INSERT, UPDATE, or
DELETE operation.

Q2: Can you explain the difference between BEFORE and AFTER triggers?

• Answer: BEFORE triggers are executed before the operation (INSERT, UPDATE, DELETE) is
performed on the table. They are commonly used for validation or modifying data before it is
inserted or updated. AFTER triggers execute after the operation and are often used for
logging, auditing, or cascading changes to other tables.

Q3: Give an example of a use case where you would use a trigger.

• Answer: A trigger could be used to automatically update a "last_modified" timestamp

column in a table whenever a record is updated, ensuring that the timestamp always reflects
the last time the record was modified.

2. Views

Q1: What is a view in SQL?

• Answer: A view is a virtual table in SQL that is based on the result set of a query. Views can
be used to simplify complex queries, enforce security, and present data in a specific format
without altering the underlying tables.

Q2: Can a view be updated?

• Answer: Some views can be updated, depending on the complexity of the query that defines
the view. A view is updatable if it meets certain criteria, such as containing no aggregate
functions, DISTINCT, GROUP BY, or JOINs with multiple tables. However, views that include
these elements are generally not updatable.

Q3: What are the advantages of using views?

• Answer: Views provide several advantages, including data security (restricting access to
specific rows or columns), simplification of complex queries, and data abstraction by hiding
the complexity of the underlying table structures.

3. Grouping and HAVING

Q1: What is the purpose of the GROUP BY clause in SQL?

• Answer: The GROUP BY clause groups rows that have the same values in specified columns
into summary rows, like "finding the number of customers in each country." It's often used
with aggregate functions like COUNT, SUM, AVG, etc.

Q2: How is the HAVING clause different from the WHERE clause?

• Answer: The WHERE clause filters rows before the grouping operation takes place, while the
HAVING clause filters groups after the grouping operation. HAVING is often used with GROUP
BY to filter out groups that do not meet certain criteria.
Q3: Write a query to find departments with more than 10 employees.

• Answer:

SELECT department_id, COUNT(*)

FROM employees

GROUP BY department_id

HAVING COUNT(*) > 10;

4. Transactions

Q1: What is a transaction in SQL?

• Answer: A transaction is a sequence of one or more SQL operations treated as a single unit
of work. The operations within a transaction either all succeed (commit) or all fail (rollback),
ensuring data integrity.

Q2: What are the ACID properties of a transaction?

• Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties
ensure that transactions are processed reliably:

o Atomicity: All operations in a transaction succeed or fail as a single unit.

o Consistency: A transaction takes the database from one consistent state to another.

o Isolation: Transactions are isolated from each other until they are completed.

o Durability: Once a transaction is committed, its changes are permanent, even in the
event of a system failure.

Q3: How would you implement a transaction in MySQL?

• Answer:

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT;

5. Indexes

Q1: What is an index in MySQL, and why is it used?

• Answer: An index is a database object that improves the speed of data retrieval operations
on a table by providing a quick way to look up records. Indexes are particularly useful for
speeding up queries that involve WHERE clauses, JOINs, or sorting operations.

Q2: What are the types of indexes in MySQL?

• Answer: Common types of indexes in MySQL include:

o Primary Key Index: Automatically created when a primary key is defined.

 o Unique Index: Ensures that all values in a column are unique.

o Full-Text Index: Used for full-text searches.

o Composite Index: An index on multiple columns.

Q3: What are the downsides of using indexes?

• Answer: While indexes speed up read operations, they can slow down write operations
(INSERT, UPDATE, DELETE) because the index must be updated. Indexes also consume
additional disk space.

6. Database Design

Q1: What are the steps involved in database design?

• Answer: The key steps in database design are:

1. Requirement Analysis.

2. Conceptual Design (ERDs).

3. Logical Design (Schema design).

4. Physical Design (Storage, indexing, optimization).

5. Implementation.

Q2: Explain normalization and why it is important in database design.

• Answer: Normalization is the process of organizing a database to reduce redundancy and

improve data integrity. It involves dividing large tables into smaller, related tables and linking
them through foreign keys. Normalization ensures that the database is free from anomalies
like update, insert, and delete anomalies.

Q3: What is a primary key, and why is it important?

• Answer: A primary key is a unique identifier for a record in a table. It ensures that each
record can be uniquely identified, which is essential for indexing, searching, and establishing
relationships with other tables.

7. Backup and Recovery

Q1: What are the common methods of backing up a MySQL database?

• Answer: Common methods include:

o Logical Backup: Using mysqldump to export the database schema and data.

o Physical Backup: Copying the actual database files (data directory).

o Incremental Backup: Only backing up data that has changed since the last backup.

Q2: How would you restore a database from a backup?

• Answer:

mysql -u username -p database_name < backup.sql

Q3: What is point-in-time recovery?

• Answer: Point-in-time recovery allows you to restore a database to a specific moment in

time, usually by restoring a full backup and then applying transaction logs up to the desired
point in time.

8. Stored Procedures and Functions

Q1: What is the difference between a stored procedure and a function in MySQL?

• Answer: A stored procedure performs a set of operations and does not necessarily return a
value, whereas a function performs a calculation and returns a value. Functions can be used
in SQL statements, while stored procedures are executed using the CALL statement.

Q2: What are the benefits of using stored procedures?

• Answer: Stored procedures provide performance benefits by reducing the amount of data
transferred between the database and application, allow for code reuse, encapsulate
business logic, and improve security by limiting direct access to the underlying data.

Q3: How do you create and execute a stored procedure in MySQL?

• Answer:

DELIMITER //

CREATE PROCEDURE GetEmployee(IN emp_id INT)

BEGIN

SELECT * FROM employees WHERE employee_id = emp_id;

END //

DELIMITER ;

CALL GetEmployee(101);

These questions and answers cover the key concepts and practical aspects of MySQL that are
commonly asked in interviews, helping you to prepare effectively.

Here are additional interview questions and answers for the topics you’re interested in:

1. Triggers

Q4: Can you create a trigger that logs changes in a table?

• Answer: Yes, here's an example of a trigger that logs changes when a record in the
employees table is updated:

CREATE TRIGGER log_employee_updates

AFTER UPDATE ON employees

FOR EACH ROW

BEGIN
 INSERT INTO employee_log (employee_id, old_salary, new_salary, change_date)

VALUES (OLD.employee_id, OLD.salary, NEW.salary, NOW());

END;

Q5: What are some limitations of using triggers?

• Answer: Triggers can introduce complexity and make debugging difficult since they operate
implicitly. They can also impact performance if they involve heavy operations, and they may
cause unexpected behavior if not carefully managed, especially when multiple triggers are
involved.

2. Views

Q4: How can you update data in a view if it includes a join?

• Answer: Generally, updating data in a view that includes a join is not straightforward because
it involves multiple tables. MySQL allows updates on views with joins only if the update can
be mapped directly to an update in a single underlying table, and the other tables involved in
the join do not prevent the update.

Q5: How do you create an updatable view?

• Answer: To create an updatable view, the view should be based on a single table without any
of the following:

• Aggregate functions (e.g., SUM, AVG).

• DISTINCT or GROUP BY clauses.

• Subqueries in the SELECT list.

• UNION or UNION ALL clauses.

Example:

CREATE VIEW employee_view AS

SELECT employee_id, first_name, last_name, department_id

FROM employees;

3. Grouping and HAVING

Q4: What happens if you use the WHERE clause with GROUP BY?

• Answer: The WHERE clause filters records before the grouping operation takes place. The
result is that only rows satisfying the WHERE condition are included in the groups formed by
GROUP BY.

Q5: Write a query to find the average salary for each department but only for departments where
the average salary is above 50000.

• Answer:

SELECT department_id, AVG(salary) AS avg_salary

FROM employees

GROUP BY department_id

HAVING AVG(salary) > 50000;

4. Transactions

Q4: How can you ensure that a series of SQL operations within a transaction are atomic?

• Answer: You ensure atomicity by using START TRANSACTION or BEGIN, followed by the SQL
operations, and then ending with either COMMIT (to save changes) or ROLLBACK (to undo
changes). If any operation fails, a rollback should be triggered.

Q5: What are some common pitfalls of using transactions?

• Answer: Common pitfalls include:

o Long-running transactions can lead to locks on resources, affecting performance.

o Nested transactions are not fully supported in MySQL, making it challenging to

handle multiple transaction layers.

o Improper error handling can result in partial commits if a transaction isn't rolled back
correctly.

5. Indexes

Q4: What is a composite index, and when should you use it?

• Answer: A composite index is an index on two or more columns of a table. It's useful when
queries frequently filter or sort results based on these multiple columns. For example, if a
table is often queried by both first_name and last_name, a composite index on these
columns can improve performance.

Q5: Explain the difference between a clustered index and a non-clustered index.

• Answer: In MySQL, a clustered index is where the table's data is physically stored in the
order of the primary key (or the clustered index). There is only one clustered index per table.
A non-clustered index, on the other hand, stores a pointer to the data rather than the data
itself, and a table can have multiple non-clustered indexes.

6. Database Design

Q4: What is denormalization, and when would you use it?

• Answer: Denormalization is the process of adding redundancy to a database design by

merging tables or adding duplicate data to improve read performance. It's used in scenarios
where complex joins in a normalized database slow down read operations, such as in OLAP
systems.

Q5: Explain the concept of a surrogate key.

• Answer: A surrogate key is an artificial key that is used as a unique identifier for records in a
table. Unlike natural keys, surrogate keys have no business meaning and are usually auto-
incrementing integers or UUIDs. They are used when no suitable natural key exists or when a
simple key is preferred for indexing and performance reasons.
7. Backup and Recovery

Q4: What is the difference between a full backup and an incremental backup?

• Answer: A full backup involves copying the entire database, including all data and schema.
An incremental backup only copies data that has changed since the last backup, making it
faster and requiring less storage space. However, restoring from incremental backups can be
more complex as you need to apply all incremental backups since the last full backup.

Q5: How can you perform a point-in-time recovery in MySQL?

• Answer: To perform a point-in-time recovery:

1. Restore the last full backup.

2. Apply any incremental backups.

3. Replay binary logs up to the desired point in time using the mysqlbinlog utility. Example:

mysqlbinlog --stop-datetime="2024-08-30 12:00:00" mysql-bin.000001 | mysql -u root -p

8. Stored Procedures and Functions

Q4: What are the differences between deterministic and non-deterministic functions in MySQL?

• Answer: Deterministic functions always return the same result for the same input, regardless
of any external factors (e.g., ABS, LOWER). Non-deterministic functions may return different
results even with the same input (e.g., NOW, RAND) because they depend on factors like the
system clock or random number generation.

Q5: How do you handle exceptions in stored procedures in MySQL?

• Answer: In MySQL, exceptions in stored procedures can be handled using the DECLARE ...
HANDLER statement. You can specify a handler for conditions such as SQLEXCEPTION,
SQLWARNING, or a specific error code. Example:

DELIMITER //

CREATE PROCEDURE TransferFunds(

IN from_account INT,

IN to_account INT,

IN amount DECIMAL(10,2))

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

ROLLBACK;

SELECT 'Transaction failed';

END;
 START TRANSACTION;

UPDATE accounts SET balance = balance - amount WHERE account_id = from_account;

UPDATE accounts SET balance = balance + amount WHERE account_id = to_account;

COMMIT;

END //

DELIMITER ;