2017919_16458_Apostila+Alunos+BD1+P2

Prévia do material em texto

BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 1
35.327 - BANCO DE DADOS I (Apostila 2)
Prof. Paulo Ricardo Rodegheri
Ciência da Computação
Universidade Regional Integrada do Alto Uruguai e das 
Missões - URI - Erechim
♦Linguagens de Consulta
♦Álgebra Relacional
♦Operações Fundamentais
♦Operadores Sobre Conjuntos
♦União, Intersecção, Diferença, Produto 
Cartesiano
♦Operadores Específicos
♦Seleção, Projeção, Junção, Divisão
♦SQL
♦Comandos da DDL
♦Create, Alter, Drop
♦Tipos de Dados em SQL
♦Primary Key, Foreign Key, Not null
♦Comandos da DML
♦ Select, Update, Delete, Union
♦Sub-selects
♦Funções (Avg, Max, Min, Sum, Count)
♦Clausula Distinct e Order By
♦Uso de Agregados
♦Clausula Group By e Having
♦Criação e Uso de Visões
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 2
LINGUAGENS DE CONSULTA
Uma linguagem de consulta é aquela, na qual usuários 
requisitam informações ao BD. Essas linguagens são tipicamente de 
mais alto nível do que as linguagens de programação comuns. Podem 
ser classificadas como:
♦ PROCEDURAIS: O usuário instrui o sistema a executar uma 
seqüência de operações no BD a fim de obter um resultado 
desejado.
♦ NÃO PROCEDURAIS: O usuário descreve a informação 
desejada sem fornecer um procedimento específico para 
obter tal informação.
ÁLGEBRA RELACIONAL
A álgebra relacional é uma linguagem de consulta procedural. Ela 
consiste num conjunto de operações que usam uma ou duas 
relações (tabelas) como entrada e produzem uma nova relação 
como resultado, isto é, o conjunto de objetos são as tabelas. Uma 
operação possui como operando e como resultado tabelas.
As operações fundamentais da álgebra relacional são: 
Selecionar, Projetar, Produto cartesiano, Renomear, União e 
Diferença de conjuntos.
Além das operações fundamentais, existem diversas outras 
operações: intersecção, junção, divisão e atribuição.
OPERAÇÕES FUNDAMENTAIS
As operações Selecionar, Projetar e Renomear são chamadas 
operações unárias, uma vez que operam sobre uma única relação. As 
outras três operações, produto cartesiano, união e diferença, 
operam com os pares de relação, e são chamadas de operações 
binárias.
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 3
Os operadores podem ser divididos em duas categorias:
♦ OPERADORES SOBRE CONJUNTOS:
♦ UNIÃO
♦ INTERSECÇÃO
♦ DIFERENÇA
♦ PRODUTO CARTESIANO
♦ OPERADORES ESPECÍFICOS:
♦ SELEÇÃO
♦ PROJEÇÃO
♦ JUNÇÃO
♦ DIVISÃO
S E L E Ç Ã O
A operação selecionar seleciona tuplas que satisfazem uma 
determinada condição. O resultado é uma tabela que contém as linhas 
que obedecem a um determinado critério. Usa-se a letra minúscula 
grega sigma (σ) para representar a seleção. O predicado aparece 
como subscrito de ó. A relação argumento é dada entre parênteses.
Sintaxe: σ<critério de seleção> (<tabela>)
Onde: <tabela> é o nome de uma tabela ou uma expressão de 
álgebra relacional que resulta em uma tabela e <critério de seleção> é 
uma expressão booleana que envolve literais e valores de atributos da 
tabela.
 
Ex.: σcep-cliente=99700000 (clientes) 
σ (status-fornec > 5 and cidade-fornec= "porto alegre") (fornec) 
O resultado da seleção tem colunas com os mesmos nomes e 
domínios da tabela de entrada.
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 4
P R O J E Ç Ã O
A projeção tem como operando uma tabela. O resultado é uma 
tabela que contém apenas as colunas selecionadas. A operação 
projetar é uma relação unária. A projeção é representada pela letra 
grega pi (Π). Listamos os atributos que desejamos que apareçam no 
resultado como subscrito de Π. O argumento de relação segue entre 
parênteses. 
Sintaxe: Π<lista de colunas> (<tabela>)
Ex.: Π nome-cliente, cep-cliente (clientes) 
Π nome-cliente (σ cep-cliente=99700000 (cliente))
A projeção pode resultar também na eliminação de linhas, caso 
colunas que são parte da chave forem eliminadas. Uma tabela é um 
conjunto de linhas. Portanto, se uma coluna cujos valores distinguem 
diferentes linhas é eliminada, surgem linhas duplicadas na tabela, que 
devem ser eliminadas. Ex:
 Π cidade-fornec (fornec) 
