How to do a direct calculation in the database

Question

How to do a direct calculation in the database

Navigation

#1 by (0 votes)
#2 by (-1 votes)

1

I would like to calculate a teacher's salary based on the discipline load it teaches, I created a Java algorithm for it, but I can not pass it on to SQL

Algorithm in Java

public double calcSalario() { //Calcula o salário do professor
    switch (this.getEscolaridade()) {
        case 1:
            this.salario = 20 * this.calcCargaHoraria();
            break;
        case 2:
            this.salario = 23 * this.calcCargaHoraria();
            break;
        default:
            this.salario = 25 * this.calcCargaHoraria();
            break;
    }

    return salario;
}

But I can not do anything close to that in SQL, how could I do the same thing directly in the database?

This is the structure of my bank

create table pessoa(
    Id_pessoa tinyint not null,
    Nome_pessoa varchar(45) not null,
    Cpf_pessoa varchar(20) not null,
    Sexo_pessoa enum('m', 'f', 'nd') not null,
    primary key(Id_pessoa)
) default charset = utf8;

create table aluno(
    Id_aluno tinyint not null,
    Id_pessoa tinyint not null,
    Score_aluno double,
    Bolsista_aluno tinyint not null,
    primary key(Id_aluno),
    foreign key(Id_pessoa) references pessoa(Id_pessoa)
) default charset = utf8;

create table professor(
    Id_professor tinyint not null,
    Id_pessoa tinyint not null,
    Efetivo_professor tinyint not null,
    Escolaridade_professor tinyint not null,
    Salario_professor double,
    primary key(Id_professor),
    foreign key(Id_pessoa) references pessoa(Id_pessoa)
) default charset = utf8;

create table disciplina(
    Id_disciplina tinyint not null,
    Nome_disciplina varchar(45) not null,
    CargaHoraria_disciplina int not null,
    primary key(Id_disciplina)
) default charset = utf8;

create table curso(
    Id_curso tinyint not null,
    Nome_curso varchar(45) not null unique,
    primary key(Id_curso)
) default charset = utf8;

create table aluno_disciplina(
    Id_aluno tinyint not null,
    Id_disciplina tinyint not null,
    primary key(Id_aluno, Id_disciplina),
    foreign key(Id_aluno) references aluno(Id_aluno),
    foreign key(Id_disciplina) references disciplina(Id_disciplina)
) default charset = utf8; -- Relaciona o aluno com a disciplina que ele está cursando

create table professor_disciplina(
    Id_professor tinyint not null,
    Id_disciplina tinyint not null,
    primary key(Id_professor, Id_disciplina),
    foreign key(Id_professor) references professor(Id_professor),
    foreign key(Id_disciplina) references disciplina(Id_disciplina)
) default charset = utf8; -- Relaciona o professor com a matéria que ele relaciona

create table disciplina_curso(
    Id_disciplina tinyint not null,
    Id_curso tinyint not null,
    primary key(Id_disciplina, Id_curso),
    foreign key(Id_disciplina) references disciplina(Id_disciplina),
    foreign key(Id_curso) references curso(Id_curso)
) default charset = utf8; -- Relaciona a disciplina com o curso

sql java

asked by anonymous 02.07.2017 / 07:16

2 answers

Laravel Route Authentication How to capture the CMC7 code with the Tesseract API?

score 0 · Answer 1

A simple case solves your problem:

select 
c.nomeProfessor,
case when c.carga = 1 then
    20 * c.carga_horaria
when = 2 then 
[...]
end as salarioProfessor
from tabelaCargaHoraria c inner join tabelaProfessor p on c.idProfessor=p.id

score -1 · Answer 2

I would like to calculate a teacher's salary based on the discipline course load he teaches.

Here is a suggestion, which first adds up the workload of all disciplines the teacher teaches and then calculates the salary.

-- código #1
with calcCargaHoraria as (
SELECT PD.Id_professor, sum(D.CargaHoraria_disciplina) as totalCargaHoraria
  from professor_disciplina as PD 
       inner join disciplina as D on D.Id_disciplina = PD.Id_disciplina
  group by PD.Id_professor
)
SELECT P.Id_professor, CH.totalCargaHoraria, 
       case P.Escolaridade_professor
            when 1 then 20 * CH.totalCargaHoraria
            when 2 then 23 * CH.totalCargaHoraria
            else 25 * CH.totalCargaHoraria end as salario
  from Professor as P
       left join calcCargaHoraria as CH on CH.Id_professor = P.Id_professor;

In the code above it is assumed that a teacher does not have more than one class for the same subject, due to the absence of information about classes. But the usual thing is for a teacher to minister discipline in one or more classes. It seems to me that the data model needs to be reevaluated.