SQL-Important Sql Queries for interview

How to fetch 2nd highest salary:


    SELECT Salary,custname FROM
   ( SELECT Salary,custname,ROW_NUMBER() OVER(ORDER BY Salary) As RowNum
      FROM customer ) As A
    WHERE A.RowNum IN (2,(select count(salary) from customer))



Delete Duplicate records:

1st Method:

DELETE FROM dbo.Customer
WHERE ID NOT IN (SELECT MIN(ID)
FROM dbo.Customer GROUP BY Custname,Salary)


2nd Method:
Delete duplicate records using Row_Number():

WITH DuplicateRecords AS
(
SELECT *,row_number() OVER(PARTITION BY Custname,Salary ORDER BY
Custname)
AS RowNumber FROM Customer
)
DELETE FROM DuplicateRecords WHERE RowNumber>1


3rd Method:
SELECT DISTINCT (Custname),address,salary INTO TempTable FROM customer
DELETE dbo.Customer WHERE CustName
IN (SELECT CustName FROM TempTable)
INSERT dbo.Customer SELECT Custname,salary,address FROM TempTable
DROP TABLE TempTable




change male to female:

UPDATE table_1 SET gender =
(
CASE
WHEN
((gender='m'))
THEN
'f'
ELSE
'm'
END
)