Resulta em uma tabela que contém todas as cidades em que há 
fornecedores. Note-se que se houverem múltiplos fornecedores na 
mesma cidade, as diferentes linhas são eliminadas.
R E N A M E (RENOMEAR)
Alternativamente, podemos mostrar explicitamente a sequência 
de operações, dando um nome para cada relação. 
Ex.: Temp1 Π nome-cliente (σ cep-cliente=99700000 (cliente))
 Temp2 Π nome-cliente (σ cep-cliente=99100150 (cliente))
 Temp3 (NOMEDOCLIENTE) Temp1 ∪ Temp2
 
 
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 5
OPERAÇÕES DA TEORIA DOS CONJUNTOS
A álgebra relacional empresta da teoria dos conjuntos quatro 
operadores: União, Intersecção, Diferença e Produto Cartesiano.
Sintaxe da operação União: 
<tabela>1 ∪ <tabela>2 
Sintaxe da operação Intersecção:
<tabela>1 ∩ <tabela>2 
Sintaxe da operação Diferença:
<tabela>1 - <tabela>2 
Nos três casos, a operação possui duas tabelas como operando. 
As tabelas devem ser compatíveis para união, isto é, devem possuir 
o mesmo número de colunas e o domínio da i-ésima coluna de uma 
tabela deve ser idêntico ao domínio da i-ésima coluna da outra. 
Quando os nomes das colunas forem diferentes, adota-se a 
convenção de usar os nomes das colunas da primeira tabela.
Ex:
Temp1 Π cod-fornec (Embarq)
Temp2 Π cod-fornec (σ status-fornec > 5 (fornec) )
R Temp1 ∩ Temp2
Obtém os códigos de todos os fornecedores que tem embarques 
e que tem status > 5.
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 6
P R O D U T O C A R T E S I A N O
O produto cartesiano possui como operandos duas tabelas 
(operação binária). O resultado é uma tabela cujas linhas são a 
combinação das linhas das tabelas <tab>1 e <tab>2, tomando-se uma 
linha da <tab>1 e concatenando-a com uma linha da <tab>2. 
Portanto, o total de colunas do produto cartesiano é igual ao 
número de colunas da primeira tabela mais o número de colunas 
da segunda tabela e o número de linhas é igual ao número de 
linhas da primeira tabela multiplicado pelo número de linhas da 
segunda tabela.
Ex. : embarq  peca
A operação produto cartesiano não é usada isoladamente. 
Normalmente, ela é combinada com uma seleção que envolve as 
diversas tabelas multiplicadas.Ex: Produto cartesiano com Seleção
 Π nomepeca 
 embarq.codpeca=peca.codpeca (embarq  peca)
