Aula 05 - Mais SQL

Prévia do material em texto

Mais SQL: Consultas complexas, 
triggers, views e modificação de 
esquema
Disciplina: Banco de Dados
Professor: Wandré Nunes de 
Pinho Veloso
2
Comparações envolvendo NULL e 
lógica de três valores
• SQL tem diversas regras para lidar com valores 
NULL, que pode ser
– Valor desconhecido (existe, mas não é conhecido)
• Data de nascimento de alguém que eu não sei
– Valor não disponível (existe, mas é propositalmente 
retido)
• Telefone residencial que não deseja-se listar
– Valor não aplicável (o atributo é indefinido para essa 
tupla)
• Atributo Cônjuge de uma pessoa não casada
• A SQL não distingue entre os significados de 
NULL
3
Comparações envolvendo NULL e 
lógica de três valores
• Quando um NULL está envolvido em uma 
operação de comparação, o resultado é 
considerado UNKNOWN, ou 
desconhecido (e pode ser TRUE ou 
FALSE)
– O SQL usa uma lógica de três valores com os 
valores TRUE, FALSE, e UNKNOWN em vez 
da lógica booleana padrão
Comparações envolvendo NULL e 
lógica de três valores
AND TRUE FALSE UNKNOWN
TRUE TRUE FALSE UNKNOWN
FALSE FALSE FALSE FALSE
UNKNOWN UNKNOWN FALSE UNKNOWN
4
Comparações envolvendo NULL e 
lógica de três valores
OR TRUE FALSE UNKNOWN
TRUE TRUE TRUE TRUE
FALSE TRUE FALSE UNKNOWN
UNKNOWN TRUE UNKNOWN UNKNOWN
5
Comparações envolvendo NULL e 
lógica de três valores
NOT
TRUE FALSE
FALSE TRUE
UNKNOWN UNKNOWN
6
• Observe que na lógica booleana padrão 
somente valores TRUE e FALSE são 
permitidos
7
Comparações envolvendo NULL e 
lógica de três valores
• Nas consultas seleção-projeção-junção, a 
regra geral é que somente as 
combinações de tuplas que avaliam a 
expressão lógica na cláusula WHERE da 
consulta como TRUE são selecionadas
– As tuplas que são avaliadas como FALSE ou 
UNKNOWN não são selecionadas
– Existem exceções a essa regra para certas 
operações, como junções externas (outer 
joins)
8
Comparações envolvendo NULL e 
lógica de três valores
• SQL permite consultas que verificam se o 
valor de um atributo é NULL
– Utiliza os operadores de comparação IS ou 
IS NOT
– Recuperar os nomes de todos os funcionários 
que não possuem supervisores
SELECT Pnome, Unome
FROM FUNCIONARIO
WHERE Cpf_supervisor IS NULL;
9
Consultas aninhadas, tuplas e 
comparações de conjunto
• São blocos select-from-where completos 
dentro da cláusula WHERE de outra consulta
– Essa outra consulta é chamada de consulta 
externa
• Utilizamos o operador de comparação IN, 
que compara um valor v com um conjunto (ou 
multiconjunto) de valores V e avalia como 
TRUE se for um dos elementos em V
10
Consultas aninhadas, tuplas e 
comparações de conjunto
• Exemplo:
– A primeira consulta aninhada seleciona os 
números dos projetos que possuem um 
funcionário com sobrenome ‘Silva’ envolvido 
como gerente
– A segunda consulta aninhada seleciona os 
números dos projetos que possuem um 
funcionário com o sobrenome ‘Silva’ 
envolvido como trabalhador
– Na consulta externa, usamos o OR para recuperar 
a tupla PROJETO se o valor de Projnumero 
dessa tupla estiver no resultado de qualquer uma 
das consultas aninhadas
11
Consultas aninhadas, tuplas e 
comparações de conjunto
SELECT DISTINCT Projnumero
FROM PROJETO
WHERE Projnumero IN
(SELECT Projnumero
FROM PROJETO, DEPARTAMENTO, FUNCIONARIO
WHERE Dnum=Dnumero AND Cpf_gerente=Cpf 
AND Unome=‘Silva’)
OR Projnumero IN
(SELECT Pnr
FROM TRABALHA_EM, FUNCIONARIO
WHERE Fcpf=Cpf AND Unome=‘Silva’);
12
Consultas aninhadas, tuplas e 
comparações de conjunto
• Se uma consulta aninhada retornar um 
único atributo e uma única tupla, o 
resultado da consulta será um único valor
– Nesses casos é permitido usar o operador = 
em vez de IN para comparação
• Em geral, a consulta aninhada retornará 
uma tabela (relação), que é um conjunto 
ou multiconjunto de tuplas
13
Consultas aninhadas, tuplas e 
comparações de conjunto
• A SQL permite o uso de tuplas de valores em 
comparações, colocando-os entre parênteses
SELECT DISTINCT Fcpf
FROM TRABALHA_EM
WHERE (Pnr, Horas) IN (
SELECT Pnr, Horas
FROM TRABALHA_EM
WHERE Fcpf=‘12345678966’ );
• Essa consulta selecionará os Fcpfs de todos os 
funcionários que trabalham na mesma 
combinação (projeto, horas) em algum projeto 
que o funcionário do Cpf = ‘12345678966’ 
trabalha
14
Consultas aninhadas, tuplas e 
comparações de conjunto
• Além do operador IN, diversos outros 
operadores de comparação podem ser 
usados para comparar um valor com um 
conjunto ou multiconjunto
– = ANY (ou = SOME)
• Retorna TRUE se o valor for igual a algum valor no 
conjunto, e, portanto, é equivalente a IN
– Outros operadores que podem ser 
combinados com ANY (ou SOME) incluem >, 
>=, <, <= e <>
15
Consultas aninhadas, tuplas e 
comparações de conjunto
• A palavra-chave ALL também pode ser 
combinada com cada um desses operadores
– Por exemplo, a condição de comparação (v > ALL V) 
retorna TRUE se o valor v é maior que todos os 
valores no conjunto (ou multiconjunto) V
SELECT Unome, Pnome
FROM FUNCIONARIO
WHERE Salario > ALL 
( SELECT Salario
FROM FUNCIONARIO
WHERE Dnr=5 );
– Também ser especificada usando MAX
16
Consultas aninhadas, tuplas e 
comparações de conjunto
• Podemos lidar com a possível ambiguidade 
entre nomes de atributo se existirem atributos 
com o mesmo nome
SELECT F.Pnome, F.Unome
FROM FUNCIONARIO AS F
WHERE F.Cpf IN (
SELECT D.Fcpf
FROM DEPENDENTE AS D
WHERE F.Pnome=D.Nome_dependente 
AND F.Sexo=D.Sexo );
17
Consultas aninhadas, tuplas e 
comparações de conjunto
• É aconselhável criar variáveis de tupla 
(apelidos) para todas as tabelas 
referenciadas em uma consulta SQL, para 
evitar erros e ambiguidades em potencial
18
Consultas aninhadas correlacionadas
• Sempre que uma condição na cláusula 
WHERE de uma consulta aninhada referencia 
algum atributo de uma relação declarada na 
consulta externa, as duas consultas são 
consideradas correlacionadas
• Em geral, uma consulta escrita com blocos 
aninhados select-from-where e usando os 
operadores de comparação = ou IN sempre 
pode ser expressa como uma única consulta 
em bloco
19
Consultas aninhadas correlacionadas
SELECT F.Pnome, F.Unome
FROM FUNCIONARIO AS F, DEPENDENTE 
AS D
WHERE F.Cpf=D.Cpf AND 
F.Pnome=D.Nome_dependente AND 
F.Sexo=D.Sexo;
20
As funções EXISTS e UNIQUE
• EXISTS é usado para verificar se o 
resultado de uma consulta aninhada 
correlacionada é vazio (não contém 
tuplas) ou não
– O resultado de EXISTS é booleano (TRUE se 
tiver pelo menos uma tupla como resultado e 
FALSE senão retornar nada)
– Também existe o NOT EXISTS
21
As funções EXISTS e UNIQUE
SELECT F.Pnome, F.Unome
FROM FUNCIONARIO AS F
WHERE EXISTS (
SELECT *
FROM DEPENDENTE AS D
WHERE F.Cpf=D.Fcpf AND 
F.Sexo=Dsexo AND 
F.Pnome=D.Nome_dependente );
22
As funções EXISTS e UNIQUE
• EXISTS e NOT EXISTS costuma ser 
usados em conjunto com uma consulta 
aninhada correlacionada
SELECT Pnome, Unome
FROM FUNCIONARIO
WHERE NOT EXISTS (
SELECT *
FROM DEPENDENTE
WHERE Cpf=Fcpf );
– Recupera os nomes dos funcionários que não 
possuem dependentes
23
As funções EXISTS e UNIQUE
• Listar os nomes dos gerentes que possuem pelo 
menos um dependente (se pelo menos uma da 
primeira e uma da segunda existir, selecionar)
SELECT Pnome, Unome
FROM FUNCIONARIO
WHERE EXISTS (
SELECT *
FROM DEPENDENTE
WHERE Cpf=Fcpf )
AND EXISTS (
SELECT *
FROM DEPARTAMENTO
WHERE Cpf=Cpf_gerente);
24
As funções EXISTS e UNIQUE
• Exercício
– Reescrever a consulta anterior sem nenhuma 
consulta aninhada
25
As funções EXISTS e UNIQUE
SELECT Pnome, Unome
FROM FUNCIONARIO
WHERE NOT EXISTS( 
(
SELECT Projnumero
FROM PROJETO
WHERE Dnum=5)
EXCEPT (
SELECT Pnr
FROM TRABALHA_EM
WHERE Cpf=Fcpf) 
);
• A primeira subconsulta seleciona 
todos os projetos controlados 
pelo departamento 5
• A segunda seleciona todos os 
projetos em que o funcionário 
particular trabalha
• Se a diferença de conjunto do 
resultado da primeira menos o 
resultado da segunda for vazio, 
significa que o funcionário 
trabalha em todos os projetos e, 
portanto, é selecionado
26
As funções EXISTS e UNIQUE
SELECT Unome, Pnome
FROM FUNCIONARIO F
WHERE NOT EXISTS (
SELECT *
FROM TRABALHA_EM 
T1
WHERE (T1.Pnr IN ( 
SELECT Projnumero
FROM PROJETO
WHERE Dnum=5)
AND NOT EXISTS
( SELECT *
FROM TRABALHA_EM 
T2
WHERE T2.Fcpf=Cpf
AND 
T2.Pnr=F.Pnr)));
• A consulta aninhada externa 
seleciona quaisquer tuplas de T1 
cujo Pnr é de um projeto 
controlado pelo departamento 5 
senão houver uma tupla em T2 
com o mesmo Pnr e o mesmo 
Cpf daquele da tupla 
FUNCIONARIO em 
consideração na consulta 
externa
• Senão existir tal tupla, 
selecionamos a tupla 
FUNCIONARIO
27
As funções EXISTS e UNIQUE
• Existe outra função em SQL, UNIQUE(C), 
que retorna TRUE se não houver tuplas 
duplicadas no resultado da consulta C
– Caso contrário ela retorna FALSE
• Isso pode ser usado para testar se o 
resultado de uma consulta aninhada é um 
conjunto ou um multiconjunto
28
Conjuntos explícitos e renomeação de 
atributos em SQL
• É possível usar um conjunto explícito de 
valores na cláusula WHERE, em vez de 
uma consulta aninhada
SELECT DISTINCT Fcpf
FROM TRABALHA_EM
WHERE Pnr IN (1, 2, 3);
29
Conjuntos explícitos e renomeação de 
atributos em SQL
• É possível renomear qualquer atributo que 
pareça no resultado de uma consulta 
acrescentando o qualificador AS seguido 
pelo novo nome desejado
SELECT F.Unome AS Nome_funcionario, 
S.Unome AS Nome_supervisor
FROM FUNCIONARIO AS F, FUNCIONARIO 
AS S
WHERE F.Cpf_supervisor=S.Cpf;
30
Tabelas de junção em SQL e junções 
externas (outer joins)
• O conceito de uma tabela de junção (ou 
relação de junção) foi incorporado na SQL 
para permitir aos usuários especificar uma 
tabela resultante de uma operação de junção 
na cláusula FROM de uma consulta
– Primeiramente há a junção das relações, depois 
selecionam-se as tuplas e atributos desejados
SELECT Pnome, Unome, Endereco
FROM (FUNCIONARIO JOIN DEPARTAMENTO ON 
Dnr=Dnumero)
WHERE Dnome=‘Pesquisa’;
31
Tabelas de junção em SQL e junções 
externas (outer joins)
• O conceito de uma tabela de junção 
também permite que o usuário especifique 
diferentes tipos de junção, como
– NATURAL JOIN (junção natural)
– OUTER JOIN (junção externa)
32
Tabelas de junção em SQL e junções 
externas (outer joins)
• NATURAL JOIN (junção natural)
– Nenhuma condição de junção é especificada
– Cria-se uma condição EQUIJOIN implícita 
para cada par de atributos com o mesmo 
nome
– Cada par de atributos desse tipo é incluído 
apenas uma vez na relação resultante
– Se os nomes dos atributos de junção não 
forem os mesmos, é possível renomear e 
depois aplicar o NATURAL JOIN
33
Tabelas de junção em SQL e junções 
externas (outer joins)
SELECT Pnome, Unome, Endereco
FROM (FUNCIONARIO NATURAL JOIN 
(DEPARTAMENTO AS DEP (Dnome, Dnr, 
Cpf_gerente, 
Data_inicio_gerente)))
WHERE Dnome=‘Pesquisa’;
– O significado da condição de junção para esse 
NATURAL JOIN é FUNCIONARIO.Dnr = 
DEPT.Dnr, porque esse é o único par de 
atributos com o mesmo nome após a 
renomeação
34
Tabelas de junção em SQL e junções 
externas (outer joins)
• O tipo padrão de junção em uma tabela de 
junção é chamado de inner join, onde 
cada uma tupla é incluída no resultado 
somente se uma tupla combinar na outra 
relação
35
Tabelas de junção em SQL e junções 
externas (outer joins)
• Se um usuário exigir que todos os 
funcionários sejam incluídos, uma OUTER 
JOIN precisa ser usada explicitamente
SELECT F.Unome AS Nome_funcionario, 
S.Unome AS Nome_supervisor
FROM (FUNCIONARIO AS F LEFT OUTER 
JOIN FUNCIONARIO AS S ON 
F.Cpf_supervisor=F.Cpf);
36
Tabelas de junção em SQL e junções 
externas (outer joins)
• Opções disponíveis para especificar tabelas 
de junção
– INNER JOIN
• Apenas pares de tuplas que combinam com a 
condição de junção são recuperadas
• O mesmo que JOIN
– LEFT OUTER JOIN ou LEFT JOIN
• Toda tupla na tabela da esquerda precisa aparecer no 
resultado
• Se ela não tiver uma tupla combinando, ela é 
preenchida com valores NULL para os atributos da 
tabela da direita
37
Tabelas de junção em SQL e junções 
externas (outer joins)
• Opções disponíveis para especificar tabelas 
de junção
– RIGHT OUTER JOIN ou RIGHT JOIN
• Toda tupla na tabela da direita precisa aparecer no 
resultado
• Se ela não tiver uma tupla combinando, ela é 
preenchida com valores NULL para os atributos da 
tabela da esquerda
– FULL OUTER JOIN ou FULL JOIN
• Trará todas as tuplas, pelo menos 1 vez, que estejam 
nas duas tabelas
• Podemos dizer que é a junção de LEFT OUTER JOIN 
e RIGHT OUTER JOIN
38
Tabelas de junção em SQL e junções 
externas (outer joins)
• Opções disponíveis para especificar 
tabelas de junção
– NATURAL LEFT OUTER JOIN
• Usado se os atributos de junção tiverem o mesmo 
nome
– NATURAL RIGHT OUTER JOIN
• Usado se os atributos de junção tiverem o mesmo 
nome
39
Tabelas de junção em SQL e junções 
externas (outer joins)
• Opções disponíveis para especificar 
tabelas de junção
– CROSS JOIN
• Usado para especificar a operação de produto 
cartesiano, embora isso só deva ser feito com o 
máximo de cuidado, pois gera todas as 
combinações de tuplas possíveis
40
Tabelas de junção em SQL e junções 
externas (outer joins)
• Também é possível aninhar 
especificações de junção
– Uma das tabelas em uma junção pode ela 
mesma ser uma tabela de junção
• Isso permite a especificação da junção de 
três ou mais tabelas como uma única 
tabela de junção
– Chamado de junção múltipla
41
Tabelas de junção em SQL e junções 
externas (outer joins)
• Qual das seguintes consultas é mais 
rápida?
– Por quê?
• Elas são equivalentes (produzem o 
mesmo resultado)?
42
Tabelas de junção em SQL e junções 
externas (outer joins)
SELECT Projnumero, Dnum, Unome, Endereco, 
Datanasc
FROM PROJETO, DEPARTAMENTO, FUNCIONARIO
WHERE Dnum=Dnumero AND Cpf_gerente=Cpf AND 
Projlocal=‘Mauá’;
SELECT Projnumero, Dnumero, Unome, 
Endereco, Datanasc
FROM ((PROJETO JOIN DEPARTAMENTO ON 
Dnum=Dnumero) JOIN FUNCIONARIO ON 
Cpf_gerente=Cpf)
WHERE Projlocal=‘Mauá’;
43
Tabelas de junção em SQL e junções 
externas (outer joins)
• Em algumas implementações do SQL 
podem haver algumas diferenças
• No Oracle, por exemplo, existe uma 
sintaxe proprietária, descrita a seguir
– A partir da versão 9.2 foi implementado o 
LEFT OUTER JOIN, RIGHT OUTER JOIN e 
o FULL OUTER JOIN
44
Tabelas de junção em SQL e junções 
externas (outer joins) no Oracle
•Oracle
SELECT F.Unome, S.Unome
FROM FUNCIONARIO F, FUNCIONARIO S
WHERE F.Cpf_supervisor (+) = S.Cpf;
•Padrão SQL
SELECT F.Unome, S.Unome
FROM (FUNCIONARIO AS F LEFT OUTER JOIN 
FUNCIONARIO AS S ON F.Cpf_supervisor = 
S.Cpf);
45
Tabelas de junção em SQL e junções 
externas (outer joins) no Oracle
•Oracle
SELECT F.Unome, S.Unome
FROM FUNCIONARIO F, FUNCIONARIO S
WHERE F.Cpf_supervisor = (+) S.Cpf;
•Padrão SQL
SELECT F.Unome, S.Unome
FROM (FUNCIONARIO AS F RIGHT OUTER JOIN 
FUNCIONARIO AS S ON F.Cpf_supervisor = 
S.Cpf);
46
Tabelas de junção em SQL e junções 
externas (outer joins) no Oracle
•Oracle
SELECT F.Unome, S.Unome
FROM FUNCIONARIO F, FUNCIONARIO S
WHERE F.Cpf_supervisor(+) = (+) S.Cpf;
•Padrão SQL
SELECT F.Unome, S.Unome
FROM (FUNCIONARIO AS F FULL OUTER JOIN 
FUNCIONARIO AS S ON F.Cpf_supervisor = 
S.Cpf);
47
Funções de agregação em SQL
• Usadas para resumir informações de várias 
tuplas em uma síntese de tupla única
• O agrupamento é usado para criar subgrupos 
de tuplas antes do resumo
• Podem ser usadas na cláusula SELECT ou na 
HAVING
– COUNT
– SUM
– MAX
– MIN
– AVG
48
Funções de agregação em SQL
• Achar a soma dos salários de todos os 
funcionários, o salário máximo, o salário 
mínimo e a média dos salários
SELECT SUM (Salario),
 MAX (Salario),
 MIN (Salario),
 AVG (Salario)
