Interview Q&A

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: A FULL OUTER JOIN returns all rows from both tables, matching rows where possible. If there's no match, the result will contain NULL values for the columns from the table that doesn't have a match. Use case: When you want to combine all records from both tables, regardless of whether they match, and include NULL where no match exists.

Example code

SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.id = table2.id;

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 JOIN condition specifies the columns that will be used to match rows between two or more tables. This condition typically uses ON or USING in SQL.

Example code

SELECT * FROM table1 JOIN table2 ON table1.id = table2.id; In this example, the condition table1.id = table2.id determines how the rows from both tables will be joined.

Real-world example (ShopNest)

An invoice query INNER JOINs Orders and OrderItems, and LEFT JOINs Discounts so orders without a coupon still appear.

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: WHERE: Filters rows before any grouping is done (i.e., filters individual records). HAVING: Filters records after grouping is done (i.e., filters grouped results).

Example code

SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000; In this query, HAVING is used to filter groups that have an average salary greater than 50,000.

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.

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.