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,
- 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.