Obtém os nomes de todas as peças para as quais há embarque.
J U N Ç Ã O
A combinação de uma operação de seleção aplicada sobre uma 
operação de produto cartesiano é comum em aplicações de BD. É 
através dela que os dados de tabelas relacionadas são associados. A 
operação de junção corresponde exatamente á seqüência de 
operações descrita acima.
Sintaxe: <tabela>1 || <critério> <tabela>2
Onde <tabela> é o nome de uma tabela ou uma expressão que 
resulta em uma tabela e <critério> é uma expressão booleana 
envolvendo literais e valores de atributos das duas tabelas. A junção 
tem como operandos duas tabelas. O resultado é equivalente a 
executar:
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 7
σ <critério> (<tabela>1  <tabela>2)
Ex: embarq || (embarq.codforn = fornec.codforn) fornec
Associa cada linha de embarquecom a correspondente linha de 
fornecedor.
JUNÇÃO THETA
O critério de junção pode envolver qualquer expressão booleana, 
inclusive comparações do tipo <, >, ≠,... entre os valores de atributos 
das tabelas envolvidas na junção. Essa operação genérica de junção é 
chamada Junção theta.
EQUIJUNÇÃO
Na maior parte dos casos, o <critério> de junção é uma 
expressão como mostrada no exemplo, que envolve apenas igualdade 
de valores de atributos de diferentes tabelas. Este tipo de junção é 
chamada equijunção e é representada pelo símbolo *.
Sintaxe:
<tabela>1 * (<lista>1) , (<lista>2) <tabela>2
onde <lista>1 e <lista>2 são listas dos nomes das colunas das 
tabelas 1 e 2 respectivamente, cujos valores são comparados um a 
um para fazer a junção.
A operação de Equijunção distingue-se da junção theta pelo fato 
de eliminar a segunda coluna em cada um dos pares que são 
comparados (já que os valores da segunda coluna são idênticos aos 
primeiros).
Ex. : embarq * (codfornec), (codfornec) fornec
Associa cada linha de embarque com a correspondente linha de 
fornecedor.
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 8
JUNÇÃO NATURAL
Nos casos em que as colunas de junção possuem o mesmo 
nome, não é necessária a lista de nomes de colunas.
Sintaxe: <tabela>1 * <tabela>2
Ex: embarq * fornec
Associa cada linha de embarque com a correspondente linha de 
fornecedor.
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 9
D I V I S Ã O
Como a junção, a divisão é uma operação de álgebra relacional 
que pode ser construída a partir de outras, e é útil em muitas situações 
que aparecem freqüentemente.
Sintaxe: <tabela>1 ÷ <tabela>2
A operação de divisão tem duas tabelas como operandos. Os 
nomes das colunas e respectivos domínios da <tabela>2 (C2) devem 
estar contidos dentro dos nomes das colunas e respectivos domínios 
da <tabela>1 (C1). A tabela resultante tem como nomes de colunas e 
domínios aqueles que aparecem na <tabela>1, mas não aparecem na 
<tabela>2 (C1 - C2). Para que uma linha apareça no resultado, é 
necessário que sua concatenação com cada linha da <tabela>2 
apareça também na <tabela>1.
Ex. : (Πcodfornec, codpeca (embarq)) ÷
(Πcodpeca 
(σ cidadepeca='Poa' or cidadepeca = 'Rio' (peca)))
A consulta obtém os códigos dos fornecedores que possuem 
embarques para todas peças de 'Poa' ou 'Rio' (a palavra todos muitas 
vezes está associada a operação de divisão).
CONJUNTO MÍNIMO DE OPERAÇÕES
Muitas operações podem ser derivadas de outras. Foi 
identificado um conjunto mínimo (completo) de operações, das quais 
todas as demais podem ser derivadas:
♦ Seleção
♦ Projeção
♦ União
♦ Diferença
♦ Produto Cartesiano
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 10
PODER DE EXPRESSÃO DA ÁLGEBRA RELACIONAL
O poder de expressão (conjunto de consultas que se pode 
escrever) da álgebra relacional é limitado. Há consultas que não 
podem ser expressas em álgebra:
♦ Consultas que envolvem funções de agregação, como 
contagem de linhas que atendem a um critério ou a soma de 
valores de uma coluna.
♦ Consultas recursivas, isto é, consultas que envolvem obter 
um valor de atributo em uma linha, usando este valor buscar 
outra linha na mesma tabela e aí obter novo valor, o qual é 
usado para buscar outra linha e assim recursivamente 
(exemplo: em uma tabela com códigos de empregados e com 
os códigos de seus gerentes, obter para um determinado 
empregado todos os seus subordinados a todos os níveis 
hierárquicos).
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 11
JUNÇÃO EXTERNA (OUTER-JOIN)
A operação de junção concatena duas linhas das tabelas que 
estão sendo juntadas com base no critério de junção (normalmente 
por igualdade de valores de atributos). 
Uma linha que não possua nenhuma linha na outra tabela associada 
pelo critério de junção não aparece na tabela resultado.
Ex: Obter os dados de todos os empregados junto com o nome de 
seu departamento, caso o empregado seja gerente do 
departamento.
Esta consulta não pode ser resolvida com a junção, já que do 
resultado participariam apenas as linhas de empregados que são 
gerentes e não dos demais.
Para este caso, usa-se a junção externa (outer-join).
empregado  (codemp = codempger) departamento
O operador  é chamado de Junção externa esquerda 
(left outer-join)
 
A junção externa esquerda contém ao menos uma vez cada 
linha da tabela á esquerda do operador (empregado). Esta linha 
aparece concatenada com uma linha com brancos, caso o critério de 
junção não se verifique para nenhuma linha da tabela á direita do 
operador de junção. Caso o critério de junção seja verdadeiro para 
uma ou mais linhas da tabela á direita, a linha da tabela á esquerda 
aparecerá concatenada com uma ou mais linhas da tabela á direita.
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 12
S Q L
SQL (Structured Query Language) é uma linguagem comercial 
de definição e manipulação de BDs Relacionais.
SQL (inicialmente chamava-se SEQUEL) surgiu no centro de 
pesquisa de San Jose da IBM, dentro do projeto System R.
Hoje SQL é padrão de fato (principais BDs tem suporte para 
SQL) e de direito, através de versões padronizadas (SQL/1 - 1996, 
modificações em 1989, SQL/2 - 1992, SQL/3 – 1999 – 2003). 
COMPONENTES DE SQL
SQL é uma linguagem completa de manipulação de BD. 
Oferece as seguintes funcionalidades:
♦ Uma DDL para definição do esquema da base de dados
♦ Uma DML para programação de consultas e transações que 
inserem, removem e alteram linhas de tabelas.
♦ Uma versão de SQL embutida em linguagens de programação 
de 3ª geração (COBOL, C, etc.) estendendo-as para a manipulação 
de BD.
♦ Instruções para a definição de visões (tabelas virtuais).
♦ Instruções para controle de autorização de acesso.
♦ Instruções para controle de transações e concorrência.
♦ Instruções para especificação de restrições de integridade.
ADERÊNCIA A PADRÕES
A aderência a padrões de SQL é importante apenas para 
aqueles que desenvolvem toda a aplicação em SQL (normalmente 
embutido, pois SQL não é suficiente para desenvolver aplicações).
O padrão é irrelevante para aqueles que usam ferramentas como 
geradores de telas, relatórios, etc., pois estes são proprietários - os 
usuários estão presos ao fornecedor da ferramenta.
Praticamente todo o fornecedor de SGBD afirma que seu SQL é 
compatível com o padrão. Esta compatibilidade só pode ser garantida 
se verificada por um órgão independente. Nos EUA há um órgão do 
governo (NIST) que faz a validação de aderência ao padrão SQL.
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 13
INSTRUÇÕES DA DDL
SQL oferece três instruções para definição do esquema da base 
de dados:
Create Table: define a estrutura de uma tabela, suas restrições 
de integridade e a criação da tabela vazia.
Drop Table: elimina a tabela da base de dados.
Alter Table: permite modificar (adicionar, excluir, modificar) a 
estrutura de uma tabela existente. 
Incluir Coluna: 
alter table clientes
add ender_cliente varchar (35)
Alterar nome de uma Coluna:
 alter table clientes
