SQL Avançado Revisando SQL...

1. SQL AvançadoRevisando SQL... Renata Viegas renatafviegas@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 WHERE 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 SELECTTOP 20 codigo,nome,fone FROM cliente SELECTTOP n [percent] <lista_de_colunas> FROM <tabela>

9. Consultas Simples • Exemplo 4: exibir código, nome e preço de venda dos 10% primeiros produtos cadastrados SELECTTOP 10 PERCENT codigo, nome,preco FROM 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, quantEst*preco ‘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: SELECTDISTINCT <colunas> FROM <tabelas> • 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 <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

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 <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 WHEREdtNasc > ’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 <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 WHEREestoque_atual BETWEEN 10 AND 30 ORDERBY 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 WHEREdataNasc BETWEEN ’01/01/1960’ AND ’31/12/1969’ ORDERBY dataNasc DESC,nome

32. 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 WHEREbairro IN (‘Manaira’, ‘Bessa’) ORDERBY 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 WHEREtipo NOTIN (‘2’,‘4’) ORDERBY tipo DESC

34. Filtrando Dados com Valores Nulos • Sintaxe: SELECT <colunas> FROM <tabelas> WHERE <coluna> 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 WHEREemail ISNULL • Exemplo 25: Exibir dados dos funcionários cujo número do telefone seja conhecido SELECT * FROM funcionarios WHEREtelefone ISNOTNULL

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 WHEREestado <> ‘PB’ NÃO !!!

37. 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

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 WHEREpreco > 10

41. 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