FBD 08 SQL Avancado

Prévia do material em texto

SQL Avançado 
 
 
Regis Pires Magalhães 
regispires@lia.ufc.br 
Renomeação de atributos e relações 
• Acrescentar o qualificador AS seguindo pelo 
novo nome desejado. 
• AS é usado para apelidar os nomes tanto do 
atributo, quanto da relação. 
Comparações envolvendo NULL 
• Significados de NULL 
▫ Valor desconhecido. 
▫ Valor indisponível ou retido. 
▫ Atributo não aplicável. 
• Ao usar NULL em uma comparação, SQL usa 
uma lógica de três valores para determinar se 
uma expressão envolvendo AND, OR e NOT é 
verdadeira ou falsa. 
Conectivos lógicos na lógica de três 
valores 
Comparações envolvendo NULL 
• Consultas podem checar se um atributo é NULL: 
▫ IS NULL 
▫ IS NOT NULL 
 
Consultas 
• Checando valores nulos 
▫ Predicado IS [NOT] NULL 
▫ Exemplo 
select * from Empregado 
where dt-nasc is null 
 
select * from Empregado 
where dt-nasc is not null 
 
Consultas 
• Predicado [NOT] IN 
 Verifica a pertinência de elementos em um conjunto 
 Exemplo 
select nome 
from Empregado 
where matr in (1,5,8,9); 
 
select nome 
from Empregado 
where matr not in (1,5,8,9) 
 
 
8 
SQL 
• Funções Agregadas 
▫ Funções embutidas (built-in) aplicadas sobre uma coleção 
de valores (colunas) do banco de dados 
▫ sum 
 Retorna o somatório dos valores de uma coleção 
▫ avg 
 Retorna a média dos valores de uma coleção 
▫ max 
 Retorna o maior valor de uma coleção de valores 
▫ min 
 Retorna o menor valor de uma coleção 
▫ count 
 Retorna o número de elementos de uma coleção 
▫ Sintaxe 
 nome-da-função (ALL | DISTINCT nome-coluna) | count(*) 
 Não podem ser utilizados na cláusula WHERE 
SQL – Funções de agregação 
• COUNT 
▫ Retorna o número de tuplas ou valores. 
• SUM, MAX, MIN e AVG 
▫ Retornam, respectivamente, a soma, o valor 
máximo, o valor mínimo e a média desses valores. 
• Essas funções podem ser usadas nas cláusulas 
SELECT ou HAVING. 
SQL – Funções agregadas 
select pnome funcionario, count(*) numero_depend 
from funcionario, dependente 
where cpf=fcpf 
group by pnome; 
SQL – Funções agregadas 
• 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. 
SQL – Funções agregadas 
• 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. 
SQL – Funções agregadas 
• Recuperar o número total de funcionários na 
empresa. 
SQL – Funções agregadas 
• Recuperar o número de funcionários no 
departamento ‘Pesquisa’. 
SQL – Funções agregadas 
• Contar o número de valores de salário distintos no 
banco de dados. 
16 
SQL 
• Exercícios 
▫ Encontre o número de empregados lotados no 
departamento de Informática 
 
 
 
▫ Encontre o montante da folha de pagamento da 
empresa 
 
 
▫ Encontre o salário médio pago pela empresa 
 
 
 
select count(*) 
from Empregados as e, Departamentos as d 
where e.id_dept=d.id and d.nome = ‘Informática’; 
select sum(salario) 
from Empregado 
select avg(salario) 
from Empregado 
17 
SQL 
• Agrupando tuplas no SQL 
▫ Aplicar funções agregadas a diferentes grupos de tuplas 
▫ Exemplo 
 Listar a quantidade de empregados por departamento 
▫ Cláusula 
 GROUP BY 
▫ Exemplo 
select id_dept, count(*) as quantidade_empregados 
from Empregados 
group by id_dept 
id_dept quantidade_empregados 
----------- -------------------- 
1 4 
2 2 
A função count é aplicada 
para o conjunto de tuplas 
de cada grupo 
SQL 
• Agrupando tuplas no SQL (cont.) 
▫ Todas colunas que aparecem na cláusula select têm 
que aparecer na cláusula group by 
 Exceto os argumentos da funções agregadas 
▫ Exemplo de sintaxe incorreta 
select lotacao, matr, count(*) from empregado 
group by lotacao 
▫ Exemplos 
 Lista de todos empregados e a quantidade de dependentes que 
cada um deles possui. 
 
