Interview Q&A

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: Aggregate functions perform a calculation on a set of values and return a single value. Common aggregate functions include: COUNT(): Returns the number of rows. SUM(): Returns the sum of a column. AVG(): Returns the average of a column. MAX(): Returns the maximum value. MIN(): Returns the minimum value.

Example code

SELECT AVG(salary) FROM 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: Data Storage: Table: Stores actual data in the database.

Explain a bit more

View: A virtual table that shows data from one or more tables but does not store data. It derives its data from the underlying tables each time it is queried. Modifications: Table: You can insert, update, or delete data directly. View: You cannot modify data in a view unless certain conditions are met. Some views (those based on a single table) are updatable, while others (especially those with JOINs, aggregations, or complex logic) are not. Performance: Table: Generally optimized for performance with indexes, data distribution, and so on. View: May have slower performance due to the query being executed each time the view is accessed, especially if it's a complex view or involves large datasets.

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: ccess to the data without revealing the full database schema. Security: By granting access to a view instead of a table, you can restrict access to sensitive data and only expose the columns or rows that are necessary.

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: Simplifies Complex Queries: Views can encapsulate complex queries, making them reusable and easier to manage.

Explain a bit more

Data Abstraction: Views abstract the underlying table structure and allow for easier access to the data without revealing the full database schema. Security: By granting access to a view instead of a table, you can restrict access to sensitive data and only expose the columns or rows that are necessary. Consistency: A view can standardize how data is accessed across multiple queries or applications, ensuring consistent results. Reduced Redundancy: Rather than writing the same complex query multiple times, you can create a view to encapsulate it and reuse the view in different 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: Yes, views can be updated in SQL, but there are conditions for when this is possible: Updatable Views: A view is updatable if it: Is based on a single table. Does not contain aggregation functions like COUNT(), AVG(), or SUM().

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: Under what conditions? Yes, views can be updated in SQL, but there are conditions for when this is possible: Updatable Views: A view is updatable if it: Is based on a single table. Does not contain aggregation functions like COUNT(), AVG(), or SUM(). Does not include DISTINCT, GROUP BY, JOIN, or other complex operations that modify the set of rows. Non-Updatable Views: Views that use complex JOINs, aggregations, or…

Explain a bit more

GROUP BY clauses are typically non-updatable. You can still modify data through these views by using triggers, such as INSTEAD OF triggers, which allow you to define custom behavior for updating or deleting rows. Example of an updatable view: CREATE VIEW simple_view AS SELECT id, name, salary FROM employees; - You can now update 'simple_view' directly: UPDATE simple_view SET salary = 60000 WHERE id = 101;

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: re certain DBMS-specific features and optimizations (e.g., indexed views in SQL Server).

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: Here’s how you can create a view in each of these DBMS: SQL Server: CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition; PostgreSQL: CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition; MySQL: CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition; The syntax for creating a view is generally the same across these databases, although…

Explain a bit more

there are certain DBMS-specific features and optimizations (e.g., indexed views in SQL Server).

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 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: Filter OrderDate from Jan 1 of current year, GROUP BY CustomerId, SUM(Amount). Or use a window with PARTITION BY CustomerId and a date filter.

Sample solution

DECLARE @Start DATE = DATEFROMPARTS(YEAR(GETDATE()), 1, 1);
SELECT CustomerId, SUM(Amount) AS YtdSales
FROM Orders
WHERE OrderDate >= @Start AND OrderDate < DATEADD(DAY, 1, CAST(GETDATE() AS date))
GROUP BY CustomerId;

Half-open date ranges [start, end) avoid time-of-day bugs.

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.

Short answer: ROW_NUMBER() OVER (PARTITION BY group ORDER BY ...) then filter rn = n in an outer query.

Sample solution

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY DepartmentId ORDER BY Salary DESC
    ) AS rn
    FROM Employees
) t
WHERE rn = 2;

This pattern replaces many correlated TOP 1 subqueries.

Short answer: Use EXCEPT, FULL OUTER JOIN on keys WHERE one side IS NULL, or NOT EXISTS. EXCEPT is concise for identical column lists.

Sample solution

-- In A but not B
SELECT EmpId FROM EmployeesA
EXCEPT
SELECT EmpId FROM EmployeesB;

EXCEPT compares full row projections — align columns carefully.

Short answer: Normal = does not match NULLs. Use IS NULL / IS NOT NULL, or ISNULL/COALESCE for display defaults. For “both NULL or equal”, use EXISTS patterns or INTERSECT, or (a = b OR (a IS NULL AND b IS NULL)).

Three-valued logic (TRUE/FALSE/UNKNOWN) is a classic theory follow-up.

Short answer: Use UNPIVOT or CROSS APPLY (VALUES ...) to turn columns into attribute/value rows — handy for EAV-style reporting.

Sample solution

SELECT EmpId, Metric, Val
FROM EmployeesWide
CROSS APPLY (VALUES
    ('Salary', Salary),
    ('Bonus', Bonus),
    ('Allowance', Allowance)
) v(Metric, Val);

VALUES unpivot is often clearer than the UNPIVOT operator.

Short answer: Do not wrap the column: avoid WHERE YEAR(OrderDate)=2025. Use a range filter such as OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01' so an index can be used.

Sargable = search argument able — keep functions on the constant side.

Short answer: BEGIN TRAN; work; COMMIT. On error, ROLLBACK inside CATCH. Check @@TRANCOUNT / XACT_STATE(). Mention ACID briefly.

Sample solution

BEGIN TRY
    BEGIN TRAN;
    -- DML here
    COMMIT TRAN;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRAN;
    THROW;
END CATCH;

THROW (not old-style RAISERROR alone) preserves error details in modern T-SQL.

Short answer: ROW_NUMBER() PARTITION BY CustomerId ORDER BY OrderDate DESC and filter rn = 1. Alternatively CROSS APPLY TOP 1.

Sample solution

SELECT CustomerId, OrderId, OrderDate, Amount
FROM (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY CustomerId ORDER BY OrderDate DESC, OrderId DESC
    ) AS rn
    FROM Orders
) t
WHERE rn = 1;

Add a deterministic tie-breaker (OrderId) when dates collide.