Interview Q&A

Short answer: LEFT JOIN: Returns all records from the left table, and matched records from the right table. If there's no match, NULL values are returned for the right table's columns. RIGHT JOIN: Returns all records from the right table, and matched records from the left table. If there's no match, NULL values are returned for the left table's columns.

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: The UNION ALL operator combines the result sets of two or more queries, but unlike UNION, it does not remove duplicate rows.

Example code

SELECT name FROM employees UNION ALL SELECT name FROM contractors; This will return all names from both employees and contractors, including duplicates.

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 subquery in the FROM clause allows you to treat the result of a query as a temporary table, which can then be joined or queried further.

Example code

SELECT avg_salary FROM (SELECT AVG(salary) AS avg_salary FROM employees) AS avg_table; This query calculates the average salary using a subquery in the FROM clause. Views

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 in SQL is a virtual table that is defined by a SELECT query. It does not store data itself but rather provides a way to view and work with the results of a query as if it were a table. Views allow users to access specific, formatted data without modifying the underlying tables directly. Purpose: Simplify complex queries, abstract the database schema, and improve security by exposing only the necessary data.

Example code

CREATE VIEW employee_view AS SELECT id, name, department FROM employees; You can then query the view like a regular table: SELECT * FROM employee_view;

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: An indexed view (also called a materialized view) in SQL Server is a view that has a unique clustered index created on it.

Explain a bit more

This results in the view storing the query results physically in the database, similar to a table. It is useful for improving performance when you have complex queries that aggregate data, as the results of the view are precomputed and stored. Benefits: Faster read performance for complex aggregations or queries. Reduces the need for recomputing the result of a complex query each time the view is accessed. Downside: Insert, update, or delete operations on the underlying tables will incur additional overhead because the indexed view must also be updated.

Example code

CREATE VIEW employee_summary AS SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department; - Create a clustered index on the view CREATE UNIQUE CLUSTERED INDEX idx_employee_summary ON employee_summary(department);

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: 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: Talk about DMVs: sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_os_waiting_tasks, and reading plans. For query writing interviews, also discuss indexes, sargability, and avoiding SELECT *.

Sample solution

SELECT r.session_id, r.status, r.command, r.wait_type, r.blocking_session_id,
       t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id <> @@SPID;

Even app developers score points naming DMVs and “parameter sniffing” briefly.

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: A covering index includes all columns a query needs (key + INCLUDE), enabling an Index Seek/Scan without Key Lookup. In plans, look for absence of Key Lookup and lower estimated cost.

Offer: CREATE INDEX ... INCLUDE (ColA, ColB) as the practical answer.

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: Assign ROW_NUMBER() then filter rn % 2 = 1 for odd rows. Useful in puzzles; rare in production.

Sample solution

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY EmpId) AS rn
    FROM Employees
) t
WHERE rn % 2 = 1;

Clarify ordering — “odd rows” is meaningless without ORDER BY.

Short answer: JOIN children, GROUP BY parent key, HAVING COUNT(*) >= N.

Sample solution

SELECT d.DepartmentId, d.Name, COUNT(*) AS EmpCount
FROM Departments d
INNER JOIN Employees e ON e.DepartmentId = d.DepartmentId
GROUP BY d.DepartmentId, d.Name
HAVING COUNT(*) >= 5;

INNER JOIN excludes departments with zero employees — use LEFT JOIN if zeros matter.

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: DELETE removes rows (logged, can WHERE, fires triggers). TRUNCATE deallocates pages (minimal logging, resets IDENTITY, needs permissions, almost no WHERE). DROP removes the table object.

Identity reset on TRUNCATE is a favorite follow-up.

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 AVG(Amount) OVER (ORDER BY OrderDate ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).

Sample solution

SELECT OrderDate, Amount,
       AVG(Amount * 1.0) OVER (
           ORDER BY OrderDate
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS MovingAvg7
FROM DailySales;

ROWS vs RANGE matters when OrderDate has duplicates.

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.