SQL- Funções agregadas 
• Para cada departamento, recuperar o número do 
departamento, o número de funcionários no 
departamento e seu salário médio. 
SQL – Funções agregadas 
Agrupamento de tuplas FUNCIONARIO pelo valor de Dnr: 
SQL- Funções agregadas 
• Para cada projeto, recuperar o número do projeto, o 
nome do projeto e o número de funcionários que 
trabalham nesse projeto. 
SQL- Funções agregadas 
• 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. 
Após aplicar a cláusula WHERE, mas 
antes de aplicar HAVING 
Grupos não 
selecionados 
pela condição 
HAVING 
Após aplicar a condição da cláusula 
HAVING 
SQL – Funções agregadas 
• 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. 
26 
SQL 
• Agrupando tuplas no SQL (cont.) 
▫ Exemplos (cont.) 
 Listar maiores e menores salários de cada departamento 
 
 
 
 
 Totalizar a quantidade de itens vendidos por vendedor e por item, 
considerando o seguinte esquema 
 Vendedor(matr, nome, salário) 
 Vendas(matr,cod_item,qtde, pr-venda,dt-hora-venda) 
 Estoque(cod_item, referência, pr_compra,pr_venda,qtde) 
select d.nome, max(e.salario) as Maior_Salario, min(e.salario) as 
Menor_Salario 
from Departamento d, Empregado e 
where d.cod_dep=e.lotacao 
group by d.nome 
select v.nome, e.referência, sum(d.qtde) as total 
from Estoque e, Vendedor v, Vendas d 
where e.cod_item=d.cod_item and v.matr=d.matr 
group by v.nome, e.referência 
27 
SQL 
• Selecionando grupos 
▫ Listar nome dos departamentos cuja média salarial seja 
maior que 7000 
▫ Cláusula having 
 Filtro de grupos 
 Exemplo 
select d.nome, avg(salario) 
from Departamento d, Empregado e 
where d.cod_depart=e.lotacao 
group by d.nome 
 having avg(e.salario)>7000 
▫ Consulta com where e having 
 predicado da cláusula where é avaliado primeiramente 
 Tuplas que satisfazem o predicado são agrupadas pelo group by 
 Predicado da cláusula having é avaliado 
 Grupos que satisfazem o predicado aparecem no resultado 
28 
SQL 
• Selecionando grupos (cont.) 
▫ Exemplos 
 Listar nome e média salarial dos departamentos que possuem 
mais de 10 empregados lotados 
 
 
 
 
 Listar nome e quantidade de empregados dos departamentos 
cuja média salarial é maior que 5000 
select d.nome, avg(e.salario) as 
Média_Salarial 
from Departamento d, Empregado e 
where d.cod_dep=e.lotacao 
group by d.nome 
 having count(matr)>=11 
select d.nome, count(*) as Número_Empregados 
from Departamento d, Empregado e 
where d.cod_dep=e.lotacao 
group by d.nome 
 having avg(e.salario)>5000 
SQL 
• Selecionando grupos (cont.) 
▫ Exemplos 
 Listar nome de vendedores e quantidade de vendas efetuadas dos 
vendedores com volume de vendas superior a 10000 (classificado 
em ordem decrescente por quantidade de vendas), considerando o 
seguinte esquema: 
 Vendedor(matr, nome, salário) 
 Vendas(matr,cod_item,qtde, pr-venda,dt-hora-venda) 
 Estoque(cod_item, referência, pr_compra,pr_venda,qtde) 
select v.nome, count(*) as total_de_vendas 
from Vendedor v, Vendas d 
where v.matr=d.matr 
group by v.nome 
 having sum(d.qtde*d.pr_venda) > 10000 
order by count(*) 
Forma básica do comando SELECT 
SELECT <lista de atributos e funções> 
FROM <lista de tabelas> 
[ WHERE <condição> ] 
[ GROUP BY <atributos de agrupamento> ] 
[ HAVING <condição de agrupamento> ] 
[ ORDER BY <atributosde ordenação> ]; 
Consultas aninhadas 
• Blocos select-from-where dentro da cláusula 
WHERE de outra consulta (consulta externa). 
 
Consultas aninhadas 
Não aninhada 
Consultas aninhadas 
Aninhada 
Consultas aninhadas 
• Operador de comparação IN 
▫ Compara o valor v com um conjunto de valores V. 
▫ Resulta TRUE se v é um dos elementos em V. 
Consultas aninhadas 
• SQL permite o uso de tuplas de valores em 
comparações. 
▫ Devem ser escritas entre parênteses. 
select distinct Fcpf, pnr, horas 
from trabalha_em 
where (pnr,horas) in ( 
 select pnr,horas 
 from trabalha_em, funcionario 
 where fcpf='12345678966' 
); 
36 
SQL 
• Consulta SQL aninhada (subconsulta) 
▫ Consulta SQL especificada dentro de uma outra 
consulta SQL 
▫ Exemplo 
 Listar todos os empregados que têm salário maior que a 
média salarial da empresa 
 
select pnome,salario 
from funcionario 
where salario > (select avg(salario) from 
funcionario); 
37 
SQL 
• Consulta SQL aninhada (cont.) 
▫ Subconsulta escalar 
 Subconsulta que retorna apenas um valor (uma 
única linha e uma única coluna) 
 Pode aparecer na lista de argumentos da cláusula 
