2008.2
SQL Avançado
Revisando SQL...
Renata Viegas
[email protected]
2008.2
Objetivos
• Revisando a sintaxe SQL
– SELECT, UPDATE, INSERT, DELETE
• Manipulando expressões
– Funções matemáticas, etc
•
•
•
•
Condições de Pesquisa
Funções de Agregação e Agrupamento
Junção de tabelas
Subconsultas e tabelas temporárias
2008.2
Structured Query Language (SQL)
• Linguagem não procedural que requer do
usuário qual dado é necessário sem
especificar como obtê-lo
• Poupa tempo de programação, mas exige
treino para se dominar
• Suporte cliente-servidor
• Controle de Acesso
2008.2
Structured Query Language (SQL)
• Integridade dos Dados
• Independência
de
fabricante:
está
incorporada em quase todos os SGBDs em
seu
padrão
ANSI,
com
extensões
proprietárias de cada fabricante
2008.2
SELECT – Consultando Dados
• Sintaxe Básica
SELECT lista_de_colunas
FROM lista_de_tabelas
WHERE condições
2008.2
SELECT – Consultando Dados
• Cláusulas Adicionais
– INTO: especifica uma nova tabela que conterá o
resultado da consulta
– ORDER BY: classifica o resultado da consulta
– GROUP BY: agrupa as linhas das consultas com
base nos valores de uma ou mais colunas
– HAVING: especifica as condições usadas para
filtrar agrupamento de dados no resultado da
consulta. Só deve ser usado com o GROUP BY
2008.2
Consultas Simples
• Exemplo 1: exibir todos os dados de todos os
clientes
SELECT * FROM cliente
• Exemplo 2: exibir código, nome e telefone de
todos os clientes
SELECT codigo, nome, fone
FROM cliente
2008.2
Consultas Simples
• Exemplo 3: exibir código, nome e telefone
dos 20 primeiros clientes cadastrados na
empresa
SELECT TOP 20 codigo,nome,fone
FROM cliente
SELECT TOP n [percent] <lista_de_colunas>
FROM <tabela>
2008.2
Consultas Simples
• Exemplo 4: exibir código, nome e preço de
venda
dos
10%
primeiros
produtos
cadastrados
SELECT TOP 10 PERCENT codigo,
nome,preco
FROM produto
2008.2
Consultas Simples
• Exemplo 5: exibir código, nome, telefone e
uma coluna contendo ‘classificado’ para todos
os clientes
SELECT codigo,nome,fone,
’classificado’
FROM cliente
2008.2
Consultas Simples
• Exemplo 6: exibir código, nome e uma coluna
contendo ‘classificado’ com o cabeçalho
‘Classificação ’para todos os clientes
SELECT codigo,nome,
’classificado’ ’Classificação’
FROM cliente
2008.2
Consultas Simples
• Exemplo 7: exibir número, descrição e preço
de um produto, renomeando a coluna
número para código
SELECT numero as ‘Codigo’,
descricao, preco
FROM produto
2008.2
Manipulando Expressões
• Um comando SELECT também pode retornar como
coluna de resultado um valor calculado.
• Exemplo 8: Exibir código, quantidade em estoque,
preço da venda e valor total (quantidade * preço da
venda) para cada produto
SELECT codigo, quantEst AS
Quantidade, preco,
quantEst*preco ‘Valor Total’
FROM produto
2008.2
Funções Matemáticas
• Além de operadores aritméticos, podemos
usar funções matemáticas (consulte a
referência do SGBD)
– ROUND (valor, n): arredonda o valor para n
casas decimais
– POWER (valor, p): retorna o valor elevado à
potência p
2008.2
Funções Matemáticas
• Exemplo 9: exiba código, preço e valor
arredondado do preço de cada produto para
1 casa decimal
SELECT codigo,preco,
ROUND (preco,1)
FROM produto
2008.2
Funções Matemáticas
• Exemplo 10: exiba o preço do produto
elevado a potência de 3
SELECT POWER(preco,3)
FROM produto
2008.2
Funções de Caracteres
• Aplicação: muito úteis para manipular dados
do tipo caractere
– SUBSTRING(expr, inicio, tam): Extrai uma parte
de uma string desde inicio e com tam caracteres
– LOWER(expr): converte para minúsculo
2008.2
Funções de Caracteres
• Exemplo 11: exiba os 10 primeiros caracteres
do título de um livro em minúsculo e seu
preço da tabela “livros”
SELECT LOWER(SUBSTRING(titulo,1, 10))
Titulo, preco
FROM livros
2008.2
Funções de Data e Hora
• Aplicação: em colunas do tipo datetime, que
armazenam data e hora
– DatePart(parte,data): retorna a parte especificada da
data
Argumento Parte
yy
O ano
hh
horas
qq
o trimestre
mi
minutos
mm
o mês
ss
segundos
dd
dia do mês
ms
milisegundos
2008.2
Funções de Data e Hora
• Exemplo 12: Exiba o nome e mês de
aniversário de todos os funcionários
SELECT nome,
DatePart (mm,dataNasc)
FROM funcionarios
2008.2
Funções de Data e Hora
• GetDate(): retorna a data e hora atuais
– Exemplo 13: exiba a data de aniversário e o dia
atual para todos os funcionários
SELECT dtNasc, GetDate() ‘Dia
de Hoje’
FROM funcionarios
2008.2
Funções de Conversão
• Aplicação: conversão de um tipo de dado em outro
CONVERT (tipo de dado, valor)
• Exemplo 14: Exiba o preço de um produto
convertido para string apenas no select
SELECT Convert( char(10), preco)
FROM produto
2008.2
Funções de Conversão
• Com valores datetime, convert pode ter um
parâmetro a mais que especifica o formato
da data a ser usado
Padrão brasileiro: 3 (dd/mm/aa) e 103 (dd/mm/aaaa)
Padrão americano: 1 (mm/dd/aa) e 101 (mm/dd/aaaa)
2008.2
Funções de Conversão
• Exemplo 15: Exiba a data de hoje no formato
brasileiro
SELECT Convert( char(10),
GetDate(), 103)
2008.2
EXERCÍCIOS
• Quer enriquecer suas expressões SQL? Pesquise as
seguintes funções no SQL Server
– Funções de Caracteres
• Upper(), Ltrim(), Rtrim(), Space(), Str()
– Funções de Data e Hora
• DateAdd(), DateDiff(), DateName()
• Traga na próxima aula instruções
demonstrando o uso destas funções !! 
SELECT
2008.2
Eliminando Duplicação de Linhas
• Sintaxe:
SELECT DISTINCT <colunas>
FROM <tabelas>
• Exemplo 16: Exibir código de todos os clientes que
já fizeram pedido na empresa
SELECT DISTINCT cod_cliente
FROM clientes
2008.2
Ordenando Resultados
• Sintaxe:
SELECT <colunas> FROM <tabelas>
ORDER BY <coluna> [ASC] [DESC]
• Exemplo 17: Exibir código, nome e telefone de todos os
clientes, ordenado pelo nome do cliente de forma
ascendente
SELECT cod_cliente, nome, tel
FROM clientes
ORDER BY nome
2008.2
Ordenando Resultados
• Exemplo 18: Exibir nome, cidade e sexo de
todos os clientes ordenado por cidade em
ordem ascendente e sexo em ordem
descendente
SELECT nome, cidade, sexo
FROM clientes
ORDER BY cidade, sexo DESC
2008.2
Filtrando os Dados
• Sintaxe:
SELECT <colunas> FROM <tabelas>
WHERE <coluna operador expressão>
• Exemplo 19: Exibir todos os dados dos funcionários que
nasceram a partir de 1950
SELECT *, Convert(varchar, dtNas,
103)
FROM funcionarios
WHERE dtNasc > ’31/12/1949’
Qual outra maneira de fazer esta consulta
usando funções de data na cláusula Where ??
2008.2
Filtrando Dados Usando Intervalo
• Sintaxe:
SELECT <colunas> FROM <tabelas>
WHERE <coluna BETWEEN expressão>
• Exemplo 20: Exibir todos os dados de produtos cujo
estoque esteja entre 10 e 30 unidades, ordenados pelo
nome do produto
SELECT * FROM produtos
WHERE estoque_atual
BETWEEN 10 AND 30
ORDER BY nome
2008.2
Filtrando Dados Usando Intervalo
• Exemplo 21: Exibir todos os dados de funcionários que
nasceram na década de 60, ordenados pela data de
nascimento
em ordem descendente, e pelo nome do
funcionário em ordem ascendente.
SELECT * FROM funcionarios
WHERE dataNasc
BETWEEN ’01/01/1960’
AND ’31/12/1969’
ORDER BY dataNasc DESC,nome
2008.2
Filtrando Dados Usando Listas
• Sintaxe:
SELECT <colunas> FROM <tabelas>
WHERE <coluna IN lista>
• Exemplo 22: Exibir todos os dados dos funcionários que
residam em Manaíra ou Bessa, ordenados pelo nome
SELECT * FROM funcionarios
WHERE bairro IN (‘Manaira’, ‘Bessa’)
ORDER BY nome
2008.2
Filtrando Dados Usando Listas
• Exemplo 23: Exibir todos os produtos que
não sejam do tipo 2 ou 4, ordenados pelo
tipo em ordem descendente
SELECT * FROM produtos
WHERE tipo NOT IN (‘2’,‘4’)
ORDER BY tipo DESC
2008.2
Filtrando Dados com Valores Nulos
• Sintaxe:
SELECT <colunas> FROM <tabelas>
WHERE <coluna> IS [NOT] NULL
• Significado
– Valores não formatados ou desconhecidos
2008.2
Filtrando Dados com Valores Nulos
• Exemplo 24: Exibir os dados dos funcionários que
não tenham e-mail
SELECT * FROM funcionarios
WHERE email IS NULL
• Exemplo 25: Exibir dados dos funcionários cujo
número do telefone seja conhecido
SELECT * FROM funcionarios
WHERE telefone IS NOT NULL
2008.2
Filtrando Dados com Valores Nulos
• Você acha que as linhas com valores NULL
serão retornadas de acordo com o SELECT
abaixo ??
SELECT * FROM funcionarios
WHERE estado <> ‘PB’
NÃO !!!
2008.2
Filtrando Dados usando Strings
• Sintaxe:
SELECT <colunas> FROM <tabelas>
WHERE <coluna> LIKE <string>
• Significado:
– Casamento de Padrão: uma string contendo caracteres
que podem ser combinados com parte de outra string
– % (porcentagem): seqüência de caracteres
– _ (sublinhado): Combina com um único caractere
2008.2
Filtrando Dados usando Strings
• Outras combinações:
– [] (colchetes): combina uma faixa de caracteres
– ^ (circunflexo): significa “negação”
• Como encontrar todos os nomes que começam com A ou B?
– Usar LIKE ‘[AB]%’
• Como encontrar todos os nomes que começam com as letras
de A até E?
– Usar LIKE ‘[A-E]%’
• E todos os nomes que não iniciam com V?
– Usar LIKE ‘[^V]%’
2008.2
Funções Agregadas
• COUNT(): conta o número de valores de uma
coluna
• SUM(): soma os valores de uma coluna de dados
numéricos
• AVG(): calcula a média de uma coluna de dados
numéricos
• MAX(): determina o maior valor de uma coluna
• MIN() : determina o menor valor de uma coluna
2008.2
Funções Agregadas
• Obs.: A cláusula DISTINCT pode ser usada como parte do
argumento para eliminar linhas repetidas antes da aplicação
da função
• Exemplo 26: Exibir a quantidade de produtos vendidos com
preço maior que R$ 10,00
SELECT COUNT (DISTINCT cod_p)
FROM produtos
WHERE preco > 10
2008.2
Para Pesquisar!! Cláusula COMPUTE
• Sintaxe:
SELECT <colunas> FROM <tabelas>
ORDER BY <coluna>
COMPUTE <lista de funções de agregação>
BY <lista de colunas>
• Para que serve?
– Gerar totalizadores que aparecem como colunas adicionais resumo no
final do conjunto resultado
• O que devo fazer ??
– Trazer 3 exemplos práticos desta cláusula