Funções SQL para Análise de Dados

Prévia do material em texto

Seminários - III
SQL Funções - 1
Análise e Desenvolvimento de Sistemas
Professor : 
ROBERTO YUKIO NISHIMURA
ESPECIALISTA EM ADMINISTRAÇÃO DA 
ENGENHARIA DE SOFTWARE
SEMINÁRIOS - III
SQL Funções - 1
Modelo utilizado
Modelo utilizado
Funções
COUNT (*) – conta quantos registros estão 
sendo selecionados.
Ex. select count(*) from carro;
select count(*) from modelo where
dir_hid_modelo = ‘s’;
SUM ( ) – soma o conteúdo do campo dos 
registros que estão sendo selecionados;
Ex. select sum(nr_passageiro_modelo) from
modelo;
Max ( ) – identifica o maior valor do campo 
selecionado.
Ex. select Max(dt_ano_carro) from carro;
select max(qt_mala_modelo) from modelo;
Min ( ) – identifica o menor valor do campo 
selecionado.
Ex. select Min(dt_ano_carro) from carro;
select min(qt_mala_modelo) from modelo;
Funções
Funções
Avg ( ) – identifica o valor médio do campo 
selecionado.
Ex. select Avg(dt_ano_carro) from carro;
select avg(qt_mala_modelo) from modelo;
LENGTH ( ) – retorna a quantidade de caracteres na 
string selecionada.
Ex. select Length(nm_cliente), nm_cliente from
cliente;
select length(nm_modelo), nm_modelo from
modelo;
Funções
SUBSTR(X,Y,Z) – retornará a quantidade de Y 
caracteres de X a partir da posição Z.
Ex. select Substr(nm_cliente, 3, 4), nm_cliente from
cliente;
select substr(nm_modelo, 2, 3), nm_modelo
from modelo;
Seminários - III
SQL Funções - 2
Análise e Desenvolvimento de Sistemas
Professor : 
ROBERTO YUKIO NISHIMURA
ESPECIALISTA EM ADMINISTRAÇÃO DA 
ENGENHARIA DE SOFTWARE
SEMINÁRIOS - III
SQL Funções - 2
LOWER ( ) – retorna todos os caracteres em letras 
minúsculas.
Ex. select Lower(id_placa_carro), id_placa_carro
from carro;
UPPER ( ) – retorna todos os caracteres em letras 
maiúsculas.
Ex. select Upper(nm_cliente), nm_cliente from
cliente;
Funções
Funções
INITCAP ( ) – retorna o primeiro carácter de 
qualquer palavra em maiúsculo e o restante dos 
caracteres da palavra em minúsculo.
Ex. select Initcap(nm_modelo), nm_modelo from
modelo;
select initcap(‘aVeNida sAO pauLo’) from dual;
TRIM(X from Y) – retira 1 carácter definido em X a 
esquerda ou a direita de Y.
Ex. select Trim(‘r’ from nm_cliente) from cliente;
select trim(‘o’ from nm_cliente) from cliente;
Funções
LTRIM(X,Y) – retira da string X o valor definido em Y, 
da esquerda para a direita.
Ex. select Ltrim(nm_cliente,’r’), nm_cliente from
cliente;
select ltrim(nm_cor_carro,’pr’), nm_cor_carro
from carro;
RTRIM(X,Y) – igual ao LTRIM só que iniciando da 
direita para a esquerda.
Ex. select Rtrim(nm_cor_carro,’o’), nm_cor_carro
from carro;
LPAD(X,Y,Z) – esta função defini um tamanho de 
retorno Y da string X, se X for menor, completará 
com Z da esquerda para direita, se X for maior, 
cortará os caracteres da direita para esquerda.
Ex. select Lpad(nm_modelo,5,’.’), nm_modelo from
modelo;
select lpad(nm_modelo,8,’#’), nm_modelo from
modelo;
Funções
Funções
RPAD(X,Y,Z) – esta função defini um tamanho de 
retorno Y da string X, se X for menor, completará 
com Z da direita para esquerda, se X for maior, 
cortará os caracteres da direita para esquerda.
Ex. select Rpad(nm_modelo,5,’.’), nm_modelo from
modelo;
select rpad(nm_modelo,8,’#’), nm_modelo from
modelo;
Seminários - III
SQL Funções - 3
Análise e Desenvolvimento de Sistemas
Professor : 
ROBERTO YUKIO NISHIMURA
ESPECIALISTA EM ADMINISTRAÇÃO DA 
ENGENHARIA DE SOFTWARE
SEMINÁRIOS - III
SQL Funções - 3
O SGBD Oracle possui funções específicas para o 
tratamento de campos tipo DATA.
SYSDATE – retorna a data atual do servidor onde 
está instalado o Banco de Dados. GETDATE
Ex. select sysdate from dual;
O SYSDATE aceita adicionar ou subtrair números 
inteiros (equivalentes a dias).
Ex. select sysdate + 1, sysdate, sysdate – 1 from
dual;
Funções de Data
Funções de Data
TO_DATE(X,Y) – converte a string X em data com a 
máscara definida em Y.
Ex. select to_date(‘01/23/2018’,’mm/dd/yyyy’) from
dual;
select to_date(‘2018/06/30’,’yyyy/mm/dd’) from
dual;
ADD_MONTHS(X,Y) – adiciona Y meses sobre a data 
definida em X.
Ex. select add_months(sysdate, 3) from dual;
select add_months
(to_date(‘15/05/2018’,’dd/mm/yyyy’), 6) from dual;
Funções de Data
Funções de Data
MONTHS_BETWEEN(X,Y) – retorna a quantidade de 
meses entre as datas X e Y.
Ex. select months_between (sysdate, sysdate+ 100) 
from dual;
select months_between
(to_date(’01/01/2000’,’dd/mm/yyyy’) , sysdate) from
dual;
select months_between (sysdate, 
to_date(‘01/01/2000’,’dd/mm/yyyy’)) from dual;
NEXT_DAY(X,Y) – retornará o próximo dia da 
semana descrito em Y em relação a data definida 
em X.
Ex. select next_day(sysdate,’domingo’) from dual;
select next_day(sysdate,’sexta-feira’) from dual;
select next_day(sysdate,’segunda’) from dual;
Funções de Data
Funções de Data
LAST_DAY( ) – retorna o último dia do mês.
Ex. select last_day(sysdate) from dual;
select last_day(‘01/01/2018’) from dual;
select last_day(‘2017/12/25’) from dual; (dá erro)
CURRENT_DATE – retorna a data corrente na zona 
de tempo da sessão do usuário.
Ex. select current_date from dual;
select current_date, sysdate from dual;
* importante quando for desenvolver sistemas que 
utilizem fusos horários ou quando o banco de 
dados ficar hospedado em nuvem.
Funções de Data
Seminários - III
SQL - 4
Análise e Desenvolvimento de Sistemas
Professor : 
ROBERTO YUKIO NISHIMURA
ESPECIALISTA EM ADMINISTRAÇÃO DA 
ENGENHARIA DE SOFTWARE
SEMINÁRIOS - III
SQL - 4
* - substitui o nome de todas as colunas de uma 
tabela no comando SELECT.
Ex. select * from cliente;
select * from modelo;
select * from carro;
select * from reserva;
SQL
LIKE – permite a pesquisa dentro de um campo 
string de carácter.
% - quantos caracteres forem necessários.
Ex. select * from cliente where nm_cliente like ‘%’;
select * from cliente where nm_cliente like ‘r%’;
select * from cliente where nm_cliente like ‘%o’;
select * from cliente where nm_cliente like
‘%n%’;
select * from cliente where nm_cliente like ‘r%o’;
SQL
LIKE – permite a pesquisa dentro de um campo 
string de carácter.
_ - 1 carácter apenas.
Ex. select * from cliente where nm_cliente like ‘_’;
select * from cliente where nm_cliente like
‘r_berto’;
select * from cliente where nm_cliente like
‘rober_o’;
select * from cliente where nm_cliente like
‘_nderso_’;
SQL
SQL
select * from cliente where nm_cliente like ‘_o%’;
select * from cliente where nm_cliente like ‘%n_’;
Select * from cliente where nm_cliente like ‘%o%o’;
BETWEEN – entre dois valores;
Ex. select * from cliente where id_cpf_cliente
between ‘33333333333’ and ’66666666666’;
select nm_modelo, qt_mala_modelo from
modelo where qt_mala_modelo between 3 and 6;
SQL
SQL
IN – filtro com lista de valores.
Ex. select * from cliente where id_cpf_cliente IN 
(‘11111111111’, ‘33333333333’, ‘55555555555’);
select * from reserva where id_placa_carro IN 
(‘HND0123’, ‘VWB7125’, ‘FRD5153’);
Seminários - III
SQL - 5
Análise e Desenvolvimento de Sistemas
Professor : 
ROBERTO YUKIO NISHIMURA
ESPECIALISTA EM ADMINISTRAÇÃO DA 
ENGENHARIA DE SOFTWARE
SEMINÁRIOS - III
SQL - 5
CONCAT – concatenar 2 campos apresentando com 
um outro nome.
Ex. select CONCAT(id_placa_carro, 
nm_marca_carro) placa_carro from carro;
select CONCAT (nm_marca_carro, nm_modelo) 
carro_modelo from carro, modelo where
carro.id_modelo = modelo.id_modelo;
SQL
SQL
ORDER BY – classificação do resultado apresentado, 
mas não altera ordem dentro da tabela.Ex. select * from cliente ORDER BY nm_cliente;
select * from cliente order by nm_cliente ASC;
select * from cliente order by nm_cliente DESC;
select * from carro order by nm_cor_carro, 
dt_ano_carro;
select * from carro order by 3,4;
desc carro para ver a ordem dos campos.
SQL
DISTINCT – apresenta somente 1 ocorrência de um 
registro que tenha resultados repetidos.
Ex. select id_cpf_cliente from reserva;
select DISTINCT(id_cpf_cliente) from reserva;
select id_placa_carro from reserva;
select DISTINCT(id_placa_carro) from reserva;
SQL
GROUP BY – seleção de dados por agrupamento de 
campo ou valor ou expressão.
Ex. select count(*), qt_mala_modelo from modelo 
group by qt_mala_modelo;
select count(*), qt_mala_modelo, 
max(qt_mala_modelo), min(qt_mala_modelo) 
from modelo group by qt_mala_modelo;
SQL
HAVING – condição de busca e agrupamento.
Ex. select count(*), qt_mala_modelo, 
max(qt_mala_modelo), min(qt_mala_modelo) from
modelo group by qt_mala_modelo HAVING 
qt_mala_modelo > 5;
select count(*), qt_mala_modelo, 
max(qt_mala_modelo), 
min(qt_mala_modelo), avg(qt_mala_modelo) 
from modelo group by qt_mala_modelo HAVING 
AVG(qt_mala_modelo) > 5;
Seminários - III
SQL View - 5
Análise e Desenvolvimento de Sistemas
Professor : 
ROBERTO YUKIO NISHIMURA
ESPECIALISTA EM ADMINISTRAÇÃO DA 
ENGENHARIA DE SOFTWARE
SEMINÁRIOS - III
SQL VIEW - 6
VIEW é uma tabela virtual baseado no resultado de 
um comando select.
VIEW pode ser criada com apenas alguns campos 
da tabela.
VIEW pode ser criada com apenas algumas linhas 
da tabela.
VIEW pode ser criada com dados de mais de uma 
tabela.
SQL VIEW
SQL VIEW
CREATE VIEW <nome da visão> AS SELECT 
<campos> FROM <tabelas> WHERE <condição>;
Ex. create view CAR1 as select id_placa_carro, 
nm_modelo, nm_marca_carro from carro, modelo 
where carro.id_modelo = modelo.id_modelo;
Desc car1;
Select * from car1;
SQL VIEW
create view CAR2 as select id_placa_carro, 
nm_modelo, nm_marca_carro, nm_cor_carro, 
tp_cambio_modelo from carro, modelo where
carro.id_modelo = modelo.id_modelo and
tp_cambio_modelo = ‘a’;
Desc car2;
Select * from car2;
SQL VIEW
Insert into CAR1 values (‘ABC9876’, ‘sentra’,’nissan’);
Dá erro.
“não é possível modificar uma tabela base através 
de uma view junção”.
SQL VIEW
create view CAR3 as select id_modelo, nm_modelo, 
nr_porta_modelo, dir_hid_modelo from modelo;
Insert into car3 values (6,’sentra’,4,’s’);
Em visões simples, é possível realizar insert.
Tem que verificar campos obrigatórios (notnull).
SQL VIEW
DROP VIEW remove a visão sem afetar as tabelas 
que originaram a visão.
Drop view car1;
Drop view car2;
Drop view car3;