SQL interviews aren’t just about writing queries — they’re about solving real business problems using smart, logical thinking. Whether it’s selecting data, combining tables or computing values, interviewers want to see how well you understand the tools. In this guide, we’ll walk through some of the most common SQL query challenges questions and how to approach them like a pro. From basic SELECTs to fine tuned optimization, this blog covers everything you need to shine in your next interview.
Top 24 SQL Queries Interview Questions and Answers for 2025
SQL interview questions often test your ability to retrieve, manipulate, and summarize data efficiently. You’ll be asked to write queries for business scenarios like finding top selling products, calculating averages or filtering out duplicates. These questions evaluate not just your syntax but your understanding of how databases work. Practice is key here: the more problems you solve, the sharper your logic becomes.
Q1. How do you fetch the top 3 highest salaried employees from a table?
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3
Explanation: This query sorts the employees by salary in descending order and limits the result to the top 3, giving the highest paid employees.
Table:
| id | name | salary |
| 1 | Amar | 70000 |
| 2 | Ayush | 95000 |
| 3 | Abhi | 85000 |
| 4 | Sneha | 90000 |
Output:
| name | salary |
| Ayush | 95000 |
| Sneha | 90000 |
| Abhi | 85000 |
Q2. How to count total employees in each department?
SELECT department_id, COUNT(*) AS total_employees
FROM employees
GROUP BY department_id
Explanation: This query groups employees by department and uses COUNT to find the number of employees in each.
Table:
| id | name | deparment_id |
| 1 | Amar | 101 |
| 2 | Ayush | 102 |
| 3 | Abhi | 101 |
Output:
| department_id | total_employees |
| 101 | 2 |
| 102 | 1 |
Q3. How do you retrieve employee details whose name starts with ‘A’?
SELECT *
FROM employees
WHERE name LIKE ‘A%’
Explanation: This query uses the LIKE operator to filter employees whose name starts with ‘A’.
Table:
| id | name | salary |
| 1 | Ayush | 75000 |
| 2 | Abhi | 85000 |
Output:
| id | name | salary |
| 1 | Ayush | 75000 |
Q4. How do you find the employee with the minimum salary?
SELECT *
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees)
Explanation: Subquery fetches the minimum salary and the main query retrieves the employee(s) with that salary.
Table:
| id | name | salary |
| 1 | Amar | 60000 |
| 2 | Ayush | 75000 |
| 3 | Abhi | 85000 |
Output:
| id | name | salary |
| 1 | Amar | 60000 |
SQL JOIN Interview Questions
Joins are a staple in almost every SQL interview and for good reason. In real world scenarios, data is spread across multiple tables. You’ll often need to perform INNER JOINs, LEFT JOINs or even FULL OUTER JOINs to merge tables like employee records with departments or customers with transactions. But it’s not just about writing the JOIN. It’s knowing which one fits the use case and why it works.
Q1. How can you list employees with their department names?
SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id
Explanation: This query performs an INNER JOIN to combine employee names with their department names based on department ID.
Tables: employees
| id | name | department_id |
| 1 | Amar | 101 |
| 2 | Ayush | 102 |
Departments
| id | department_name |
| 101 | HR |
| 102 | IT |
Output:
| name | department_name |
| Amar | HR |
| Ayush | IT |
Q2. How to find employees who don’t belong to any department?
SELECT name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL
Explanation: LEFT JOIN keeps all employees and adds department info. If department ID is NULL, the employee isn’t assigned to any department.
Table:
| id | name | deparment_id |
| 1 | Amar | NULL |
| 2 | Ayush | 102 |
Output:
| name |
| Amar |
Q3. How to list departments with no employees?
SELECT department_name
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
WHERE e.id IS NULL
Explanation: LEFT JOIN finds departments with no matching employee entries (i.e, empty departments).
Tables: departments
| id | department_name |
| 101 | HR |
| 102 | IT |
| 103 | Finance |
Employees
| id | Name | department_id |
| 1 | Ayush | 102 |
Output:
| department_name |
| HR |
| Finance |
Q4. How to get count of employees in each department with department name?
SELECT d.department_name, COUNT(e.id) AS total_employees
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.department_name
Explanation: Combines LEFT JOIN with GROUP BY to get department names and their corresponding employee counts.
Tables: departments
| id | department_name |
| 101 | HR |
| 102 | IT |
| 103 | Finance |
employees
| id | name | department_id |
| 1 | Amar | 102 |
Output:
| department_name | total_employees |
| HR | 0 |
| IT | 1 |
| Finance | 0 |
SQL Aggregate Function Interview Questions
Aggregate functions like SUM(), COUNT(), AVG(), MIN(), and MAX() are essential tools for summarizing data. Interviewers often combine these with GROUP BY to test your ability to analyze datasets for instance, calculating monthly sales per region or counting users per plan type. These questions check your analytical thinking and command over data grouping.
Q1. How do you calculate the total salary of employees?
SELECT SUM(salary) AS total_salary
FROM employees
Explanation: The SUM function is used to calculate the total salary of all employees in the table.
Table:
| id | name | salary |
| 1 | Amar | 70000 |
| 2 | Ayush | 95000 |
| 3 | Abhi | 85000 |
Output:
| total_salary |
| 250000 |
Q2. How do you find the average salary of employees?
SELECT AVG(salary) AS average_salary
FROM employees
Explanation: The AVG function computes the average salary across all employees.
Table:
| id | name | salary |
| 1 | Amar | 70000 |
| 2 | Ayush | 95000 |
| 3 | Abhi | 85000 |
Output:
| average_salary |
| 80000 |
Q3. How do you find the maximum salary of employees?
SELECT MAX(salary) AS max_salary
FROM employees
Explanation: The MAX function retrieves the highest salary in the table.
Table:
| id | name | salary |
| 1 | Amar | 70000 |
| 2 | Ayush | 95000 |
| 3 | Abhi | 85000 |
Output:
| max_salary |
| 95000 |
Q4. How do you find the number of distinct salary values?
SELECT COUNT(DISTINCT salary) AS distinct_salaries
FROM employees
Explanation: The COUNT(DISTINCT) function counts unique salary values, ignoring duplicates.
Table:
| id | name | salary |
| 1 | Amar | 70000 |
| 2 | Ayush | 95000 |
| 3 | abhi | 95000 |
Output:
| distinct_salaries |
| 2 |
SQL Subqueries and Nested Queries
Subqueries are used when one query depends on the result of another like finding employees earning more than the average salary. Nested queries can be part of the SELECT, WHERE or FROM clause. They’re frequently used in filtering, comparison, and conditional logic. Interviewers love these because they test your ability to break down complex problems into smaller steps.
Q1. How do you retrieve employees with a salary greater than the average salary?
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
Explanation: The subquery calculates the average salary, and the outer query fetches employees with salaries greater than that average.
Table:
| id | name | salary |
| 1 | Amar | 60000 |
| 2 | Ayush | 75000 |
| 3 | Abhi | 90000 |
Output:
| name | salary |
| Ayush | 75000 |
| Abhi | 90000 |
Q2. How do you find employees working in the same department as ‘Ayush’?
SELECT name
FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE name = ‘Ayush’)
Explanation: The subquery fetches Ayush’s department ID and the outer query lists employees in that same department.
Table:
| id | name | department_id |
| 1 | Amar | 101 |
| 2 | Ayush | 101 |
| 3 | Abhi | 102 |
Output:
| name |
| Amar |
Q3. How do you find employees who earn more than the lowest salary in the company?
SELECT name, salary
FROM employees
WHERE salary > (SELECT MIN(salary) FROM employees)
Explanation: The subquery retrieves the lowest salary and the outer query filters employees earning more than that salary.
Table:
| id | name | salary |
| 1 | Amar | 60000 |
| 2 | Ayush | 75000 |
| 3 | Abhi | 85000 |
Output:
| name | salary |
| Ayush | 75000 |
| Abhi | 85000 |
Q4. How do you find employees with a salary greater than the average of their department?
SELECT name, salary, department_id
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id)
Explanation: The subquery calculates the department wise average salary and the outer query returns employees with salaries above the department’s average.
Table:
| id | name | salary | department_id |
| 1 | Amar | 60000 | 101 |
| 2 | Ayush | 75000 | 101 |
| 3 | Abhi | 85000 | 102 |
Output:
| name | salary | department_id |
| Ayush | 75000 | 101 |
| Abhi | 85000 | 102 |
SQL Query Optimization Tips
Knowing how to write a query is good. Knowing how to make it fast is even better. SQL query optimization questions assess your understanding of performance indexing, avoiding unnecessary columns in SELECT, using WHERE over HAVING, limiting the use of wildcards and leveraging joins correctly. A well-optimized query is like clean code efficient, readable and scalable.
Q1. How to optimize a query that fetches all employees’ details?
SELECT *
FROM employees
WHERE department_id IS NOT NULL
Explanation: Use specific columns instead of SELECT * to improve performance and avoid retrieving unnecessary data.
Table:
| id | name | department_id |
| 1 | Amar | 101 |
| 2 | Ayush | 102 |
| 3 | Abhi | NULL |
Output:
| id | name | department_id |
| 1 | Amar | 101 |
| 2 | Ayush | 102 |
Q2. How do you optimize a query with a complex JOIN?
SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > 70000
Explanation: Ensure that the JOIN condition uses indexed columns (such as department_id and id) to speed up the query.
Table:
| id | name | salary | department_id |
| 1 | Amar | 80000 | 101 |
| 2 | Ayush | 90000 | 102 |
Output:
| name | department_name |
| Amar | HR |
| Ayush | IT |
Q3. How do you optimize a query with GROUP BY?
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
Explanation: Indexing department_id helps optimize the grouping process for faster results.
Table:
| id | name | department_id |
| 1 | Amar | 101 |
| 2 | Ayush | 102 |
| 3 | Abhi | 101 |
Output:
| department_id | COUNT(*) |
| 101 | 2 |
| 102 | 1 |
Q4. How to avoid using DISTINCT in a query for optimization?
SELECT name, salary
FROM employees
WHERE salary > 50000
Explanation: Instead of using DISTINCT, focus on proper filtering conditions to avoid unnecessary operations.
Table:
| id | name | salary |
| 1 | Amar | 60000 |
| 2 | Ayush | 70000 |
| 3 | Abhi | 80000 |
Output:
| name | salary |
| Amar | 60000 |
| Ayush | 70000 |
| Abhi | 80000 |
SQL Window Functions Interview Questions
Functions like ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD() and LAG() give you analytical power without losing the detail of individual rows. These are used in scenarios like ranking employees, finding running totals or identifying changes between periods. Window functions often appear in advanced interviews and mastering them can really set you apart.
Q1. How do you calculate the running total of salaries?
SELECT name, salary,
SUM(salary) OVER (ORDER BY id) AS running_total
FROM employees
Explanation: The SUM window function calculates a running total, ordered by id.
Table:
| id | name | salary |
| 1 | Amar | 60000 |
| 2 | Ayush | 70000 |
| 3 | Abhi | 80000 |
Output:
| name | salary | running_total |
| Amar | 60000 | 60000 |
| Ayush | 70000 | 130000 |
| Abhi | 80000 | 210000 |
Q2. How do you calculate the rank of employees based on their salary?
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
Explanation: The RANK window function assigns a rank to each employee based on their salary in descending order.
Table:
| id | name | salary |
| 1 | Amar | 60000 |
| 2 | Ayush | 75000 |
| 3 | Abhi | 75000 |
Output:
| name | salary | salary_rank |
| Ayush | 75000 | 1 |
| Abhi | 75000 | 1 |
| Amar | 60000 | 3 |
Q3. How do you calculate the moving average of employee salaries over the last 3 rows?
SELECT name, salary,
AVG(salary) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees
Explanation: The AVG window function computes the moving average of salaries, considering the current row and the two preceding rows.
Table:
| id | name | salary |
| 1 | Amar | 60000 |
| 2 | Ayush | 70000 |
| 3 | Abhi | 80000 |
| 4 | Sneha | 90000 |
Output:
| name | salary | moving_avg |
| Amar | 60000 | 60000 |
| Ayush | 70000 | 65000 |
| Abhi | 80000 | 76666.67 |
| Sneha | 90000 | 80000 |
Q4. How do you find the first salary in each department?
SELECT name, salary,
FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary) AS first_salary
FROM employees
Explanation: The FIRST_VALUE window function retrieves the first salary within each department, based on salary order.
Table:
| id | name | salary | department_id |
| 1 | Amar | 60000 | 101 |
| 2 | Ayush | 70000 | 101 |
| 3 | Abhi | 80000 | 102 |
| 4 | Sneha | 90000 | 102 |
Output:
| name | salary | first_salary |
| Amar | 60000 | 60000 |
| Ayush | 70000 | 60000 |
| Abhi | 80000 | 80000 |
| Sneha | 90000 | 80000 |
Final Thought
SQL isn’t just a technical skill. It’s a way of thinking. A solid understanding of SQL opens doors to high impact roles in data analysis, BI and data engineering. At ConsoleFlare, we train aspiring professionals with hands-on projects, expert led sessions and real world case studies. Whether you’re prepping for an interview or sharpening your data skills, our program ensures you’re not just ready for questions — you’re ready for the job.
For more such content and regular updates, follow us on Facebook, Instagram, LinkedIn