alter ender_cliente to endercli
Alterar tipo/tamanho de uma Coluna:
 alter table clientes
alter endercli type char(50)
Excluir uma Coluna:
 alter table clientes
drop endercli
Criar uma chave primária:
 alter table clientes
add primary key (codcli) 
Criar uma chave estrangeira:
 alter table clientes
add foreign key (codcidade) references cidades 
(codcid)Alterar o nome de uma tabela:
 alter table clientes rename tabclientes
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 14
CRIAÇÃO DE TABELAS
create table peca
( codpeca integer (4) not null,
nomepeca varchar (50) not null,
corpeca char (10),
pesopeca numeric (7,2) ,
cidadepeca varchar(40),
primary key (codpeca))
create table fornec
( codfornec integer (4) not null,
nomefornec varchar (50) not null,
statusfornec integer (1),
cidadefornec varchar(40),
primary key (codfornec))
create table embarq
(codpeca integer (4) not null,
 codfornec integer (4) not null,
 qtdeembarq integer (5) not null,
 primary key (codpeca, codfornec),
 foreign key (codpeca) references peca,
 foreign key (codfornec) references fornec)
PECA EMBARQ FORNEC
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 15
OBSERVAÇÕES:
♦ Em SQL o conjunto de domínios de valores de atributos é fixo. 
Seria desejável que o domínio fosse definível pelo usuário (ex. dias 
da semana, meses do ano, etc).
♦ Nos SGBDs comerciais são oferecidos domínios adicionais aos 
do padrão (CHAR, VARCHAR, INTEGER, REAL, ...) destinados a 
aplicações especiais como DATE e domínios para armazenar 
campos longos (BLOB (Binary Large Object), CLOB (Caractere 
Large Object)), destinados a conter imagens, sons, vídeos, etc.
♦ A clausula NOT NULL especifica que uma coluna não admite o 
valor vazio (requerido para colunas que sejam chave primária).
♦ As colunas de uma tabela são classificadas na ordem de sua 
definição (linhas não têm classificação).
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 16
ELIMINAÇÃO E ALTERAÇÃO DE TABELAS
Para eliminar completamente uma tabela (vazia ou não) da base 
de dados, é usada a instrução:
DROP TABLE embarq
Para modificar a estrutura de tabelas já existentes na base de 
dados, há uma instrução que permite adicionar colunas a tabelas:
ALTER TABLE embarq ADD dataembarq date
Observe-se que:
 A instrução adiciona uma nova coluna com o valor vazio para todas 
as linhas.
 Os valores para as linhas devem ser adicionados através de 
instruções da DML.
 Não pode ser especificada a clausula NOT NULL já que a coluna é 
criada com o valor vazio.
 Para fazer outros tipos de alterações da definição de tabelas:
 Armazenar o conteúdo da tabela em tabela temporária ou 
arquivo do SO.
 Eliminar a tabela antiga (DROP TABLE)
 Definir a nova tabela (CREATE TABLE)
 Carregar a nova tabela a partir da tabela intermediária ou 
arquivo do SO criado anteriormente.
MODELO BÁSICO DA INSTRUÇÃO DE CONSULTA
A sintaxe básica de execução da instrução SQL é:
SELECT <lista de colunas>
FROM <lista de tabelas>
WHERE <critério>
O modelo básico de execução da instrução SQL é:
 É feito o produto cartesiano das tabelas envolvidas
 São selecionadas as linhas da tabela que obedecem ao critério
 É feita a projeção sobre as colunas que vão ao resultado.
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 17
Exemplos de Instruções SQL
 Obter todos os dados da tabela peças
