Interview Questions in SQL

1. Get all employee details from the employee table

Select * from M_Employees 

2. Get length of NAME from employee table

select len(emp1) from M_Employees

3. Get Name from employee table after replacing 'a' with '$'

select REPLACE(emp1,'a','$') from M_Employees

4.  Write syntax to delete table employee

   DROP table employee;

5. Get employee details from employee table who joined after January 31^st

Select * from M_Employees where jdate >'01/31/2014'

      6. Get TOP 2 salary from employee table 

   select top 2 salary from M_Employees order by salary Desc

   7. 3rd Highest Salary of Employee

       Method 1: 

         select distinct salary from M_Employees e1 where 3= (select count (distinct salary)

         from M_Employees e2  wheree1.salary<=e2.salary)

   Method 2: 

       select MIN(salary) from  M_Employees where salary in   

  (select top 3 salary from M_Employees order by Salary desc)

Method 3: 

select max(salary) from M_Employees where salary Not in (select top 2 salary from M_Employees order  by salary Desc )  

8. 2nd Highest Salary of Employee

Method 1:

 select max(salary) from M_Employees where salary Not in 

(select max(salary)from M_Employees )

     

           Method 2: 

     select MIN(salary) from  M_Employees where salary in (select top 2               salary from M_Employees order by Salary desc)

          Method 3:      

     SELECT max(salary) FROM M_Employees WHERE salary < (SELECT max(salary)

     FROM M_Employees)

         Method 4:          

          SELECT max(salary) FROM M_Employees WHERE salary Not in (select top 1 salary     from M_Employees order  by salary Desc)

   

    Method 5:      

5             SELECT top 1 salary FROM (select top 2 salary from M_Employees order by salary   Desc) as sal order by salary