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