Interview Q&A

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: 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 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: 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: And vice versa. This often requires a junction 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: One-to-One (1:1): Each row in one table is linked to one row in another table. One-to-Many (1:M): A row in one table can be linked to many rows in another table. Many-to-Many (M:N): Rows in one table can be linked to many rows in another table and vice versa. This often requires a junction 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: 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 composite index is an index that involves more than one column in a table. It's used when queries often filter or sort by multiple columns, optimizing performance for those specific queries.

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: INNER JOIN: Returns only the rows that have matching values in both tables. LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and the matching rows from the right table. If no match is found, NULLs are returned for columns from the right table.

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: 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: 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 PIVOT with aggregate + IN list of column values, or conditional aggregation with CASE (more portable and flexible).

Sample solution

-- Conditional aggregation (often preferred)
SELECT CustomerId,
       SUM(CASE WHEN Year = 2024 THEN Amount ELSE 0 END) AS Y2024,
       SUM(CASE WHEN Year = 2025 THEN Amount ELSE 0 END) AS Y2025
FROM Sales
GROUP BY CustomerId;

-- PIVOT operator
SELECT CustomerId, [2024], [2025]
FROM (SELECT CustomerId, Year, Amount FROM Sales) src
PIVOT (SUM(Amount) FOR Year IN ([2024], [2025])) p;

CASE aggregation is easier when pivot columns are dynamic.

Short answer: APPLY invokes a table-valued expression per outer row. CROSS APPLY is like INNER JOIN (outer row must produce rows). OUTER APPLY is like LEFT JOIN (keeps outer rows with NULLs). Useful for TOP N per group and TVFs.

Sample solution

-- Top 2 orders per customer
SELECT c.CustomerId, c.Name, o.OrderId, o.Amount
FROM Customers c
CROSS APPLY (
    SELECT TOP (2) OrderId, Amount
    FROM Orders o
    WHERE o.CustomerId = c.CustomerId
    ORDER BY Amount DESC
) o;

TOP N per group via CROSS APPLY is a classic SQL Server interview flex.

Short answer: MERGE matches source to target ON a key, then WHEN MATCHED UPDATE, WHEN NOT MATCHED INSERT. Discuss concurrency and that some teams prefer separate UPDATE/INSERT.

Sample solution

MERGE INTO Employees AS t
USING (SELECT @EmpId AS EmpId, @Name AS Name, @Salary AS Salary) AS s
ON t.EmpId = s.EmpId
WHEN MATCHED THEN
    UPDATE SET Name = s.Name, Salary = s.Salary
WHEN NOT MATCHED THEN
    INSERT (EmpId, Name, Salary) VALUES (s.EmpId, s.Name, s.Salary);

Know that MERGE has historically had race/edge-case caveats — saying so shows maturity.

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: 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.

Short answer: T-SQL supports UPDATE ... FROM with JOINs. Be careful with one-to-many joins (nondeterministic updates). Prefer MERGE or ensure uniqueness.

Sample solution

UPDATE e
SET e.DepartmentName = d.Name
FROM Employees e
INNER JOIN Departments d ON d.DepartmentId = e.DepartmentId;

Mention nondeterministic update risk if multiple matched rows exist.

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.