SQL Interview Questions | Gate Smashers

Write a query to display all Engineering employees ordered by salary from highest to lowest?

Interview-ready answer

SELECT employee_name, salary FROM Employees WHERE department = 'Engineering' ORDER BY salary DESC;

Employees

employee_id	employee_name	department	salary	manager_id
101	Aarav	Engineering	95000	105
102	Meera	Engineering	72000	105
103	Kabir	Sales	65000	106
104	Isha	Sales	92000	106
105	Rohan	Engineering	90000	NULL
106	Neha	Sales	88000	NULL

SQL querysql

SELECT employee_name, salary
FROM Employees
WHERE department = 'Engineering'
ORDER BY salary DESC;

Understand it clearly

Direct answer

Filter the Employees table to Engineering rows and sort the result by salary in descending order. The SQL query below returns employee_name and salary for Engineering employees with the highest salaries first.

How it works

The query uses a WHERE clause to restrict rows to the Engineering department and an ORDER BY clause to place the highest salary first.

Filter: WHERE removes employees from other departments before the final result is returned.
Sort: ORDER BY salary DESC places the highest salary first.

Dataset / Schema

The query operates on the Employees table which contains the following columns: employee_id, employee_name, department, salary, manager_id. The sample data from the supplied material is shown in the table block below.

Deterministic ordering (note)

If multiple employees share the same salary and you need a deterministic result order, add a second sort key such as employee_id to ORDER BY (for example: ORDER BY salary DESC, employee_id).

Write a query to find the number of employees in each department?

Interview-ready answer

SELECT department, COUNT(*) AS employee_count FROM Employees GROUP BY department;

Employees

employee_id	employee_name	department	salary	manager_id
101	Aarav	Engineering	95000	105
102	Meera	Engineering	72000	105
103	Kabir	Sales	65000	106
104	Isha	Sales	92000	106
105	Rohan	Engineering	90000	NULL
106	Neha	Sales	88000	NULL

SQL querysql

SELECT department, COUNT(*) AS employee_count
FROM Employees
GROUP BY department;

Understand it clearly

Direct query

Group employees by the department and count rows per group. The query to use is shown in the code block below; it returns one row per department that exists in the Employees table, with the number of employees in that department.

How it works

GROUP BY creates one group for every distinct department value in the Employees table. COUNT(*) then counts every row in each group, producing the employee count for that department.

GROUP BY: Produces one group per distinct department value.
**COUNT(*):** Counts every row in each group (includes rows even if unrelated columns are NULL).

Departments with no employees

If you need to show departments that have zero employees, start from a Departments table and LEFT JOIN Employees so that departments without matching employees still appear with a count of zero (the supplied query groups only rows present in Employees).

When to use: Use a LEFT JOIN from Departments to Employees when you must include departments that currently have no employees.

NULL handling

COUNT(*) counts rows regardless of NULL values in other columns. That means rows with NULLs in unrelated columns still contribute to the department count.

Quick comparison

BasisGrouping from Employees (only departments present in Employees)Including empty departments (Departments LEFT JOIN Employees)

Result includes departments with no employeesNo — only departments that appear in Employees are returnedYes — starting from Departments and LEFT JOIN will keep departments with zero employees

Requires a Departments tableNoYes — you must have a Departments table to include empty departments

Counting behaviorCOUNT(*) counts every row in each group, including rows with NULLs in unrelated columnsSame counting behavior for matched/returned rows; unmatched departments will show zero when you COUNT(Employees.employee_id) or use COALESCE on the count

Write a query to find the second-highest distinct salary?

Interview-ready answer

Use the maximum salary that is less than the overall maximum: SELECT MAX(salary) AS second_highest_salary FROM Employees WHERE salary < (SELECT MAX(salary) FROM Employees); This returns the second-highest distinct salary (NULL if fewer than two distinct salaries exist).

Employees

employee_id	employee_name	department	salary	manager_id
101	Aarav	Engineering	95000	105
102	Meera	Engineering	72000	105
103	Kabir	Sales	65000	106
104	Isha	Sales	92000	106
105	Rohan	Engineering	90000	NULL
106	Neha	Sales	88000	NULL

SQL querysql

SELECT MAX(salary) AS second_highest_salary
FROM Employees
WHERE salary < ( SELECT MAX(salary) FROM Employees );

Understand it clearly

Query (direct answer)

Find the second-highest distinct salary by selecting the maximum salary that is strictly less than the overall maximum salary. The query below implements this approach and returns NULL when fewer than two distinct salaries exist.

Query: See the SQL code block in Content Blocks for the exact statement.

How it works

The solution uses a subquery to determine the overall maximum salary, then the outer query finds the largest salary smaller than that value. This ensures the result is the second-highest distinct salary rather than a tied value equal to the maximum.

Inner query: Finds the highest salary: (SELECT MAX(salary) FROM Employees).
Outer query: Finds the maximum salary where salary is less than the value returned by the inner query.

Edge case

