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 over 10 years of experience, you can use the following SQL query:
UPDATE Emp
SET sal = sal * 1.15
WHERE (DATEDIFF(year, hiredate, CURRENT_DATE) > 10)
This query will update the "sal" column in the Emp table, multiplying it by 1.15 for employees whose experience (based on hiredate) is greater than 10 years.
b) To delete employees who have completed 30 years of service, you can use the following SQL query:
DELETE FROM Emp
WHERE (DATEDIFF(year, hiredate, CURRENT_DATE) >= 30)
This query will delete rows from the Emp table where the difference in years between the hiredate and current date is greater than or equal to 30.
c) To display the manager who has the maximum number of employees working under him, you can use the following SQL query:
SELECT e.mgr, COUNT(*) as num_employees
FROM Emp e
GROUP BY e.mgr
HAVING COUNT(*) = (SELECT MAX(emp_count)
FROM (SELECT COUNT(*) as emp_count
FROM Emp
GROUP BY mgr) AS counts)
This query will count the number of employees for each manager, and then select the manager(s) with the maximum count. The result will include the manager's employee ID (mgr) and the number of employees (num_employees).
d) To create a view containing employee names and their managers, you can use the following SQL query:
CREATE VIEW EmpMgrView AS
SELECT e.ename as employee_name, e.mgr as manager_id, m.ename as manager_name
FROM Emp e
JOIN Emp m ON e.mgr = m.empno
This query will create a view named "EmpMgrView" that contains the employee name (employee_name), the manager's employee ID (manager_id), and the manager's name (manager_name). The view is created by joining the Emp table with itself based on the manager's employee ID (mgr) and the employee's ID (empno).