use bdtrabalho

1. Consultar todas as notas e freqüências do aluno 1.
   Apresente também o nome da disciplina e o nome do professor.

select
	d.dis_nome,
	p.prf_nome,
	a.apr_nota,
	convert(
		varchar(5),
		convert(
			decimal(4,1),
			((1 - a.apr_falta * 1.0 / d.dis_ch) * 100)
		)
	) + '%' frequencia
from
	aproveitamentos a,
	disciplinas d,
	professores p
where
	a.dis_codigo = d.dis_codigo and
	d.prf_codigo = p.prf_codigo and
	a.alu_rm = 1

2. Consultar todos os alunos que freqüentaram a disciplina 1,
   apresentando o nome do aluno, nota e freqüência.

select
	a.alu_nome,
	ap.apr_nota,
    convert(
		varchar(5),
		convert(
			decimal(4,1),
			((1 - ap.apr_falta * 1.0 / d.dis_ch) * 100)
		)
	) + '%' frequencia
	
from
	aproveitamentos ap,
	alunos a,
	disciplinas d
where
	ap.dis_codigo = 1 and
	ap.alu_rm = a.alu_rm and
	ap.dis_codigo = d.dis_codigo

3. Consultar os alunos que reprovaram somente por falta na disciplina 1
   no primeiro semestre de 2008.

select
	al.alu_nome,
	ap.apr_ano,
	ap.apr_sem,
	Convert(decimal(3,1),((1 - ap.apr_falta * 1.0 / d.dis_ch) * 100)) frequencia
from
	aproveitamentos ap,
	disciplinas d,
	alunos al
where
	ap.dis_codigo = 1 and
	ap.apr_ano = 2008 and
	ap.apr_sem = 1 and
	ap.dis_codigo = d.dis_codigo and
	ap.alu_rm = al.alu_rm and
	(1 - ap.apr_falta * 1.0 / d.dis_ch) < 0.75 and
	not ap.apr_nota < 7	-- para não exibir os alunos reprovados por nota e falta => Buslaboo

4. Consultar os alunos que reprovaram somente por nota na disciplina 1
   no primeiro semestre de 2008.

select
	al.alu_nome,
	ap.apr_ano,
	ap.apr_sem,
	ap.apr_nota
from
	aproveitamentos ap,
	alunos al,
	disciplinas d
where
	ap.dis_codigo = 1 and
	ap.apr_ano = 2008 and
	ap.apr_sem = 1 and
	ap.alu_rm = al.alu_rm and
	ap.dis_codigo = d.dis_codigo and
	ap.apr_nota < 7 and
	not (1 - ap.apr_falta * 1.0 / d.dis_ch) < 0.75 -- Para não recuperar os alunos reprovado por nota e falta
	

5. Consultar os alunos que reprovaram por nota e também por freqüência
   na disciplina 1 no primeiro semestre de 2008.

select
	al.alu_nome,
	ap.apr_ano,
	ap.apr_sem,
	ap.apr_nota,
	Convert(decimal(3,1),((1 - ap.apr_falta * 1.0 / d.dis_ch) * 100)) frequencia
from
	aproveitamentos ap,
	disciplinas d,
	alunos al
where
	ap.dis_codigo = 1 and
	ap.apr_ano = 2008 and
	ap.apr_sem = 1 and
	ap.dis_codigo = d.dis_codigo and
	ap.alu_rm = al.alu_rm and
	ap.apr_nota < 7 and
	(1 - ap.apr_falta * 1.0 / d.dis_ch) < 0.75

6. Consultar os alunos que reprovaram por nota ou também por freqüência
   na disciplina 1 no primeiro semestre de 2008.

select
	al.alu_nome,
	ap.dis_codigo,
	ap.apr_ano,
	ap.apr_sem,
	Convert(decimal(5,2),(1 - ap.apr_falta * 1.0 / d.dis_ch))*100 Frequencia,
	ap.apr_nota Nota
from
	aproveitamentos ap,
	disciplinas d,
	alunos al
where
	ap.dis_codigo = 1 and
	ap.apr_ano = 2008 and
	ap.apr_sem = 1 and
	ap.dis_codigo = d.dis_codigo and
	ap.alu_rm = al.alu_rm and
	(ap.apr_nota < 7 or
	(1 - ap.apr_falta * 1.0 / d.dis_ch) < 0.75)

7. Consultar a média geral de cada aluno, apresentando o RM e o nome do aluno.