If the table contains fewer than two distinct salary values, the WHERE filter excludes all rows and the aggregate MAX returns NULL. That indicates no second-highest distinct salary exists.

Result when insufficient distinct salaries: NULL

When to prefer DENSE_RANK

Use DENSE_RANK (or similar window functions) when you need to return employee details along with the nth-highest salary or when requesting arbitrary ranks (e.g., 3rd highest). DENSE_RANK handles ties and makes it easy to select rows with a specific rank rather than only the scalar salary value.

Use-case: Return employee rows or nth-highest values; handle ties explicitly.

Write a query to find employees earning more than the company average salary?

Interview-ready answer

Compute the company average with a scalar subquery and return employees whose salary exceeds it: SELECT employee_name, salary FROM Employees WHERE salary > ( SELECT AVG(salary) FROM Employees );

Employees (sample)

employee_id	employee_name	department	salary	manager_id
101	Aarav	Engineering	95000	105
102	Meera	Engineering	72000	105
103	Kabir	Sales	65000	106
104	Isha	Sales	92000	106
105	Rohan	Engineering	90000	NULL
106	Neha	Sales	88000	NULL

SQL querysql

SELECT employee_name, salary
FROM Employees
WHERE salary > (
  SELECT AVG(salary)
  FROM Employees
);

Understand it clearly

Direct answer (query)

Use a scalar subquery to calculate the average salary for the whole table, then filter employees whose salary is greater than that average. The exact query is provided in the code block below.

How it works

The query uses an uncorrelated scalar subquery that returns a single AVG(salary) value for the entire Employees table. The outer WHERE clause compares each employee's salary to that single average value and returns only those exceeding it.

Subquery: Returns one AVG(salary) value for the complete table.
Outer filter: Compares each employee's salary with the subquery result and keeps those greater than the average.

NULL salary behavior

AVG ignores NULL salaries by default. If your business rules require treating NULL as zero or handling them differently, explicitly apply COALESCE or other logic before averaging; otherwise, do not assume NULL represents zero.

Sample data reference

A sample Employees table is provided below to illustrate the dataset the query runs against. The code block contains the exact SQL statement shown earlier.

Write a query to find the highest salary in each department?

Interview-ready answer

Group rows by department and calculate MAX(salary) for each group. SQL: SELECT department, MAX(salary) AS highest_salary FROM Employees GROUP BY department;

Employees

employee_id	employee_name	department	salary	manager_id
101	Aarav	Engineering	95000	105
102	Meera	Engineering	72000	105
103	Kabir	Sales	65000	106
104	Isha	Sales	92000	106
105	Rohan	Engineering	90000	NULL
106	Neha	Sales	88000	NULL

SQL querysql

SELECT department, MAX(salary) AS highest_salary
FROM Employees
GROUP BY department;

Understand it clearly

Direct answer

Group rows by department and compute the maximum salary for each group. The query returns one row per department with that department's highest salary.

How it works

The query aggregates rows by the department column and applies the MAX() aggregate function to the salary column to produce the highest salary per department.

Result: Returns the highest salary value per department (one row per department).
Limitations: Does not return which employee(s) earn that salary — only the salary value.

Returning employee details (preserve ties)

If you need the employee(s) who earn the highest salary in each department (including ties), use a window function such as DENSE_RANK() partitioned by department, or compare each employee's salary to the department maximum via a join or subquery.

Option 1: DENSE_RANK() over (partition by department order by salary DESC) to pick top-ranked employees and preserve ties.
Option 2: Join Employees to an aggregated subquery that selects department and MAX(salary) to filter employees whose salary equals the department maximum.

Sample data

The following Employees table is the dataset referenced by the query. Use it to validate results or to construct queries that return employee details along with the department maximums.

Write a query to find employees whose salary is higher than their manager's salary?

Interview-ready answer

Use a self-join on the Employees table to compare each employee’s salary with their manager’s salary. The query joins Employees to itself on e.manager_id = m.employee_id and filters where e.salary > m.salary (see SQL block below).

Employees

employee_id	employee_name	department	salary	manager_id
101	Aarav	Engineering	95000	105
102	Meera	Engineering	72000	105
103	Kabir	Sales	65000	106
104	Isha	Sales	92000	106
105	Rohan	Engineering	90000	NULL
106	Neha	Sales	88000	NULL

SQL querysql

SELECT e.employee_name, e.salary, m.employee_name AS manager_name, m.salary AS manager_salary
FROM Employees AS e
JOIN Employees AS m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

Understand it clearly

Interview‑ready answer (query)

Compare each employee to their manager by self‑joining Employees and selecting employees whose salary is greater than their manager’s salary. The exact SQL is provided in the code block below.

Selected columns: e.employee_name, e.salary, m.employee_name AS manager_name, m.salary AS manager_salary

How it works

This uses a self‑join: the Employees table is aliased twice

once for the employee row (e) and once for the manager row (m). The join condition e.manager_id = m.employee_id pairs each employee with their manager row in the same table. After the join, the WHERE clause e.salary > m.salary keeps only those employee rows whose salary exceeds the matched manager’s salary.