select e na cláusula where 
 Exemplo 
 Listar nome dos empregados com a quantidade de 
dependentes de cada empregado 
 
select pnome, (select count(*) from 
dependente where fcpf=cpf) 
from funcionario; 
38 
SQL 
• Consulta SQL aninhada (cont.) 
▫ Usando o Predicado [NOT] IN 
 Listar os CPFs dos funcionários que trabalham 
em projetos localizados em Maua. 
select distinct fcpf 
from trabalha_em 
where pnr in ( 
 select projnumero 
 from projeto 
 where projlocal='Maua' 
); 
Consultas aninhadas 
• É possível o uso de um conjunto explícito de 
valores na cláusula WHERE. 
Consultas aninhadas 
• Evite erros em ambiguidades criando apelidos (aliases) para 
todas as tabelas referenciadas em uma consulta SQL. 
Consultas aninhadas 
SQL – Funções agregadas 
• Para cada departamento que tem mais de cinco funcionários, 
recuperar o número do departamento e o número de seus 
funcionários que estão ganhando mais de R$ 40.000,00. 
Consultas aninhadas 
• Usar outros operadores de comparação para 
comparar um único valor v. 
• Operador = ANY (ou = SOME) 
▫ Retorna TRUE se o valor v é igual a algum valor no 
conjunto V e, portanto, é equivalente a IN. 
• Outros operadores que podem ser combinados com 
ANY (ou SOME): >, >=, <, <= e <>. 
• ALL também pode ser combinada com cada um 
desses operadores. 
▫ v > ALL V retorna TRUE se o valor v é maior do que 
todos os valores no conjunto V. 
Consultas aninhadas 
select distinct fcpf 
from trabalha_em 
where pnr = any ( 
 select projnumero 
 from projeto 
 where projlocal='Maua' 
); 
select distinct fcpf 
from trabalha_em 
where pnr = some ( 
 select projnumero 
 from projeto 
 where projlocal='Maua' 
); 
select distinct fcpf 
from trabalha_em 
where pnr in ( 
 select projnumero 
 from projeto 
 where projlocal='Maua' 
); 
Consultas aninhadas 
select pnome, salario 
from funcionario 
where salario <= ALL (select salario from 
funcionario); 
select pnome, salario 
from funcionario 
where salario = (select min(salario) from 
funcionario); 
Consultas aninhadas 
select pnome, salario 
from funcionario 
where salario >= ALL (select salario from 
funcionario); 
select pnome, salario 
from funcionario 
where salario = (select max(salario) from 
funcionario); 
Consultas aninhadas 
• Consulta para retornar os nomes dos funcionários 
cujo salário é maior do que o salário de todos os 
funcionários no departamento 5. 
Essa consulta também pode ser especificada usando a 
função de agregação MAX. 
48 
SQL 
• Consulta SQL aninhada (cont.) 
▫ Predicados SOME, ANY e ALL 
 Listar empregados que ganham salários maior ou 
igual a média salarial de um departamento 
 Sintaxe 
 expr  {SOME | ANY | ALL} (subconsulta) 
   {<, <=,>, >=, =, <>} 
 
select nome 
from Empregado 
where salário >=some (select avg(salário) from Empregado 
 group by lotação) 
49 
SQL 
• Consulta SQL aninhada (cont.) 
▫ Predicados SOME, ANY e ALL (cont.) 
 SOME (subconsulta) e ANY (subconsulta) 
 Retornam verdade se e somente se 
▫ Para pelo menos um elemento s retornado pela 
subconsulta, expr  s é verdade 
 São equivalentes 
 ALL (subconsulta) 
 Retorna verdade se e somente se, 
▫ Para todo elemento s retornado pela subconsulta, 
expr  s é verdade 
 Listar funcionários que ganham salários maior ou igual ao 
maior salário pago pela empresa. 
 
select pnome, salario from funcionario where salario >= 
ALL (select salario from funcionario); 
select pnome, salario from funcionario where salario = 
(select max(salario) from funcionario); 
50 
SQL 
• Consulta SQL aninhada (cont.) 
▫ Predicados SOME, ANY e ALL (cont.) 
 Listar o departamento com maior média salarial 
 Não é permitido função agregada composta 
select d.nome 
from Empregado e, Departamento d 
where d.cod_depart=e.lotação 
group by d.nome 
 having avg(salário) >=all (select avg(salário) from Empregado 
 group by lotação) 
Consultas aninhadas correlacionadas 
• Ocorre quando uma consulta aninhada referencia 
algum atributo de uma relação declarada na 
consulta externa. 
• Nesse caso, as duas consultas são chamadas 
correlacionadas. 
• A consulta aninhada é avaliada uma vez para cada 
tupla (ou combinação de tuplas) na consulta 
externa. 
Consultas aninhadas correlacionadas 
• EXISTS e NOT EXISTS 
▫ Verifica se o resultado de uma consulta aninhada 
correlacionada é vazio (não contém tuplas) ou não. 
 