select
	al.alu_rm,
	al.alu_nome,
	Convert(decimal(4,2),avg(ap.apr_nota)) media
from
	aproveitamentos ap,
	alunos al
where
	ap.alu_rm = al.alu_rm
group by
	al.alu_rm,
	al.alu_nome

8. Consultar a média geral dos alunos que possuem a média inferior a 6,0.

select
	al.alu_rm,
	al.alu_nome,
	Convert(decimal(4,2),avg(ap.apr_nota)) media
from
	aproveitamentos ap,
	alunos al
where
	ap.alu_rm = al.alu_rm
group by
	al.alu_rm,
	al.alu_nome
having
	avg(ap.apr_nota) < 6

9. Consultar a média geral somente dos alunos que possuem a média superior ou igual a 6,0.

select
	al.alu_rm,
	al.alu_nome,
	Convert(decimal(4,2),avg(ap.apr_nota)) media
from
	aproveitamentos ap,
	alunos al
where
	ap.alu_rm = al.alu_rm
group by
	al.alu_rm,
	al.alu_nome
having
	avg(ap.apr_nota) >= 6

10. Consultar a média geral de cada disciplina, apresentando o código e o nome da disciplina.

select
	d.dis_codigo,
	d.dis_nome,
	Convert(decimal(4,2),avg(ap.apr_nota)) media
from
	aproveitamentos ap,
	disciplinas d
where
	ap.dis_codigo = d.dis_codigo
group by
	d.dis_codigo,
	d.dis_nome

11. Consultar os alunos que foram reprovados na disciplina 1, no primeiro
    semestre de 2008, apresentando o nome do aluno, nota e a freqüência.

select
	al.alu_nome,
	ap.apr_ano,
	ap.apr_sem,
	ap.apr_nota,
	Convert(decimal(4,1),((1 - ap.apr_falta * 1.0 / d.dis_ch) * 100)) frequencia
from
	aproveitamentos ap,
	disciplinas d,
	alunos al
where
	ap.dis_codigo = 1 and
	ap.apr_ano = 2008 and
	ap.apr_sem = 1 and
	ap.dis_codigo = d.dis_codigo and
	ap.alu_rm = al.alu_rm and
	(ap.apr_nota < 5 or
	(1 - ap.apr_falta * 1.0 / d.dis_ch) < 0.75)

12. Consultar, em ordem decrescente, as disciplinas que reprovaram mais alunos, mostrando
    o nome da disciplina e a quantidade de alunos que não conseguiram a nota 6,0.

select
	d.dis_nome,
	count(*) qtd
from
	aproveitamentos a,
	disciplinas d
where
	a.apr_nota < 6 and
	a.dis_codigo = d.dis_codigo
group by
	a.dis_codigo,
	d.dis_nome
order by
	qtd desc
	

13. Consultar os alunos mais faltosos no primeiro semestre de 2008 da disciplina 1.

select
	al.alu_nome,
	ap.apr_falta
from
	aproveitamentos ap,
	alunos al
where
	ap.apr_ano = 2008 and
	ap.apr_sem = 1 and
	ap.dis_codigo = 1 and
	ap.alu_rm = al.alu_rm
order by
	ap.apr_falta desc


14. Consultar a quantidade de alunos que cursou a disciplina 1 no primeiro semestre de 2008.

select
	count(*) qtd
from
	aproveitamentos a
where
	a.dis_codigo = 1 and
	a.apr_ano = 2008 and
	a.apr_sem = 1

15. Consultar a quantidade de disciplina que cada aluno cursou no primeiro semestre de 2008,
    apresentando o RM, o nome do aluno e a quantidade de disciplinas cursadas.

select
	ap.alu_rm,
	al.alu_nome,
	count(*) disciplinas_cursadas
from
	aproveitamentos ap,
	alunos al
where
	ap.alu_rm = al.alu_rm and
	ap.apr_ano = 2008 and
	ap.apr_sem = 1
group by
	ap.alu_rm,
	al.alu_nome

16. Consultar as disciplinas que reprovaram aluno no primeiro semestre de 2008, apresentando
    a quantidade de alunos reprovado por disciplina.

select
	d.dis_nome,
	count(*) qtd_reprovados
from
	aproveitamentos a,
	disciplinas d
where
	a.dis_codigo = d.dis_codigo and
	a.apr_ano = 2008 and
	a.apr_sem = 1 and
	(a.apr_nota < 5 or
	(1 - a.apr_falta * 1.0 / d.dis_ch) < 0.75)
group by
	a.dis_codigo,
	d.dis_nome

