Interview Q&A

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: 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: 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: Window functions allow you to perform calculations across a set of table rows that are related to the current row, without collapsing the result set into a single row.

Example code

ROW_NUMBER() generates a sequential integer to each row within the result set. Example: SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num FROM employees; This query adds a sequential row number to each employee, ordered by salary.

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

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: Divide each Amount by SUM(Amount) OVER () and multiply by 100. Cast carefully to avoid integer division.

Sample solution

SELECT ProductId, Amount,
       CAST(100.0 * Amount / SUM(Amount) OVER () AS DECIMAL(5,2)) AS PctOfTotal
FROM Sales;

Use 100.0 (not 100) to force decimal math.

Short answer: Use STRING_AGG(expression, separator) WITHIN GROUP (ORDER BY ...) on modern SQL Server. Older trick: FOR XML PATH.

Sample solution

SELECT DepartmentId,
       STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name) AS Employees
FROM Employees
GROUP BY DepartmentId;

Mention STRING_AGG availability (SQL Server 2017+) if the environment might be older.