SQL queries you must practice for your next interview — Part 3

Here is a list of common SQL queries that I was asked in various interviews over a period of 8+ years.

Q1) Find nth highest salary from the table?

select Top 1 Emp_Salary from (select distinct top N Emp_Salary from EmployeeDetails order by Emp_Salary desc) as TopSalary order by Emp_Salary asc

Without using Top/Limit:

SELECT Emp_Salary

FROM EmployeeDetails Emp1

WHERE N-1 = (

SELECT COUNT( DISTINCT ( Emp2.Emp_Salary ) )

FROM EmployeeDetails Emp2

WHERE Emp2.Emp_Salary > Emp1.Emp_Salary

)

Q2) In one table there are duplicated Id’s of an employee, with salary in each row. Write a query to get the sum of all the duplicate records salary in a separate column.

select Sum(EmpSalary) as salary from Employee Group by Id

Q3) How to remove duplicate records from database having primary key column?

When PK is present

Delete From People where RowId NOT IN (select MIN(RowId) FROM people GROUP BY [Name], [City], [State])

When PK is not present

Delete from EmployeeDetails where Emp_Id in (select Emp_id from EmployeeDetails group by Emp_id, Emp_Name, Emp_Gender, Emp_DeptId, Emp_Age, Emp_Salary having COUNT(*)>1)

Q4)Find sum of total salary of male and female employees.

select Emp_Gender, SUM(Emp_Salary)

from EmployeeDetails

group by Emp_Gender;

Q5)Write a SQL query to fetch project-wise count of employees sorted by number of employees count in descending order.

select Emp_DeptId,count(Emp_Id)as TotalEmployee from EmployeeDetails

group by Emp_DeptId order by TotalEmployee desc

Q6) Write a query to fetch only the first name(string before space) from the Emp_Name column of EmployeeDetails table and last Name

select SUBSTRING(Emp_Name,0,CharIndex(‘ ‘,Emp_Name))as FirstName from EmployeeDetails

SELECT LEFT(Emp_Name, CHARINDEX(‘ ‘,Emp_Name) — 1)as FirstName FROM EmployeeDetails;

The LEFT() function extracts a number of characters from a string (starting from left).

The SUBSTRING() function extracts some characters from a string.

Q7) Write a query to fetch employee names and their respective department records. Return employee details even if the dept record is not present for the employee.

Note that here we need all the details of employee table and only matching records from department table. Thus, here we need to do Left Join.

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

select e.Emp_Name,d.Dept_Name from EmployeeDetails e left join DepartmentDetails d on e.Emp_DeptId= d.Dept_Id

Q8) Write a SQL query to fetch all the Employees who are also managers from EmployeeDetails table.

select * from EmployeeDetails e inner join EmployeeDetails d on e.EmpId=d.ManagerId

Q9) Write a SQL query to fetch only odd rows from table.

The ROW_NUMBER() is a window function that assigns a sequential integer to each row within a partition of a result set. The row number starts with one for the first row in each partition.

select * from

(

select *,ROW_NUMBER() over(order by Emp_Id)as tRowNumber from EmployeeDetails

) e where e.tRowNumber%2!=0

Q10) Find the Dept id Which has maximum strength of the employees.

select Top 1 Emp_DeptId, Count(*) as NoOfEmployees from EmployeeDetails group by Emp_DeptId order by NoOfEmployees desc

I will keep updating this list as I face more interviews. If you are interested in more real-time interview question related to SQL check my other post here “SQL interview questions you must know — Part 1" and for more scenario-based questions refer to “SQL interview questions you must know- Part 2”.

Liked the post? If you found this useful consider buying me a coffee here https://bmc.link/shreetanu9R