Transcript Bases de Dados

Bases de Dados
Consultas (Queries)
Informática Aplicada
Março 2010
Consultas
 Possibilitam:
 Ter diferentes perspectivas sobre os dados.
 Seleccionar e editar a informação que satisfaz determinados critérios.
 Para quê Consultas ?
Seleccionar os campos que vão ser apresentados.
Seleccionar os registos que vão ser apresentados.
Ordenar registos.
Obter campos calculados através de expressões.
Sumarizar e agregar dados. Calcular totais.
Alterar o valor de campos nos registos que satisfazem determinadas
condições.
 Eliminar registos que satisfazem determinadas condições.






2
Informática Aplicada
Consultas
 Como Funciona uma Consulta ?
Fonte de Informação (tabela ou consulta) ==> Consulta ==> Tabela Virtual
 Uma Tabela virtual tal como as tabelas é um conjunto de registos.
 A diferença está em que a tabela é permanente enquanto a tabela virtual só
existe enquanto a Consulta está a ser executada
 Tipos de Consultas:
 Select Query seleccionar informação
Nosso foco aqui
 Action Query alterar a informação
 CrossTab Query permite dispor a informação em 2 dimensões
3
Informática Aplicada
Consultas de Selecção
Passos para criar uma consulta de selecção
1.
2.
3.
4.
4
Seleccionar as tabelas (físicas ou virtuais)
Seleccionar os campos (ou expressões) a visualizar
Definir os critérios de selecção dos registos
Definir a ordem dos registos
Informática Aplicada
Definição de uma consulta
 Desenho baseado num exemplo (Query by Example (QBE))
 Usando comandos SQL (Structured Query Language)
SELECT nome FROM Alunos
 Usando ajuda de sistema (Wizard)
5
Informática Aplicada
Criar uma nova consulta
Usamos a opção design view
Depois seguimos os passos que referimos
6
Informática Aplicada
Exemplo 1
Consideremos a BD seguinte
Suponhamos que queremos listar por ordem alfabética
os títulos dos livros foram emprestados à Ana.
O primeiro passo da construção da consulta é
seleccionar as tabelas necessárias. Neste caso precisamos de todas.
7
Informática Aplicada
Exemplo 1 cont.
Passo 1 . Seleccionar as tabelas necessárias
As tabelas são colocadas no espaço superior
Para as colocar basta seleccionar a tabela na janela “show table” e
carregar no botão Add
8
Informática Aplicada
Exemplo 1 cont.
Passo 2 . Seleccionar os campos (ou expressões) a visualizar
No nosso caso pretendemos ver o nome do livro
arrastamos o
campo para a
linha Field da
consulta
A checkbox
show deve
estar
selecionada
9
Informática Aplicada
Exemplo 1 cont.
Passo 3 . Definir os critérios de selecção dos registos
No nosso caso pretendemos ver os livros emprestados à Ana,
logo o campo nome do sócio deve ter o valor “Ana”
Arrastamos o
campo nome
do sócio para
a linha Field
da consulta
A condição é colocada na linha Criteria
10
Informática Aplicada
Desactivamos
a checkbox
show sob este
campo
Exemplo 1 cont.
Passo 4 . Definir a ordem dos registos
Pretendemos os nomes dos livros ordenados alfabeticamente
Alteramos a
opção Sort
sob o campo
nome do livro
para
ascending
11
Informática Aplicada
Consultas de Selecção - Sumário
 É o tipo de consulta mais frequentemente utilizado.
 Possibilita seleccionar a informação (quer os registos quer os campos) a apresentar, ordenar
a informação, obter campos calculados a partir de expressões que envolvem outros campos.
 Seleccionar campos
 Os campos da fonte de registos que se pretende visualizar na tabela virtual deverão ser
indicados na linha Field.
 Ordenar registos
 A linha Sort possibilita definir a ordem pela qual os registos são apresentados. Cada campo
da tabela virtual pode ser ordenado:
 por ordem crescente ("Ascending") ou
 por ordem decrescente ("Descending").
 A sequência de campos na Consulta indica a ordem das chaves de ordenação.
 Campos calculados
 São elaborados na linha Field. A sintaxe de um campo calculado é:
[Nome_do_Campo:]Expressão
 Na elaboração da expressão poderá utilizar funções da Access e funções definidas pelo
utilizador (Objecto:Modules).
12
Informática Aplicada
Exemplo 2 - Consulta sobre Alunos e Notas
 Queremos uma pauta com os nomes dos aluno, as designações das disciplinas,
