Linguagem e Técnica de Programação - SQL
SQL - Structured Query Language
Linguagem de Consulta Estruturada
Respostas dos Exercícios:
AlunosI AlunosII DuplicatasI DuplicatasII Funcionários Join Especiais Insert/Update/Delete
1.1.Introdução ao SQL
SQL é a linguagem padrão da indústria de bancos de dados relacionais. O padrão ANSI
a adotou em 1986. Os principais Bancos de Dados Relacionais aceitam alguma forma do SQL,
procurando estar no padrão ANSI. Abaixo, algumas terminologias em Banco de Dados:
. Tabela - Estrutura básica de armazenamento de dados;
. Linha - Combinação dos valores das colunas em uma tabela.
O mesmo que registro;
. Coluna - Representa um tipo de dados em uma tabela.
É descrita por um nome de coluna;
. Atributo - área que pode conter um tipo de dados. É a interseção de uma
linha com uma coluna. O mesmo que campo;
. Chave Primária - É a coluna que identifica com exclusividade cada linha de
uma tabela;
. Chave Estrangeira - É uma coluna ou conjunto de colunas referente a uma chave
primária de uma outra tabela. A partir da chave estrangeira
podemos relacionar tabelas (join).
Início da Página
1.2.Funções do VFP
Durante a execução de uma consulta, podemos escolher o formato de exibição do campo.
Para isso utilizamos algumas funções do aplicativo utilizado. Lembre-se que o SQL é
utilizado em vários aplicativos, e cada aplicativo possui suas próprias funções. As funções
de conversão de dados do VFP são:
| Função | Descrição | Exemplo |
| DATE() | Retorna data atual do Sistema | DATE() |
| DTOC() | Converte campo data para caracter | DTOC(nascimento) |
| DAY() | Retorna apenas dia do campo data | DAY(nascimento) |
| MONTH() | Retorna apenas mês do campo data | MONTH(nascimento) |
| YEAR() | Retorna apenas ano do campo data | YEAR(nascimento) |
| SUBSTR() | Retorna uma parte do campo caracter | SUBSTR(nome,1,20)
| VAL() | Converte campo caracter para numérico | VAL(cidade) |
As funções podem ser usadas tanto no SELECT quanto na cláusula WHERE.
Início da Página
2.1.Comando Select
O comando principal do SQL é o comando recuperador de informações, o SELECT.
Este comando permite recuperar (exibir) dados de uma ou mais tabelas. Quando utili-
zamos em mais de uma tabela, devemos utilizar o relacionamento de chaves primárias e
estrangeiras para transformar dois registros em um só. Na verdade, a tabela criada é apenas
lógica. Na execução do Select existem apenas duas cláusulas obrigatórias: o próprio Select,
que define as colunas a serem exibidas e o FROM, que define as tabelas que serão
utilizadas. Exemplo:
SELECT * FROM alunos
onde "*" significa que todas as colunas serão exibidas e "Alunos" é o nome da tabela.
No lugar do "*", você pode informar os campos que você deseja exibir. Também pode
alterar o nome da coluna exibida (apenas para o resultado do select). Exemplo:
SELECT aluno Código, nome, nascimento Data FROM alunos
O comando acima exibe três campos da tabela alunos, sendo que o primeiro e o terceiro
campo tiveram o nome da coluna alterada para Código e Data, respectivamente.
Início da Página
2.2.Cláusula Where
Responsável por filtrar linhas da tabela, definindo uma ou mais condições. Exemplo:
Exibir todos os campos da tabela ALUNOS onde o sexo é igual a "F":
Select * From Alunos Where sexo = "F"
Na cláusula Where podemos utilizar mais de uma condição, ligando-as com AND e OR.
Como estas ligações lógicas são do SQL e não do aplicativo, NÃO usamos os pontos no início e
no fim (.and. e .or.), utilizadas normalmente na linguagem de programação.
Outras duas opções do Where são o IN e o BETWEEN. O IN é semelhante ao OR, podendo
usar qualquer valor (numérico, data ou caracter) e seus valores de comparação ficam entre
parênteses. Usamos o IN quando os valores de comparação são mínimos ou quando há intervalos
entre eles (01 ou 05, 'A' ou 'M' ou 'T'). Já o BETWEEN é uma opção de faixa de valores, que
não contém intervalos (salários entre R$500 e R$900, nomes de 'A' até 'F'). Exemplos:
Select * from alunos where cidade in ("01","04","37")
Select * from alunos where sexo = "F" and turno in ("M","V")
Select * from alunos where month(nascimento) between 1 and 6
Select * from alunos where nome in ("A","C") and day(nascimento) between 10 and 20
Observação: Podemos utilizar a opção NOT para condições contrárias do IN e BETWEEN.
Início da Página
2.3.Opção Like no Where
Esta opção é utilizada na pesquisa parcial de dados, como nome incompleto de cliente,
sobrenome igual a nome, etc. Para utilizar o Like, definimos que tipo de substituição
queremos em um campo caracter: substituição de um ou mais caracteres. Utilizamos:
_ (underline) : define a substituição de apenas um caracter;
% (percentual): define a substituição de uma cadeia de caracteres (string), não
importa o tamanho. Podemos usar a opção NOT para negação.
Exemplos: Listar todas as pessoas onde o nome inicia com 'EDUARDO'
Select * From alunos Where nome Like 'EDUARDO %'
Listar todas as pessoas que possuam, nome ou sobrenome, 'PAULA'
Select * From alunos Where nome Like '% PAULA %'
Listar todos os nomes onde a segunda letra é 'A'
Select * From alunos Where nome Like '_A%'
Observação: Se precisamos pesquisar uma string que possua o sinal _ ou %, devemos utilizar
o parâmetro ESCAPE, precedendo o caracter com o simbolo informado no ESCAPE.
Exemplo: Pesquisar palavras que iniciam com '%':
Select * From alunos Where nome Like '\%%' Escape '\'
Início da Página
2.4.Cláusula Order By
Ordena a exibição por uma ou mais colunas definidas (e separadas por vírgula). Exemplo:
SELECT aluno, nome,curso ;
FROM Alunos ;
ORDER BY curso, nome
Os campos que forem usados na cláusula ORDER BY não são obrigados a aparecer na lista
definida pelo SELECT. Após o nome da coluna ordenada, podemos usar a opção DESC
para ordernar de forma descendente, senão a opção ASC (ascendente) que já é padrão. Pode-
mos substituir o nome do campo pelo seu número da posição no select. Exemplo:
SELECT aluno, nome, curso ;
FROM alunos ;
ORDER BY 3, 2
Início da Página
2.5.Funções do SQL
O SQL possui funções próprias que calculam valores agrupando mais de uma linha. Pode
ser a contagem de linhas (registros) ou um cálculo com um campo numérico da tabela.
AVG() : calcula a média de uma coluna de dados numéricos.
COUNT(): conta o número de itens selecionado em uma coluna.
MIN() : determina o menor valor de Item_Selecionado em uma coluna.
MAX() : determina o maior valor de Item_Selecionado em uma coluna.
SUM() : totaliza uma coluna de dados numéricos. Exemplos:
Qual é a média de débitos dos alunos da cidade "01"
Select avg(debito) from alunos where cidade = "01"
Quantos alunos do sexo feminino estudam o curso "02"
select count(*) from alunos where sexo = "F" and curso = "02"
Qual é o menor e o maior débito dos alunos do turno matutino
select min(debito), max(debito) from alunos where turno = "M"
Qual é a soma e quantos alunos devem acima de R$ 100,00
select sum(debito), count(*) from alunos where debito > 100
Início da Página
2.6.Cláusula Group By
Agrupa as linhas de consulta com base nos valores de uma ou mais colunas. O parâmetro
pode ser o nome do campo ou seu número no resultado do comando Select. A cláusula Group By
é muito útil quando a utilizamos para trabalhar junto às funções do comando Select, como
encontrar a soma de um campo numérico, dependendo de outro campo (sum), ou simplesmente
contar o número de registro agrupados (count). Exemplo:
Quantos alunos existem em cada curso?
Select curso, count(*) Quantidade From alunos GROUP BY CURSO
Quantos alunos estudam por turno e curso?
Select turno, curso, count(*) From alunos GROUP BY 1, CURSO
Início da Página
2.7.Cláusula Having
Especifica uma condição de filtragem que os grupos devem satisfazer para que sejam
incluídos nos resultados da consulta. HAVING deve ser utilizada com GROUP BY. Ela pode
incluir quantas condições de filtragem você desejar, conectadas com o operador AND ou OR.
Você também pode utilizar NOT para inverter o valor de uma expressão lógica.
A maior importância da cláusula Having é ser utilizada como o WHERE, porém para o
resultado do GROUP BY. No where, não há como utilizar as funções do SELECT dentro do
where (sum, count, min ...); somente o HAVING aceita estas funções na filtragem. Exemplo:
Quantos alunos existem em cada curso? Exiba apenas os que possuem 44 alunos.
Select curso, count(*) Quantidade From alunos group by curso ;
HAVING count(*) = 44
Quantos alunos estudam por turno e curso, onde o menor débito é igual a R$10,00?
Select turno, curso, count(*) From alunos GROUP BY 1, CURSO ;
HAVING MIN(DEBITO) = 10
Início da Página
2.8.Opção Top
Executa uma query onde o resultado da consulta contém um número específico de linhas
ou uma porcentagem de linhas no resultado da consulta. Você deve incluir uma cláusula
ORDER BY quando incluir a cláusula TOP. A cláusula ORDER BY especifica as colunas nas
quais a cláusula TOP determina o número de linhas para incluir no resultado da consulta.
Observação: se o campo ordenado se repetir (data, valor) podemos ter mais linhas do
que definimos.
Exemplos:
Selecionar apenas as 5 primeiras linhas de alunos ordenada pela chave primária
Select Top 5 aluno, nome From alunos Order By nome
Quais são os dois maiores débitos de alunos?
Select top 2 aluno, nome, debito from alunos order by debito desc
Quais são os 2% maiores débitos de alunos?
Select top 2 PERCENT aluno, nome, debito from alunos order by debito desc
Início da Página
2.9.Opção Distinct
Uma seleção padrão é executada com a opção ALL, que trabalha com todas as linhas
da(s) tabela(s). A opção DISTINCT exclui linhas duplicadas das colunas selecionadas.
Observação: Você só pode utilizar DISTINCT uma vez em cada SELECT.
Exemplo:
Exibir as linhas existentes da tabela alunos que combinam cidade e turno:
Select Distinct cidade, turno from alunos
Início da Página
3.1.Relacionamento (Join)
Para criarmos um banco de dados, a análise dos dados (utilizando Ferramentas CASE) deve
gerar informações normalizadas para não haver armazenamento redundante. A partir do DER
(Diagrama Entidade-Relacionamento) ligamos as chaves primárias às chaves estrangeiras para
identificar as relações entre tabelas. No SQL, esta ligação sempre deve ser informada no
comando para recuperar uma Query que pode possuir campos de mais de uma tabela. Por exemplo,
no comando Select, para exibir o código do funcionário e a UF da sua cidade devemos executar:
SELECT funcions.funcionario, cidades.uf FROM funcions, cidades ;
WHERE funcions.cidade = cidades.cidade
Note que o campo PODE ser precedido do nome da tabela mais um ponto, para mostrar de qual
tabela corresponde o campo. Se exibir nomes de campo iguais de duas tabelas, você DEVE
colocar o nome da tabela antes do campo. Exemplo:
SELECT funcions.nome, cidades.nome FROM funcions, cidades ;
WHERE funcions.cidade = cidades.cidade
De qualquer forma, sempre a condição de ligação é a comparação entre chave primária e
chave estrangeira. NUNCA esqueça de colocar esta condição de ligação, senão haverá uma gera-
ção de relacionamento errado (N para N inválido). O nome da tabela pode ser substituído, na
identificação dos campos, por um apelido (alias). Desta forma, você estará customizando a
digitação do comando. Exemplo:
SELECT f.funcionario, f.nome, c.cidade, c.nome, c.uf FROM funcions F, cidades C ;
WHERE f.cidade = C.cidade
A Query exibida não é uma tabela (ou visão) física. É lógica. Por isso, não haverá criação
física (no HD) de uma nova tabela.
Cuidado: se há join com duas tabelas, há uma condição de ligação. Se há join com mais de
duas tabelas, haverá mais de uma condição de ligação. Exemplo:
SELECT f.nome, c.nome, e.nome FROM funcions f, cidades c, estab e ;
WHERE f.cidade = c.cidade AND f.estab = e.estab
Início da Página
3.2. Operadores
As colunas recuperadas pelo comando SELECT, até agora, foram atributos das tabelas em
sua forma simples ou utilizando funções do aplicativo (val(), month(), year(), etc). Mas
também podemos gerar colunas que são resultados de cálculos aritméticos, utilizando opera-
dores como: +, -, *, /, **. Exemplos:
Exiba o nome do funcionário e seu salário com aumento de 5%
SELECT nome, salario * 1.05 Com_Aumento FROM funcions
Exiba a duplicata 10 com o valor menos R$3,00 e o campo pago dividido por 3
SELECT duplicata, valor - 33 Valor, pago / 3 Dividido FROM duplics
Por falta de exemplo, digite o comando abaixo
SELECT nome, 3 ** 3 Potencia FROM funcions where salario > 1400
Outros exemplos de alteração das colunas recuperadas são a concatenação e as funções
substr() e iif(). Normalmente são utilizados para emissão de relatório ou consultas:
Exiba dia e ano do nascimento e código mais nome concatenados dos funcionarios
SELECT substr(dtoc(nascimento),1,5), funcionar+' - '+nome FROM funcions
Exiba o valor das duplicatas e 'alto ' se valor é maior que R$20,00 senão 'baixo'
SELECT valor, iif(valor>20,'alto ','baixo') FROM duplics
Início da Página
3.3. Sub-Query
A cláusula WHERE do comando Select serve para filtrar, com condições, o número de linhas
recuperadas numa query. Porém, pode haver situação que a condição não pode ser concretizada
com as informações atuais do Select. Para isso, usamos o resultado de um Select para auxiliar
na condição da cláusula WHERE, tendo um select dentro de outro select (Sub-Query). Exemplo:
Quais são os funcionários que recebem mais que a média de todo o cadastro?
.Forma Simples:
Select avg(salario) from funcions (para achar o salario medio: 348,85)
Select * from funcions where salario > 348.85
.SUB-QUERY:
Select * from funcions where salario > (select avg(salario) from funcions)
Quais são os funcionários do mesmo estabelecimento dos funcionários 24 e 127?
Select * from funcions where estab in ;
(select estab from funcions where val(funcionar) in (24,127))
OBSERVAÇÕES:
. Atenção com a quantidade de linhas do segundo select. Se retornar uma linha, pode-se
utilizar =, >, <, <>. Se retornar mais de uma linha, TEM que utilizar IN.
. Repare nos exemplos acima: o resultado da sub-query deve ser sempre compatível com o
campo da condição do Select principal.
Início da Página
3.3. Cláusula UNION
Combina os resultados finais de um SELECT com os resultados finais de outro SELECT. Como
padrão, UNION verifica os resultados combinados e elimina linhas duplicadas. Utilizando ALL,
o comando não elimina linhas duplicadas.
Observações Importantes para o UNION:
· Não é possível utilizar UNION para combinar subconsultas;
· Os dois comandos SELECT devem ter o mesmo número de colunas na saída da consulta;
· Cada coluna nos resultados da consulta de um SELECT deve ter o mesmo tipo de dado e a
mesma largura da coluna correspondente ao outro SELECT;
· Se uma cláusula ORDER BY estiver incluída, ela afetará o resultado inteiro. Só utilize
número de coluna. Exemplos:
Crie uma query exibindo os bancos e estabelecimentos em ordem alfabética:
Select * from bancos UNION Select * from estabs order by 2
Crie uma query exibindo os bancos e cidades em ordem alfabética (estruturas diferentes):
Select banco+space(3),nome+space(10),space(2) from bancos UNION ;
Select cidade, nome, uf from cidades order by 2
UNION mais complexo. Junte um select de totais de cooperados por banco e outro select de
cooperados. Exiba na ordem de sub-totais e cooperados por banco:
Select 1,count(*),banco,space(32) from cooperados group by 3 UNION ;
Select 2,val(cooperado),banco,nome from cooperados order by 3,1
Início da Página
3.4. Cláusula JOIN
Normalmente, criamos querys com relacionamentos entre tabelas para saber informações que
possuem realmente relacionamento (nome do estabelecimento do funcionario, nome da uf do
funcionario, etc). Porém, em algum momento, podemos precisar justamente do contrário, isto é,
registros que não se relacionam com a outra tabela. Motivo: a chave primária de uma tabela não
existe na chave secundária de outra tabela.
INNER JOIN - especifica que o resultado da consulta contém somente linhas de uma tabela,
que correspondem a uma ou mais linhas em outra tabela. O mesmo uso do Where que conhecemos.
LEFT JOIN - especifica que o resultado da consulta contém todas as linhas da tabela à
esquerda da palavra-chave JOIN, correspondendo apenas às linhas da tabela à direita da
palavra-chave JOIN.
RIGHT JOIN - especifica que o resultado da consulta contém todas as linhas da tabela à
direita da palavra-chave JOIN, correspondendo apenas às linhas da tabela à esquerda da
palavra-chave JOIN.
FULL JOIN - especifica que o resultado da consulta contém todas as linhas correspondentes
e não correspondentes de ambas as tabelas.
ON - especifica as condições para as quais as tabelas são associadas.
EXEMPLO:
Quais são as cidades que não possuem nenhum aluno cadastrado de sua procedência:
SELECT a.cidade, c.cidade, c.nome from alunos a RIGHT JOIN cidades c ;
ON a.cidade = c.cidade where a.cidade = space(2)
Início da Página
3.4. Cláusula INTO
Especifica onde os resultados da consulta devem ser armazenados. Destino pode ser:
TABLE - Cria um arquivo do tipo DBF
CURSOR - Cria um cursor temporário. Mais utilizado para relatórios.
ARRAY - Cria uma variável matriz do tamanho do número de linhas x colunas da query, onde
a primeira ocorrência é a linha e a Segunda ocorrência é a coluna.
EXEMPLOS:
Crie uma tabela de alunos da cidade 04 chamada cidade04.dbf:
SELECT * from alunos where cidade = "04" INTO TABLE cidade04
Crie um curso com código e nome do aluno e nome da sua cidade:
SELECT a.aluno,a.nome,c.nome from alunos a, cidades c where a.cidade = c.cidade ;
INTO CURSOR alunocid
Gere em uma variável matriz a quantidade de alunos da cidade 04:
SELECT COUNT(*) from alunos where cidade = "04" INTO ARRAY x
Início da Página
4.1. Comando INSERT
Inclui um registro ao final de uma tabela contendo os valores de campo especificados.
Quando o registro a ser adicionado for de uma tabela do banco de dados, com definição de
integridade referencial, chave primária e chaves secundárias poderá haver problemas da in-
serção como repetição da chave primária e campos não nulos.
Cláusulas:
INTO tabela - Define o nome da tabela
(nome_de_campos) - lista dos campos do registro que receberão os valores do VALUES
VALUES (valores) - Valores a serem atribuídos nos campos identificados. Devem estar
na mesma sequência e mesmo tipo dos campos.
Exemplo:
Incluir um registro na tabela cidades com valores correspondentes:
INSERT INTO cidades (cidade, nome, uf) VALUES ("00020","BETIM","MG")
Incluir um registro na tabela Funcions:
INSERT INTO funcions (funcionar,nome,nascimento,salario) ;
VALUES ("000090","MARIA JUVENTINA",ctod("05/05/80"),700)
Início da Página
4.2. Comando UPDATE
Altera valores de uma ou mais linhas da tabela especificada. Devem ser tomados os mesmos
cuidados do comando INSERT quanto a chave primária e integridade referencial.
Sintaxe:
UPDATE nome_tabela - identifica o nome da tabela
SET atribuição1,atribuição n - define os campos a serem alterados
WHERE condição - especifica os registros a serem alterados. Se a cláusula Where for
omitida a atualização será feita para todos os registros.
Exemplo:
Altere a UF da cidade 20 da tabela cidades para "RJ"
UPDATE cidades SET uf = "RJ" WHERE cidade = "00020"
Altere o salário dos funcionários da cidade "01", acrescentando 5%
UPDATE funcions SET salario = salario * 1.05 WHERE cidade = "01"
Início da Página
4.3. Comando DELETE
Marca um ou mais registros para exclusão. A exclusão definitiva será feita quando utili-
zamos o comando PACK. Para reintegrar os registros marcados para exclusão utilizamos o coman-
do RECALL ALL.
Sintaxe:
DELETE FROM nome_tabela - Define o nome da tabela dos registros a serem excluidos
WHERE condição - Especifica os registros a serem excluidos.
Exemplo:
Excluir o registro onde código do funcionário é 000735
DELETE FROM funcions WHERE funcionar = "000735"
Excluir os funcionarios que recebem mais de R$ 1000,00. Reintegre-os depois
DELETE FROM funcions WHERE salario > 1000
SELECT FUNCIONS
RECALL ALL
Início da Página
5.1.Exercícios com Alunos I
SELECT com WHERE
Observações dos campos (TABELA ALUNOS.DBF)
Campo SEXO -> M - Masculino F - Feminino
Campo TURNO -> M - Matutino V - Vespertino N - Noturno
Campo SITUACAO -> A - Aprovado R - Reprovado E - Exame Suplementar
1) Qual é o nome dos alunos do sexo feminino da tabela Alunos?
2) Quais são os alunos da cidade 04 e que fazem o curso 03?
3) Qual é a cidade, o curso e o sexo dos alunos do turno matutino (valor 'M')?
4) Quais são as alunas que nasceram em Outubro?
5) Quem nasceu antes de 1980?
6) Quem nasceu em novembro e veio das cidades 01, 03 e 04?
7) Qual é o nome, o turno, o curso e o nascimento que estuda a noite, nasceu em 1980 e
possui um nome que inicia com A, M ou F?
8) Quem fará Exame Suplementar no curso 04?
9) Quais são as mulheres que estudam de manhã, e que fazem o curso 01 ou o curso 04?
10) Quem nasceu no primeiro semestre e na primeira quinzena do mês?
11) Quem foi reprovado ou ficou em exame suplementar que possui o nome começando com M?
12) Quem está aprovado no curso 02 ou 04, que faz aniversário entre 15 e 20 dos meses
de abril, maio e junho, e estudou de manhã ou a tarde?
Início da Página
5.2.Exercícios com Alunos II
1) Qual é a quantidade de alunos por cidade?
2) Quantos homens e quantas mulheres estão cadastradas na tabela alunos?
3) Quantas mulheres estão estudando por turno?
4) Qual é a soma de débitos dos alunos por sexo e turno?
5) Quais são os alunos que possuem "MARIA" no nome ou sobrenome?
6) Quais são os alunos onde o nome possui a segunda letra igual a letra "L"?
7) Qual é a soma de débitos por curso apenas dos alunos aprovados?
8) Altere o nº 7 para exibir apenas os cursos onde a soma de débitos é maior que R$1000,00?
9) Qual é o maior débito de alunos por cidade?
10) Qual é o menor débito, o maior débito e a média dos débitos dos alunos por cidade, onde
o débito está entre R$5,00 e R$150,00?
11) Qual é o nome e o débito dos dois maiores devedores da tabela alunos?
12) Qual é o nome e o débito dos seis maiores devedores da tabela alunos?
13) Quais são as cidades cadastradas para os alunos existentes? (Uma linha por cidade).
14) Quais são os nomes de alunos que iniciam com A, a segunda letra é qualquer uma e a
terceira é I?
15) Quais são os dias de nascimento existentes para os alunos da cidade 01 e dos cursos 01,
03 e 04.
Início da Página
5.3.Exercícios com Duplicatas I
Observações dos campos
Campo FORMA -> P - Duplicata a PAGAR R - Duplicata a RECEBER
Campo TIPOPGTO -> V - Pagto a VISTA P - Pagto a PRAZO
Campo VENDEDOR -> Será igual a zero se a duplicata é a PAGAR.
1) Quais são as duplicatas do vendedor 3?
2) Quais são as duplicatas que não foram pagas em 1997?
3) Qual é a maior duplicata a pagar?
4) Qual é a menor duplicata a receber?
5) Quais são as duplicatas RECEBIDAS com atraso?
6) Qual é o maior valor da duplicata recebida, à vista, do mês de janeiro e do vendedor 3?
7) Quais são as 4 maiores vendas do vendedor 3?
8) Qual foi o valor pago (dupl. a pagar) por mês (campos Baixa e Pago)?
9) Quantas e qual é a soma das duplicatas que ainda não foram pagas ou recebidas? Exibir
o resultado separado pela forma. (campos Baixa, Valor e Forma)
Início da Página
5.4.Exercícios com Duplicatas II
Observações dos campos
Campo FORMA -> P - Duplicata a PAGAR R - Duplicata a RECEBER
Campo TIPOPGTO -> V - Pagto a VISTA P - Pagto a PRAZO
Campo VENDEDOR -> Será igual a zero se a duplicata é a PAGAR.
a - Crie uma query que exiba as colunas duplicata, vencimento, valor, pago e vendedor
ordenada por mes de vencimento crescente e valor decrescente;
b - Selecione duplicatas apenas com data de vencimento de 1997;
c - Exiba duplicatas onde valor é maior ou igual a 80,00. Somente duplicatas a receber;
d - Exiba a query da letra c somente para duplicatas do mês de fevereiro e março
(não importa o ano);
e - Altere a query da letra d para ser exibida classificada pelo número da duplicata;
f - Selecione as colunas duplicata, data, valor e vendedor;
g - Altere a query da letra f para selecionar apenas as duplicatas com valor de R$ 10,00
a R$ 70,00;
h - Complemente a letra g com a condição de exibir somente as duplicatas do mês de
dezembro;
i - Altere a query da letra g para ser exibida classificada pela coluna vencimento em
ordem decrescente e coluna duplicata em ordem ascendente;
j - Selecione os campos duplicata, valor, vpago e dataentra;
k - Altere a letra j para exibir as duplicatas 50 a 500, em ordem de vendedor;
l - Altere a letra j para exibir as duplicatas do vendedor 1 ou 3, em ordem decrescente
do campo pago;
Início da Página
5.5.Exercícios com Funcionários
1) Quais são os 2 maiores salários da tabela funcions?
2) Quais são os nomes de funcionários que possuem o nome ou sobrenome PEDRO?
3) Quantos funcionários nasceram no primeiro semestre (quantidade por mês)?
4) Quais são as cidades que, somando o salário dos funcionários, recebem mais de R$15000?
5) Exiba os funcionários que possuem, no nome, as letras U, O, I, E e A (nesta ordem).
6) Altere o nº 5 para exibir a media dos salarios por estabelecimento e cidade.
7) Altere o nº 6 para exibir os agrupamentos onde a quantidade de funcionarios é igual
a três e a média salarial é maior que R$ 175,00
8) Qual é o maior salário por cidade? Exiba apenas as cidades onde o maior salário é
maior que R$ 1400,00.
9) Quais são as cidades, a média e o menor salário das cidades onde a quantidade de
funcionários é menor que 20 pessoas?
10) Quantas pessoas por estabelecimento existem para os estabelecimentos onde a média do
salário está entre R$ 150 e R$ 1000?
Início da Página
5.6.Exercícios com Join
Alunos, Cidades e Cursos
1)Qual é a quantidade de alunos por cidade? Exiba também o nome da cidade.
2)Qual é a soma de débitos da cidade de Governador Valadares?
3)Quais são os alunos que estudam Engenharia Civil?
4)Quais são os alunos de Governador Valadares que estudam Engenharia Civil?
5)Quais são as alunas "ANA" que vieram da Bahia (BA)?
6)Quantos alunos, por estado (UF), devem mais de R$50,00?
Filmes, tipos e precos
7)Quais são os filmes de comédia ('Comedia') que custam R$ 0,90 (0.9) a locação?
Exiba nome do filme, o tipo de filme e valor da locação.
8)Quais são os filmes do mesmo preço do filme '00003'?
Exiba nome do filme e o valor da locação.
9)Qual é o tipo de filme que possui mais fitas para locação?
Exiba a quantidade e o nome do tipo do filme.
10)Se todas as fitas estivessem alugadas, quanto a locadora receberia por tipo de filme?
Exiba o código do tipo de filme e a soma a receber.
Início da Página
5.7.Exercícios com Comandos Especiais
Sub-query com a tabela alunos
1- Quais são os alunos que possuem débito maior que a media dos débitos dos alunos da
cidade 01?
2- Quem nasceu no mesmo mês que o aluno 00014?
3- Quem é mais velho que o aluno 01466?
4- Quais são os alunos da mesma cidade do aluno 01568 ou do aluno 00010?
FULL, LEFT e RIGHT JOIN
5- Quais são os alunos que ainda não pagaram mensalidades? (alunos e pagamentos.dbf)
6- Quais são as cidades da tabela cidades não utilizadas na tabelas alunos?
7- Por um erro de crítica no cadastro, alguns cooperados possuem bancos inexistentes na
tabela bancos. Quais são estes cooperados?
8- No comando select acima, altere-o para exibir tambem os bancos cadastrados na tabela
bancos que nao possuem nenhum cooperado.
9- Quais sao as duplicatas (duplics) que possuem o codigo do vendedor nao cadastrado na
tabela de vendedores (venders)?
10- Quais sao os vendedores imprestaveis (nao venderam nada em duplics)?
11- Exiba o resultado dos exercicios 9 e 10 juntos. Início da Página
UNION
12- Crie uma query que una o código e nome da cidade com o código e nome do estabeleci-
mento, em ordem alfabética
13- Crie uma query que une um select com a quantidade de cidades por UF e outro select
com todas as cidades, em ordem de uf.
14- Crie uma query com três comandos select usando a tabela alunos que resulta:
"Nascidos em Agosto " | quantidade
"Do Sexo Feminino " | quantidade
"Da Cidade igual a 01" | quantidade
INTO e Operadores
15- Crie um cursor chamado desc_aluno com o nome do aluno, nome da sua cidade, campo sexo
descritivo ("masculino" para M e "feminino" para f) e nome do seu curso.
16- Crie uma matriz chamada ufs com a UF e a quantidade de cidades por UF.
17- Crie uma tabela .dbf chamada alucurso04 com codigo do aluno, nome do aluno e nome do
turno ("Matutino" para M, "Vespertino" para V e "Noturno" para N), para os alunos do
curso 04.
5.8.Exercícios com Insert, Update e Delete
1- Insira um novo banco na tabela Bancos com o codigo igual a 123
2- Altere todos os cooperados que possuem o cadastro no banco 013 e 015 para o novo banco
cadastrado (codigo 123)
3) Exclua as duplicatas (duplics) onde o valor pago (campo PAGO) é menor que R$ 2,00 e
diferente de zero.
Início da Página
5.9. Tabelas para os Exercícios
Durante toda a utilização do conteúdo da página, vamos necessitar de algumas tabelas do
tipo .DBF, principalmente durante os exercícios. Mas atenção: as tabelas são extensão DBF
para o VISUAL FOXPRO 5.
Para receber as tabelas, envie-me um e-mail
OBSERVAÇÃO: Se você precisa das tabelas em outra versão avise no e-mail.
Início da Página
Respostas dos Exercícios
Respostas - Aluno I
1) SELECT nome FROM alunos where sexo = "F"
2) SELECT * FROM alunos where cidade = "04" and curso = "03"
3) SELECT cidade, curso, sexo FROM alunos where turno = "M"
4) SELECT * FROM alunos where sexo = "F" and month(nascimento) = 10
5) SELECT * FROM alunos where year(nascimento) < 1980
6) SELECT * FROM alunos where month(nascimento) = 11 and cidade in ("01","03","04")
7) SELECT nome, turno, curso, nascimento FROM alunos where turno = "N" and ;
year(nascimento) = 1980 and nome in ("A","M","F")
8) SELECT * FROM alunos where curso = "04" and situacao = "E"
9) SELECT * FROM alunos where sexo = "F" and turno = "M" and curso in ("01","04")
10) SELECT * FROM alunos where month(nascimento) < 7 and day(nascimento) < 16
11) SELECT * FROM alunos where situacao in ("R","E") and nome = "M"
12) SELECT * FROM alunos where situacao = "A" and curso in ("02","04") and ;
day(nascimento) between 15 and 20 and month(nascimento) between 4 and 6 and ;
turno in ("M","V")
Início da Página
Respostas - Alunos II
1) SELECT cidade, count(*) FROM alunos group by cidade
2) SELECT sexo, count(*) FROM alunos group by sexo
3) SELECT turno, count(*) FROM alunos group by turno
4) SELECT sexo, turno, sum(debito) FROM alunos group by sexo, turno
5) SELECT nome FROM alunos where nome like "%MARIA$"
6) SELECT nome FROM alunos where nome like "_L%"
7) SELECT sum(debito), curso FROM alunos where situacao = "A" ;
group by curso
8) SELECT sum(debito), curso FROM alunos where situacao = "A" ;
group by curso having sum(debito) > 1000
9) SELECT max(debito),cidade FROM alunos group by cidade
10) SELECT cidade, min(debito), max(debito), avg(debito) FROM alunos ;
group by cidade where debito between 5 and 150
11) SELECT TOP 2 nome, debito FROM alunos order by debito desc
12) SELECT TOP 6 nome, debito FROM alunos order by debito desc
13) SELECT DISTINCT cidade from alunos
14) SELECT nome FROM alunos where nome like "A_I%"
15) SELECT DISTINCT DAY(NASCIMENTO) FROM alunos where cidade = "01" and ;
curso in ("01","03","04")
Início da Página
Respostas - Duplicatas I
1) SELECT * FROM duplics where vendedor = "0003"
2) SELECT * FROM duplics where year(vencimento) = 1997 and pago = 0
3) SELECT max(valor) from duplics where forma="P"
4) SELECT min(valor) from duplics where forma="R"
5) SELECT * FROM duplics where baixa > vencimento and forma="R"
6) SELECT max(valor) from duplics where tipopgto="V" and ;
month(vencimento) = 1 and vendedor="0003"
7) SELECT TOP 4 * from duplics where vendedor = "0003" ;
order by valor desc
8) SELECT sum(pago), month(baixa) from duplics group by 2 ;
where pago > 0
9) SELECT count(*), sum(valor), forma from duplics ;
where pago = 0 group by forma
Início da Página
Respostas - Duplicatas II
a) SELECT DUPLICATA, VENCIMENTO, VALOR, PAGO, VENDEDOR FROM DUPLICS ;
ORDER BY VENCIMENTO ASC, VALOR DESC
b) SELECT * FROM DUPLICS WHERE YEAR(VENCIMENTO)=1997
c) SELECT * FROM DUPLICS WHERE VALOR >=80 AND FORMA = "R"
d) SELECT * FROM DUPLICS WHERE VALOR >=80 AND FORMA = "R" AND MONTH(VENCIMENTO) IN (2,3)
e) SELECT * FROM DUPLICS WHERE VALOR >=80 AND FORMA = "R" AND MONTH(VENCIMENTO) IN (2,3) ;
ORDER BY DUPLICATA
f) SELECT DUPLICATA, VENCIMENTO, VALOR, VENDEDOR FROM DUPLICS
g) SELECT DUPLICATA, VENCIMENTO, VALOR, VENDEDOR FROM DUPLICS ;
WHERE VALOR BETWEEN 10 AND 70
h) SELECT DUPLICATA, VENCIMENTO, VALOR, VENDEDOR FROM DUPLICS ;
WHERE VALOR BETWEEN 10 AND 70 AND MONTH(VENCIMENTO) = 12
i) SELECT DUPLICATA, VENCIMENTO, VALOR, VENDEDOR FROM DUPLICS ;
WHERE VALOR BETWEEN 10 AND 70 ORDER BY VENCIMENTO DESC, DUPLICATA
j) SELECT DUPLICATA, VALOR, PAGO, ENTRADA FROM DUPLICS
k) SELECT DUPLICATA, VALOR, PAGO, ENTRADA FROM DUPLICS WHERE VAL(DUPLICATA) BETWEEN ;
50 AND 500 ORDER BY VENDEDOR
l) SELECT DUPLICATA, VALOR, PAGO, ENTRADA FROM DUPLICS WHERE VENDEDOR IN ("0001","0003") ;
ORDER BY PAGO DESC
Início da Página
Respostas - Funcionários
1) SELECT TOP 2 NOME, SALARIO FROM FUNCIONS ORDER BY SALARIO DESC
2) SELECT NOME FROM FUNCIONS WHERE NOME LIKE "%PEDRO%"
3) SELECT COUNT(*), MONTH(NASCIMENTO) FROM FUNCIONS GROUP BY 2 WHERE MONTH(NASCIMENTO) < 7
4) SELECT SUM(SALARIO),CIDADE FROM FUNCIONS GROUP BY CIDADE HAVING SUM(SALARIO)>15000
5) SELECT NOME FROM FUNCIONS WHERE NOME LIKE "%U%O%I%E%A%"
6) SELECT AVG(SALARIO),ESTAB,CIDADE FROM FUNCIONS WHERE NOME LIKE "%U%O%I%E%A%" ;
GROUP BY ESTAB, CIDADE
7) SELECT AVG(SALARIO),ESTAB,CIDADE FROM FUNCIONS WHERE NOME LIKE ;
"%U%O%I%E%A%" GROUP BY ESTAB, CIDADE HAVING COUNT(*) = 3 AND AVG(SALARIO) > 175
8) SELECT MAX(SALARIO),CIDADE FROM FUNCIONS GROUP BY CIDADE HAVING MAX(SALARIO) >1400
9) SELECT CIDADE, AVG(SALARIO), MIN(SALARIO) FROM FUNCIONS GROUP BY CIDADE ;
HAVING COUNT(*) < 20
10) SELECT COUNT(*), ESTAB FROM FUNCIONS GROUP BY ESTAB HAVING AVG(SALARIO) ;
BETWEEN 150 AND 1000
Início da Página
Respostas - Com Join
1) select c.nome, count(*) from alunos a, cidades c where a.cidade = c.cidade ;
group by c.cidade
2) select c.nome, sum(debito) from alunos a, cidades c where a.cidade = c.cidade and ;
c.nome = "GOVERNADOR VALADARES"
3) select a.nome, c.nome from alunos a, cursos c where a.curso = c.curso and ;
c.nome = "Engenharia Civil"
4) select a.nome, c.nome ,ci.nome from alunos a, cursos c, cidades ci ;
where a.curso = c.curso and a.cidade = ci.cidade and c.nome = "Engenharia Civil" and ;
ci.nome = "GOVERNADOR VALADARES"
5) select a.nome, c.uf from alunos a, cidades c where a.cidade = c.cidade and ;
a.nome like "ANA%" and c.uf = "BA"
6) select count(*), c.uf from alunos a, cidades c ;
where a.cidade = c.cidade and a.debito > 50 group by c.uf
7) select t.nome, p.valor, f.nome from filmes f, tipos t, precos p ;
where f.preco = p.preco and f.tipo = t.tipo and p.valor = 0.9 and t.nome = 'Comedia'
8) select f.filme, f.nome, p.valor from filmes f, precos p where f.preco = p.preco and ;
p.valor = (select p.valor from filmes f, precos p where f.preco = p.preco and ;
f.filme = "00003")
9) select top 1 count(*),t.nome from filmes f, tipos t ;
where f.tipo = t.tipo group by t.tipo order by 1 desc
10) select f.tipo, sum(p.valor) from filmes f, precos p where f.preco = p.preco ;
group by f.tipo
Início da Página
Respostas - Especiais
1) select * from alunos where debito > (select avg(debito) from alunos where cidade="01")
2) select * from alunos where month(nascimento) = ;
(select month(nascimento) from alunos where aluno="00014")
3) select * from alunos where nascimento < ;
(select nascimento from alunos where aluno="00010")
4) select * from alunos where cidade in ;
(select cidade from alunos where aluno in ("01568","00010"))
OBSERVACAO: A execução é lenta, pois é uma subquery com dois IN's
5) select * from alunos a left join pagamentos p on a.aluno = p.aluno where p.aluno = " "
6) select * from cidades c left join alunos a on a.cidade = c.cidade where a.cidade = " "
7) select * from bancos b right join cooperados c on b.banco = c.banco where b.banco = " "
8) select * from bancos b full join cooperados c on b.banco = c.banco ;
where b.banco = " " OR c.banco = " "
9) select * from duplics d left join venders v on d.vendedor = v.vendedor ;
where v.vendedor = " " (REPARE: somente duplicatas a pagar)
10) select * from duplics d right join venders v on d.vendedor = v.vendedor ;
where d.vendedor=" "
11) select * from duplics d full join venders v on d.vendedor = v.vendedor ;
where d.vendedor=" " or v.vendedor = " "
12) select cidade, nome from cidades union ;
select estab, nome+space(10) from estabs order by 2
13) select 1, count(*), "Estado:"+uf+space(21), uf from cidades group by 4 union all ;
select 2, 0, nome, uf from cidades order by 4, 1
14) select "Nascidos em Agosto", count(*) from alunos where month(nascimento) = 8 union ;
select "Do Sexo Feminino ", count(*) from alunos where sexo = "F" union ;
select "Cidade igual a 01 ", count(*) from alunos where cidade = "01"
15) select a.nome, c.nome, iif(sexo="M","Masculino","Feminino "), cr.nome ;
from alunos a, cidades c, cursos cr where a.cidade = c.cidade and a.curso = cr.curso ;
into cursor desc_aluno
16) select uf, count(*) Quant_Cidades from cidades group by 1 into array ufs
17) select aluno, nome, iif(turno="M","Matutino ",iif(turno="V","Vespertino","Noturno ")) ;
Turno from alunos into table alucurso04
Início da Página
Respostas - Insert, Update e Delete
1) insert into bancos (banco, nome) values ("123","BANCO MUNDIAL")
2) update cooperados set banco = "123" where banco in ("013","015")
3) delete from duplics where pago > 0 and pago < 2
Início da Página
