Sunday, January 7, 2024

Top 15 SQL Query Interview Questions for Practice (with Solutions)

Hello folks, if you are preparing for technical interviews for software developer, system admin, data analyst, or data scientist, one skill you must prepare is SQL. It's one of the important topic for programmers, support engineers and DBAs as you have to deal with SQL in your day to day job. If you don't know, SQL is the declarative language that allows you to interact with the database. By using SQL you can insert, update, search, and delete data in a relational database. The code you write is called SQL queries and it's one of the essential skills for many technical people who have to deal with databases. That's why SQL queries are very popular in interviews and you will often see one or more SQL queries on interviews. 

They are not just popular in the telephonic or face-to-face interviews but also during the written tests and that's why it's become imperative for developers, DBAs, and Data scientists to practice SQL queries during coding interview preparation. In this article, I will share SQL queries you can practice for SQL and Database interviews.

And, what could be better than solving the popular SQL queries which have been asked a number of times during SQL interviews. Yes, that's how I learned most of my SQL and database skills, by practicing SQL queries from interviewers and that's what I am going to share here. 

These are the top most popular SQL query interview questions for developers and junior DBAs, if you are preparing for a Developer or DBA interview, make sure you can solve these SQL queries. Most likely you already know them but even if you don't it won't take long to master them. 

15 Questions based on SQL Queries from Interviews

Here are the popular SQL queries which are commonly asked on technical interviews including Java and database engineer interviews. 

1. How do you find the second highest salary of employees in the Emp table?


select min(salary) 
from employee 
where salary in ( select salary from employee order by salary limit 2)

I have also shared a detailed solution including other database like Microsoft SQL and Oracle in my post 5 ways to find the height salary in SQL

2. How do you find duplicate employees in the employee table? (group by and count())

Detailed answer: Finding duplicate employees in an employee table typically involves identifying records where certain key fields or a combination of fields have identical values. 

Here is an example SQL query that you can use to find duplicate employees based on the employee's name in a hypothetical employees table:

SELECT employee_name, COUNT(*)
FROM employees
GROUP BY employee_name

This query selects the employee_name and counts how many times each name appears in the employees table. The GROUP BY clause groups the results by employee_name, and the HAVING clause filters out names that only appear once.

You may need to adjust the query based on the specific structure of your employees table and the criteria for identifying duplicates. If, for example, you have an employee_id field that should be unique, you can include it in the query to refine the duplicates search.

Remember to replace employee_name and employees with the actual column name for employee names and the table name, respectively. Adjust the query based on your database schema and requirements.

3. How do you find all the employees who are managers? (Self Join)

To find all employees who are managers in a hypothetical employees table, you can use a SQL query that looks for employees whose IDs match the manager_id in the same table. Assuming that the manager_id column refers to the employee ID of the manager, you can use a self-join to achieve this. 

Here's an example query:

SELECT e.employee_id, e.employee_name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;

This query selects the employee_id and employee_name from the employees table for employees who are managers. The self-join is performed by matching the manager_id of each employee (e.manager_id) with the employee_id of another record in the same table (m.employee_id). Adjust column names based on your actual schema.

If your employees table has a column indicating whether an employee is a manager (e.g., is_manager), you can use a simpler query:

SELECT employee_id, employee_name
FROM employees
WHERE is_manager = 1;

This query selects employees with is_manager set to 1, assuming 1 represents a manager. Adjust the column name and value based on your actual schema.

Remember to adapt these queries to your specific database schema and column names.

4. Given an EMPLOYEE table and a DEPARTMENT table, how would you select the combined salary of each department that has a higher combined salary than mine?

To select the combined salary of each department that has a higher combined salary than the department with a specific identifier (e.g., "My Department"), you can use a SQL query that involves joining the EMPLOYEE and DEPARTMENT tables, grouping by department, and applying a condition to filter out departments with a lower combined salary. 

Here's a generic example:

Assuming the tables have the following structures:

  • EMPLOYEE table: employee_id, employee_name, salary, department_id
  • DEPARTMENT table: department_id, department_name

And assuming your department's name is "My Department," you can use the following query:

SELECT d.department_id,


       SUM(e.salary) AS combined_salary


JOIN EMPLOYEE e ON d.department_id = e.department_id

GROUP BY d.department_id, d.department_name

