1 Exercício - Banco de Dados - Universidade - Tabelas : Aluno, Disciplina, Professores, Turma e Historico

create database Exerciocio1
use Exerciocio1

/*
Criar base de Dados
Aluno(ra,nome,endereco,cidade)
Disciplina(cod_disc,nome_disc,carga_hor)
Professores(cod_pro,nome_pro,endereco,cidade)
Turma(cod_disc,cod_turma,cod_prof,ano,horario)
Historico(ra,cod_disc,cod_turma,cod_profe,ano,frequencia,nota)
*/
create table alunos(
ra int primary key not null,
nome varchar(150),
endereco varchar(200),
cidade varchar(50))

create table disciplinas(
cod_disc varchar(10) primary key not null,
nome_disc varchar(50),
carga_hor int)

create table professores(
cod_pro varchar(10) primary key not null,
nome_pro varchar(150),
endereco_prof varchar(200),
cidade_pro varchar(50))

create table turmas(
cod_disc varchar(10) foreign key references disciplinas(cod_disc),
cod_pro varchar(10) foreign key references professores(cod_pro),
ano int,
horario int,
cod_turma varchar(10) primary key)

create table historico(
ra int foreign key references alunos(ra)primary key,
cod_disc varchar(10) foreign key references disciplinas(cod_disc),
cod_turma varchar(10) foreign key references turmas(cod_turma),
cod_pro varchar(10) foreign key references professores(cod_pro),
ano int,
frequencia float,
nota float)

/* Apagar Tabela e Refezer*/
drop table turmas

/*Selecionar todas pra verificar */
select * from alunos
select * from disciplinas
select * from professores
select * from turmas
select * from historico

/*1. Encontre o RA dos alunos com nota em BD em 2010 menor que 5 (obs: BD = código da disciplinas).*/
SELECT ra
FROM historico
WHERE cod_disc ='BD' AND ano = 2010 AND nota <5

/*2. Forneça o RA, nome e nota dos alunos com nota menor que 5 em BD em 2010.*/
SELECT a.ra, a.nome, a.nome
from historico h, alunos a
where cod_disc='BD' and ano= 2010 and nota <5 and a.ra=h.ra

/*3. Forneça o nome dos professores de BD em 2010.*/
SELECT distinct(p.nome_pro)
from professores p, turmas t
where p.cod_pro=t.cod_pro and t.ano= 2010 and t.cod_disc='BD'

/*4. Encontre o nome, endereço, cidade dos alunos e código das disciplinas onde os alunos tiveram nota menor que 5 em 2010.*/
SELECT a.nome, a.endereco, a.cidade, h.cod_disc
from alunos a, historico h
where a.ra = h.ra and h.nota<5 and h.ano= 2010

/*5. Obtenha o nome e RA dos alunos do professor em 2010.*/
SELECT distinct a.nome, a.ra
from alunos a, professores p, historico h
where h.ra=a.ra and h.cod_pro = p.cod_pro
and p.nome_pro like '%NOME%'
and h.ano = 2010

/*6. Localize o nome e RA dos alunos do professor JOSÉ em 2010 ou 2009 que tiveram aulas com o professor MARCOS em algum ano.*/
SELECT distinct a.nome, a.ra
from alunos a, professores p, historico h
where h.ra=a.ra and h.cod_pro = p.cod_pro
and p.nome_pro like '%JOSÉ%'
and (h.ano =2010 or h.ano = 2009)
AND a.ra in
(select h.ra
from historico h, professores p
where h.cod_pro = p.cod_pro
and p.nome_pro like '%MARCOS%')

/*7. Forneça o histórico escolar do aluno de nome Alex, ou seja, seu RA, nome,
a lista de disciplinas que ele já cursou contendo o código e nome da disciplina,
freqüência e nota e ano que o aluno a cursou.*/
select a.ra, a.nome,
h.cod_disc, d.nome_disc,
h.ano, h.frequencia, h.nota
from historico h, alunos a, disciplinas d
where a.ra = h.ra
and h.cod_disc = d.cod_disc
and a.nome like '%ALEX%'

/*8. Encontre o nome e endereço dos alunos e professores de Campinas.*/
select a.nome ,a.endereco, 'aluno'  as tipo
from alunos a
where a.cidade='CAMPINAS'
union
select p.nome_pro ,p.endereco_prof, 'prof'
from professores p
where p.cidade_pro='CAMPINAS'

/*9. Forneça o nome dos alunos que cursaram disciplinas com carga horária menor que 60 horas, bem como os respectivos professores que as lecionaram.*/
select a.nome, p.nome_pro
from alunos a, disciplinas d, professores p, historico h
where
a.ra = h.ra and
d.cod_disc = h.cod_disc and
p.cod_pro = h.cod_pro and
d.carga_hor<60

/*10. Localize o nome dos professores que lecionaram matérias nas quais o aluno “Pedro Paulo Cunha” foi reprovado. (nota < 5)*/
select p.nome_pro
from alunos a, professores p, historico h
where
a.ra = h.ra and
p.cod_pro = h.cod_pro and
upper(a.nome) = 'PEDRO PAULO CUNHA' AND
h.nota<5

