Using Employee Database perform the following queries
a). Determine the names of employee, who earn more than their managers.
b.) Determine the names of employees, who take highest salary in their departments.
c) .Determine the employees, who are located at the same place.
d). Determine the employees, whose total salary is like the minimum Salary of any
department.
e). Determine the department which does not contain any employees
a) To find the names of employees who earn more than their managers, we can use the following query:
SELECT e1.employee_name
FROM employee e1
JOIN employee e2 ON e1.manager_id = e2.employee_id
WHERE e1.salary > e2.salary;
b) To find the names of employees who take the highest salary in their departments, we can use the following query:
SELECT e1.employee_name
FROM employee e1
JOIN (
SELECT department_id, MAX(salary) AS max_salary
FROM employee
GROUP BY department_id
) e2 ON e1.department_id = e2.department_id AND e1.salary = e2.max_salary;
c) To find the employees who are located at the same place, we can use the following query:
SELECT e1.employee_name, e2.location
FROM employee e1
JOIN employee e2 ON e1.location = e2.location AND e1.employee_id != e2.employee_id;
d) To find the employees whose total salary is like the minimum salary of any department, we can use the following query:
SELECT e1.employee_name
FROM employee e1
JOIN (
SELECT department_id, MIN(salary) AS min_salary
FROM employee
GROUP BY department_id
) e2 ON e1.department_id = e2.department_id AND e1.salary = e2.min_salary;
e) To find the department which does not contain any employees, we can use the following query:
SELECT d.department_name
FROM department d
LEFT JOIN employee e ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;