Interview Q&A

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: SELECT, INSERT, UPDATE, DELETE). SELECT, INSERT, UPDATE, DELETE). SELECT, INSERT, UPDATE, DELETE). SELECT, INSERT, UPDATE, DELETE). SELECT, INSERT, UPDATE, DELETE). SELECT, INSERT, UPDATE, DELETE). SELECT, INSERT, UPDATE, DELETE). SELECT, INSERT, UPDATE, DELETE).

Example code

SELECT, INSERT, UPDATE, DELETE). SELECT, INSERT, UPDATE, DELETE). SELECT, INSERT, UPDATE, DELETE). SELECT, INSERT, UPDATE, DELETE). SELECT, INSERT, UPDATE, DELETE). SELECT, INSERT, UPDATE, DELETE). SELECT, INSERT, UPDATE, DELETE). SELECT, INSERT, UPDATE, DELETE).

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 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: And cache usage to monitor the internal database performance.

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: nother non-key attribute. For example: Original Table: Employee_ID Employee_Nam Department Department_Manager 1 Alice HR John 2 Bob IT Sarah ● 1NF: Remove repeating groups and ensure atomic values. 2NF: Remove partial dependencies. In this case, split into separate tables for employees and departments. 3NF: Remove transitive dependencies. The………… Department_Manager is a dependency on Department, not directly on…

Explain a bit more

Employee, so it should be moved to a separate table for Departments.

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: Trade-offs: CA (Consistency and Availability): Systems that prioritize consistency and availability 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: Use ETL (Extract, Transform, Load) tools to clean, transform, and shape the data as required.

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: Set up continuous integration (CI) pipelines to test schema migrations on different environments to ensure smooth deployment.

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