Consultas aninhadas correlacionadas 
• EXISTS e NOT EXISTS 
select pnome 
from funcionario 
where exists ( 
 select * 
 from dependente 
 where cpf = fcpf 
); 
select pnome 
from funcionario 
where cpf in ( 
 select fcpf from dependente 
); 
Consultas aninhadas correlacionadas 
• Recuperar os nomes de funcionários que não 
possuem dependentes. 
 
Consultas aninhadas correlacionadas 
• Listar os nomes dos gerentes que possuem pelo 
menos um dependente. 
 
Consultas aninhadas correlacionadas 
• Recuperar o nome de cada funcionário que trabalha 
em todos os projetos controlados pelo departamento 
5. 
 
Consultas aninhadas correlacionadas 
• Recuperar os nomes de todos os funcionários que 
têm dois ou mais dependentes. 
select pnome 
from funcionario 
where ( 
 select count(*) 
 from dependente 
 where cpf = fcpf 
) >= 2; 
58 
SQL 
• Consulta SQL aninhada (cont.) 
• Predicado EXISTS 
• Verifica se o conjunto retornado por uma subconjunto é vazio ou 
não 
• Listar empregados que possuem dependentes 
select pnome 
from funcionario 
where exists ( 
 select * 
 from dependente 
 where cpf=fcpf 
); 
select pnome from funcionario 
where cpf in ( 
 select fcpf from dependente 
); 
59 
SQL 
• Consulta SQL aninhada (cont.) 
▫ Predicado EXISTS (cont.) 
 Sintaxe 
 [NOT] EXISTS (subconsulta) 
 EXISTS (subconsulta) 
 Retorna verdade se e somente se 
▫ O conjunto retornado por subconsulta não é vazio 
 NOT EXISTS (subconsulta) 
 Retorna verdade se e somente se 
▫ O conjunto retornado por subconsulta é vazio 
 Listar nome de departamentos com empregados ganhando 
duas vezes mais que a média do departamento 
select d.nome 
from Departamento d 
where exists (select * from Empregado e 
 where e.lotacao=d.cod_depart and 
 salário > (2*(select avg(salário) from Empregado 
 where lotação=e.lotação)))UNIQUE 
• A função UNIQUE(C) retorna TRUE se não 
houver tuplas duplicadas no resultado da 
consulta C. 
Junções 
• Usuários podem especificar uma tabela 
resultante de uma operação de junção na 
cláusula FROM de uma consulta. 
▫ Consulta a seguir tem uma única tabela de junção. 
Junções 
• Tipos 
▫ NATURAL JOIN 
 Natural JOIN sobre duas relações R e S, nenhuma 
condição de junção é especificada. 
 Cria-se uma condição EQUIJOIN implícita para 
cada par de atributos com o mesmo nome. 
 
 
select pnome, nome_dep 
from funcionario 
 natural join departamento as dep 
 (nome_dep, dnr, cpf_gerente, data_inicio_gerente); 
Junções 
• Tipos 
▫ NATURAL JOIN 
 
 
Junções 
• Inner join 
▫ Tipo padrão de junção em uma tabela de junção. 
▫ A tupla é incluída no resultado somente se uma 
tupla correspondente existir na outra relação. 
select pnome, dnome 
from funcionario 
 inner join departamento 
 on dnr=dnumero; 
select pnome, dnome 
from funcionario, departamento 
 where dnr=dnumero; 
Explícito 
Implícito 
Junções 
• LEFT OUTER JOIN 
▫ Toda tupla na tabela esquerda deve aparecer no 
resultado. 
▫ Se não houver tupla correspondente: 
 Preenchido com valores NULL para atributos da 
tabela da direita. 
select pnome, dnome 
from funcionario 
 left outer join departamento 
 on dnr=dnumero; 
Junções 
Junções 
Junções 
• RIGHT OUTER JOIN 
▫ Toda tupla na tabela direita deve aparecer no 
resultado. 
▫ Se não houver tupla correspondente: 
 Preenchido com valores NULL para atributos da 
tabela da esquerda. 
select pnome, dnome 
from funcionario 
 right outer join departamento 
 on dnr=dnumero; 
Junções 
• FULL OUTER JOIN 
▫ Toda tupla nas tabelas direita e esquerda deve 
aparecer no resultado. 
select pnome, dnome 
from funcionario 
 full outer join departamento 
 on dnr=dnumero; 
Junções 
• Sintaxe do Oracle: 
▫ +=  LEFT OUTER JOIN 
▫ =+  RIGHT OUTER JOIN 
▫ +=+  FULL OUTER JOIN 
Junções 
• Múltiplas junções em uma mesma consulta. 
72 
SQL 
• Formas de Junção em SQL Avançado 
▫ Listar nomes dos empregados e os nomes dos 
seus departamentos. 
 select d.nome 
 from Empregado as e, Departamento as d 
 where d.id =e.id_dept 
