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