IMPORTANT SQLS QUERIES.

These are important for interview and reference for other needs in day to day jobs.

1. ) Second Highest Salary: We can achieve this using - MAX , LIMIT and TOP

MAX:

select max(salary) from EMP where salary < (select max(salary) from EMP)

select max(salary) from EMP where salary not in(select max(salary) from EMP)

USING ROWNUM:
select * from (
select a.*, row_number() over (order by salary desc) as row_num from Emp a
) where row_num = 2;

USING RANK:

select * from ( select a.*, rank() over (order by salary desc) as rank from Emp a ) where rank = 2;

LIMIT: select salary from ( select salary from EMP ORDER BY salary DESC LIMIT 2) AS EMP ORDER BY salary limit 1;

• Here the inner sql will return the top 2 rows in desc order.
• last order by will arrange again in ascending and return first row.

TOP: Select Top 1 salary from ( select Top 2 salary from EMP ORDER BY salary desc) AS EMP1 ORDER BY salary ASC;

2.) QUERY to find duplicate records in table.

select emp_name from EMP group by emp_name having count(emp_name) > 1;