SQL queries - 2

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;