produto cartesiano projeção 
seleção 
d.nome(d.id=e.id_dept(Departamento x Empregado)) 

 
Departamento 
d.id=e.id_dept
Empregado 
73 
SQL 
• Formas de Junção em SQL Avançado (cont.) 
▫ Sintaxe da cláusula FROM 
[ FROM {<tabela_fonte>} [,...n] ] 
<table_fonte> ::= 
 nome_tabela [ [AS] qualificador ] 
 | <tabela_fonte> <tipo_junção> <tabela_fonte> ON 
 <condição_junção> 
 
 <tipo_junção> ::= 
 [ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ] [ <join_hint> ] 
 JOIN 
74 
SQL 
• Formas de Junção em SQL Avançado (cont.) 
▫ Sintaxe da cláusula FROM (cont.) 
 Tipos de junção 
 Junção theta 
▫ INNER JOIN 
 Junção externa à esquerda 
▫ LEFT OUTER JOIN 
 Junção externa à direita 
▫ RIGHT OUTER JOIN 
 Junção externa completa 
▫ FULL OUTER JOIN 
▫ Junção theta 
 Exemplo 
 Listar nome dos empregados com o nome do respectivo 
departamento 
select e.nome, d.nome 
from Empregado as e inner join Departamento as d on e.id_dept = d.id 
75 
SQL 
• Formas de Junção em SQL Avançado (cont.) 
 Junção Externa (OUTER JOIN) 
Adicionar tuplas extras ao resultado de uma 
junção natural 
76 
SQL 
• Formas de Junção em SQL Avançado 
(cont.) 
▫ LEFT OUTER JOIN 
 Calcula o resultado da junção 
 Adiciona ao resultado da junção 
▫ Tuplas da relação à esquerda que não satisfazem a 
condição de junção 
▫ Atribui valores nulos aos atributos não definidos 
para estas tuplas 
 Exemplo 
 Listar nome do empregado e o nome de seu 
dependente. O nome do empregado deve ser 
exibido mesmo que ele não possua dependente. 
 
77 
SQL 
• Formas de Junção em SQL Avançado (cont.) 
▫ RIGHT OUTER JOIN 
 Calcula o resultado da junção 
 Adiciona ao resultado da junção 
▫ Tuplas da relação à direita que não satisfazem a condição 
de junção 
▫ Atribui valores nulos aos atributos não definidos para estas 
tuplas 
 Exemplo 
 Listar nome do empregado e o nome de seu 
dependente. O nome do empregado deve ser exibido 
mesmo que ele não possua dependente. 
78 
SQL 
• Formas de Junção em SQL Avançado (cont.) 
▫ FULL OUTER JOIN 
 Calcula o resultado da junção 
 Adiciona ao resultado da junção 
▫ Tuplas das relações envolvidas na junção que não satisfazem a 
condição de junção 
▫ Atribui valores nulos aos atributos não definidos para estas tuplas 
 Exemplo 
 Listar nome do empregado e o nome de seu departamento. O nome 
do empregado deve ser exibido mesmo que ele não possua 
departamento. Da mesma forma, o nome do departamento deve 
ser exibido mesmo que ele não tenha empregados. 
O MySQL não implementa o FULL OUTER JOIN. A alternativa é fazer 
a união (UNION) dos resultados do LEFT OUTER JOIN com os 
resultados do RIGHT OUTER JOIN. 
Especificando restrições como asserções 
• CREATE ASSERTION 
▫ Especifica tipos adicionais de restrições que estão 
fora do escopo das restrições embutidas do 
modelo relacional (chaves primária e única, 
integridade de entidade e integridade referencial). 
▫ Essas restrições podem ser especificadas dentro 
do comando CREATE TABLE. 
Especificando restrições como asserções 
• CREATE ASSERTION 
▫ Especifica uma consulta que seleciona as tuplas que 
violam a condição desejada. 
▫ Sempre que alguma tupla no BD fizer com que a 
condição de um comando ASSERTION seja avaliada 
como FALSE, a restrição é violada. 
▫ As cláusulas CHECK sobre atributos, domínios e 
tuplas individuais são verificadas somente quando as 
tuplas são inseridas ou atualizadas. 
 A verificação de restrição é mais eficiente nesses casos. 
 Usar CREATE ASSERTION somente nos casos em que 
não seja possível usar CHECK em atributos e domínios. 
O PostgreSQL ainda não implementa CREATE ASSERTION. 
Especificando restrições como asserções 
• 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. 
Especificando ações como triggers 
• Bancos de dados ativos oferecem funcionalidades para 
especificar regras ativas. 
• Essas regras podem ser disparadas automaticamente por 
eventos e podem iniciar certas ações especificadas na 
declaração da regra para que ocorram se certas condições 
forem atendidas. 
• Funcionalidades fornecidas pelos bancos de dados ativos 
estão disponíveis na forma de triggers (gatilhos), que faz 
parte da SQL-99 e de padrões mais recentes. 
• Triggers (gatilhos) são regras que especificam ações que 
são disparadas automaticamente por certos eventos. 
 
