Interview Q&A

LTER INDEX idx_name ON table_name REORGANIZE;

PostgreSQL:

PostgreSQL does not have an explicit REORGANIZE command, but you can run VACUUM to

clean up the database and reduce fragmentation:

VACUUM INDEX idx_name;

MySQL:

OPTIMIZE TABLE table_name;

• Rebuilding an Index: A more intensive operation where the index is completely dropped

nd recreated. This can reduce fragmentation to nearly zero.

SQL Server:

LTER INDEX idx_name ON table_name REBUILD;

PostgreSQL:

REINDEX INDEX idx_name;

MySQL:

LTER TABLE table_name DROP INDEX idx_name, ADD INDEX idx_name

(column_name);

Reorganizing an Index: A lighter operation that compacts the index and defragments it. It is

used when fragmentation is low (less than 30%).

SQL Server:

ALTER INDEX idx_name ON table_name REORGANIZE;

PostgreSQL:

PostgreSQL does not have an explicit REORGANIZE command, but you can run VACUUM to

clean up the database and reduce fragmentation:

VACUUM INDEX idx_name;

MySQL:

OPTIMIZE TABLE table_name;

Rebuilding an Index: A more intensive operation where the index is completely dropped

and recreated. This can reduce fragmentation to nearly zero.

SQL Server:

ALTER INDEX idx_name ON table_name REBUILD;

PostgreSQL:

REINDEX INDEX idx_name;

MySQL:

ALTER TABLE table_name DROP INDEX idx_name, ADD INDEX idx_name

(column_name);

Answer: For large datasets, query optimization can be crucial to ensure performance is not impacted. Here are several tips:

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

transaction is a sequence of SQL operations that are treated as a single unit of work. A

transaction must either be fully completed (committed) or fully undone (rolled back) to

ensure the integrity of the database. Transactions provide the foundation for the ACID

properties (Atomicity, Consistency, Isolation, Durability) that ensure reliable and consistent

operations.

Answer: transaction are invisible to others until the transaction is committed. Durability: Once a transaction is committed, the changes are permanent, even in the case of a system crash.

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

The ACID properties are critical to ensuring the reliability of transactions in a database:

• Atomicity: All operations within a transaction are executed completely or not at all.
• Consistency: A transaction takes the database from one valid state to another,

ensuring that all rules (constraints, triggers, etc.) are respected.

• Isolation: Transactions are isolated from one another, meaning intermediate steps of

a transaction are invisible to others until the transaction is committed.

• Durability: Once a transaction is committed, the changes are permanent, even in the

case of a system crash.

re saved and visible to other transactions.

• When to use: After the transaction operations have completed successfully and you

want to ensure that the changes are saved to the database.

Example:

BEGIN TRANSACTION;

UPDATE employees SET salary = 5000 WHERE id = 1;

COMMIT;

The COMMIT statement is used to finalize a transaction by making all the changes made

during the transaction permanent. It ensures that all changes made during the transaction

are saved and visible to other transactions.

• When to use: After the transaction operations have completed successfully and you

want to ensure that the changes are saved to the database.

Example:

BEGIN TRANSACTION;

UPDATE employees SET salary = 5000 WHERE id = 1;

COMMIT;

The ROLLBACK statement is used to undo all changes made during a transaction. If an error

occurs or something goes wrong, ROLLBACK ensures that the database is restored to its

state before the transaction began.

• When to use: If any part of the transaction fails or if you wish to cancel the changes

made during the transaction.

Example:

BEGIN TRANSACTION;

UPDATE employees SET salary = 5000 WHERE id = 1;

• - Something goes wrong

ROLLBACK;

SAVEPOINT is a way to set a point within a transaction to which you can later roll back if

necessary. It provides more granular control, allowing partial rollback rather than undoing the

entire transaction.

• When to use: When you want to mark certain stages within a transaction and allow

for partial rollback if an error occurs.

Example:

BEGIN TRANSACTION;

