Friday, January 28, 2022

Top 15 SQL Query Interview Questions for Practice

 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 the declarative langauge 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. Here are some 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, 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. 


Questions based on SQL Queries from Interviews

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

SELECT MAX(SALARY) 
FROM EMPLOYEE 
WHERE SALARY 
NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE) //generic


//MYSQL

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



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

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

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?

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

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

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

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

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

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

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

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
HAVING COUNT(Name) < 3;


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?

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


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