17. Consultar as disciplinas do curso 1, apresentando o nome da disciplina, carga horária,
    o limite de faltas e o nome do professor que a ministra.

select
	d.dis_nome,
	d.dis_ch,
	Convert (int, (d.dis_ch * 0.25)) limite_faltas,
	p.prf_nome
from
	cursos c,
	disciplinas d,
	professores p
where
	c.cur_codigo = 1 and
	d.cur_codigo = c.cur_codigo and
	d.prf_codigo = p.prf_codigo

18. Consultar as disciplinas ministradas pelo professor 1, apresentando o nome da disciplina
    que ministra e o nome do curso que essa disciplina é ministrada.

select
	c.cur_nome,
	d.dis_nome
from
	disciplinas d,
	cursos c
where
	d.prf_codigo = 1 and
	d.cur_codigo = c.cur_codigo

19. Consultar, para cada disciplina, o nome do curso que é ministrada e o nome do professor.

select
	c.cur_nome,
	d.dis_nome,
	p.prf_nome
from
	disciplinas d,
	cursos c,
	professores p
where
	d.cur_codigo = c.cur_codigo and
	d.prf_codigo = p.prf_codigo

20. Consultar, para cada aluno, qual é o curso que ele cursa.

select
	distinct (al.alu_nome), -- distinct evita duplicação de registros
	c.cur_nome
from
	alunos al,
	aproveitamentos ap,
	disciplinas d,
	cursos c
where
	ap.dis_codigo = d.dis_codigo and
	ap.alu_rm = al.alu_rm and
	d.cur_codigo = c.cur_codigo

21. Consultar somente as disciplinas que reprovou mais do que dois alunos no primeiro
    semestre de 2008.

select
	d.dis_nome,
	count(*) qtd_reprovados
from
	aproveitamentos ap,
	disciplinas d
where
	ap.apr_ano = 2008 and
	ap.apr_sem = 1 and
	ap.dis_codigo = d.dis_codigo and
	(ap.apr_nota < 5 or
	(1 - ap.apr_falta * 1.0 / d.dis_ch) < 0.75)
group by
	ap.dis_codigo,
	d.dis_nome
having
	count(*) > 2

22. Consultar somente os alunos (RM e nome) que foram reprovados em mais do que duas
    disciplinas no primeiro semestre de 2008.

-- Execute os inserts abaixo antes do select para reprovar o aluno buslaboo em mais do que duas disciplinas.
insert into aproveitamentos values (2,4,2008,1,2,10)
insert into aproveitamentos values (3,4,2008,1,2,10)

select
	al.alu_rm,
	al.alu_nome,
	count(*) qtd_reprovados
from
	aproveitamentos ap,
	alunos al,
	disciplinas d
where
	ap.apr_ano = 2008 and
	ap.apr_sem = 1 and
	ap.alu_rm = al.alu_rm and
	ap.dis_codigo = d.dis_codigo and
	(ap.apr_nota < 5 or
	(1 - ap.apr_falta * 1.0 / d.dis_ch) < 0.75)
group by
	al.alu_rm,
	al.alu_nome
having
	count(*) > 2

23. Consultar somente os professores que ministram mais do que duas disciplinas.

-- Execute os inserts abaixo antes do select para que o professor 1 possa ministrar mais do que duas disciplinas.
insert into disciplinas values ('Banco de Dados I', 72,  1,1)
insert into disciplinas values ('Banco de Dados II', 72, 1,1)

select
	p.prf_codigo, p.prf_nome,
	count(*) qtd
from
	disciplinas d,
	professores p
where
	d.prf_codigo = p.prf_codigo
group by
	p.prf_codigo,
	p.prf_nome
having
	count(*) > 2

24. Consultar somente as disciplinas que foram cursadas por no mínimo três alunos no
    primeiro semestre de 2008.

select
	d.dis_nome,
	count(*) qtd
from
	aproveitamentos a,
	disciplinas d
where
	a.apr_ano = 2008 and
	a.apr_sem = 1 and
	a.dis_codigo = d.dis_codigo
group by
	d.dis_codigo,
	d.dis_nome
having
	count(*) >= 3

25. Consultar os professores que ministram aulas em mais de um curso.

select
	p.prf_nome,
	count(*) qtd
from
	professores p,
	disciplinas d,
	cursos c
where
	d.cur_codigo = c.cur_codigo and
	d.prf_codigo = p.prf_codigo
group by
	p.prf_codigo,
	p.prf_nome
having
	count(*) > 1