Bases de Dados
DML – Data Manipulation Language
Parte 2
Funções de Grupo
z
São funções aplicáveis a conjuntos de linhas de uma
tabela
{
À totalidade dos elementos ou
{
A um subconjunto especificado na cláusula WHERE
z
Obtém-se como resultado apenas 1 elemento,
independentemente da forma como foi especificado o
conjunto
{
Um conjunto é especificado utilizando a cláusula GROUP BY
(subconjunto dos elementos de uma tabela)
{
Quando nada é dito, ou seja, não usamos GROUP BY, estamos
a trabalhar sobre todos os registos da tabela
z
As funções de grupo utilizam-se na lista de selecção ou
na cláusula HAVING
z
É possível o seu encadeamento (ex: MAX(AVG(x)) )
Funções de Grupo
z
MAX([DISTINCT] exp) – Máximo da expressão
z
MIN([DISTINCT] exp) – Mínimo da expressão
z
AVG([DISTINCT] n) – Valor médio de n ignorando
valores nulos
z
SUM([DISTINCT] n) – Soma os valores de n ignorando
valores nulos
z
NVL(n,valor) – Se é null mostrar valor
z
COUNT([DISTINCT] exp) – N.º de vezes que exp toma
um valor não nulo
z
COUNT(*) – Conta todas as linhas seleccionadas
incluindo as repetidas e que contenham valores nulos
SUM e AVG aplicam-se apenas a valores numéricos.
Exemplo - Contar Linhas
z
Para contar quantos registos existem para
determinada query utilizamos a função
COUNT
SELECT COUNT(*) FROM aluno;
SELECT COUNT(DISTINCT local_aluno) FROM aluno; SELECT COUNT(nome_aluno) FROM aluno;
Exercício 1
z
Quantos alunos existem?
SELECT COUNT(*) “Numero de alunos” FROM aluno;
Exercício 2
z
Contabilizar o número de alunos mas por
localidade.
SELECT COUNT(local_aluno) “Numero errado de alunos” FROM aluno;
DISTINCT com funções de linha
SELECT DISTINCT COUNT(sexo_aluno) FROM aluno;
SELECT COUNT(DISTINCT(sexo_aluno)) FROM aluno;
Exercício 3
z
Qual a idade média dos alunos?
DEFINE idade = TRUNC((sysdate-data_nsc_aluno)/365.25); SELECT AVG(&idade) “Idade média”
Exercício 4
z
Qual a idade do aluno mais velho?
DEFINE idade = TRUNC((sysdate-data_nsc_aluno)/365.25); SELECT MAX(&idade) “Idade do aluno mais velho”
A Cláusula GROUP BY
z
Utilizada quando se pretende trabalhar
sobre subgrupos dos registos de uma
tabela:
SELECT <atributos>, <funções de grupo> FROM <tabelas>
[WHERE <condições>]
[ORDER BY <atributos [ASC | DESC]>] [GROUP BY <atributos>];
Quantos alunos existem do sexo Masculino e Feminino?
SELECT sexo_aluno “Sexo”, COUNT(*) “Total” FROM aluno
Exercício 5
z
Quantos alunos do curso 1 existem por
localidade?
SELECT UPPER(local_aluno), COUNT(*) FROM aluno
WHERE cod_curso = 1 AND local_aluno IS NOT NULL GROUP BY UPPER(local_aluno);
Exercício 6
z
Quais os nomes dos alunos que moram
na mesma localidade?
Não é possível agrupar por localidade, visto existir mais de um nome por localidade.
SELECT nome_aluno, local_aluno FROM aluno
Exercício 7
z
Para as idades dos alunos, mostrar
quantos têm a mesma idade.
DEFINE idade = TRUNC((sysdate-data_nsc_aluno)/365.25); SELECT &idade Idade, COUNT(*) Numero
FROM aluno
A Cláusula HAVING
z
Quando o número de elementos em cada
subgrupo é extenso torna-se mais viável
observar de forma independente apenas
aqueles que obedecem a determinada
característica;
z
A cláusula HAVING serve justamente para
restringir os grupos a mostrar como resultado:
SELECT <atributos>, <funções de grupo> FROM <tabelas>
[WHERE <condições>]
[ORDER BY <atributos [ASC | DESC]>] [GROUP BY <atributos>]
Exemplo – Cláusula HAVING
Quais os nomes daslocalidades que têm mais do que um aluno a estudar na EST e quantos alunos têm?
SELECT local_aluno, COUNT(*) FROM aluno
WHERE local_aluno IS NOT NULL GROUP BY local_aluno
Exercício 8
z
Mostrar as localidades que tenham
apenas uma aluna a estudar na EST
(ordenado por localidade).
SELECT local_aluno Localidade FROM aluno
WHERE sexo_aluno = ‘F’ GROUP BY local_aluno HAVING COUNT(*) = 1;
Os Subgrupos
z
É ainda possível formar grupos dentro de
grupos:
Quantos alunos,
discriminados por sexo, existem em cada curso?
SELECT cod_curso Curso, sexo_aluno Sexo, COUNT(*) FROM aluno
Dicionário de dados (1)
z
A tabela dictionary indica-nos quais as
tabelas que constituem o dicionário.
Descrição da tabela DICTIONARY Visualizar todas as tabelas de sistema disponíveis para o utilizador.
Dicionário de dados (2)
Visualizar todas as tabelas que o
Junção de Tabelas
z
É com a junção que cruzamos a informação de
diferentes tabelas
z
Em SQL fazem-se junções indicando na
cláusula WHERE de um SELECT a condição de
junção das tabelas envolvidas
z
Passos a seguir:
{
Pensar no produto cruzado das tabelas (no FROM)
{
Identificar a condição de junção (no WHERE)
Exemplo – Junção de Tabelas
Obter uma lista de todos os alunos com o respectivo curso em que cada um está inscrito!
Tendo 10 alunos e 4 cursos, vamos obter um produto cruzado com 40 registos de resultado!
Agora fazemos uma equi-junção (=)
Neste caso, queremos restringir os elementos por forma a ver apenas as linhas em que o código do curso é igual (comum a ambas as tabelas)
Nomes Simbólicos para Tabelas
z
Da mesma forma que podemos definir
pseudónimos para as colunas num
SELECT, também podemos definir um
pseudónimo (alias) para uma tabela
z
Tal definição é feita na cláusula FROM,
após o nome da tabela e é utilizada em
posteriores referências à mesma
Exemplo - Nomes Simbólicos para
Tabelas
Nomes simbólicos a e c para as tabelas aluno e curso, respectivamente
Exercício 9
z
Mostrar os possíveis pares (M e F) que se
conseguem formar entre alunos do
mesmo curso.
Não mostrando pares repetidos
Operadores SQL sobre Conjuntos
z
São úteis na construção de queries que se
refiram a tabelas distintas
z
Combinam os resultados de duas ou mais
instruções de selecção num só
z
A ordem de execução é de cima para baixo mas
podemos modificar prioridades de execução
usando parêntesis
z
Os operadores existentes são:
{
MINUS: diferença de conjuntos
{
UNION[ALL]: união de conjuntos (sem ALL mostra
apenas os registos distintos)
Regras - Operadores Sobre Conjuntos
z
As instruções SELECT têm de envolver um igual
número de atributos
z
As colunas correspondentes têm de representar
o mesmo tipo de dados
z
Os nomes das colunas do primeiro SELECT são
os que aparecem no cabeçalho do resultado
z
A cláusula ORDER BY só aparece no fim da
instrução após a aplicação de todos os
Exemplo - Operadores sobre Conjuntos
Quais os alunos que ainda não
concluíram
nenhuma cadeira? SELECT num_aluno, nome_aluno
FROM aluno MINUS
SELECT DISTINCT a.num_aluno, nome_aluno FROM aluno a, inscricao i
Exemplo – Operadores Sobre Conjuntos
Quais os nomes de todos os alunos e professores da EST?
Quais os alunos cujo nome coincide com o de algum professor?
Junção Externa
z
Ocorre quando se faz uma junção em que
nem todos os atributos de uma tabela têm
valor correspondente na outra (por
exemplo, numa delas são NULL)
Aqui mostramos todas as categorias, mesmo aquelas em que não há nenhum professor...