e as notas, que nos mostre
nome
disciplina
nota
António
matemática
14,4
António
informática
15,6
Maria
bases de dados e conhecimento
16,7
 Para tal criamos no Access um novo objecto do tipo query.
 Janela de Base de Dados:
 Query: New: New Query (ou Wizard)
 Adicionamos as 3 tabelas (Add table): Preenchemos os campos (Fields)
 Carregamos no Datasheet view.
13
Informática Aplicada
Exemplo 2 (cont.)
14
Informática Aplicada
Consultas com condições / selecção de registos
 Queremos as notas de matemática de todos os alunos.
nome
nota
António
14,4
Maria
14,8
 Podemos uma nova consulta (query: new).
Ou modificar uma consulta existente (query: design).
 Na janela da consulta indicamos que queremos mostrar os campos alunos.nome
notas.nota e que o campo disciplinas.disciplina deve ser igual a matemática.
 Esta última informação é da da na área de critério da janela da consulta.
Aqui podemos exprimir condições E e OU.
 Exercício:
 Como obter uma listagem das notas positivas de informática?
15
Informática Aplicada
Consultas com condições / selecção de registos
16
Informática Aplicada
Consultas com condições / selecção de registos
 Seleccionar registos
Os registos são seleccionados definindo critérios a nível do campo, na
linha Criteria da Consulta.
 Se o critério é uma constante ou uma expressão são seleccionados os
registos para os quais o campo assume o valor indicado.
 Se o critério é uma relação (">12") são seleccionados os registos para os
quais a relação é verdadeira.
 Critérios que envolvem conjunções ou disjunções de condições:
 Critérios AND - definidos na mesma linha da grelha da query.
 Critérios OR - definidos em linhas diferentes da grelha da query.
17
Informática Aplicada
Critérios
 Exemplos de expressões usadas em critérios:
 Tipo
Expressão
 Text
Not "TP1"
todas as turmas excepto TP1
 Text
Like S* OR R* Nomes começados por S ou R
 Date
>#1/1/01# Datas superiores a 1 de Janeiro de 2001
 Date
Between #1/1/00# AND #3/31/01# datas entre 1/1/2000 e
31/3/2001
18
Informática Aplicada
Uma consulta com campos calculados
 Queremos que na pauta dos alunos apareça “Apr” ou “Repr” consoante a nota.
nome
disciplina
nota
classi
António
matemática
14,4
Apr
António
informática
15,6
Apr
Maria
matemática
6,7
Rep
 O Access permite a apresentação de valores calculados a partir, por
exemplo, de outros campos.
Field nome
Show
X
19
disciplina
X
nota
X
classi: iif([nota]<10;”Rep”;”Apr”)
X
Informática Aplicada
Consultas com parâmetros
 A utilização de constantes na definição de critérios em Consultas leva á
proliferação destas.
 O Access possibilita utilizar variáveis na definição de critérios:
 Uma variável é definida por um nome, diferente dos campos da fonte de
registos, entre parêntesis rectos.
 Ao executar a consulta o Access abre uma caixa de diálogo pedindo ao
utilizador para atribuir um valor á variável.
20
Informática Aplicada
Outros exemplo de uma consulta com parâmetros
 Queremos saber as notas de um aluno escolhido pelo utilizador.
Field nome
Show
disciplina
nota
X
X
Criteria [Diga qual o aluno]
21
Informática Aplicada
Como é que o ACCESS integra a informação de várias
tabelas?
O que acontece se as tabelas não estiverem relacionadas?
22
Informática Aplicada
Consultas com 2 tabelas não relacionadas
 Suponha as duas tabelas:
 Qual será o resultado da consulta?
23
Informática Aplicada
Consultas com 2 tabelas não relacionadas
 Todas as linhas da tabela 1 são combinadas com todas as linhas da
tabela 2 (gera o produto Cartesiano)
24
Informática Aplicada
Consulta com tabelas relacionadas
 No caso de tabelas relacionadas a consulta só combina os registos com o
mesmo valor dos campos comuns.
SELECT alunos.bi, alunos.nome, notas.dis, notas.data, notas.nota
FROM alunos INNER JOIN notas ON alunos.bi = notas.bi
25
Informática Aplicada
Consulta com tabelas relacionadas
 No caso de tabelas relacionadas a consulta só combina os registos com o
mesmo valor dos campos comuns.
 O campo bi aparece nas duas tabelas.
 Seleccionando o campo bi da tabela Notas (o lado N da relação), ao inserir um novo
