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, 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
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.
- Difference between UNION and UNION ALL in SQL
- 10 Examples of ALTER command in SQL
- MySQL vs NoSQL comparison
- Difference between Self and Equi join in SQL
- What are T-SQL Window Functions?
- How to use Stored Procedure in SQL?
- 4 Examples of CASE expression in SQL Server
- How to create and rollback transaction in database?
- How to convert String to Date in SQL?
- How to use WHERE and HAVING clause in SQL
- How to find top 10 Records in SQL
- Difference between Truncate, Drop, and Delete in SQL
No comments:
Post a Comment