Question

Suppose you are given a relation emp(empid, dept, salary) and wish to maintain a materialized view deptsalary(dept, totalsalary) which stores the total salary for all employees of each department. Suppose the system does not support materialized views but supports triggers. Write an SQL trigger on insert on emp to keep the relation deptsalary up to date. Do not worry about deletes or updates. You can assume that there is already a tuple for each department in deptsalary so you do not need to worry about new deparments.

Answer 1

WITH deptsalary AS (

SELECT dept, SUM(salary)

FROM emp

GROUP BY dept)

SELCT *

FROM deptsalary

Step-by-step explanation:

deptsalary is temporary table.

dept and its respective sum of salary is to be displayed