SELECT codpeca, nomepeca, corpeca, cidadepeca
FROM peca
Ou
SELECT *
FROM peca
 Obter os nomes de todas as peças
SELECT nomepeca
FROM peca
Neste caso, se houver duas peças com o mesmo nome, haverá 
duas linhas idênticas no resultado (contrariando os princípios de 
SGBDs Relacional).
SQL não elimina duplicata "by default" - eliminação de duplicatas 
é uma operação cara em termos de performance (normalmente 
envolve "Sort")
SELECT DISTINCT nomepeca
FROM peca
Clausula DISTINCT especifica a eliminação de duplicatas 
(equivalente a projeção da álgebra relacional)
 Obter os códigos e nomes de fornecedores com status > 5 e 
que sejam do Rio
SELECT codfornec, nomefornec
FROM fornec
WHERE statusfornec > 5 AND 
 cidadefornec = 'rio'
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 18
 Para cada Embarque com quantidade > 300 obter o código do 
fornecedor, o código da peça e o nome da peça.
SELECT e.codfornec, e.codpeca, p.nomepeca
FROM embarq e, peca p
WHERE e.qtdeembarq > 300 AND
 ecodpeca = p.codpeca
UTILIZANDO INNER JOIN:
SELECT e.codfornec, e.codpeca, p.nomepeca
FROM embarq e
INNER JOIN peca p on e.codpeca = p.codpeca
 WHERE E e.qtdeembarq > 300 
A consulta envolve duas tabelas: a clausula WHERE especifica 
os atributos de junção das duas tabelas.
Corresponde a seqüência Seleção-Junção-Projeção comum na 
álgebra relacional.
Observar a qualificação da coluna codpeca. Sempre que um 
nome de coluna for ambíguo (aparecer em duas tabelas) ele deve ser 
qualificado. Apenas nomes ambíguos necessitam de qualificação.
 Obter os nomes de fornecedores das peças de cor vermelha
SELECT nomefornec
FROM fornec, embarq, peca
WHERE corpeca = 'verm' AND
 embarq.codfornec = fornec.codfornec
Em SQL não há uma operação como a junção natural - o 
usuário é obrigado a especificar os atributos de junção.
 Obter todos os pares de nomes de fornecedores que 
encontram-se na mesma cidade
SELECT f1.nomefornec, f2.nomefornec
FROM fornec f1, fornec f2
WHERE f1.cidadefornec = f2.cidadefornec AND 
f1.Codfornec < f2.codfornec
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 19
Neste caso, é necessário renomear a tabela de fornecedores, já 
que a consulta faz referência a duas linhas da tabela. Isto é feita 
através de "aliases" definidos na clausula FROM.
Aliáses tem função análoga a das variáveis de tupla de cálculo 
relacional.
A comparação de códigos de fornecedores serve apenas para 
eliminar duas linhas do mesmo fornecedor e evitar que o mesmo par 
de fornecedores <fx, fy> apareça no resultado também em ordem 
inversa <fy, fx>.
 Obter os códigos das peças que sejam de 'poa' OU para os 
quais haja embarques do fornecedor de código 'F99'
( SELECT codpeca
FROM peca
WHERE cidadepeca = 'poa')
 UNION
( SELECT codpeca
FROM embarq
WHERE codfornec = 'F99')
O operador de UNIÃO é equivalente ao da álgebra relacional. A 
união elimina duplicatas.
O operador UNION ALL não elimina duplicatas.
Em alguns SGBDs também são implementadas intersecção e 
diferença, mas o padrão define apenas união.
 Obter os códigos dos fornecedores que tem embarques de 
peças de cor vermelha
SELECT codfornec
FROM embarq, peca
WHERE corpeca = 'verm' AND
embarq.codpeca = peca.codpeca
No caso o resultado da consulta envolve apenas colunas da 
tabela peça, mas a clausula FROM referencia também a tabela 
embarq.
Para este tipo de consulta a solução mais natural é através de 
consultas aninhadas (embutidas). Ex.:
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 20
SELECT codfornec
FROM embarq
WHERE codpeca IN
( SELECT codpeca
FROM peca
WHERE corpeca = 'verm')
 Obter os nomes dos fornecedores de peças de cor vermelha
SELECT nomefornec
FROM fornec, embarq, peca
WHERE corpeca = 'verm' AND
embarq.codpeca = peca.codpeca AND
embarq.codfornec = fornec.codfornec
Pode ser resolvida com consultas aninhadas:
 SELECT nomefornec
FROM fornec
WHERE corfornec IN
( Select codfornec
FROM embarq
WHERE corpeca IN
( Select codpeca
FROM peca
WHERE corpeca = 'verm'))
UTILIZANDO INNER JOIN:
SELECT f.nomefornec
FROM fornec f
INNER JOIN embarq e on f.codfornec = e.codfornec
 INNER JOIN peca p on e.codpeca = e.codpeca
