1. Employees earning more than employee 103

SELECT first_name, last_name
FROM employees
WHERE salary >
      (SELECT salary 
       FROM employees
       WHERE employee_id = 103);

2. Employees with the same job as employee 169

SELECT first_name, last_name, salary, department_id, job_id
FROM employees
WHERE job_id =
      (SELECT job_id
       FROM employees
       WHERE employee_id = 169);

3. Employees earning the minimum salary of any department

SELECT first_name, last_name, salary, department_id
FROM employees
WHERE salary IN (
      SELECT MIN(salary)
      FROM employees
      GROUP BY department_id);

4. Employees earning above the company’s average salary

SELECT employee_id, first_name, last_name
FROM employees
WHERE salary >
      (SELECT AVG(salary)
       FROM employees);

5. Employees who report directly to Payam

SELECT first_name, last_name, employee_id, salary
FROM employees
WHERE manager_id =
      (SELECT employee_id
       FROM employees
       WHERE first_name = 'Payam');

6. All employees working in the Finance department

SELECT e.department_id, e.first_name, e.job_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Finance';

7. Employee whose salary is 3000 and manager ID is 121

SELECT *
FROM employees
WHERE (salary, manager_id) = (3000, 121);

8. Employees with IDs 134, 159, or 183

SELECT *
FROM employees
WHERE employee_id IN (134, 159, 183);

9. Employees earning between 1000 and 3000

SELECT *
FROM employees
WHERE salary BETWEEN 1000 AND 3000;

10. Employees earning between the lowest salary and 2500

SELECT *
FROM employees
WHERE salary BETWEEN
      (SELECT MIN(salary) FROM employees) AND 2500;

11. Employees not working in departments whose managers’ IDs are 100–200

SELECT *
FROM employees
WHERE department_id NOT IN (
      SELECT department_id
      FROM departments
      WHERE manager_id BETWEEN 100 AND 200);

12. Employees earning the second-highest salary

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

13. Employees in the same department as Clara, except Clara

SELECT first_name, last_name, hire_date
FROM employees
WHERE department_id =
      (SELECT department_id FROM employees WHERE first_name = 'Clara')
  AND first_name <> 'Clara';

14. Employees working in a department that has at least one employee with ‘T’ in their name

SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id IN (
      SELECT department_id
      FROM employees
      WHERE first_name LIKE '%T%');

15. Employees earning more than the average salary AND working in a department where someone has a ‘J’ in their name

SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
  AND department_id IN (
      SELECT department_id
      FROM employees
      WHERE first_name LIKE '%J%');

16. Employees whose department is located in Toronto

SELECT first_name, last_name, employee_id, job_id
FROM employees
WHERE department_id =
      (SELECT department_id
       FROM departments
       WHERE location_id =
           (SELECT location_id
            FROM locations
            WHERE city = 'Toronto'));

17. Employees earning less than ANY salary of MK_MAN employees

SELECT employee_id, first_name, last_name, job_id
FROM employees
WHERE salary < ANY (
      SELECT salary
      FROM employees
      WHERE job_id = 'MK_MAN');

18. Same as above but excluding MK_MAN job title

SELECT employee_id, first_name, last_name, job_id
FROM employees
WHERE salary < ANY (
      SELECT salary FROM employees WHERE job_id = 'MK_MAN')
  AND job_id <> 'MK_MAN';

19. Employees earning more than ALL salaries of PU_MAN employees (excluding PU_MAN)

SELECT employee_id, first_name, last_name, job_id
FROM employees
WHERE salary > ALL (
      SELECT salary FROM employees WHERE job_id = 'PU_MAN')
  AND job_id <> 'PU_MAN';

20. Employees earning more than every department’s average salary

SELECT employee_id, first_name, last_name, job_id
FROM employees
WHERE salary > ALL (
      SELECT AVG(salary)
      FROM employees
      GROUP BY department_id);

21. Show employee names and departments only if someone earns more than 3700

SELECT first_name, last_name, department_id
FROM employees
WHERE EXISTS (
      SELECT 1
      FROM employees
      WHERE salary > 3700);

22. Departments where at least one employee exists, along with total salary

SELECT d.department_id, totals.total_amount
FROM departments d
JOIN (
      SELECT department_id, SUM(salary) AS total_amount
      FROM employees
      GROUP BY department_id
     ) totals
ON d.department_id = totals.department_id;

23. Replace job titles using CASE (ST_MAN → SALESMAN, IT_PROG → DEVELOPER)

SELECT employee_id, first_name, last_name,
       CASE job_id
            WHEN 'ST_MAN' THEN 'SALESMAN'
            WHEN 'IT_PROG' THEN 'DEVELOPER'
            ELSE job_id
       END AS designation,
       salary
FROM employees;

24. Tag employees as HIGH or LOW based on average salary

SELECT employee_id, first_name, last_name, salary,
       CASE
           WHEN salary >= (SELECT AVG(salary) FROM employees)
           THEN 'HIGH'
           ELSE 'LOW'
       END AS SalaryStatus
FROM employees;

25. Show salary comparison with average + HIGH/LOW status

SELECT employee_id, first_name, last_name,
       salary AS SalaryDrawn,
       ROUND(
            salary - (SELECT AVG(salary) FROM employees),
            2
       ) AS AvgCompare,
       CASE
           WHEN salary >= (SELECT AVG(salary) FROM employees)
           THEN 'HIGH'
           ELSE 'LOW'
       END AS SalaryStatus
FROM employees;