--ex1 monitoria
SELECT PV.nota
FROM Pessoa P
	INNER JOIN Aluno A
		ON P.matricula_pessoa = A.matricula_aluno
	INNER JOIN Prova PV
		ON PV.matricula_aluno = A.matricula_aluno
WHERE P.nome = 'Augustus Kilter'
	AND PV.ano_semestre = '2010.2'
ORDER BY PV.nota DESC;

--ex2 meu jeito (errado) :(
SELECT * FROM Projeto 
WHERE codigo_projeto IN
	(
		SELECT PJ.codigo_projeto FROM Projeto PJ, Aluno_Turma AT, Pessoa P
			WHERE at.codigo_projeto = pj.codigo_projeto AND at.matricula_aluno = p.matricula_pessoa AND p.nome = 'Joao Custodia'
	)
ORDER BY conceito;

--ex3

--ex4 (meu jeito)
SELECT  p.nome, c.nome AS NOME_CURSO, pj.titulo, at.ano_semestre
	FROM Curso c
	INNER JOIN Aluno_Turma at
		ON at.codigo_curso = c.codigo_curso
	LEFT JOIN Projeto PJ
		ON pj.codigo_projeto = at.codigo_projeto
	INNER JOIN Pessoa P
		ON p.matricula_pessoa = at.matricula_aluno
WHERE at.codigo_disciplina = '5';

--ex5
SELECT c.codigo_curso, c.nome, COUNT(*) AS QTD
FROM Curso c
	LEFT OUTER JOIN Aluno A
		ON a.codigo_curso = c.codigo_curso
GROUP BY c.codigo_curso, c.nome
ORDER BY QTD DESC;

--ex12 PL

CREATE OR REPLACE FUNCTION qtd_creditos
	(mat aluno.matricula_aluno%TYPE)
	RETURN NUMBER IS

retorno NUMBER;

BEGIN
	SELECT COUNT(at.matricula_aluno)*5
			+ COUNT(at.codigo_projeto)*1
			+ COUNT(m.matricula_aluno)*2 INTO retorno	--guarda o retorno do select na variável 
	FROM aluno_turma at, monitoria m, aluno a			--toda consulta em pl tem que guardar numa variável
	WHERE a.matricula_aluno = at.matricula_aluno
		AND m.matricula_aluno = a.matricula_aluno
		AND a.matricula_aluno = mat;
		
	RETURN retorno;
END;
/ --nao esquecer dessa barra sempre depois do end

SELECT qtd_creditos(9999) FROM DUAL;

--ex13
	set serveroutput on

CREATE OR REPLACE PROCEDURE dados_projeto
	(tit IN projeto.titulo%TYPE) IS
	
	v_codigo_projeto projeto.codigo_projeto%TYPE;
	v_titulo projeto.titulo%TYPE;
	v_conceito projeto.conceito%TYPE;
	v_hp projeto.hp%TYPE;
	
BEGIN
	SELECT codigo_projeto, titulo, conceito, hp
		INTO v_codigo_projeto, v_titulo, v_conceito, v_hp
		FROM projeto
		WHERE titulo LIKE tit;
	
	dbms_output.put_line(
		'COD': || v_codigo_projeto ||
		' - TIT: ' || v_titulo ||
		' - CON: ' || v_conceito
		' -HP: ' || v_hp);
		
END;
/

EXECUTE dados_projeto('Rede Aberta');