Join condition: e.manager_id = m.employee_id — links an employee to their manager row.
Filter: WHERE e.salary > m.salary — returns only employees paid more than their manager.

Behavior for employees without managers

An INNER JOIN is used in this query, so employees with a NULL manager_id (no manager) are excluded automatically because there is no matching manager row. If you wanted to include those employees for inspection, use a LEFT JOIN instead and handle NULL manager rows in the WHERE or HAVING logic.

Current behavior: Employees with no manager are excluded (inner join).
Alternative: Use LEFT JOIN to include employees without managers for additional checks.

Sample data (reference)

The Employees table sample used to illustrate this query is shown below.

Note: Do not repeat the full dataset here — the table block below contains the exact rows used.

Write a query to display each order with the customer name?

Interview-ready answer

SELECT o.order_id, c.customer_name, o.amount, o.status FROM Orders AS o JOIN Customers AS c ON o.customer_id = c.customer_id; -- Joins Orders to Customers using customer_id to display each order with the customer name. Use LEFT JOIN instead of JOIN if you need to include orders without a matching customer.

Customers

customer_id	customer_name	city
1	Aditi Sharma	Delhi
2	Rahul Verma	Mumbai
3	Simran Kaur	Chandigarh
4	Arjun Nair	Bengaluru

Orders

order_id	customer_id	amount	status
501	1	2400	Delivered
502	1	3200	Delivered
503	2	1800	Delivered
504	2	4100	Pending
505	3	950	Cancelled

SQL querysql

SELECT o.order_id, c.customer_name, o.amount, o.status
FROM Orders AS o
JOIN Customers AS c ON o.customer_id = c.customer_id;

Understand it clearly

Direct query

Use an equi-join between Orders and Customers on the customer_id column. The query returns order-level details together with the customer's name. See the SQL code block in the content blocks below for the exact statement.

Relationship key

customer_id is the relationship key linking the two tables: Orders.customer_id corresponds to Customers.customer_id.

Key: customer_id ties Orders to Customers.
Returned columns: order_id, customer_name, amount, status (as shown in the query).

Join behavior and when to use

The supplied query uses an INNER JOIN (written as JOIN) which returns only orders that have a matching customer row. If you need to preserve orders that lack a matching customer (orphaned orders), use a LEFT JOIN with Orders as the left table.

INNER JOIN: Returns only orders with a matching customer.
LEFT JOIN: Returns all orders; customer columns will be NULL where no matching customer exists.

Sample data

The following tables are the sample Customers and Orders data referenced by the query. Use them to verify the query's behavior.

Quick comparison

BasisINNER JOINLEFT JOIN

Returned rowsOnly orders that have a matching customer row.All orders; unmatched customers show NULL for customer columns.

When to useWhen you only want orders with existing customers.When you must keep orphaned orders visible even if customer is missing.

Write a query to find customers who have never placed an order?

Interview-ready answer

Use a LEFT JOIN from Customers to Orders and keep rows where the joined order is NULL. Example: SELECT c.customer_id, c.customer_name FROM Customers AS c LEFT JOIN Orders AS o ON c.customer_id = o.customer_id WHERE o.order_id IS NULL;

Customers

customer_id	customer_name	city
1	Aditi Sharma	Delhi
2	Rahul Verma	Mumbai
3	Simran Kaur	Chandigarh
4	Arjun Nair	Bengaluru

Orders

order_id	customer_id	amount	status
501	1	2400	Delivered
502	1	3200	Delivered
503	2	1800	Delivered
504	2	4100	Pending
505	3	950	Cancelled

SQL querysql

SELECT c.customer_id, c.customer_name
FROM Customers AS c
LEFT JOIN Orders AS o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Understand it clearly

Direct answer (query)

Left-join every customer to Orders and retain only those rows where no order matched (order_id is NULL). The exact SQL is provided in the code block below.

Approach & rationale

A LEFT JOIN preserves all rows from the left table (Customers). When no matching Orders row exists for a customer, the Orders columns become NULL; filtering WHERE o.order_id IS NULL returns customers with no orders. It is safer to test a non-nullable key (o.order_id) rather than a business column that may itself be NULL, to avoid false positives.

Preserve all customers: LEFT JOIN keeps every customer row regardless of matching orders.
Detect no match: WHERE o.order_id IS NULL filters customers with no corresponding Orders row.
Key selection: Test a non-nullable key (order_id) for NULL rather than a nullable business column.

Alternative

NOT EXISTS is an equally valid and often very clear alternative to the LEFT JOIN pattern. Use whichever form matches your team’s style or is more readable in the given context; both express the intent of finding customers without orders.

Alternative pattern: Use NOT EXISTS with a correlated subquery as a clear alternative.

Example data (reference)

The following sample Customers and Orders tables illustrate the data used with the query. See the code block for the exact SQL.

Quick comparison

BasisLEFT JOINNOT EXISTS

MechanismJoin customers to orders; filter where joined order row is NULL.Correlated subquery that tests for absence of matching orders.