/*11. Encontre o RA dos alunos que já cursaram todas as disciplinas lecionadas pelo prof.João.*/
Select distinct a.nome, a.ra
from alunos a
where not exists (select cod_disc
                  from professores p, turmas t
                  where p.cod_pro = t.cod_pro and
                  P.nome_pro like '%JOÃO%'
                  and cod_disc not in (select cod_disc
                                       from historico h
                                       where a.ra = h.ra))

/*12. Encontre o Ra, nome e média das notas dos alunos que cursaram todas as matérias lecionadas por professores de Campinas.*/
Select distinct A.ra, A.nome, AVG(nota)
from alunos A , historico h where A.ra= h.ra  and
 not exists
(select cod_disc
from professores p, turmas t
where p.cidade_pro = 'CAMPINAS'and cod_disc not in
     (select cod_disc
      from historico h
      where A.ra = h.ra))
group by A.ra, A.nome

/*13. Localize o RA e nome dos alunos com nota menor que qualquer uma das notas do aluno de RA 20090121.*/
select a.ra,a.nome
from alunos a, historico h
where a.ra = h.ra and
h.nota <any
(select nota from historico where ra = 20090121)

/*14. Forneça o Ra, nome e média das notas por alunos.*/
select a.ra, a.nome, avg(h.nota)
from alunos a, historico h
where a.ra=h.ra
group by a.ra, a.nome

/*15. Encontre o nome dos alunos que não cursaram nenhuma disciplina oferecida em 2010.*/
select nome
from alunos
where ra not in (select distinct ra from historico where ano= 2010 )

/*16.  Forneça o nome dos professores que somente lecionaram matérias com carga horária foi inferior a 60 horas.*/
Select p.nome_pro
from professores p,turmas t
where p.cod_pro = t.cod_pro and
p.cod_pro not in ( select t.cod_pro
                    from turmas t, disciplinas d
                    where t.cod_disc = d.cod_disc and carga_hor >=60)

/*17. Encontre o nome dos alunos que não foram reprovados em nenhuma matéria.*/
select a.nome
from  alunos a
where a.ra not in (select a.ra
                   from historico h, disciplinas d
                   where h.cod_disc = d.cod_disc and h.nota <6.0)

/*18. Forneça o RA e nota dos alunos com nota em BD em 2010 menor que a média das notas na disciplina.*/
select a.nome, h.nota
from alunos a , historico h
where a.ra = h.ra and  h.cod_disc='BD' and ano= 2010
            and nota <all (select avg(nota)
                           from historico
                           where cod_disc='BD'and ano = 2010)

/*19. Encontre o código e nome das disciplinas e media das notas por disciplina, mostre apenas as disciplinas com média de notas maior que 5.0.*/
select d.cod_disc ,d.nome_disc, avg(nota)
from  disciplinas d, historico h
where  h.cod_disc = d.cod_disc
 group by d.cod_disc, d.nome_disc having avg(nota) >5

/*20. Forneça o número de alunos que fizeram BD em 2010.*/
SELECT count(*) from historico where cod_disc ='bd' and ano = 2010

/*21. Encontre a disciplina com maior média de notas.*/
Select nome_disc, avg(nota)
from disciplinas D, historico H
Where D.cod_disc = H.cod_disc
group by nome_disc
having avg(nota) >= all( Select avg(nota)
                         from historico
                         group by cod_disc)

/*22. Forneça o código das disciplinas com média menor que a média das notas em BD.*/
Select cod_disc, AVG(nota)
from historico
group by cod_disc
having avg(nota) < all (select avg(nota)
                        from historico
                        where cod_disc = 'BD')

/*23. Forneça o nome dos professores que já lecionaram alguma disciplina para o aluno de RA 5400001.*/
Select P.nome_pro
from professores P, historico h
where P.cod_pro = h.cod_pro and h.RA = 5400001

/*24. Encontre o nome das disciplinas com média de freqüência abaixo de 80%.*/
Select nome_disc
From disciplinas D,historico H
Where H.cod_disc = D.cod_disc
group by nome_disc, carga_hor
having avg(frequencia) < 0.8 * carga_hor

/*25. Forneça o nome dos alunos que tiveram no mínimo 2 reprovações em 2010.*/
Select A.Nome
From alunos A, historico H
Where A.ra = H.ra and ano = 2010 and nota < 6.0
group by A.nome
having count(*) >= 2

/*26. Quantas vezes o aluno “José da Silva” cursou a disciplina de Banco de Dados.*/
select count(*)
from alunos a, historico h, disciplinas d
where a.ra=h.ra
and d.cod_disc=h.cod_disc
and a.nome ='Jose da Silva'
and d.nome_disc='Banco de Dados'

/*27. Quantos alunos já cursaram a disciplina de Banco de Dados em 2009 e 2010.*/
select count(*)
from historico h inner join disciplinas d on D.cod_disc= h.cod_disc
where d.nome_disc='Banco de Dados' and (ano = 2009 or ano = 2010)

