SQL queries - 1

Find second highest salary of Employee
select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );
 
Find Max Salary from each department
SELECT DeptID, MAX(Salary) FROM Employee GROUP BY DeptID;
 
Print department name instead of department Id for above query  
SELECT DeptName, MAX(Salary) FROM Employee e RIGHT JOIN Department d ON e.DeptId = d.DeptID GROUP BY DeptName;
 
Print the name of distinct employee whose DOB is between 21/01/1947 to 31/12/1975
SELECT DISTINCT EmpName FROM Employees WHERE DOB BETWEEN ‘21/01/1947’ AND ‘31/12/1975’;
 
Find number of employees according to gender whose DOB is between 21/01/1947 to 31/12/1975  
SELECT COUNT(*), sex from Employees WHERE DOB BETWEEN '21/01/1947' AND '31/12/1975' GROUP BY sex;
 
Find name of employee whose name Start with ‘M’  
SELECT * FROM Employees WHERE EmpName like 'M%';
 
Find all Employee records containing the word "Ram", ignoring the case  
SELECT * from Employees WHERE UPPER(EmpName) like '%RAM%';
 
Find duplicate rows in a table  
SELECT * FROM emp a WHERE rowid = ( SELECT MAX(rowid) FROM EMP b WHERE a.empno=b.empno )
 
Delete duplicate rows in a table  
DELETE FROM emp a WHERE rowid != (SELECT MAX(rowid) FROM emp b WHERE a.empno=b.empno);
 
Find all the students, whose marks are greater than average marks  
SELECT student, marks from table WHERE marks > SELECT AVG(marks) from table)
 
Find all employees which are also manager
SELECT e.name, m.name FROM Employee e, Employee m WHERE e.mgr_id = m.emp_id;