6. Aprofundamento na linguagem SQL
6.1. Introduction
Neste capítulo, apresentamos
- outras sintaxes do comando SELECT, que o tornam um comando de consulta muito poderoso, especialmente para consultar várias tabelas ao mesmo tempo.
- sintaxes ampliadas de comandos já estudados
Para ilustrar os diversos comandos, trabalharemos com as seguintes tabelas utilizadas para o gerenciamento de pedidos em um sistema de distribuição de livros PME:
6.1.1. a tabela CLIENTS
Ela armazena informações sobre os clientes do PME:
 |
número que identifica o cliente de forma exclusiva — chave primária | |
nome do cliente | |
I = Pessoa física, E = Empresa, A = Órgão público | |
nome, no caso de pessoa física | |
Nome da pessoa de contato no cliente (no caso de uma empresa ou órgão público) | |
Endereço do cliente – rua | |
cidade | |
CEP | |
Telefone | |
Desde quando é cliente? | |
O (Sim) se o cliente deve dinheiro à empresa e N (Não) caso contrário. |
6.1.2. a tabela ARTICLES
Ela armazena informações sobre os produtos vendidos, neste caso, livros. Sua estrutura é a seguinte:
número que identifica um livro de forma exclusiva (ISBN = International Standard Book Number) — chave primária | |
Título do livro | |
Código que identifica uma editora de forma exclusiva | |
Nome do autor | |
Resumo do livro | |
Quantidade vendida no 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 a ser encomendada | |
Nível mínimo de estoque | |
Quantidade em estoque |
Seu conteúdo poderia ser o seguinte:
6.1.3. a tabela COMMANDES
Ela armazena informações sobre os pedidos feitos pelos clientes. Sua estrutura é a seguinte:
Número que identifica um pedido de forma exclusiva — chave primária | |
Número do cliente que fez este pedido - chave estrangeira - referência CLIENTS(ID) | |
Data de registro deste pedido | |
O (Sim) se o pedido tiver sido cancelado e N (Não) caso contrário. |
6.1.4. a tabela DETAILS
Ela contém os detalhes de um pedido, ou seja, as referências e quantidades dos livros encomendados. Sua estrutura é a seguinte:
Número do pedido — chave estrangeira que faz referência à coluna NOCMD da tabela COMMANDES | |
Nº do livro encomendado — chave estrangeira que faz referência à coluna ISBN da tabela LIVRES | |
Quantidade encomendada |
Seu conteúdo poderia ser o seguinte:
Acima, vemos que o pedido nº 3 (NOCMD) refere-se a três livros. Isso significa que o cliente encomendou três livros ao mesmo tempo. As referências desse cliente podem ser encontradas na tabela [COMMANDES], onde se observa que o pedido nº 3 foi feito pelo cliente nº 5. A tabela [CLIENTS] nos informa que o cliente nº 5 é a empresa NetLogos, de Segré.
6.2. O pedido SELECT
Propomos aqui aprofundar nosso conhecimento sobre o pedido SELECT, apresentando novas sintaxes do mesmo.
6.2.1. Sintaxe de uma consulta em várias tabelas
SELECT coluna1, coluna2, ... FROM tabela1, tabela2, ..., tabela p WHERE condition ORDER BY ... | |
A novidade aqui reside no fato de 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 por meio da notação tablei.colonnej. O código condition pode se referir às colunas de diferentes tabelas. |
Funcionamento
É gerada a tabela cartesiana de table1, table2, ..., tablep. Se ni for o número de linhas de tablei, a tabela construída terá, portanto, n1*n2*...*np linhas, contendo o conjunto das colunas das diferentes tabelas. | |
A condition da WHERE é aplicada a essa tabela. Assim, é gerada uma nova tabela | |
Esta é ordenada de acordo com o modo indicado em ORDER. | |
As colunas solicitadas no SELECT são exibidas. |
Exemplos
Utilizamos as tabelas apresentadas anteriormente. Queremos conhecer os detalhes dos pedidos feitos 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
Observe-se que, após FROM, é inserido o nome de todas as tabelas cujas colunas são referenciadas. No exemplo anterior, todas as colunas selecionadas pertencem à tabela DETAILS. No entanto, a condição faz referência à tabela COMMANDES. Daí a necessidade de nomear esta última após o FROM. A operação que verifica a igualdade entre colunas de duas tabelas diferentes é frequentemente chamada de equijunção.
A consulta SELECT também poderia ter sido escrita da seguinte maneira:
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 nossos exemplos. Queremos o mesmo resultado de antes, mas com o título do livro encomendado, em vez de seu número: 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 [COMMANDES] para acessar a data do pedido de um livro
- uma delas com a tabela [ARTICLES] para ter acesso ao título do livro encomendado
Além disso, queremos o nome do cliente que fez o pedido:
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
Além disso, queremos as datas dos pedidos e que elas sejam exibidas em 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 serem observadas nas junções:
- Após SELECT, colocam-se as colunas que se deseja exibir. Se a coluna existir em várias tabelas, ela deve ser precedida pelo nome da tabela.
- Após FROM, colocam-se todas as tabelas que serão exploradas pelo SELECT, ou seja, as tabelas que contêm as colunas que se encontram após SELECT e WHERE.
6.2.2. A auto-junção
Queremos saber quais livros têm um preço de venda 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 da junção são, neste caso, idênticas: a tabela articles. Para diferenciá-las, atribui-se a elas um alias: from articles a, articles b. O alias da primeira tabela é a e o da segunda, b. Essa sintaxe pode ser usada mesmo que as tabelas sejam diferentes. Ao utilizar um alias, ele deve ser usado em todo o comando SELECT no lugar da tabela a que se refere.
6.2.3. Junção externa
Queremos identificar os clientes que compraram algo em setembro, incluindo a data do pedido. Os demais clientes são exibidos sem essa 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'
Ficamos surpresos por não obter o resultado esperado. Deveríamos ter todos os clientes presentes na tabela [CLIENTS], o que não é o caso. Ao refletir sobre o funcionamento da junção externa, percebemos que os clientes que não compraram foram associados a uma linha vazia da tabela COMMANDES e, portanto, a uma data vazia (valor NULL na terminologia SQL). Essa data, portanto, não atende à condição definida para a data, e o cliente correspondente não é exibido. 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 para nossa pergunta.
6.2.4. Consultas aninhadas
SELECT coluna[s] FROM tabela[s] WHERE expressão operador consulta ORDER BY ... | |
requête é um comando SELECT que retorna um grupo de 0, 1 ou mais valores. Temos, então, uma condição WHERE do tipo expressão operador (val1, val2, ..., vali) expression e vali devem ser do mesmo tipo. Se a consulta retornar um único valor, voltamos a uma condição do tipo expressão operador valor que já conhecemos bem. Se a consulta retornar uma lista de valores, poderemos utilizar os seguintes operadores:
expression IN (val1, val2, ..., vali): verdadeiro se expression tiver como valor um dos elementos da lista vali.
inverso de IN
deve ser precedido por =, !=, >, >=, <, <= expression >= ANY (val1, val2, .., valn): verdadeiro se expression for >= a um dos valores vali da lista
deve ser precedido por =, !=, >, >=, <, <= expression >= ALL (val1, val2, .., valn): verdadeiro se a expressão for >= a todos os valores válidos da lista
consulta: verdadeira se a função requête retornar pelo menos uma linha. |
Exemplos
Retomamos a questão já resolvida por uma equijunção: exibir os títulos com 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')
Essa solução parece mais intuitiva do que a da junção equi. Faz-se uma primeira filtragem com um SELECT e, em seguida, uma segunda sobre o resultado obtido. É possível realizar várias filtragens em série dessa maneira.
Queremos identificar os títulos cujo preço de venda seja superior ao preço médio de venda:
Quais são os clientes que encomendaram os títulos resultantes da 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ções
- seleciona-se na tabela DETAILS os códigos ISBN que estão entre os livros com preço superior ao preço médio dos livros.
- Nas linhas selecionadas na etapa anterior, não consta o código de cliente IDCLI. Ele se encontra na tabela COMMANDES. A ligação entre as duas tabelas é feita pelo número do pedido NOCMD, daí a equijunção COMMANDES.nocmd=DETAILS.nocmd.
- Um mesmo cliente pode ter comprado várias vezes um dos livros em questão; nesse caso, seu código IDCLI aparecerá várias vezes. Para evitar isso, colocamos a chave DISTINCT após SELECT. DISTINCT, de maneira geral, elimina as duplicatas nas linhas resultantes de um SELECT.
- Para obter o nome do cliente, precisaríamos realizar uma junção equi entre as tabelas COMMANDES e CLIENTS, conforme mostra a consulta a seguir.
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
Encontrar os clientes que não fizeram nenhum pedido 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 é possível filtrar linhas de outra forma além da cláusula WHERE: utilizando a cláusula HAVING em conjunto com as cláusulas GROUP e BY. A cláusula HAVING filtra grupos de linhas.
Da mesma forma que na cláusula WHERE, a sintaxe
HAVING expression opérateur requête
é válida, com a restrição já apresentada de que expression deve ser uma das expressões expri da cláusula
GROUP BY expr1, expr2, ...
Exemplos
Quais são as quantidades vendidas dos livros com mais de 200F?
Vamos primeiro exibir 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)
De uma forma talvez mais clara, 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 correlacionadas
No caso de consultas aninhadas, há uma consulta pai (a consulta mais externa) e uma consulta filha (a consulta mais interna). A consulta pai só é avaliada depois que a consulta filha tiver sido completamente avaliada.
As consultas correlacionadas têm a mesma sintaxe, com a seguinte diferença: a consulta filha realiza uma junção com a tabela da consulta mãe. Nesse caso, o conjunto consulta-mãe/consulta-filha é avaliado repetidamente para cada linha da tabela mãe.
Exemplo
Retomamos o exemplo em que queremos os nomes dos clientes que não fizeram nenhum pedido 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 é executada na tabela clients. A consulta secundária realiza uma junção entre as tabelas clients e commandes. Portanto, temos uma consulta correlacionada. Para cada linha da tabela clients, a consulta filha é executada: ela procura o código id do cliente nos pedidos feitos após 24 de setembro. Se não encontrar (not exists), o nome do cliente é exibido. Em seguida, passa-se para a próxima linha da tabela clients.
6.2.6. Critérios de seleção para a consulta SELECT
Vimos, em várias ocasiões, que era possível obter o mesmo resultado por meio de diferentes gravações na tabela SELECT. Vejamos um exemplo: exibir os clientes que fizeram algum pedido:
Junção
Consultas aninhadas
dá o mesmo resultado.
Consultas correlacionadas
SQL>
select nom from clients
where exists (select * from commandes where commandes.idcli=clients.id)
dá o mesmo resultado.
Os autores Christian MAREE e Guy LEDANT, em seu livro ‘SQL, Introdução, Programação e Domínio’, propõem alguns critérios de escolha:
Desempenho
O usuário não sabe como o SGBD “consegue” encontrar os resultados que ele solicita. Portanto, somente por meio da experiência ele descobrirá que determinada sintaxe é mais eficiente do que outra. MAREE e LEDANT afirmam, com base na experiência, que as consultas correlacionadas geralmente parecem mais lentas do que as consultas aninhadas ou as junções.
Formulação
A formulação por consultas aninhadas costuma ser mais legível e intuitiva do que a junção. No entanto, nem sempre é viável. Há dois pontos que merecem atenção especial:
- As tabelas que contêm as colunas argumentos do SELECT (SELECT col1, col2, ...) devem ser nomeadas após a palavra-chave FROM. O produto cartesiano dessas tabelas é então realizado, o que se denomina junção.
- Quando a consulta exibe resultados provenientes de uma única tabela e a filtragem das linhas dessa tabela exige a consulta a outra tabela, podem ser utilizadas consultas aninhadas.
6.3. Extensões de sintaxe
Por uma questão de conveniência, apresentamos na maioria das vezes sintaxes reduzidas dos diversos comandos. Nesta seção, apresentamos sintaxes ampliadas. Elas são intuitivas, pois são análogas às do comando SELECT, amplamente estudado.
INSERT
INSERT INTO table (col1, col2, ..) VALUES (val1, val2, ...) | |
INSERT INTO table (col1, col2, ...) (requête) | |
Essas duas sintaxes foram apresentadas |
DELETE
DELETE FROM table WHERE condition | |
Essa sintaxe é conhecida. Acrescentemos que a condição pode conter uma consulta com a sintaxe WHERE expressão operador (consulta) |
UPDATE
UPDATE table SET col1=expr1, col2=expr2, ... WHERE condition | |
Essa sintaxe já foi apresentada. Acrescentemos que a condição pode conter uma consulta com a sintaxe WHERE expressão operador (consulta) |
UPDATE table SET (col1, col2, ..) = consulta1, (cola, colb, ..) = consulta2, ... WHERE condition | |
Os valores atribuídos às diferentes colunas podem ser provenientes de uma consulta. |