Interview Q&A

Short answer: record has a unique identifier.

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: pages, slowing down query performance.

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: Faster Search: Indexes allow the database to locate rows much faster than a full? is a common interview topic in SQL & Databases. Give a clear definition, then one concrete example.

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: SQL Databases (Relational Databases): These are structured databases that use Structured Query Language (SQL) for defining and manipulating data.

Explain a bit more

They store data in tables with rows and columns. SQL databases enforce a strict schema, meaning the structure of the data must be defined beforehand. Examples: MySQL, PostgreSQL, SQL Server, Oracle NoSQL Databases (Non-relational Databases): These databases are more flexible and allow storage of unstructured or semi-structured data. They don't require a fixed schema and are often used for large-scale applications where flexibility, scalability, and speed are more important than the strict relational model. Examples: MongoDB, Cassandra, CouchDB, Firebase

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: Consistency: The database moves from one valid state to another, maintaining? is a common interview topic in SQL & Databases. Give a clear definition, then one concrete example.

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: Extract the data from the source database using export tools or direct queries.

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: Store migration scripts in version control (e.g., Git) and ensure that each change is applied in a sequential, ordered fashion.

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: Each tenant has its schema in the same database. The schema contains the same tables but is isolated per tenant. Pros: Better data isolation compared to the first approach. Cons: More complex to manage schema changes across tenants.

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 pattern used when two entities are related in a way where both can have multiple associations with each other. Example: A Student can be enrolled in many Courses, and a Course can have many Students. This would typically be modeled using a junction table like Student_Course.

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: Monitor CPU, memory usage, disk I/O, and network latency using tools like Prometheus, Grafana, or cloud-specific monitoring tools like AWS CloudWatch or Azure Monitor.

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: Partitioning is the process of dividing a single table into smaller, more manageable pieces (partitions). This can be done either horizontally or vertically. Horizontal Partitioning: Data is divided based on rows (e.g., splitting a sales table by date range). Vertical Partitioning: Data is divided based on columns (e.g., splitting a user table into two: one for personal information and the other for preferences).

Example code

A sales table might be partitioned by year (2019, 2020, etc.), where each partition is stored in a different location or server.

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: Two or more databases act as both masters and slaves, allowing for both read and write operations on any node. Advantages: High availability and load balancing for both reads and writes. Disadvantages: Potential for data conflicts when writes happen simultaneously on multiple nodes.

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: Use cron jobs to automate pg_dump commands. Example (Linux): 0 3 * * * pg_dump -U postgres mydatabase > /path/to/backup/mydatabase_$(date +\%Y\%m\%d).sql Use pgBackRest or Barman for more advanced backup automation.

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 differential backup includes all the changes made since the last full backup.

Explain a bit more

It does not depend on other differential backups. Advantages: Smaller than a full backup, and quicker to take compared to full backups. Disadvantages: Can become larger over time as more changes accumulate between full backups. Example: If the last full backup was taken on Monday, a differential backup taken on Wednesday would include all changes since Monday.

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: access-controlled locations, ideally offsite or in the cloud (e.g., AWS S3, Azure Blob Storage). Use encrypted cloud storage options.

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 Customer can have multiple Orders. An Order can contain multiple Products (many-to-many relationship). A Product can belong to multiple Categories. Payments are linked to Orders.

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: on the primary key. Remove partial dependencies (when a non-key attribute depends on part of a composite 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: Larger Indexes: Fragmentation can lead to larger index sizes and more disk space? is a common interview topic in SQL & Databases. Give a clear definition, then one concrete example.

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: WHERE, JOIN, ORDER BY, and GROUP BY clauses to choose the most appropriate index.

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: Large Tables: Indexes are especially helpful for large tables that are queried? is a common interview topic in SQL & Databases. Give a clear definition, then one concrete example.

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: Efficient Sorting: Since indexes store data in sorted order, operations like ORDER? is a common interview topic in SQL & Databases. Give a clear definition, then one concrete example.

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: Structured Data: SQL is best suited for structured data where relationships between data are important.

Explain a bit more

ACID Compliance: SQL databases generally support ACID (Atomicity, Consistency, Isolation, Durability) properties, making them ideal for applications where data integrity is crucial, such as banking systems. Complex Queries: SQL databases provide powerful query capabilities with joins, subqueries, and aggregations. Maturity and Ecosystem: SQL databases have been around for decades, offering a robust set of features, tools, and community support.

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: vailability will fail during network partitions. CP (Consistency and Partition Tolerance): Systems that prioritize consistency and partition tolerance may not be available during network issues. AP (Availability and Partition Tolerance): Systems that prioritize availability and partition tolerance may return stale data.

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: Wrap DENSE_RANK() OVER (ORDER BY Salary DESC) and filter WHERE rnk = @N. Parameterize N. For distinct salaries only, DENSE_RANK is usually what interviewers want.

Sample solution

DECLARE @N INT = 3;
SELECT EmpId, Name, Salary
FROM (
    SELECT EmpId, Name, Salary,
           DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk
    FROM Employees
) t
WHERE rnk = @N;

Ask if they want all employees at that rank or one salary value.

Short answer: Use a CTE with ROW_NUMBER() PARTITION BY the duplicate key, then DELETE WHERE rn > 1. This is the standard T-SQL interview answer.

Sample solution

WITH d AS (
    SELECT EmpId,
           ROW_NUMBER() OVER (PARTITION BY Email ORDER BY EmpId) AS rn
    FROM Employees
)
DELETE FROM d WHERE rn > 1;

Edge cases to mention

• Which row to keep (MIN EmpId vs latest UpdatedAt)
• Composite duplicate keys

Mistakes to avoid

• DELETE without ROW_NUMBER preview SELECT first
• Using DISTINCT in DELETE incorrectly

Always SELECT from the CTE before DELETE in real work — mention that safety habit.