3) Employee Database An Enterprise wishes to maintain a database to automate its
operations. Enterprise is divided into certain departments and each department
consists of employees. The following two tables describes the automation schem as
Dept (deptno, dname, loc) Emp (empno, ename, job, mgr, hiredate, sal, comm,
deptno).
a). Update the employee salary by 15%, whose experience is greater than 10 years.
b) Delete the employees, who completed 30 years of service.
c) Display the manager who is having maximum number of employees working under
him?
d) Create a view, which contain employee names and their manager
a) To update the employee salary by 15% for those with more than 10 years of experience, you can use the following SQL statement:
UPDATE Emp
SET sal = sal * 1.15
WHERE hiredate < DATEADD(year, -10, GETDATE())
This query will update the salary for all employees whose hire date is more than 10 years before the current date.
b) To delete employees who have completed 30 years of service, you can use the following SQL statement:
DELETE FROM Emp
WHERE DATEDIFF(year, hiredate, GETDATE()) >= 30
This query will delete all employees whose hire date is at least 30 years before the current date.
c) To display the manager with the maximum number of employees working under them, you can use the following SQL statement:
SELECT mgr, COUNT(*) as num_employees
FROM Emp
GROUP BY mgr
ORDER BY num_employees DESC
LIMIT 1
This query will group the employees by manager (mgr), count the number of employees under each manager, and then return the manager with the highest count as the result.
d) To create a view containing employee names and their managers, you can use the following SQL statement:
CREATE VIEW EmployeeManagerView AS
SELECT e.ename as employee_name, m.ename as manager_name
FROM Emp e
INNER JOIN Emp m ON e.mgr = m.empno
This will create a view named "EmployeeManagerView" that contains two columns: "employee_name" and "manager_name". The view will use a self-join on the "Emp" table to match each employee with their respective manager.