Especificando ações como triggers 
• Modelo usado para especificar regras de banco de dados ativo é 
conhecido como modelo Evento-Condição-Ação (ECA). 
• Uma regra no modelo ECA tem três componentes: 
1. O(s) evento(s) que dispara(m) a regra. 
 Esses eventos normalmente são operações de atualização do banco de dados que 
são aplicadas explicitamente ao banco de dados. 
2. A condição que determina se ação da regra deve ser executada. 
 Quando o evento que dispara a ação tiver ocorrido, uma condição opcional pode 
ser avaliada. 
 Se nenhuma condição for especificada, a ação será executada quando ocorrer o 
evento. 
 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 de um trigger.3. A ação a ser tomada. 
 A ação normalmente é uma sequência de comandos SQL, mas também poderia 
ser uma transação do banco de dados ou um programa externo que será 
executado automaticamente. 
Especificando ações como triggers 
• CREATE TRIGGER 
▫ Especifica ações automáticas que serão realizadas 
quando certos eventos e condições ocorrerem. 
▫ Comando usado para monitorar o banco de dados 
e disparar determinadas ações quando for 
satisfeita. 
▫ Uma trigger típica possui três componentes: 
 Evento(s) 
 Condição 
 Ação 
Especificando ações como triggers 
• A sintaxe geral resumida para criação de um 
trigger no sistema Oracle é a seguinte: 
 CREATE TRIGGER <nome trigger> 
 (AFTER | BEFORE) <evento trigger> ON <nome tabela> 
 [ FOR EACH ROW ] 
 [ WHEN <condição> ] 
 <ações da trigger>; 
Especificando ações como triggers 
• Sintaxe PostgreSQL: 
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } 
{ event [ OR ... ] } 
 ON table 
 [ FROM referenced_table_name ] 
 { NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | 
INITIALLY DEFERRED } } 
 [ FOR [ EACH ] { ROW | STATEMENT } ] 
 [ WHEN ( condition ) ] 
 EXECUTE PROCEDURE function_name ( arguments ) 
event pode ser: 
• INSERT 
• UPDATE [ OF column_name [, ... ] ] 
• DELETE 
• TRUNCATE (remove todas as linhas de uma ou mais tabelas) 
Especificando ações como triggers 
• No PostgreSQL é preciso inicialmente criar uma 
função que retorna uma trigger. 
▫ Essa função será executada pela trigger. 
▫ Exemplo 1: 
create or replace function remove_empr() returns trigger as 
$$ 
begin 
delete from empregado where superv =OLD.cpf; 
delete from departamento where gerente=OLD.cpf; 
delete from dependentes where emp =OLD.cpf; 
delete from trabalha_no where emp =OLD.cpf; 
return null; 
end; 
$$ language plpgsql; 
Especificando ações como triggers 
• Definindo a trigger propriamente dita no 
PostgreSQL: 
create trigger remove_empregado 
after delete on empregado 
for each row execute procedure remove_empr(); 
Especificando ações como triggers 
• Exemplo 2: 
create or replace function insere_depart() returns trigger as $$ 
begin 
if (select count(cpf) from empregado where cpf =NEW.gerente) = 0 
then 
 RAISE EXCEPTION 'Inserção do departamento com gerente % não pode 
ser realizada, pois não existe empregado com esse cpf.', 
NEW.gerente; 
end if; 
return NEW; 
end; 
$$ language plpgsql; 
create trigger insere_departamento 
before insert on departamento 
for each row execute procedure insere_depart(); 
Especificando ações como triggers 
• Exemplo 3: 
create or replace function atualiza_empr() returns trigger as $$ 
begin 
IF NEW.cpf != OLD.cpf then 
 update departamento set gerente=NEW.cpf where gerente=OLD.cpf; 
 update dependentes set emp =NEW.cpf where emp =OLD.cpf; 
 update trabalha_no set emp =NEW.cpf where emp =OLD.cpf; 
 update empregado set superv =NEW.cpf where superv = OLD.cpf; 
end if; 
return NEW; 
end; 
$$ language plpgsql; 
drop trigger if exists atualiza_empregado on empregado; 
create trigger atualiza_empregado 
after update on empregado 
for each row execute procedure atualiza_empr(); 
Especificando ações como triggers 
• CREATE TRIGGER 
▫ A cláusula BEFORE ou AFTER especifica que a regra será disparada 
antes ou depois, respectivamente, que ocorrerem os eventos que 
disparam a regra. 
▫ Os eventos básicos que podem ser definidos para disparar as regras 
são INSERT, DELETE e UPDATE. 
 No caso de UPDATE, podem-se especificar os atributos a serem 
