Interview Q&A

Short answer: A FULL OUTER JOIN returns all rows from both tables, matching rows where possible. If there's no match, the result will contain NULL values for the columns from the table that doesn't have a match. Use case: When you want to combine all records from both tables, regardless of whether they match, and include NULL where no match exists.

Example code

SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.id = table2.id;

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: A JOIN condition specifies the columns that will be used to match rows between two or more tables. This condition typically uses ON or USING in SQL.

Example code

SELECT * FROM table1 JOIN table2 ON table1.id = table2.id; In this example, the condition table1.id = table2.id determines how the rows from both tables will be joined.

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: WHERE: Filters rows before any grouping is done (i.e., filters individual records). HAVING: Filters records after grouping is done (i.e., filters grouped results).

Example code

SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000; In this query, HAVING is used to filter groups that have an average salary greater than 50,000.

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: 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: 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: 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: Gaps-and-islands: ROW_NUMBER() ordered by date, subtract from date to form an island key, then GROUP BY user + island key and count days.

Sample solution

WITH d AS (
    SELECT UserId, LoginDate,
           DATEADD(DAY, -ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY LoginDate), LoginDate) AS grp
    FROM UserLogins
)
SELECT UserId, MIN(LoginDate) AS StartDate, MAX(LoginDate) AS EndDate, COUNT(*) AS Days
FROM d
GROUP BY UserId, grp
HAVING COUNT(*) >= 3;

Naming “gaps and islands” immediately signals senior SQL fluency.

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: 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: Filter orders to the year, group by customer, and HAVING COUNT(DISTINCT month) = 12.

Sample solution

SELECT CustomerId
FROM Orders
WHERE OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01'
GROUP BY CustomerId
HAVING COUNT(DISTINCT DATEPART(MONTH, OrderDate)) = 12;

COUNT(DISTINCT month) is the key — COUNT(*) alone is wrong if multiple orders/month.

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: Use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary) OVER (), or pick middle ROW_NUMBER values for odd/even counts.

Sample solution

SELECT DISTINCT
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary) OVER () AS MedianSalary
FROM Employees;

PERCENTILE_CONT is continuous; PERCENTILE_DISC picks an actual data value.

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: 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: 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: ROLLUP creates subtotals and a grand total. GROUPING SETS lists exact aggregation levels. GROUPING(col) detects total rows (NULLs that mean “all”).

Sample solution

SELECT DepartmentId, JobTitle, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY ROLLUP (DepartmentId, JobTitle);

Use GROUPING() to distinguish real NULL dimension values from rollup NULLs.

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