Scribd
Upload
10 views1 page

Imp Query

The document provides SQL queries for various tasks, including deleting duplicate records from the Employees table, selecting employees hired within a specific number of months, and finding the nth highest salary using both a subquery and a Common Table Expression (CTE). Additionally, it includes a query to retrieve even-numbered records from a test table. The document serves as a reference for performing these common SQL operations.

Uploaded by

mr.deepak.indian
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
10 views1 page

Imp Query

The document provides SQL queries for various tasks, including deleting duplicate records from the Employees table, selecting employees hired within a specific number of months, and finding the nth highest salary using both a subquery and a Common Table Expression (CTE). Additionally, it includes a query to retrieve even-numbered records from a test table. The document serves as a reference for performing these common SQL operations.

Uploaded by

mr.deepak.indian
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

### Deleting duplicat Records

WITH EmployeesCTE AS
(
SELECT *, ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) AS RowNumber
FROM Employees
)
DELETE FROM EmployeesCTE WHERE RowNumber > 1

### Replace N with number of months


Select *
FROM Employees
Where DATEDIFF(MONTH, HireDate, GETDATE()) Between 1 and N

##### To find nth highest salary SELECT TOP 1 SALARY


FROM (
SELECT DISTINCT TOP N SALARY
FROM EMPLOYEES
ORDER BY SALARY DESC
) RESULT
ORDER BY SALARY

##### To find nth highest salary using CTE


WITH RESULT AS
(
SELECT SALARY,
DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK
FROM EMPLOYEES
)
SELECT TOP 1 SALARY
FROM RESULT
WHERE DENSERANK = N
############# Getting even or odd record from table

Select * from
(Select *,ROW_NUMBER() over (order by Id) as rowNum
from test)Result
Where rowNum%2=0

You might also like