[email protected], © 2009
SQL 3
UNIVERSIDADE DA BEIRA INTERIOR
Departamento de Informática
Bases de Dados I
LEI/2
Aluno
NumAluno Nome
12425
António Carlos
13181
Rita Campos
13829
Joana Almeida
Curso
CodCurso Naturalidade
123
Covilhã
76
Porto
22
Lisboa
Disciplina
CodCurso Créditos
Descrição
22
145
Engenharia Química
76
160
Matemática
123
154
Português
651
132
Medicina
Inscrição
CodDisciplina CodCurso Ano Créditos Descrição
1
22
1 3
Álgebra
2
76
2 4
Química
3
651
1 2
Gestão Médica
4
123
3 5
Análise
NumAluno
12425
12425
13829
13829
Ano
2003
2004
2003
2004
CodDisciplina
1
4
2
4
Classificação
NumAluno CodDisciplina
12425
11
13829
21
12425
77
13829
12
Ano
2003
2004
2003
2001
Nota
14
8
12
11
As tabelas acima transpostas dizem respeito à base de dados utilizada pelos
Serviços Académicos da Universidade da Beira Interior para registar as classificações
dos alunos. Cada aluno pertence exclusivamente a um curso e, para simplificar a
situação, supõe-se que não é permitida a transferência de alunos entre cursos.
Cada disciplina leccionada a um determinado curso tem obviamente um código
diferente, apesar de disciplinas com códigos diferentes corresponderem na prática à
mesma disciplina. (2820, 3313, 1466 são códigos respeitantes à disciplina de “Bases de
Dados I”, de diferentes cursos)
Cada disciplina possui um valor de “créditos” associado, sendo este valor
atribuído ao aluno após ter efectuado uma prova com classificação final igual ou
superior a 10 valores. Mais uma vez, para efeitos de simplificação, supõe-se que não
1 de 4
existe a possibilidade de executar exames para melhoria de nota, ou seja, para cada
disciplina, o aluno apenas pode ter uma nota positiva.
1- Crie o respectivo modelo Entidade / Relacionamento, indicando a cardinalidade
e obrigatoriedade de cada relação existente.
2- Com base no esquema de relações acima transcrito, efectue a respectiva
passagem para o modelo físico da base de dados pretendida, especificando todas
as restrições que achar convenientes.
3- Implemente blocos de SQL que permitam seleccionar a informação pretendida:
a. Listagem dos alunos do curso com código igual a “7”
b. Listagem dos alunos de “Engenharia Química”
c. Listagem dos alunos inscritos à disciplina com código igual a “71” no
ano de 2004.
d. Listagem das disciplinas pertencentes ao curso de “Física Óptica”,
ordenadas pelo ano lectivo a que pertencem
e. Listagem das disciplinas que não têm alunos inscritos em 2004.
f. Listagem das disciplinas (Código + Descrição) com mais de 40 alunos
inscritos.
g. Listagem dos alunos inscritos a “Bases de Dados I”
h. Listagem da descrição das disciplinas com mais que 100 alunos inscritos.
i. Nome e valor total de créditos do aluno com número igual a “12345”.
j. Listagem dos alunos que ainda não foram aprovados a “Análise
Matemática”.
k. Listagem dos alunos que, tendo efectuado pelo menos uma prova, não
foram aprovados a “Análise Matemática”.
l. Total de alunos aprovados à disciplina com código “123” no ano lectivo
de 2003.
m. Total de alunos inscritos à disciplina com código “123” no ano lectivo de
2004.
n. Total de alunos que, estando inscritos, não realizaram nenhuma prova à
disciplina com código “1454” durante o ano lectivo de 2003.
o. Total de alunos que não realizaram nenhuma prova às disciplinas em que
se encontravam inscritos no ano lectivo de 2003.
p. Listagem com a descrição e total de alunos inscritos no ano de 2004.
Esta deve estar ordenada decrescentemente pelo número total de alunos.
q. Supondo que a média de um aluno é dada simplesmente pela média
aritmética das suas classificações positivas, pretende-se listar o número e
média do aluno com número “12345”.
r. Listagem dos alunos que nunca obtiveram nenhuma classificação
negativa.
s. Listagem dos alunos que já terminaram o curso e nunca tiveram
nenhuma classificação negativa.
t. Listagem ordenada dos alunos do curso de “Gestão”, ordenados pela sua
classificação média actual.
2 de 4
u. Listagem dos alunos que já terminaram a licenciatura (total de créditos
maior ou igual que o valor previsto na tabela “Curso”). Esta listagem
deve estar ordenada pelo número de aluno.
v. Listagem das disciplinas que aumentaram o número de alunos em 2004.
(Disciplinas para as quais existiram mais novos alunos inscritos em
2004 que aprovados em 2003)
w. Listagem dos cursos com menos que 20 alunos actuais (não terminaram
o curso)
x. Classificação média dos alunos à disciplina de “Análise Matemática”
durante o ano lectivo de 2003.
y. Listagem das disciplinas e respectiva classificação média durante o ano
lectivo de 2003. Esta deve estar ordenada pela classificação média.
4- Imagine que as restrições explicitadas no texto introdutório (impossibilidade de
mudar de curso ou efectuar melhorias de classificação) não existiam. Proponha
as alterações ao modelo descrito por forma a permitir registar e consultar
qualquer item mencionado na questão anterior. Elabore o correspondente
diagrama Entidade / Relacionamento.
5- Considere o seguinte esquema de relações:
Departamento(CodDepartamento, Nome, Piso, CodChefe)
Empregado(Codigo,Nome, Salario, CodSuperior, Departmento)
Venda(Departmento, CodProducto, Data, Quantidade)
Produto(CodProduto, Descricao, Valor)
a. Implemente blocos SQL para responder a cada uma das seguintes
questões:
1. Quais os empregados que trabalham no departamento “Sapatos”?
2. Quais os empregados que ganham mais de 2000 EUR?
3. Quais os empregados do departamento de “Senhora” que ganham mais
que 200 EUR?
4. Quais os empregados que trabalham no segundo piso?
5. Quais os empregados que ganham mais que o seu patrão?
6. Quais os empregados que são chefes de um departamento mas não têm
ningúem directamente a seu cargo?
7. Quais os empregados que ganham mais que o chefe do departamento
de “Jardinagem”?
3 de 4
8. Quais os empregados que trabalham em departamentos que efectuaram
alguma venda inferior a 100 EUR?
9. Quais os empregados que ganham mais que o melhor pago do
departamento “Homem”?
10. Que produtos forma vendidos no terceiro piso?
11. Que produtos nunca foram vendidos?
12. Que produtos fora vendidos em todos os pisos?
13. Que produtos foram vendidos em pelo menos dois departamentos?
14. Que produtos nunca foram vendidos no segundo piso?
15. Que produtos foram vendidos por todos os departamentos?
16. Que produtos foram vendidos por todos os departamentos do segundo
piso?
17. Que produtos forma vendidos exclusivamente pelo departamento de
“Criança”?
18. Em que departamentos todos os empregados ganham menos que o
respectivo chefe?
19. Em que departamentos os empregados ganham menos que o chefe
pior remunerado da empresa?
20. Que produtos forma vendidos exclusivamente no segundo piso?
21. Qual a soma dos salários dos empregados que trabalham no segundo
piso?
22. Qual a soma dos salários dos empregados que trabalham em
departamentos com menos de 10 empregados?
4 de 4