registo na tabela virtual e digitando um bi, automáticamente o campo Nome é
preenchido.
 O comportamento da consulta é diferente se o campo bi tivesse sido seleccionado da
tabela Alunos: não seria possível inserir novos registos e só seria possível editar (alterar)
os campos provenientes da tabela Notas, o lado N da relação.
26
Informática Aplicada
Consultas com valores agregados
 Queremos saber as médias de todos os alunos.
nome
média
António
14,9
Maria
11,3
 Carregamos no botão [] da barra de ferramentas.
Agora podemos agrupar os dados por nome e sumarizar por média
em nota.
Field nome
média: [nota]
Total Group by
Avg
Show
27
X
X
Informática Aplicada
Consultas com valores agregados
 Queremos qualificar o nível do aluno em função da média.
nome
média
nível
António
14,9
Bom
Maria
11,3
Médio
 Usamos uma consulta de sumariação, agregamos as notas para
calcular a média.
 Calculamos uma Expressão a partir desse valor agregado.
Field nome
média: [nota]
Total Group by Avg
Show
X
Expression
X
28
nível: iif([média]>=14; “Médio”; “Bom”)
X
Informática Aplicada
Consultas com valores agregados
 Queremos agora ver apenas as médias dos alunos no primeiro
ano.
nome
média
António
13,2
Maria
14,1
 Usamos uma condição Where sobre o campo ano.
Field nome
média: [nota]
Total Group by Avg
Where
Criteria
Show
29
disciplinas.ano
1
X
X
Informática Aplicada
Sumarizar Dados
 A tabela notas tem as notas dos alunos a diferentes disciplinas ao longo
dos anos.
 Pergunta:
 Qual a média das notas a IA em 2009?
 Qual a média das notas, em 2009, ás diferentes disciplinas?
30
Informática Aplicada
Consultas com valores agregados - Sumário
 Permitem agrupar os registos pelo valor de um ou mais campos e obter
somas, médias, etc dos valores de outros campos.
 O botão
acrescenta uma linha de totais na grelha da consulta.
 Nesta linha é possível definir um conjunto de operações sobre os valores do
campo escolhido e que envolvem todos os registos seleccionados da tabela
virtual,
 Group By agrupar registos pelo valor do campo.
 Expression Calcular valores baseados nas funções de sumarização apresentadas de
seguida. Tem que incluir obrigatoriamente uma função de sumarização.
 Where: selecionar os registos
 As operações disponíveis são:
31
Informática Aplicada
Consultas com Totais
32
SELECT notas.Disciplina, Avg(notas.Nota) AS MédiaDeNota
FROM alunos INNER JOIN notas ON alunos.Bi = notas.Bi
WHERE (((Year([Data]))=2009))
GROUP
Informática
AplicadaBY notas.Disciplina;
Exercícios
33
Informática Aplicada
Exercício – Grupo 1
 Seleccionar os alunos da turma Tp1 ordenados por ordem alfabética do
nome.
 Calcule a nota final.
 Seleccionar os alunos da turma Tp1 cujo nome começa por "A".
 Seleccionar os alunos da turma Tp2 com positiva na nota final.
 Seleccionar os alunos com positiva a todas as avaliações.
 Seleccionar os alunos com positiva a pelo menos uma das avaliações.
 Seleccionar os alunos cuja nota ao trabalho é maior que a nota ao teste.
 Elabore uma consulta que mostre, para cada turma, a média da nota final.
 Elabore uma consulte que aumente em 5% a nota ao trabalho dos alunos da
turma TP1.
34
Informática Aplicada
Exercício – Grupo 2
Na base de dados de alunos/notas/disciplinas defina queries para obter:
O conjunto dos nome dos alunos.
Os códigos das disciplinas com nota superior a 10.
As notas de uma disciplina segundo um código dado.
As notas que cada aluno tirou a cada disciplina
As disciplinas do primeiro ano (códigos)
As disciplinas do primeiro e do segundo ano (nomes).
Os alunos cujo nome começa por A.
Os alunos com notas lançadas depois do dia D (escolha um dia adequado aos
dados que lançou).
10.Os alunos com notas lançadas com notas lançadas entre dois dias dados pelo
utilizador.
11.As notas positivas que cada aluno tirou a cada disciplina.
12.Os nomes dos alunos que tiraram notas acima de 14
1.
2.
3.
4.
5.
6.
7.
8.
9.
Informática Aplicada
35
Exercício – Grupo 2 (cont.)
13. Os nomes dos alunos que tiveram nota a informática.
14. Os nome das disciplinas com nota positiva para um aluno dado pelo utilizador.
15. A nota máxima de cada aluno.
16. A nota média de cada aluno em cada ano da licenciatura.
17. O tempo de funcionamento de cada disciplina calculado a partir da nota
lançada mais antiga.
Tópicos avançados:
13. Uma tabela de dupla entrada com a nota de cada aluno a cada disciplina.
14. Uma tabela de dupla entrada com a nota média de cada aluno por ano de
licenciatura.
15. Defina também queries para:
16. Reduzir todas as notas em 10%.
17. Subir todas as notas em 20%, mas ter atenção ao limite de 20 valores (usar
função iif).
18. Apagar todas as notas de um aluno dado pelo utilizador.
Informática Aplicada
36
Exercicio 3
 A base de dados de uma tesouraria contem a tabela "Movimentos", com a
