How to update the result field based on criteria?

Navigation
4

I have the following table: 

CREATE TABLE ALUNO (
  RA NUMBER(9),
  DISCIPLINA VARCHAR2(30),
  MEDIA NUMBER(3,1),
  CARGA_HORA NUMBER(2),
  FALTAS NUMBER(2),
  RESULTADO VARCHAR2(10)
);

And the following data: 

INSERT INTO ALUNO VALUES (1,'SQL',7.5,80,20,'');
INSERT INTO ALUNO VALUES (2,'PLSQL',5.5,80,20,'');
INSERT INTO ALUNO VALUES (3,'MBD',7.5,80,40,'');

I have the following sql: 

select ra,
   Disciplina,
   Media,
   Carga_Hora,
   Faltas,
   case when (Media >= 7) and (trunc((Faltas / Carga_Hora * 100)) <= 25) then 'APROVADO'
        when (Media between 5 and 6.9) and (trunc((Faltas / Carga_Hora * 100)) <= 25) then 'EXAME'
   else 'REPROVADO'  end Resultado

from student  order by ra

I need to create a block to fill the result field:

  • If the student has an average of 7.0 or higher and his or her absences do not exceed 25% of the course hours, the result will be: APROVADO .
  • If the student has an average of less than 7.0 and his or her absences do not exceed 25% of the course's hours, the result will be: EXAME .
  • For other cases the result will be: REPROVADO .
    • asked by anonymous 12.11.2014 / 21:04

    1 answer

    1

    If the idea is to update the table with the results, just make a UPDATE with a CASE : 

    UPDATE ALUNO SET RESULTADO = (
    CASE
      WHEN MEDIA >= 7.0 AND FALTAS / CARGA_HORA <= 0.25 THEN 'APROVADO'
      WHEN FALTAS / CARGA_HORA <= 0.25 THEN 'EXAME'
      ELSE 'REPROVADO'
    END
  );

    Demo on SqlFiffle

        
    12.11.2014 / 22:29
    How to automate merge in git / github with hook or another tool? How do I convert text to HTML entities in a way compatible with CKEditor?