Mastering SQL for Technical Interviews: Top 10 Queries You Must Know

Why SQL is Mandatory in Technical Rounds

Whether you are interviewing for a Software Developer, Data Analyst, QA Engineer, or DevOps position, SQL (Structured Query Language) is almost always part of the technical screening. Databases are the foundation of modern software systems, and employers want to ensure you can write efficient queries to retrieve, manipulate, and optimize data. Vague theoretical knowledge is not enough; you must be prepared to write queries live under time pressure.

This coding tutorial covers the top 10 SQL queries and concepts that are frequently tested in interviews, along with performance optimization tips.

1. Understanding SQL JOINs (Inner, Left, Right, Full)

JOINs are used to combine rows from two or more tables based on a related column.

• INNER JOIN: Returns records that have matching values in both tables.
• LEFT JOIN: Returns all records from the left table, and the matched records from the right. If no match exists, NULLs are returned for the right.

SELECT e.EmployeeName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

2. The GROUP BY Clause and Aggregations

Hiring managers frequently ask questions requiring you to group data and perform aggregate calculations (like COUNT, SUM, AVG).

SELECT DepartmentID, COUNT(EmployeeID) AS TotalEmployees, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) > 50000;

*Note: Use `HAVING` to filter aggregated results, as the `WHERE` clause cannot be used with aggregate functions.*

3. Finding the N-th Highest Salary (Classic Interview Question)

This is a classic query that tests your understanding of subqueries or window functions.

Method A (Subquery):

SELECT MIN(Salary) AS ThirdHighestSalary
FROM (SELECT DISTINCT TOP 3 Salary FROM Employees ORDER BY Salary DESC) AS Emp;

Method B (Window Function - Recommended for SQL Server/Postgres):

WITH RankedSalaries AS (
  SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
  FROM Employees
)
SELECT Salary FROM RankedSalaries WHERE SalaryRank = 3;

4. Finding Duplicate Records in a Table

You may be asked to identify duplicate email addresses or usernames in a database.

SELECT Email, COUNT(Email) AS DuplicateCount
FROM Users
GROUP BY Email
HAVING COUNT(Email) > 1;

5. SQL Query Optimization Tips

Writing a query that works is only the first step. Senior interviewers will ask how to make it faster:

1. Create Indexes: Ensure columns used frequently in WHERE clauses or JOIN conditions have indexes created.
2. Avoid SELECT *: Retrieve only the specific columns you need to reduce network load.
3. Avoid Subqueries: Use JOINs instead of nested subqueries where possible, as modern database optimizers execute JOINs more efficiently.