Interview Q&A

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

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

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

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: Normal forms are guidelines used to organize a relational database schema.

Explain a bit more

Each form addresses a different kind of redundancy or dependency.

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: To normalize a database to 3NF:

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: Data redundancy occurs when the same piece of data is stored in multiple places, which can lead to inconsistencies and wasted storage. To avoid redundancy:

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: pplication? Designing a database schema for a large e-commerce application involves careful consideration of data requirements, scalability, and normalization. Key entities and relationships to consider:

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: Designing a database schema for a large e-commerce application involves careful consideration of data requirements, scalability, and normalization. Key entities and relationships to consider:

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: 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: Advantages: Scalability: MongoDB scales horizontally through sharding, which can handle large datasets across distributed clusters.

Explain a bit more

Flexibility: Schema-less design allows for dynamic data models and easier iteration during development. Performance: It can perform high-throughput reads and writes for large datasets. High Availability: Through replica sets, MongoDB ensures data availability and fault tolerance. Disadvantages: Consistency: By default, MongoDB uses eventual consistency, which may not be suitable for all applications (though it supports ACID transactions in some cases). Complexity in Transactions: While MongoDB now supports multi-document transactions, working with them is more complex compared to SQL. Data Duplication: The flexibility can sometimes lead to data duplication and inconsistency if not properly managed.

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: 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: Database: A container for collections. MongoDB allows you to have multiple databases within the same instance. Collection: A group of MongoDB documents. Collections are similar to tables in SQL but don’t have a fixed schema.

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: Create: db.collection.insertOne({ name: "Alice", age: 25 }); db.collection.insertMany([{ name: "Bob", age: 30 }, { name: "Charlie", age: 35 }]); Read: db.collection.find({ age: { $gt: 30 } }); db.collection.findOne({ name: "Alice" }); Update: db.collection.updateOne({ name: "Alice" }, { $set: { age: 26 } }); db.collection.updateMany({ age: { $lt: 30 } }, { $set: { status: "young" } }); Delete:…

Explain a bit more

db.collection.deleteOne({ name: "Alice" }); db.collection.deleteMany({ age: { $lt: 30 } });

Example code

Create: db.collection.insertOne({ name: "Alice", age: 25 }); db.collection.insertMany([{ name: "Bob", age: 30 }, { name: "Charlie", age: 35 }]); Read: db.collection.find({ age: { $gt: 30 } }); db.collection.findOne({ name: "Alice" }); Update: db.collection.updateOne({ name: "Alice" }, { $set: { age: 26 } }); db.collection.updateMany({ age: { $lt: 30 } }, { $set: { status: "young" } }); Delete: db.collection.deleteOne({ name: "Alice" }); db.collection.deleteMany({ age: { $lt: 30 } });

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.

Short answer: The $in operator is used to match values in a specified array. It checks if the value of a field is equal to any value in the provided array.

Example code

db.collection.find({ age: { $in: [25, 30, 35] } }); This query finds all documents where the age field is 25, 30, or 35.

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.