--Exercicios
--1)
--Montar a seguinte tabela de saída:
--(ra formatado, nome, nota_final, conceito, 
--faltante(quanto faltou para passar (null 
--para aprovados)), min_exame (quanto precisa 
--tirar no exame para passar (null para 
--alunos com notas maior que 6,0 e menor que
--3,0)))
SELECT 
	SUBSTRING(al.ra,1,9)+'-'+SUBSTRING(al.ra,10,1) AS ra,
	al.nome, CAST(SUM(av.peso * nt.nota) AS DECIMAL(7,2)) AS nota_final, 
	CASE	WHEN CAST(SUM(av.peso * nt.nota) AS DECIMAL(7,2)) >= 6.0 
			THEN 'APROVADO' ELSE 'REPROVADO' END AS conceito,
	CASE	WHEN CAST(SUM(av.peso * nt.nota) AS DECIMAL(7,2)) >= 6.0 
			THEN NULL ELSE CAST(6.0 - SUM(av.peso * nt.nota) AS DECIMAL(7,2)) 
	END AS faltante,
	CASE	WHEN CAST(SUM(av.peso * nt.nota) AS DECIMAL(7,2)) >= 6.0 
			THEN NULL ELSE
				CASE WHEN CAST(SUM (av.peso * nt.nota) AS DECIMAL(7,2)) <= 3.0
				THEN NULL 
				ELSE CAST(12.0 - SUM(av.peso * nt.nota) AS DECIMAL(7,2)) 
				END 
	END AS min_exame
FROM alunos al 
INNER JOIN notas nt
ON al.ra = nt.ra_aluno
INNER JOIN avaliacoes av
ON av.id = nt.id_avaliacao
INNER JOIN materias mat
ON mat.id = nt.id_materia
WHERE mat.nome LIKE '%Arq%'
GROUP BY ra, mat.nome,al.nome
ORDER BY al.nome


--2)
-- Montar a seguinte tabela de saída:
--(ra formatado, nome, nota)
--para os alunos que tem a maior e a menor 
--nota de Arquitetura, P1
SELECT al.nome, av.tipo, nt.nota 
FROM alunos al 
INNER JOIN notas nt
ON al.ra = nt.ra_aluno
INNER JOIN avaliacoes av
ON av.id = nt.id_avaliacao
INNER JOIN materias mat
ON mat.id = nt.id_materia
WHERE mat.nome LIKE '%Arq%' AND av.tipo = 'P1' AND
(nt.nota IN (
SELECT MIN(notas.nota)
FROM notas INNER JOIN avaliacoes
ON notas.id_avaliacao = avaliacoes.id
INNER JOIN materias
ON materias.id = notas.id_materia
WHERE materias.nome LIKE '%Arq%' AND 
avaliacoes.tipo = 'P1'
)
OR 
nt.nota IN (
SELECT MAX(notas.nota)
FROM notas INNER JOIN avaliacoes
ON notas.id_avaliacao = avaliacoes.id
INNER JOIN materias
ON materias.id = notas.id_materia
WHERE materias.nome LIKE '%Arq%' AND 
avaliacoes.tipo = 'P1'
)
)
--3)--Consultar a menor nota de alguma avaliação de alguma matéria
SELECT MIN(nt.nota) AS nota_maxima 
FROM avaliacoes av
INNER JOIN notas nt
ON av.id = nt.id_avaliacao
INNER JOIN materias mat
ON mat.id = nt.id_materia
WHERE av.tipo = 'P1' AND mat.nome LIKE 'Arq%'