historico.sql

SELECT 
    ND.NOME AS disciplina,
    PT.PERIODO AS periodo,
    AC.TURNOATUAL AS turnoAtual,
    D.CARGAHORARIATOTAL AS cargaHorariaTotal,
    N.MEDIAFINAL AS mediaFinal,
    PE.NOME AS docenteNome,
    PE.CPF AS docenteCPF,
    DPA.SITUACAO AS alunoSituacao,
CASE 
    WHEN PE.GRAUINSTRUCAO = 5 THEN "Graduação"
    WHEN PE.GRAUINSTRUCAO = 6 THEN "Especialização"
    WHEN PE.GRAUINSTRUCAO = 7 THEN "Mestrado"
    WHEN PE.GRAUINSTRUCAO = 8 THEN "Doutorado"
END AS docenteTitulacao
FROM 
    OPESSOAS AS PE, 
    OUSUARIOS AS US, 
    AALUNO AS AL, 
    AALUNOCURSO AS AC, 
    APERIODOSALUNO AS PA, 
    APERIODOLETIVO AS PL, 
    ANOMEDISCIPLINA AS ND, 
    APERIODOTURMA AS PT,
    APROFESSORDISCIPLINA AS PD, 
    ADISCIPLINASPERIODOSALUNO AS DPA
INNER JOIN ANOTAS N ON N.CODDISCIPLINASPERIODOSALUNO = DPA.CODDISCIPLINASPERIODOSALUNO
INNER JOIN ADISCIPLINAS D ON D.CODDISCIPLINA = DPA.CODDISCIPLINA 
INNER JOIN APERIODOTURMADISCIPLINA PTD ON PTD.CODDISCIPLINA = D.CODDISCIPLINA
INNER JOIN APROFESSORDISCIPLINA PDISC ON PDISC.CODPERIODOTURMADISCIPLINA = PTD.CODPERIODOTURMADISCIPLINA
INNER JOIN APROFESSOR PR ON PR.CODPROFESSOR = PDISC.CODPROFESSOR
WHERE 
    AC.MATRICULA = '<matricula>'
    AND PE.CODPESSOA = US.CODPESSOA
    AND US.CODUSUARIO = PR.CODUSUARIO
    AND AL.CODALUNO = AC.CODALUNO
    AND AC.CODALUNOCURSO = PA.CODALUNOCURSO 
    AND PA.CODPERIODOSALUNO = DPA.CODPERIODOSALUNO
    AND DPA.CODPERIODOTURMA = PT.CODPERIODOTURMA
    AND PTD.CODPERIODOTURMA = PT.CODPERIODOTURMA
    AND PT.CODPERIODOLETIVO = PL.CODPERIODOLETIVO
    AND ND.CODNOMEDISCIPLINA = D.CODNOMEDISCIPLINA
    AND AL.EXCLUIDO = 'N' 
    AND PA.EXCLUIDO = 'N'
    AND D.EXCLUIDO = 'N' 
    AND PE.EXCLUIDO = 'N'
    AND DPA.EXCLUIDO = 'N' 
    AND AC.EXCLUIDO = 'N'
    AND PT.EXCLUIDO = 'N' 
    AND DPA.SITUACAO IN ('2', '9')
GROUP BY (DPA.CODDISCIPLINASPERIODOSALUNO)
ORDER BY PT.PERIODO, ND.NOME, PL.NOME;