6. SQL avançado
6.1. Introdução
Neste capítulo, apresentamos
- sintaxes adicionais da instrução SELECT que a tornam um comando de consulta muito poderoso, especialmente para consultar várias tabelas de uma só vez.
- sintaxes estendidas de comandos já abordados
Para ilustrar as várias ordens, iremos trabalhar com as seguintes tabelas utilizadas para a gestão de encomendas numa empresa de distribuição de livros de pequena a média dimensão:
6.1.1. a tabela CLIENTS
Armazena informações sobre os clientes da empresa:
 |
Um identificador único para o cliente - chave primária | |
nome do cliente | |
I=Particular, E=Empresa, A=Governo | |
nome próprio para um indivíduo | |
Nome da pessoa de contacto nas instalações do cliente (no caso de uma empresa ou entidade governamental) | |
Endereço do cliente - rua | |
Cidade | |
Código postal | |
Telefone | |
Desde quando é cliente? | |
Y (Sim) se o cliente tiver dívidas para com a empresa e N (Não) caso contrário. |
6.1.2. A tabela ARTIGOS
Armazena informações sobre os produtos vendidos, neste caso livros. A sua estrutura é a seguinte:
Um identificador único para um livro (ISBN = International Standard Book Number) - chave primária | |
Título do livro | |
Código que identifica de forma única uma editora | |
Nome do autor | |
Resumo do livro | |
Quantidade vendida este ano | |
Quantidade vendida no ano anterior | |
Data da última venda | |
Quantidade da última entrega | |
Data da última entrega | |
Preço de venda | |
Custo de aquisição | |
Quantidade mínima de encomenda | |
Nível mínimo de stock | |
Quantidade em stock |
O seu conteúdo pode ser o seguinte:
6.1.3. a tabela ORDERS
Armazena informações sobre as encomendas feitas pelos clientes. A sua estrutura é a seguinte:
Um identificador único para uma encomenda - chave primária | |
ID do cliente para esta encomenda - chave estrangeira - referência a CUSTOMERS(ID) | |
Data em que esta encomenda foi registada | |
O (Sim) se a encomenda foi cancelada e N (Não) caso contrário. |
6.1.4. A tabela DETAILS
Contém os detalhes de uma encomenda, ou seja, os títulos e as quantidades dos livros encomendados. A sua estrutura é a seguinte:
Número da encomenda - chave estrangeira que faz referência à coluna NOCMD da tabela COMMANDES | |
Número do livro encomendado - chave estrangeira que faz referência à coluna ISBN na tabela BOOKS | |
Quantidade encomendada |
O seu conteúdo pode ser o seguinte:
Acima, vemos que a encomenda n.º 3 (NOCMD) envolve três livros. Isto significa que o cliente encomendou três livros ao mesmo tempo. Os registos deste cliente podem ser encontrados na tabela [ORDERS], onde vemos que a encomenda n.º 3 foi feita pelo cliente n.º 5. A tabela [CUSTOMERS] indica-nos que o cliente n.º 5 é a empresa NetLogos, em Segré.
6.2. A instrução SELECT
Aqui, pretendemos aprofundar a nossa compreensão da instrução SELECT, introduzindo novas sintaxes para a mesma.
6.2.1. Sintaxe de uma consulta com várias tabelas
SELECT coluna1, coluna2, ... FROM tabela1, tabela2, ..., tabelaP WHERE condição ORDER BY ... | |
A novidade aqui é que as colunas coluna1, coluna2, ... provêm de várias tabelas tabela1, tabela2, ... Se duas tabelas tiverem colunas com o mesmo nome, a ambiguidade é resolvida utilizando a notação tabelai.coluna. A condição pode aplicar-se a colunas de tabelas diferentes. |
Como funciona
É construído o produto cartesiano das tabelas table1, table2, ..., tablep. Se n_i for o número de linhas na tabela_i, a tabela resultante terá n₁*n₂*...*n_p linhas contendo todas as colunas das diferentes tabelas. | |
A condição WHERE é aplicada a esta tabela. É assim produzida uma nova tabela | |
Esta tabela é ordenada de acordo com o método especificado em ORDER. | |
São apresentadas as colunas especificadas após SELECT. |
Exemplos
Utilizamos as tabelas apresentadas anteriormente. Pretendemos ver os detalhes das encomendas efetuadas após 25 de setembro:
SQL>select details.nocmd,isbn,qte from commandes,details
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
Note que, após FROM, listamos os nomes de todas as tabelas cujas colunas estamos a referenciar. No exemplo anterior, todas as colunas selecionadas pertencem à tabela DETAILS. No entanto, a condição faz referência à tabela ORDERS. Daí a necessidade de listar esta última após FROM. A operação que verifica a igualdade entre colunas em duas tabelas diferentes é frequentemente chamada de equijoin.
A consulta SELECT também poderia ter sido escrita da seguinte forma:
SQL> select details.nocmd,isbn,qte from commandes
inner join details on details.nocmd=commandes.nocmd
where commandes.datecmd>'25-sep-91'
Vamos continuar com os nossos exemplos. Queremos o mesmo resultado de antes, mas com o título do livro encomendado, em vez do seu ISBN:
SQL>select commandes.nocmd, articles.titre, details.qte
from commandes,articles,details
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
and details.isbn=articles.isbn
O mesmo resultado é obtido com a seguinte consulta SQL, que é menos legível:
SQL> select details.nocmd,articles.titre,details.qte from details
inner join commandes on details.nocmd=commandes.nocmd
inner join articles on details.isbn=articles.isbn
where commandes.datecmd>'25-sep-91'
Acima, são realizadas duas junções internas com a tabela [DETAILS]:
- uma com a tabela [ORDERS] para aceder à data de encomenda de um livro
- uma com a tabela [ARTICLES] para aceder ao título do livro encomendado
Também queremos o nome do cliente que efetuou a encomenda:
SQL>select commandes.nocmd, articles.titre, qte ,clients.nom
from commandes,details,articles,clients
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
and details.isbn=articles.isbn
and commandes.idcli=clients.id
Também queremos as datas dos pedidos e que estas sejam apresentadas por ordem decrescente:
SQL>select commandes.nocmd, commandes.datecmd, articles.titre, qte ,clients.nom
from commandes,details,articles,clients
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
and details.isbn=articles.isbn
and commandes.idcli=clients.id
order by commandes.datecmd descending
Aqui estão algumas regras a seguir ao criar junções:
- Após SELECT, liste as colunas que pretende apresentar. Se a coluna existir em várias tabelas, preceda-a com o nome da tabela.
- Após FROM, liste todas as tabelas que serão consultadas pela instrução SELECT, ou seja, as tabelas que contêm as colunas listadas após SELECT e WHERE.
6.2.2. Autojuntão
Queremos encontrar os livros que têm um preço de retalho superior ao do livro «Using SQL»:
SQL>select a.titre from articles a, articles b
where b.titre='Using SQL'
and a.prixvente>b.prixvente
As duas tabelas envolvidas na junção são idênticas neste caso: a tabela articles. Para as distinguir, atribuímos-lhes aliases: a partir de articles a, articles b. O alias da primeira tabela é a, e o da segunda é b. Esta sintaxe pode ser utilizada mesmo que as tabelas sejam diferentes. Quando se utiliza um alias, este deve ser utilizado ao longo de toda a instrução SELECT, em substituição da tabela a que se refere.
6.2.3. Juntar Externa
Queremos identificar os clientes que fizeram uma compra em setembro, juntamente com a data da encomenda. Os outros clientes são apresentados sem esta data:
SQL>select clients.nom,commandes.datecmd from clients
left outer join commandes on clients.id=commandes.idcli
where datecmd between '01-sep-91' and '30-sep-91'
É surpreendente que, neste caso, não obtenhamos o resultado correto. Deveríamos ter todos os clientes presentes na tabela [CLIENTS], o que não é o caso. Quando pensamos em como funciona uma junção externa, percebemos que os clientes que não fizeram uma compra foram associados a uma linha vazia na tabela ORDERS e, portanto, a uma data vazia (um valor NULL na terminologia SQL). Esta data não cumpre a condição definida para a data, pelo que o cliente correspondente não é apresentado. Vamos tentar outra coisa:
SQL>select clients.nom,commandes.datecmd from clients
left outer join commandes on clients.id=commandes.idcli
where (commandes.datecmd between '01-sep-91' and '30-sep-91')
or (commandes.datecmd is null)
Desta vez, obtemos a resposta correta à nossa pergunta.
6.2.4. Consultas aninhadas
SELECT coluna[s] FROM tabela[s] WHERE expressão operador de consulta ORDER BY ... | |
Uma consulta é uma instrução SELECT que devolve um conjunto de 0, 1 ou mais valores. Temos então uma condição WHERE do tipo expressão operador (val1, val2, ..., vali) expressão e vali devem ser do mesmo tipo. Se a consulta devolver um único valor, ficamos reduzidos a uma condição do tipo expressão operador valor , com a qual estamos familiarizados. Se a consulta devolver uma lista de valores, podemos utilizar os seguintes operadores:
expressão IN (val1, val2, ..., vali): verdadeiro se a expressão for avaliada como um dos elementos da lista vali.
oposto de IN
deve ser precedida por =, !=, >, >=, <, <= expressão >= ANY (val1, val2, .., valn): verdadeiro se a expressão for >= um dos valores vali da lista
deve ser precedida por =, !=, >, >=, <, <= expressão >= ALL (val1, val2, .., valn): verdadeiro se a expressão for >= todos os valores válidos na lista
consulta: verdadeiro se a consulta devolver pelo menos uma linha. |
Exemplos
Vamos revisitar a questão já resolvida por uma equijoin: exibir os títulos com um preço de venda superior ao do livro «Using SQL».
SQL>select titre from ARTICLES
where prixvente > (select prixvente from ARTICLES where titre='Using SQL')
Esta solução parece mais intuitiva do que a equijoin. Realizamos um filtro inicial com um SELECT e, em seguida, um segundo filtro no conjunto resultante. Desta forma, podemos realizar vários filtros em sequência.
Queremos encontrar os títulos com um preço de venda superior ao preço médio de venda:
Quais os clientes que encomendaram os títulos devolvidos pela consulta anterior?
SQL>select distinct idcli from COMMANDES,DETAILS
where DETAILS.isbn in
(select isbn from ARTICLES where prixvente
> (select avg(prixvente) from ARTICLES))
and COMMANDES.nocmd=DETAILS.nocmd
Explicação
- Selecionamos da tabela DETAILS os códigos ISBN encontrados entre os livros com um preço superior ao preço médio dos livros.
- Nas linhas selecionadas no passo anterior, o ID do cliente (IDCLI) não está presente. Este encontra-se na tabela ORDERS. A ligação entre as duas tabelas é estabelecida através do número da encomenda (NOCMD), daí a condição de junção ORDERS.nocmd=DETAILS.nocmd.
- Um único cliente pode ter comprado um dos livros relevantes várias vezes, caso em que o seu código IDCLI apareceria várias vezes. Para evitar isso, colocamos a palavra-chave DISTINCT após SELECT. DISTINCT geralmente elimina duplicados das linhas devolvidas por uma consulta SELECT.
- Para recuperar o nome do cliente, teríamos de realizar uma junção adicional entre as tabelas ORDERS e CUSTOMERS, conforme mostrado na consulta seguinte.
SQL> select distinct CLIENTS.nom from COMMANDES,DETAILS,CLIENTS
where DETAILS.isbn in
(select isbn from ARTICLES where prixvente
> (select avg(prixvente) from ARTICLES))
and COMMANDES.nocmd=DETAILS.nocmd
and COMMANDES.IDCLI=CLIENTS.ID
Encontre os clientes que não fizeram uma encomenda desde 24 de setembro:
SQL>select nom from CLIENTS
where clients.id not in
(select distinct commandes.idcli from commandes where datecmd>='24-sep-91')
Vimos que as linhas podem ser filtradas de outras formas além da cláusula WHERE: utilizando a cláusula HAVING em conjunto com a cláusula GROUP BY. A cláusula HAVING filtra grupos de linhas.
Tal como na cláusula WHERE, a sintaxe
HAVING expression opérateur requête
é possível, com a restrição já mencionada de que a expressão deve ser uma das expressões expri na cláusula
GROUP BY expr1, expr2, ...
Exemplos
Quais são os números de vendas dos livros com preço superior a 200F?
Primeiro, vamos apresentar as quantidades vendidas por título:
SQL>select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
where DETAILS.isbn=ARTICLES.isbn
group by titre
Agora, vamos filtrar os títulos:
SQL> select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
where DETAILS.isbn=ARTICLES.isbn
group by titre
having titre in (select titre from ARTICLES where prixvente>200)
Talvez de forma mais óbvia, poderíamos ter escrito:
SQL>select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
where DETAILS.isbn=ARTICLES.isbn
and ARTICLES.prixvente>200
group by titre
6.2.5. Consultas aninhadas
No caso de consultas aninhadas, existe uma consulta pai (a consulta mais externa) e uma consulta filho (a consulta mais interna). A consulta pai só é avaliada depois de a consulta filho ter sido totalmente avaliada.
As consultas correlacionadas têm a mesma sintaxe, com a seguinte diferença menor: a consulta filha realiza uma junção na tabela da consulta pai. Neste caso, o par de consultas pai-filha é avaliado repetidamente para cada linha da tabela pai.
Exemplo
Vamos revisitar o exemplo em que queremos os nomes dos clientes que não fizeram uma encomenda desde 24 de setembro:
SQL>
select nom from clients
where not exists
(select idcli from commandes
where datecmd>='24-sep-91'
and commandes.idcli=clients.id)
A consulta principal opera na tabela de clientes. A consulta secundária realiza uma junção entre as tabelas de clientes e de encomendas. Trata-se, portanto, de uma consulta correlacionada. Para cada linha da tabela de clientes, a consulta secundária é executada: procura o ID do cliente nas encomendas feitas após 24 de setembro. Se não encontrar nenhuma (not exists), o nome do cliente é exibido. Em seguida, passa para a linha seguinte da tabela de clientes.
6.2.6. Critérios para escrever a instrução SELECT
Vimos, em várias ocasiões, que é possível obter o mesmo resultado utilizando diferentes instruções SELECT. Vejamos um exemplo: Apresentar os clientes que fizeram uma encomenda:
Juntar
Consultas aninhadas
retorna o mesmo resultado.
Consultas correlacionadas
SQL>
select nom from clients
where exists (select * from commandes where commandes.idcli=clients.id)
retorna o mesmo resultado.
Os autores Christian MAREE e Guy LEDANT, no seu livro «SQL: Introdução, Programação e Domínio», sugerem alguns critérios de seleção:
Desempenho
O utilizador não sabe como o SGBD «consegue» encontrar os resultados que solicita. Por isso, só através da experiência é que descobrirá que uma consulta é mais eficiente do que outra. MAREE e LEDANT afirmam, com base na experiência, que as consultas correlacionadas parecem geralmente mais lentas do que as consultas aninhadas ou as junções.
Formulação
A formulação utilizando consultas aninhadas é frequentemente mais legível e intuitiva do que as junções. No entanto, nem sempre é utilizável. Devem ser destacados dois pontos em particular:
- As tabelas que contêm as colunas especificadas na cláusula SELECT (SELECT col1, col2, ...) devem ser listadas após a palavra-chave FROM. É então realizado o produto cartesiano destas tabelas, o que é conhecido como uma junção.
- Quando a consulta apresenta resultados de uma única tabela e a filtragem das linhas dessa tabela requer a consulta a outra tabela, podem ser utilizadas consultas aninhadas.
6.3. Extensões de sintaxe
Por conveniência, apresentámos principalmente sintaxes abreviadas para os vários comandos. Nesta secção, apresentamos as suas sintaxes expandidas. Estas são autoexplicativas, pois são análogas às do comando SELECT, amplamente estudado.
INSERT
INSERT INTO tabela (col1, col2, ...) VALUES (val1, val2, ...) | |
INSERT INTO tabela (col1, col2, ..) (consulta) | |
Estas duas sintaxes foram apresentadas |
DELETE
DELETE FROM tabela WHERE condição | |
Esta sintaxe é bem conhecida. Note-se que a condição pode conter uma consulta utilizando a sintaxe WHERE expressão operador (consulta) |
UPDATE
ATUALIZAR tabela SET col1=expr1, col2=expr2, ... WHERE condição | |
Esta sintaxe já foi apresentada. Note que a condição pode conter uma consulta utilizando a sintaxe WHERE expressão operador (consulta) |
UPDATE tabela SET (col1, col2, ..) = consulta1, (cola, colb, ..) = consulta2, ... WHERE condição | |
Os valores atribuídos às várias colunas podem provir de uma consulta. |