Post
Ask Question

Top 5 Tricky/Important SQL Queries And Their Answers

Here we will explain the top 5 most-asked queries in SQL Server with the solutions.
 

Q: 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.
 
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,
  1. CREATE TABLE #tempTable ( StudID INT, StudName VARCHAR(50))  
Insert the values in #tempTable temporary table with some random or duplicate value like this,
  1. 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.
  1. WITH CTESTUD AS (  
  2. SELECT StudID , StudName , row_number() OVER(PARTITION BY StudID , StudName order by StudID ) AS StudRowNumber  
  3. FROM #tempTable  
  4. )  
  5. 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,
  1. 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,
  1. SELECT NAME, ACCOUNTNUMBER, SALARY  
  2. FROM  
  3. (  
  4.    SELECT VALUEFIELD, COLUMNNAME  
  5.    from #tempTable  
  6. ) d  
  7. PIVOT  
  8. (  
  9.    MAX(VALUEFIELD)  
  10.    FOR COLUMNNAME IN (NAME, ACCOUNTNUMBER, SALARY)  
  11. ) 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,
  1. CREATE table #tempDept (DeptID int, DeptName varchar(50));  
  2. CREATE table #tempEmployee (EmpID int, EmpName varchar(50),DeptID int);  
Insert some values in both temporary tables like this,
  1. INSERT INTO #tempDept VALUES (1, 'Admin'),(2, 'HR'),(3, 'IT');  
  2. 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,
  1. SELECT TOP 1 DeptName FROM #tempEmployee E INNER JOIN #tempDept D ON E.DeptID=D.DeptID  
  2. 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,
  1. 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,
  1. 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,
  1. WITH SALARYCTE AS  
  2. (  
  3.    SELECT EmpName, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS DENSERANKSALARY  
  4.    FROM #TEMPTABLE  
  5. )  
  6. 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.
 

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,
  1. CREATE TABLE #Employee  
  2. ( EmpId INT, EmpName VARCHAR(30), ManagerId INT )  
Insert some values in #Employee temporary table like this,
  1. 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,
  1. SELECT DISTINCT e.EmpId,e.EmpName,m.EmpId,m.EmpName FROM #Employee e,#Employee m  
  2. 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.



TRENDING UP

View All

Mastering SQL