Tricky SQL questions with Example

1. Question: Write an SQL query to find the second highest salary from an "Employees" table.

Example:

	SELECT MAX(Salary) AS SecondHighestSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

2. Question: Write an SQL query to find the nth highest salary from an "Employees" table.

Example:

SELECT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT n-1, 1;

Note: Replace "n" with the desired position of the highest salary. For example, if you want to find the 3rd highest salary, replace "n" with 3.

3. Question: Write an SQL query to find all employees who have duplicates in their names.

Example:

SELECT Name, COUNT(*) AS NameCount
FROM Employees
GROUP BY Name
HAVING COUNT(*) > 1;

4.Question: Write an SQL query to find the departments with the highest number of employees.

SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) = (SELECT MAX(EmployeeCount) FROM (SELECT COUNT(*) AS EmployeeCount FROM Employees GROUP BY Department) AS temp);

5.Question: Write an SQL query to find the employees who joined before their managers.

Example:

SELECT e.Name AS EmployeeName, m.Name AS ManagerName
FROM Employees e
INNER JOIN Employees m ON e.ManagerID = m.ID
WHERE e.JoinDate < m.JoinDate;