Interview Q&A

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: 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: 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: 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: 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: 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: 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: 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: Referential integrity ensures that relationships between tables remain consistent. Specifically, it guarantees that foreign keys in a table must match primary keys in another table, or they must be NULL.

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 view is a virtual table created by querying data from one or more tables. It does not store data itself but presents it in a specific format. Use cases: Simplify complex queries: A view can encapsulate complex queries for easier reuse. Data security: Views can restrict access to certain columns or rows for users without giving them full table access.

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: transaction in SQL is a sequence of operations performed as a single unit of work. Transactions ensure that database operations are performed atomically. Lifecycle:

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: Can you explain the transaction lifecycle? A transaction in SQL is a sequence of operations performed as a single unit of work. Transactions ensure that database operations are performed atomically. Lifecycle:

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: Clustered Index: The data is stored in the order of the index. A table can have only one clustered index because the rows can only be ordered in one way. Non-clustered Index: The index is separate from the data, and the index contains pointers to the data. A table can have multiple non-clustered indexes.

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: A subquery is a query within a query. It is used to retrieve data that will be used in the main query. Use case: Filtering: When the result of a subquery is used to filter data in the outer query. Aggregation: When the result of the subquery is used in aggregate functions.

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: UNION: Combines the result sets of two or more queries and removes duplicate rows. UNION ALL: Combines the result sets of two or more queries and includes all rows, even duplicates. Joins & 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: 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: 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.

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: 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: TOP (n) returns first n rows (optionally WITH TIES). OFFSET/FETCH is ANSI-style paging and requires ORDER BY. For keyset paging at scale, prefer seek-based pagination over deep OFFSET.

Sample solution

-- Page 3 of 10
SELECT EmpId, Name
FROM Employees
ORDER BY EmpId
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

SELECT TOP (10) WITH TIES Name, Salary
FROM Employees
ORDER BY Salary DESC;

Deep OFFSET on large tables is expensive — mention keyset paging as a follow-up.

Short answer: When rows relate to other rows in the same table — classic employee/manager, find peers in same department, or compare a row to another version.

Sample solution

SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees e
LEFT JOIN Employees m ON e.ManagerId = m.EmpId;

Always alias both sides clearly (e/m) — interviewers flag missing aliases.

Short answer: ISNULL(a,b) is T-SQL specific, two arguments, return type biased to first argument. COALESCE is ANSI, many arguments, type precedence rules. Prefer COALESCE for portability; know ISNULL for legacy code.

Interview gotcha: ISNULL truncates based on first argument’s type length.

Short answer: GROUP BY Email HAVING COUNT(*) > 1.

Sample solution

SELECT Email, COUNT(*) AS Cnt
FROM Employees
GROUP BY Email
HAVING COUNT(*) > 1
ORDER BY Cnt DESC;

HAVING filters groups; WHERE filters rows before grouping — say that clearly.