Null-safetyRelies on NULL in joined row; test a non-nullable key to be safe.Does not rely on NULLs; explicitly checks for non-existence.

ReadabilityStraightforward when showing joined data; intent may be slightly implicit.Often very clear and explicit about absence, per supplied material.

Write a query to find customers who placed more than one order?

Interview-ready answer

Join Customers to Orders, GROUP BY the customer (customer_id and customer_name) and use HAVING COUNT(*) > 1 to keep only customers with more than one order.

Customers

customer_id	customer_name	city
1	Aditi Sharma	Delhi
2	Rahul Verma	Mumbai
3	Simran Kaur	Chandigarh
4	Arjun Nair	Bengaluru

Orders

order_id	customer_id	amount	status
501	1	2400	Delivered
502	1	3200	Delivered
503	2	1800	Delivered
504	2	4100	Pending
505	3	950	Cancelled

SQL querysql

SELECT c.customer_id, c.customer_name, COUNT(*) AS order_count
FROM Customers AS c
JOIN Orders AS o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(*) > 1;

Understand it clearly

Direct answer

Join the Customers and Orders tables, group by the customer, and filter groups whose order count is greater than one using HAVING COUNT(*) > 1.

Core query pattern: JOIN → GROUP BY customer_id, customer_name → HAVING COUNT(*) > 1

Query explained step-by-step

The query joins Customers to Orders to associate each order with its customer, groups the joined rows per customer, computes the number of orders per customer with COUNT(*), and then filters to retain only groups where that count exceeds one.

Join: Match Customers.customer_id to Orders.customer_id.
Group: Group by the customer to aggregate orders per customer (include customer_id and customer_name to keep rows distinct).
Filter (HAVING): Use HAVING COUNT(*) > 1 to return only customers with more than one order.

Grouping robustness

Grouping by both customer_id and customer_name prevents accidental merging of different customers who might share the same name. Including the unique identifier (customer_id) ensures portability and correctness across SQL dialects while still making results readable by including customer_name.

Reason: customer_id guarantees uniqueness; customer_name is included for clarity.

WHERE vs HAVING (usage note)

WHERE filters individual rows before aggregation, whereas HAVING filters the grouped result after aggregate functions (like COUNT) are computed. To filter by an aggregate value (for example, number of orders per customer), use HAVING rather than WHERE.

Practical rule: Use WHERE for row-level conditions and HAVING for conditions on aggregates.

Quick comparison

BasisWHEREHAVING

Stage of operationFilters rows before aggregationFilters groups after aggregation

Filters targetIndividual rowsGrouped/aggregated results

Can reference aggregates?No (cannot use COUNT, SUM, etc. directly)Yes (can use COUNT, SUM, etc.)

Typical useRow-level predicates (e.g., status = 'Delivered')Conditions on aggregates (e.g., HAVING COUNT(*) > 1)

Write a query to find the highest-spending customer based only on delivered orders?

Interview-ready answer

Use delivered orders only, sum each customer's delivered amounts, sort descending and return the top row. SQL: SELECT c.customer_id, c.customer_name, SUM(o.amount) AS total_spending FROM Customers AS c JOIN Orders AS o ON c.customer_id = o.customer_id WHERE o.status = 'Delivered' GROUP BY c.customer_id, c.customer_name ORDER BY total_spending DESC LIMIT 1;

Customers

customer_id	customer_name	city
1	Aditi Sharma	Delhi
2	Rahul Verma	Mumbai
3	Simran Kaur	Chandigarh
4	Arjun Nair	Bengaluru

Orders

order_id	customer_id	amount	status
501	1	2400	Delivered
502	1	3200	Delivered
503	2	1800	Delivered
504	2	4100	Pending
505	3	950	Cancelled

SQL querysql

SELECT c.customer_id, c.customer_name, SUM(o.amount) AS total_spending
FROM Customers AS c
JOIN Orders AS o ON c.customer_id = o.customer_id
WHERE o.status = 'Delivered'
GROUP BY c.customer_id, c.customer_name
ORDER BY total_spending DESC
LIMIT 1;

Understand it clearly

Direct query

The SQL below finds the highest-spending customer based only on delivered orders by filtering to delivered rows, aggregating per customer, ordering by the total spending in descending order, and returning the first row.

Query: See the code block in content_blocks for the exact SQL.

Why filter before aggregation

Filtering to WHERE o.status = 'Delivered' before the GROUP BY ensures only delivered orders contribute to each customer's summed spending. This prevents pending or cancelled orders from inflating totals and yields the correct highest spender based solely on delivered orders.

Reason: Excluding non-delivered rows before aggregation avoids incorrect totals.

Steps performed by the query

The query follows a clear sequence:

Filter: WHERE o.status = 'Delivered' — include only delivered orders.
Join: JOIN Customers to Orders on customer_id to access customer details.
Aggregate: SUM(o.amount) grouped by customer_id and customer_name to compute total_spending per customer.
Order & limit: ORDER BY total_spending DESC and LIMIT 1 to return the highest spender.

Portability and tie behavior