WHERE p.corpeca = 'verm' 
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 21
Consultas podem ser aninhadas em múltiplos níveis:
 Obter os nomes dos fornecedores que tem embarques da peça 
de código'P1'
Solução com Produto Cartesiano:
SELECT nomefornec
FROM fornec, embarq
WHERE codpeca = ‘P1’ AND
embarq.codfornec = fornec.codfornec
Solução com Consultas Aninhadas :
SELECT nomefornec
FROM fornec
WHERE codfornec IN
( SELECT codfornec
FROM embarq
WHERE codpeca = 'P1')
OU
SELECT nomefornec
FROM fornec
WHERE ‘P1’ IN
( SELECT codpeca
FROM embarq
WHERE codfornec = fornec.codfornec)
A última solução pode ser expressa como "obter os nomes dos 
fornecedores para os quais P1 é o código de uma das peças por eles 
fornecidas".
Idealmente um otimizador deveria estar em condições de 
executar qualquer das três alternativas com a mesma performance. O 
programador não deveria ser obrigado a conhecer a solução.
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 22
 Obter os nomes dos fornecedores para os quais há embarques 
de peças localizadas na mesma cidade do fornecedor
SELECT nomefornec
FROM fornec
WHERE codfornec, cidadefornec IN
( SELECT embarq.codfornec, peca.cidadepeca
FROM embarq, peca
WHERE embar.codpeca = peca.codpeca AND 
 embarq.codfornec = fornec.codfornec )
Nesta consulta testa-se se uma linha (indicada por Codfornec, 
Cidadefornec faz parte de uma tabela obtida através de uma consulta 
aninhada).
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 23
 Obter os nomes dos fornecedores para os quais há ao menos 
um embarque
SELECT nomefornec
FROM fornec
WHERE EXISTS
( SELECT *
FROM embarq
WHERE codfornec = fornec.codfornec)
A clausula EXISTS tem função análoga ao quantificador 
existencial do cálculo relacional.
 Obter os nomes dos fornecedores para os quais não há 
embarques
SELECT nomefornec
FROM fornec
WHERE NOT EXISTS
( SELECT *
FROM embarq
WHERE codfornec = fornec.codfornec)
 Obter os códigos dos fornecedores que possuem embarques 
para todas peças de cor vermelha
Essa operação seria resolvida usando o operador de divisão da 
álgebra relacional ou o quantificador universal do cálculo relacional.
Em SQL, não há clausula análoga ao quantificador existencial. É 
necessário usar a negação do quantificador existencial.
O enunciado da consulta pode ser rescrito para:
 Obter os códigos dos fornecedores tal que não exista uma 
