Linguagem SQL FUNÇÕES

Prévia do material em texto

Banco de Dados II
Linguagem SQL
Aula 3
Prof. Leonardo Ferrareto
Graduado em Téc. em 
Processamento 
de Dados
Especialista em Banco de Dados
D.B.A – SQL Server
Junções Horizontais
Junções Verticais
:Funções 
Agregação
Getdate()
Funções de transformação
Funções de Data
Funções de usuários
Conteúdo
Juntar tabelas significa considerar registros de 
mais de uma tabela que tenham ou não os 
mesmos valores em colunas consideradas chaves 
ou algo equivalente.
Para isso pode- :se realizar
Junções Verticais.
Junções Horizontais.
Juntado tabelas
Produto Cartesiano (Cross Join)
Junção Intrínseca (Inner Join)
Junção Extrínseca (Outer Join)
Junção horizontal
Junções Horizontais em que o registro de uma 
tabela e de outra se complementam, tornando-se 
um só registro com a quantidade somada de suas 
colunas, tais como:
Produto Cartesiano (Cross Join). 
Junção Intrínseca (Inner Join). 
Junção Extrínseca (Outer Join). 
Tipos de junções 
Considere as tabelas:
Marca 
Modelo
Exemplo de tabelas
O comando irá trazer todos os registros. Irá listar pra 
cada registro da tabela da esquerda todos os registros 
da tabela da direita, formando um produto cartesiano. 
Produto cartesiano (Cross Join)
Considere o Có :digo
SELECT * FROM TABMARCA 
CROSS JOIN TABMODELO WHERE TABMARCA.MARCA_ID IN (1,2)
O resultado será:
Produto cartesiano (Cross Join)
Outra forma de representar é definindo na clausula from as 
tabelas separadas por virgula.
Considere o Có :digo
Produto cartesiano (Cross Join)
SELECT *
FROM TABMARCA, 
TABMODELO
WHERE TABMARCA.MARCAID 
IN (1,2)
Permite trazer um conjunto de dados de tabelas onde 
os campos estão relacionados através de igualdade. O 
comando impõe obrigatoriedade, ou seja trará apenas 
registros onde os valores de comparação são iguais 
nas duas tabelas da relação
Junção intrínseca (Inner Join)
Considere o Código:
SELECT *
FROM TABMARCA
INNER JOIN TABMODELO ON
TABMARCA.MARCA_ID = TABMODELO.MARCA_ID
O resultado será:
Junção intrínseca (Inner Join)
Permite trazer um conjunto de dados de tabelas onde 
os campos estão relacionados através de igualdade. O 
comando não impõe obrigatoriedade, ou seja, trará
registros onde os valores de comparação são iguais 
nas duas tabelas da relação, e os registros onde os 
valores de comparação não são iguais nas duas 
tabelas da relação. A cláusula OUTER JOIN possui os 
:tipos Left, Right e Full.
Junção extrínseca (Outer Join)
Considera a tabela da esquerda, ou seja, considerará
todos os registros da tabela da esquerda mesmo que a 
mesma não possua registros de referencia na tabela 
da direita.
Left Outer Join
Considere o Có :digo
SELECT *
FROM TABMARCA
LEFT OUTER JOIN TABMODELO ON
TABMARCA.MARCA_ID = TABMODELO.MARCA_ID
O resultado será:
Left Outer Join
Considera a tabela da direita, ou seja, considerará
todos os registros da tabela da direita mesmo que a 
mesma não possua registros de referência na tabela 
da esquerda.
Right Outer Join
Considere o có :digo
SELECT *
FROM TABMARCA
RIGHT OUTER JOIN TABMODELO ON
TABMARCA.MARCA_ID = TABMODELO.MARCA_ID
O resultado será:
Right Outer Join
Considerará todos os registros das duas tabelas, ou 
seja, considera todos os registros que possuem 
relação e os que não possuem relação.
Full Outer Join
Considere o có :digo
SELECT *
FROM TABMARCA
FULL OUTER JOIN TABMODELO ON
TABMARCA.MARCA_ID = TABMODELO.MARCA_ID
O resultado será:
Full Outer Join
Atividade
De acordo com a tabela abaixo, mostre o comando que 
traga o nome da cidade e a qual estado ela pertence.
Resultado esperado:
Atividade
Resoluçã :o
SELECT cidadeNome, estadoUF
FROM TabCidade
INNER JOIN TabEstado
ON cidadeEstadoID = estadoID
Conjuntos Regulares
Union All
Union
Intersect
Except
Junção vertical
Conjuntos regulares são resultados de instruções 
SELECT.
Que têm a mesma quantidade de colunas, não 
precisando ter o mesmo nome.
Cujas colunas têm os mesmos tipos de dados, 
entretanto, se o tipo de dado for CHAR ou VARCHAR, 
o tamanho também tem que ser o mesmo.
Conjuntos regulares
Considere o código
SELECT ING_ID,
ING_DESCRICAO 
FROM INGREDIENTE
O resultado será:
Conjuntos regulares
Considere o código
SELECT 
PRO_ID,PRO_DESCRICAO 
FROM PRODUTO 
O resultado será:
Une dos conjuntos regulares mostrando todas as 
linhas das duas tabelas, mesmo que uma linha já
tenha sido mostrada.
Union All
Considere o código:
SELECT ING_ID, ING_DESCRICAO
FROM INGREDIENTE
UNION ALL
SELECT PRO_ID, PRO_DESCRICAO
FROM PRODUTO
O resultado será:
Union All
Une dos conjuntos regulares mostrando todas não 
coincidentes e as que são coincidentes será exibida 
uma única vez.
Union
Considere o código:
SELECT ING_ID, ING_DESCRICAO
FROM INGREDIENTE
UNION
SELECT PRO_ID, PRO_DESCRICAO
FROM PRODUTO
O resultado será:
Union
Apresenta apenas as linhas onde os conteúdos são 
coincidentes e apenas os coincidentes, ou seja, 
possuem nas duas tabelas.
Intersect
Considere o código abaixo:
SELECT ING_ID, ING_DESCRICAO
FROM INGREDIENTE
INTERSECT
SELECT PRO_ID, PRO_DESCRICAO
FROM PRODUTO
O resultado será:
Intersect
Apresenta apenas as linhas do primeiro conjunto 
regular, e os conteúdos não são coincidentes.
Except
Considere o código abaixo:
SELECT ING_ID, ING_DESCRICAO
FROM INGREDIENTE
EXCEPT
SELECT PRO_ID, PRO_DESCRICAO
FROM PRODUTO
O resultado será:
Except
Atividade
Temos duas tabelas no banco de dados idênticas, porém com nomes 
diferentes, uma com o nome Produto e a outra como Mercadoria, 
conforme a imagem. Deseja-se saber quais produtos que contém na 
tabela produto e não são coincidentes com os produtos cadastrados 
na tabela Mercadoria. Mostre o comando SQL (select)
Atividade
Resoluçã :o
SELECT idProduto, nomeProduto, 
saldoProduto, precoProduto
FROM produto
EXCEPT
SELECT idMercadoria, nomeMercadoria, 
saldoMercadoria, precoMercadoria
FROM mercadoria
São códigos em T-SQL que podem ser classificados
como:
Agregação
Funções de Data
Funções de Usuários
Funções
São códigos em T-SQL que podem ser classificados
como:
Agregação
Funções de Data
Funções de Usuários
Funções
:Tipos
Count
Sum
Avg
Min
Max
Funções de agregação
COUNT 
Diferentemente das outras funções de grupo, o 
COUNT retorna o número de linhas que atende a 
uma determinada condição. Podemos utilizá-lo 
com um asterisco entre parênteses, para indicar 
que queremos saber a quantidade total de 
linhas, independentemente de haver linhas com 
colunas nulas ou não. 
Funções de agregação - COUNT
COUNT 
Se desejarmos saber quantos registros temos na 
tabela cliente:
SELECT COUNT(*) FROM CLIENTE 
Funções de agregação - COUNT
COUNT 
Se desejarmos saber quantos clientes são homens e 
quantos sã :o mulheres
SELECT SEXO, COUNT(*) 
FROM CLIENTE 
GROUP BY SEXO
Nesse caso devemos agrupar pela coluna SEXO, para 
isso devemos usar a cláusula GROUP BY
Funções de agregação - COUNT
SUM 
Retorna o valor total de uma determinada coluna 
em um determinado grupo de linhas. 
Funções de agregação - SUM
SUM 
Assim, se quisermos saber a somatória total dos 
produtos existentes na tabela PRODUTO: 
SELECT SUM(PRECO) 
FROM PRODUTO 
Funções de agregação - SUM
SUM 
Se desejarmos saber a somatória dos preços, 
separados pelo grupo, devemos usar a cláusula 
GROUP BY.
SELECT GRUPO_PROD,
SUM(PRECO)FROM PRODUTO 
GROUP BY GRUPO_PROD
Funções de agregação - SUM
AVG 
Extrai a média aritmética de um determinado grupo 
de linhas. Para saber o preço médio dos Produtos, 
execute o seguinte comando:
SELECT AVG(PRECO) FROM PRODUTO 
Funções de agregação - SUM
AVG 
Para saber a média aritmética dos preços dos 
produtos, dividido por grupo, devemos usar a 
cláusula GROUP BY.
SELECT GRUPO_PROD, AVG(PRECO) 
FROM PRODUTO 
GROUP BY GRUPO_PROD;
Funções de agregação - SUM
MIN 
Retorna o menor valor de uma coluna em um grupo 
de linhas. Podemos utilizá-la para colunas do tipo 
data ou alfanuméricas. Para saber o preço de venda 
mais barato da loja, execute o seguinte comando:
SELECT MIN(PRECO) FROM PRODUTO 
Funções de agregação - SUM
MIN 
Retornar o menor preço dos produtos, dividido por 
grupo.
SELECT GRUPO_PROD,
MIN(PRECO) 
FROM PRODUTO 
GROUP BY GRUPO_PROD;
Funções de agregação - SUM
MAX 
Retorna o maior valor de uma coluna em um grupo 
de linhas. Igualmente ao MIN, pode-se utilizá-la 
para colunas do tipo data ou alfanuméricas. Para 
saber qual é o produto mais caro da loja, execute o 
seguinte comando:
SELECT MAX(PRECO) FROM PRODUTO; 
Funções de agregação - SUM
MAX 
Retornar o maior preço dos produtos, separados 
por grupo, nesse caso devemos usar a cláusula 
GROUP BY:
SELECT GRUPO_PROD,
MAX(PRECO) 
FROM PRODUTO
GROUP BY GRUPO_PROD; 
Funções de agregação - SUM
Função que retorna a data e hora corrente do SQL 
Server, ou seja, se for instalado local retornará a data 
e hora local, caso for um banco instalado no servidor 
ele retornará a data e hora no servidor. 
:Exemplo
� SELECT GETDATE() as CurrentDateTime
Getdate()
DATEDIFF: Função que retorna um valor considerado 
um período. Para isso é necessário informar o 
intervalo desejado.
Sintaxe: DateDiff(intervalor, DataInicial, DataFinal)
� Exemplo:
Select DateDiff (YEAR, 
’01/01/2000’, DTNASCIMENTO) From
Cliente
Funções de data
:DATEADD Retorna um valor adicionando um 
intervalo determinado.
:Sintaxe DATEADD (intervalo, Valor,expressao)
:Exemplo 
� Select DATEADD(YEAR, -
5,DTNASCIMENTO) From Cliente
Funções de data
DATEPART: Retorna um valor particionado da data.
:Sintaxe DATEPART (intervalo, Valor,expressao)
Exemplo:
Select DATEPART(YEAR, DTNASCIMENTO) 
From Cliente
Funções de data
YEAR: Retorna o valor numérico correspondente ao ano.
:Exemplo Select Year(dtnascimento) from cliente
MONTH: Retorna o valor numérico correspondente ao 
mês.
:Exemplo Select Month(dtnascimento) from cliente
DAY: Retorna o valor numérico correspondente ao dia.
:Exemplo Select Day(dtnascimento) from cliente
Funções de data
Cast:
É uma função do padrão ANSI.
Convert:
É uma função do engine do SQl Server. Convert
pode ser utilizado para formatação de valores do 
tipo datetime, money,... . 
Funções de conversão 
Cast:
:Sintaxe
� CAST( expression as _date type[lenght] ;)
Funções de conversão - CAST 
:Exemplo
/Transformar a data hora atual, retornado pelo 
GETDATE() em somente hora
� SELECT CAST(getdate() as TIME);
Funções de conversão - CAST 
:CONVERT
:Sintaxe
� CAST( _date type[lenght], expression [,style] ;)
Funções de conversão - CONVERT
:Fonte 
:// / / /https sqlgo.files.wordpress.com 2011 04 image28.png
Funções de conversão - CONVERT
:Exemplo
Converter a data e hora atual (getdate()) usando a tabela de 
:estilos
SELECT CONVERT( VARCHAR, GETDATE(),111) as '111'
SELECT CONVERT( VARCHAR, GETDATE(),113) as '113'
Funções de conversão - CONVERT
São funções definidas pelo próprio usuário, que não 
fazem parte do conjunto de funções do SQL Server, 
essas funções podem retornar valores comuns ou 
mesmo um valor no formato de uma tabela, com 
múltiplas ocorrências. 
Function – Funções de usuário
Sintaxe (Retornando um único valor) 
CREATE FUNCTION <<NOME FUNÇÃO>> (<<PARAMETROS>> ) 
RETURNS <<TIPO RETORNO>> 
AS 
BEGIN 
DECLARE <<VARIAVEL DE RETORNO>> <<TIPO>> 
<< CODIGO IMPLEMENTADO>> 
RETURN <<VARIAVEL DE RETORNO>> 
END 
Function – Funções de usuários
Sintaxe (Retornando uma tabela) 
CREATE FUNCTION <<NOME FUNÇÃO>> (<<PARAMETROS>> ) 
RETURNS <<NomeTabela>> Table ( <<campo>> <<tipo>>) 
AS 
BEGIN 
<< CODIGO IMPLEMENTADO>> 
RETURN 
END 
Function – Funções de usuários
Exemplo – Função que retorna o dia da semana
Function – Funções de usuários
Exemplo – Função que retorna o dia da semana
Executando a função
Resultado
Function – Funções de usuários
Atividade
Criar uma função de usuário que retorne o nome do 
Mês de uma determinada data
Atividade
Resoluçã :o
No computador
Obrigado
71