Top 5 Tricky/Important SQL Queries And Their Answers

CREATE TABLE #tempTable ( StudID INT, StudName VARCHAR(50))  

INSERT INTO #tempTable VALUES(1,'Ak'),(2,'SG'),(3,'RJ'),(2,'SG');  

WITH CTESTUD AS (  
SELECT StudID , StudName , row_number() OVER(PARTITION BY StudID , StudName order by StudID ) AS StudRowNumber  
FROM #tempTable  
)  
DELETE CTESTUD WHERE StudRowNumber > 1  

INSERT INTO #tempTable VALUES (1, 'Akash', 'NAME'), (2, '123456789012', 'ACCOUNTNUMBER'),(3, '20000.40', 'SALARY');  

SELECT NAME, ACCOUNTNUMBER, SALARY  
FROM  
(  
   SELECT VALUEFIELD, COLUMNNAME  
   from #tempTable  
) d  
PIVOT  
(  
   MAX(VALUEFIELD)  
   FOR COLUMNNAME IN (NAME, ACCOUNTNUMBER, SALARY)  
) PIV;  

Solution

Here I will explain how to find the department with maximum number of employees in SQL Server.

Create a temporary table

Create a two temporary tables (department and employee) and make a relationship between both tables. SQL query like this,

CREATE table #tempDept (DeptID int, DeptName varchar(50));  
CREATE table #tempEmployee (EmpID int, EmpName varchar(50),DeptID int);  

Insert some values in both temporary tables like this,

INSERT INTO #tempDept VALUES (1, 'Admin'),(2, 'HR'),(3, 'IT');  
INSERT INTO #tempEmployee VALUES (1, 'AK',1),(2, 'RJ',1),(3, 'SK',2);  

SQL query to find department with maximum employees,

Write a query to find out the highest number of employees in the department. SQL query given in below,

SELECT TOP 1 DeptName FROM #tempEmployee E INNER JOIN #tempDept D ON E.DeptID=D.DeptID  
GROUP BY DeptName ORDER BY COUNT (*) DESC  

Q4. How to find the nth highest salary in SQL Server?

Solution

Here I will explain how to find the nth highest salary in SQL Server.

Create a temporary table

Here the #TEMPTABLE table has the three columns EmpID, EmpName and Salary. Salary is a field where we store the salary of an employee. Use following SQL query to create #TEMPTABLE table and build schema like this,

CREATE TABLE #TEMPTABLE (EmpID INT, EmpName VARCHAR(20), Salary DECIMAL(16,2));  

Insert some values in a temporary table

Now we insert a number of rows in the #tempTable table like this,

INSERT INTO #tempTable VALUES (1, 'Akash', 10000), (2, 'Rajesh', 15000),(3, 'Sanjay', 200000);  

SQL query to find nth highest salary

I used the CTE (Common Table Expression) and DENSE_RANK() method to find the highest salary in SQL table. Write a below given SQL query to find out the nth highest salary,

WITH SALARYCTE AS  
(  
   SELECT EmpName, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS DENSERANKSALARY  
   FROM #TEMPTABLE  
)  
SELECT EmpName, Salary FROM SALARYCTE WHERE DENSERANKSALARY = 2  

Note

For the 2nd maximum you can replace N with 2, and for 3rd maximum replace N with 3.

Find a detailed article about Find Nth Highest Salary in SQL Server here: Find Nth Highest Salary in SQL Server

Q5. How to find employee manager in SQL Server without using self join?

Solution

Here I will explain how to find employee manager in SQL Server without using self join.

Create a temporary table

Use the below-given SQL query to create temporary table and build schema like this,

CREATE TABLE #Employee  
( EmpId INT, EmpName VARCHAR(30), ManagerId INT )  

Insert some values in #Employee temporary table like this,

INSERT INTO #Employee VALUES(1,'E01',1),(2,'E02',1) ,(3,'E03',2) ,(4,'E04',2)  

SQL query to find employee manager in SQL Server without using self join,

SELECT DISTINCT e.EmpId,e.EmpName,m.EmpId,m.EmpName FROM #Employee e,#Employee m  
WHERE e.ManagerId = m.empId GROUP BY e.EmpId,e.EmpName,m.EmpId,m.EmpName  

Conclusion

In this article, I have explained some tricky and important SQL queries with their solutions. I hope this article was helpful for all of you. Thank you.