Interview Q&A

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: Primary Key: A unique identifier for each record in a database table. No two rows in a table can have the same primary key value. It ensures entity integrity.

Example code

user_id in a users table. Foreign Key: A field (or a combination of fields) in one table that uniquely identifies a row of another table. It establishes a relationship between two tables and enforces referential integrity. Example: user_id in an orders table linking to user_id in the users 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: 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: Normalization is the process of organizing the data in a database to reduce redundancy and dependency by dividing large tables into smaller ones and linking them with relationships. Importance: Reduces Data Redundancy: Ensures data is only stored once. Improves Data Integrity: Ensures consistency and correctness of data. Simplifies Updates: Easier to maintain and modify data without affecting the entire system.

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: 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: Why is it important? Normalization is the process of organizing the data in a database to reduce redundancy and dependency by dividing large tables into smaller ones and linking them with relationships. Importance: Reduces Data Redundancy: Ensures data is only stored once. Improves Data Integrity: Ensures consistency and correctness of data. Simplifies Updates: Easier to maintain and modify data without affecting…

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: 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: Denormalization is the process of combining tables that were previously normalized. This introduces redundancy to optimize read-heavy operations. When to use: Performance Optimization: Useful for applications where read speed is crucial and the overhead of complex joins needs to be minimized. Reporting and Analytics: When querying large datasets for reports or aggregations.

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: A database schema is the structure that defines the organization of data in a database. It includes definitions of tables, relationships, indexes, constraints, and other elements.

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: GROUP BY DepartmentId with MAX(Salary), or use RANK/DENSE_RANK PARTITION BY DepartmentId to also return employee names tied for max.

Sample solution

-- Aggregate only
SELECT DepartmentId, MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY DepartmentId;

-- Employees earning the max in their department
SELECT EmpId, Name, DepartmentId, Salary
FROM (
    SELECT *, DENSE_RANK() OVER (
        PARTITION BY DepartmentId ORDER BY Salary DESC
    ) AS rnk
    FROM Employees
) t
WHERE rnk = 1;

If they want names, window functions beat a join-back to MAX aggregates.

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: Anchor member selects the root manager; recursive member joins employees whose ManagerId equals the CTE EmpId. UNION ALL combines them. Watch MAXRECURSION.

Sample solution

WITH Hierarchy AS (
    SELECT EmpId, ManagerId, Name, 0 AS Lvl
    FROM Employees
    WHERE ManagerId IS NULL   -- root
    UNION ALL
    SELECT e.EmpId, e.ManagerId, e.Name, h.Lvl + 1
    FROM Employees e
    INNER JOIN Hierarchy h ON e.ManagerId = h.EmpId
)
SELECT * FROM Hierarchy
ORDER BY Lvl, Name
OPTION (MAXRECURSION 100);

Edge cases to mention

• Cycles in hierarchy
• Multiple roots
• Deep trees hitting recursion limit

Say “anchor + recursive member + termination” — that structure is the interview checklist.

Short answer: INNER returns matches only. LEFT keeps all left rows (NULL right when no match). RIGHT mirrors LEFT. FULL keeps unmatched from both. CROSS is Cartesian product. Be ready to write examples.

Sample solution

-- Employees without matching department (orphan check)
SELECT e.*
FROM Employees e
LEFT JOIN Departments d ON d.DepartmentId = e.DepartmentId
WHERE d.DepartmentId IS NULL;

Draw Venn diagrams verbally in 20 seconds — clarity scores points.

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.