Interview Q&A

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: A materialized view is a database object that stores the result of a query physically, similar to an indexed view but more generalized.

Explain a bit more

Unlike regular views, which generate their result dynamically when queried, materialized views store the result set and can be periodically refreshed. Advantages: Faster query performance, especially for complex queries or aggregations, since the data is precomputed and stored. Refresh: The data in a materialized view may become outdated over time, so it needs to be refreshed periodically, either manually or automatically, depending on the DBMS.

Example code

CREATE MATERIALIZED VIEW sales_summary AS SELECT product_id, SUM(sales) AS total_sales FROM sales GROUP BY product_id; - Refresh the materialized view when needed: REFRESH MATERIALIZED VIEW sales_summary; Materialized views are supported in systems like PostgreSQL and Oracle, but MySQL does not have a built-in materialized view feature. You can simulate one using tables and scheduled jobs.

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: To delete a view, you use the DROP VIEW statement. This removes the view from the database entirely.

Example code

DROP VIEW view_name; Be cautious when dropping a view, as it will no longer be available for use in queries. Ensure no other objects are dependent on the view before dropping it.

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: Optimizing views is important to ensure that your queries are efficient.

Explain a bit more

Here are some strategies: Avoid Complex Views: Avoid creating views with complex logic (e.g., multiple JOINs, GROUP BY, or subqueries), as they can result in slower performance.

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: BEGIN SELECT * FROM employees WHERE id = @emp_id; END; PostgreSQL: CREATE OR REPLACE FUNCTION GetEmployeeDetails(emp_id INT) RETURNS TABLE(id INT, name VARCHAR) AS $$ BEGIN RETURN QUERY SELECT id, name FROM employees WHERE id = emp_id; END; $$ LANGUAGE plpgsql; MySQL: DELIMITER // CREATE PROCEDURE GetEmployeeDetails(IN emp_id INT) BEGIN SELECT… * FROM……… employees WHERE id = emp_id; END // DELIMITER ; The syntax…

Explain a bit more

varies slightly between the databases, but the core idea remains the same.

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: CREATE PROCEDURE GetEmployeeDetails (@emp_id INT) AS BEGIN SELECT * FROM employees WHERE id = @emp_id; END; PostgreSQL: CREATE OR REPLACE FUNCTION GetEmployeeDetails(emp_id INT) RETURNS TABLE(id INT, name VARCHAR) AS $$ BEGIN RETURN QUERY SELECT id, name FROM employees WHERE id = emp_id; END; $$ LANGUAGE plpgsql; MySQL: DELIMITER // CREATE PROCEDURE GetEmployeeDetails(IN emp_id INT) BEGIN SELECT * FROM…

Explain a bit more

employees WHERE id = emp_id; END // DELIMITER ; The syntax varies slightly between the databases, but the core idea remains the same.

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

Short answer: Use STRING_SPLIT (SQL Server 2016+), or OPENJSON, or a numbers table. Note STRING_SPLIT historically lacked guaranteed order unless using the enable_ordinal parameter on newer versions.

Sample solution

SELECT LTRIM(RTRIM(value)) AS Tag
FROM STRING_SPLIT('sql,interview,t-sql', ',');

Ask about ordering requirements before picking STRING_SPLIT.

Short answer: Clustered index defines physical row order (one per table, often PK). Nonclustered is a separate structure pointing to heap/clustered keys. Queries filtering/sorting on clustered keys can avoid sorts and lookups.

Relate to query plans: Clustered Index Seek vs Key Lookup vs Bookmark Lookup.

Short answer: Compute each employee’s salary share within department via Salary / SUM(Salary) OVER (PARTITION BY DepartmentId).

Sample solution

SELECT EmpId, DepartmentId, Salary,
       CAST(100.0 * Salary / SUM(Salary) OVER (PARTITION BY DepartmentId) AS DECIMAL(5,2)) AS DeptPct
FROM Employees;

Window aggregates keep detail rows — unlike GROUP BY alone.

Short answer: DELETE with WHERE DepartmentId IN (departments where ManagerId IS NULL) or use JOIN. Preview with SELECT first.

Sample solution

DELETE e
FROM Employees e
INNER JOIN Departments d ON d.DepartmentId = e.DepartmentId
WHERE d.ManagerId IS NULL;

In interviews, always say you would run SELECT with the same FROM/WHERE before DELETE.