FROM FUNCIONARIO;
49
Funções de agregação em SQL
• Achar a soma dos salários de todos os 
funcionários do departamento ‘Pesquisa’, 
bem como o salário máximo, o salário mínimo 
e a média dos salários nesse departamento
SELECT SUM (Salario), MAX (Salario),
 MIN (Salario), AVG (Salario)
FROM (FUNCIONARIO JOIN DEPARTAMENTO ON 
Dnr=Dnumero)
WHERE Dnome=‘Pesquisa’;
50
Funções de agregação em SQL
• Recuperar o número total de funcionários na 
empresa
SELECT COUNT (*)
FROM FUNCIONARIO;
• Recuperar o número de funcionários no 
departamento ‘Pesquisa’
SELECT COUNT (*)
FROM FUNCIONARIO, DEPARTAMENTO
WHERE Dnr=Dnumero AND 
Dnome=‘Pesquisa’;
51
Funções de agregação em SQL
• Contar o número de valores de salário 
distintos no Banco de Dados
SELECT COUNT (DISTINCT Salario)
FROM FUNCIONARIO;
• Se escrevermos COUNT (Salario), os 
valores duplicados serão contados, porém, 
valores NULL não serão contados, ou seja, 
serão descartados
– Em geral, valores NULL são descartados quando 
as funções de agregação são aplicadas a um 
atributo
52
Funções de agregação em SQL
• Podemos usar uma função de agregação em 
uma consulta aninhada correlacionada e usar 
na cláusula WHERE
– Recuperar os nomes dos funcionários com 2 ou 
mais dependentes
SELECT Unome, Pnome
FROM FUNCIONARIO
WHERE ( SELECT COUNT (*)
 FROM DEPENDENTE
 WHERE Cpf=Fcpf ) >=2;