/*28 Forneça o nome dos alunos que obtiveram mais que 2 reprovações em algum ano.*/
select a.nome, h.ano, count(*)
from alunos a, historico h
where h.ra = h.ra and h.nota<6.0
group by a.nome, h.ano
having count(*) >2

/*29. Encontre o nome dos professores e das disciplinas onde o número de reprovações foi superior a 20 alunos em 2010.*/
select p.nome_pro, d.nome_disc
from professores p, disciplinas d, historico h
where h.cod_pro = p.cod_pro and d.cod_disc= h.cod_disc
and h.nota<6 and h.ano = 2010
group by p.nome_pro, d.nome_disc
having count(*) >20

/*30. Forneça o nome dos professores e código das disciplinas lecionadas por ele em 2010 e a media das notas por disciplina.*/
select p.nome_pro, h.cod_disc, avg(h.nota)
from professores p, historico h
where h.cod_pro=p.cod_pro and h.ano = 2010
group by p.nome_pro, h.cod_disc

/*Operações Diversas*/
/*Agora, iremos fazer algumas atualizações nas tabelas que criamos acima.*/

/*1. Insira seus dados no BD: tabela de alunos (seu nome, Ra, endereço, cidade),
tabela de histórico cursando BD em 2009 na turma A com o professor JAC e obtendo nota 8.2 e freqüência 80,
e a disciplina de EDA em 2010 na turma A com o professor RCSV e obtendo nota 7.5 e freqüência 75.*/
INSERT into alunos
values (0912252, 'Aline Bossi', 'Rua manoel', 'Piracicaba')

INSERT into historico
values (0912252,'BD','A','JAC','2009','80','8.2')

INSERT into historico
values (0912252,'EDA','A','RCSV',2010,75,7.5)

select * from disciplinas

/*2. Insira todos os alunos da disciplina de BD (Banco de Dados) em 2009 e tiveram nota > 5,
cursando a disciplina BDII (Banco de Dados II) em 2010 com o mesmo código de turma e o mesmo professor,
 mas com freqüência e nota desconhecidas.*/
insert into historico
select h.ra, 'BDII',
h.cod_turma, h.cod_pro,
2010, null, null
from historico h
where h.cod_disc = 'BD'
and h.ano = 2009 and h.nota >5

/*3. Altere as notas dos alunos de Banco de Dados (BD) em 2010 com o professor JAC através da seguinte regra:
     – notas entre [4.0 e 5.0[ ficaram com 4.0 (inclusive a esquerda e exclusive a direita).
     – notas entre [5.0 e 9.5[ terão acréscimo de 0.5 na nota.
    – notas acima de 9.5, inclusive, ficarão com 10.0*/
update historico
set nota  = 4
where nota>=4 and nota<5
and cod_disc = 'BD'
and ano = 2010
and cod_pro='JAC';

update  historico
set  nota  = 10
where nota>=9.5 and cod_disc = 'BD'
and ano = 2010 and cod_pro='JAC';

update  historico
set nota = nota +0.5
where nota>=5.0 and nota<9.5 and cod_disc = 'BD'
and ano = 2010 and cod_pro='JAC';

/*4. Remova todas as informações referentes ao aluno “Jorge dos Santos” do sistema.*/
delete from  historico
where ra IN
(select ra from alunos where nome='Jorge dos Santos');

delete from alunos
where nome = 'Jorge dos Santos';

/*5. Faça uma atualização na freqüência dos alunos de BD em 2010 com o professor JC de acordo com a seguinte regra:
    – freqüência abaixo de 45, inclusive, acréscimo de 3.0%
    – freqüência acima de 45, acréscimo de 5.0%*/
update h historico
set  h.frequencia = h.frequencia + 0.05* h.frequencia
where h.frequencia >45
and h.cod_disc='Bd'
and h.ano = 2010 and h.cod_pro = 'JAC';
update historico h
set  h.frequencia = h.frequencia * 1.03
where h.frequencia <=45
and h.cod_disc='Bd'
and h.ano = 2010
and h.cod_pro = ‘JAC’;

/*6. Insira os seguintes dados: Aluna de RA 20090301 obteve nota 8.0 e freqüência 60 em BD (EM 2009 COM O PROFESSOR JAC NA TURMA A).*/
INSERT into historico
values(20090301, 'BD','A', 'JC', 2009, 60, 8 )

/*7. Execute todas as operações necessárias para substituir o nome do Prof. João Pedro por José Pedro,
visto ter ocorrido um erro no momento de cadastrar o nome do professor.*/
update professores
set nome_pro='Jose Pedro'
where nome_pro='Joao Pedro'

/*8. Substitua todas as matérias lecionadas pelo Prof. Raimundo Claudio Vasconcelos em 2010 pelo Prof. JAC.*/
update turmas
set cod_pro = 'JAC'
where ano = 2010 and cod_pro in ( select cod_pro
                                   from professores
                                   where nome_pro = 'João')