Interview Q&A

Short answer: Creating an Index: CREATE INDEX idx_index_name ON table_name(column_name); Dropping an Index: DROP INDEX idx_index_name ON table_name; In SQL Server, dropping an index: DROP INDEX idx_index_name; -- No need to specify table name.

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Clustered Index: The data in the table is physically sorted based on the clustered index.

Explain a bit more

Each table can have only one clustered index (usually the primary key). Fast for queries that involve range-based retrieval (e.g., BETWEEN or ORDER BY). Non-Clustered Index: The data in the table is not physically sorted. The non-clustered index is stored separately from the actual data. A table can have multiple non-clustered indexes. Faster for point lookups (single value queries) but less efficient for range-based searches.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Indexes can slow down data modification operations: INSERT: When a new row is inserted, the index must also be updated to include the new value, adding extra overhead.

Explain a bit more

UPDATE: If the indexed column is updated, the index must be modified to reflect the new value, which can be slower. DELETE: When a row is deleted, the corresponding index entries must be removed, causing additional overhead. While indexes speed up SELECT queries, they can reduce the performance of INSERT, UPDATE, and DELETE operations due to the extra work needed to maintain the index.

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Consider creating an index when:

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: A composite index is an index that is created on multiple columns.

Explain a bit more

It is used when a query filters or sorts based on more than one column. Composite indexes are especially useful when queries frequently involve conditions on multiple columns. Example Use Case: If a query filters by both last_name and first_name, creating a composite index on (last_name, first_name) will speed up the query. Example of creating a composite index: CREATE INDEX idx_lastname_firstname ON employees(last_name, first_name);

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Unique Index: Ensures that the values in the indexed columns are unique.

Explain a bit more

Can be created on any column and allows NULL values (in most DBMS). A table can have multiple unique indexes. Primary Key Index: Enforces the uniqueness of the column(s) and also guarantees NOT NULL constraint on the column(s). A table can only have one primary key. Automatically creates a unique index. In short, both enforce uniqueness, but the primary key also guarantees that the column(s) cannot be NULL.

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: The query optimizer in the database engine decides which index to use based on several factors:

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: A covering index is an index that contains all the columns needed by a query, meaning the query can be answered entirely by the index without accessing the actual table data. This can improve query performance by reducing disk I/O.

Example code

If a query selects id, name, and salary from the employees table and there is an index on (id, name, salary), the index itself is sufficient to satisfy the query, and the database doesn't need to access the table at all.

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Index fragmentation occurs when data is inserted, updated, or deleted, causing the index structure to become inefficient. This can negatively impact performance in several ways:

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: 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.

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Reorganizing an Index: A lighter operation that compacts the index and defragments it.

Explain a bit more

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);

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

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

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short 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.

Real-world example (ShopNest)

Checkout wraps stock decrement + order insert in a transaction so you never sell stock you do not have.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: 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.

Explain a bit more

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.

Real-world example (ShopNest)

Checkout wraps stock decrement + order insert in a transaction so you never sell stock you do not have.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: re saved and visible to other transactions.

Explain a bit more

When to use: After the transaction operations have completed successfully and you want to ensure that the changes are saved to the database. 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. BEGIN TRANSACTION; UPDATE employees SET salary = 5000 WHERE id = 1; COMMIT; 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 code

BEGIN TRANSACTION; UPDATE employees SET salary = 5000 WHERE id = 1; COMMIT; 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. 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; 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;

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: 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 code

BEGIN TRANSACTION; UPDATE employees SET salary = 5000 WHERE id = 1; COMMIT;

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: 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 code

BEGIN TRANSACTION; UPDATE employees SET salary = 5000 WHERE id = 1; - Something goes wrong ROLLBACK;

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: A 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 code

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;

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: 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.

Real-world example (ShopNest)

Checkout wraps stock decrement + order insert in a transaction so you never sell stock you do not have.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Can you explain the different isolation levels (e.g., READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE)?

Explain a bit more

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.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: 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.

Real-world example (ShopNest)

Checkout wraps stock decrement + order insert in a transaction so you never sell stock you do not have.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Transactions impact concurrent operations by introducing locking mechanisms to ensure that multiple transactions don't interfere with each other and cause inconsistent data.

Explain a bit more

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.

Real-world example (ShopNest)

Checkout wraps stock decrement + order insert in a transaction so you never sell stock you do not have.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: 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.

Real-world example (ShopNest)

Checkout wraps stock decrement + order insert in a transaction so you never sell stock you do not have.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short 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.

Real-world example (ShopNest)

Checkout wraps stock decrement + order insert in a transaction so you never sell stock you do not have.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Deadlocks occur when two or more transactions are waiting for each other to release locks, causing a cycle of dependencies.

Explain a bit more

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.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.