Interview Q&A

Short answer: Each tenant gets its own database instance. Pros: Complete isolation, better security, and individual scaling. Cons: Higher overhead for database management and maintenance. Example: SaaS applications often use multi-tenancy to serve multiple clients with the same infrastructure while keeping data separated.

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 flexible schema design used for scenarios where attributes of entities are dynamic and can vary over time. Example: A Product might have a variety of attributes like size, color, and weight, but some products may not have all attributes, so you store them as key-value pairs.

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 pagination techniques (LIMIT, OFFSET, or ROWNUM) to retrieve data in chunks rather than all at once. Avoid fetching unnecessary columns or rows.

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: Track buffer cache hit ratio, transaction log size, locks and deadlocks, and cache usage to monitor the internal database performance.

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: Horizontal scaling involves adding more database servers to distribute the load, typically using replication, sharding, or clustering. Advantages: It can handle much larger datasets and traffic since the workload is distributed across multiple servers. Disadvantages: More complex to implement and maintain, requires specialized techniques like load balancing and data partitioning.

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 is written to both the primary and secondary database simultaneously. Advantages: Ensures data consistency between all replicas. Disadvantages: May incur performance overhead due to the need to wait for replication.

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 transaction log backup records all the changes made to the database since the last transaction log backup.

Explain a bit more

It allows point-in-time recovery. Advantages: Enables recovery of the database to any specific point in time (assuming all previous logs are available). Disadvantages: Requires continuous backups of transaction logs to maintain full recovery. Example: After a transaction log backup, you can restore to a specific moment in time by replaying the logs up to that point.

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: files. Use access controls, roles, and policies to restrict access to backups.

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: responsibilities. Example (SQL Server): - Create a role CREATE ROLE Manager; - Grant permissions to the role GRANT SELECT, UPDATE ON Employees TO Manager; - Assign the role to a user EXEC sp_addrolemember 'Manager', 'john_doe'; In PostgreSQL or MySQL, you would follow similar steps using GRANT statements and role management commands.

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: Customers: Contains customer details (name, address, etc.). Orders: Contains order details and references customers. Order_Items: A join table between Orders and Products. Products: Product details (name, description, price, etc.). Inventory: Tracks stock levels.

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 another non-key attribute). Ensure that no non-key attribute is dependent on another 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…

Explain a bit more

dependencies. The Department_Manager is a dependency on Department, not directly on 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: to retrieve data. To resolve this, indexes can be reorganized or rebuilt to improve their 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: likely to improve performance when indexed.

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: PostgreSQL: PostgreSQL doesn’t have a built-in debugger, but you can use RAISE NOTICE for debugging or use third-party tools like pgAdmin for debugging. MySQL: MySQL Workbench provides a simple debugging interface that allows stepping through the stored procedure and monitoring execution. Example (PostgreSQL): RAISE NOTICE 'Debugging variable: %', my_variable;

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: ACID stands for: Atomicity: Ensures that all operations in a transaction are completed successfully, or none are.

Explain a bit more

If one part of a transaction fails, the whole transaction fails. Consistency: Guarantees that the database is always in a valid state, adhering to all rules, including constraints, after a transaction. Isolation: Ensures that transactions are executed in isolation from each other, so one transaction does not interfere with another. Durability: Ensures that once a transaction is committed, it is permanent, even in the case of system failures.

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: table can have the same primary key value. It ensures entity integrity.

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: available. If there’s no match, NULL is returned for the missing side. Use case: When you want all records from both tables.

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: Load the transformed data into the target database. For SQL databases, tools like MySQL Workbench or SQL Server Integration Services (SSIS) can be used. For NoSQL, tools like MongoDB Compass or Data Migration Tools can be utilized.

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 to improve performance by storing redundant data, reducing the need for complex joins, and speeding up read queries. Used in data warehousing and systems requiring fast reads.

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: For large inserts or updates, break the task into smaller batches to avoid overwhelming the system.

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: Identify unused indexes or fragmented indexes that could slow 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: across multiple servers.

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 is written to the primary database, and changes are replicated to secondary databases after a delay.

Explain a bit more

Advantages: Better performance due to less replication overhead. Disadvantages: Potential for data loss or inconsistency in the event of a failure. Tools for Replication: SQL Server: Use Always On Availability Groups or Transactional Replication. PostgreSQL: Use Streaming Replication or Logical Replication. MySQL: Use MySQL Replication or Group Replication. MongoDB: Use Replica Sets for automatic failover and high availability. Example (PostgreSQL Streaming Replication): # On Master Node wal_level = replica archive_mode = on archive_command = 'cp %p /var/lib/postgresql/archive/%f' # On Standby Node primary_conninfo = 'host=master_ip port=5432 user=replication_user password=replication_password' Database Scaling & Performance

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 AWS RDS, Azure SQL, or Google Cloud SQL to automate backups in cloud environments. These platforms allow automatic backup scheduling without manual intervention. Example: AWS RDS automatically performs daily backups and retains backups for a configurable retention period.

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: LEFT JOIN Orders and filter WHERE Orders.OrderId IS NULL, or use NOT EXISTS. NOT EXISTS often optimizes better and handles NULLs more safely than NOT IN.

Sample solution

-- LEFT JOIN anti-join
SELECT e.EmpId, e.Name
FROM Employees e
LEFT JOIN Orders o ON o.EmpId = e.EmpId
WHERE o.OrderId IS NULL;

-- NOT EXISTS (preferred in many plans)
SELECT e.EmpId, e.Name
FROM Employees e
WHERE NOT EXISTS (
    SELECT 1 FROM Orders o WHERE o.EmpId = e.EmpId
);

Avoid NOT IN (subquery) if the subquery can return NULL — it can yield empty results.