Sql Interview Question -

 1) 2nd Max salary -

SELECT max(salary)  FROM emp_table WHERE salary < (SELECT max(salary)  FROM emp_table);

or

SELECT max(salary) FROM emp_table WHERE salary not in  (SELECT max(salary)                  FROM emp_table); 

2) Nth max salry -

select id,saalry from emp e1 where Nth - 1 = (select count(distinct salary) from emp e2 where e2.salary > e1.salary )

3) Delete Duplicates From a Table in SQL Server

WITH cte AS (

    SELECT 

        contact_id, 

        first_name, 

        last_name, 

        email, 

        ROW_NUMBER() OVER (

            PARTITION BY 

                first_name, 

                last_name, 

                email

            ORDER BY 

                first_name, 

                last_name, 

                email

        ) row_num

     FROM 

        sales.contacts

)

DELETE FROM cte

WHERE row_num > 1;

 (CTE is  common table expression (CTE) )

4) Using GROUP BY clause to find duplicates in a table

SELECT      a,     b,     COUNT(*) occurrences FROM t1  GROUP BY     a,     b 

HAVING     COUNT(*) > 1;

5) Using ROW_NUMBER() function to find duplicates in a table

The following statement uses the ROW_NUMBER() function to find duplicate rows based on both a and b columns: 

WITH cte AS (

    SELECT 

        a, 

        b, 

        ROW_NUMBER() OVER (

            PARTITION BY a,b

            ORDER BY a,b) rownum

    FROM 

        t1

) 

SELECT 

  * 

FROM 

    cte 

WHERE 

    rownum > 1;