Revisão - Comandos SQL

Prévia do material em texto

Funções em SQL
Um ou mais INPUTS retornam um OUTPUT.
Single-row: Retornam um resultado por linha.
Multi-row: Retornam um resultado por grupo de linhas.
Funções Single-row
 Argumentos podem ser colunas ou expressoes.
Podem ser usadas no select, where e order by.
Podem ser do tipo: caracter, numerica, conversçao e de data.
Caracter:
Lower: Converte caracteres para minuscula.
Upper: Converte caracteres para maiuscula.
Initcap: Converte a primeira letra de cada palavra do argumento para maiuscula.
Concat: Converte dois argumentos, igual ao pipe (||).
Substr(String, P,T): Retorna os caracteres da String iniciando na posição P e tamanho T. Se P for negativo começa a partir do final da String. Se tamanho é omitido retorna a partir do final de P.
Lenght: Retorna o tamanho da String.
Replace(String busca, subs): Substitui uma String de busca na String fornecida pela String de substituição.
Numericas:
Round(N,D): Arredonda o numero N para D decimais.
Exemplo:
	Select round (45926,2)
	From dual 
	Round;
Trunc(N,D): Trunca o numero N para o decimal D.
Exemplo:
	Select trunc(45926,2)
	From dual;
Mod(D,d): Retorna o resto da divisão da D por d.
Data:
Oracle armazena em colunas date: dia, hora, mes, ano, minuto e segundos.
A mascara de visualização depende da linguagem usada.
Em portugues o padrão é DD/MM/AAAA
Sysdate – Retorna a data do sistema.
Exemplo:
	Select sysdate
	From dual;
Sysdate pode ser usado em aritmética:
Select sysdate +1
From dual;
Select sysdate – date_nasc
From emp;
Months between (D1,D2): numero de meses entre as datas.
	Select nome
	From dependentes
	Where months_between (sysdate, dat_inscr) <-12(ultimos 12 meses);
Add_months: Adiciona meses para uma data.
	Select add_months(dat_nasc, 12)
	From dual;
Last_day: Ultimo dia do mes da data informada.
	Select cast_day(sysdate)
	From dual;
Round e Trunc podem ser usados em data, sem orçamento de decimal.
Select trunc(sysdate)
From dual;
Conversão:
Implicita: Ocorre se o caracter representa um numero/data valido.
To_char: Numero/ data para carcter.
Exemplo:
	Select to_char(sysdate, ‘DD/MM/ YYYY’)
	From dual;
	Select to_char (sysdate, ‘DD/MM/YYYY HH24:MI:SS’)
	From dual;
To_number: char para numero.
To_date: char para data.
	Select nome
From emp
Where dat_nasc= to_date(‘04/01/2000’, ‘DD/MM/YYYY’)
Decode(Estado,’RJ’, salario*10,’SP’, salario*20, salario)
Funções podem ser alinhadas
Exemplo:
Select nome, upper(substr(nome,1,8),||’.com’) nome2
From emp;
	
Tratando Nulos
Função NVL: Susbtitui o NULL por algo requirido.
Select NULL(salario,0)
From emp;
Select NULL(dat_nasc,’01/01/2000’)
 .
 .
 .
 NULL(nome, ‘sem nome’)
Decode: Igual if/else
Usados where/select
Funções multi-row/grupo
AVG: media, ignora nulos
SUM: soma, ignora nulos
MAX: maximo, ignora nulos
MIN: minimo, ignora nulos
COUNT: Contagem de registros não nulos para a coluna fornecida para a função.
COUNT(*): Numero de linhas da tabela, inclui nulos.
Pode-se colocar o distinct dentro para remover duplicados
 COUNT(distinct nome)
	Select sum(salario)
From emp
Where dep_id=100;
Se quiser incluir os valores nulos no calculo de funções de agrupamento, usar NULL.
Select AVG(NULL (salario,0))
From emp;
Select count(salario)
From emp
Where dep_id=100;
Group by
Usar a função em subgrupos
Select dep_id, avg(salario)
From emp
Groups by dep_id
Order by 1;
Todas as colunas que estão no select e que não são argumentos das funções de grupo, devem aparecer no group by.
Select dep_id, tipo_id , avg(salario)
From emp
Group by dep_id, tipo-id
Order by 1,2;
Não pode-se usar funções de grupo na clausula where para filtrar linhas.
Having
		
	Select dep_id, tipo_id, avg(salario)
	From emp
	Group by dep_id, tipo_id
	Having avg (salario>100)
	Order by 1;
JOINS
Para usar dados de mais de uma tabela em uma única query.
Padrão ANSI 99
Padrão ‘mercado’
 Emp Depto Locais 
 Col_id 
Cidade
Estado
Codep
Nome
Codemp
Nome
Sexo
Codep
 
Using
Select colunas
From tab1 join tab2
Using (colx);
Realiza um equijoin/invejoin com uma coluna de mesmo nome.
Using não pode ter apelidos tem que usar o nome da coluna em comum.
Select emp.nome, depto.nome
From emp join depto
Using (codep)
Where emp.codep=100;
On
	Select colunas
	From tab1 join tab2
On (tab1.c1=tab2.c2);
Realiza o equijoin na condição da clausula ON.
Parenteses opcionais.
Select e.nome,d.nome
From emp e join depto d
On (e.codep=d.codep)
Where, codep=100;
Pode-se usar apelidos para as tabelas.
Mais de duas tabelas
	Select e.nome, d.nome, l.cidade
From emp e join depto d
On d.depto=e.depto
Join locais l
On d.col_id=c.loc_id
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
Select e.nome, d.nome, l.cidade
From emp e join depto d
Using (codep)
Join locais l
Using(loc_id)
Where l.ciade e ‘RJ’ and e.cod=100;
Sintaxe ‘mercado’/oracle
Emparelhamento na clausula where
Select e.nome, d.nome
From emp e, depto d
Where e.codep=d.codep
And e.codep=100
And sexo=’M’;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
	Select sexo, codemp, d.nome, cidade
	From emp e, depto d, locais l
	Where e.codep=d.codep
	And d.col_id=d.codep
	And sexo=’M’
	And d.codep=100;
Auto-relacionamento
	Select e1.nome, e2.nome
	From emp e1, emp e2
	Where e1.codemp=e2.codger
	And sexo=’M’
	Order by 2;
Outer join
Retorna linhas que não atendem ao emparelhamento
Left outer join
Right outer join
Full outer join
Left
	Select e.nome, d.nome
	From emp e left outer join depto d
	On(e.codep=d.codep);
Retorna todos os empregados mesmo os que não possuem deptos.
Right
	Select e.nome, d.nome
From emp e right outer join depto d
On(e.codep=d.codep);
Irá retornar todos os departamentos, mesmo os que não possuem empregados.
Full
	Select e.nome, d.nome
	From emp e full outer join depto d
	On(e.codep=d.codep);
Irá retornar todos empregados/deptos
Outer joins
Usar sintax ANSI
Se a condição de emparelhamento for omitida resulta em um produto cartesiano.
Exemplo:
	Select e.nome, d.nome
	From emp e, depto d;
Em ANSI: Usa-se o cross join.