Notes

a. Define a table constraint on Emp that will ensure that every department’s budget is greater than $300,000. (4 marks)

CREATE TABLE Dept ( did INTEGER PRIMARY KEY,
budget REAL CHECK (budget > 300000),
managerid INTEGER References Emp(eid)
);


b. Write SQL statements to delete all information about managers whose salaries are more than three times of the average salary of the employees in the department. Be sure to ensure that all the relevant integrity constraints are satisfied after your updates. (6 marks)

First create a view that calculates the average salary for each department
CREATE VIEW AvgSalaryByDept d AS
SELECT d.did, AVG(e.salary) AS avg_salary
FROM Dept d JOIN Emp e ON d.managerid = e.eid
GROUP BY d.did;

SQL to delete the managers whose salaries are more than three times the average salary

DELETE FROM Emp WHERE eid IN ( SELECT e.eid
FROM Emp e JOIN Works w ON e.eid = w.eid
JOIN AvgSalaryByDept a ON w.did = a.did
WHERE e.salary > 3 * a.avg_salary
);

Update the table to set null for deleted managers.

UPDATE Dept
SET managerid = NULL
WHERE managerid IN (
SELECT eid
FROM Emp
WHERE eid NOT IN (
SELECT eid
FROM Works
)
);