SQL  

Top 5 Tricky/Important SQL Queries And Their Answers

Introduction

Here we will explain the top 5 most-asked queries in SQL Server with the solutions. Read more about SQL Queries here:  Queries in SQL Server

Q 1. How to delete duplicate records from SQL Table?

Solution

Duplicate records in a SQL Server table can be a serious issue. Here I will explain how to Delete Duplicate Record or Rows from Table in SQL Server. Find a detailed article about Delete Duplicate Rows in from a table in SQL Server here:  Delete Duplicate Rows in from a table in SQL Server

To delete the duplicate rows from the table in SQL Server, you follow these steps,

  • Find duplicate rows using ROW_NUMBER() function.
  • Use DELETE statement to remove the duplicate rows.

Below I've given the complete example,

Create a temporary table

Use the below given SQL query to create temporary table and build schema like this, Find a detailed article about temporary table in SQL Server here: Temporary table in SQL Server here

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

Insert the values in #tempTable temporary table with some random or duplicate value like this,

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

SQL query to delete duplicate rows

I used CTEs (Common Table Expression) and ROW_NUMBER function to delete the duplicate rows or records in SQL Server table.

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  

Q2. How to transform row into column SQL Server?

Solution

Here I will explain how to transform row into column SQL Server.

Create a temporary table

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

  1. CREATE table #tempTable (IDFEILD int, VALUEFIELD varchar(20), COLUMNNAME varchar(50));  

Insert some values in a temporary table like this,

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

SQL query to transform row into column

In SQL Server you can use the PIVOT function to transform the data from rows to columns like this,

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

Q3. How to find the department with the maximum number of employees in SQL Server?

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.

Membership not found