|
SQL> create table dept
2 ( deptno int primary key,
3 sum_of_salary number
4 );
Table created.
SQL>
SQL> create table emp
2 ( empno int primary key,
3 deptno references dept,
4 salary number
5 );
Table created.
SQL>
SQL> insert into dept ( deptno ) values ( 1 );
1 row created.
SQL>
SQL> insert into dept ( deptno ) values ( 2 );
1 row created.
SQL> insert into emp ( empno, deptno, salary )
2 values ( 100, 1, 55 );
1 row created.
SQL>
SQL> insert into emp ( empno, deptno, salary )
2 values ( 101, 1, 50 );
1 row created.
SQL>
SQL>
SQL> update dept
2 set sum_of_salary =
3 ( select sum(salary)
4 from emp
5 where emp.deptno = dept.deptno )
6 where dept.deptno = 1;
1 row updated.
SQL>
SQL> commit;
Commit complete.
SQL> select * from emp;
EMPNO DEPTNO SALARY
---------- ---------- ----------
100 1 55
101 50
SQL> select * from dept;
DEPTNO SUM_OF_SALARY
---------- -------------
1 105
2
SQL>
SQL>
SQL>
SQL> drop table dept cascade constraint;
Table dropped.
SQL>
SQL> drop table emp cascade constraint;
Table dropped.
|