peça vermelha para a qual não exista um embarque do 
fornecedor em questão
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 24
SELECT codfornec
FROM fornec
WHERE NOT EXISTS
( SELECT *
FROM peca
WHERE corpeca = ‘verm’ AND
 NOT EXISTS
( SELECT *
FROM embarq
WHERE codfornec = fornec.codfornec AND 
codpeca = peca.codpeca )
 Obter os nomes dos fornecedores para os quais o status foi 
informado (não está vazio) 
SELECT nomefornec
FROM fornec
WHERE statusfornec IS NOT NULL
Note-se que qualquer comparação (=, <, >, ...) com vazio resulta 
sempre em falso.
MODELO ESTENDIDO DE CONSULTA
SQL básico possui o mesmo poder de expressão de álgebra e 
cálculo relacional. Isto significa que SQL básico não oferece 
possibilidade de executar consultas recursivas nem consultas que 
envolvam a agregação de dados.
No modelo estendido, SQL possibilita a manipulação de 
agregados de dados.
A sintaxe de uma instrução de consulta no modelo estendido é:
SELECT <lista de colunas>
FROM <lista de tabelas>
[ WHERE <critério> ]
[GROUP BY <lista de colunas>]
[HAVING <critério>]
[ORDER BY <lista de colunas>]
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 25
O modelo mental de execução da instrução SQL é o seguinte:
• É feito o produto cartesiano das tabelas envolvidas
• São selecionadas as linhas da tabela que obedecem ao critério da 
clausula FROM
• São criados grupos de linhas que contenham valores idênticos nas 
colunas GROUP BY
• São selecionados os grupos que obedecem ao critério da clausula 
HAVING
• É feita a classificação do resultado pelos valores das colunas da 
clausula ORDER BY
• É feita a projeção sobre as colunas que vão ao resultado.
SQL - FUNÇÕES
SQL possui funções para computar valores:
• AVG : Média
• MIN : Mínimo
• MAX : Máximo
• TOT : Total
• COUNT : Contagem
As funções podem ser aplicadas sobre toda a tabela ou sobre 
grupos de linhas (definidos pela clausula GROUP BY).
FUNÇÕES SOBRE TODA A TABELA
 Obter o número de fornecedores na base de dados 
SELECT count (*)
FROM fornec
 Obter o número de cidades em que há fornecedores. 
SELECT count (distinct nomefornec)
FROM fornec
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 26
 Obter a quantidade máxima embarcada
SELECT MAX (qtdeembarq)
FROM embarq
SQL - USO DE AGRAGADOS
 Obter o número de embarques de cada fornecedor
SELECT codfornec, count (codpeca)
FROM embarq
GROUP BY codfornec
 Obter o número de quantidade > 300 de cada fornecedor
SELECT codfornec, count (codpeca)
FROM embarq
WHERE qtdeembarq > 300
GROUP BY codfornec
OBS: SQL padrão exige que se escreva COUNT (DISTINCT 
codpeca).
 Obter a quantidade total embarcada de peças de cor vermelha 
para cada fornecedor
SELECT codfornec, SUM (qtdeembarq)
FROM embarq
WHERE codpeca IN
( SELECT codpeca
FROM peca
WHERE corpeca = ‘verm’ )
GROUP BY codfornec
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 27
 Obter a quantidade total embarcada de cada peça. Exibir o 
resultado por ordem descendente de quantidade embarcada
SELECT codfornec, SUM (qtdeembarq)
FROM embarq
GROUP BY codfornec
ORDER BY SUM (qtdeembarq) DESC
OBS : Em algumas versões de SQL a referência a segunda coluna do 
resultado, que tem dados computados, deve ser feita pela posição da 
coluna, na forma :
ORDER BY 2 DESC
 Para as peças de cor vermelha, cuja quantidade total 
embarcada exceda 500, obter o número de seus embarques.
SELECT COUNT (codfornec)
FROM embarq
WHERE codpeca IN
( SELECT codpeca
FROM peca
WHERE corpeca = ‘verm’)
GROUP BY codfornec
HAVING SUM (qtdeembarq) > 500
Observar a diferença entre as clausulas WHERE e HAVING:
 A clausula WHERE seleciona linhas uma-a-uma - seu critério de 
seleção envolve valores de atributos da linha.
A clausula HAVING seleciona grupos (definidos por 
GROUP BY) e seu critério de seleção envolve todo grupo de linhas 
através de funções.
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 28
INSTRUÇÕES DE ATUALIZAÇÃO
SQL provê 3 instruções de atualização do conteúdo de tabelas 
previamente definidas e criadas pelo CREATE TABLE.
INSERT : Serve para inserir uma ou mais linhas em uma tabela.
DELETE : Serve para excluir uma ou mais linhas de uma tabela.
UPDATE : Serve para alterar os dados de uma ou mais linhas de uma 
tabela.
INSTRUÇÃO INSERT
♦ Incluir um novo fornecedor 
INSERT INTO fornec
VALUES ('F10', 'pedro', 12, 'poa')
O usuário deve lembrar a ordem em que as colunas foram definidas 
quando do CREATE TABLE.
INSERT INTO 
fornec (codfornec, nomefornec, cidadefornec, statusfornec)
VALUES ('F10', 'pedro', 'poa', 12)
Nesta variante o usuário não necessita conhecer a ordem 
original de definição das colunas. Além disso, atributos de valor vazio 
podem ser omitidos.
A criação da linha somente é efetivada se as restrições de 
integridade especificadas (valores não vazios, chaves primária e 
única, chave estrangeira) são obedecidas.
♦ Criar embarques com quantidade 100 para todas peças de 
cor vermelha, pelo fornecedor de código F5
INSERT INTO embarq
SELECT codpeca, 'F5', 100
FROM Peca
WHERE corpeca = ‘verm’ 
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 29
Nessa variante é possívelcriar múltiplas linhas (definidas por uma 
instrução normal de consulta) em uma tabela.
INSTRUÇÃO DELETE
♦ Excluir o fornecedor de código F5
DELETE FROM fornec
WHERE codfornec = ‘F5’
A sintaxe da clausula WHERE é a mesma da instrução de consulta. A 
exclusão somente é executada se nenhuma restrição de integridade 
(chave estrangeira) é violada - no caso significa que o fornecedor 
somente será excluído se não possuir embarques.
Alguns produtos permitem a definição de chaves estrangeiras que 
propagam a exclusão da linha que contém a chave primária para as 
linhas que contém chave estrangeira. No caso, uma exclusão de 
fornecedores implicaria na exclusão de todos os seus embarques (útil 
para relações pai-filho: empregado-dependente, nota fiscal - itens da 
nota fiscal, etc). 
♦ Excluir todos os embarques de peças vermelhas
DELETE FROM embarq
WHERE codpeca IN
( SELECT codpeca
FROM peca
WHERE corpeca = ‘verm’ )
♦ Excluir todos os embarques de pecas vermelhas
DELETE FROM embarq
INSTRUÇÃO UPDATE
♦ Modificar o status do fornecedor de código 'F1' para 20
UPDATE fornec
SET statusfornec = 20
WHERE codfornec = ‘F1’
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 30
♦ Incrementar o status do fornecedor de código 'F1' em 
10%
UPDATE fornec
SET statusfornec = statusfornec * 1,10
WHERE codfornec = ‘F1’
♦ Incrementar em 10% o status de todos os fornecedores 
que tem embarque do produto 'P2'
 
UPDATE Fornec
SET statusfornec = statusfornec * 1,10
WHERE codfornec IN
( SELECT codfornec
FROM embarq
WHERE codpeca = ‘P2’ )
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 31
VISÕES EM SQL
Uma visão em SQL é uma tabela virtual, isto é, uma tabela que não é 
armazenada fisicamente na base de dados.
O objetivo das visões é atender usuários que necessitam ver os dados 
de determinada forma, diferente da de armazenamento.
Visões são usadas em combinação com mecanismos de controle de 
acesso. 
Também são usadas para aumentar a independência de dados: 
♦ Quando a definição de tabelas básicas em uma base de 
dados é mudada, aplicações que a usam podem ser afetadas.
♦ Se a mudança nas tabelas básicas não afeta a visão, 
aplicações que usam a visão não são afetadas.
A instrução para CRIAR uma visão SQL é:
CREATE VIEW <nome> AS <consulta SQL>
A instrução para ELIMINAR uma visão SQL é:
DROP VIEW <nome>
EXEMPLOS DE VISÕES
♦ Criar uma visão da tabela de embarques, na qual, apareçam 
associados aos códigos de peça e fornecedor os seus dados.
CREATE VIEW embarqcomp1 AS 
 (SELECT embarq.codpeca, nomepeca, corpeca, pesopeca, 
cidadepeca, embarq.codfornec, nomefornec, statusfornec, 
cidadefornec, qtdeembarq
FROM embarq, peca, fornec
WHERE embarq.codpeca = peca.codpeca AND
embarq.codfornec = fornec.codfornec) 
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 32
♦ Obter o nome do fornecedor e nome da peça para cada 
embarque
SELECT nomefornec, nomepeca
FROM embarqcomp1
A instrução acessa a visão embarcomp1.
O usuário não necessita especificar toda definição da junção das 
três tabelas.
A visão não existe fisicamente. A instrução SELECT de definição 
da visão e misturada ao SELECT da consulta em tempo de tradução.
ATUALIZAÇÃO ATRAVÉS DE VISÕES
SQL permite que uma tabela da base de dados seja atualizada 
(insert, delete, update) através de uma visão. A única restrição é de 
que a atualização deve ocorrer sobre uma visão definida sobre uma 
única tabela da base de dados e que as linhas da visão estejam em 
relação um-para-um com as linhas da tabela da base de dados.
PRINCÍPIO: O mapeamento do efeito da atualização sobre uma visão 
em uma atualização sobre uma tabela da base de dados deve ser não 
ambíguo.
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 33
SQL EMBUTIDO
SQL não possui poder de expressão suficiente para construir 
aplicações completas. SQL é apenas uma linguagem de manipulação 
de bases de dados. Falta-lhe capacidade para tratar entradas e saídas 
do sistema (telas e relatórios), programação de procedimentos 
complexos, etc.
Na prática, SQL é usado junto com alguma linguagem ou 
ferramenta de geração de aplicações de terceira geração (COBOL, 
PASCAL, C, etc.).
Exemplo de SQL embutido:
Loop : = "Y"
While loop = "Y" DO
BEGIN
WRITELN ("Entre o código do fornecedor ");
READLN (Cod_forn);
EXEC SQL
 SELECT nomeforn INTO %nom_forn
 FROM fornec
 WHERE codfornec = %cod_forn;
END-EXEC
 WRITELN (nom_forn);
WRITELN ("Continuar a busca (Y ou N) ?");
READLN (loop);
END;
O trecho acima mostra como SQL pode ser combinado com uma 
linguagem de programação (Pascal).
As instruções SQL são precedidas do termo “EXEC SQL” e 
finalizadas com “END-EXEC”
 A referência a variáveis de Pascal dentro de instruções de SQL 
é precedida de um símbolo especial (%).
É usada uma forma especial de instrução de consulta dotada da 
clausula INTO.
BANCO DE DADOS I – URI Erechim - Prof. Paulo Ricardo Rodegheri 34
SQL DINÂMICO
• Permite que programas construam e submetam consultas SQL 
em tempo de execução. 
• Exemplo de uso de SQL Dinâmico dentro de um programa em C:
char *sqlprog= ‘update conta set saldo = saldo * 1.05
 where num_conta =?’
EXEC SQL prepare dinprog from sqlprog;
char conta[10]= ‘A-101’;
EXEC SQL execute dinprog using :conta;
O programa dinâmico contém uma “?”, que é colocada para manter 
um valor gerado quando o programa SQL é executado.