The supplied SQL uses LIMIT 1, which is supported by PostgreSQL, MySQL and SQLite. SQL Server uses TOP (1), while standard-style systems may use FETCH FIRST 1 ROW ONLY. Note that this query returns a single customer row when totals tie; which tied row is returned is not guaranteed unless additional tie-breaking criteria are added.

Limit syntax: Different SQL dialects use different ways to restrict result count; choose the one appropriate for your RDBMS.

Quick comparison

BasisLIMIT 1 (PostgreSQL / MySQL / SQLite)SQL Server / Standard-style

Supported byPostgreSQL, MySQL, SQLiteSQL Server uses TOP (1); Standard SQL may use FETCH FIRST 1 ROW ONLY

Example syntaxORDER BY total_spending DESC LIMIT 1SQL Server: SELECT TOP (1) ... ORDER BY total_spending DESC; Standard: ORDER BY total_spending DESC FETCH FIRST 1 ROW ONLY

Behavior on tiesReturns one row (which tied row is returned is undefined)Same — returns one row unless explicit tie-breaker is added

A table has 100 million records. You want to remove all rows as quickly as possible while keeping the table. Which SQL command will you use?

Interview-ready answer

Use TRUNCATE TABLE to remove every row quickly while keeping the table definition.

Example SQLsql

TRUNCATE TABLE Employees;

Understand it clearly

Direct answer

Use TRUNCATE TABLE when the requirement is to remove every row quickly while retaining the table definition.

Why TRUNCATE is faster

TRUNCATE normally deallocates data pages rather than deleting rows one by one, so it usually generates less logging and is faster than an unrestricted DELETE.

Mechanism: Deallocates data pages instead of row-by-row deletes.
Logging: Typically generates less transaction log activity, improving speed.

Caveats and when to use DELETE instead

The exact transaction, trigger, identity-reset and foreign-key behaviour of TRUNCATE varies by database platform. Use DELETE when you need behaviour that TRUNCATE may not provide.

Filtered deletes: Use DELETE when you need to remove only a subset of rows (filtering).
Triggers: Use DELETE when DELETE triggers must run.
Platform restrictions: Use DELETE when the platform's TRUNCATE restrictions are unacceptable or its behaviour differs from your requirements.

Example

See the example SQL code block below for the basic TRUNCATE usage.

Quick comparison

BasisTRUNCATE TABLEDELETE

OperationDeallocates data pages (not row-by-row).Deletes rows one by one.

LoggingUsually generates less logging.Generally generates more logging.

Typical use caseRemove all rows quickly while keeping the table definition.Remove specific rows, or when triggers must run or TRUNCATE is restricted.

Triggers / DB-specific behaviourBehaviour varies by database (may differ for triggers, identity reset, FK handling).DELETE triggers must run; behaviour is the standard row-delete path.

A new intern should only be able to view the Employees table but should not modify it. Which SQL command will you use?

Interview-ready answer

Grant only SELECT permission on the Employees table to the intern's database user (or, preferably, to a read-only role assigned to that user). Example SQL: GRANT SELECT ON Employees TO intern_user;

SQL querysql

GRANT SELECT ON Employees TO intern_user;

Understand it clearly

Direct answer

Grant only the SELECT permission on the Employees table to the intern's database user or to a read-only role assigned to that user. This allows the intern to view rows but not insert, update, or delete them.

Rationale

Use GRANT SELECT to follow the principle of least privilege: permit read access without granting INSERT, UPDATE or DELETE.

Least privilege: GRANT SELECT permits reads without granting INSERT, UPDATE or DELETE.
Role recommendation: Production systems commonly grant permissions to roles for easier auditing and management; assign the intern a read-only role if possible.

Syntax and notes

Syntax and schema qualification vary by database. Use the GRANT statement appropriate for your RDBMS and include schema qualification if required.

An alias created in the SELECT list cannot be referenced in the WHERE clause of the same query. How does SQL's logical execution order explain this?

Interview-ready answer

Because SQL evaluates WHERE before SELECT, a SELECT-list alias does not exist yet when WHERE is processed. Repeat the expression or compute it earlier (subquery or CTE) and filter in the outer query; some databases may permit aliases elsewhere but you should not rely on that behavior.

CTE example filtering on a computed aliassql

WITH employee_salary AS (
  SELECT employee_name, salary * 12 AS annual_salary
  FROM Employees
)
SELECT employee_name, annual_salary
FROM employee_salary
WHERE annual_salary > 600000;

Understand it clearly

Direct answer

WHERE is logically evaluated before SELECT, so any alias defined in the SELECT list does not exist when the WHERE condition is evaluated.

Logical execution order (simplified)

To see why this happens, consider the simplified logical order of SQL query processing; clauses are evaluated in this sequence:

Step 1: FROM / JOIN
Step 2: WHERE
Step 3: GROUP BY
Step 4: HAVING
Step 5: SELECT
Step 6: DISTINCT
Step 7: ORDER BY

Workarounds

Because the alias is not available in WHERE, you have these options to apply the same filter:

