Q1. Find the 2nd Highest Salary

Uses a subquery to exclude the maximum salary and find the next highest.

SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Q2. Find Duplicate Values in a Column

Uses GROUP BY and HAVING to detect duplicates.

SELECT name, COUNT(*)
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

Q3. Records in One Table but Not Another

Filters records that do not exist in a related table.

SELECT *
FROM employees
WHERE id NOT IN (SELECT employee_id FROM payroll);

Q4. Top 3 Highest Salaries (MySQL)

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Q5. Employees with Same Salary

Uses EXISTS to find matching salary values.

SELECT *
FROM employees e1
WHERE EXISTS (
  SELECT 1
  FROM employees e2
  WHERE e1.salary = e2.salary
  AND e1.id <> e2.id
);

Q6. Department-wise Highest Salary

SELECT department, MAX(salary) AS max_salary
FROM employees
GROUP BY department;

Q7. Employees Who Never Received Bonus

SELECT e.id, e.name
FROM employees e
LEFT JOIN bonus b ON e.id = b.employee_id
WHERE b.employee_id IS NULL;

Q8. Highest Salary Employee Per Department

SELECT e.*
FROM employees e
JOIN (
  SELECT department, MAX(salary) AS max_salary
  FROM employees
  GROUP BY department
) t
ON e.department = t.department
AND e.salary = t.max_salary;

Q9. Employee Count by Joining Year

SELECT YEAR(join_date) AS join_year, COUNT(*) AS total
FROM employees
GROUP BY YEAR(join_date)
ORDER BY join_year;

Q10. Employees Earning Above Dept Average

SELECT e.*
FROM employees e
JOIN (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) t
ON e.department = t.department
WHERE e.salary > t.avg_salary;

Q11. 5th Highest Salary

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 4;

Q12. Employees on Multiple Projects

SELECT employee_id, COUNT(*) AS project_count
FROM project_assignments
GROUP BY employee_id
HAVING COUNT(*) > 1;

Q13. Salary Above Company Average

SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Q14. 3 Most Recently Joined Employees

SELECT *
FROM employees
ORDER BY join_date DESC
LIMIT 3;

Q15. Employees Without Manager

SELECT id, name
FROM employees
WHERE manager_id IS NULL;

Q16. Departments with More Than 2 Employees

SELECT department, COUNT(*) AS total
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;

Q17. Salary Equals Department Average

SELECT e.*
FROM employees e
JOIN (
  SELECT dept, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept
) t ON e.dept = t.dept
WHERE e.salary = t.avg_salary;

Q18. Employees Sharing Same Project

SELECT p1.employee_id, p2.employee_id AS colleague
FROM project_assignments p1
JOIN project_assignments p2
ON p1.project_id = p2.project_id
AND p1.employee_id <> p2.employee_id;