53
Agrupamento: as cláusulas GROUP BY 
e HAVING
• Se quisermos achar o salário médio dos 
funcionários em cada departamento ou o 
número de funcionários que trabalham em 
cada projeto precisamos particionar a 
relação em subconjuntos de tuplas (ou 
grupos) não sobrepostos
– Cada grupo consistirá nas tuplas que 
possuem o mesmo valor de algum(ns) 
atributo(s), chamado(s) atributo(s) de 
agrupamento
54
Agrupamento: as cláusulas GROUP BY 
e HAVING
• Podemos, então, aplicar a função a cada 
grupo desse tipo independentemente, para 
produzir informações de resumo sobre cada 
grupo
• Cláusula GROUP BY
– Especifica os atributos de agrupamento, que 
também devem aparecer na cláusula SELECT
– O valor resultante da aplicação de cada função 
de agregação a um grupo de tuplas aparece junto 
com o valor do(s) atributo(s) de agrupamento
55
Agrupamento: as cláusulas GROUP BY 
e HAVING
• Para cada departamento, recuperar o número do 
departamento, o número de funcionários no 
departamento e seu salário médio
SELECT Dnr, COUNT(*), AVG(Salario)
FROM FUNCIONARIO
GROUP BY Dnr;
• As tuplas FUNCIONARIO são divididas em grupos 
(cada grupo tendo o mesmo valor para o atributo 
de agrupamento Dnr)
– Cada grupo contém os funcionários que trabalham no 
mesmo departamento
– COUNT e AVG são aplicadas a cada grupo de tuplas
56
Agrupamento: as cláusulas GROUP BY 
e HAVING
SELECT Dnr, COUNT(*), AVG(Salario)
FROM FUNCIONARIO
GROUP BY Dnr;
57
Agrupamento: as cláusulas GROUP BY 
e HAVING
• Se houver NULLs no atributo de 
agrupamento, então um grupo separado é 
criado para todas as tuplas com um valor 
NULL no atributo de agrupamento
58
Agrupamento: as cláusulas GROUP BY 
e HAVING
• Para cada projeto, recuperar o número do 
projeto, o nome do projeto e o número de 
funcionários que trabalham nesse projeto
– Neste caso, o agrupamento e as funções são 
aplicados após a junção das duas relações
SELECT Projnumero, Projnome, COUNT (*)
FROM PROJETO, TRABALHA_EM
WHERE Projnumero=Pnr
GROUP BY Projnumero, Projnome;
59
Agrupamento: as cláusulas GROUP BY 
e HAVING
• A SQL oferece uma cláusula HAVING, que 
pode aparecer em conjunto com uma 
cláusula GROUP BY, e oferece uma 
condição sobre a informação de resumo 
referente ao grupo de tuplas, associado a 
cada valor dos atributos de agrupamento
• Somente os grupos que satisfazem a 
condição são recuperados no resultado da 
consulta
60
Agrupamento: as cláusulas GROUP BY 
e HAVING
• Para cada projeto em que mais de dois 
funcionários trabalham, recupere o número e 
o nome do projeto e o número de funcionários 
que trabalham no projeto
SELECT Projnumero, Projnome, COUNT (*)
FROM PROJETO, TRABALHA_EM
WHERE Projnumero=Pnr
GROUP BY Projnumero, Projnome
HAVING COUNT (*) > 2;
Agrupamento: as cláusulas GROUP BY 
e HAVING
61
Estes grupos 
não são 
selecionados 
pela 
condição 
HAVING da 
consulta 
anterior
62
Agrupamento: as cláusulas GROUP BY 
e HAVING
• Embora as condições de seleção na 
cláusula WHERE limitem as tuplas às quais 
as funções são aplicadas, a cláusula 
HAVING serve para escolher grupos 
inteiros
63
Agrupamento: as cláusulas GROUP BY 
e HAVING
• Para cada projeto, recupere o número e o 
nome do projeto e o número de funcionários 
do departamento 5 que trabalham no projeto
SELECT Projnumero, Projnome, COUNT (*)
FROM PROJETO, TRABALHA_EM, FUNCIONARIO
WHERE Projnumero=Pnr AND Cpf=Fcpf AND 
Dnr=5
GROUP BY Projnumero, Projnome;
– Aqui, restringimos as tuplas na relação (e, 
portanto, as tuplas em cada grupo) àquelas que 
satisfazem a condição especificada no WHERE
64
Agrupamento: as cláusulas GROUP BY 
e HAVING
• Suponha que queremos contar o número 
total de funcionários cujos salários são 
superiores a R$ 40.000,00 em cada 
departamento, mas somente os 
departamentos em que há mais de cinco 
funcionários trabalhando
– Aqui a condição (Salario > 40000) se 
aplica apenas à função COUNT na cláusula 
SELECT
65
Agrupamento: as cláusulas GROUP BY 
e HAVING
SELECT Dnome, COUNT (*)
FROM DEPARTAMENTO, FUNCIONARIO
WHERE Dnumero=Dnr AND Salario>40000
GROUP BY Dnome
HAVING COUNT (*) > 5;
• Essa consulta seleciona:
– Somente departamentos que tenham mais de 
cinco funcionários? ou
– Que tenham mais de cinco funcionários que 
ganham, cada um, mais de R$ 40.000,00?
66
Agrupamento: as cláusulas GROUP BY 
e HAVING
• A regra é que a cláusula WHERE é 
executada primeiro, para selecionar as 
tuplas individuais ou tuplas de junção
• A cláusula HAVING é aplicada depois, 
para selecionar grupos individuais de 
tuplas
– Logo, as tuplas já estão restritas a 
funcionários que ganham mais de R$ 
40.000,00 antes que a cláusula HAVING seja 
aplicada
67
Agrupamento: as cláusulas GROUP BY 
e HAVING
• Modo correto:
SELECT Dnumero, COUNT (*)
FROM DEPARTAMENTO, FUNCIONARIO
WHERE Dnumero=Dnr AND Salario>40000 
AND Dnr IN 
( SELECT F.Dnr
FROM FUNCIONARIO F
GROUP BY F.Drn
HAVING COUNT (*) > 5);
68
Resumo das consultas em SQL
• Uma consulta pode consistir em até seis 
cláusulas, mas apenas as duas primeiras são 
obrigatórias
– Ela pode se espalhar por várias linhas, 
terminando com um ponto e vírgula 
SELECT <lista atributos e função>
FROM <lista tabelas>
[WHERE <condição>]
[GROUP BY <atributo(s) de agrupamento>]
[HAVING <condição de grupo>]
[ORDER BY <lista atributos>];
69
Consultas SQL
• A consulta é avaliada na seguinte ordem
1. FROM: para identificar todas as tabelas 
envolvidas na consulta
2. WHERE: para selecionar e juntar tuplas
3. GROUP BY e HAVING
– O ORDER BY é aplicado no fim para classificar 
o resultado da consulta• É preferível escrever uma consulta com o 
mínimo de aninhamento e de ordenação 
possível
70
Especificando restrições como 
asserções e ações como triggers
• CREATE ASSERTION
– Pode ser usado para especificar tipos adicionais 
de restrições que estão fora do escopo das 
restrições embutidas no modelo relacional (chave 
primária, integridade de entidade e referencial)
– Essas restrições podem ser especificadas no 
CREATE TABLE
• CREATE TRIGGER
– Pode ser usado para especificar ações 
automáticas que o SGBD realizará quando certos 
eventos e condições ocorrerem
71
Especificando restrições gerais como 
asserções em SQL
• Restrições gerais: aquelas que não se 
encaixam em nenhuma das categorias já 
descritas (chave primária, integridade de 
entidade e referencial)
• Cada asserção recebe um nome de 
restrição e é especificada por uma 
condição semelhante à cláusula WHERE de 
uma consulta SQL
72
Especificando restrições gerais como 
asserções em SQL
• Restrição: o salário de um funcionário não 
pode ser maior que o salário do gerente do 
departamento para o qual o funcionário 
trabalha
CREATE ASSERTION RESTRICAO_SALARIAL
CHECK (NOT EXISTS
( SELECT *
FROM FUNCIONARIO F, FUNCIONARIO G, 
DEPARTAMENTO D
WHERE F.Salario>G.Salario AND 
F.Dnr=D.Dnumero AND 
D.Cpf_gerente=G.Cpf) ); 
73
Especificando restrições gerais como 
asserções em SQL
• O nome RESTRICAO_SALARIAL é 
seguido pela palavra-chave CHECK, que é 
seguida por uma condição entre 
parênteses que precisa ser verdadeira em 
cada estado do BD para que a asserção 
seja satisfeita
– O nome da restrição pode ser usado mais 
tarde para se referir à restrição ou para 
modificá-la ou excluí-la
74
Especificando restrições gerais como 
asserções em SQL
• Qualquer condição de cláusula WHERE 
pode ser usada, mas muitas restrições 
podem ser especificadas usando o estilo 
EXISTS e NOT EXISTS das condições 
em SQL
– A restrição é violada caso o comando 
ASSERTION for avaliado como FALSE
– A restrição é satisfeita por uma estado do BD 
se nenhuma combinação de tuplas nesse 
estado do BD violar a restrição
75
Especificando restrições gerais como 
asserções em SQL
• Por vezes, utilizamos o artifício de ver se 
o resultado da consulta não for vazio, a 
asserção é violada
– No exemplo anterior, a consulta seleciona 
todos os funcionários cujos salários são 
maiores que o salário do gerente de seu 
departamento
76
Especificando restrições gerais como 
asserções em SQL
• O projetista do esquema deve usar 
CREATE ASSERTION somente em casos 
em que não é possível usar CHECK sobre 
atributos, domínios ou tuplas, de modo 
que verificações simples são 
implementadas de modo mais eficiente 
pelo SGBD
77
Introdução às triggers em SQL
• Em muitos casos, é conveniente 
especificar um tipo de ação a ser 
tomada quando certos eventos 
ocorrerem e quando certas condições 
são satisfeitas
– Exemplos:
• Especificar uma condição que, se violada, faz 
que algum usuário seja informado dela
• Um gerente ser informado se as despesas de 
viagem de um funcionário excederem certo 
limite
78
Introdução às triggers em SQL
• A condição é usada para monitorar o BD
• Vários eventos podem disparar um trigger:
– Inserir um novo registro de funcionário
– Alterar um salário de um funcionário
– Alterar o supervisor de um funcionário
79
Introdução às triggers em SQL
CREATE TRIGGER VIOLACAO_SALARIAL
BEFORE INSERT OR UPDATE OF Salario, 
Cpf_supervisor ON FUNCIONARIO
FOR EACH ROW
WHEN (NEW.Salario >
(SELECT Salario
 FROM FUNCIONARIO
 WHERE Cpf = NEW.Cpf_supervisor))
 INFORMAR_SUPERVISOR 