Repeat the expression: Use the full expression in the WHERE clause instead of the alias.
Subquery or CTE: Compute the expression in a subquery or CTE and then filter that result in the outer query using the alias.
DB-specific allowance: Some database systems allow aliases in additional clauses, but WHERE should not rely on that behavior for portable SQL.

Example

The following shows the CTE approach: compute annual_salary in a CTE, then filter on that alias in the outer query. See the code block below for the exact SQL.

A ranking query contains duplicate salaries. How will ROW_NUMBER(), RANK(), and DENSE_RANK() assign values differently?

Interview-ready answer

ROW_NUMBER gives every row a unique sequence; RANK gives ties the same rank and leaves gaps; DENSE_RANK gives ties the same rank without gaps.

Ranking behaviour

Salary	ROW_NUMBER	RANK	DENSE_RANK
90000	1	1	1
80000	2	2	2
80000	3	2	2
70000	4	4	3

SQL querysql

SELECT employee_name,
       salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_no,
       RANK() OVER (ORDER BY salary DESC)     AS rank_no,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_no
FROM Employees;

Understand it clearly

Direct answer

ROW_NUMBER assigns a unique sequential number to each row. RANK assigns the same rank to tied values but leaves gaps after ties. DENSE_RANK assigns the same rank to tied values and does not leave gaps.

Concrete example

For the salaries 90000, 80000, 80000 and 70000 the functions produce different sequences as shown in the table below.

ROW_NUMBER: 1, 2, 3, 4 (unique sequence for every row)
RANK: 1, 2, 2, 4 (ties share rank; gap after the tie)
DENSE_RANK: 1, 2, 2, 3 (ties share rank; no gap)

Practical notes

When you need repeatable ordering with ROW_NUMBER (so ties always resolve the same way), add a deterministic tie-breaker to ORDER BY (for example, ORDER BY salary DESC, employee_id ASC). Use DENSE_RANK when you need the nth distinct salary (e.g., find the 2nd highest distinct salary).

SQL example

This query shows all three ranking functions over salaries ordered descending.

Quick comparison

BasisROW_NUMBERRANK / DENSE_RANK

Tie handlingAssigns a unique sequential number to every row (no ties).Assigns the same rank to tied values.

Gaps after tiesNever leaves gaps (numbers continue sequentially).RANK leaves gaps after ties; DENSE_RANK does not leave gaps.

Typical use caseWhen each row needs a unique sequence or when you use a deterministic tie-breaker to enforce repeatable order.When you care about ranks of distinct values (e.g., nth distinct salary) or preserving ties as equal ranks.

A table contains duplicate and NULL email values. How will COUNT(*), COUNT(email), and COUNT(DISTINCT email) differ?

Interview-ready answer

COUNT(*) counts all rows; COUNT(email) counts only non-NULL email values; COUNT(DISTINCT email) counts unique non-NULL email values.

Example SQLsql

SELECT COUNT(*) AS total_rows,
       COUNT(email) AS non_null_emails,
       COUNT(DISTINCT email) AS unique_non_null_emails
FROM Customers;

Understand it clearly

Direct answer

COUNT(*) counts rows in the result set. COUNT(email) counts only non-NULL values in the email column. COUNT(DISTINCT email) counts unique non-NULL email values (duplicates removed).

**COUNT(*):** Counts every row, regardless of column values or NULLs.
COUNT(email): Counts only non-NULL email entries; ignores NULLs.
COUNT(DISTINCT email): Counts distinct (unique) non-NULL email values; duplicates are removed before counting.

How each function works

COUNT(*) does not inspect any particular column

it simply counts result rows. Aggregate functions that take a column as an argument ignore NULL values for that column. DISTINCT removes duplicate non-NULL values before the aggregate is applied.

NULL and duplicate handling

NULL values are ignored by column-based aggregates (so COUNT(email) and COUNT(DISTINCT email) do not count NULLs). Duplicate non-NULL email values are counted by COUNT(email) but are removed by COUNT(DISTINCT email), so only unique non-NULL emails contribute to the DISTINCT count.

Example query

The following query shows all three counts together; aliases label each result column for clarity. The actual SQL is provided below in the code block.

Quick comparison

BasisCOUNT(*)COUNT(email) / COUNT(DISTINCT email)

What is countedAll rows in the result setCOUNT(email): non-NULL email values; COUNT(DISTINCT email): unique non-NULL email values

NULL handlingCounts rows regardless of NULLsNULLs are ignored by both email-based counts (they do not contribute to the count)

Duplicate handlingDuplicates are included (every row counted)COUNT(email): duplicates counted; COUNT(DISTINCT email): duplicates removed before counting

A report contains missing values across primary_phone, alternate_phone, and emergency_phone. How would you return the first available value and show 'Not Available' when all three are NULL?

Interview-ready answer

Use COALESCE(primary_phone, alternate_phone, emergency_phone, 'Not Available') to return the first non-NULL phone value and fall back to 'Not Available' if all three are NULL. If blank strings should also be treated as missing, wrap each column with NULLIF(column, '') inside COALESCE.