seguinte estrutura:
Campo
Tipo
Descrição
Numero
AutoNumber
Nr. sequencial da
operação
Conta
Text
Nr. da conta da
contabilidade
Data
Date
Data da operação
Debito
Yes/No
Valor
Single
Valor da operação
 Construa a tabela e insira alguns registos
 Elabore uma consulta que possibilite seleccionar os movimentos de Janeiro
 Elabore uma consulta que construa uma nova tabela com os totais por conta dos
movimentos a debito e a crédito de Janeiro
37
Informática Aplicada
Exercicio 4
 Gestão dos clientes que compram passe para o metro. O campo Cliente é
Autonumber e o Cod-Postal e o Mes-utilizacao sao number, Integer. O campo
kilometro contém a distância linear em quilómetros medida ao longo do carril
desde a estação de origem até à estação considerada.
 Desenhe uma consulta que permita visualizar todos os passes comprados pelo cliente 73129
(com esse código) durante o ano de 2001.
 Desenhe uma consulta que permita contar quantos passes foram vendidos a clientes de cada
área de código postal, ordenados de forma decrescente, para serem utilizados no mês 3.
 Pretende-se calcular a distancia percorrida em cada viagem utilizando as capacidades das
consultas em Access. Desenhe as consultas necessárias para esse efeito.
 Sugestão: elabore três consultas. Uma obtém, para cada viagem, o kilometro correspondente à estação de entrada,
38
outra o kilometro correspondente à estação de saída, a terceira usa as duas primeiras para calcular a distancia
Informática Aplicada
percorrida em cada viagem
.
Tópicos Avançados
39
Informática Aplicada
Tabelas de duas entradas (CrossTab Query)
 Queremos um quadro com as notas de cada aluno a cada disciplina.
nome
bases de dados e...
informática
matemática
António
12,2
13,6
14,9
Maria
17,9
9,8
11,3
 Queremos um quadro com as notas de cada aluno a cada disciplina.
 Escolhemos a opção QueryCrosstab.
Field nome
disciplina
nota
Total Group by
Group by
Avg
Column Heading
Value
Crosstab Row Heading
Informática Aplicada
40
Tabelas Pivot
TRANSFORM Avg(notas.Nota) AS MédiaDeNota
SELECT Year([Data]) AS Ano
FROM notas
GROUP BY Year([Data])
PIVOT notas.Disciplina;
41
Informática Aplicada
Consultas de actualização
 Actualizar o valor de campos dos registos que satisfazem condições
UPDATE notas SET notas.Nota = [nota]*1.1
WHERE (((notas.Disciplina)="IA"));
42
Informática Aplicada
Consultas que permitem Eliminar
 Elimina registos que satisfazem condições
DELETE Year([Data]) AS ano
FROM notas
WHERE (((Year([Data]))=2008));
43
Informática Aplicada
Structured Query Language - SQL
 Linguagem de interrogação de bases de dados. Todas as Consultas em
Access correspondem a um comando SQL.
 Alguns Exemplos:
 SELECT alunos.bi, alunos.nome
FROM alunos WHERE (((alunos.bi)="1"));
 SELECT alunos.bi, alunos.nome, notas.dis, notas.data, notas.nota
FROM alunos INNER JOIN notas ON alunos.bi = notas.bi
WHERE (((alunos.bi)="1"));
 UPDATE alunos INNER JOIN notas ON alunos.bi = notas.bi
SET notas.nota = 0.1*[nota] WHERE (((alunos.bi)="1"));
 DELETE alunos.bi, alunos.*
FROM alunos WHERE (((alunos.bi)="1"));
44
Informática Aplicada