Executando Consultas sobre as Tabelas 2

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 pauloor (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 Acoand 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);