SQL examplesql

SELECT customer_name,
       COALESCE(primary_phone, alternate_phone, emergency_phone, 'Not Available') AS contact_phone
FROM Customers;

Understand it clearly

Direct answer

Return the first available phone with COALESCE and provide a fallback literal when all values are NULL. Example: COALESCE(primary_phone, alternate_phone, emergency_phone, 'Not Available'). This returns the first non-NULL argument or 'Not Available' if none exist.

How COALESCE works

COALESCE evaluates its arguments from left to right and returns the first expression that is not NULL. Evaluation stops once a non-NULL value is found, so later arguments are not evaluated for that row.

Behavior: Returns the first non-NULL argument; stops evaluating after finding one.
Type compatibility: All arguments should have compatible data types (the result type is determined by the DBMS rules for COALESCE).

Handling blank strings (not just NULL)

An empty string ('') is not NULL, so COALESCE will treat it as a valid value. If you need to treat empty strings as missing values, use NULLIF(column, '') to convert blanks to NULL before applying COALESCE.

Transformation: Use NULLIF(primary_phone, '') so blank values become NULL and are skipped by COALESCE.
Combined usage: COALESCE(NULLIF(primary_phone, ''), NULLIF(alternate_phone, ''), NULLIF(emergency_phone, ''), 'Not Available')

Example usage

Use the following SQL to select the customer name and the chosen contact phone, falling back to 'Not Available' when all phone columns are NULL (or treated as NULL when wrapped with NULLIF if desired). The exact code block is provided below.

Write a query to find each user's previous login date?

Interview-ready answer

SELECT user_id, login_date, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS previous_login FROM User_Logins;

User_Logins (sample data)

user_id	login_date	session_minutes
1	2024-07-01	35
1	2024-07-02	42
1	2024-07-03	28
2	2024-07-01	20
2	2024-07-03	25
3	2024-07-02	45

SQL querysql

SELECT
  user_id,
  login_date,
  LAG(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date
  ) AS previous_login
FROM User_Logins;

Understand it clearly

Query

Use the LAG window function to access the previous login_date per user. The query returns each row with an extra column previous_login containing the prior login_date for that user (or NULL for their first login).

SQL: LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS previous_login

How it works

The window specification PARTITION BY user_id restarts the history for each user. ORDER BY login_date establishes the chronological sequence within that user's rows so LAG returns the immediately preceding login_date.

Partition: PARTITION BY user_id — separate history per user
Order: ORDER BY login_date — chronological order for LAG to reference
First row: The first login for a user has no previous row, so previous_login is NULL

Notes & edge cases

If multiple logins can share the same login_date, include a more granular ordering column (for example a timestamp or a unique event ID) in the ORDER BY to make the previous row deterministic.

Ties: Add timestamp or event_id to ORDER BY when login_date is not unique

Sample data reference

See the sample User_Logins rows below. Run the query against that table to get each row's previous_login.

A salary report must classify employees into Low, Medium, and High salary bands inside the SELECT output. Which conditional expression would you use, and why is it preferable to IF here?

Interview-ready answer

Use a searched CASE expression. It is standard SQL, returns a value for each row inside SELECT, and evaluates conditions top-to-bottom (so check the highest threshold first). IF is database-specific and can refer to procedural control flow rather than a portable query expression. Define boundary values explicitly with the business team.

Example SQL querysql

SELECT employee_name, salary, CASE WHEN salary >= 80000 THEN 'High' WHEN salary >= 50000 THEN 'Medium' ELSE 'Low' END AS salary_band FROM Employees;

Understand it clearly

Recommended expression

Use a searched CASE expression to classify employees into Low, Medium, and High salary bands inside the SELECT output. CASE is a query expression that returns a value for each row and is part of the SQL standard.

Example query: See the SQL query in the code block below.

Why CASE is preferable

CASE is standard SQL and portable across database systems that support SQL. Because it is an expression, it can be used directly in the SELECT list to return a value per row.

Portability: Standard SQL behavior makes queries more portable between systems.
SELECT usage: CASE returns a value per row inside the SELECT output, which is what this report requires.

Evaluation order and thresholds

A searched CASE evaluates WHEN conditions from top to bottom. For salary bands you must check the highest threshold first so rows with salaries matching higher bands are classified correctly.

Order: Check highest threshold before lower ones (top-to-bottom evaluation).

IF vs CASE and boundary values

IF syntax and behavior are database-specific; in some systems IF is a procedural construct rather than a portable query expression. Because of this, CASE is preferable for a portable SELECT-based classification. Also, boundary values (exact cutoffs) should be agreed explicitly with the business team.

Boundary values: Define exact cutoffs (e.g., whether 80000 belongs to High or Medium) with the business team.

Quick comparison

BasisSearched CASEIF (database-specific)

Standard SQLPart of standard SQL and portable across systems that support CASE.Syntax and semantics are database-specific and not universally portable.

