Interview Q&A

Short answer: lso increases the risk of downtime during upgrades.

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 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, nd 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: Use case: When you only want matching records.

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: Understand the source and target databases, and map the data models. Identify data transformations, such as type conversions or renaming columns.

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 tools like Liquibase, Flyway, or Alembic to manage and version schema changes. These tools allow you to write migrations in SQL or as scripts that can be version-controlled. These tools can apply, track, and rollback schema changes.

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: All tenants share the same database and tables. A tenant identifier (e.g., tenant_id) is used to segregate data. Pros: Easier to maintain and scale. Cons: Can lead to security and data isolation issues.

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 common pattern where one record in a table can be associated with multiple records in another table. Example: A Customer can have many Orders, but each Order can belong to only one Customer.

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 indexes on columns that are used in WHERE clauses, JOIN conditions, and ORDER BY clauses. Use covering indexes to avoid full table scans and improve 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: Use EXPLAIN or QUERY PLAN to analyze query execution times and identify slow queries. Track metrics like response time, execution time, and query throughput.

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: Sharding involves splitting data into smaller, more manageable pieces called shards.

Explain a bit more

Each shard is stored on a different server (or cluster) to distribute the load. Typically, horizontal partitioning is used in sharding, where rows (rather than columns) of a database table are distributed across multiple databases. Example: A user table could be sharded by user ID, where all users with IDs 1-1000 are stored in one database, 1001-2000 in another, and so on.

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: Vertical scaling means upgrading the resources (CPU, RAM, disk space) of a single database server. Advantages: Simple to implement since you only need to upgrade the existing server. Disadvantages: There is a physical limit to how much you can scale up. It also increases the risk of downtime during upgrades.

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: One database (master) handles all writes, while one or more secondary databases (slaves) replicate the data for read queries and redundancy. Advantages: Load balancing for read-heavy applications, fault tolerance. Disadvantages: Writes are still a single point of failure.

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: SQL Server Agent: Use SQL Server Agent to schedule backup jobs.

Example code

Create a backup job in SQL Server Management Studio (SSMS) to run daily, weekly, or at specific intervals. Script for automated backups: BACKUP DATABASE MyDatabase TO DISK = 'C:\Backups\MyDatabase.bak';

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 full backup includes all the data in the database at the time the backup is taken. It is a complete snapshot of the database. Advantages: Easy to restore; ensures a full copy of the database. Disadvantages: Larger in size and takes more time compared to other backup types. Example: This backup is typically taken on a regular schedule, like daily or weekly.

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. Use tools like gpg, or built-in encryption features (e.g., SQL Server Backup Encryption, PostgreSQL using pg_dump with -Fc and encrypting the output file).

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: Define Roles: Define different roles based on business requirements (e.g., admin,? is a common interview topic in SQL & Databases. Give a clear definition, then one concrete example.

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: Entities: Products, Customers, Orders, Payments, ShoppingCart, Categories,? 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: redundancy by eliminating unnecessary duplication.

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: 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: Use DENSE_RANK() or OFFSET/FETCH. Prefer DENSE_RANK when ties matter. Avoid brittle MAX(salary) WHERE salary < MAX(salary) without discussing ties.

Problem statement

Employees(EmpId, Name, Salary). Return the second highest distinct salary.

Interview approach

1. Ask whether “second highest” means distinct salaries or second row after sort.
2. Show DENSE_RANK for distinct ranks.
3. Mention TOP 1 ... WHERE Salary < (SELECT MAX(Salary) ...) as older style.

Sample solution

-- Option A: DENSE_RANK (handles ties cleanly)
SELECT Salary
FROM (
    SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk
    FROM Employees
) t
WHERE rnk = 2;

-- Option B: OFFSET/FETCH (SQL Server)
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;

Edge cases to mention

• Fewer than 2 distinct salaries
• NULL salaries
• Multiple employees sharing the top salary

Common follow-ups

• Nth highest salary
• Second highest per department

Say DENSE_RANK vs RANK out loud — panels love that distinction.

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.