SAVEPOINT sp1;

UPDATE employees SET salary = 5000 WHERE id = 1;

• - Something goes wrong

ROLLBACK TO sp1; -- Rolls back to the savepoint, undoing only the

changes after it

COMMIT;

The isolation level in SQL defines how transactions interact with each other in terms of

visibility of data. The isolation level affects the balance between data consistency and

transaction concurrency.

• READ UNCOMMITTED: Allows dirty reads. One transaction can see uncommitted

changes made by another transaction. This is the lowest level of isolation.

• READ COMMITTED: Prevents dirty reads, but allows non-repeatable reads (data

can change during the transaction).

• REPEATABLE READ: Prevents dirty reads and non-repeatable reads, but phantom

reads (new rows can appear in a query) are still possible.

• SERIALIZABLE: The highest isolation level. Prevents dirty reads, non-repeatable

reads, and phantom reads by making transactions execute sequentially.

Can you explain the different isolation

levels (e.g., READ UNCOMMITTED, READ COMMITTED, REPEATABLE

READ, SERIALIZABLE)?

The isolation level in SQL defines how transactions interact with each other in terms of

visibility of data. The isolation level affects the balance between data consistency and

transaction concurrency.

• READ UNCOMMITTED: Allows dirty reads. One transaction can see uncommitted

changes made by another transaction. This is the lowest level of isolation.

• READ COMMITTED: Prevents dirty reads, but allows non-repeatable reads (data

can change during the transaction).

• REPEATABLE READ: Prevents dirty reads and non-repeatable reads, but phantom

reads (new rows can appear in a query) are still possible.

• SERIALIZABLE: The highest isolation level. Prevents dirty reads, non-repeatable

reads, and phantom reads by making transactions execute sequentially.

ffects concurrency in several ways:

• Locking: Transactions may lock rows or tables to prevent conflicting changes,

leading to possible delays for other transactions.

• Deadlocks: When two or more transactions are waiting for each other to release

locks, causing a cycle of dependency. This can be automatically detected and

resolved by the DBMS.

Transactions impact concurrent operations by introducing locking mechanisms to ensure

that multiple transactions don't interfere with each other and cause inconsistent data. This

affects concurrency in several ways:

• Locking: Transactions may lock rows or tables to prevent conflicting changes,

leading to possible delays for other transactions.

• Deadlocks: When two or more transactions are waiting for each other to release

locks, causing a cycle of dependency. This can be automatically detected and

resolved by the DBMS.

• Implicit Transactions: Automatically begin when a database operation is executed.

The DBMS treats each individual statement as a transaction and automatically

commits after each statement, unless explicitly rolled back.

• Explicit Transactions: Transactions that the user manually controls using BEGIN

TRANSACTION, COMMIT, and ROLLBACK. The user decides when the transaction

begins and ends.

Answer: pplication to reattempt the transaction after a short delay. Optimize Transactions: Keep transactions short and ensure that they acquire locks in the same order to reduce the likelihood of deadlocks.

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

Deadlocks occur when two or more transactions are waiting for each other to release locks,

causing a cycle of dependencies. To handle deadlocks:

• Deadlock Detection: DBMS systems (e.g., SQL Server) can automatically detect

deadlocks and terminate one of the transactions to break the deadlock.

• Retry Logic: If a deadlock is detected, you can implement a retry mechanism in your

application to reattempt the transaction after a short delay.

• Optimize Transactions: Keep transactions short and ensure that they acquire locks

in the same order to reduce the likelihood of deadlocks.

In stored procedures, you control transaction behavior using the following:

• BEGIN TRANSACTION: Explicitly starts a transaction in the stored procedure.
• COMMIT: Ends the transaction and commits all changes made during the

transaction.

• ROLLBACK: Undoes all changes made during the transaction if something goes

wrong.

• SAVEPOINT: Sets a point in the transaction to which you can roll back.

Example:

BEGIN TRANSACTION;

• - SQL operations here

