Select SQL uma Visao Geral LINGUAGEM ESTRUTURADA DE

Transcrição

Select SQL uma Visao Geral
LINGUAGEM ESTRUTURADA DE CONSULTA
O programador que chega no Visual FoxPro , vindo de linguagens padrao Xbase (Foxpro 2.x,
Clipper , Dbase, etc) acaba encontrando na linguagem SQL , uma grande aliada, e apesar dos
comandos serem diferentes do Xbase , existe muita coisa que continuará igual, porque o
Visual FoxPro possui linguagem SQL de forma nativa, basta abrir a Janela de comandos e
sair digitando instrucoes SQL. Nos tempos de Clipper por exemplo, o programador estava
acostumado a fazer "perguntas" para o Banco de dados usando os comandos USE , SET
FILTER , SET RELATION , SET ORDER , BROWSE etc. Tudo isto continua exatamente
igual no Visual FoxPro. Com a linguagem SQL você também faz tudo isto, e mais , com
resultados bem melhores, e com a vantagem que já estaremos nos preparando para
desenvolver uma aplicacao cliente/servidor.
O que devemos observar, e nunca confundir, é SQL a linguagem , com SQL SERVER , que é
um Banco de Dados da Microsoft. Mas nao nos preocupemos com este Banco de dados SQL
agora, porque dentro do Visual FoxPro, no SQL nativo, existe o "assistente de upsizing do
Sql Server" que vai se encarregar de criar um Banco de dados SQL Server , aonde podemos
mover dados locais (DBF) para um servidor remoto, transformando as tabelas de base local
(DBF) e suas visualizacoes locais, em tabelas de base remota e visualizacoes remota,
migrando assim, de um aplicativo local (Prg, Dbf) para um aplicativo cliente/servidor (Web).
Alguns conceitos , como o de "abrir"uma tabela de dados, irao mudar o conceito, porque
passaremos a nos preocupar com um servidor remoto, e em se falando de servidor remoto,
falaremos entao em fazer "conexao" com uma fonte de dados.
Já fomos muito além do que pretendiamos nesta breve introdução.
Voltando a uma aplciacao local, vamos nos deter na instrucao SQL SELECT ou SELECT
SQL, que é muito poderosa, e podemos sair escrevendo ela dentro da janela de comandos.
Select é a ligítima instrução de consulta, e ao escrever precisamos : definir qual a pergunta
deve ser respondida pelo banco de dados (tabela) e o que é que queremos saber do banco
(tabela).
Create PDF files without this message by purchasing novaPDF printer (http://www.novapdf.com)
Exemplificando : Uma pergunta do tipo : Qual minha lista de devedores ? , nao seria nada
criativo, e seria bem melhor se fosse perguntado por exemplo, "Preciso saber nome,
endereco, e quando cada um dos meus clientes está devendo, ordenado por data do
vencimento"
Tudo o que precisamos é aprender a perguntar ao banco de dados (tabela). Nos exemplos que
colocarei, deve ficam bem clarom que sempre existirao diversas formas para se atingir um
objetivo, e vamos usar a que mais fácil parecer ao aluno.
A partir daqui, imaginemos que existe uma tabela de nome "CLIENTES" com os camposque
passaremos a nos referenciar nos exemplos.
Então vamos a parte bem prática : Perguntando e Respondendo :
. Quero todos os dados de todos os clientes , nao importa a ordem.
Resposta
Select * from clientes
O sinal * indica todos (todos os campos da tabela)
Já que nenhuma filtragem foi solicitada, todos os registros da tabela serao mostrados.
Antes mesmo de continuar, é interessante lembrar que os comandos VFP podem ser escritos
em mais de uma linha, e o sinal ; (Ponto e virgula) deve ser colocado ao final de uma linha,
indicando que o comando continua na proxima linha.
.Quero o nome de todos os clientes , independente de ordem.
Resposta
Select nome as nome_do_cliente from clientes
Nome é o próprio nome do campo, nome_do_cliente , é como eu quero que saia no cabeçalho
da consulta.
.Quero o nome da empresa e o nome do contato, de todos os clientes, ordenado por nome da
empresa.
Resposta
Select nome_empresa , nome_contato from clientes order by 1
By 1 significa que vai sair em ordem pelo campo 1 da lista (Nome_empresa)
. Quero a regiao , nome da empresa e o nome do contato, de todos os clientes, ordenados por
regiao e pelo nome da empresa.
Resposta
Select regiao,nome_da_empresa,nome_contato;
from clientes order by regiao,nome_da_empresa
Na clausula order by, se precisarmos ordenar por mais campos, basta escreve-los, separando
por virgulas, e evidentemente que estará uma ordem dentro da outra. No exemplo acima, os
nomes das empresas estarao em ordem crescente dentro de cada regiao.
.Quero a lista de todas as regioes onde tenho meus clientes.
Esta pergunta, vai nos fazer usar de uma clausula GROUP BY. Acontece que existem muitos
registros que serao mostrados e tem uma grande quantidade de regioes diferentes.
Resposta
Select regiao from clientes group by regiao
E tudo isto porque : porque queremos que o nome de uma regiao saia apenas uma vez, e por
isto , usamos o agrupamento (Group By)
. Quero saber quantos clientes eu tenho em cada regiao.
Esta já é uma pergunta mais completa.
Resposta
Select regiao , count(regiao) as contagem;
from clientes group by regiao
Count(regiao) : está indicando que eu quero uma contagem (Funcao Count())
As contagem : foi necessário para identificar um cabeçalho para este total que será mostrado.
. Quero saber o nome da empresa e nome do contato de todos os clientes , ordenado pelo
nome da empresa.
Resposta
Select nome_empresa, nome_contato;
from clientes order by 1
. Quero saber quantos clientes eu tenho por regiao, porém como podem existir regioes em
"branco", nao quero que apareça aquele cabeçalho "regiao sem nome", e para mostrar uma
busca mais clara, escrevo :
Resposta
Select IIF(not empty()regiao), regiao,;
"regiao nao encontrada") as regiao , count(regiao);
as contagem from clientes group by regiao
A função IIF possibilita que se o campo "regiao" nao estiver vazio, utilizamos o seu
conteudo, senao colocamos um "regiao nao informada" no registro.
. Agora umaa pergunta mais complexa. Na tabela clientes, existe um outro campo chamado
Valor_maior , indicando de quanto foi o valor do maior pedido efetuado por um cliente.
Quero entao saber qual foi o valor mais alto já pedido em cada regiao, da regiao com valor
mais baixo, até a regiao com valor mais alto.
Resposta
Select regiao, max(valor_maior) as maior_pedido;
from clientes group by regiao order by 2
Sairão as regioes, e dentro das regioes, cfe solicitado, em ordem do menor valor ao maior
valor.
. Mas no exemplo anterior, veio muita coisa, e eu quero montar o meu ranking top 10, com as
10 regioes que tiverem os maiores valores de pedidos, da regiao com o maior pedido para a
regiao com menor pedido (ou seja , ordem decrescente)
Resposta
Select top 10 regiao, max(valor_maior);
as maior_pedido from clientes;
group by regiao order by 2 desc
Ficou tudo mais ou menos igual, apenas que a clausula TOP 10 indica que serao mostrados os
10 maiores.
Importante : Quando voce usa a clausula TOP , é "obrigatório" definir uma "ordem" (order
by)
. Agora, uma coisa mais complexa ainda. A empresa vai fazer uma promocao para 20% das
regioes de maior valor de pedido feito, e veja como solicito isto.
Resposta
Select top 20 percent regiao,;
max(valor_maior) as maior_pedido;
from clientes group by regiao order by 2 desc
Neste caso, como existe a clausula percent , o 20 significa 20 %
. Preciso saber o nome dis meus clientes na regiao de Santa maria
Resposta
Select nome_empresa from clientes ;
where regiao = "Santa maria"
A clausula Where, faz a filtragem mostrando apenas clientes que seja da regiao citada (Santa
Maria, no caso)
. Além de "Santa Maria"também quero, "Santa Rosa"
Resposta
Select nome_empresa, regiao from clientes;
where regiao = "Santa Maria" or ;
regiao = "Santa Rosa"
A unica diferença em relaçao a consulta anterior, é que esta pede duas regioes, entao na
filtragem uso a clausula OR ( OU )
. A empresa precisa agora da lista com os nomes dos clientes em 5 regioes : "sp", "rj" , "pa" ,
"pr" e "ms" .
Resposta
Select nome_empresa , regiao from clientes;
where inlist (regiao,"sp","rj","pa","pr","ms")
Acrescentou-se a clausula INLIST e nomeamos as regioes solicitadas.
Importante : Com criatividade, dentro de um prg, podemos informar quais as 5 regioes, jogar
para variáveis rg1,rg2,rg3.rg4,rg5 e escrever o select assim :
Select nome_empresa , regiao from clientes;
where inlist(regiao,rg1,rg2,rg3,rg4,rg5)
Mais ..
No caso INLIST (regiao "sp","rj","pa","pr","ms") e se esta lista fosse maior !! Neste caso use
a funcao INLIST() , voce diz para ela qual é o campo e qual a lista de valores que voce deseja
procurar, e estará resolvido.
. A empresa está fazendo uma grande promocao para os clientes, e quer presentear aqueles
que estiverem entre os registros 10 e 20 do cadastro.
Resposta
Select recno(), nome_empresa from clientes;
where between(recno(),10,20)
Estamos entao usando a funcao RECNO() , e na filtragem colocando
BETWEEN(recno(),10,20) indicando que devem ser mostrados apenas os clientes entre o
registro 10 e o 20 ( 10,11,12,13,14,15,16,17,18,19,20)
. Agora a empresa quer saber o nome dos clientes, cujo nome da empresa começa com a letra
"S"
Resposta
Select * from clientes ;
where nome_empresa like "S%"
Apenas foi usada na filtragem a condicao LIKE, indicando que só vai mostrar, do
nome_empresa , os que iniciarem por "S" (% significa que os restantes caracteres do nome
nao importa)
. Agora, a empresa vai convocar uma reuniao com todos os gerentes dos clientes. Precisa
entao uma listagem com o nome dos gerentes e o telefone.
Resposta
Select gerente, telefone from clientes;
where upper(cargo) like "%gerente%"
Veja :
No campo "cargo" vai estar gravado por exemplo, "Gerente de vendas" ou "Socio gerente de
filial" , exemplos apenas, e neste caso o LIKE "%gerente%" vai pegar qualquer tipo de
ocorrencia, desde que em algum lugar esteja gravado a palavra "gerente" (Por isso o sinal de
% antes e depois )
Upper(cargo) significa que ele vai passar o conteudo do campo CARGo para maiusculo, para
evitar problemas nas comparacoes, maiusculas e minusculas.
. A empresa quer saber : Listar as regioes onde tem mais de 2 clientes, desconsiderando
aqueles clientes que nao possuem regiao informada.
Resposta
Select regiao, count(*) as contagem;
from clientes;
where not empty(regiao);
group by regiao having contagem > = 3
Veja :
Count(*) joga o resultado para uma variavel CONTAGEM e no final a clausula HAVING faz
um filtro, especificando que a contagem precisa ser > ou igual a 3.
. A empresa quer ver a lista de clientes, datas de pedidos de cada um, ordenada pelo nome da
empresa e pela data do pedido.
Resposta
Select clientes.nome_empresa,;
pedidos.data_pedido;
from clientes,pedidos;
where clientes.codigo_cliente = pedidos.codigo_cliente;
order by clientes.nome_empresa,pedidos.data_pedido
Existem duas tabelas ; a tabela Clientes e a Pedidos e serão consultadas em paralelo. A tabela
pedidos possui os pedidos todos, e a clientes os nomes dos clientes.
Na clausula from , preciso referenciar as duas tabelas.
Na clausula WHERE , voce estabeleceu que as duas tabelas vao correr juntas, sendo unidas
pelo campo codigo_cliente que existe nas duas tabelas.
E o order By , especifica que a ordem de amostragem será por nome do cliente, e dentro de
cada cliente por data do pedido.
. A empresa precisa ver a lista de clientes e datas de pedidos de cada um (duas tabelas)
ordenada pelo nome da empresa e pela data do pedido.
Resposta
Select a.nome_empresa, b.data_pedido;
from clientes a , pedidos b;
where a.codigo_cliente = b.codigo_cliente;
order by nome_empresa,data_pedido
Só o que mudou foi o fato de terem sido associadas a cada tabela, as letras A e B , A para
Clientes e B para Pedidos. (A e B sao conhecidos como local aliás)
. Agora a empresa precisa ver a lista de clientes e datas de pedidos de cada um, ordenada pelo
nome da empresa e pela data do pedido.
Resposta
Select clientes.nome_empresa,pedidos.data_pedido;
from clientes;
inner join pedidos on ;
clientes.codigo_cliente = pedidos.codigo_cliente;
Sobre o INNER JOIN
Uma nova forma de fazer JUNÇÃO entre tabelas. Temos duas tabelas , uma está a esquerda
(clientes) e outra do lado direito (pedidos). Com o INNER JOIN , vamos saber, quais os
registros do lado esquerdo que encontraram pelo menos 1 registro equivalente do lado direito.
Assim sendo, só veremos os clientes que efetivaram algum pedido.
. A empresa quer a mesma coisa do exemplo anterior, só que agora, esta lista tem que trazer
todo mundo, mesmo que o cliente nao tenha feito pedido algum.
Resposta
Select clientes.nome_cliente, pedidos.data_pedido;
from clientes;
LEFT JOIN pedidos;
on clientes.codigo_cliente = pedidos.codigo_cliente;
Veja :
Substituindo INNER JOIN pelo LEFT JOIN , faz com que a consulta retorne todos os
registros do lado esquerdo da juncao, independente de encontrar registro equivalente do lado
direito.
. Agora a empresa quer saber, a quantidade total de pedidos atendidos por cada funcionário.
A lista tem que ser ordenada pelo nome dos funcionários. Vamos colocar outra tabela na
jogada, a tabela "funcionários"
Resposta
Select funcionarios.primeiro_nome_do_funcionario ,;
count(pedidos.codigo_cliente) as qtde_total_pedidos;
from pedidos;
inner join funcionarios ;
on funcionarios.codigo_funcionario = pedidos.codigo_funcionario;
group by funcionarios.primeiro_nome_do_funcionario;
order by funcionarios.primeiro_nome_do_funcionario
É muito fácil de entender, porque todas as clausulas já foram praticadas.
. Bem , nesta lista nao aparecem todos os funcionários, está faltando alguns, e a empresa quer
que apareçam.
Então :
Resposta
Select funcionarios.primeiro_nome_do_funcionario,;
count(pedidos,codigo_cliente) as qtde_total_pedidos ;
from pedidos;
right join funcionarios on funcionarios.codigo_funcionario;
= pedidos.codigo_funcionario;
group by primeiro_nome_do_funcionario;
order by primeiro_nome_do_funcionario
Falamos a alguns exercicios atrás, que LEFT JOIN traz todos os registros do lado esquerdo
da relacao, lembra. Pois entao, também temos o RIGHT JOIN , que traz todos os registros do
lado direito da relacao, ou seja , neste caso, traz todos os funcionarios , independente de
existir algum pedido por ele atendido ou nao. No exercicio anterior, aqueles que nao
atenderam a nenhum pedido, ficaram de fora.
. Agora a empresa quer saber quem sao os funcionarios que nao atenderam nenhum pedido.
Resposta
Select funcionarios.primeiro_nome_do_funcionario;
from funcionarios;
where funcionarios.codigo_funcionario not IN ;
(select funcionarios.codigo_funcionario from pedidos)
Agora voce vai aprender a usar sub-queries (Sub consulta). A essência da pergunta é : da lista
de funcionários, quem é que nao tem nenhum pedido atendido !
Fazemos entao o select na tabela funcionarios, filtrando por somente aqueles que o
codigo_funcionario nao estiver na tabela pedidos.
Inicialmente a sub consulta é executada, para se saber quais sao os codigo_funcionario na
tabela de pedidos, e em seguida é executada a consulta na tabela de funcionarios, procurando
somente os codigo_funcionario que nao estao no resultado da primeira consulta.
Quantas linhas de comando voce terá ganho em clipper, para fazer uma consulta destas !
Algumas dezenas com certeza.
. A empresa agora quer fazer um agrado para os funcionarios mais fiéis e produtivos. Para
isto, precisa saber quem sao os funcionarios que atenderam mais de 100 pedidos ou que
foram contratados de 1993 até hoje.
Resposta
Select alltrim(funcionarios.primeiro_nome_do_funcionario);
+" "+funcionarios.ultimo_nome_do_funcionario;
from pedidos;
inner join funcionarios on ;
funcionarios.codigo_funcionario = ;
pedidos.codigo_funcionario ;
group by funcionarios.primeiro_nome_do_funcionario;
having count(pedidos.codigo_pedido) > 100 ;
union;
select alltrim(primeiro_nome_do_funcionario)+;
" "+ultimo_nome_do_funcionario from;
funcionarios;
where year(data_admissao) > = 1993
Parece que a coisa complicou, mas nao é bem assim. Verifique, temos duas grandes
condicoes :
1 : Funcionários que atenderam mais de 100 pedidos. Voce já sabe fazer isso através do
having e do count() ( A novidade aqui, é que o count() pode ser usado diretamente junto do
having)
2 : Funcionários que foram contratados de 1993 até o dia atual. ( A unica novidade aqui é o
uso da funcao YEAR() , para ver o ano da data da contratacao.
A grande novidade, está na clausula UNION. Esta clausula UNE consultas. Entao o ideal é
criar a primeira consulta, e testaremos. Funcionou ? criamos a segunda consulta, e testamos ,
funcionou ?
Unimos entao as duas com a clausula UNION. Ela junta os registros das duas consultas , e
despreza registros repetidos ( Neste caso, o funcionário que atende aos dois critérios) Se
quiser também os registros repetidos, basta usar UNION ALL.
Como um a consulta é agregada a outra, será necessário que ambas produzam uma estrutura
igual, caso contrárioé gerado um erro.
. Agora a empresa quer uma listagem, uma unica listagem, com nome de todos os
funcionários e contatos de clientes, para mandar um cartao de natal no final do ano.
Resposta
Select padr(primeiro_nome_do_funcionario,30," ") ;
as nome , "funcionario" as tipo ;
from funcionarios;
union;
select nome_do_contato, "cliente" as tipo;
from clientes;
order by 2 , 1
A diferença é que neste caso, somaremos registros de duas tabelas diferentes.
Já falamos que a estrutura das tabelas tem de ser iguais. Entao o campo
primeiro_nome_do_cliente na tabela funcionários é do tipo Caracter, tamanho 10. Já o campo
nome_do_contato na tabela clientes também é do tipo Caracter, só que o tamanho é 30, e
agora como fica !
Lembre-se de uma regra : A primeira consulta é que dita a estrutura das demais em uma
instrucao com UNIONs ( é isso mesmo, podemos ter várias, várias consultas ligadas por
vários UNIONs). Então precisamos dar um jeito no campo primeiro_nome_do_funcionario ,
para que ele fique igual ao nome_do_contato. Para isto basta usar a funcao PADR() que
preenche um string com um numero X de um determinado caractere, no nosso caso,
preenchemos com espaços vazios, até que a string alcance o tamanho 30. Ok, agora elas já
sao compatíveis.
A clausula ORDER BY aqui só pode ser usada com o numero das colunas na lista, e nao pelo
nome dos campos, pois podem ser completamente diferentes entre as consultas.
Daqui para frente, quem quiser se especializar ainda mais, use o HELP do VFP.
21 de Agosto de 2003

Select SQL uma Visao Geral LINGUAGEM ESTRUTURADA DE

Transcrição

Documentos relacionados

Page 1 Governo da Regi函oAdministrativa Especialde Macau

Constantemente a Evoluir Interface Gráfico Pedidos

Untitled - Hotel Adagio

2009NE000700 - Tribunal Regional do Trabalho 7ª Região

2008NE000431 - Tribunal Regional do Trabalho 7ª Região

PROFESSOR DE HISTÓRIA – DISTRITO DE CASTELO DOS

Aviso de 03/12/2013 - Ministério Público do Estado do Pará

Ilustre Filósofo Dr,José Ferrater Mora: cordlals e respeitosas

ESCOLA TÚLLIO: TODO DIA ERA DIA DE ÍNDIO

6.Como formatar HD