HAVING SUM(e.salary) > (

    SELECT SUM(e2.salary)


    JOIN EMPLOYEE e2 ON d2.department_id = e2.department_id

    WHERE d2.department_name = 'My Department'


This query performs the following steps:

  • Joins the EMPLOYEE and DEPARTMENT tables on the department_id.
  • Groups the result by department_id and department_name.
  • Calculates the combined salary for each department using SUM(e.salary).
  • Filters out departments with a combined salary lower than the combined salary of "My Department" using the HAVING clause.
  • Adjust the column names and values according to your actual schema and the identifier of your department.

5. Write a query to return all employees of a particular department to say FINANCE?

You can use a simple SQL query to retrieve all employees of the "FINANCE" department. Here's an example query:

SELECT employee_id, employee_name, salary


WHERE department_id = (

    SELECT department_id


    WHERE department_name = 'FINANCE'


This query does the following:

  • The inner query retrieves the department_id of the "FINANCE" department from the DEPARTMENT table.

  • The outer query selects employee_id, employee_name, and salary from the EMPLOYEE table for all employees where the department_id matches the one obtained from the inner query.

By the way, if you want to use the code the please adjust the column names based on your actual schema.

6. How do you convert int to date in SQL Server?

In SQL Server, you can use the CONVERT function to convert an integer representing a date to an actual date. The integer should be in the format of YYYYMMDD for this conversion.

Here's an example:

DECLARE @IntDate INT = 20220107; -- Assuming 2022-01-07

In this example:
  • CONVERT(VARCHAR, @IntDate) converts the integer to a string.
  • CONVERT(DATE, ..., 112) converts the string to a date using style 112, which represents the YYYYMMDD format.
Adjust the value of @IntDate according to your specific integer date representation.

7. How to find if one date comes before the other in SQL Server?

In SQL Server, you can compare dates using the standard comparison operators. If you want to check if one date comes before another, you can use the less-than (<) or greater-than (>) operators.

Here's an example:

DECLARE @Date1 DATE = '2022-01-07';
DECLARE @Date2 DATE = '2022-01-15';

IF @Date1 < @Date2
    PRINT 'Date1 comes before Date2';
    PRINT 'Date1 does not come before Date2';

In this example, if Date1 is less than Date2, the message "Date1 comes before Date2" will be printed. Adjust the values of @Date1 and @Date2 based on your specific dates.

Keep in mind that this comparison works for DATE data types. If you're working with date and time values, you should use the DATETIME or DATETIME2 data types and compare them accordingly.

8. How do you find all employees which are managers?

To find all employees who are managers, you would typically have a column in your EMPLOYEE table that indicates the managerial status. Assuming there's a column named IsManager (boolean or bit type) or a similar indicator, you can use a simple SQL query to retrieve the information.

Here's an example query:

WHERE IsManager = 1; -- Assuming 1 represents 'true' for managerial status

9. Write a SQL query to display the name of the employee and their manager?

To display the name of the employee and their manager, you would typically need a column in your EMPLOYEE table that represents the manager for each employee, like a ManagerID column that references the employee ID of the manager. Assuming you have a table structure like this, you can use a SQL query like:

    E.EmployeeName AS Employee,
    M.EmployeeName AS Manager
    EMPLOYEE M ON E.ManagerID = M.EmployeeID;

This assumes that there is a foreign key relationship between ManagerID in the EMPLOYEE table and EmployeeID in the same EMPLOYEE table.

10. How would you select all last names that start with "J"?

To select all last names that start with "J" in SQL, you can use the WHERE clause with the LIKE operator. Here's an example query:

11. How would you select all rows where the date is 20220127?


FROM YourTableName

WHERE LastName LIKE 'J%';

Just replace YourTableName with the actual name of your table. The % in the LIKE pattern represents any sequence of characters, so 'J%' will match all last names that start with "J".

12. Find all  employees (names) who have a bigger salary than their boss

SELECT a.Name FROM Employees a JOIN Employees b
ON a.BossID = b.EmployeeId
WHERE a.Salary > b.Salary;

13. Can you write a query to display all employees who have the biggest salary in their departments

SELECT a.Name, a.DepartmentID
FROM Employees a JOIN
(SELECT a.DepartmentID, MAX(Salary)
FROM Employees a JOIN Departments b ON a.DepartmentID = b.DepartmentID
GROUP BY a.DepartmentID) b
ON a.Salary = b.max AND a.DepartmentID = b.DepartmentID;

14. Write a SQL query to search all departments that have less than 3 people in it

SELECT DepartmentID, COUNT(Name)
FROM Employees
GROUP BY DepartmentID

15. Write a Query to find  all departments along with the number of people there (tricky - people often do an "inner join" leaving out empty departments)

SELECT b.Name, COUNT(a.DepartmentID)
FROM Employees a FULL OUTER JOIN Departments b ON a.DepartmentID=b.DepartmentID
GROUP BY b.Name;

16. Write a SQL query to show employees that don't have a boss in the same department

SELECT a.Name FROM Employees a JOIN Employees b
ON a.BossID = b.EmployeeId
WHERE a.DepartmentID != b.DepartmentID;

17. Write SQL query to list all departments along with the total salary there?

Here is the SQL query to find all departments along with their total salary:
SELECT b.Name, SUM(a.Salary)
FROM Employees a FULL OUTER JOIN Departments b ON a.DepartmentID = b.DepartmentID

18. What is an ER diagram?

This is not a SQL query question but definitely worth knowing. ER diagram stands for entity relationship diagram which shows the relationship of different tables. Here is an example of ER diagram

Top 15 SQL Query Interview Questions for Practice

That's all about the common SQL queries from interviews. I will add more SQL queries to this list as and when I got them. If you have any other interesting SQL queries from your coding interviews then feel free to share with us, I may include them in the main list. 

No comments:

Post a Comment