Guias › SQL › Avançado
SQL Avançado: CTE com WITH, CTE com valor de referência, CTE + JOIN e mais (lições 1 a 5)
SQL é a linguagem que conversa com bancos de dados: com poucas palavras você pergunta ao banco o que quer saber e ele responde com uma tabela. É a base do trabalho de quem analisa dados.
Neste guia você vai ver as lições 1 a 5 do curso de SQL (nível Avançado). Cada lição explica o assunto em quatro partes: o que é, para que serve, como usar e o que você vai ver na tela. No final, há um projeto para juntar tudo o que aprendeu.
Lição 1: CTE com WITH
Uma CTE dá nome a uma consulta intermediária e deixa o código mais legível: WITH nome AS (SELECT ...) SELECT ... FROM nome. Tabelas: funcionarios(id, nome, depto_id, cargo, salario, admissao, gerente_id) e departamentos(id, nome, cidade).
O que é
CTE (a gente escreve WITH) é dar um nome a uma consulta pequena, como se ela fosse uma tabela de rascunho. A consulta principal usa esse rascunho logo em seguida.
Para que serve
Serve para quebrar uma consulta grande e complicada em pedaços, como resolver um problema de matemática em etapas. Fica mais fácil de ler e de consertar.
Como você vai usar
Escreva WITH ti AS (SELECT * FROM funcionarios WHERE depto_id = 2) e logo depois o SELECT principal, usando o nome que você criou: SELECT nome, salario FROM ti ORDER BY salario DESC.
O que vai aparecer
2 colunas (nome e salario) e 4 linhas: as pessoas do departamento 2, do maior salário (Carlos, 15000) ao menor.
Exemplo
WITH altos AS (SELECT * FROM funcionarios WHERE salario > 8000) SELECT nome FROM altosLição 2: CTE com valor de referência
Uma CTE pode guardar um número de referência (como a média) para comparar com cada linha. Com mais de uma CTE, separe por vírgula.
O que é
Uma CTE também pode guardar um número só, como a média geral, para você comparar com cada linha. Se tiver mais de uma CTE, separe uma da outra com vírgula.
Para que serve
Serve para calcular um valor uma única vez e usá-lo para comparar com todo mundo: quem ganha acima da média?
Como você vai usar
Crie WITH media_geral AS (SELECT AVG(salario) AS m FROM funcionarios) e depois peneire com FROM funcionarios, media_geral WHERE salario > m.
O que vai aparecer
2 colunas (nome e salario) e 4 linhas: Carlos, Marina, Paulo e Renata, as pessoas que ganham acima da média.
Exemplo
WITH caros AS (SELECT * FROM funcionarios WHERE salario > 5000), ti AS (SELECT * FROM caros WHERE depto_id = 2) SELECT nome, salario FROM tiLição 3: CTE + JOIN
Resuma numa CTE e junte o resultado com outra tabela.
O que é
Você pode fazer um resumo dentro de uma CTE e depois juntar esse resumo com outra tabela, como colocar o nome certo em cima de uma conta que já está pronta.
Para que serve
Serve para trocar um número de id por um nome fácil de ler num resultado que já foi resumido: o total de cada departamento, com o nome dele.
Como você vai usar
Crie a CTE com GROUP BY: WITH folha AS (SELECT depto_id, SUM(salario) AS total FROM funcionarios GROUP BY depto_id). Depois faça JOIN dela com departamentos pelo id.
O que vai aparecer
2 colunas (nome e total) e 4 linhas, da maior folha de pagamento (TI, 34700) para a menor.
Exemplo
WITH t AS (SELECT depto_id, COUNT(*) AS n FROM funcionarios GROUP BY depto_id) SELECT d.nome, t.n FROM departamentos d JOIN t ON t.depto_id = d.idLição 4: Subconsulta correlacionada
Uma subconsulta correlacionada usa colunas da consulta de fora e roda uma vez por linha.
O que é
Subconsulta correlacionada é uma perguntinha que olha para a linha de fora. Ela roda uma vez para cada linha, como o professor perguntar a cada aluno: "e a média da SUA turma?".
Para que serve
Serve para comparar cada linha com o seu próprio grupo: quem ganha mais que a média do próprio departamento, e não da empresa toda.
Como você vai usar
Dê um apelido à tabela de fora (f) e use esse apelido lá dentro da subconsulta: (SELECT AVG(salario) FROM funcionarios WHERE depto_id = f.depto_id).
O que vai aparecer
2 colunas (nome e salario) e 4 linhas: Carlos, Marina, Renata e Fernanda, cada um acima da média do seu departamento.
Exemplo
SELECT nome FROM funcionarios f WHERE salario > (SELECT AVG(salario) FROM funcionarios WHERE depto_id = f.depto_id)Lição 5: EXISTS
EXISTS é verdadeiro quando a subconsulta devolve alguma linha. Muito eficiente para "tem algum...?"
O que é
EXISTS quer dizer "existe". Ele dá resposta "sim" quando a subconsulta encontra pelo menos uma linha, e "não" quando não encontra nada.
Para que serve
Serve para perguntar "tem algum...?" de um jeito rápido: este produto tem pelo menos uma venda?
Como você vai usar
Escreva WHERE EXISTS (SELECT 1 FROM vendas v WHERE v.produto_id = p.id). A subconsulta liga as duas tabelas pela coluna em comum, e o 1 só quer dizer "tanto faz o que mostrar, só me diga se achou".
O que vai aparecer
1 coluna (nome) e 9 linhas: os produtos que têm pelo menos uma venda.
Exemplo
SELECT nome, categoria FROM produtos p WHERE EXISTS (SELECT 1 FROM vendas v WHERE v.produto_id = p.id AND v.qtd >= 20)Projeto: clientes acima da média
A loja quer olhar os clientes que mais compram.
Passo 1: crie uma CTE (WITH) chamada ativos, com o total de pedidos de cada cliente, sem contar os cancelados. Colunas: cliente_id e total.
Passo 2: usando ativos, mostre só os clientes cujo total é maior que a média dos totais da CTE. Colunas, nesta ordem:
nome: o nome do clientetotal: o total delependente:simse ele ainda tem algum pedido pendente, senãonao(useEXISTSdentro de umCASE)
Ordene do maior total para o menor.
O que este projeto usa: CTE com WITH, CTE com valor de referência, CTE + JOIN, Subconsulta correlacionada, EXISTS.
No CodeUp esse projeto é feito no próprio app, com dicas e correção automática, e não tira vidas de ninguém.
Pratique de verdade. Ler é só o começo: no CodeUp você escreve o código, recebe a correção na hora e ganha pontos.