Interview Q&A

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: 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: 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: 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: 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: 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).

Real-world example (ShopNest)

Product and Category are separate tables (normalized). The order line stores product id + price snapshot—not a giant duplicated product blob.

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: 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).

Real-world example (ShopNest)

Product and Category are separate tables (normalized). The order line stores product id + price snapshot—not a giant duplicated product blob.

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

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

Real-world example (ShopNest)

Product and Category are separate tables (normalized). The order line stores product id + price snapshot—not a giant duplicated product blob.

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: nother table. This prevents invalid references from being made. Enforcing Referencing Integrity: Use foreign keys to create relationships between tables. Enforce actions like ON DELETE CASCADE, ON UPDATE CASCADE, or ON DELETE RESTRICT to define how changes in parent tables affect related child tables. Example: A Foreign Key in the Orders table… references……… the CustomerID in the Customers table. If you try to…

Explain a bit more

delete a customer that has associated orders, referential integrity will prevent the delete unless actions like CASCADE are specified.

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: Referential integrity ensures that relationships between tables are consistent.

Explain a bit more

Specifically, it ensures that a foreign key in one table must match a primary key or a unique key in another table. This prevents invalid references from being made. Enforcing Referencing Integrity: Use foreign keys to create relationships between tables. Enforce actions like ON DELETE CASCADE, ON UPDATE CASCADE, or ON DELETE RESTRICT to define how changes in parent tables affect related child tables. Example: A Foreign Key in the Orders table references the CustomerID in the Customers table. If you try to delete a customer that has associated orders, referential integrity will prevent the delete unless actions like CASCADE are specified.

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: composite key is a combination of two or more columns in a table that can uniquely identify each record in the table. It is used when a single column is not sufficient to uniquely identify a row.

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: An Entity-Relationship Diagram (ERD) is a graphical representation of entities and their relationships to each other within a database.

Explain a bit more

It is used to visualize the structure of a database and how tables (entities) relate to one another. Entities are represented as rectangles. Attributes are shown as ovals connected to their entities. Relationships are represented as diamonds, showing how entities interact. ERDs help in the conceptual design phase of a database, outlining entities, relationships, and keys before implementation.

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: surrogate key is an artificial, system-generated key used to uniquely identify a record in a table.

Explain a bit more

It has no business meaning and is typically a number (e.g., auto-incremented ID). Surrogate Key: No business meaning. Commonly used in data warehousing and large-scale systems. Example: A column UserID that is generated automatically. Natural Key: A key that has real-world business meaning and is used to uniquely identify records. Example: SocialSecurityNumber or EmailAddress could serve as natural keys.

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: How is it different from a natural key? A surrogate key is an artificial, system-generated key used to uniquely identify a record in a table. It has no business meaning and is typically a number (e.g., auto-incremented ID). Surrogate Key: No business meaning. Commonly used in data warehousing and large-scale systems. Example: A column UserID that is generated automatically. Natural Key: A key that has real-world…

Explain a bit more

business meaning and is used to uniquely identify records. Example: SocialSecurityNumber or EmailAddress could serve as natural keys.

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: MongoDB is a NoSQL document-oriented database that stores data in flexible, semi-structured documents rather than in rows and columns like SQL databases.

Explain a bit more

Unlike SQL databases, which rely on rigid schemas, MongoDB uses a schema-less approach where each document can have different fields and structures. Key differences: SQL Databases: Store data in tables with a fixed schema and use ACID transactions for consistency. MongoDB: Uses a document model (e.g., JSON-like structures) and offers more flexibility, scalability, and ease of replication but may sacrifice some consistency in distributed environments.

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: document in MongoDB is a record represented as a JSON-like object (BSON format) that contains field-value pairs. It can have a flexible structure, meaning each document in a collection may contain different fields and data types.

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: How does it differ from rows in SQL databases?

Explain a bit more

A document in MongoDB is a record represented as a JSON-like object (BSON format) that contains field-value pairs. It can have a flexible structure, meaning each document in a collection may contain different fields and data types. Differences from SQL Rows: In SQL, a row is a fixed set of columns (defined by the schema), while in MongoDB, a document is more flexible and can vary in structure. A row in SQL is constrained by a schema, whereas a document in MongoDB can store complex, nested data structures (arrays, sub-documents).

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: mount of data the system needs to scan. MongoDB creates indexes on fields that are queried frequently.

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: How is it different from SQL indexes? Indexing in MongoDB is a mechanism to improve query performance by reducing the amount of data the system needs to scan. MongoDB creates indexes on fields that are queried frequently. Differences from SQL indexes: MongoDB uses B-tree indexes (by default) but also supports other types like hashed indexes, geospatial indexes, and text indexes. SQL indexes are typically built on a…

Explain a bit more

fixed schema and are more rigid, while MongoDB indexes can be dynamic, allowing indexing on any field within a document. MongoDB supports compound indexes and array indexes for more complex queries.

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.