atualizados. 
▫ A cláusula ON determina a relação em que a regra é especificada. 
▫ A cláusula FOR EACH ROW especifica que a regra será disparada uma 
vez para cada linha que é afetada pelo evento de disparo. 
 Isso é conhecido como trigger de nível de linha. 
▫ A cláusula FOR EACH STATEMENT indica o uso de uma trigger em 
nível de comando que dispara a regra apenas uma vez, mesmo que 
várias tuplas sejam afetadas pelo evento de disparo. 
Especificando ações como triggers 
• CREATE TRIGGER 
▫ A trigger pode especificar nomes de variável de tupla em 
particular para as tuplas antigas e novas. 
 Dentro da cláusula REFERENCING, nomeamos variáveis de 
tupla (apelidos) para nos referirmos à(s) tupla(s) OLD (antes 
da modificação) e à(s) tupla(s) NEW (após a modificação), 
respectivamente. 
▫ A cláusula WHEN é usada para especificar quaisquer 
condições que precisam ser verificadas após a regra ser 
disparada, mas antes que a ação seja executada. 
 A ação especificada após a condição pode ser um comando 
SQL ou uma sequência de comandos SQL delimitados por 
BEGIN e END. 
Especificando ações como triggers 
• Usando SQL99, atualizar o total salarial de um 
departamento a cada atualização de salário de 
um funcionário desse departamento: 
CREATE TRIGGER salario_total1 
 AFTER UPDATE OF salario ON FUNCIONARIO 
 REFERENCING OLD ROW AS O, NEW ROW AS N 
 FOR EACH ROW 
 WHEN (N.id_dept IS NOT NULL) 
 UPDATE DEPARTAMENTO 
 SET salario_total = salario_total + N.salario – O.salario 
 WHERE id_dept = N.id_dept; 
 
Especificando ações como triggers 
Comandos de controle de transação 
• SQL também possui comandos de controle de transação. 
• Estes são usados para especificar unidades de 
processamento de banco de dados para fins de controle de 
concorrência e recuperação. 
• Comandos: 
▫ BEGIN 
 Inicia um bloco de transação. 
▫ COMMIT 
 Confirma a transação corrente. 
▫ ROLLBACK 
 Aborta e desfaz as operações realizadas na transação corrente. 
SQL – Visões 
• Acesso a um banco de dados 
▫ Requer conhecimento do esquema 
 Indesejável 
 Para usuários inexperientes 
 Desenvolvedores de aplicativos que acessam o BD 
▫ Por questões de segurança e privacidade 
 Grupos de usuários devem ter acesso a dados de interesse 
 O acesso a todo o banco de dados é perigoso 
• Janelas sobre o banco de dados 
▫ Cada janela mostra parte do banco de dados 
 Diferentes visões 
▫ Visões (views) 
 Definidas sobre tabelas do banco de dados 
 Tabelas base 
SQL – Criando uma visão 
create or replace view func_dep(func,depart) as ( 
 select pnome || ' ' || minicial || ' ' || unome, 
dnome 
 from funcionario f, departamento d 
 where dnumero = dnr 
); 
create or replace view func_dep as ( 
 select pnome || ' ' || minicial || ' ' || unome 
as func, dnome as depart 
 from funcionario f, departamento d 
 where dnumero = dnr 
); 
SQL – Consultando uma visão 
select funcionario 
from func_dep 
where funcionario like 'J%' 
order by funcionario; 
Visões (tabelas virtuais) em SQL 
• Conceito de uma visão em SQL: 
▫ Tabela única geralmente derivada de outras tabelas. 
▫ Considerada como uma tabela virtual. 
• Usos 
▫ Simplifica a especificação de certas consultas. 
▫ Mecanismo de segurança e autorização. 
• Comando CREATE VIEW 
▫ Define um nome de tabela, uma lista de nomes de 
atributos e uma consulta para especificar o conteúdo da 
visão. 
▫ A visão está sempre atualizada. 
▫ O comando DROP VIEW elimina uma visão. 
100 
SQL – Visões 
• Definição de visões em SQL 
 CREATE VIEW nome_da_visão 
 [(nome_coluna {, nome_coluna …})] 
 AS 
 subquery [WITH CHECK OPTION] 
 
 WITH CHECK OPTION 
 Especifica que atualizações (INSERT ou UPDATE) 
na tabela base só serão permitidas se 
▫ resultam em tuplas visíveis para a visão 
Visões (tabelas virtuais) em SQL 
Visões (tabelas virtuais) em SQL 
Implementação e atualização de view 
• Técnicas principais 
1. Modificação de consulta 
 Modifica a consulta da view por uma consulta nas 
tabelas base. 
 Desvantagem: Ineficiente paravisões definidas por 
consultas complexas. 
Implementação e atualização de view 
• Técnicas principais 
2. Materialização de view 
 Cria fisicamente uma tabela de view temporária quando a 
