Curso
Relatórios em SQL
4 h
39.7K
Neste tutorial, o foco é no SQL ANSI (American National Standards Institute), que funciona em qualquer banco de dados como Oracle, MySQL, Microsoft SQL Server, etc. Vamos começar com uma introdução ao SQL (Structured Query Language) e por que um cientista de dados precisa dele.
SQL e ciência de dados
Nesta parte do tutorial, você verá por que um cientista de dados deve aprender SQL. Veja como o SQL pode impulsionar sua carreira como cientista de dados:
- SQL se tornou requisito em grande parte das vagas de ciência de dados, como: analista de dados, desenvolvedor de BI (Business Intelligence), programador e desenvolvedor de banco de dados. Com SQL, você se comunica com o banco de dados e trabalha diretamente com seus dados.
- Se você já usou ferramentas como Tableau ou outro software de relatórios e visualização, deve ter visto como conectar seu projeto a um banco de dados e, em poucos cliques, arrastar e soltar gráficos no relatório apenas escolhendo os campos — o software faz o resto. Por trás dessa interface gráfica, há SQL rodando e interagindo com o banco. Ao aprender SQL, você pode interagir diretamente com o banco de dados.
- SQL pode ser usado com várias linguagens de programação, como PHP e Java. Você pode criar suas próprias visualizações integrando SQL ao aplicativo ou buscar dados no banco e convertê-los para XML e JSON para usar em web services ou APIs.
- Os bancos de dados evoluíram ao longo dos anos. Com Big Data em evidência e dados no nosso dia a dia, bancos NoSQL também ganharam força. Aprender SQL ajuda a construir uma base sólida em bancos de dados, entender quando usar bancos estruturados e quando usar NoSQL, além de valorizar as diferenças entre eles.
A seguir, apresento uma rápida introdução e termos usados em SQL e bancos de dados que vão ajudar você a programar mais rápido. Se você já sabe o que é um banco relacional e o que é SQL, pode pular direto para o código.
Introdução ao SQL e a bancos de dados
Um banco de dados é uma coleção organizada de informações. Para gerenciá-los, usamos Sistemas de Gerenciamento de Banco de Dados (SGBD ou DBMS). Um DBMS armazena, recupera e altera dados nos bancos mediante solicitação.
Ao longo do tempo, surgiram diferentes tipos de bancos: hierárquicos, em rede, relacionais e, hoje, bancos NoSQL. Um banco de dados relacional é uma coleção de relações ou tabelas bidimensionais.
Veja algumas terminologias usadas em SGBDR:
| Termo | Descrição |
|---|---|
| Tabela | A tabela é a estrutura básica de armazenamento de um SGBDR. Ela guarda todos os dados necessários sobre algo do mundo real. Exemplo: Employees (funcionários). |
| Linha ou tupla | Representa todos os dados necessários de um funcionário específico. Cada linha pode ser identificada por uma chave primária, que não permite duplicidade. |
| Coluna ou atributo | Geralmente se refere a uma característica de uma entidade. |
| Primary Key | Um campo que identifica exclusivamente uma linha. |
| Foreign Key | Uma chave estrangeira é uma coluna que identifica como as tabelas se relacionam. Ela referencia uma chave primária em outra tabela. |
Você pode relacionar várias tabelas usando chaves primárias e estrangeiras. Cada linha em um banco relacional é identificada de forma única por uma Primary Key (PK). Você pode referenciar outra tabela usando uma Foreign Key (FK). Por exemplo:
Bancos de dados relacionais são acessados usando SQL (Structured Query Language). Todo banco suporta SQL padrão ANSI e, além disso, pode ter sua própria sintaxe para facilitar algumas operações. Neste tutorial, você vai aprender SQL ANSI para poder trabalhar em qualquer banco. Podemos dividir o SQL ANSI em cinco seções. Vou listá-las, mas aqui vamos focar em duas que são mais relevantes: Data Retrieval e DML:
- Data Retrieval:
- Select.
- Data Manipulation Language (DML):
- Insert, Update, Delete, Merge.
- Data Definition Language (DDL):
- Create, Alter, Drop, Rename, Truncate.
- Data Control Language (DCL):
- Grant, Revoke.
- Transaction Control:
- Commit, Rollback, Savepoint.
Existem diferentes fornecedores de SGBDR. Os mais comuns e utilizados são:
- Oracle (Oracle Corporation)
- Microsoft SQL Server (Microsoft)
- MySQL (Oracle Corporation)
- PostgreSQL (PostgreSQL Global Development Group)
- SQLite (Desenvolvido por D. Richard Hipp)
Onde você executa consultas SQL? Pode ser em:
- Um software de relatórios que lê do banco e exibe os dados (por exemplo: Tableau, Microsoft BI)
- Uma GUI de administração do banco (por exemplo: TOAD, SQL Developer para Oracle, phpMyAdmin para MySQL)
- Uma interface direta via console para o banco (por exemplo: SQL*Plus para Oracle)
Se você tiver credenciais para acessar um banco, pode visualizar os objetos usando consultas ou uma GUI, conforme o fornecedor do banco.
É isso! Vamos aprender SQL na prática…
Tipos de dados em SQL
Cada coluna em um banco tem um nome, um tipo de dado e, às vezes, um tamanho associado. É função do desenvolvedor de banco desenhar o modelo e decidir quais tipos usar conforme os requisitos e o volume de dados.
Como cientista de dados, é importante conhecer os tipos de dados para usar bem as funções do banco e escrever consultas com precisão. Há um tipo de dado para cada tipo de coluna no banco: nome de pessoa, textos, números, imagens armazenadas e assim por diante.
A seguir, você verá os tipos básicos em Oracle Server, SQL Server e MySQL:
Para se aprofundar:
SQL e geração de relatórios
Para todo o SQL usado neste tutorial, considere o seguinte esquema de banco de exemplo:
Considere um banco com duas tabelas: emp, que guarda dados de funcionários, e dept, que guarda os registros dos departamentos.
A tabela emp tem número do funcionário (empno), nome (ename), salário (sal), comissão (comm), cargo (job), id do gerente (mgr), data de contratação (hiredate) e número do departamento (deptno). Como o gerente também é um funcionário e tem um número de funcionário, mgr corresponde a um dos empno cujo job é "MANAGER".
A tabela dept tem número do departamento (deptno), nome do departamento (dname) e localização (loc).
Observe que bancos diferentes têm formatos de data diferentes. Aqui, DD-MON-YY é o padrão do Oracle. Em Microsoft SQL Server e MySQL, o padrão é YYYY-MM-DD.
As suas tabelas podem ser diferentes, então ajuste somente os nomes de tabelas e atributos conforme o seu cenário. Neste tutorial, você apenas vai ler dados, sem escrever, atualizar ou criar novas tabelas/objetos. Ou seja, sem risco de perda ou alteração de dados!
Informação: Schema é o conjunto de objetos (tabelas, funções, procedures, views) que pertencem a um usuário.
No esquema acima, a tabela emp tem seis atributos para a entidade Employee. A tabela dept tem três atributos para a entidade Department.
Recuperando dados com SELECT
Uma instrução SELECT recupera informações do banco. Com SELECT, você pode:
1. Projeção: escolher as colunas da tabela que quer retornar. Você pode selecionar poucas ou muitas colunas, conforme precisar.
2. Seleção: escolher as linhas da tabela que quer retornar.
Você pode aplicar critérios para restringir as linhas exibidas.
3. Junção: reunir dados armazenados em tabelas diferentes criando um vínculo entre elas.
O SELECT básico permite especificar quais colunas você quer e de qual tabela. A cláusula SELECT define colunas e expressões; a cláusula FROM define de qual tabela buscar os dados.
Exemplo: Quais são os nomes de todos os funcionários e seus cargos: SELECT ename, job FROM employee; A consulta acima retornará algo como:
| ename | job |
|---|---|
| A | Salesman |
| B | Manager |
| C | Manager |
Para selecionar todos os atributos e todas as linhas da tabela, use o operador *:
SELECT *
FROM employee;
The output will be:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
Algumas dicas para escrever SQL:
- SQL não é sensível a maiúsculas/minúsculas
- Escreva cláusulas em linhas separadas para melhorar a legibilidade
- Você pode escrever uma instrução SQL em uma ou mais linhas
Você também pode criar expressões usando os operadores +, -, /, * sobre dados numéricos e de data para gerar os resultados desejados. Por exemplo, calcular 20% do salário de todos os funcionários:
SELECT ename, sal*(20/100)
FROM employee;
Output:
ENAME SAL*(20/100)
---------- ------------
SMITH 160
ALLEN 320
WARD 250
JONES 595
BLAKE 570
Informação:
- Parênteses ajudam a deixar a expressão mais clara
- Divisão e multiplicação têm precedência sobre soma e subtração
- Se houver mesma precedência, a avaliação ocorre da esquerda para a direita
Valores NULL são tratados de forma particular nos bancos. NULL indica valor desconhecido. Qualquer operação com NULL resulta em NULL. Bancos diferentes têm funções diferentes para tratar NULL. Há uma função comum em MySQL, Microsoft SQL Server e Oracle para tratar NULLs:
SELECT ename, sal+COALESCE(comm,0)
FROM employee;
Output:
ENAME SAL+COALESCE(COMM,0)
---------- --------------------
SMITH 800
ALLEN 1900
WARD 1750
JONES 2975
BLAKE 2850
Apelidos de coluna e concatenação
Nos exemplos acima, o nome da coluna segue o campo do banco ou a expressão selecionada. Em relatórios, às vezes você quer renomear os cabeçalhos. Isso pode ser feito com aliases.
SELECT ename AS "Emp Name", sal*(20/100) as "20% of Salary"
FROM employee;
Output:
Emp Name 20% of Salary
---------- ------------
SMITH 160
ALLEN 320
WARD 250
JONES 595
BLAKE 570
Quando o alias tiver espaços, use aspas duplas. Caso contrário, não é necessário. Em alguns casos, você pode omitir o AS.
Você pode formatar a saída por concatenação. Dá para inserir seus próprios textos usando a função CONCAT ou operadores como || ou +, dependendo do banco:
- Oracle suporta CONCAT() e ||, mas CONCAT() aceita apenas dois argumentos (use CONCAT aninhado).
- MySQL usa CONCAT()
- Microsoft SQL Server usa o operador "+" e CONCAT().
Oracle: SELECT '20% of salary of '||ename||' is '||sal*(20/100) as "20% of salary" FROM employee;
SELECT CONCAT(CONCAT('20% of salary of',ename),CONCAT(' is ',sal(20/100))) as "20% of salary" FROM employee; MySQL e Microsoft SQL Server: SELECT CONCAT('20% of salary of ',ename,' is ',sal(20/100)) as "20% of salary" FROM employee; Todos terão a mesma saída:
20% of salary
-----------------------------------------------------------------------
20% of salary of SMITH is 160
20% of salary of ALLEN is 320
20% of salary of WARD is 250
20% of salary of JONES is 595
20% of salary of BLAKE is 570
Eliminando linhas repetidas com DISTINCT
SELECT deptno
FROM employee;
Above query will result in:
DEPTNO
----------
20
30
30
20
30
Aqui, os deptno 20 e 30 se repetem. Você pode eliminar repetições usando DISTINCT na cláusula SELECT.
SELECT distinct ename, deptno, job
FROM employee;
DEPTNO
----------
30
20
Restringindo e ordenando dados
A cláusula WHERE filtra dados com base em uma condição.
Encontre todos os funcionários cujo job é CLERK:
SELECT ename, job
FROM employee
WHERE job='CLERK';
ENAME JOB
---------- ---------
SMITH CLERK
Você pode filtrar resultados com diferentes condições. Para isso, usamos operadores, símbolos condicionais e palavras-chave específicas:
O uso de =, <>, !=, >=, <=, >, < é direto, como no exemplo acima que usa =.
Sintaxe de AND e OR: SELECT column1, column2,.. FROM table_name WHERE condition1 AND condition2 AND condition 3...;
SELECT column1, column2,.. FROM table_name WHERE condition1 OR condition2 OR condition 3...; Encontre nomes de funcionários cujo job é MANAGER e que pertencem ao departamento 30:
SELECT ename
FROM employee
WHERE job='MANAGER' AND deptno=30;
ENAME
----------
BLAKE
SELECT ename
FROM employee
WHERE job='MANAGER' OR deptno=30;
ENAME
----------
ALLEN
WARD
JONES
BLAKE
Sintaxe de NOT: SELECT column1, column2, ... FROM table_name WHERE NOT condition; Encontre todos os funcionários cujo job não é SALESMAN:
SELECT ename, job
from employee
WHERE NOT job='SALESMAN';
ENAME JOB
---------- ---------
SMITH CLERK
JONES MANAGER
BLAKE MANAGER
Você pode criar condições complexas usando AND, OR e NOT. A precedência é:
- NOT
- AND
- OR
Encontre todos os funcionários cujo job não é CLERK e que pertencem ao departamento 20:
SELECT ename, job
from employee
WHERE NOT job='SALESMAN' AND sal>800;
ENAME JOB
---------- ---------
JONES MANAGER
BLAKE MANAGER
Aqui, NOT é avaliado primeiro; depois, AND.
Outros operadores úteis:
BETWEEN...AND:
SELECT *
FROM employee
WHERE sal BETWEEN 1000 AND 2000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
LIKE:
LIKE usa dois curingas: porcentagem % e sublinhado _ para representar a quantidade de caracteres no padrão.
- % significa zero, um ou vários caracteres
- %M%: corresponde a qualquer string com M em qualquer posição
- M%: corresponde a valores que começam com M
- %M: corresponde a valores que terminam com M
- M%A: começa com M e termina com A
Os padrões diferenciam maiúsculas de minúsculas.
- _ especifica o número de caracteres desconhecidos antes ou depois do caractere conhecido. Cada sublinhado representa um caractere.
- _r%: corresponde a valores com r na segunda posição.
Obtenha nomes de todos os funcionários cujos nomes começam com "B":
SELECT *
FROM employee
WHERE ename LIKE 'B%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
Obtenha nomes de todos os funcionários cujos nomes começam com "A" e possuem "E" em qualquer posição após "A":
SELECT *
FROM employee
WHERE ename LIKE 'A%E%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
IN(value1, value2, value3, ...):
A função IN() aceita um ou mais valores e permite comparar uma coluna com os valores entre parênteses na cláusula WHERE:
SELECT ename, job, hiredate
FROM employee
WHERE job IN ('CLERK','SALESMAN');
ENAME JOB HIREDATE
---------- --------- ---------
SMITH CLERK 17-DEC-80
ALLEN SALESMAN 20-FEB-81
WARD SALESMAN 22-FEB-81
Você também pode usar um SELECT dentro do IN() que retorne valores. Exemplo:
SELECT ename, job, hiredate
FROM employee
WHERE deptno IN (select deptno FROM department WHERE loc='CHICAGO');
ENAME JOB HIREDATE
---------- --------- ---------
ALLEN SALESMAN 20-FEB-81
WARD SALESMAN 22-FEB-81
BLAKE MANAGER 01-MAY-81
O SELECT dentro do IN() também é chamado de subquery. Falaremos mais sobre subconsultas adiante!
IS NULL:
IS NULL verifica valores NULL em um atributo. Por exemplo, encontre todos os funcionários que não têm comissão:
SELECT ename, job, sal
FROM employee
WHERE comm IS NULL;
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
JONES MANAGER 2975
BLAKE MANAGER 2850
Se quiser os nomes de quem recebeu comissão, use IS NOT NULL:
SELECT ename, job, sal, comm
FROM employee
WHERE comm IS NOT NULL;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
ALLEN SALESMAN 1600 300
WARD SALESMAN 1250 500
Para filtrar usando colunas de data, utilize o formato padrão do banco. Se precisar de outro formato, use funções de data (veremos mais adiante). Encontre todos os funcionários contratados após 21 de fevereiro de 1981:
(Aqui, usamos o formato padrão do Oracle)
SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81';
ENAME JOB SAL COMM
---------- --------- ---------- ----------
WARD SALESMAN 1250 500
JONES MANAGER 2975
BLAKE MANAGER 2850
Expressões condicionais mais complexas podem ser criadas combinando os operadores acima, mas é preciso atenção à precedência — a ordem em que os operadores são avaliados. Veja as regras por banco:
- Microsoft Transact-SQL operator precedence
- Oracle 10g condition precedence
- Oracle MySQL 9 operator precedence
- PostgreSQL operator Precedence
- SQLite operator Precedence
Duas funções úteis: ANY() e ALL() podem ser usadas em condições. Exemplo:
SELECT ename, job, sal, comm FROM employee WHERE deptno=ANY(SELECT deptno from dept WHERE loc='NEW YORK');
SELECT ename, job, sal, comm FROM employee WHERE deptno=ALL(SELECT deptno from dept WHERE dname='SALES');
Note o uso de subconsultas nos exemplos acima. Você aprenderá mais sobre elas a seguir.
Ordenando resultados com ORDER BY
Você pode ordenar resultados em ordem ascendente (ASC) ou descendente (DESC) por qualquer atributo ou múltiplos atributos. Também é possível ordenar por aliases definidos no SELECT:
SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY sal desc;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
WARD SALESMAN 1250 500
Observação: a ordem padrão é ascendente (ASC) e não precisa ser especificada.
SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY sal;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
WARD SALESMAN 1250 500
BLAKE MANAGER 2850
JONES MANAGER 2975
Você pode informar várias colunas no ORDER BY, que serão avaliadas na ordem informada. Exemplo: ordenar funcionários primeiro por deptno (asc) e depois por nome (desc):
SELECT ename, job, sal, comm, deptno
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY deptno ASC, ename DESC;
ENAME JOB SAL COMM DEPTNO
---------- --------- ---------- ---------- ----------
JONES MANAGER 2975 20
WARD SALESMAN 1250 500 30
BLAKE MANAGER 2850 30
Usando funções de linha única para personalizar a saída
Há inúmeras funções em SGBDs para tarefas comuns como obter tamanho de string, juntar strings, formatar, executar operações matemáticas e mais. Em SQL, temos dois tipos de funções de linha:
- Funções de linha única (single-row)
- Funções de múltiplas linhas (multi-row)
Funções de linha única:
São aplicadas a cada linha e retornam um resultado por linha. CONCAT() é uma função de manipulação de caracteres de linha única. Elas podem ser usadas em SELECT, WHERE e ORDER BY. Em geral, existem funções de linha única para:
- Manipulação de caracteres
- Data e hora
- Números
- Conversão
Bancos diferentes têm nomes diferentes para funções semelhantes. Aqui, destaco funções úteis comuns entre Microsoft SQL Server, Oracle e MySQL. Algumas já apareceram acima. No fim da seção, há links com a lista completa de funções para você praticar.
Vamos por categoria:
Funções de manipulação de caracteres
LOWER(): converte string para minúsculas.
SELECT lower(ename) as ename
FROM employee;
ENAME
----------
smith
allen
ward
jones
blake
UPPER(): converte string para maiúsculas.
SELECT upper(ename) as ename
FROM employee;
ENAME
----------
SMITH
ALLEN
WARD
JONES
BLAKE
SUBSTR()[Oracle, MySQL]: retorna a substring especificada SUBSTR(string, posição_inicial, tamanho)
SUBSTRING()[SQL Server]: retorna a substring especificada SUBSTRING(string, posição_inicial, tamanho).
SELECT SUBSTR(ename,2,3) as substr_ename FROM employee;
No SQL Server, troque o nome da função para SUBSTRING.
SUBSTR_ENAME ------------ MIT LLE ARD ONE LAK
LENGTH()[Oracle, MySQL]: retorna o tamanho da string entre parênteses
LEN()[SQL Server]: retorna o tamanho da string entre parênteses
SELECT LENGTH(ename) as len_ename FROM employee;
No SQL Server, troque para LEN.
LEN_ENAME
----------
5
5
4
5
5
Funções como preenchimento (padding) à esquerda/direita e replace variam de sintaxe entre bancos. Listas de funções de caracteres e strings dos três fornecedores:
Funções numéricas
| Nome | Função |
|---|---|
| ROUND(m,n): | Arredonda o valor m para n casas decimais. |
| ABS(m,n): | Retorna o valor absoluto de um número. |
| FLOOR(n): | Retorna o maior inteiro menor ou igual a n. |
| MOD(m,n): | Retorna o resto da divisão de m por n (no SQL Server, use o operador %: 35 % 6) |
Oracle:
SELECT ROUND(45.926,2),MOD(11,5),FLOOR(34.4),ABS(-24) from dual;
OUTPUT:
ROUND(45.926,2) MOD(11,5) FLOOR(34.4) ABS(-24)
--------------- ---------- ----------- ----------
45.93 1 34 24
MySQL:
SELECT ROUND(45.926,2),MOD(11,5),FLOOR(34.4),ABS(-24);
SQL Server:
SELECT ROUND(45.926,2) as round,11%5 as mod, FLOOR(34.4) as floor,ABS(-24) as abs;
Listas de funções numéricas:
Funções de conversão
Funções de conversão mudam um tipo de dado para outro. Elas variam entre servidores. Veja um exemplo em Oracle usando to_char para personalizar a saída. O formato padrão de data no Oracle é DD-MON-YY:
SELECT ename, to_char(hiredate,'DD, MONTH YYYY') as Hiredate, to_char(hiredate,'DY') as Day
from employee;
ENAME Hiredate DAY
---------- -------------------------------------------- ------------
SMITH 17,DECEMBER 1980 WED
ALLEN 20,FEBRUARY 1981 FRI
WARD 22,FEBRUARY 1981 SUN
JONES 02,APRIL 1981 THU
BLAKE 01,MAY 1981 FRI
Há muitas funções de conversão em cada servidor. Para explorar mais:
- Microsoft SQL server conversion functions
- Oracle server conversion functions
- MySQL conversion functions
Funções de data e hora
Funções de data e hora permitem manipular datas, como somar dias, calcular meses entre duas datas e mais. Elas são muito úteis para relatórios. Funções de data variam entre bancos; a mesma funcionalidade pode ter nomes diferentes. Veja alguns exemplos e, depois, links com as funções dos bancos mais usados: Oracle, SQL Server e MySQL. Teste no seu servidor!
- Oracle Date and Time functions
- MySQL Date and Time functions
- Microsoft SQL Server Date and Time functions
Agrupando resultados em SQL
Funções de grupo (multi-row) são aplicadas a grupos e retornam um resultado por grupo. Você vai usá-las para descobrir, por exemplo, vendas totais por trimestre, preço médio de um produto em um período, maior investimento recebido no mês, e assim por diante.
Gerando dados agregados com funções de grupo
A cláusula GROUP BY agrupa resultados e é usada com funções como COUNT, MAX, MIN, AVG e SUM. Sintaxe:
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
Pontos importantes sobre GROUP BY:
- Aliases não podem ser usados no GROUP BY
- WHERE sempre vem antes do GROUP BY
- HAVING vem após o GROUP BY e aplica condições sobre funções de grupo
- ORDER BY sempre vem por último
COUNT conta o número de linhas conforme a condição (ou sem condição). COUNT ignora valores nulos.
Conte todos os funcionários cujo nome começa com "A":
SELECT count(ename)
FROM employee
WHERE ename LIKE 'A%';
Output:
COUNT(ENAME)
------------
1
Encontre o total, a média, o mínimo e o máximo de salários na tabela employee:
SELECT sum(sal) as "TOTAL SAL", avg(sal) as "AVG SAL", min(sal) as "MIN SAL", max(sal) as "MAX SAL"
FROM employee;
TOTAL SAL AVG SAL MIN SAL MAX SAL
---------- ---------- ---------- ----------
9475 1895 800 2975
Você pode agrupar o resultado por departamento:
SELECT sum(sal) as "TOTAL SAL", avg(sal) as "AVG SAL", min(sal) as "MIN SAL", max(sal) as "MAX SAL", deptno
FROM employee
GROUP BY deptno;
TOTAL SAL AVG SAL MIN SAL MAX SAL DEPTNO
---------- ---------- ---------- ---------- ----------
5700 1900 1250 2850 30
3775 1887.5 800 2975 20
Você também pode calcular variância e desvio padrão usando:
| Para Oracle e MySQL | MS SQL Server |
|---|---|
| SELECT STDDEV(column_name) FROM table_name; | SELECT STDEV(column_name) FROM table_name; |
| SELECT VARIANCE(column_name) FROM table_name; | SELECT VAR(column_name) FROM table_name; |
Você pode agrupar por mais de uma coluna.
Some os salários por cargo dentro de cada departamento:
SELECT sum(sal) as "TOTAL SAL", deptno, job
FROM employee
GROUP BY deptno, job;
TOTAL SAL DEPTNO JOB
---------- ---------- ---------
800 20 CLERK
2850 30 SALESMAN
2975 20 MANAGER
2850 30 MANAGER
Você pode restringir os resultados agregados com HAVING. Ele é usado apenas para condições sobre grupos.
Observação: não use WHERE para aplicar condições sobre funções de grupo.
SELECT sum(sal) as "TOTAL SAL", deptno, job
FROM employee
GROUP BY deptno, job
HAVING sum(sal)>1000
ORDER BY sum(sal);
TOTAL SAL DEPTNO JOB
---------- ---------- ---------
2850 30 SALESMAN
2850 30 MANAGER
2975 20 MANAGER
Exibindo dados de múltiplas tabelas
Nesta seção, abordaremos os seguintes joins:
- Produto cartesiano/Cross join
- Inner join/EquiJoin
- Natural join
- Outer joins (Left, Right, Full)
- Self join
Muitas vezes, você precisa de dados de várias tabelas. Veja o exemplo:
Para gerar um relatório como esse, é preciso relacionar employee e department e buscar dados de ambas. Para isso, usamos joins no SQL.
Produto cartesiano/Cross join:
O produto cartesiano é formado quando cada tupla de uma relação R combina com cada tupla de uma relação S.
O produto cartesiano (CROSS JOIN) multiplica duas tabelas para formar uma relação com todos os pares possíveis de tuplas. Se R tem I tuplas e M atributos e S tem J tuplas e N atributos, o resultado terá I×J tuplas e M+N atributos. Você pode formar um produto cartesiano usando CROSS JOIN conforme o padrão SQL.
SELECT empno, ename, dname
FROM employee, department;
OR
SELECT empno, ename, dname
FROM employee CROSS JOIN department;
EMPNO ENAME DNAME
---------- ---------- --------------
7369 SMITH ACCOUNTING
7499 ALLEN ACCOUNTING
7521 WARD ACCOUNTING
7566 JONES ACCOUNTING
7698 BLAKE ACCOUNTING
7369 SMITH RESEARCH
7499 ALLEN RESEARCH
7521 WARD RESEARCH
7566 JONES RESEARCH
7698 BLAKE RESEARCH
7369 SMITH SALES
EMPNO ENAME DNAME
---------- ---------- --------------
7499 ALLEN SALES
7521 WARD SALES
7566 JONES SALES
7698 BLAKE SALES
15 linhas selecionadas.
Como employee tem 5 tuplas e department tem 3, o produto cartesiano tem 5×3=15 linhas. Um produto cartesiano é gerado quando:
- Não se usa condição de junção
- A condição de junção é inválida ou mal formada
Quando precisamos de dados de duas ou mais tabelas, criamos a condição de junção usando atributos em comum — normalmente chave primária e chave estrangeira.
Produtos cartesianos são usados para simular grandes volumes de dados em testes.
Inner join/EquiJoin:
Inner join (ou equijoin) usa o relacionamento entre chaves primárias e estrangeiras para unir duas ou mais tabelas:
SELECT ename, dname
FROM employee e,department d
WHERE e.deptno=d.deptno;
OR
SELECT ename, dname
FROM employee e
JOIN department d
ON e.deptno=d.deptno;
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
BLAKE SALES
No exemplo, deptno é chave primária em department e chave estrangeira em employee. É possível adicionar mais condições lógicas como vimos acima.
Pontos para lembrar sobre JOINS:
- Se a mesma coluna existir em mais de uma tabela, prefixe o nome da coluna com o nome da tabela. Isso também melhora a clareza.
- Para juntar n tabelas, é necessário no mínimo n-1 condições de junção. Ex.: para juntar quatro tabelas, no mínimo três joins.
Aliases de tabela: aliases (como e e d em FROM employee e, department d) ajudam o banco a identificar de qual tabela é a coluna, especialmente quando há colunas iguais em múltiplas tabelas, evitando erro de coluna ambígua — e ainda poupam digitação.
Também é possível criar nonequi-joins, que unem tabelas com base em condições diferentes de igualdade. Considere a tabela salgrade com a faixa salarial por grade:
Para descobrir a grade de cada funcionário com base no salário (grades em salgrade e salários em employee):
SELECT e.ename, e.sal, s.grade
FROM employee e, salgrade s
WHERE e.sal between s.losal AND s.hisal;
ENAME SAL GRADE
---------- ---------- ----------
JONES 2975 4
BLAKE 2850 4
ALLEN 1600 3
WARD 1250 2
SMITH 800 1
Exemplo: você precisa dos nomes dos funcionários, seus salários, a grade e o nome do departamento. Como o salário está em employee, a grade em salgrade e o nome do departamento em department, você fará um join entre três tabelas:
SELECT e.ename, e.sal, d.dname, s.grade FROM employee e, department d, salgrade s WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal; Output: ENAME SAL DNAME GRADE ---------- ---------- -------------- ---------- JONES 2975 RESEARCH 4 BLAKE 2850 SALES 4 ALLEN 1600 SALES 3 WARD 1250 SALES 2 SMITH 800 RESEARCH 1
No exemplo acima, três tabelas e duas condições de junção — uma delas é nonequi-join.
Natural join:
Natural joins permitem que o banco una tabelas automaticamente, casando colunas com o mesmo nome. Se nomes coincidirem, mas os tipos forem diferentes, ocorre erro.
Sintaxe: SELECT FROM table1 NATURAL JOIN table2; SELECT FROM employee NATURAL JOIN department; Como o natural join encontra colunas automaticamente, ele pode achar mais de uma coluna com o mesmo nome e tipos diferentes, causando erro. Nesse caso, use USING para especificar as colunas da junção de igualdade.
Observação: NATURAL JOIN e USING são cláusulas diferentes e usadas separadamente. Não podem ser usadas simultaneamente.
SELECT e.ename, d.dname, e.sal FROM employee e JOIN department d USING (deptno) WHERE deptno=20; output: ENAME DNAME SAL ---------- -------------- ---------- SMITH RESEARCH 800 JONES RESEARCH 2975
Colunas no USING não podem usar prefixos de tabela em nenhuma parte do SQL. Por exemplo, isto está incorreto:
SELECT e.ename, d.dname, e.sal FROM employee e JOIN department d USING (d.deptno) WHERE d.deptno=20;
d.deptno está incorreto. Use apenas deptno. Outer joins:
Há três outer joins:
- Left outer join: retorna o resultado do inner join e as linhas não correspondentes da tabela da esquerda
- Right outer join: retorna o resultado do inner join e as linhas não correspondentes da tabela da direita
- Full outer join: retorna o resultado do inner join e as linhas não correspondentes de ambos os lados
Vamos ver cada um:
SELECT e.ename, s.grade FROM salgrade s LEFT OUTER JOIN employee e ON e.sal BETWEEN s.losal AND s.hisal; Output: ENAME DEPTNO DNAME ---------- ---------- -------------- SMITH 20 RESEARCH JONES 20 RESEARCH ALLEN 30 SALES WARD 30 SALES BLAKE 30 SALES.sal BETWEEN s.losal AND s.hisal; Output: ENAME DEPTNO DNAME ---------- ---------- -------------- SMITH 20 RESEARCH JONES 20 RESEARCH ALLEN 30 SALES WARD 30 SALES BLAKE 30 SALESSELECT e.ename, d.deptno, d.dname
FROM employee e RIGHT OUTER JOIN department d
ON e.deptno=d.deptno;
output:
ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
JONES 20 RESEARCH
BLAKE 30 SALES
10 ACCOUNTINGSELECT e.ename, d.deptno, d.dname
FROM employee e FULL OUTER JOIN department d
ON e.deptno=d.deptno;
Output:
ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
JONES 20 RESEARCH
BLAKE 30 SALES
10 ACCOUNTING
A tabela department tinha uma linha sem correspondência para deptno 10, e não havia linhas sem correspondência em employee. Da mesma forma, salgrade tem uma linha sem correspondência para a grade 5.
Self-join:
Há situações em que você precisa juntar uma tabela com ela mesma. Por exemplo, employee contém informações de todos os funcionários, inclusive gerentes (veja a employee acima). Se você precisar listar funcionários e seus gerentes, fará um self-join assim:
SELECT e.ename as Employee, m.ename as Manager
FROM employee e, employee m
WHERE e.mgr=m.empno;
EMPLOYEE MANAGER
---------- ----------
ALLEN BLAKE
WARD BLAKE
Usando subconsultas para resolver problemas
Uma subconsulta é uma consulta dentro de outra. Ela ajuda a quebrar consultas grandes em partes. Subconsultas podem ser aninhadas e usadas em:
- WHERE
- FROM
- HAVING
Exemplo: encontre funcionários cujo salário é maior que o de outro funcionário, neste caso, James. Vamos decompor:
- Encontre o salário de James
- Compare o salário de James com o dos demais
Isso pode ser resolvido com uma subconsulta. A subconsulta (interna) executa uma vez antes da consulta principal (externa).
SELECT empno, ename FROM employee WHERE sal>(SELECT sal from employee where ename='JAMES');
Tipos de subconsultas:
-
Single-row subquery: retorna apenas uma linha do SELECT interno.
-
Multiple-row subquery: retorna mais de uma linha do SELECT interno.
Pontos para lembrar:
- Coloque subconsultas entre parênteses.
- Posicione subconsultas à direita do operador de comparação.
- Use operadores de linha única (>, <, >=, <=, <>) com subconsultas de linha única.
- Use operadores de múltiplas linhas (IN, ANY, ALL) com subconsultas de múltiplas linhas.
Single-row subquery:
Encontre nomes de funcionários cujo cargo é o mesmo do empno 7521
SELECT ename, job
FROM employee
WHERE job=(SELECT job FROM employee WHERE empno=7521);
ENAME JOB
---------- ---------
ALLEN SALESMAN
WARD SALESMAN
Selecione o salário máximo dos departamentos cujo máximo seja maior ou igual ao máximo do departamento 20
SELECT deptno, max(sal)
FROM employee
GROUP BY deptno
HAVING max(sal)>=(SELECT max(sal) FROM employee WHERE deptno=20);
DEPTNO MAX(SAL)
---------- ----------
20 2975
Multiple-row subquery:
Encontre funcionários cujo salário é igual a qualquer um dos salários de quem tem cargo de Manager. A subconsulta pode retornar várias linhas:
SELECT ename, job, sal
FROM employee
WHERE sal IN (SELECT sal FROM employee WHERE job='MANAGER');
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
Quando subconsultas são usadas no FROM, elas se comportam como uma tabela temporária lógica (não é criada fisicamente). Exemplo:
SELECT e.ename, e.job, e.sal
FROM employee e, (SELECT deptno FROM department WHERE loc='DALLAS') d
WHERE e.deptno=d.deptno;
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
JONES MANAGER 2975
Usando operadores de conjunto
Em SQL, operadores de conjunto combinam resultados de múltiplas consultas em um único resultado. Baseiam-se na teoria de conjuntos e operações como UNION, MINUS e INTERSECT. Aqui você verá como usá-los para otimizar suas consultas. Vamos abordar:
Informação: Consultas que usam operadores de conjunto são chamadas de instruções compostas (compound statements).
- UNION e UNION ALL
- INTERSECT
- EXCEPT (padrão SQL) e MINUS (específico do Oracle)
UNION
Considere duas relações R e S; UNION seleciona todas as linhas de R e todas de S, eliminando duplicatas. O máximo de linhas retornadas é r+s (r linhas em R e s em S).
Selecione todos os nomes de departamentos, incluindo os nomes dos departamentos de funcionários contratados antes de 23-OCT-1999
SELECT dname
FROM department
UNION
SELECT dname
FROM department, employee
WHERE department.deptno=employee.deptno AND employee.hiredate<to_date('23-OCT-1999');
DNAME
--------------
ACCOUNTING
OPERATIONS
RESEARCH
SALES
Pontos para lembrar sobre UNION:
- O número de colunas e os tipos de dados selecionados devem ser idênticos em todos os SELECTs da consulta.
- Os nomes das colunas não precisam ser iguais.
- A saída é ordenada de forma ascendente pela primeira coluna do SELECT.
- Valores NULL não são ignorados na verificação de duplicidade.
UNION ALL combina resultados de uma ou mais consultas e NÃO remove duplicatas; portanto, DISTINCT não se aplica. Considere outra tabela emp com funcionários do ano 2000. Busque empno, nome e job de emp e employee:
SELECT empno, ename, job
FROM employee
UNION ALL
SELECT empno, ename, job
FROM emp;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7698 BLAKE MANAGER
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
EMPNO ENAME JOB
---------- ---------- ---------
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
INTERSECT
INTERSECT retorna os valores comuns entre dois conjuntos, conforme a teoria de conjuntos. Para R e S, retorna todas as tuplas presentes em ambos.
SELECT empno, ename, job
FROM employee
INTERSECT
SELECT empno, ename, job
FROM emp;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7698 BLAKE MANAGER
Pontos para lembrar sobre INTERSECT:
- O número de colunas e os tipos de dados selecionados devem ser idênticos em todos os SELECTs.
- Os nomes das colunas não precisam ser iguais.
- INTERSECT não ignora valores NULL.
EXCEPT e MINUS:
EXCEPT (SQL Server) e MINUS (Oracle) fazem a mesma coisa: selecionam todas as linhas distintas do primeiro SELECT que não aparecem no segundo. Para entender, veja a tabela:
Suponha que você precise de produtos com quantidade entre 1 e 100, exceto as quantidades entre 50 e 75. Use EXCEPT no SQL Server e MINUS no Oracle.
SELECT prod_name, qty FROM products WHERE qty BETWEEN 1 AND 100 EXCEPT SELECT prod_name, qty FROM products WHERE qty BETWEEN 50 AND 75;
Para Oracle:
SELECT prod_name, qty
FROM products
WHERE qty BETWEEN 1 AND 100
MINUS
SELECT prod_name, qty
FROM products
WHERE qty BETWEEN 50 AND 75;
PROD_NAME QTY
------------- ----------
COLGATE 1
SENSODYNE 100
SENSODYNE TOOTHBRUSH 30
Pontos para lembrar sobre EXCEPT/MINUS:
- O número de colunas e os tipos de dados selecionados devem ser idênticos em todos os SELECTs.
- Os nomes das colunas não precisam ser iguais.
- Todas as colunas usadas no WHERE devem estar no SELECT para o MINUS funcionar.
Parabéns!
Você chegou ao fim do tutorial. Aqui, você aprendeu uma base sólida de SQL para avançar na sua jornada em ciência de dados. SQL é essencial para gerar relatórios a partir de bancos de dados. Você viu os fundamentos de bancos e SQL, tipos de dados comuns, funções para formatar relatórios, agregações para criar resumos e também diferentes formas de combinar dados de múltiplas tabelas conforme sua necessidade. Este tutorial foi feito para alunos de ciência de dados e vai ajudar não só com bancos relacionais, mas também a navegar melhor por bancos NoSQL e aplicar SQL em estudos de Big Data.
Se quiser aprender mais sobre SQL, faça o curso gratuito da DataCamp Intro to SQL for Data Science.
Referências
Tópicos
