Top 15 Real SQL Queries Asked in Backend Developer Interviews

Top 15 Real SQL Queries Asked in Backend Developer Interviews

If you're preparing for a backend developer interview, SQL is one skill you can’t afford to ignore. Whether it's a startup or a product-based company like Amazon, Swiggy, or Paytm, SQL queries are commonly asked to test your data handling and logic-building ability. Here's a list of 15 real-world SQL queries that interviewers ask again and again — with sample table references and expected outputs.

1. Find the second highest salary from the Employee table.

SELECT MAX(salary) AS Second_Highest_Salary
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

2. Retrieve employee details who have the highest salary in each department.

SELECT *
FROM employee e
WHERE salary = (
  SELECT MAX(salary)
  FROM employee
  WHERE department_id = e.department_id
);

3. Write a query to fetch duplicate records from a table.

SELECT name, COUNT(*)
FROM employee
GROUP BY name
HAVING COUNT(*) > 1;

4. Get employee names who joined in the last 3 months.

SELECT name
FROM employee
WHERE joining_date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH);

5. Show department-wise average salary and count of employees.

SELECT department_id, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
FROM employee
GROUP BY department_id;

6. Write a query to fetch top 3 highest-paid employees.

SELECT *
FROM employee
ORDER BY salary DESC
LIMIT 3;

7. Get all employees who don’t have managers.

SELECT *
FROM employee
WHERE manager_id IS NULL;

8. Fetch all employees who belong to departments that have more than 5 employees.

SELECT *
FROM employee
WHERE department_id IN (
  SELECT department_id
  FROM employee
  GROUP BY department_id
  HAVING COUNT(*) > 5
);

9. Find all employees whose name starts and ends with a vowel.

SELECT name
FROM employee
WHERE name REGEXP '^[aeiouAEIOU].*[aeiouAEIOU]$';

10. Get employee(s) with salary more than the average salary.

SELECT *
FROM employee
WHERE salary > (SELECT AVG(salary) FROM employee);

11. Fetch all employees and their department names using JOIN.

SELECT e.name, d.department_name
FROM employee e
JOIN department d ON e.department_id = d.id;

12. Write a query to find employees with the same salary.

SELECT salary
FROM employee
GROUP BY salary
HAVING COUNT(*) > 1;

13. Get the latest joining employee in each department.

SELECT *
FROM employee e
WHERE joining_date = (
  SELECT MAX(joining_date)
  FROM employee
  WHERE department_id = e.department_id
);

14. Find the total salary paid in each department including department name.

SELECT d.department_name, SUM(e.salary) AS total_salary
FROM employee e
JOIN department d ON e.department_id = d.id
GROUP BY d.department_name;

15. Get employees whose salaries are within the top 10% of all salaries.

SELECT *
FROM employee
WHERE salary >= (
  SELECT PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY salary)
  FROM employee
);

*Note: The above syntax is for PostgreSQL. For MySQL, use window functions or percentile calculation logic manually.

Conclusion

Mastering these SQL queries will not only boost your confidence but also help you crack backend interviews easily. Practice them with mock tables and explain the logic during interviews — that’s what interviewers love.

Follow InterviewYatra.com for more backend interview content, system design questions, and real company interview experiences.