SQL Query Optimization Interview Questions

The cleanest approach uses a subquery to exclude the maximum salary and then picks the max of what remains. Another common approach uses DENSE_RANK() which handles ties correctly, which is usually what interviewers actually want.

-- Subquery approach
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Window function approach (handles ties)
SELECT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 2;

You use the SUBSTRING (or LEFT) function to extract the first 5 characters from a string. Both work across most major databases, but LEFT is more concise when you always want characters from the start.

SELECT SUBSTRING(column_name, 1, 5) FROM table_name;

-- Or equivalently
SELECT LEFT(column_name, 5) FROM table_name;

The standard approach is to use GROUP BY on the department and aggregate salary with MAX(). You join back to the departments table if you want department names alongside the results.

SELECT department_id, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id
ORDER BY department_id;

-- With department name via join
SELECT d.department_name, MAX(e.salary) AS max_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name
ORDER BY d.department_name;

The biggest wins usually come from proper indexing. Make sure you have indexes on columns used in WHERE clauses, JOIN conditions, and ORDER BY, and use covering indexes where possible to avoid table lookups. Beyond indexes, avoid SELECT * and only retrieve the columns you need, rewrite correlated subqueries as JOINs or CTEs, and analyze query execution plans to find full table scans or expensive sort operations. At the database level, partitioning large tables, updating statistics regularly, and making sure queries aren't blocked by locks also have a big impact on throughput.

UPDATE STATISTICS tells SQL Server to refresh the statistical information it holds about the distribution of data values in table columns and indexes. The query optimizer relies on these statistics to choose efficient execution plans, so when they become stale after large INSERT, UPDATE, or DELETE operations, query performance can degrade significantly. You would run it manually after a bulk load, after rebuilding indexes, or when you notice execution plans making poor choices like scanning instead of seeking. SQL Server does update statistics automatically by default, but that threshold is based on a percentage of row changes and may not trigger quickly enough on very large tables.

UPDATE STATISTICS is useful when a large number of rows have been inserted, deleted, or updated in a table and the query optimizer is using outdated statistics to build execution plans. SQL Server updates statistics automatically by default, but that can lag behind on heavily modified tables or after bulk loads. You'd run it manually when you notice sudden query plan degradation, after a large ETL job, or after rebuilding indexes to ensure the optimizer has accurate row distribution data. In SQL Server syntax it's UPDATE STATISTICS table_name or you can target a specific index.

-- Update all statistics on a table
UPDATE STATISTICS Employees;

-- Update statistics on a specific index
UPDATE STATISTICS Employees IX_Employees_Salary;

The syntax varies slightly by database. In SQL Server you use GETDATE() or the ANSI-standard CURRENT_TIMESTAMP. In MySQL it's CURDATE() for just the date or NOW() for date and time. In PostgreSQL you use CURRENT_DATE or NOW().

-- SQL Server
SELECT GETDATE();           -- date + time
SELECT CAST(GETDATE() AS DATE);  -- date only

-- MySQL
SELECT CURDATE();

-- PostgreSQL
SELECT CURRENT_DATE;

Combine a GROUP BY on the Gender column with a BETWEEN filter on DOB. This gives you a count per gender for that birth year range in a single pass.

SELECT Gender, COUNT(*) AS EmployeeCount
FROM Employees
WHERE DOB BETWEEN '1960-01-01' AND '1975-12-31'
GROUP BY Gender;

A straightforward WHERE clause with a >= comparison handles this. If you also want to see the salary in the output to confirm, just include it in the SELECT list.

SELECT Name, Salary
FROM Employees
WHERE Salary >= 10000
ORDER BY Salary DESC;

Use the LIKE operator with 'M%' as the pattern. The percent sign matches any sequence of characters after 'M', so this returns anyone whose name begins with that letter. Keep in mind that LIKE is case-sensitive in some collations, so if you need case-insensitive matching on a case-sensitive database you may need to wrap the column in UPPER() or LOWER().

SELECT Name
FROM Employees
WHERE Name LIKE 'M%';

Wrap the column in UPPER() and compare against the uppercase version of the search term. This makes the comparison case-insensitive regardless of the database collation. Alternatively, on databases like SQL Server with a case-insensitive collation by default, a plain LIKE 'joe' already works, but the UPPER approach is more portable.

SELECT *
FROM Employees
WHERE UPPER(Name) LIKE '%JOE%';

Each major database has its own function for extracting the year from a date. SQL Server uses YEAR() or DATEPART(), MySQL uses YEAR(), and PostgreSQL uses EXTRACT() or DATE_PART(). All of them return an integer.

-- SQL Server / MySQL
SELECT Name, YEAR(DOB) AS BirthYear
FROM Employees;

-- PostgreSQL
SELECT Name, EXTRACT(YEAR FROM DOB) AS BirthYear
FROM Employees;

-- ANSI SQL (works in most databases)
SELECT Name, EXTRACT(YEAR FROM DOB) AS BirthYear
FROM Employees;

The cleanest way is to use INTERSECT, which returns only the rows that appear in both result sets. If your database does not support INTERSECT, you can achieve the same thing with an INNER JOIN on all the relevant columns. INTERSECT automatically handles duplicates and is typically the most readable approach.

-- Using INTERSECT
SELECT Id, Name FROM TableA
INTERSECT
SELECT Id, Name FROM TableB;

-- Alternative using INNER JOIN
SELECT a.Id, a.Name
FROM TableA a
INNER JOIN TableB b ON a.Id = b.Id AND a.Name = b.Name;

Use either SELECT DISTINCT or GROUP BY depending on what you need. DISTINCT removes duplicate rows from the result set, while GROUP BY is more useful when you also want to aggregate data. For simply getting unique records, DISTINCT is the more readable choice.

-- Using DISTINCT
SELECT DISTINCT Name, Department
FROM Employees;

-- Equivalent using GROUP BY
SELECT Name, Department
FROM Employees
GROUP BY Name, Department;

The cleanest way to do this is with a subquery that calculates the average and then filters in the outer WHERE clause. Using AVG() in a subquery keeps the logic clear and the optimizer generally handles it well.

SELECT student, marks
FROM student_marks
WHERE marks > (SELECT AVG(marks) FROM student_marks);