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