(NEW.Cpf_Supervisor, NEW.Cpf);
Stored Procedure
80
Introdução às triggers em SQL
• Uma trigger típica tem três componentes
1. O(s) evento(s): Operações de atualização 
no BD
• Inserir um novo registro, alterar salário de um 
funcionário ou alterar o supervisor de um 
funcionário
• A pessoa que escreve a trigger precisa garantir 
que todos os eventos possíveis sejam 
considerados
• São especificados após a palavra-chave BEFORE 
ou AFTER
81
Introdução às triggers em SQL
• Uma trigger típica tem três componentes
2. A condição de execução da ação
• Depois que o evento de disparo tiver ocorrido, 
uma condição opcional pode ser avaliada
• Se uma condição for especificada, ela primeiro é 
avaliada e, somente se for avaliada como 
verdadeira, a ação da regra será executada
• A condição é especificada na cláusula WHEN da 
trigger
82
Introdução às triggers em SQL
• Uma trigger típica tem três componentes
3. A ação a ser tomada:
• Normalmente é uma sequência de instruções em 
SQL
• Poderia ser uma transação do BD ou um 
programa externo que seria executado 
automaticamente
• No exemplo anterior, a ação é executar o 
procedimento armazenado (stored procedure) 
INFORMAR_SUPERVISOR
83
Introdução às triggers em SQL
• As triggers podem ser usadas em várias 
aplicações
– Manutenção da coerência do BD
– Monitoramento de atualizações do BD
– Atualização de dados derivados 
automaticamente
84
Visões (views)
• Uma view em terminologia SQL é uma única 
tabela que é derivada de outras tabelas
– Essas outras tabelas são chamadas de tabelas 
da base
• Uma view não necessariamente existe em 
forma física
– É considerada uma tabela virtual (ou tabela de 
definição), ao contrário das tabelas da base, 
cujas tuplas sempre estão armazenadas 
fisicamente no BD
85
Visões (views)
• Uma view é como um modo de especificar uma 
tabela que precisamos referenciar com 
frequência, embora ela possa não existir 
fisicamente
– Por exemplo: podemos emitir frequentemente 
consultas que recuperam o nome de funcionário e os 
nomes dos projetos em que o funcionário trabalha, 
porém, essas informações estão em 3 tabelas
– Podemos definir uma view que é especificada como o 
resultado das junções dessas 3 tabelas
– Assim, ao emitir consultas sobre a view, são feitas 
leituras em uma única tabela, em vez de leituras de 2 
junções sobre 3 tabelas
86
Especificação das views em SQL
• O comando para especificar uma view é 
CREATE VIEW
– A view recebe um nome de tabela (virtual), ou 
nome de view, uma lista de nomes de atributo 
e uma consulta para especificar o conteúdo 
da view
87
Especificação das views em SQL
CREATE VIEW TRABALHA_EM1
AS SELECT Pnome, Unome, Projnome, 
Horas
FROM FUNCIONARIO, PROJETO, 
TRABALHA_EM
WHERE Cpf=Fcpf AND Pnr=Projnumero;
88
Especificação das views em SQL
CREATE VIEW DEP_INFO(Dep_nome, 
Qtd_func, Total_sal)
AS SELECT Dnome, COUNT (*), SUM 
(Salario)
FROM DEPARTAMENTO, FUNCIONARIO
WHERE Dnumero=Dnr
GROUP BY Dnome;
89
Especificação das views em SQL
• A view DEP_INFO especifica, explicitamente, 
novos nomes de atributo, usando uma 
correspondência um para um entre os 
atributos especificados na cláusula CREATE 
VIEW e aqueles especificados na cláusula 
SELECT da consulta que define a view
• Podemos especificar consultas SQL em um 
view da mesma forma como fazemos 
consultas envolvendo tabelas da base
90
Especificação das views em SQL
• Recuperar o primeiro e o último nome de 
todos os funcionários que trabalham no 
‘ProdutoX’, usando a view TRABALHA_EM1
SELECT Pnome, Unome,
FROM TRABALHA_EM1
WHERE Projnome=‘ProdutoX’;
• A mesma consulta exigiria a especificação de 
duas junções, se fosse realizada sobre as 
relações da base diretamente
– É mais simples e rápido
91
Especificação das views em SQL
• Se não precisarmos mais de uma view, 
podemos usar o comando DROP VIEW 
para descartá-la
DROP VIEW TRABALHA_EM1 
92
Implementação e atualização de view
• A estratégia chamada materialização de 
view consisteem criar fisicamente uma 
tabela de view temporária quando a view for 
consultada pela primeira vez e manter essa 
tabela na suposição de que outras consultas 
a view acontecerão em seguida
– Nesse caso, deve ser desenvolvida uma 
estratégia de atualização automática da tabela
– As técnicas que usam o conceito de atualização 
incremental têm sido desenvolvidas para essa 
finalidade. A tabela de view é modificada quando 
há alguma atualização uma das tabelas de base
93
Implementação e atualização de view
• A atualização das views é complicada e 
pode ser ambígua
• Em geral, uma atualização em uma view 
definida sobre uma única tabela sem 
quaisquer funções de agregação pode ser 
mapeada para uma atualização sobre a 
tabela base sob certas condições
94
Implementação e atualização de view
• Uma view com uma única tabela de definição 
é atualizável se seus atributos tiverem a 
chave primária da relação da base, bem 
como todos os atributos com a restrição NOT 
NULL que não tem valor default especificado
• As views definidas sobre múltiplas tabelas 
usando junções geralmente não são 
atualizáveis
• As views definidas usando funções de 
agrupamento e agregação não são 
atualizáveis
95
Instruções de alteração de esquema 
em SQL
• Comandos de evolução de esquema
– Usados para alterar um esquema, 
acrescentando ou removendo tabelas, 
atributos, restrições e outros elementos dele
– Pode ser feito enquanto o BD está operando 
e não exige recompilação do esquema
– Certas verificações precisam ser feitas pelo 
SGBD para garantir que as mudanças não 
afetarão o restando do BD, tornando-o 
inconsistente
96
O comando DROP
• Remove tabelas, domínios ou restrições, 
além do próprio esquema
– Se todo um esquema não for mais 
necessário, o comando DROP SCHEMA pode 
ser utilizado
• Existem duas opções de comportamento 
de DROP
– CASCADE
– RESTRICT
97
O comando DROP
• Para remover o esquema do Banco de Dados 
EMPRESA e todas as suas tabelas, domínios 
e outros elementos, o CASCADE é usado
– DROP SCHEMA EMPRESA CASCADE;
• Se a opção RESTRICT for usada ao invés de 
CASCADE, o esquema será removido somente 
se ele não tiver elementos
– No RESTRICT, o usuário deverá remover, 
individualmente, cada elemento no esquema, 
depois remover o próprio esquema
98
O comando DROP
• Se uma relação da base (tabela) dentro de um 
esquema não for mais necessária, a relação e 
sua definição podem ser excluídas usando o 
comando DROP TABLE
DROP TABLE DEPENDENTE CASCADE
• Se a opção RESTRICT for usada ao invés de 
CASCADE, a tabela é removida somente se ela 
não for referenciada em quaisquer restrições (por 
exemplo, por definições de chave estrangeira) ou 
views, ou por quaisquer outros elementos
– Com o CASCADE, todas essas restrições, views e 
outros elementos que referenciam a tabela serão 
excluídos automaticamente
99
O comando DROP
• O comando DROP TABLE não apenas 
exclui todos os registros na tabela se tiver 
sucesso, mas também remove a definição 
da tabela no catálogo
– Se for desejado excluir apenas os registros, 
mas deixar a definição de tabela para uso 
futuro, então o comando DELETE deve ser 
usado no lugar do DROP TABLE
100
O comando ALTER
• O ALTER modifica
– Tabela de base
• Acrescentar ou remover uma coluna (atributo)
• Alterar uma definição de coluna
• Acrescentar ou remover restrições de tabela
– Outros elementos de esquema nomeados
101
O comando ALTER
CREATE TABLE FUNCIONARIO
(Pnome VARCHAR(15) NOT NULL, Minicial 
CHAR, Unome VARCHAR(15) NOT NULL, Cpf 
CHAR(11) NOT NULL, Datanasc DATE, 
Endereco VARCHAR(30), Sexo CHAR, Salario 
DECIMAL(10,2), Cpf_supervisor CHAR(11) 
NOT NULL, Dnr INT PRIMARY KEY(Cpf));
• Para incluir um atributo que mantém as tarefas 
dos funcionários na relação de base 
FUNCIONARIO do esquema EMPRESA
ALTER TABLE EMPRESA.FUNCIONARIO ADD 
COLUMN Tarefa VARCHAR(12);
102
O comando ALTER
• Para inserir um novo valor para o atributo 
Tarefa em cada tupla individual já 
existente nas tabelas, basta usar o 
comando UPDATE para cada tupla ou 
especificar o valor default
– Se nenhuma cláusula default for especificada, 
o novo atributo receberá o valor NULL em 
todas as tuplas da relação imediatamente
– Logo, a restrição NOT NULL não é permitida 
nesse caso
103
O comando ALTER
• Para remover uma coluna, temos que 
escolher entre CASCADE ou RESTRICT
– CASCADE: todas as restrições e views que 
referenciam a coluna são removidas 
automaticamente do esquema, junamente 
com a coluna
– RESTRICT: o comando só tem sucesso se 
nenhuma view ou restrição referenciar a 
coluna
104
O comando ALTER
ALTER TABLE EMPRESA.FUNCIONARIO DROP 
COLUMN Endereco CASCADE;
• Também é possível alterar uma definição de 
coluna removendo uma cláusula default ou 
definindo uma
ALTER TABLE EMPRESA.DEPARTAMENTO ALTER 
COLUMN Cpf_gerente DROP DEFAULT;
ALTER TABLE EMPRESA.DEPARTAMENTO ALTER 
COLUMNS Cpf_gerente SET DEFAULT 
‘33344555587’;
105
O comando ALTER
• Também é possível alterar as restrições 
especificadas sobre uma tabela ao 
acrescentar ou remover uma restrição 
nomeada
ALTER TABLE EMPRESA.FUNCIONARIO
DROP CONSTRAINT CHESUPERFUNC 
CASCADE;
– Se quisermos acrescentar uma nova 
restrição, basta colocar a cláusula ADD
106
Bibliografia
• Capítulo 5: Elsmari Ramez; Navathe 
Shamkant B. Sistemas de Banco de 
Dados – Fundamentos e Aplicações. 6 ed. 
Pearson, São Paulo, 2011.