Operadores Lógicos
AND conjunção
OR disjunção
NOT negação
Problema:
Quais são os produtos que têm unidade igual a ‘kg’ e valor unitário maior do que R$ 2,00?
select desc_prod from produto where unid_prod = ‘kg’ and val_unit > 2.00;
Problema:
Liste todos os clientes localizados na cidade de São Paulo ou que tenham CEP entre 20000005 e 20000010.
select nome_cli,cidade,cep from cliente where cidade = ‘sao paulo‘ or (cep>=’20000005′ and cep<=’20000010′);
Observação:
A prioridade do operador AND é maior do que a prioridade do operador OR; logo, neste exemplo, a utilização dos parênteses é opcional.
Problema:
Mostrar todos os pedidos que não tenham prazo de entrega superior a 15 dias.
select num_ped from pedido where not (prazo_entr > 15);
Operadores BETWEEN e NOT BETWEEN
WHERE <nome_coluna> BETWEEN <valor1> AND <valor2>
WHERE <nome_coluna> NOT BETWEEN <valor1> AND <valor2>
Este operador possibilita a seleção de uma faixa de valores sem a necessidade
do uso dos operadores >=, <= e AND.
<valor1> e <valor2> têm que ter o mesmo tipo de dado que <nome_coluna>.
Problema:
Liste o código e a descrição dos produtos que tenham o valor unitário na faixa de R$ 0,10 a R$ 3,00.
select cod_prod,desc_prod from produto where val_unit between 0.10 and 3.00;
Operadores LIKE e NOT LIKE
WHERE <nome_coluna> LIKE <valor>
WHERE <nome_coluna> NOT LIKE <valor>
Aplicáveis apenas a colunas dos tipos CHAR e VARCHAR.
Funcionam de modo análogo aos operadores = e <>, porém o poder dos operadores LIKE e NOT LIKE está na utilização dos símbolos % e _, que podem fazer o papel de “coringa”:
% substitui uma palavra
_ substitui um caracter qualquer
Exemplos:
‘apis%’ se aplicaria às seguintes cadeias de caracteres:
‘lapis preto’
‘lapis cera’
‘lapis borracha’
‘broca n_’ se aplicaria às seguintes cadeias de caractere:
‘broca n1’
‘broca n9’
‘broca n3’
Problema:
Listar todos os produtos que tenham a sua unidade começando por k (lembre-se de que a coluna unid_prod foi definida como char(03)).
select cod_prod,desc_prod from produto where unid_prod like ‘k__’;
Problema:
Listar todos os vendedores cujos os nome não comecem por ‘A’.
select cod_vend,nome_vend from vendedor where nome_vend not like ‘A%’;
Operadores IN e NOT IN
WHERE <nome_coluna> IN <lista_de_valores>
WHERE <nome_coluna> NOT IN (<lista_de_valores>)
Seleciona as linhas cujo o valor da coluna <nome_coluna> pertença ao conjunto <lista_de_valores>.
Problema:
Listar todos os vendedores cujas as faixas de comissão sejam ‘a’ ou ‘b’.
select cod_vend,nome_vend from vendedor where faixa_comiss in (‘a’,’b‘);
Operadores IS NULL e IS NOT NULL
WHERE <nome_coluna> IS NULL
WHERE <nome_coluna> IS NOT NULL
A utilização do valor nulo (NULL) é problemática, pois as diversas implementações da linguagem SQL podem adotar qualquer representação para o valor nulo.
Problema:
Mostrar os clientes que não tenham endereço cadastrado.
select nome_cli from cliente where endereco is null;
Ordenando os Dados Selecionandos
SELECT <lista_de_colunas> FROM <nome_tabela> WHERE <condição_de_seleção>
ORDER BY {<nome_coluna>|<num_col> [ASC|DESC]} Onde <nome_coluna> se refere à coluna segundo a qual as linhas serão ordenadas, e <num_col> se refere à posição relativa da coluna a <lista_de_colunas> projetadas, contada da esquerda para a direita, e não à posição na tabela original.
As cláusulas ASC e DESC denotam ordenação ascendente e descendente respectivamente. A forma ascendente de ordenação é assumida caso nenhuma opção seja informada explicitamente.
Problema:
Mostrar em ordem alfabética a lista de vendedores e seus respectivos salários fixos.
select nome_vend,sal_fixo from vendedor order by nome_vend;
Problema:
Listar os nomes, as cidades e os estados de todos os clientes, ordenados por estado e cidade de forma descendente.
select nome_cli,cidade,uf from cliente order by uf desc,cidade desc;
Problema:
Mostrar a descrição e o valor unitário de todos os produtos que tenham unidade ‘kg’ em ordem ascendente de valor unitário.
select desc_prod,val_unit from produto where unid_prod = ‘kg’ order by 2;
Realizando Cálculos sobre a Informação Selecionada
Podemos criar dinamicamente um campo que não pertença à tabela original através de operações executadas sobre os campos projetados.
Problema:
Exibir o novo salário fixo dos vendedores da faixa de comissão ‘C’, calculado com base no reajuste de 75% sobre o salário atual acrescido de R$ 120,00 de bonificação. Ordene a relação resultante pelo nome do vendedor.
select nome_vend,((sal_fixo*1.75)+120) as novo_sal from vendedor where faixa_comiss=’c’ order by nome_vend;
Máximos (MAX) e Mínimos (MIN)
Problema:
Mostrar o menor e o maior salário entre os vendedores.
select MIN(sal_fixo),MAX(sal_fixo) from vendedor;
Totalizando Colunas (SUM)
Problema:
Mostrar a quantidade total pedida para o produto cimento, de código 200.
select SUM(qtd_ped) from item_pedido where cd_prod=200;
Calculando Médias (AVG)
Problema:
Qual é a média dos salários fixos dos vendedores?
select AVG(sal_fixo) from vendedor;
Contando as Linhas (COUNT)
Problema:
Quantos vendedores ganham acima de R$ 2.000,00 de salário fixo?
select COUNT(*) from vendedor where sal_fixo>2000;
A Palavra-Chave DISTINCT
Várias linhas de uma tabela podem conter os mesmos valores para as suas colunas duplicidade), com exceção da chave primária. Quando desejarmos eliminar a duplicidade, podemos inserir a palavra-chave DISTINCT após a palavra-chave SELECT.
Problema:
Em que cidades as nossa empresa possui clientes?
select DISTINCT cidade from cliente;
Agrupando a Informação Selecionada (GROUP BY)
Existem ocasiões em que desejamos aplicar uma função de agregação não somente a um conjunto de tuplas, mas também organizar a informação em determinadas categorias. Isto é possível através do uso da cláusula GROUP BY.
Problema:
Listar o número de itens existente em cada pedido.
select no_ped, count(*) as total_itens from item_pedido group by no_ped;
Inicialmente as linha são agrupadas através do(s) atributo(s) fornecido(s) na cláusula GROUP BY; neste caso, no_ped. Em um segundo passo, é aplicada a operação COUNT(*) para cada grupo de linhas que tenha o mesmo número de pedido. Após a operação de contagem de cada grupo, o resultado da consulta é apresentado.
Normalmente, a cláusula GROUP BY é utilizada em conjunto com as
operações COUNT e AVG.
A Cláusula HAVING
Às vezes temos que definir condições e aplicá-las aos grupos ao invés de fazê-lo a cada linha separadamente. Por exemplo, suponha que desejemos listar todos os pedidos que possuam mais de um item. Esta condição não se aplica a uma única linha separadamente, mas a cada grupo definido pela cláusula GROUP BY. Para exprimir tal consulta, usamos a cláusula HAVING. A condição da cláusula HAVING é aplicada após a formação dos grupos; logo, podemos usar funções de agregação na construção das condições da cláusula HAVING.
Problema:
Listar os pedidios que possuam mais de um item.
select no_ped,count(*) as total_itens from item_pedido group by no_ped having count(*)>1;
Recuperando Dados de Várias Tabelas (JOINS)
Algumas consultas necessitam acessar simultaneamente várias tabelas, o que leva à realização de junções (JOINS) entre as tabelas para poder extrair as informações necessárias para a consulta formulada.
Qualificadores de Nomes
Um qualificador de nome consiste do nome da tabela, seguido de um ponto, seguido por um nome de uma coluna da tabela. Por exemplo, o qualificador da coluna DESC_PROD da tabela PRODUTO será PRODUTO.DESC_PROD. Os qualificadores de nome são utilizados em uma consulta para efetivar a junção (JOIN) entre as tabelas.
Problema:
Faça uma junção da tabela de clientes com a de pedidos, exibindo o nome do
cliente, o código do cliente e o número do pedido.
select cod_cli,nome_cli,pedido.num_ped from cliente,pedido;
Neste exemplo foi executado um produto cartesiano das tabelas CLIENTE e PEDIDO, seguido de uma projeção das colunas exibidas. Neste caso, poucas informações úteis podem ser extraídas da relação resultante. Devemos então aplicar critérios de seleção à junção para podermos obter algum resultado concreto.
Problema:
A que clientes estão associados os pedidos existentes? Listar pelos nomes dos clientes.
select nome_cli,pedido.cd_cli,pedido.num_ped from cliente,pedido Where cliente.cod_cli=pedido.cd_cli;
A equação apresentada na cláusula WHERE é chamada de EQUAÇÃO DE JUNÇÃO.
Podemos utilizar os operadores LIKE, NOT LIKE, IN, NOT IN, NULL, NOT NULL, os operadores relacionais e operadores AND, OR e NOT, na cláusula WHERE de uma junção de tabelas.
Problema:
Quais são os clientes que têm pedidos com prazos de entrega superiores a 15 dias e que estão localizados nos estados de São Paulo ou do Rio de Janeiro?
select nome_cli,uf,pedido.num_ped,pedido.prazo_entr from cliente,pedido where cliente.cod_cli=pedido.cd_cli and uf in (‘rj‘,’sp‘) and prazo_entr>15;
Problema:
Mostrar os pedidos dos clientes e seus respectivos prazos de entrega,
ordenados do maior para o menor.
select nome_cli,pedido.num_ped,pedido.prazo_entr from cliente,pedido where cliente.cod_cli=pedido.cd_cli order by prazo_entr DESC;
Sinônimos
Para que não seja necessário escrever o nome da tabela nas qualificações de nomes, podemos utilizar ALIASES definidos na própria consulta. A definição dos ALIASES é feita na cláusula FROM, sendo então utilizada nas outras cláusulas (WHERE, ORDER BY, GROUP BY, HAVING e
SELECT) de uma consulta.
Problema:
Exibir os vendedores (ordenados por nome) que emitiram pedidos com prazos de entrega superiores a 15 dias e que tenham salários fixos iguais ou superiores a R$ 1.000,00.
select distinct nome_vend,prazo_entr from vendedor V, pedido P where V.cod_vend=P.cd_vend and V.sal_fixo>1000 and prazo_entr>15 order by nome_vend;
Junções Envolvendo Três ou mais Tabelas
Problema:
Exiba a relação dos clientes localizados no Rio de Janeiro (ordenados alfabeticamente) que têm pedidos do produto Chapa de Aco com prazos de entrega superirores a 15 dias.
select nome_cli,desc_prod,no_ped,prazo_entr from cliente C,pedido P,item_pedido IP,produto PR where C.cod_cli=P.cd_cli and P.num_ped=IP.no_ped and IP.cd_prod=PR.cod_prod and PR.desc_prod=’Chapa de Aco‘ and P.prazo_entr>15 and
C.uf=’rj‘ order by C.nome_cli;
Problema:
Mostre os nomes de todos os vendedores que venderam Chapa de Aço em quantidade superior a 300 Kg.
select distinct nome_vend from vendedor V, pedido P, item_pedido IP, produto PR
where V.cod_vend=P.cd_vend and p.num_ped=IP.no_ped and IP.cd_prod=PR.cod_prod and
IP.qtd_ped>300 and PR.desc_prod=’Chapa de Aco‘;
Problema:
Quantos clientes fizeram pedidos com a vendedora Ana Cristina?
select count(distinct cod_cli) from cliente C, pedido P, vendedor V where C.cod_cli=P.cd_cli and p.cd_vend=V.cod_vend and V.nome_vend=’Ana Cristina’;
Problema:
Quantos clientes das cidades do Rio de Janeiro e Niterói tiveram seus pedidos tirados com a vendedora Ana Cristina?
select C.cidade,count(distinct cod_cli) as num_clientes from cliente C, pedido P, vendedor V
where C.cod_cli=P.cd_cli and C.cidade in (‘rio de janeiro’,’niteroi‘) and p.cd_vend=V.cod_vend and V.nome_vend=’Ana Cristina’ group by C.cidade;
Utilizando Consultas Aninhadas (Subqueries)
Chamamos de consulta aninhada à consulta cujo o resultado é utilizado por
outra consulta, de forma encadeada e contida no mesmo comando SQL.
Problema:
Que produtos estão incluídos em um pedido qualquer com a quantidade
pedida igual a 100?
select desc_prod from produto where cod_prod IN (select cd_prod from item_pedido where qtd_ped=100);
Problema:
Quais vendedores ganham um salário fixo abaixo da média?
select nome_vend from vendedor where sal_fixo < (select avg(sal_fixo) from vendedor);
Problema:
Quais os vendedores que só venderam produtos comercializados em
quilogramas (Kg)?
SELECT DISTINCT cod_vend,nome_vend FROM vendedor V, pedido p, item_pedido ip, produto pr WHERE p.num_ped=ip.no_ped AND ip.cd_prod=pr.cod_prod AND p.cd_vend=v.cod_vend AND pr.unid_prod=’kg’
Problema:
Quais clientes realizaram mais de dois pedidos?
select nome_cli from cliente C where exists (select count(*) from pedido where cd_cli=C.cod_cli having count(*)>2);
Problema:
Quais os produtos que não estão presentes em nenhum pedido?
select cod_prod,desc_prod from produto P where not exists (select * from item_pedido
where cd_prod=P.cod_prod);