How to insert the average of a table into another table?

Question

I created this bank:

create table aluno(
codaluno int primary key,
nome varchar(30),
datanasc date);

create table curso(
codcurso int primary key,
descricao_c varchar(200));

create table disciplina(
coddisc int primary key,
descricao_d varchar(200),
creditos int,
cod_curso int,
constraint fk_codcurso foreign key (cod_curso) references curso (codcurso));

create table matricula(
cod_aluno int,
cod_disc int,
data date,
nota1 number(3,1),
nota2 number(3,1),
nota3 number(3,1),
constraint fk_codaluno foreign key (cod_aluno) references aluno (codaluno),
constraint fk_coddisc foreign key (cod_disc) references disciplina (coddisc));

create table historico(
semestre int,
nota_final number(3,1),
cod_alun int,
cod_dis int,
constraint fk_codal foreign key (cod_alun) references aluno (codaluno),
constraint fk_coddi foreign key (cod_dis) references disciplina (coddisc));

insert into aluno values (01, 'John', to_date('07/04/1998', 'DD/MM/YYYY'));
insert into aluno values (02, 'Anna', to_date('03/07/2000', 'DD/MM/YYYY'));
insert into aluno values (03, 'Marcio', to_date('05/12/1995', 'DD/MM/YYYY'));

insert into curso values (01, 'Computação');
insert into curso values (02, 'Direito');

insert into disciplina values (01, 'Algoritmos 1', 4, 1);
insert into disciplina values (02, 'Código Penal', 4, 2);

insert into matricula values (01, 01, to_date('08/01/2018', 'DD/MM/YYYY'), 9.3, 10.0, 8.8);
insert into matricula values (02, 01, to_date('07/01/2019', 'DD/MM/YYYY'), 8.7, 7.5, 10.0);
insert into matricula values (03, 01, to_date('09/01/2017', 'DD/MM/YYYY'), 10.0, 10.0, 9.5);

insert into historico values (01, 0.0, 01, 01);
insert into historico values (01, 0.0, 02, 01);
insert into historico values (01, 0.0, 03, 01);

I would like to know how to take the average of a student’s 3 grades and insert this value into the final grade that is in the historical table. Obviously I will have to update it and I tried to do this through a trial:

create or replace procedure UPDATEhistorico (   
p_nota_final in historico.nota_final%TYPE)  
is  
begin  
update historico set AVG(nota_final)=p_notafinal where aluno.codaluno = matricula.cod_aluno
and disciplina.coddisc = matricula.cod_disc
and curso.codcurso = disciplina.cod_curso;  
end;

It makes no sense what I tried to do, so I came to ask for help.

Answer 1

It is possible to do through a update as follows:

Update historico set
  nota_final =  (Select (NVL(m.nota1,0)+NVL(m.nota2,0)+NVL(m.nota3,0))/3 from matricula m
                  where m.cod_aluno = historico.cod_alun
                    and m.cod_disc = historico.cod_dis
                    and historico.semestre = 1)    

After the execution of the command, the data of your example was like this:
Consultation 1:

Select * from historico

Upshot:

SEMESTER	NOTA_FINAL	COD_ALUN	COD_DIS
1	9.4	1	1
1	8.7	2	1
1	9.8	3	1

Example available in http://sqlfiddle.com/#! 4/c7407/1.

Since in your example there are the fields for each note, I used the sum and division to generate the mean. I correlated the history and matricula tables to generate the average and update the field nota_final table historico.
Used NVL to predict null values and to consider them as zero. The semester is informed in the update.

Professional Vision:

Professionally, the historico should be updated via Trigger that would automate this update every time a registration note was updated. Since if a registration note is updated and the update command of the historico not run, you will have inconsistent data in your database.
Another point to consider is whether it is really necessary to generate this history or whether the creation of a view would not provide the information you need.
These are points to be analyzed according to your business rule.