Interview Q&A

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: and recovery procedures to verify data integrity. Example (SQL Server): BACKUP DATABASE MyDatabase TO DISK = 'D:\Backups\MyDatabase.bak' WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = MyCert); Restoration

Example code

RESTORE DATABASE MyDatabase FROM DISK = 'D:\Backups\MyDatabase.bak' WITH FILE = 1, NOUNLOAD, STATS = 10; Additional Best Practices: Use incremental backups: Reduce the backup size and time by only backing up data that has changed since the last backup. Backup Retention Policy: Implement a retention policy to ensure old backups are properly archived or deleted. Backup & Recovery

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: CategoryID, CustomerID).

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: with primary keys and referential integrity rather than repeating the 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: fragmentation, and distribution of data to make its decision.

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: n index on the filtering columns can improve 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: of data from disk. In summary, indexes drastically speed up SELECT queries at the cost of increased overhead during INSERT, UPDATE, and DELETE operations.

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: Ensure the integrity and completeness of the data after migration by performing data checks (e.g., record counts, sampling).

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: This pattern helps to normalize the data when you have a set of static values used repeatedly across the database. Example: A Country table that contains a list of country names, which is then referenced by other tables like Customers or Employees.

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: Avoid complex subqueries or nested SELECTs, especially in large tables. Rewrite queries using joins or CTEs (Common Table Expressions).

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: For distributed systems, monitor the lag between the primary database and read replicas. Tools for Monitoring: New Relic, Datadog, SolarWinds, pg_stat_activity (for PostgreSQL), SHOW STATUS (for MySQL), or SQL Server Profiler for SQL Server.

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: frequently accessed data, and using denormalization where appropriate. NoSQL (MongoDB - Optional)

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: (e.g., a unique index) is required.

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: Indexes are data structures that speed up the retrieval of data from a database. They work like the index in a book, allowing quick lookup of data without having to scan the entire table. Importance: Faster Searches: Improves query performance, especially for large datasets. Efficient Sorting: Helps in sorting and filtering operations. Primary and Foreign Keys: Automatically indexed to ensure quick data retrieval.

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: Analyze the query execution plan to identify any inefficient operations (e.g., full table scans) and refactor accordingly. Advanced Topics

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: Caching: Use query caching for frequently run queries.? 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: Can you give examples? Database Constraints are rules applied to ensure the integrity and accuracy of the data within a database. Examples include: NOT NULL: Ensures that a column cannot have a NULL value. UNIQUE: Ensures all values in a column are unique. CHECK: Ensures that all values in a column satisfy a specific condition. DEFAULT: Sets a default value for a column if no value is specified. FOREIGN KEY: Ensures…

Explain a bit more

the value in one table corresponds to a valid value in another table.

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 trigger is a special kind of stored procedure that is automatically executed or fired when certain events occur in a database, such as INSERT, UPDATE, or DELETE. Example: A trigger that automatically updates a timestamp field every time a row is modified.

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: Developed by Microsoft, it's known for its strong integration with other Microsoft products.

Explain a bit more

It’s commonly used in enterprise environments. PostgreSQL: An open-source, object-relational database known for its standards compliance, extensibility, and advanced features like support for complex queries, JSONB, and custom data types. MySQL: An open-source relational database known for its speed and ease of use. It's often used in web applications (e.g., with PHP) and is less feature-rich than PostgreSQL but highly reliable.

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: ROW_NUMBER gives unique sequence even for ties. RANK leaves gaps after ties. DENSE_RANK does not leave gaps. All use OVER (ORDER BY ...).

Sample solution

SELECT Name, Score,
       ROW_NUMBER() OVER (ORDER BY Score DESC) AS rn,
       RANK()       OVER (ORDER BY Score DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY Score DESC) AS dense_rnk
FROM Students;

Memorize one example with ties: scores 100,100,90 → RANK 1,1,3 vs DENSE_RANK 1,1,2.

Short answer: Non-correlated runs once and is independent of the outer row. Correlated references outer columns and conceptually runs per outer row. EXISTS often uses correlation efficiently.

Sample solution

-- Non-correlated
SELECT * FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

-- Correlated: employees earning above their department average
SELECT e.*
FROM Employees e
WHERE e.Salary > (
    SELECT AVG(e2.Salary)
    FROM Employees e2
    WHERE e2.DepartmentId = e.DepartmentId
);

Mention that correlated subqueries can be rewritten with JOINs/windows for clarity/performance.

Short answer: LAG looks at the previous row; LEAD looks at the next row within an ordered partition. Great for day-over-day diffs.

Sample solution

SELECT OrderDate, Amount,
       LAG(Amount, 1) OVER (ORDER BY OrderDate) AS PrevAmount,
       LEAD(Amount, 1) OVER (ORDER BY OrderDate) AS NextAmount,
       Amount - LAG(Amount, 1) OVER (ORDER BY OrderDate) AS Delta
FROM DailySales;

Always specify ORDER BY in OVER — without it the result is nondeterministic.

Short answer: Use SUM(Amount) OVER (ORDER BY OrderDate ROWS UNBOUNDED PRECEDING). Prefer ROWS over RANGE when you want physical cumulative sums with ties on the order key.

Sample solution

SELECT OrderId, OrderDate, Amount,
       SUM(Amount) OVER (
           ORDER BY OrderDate, OrderId
           ROWS UNBOUNDED PRECEDING
       ) AS RunningTotal
FROM Orders;

Mention PARTITION BY CustomerId for per-customer running totals.

Short answer: EXISTS tests for at least one matching row and short-circuits. IN compares to a list/set. NOT IN fails if the list contains NULL. Prefer EXISTS/NOT EXISTS for anti-semi-joins.

If you remember only one rule: beware NOT IN + NULL.