IF (some_condition)

BEGIN

COMMIT; -- Commit if condition is true

END

ELSE

BEGIN

ROLLBACK; -- Rollback if condition is false

END

Normalization & Database Design

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

dependency by dividing large tables into smaller, manageable tables and defining

relationships between them. This process aims to improve the structure of the database by

minimizing the chances of data anomalies (insertion, update, and deletion anomalies).

ddresses a different kind of redundancy or dependency.

• 1NF (First Normal Form):
• A table is in 1NF if it contains only atomic (indivisible) values and each record

has a unique identifier (Primary Key).

• No repeating groups or arrays are allowed.
• 2NF (Second Normal Form):
• A table is in 2NF if it is in 1NF and all non-key attributes are fully

functionally dependent on the primary key.

• Eliminates partial dependency (when a non-key attribute depends on only

part of a composite primary key).

• 3NF (Third Normal Form):
• A table is in 3NF if it is in 2NF and there are no transitive dependencies

(non-key attributes depending on other non-key attributes).

• This removes dependencies between non-key attributes.
• BCNF (Boyce-Codd Normal Form):
• A table is in BCNF if it is in 3NF and every determinant is a candidate key.
• This is a stricter version of 3NF.
• 4NF (Fourth Normal Form):
• A table is in 4NF if it is in BCNF and multi-valued dependencies are

removed.

• This ensures that a record doesn’t have two or more independent

multi-valued attributes.

• 5NF (Fifth Normal Form):
• A table is in 5NF if it is in 4NF and cannot be decomposed further without

losing data.

Normal forms are guidelines used to organize a relational database schema. Each form

addresses a different kind of redundancy or dependency.

• 1NF (First Normal Form):
• A table is in 1NF if it contains only atomic (indivisible) values and each record

has a unique identifier (Primary Key).

• No repeating groups or arrays are allowed.
• 2NF (Second Normal Form):
• A table is in 2NF if it is in 1NF and all non-key attributes are fully

functionally dependent on the primary key.

• Eliminates partial dependency (when a non-key attribute depends on only

part of a composite primary key).

• 3NF (Third Normal Form):
• A table is in 3NF if it is in 2NF and there are no transitive dependencies

(non-key attributes depending on other non-key attributes).

• This removes dependencies between non-key attributes.
• BCNF (Boyce-Codd Normal Form):
• A table is in BCNF if it is in 3NF and every determinant is a candidate key.
• This is a stricter version of 3NF.
• 4NF (Fourth Normal Form):
• A table is in 4NF if it is in BCNF and multi-valued dependencies are

removed.

• This ensures that a record doesn’t have two or more independent

multi-valued attributes.

• 5NF (Fifth Normal Form):
• A table is in 5NF if it is in 4NF and cannot be decomposed further without

losing data.

Denormalization is the process of intentionally introducing redundancy into a database by

merging tables or adding redundant data to reduce the complexity of database queries,

improve read performance, and simplify the schema.

• When to use:
• When read performance is more critical than data integrity and

normalization (e.g., in OLAP systems or reporting).

• In cases where complex JOINs are causing performance bottlenecks.
• In high-performance environments where frequently accessed data can be

stored redundantly to reduce query times.

To normalize a database to 3NF:

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

Answer: ttributes like CourseName only depend on CourseID would violate 2NF because CourseName is partially dependent on the composite key. You would separate this into two tables (one for Courses and one for Students).

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

• 1NF (First Normal Form): Ensures that the table has atomic columns (no

multi-valued or repeating groups) and each row has a unique identifier (Primary Key).

• 2NF (Second Normal Form): The table must be in 1NF and all non-key columns

must depend on the entire primary key (i.e., there should be no partial

dependencies).

Example:

• A table with a composite primary key (e.g., StudentID, CourseID) where non-key

attributes like CourseName only depend on CourseID would violate 2NF because

CourseName is partially dependent on the composite key. You would separate this

into two tables (one for Courses and one for Students).