Interview Q&A

Short answer: IN: Checks whether a value is present in a list or a subquery’s result set.

Explain a bit more

EXISTS: Checks whether a subquery returns any rows, returning TRUE if the subquery returns one or more rows, otherwise FALSE. Example with IN: SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name = 'HR'); Example with EXISTS: SELECT name FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE d.id = e.department_id AND d.name = 'HR');

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: The BETWEEN operator filters the result set within a given range. It is inclusive, meaning it includes the boundary values.

Example code

SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000; This query will return employees whose salaries are between 40,000 and 60,000 (inclusive).

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: The LIKE operator is used to search for a specified pattern in a column. % matches any sequence of characters. _ matches a single character.

Example code

SELECT * FROM employees WHERE name LIKE 'J%n'; -- Names that start with 'J' and end with 'n'

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: COUNT(*): Counts all rows, including rows with NULL values in any column. COUNT(column_name): Counts only non-NULL values in the specified column.

Example code

SELECT COUNT(*) FROM employees; -- Counts all rows SELECT COUNT(salary) FROM employees; -- Counts rows where salary is NOT 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: Conditional aggregation allows you to apply aggregate functions to a subset of data that meets a certain condition. This is usually done with a CASE expression.

Example code

SELECT department, SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count, SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count FROM employees GROUP BY department; This query counts the number of male and female employees in each department.

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 Common Table Expression (CTE) is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs are often used for organizing complex queries.

Example code

WITH EmployeeCTE AS ( SELECT name, salary FROM employees WHERE salary > 50000 SELECT * FROM EmployeeCTE;

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: The CASE statement provides conditional logic in SQL queries. It returns a value based on certain conditions.

Example code

SELECT name, CASE WHEN salary > 70000 THEN 'High' WHEN salary BETWEEN 40000 AND 70000 THEN 'Medium' ELSE 'Low' END AS salary_range 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: 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: 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: And query data without modifying the underlying tables directly. Views do not support procedural logic, loops, or variables.

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: View: A view is a virtual table defined by a SELECT query.

Explain a bit more

It provides a way to view and query data without modifying the underlying tables directly. Views do not support procedural logic, loops, or variables. Stored Procedure: A stored procedure is a set of SQL statements that can be executed as a single unit. It can include complex logic like loops, conditionals, and multiple queries. Stored procedures can perform INSERT, UPDATE, DELETE, and other operations on the database. They can also return results. Example of a stored procedure: CREATE PROCEDURE GetEmployeeSalary(IN emp_id INT) BEGIN SELECT salary FROM employees WHERE id = emp_id; END;

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 stored procedure is a precompiled collection of SQL statements that can be executed as a single unit.

Explain a bit more

It allows you to encapsulate logic, such as data manipulation, complex queries, or repetitive tasks, into reusable blocks. Stored procedures can accept parameters, perform actions like SELECT, INSERT, UPDATE, and DELETE, and return results. Benefits: Improved performance (since the procedure is precompiled), code reusability, centralized logic, and security (by restricting direct access to tables).

Example code

CREATE PROCEDURE GetEmployeeDetails (IN emp_id INT) BEGIN SELECT * FROM employees WHERE id = emp_id; END;

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: Function: Return Type: Always returns a value (can be scalar or table).

Explain a bit more

Used in Queries: Can be used in SELECT, WHERE, and ORDER BY clauses. Side Effects: Typically designed to perform calculations or return a result without modifying the database. Stored Procedure: No Return Type: May or may not return values (using output parameters or result sets). Used for Actions: Typically used for performing database operations (like INSERT, UPDATE, DELETE). Side Effects: Designed to perform operations that modify data or perform business logic.

Example code

Function (returns a value): CREATE FUNCTION GetEmployeeSalary (emp_id INT) RETURNS DECIMAL BEGIN RETURN (SELECT salary FROM employees WHERE id = emp_id); END; Stored Procedure (performs an action): CREATE PROCEDURE UpdateEmployeeSalary (IN emp_id INT, IN new_salary DECIMAL) BEGIN UPDATE employees SET salary = new_salary WHERE id = emp_id; END;

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: Stored Procedures: The RETURN keyword in a stored procedure is typically used to exit the procedure and can optionally return an integer value (usually indicating the success or failure of the procedure). The return value is often used for error handling or status reporting. Indexing

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 index in SQL is a database object that improves the speed of data retrieval operations on a table.

Explain a bit more

It works by maintaining a sorted order of column values in a separate structure, allowing for faster search and retrieval compared to scanning every row in the table. The index essentially creates a quick lookup table to find specific values. Example of an index creation: CREATE INDEX idx_column_name ON table_name(column_name); Indexes are particularly useful for queries that involve WHERE, JOIN, ORDER BY, and GROUP BY clauses.

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: Clustered Index: The data in the table is physically sorted based on the clustered index.

Explain a bit more

Each table can have only one clustered index (usually the primary key). Fast for queries that involve range-based retrieval (e.g., BETWEEN or ORDER BY). Non-Clustered Index: The data in the table is not physically sorted. The non-clustered index is stored separately from the actual data. A table can have multiple non-clustered indexes. Faster for point lookups (single value queries) but less efficient for range-based searches.

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 removes duplicates (extra sort/distinct cost). UNION ALL keeps all rows and is faster when duplicates are impossible or acceptable.

Default to UNION ALL unless you truly need distinct.

Short answer: Filter HireDate >= DATEADD(DAY, -30, CAST(GETDATE() AS date)). Prefer sargable predicates — avoid wrapping the column in functions when possible.

Sample solution

SELECT EmpId, Name, HireDate
FROM Employees
WHERE HireDate >= DATEADD(DAY, -30, CAST(SYSUTCDATETIME() AS date));

Mention SYSUTCDATETIME vs GETDATE and time-zone awareness for global apps.

Short answer: GROUP BY ManagerId, COUNT(*), order descending, TOP 1. Join back for manager name.

Sample solution

SELECT TOP (1) m.EmpId, m.Name, COUNT(*) AS DirectReports
FROM Employees e
INNER JOIN Employees m ON e.ManagerId = m.EmpId
GROUP BY m.EmpId, m.Name
ORDER BY DirectReports DESC;

Ask how to break ties — TOP 1 WITH TIES may be desired.

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