— Comando SQL
select email, first_name, last_name from sales.customers
— Consulta com DISTINCT
select distinct city from sales.customers order by city
— Comando SQL
select * from sales.customers
— Consulta com DISTINCT
select distinct brand, model_year from sales.products order by brand
— Consulta com filtro
select email, state from sales.customers where state = ‘SC’
— Consulta com filtro
select * from sales.customers where state = ‘SC’
— Consulta com filtro
select email, state, birth_date from sales.customers where (state = ‘SC’ or state = ‘MG’) and birth_date < ‘1991-12-28’
order by email limit 10
— Consulta com ordenação
select * from sales.products order by price desc limit 10
— Consulta com DISTINCT
select distinct state from sales.customers order by state
— Consulta com filtro
select distinct city from sales.customers where state = ‘MG’ order by city
— Consulta com ordenação
select * from sales.funnel order by add_to_cart_date desc limit 10
— Comando SQL
select * from sales.funnel
— Comando SQL
select * from sales.customers limit 10
— Comando SQL
select email, birth_date, (current_date – birth_date) / 365 as anos from sales.customers limit 10
— Comando SQL
select email, birth_date, (current_date – birth_date) / 365 as “idade do cliente” from sales.customers limit 10
— Consulta com ordenação
select email, birth_date, (current_date – birth_date) / 365 as “idade do cliente” from sales.customers order by “idade do cliente” limit 10
— Consulta com ordenação
select first_name || ‘ ‘ || last_name “Nome Completo”, email, birth_date, (current_date – birth_date) / 365 as “idade do cliente” from sales.customers order by “idade do cliente” limit 10
— Comando SQL
select
customer_id,
first_name,
professional_status,
(professional_status = ‘clt’) as “Cliente CLT”
from sales.customers limit 10
— Consulta com filtro
select price, brand, model_year from sales.products where price between 50900 and 60000 order by brand
— Consulta com filtro
select price, brand, model_year from sales.products where price not between 50900 and 60000 order by brand
— Consulta com filtro
select price, brand, model_year from sales.products where brand in(‘HONDA’, ‘FIAT’)
— Consulta com filtro
select price, brand, model_year from sales.products where brand NOT in(‘HONDA’, ‘FIAT’)
— Consulta com filtro
select * from sales.customers where FIRsT_NAME LIKE ‘%ANA%’
— Consulta com filtro
select * from sales.customers where FIRsT_NAME ILIKE ‘%ana%’
— Consulta com filtro
select * from temp_tables.regions where population is null
— Função COUNT
select count(*) from sales.funnel
— Função COUNT
select count(paid_date) from sales.funnel
— Consulta com DISTINCT
select count(distinct product_id) from sales.funnel
— Funções de agregação
select min(price), max(price), avg(price) from sales.products
— Consulta com filtro
select * from sales.products where price = (Select max(price) from sales.products)
— Agrupamento de dados
select state, count(*) contagem from sales.customers group by state order by contagem desc
— Agrupamento de dados
select state, professional_status, count(*) contagem from sales.customers group by state, professional_status order by contagem desc
— Agrupamento de dados
select state from sales.customers group by state
— Agrupamento de dados
select state, count(*) from sales.customers group by state having count(*) > 10
— Comando SQL
select * from temp_tables.tabela_1
— Comando SQL
select * from temp_tables.tabela_2
— Comando SQL
select t1.cpf, t1.name, t2.state
from temp_tables.tabela_1 as t1
left join temp_tables.tabela_2 as t2
on t1.cpf = t2.cpf
— Comando SQL
select t1.cpf, t1.name, t2.state
from temp_tables.tabela_1 as t1
right join temp_tables.tabela_2 as t2
on t1.cpf = t2.cpf
— Comando SQL
select t1.cpf, t1.name, t2.state
from temp_tables.tabela_1 as t1
inner join temp_tables.tabela_2 as t2
on t1.cpf = t2.cpf
— Comando SQL
select t1.cpf, t1.name, t2.state
from temp_tables.tabela_1 as t1
full join temp_tables.tabela_2 as t2
on t1.cpf = t2.cpf
— Função COUNT
select cus.professional_status, count(fun.paid_date) as pagamentos
from sales.funnel as fun
left join sales.customers as cus
on fun.customer_id = cus.customer_id
group by cus.professional_status
order by pagamentos
— Comando SQL
select * from temp_tables.ibge_genders limit 10
— Comando SQL
select * from sales.funnel
— Comando SQL
select
ibge.gender,
count(fun.paid_date)
from sales.funnel as fun
left join sales.customers as cus
on fun.customer_id = cus.customer_id
left join temp_tables.ibge_genders as ibge
on lower(cus.first_name) = ibge.first_name
group by ibge.gender
–Union
— Comando SQL
select * from sales.products
union all
— Comando SQL
select * from temp_tables.products_2
–Subquery 01 no where
— Consulta com filtro
select * from sales.products where price = (select min(price) from sales.products)
–Subquery 02 no with
with alguma_tabela as (select professional_status, (current_date – birth_date) / 365 as idade
from sales.customers )
— Funções de agregação
select professional_status, avg(idade) as idade_media from alguma_tabela
group by professional_status
–Subquery 03 no from
— Funções de agregação
select professional_status, avg(idade) as idade_media from (select professional_status, (current_date – birth_date) / 365 as idade
from sales.customers ) as alguma_tabela
group by professional_status
— (Exemplo 4) Subquery no SELECT
— Na tabela sales.funnel crie uma coluna que informe o nº de visitas acumuladas
— que a loja visitada recebeu até o momento
— Comando SQL
select
fun.visit_id,
fun.visit_page_date,
sto.store_name,
(
— Função COUNT
select count(*)
from sales.funnel as fun2
where fun2.visit_page_date <= fun.visit_page_date
and fun2.store_id = fun.store_id
) as visitas_acumuladas
from sales.funnel as fun
left join sales.stores as sto
on fun.store_id = sto.store_id
order by sto.store_name, fun.visit_page_date
with primeira_visita as (
— Funções de agregação
select customer_id, min(visit_page_date) as visita_1
from sales.funnel
group by customer_id
)
— Comando SQL
select
fun.visit_page_date,
(fun.visit_page_date <> primeira_visita.visita_1) as lead_recorrente,
count(*)
from sales.funnel as fun
left join primeira_visita
on fun.customer_id = primeira_visita.customer_id
group by fun.visit_page_date, lead_recorrente
order by fun.visit_page_date desc, lead_recorrente
— Comando SQL
select ‘2021-10-01’::date – ‘2021-09-01’::date
— Comando SQL
select ‘100’::numeric – ’10’::numeric
— Comando SQL
select replace(112122::text,’1′,’A’)
— Comando SQL
select cast(‘2021-10-01’ as date) – cast(‘2021-09-01’ as date)
with faixa_de_renda as (
— Comando SQL
select income,
case
when income < 5000 then ‘0-5000’
when income >= 5000 and income < 10000 then ‘5000-10000’
when income >= 10000 and income <15000 then ‘10000-15000’
else ‘15000+’
end as faixa_renda
from sales.customers
)
— Função COUNT
Select faixa_renda, count(*)
from faixa_de_renda
group by faixa_renda
— Consulta com filtro
select * from temp_tables.regions where population is null
–case when
— Comando SQL
select *, case when population is not null then population
else (select avg(population) from temp_tables.regions)
end as populacao_ajustada
from temp_tables.regions
where population is null
–coalesce
— Funções de agregação
select *, coalesce(population, (select avg(population) from temp_tables.regions))
from temp_tables.regions
where population is null
— Comando SQL
select UPPER(‘São Paulo’) = ‘SÃO PAULO’
— Comando SQL
select lower(‘São Paulo’) = ‘são paulo’
— Comando SQL
select trim(‘São Paulo ‘) = ‘São Paulo’
— Comando SQL
select replace(‘SAO PAULO’,’SAO’, ‘SÃO’) = ‘SÃO PAULO’
— somando data em dias
— Comando SQL
select current_date + 10
— somando data em semana
— Comando SQL
select current_date + interval ’10 weeks’
— somando data em meses
— Comando SQL
select (current_date + interval ’10 weeks’)::date
— somando em horas
— Comando SQL
select (current_date + interval ’10 hours’)
— diferença de dias
— Comando SQL
select (current_date – ’01-06-2018′)
— diferença de dias / por semana
— Comando SQL
select (current_date – ’01-06-2018′) / 7
— diferença de dias / por mes
— Comando SQL
select (current_date – ’01-06-2018′) / 30
— diferença de dias / por ano
— Comando SQL
select (current_date – ’01-06-2018′) / 365
— criando função
— Criação de função
create function datediff(unidade varchar, data_inicial date, data_final date)
returns integer
language sql
as
$$
— Comando SQL
select
case
when unidade in(‘d’,’day’,’days’) then (data_final – data_inicial)
when unidade in(‘w’,’week’,’weeks’) then (data_final – data_inicial) / 7
when unidade in(‘m’,’month’,’months’) then (data_final – data_inicial) / 30
when unidade in(‘y’,’year’,’years’) then (data_final – data_inicial) / 365
end as diferenca
$$
— Comando SQL
select datediff(‘day’, ‘2021-02-04’::date, current_date)
— Comando SQL
select datediff(‘w’, ‘2021-02-04’::date, current_date)
— Comando SQL
select datediff(‘m’, ‘2021-02-04’::date, current_date)
— Comando SQL
select customer_id, datediff(‘years’, birth_date, current_date) as idade_cliente
into temp_tables.customers_age
from sales.customers
— Comando SQL
select * from temp_tables.customers_age
— Consulta com DISTINCT
select distinct professional_status from sales.customers
— Criação de tabela
create table temp_tables.profissoes(
professional_status varchar,
status_profissional varchar
)
— Comando SQL
select * from temp_tables.profissoes
— Inserção de dados
insert into temp_tables.profissoes (professional_status, status_profissional)
values
(‘freelancer’, ‘freelancer’),
(‘retired’, ‘aposentado(a)’),
(‘clt’,’clt’),
(‘self_employed’, ‘autônomo(a)’),
(‘other’,’outro’),
(‘businessman’,’empresário(a)’),
(‘civil_servant’,’funcionário publico(a)’),
(‘student’,’estudante’)
— Inserção de dados
insert into temp_tables.profissoes (professional_status, status_profissional)
values
(‘unemployed’,’desempregado(a)’),
(‘trainee’,’estagiário(a)’)
— Atualização de dados
update temp_tables.profissoes set professional_status = ‘intern’
where status_profissional = ‘estagiário(a)’
— Exclusão de dados
delete from temp_tables.profissoes where status_profissional = ‘estagiário(a)’
— Alteração de estrutura da tabela
alter table sales.customers add customer_age int
— Comando SQL
select * from sales.customers
— Atualização de dados
update sales.customers
set customer_age = datediff(‘years’, birth_date, current_date)
where true
— Alteração de estrutura da tabela
alter table sales.customers alter column customer_age type varchar
— Alteração de estrutura da tabela
alter table sales.customers rename column customer_age to age
— Alteração de estrutura da tabela
alter table sales.customers drop column idade
- 📌 01 – SELECT
- 📌 02 – WHERE
- 📌 03 – ORDER BY
- 📌 04 – DISTINCT
- 📌 05 – LIMIT
- 📌 06 – BETWEEN
- 📌 07 – LIKE e ILIKE
- 📌 08 – Funções de Agregação (COUNT, AVG, MIN, MAX)
- 📌 09 – GROUP BY e HAVING
- 📌 10 – JOIN (LEFT, RIGHT, INNER, FULL)
- 📌 11 – UNION
- 📌 12 – SUBQUERIES
- 📌 13 – CASE e COALESCE
- 📌 14 – Manipulação de Datas
- 📌 15 – CAST e Conversões
- 📌 16 – CREATE FUNCTION
- 📌 17 – CREATE TABLE
- 📌 18 – INSERT
- 📌 19 – UPDATE
- 📌 20 – DELETE
- 📌 21 – ALTER TABLE
