SQL Avanзado Revisando SQL...

1
SQL AvançadoRevisando SQL...

• Renata Viegas
• renatafviegas_at_gmail.com

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

3
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

4
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

5
SELECT Consultando Dados

• Sintaxe Básica

SELECT lista_de_colunas FROM lista_de_tabelas WHER
E condições
6
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

7
Consultas Simples

• Exemplo 1 exibir todos os dados de todos os
  clientes
• Exemplo 2 exibir código, nome e telefone de
  todos os clientes

SELECT FROM cliente
SELECT codigo, nome, fone FROM cliente
8
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 ltlista_de_colunasgt FROM
lttabelagt
9
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 FRO
M produto
10
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

11
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
12
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
13
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,
  quantEstpreco Valor Total
• FROM produto

14
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

15
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

16
Funções Matemáticas

• Exemplo 10 exiba o preço do produto elevado a
  potência de 3
• SELECT POWER(preco,3)
• FROM produto

17
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

18
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

19
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
20
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

21
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

22
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

23
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)
24
Funções de Conversão

• Exemplo 15 Exiba a data de hoje no formato
  brasileiro
• SELECT Convert( char(10), GetDate(), 103)

25
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 SELECT
  demonstrando o uso destas funções !! ?

26
Eliminando Duplicação de Linhas

• Sintaxe
• SELECT DISTINCT ltcolunasgt
• FROM lttabelasgt
• Exemplo 16 Exibir código de todos os clientes
  que já fizeram pedido na empresa

SELECT DISTINCT cod_cliente FROM clientes
27
Ordenando Resultados

• Sintaxe
• SELECT ltcolunasgt FROM lttabelasgt
• ORDER BY ltcolunagt 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

28
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
29
Filtrando os Dados

• Sintaxe
• SELECT ltcolunasgt FROM lttabelasgt
• WHERE ltcoluna operador expressãogt
• Exemplo 19 Exibir todos os dados dos
  funcionários que nasceram a partir de 1950
• SELECT , Convert(varchar, dtNas, 103)
• FROM funcionarios
• WHERE dtNasc gt 31/12/1949

Qual outra maneira de fazer esta consulta usando
funções de data na cláusula Where ??
30
Filtrando Dados Usando Intervalo

• Sintaxe
• SELECT ltcolunasgt FROM lttabelasgt
• WHERE ltcoluna BETWEEN expressãogt
• 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
31
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
32
Filtrando Dados Usando Listas

• Sintaxe
• SELECT ltcolunasgt FROM lttabelasgt
• WHERE ltcoluna IN listagt
• 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

33
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
34
Filtrando Dados com Valores Nulos

• Sintaxe
• SELECT ltcolunasgt FROM lttabelasgt
• WHERE ltcolunagt IS NOT NULL
• Significado
• Valores não formatados ou desconhecidos

35
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
36
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 ltgt PB

NÃO !!!
37
Filtrando Dados usando Strings

• Sintaxe
• SELECT ltcolunasgt FROM lttabelasgt
• WHERE ltcolunagt LIKE ltstringgt
• 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

38
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

39
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

40
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 gt 10

41
Para Pesquisar!! Cláusula COMPUTE

• Sintaxe
• SELECT ltcolunasgt FROM lttabelasgt
• ORDER BY ltcolunagt
• COMPUTE ltlista de funções de agregaçãogt
• BY ltlista de colunasgt
• 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