Resolução

Prévia do material em texto

Lista 6.2 - Banco de Dados I
Gustavo Henrique Salles, 2020005750
Discente de Sistemas de Informação – Universidade Federal de Itajubá (UNIFEI)
salles@unifei.edu.br
1 Modelo Lógico:
pessoa(cpf, nome*)
chave candidata: nome
professor(cpfProfessor, salario*)
cpfProfessor referência pessoa(cpf)
aluno(cpfPessoa, matricula*)
cpfPessoa referência pessoa(cpf)
chave candidata: matricula
disciplina(codigoDisc, nome*, numeroCreditos*, idProfessor*)
idProfessor referência professor(cpf_professor)
chave candidata: nome
turma(numero, idProfessor, codDisciplina, sala*, horario*)
idProfessor referência professor(cpf_professor)
codDisciplina referência disciplina(codigoDisc)
matric(numeroTurma, idAluno, status*)
numeroTurma referência turma(numero)
idAluno referência aluno(cpfPessoa)
prova(numero, codDisciplina, valor*, descrição*)
codDisciplina referência disciplina(codigoDisc)
faz(numeroProva, idAluno, nota*)
numeroProva referência prova(numero)
idAlu12no referência aluno(cpfPessoa)
Consultas:
a) Retorne os nomes dos alunos, os códigos das disciplinas que estão
matriculados, o número da turma e o status da matricula do aluno.
select distinct pnome, codigodisc, tnumero, status from pessoa, disciplina, turma, matric
where cpf in (select acpf from aluno)
b) Retorne os nomes dos alunos com os códigos das disciplinas, incluindo
aqueles alunos que não estão matriculados em disciplinas.
select distinct pnome as Aluno, coddisciplina as Disciplina
from pessoa
inner join aluno on cpf = acpf
left join matric on acpf = idaluno
left join turma on numeroturma = idturma
c) Retorne os códigos das disciplinas, o nome da disciplina, o nome do professor
responsável (regente), o número da prova(s) (caso exista) e o valor da prova(s)
para todas as provas cadastradas
select codigodisc as Codigo_Disciplina, dnome as Nome_Disciplina, pnome as Professor,
pdescricao as Prova, valor as valor_Prova
from disciplina
inner join pessoa on idprofessor = cpf
left join prova on codigodisc = coddisciplina
d) Retorne a quantidade de prova por disciplina (código)
select count(*) as Quantidade from prova group by coddisciplina
e) Retorne a quantidade de provas das disciplinas do professor João da Silva
Matos
select count(*) as quantidade_Prova, disciplina
from prova
inner join disciplina on coddisciplina = codigodisc
where idprofessor = (select cpf from pessoa where pnome ='João da Silva Matos' ) group
by(disciplina)
f) Retorne a quantidade de provas das disciplinas do professor João da Silva
Matos somente para aquelas disciplinas que possuem mais de 2 provas.
select count(*) as quantidade_Prova, disciplina
from prova
inner join disciplina on coddisciplina = codigodisc
where idprofessor = (select cpf from pessoa where pnome ='João da Silva Matos' ) group
by(disciplina) having count(*) >= 2
g) Retorne o código, nome do professor e nome e das disciplinas que possuem
turma cujo horário é noturno (hora maior que 19).
select codDisciplina as Codigo, dnome as Disciplina, pnome as Professor
from turma
inner join disciplina on codDisciplina = codigodisc
inner join pessoa on tidprofessor = cpf
where horario >= '19:00'
h) Retorne a quantidade de turmas das disciplinas cujo horário é noturno (hora
maior que 19).
select count(*) as quantidade_turmas, coddisciplina as disciplina
from turma
where horario >= '19:00'
group by (coddisciplina)
i) Retorne a quantidade de turmas das disciplinas cujo horário é noturno (hora
maior que 19) somente para as disciplinas que possuem mais de 4 turmas.
select count(*) as quantidade_turmas, coddisciplina as disciplina
from turma
where horario >= '19:00'
group by (coddisciplina)
having count(*) >= 4
j) Suponha que existam alunos que são professores (use sub-consultas):
Nao entendi
k) Retorne o nome das pessoas que são alunos e professores.
select pnome as Nome
from pessoa
inner join professor on cpf = pcpf
inner join aluno on acpf = pcpf
(obs: Nao tem inserido no bd)
l) Retorne o nome das pessoas que são alunos e/ou professores.
select pnome as Nome
from pessoa
full join professor on cpf = pcpf
full join aluno on acpf = pcpf
where pnome is not null
m) Retorne o nome das pessoas que são alunos e não são professores
select pnome as Nome
from pessoa
join aluno on acpf = cpf
having count(*) >= 4