view for consultada pela primeira vez. 
 Mantém essa tabela na suposição de que outras consultas 
à view acontecerão em seguida. 
 Requer uma estratégia eficiente para atualizar 
automaticamente a tabela de visão quando as tabelas base 
são atualizadas. 
 Estratégias de atualização incremental 
▫ O SGBD determina que tuplas novas devem ser inseridas, 
removidas ou modificadas em uma tabela de visão 
materializada. 
Implementação e atualização de view 
• Atualização de visão é complicada e pode ser 
ambígua. 
• Atualização em uma visão definida sobre uma única 
tabela sem funções agregadas. 
▫ Pode ser mapeada para uma atualização na tabela 
base. 
• Visões envolvendo junções 
▫ Frequentemente não é possível o SGBD determinar 
qual das atualizações é pretendida. 
• Cláusula WITH CHECK OPTION 
▫ Precisa ser acrescentada ao final da definição da visão, 
se uma visão tiver de ser atualizada. 
 
Atualização de visão 
Duas 
atualizações 
possíveis: 
SQL – Visões 
• Tipos de visões 
▫ Visão virtual 
 A definição da visão é armazenada 
 Dados da visão não são persistentes 
 Sempre que referenciada 
 Os dados são materializados 
▫ Custo praticamente igual a cada materialização 
 Quanto ao acesso 
 Somente leitura 
▫ Visões que só permitem acesso de leitura 
 Permitem atualização 
▫ Visões que permitem atualizações nas tabelas base 
108 
SQL – Visões 
• Tipos de visões (cont.) 
▫ Visão materializada 
 Dados e definição são persistentes 
 Problema de atualização dos dados da visão 
 Sempre que há uma atualização nas tabelas base da 
visão 
 Recalculada 
 Atualizada 
▫ Com intervenção humana 
▫ Automática 
 Reduz custos de materialização de resultado 
 Visões somente para leitura 
 Aplicações 
 Implementação Data Warehouse 
 Integração de fontes de dados heterogêneas 
SQL – Visões 
• Exemplos 
▫ Definindo visões 
create view V1 (nome_departamento, nome_empregado) 
as select d.nome,e.nome 
 from Departamento d inner join Empregado e 
 on d.id=e.id_dept 
 
create view V2 (nome_empregado, número_de_dependentes) 
as select e.nome, (select count(*) from Dependente 
where matr_resp=e.matr) 
 from Empregado e 
 
create view V3 (matrícula, salário) 
as select nome,salário 
 from Empregado 
 where salário<700 with check option 
SQL – Visões 
• Exemplos 
▫ Acessando o banco de dados através de visões 
select * from V1 
select * from V2 where número_de_dependentes>2 
select * from V3 
matrícula salário 
----------------------------------- ------------------------ 
caio 500.0 
rebeca 500.0 
 
▫ Atualizando o banco de dados através de visões 
update v3 set salário=salário+100 
select * from V3 
matrícula salário 
----------------------------------- ------------------------ 
caio 600.0 
rebeca 600.0 
update v3 set salário=salário+150 Erro, pois as tuplas a serem 
alteradas vão deixar de ser 
visíveis para V3 
Instruções de alteração de esquema 
• Comandos de evolução de esquema 
▫ Podem ser executados enquanto o banco de dados 
está em operação. 
• Não requerem recompilação do esquema. 
Comando DROP 
• Usado para remover elementos nomeados do 
esquema, como tabelas, domínios ou restrições. 
• Também é possível remover um esquema. 
• Opções de comportament de DROP: 
▫ CASCADE  capaz de remover o esquema e todas 
as suas tabelas, domínios e outros elementos. 
▫ RESTRICT  o esquema é removido somente se 
ele não tiver elementos. 
 O usuário deve remover individualmente cada 
elemento no esquema, para depois remover o 
próprio esquema. 
Comando DROP 
Comando ALTER 
• Permite a alteração da definição de uma tabela 
da base ou de outros elementos de esquema 
nomeados. 
• Ações de alteração de tabela incluem: 
▫ Adicionar ou remover uma coluna (atributo). 
▫ Alterar uma definição de coluna. 
▫ Adicionar ou remover restrições de tabela. 
Comando ALTER 
• Exemplos: 
Resumo dos comandos SQL 
Resumo dos comandos SQL 
Resumo dos comandos SQL 
OBS: Os comandos para criar e excluir índices não fazem parte 
do padrão SQL. 
Referências 
• Elsmari, R., Navathe, Shamkant B. “Sistemas 
de Banco de Dados”. 6ª Edição, Pearson 
Brasil, 2011.  Capítulo 5 
• Silberschatz, A., Korth, H., Sudarshan, S. 
“Sistema de Banco de Dados”. 5ª Edição, 
Editora Campus, 2006. 
• Slides Prof. José Maria (UFC). 
regispires@lia.ufc.br