Returns value in SELECTReturns a value per row and can be used directly in SELECT.May not be a query expression in all systems; can be procedural.

Evaluation orderEvaluates WHEN conditions top-to-bottom; order matters for thresholds.Behavior depends on implementation and may not follow the same expression semantics.

PortabilityPortable across databases that support SQL CASE.Less portable; behavior can differ or be unavailable as a SELECT expression.

A percentage calculation divides achieved_sales by target_sales, but some targets are zero. How would you prevent a divide-by-zero error while preserving those rows?

Interview-ready answer

Wrap the denominator with NULLIF(target_sales, 0) so a zero denominator becomes NULL and the division returns NULL instead of causing an error. Multiply by 100.0 to promote decimal arithmetic. Use COALESCE only if the business explicitly wants an alternative display value (e.g., 0).

Example SQLsql

SELECT employee_id, achieved_sales * 100.0 / NULLIF(target_sales, 0) AS achievement_pct
FROM Sales_Targets;

Understand it clearly

Direct fix

Prevent divide-by-zero by converting a zero denominator into NULL using NULLIF(target_sales, 0). The division then yields NULL rather than raising an error, and the row remains in the result set.

Denominator conversion: Use NULLIF(target_sales, 0) so zero becomes NULL for the division.

Why rows are preserved

Because NULL is a valid value in a result set, rows with target_sales = 0 are retained. The computed percentage becomes NULL, which accurately signals that the percentage cannot be calculated for that row.

Signal of missing value: NULL indicates the percentage is undefined rather than hiding or removing the row.

Decimal arithmetic

Multiplying by 100.0 (a floating/decimal literal) promotes decimal arithmetic in many databases so you get a fractional percentage rather than integer truncation.

Precision: Use 100.0 (not 100) to ensure non-integer division results where supported.

Using COALESCE only when needed

If the business prefers a specific display value instead of NULL (for example, show 0%), wrap the result with COALESCE to substitute that value. Do this only when a substitute is a deliberate business requirement.

Alternative display: Example pattern: COALESCE( /* division expression that may yield NULL */ , 0) — use only if you want to display 0 instead of NULL.

Quick comparison

BasisNULLIF (recommended)COALESCE (alternative display)

Behavior with zeroConverts zero to NULL, preventing divide-by-zero.Replaces NULL with a chosen value (e.g., 0) for display if applied.

Division resultDivision returns NULL (no error) and row is preserved.If used alone it doesn't prevent divide-by-zero; used with NULLIF it can replace the resulting NULL for presentation.

When to useWhen you want to avoid errors and indicate an undefined percentage.When you explicitly want an alternative display value instead of NULL.

Write a query to delete duplicate records while keeping the newest record for each email?

Interview-ready answer

Rank each email group by created_at descending and delete rows whose row number is greater than one. Use ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC, customer_id DESC) to mark the newest row (rn = 1) per email and remove rows with rn > 1. Test the DELETE inside a transaction and preview the ranked rows before deleting.

Customer_Records

customer_id	customer_name	email	created_at
301	Nikhil Jain	nikhil@email.com	2024-01-12 10:15
302	Sara Ali	sara@email.com	2024-02-03 09:10
303	Nikhil Jain	nikhil@email.com	2024-05-21 18:30
304	Dev Patel	dev@email.com	2024-06-11 12:45

SQL querysql

WITH ranked AS (
  SELECT
    customer_id,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY created_at DESC, customer_id DESC
    ) AS rn
  FROM Customer_Records
)
DELETE FROM Customer_Records
WHERE customer_id IN (
  SELECT customer_id FROM ranked WHERE rn > 1
);

Understand it clearly

Solution (one-liner)

Rank each email group by created_at descending and delete rows whose row number is greater than one. The ROW_NUMBER() window function identifies the newest row per email; then delete all rows with rn > 1.

Core idea: Use ROW_NUMBER() PARTITION BY email ORDER BY created_at DESC (plus a tie-breaker) and delete where rn > 1.

How it works

ROW_NUMBER assigns rn = 1 to the newest row in each email partition; all older duplicates receive rn > 1. The query builds a ranked CTE and then deletes rows that are not the top-ranked record for their email.

Partitioning: PARTITION BY email groups rows by email address.
Ordering: ORDER BY created_at DESC picks the newest row first.
Selection: Rows with rn > 1 are older duplicates to be deleted.

Tie-breaker & determinism

When timestamps can match, add a deterministic tie-breaker so the engine always picks the same row to keep. In the supplied query customer_id DESC is used as the tie-breaker so the row with the highest customer_id among equal timestamps is kept.

Tie-breaker: Include an additional ORDER BY column (e.g., customer_id DESC) to ensure deterministic behavior when created_at values are identical.

Safety & testing

DELETE-with-CTE syntax and behavior can vary across databases. Always preview the ranked results and run the actual DELETE inside a transaction you can roll back if needed.

Preview: Run the SELECT from the ranked CTE to confirm rn assignments before deleting.
Transaction: Wrap the DELETE in a transaction so you can roll back if the deletion is not as expected.