LINGUAGENS DE CONSULTA Uma linguagem de consulta é

BANCO DE DADOS I - URI - Prof. Paulo Ricardo Rodegheri
1
/,1*8$*(16'(&2168/7$
Uma OLQJXDJHP GH FRQVXOWD é 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:
♦
♦
352&('85$,6O usuário instrui o sistema a executar uma
seqüência de operações no BD a fim de obter um resultado
desejado.
1­2 352&('85$,6 O usuário descreve a informação
desejada sem fornecer um procedimento específico para
obter tal informação.
È/*(%5$5(/$&,21$/
A álgebra relacional é uma linguagem de consulta SURFHGXUDOEla
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:
6HOHFLRQDU 3URMHWDU 3URGXWR FDUWHVLDQR 5HQRPHDU 8QLmR H
'LIHUHQoDGHFRQMXQWRV
Além das operações fundamentais, existem diversas outras
operações: LQWHUVHFomRMXQomRGLYLVmRHDWULEXLomR
23(5$d®(6)81'$0(17$,6
As operações 6HOHFLRQDU3URMHWDUH5HQRPHDU são chamadas
operações XQiULDVuma vez que operam sobre uma única relação. As
outras três operações, SURGXWR FDUWHVLDQR, XQLmR e GLIHUHQoD,
operam com os pares de relação, e são chamadas de RSHUDo}HV
ELQiULDV
BANCO DE DADOS I - URI - Prof. Paulo Ricardo Rodegheri
2
Os operadores podem ser divididos em duas categorias:
♦
23(5$'25(662%5(&21-81726
♦ 81,­2
♦
♦
♦
♦
,17(56(&d­2
',)(5(1d$
352'872&$57(6,$12
23(5$'25(6(63(&Ë),&26
♦ 6(/(d­2
♦
♦
♦
352-(d­2
-81d­2
',9,6­2
6(/(d­2
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 - Prof. Paulo Ricardo Rodegheri
3
352-(d­2
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 XQiULD. 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.
23(5$d®(6'$7(25,$'26&21-81726
A álgebra relacional empresta da teoria dos conjuntos quatro
operadores: 8QLmR,QWHUVHFomR'LIHUHQoDH3URGXWR&DUWHVLDQR
Sintaxe da operação União:
<tabela>1 ∪ <tabela>2
Sintaxe da operação Intersecção:
<tabela>1 ∩ <tabela>2
BANCO DE DADOS I - URI - Prof. Paulo Ricardo Rodegheri
4
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 FRPSDWtYHLVSDUDXQLmRisto é, 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
Hque tem status > 5.
352'872&$57(6,$12
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.
3RUWDQWR R WRWDO GH FROXQDV GR SURGXWR FDUWHVLDQR p LJXDO DR
Q~PHURGHFROXQDVGDSULPHLUDWDEHODPDLVRQ~PHURGHFROXQDV
GD VHJXQGD WDEHOD H R Q~PHUR GH OLQKDV p LJXDO DR Q~PHUR GH
OLQKDV GD SULPHLUD WDEHOD PXOWLSOLFDGR SHOR Q~PHUR GH OLQKDV GD
VHJXQGDWDEHOD
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.
BANCO DE DADOS I - URI - Prof. Paulo Ricardo Rodegheri
5
-81d­2
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:
σ <critério> (<tabela>1 <tabela>2)
Ex: embarq || (embarq.codforn = fornec.codforn) fornec
Associa cada linha de embarque com a correspondente linha de
fornecedor.
-81d­27+(7$
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 -XQomRWKHWD
BANCO DE DADOS I - URI - Prof. Paulo Ricardo Rodegheri
6
(48,-81d­2
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 HTXLMXQomRe é 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.
-81d­21$785$/
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 - Prof. Paulo Ricardo Rodegheri
7
',9,6­2
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).
&21-81720Ë1,02'(23(5$d®(6
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 - Prof. Paulo Ricardo Rodegheri
8
32'(5'((;35(66­2'$È/*(%5$5(/$&,21$/
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 IXQo}HV GH DJUHJDomR como
contagem de linhas que atendem a um critério ou a soma de
valores de uma coluna.
Consultas UHFXUVLYDV, 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 - Prof. Paulo Ricardo Rodegheri
9
-81d­2(;7(51$287(5-2,1
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: 2EWHURVGDGRVGHWRGRVRVHPSUHJDGRVMXQWRFRPRQRPHGH
VHX GHSDUWDPHQWR FDVR R HPSUHJDGR VHMD JHUHQWH GR
GHSDUWDPHQWR
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 -XQomR H[WHUQD HVTXHUGD
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 - Prof. Paulo Ricardo Rodegheri
10
&È/&8/25(/$&,21$/'(783/$
Quando se escreve uma expressão da álgebra relacional,
especifica-se uma seqüência de procedimentos que geram a resposta
a nossa consulta. O cálculo relacional de tuplas, por contraste, é uma
linguagem de consulta não procedural. Ela descreve a informação
desejada sem dar um procedimento específico para obter tal
informação. É uma linguagem teórica baseada no FiOFXOR GH
SUHGLFDGRV
Poder de expressão é idêntico ao da álgebra relacional, isto é,
toda consulta que pode ser expressa com álgebra relacional também
pode ser expressa com cálculo e vice-versa.
SQL incorpora algumas idéias do cálculo relacional.
Uma expressão de cálculo relacional tem a seguinte forma:
..., V.D, V.E [WHERE f]
onde:
..., V são variáveis de tuplas, isto é, variáveis definidas sobre as linhas
de uma tabela (antes de escrever a expressão é necessário definir o
escopo de cada variável)
..., D, E são nomes de colunas de tabelas (A é o nome de uma coluna
de T, E é o nome de uma coluna de V)
f é uma expressão lógica (resulta falso ou verdadeiro) sobre as
variáveis da expressão (T,...V). Esta expressão pode envolver:
♦ Condições sobre valores de atributos e literais como:
T.A > T.B + 5
V.E = "abcd"
♦ Fórmulas lógicas construídas sobre as condições usando
combinações: NOT, AND, OR, ...
♦ Usando qualificadores:
Universal
∀T (f)
Existencial ∃T (f)
BANCO DE DADOS I - URI - Prof. Paulo Ricardo Rodegheri
11
O efeito da expressão de cálculo pode ser resumido pelo seguinte
modelo mental de execução:
♦
♦
♦
É feito o produto cartesiano de todas as tabelas envolvidas
(referenciadas por T,....,V)
No produto cartesiano são selecionadas as linhas que tornam
a fórmula f verdadeira
É feita a projeção sobre as colunas A,B,...D,E
Exemplos:
Nos exemplos que seguem supõe-se que:
A variável SHFDesteja definida sobre a tabela peca
A variável IRUQHFesteja definida sobre a tabela fornec
A variável HPEDUTesteja definida sobre a tabela embarq
SHFDQRPH
Resulta uma tabela que contém os nomes de todas as peças.
SHFDQRPHWHERE SHFDFRG 3
Resulta uma tabela que contém todos os dados da peça de
código P1.
IRUQHFFRGIRUQHFQRPHIRUQHFVWDWXV
WHERE (IRUQHFVWDWXV!$1'IRUQHFFLGDGH 5,2
Resulta uma tabela com o código, nome e status dos
fornecedores que tenham status > 5 e sejam do Rio.
HPEDUTFRGIRUQHF HPEDUTFRGSHFD SHFDQRPH WHERE
(HPEDUTFRGSHFD SHFDFRGSHFD
Obtém códigos de fornecedores, códigos e nomes de peças para
todos os embarques.
BANCO DE DADOS I - URI - Prof. Paulo Ricardo Rodegheri
12
862'248$17,),&$'25(;,67(1&,$/
A fórmula ∃T (f) (∃ é o quantificador existencial - existe) é
verdadeira quando existe ao menos uma linha T que torna a
fórmula f verdadeira.
É usada para exigir que uma linha de uma tabela que vai
para o resultado esteja associada de alguma forma (especificada
pela fórmula f) a uma linha de outra tabela(T) que não vai ao
resultado.
Exemplos:
IRUQHFQRPHWHERE
HPEDUTIRUQHFFRG HPEDUTFRGIRUQHF Obtém os nomes dos fornecedores para os quais há ao menos
XP embarque.
IRUQHFQRPH WHERE
HPEDUT
IRUQHFFRG HPEDUTFRGIRUQHF$1'
SHFDSHFDFRG HPEDUTFRGSHFD$1'
SHFDFRU YHUP
Obtém os nomes de fornecedores que tem embarque de ao
menos uma peça na cor vermelha.
IRUQHFQRPH WHERE
NOT HPEDUT
IRUQHFFRG HPEDUTFRGIRUQHF$1'
HPEDUTFRGSHFD 3
Obtém os nomes dos fornecedores que não tem embarques da
peça de código P2.
BANCO DE DADOS I - URI - Prof. Paulo Ricardo Rodegheri
13
64/
64/6WUXFWXUHG4XHU\/DQJXDJHé 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 - 1994)
&20321(17(6'(64/
64/ é uma linguagem completa de PDQLSXODomR 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.
$'(5Ç1&,$$3$'5®(6
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.
BANCO DE DADOS I - URI - Prof. Paulo Ricardo Rodegheri
14
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.
,16758d®(6'$''/
64/ oferece três instruções para definição do esquema da base
de dados:
&UHDWH 7DEOH que define a estrutura de uma tabela, suas
restrições de integridade e a criação da tabela vazia.
'URS7DEOH que elimina a tabela da base de dados.
$OWHU 7DEOH que permite adicionar colunas a uma tabela
existente (altera a estrutura da tabela).
&5,$d­2'(7$%(/$6
create table peca
(
codpeca
nomepeca
corpeca
pesopeca
cidadepeca
primary key
char(4)
varchar (50)
char (10),
integer ,
varchar(40),
(codpeca))
not null,
not null,
create table fornec
(
codfornec
nomefornec
statusfornec
cidadefornec
primary key
char(4)
varchar (50)
integer,
varchar(40),
(codfornec))
not null,
not null,
BANCO DE DADOS I - URI - Prof. Paulo Ricardo Rodegheri
create table embarq
(codpeca
codfornec
qtdeembarq
primary key
foreign key
foreign key
15
char(4)
not null,
char(4)
not null,
integer
not null,
(codpeca, codfornec),
(codpeca) references peca,
(codfornec) references Fornec)
2%6(59$d®(6
♦ Em SQL o conjunto de domínios de valores de atributos é IL[R
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 (BLOBs, até 2 GB) 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 - Prof. Paulo Ricardo Rodegheri
(/,0,1$d­2($/7(5$d­2'(7$%(/$6
16
Para eliminar completamente uma tabela (vazia ou não) da base
de dados, é usada a instrução:
'5237$%/(HPEDUT
Para modificar a estrutura de tabelas já existentes na base de
dados, há uma instrução que permite adicionar colunas a tabelas:
$/7(57$%/(HPEDUT$''GDWDHPEDUTGDWH
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.
02'(/2%È6,&2'$,16758d­2'(&2168/7$
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 SURGXWRFDUWHVLDQR 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 - Prof. Paulo Ricardo Rodegheri
([HPSORVGH,QVWUXo}HV64/
¾ 2EWHUWRGRVRVGDGRVGDWDEHODSHoDV
SELECT codpeca, nomepeca, corpeca, cidadepeca
FROM peca
17
2X
SELECT *
FROM peca
¾ 2EWHURVQRPHVGHWRGDVDVSHoDV
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 QmRelimina 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)
¾ 2EWHU RV FyGLJRV H QRPHV GH IRUQHFHGRUHV FRP VWDWXV ! H
TXHVHMDPGR5LR
SELECT codfornec, nomefornec
FROM fornec
WHERE statusfornec > 5 AND
cidadefornec = "rio"
BANCO DE DADOS I - URI - Prof. Paulo Ricardo Rodegheri
18
¾ 3DUDFDGD(PEDUTXHFRPTXDQWLGDGH!REWHURFyGLJRGR
IRUQHFHGRURFyGLJRGDSHoDHRQRPHGDSHoD
SELECT codfornec, embarq.codpeca nomepeca
FROM embarq, peca
WHERE qtdeembarq > 300 AND
embarq.codpeca = peca.codpeca
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 TXDOLILFDomR 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.
¾ 2EWHURVQRPHVGHIRUQHFHGRUHVGDVSHoDVGHFRUYHUPHOKD
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.
¾ 2EWHU WRGRV RV SDUHV GH QRPHV GH IRUQHFHGRUHV TXH
HQFRQWUDPVHQDPHVPDFLGDGH
SELECT f1.nomefornec, f2.nomefornec
FROM fornec f1, fornec f2
WHERE f1.cidadefornec = f2.cidadefornec AND
f1.Codfornec < f2.codfornec
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
BANCO DE DADOS I - URI - Prof. Paulo Ricardo Rodegheri
19
de fornecedores <fx, fy> apareça no resultado também em ordem
inversa <fy, fx>.
¾ 2EWHU RV FyGLJRV GDV SHoDV TXH VHMDP GH SRD 28 SDUD RV
TXDLVKDMDHPEDUTXHVGRIRUQHFHGRUGHFyGLJR)
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.
¾ 2EWHU RV FyGLJRV GRV IRUQHFHGRUHV TXH WHP HPEDUTXHV GH
SHoDVGHFRUYHUPHOKD
SELECT codfornec
FROM embarq, peca
WHERE corpeca = "verm" AND
embarq.codpeca = peca.codpeca
No caso o resultado da consulta envolve apenas colunas da
tabela SHoD, mas a clausula FROM referencia também a tabela
HPEDUT.
Para este tipo de consulta a solução mais natural é através de
FRQVXOWDV DQLQKDGDV (embutidas). Ex.:
SELECT codfornec
FROM embarq
WHERE codpeca IN
( SELECT codpeca
FROM peca
WHERE corpeca = "verm")
BANCO DE DADOS I - URI - Prof. Paulo Ricardo Rodegheri
20
¾ 2EWHURVQRPHVGRVIRUQHFHGRUHVGHSHoDVGHFRUYHUPHOKD
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"))
Consultas podem ser aninhadas em múltiplos níveis:
¾ 2EWHURVQRPHVGRVIRUQHFHGRUHVTXHWHPHPEDUTXHVGDSHoD
GHFyGLJR3
6ROXomRFRP3URGXWR&DUWHVLDQR
SELECT nomefornec
FROM fornec, embarq
WHERE codpeca = ‘P1’ AND
embarq.codfornec = fornec.codfornec
6ROXomRFRP&RQVXOWDV$QLQKDGDV
SELECT nomefornec
FROM fornec
WHERE codfornec IN
( SELECT codfornec
FROM embarq
WHERE codpeca = "P1")
BANCO DE DADOS I - URI - Prof. Paulo Ricardo Rodegheri
28
SELECT nomefornec
FROM fornec
WHERE ‘P1’ IN
( SELECT codpeca
FROM embarq
WHERE codfornec = fornec.codfornec)
21
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.
¾ 2EWHURVQRPHVGRVIRUQHFHGRUHVSDUDRVTXDLVKiHPEDUTXHV
GHSHoDVORFDOL]DGDVQDPHVPDFLGDGHGRIRUQHFHGRU
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 - Prof. Paulo Ricardo Rodegheri
22
¾ 2EWHURVQRPHVGRVIRUQHFHGRUHVSDUDRVTXDLVKiDRPHQRV
XPHPEDUTXH
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.
¾ 2EWHU RV QRPHV GRV IRUQHFHGRUHV SDUD RV TXDLV QmR Ki
HPEDUTXHV
SELECT nomefornec
FROM fornec
WHERE NOT EXISTS
SELECT *
FROM embarq
WHERE codfornec = fornec.codfornec)
¾ 2EWHU RV FyGLJRV GRV IRUQHFHGRUHV TXH SRVVXHP HPEDUTXHV
SDUDWRGDVSHoDVGHFRUYHUPHOKD
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:
¾ 2EWHU RV FyGLJRV GRV IRUQHFHGRUHV WDO TXH QmR H[LVWD XPD
SHoD YHUPHOKD SDUD D TXDO QmR H[LVWD XP HPEDUTXH GR
IRUQHFHGRUHPTXHVWmR
BANCO DE DADOS I - URI - Prof. Paulo Ricardo Rodegheri
23
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 )
¾ 2EWHU RV QRPHV GRV IRUQHFHGRUHV SDUD RV TXDLV R VWDWXV IRL
LQIRUPDGRQmRHVWiYD]LR
SELECT nomefornec
FROM fornec
WHERE statusfornec IS NOT NULL
Note-se que qualquer comparação (=, <, >, ...) com vazio resulta
sempre em falso.
02'(/2(67(1','2'(&2168/7$
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 - Prof. Paulo Ricardo Rodegheri
24
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.
64/)81d®(6
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).
)81d®(662%5(72'$$7$%(/$
¾
2EWHURQ~PHURGHIRUQHFHGRUHVQDEDVHGHGDGRV
¾
2EWHURQ~PHURGHFLGDGHVHPTXHKiIRUQHFHGRUHV
SELECT count (*)
FROM fornec
SELECT count (distinct nomefornec)
FROM fornec
BANCO DE DADOS I - URI - Prof. Paulo Ricardo Rodegheri
¾
¾
¾
2EWHUDTXDQWLGDGHPi[LPDHPEDUFDGD
25
SELECT MAX (qtdeembarq)
FROM embarq
64/862'($*5$*$'26
2EWHURQ~PHURGHHPEDUTXHVGHFDGDIRUQHFHGRU
SELECT codfornec, count (codpeca)
FROM embarq
GROUP BY codfornec
2EWHURQ~PHURGHTXDQWLGDGH!GHFDGDIRUQHFHGRU
SELECT codfornec, count (codpeca)
FROM embarq
WHERE qtdeembarq > 300
GROUP BY codfornec
OBS: SQL padrão exige que se escreva COUNT (DISTINCT
codpeca).
¾ 2EWHUDTXDQWLGDGHWRWDOHPEDUFDGDGHSHoDVGHFRUYHUPHOKD
SDUDFDGDIRUQHFHGRU
SELECT codfornec, SUM (qtdeembarq)
FROM embarq
WHERE codpeca IN
SELECT codpeca
FROM peca
WHERE corpeca = ‘verm’ )
GROUP BY codfornec
BANCO DE DADOS I - URI - Prof. Paulo Ricardo Rodegheri
26
¾ 2EWHU D TXDQWLGDGH WRWDO HPEDUFDGD GH FDGD SHoD ([LELU R
UHVXOWDGRSRURUGHPGHVFHQGHQWHGHTXDQWLGDGHHPEDUFDGD
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
¾ 3DUD DV SHoDV GH FRU YHUPHOKD FXMD TXDQWLGDGH WRWDO
HPEDUFDGDH[FHGDREWHURQ~PHURGHVHXVHPEDUTXHV
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 - Prof. Paulo Ricardo Rodegheri
27
,16758d®(6'($78$/,=$d­2
SQL provê 3 instruções de atualização do conteúdo de tabelas
previamente definidas e criadas pelo CREATE TABLE.
,16(57Serve para inserir uma ou mais linhas em XPD tabela.
'(/(7(Serve para excluir uma ou mais linhas de XPD tabela.
83'$7(Serve para alterar os dados de uma ou mais linhas de XPD
tabela.
,16758d­2,16(57
♦
,QFOXLUXPQRYRIRUQHFHGRU
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.
♦
&ULDUHPEDUTXHVFRPTXDQWLGDGHSDUDWRGDVSHoDVGHFRU
YHUPHOKDSHORIRUQHFHGRUGHFyGLJR)
INSERT INTO embarq
SELECT codpeca, "F5", 100
FROM Peca
WHERE corpeca = ‘verm’
BANCO DE DADOS I - URI - Prof. Paulo Ricardo Rodegheri
28
Nessa variante é possível criar múltiplas linhas (definidas por
uma instrução normal de consulta) em uma tabela.
♦
,16758d­2'(/(7(
([FOXLURIRUQHFHGRUGHFyGLJR)
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).
♦
♦
([FOXLUWRGRVRVHPEDUTXHVGHSHoDVYHUPHOKDV
DELETE FROM embarq
WHERE codpeca IN
( SELECT codpeca
FROM peca
WHERE corpeca = ‘verm’ )
([FOXLUWRGRVRVHPEDUTXHVGHSHFDVYHUPHOKDV
DELETE FROM embarq
,16758d­283'$7(
BANCO DE DADOS I - URI - Prof. Paulo Ricardo Rodegheri
♦
♦
0RGLILFDURVWDWXVGRIRUQHFHGRUGHFyGLJR)SDUD
UPDATE fornec
SET statusfornec = 20
WHERE codfornec = ‘F1’
,QFUHPHQWDURVWDWXVGRIRUQHFHGRUGHFyGLJR)HP
UPDATE fornec
SET statusfornec = statusfornec * 1,10
WHERE codfornec = ‘F1’
♦
,QFUHPHQWDUHPRVWDWXVGHWRGRVRVIRUQHFHGRUHVTXH
WHPHPEDUTXHGRSURGXWR3
UPDATE Fornec
SET statusfornec = statusfornec * 1,10
WHERE codfornec IN
( SELECT codfornec
FROM embarq
WHERE codpeca = ‘P2’ )
29
BANCO DE DADOS I - URI - Prof. Paulo Ricardo Rodegheri
9,6®(6(064/
30
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>
(;(03/26'(9,6®(6
♦
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 - Prof. Paulo Ricardo Rodegheri
♦
31
2EWHU R QRPH GR IRUQHFHGRU H QRPH GD SHoD SDUD FDGD
HPEDUTXH
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.
$78$/,=$d­2$75$9e6'(9,6®(6
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 XPD
~QLFD tabela da base de dados e que as linhas da visão estejam em
relação XPSDUDXP com as linhas da tabela da base de dados.
35,1&Ë3,2: O mapeamento do efeito da atualização sobre uma visão
em uma atualização sobre uma tabela da base de dados deve ser QmR
DPEtJXR.
BANCO DE DADOS I - URI - Prof. Paulo Ricardo Rodegheri
32
64/(0%87,'2
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);
(;(&64/
SELECT nomeforn INTO %nom_forn
FROM fornec
WHERE codfornec = %cod_forn;
(1'(;(&
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 ,172.
BANCO DE DADOS I - URI - Prof. Paulo Ricardo Rodegheri
33
64/',1Æ0,&2
• 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:
FKDU *sqlprog= ‘XSGDWH conta VHW saldo = saldo * 1.05
ZKHUH num_conta =?’
EXECSQLSUHSDUHdinprogIURPsqlprog;
FKDUconta[10]= ‘A-101’;
EXECSQLH[HFXWHdinprogXVLQJ:conta
O programa dinâmico contém uma “?”, que é colocada para manter
um valor gerado quando o programa SQL é executado.