Neste curso, Mike Dane ensinará os fundamentos de gerenciamento de bancos de dados e SQL.

O curso começa com Mike ajudando você a instalar o MySQL no Windows ou Mac. Em seguida, ele explora temas como design de schemas, operações Create-Read-Update-Delete (CRUD) e outros fundamentos de bancos de dados.

Se você nunca estudou bancos de dados ou SQL antes, este curso é um excelente ponto de partida. O curso abrange:

• O que é um banco de dados
• Tabelas e chaves
• Fundamentos do SQL
• Instalação do MySQL no Windows/Mac
• Criação de tabelas
• Inserção de dados
• Restrições
• Atualização e exclusão de dados
• Consultas básicas
• Introdução a bancos de dados de empresas
• Criação de bancos de dados de empresas
• Outras consultas básicas
• Funções
• Caracteres curingas
• Uniões (Union)
• Junções (Join)
• Consultas aninhadas
• On Delete
• Triggers
• Diagramas de entidade e relacionamento

Você pode assistir o curso completo de SQL em 4 horas, sem anúncios, no YouTube (curso em inglês). Você pode assisti-lo clicando diretamente no link abaixo.

Boa programação para você!

Não exagero ao dizer que gosto muito de trabalhar com o SQL em meus projetos pessoais e profissionais.

Sua simplicidade e o fato de ser direto ao ponto apelam para o meu desejo de ter limites bem definidos com relação ao que a linguagem me deixará ou não "sair impune" em termos de sintaxe.

O SQL é estruturado de maneira clara e concisa em termos de operação, onde a entrada do usuário dita os dados retornados. Daí meu comentário sobre ter limites bem definidos na sintaxe.

Embora eu celebre sua resiliência (o SQL foi criado em 1974, tendo sido lançado inicialmente em 1986) na comunidade de desenvolvedores, também sei que, ao trabalhar com dados, pode parecer que até mesmo os fundamentos são estressantes e assustadores.

Estou aqui (espero) para lançar luz sobre esses fundamentos de trabalho com dados: excluir uma linha inteira de dados.

Embora não analisemos o processo de estabelecer uma tabela no SQL ou de preencher essa tabela com dados/atualizar esses dados, faço aqui o link para esses outros artigos (em inglês) caso você queira saber mais ou ainda revisar o assunto.

Certo. Agora, vamos à parte divertida – vamos começar a excluir dados de uma tabela!

Visão geral de exclusão de linhas no SQL

Aqui está a tabela, que chamamos de Cute_Doggos (cachorros fofos), que usaremos em nosso exemplo para a exclusão de dados:

Como você pode ter imaginado, são dados fictícios de uma tabela que eu tirei do nada. 🙂 Porém, espero que eles ilustrem aquilo a que nos dispomos.

Como acontece com a maioria dos aspectos de natureza técnica, nunca é demais verificar a documentação oficial também. Se você deseja fazer isso, a Microsoft tem ótimas informações detalhadas sobre a instrução DELETE no SQL (em inglês).

Vamos ao que importa nesse artigo – falar de exclusão de dados.  A ação é tão simples quanto o próprio nome e aqui está a sintaxe de base:

DELETE FROM nome_da_tabela

***Com essa sintaxe, você exclui todas as linhas de dados da tabela inteira.***

Em nosso exemplo acima, a consulta seria a seguinte:

DELETE FROM Cute_Doggos

Esse pode ser nossa finalidade. Quanto mais você escrever SQL, mais casos você encontrará de se usar a instrução DELETE nesse sentido.

Eu usei essa declaração muitas vezes para limpar uma tabela depois que ela foi preenchida com dados de teste. Essa abordagem nos permite manter os nomes de coluna, tipos de dados, índices e estrutura geral da tabela, de fato, sem excluir a tabela real.

Como observação, se sua finalidade for a de excluir todas as linhas dentro de uma tabela, a abordagem mais rápida seria a de usar a instrução TRUNCATE TABLE (em inglês), já que usa muito menos recursos de sistema.

Exemplo de consultas com Delete

Na grande maioria das vezes, quando você usa a funcionalidade DELETE, você vai querer direcionar melhor sua abordagem. Para isso, adicionaremos uma condição e a sintaxe ficará assim:

DELETE FROM nome_da_tabela WHERE condicoes_existentes

Usando nossa tabela de cães acima, a consulta teria essa aparência:

DELETE FROM Cute_Doggos WHERE dbo.Cute_Doggos.Height > 18

Isso removeria todas as entradas da nossa tabela que correspondam à nossa condição de que a altura (height) seja maior que 18. Se você estiver usando o Microsoft SQL Server Manager, receberá uma instrução de retorno assim:

(1 row affected) (1 linha afetada)

Se você quiser ver as linhas de dados que foram excluídas (para fins de registro, indicações visuais para usuários e assim por diante), podemos usar a instrução OUTPUT para fazer exatamente isso.

Nossa tabela, agora, ficaria assim:

A condição que definimos é totalmente nossa escolha. Se isso for muito estreito para suas necessidades, há outras opções.

Digamos que você não se importe com os registros específicos que remove, apenas que você precisa remover um certo número de registros na tabela.

DELETE TOP 2 FROM Cute_Doggos

Você pode estar pensando que essa consulta removeria os dois primeiros registros da tabela – e você não está muito longe da verdade. O único problema é que, como o SQL armazena registros em uma ordem aleatória, essa sintaxe removerá dois registros ALEATÓRIOS da tabela.

Se quiser remover uma porcentagem de registros, o SQL também consegue fazer isso:

DELETE TOP 25 PERCENT FROM Cute_Doggos

Mais uma vez, a consulta excluirá registros ALEATÓRIOS. Em nosso exemplo, como temos quatro registros, essa consulta excluiria um registro aleatório (4 * 0.25 = 1).

Como comentário final, ao usar a palavra-chave TOP, não podemos usar uma instrução ORDER BY, em função da aleatoriedade do armazenamento de registros.

Conclusão

Agora que você viu a instrução DELETE em ação, você está pronto para testar e excluir todos os dados de todas as tabelas! Quem sabe não faça isso? Poderá custar um final de semana prolongado de restauração de backups. 😉

De qualquer forma, agora você está pronto para começar a usá-la bem.

Espero que o artigo tenha sido útil. Confira meu blog (em inglês). Atualmente, estou reconstruindo o blog em Gatsby/WordPress. Confira um artigo em breve sobre o assunto. No blog, eu frequentemente publico artigos sobre desenvolvimento para a web, vida em geral e aprendizagem.

Enquanto estiver lá no blog, inscreva-se também na minha newsletter. Você pode fazer isso no canto superior direito da página principal do blog. Gosto de enviar artigos interessantes (meus e de outros autores), recursos e ferramentas para desenvolvedores de vez em quando.

Se tiver perguntas sobre este artigo ou sobre qualquer coisa em geral, deixe um oi no Twitter, em outra rede social que você encontrar na newsletter, na página principal do blog ou no meu perfil aqui no freeCodeCamp. 😀

Tenha um ótimo dia! Boa aprendizagem e uma ótima programação para você.

As joins (ou junções, no português) do SQL permitem que nossos sistemas de gerenciamento de banco de dados sejam, bem, relacionais.

Joins nos permitem reconstruir nossas tabelas separadas dentro do banco de dados em relacionamentos poderosos para nossas aplicações.

Neste artigo, vamos olhar para cada tipo diferente de join no SQL e aprender como usá-los.

Sobre o que vamos tratar neste artigo:

• O que é uma join?
• Como configurar nosso banco de dados
• CROSS JOIN
• Como configurar nossos dados de exemplo (diretores e filmes)
• FULL OUTER JOIN
• INNER JOIN
• LEFT JOIN / RIGHT JOIN
• Como filtrar dados com LEFT JOIN
• Múltiplas joins
• Joins com condições extras
• A realidade sobre escrever queries (ou buscas, no português)  com joins

(Alerta de spoiler: abordaremos cinco tipos diferentes– mas você precisará saber apenas dois deles!)

O que é uma join?

Uma join é uma operação que combina duas ou mais linhas em uma só linha.

Essas linhas normalmente vêm de duas tabelas diferentes, embora não precisem ser.

Antes de olharmos como escrever uma join propriamente dita, vamos olhar para o que seria o resultado de uma join.

Vamos pegar o exemplo de um sistema que armazena informações sobre usuários e seus endereços.

As linhas da tabela que armazena as informações dos usuários se parece com isso:

 id |     name     |        email        | age
----+--------------+---------------------+-----
  1 | John Smith   | johnsmith@gmail.com |  25
  2 | Jane Doe     | janedoe@Gmail.com   |  28
  3 | Xavier Wills | xavier@wills.io     |  3
...
(7 rows)

E as linhas da tabela que armazena informações sobre os endereços seria desse jeito:

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

Poderíamos escrever queries para buscar separadamente tanto as informações do usuário como seu endereço – mas, idealmente, poderíamos escrever apenas uma query para retornar todos os usuários e seus endereços no mesmo resultado.

E é exatamente isso que a join nos permite!

Veremos como escrever essas junções em breve, mas, se juntarmos nossas informações de usuário com as de endereço, teremos este resultado:

 id |     name     |        email        | age | id |      street       |     city      | state | user_id
----+--------------+---------------------+-----+----+-------------------+---------------+-------+---------
  1 | John Smith   | johnsmith@gmail.com |  25 |  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | Jane Doe     | janedoe@Gmail.com   |  28 |  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | Xavier Wills | xavier@wills.io     |  35 |  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

Aqui, podemos ver todos os nossos usuários e seus respectivos endereços em um único resultado.

Além de produzir resultados combinados, outro uso importante das joins é retirar informações adicionais da nossa busca, que também podem ser filtrados.

Por exemplo, se quisermos enviar uma correspondência física para nossos usuários que moram na cidade de Oklahoma, podemos usar essa join e definir um filtro baseado na coluna city.

Agora que sabemos o propósito das joins– vamos escrever algumas!

Como configurar nosso banco de dados

Antes de começarmos a escrever nossas queries, precisamos configurar nosso banco de dados.

Para os nossos exemplos, usaremos o PostgreSQL, mas a escrita das queries e os conceitos mostrados aqui podem ser facilmente traduzidos para qualquer outro sistema de banco de dados moderno (como o MySQL, o SQL Server etc.).

Para trabalhar com um banco PostgreSQL, usamos o psql – a linha de comando interativa do PostgreSQL. Se você tiver algum outro client (ou serviço, em português) com o qual esteja acostumado a trabalhar, não tem problema.

Vamos começar criando nosso banco de dados. Com o PostegreSQL já instalado, podemos executar o comando createdb <nome-do-banco-da-dados> em nosso terminal para criar um banco de dados. Vou chamar o meu de fcc:

$ createdb fcc

Vamos iniciar o console interativo usando o comando  psql e conectar o banco de dados que criamos usando \c <nome-do-banco-de-dados>:

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

Observação: eu limpei o resultado do psql nesses exemplos para tornar mais fácil a leitura. Então, não se preocupe se o seu retorno não for exatamente igual ao que está vendo no seu terminal.

Eu aconselho você a seguir os exemplos e executar as queries por conta própria. Desse modo, você aprenderá e se lembrará com mais facilidade, trabalhando nos exemplos em vez de apenas ler sobre eles.

Agora, vamos às joins!

CROSS JOIN

O tipo mais simples de join que podemos fazer é a CROSS JOIN, ou "produto cartesiano".

Essa join pega cada linha de uma tabela e a combina com cada linha da outra tabela.

Se tivéssemos duas listas – uma contendo 1, 2, 3 e a outra contendo A, B, C — o produto cartesiano das duas seria:

1A, 1B, 1C
2A, 2B, 2C
3A, 3B, 3C

Cada valor da primeira lista é emparelhado com cada valor da segunda lista.

Vamos escrever esse exemplo simples em uma query do SQL.

Primeiro, vamos criar duas tabelas simples e inserir alguns dados:

CREATE TABLE letters(
  letter TEXT
);

INSERT INTO letters(letter) VALUES ('A'), ('B'), ('C');

CREATE TABLE numbers(
  number TEXT
);

INSERT INTO numbers(number) VALUES (1), (2), (3);

Nossas duas tabelas, letters e numbers, tem apenas uma coluna: um simples campo de texto.

Agora, vamos juntá-las com um CROSS JOIN:

SELECT *
FROM letters
CROSS JOIN numbers;

 letter | number
--------+--------
 A      | 1
 A      | 2
 A      | 3
 B      | 1
 B      | 2
 B      | 3
 C      | 1
 C      | 2
 C      | 3
(9 rows)

Esse é o tipo mais simples de join que podemos fazer – mesmo nesse exemplo simples, no entanto, podemos ver a join em funcionamento: as duas linhas separadas (uma de letters e outra de numbers) foram combinadas para formar uma única linha.

Embora esse tipo de join seja frequentemente discutido como sendo apenas um exemplo acadêmico, ele tem um bom caso de uso: cobrir intervalos de datas.

CROSS JOIN com intervalos de datas

Um bom caso de uso do CROSS JOIN é pegar cada linha de uma tabela e aplicar para cada dia de um intervalo de data específico.

Digamos, por exemplo, que estávamos criando uma aplicação que monitora atividades diárias – coisas como escovar os dentes, tomar o café da manhã ou tomar banho.

Se você quiser gerar um registro para cada tarefa e para cada dia da semana passada, você poderia usar a CROSS JOIN em um intervalo de datas.

Para criar esse intervalo, podemos usar a função generate_series:

SELECT generate_series(
  (CURRENT_DATE - INTERVAL '5 day'),
  CURRENT_DATE,
  INTERVAL '1 day'
)::DATE AS day;

A função generate_series recebe três parâmetros:

O primeiro parâmetro é o valor de início. Nesse exemplo, usamos CURRENT_DATE - INTERVAL '5 day'. Isso retorna o dia atual menos 5 dias – ou "cinco dias atrás".

O segundo parâmetro é a data atual (CURRENT_DATE).

O terceiro parâmetro é o "intervalo" – ou o quanto nós queremos incrementar em cada valor por vez. Já que essas são tarefas diárias, vamos usar o intervalo de um dia (INTERVAL '1 day').

Juntando tudo, isso gera uma série de datas começando de cinco dias atrás, terminando hoje e sendo incrementando um dia por vez.

Finalmente, removemos a parte do tempo, lançando a saída desses valores para uma data usando ::DATE, e atribuímos um nome à coluna usando AS day para fazer com que o retorno seja um pouco melhor.

O resultado dessa query são os últimos cinco dias mais o dia de hoje:

    day
------------
 2020-08-19
 2020-08-20
 2020-08-21
 2020-08-22
 2020-08-23
 2020-08-24
(6 rows)

Voltando ao nosso exemplo de tarefas diárias, vamos criar uma tabela simples para conter as tarefas que queremos completar e inserir algumas tarefas:

CREATE TABLE tasks(
  name TEXT
);

INSERT INTO tasks(name) VALUES
('Escovar os dentes'),
('Tomar o café da manhã'),
('Tomar banho'),
('Vestir-se');

Nossa tabela tasks tem apenas uma coluna, name, Nela, nós inserimos quatro tarefas.

Agora, vamos usar a CROSS JOIN nas nossas tarefas e na query que cria o intervalo de datas:

SELECT
  tasks.name,
  dates.day
FROM tasks
CROSS JOIN
(
  SELECT generate_series(
    (CURRENT_DATE - INTERVAL '5 day'),
    CURRENT_DATE,
    INTERVAL '1 day'
  )::DATE	AS day
) AS dates

(Já que nossa query de produção de dados não é exatamente uma tabela, nós podemos escrevê-la como uma subquery.)

A partir dessa query, retornamos o nome das tarefas e o dia. O resultado terá essa aparência:

     name              |    day
---------------+-------------------
 Escovar os dentes     | 2020-08-19
 Escovar os dentes     | 2020-08-20
 Escovar os dentes     | 2020-08-21
 Escovar os dentes     | 2020-08-22
 Escovar os dentes     | 2020-08-23
 Escovar os dentes     | 2020-08-24
 Tomar o café da manhã | 2020-08-19
 Tomar o café da manhã | 2020-08-20
 Tomar o café da manhã | 2020-08-21
 Tomar o café da manhã | 2020-08-22
 ...
 (24 rows)

Como esperávamos, nós recebemos uma linha para cada tarefa para cada dia em nosso intervalo de datas.

A CROSS JOIN é a join mais simples que podemos fazer, mas, para ver os outros tipos, vamos precisar de um exemplo mais realista de configuração de tabela:

Como configurar nossos dados de exemplo (diretores e filmes)

Para ilustrar os próximos tipos de join, vamos usar o exemplo de movies e movie directors (filmes e diretores de filmes, em português).

Nessa situação, o filme tem apenas um diretor, mas um filme não necessariamente precisa ter um diretor associado – imagine que um novo filme foi anunciado mas a escolha do diretor ainda não foi feita ou confirmada.

Nossa tabela directors armazenará o nome de cada diretor, enquanto a tabela movies armazenará o nome de cada filme, assim como a referência do diretor associado (caso haja uma).

Vamos criar as duas tabelas e inserir alguns dados nelas:

CREATE TABLE directors(
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

INSERT INTO directors(name) VALUES
('John Smith'),
('Jane Doe'),
('Xavier Wills')
('Bev Scott'),
('Bree Jensen');

CREATE TABLE movies(
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  director_id INTEGER REFERENCES directors 
);

INSERT INTO movies(name, director_id) VALUES
('Movie 1', 1),
('Movie 2', 1),
('Movie 3', 2),
('Movie 4', NULL),
('Movie 5', NULL);

Temos cinco diretores, cinco filmes e três desses filmes tem diretores associados a eles. O diretor com o ID 1 tem dois filmes, enquanto o diretor ID 2 tem um.

FULL OUTER JOIN

Agora que temos alguns dados para trabalhar, vamos olhar a FULL OUTER JOIN.

A FULL OUTER JOIN tem algumas semelhanças com a CROSS JOIN, mas algumas diferenças importantes.

A primeira diferença é que uma FULL OUTER JOIN precisa de uma condição para fazer a join.

A condição da join especifica como duas linhas de duas tabelas diferentes estão relacionadas uma com a outra, bem como o critério a ser usado para juntá-las.

No nosso exemplo, nossa tabela movies tem uma referência aos diretores através da coluna director_id. Essa coluna corresponde à coluna id da tabela directors. Essas são as duas colunas que usaremos como condição para a join.

Escrevemos essa join entre as duas tabelas desta forma:

SELECT *
FROM movies
FULL OUTER JOIN directors
  ON directors.id = movies.director_id;

Observe a condição da join especificada, que corresponde o filme ao seu diretor : ON movies.director_id = directors.id.

Nossos resultados vão se parecer com um produto cartesiano estranho:

  id  |  name   | director_id |  id  |     name
------+---------+-------------+------+--------------
    1 | Movie 1 |           1 |    1 | John Smith
    2 | Movie 2 |           1 |    1 | John Smith
    3 | Movie 3 |           2 |    2 | Jane Doe
    4 | Movie 4 |        NULL | NULL | NULL
    5 | Movie 5 |        NULL | NULL | NULL
 NULL | NULL    |        NULL |    5 | Bree Jensen
 NULL | NULL    |        NULL |    4 | Bev Scott
 NULL | NULL    |        NULL |    3 | Xavier Wills
(8 rows)

As primeiras linhas que veremos são aquelas onde o filme tem um diretor e que nossa condição de join avaliou como verdadeira.

No entanto, após essas linhas, vemos algumas linhas restantes de cada tabela – mas com valores NULL, onde a outra tabela não teve uma correspondência.

Observação: se você não está familiarizado com valores do tipo NULL, pode veja esta explicação (em inglês) no tutorial de operadores SQL

Também vemos outra diferença entre a CROSS JOIN e a FULL OUTER JOIN aqui. A FULL OUTER JOIN retorna uma linha distinta em cada linha– diferentemente da CROSS JOIN, que retorna múltiplas.

INNER JOIN

O próximo tipo de join, a INNER JOIN, é mais comumente utilizado do que todos os outros.

Uma inner join retorna apenas as linhas onde a condição de join é verdadeira.

No nosso exemplo, uma inner join entre nossas tabelas de  movies e directors retornaria apenas registros onde o filme possui um diretor atribuído.

A sintaxe é basicamente a mesma que vimos antes:

SELECT *
FROM movies
INNER JOIN directors
  ON directors.id = movies.director_id;

Nosso resultado mostra apenas os três filmes que possuem um diretor.

 id |  name   | director_id | id |    name
----+---------+-------------+----+------------
  1 | Movie 1 |           1 |  1 | John Smith
  2 | Movie 2 |           1 |  1 | John Smith
  3 | Movie 3 |           2 |  2 | Jane Doe
(3 rows)

Já que uma inner join inclui apenas as linhas que tenham uma condição de join que a corresponda, a ordem de junção das duas tabelas não fará diferença.

Se invertermos a ordem das tabelas, a query terá o mesmo resultado:

SELECT *
FROM directors
INNER JOIN movies
  ON movies.director_id = directors.id;

 id |    name    | id |  name   | director_id
----+------------+----+---------+-------------
  1 | John Smith |  1 | Movie 1 |           1
  1 | John Smith |  2 | Movie 2 |           1
  2 | Jane Doe   |  3 | Movie 3 |           2
(3 rows)

Como listamos a tabela de directors primeiro nessa query e selecionamos todas as colunas (SELECT *), vemos a coluna de directors primeiro e depois as outras colunas de movies - o resultado dos dados, no entanto, é o mesmo.

Essa é uma propriedade muito útil das inner joins, mas não é uma realidade de todos os outros tipos de join– como veremos no nosso próximo exemplo.

LEFT JOIN/RIGHT JOIN

Os próximos dois tipos de join usam um modificador (LEFT ou RIGHT) que afeta qual dado de tabela será incluído no resultado final.

Observação: a LEFT JOIN e a RIGHT JOIN também podem ser referidas como LEFT OUTER JOIN e RIGHT OUTER JOIN.

Essas joins são usadas em queries onde queremos retornar todos os dados da tabela específica e, caso existam, os dados correspondentes da outra tabela.

Se os dados correspondentes não existirem, ainda teremos os dados da tabela primária.

Seria como uma busca por uma informação específica e um bônus caso a informação de bônus exista.

Isso será simples de entender com um exemplo. Vamos encontrar todos os filmes e seus diretores, mas não vamos nos importar com o fato de eles terem um diretor ou não – esse é o bônus:

SELECT *
FROM movies
LEFT JOIN directors
  ON directors.id = movies.director_id;

A query segue o mesmo padrão que vimos antes – apenas especificamos a join como LEFT JOIN.

Nesse exemplo, a tabela movies é a tabela left (ou da esquerda, em português).

Se escrevermos a query em uma linha, fica um pouco mais fácil de ver:

... FROM movies LEFT JOIN directors ...

Uma left join retorna todos os dados da tabela da esquerda.

Uma left join retorna também todas linhas da tabela da direita (a outra tabela, no caso) que correspondam à condição da join.

As colunas da tabela da direita que não correspondam à condição da join serão retornadas como NULL.

 id |  name   | director_id |  id  |    name
----+---------+-------------+------+------------
  1 | Movie 1 |           1 |    1 | John Smith
  2 | Movie 2 |           1 |    1 | John Smith
  3 | Movie 3 |           2 |    2 | Jane Doe
  4 | Movie 4 |        NULL | NULL | NULL
  5 | Movie 5 |        NULL | NULL | NULL
(5 rows)

Olhando para os resultados,  podemos ver o porquê de esse tipo de join ser útil para uma busca que "traga tudo disto e, caso exista, um pouco daquilo".

RIGHT JOIN

A RIGHT JOIN funciona exatamente como a LEFT JOIN – exceto que as regras agora são inversas para a ordem das tabelas.

Em uma right join, todas as linhas da tabela da direita são retornadas. A tabela da esquerda é retornada com base na condição da join.

Vamos usar a mesma query que vimos acima e substituir a LEFT JOIN por RIGHT JOIN:

SELECT *
FROM movies
RIGHT JOIN directors
  ON directors.id = movies.director_id;

  id  |  name   | director_id | id |     name
------+---------+-------------+----+--------------
    1 | Movie 1 |           1 |  1 | John Smith
    2 | Movie 2 |           1 |  1 | John Smith
    3 | Movie 3 |           2 |  2 | Jane Doe
 NULL | NULL    |        NULL |  5 | Bree Jensen
 NULL | NULL    |        NULL |  4 | Bev Scott
 NULL | NULL    |        NULL |  3 | Xavier Wills
(6 rows)

Nosso resultado agora são todas as linhas de directors e, caso existam, os dados de movies.

Tudo  o que fizemos foi trocar qual tabela é considerada primária – a tabela da qual queremos ver todos os dados sem considerar se temos dados associados.

LEFT JOIN/RIGHT JOIN na produção de aplicações

Na criação de uma aplicação, eu utilizo apenas LEFT JOIN e nunca uso RIGHT JOIN.

Eu faço isso porque, na minha opinião, um LEFT JOIN torna a query muito mais fácil de ser lida e entendida.

Quando estou escrevendo uma query, gosto de começar a pensar em um resultado "base", digamos todos os dados de filmes, e então trazer (ou retirar) os grupos de informações dessa base.

Como eu gosto de começar com uma base, a LEFT JOIN se encaixa bem nessa linha de raciocínio. Eu quero todas as linhas da minha tabela base (a tabela da esquerda) e, condicionalmente, as da tabela da direita.

Na prática, eu não acho que já tenha visto uma RIGHT JOIN em uma aplicação em produção. Entenda, não tem nada de errado com uma RIGHT JOIN - eu apenas acho que deixa a query mais difícil de entender.

Reescrevendo o RIGHT JOIN

Se quisermos virar nosso cenário acima, ao invés de retornar todos os diretores e condicionalmente seus filmes, podemos facilmente reescrever a RIGHT JOIN para uma LEFT JOIN.

Tudo que precisamos fazer é trocar a ordem das tabelas da query e mudar a palavra RIGHT para LEFT:

SELECT *
FROM directors
LEFT JOIN movies
  ON movies.director_id = directors.id;

Observação: eu gosto de colocar a tabela que será adicionada (a da direita– no exemplo acima, de movies) primeiro na condição da join (ON movies.director_id = ...) – mas essa é apenas minha preferencia pessoal.

Como filtrar dados com LEFT JOIN

Existem dois casos de uso para a LEFT JOIN (ou a RIGHT JOIN).

O primeiro, nós já vimos: retornar todas as linhas de uma tabela e, condicionalmente, as da outra.

O segundo caso de uso é para retornar as linhas da primeira tabela onde os dados da segunda tabela não estão presentes.

O cenário tem essa aparência: encontre todos os diretores que não pertencem a nenhum filme.

Para fazer isso, vamos começar com uma LEFT JOIN e nossa tabela directors será a primária ou a tabela da esquerda:

SELECT *
FROM directors
LEFT JOIN movies
  ON movies.director_id = directors.id;

Para um diretor que não pertence a nenhum filme, as colunas da tabela movies são NULL:

 id |     name     |  id  |  name   | director_id
----+--------------+------+---------+-------------
  1 | John Smith   |    1 | Movie 1 |           1
  1 | John Smith   |    2 | Movie 2 |           1
  2 | Jane Doe     |    3 | Movie 3 |           2
  5 | Bree Jensen  | NULL | NULL    |        NULL
  4 | Bev Scott    | NULL | NULL    |        NULL
  3 | Xavier Wills | NULL | NULL    |        NULL
(6 rows)

No nosso exemplo, os diretores com ID 3, 4 e 5 não pertencem a nenhum filme.

Para filtrar nosso resultado para apenas essas linhas, podemos adicionar uma cláusula WHERE que retornará apenas os filmes onde os dados são NULL:

SELECT *
FROM directors
LEFT JOIN movies
  ON movies.director_id = directors.id
WHERE movies.id IS NULL;

 id |     name     |  id  | name | director_id
----+--------------+------+------+-------------
  5 | Bree Jensen  | NULL | NULL |        NULL
  4 | Bev Scott    | NULL | NULL |        NULL
  3 | Xavier Wills | NULL | NULL |        NULL
(3 rows)

E aqui estão nossos três filmes sem diretor!

É comum usar a coluna id da tabela para filtrar os dados (WHERE movies.id IS NULL), mas todas as colunas da tabela movies são NULL - nesse caso, qualquer uma funcionaria.

Já que sabemos que todas as colunas de movies seriam NULL. Na query acima, poderíamos escrever apenas SELECT directors.* ao invés de SELECT * para retornar todas as informações dos diretores.

Usando a LEFT JOIN para encontrar dados correspondentes

Na nossa query anterior, encontramos diretores que não pertenciam a nenhum filme.

Usando a mesma estrutura, podemos encontrar os diretores que pertencem a algum filme mudando apenas a nossa condição WHERE para buscar as linhas onde os dados do filme não são NULL:

SELECT *
FROM directors
LEFT JOIN movies
  ON movies.director_id = directors.id
WHERE movies.id IS NOT NULL;

 id |    name    | id |  name   | director_id
----+------------+----+---------+-------------
  1 | John Smith |  1 | Movie 1 |           1
  1 | John Smith |  2 | Movie 2 |           1
  2 | Jane Doe   |  3 | Movie 3 |           2
(3 rows)

Pode até parecer prático, mas, na verdade, nós implementamos novamente a INNER JOIN!

Múltiplas joins

Nós já vimos como combinar duas tabelas, mas e se quisermos fazer múltiplas joins em uma linha?

Na verdade, é bastante simples, mas para ilustrar isso vamos precisar de uma terceira tabela: tickets.

Essa tabela representará os ingressos vendidos por filme:

CREATE TABLE tickets(
  id SERIAL PRIMARY KEY,
  movie_id INTEGER REFERENCES movies NOT NULL
);

INSERT INTO tickets(movie_id) VALUES (1), (1), (3);

A tabela tickets  tem apenas um id e a referência a um filme: movie_id.

Nós também inserimos dois ingressos vendidos para o filme ID 1, e um ingresso vendido para o filme ID 3.

Agora, vamos combinar directors com movies — e, então, movies com tickets!

SELECT *
FROM directors
INNER JOIN movies
  ON movies.director_id = directors.id
INNER JOIN tickets
  ON tickets.movie_id = movies.id;

Já que ambas são inner joins, a ordem em que nós escrevemos a join não importa. Poderíamos ter começado com tickets e, então, adicionado movies e finalizado com a adição de directors.

Novamente, tudo se resume a forma na qual você está tentando consultar e o que faz a busca mais compreensível.

No nosso resultado, vamos perceber que reduzimos ainda mais as linhas retornadas:

 id |    name    | id |  name   | director_id | id | movie_id
----+------------+----+---------+-------------+----+----------
  1 | John Smith |  1 | Movie 1 |           1 |  1 |        1
  1 | John Smith |  1 | Movie 1 |           1 |  2 |        1
  2 | Jane Doe   |  3 | Movie 3 |           2 |  3 |        3
(3 rows)

Isso faz sentido porque adicionamos outra INNER JOIN. Na prática, ela adiciona uma outra condicional do tipo "AND" na nossa busca.

Nossa query, essencialmente, diz: "retorne todos os diretores que pertencem a filmes que também tenham ingressos vendidos"

Se, ao invés disso, quiséssemos encontrar diretores que pertencem a filmes que talvez não tenham ingressos vendidos ainda, poderíamos substituir nossa INNER JOIN por uma LEFT JOIN:

SELECT *
FROM directors
JOIN movies
  ON movies.director_id = directors.id
LEFT JOIN tickets
  ON tickets.movie_id = movies.id;

Podemos ver que Movie 2 agora volta ao nosso conjunto de resultados:

 id |    name    | id |  name   | director_id |  id  | movie_id
----+------------+----+---------+-------------+------+----------
  1 | John Smith |  1 | Movie 1 |           1 |    1 |        1
  1 | John Smith |  1 | Movie 1 |           1 |    2 |        1
  2 | Jane Doe   |  3 | Movie 3 |           2 |    3 |        3
  1 | John Smith |  2 | Movie 2 |           1 | NULL |     NULL
(4 rows)

Esse filme ainda não teve nenhum ingresso vendido, mas foi excluído anteriormente dos resultados por conta da INNER JOIN.

Vou deixar isso como um Exercício para o Leitor™. Como você encontraria os diretores que pertencem a algum filme e não tem nenhum ingresso vendido?

A ordem de execução das joins

No fim, realmente não ligamos para a ordem como as joins são executadas.

Uma das diferenças principais entre o SQL e outras linguagens de programação modernas é o fato de o SQL ser uma linguagem declarativa.

Isso significa que especificamos o que queremos, mas não especificamos os detalhes da execução – esses detalhes são deixados para o planejador de buscas da base de dados. Especificamos as joins que queremos e as condições e o planejador do banco de dados cuida do resto.

Na realidade, porém, o banco de dados não está juntando três tabelas ao mesmo tempo. Ao invés disso, ele provavelmente unirá as duas primeiras tabelas em um resultado intermediário e, então, usará esse resultado para unir a terceira tabela.

(Observação: essa é uma explicação bastante simplificada.)

Então, como estamos trabalhando em buscas com várias joins, podemos simplesmente pensar que são joins entre duas tabelas – mesmo que uma delas seja bastante grande.

Joins com condições extras

O último tópico de que trataremos são as joins com condições extras.

Da mesma forma que ocorre com a cláusula WHERE, podemos adicionar quantas condicionais quisermos as condições da join.

Por exemplo, se quisermos encontrar os filmes cujos diretores não tenham o nome "John Smith", podemos adicionar uma condição extra à nossa join com um AND:

SELECT *
FROM movies
INNER JOIN directors
  ON directors.id = movies.director_id
  AND directors.name <> 'John Smith';

Podemos usar qualquer operador onde colocaríamos uma cláusula WHERE nessa condicional da join.

Podemos também ter o mesmo resultado dessa busca se colocarmos a condicional com a cláusula WHERE :

SELECT *
FROM movies
INNER JOIN directors
  ON directors.id = movies.director_id
WHERE directors.name <> 'John Smith';

Existem algumas diferenças sutis acontecendo por trás dos panos aqui, mas, para o propósito deste artigo, o resultado é o mesmo.

(Se você não é familiarizado com todas as outras formas de filtrar uma busca em SQL, dê uma olhada neste artigo comentado anteriormente - em inglês.)

A realidade sobre escrever queries e joins

Na realidade, eu apenas uso joins de três formas diferentes:

INNER JOIN

O primeiro caso de uso é o de registros onde o relacionamento entre duas tabelas existe. Isso é coberto totalmente pelo INNER JOIN.

Essas são situações como encontrar "filmes que tenham diretores" ou "usuários que tenham postagens".

LEFT JOIN

O segundo caso de uso é o de registros de uma tabela – e se o relacionamento existir– e registros de uma segunda tabela. Esse caso é coberto pelo LEFT JOIN.

Esse caso é o de situações como "filmes com diretores caso existam" ou "usuários com postagens caso haja alguma"

Exclusão do  LEFT JOIN

O terceiro caso de uso mais comum é o nosso segundo caso com uma LEFT JOIN: encontrar os registros de uma tabela que não tenham relação com a segunda tabela.

São situações como "filmes sem diretores" ou "usuários sem postagens".

Dois tipos de joins muito úteis.

Eu não acho que já tenha usado uma FULL OUTER JOIN ou uma  RIGHT JOIN em uma aplicação em produção. Esses tipos de caso de uso não costumam aparecer frequentemente ou a query pode ser escrita de forma mais simples (no caso do RIGHT JOIN).

Ocasionalmente, eu já usei o CROSS JOIN para algumas coisas como espalhar registros em um intervalo de datas (como vimos no começo do artigo), mas esse cenário também não costuma aparecer com frequência.

Então, boas notícias! Existem realmente dois tipos de joins que você precisa saber para 99,9% dos casos que aparecerão: INNER JOIN e LEFT JOIN!

Se você gostou deste artigo, pode seguir o autor no Twitter, onde ele fala sobre assuntos relacionados a bancos de dados e a desenvolvimento.

Obrigado pela leitura!

Um último conselho para o final: a maioria dos bancos de dados permitirá que você escreva apenas JOIN no lugar do INNER JOIN – isso poupará um pouco de digitação. :)

SQL é a abreviação de Structured Query Language, ou Linguagem de Consulta Estruturada. Ela é usada para todos os tipos de bancos de dados relacionais.

Exemplo de sintaxe básica de SQL

Este guia fornece uma descrição básica e geral da sintaxe das instruções em SQL.

SQL é um padrão internacional (ISO), mas você encontrará muitas diferenças entre as implementações. Este guia usa o MySQL como exemplo. Se você usar um dos vários outros Sistemas de Gerenciamento de Bancos de Dados (conhecidos pela sigla DBMS, em inglês), precisará conferir o manual do DBMS em questão, se necessário.

Do que trataremos

• Uso (define que banco de dados a instrução utilizará)
• Instruções com SELECT e FROM
• Instrução WHERE (AND/OR, IN, BETWEEN e LIKE)
• ORDER BY (ASC, DESC)
• GROUP BY e HAVING

Como usar

O comando abaixo é usado para selecionar o banco de dados contendo as tabelas para suas instruções em SQL:

use fcc_sql_guides_database; -- seleciona o banco de dados da amostra, chamado fcc_sql_guides

Instruções SELECT e FROM

A parte SELECT normalmente é utilizada para determinar quais colunas dos dados você quer exibir nos resultados. Também existem opções que você pode usar para mostrar dados que não são de uma coluna da tabela.

Este exemplo mostra duas colunas selecionadas da tabela "student" e duas colunas calculadas. A primeira das colunas calculadas é um número sem significado. A outra é a data do sistema.

	select studentID, FullName, 3+2 as five, now() as currentDate
    from student;

Instrução WHERE (AND/OR, IN, BETWEEN e LIKE)

A instrução WHERE é usada para limitar o número de linhas retornado.

    select studentID, FullName, sat_score, recordUpdated
    from student
    where (
		studentID between 1 and 5
		or studentID = 8
        or FullName like '%Maximo%'
		);

Neste caso, usar todas as propriedades torna a instrução WHERE bastante ridícula.

Compare o resultado com a instrução SQL acima para seguir a lógica.

Serão apresentadas linhas que:

• Têm studentIDs entre 1 e 5 (incluindo os extremos)
• ou studentID = 8
• ou ainda que tenha "Maximo" no nome

O exemplo a seguir é semelhante, mas especifica ainda mais excluindo os alunos com pontuações de SAT que estiverem entre 1000 e 1400. Esses não serão apresentados:

    select studentID, FullName, sat_score, recordUpdated
    from student
    where (
		studentID between 1 and 5
		or studentID = 8
        or FullName like '%Maximo%'
		)
		and sat_score NOT in (1000, 1400);

ORDER BY (ASC, DESC)

ORDER BY nos oferece uma maneira de ordenar o conjunto de resultados por um ou mais itens da seção SELECT. Abaixo veremos a mesma lista que vemos acima, mas ordenada pelo Full Name (nome completo) dos alunos. A ordem padrão é a ascendente (ASC), mas para ordenar em ordem inversa (descendente), é possível usar DESC.

SELECT studentID, FullName, sat_score
FROM student
ORDER BY FullName DESC;

+-----------+------------------------+-----------+
| studentID | FullName               | sat_score |
+-----------+------------------------+-----------+
|         2 | Teri Gutierrez         |       800 |
|         3 | Spencer Pautier        |      1000 |
|         6 | Sophie Freeman         |      1200 |
|         9 | Raymond F. Boyce       |      2400 |
|         1 | Monique Davis          |       400 |
|         4 | Louis Ramsey           |      1200 |
|         7 | Edgar Frank "Ted" Codd |      2400 |
|         8 | Donald D. Chamberlin   |      2400 |
|         5 | Alvin Greene           |      1200 |
+-----------+------------------------+-----------+
9 rows in set (0.00 sec)

Abaixo vemos a lista NÃO ORDENADA, atual e completa dos alunos, para comparar à lista acima.

SELECT studentID, FullName, sat_score, rcd_updated FROM student;

+-----------+------------------------+-----------+---------------------+
| studentID | FullName               | sat_score | rcd_updated         |
+-----------+------------------------+-----------+---------------------+
|         1 | Monique Davis          |       400 | 2017-08-16 15:34:50 |
|         2 | Teri Gutierrez         |       800 | 2017-08-16 15:34:50 |
|         3 | Spencer Pautier        |      1000 | 2017-08-16 15:34:50 |
|         4 | Louis Ramsey           |      1200 | 2017-08-16 15:34:50 |
|         5 | Alvin Greene           |      1200 | 2017-08-16 15:34:50 |
|         6 | Sophie Freeman         |      1200 | 2017-08-16 15:34:50 |
|         7 | Edgar Frank "Ted" Codd |      2400 | 2017-08-16 15:35:33 |
|         8 | Donald D. Chamberlin   |      2400 | 2017-08-16 15:35:33 |
|         9 | Raymond F. Boyce       |      2400 | 2017-08-16 15:35:33 |
+-----------+------------------------+-----------+---------------------+
9 rows in set (0.00 sec)

Podemos limitar ainda mais. Nesse caso, com studentIDs entre 1 e 5 OU studentID de 8 OU nome completo que contenha "Maximo" E pontuação do SAT que não seja de 1000 ou 1400

    select studentID, FullName, sat_score
    from student
    where (studentID between 1 and 5 -- incluindo os extremos
		or studentID = 8
        or FullName like '%Maximo%')
		and sat_score NOT in (1000, 1400)
	order by FullName DESC;

GROUP BY e HAVING

GROUP BY nos dá uma maneira de combinar linhas e dados agregados. A instrução HAVING é como a instrução WHERE acima, com a exceção de que ela atua nos dados agrupados.

Os dados abaixo pertencem aos dados de contribuições de campanha que usamos em alguns desses guias.

A instrução em SQL que segue responde a pergunta: "quais candidatos receberam o menor número de contribuições (não em termos de dinheiro, mas de contagem (*)) em 2016, mas somente aqueles que tiveram mais de 80 contribuições?"

Ordenar esse conjunto de dados em uma ordem descendente (DESC) coloca os candidatos com o maior número de contribuições na parte de cima da lista.

    select Candidate, Election_year, sum(Total_$), count(*)
    from combined_party_data
    where Election_year = 2016
    group by Candidate, Election_year
    having count(*) > 80
    order by count(*) DESC;

O operador BETWEEN em SQL

O operador BETWEEN é útil, por causa do otimizador de consultas do SQL (ou SQL Query Optimizer). Embora BETWEEN seja, funcionalmente, o mesmo que: x <= elemento <= y, o otimizador de consultas do SQL reconhecerá esse comando mais rápido e terá um código otimizado para executá-lo.

Esse operador é usado em uma instrução WHERE ou em uma instrução GROUP BY/HAVING.

As linhas selecionadas são as que têm um valor maior que o mínimo e menor que o máximo.

É importante lembrar de que os valores inseridos no comando são excluídos do resultado. Obtemos apenas os valores entre eles.

Essa é a sintaxe para usar a função em uma instrução WHERE:

select field1, testField
from table1
where testField between min and max

Aqui temos um exemplo usando a tabela student e a instrução WHERE:

-- sem a instrução WHERE
select studentID, FullName, studentID
from student;
    
-- com a instrução WHERE e com BETWEEN
select studentID, FullName, studentID
from student
where studentID between 2 and 7;

Aqui temos um exemplo de uso na tabela de fundos de campanha com a instrução HAVING. Ele retornará linhas onde a soma das doações para um candidato estavam ente US$3 milhões e US$18 milhões com base na instrução HAVING e na parte GROUP BY da instrução. Veja mais sobre agregação no guia específico.

select Candidate, Office_Sought, Election_Year, format(sum(Total_$),2)
from combined_party_data
where Election_Year = 2016
group by Candidate, Office_Sought, Election_Year
having sum(Total_$) between 3000000 and 18000000
order by sum(Total_$) desc; 

Uma tabela é um grupo de dados armazenado em um banco de dados.

Para criar uma tabela em um banco de dados, use a instrução CREATE TABLE. Você dá um nome à tabela e uma lista de colunas com seus tipos de dados.

CREATE TABLE TABLENAME(Attribute1 Datatype, Attribute2 Datatype,........);

Aqui temos um exemplo de criação da tabela Person:

CREATE TABLE Person(
  Id int not null,
  Name varchar not null,
  DateOfBirth date not null,
  Gender bit not null,
  PRIMARY KEY( Id )
);

No exemplo acima, cada Person (pessoa) tem um Name (nome), uma Date of Birth (data de nascimento) e um Gender (gênero). A coluna Id é a chave que identifica uma pessoa na tabela. Você usa a palavra-chave PRIMARY KEY para configurar uma ou mais colunas como uma chave primária.

Uma coluna pode ser not null ou null, indicando se ela é ou não obrigatória.

Consultas do tipo INSERT são uma maneira de inserir dados em uma tabela. Digamos que tenhamos criado uma tabela usando o seguinte:

CREATE TABLE example_table ( name varchar(255), age int)

example_table

Name|Age
--- | --- 

Agora, para adicionar dados a essa tabela, usaremos INSERT, dessa forma:

INSERT INTO example_table (column1,column2) VALUES ("Andrew",23)

example_table

Name|Age
--- | --- 
Andrew|23

Até mesmo a instrução abaixo funcionará. Sempre é bom, no entanto, especificar quais dados estão sendo inseridos em quais colunas.

INSERT INTO table_name VALUES ("John", 28)

example_table

Name|Age
--- | --- 
Andrew|23
John|28

AND é usado em uma instrução WHERE ou em uma instrução GROUP BY/HAVING para limitar as linhas retornadas pela instrução executada. Use AND quando é necessário atender a mais de uma condição.

Usamos a tabela student para apresentar exemplos.

Aqui temos a tabela student sem usar uma instrução WHERE:

select * from student;

Agora, a instrução WHERE é adicionada para que sejam exibidos apenas os alunos de programação:

select * from student 
where programOfStudy = 'Programming';

Em seguida, a instrução WHERE é atualizada com AND para mostrar resultados dos alunos de programação que também tenham uma pontuação no SAT maior que 800:

select * from student 
where programOfStudy = 'Programming' 
and sat_score > 800;

Abaixo temos um exemplo mais complexo da tabela de contribuições de campanha. O exemplo tem uma instrução GROUP BY com uma instrução HAVING usando um AND para restringir o número de registros retornados a candidatos de 2016 com contribuições entre US$3 milhões e US$18 milhões no total.

select Candidate, Office_Sought, Election_Year, FORMAT(sum(Total_$),2) from combined_party_data
where Office_Sought = 'PRESIDENT / VICE PRESIDENT'
group by Candidate, Office_Sought, Election_Year
 having Election_Year = 2016 and sum(Total_$) between 3000000 and 18000000
order by sum(Total_$) desc;

Perguntas de entrevistas comuns sobre SQL

O que é um inner join em SQL?

Esse é o tipo padrão de join se nenhum join for especificado. Ele retorna todas as linhas nas quais haja ao menos uma correspondência nas duas tabelas.

SELECT * FROM A x JOIN B y ON y.aId = x.Id

O que é um left join em SQL?

Um left join retorna todas as linhas da tabela da esquerda e as linhas correspondentes da tabela da direita. As linhas da tabela da esquerda serão retornadas mesmo sem ter uma correspondência na tabela da direita. As linhas da tabela da esquerda sem correspondência na tabela da direita terão null nos valores da tabela da direita.

SELECT * FROM A x LEFT JOIN B y ON y.aId = x.Id

O que é um right join em SQL?

Um right join retorna todas as linhas da tabela da direita e as linhas correspondentes da tabela da esquerda. Ao contrário do left join, ele retornará todas as linhas da tabela da direita mesmo que não haja correspondência na tabela da esquerda. As linhas da tabela da direita que não têm correspondência na tabela da esquerda terão valores null para as colunas da tabela da esquerda.

SELECT * FROM A x RIGHT JOIN B y ON y.aId = x.Id

O que é um full join em SQL?

Um full join retorna todas as linhas para as quais haja uma correspondência em qualquer uma das tabelas. Assim, se houver linhas na tabela da esquerda sem correspondência na tabela da direita, elas serão incluídas. Se houver linhas na tabela da direita que não têm correspondências na tabela da esquerda, elas também serão incluídas.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName

Qual é o resultado do comando seguinte?

  DROP VIEW view_name

Aqui, teremos um erro, pois não podemos realizar uma operação DML em uma view.

Podemos fazer um rollback após usar um comando ALTER?

Não, pois ALTER é um comando DDL e servidores da Oracle realizam um COMMIT automático quando as instruções DDL são executadas.

Qual é a única restrição que impõe as regras em nível de coluna?

NOT NULL é a única restrição que funciona em nível de coluna.

O que são as pseudocolunas em SQL? Pode fornecer alguns exemplos?

Uma pseudocoluna é uma função que retorna um valor gerado pelo sistema. O motivo pelo qual isso é conhecido assim é porque uma pseudocoluna é um valor atribuído pela Oracle usado no mesmo contexto que uma coluna de banco de dados da Oracle, mas não armazenado em disco.

    ROWNUM, ROWID, USER, CURRVAL, NEXTVAL etc.

Crie a função role_tables_and_views

    CREATE ROLE role_tables_and_views

Dê à função da pergunta anterior os privilégios para se conectar ao banco de dados e os privilégios de criar tabelas e views.

O privilégio para se conectar com o banco de dados é CREATE SESSION. O privilégio para criar tabelas é CREATE TABLE. O privilégio para criar views é CREATE VIEW.

    GRANT Create session, create table, create view TO role_tables_and_views

Crie um usuário my723acct com a senha kmd26pt. Use os dados de usuário e as tablespaces de dados temporários fornecidas pelo PO8 e dê a esse usuário 10M de espaço de armazenamento em dados de usuários e 5M de espaço de armazenamento em temporary_data.

    CREATE USER my723acct IDENTIFIED BY kmd26pt
    DEFAULT TABLESPACE user_data
    TEMPORARY TABLESPACE temporary_data
    QUOTA 10M on user_data QUOTA 5M on temporary_data

Dê à função da pergunta anterior aos usuários anny e rita

    GRANT role_tables_and_views TO anny, rita

Escreva um comando que altere a senha do usuário rita de abcd para dfgh

    ALTER USER rita IDENTIFIED BY dfgh

Os usuários rita e anny não têm privilégios de SELECT na tabela INVENTORY, criada por SCOTT. Escreva um comando para permitir que SCOTT dê aos usuários privilégios de SELECT para essas tabelas.

    GRANT select ON inventory TO rita, anny

O usuário rita foi transferido e agora está indo para outra empresa. Como os objetos que ela criou já não estão sendo usados, escreva um comando para remover esse usuário e todos os seus objetos.

Aqui, a opção CASCADE é necessária para remover todos os objetos do usuário no banco de dados.

   DROP USER rita CASCADE

O usuário rita foi transferido e já não precisa do privilégio que foi dado a ela pela função role_tables_and_views. Escreva um comando para removê-la de seus privilégios anteriormente recebidos, exceto pelo fato de ela ainda poder se conectar ao banco de dados.

    REVOKE select ON scott.inventory FROM rita
    REVOKE create table, create view FROM rita

Escreva uma consulta em SQL para encontrar o enésimo maior salário da tabela

   SELECT TOP 1 Salary
   FROM (
      SELECT DISTINCT TOP N Salary
      FROM Employee
      ORDER BY Salary DESC
      )
    ORDER BY Salary ASC

Instrução CREATE VIEW em SQL

O que é uma view?

Uma view é um objeto de banco de dados que apresenta os dados existentes em uma ou mais tabelas. Views são usadas de modo semelhante às tabelas, mas elas não contêm dados. Elas simplesmente "apontam" para os dados que existem em outros lugares (como tabelas ou outras views, por exemplo).

Por que elas são tão legais?

• Views São uma maneira de limitar os dados apresentados. Por exemplo, o departamento de recursos humanos filtrou informações para poder apresentar somente aquelas que não são confidenciais. As informações confidenciais, neste caso, poderiam ser números da previdência, gênero do funcionário, valor do salário, endereço residencial etc.
• Dados complexos em mais de uma tabela podem ser combinados em uma única "view". Isso pode facilitar a vida para analistas de empresas e programadores.

Dicas de segurança importantes

• Views são gerenciadas pelo sistema. Quando os dados nas tabelas relacionadas são alterados, adicionados ou atualizados, a view é atualizada pelo sistema. Queremos usá-las apenas quando necessário para gerenciar o uso dos recursos do sistema.
• Em MySQL, alterações ao design da tabela (ou seja, colunas novas ou excluídas) realizados APÓS a criação de uma view não são atualizados na própria view. A view teria de ser atualizada ou recriada.
• Views são um dos quatro tipos de objeto de bancos de dados padrão. Os outros são tabelas, procedimentos armazenados e funções.
• Views geralmente podem ser tratadas como tabelas, mas as atualizações são limitadas ou não disponíveis quando a view contém mais de uma tabela.
• Há vários outros detalhes sobre as views que estão além do escopo desse guia introdutório. Aproveite seu tempo para ler o manual dos gerenciadores de bancos de dados e divirta-se com esse objeto do SQL poderoso.

Sintaxe da instrução Create View (MySQL)

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
	[WITH [CASCADED | LOCAL] CHECK OPTION]

Este guia tratará da parte da instrução que vemos abaixo…

CREATE
    VIEW view_name [(column_list)]
    AS select_statement

Exemplo de criação de views a partir da tabela student

Observações:

• O nome da view tem um "v" no início. Recomenda-se que o nome da view indique que ela é uma view de alguma maneira para facilitar a vida de programadores e administradores de bancos de dados. Sua TI deve ter as próprias regras para nomear objetos.
• As colunas na view são limitadas pela instrução SELECT e pelas linhas de dados pela instrução WHERE.
• O caractere "`" ao redor dos nomes das views são obrigatórios, por causa do "-" que aparece nos nomes. O MySQL relata um erro se não estiverem lá.

create view `programming-students-v` as
select FullName, programOfStudy 
from student 
where programOfStudy = 'Programming';

select * from `programming-students-v`;

Exemplo de uso de uma view para combinar dados de mais de uma tabela

Uma tabela sobre a demografia dos alunos foi adicionada ao banco de dados para demonstrar esse uso. Essa view combinará as tabelas.

Observações:

• Para "unir" as tabelas por meio de um join, elas precisam ter campos em comum (geralmente, as chaves primárias) que identificam cada linha com exclusividade. Neste caso, falamos da studentID. Se quiser saber mais sobre o assunto, consulte o guia sobre joins em SQL (em inglês).
• Observe o "alias" dado a cada tabela ("s" para student - aluno - e "sc" para student contact - contato do aluno). É uma ferramenta que serve para encurtar os nomes das tabelas e facilitar a identificação de qual tabela está sendo usada. É mais fácil do que digitar longos nomes de tabela repetidamente. Neste exemplo, foi necessário porque o nome de coluna studentID é igual nas duas tabelas. O sistema apresentaria um "erro de nome ambíguo de coluna" se não especificássemos que tabela usar.

Como ocorre com tudo o que está relacionado a SQL, há MUITO MAIS do que é oferecido nesse guia introdutório. Espero que este guia dê a você pelo menos o suficiente para começar. Consulte o manual para seu gerenciador de bancos de dados e divirta-se experimentando com opções diferentes por sua conta.

SQL significa Linguagem de Consulta Estruturada (Structured Query Language em inglês). Os comandos SQL são as instruções usadas para se comunicar com um banco de dados para executar tarefas, funções e consultas com dados.

Os comandos do SQL podem ser usados ​​para pesquisar o banco de dados e realizar outras funções, como criar tabelas, adicionar dados a tabelas, modificar dados e descartar tabelas.

Aqui está uma lista de comandos SQL básicos (às vezes chamados de instruções) que você deve saber se vai trabalhar com SQL.

SELECT e FROM

A parte SELECT (selecionar, em inglês) de uma consulta determina quais colunas dos dados serão exibidas nos resultados. Também há opções que você pode aplicar para mostrar dados que não são uma coluna da tabela.

O exemplo abaixo mostra três colunas selecionadas na tabela "student" (aluno, em inglês), para isso usamos o comando FROM (vindo de, em inglês) e uma coluna calculada. O banco de dados armazena o studentID, FirstName e LastName do aluno (id do aluno, nome e sobrenome em inglês, respectivamente). Podemos combinar as colunas FirstName e LastName para criar a coluna calculada FullName. (nome completo, em inglês)‌.

SELECT studentID, FirstName, LastName, FirstName + ' ' + LastName AS FullName
FROM student;

+-----------+-------------------+------------+------------------------+
| studentID | FirstName         | LastName   | FullName               |
+-----------+-------------------+------------+------------------------+
|         1 | Monique           | Davis      | Monique Davis          |
|         2 | Teri              | Gutierrez  | Teri Gutierrez         |
|         3 | Spencer           | Pautier    | Spencer Pautier        |
|         4 | Louis             | Ramsey     | Louis Ramsey           |
|         5 | Alvin             | Greene     | Alvin Greene           |
|         6 | Sophie            | Freeman    | Sophie Freeman         |
|         7 | Edgar Frank "Ted" | Codd       | Edgar Frank "Ted" Codd |
|         8 | Donald D.         | Chamberlin | Donald D. Chamberlin   |
|         9 | Raymond F.        | Boyce      | Raymond F. Boyce       |
+-----------+-------------------+------------+------------------------+
9 rows in set (0.00 sec)

CREATE TABLE

CREATE TABLE (criar tabela, em inglês) faz exatamente o que parece: cria uma tabela no banco de dados. Você pode especificar o nome da tabela e as colunas que devem estar nela.

CREATE TABLE nome_da_tabela (
    coluna_1 tipo_de_dados,
    coluna_2 tipo_de_dados,
    coluna_3 tipo_de_dados
);

ALTER TABLE

ALTER TABLE (alterar tabela, em inglês) altera a estrutura de uma tabela. Aqui está demonstrado como você adicionaria uma coluna a um banco de dados:

ALTER TABLE nome_da_tabela
ADD coluna tipo_de_dados;

CHECK

A instrução CHECK (verificar, em inglês) é usada para limitar o intervalo de valores que pode ser colocado em uma coluna.

Se você definir uma instrução CHECK em uma única coluna, ela permitirá apenas determinados valores para essa coluna. Se você definir uma instrução CHECK em uma tabela, ela poderá limitar os valores em determinadas colunas com base nos valores de outras colunas dessa linha.

O SQL a seguir cria uma instrução CHECK na coluna "Age" (idade, em inglês) quando a tabela "Persons" (pessoas, em inglês) é criada. CHECK garante que você não tenha pessoas com menos de 18 anos.‌

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
);

Para permitir a nomeação de uma instrução CHECK e para definir uma instrução CHECK em várias colunas, use a seguinte sintaxe no SQL:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255),
    CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);

‌WHERE

(AND ,OR , IN, BETWEEN e LIKE)

A instrução WHERE (onde, em inglês) é usada para limitar o número de linhas retornadas.

Como exemplo, primeiro mostraremos uma instrução SELECT e resultados sem declaração WHERE. Em seguida, adicionaremos uma instrução WHERE que usa todos os cinco qualificadores acima.‌  

SELECT studentID, FullName, sat_score, rcd_updated FROM student;

+-----------+------------------------+-----------+---------------------+
| studentID | FullName               | sat_score | rcd_updated         |
+-----------+------------------------+-----------+---------------------+
|         1 | Monique Davis          |       400 | 2017-08-16 15:34:50 |
|         2 | Teri Gutierrez         |       800 | 2017-08-16 15:34:50 |
|         3 | Spencer Pautier        |      1000 | 2017-08-16 15:34:50 |
|         4 | Louis Ramsey           |      1200 | 2017-08-16 15:34:50 |
|         5 | Alvin Greene           |      1200 | 2017-08-16 15:34:50 |
|         6 | Sophie Freeman         |      1200 | 2017-08-16 15:34:50 |
|         7 | Edgar Frank "Ted" Codd |      2400 | 2017-08-16 15:35:33 |
|         8 | Donald D. Chamberlin   |      2400 | 2017-08-16 15:35:33 |
|         9 | Raymond F. Boyce       |      2400 | 2017-08-16 15:35:33 |
+-----------+------------------------+-----------+---------------------+
9 rows in set (0.00 sec)

Agora, repetiremos a consulta SELECT, mas limitaremos as linhas retornadas usando uma instrução WHERE.

STUDENT studentID, FullName, sat_score, recordUpdated
FROM student
WHERE (studentID BETWEEN 1 AND 5 OR studentID = 8)
        AND
        sat_score NOT IN (1000, 1400);

+-----------+----------------------+-----------+---------------------+
| studentID | FullName             | sat_score | rcd_updated         |
+-----------+----------------------+-----------+---------------------+
|         1 | Monique Davis        |       400 | 2017-08-16 15:34:50 |
|         2 | Teri Gutierrez       |       800 | 2017-08-16 15:34:50 |
|         4 | Louis Ramsey         |      1200 | 2017-08-16 15:34:50 |
|         5 | Alvin Greene         |      1200 | 2017-08-16 15:34:50 |
|         8 | Donald D. Chamberlin |      2400 | 2017-08-16 15:35:33 |
+-----------+----------------------+-----------+---------------------+
5 rows in set (0.00 sec)

UPDATE

Para atualizar um registro em uma tabela, você usa a instrução UPDATE (atualizar, em inglês).

Use a instrução WHERE para especificar quais registros você deseja atualizar. É possível atualizar uma ou mais colunas por vez. A sintaxe é:‌

UPDATE nome_da_tabela
SET coluna1 = valor1, 
    coluna2 = valor2, ...
WHERE condição;

Aqui está um exemplo atualizando Name (nome, em inglês) do registro com Id 4:‌  

UPDATE Person
SET Name = "Elton John"
WHERE Id = 4;

Você também pode atualizar colunas em uma tabela usando valores de outras tabelas. Use a instrução JOIN (unir, em inglês) para obter dados de várias tabelas. A sintaxe é:

UPDATE nome_da_tabela1
SET nome_da_tabela1.coluna1 = nome_da_tabela2.colunaA
    nome_da_tabela1.coluna2 = nome_da_tabela2.colunaB
FROM nome_da_tabela1
JOIN nome_da_tabela2 ON nome_da_tabela1.ChaveEstrangeira = nome_da_tabela2.Chave

Aqui está um exemplo de atualização de Manager (gerenciador, em inglês) de todos os registros:‌      

UPDATE Person
SET Person.Manager = Department.Manager
FROM Person
JOIN Department ON Person.DepartmentID = Department.ID

GROUP BY

GROUP BY (agrupar por, em inglês) permite combinar linhas e agregar dados.

Aqui está a sintaxe de GROUP BY:‌    

SELECT nome_da_coluna, COUNT(*)
FROM nome_da_tabela
GROUP BY nome_da_coluna;

HAVING

HAVING (contendo, em inglês) permite filtrar os dados agregados pela instrução GROUP BY para que o usuário obtenha um conjunto limitado de registros para visualização.

Aqui está a sintaxe de HAVING:‌  

SELECT nome_da_coluna, COUNT(*)
FROM nome_da_tabela
GROUP BY nome_da_coluna
HAVING COUNT(*) > valor;

AVG()

"Average" (média, em inglês) é usado para calcular a média de uma coluna numérica do conjunto de linhas retornado por uma instrução SQL.

Aqui está a sintaxe para usar a função:‌

SELECT campoAgrupamento, AVG(num_campo)
FROM tabela1
GROUP BY campoAgrupamento

Aqui está um exemplo usando a tabela de alunos:‌  

SELECT studentID, FullName, AVG(sat_score) 
FROM student 
GROUP BY studentID, FullName;

AS

AS (como, em inglês - similar a like, que vemos posteriormente) permite renomear uma coluna ou tabela usando um alias.‌  

SELECT user_only_num1 AS AgeOfServer, (user_only_num1 - warranty_period) AS NonWarrantyPeriod FROM server_table

Isso resulta na saída abaixo.‌

+-------------+------------------------+
| AgeOfServer | NonWarrantyPeriod      | 
+-------------+------------------------+
|         36  |                     24 |
|         24  |                     12 | 
|         61  |                     49 |
|         12  |                      0 | 
|          6  |                     -6 |
|          0  |                    -12 | 
|         36  |                     24 |
|         36  |                     24 | 
|         24  |                     12 | 
+-------------+------------------------+

Você também pode usar AS para atribuir um nome a uma tabela para facilitar a referência em junções.‌  

SELECT ord.product, ord.ord_number, ord.price, cust.cust_name, cust.cust_number FROM customer_table AS cust

JOIN order_table AS ord ON cust.cust_number = ord.cust_number

Isso resulta na saída como abaixo.

+-------------+------------+-----------+-----------------+--------------+
| product     | ord_number | price     | cust_name       | cust_number  |
+-------------+------------+-----------+-----------------+--------------+
|     RAM     |   12345    |       124 | John Smith      |  20          |
|     CPU     |   12346    |       212 | Mia X           |  22          |
|     USB     |   12347    |        49 | Elise Beth      |  21          |
|     Cable   |   12348    |         0 | Paul Fort       |  19          |
|     Mouse   |   12349    |        66 | Nats Back       |  15          |
|     Laptop  |   12350    |       612 | Mel S           |  36          |
|     Keyboard|   12351    |        24 | George Z        |  95          |
|     Keyboard|   12352    |        24 | Ally B          |  55          |
|     Air     |   12353    |        12 | Maria Trust     |  11          |
+-------------+------------+-----------+-----------------+--------------+

ORDER BY

ORDER BY (ordenar por, em inglês) nos dá uma maneira de classificar o conjunto de resultados por um ou mais itens na seção SELECT. Aqui está um SQL classificando os alunos por FullName em ordem decrescente. A ordem de classificação padrão é crescente (ASC), mas, para classificar na ordem oposta (decrescente), você usa DESC.‌  

SELECT studentID, FullName, sat_score
FROM student
ORDER BY FullName DESC;

COUNT

COUNT (contar, em inglês) contará o número de linhas e retornará essa contagem como uma coluna no conjunto de resultados.

Aqui estão alguns exemplos onde você usaria COUNT:

• Para contar todas as linhas em uma tabela (GROUP BY não é obrigatório)
• Para contar os totais de subconjuntos de dados (requer um GROUP BY na instrução)

Essa instrução SQL fornece uma contagem de todas as linhas. Observe que você pode dar um nome à coluna COUNT resultante usando "AS".‌  

SELECT count(*) AS studentCount FROM student; 

DELETE

DELETE (excluir, em inglês) é usado para excluir um registro em uma tabela.

Tome cuidado. Você pode excluir todos os registros da tabela ou apenas alguns deles. Use a condição WHERE para especificar quais registros você deseja excluir. A sintaxe é:‌

DELETE FROM table_name
WHERE condition;

Aqui está um exemplo de exclusão do registro com Id 3 na tabela Person:‌

DELETE FROM Person
WHERE Id = 3;

INNER JOIN

JOIN (junção, em inglês), também chamado de inner join (junção interna, em inglês), seleciona registros que têm valores correspondentes em duas tabelas.‌  

SELECT * FROM A x JOIN B y ON y.aId = x.Id

LEFT JOIN

A LEFT JOIN (junção à esquerda, em inglês) retorna todas as linhas da tabela da esquerda e as linhas correspondentes da tabela da direita. As linhas na tabela da esquerda serão retornadas mesmo que não haja correspondência na tabela da direita. As linhas da tabela à esquerda sem correspondência na tabela à direita terão os valores da tabela à direita null.‌

SELECT * FROM A x LEFT JOIN B y ON y.aId = x.Id

‌RIGHT JOIN

A RIGHT JOIN (junção à direita, em inglês) retorna todas as linhas da tabela da direita e as linhas correspondentes da tabela da esquerda. Ao contrário de uma junção à esquerda, isso retornará todas as linhas da tabela à direita, mesmo quando não houver correspondência na tabela à esquerda. As linhas na tabela à direita que não têm correspondência na tabela à esquerda terão valores null para as colunas da tabela à esquerda.‌

SELECT * FROM A x RIGHT JOIN B y ON y.aId = x.Id

FULL OUTER JOIN

A FULL OUTER JOIN (junção externa completa, em inglês) retorna todas as linhas para as quais haja uma correspondência em qualquer uma das tabelas. Portanto, se houver linhas na tabela da esquerda que não tenham correspondência na tabela da direita, elas serão incluídas. Além disso, se houver linhas na tabela à direita que não tenham correspondência na tabela à esquerda, elas também o serão.‌

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName

INSERT

INSERT (inserir, em inglês) é uma maneira de inserir dados em uma tabela.‌

INSERT INTO nome_da_tabela (coluna_1, coluna_2, coluna_3) 
VALUES (valor_1, 'valor_2', valor_3);

LIKE

LIKE  (como, em inglês - comparar com "as" acima) é usado em uma instrução com WHERE ou HAVING(como parte de GROUP BY) para limitar as linhas selecionadas aos itens quando uma coluna possui um determinado padrão de caracteres contido nela.

Este SQL selecionará os alunos que tem FullName começando com "Monique" ou que terminam com "Greene".‌    

SELECT studentID, FullName, sat_score, rcd_updated
FROM student 
WHERE 
    FullName LIKE 'Monique%' OR 
    FullName LIKE '%Greene'; 

+-----------+---------------+-----------+---------------------+
| studentID | FullName      | sat_score | rcd_updated         |
+-----------+---------------+-----------+---------------------+
|         1 | Monique Davis |       400 | 2017-08-16 15:34:50 |
|         5 | Alvin Greene  |      1200 | 2017-08-16 15:34:50 |
+-----------+---------------+-----------+---------------------+
2 rows in set (0.00 sec)

Você pode colocar NOT antes de LIKE para excluir as linhas com o padrão de string em vez de selecioná-las. Esse SQL exclui registros que contenham "encer Pauti" e "Ted" na coluna FullName.‌    

SELECT studentID, FullName, sat_score, rcd_updated
FROM student 
WHERE FullName NOT LIKE '%encer Pauti%' AND FullName NOT LIKE '%"Ted"%';

+-----------+----------------------+-----------+---------------------+
| studentID | FullName             | sat_score | rcd_updated         |
+-----------+----------------------+-----------+---------------------+
|         1 | Monique Davis        |       400 | 2017-08-16 15:34:50 |
|         2 | Teri Gutierrez       |       800 | 2017-08-16 15:34:50 |
|         4 | Louis Ramsey         |      1200 | 2017-08-16 15:34:50 |
|         5 | Alvin Greene         |      1200 | 2017-08-16 15:34:50 |
|         6 | Sophie Freeman       |      1200 | 2017-08-16 15:34:50 |
|         8 | Donald D. Chamberlin |      2400 | 2017-08-16 15:35:33 |
|         9 | Raymond F. Boyce     |      2400 | 2017-08-16 15:35:33 |
+-----------+----------------------+-----------+---------------------+
7 rows in set (0.00 sec)

Python e SQL são duas das linguagens mais importantes para analistas de dados.

Neste artigo, mostrarei tudo que você precisa saber para conectar o Python e o SQL.

Você aprenderá a obter dados de bancos de dados relacionais diretamente de seus pipelines de aprendizado de máquina, armazenar dados de suas aplicações em Python em um banco de dados próprio ou qualquer outro caso de uso que possa surgir.

Juntos, vamos aprender:

• Por que aprender a usar Python e SQL juntos?
• Como configurar seu ambiente Python e MySQL Server
• Conectar ao MySQL Server usando Python
• Criar um novo banco de dados
• Criar tabelas e relacionamentos entre elas
• Preencher as tabelas com dados
• Ler dados
• Atualizar registros
• Apagar registros
• Criar registros a partir de listas do Python
• Criar funções reutilizáveis para fazer tudo isso por nós no futuro

São muitas coisas úteis e interessantes. Vamos começar!

Um breve comentário antes de começarmos: um Jupyter Notebook com todos os códigos usados neste tutorial está disponível neste repositório do GitHub. Reproduzir os códigos enquanto lê este artigo é altamente recomendado!

O banco de dados e os códigos SQL usados aqui são todos provenientes da minha série Introduction to SQL (Introdução ao SQL) publicada (em inglês) no site Towards Data Science (entre em contato se tiver algum problema para visualizar os artigos e eu enviarei um link para que eles possam ser acessados gratuitamente).

Se não está acostumado com SQL e com os conceitos subjacentes aos bancos de dados relacionais, eu indicaria essa série (além disso, é claro que há uma enorme quantidade de artigos excelentes disponíveis aqui no freeCodeCamp!)

Por que Python com SQL?

Para analistas de dados e cientistas de dados, o Python apresenta muitas vantagens. Uma enorme variedade de bibliotecas de código aberto o torna uma ferramenta incrivelmente útil para qualquer analista de dados.

Nós temos o pandas, o NumPy e o Vaex para análise de dados, o Matplotlib, o seaborn e o Bokeh para visualização, além do TensorFlow, do scikit-learn e do PyTorch para aprendizagem de máquina (além de muitas, muitas outras).

Com sua curva de aprendizagem (relativamente) fácil e versatilidade, não é de admirar que o Python seja uma das linguagens de programação que mais crescem.

Portanto, se optar por usar Python para análise de dados, vale a pena perguntar: de onde vêm todos esses dados?

Embora haja uma enorme variedade de fontes para conjuntos de dados, em muitos casos – particularmente, em empresas - os dados são armazenados em um banco de dados relacional. Os bancos de dados relacionais são uma maneira extremamente eficiente, poderosa e amplamente utilizada para criar, ler, atualizar e excluir dados de todos os tipos.

Os sistemas de gerenciamento de bancos de dados relacionais (SGBDR) mais utilizados - Oracle, MySQL, Microsoft SQL Server, PostgreSQL, IBM DB2 - usam a Linguagem de Consulta Estruturada (SQL, da sigla em inglês, Structured Query Language) para acessar e modificar os dados.

Observe que cada SGBDR usa um tipo um pouco diferente de SQL. Portanto, o código SQL escrito para um geralmente não funcionará com outro sem modificações (normalmente pequenas). No entanto, os conceitos, estruturas e operações são, em grande parte, idênticos.

Isso quer dizer que, para um analista de dados, uma sólida compreensão de SQL é de grande importância. Saber como usar Python e SQL juntos dará a você uma vantagem ainda maior na hora de trabalhar com os seus dados.

O restante deste artigo será dedicado a mostrar exatamente como podemos fazer isso.

Para começar

Requisitos e instalação

Para programar junto com este tutorial, você precisará do seu próprio ambiente Python configurado.

Eu uso a distribuição Anaconda, mas há muitas maneiras de fazer isso. Basta pesquisar no Google "como instalar o Python" se precisar de ajuda. Você também pode usar o Binder para programar usando um Jupyter Notebook associado.

Nós vamos usar o MySQL Community Server, que é gratuito e amplamente utilizado na indústria. Se estiver utilizando o Windows, este guia (em inglês) o ajudará na hora de configurar. Aqui estão os guias para os usuários de Mac e Linux  (embora as instruções possam variar de acordo com a distribuição Linux utilizada).

Depois de configurados, precisaremos fazer com que eles se comuniquem.

Para isso, precisaremos instalar a biblioteca MySQL Connector para o Python. Faça isso seguindo estas instruções, ou simplesmente use o pip:

pip install mysql-connector-python

Também utilizaremos a biblioteca pandas. Certifique-se de que ela também esteja instalada.

pip install pandas

Importando as bibliotecas

Como em todo projeto em Python, a primeira coisa que devemos fazer é importar nossas bibliotecas.

É uma boa prática importar todas as bibliotecas que vamos usar no início do projeto. Assim, as pessoas que lerão ou revisarão nosso código saberão, até certo ponto, o que está por vir, sem surpresas.

Para este tutorial, utilizaremos apenas duas bibliotecas - o MySQL Connector e o pandas.

import mysql.connector
from mysql.connector import Error
import pandas as pd

Importamos a função Error separadamente para que tenhamos acesso fácil a ela em nossas funções.

Conectando ao MySQL Server

A essa altura, já devemos ter o MySQL Community Server configurado em nosso sistema. Agora, precisamos escrever um código em Python que nos permita estabelecer uma conexão com este servidor.

def create_server_connection(host_name, user_name, user_password):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password
        )
        print("MySQL Database connection successful")
    except Error as err:
        print(f"Error: '{err}'")

    return connection

É uma boa prática a criação de uma função para tornar reutilizável um código como esse, permitindo que ele seja utilizado repetidas vezes com o mínimo de esforço. Uma vez criado, você poderá reutilizá-lo em todos os seus projetos futuros e o seu “eu do futuro” será grato!

Vamos revisar o código, linha por linha, para entendermos o que está acontecendo aqui:

Na primeira linha, damos um nome à função (create_server_connection) e aos seus argumentos (host_name, user_name e user_password).

Na linha seguinte, encerramos quaisquer conexões existentes para que o servidor não fique confuso com várias conexões abertas.

Em seguida, usamos um  bloco try-except (texto em inglês) do Python para lidar com possíveis erros. A primeira parte tenta criar uma conexão com o servidor usando o método mysql.connector.connect() (texto em inglês) e os detalhes especificados pelo usuário nos argumentos da função. Se isso funcionar, a função imprime uma pequena mensagem de sucesso.

O código referente ao bloco except imprime o erro que o MySQL Server retorna se, infelizmente, houver um erro.

Por fim, se a conexão for bem-sucedida, a função retornará um objeto de conexão (texto em inglês).

Na prática, atribuímos o resultado dessa função a uma variável, que então se torna o nosso objeto de conexão. Podemos, depois disso, aplicar outros métodos, como o cursor (texto em inglês), a ele e criar outros objetos úteis.

connection = create_server_connection("localhost", "root", pw)

O código abaixo deve resultar em uma mensagem de sucesso:

Criando um novo banco de dados

Agora que estabelecemos uma conexão, nosso próximo passo é criar um banco de dados em nosso servidor.

Neste tutorial, faremos isso uma única vez, mas, novamente, criaremos uma função reutilizável de modo que tenhamos um código que possamos reutilizar em projetos futuros.

def create_database(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        print("Database created successfully")
    except Error as err:
        print(f"Error: '{err}'")

Essa função recebe dois argumentos, connection (nosso objeto de conexão) e query (um código SQL que escreveremos na próxima etapa). Ela executa a consulta no servidor através da conexão.

Usamos o método cursor do nosso objeto de conexão para criar um objeto do tipo cursor (o MySQL Connector usa o paradigma de programação orientado a objetos, portanto, há muitos objetos herdando propriedades de objetos pai).

Este objeto cursor possui métodos como execute, executemany (que usaremos neste tutorial - textos em inglês) assim como vários outros métodos úteis.

Se ajudar, podemos pensar que o objeto cursor dá acesso ao cursor que fica piscando em um terminal do MySQL Server.

Em seguida, definimos uma consulta para criar o banco de dados e executar a função:

Todos os códigos SQL deste tutorial estão explicados na minha série de tutoriais Introduction to SQL (Introdução ao SQL, em inglês), e o código completo está disponível em um Jupyter Notebook neste repositório do GitHub. Portanto, não explicarei o que o código SQL faz neste tutorial.

No entanto, esse é talvez o código SQL mais simples possível. Se você pode ler em inglês, provavelmente pode descobrir o que ele faz!

Executar a função create_database com os argumentos acima resulta na criação de um banco de dados chamado 'school' em nosso servidor.

Por que nosso banco de dados é chamado de 'school' (escola)? Talvez agora seja um bom momento para examinarmos em mais detalhes o que vamos implementar exatamente neste tutorial.

Nosso banco de dados

Seguindo o exemplo da minha série anterior, implementaremos o banco de dados para a International Language School (Escola Internacional de Idiomas) - uma escola fictícia de treinamento de idiomas que oferece aulas de idiomas profissionais para clientes corporativos.

O Diagrama Entidade Relacionamento (DER) (texto em inglês) apresenta as entidades (Teacher, Client, Course e Participant) e define as relações entre elas.

Todas as informações sobre o que é um DER e o que considerar ao criar um e projetar um banco de dados podem ser encontradas neste artigo (em inglês).

O código SQL, os requisitos do banco de dados e os dados para inclusão no banco de dados estão todos disponíveis neste repositório do GitHub, mas você também verá tudo à medida que avançarmos neste tutorial.

Conectando ao banco de dados

Agora que criamos um banco de dados no MySQL Server, podemos modificar nossa função create_server_connection para conectar diretamente a esse banco de dados.

Observe que é possível - comum, na verdade - ter vários bancos de dados em um servidor MySQL, então queremos nos conectar sempre e automaticamente ao banco de dados em que estamos interessados.

Podemos fazer da seguinte maneira:

def create_db_connection(host_name, user_name, user_password, db_name):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password,
            database=db_name
        )
        print("MySQL Database connection successful")
    except Error as err:
        print(f"Error: '{err}'")

    return connection

Essa é exatamente a mesma função, mas agora temos mais um argumento - o nome do banco de dados - que é passado para o método connect().

Criando uma função para execução de consultas

A última função que criaremos (por enquanto) é extremamente vital - uma função de execução de consulta. Ela pegará nossas consultas SQL, armazenadas como strings do Python, e as passará para que o método cursor.execute() as execute no servidor.

def execute_query(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        print("Query successful")
    except Error as err:
        print(f"Error: '{err}'")

Essa função é idêntica à nossa função create_database, exceto por usar o método connection.commit() para garantir que os comandos detalhados em nossas consultas SQL sejam implementados.

Essa função será nosso carro-chefe, que usaremos (junto com create_db_connection) para criar tabelas, estabelecer relacionamentos entre essas tabelas, preencher as tabelas com dados e atualizar e excluir registros em nosso banco de dados.

Se você for um especialista em SQL, essa função permitirá que você execute todos e quaisquer comandos e consultas complexas que você possa ter, diretamente de um script em Python. Ela pode ser uma ferramenta muito poderosa para gerenciar seus dados.

Criando tabelas

Agora estamos prontos para começar a executar comandos SQL em nosso servidor e começar a construir nosso banco de dados. A primeira coisa que queremos fazer é criar as tabelas necessárias.

Vamos começar com a tabela Teacher (professor, em inglês):

create_teacher_table = """
CREATE TABLE teacher (
  teacher_id INT PRIMARY KEY,
  first_name VARCHAR(40) NOT NULL,
  last_name VARCHAR(40) NOT NULL,
  language_1 VARCHAR(3) NOT NULL,
  language_2 VARCHAR(3),
  dob DATE,
  tax_id INT UNIQUE,
  phone_no VARCHAR(20)
  );
 """

connection = create_db_connection("localhost", "root", pw, db) # Connect to the Database
execute_query(connection, create_teacher_table) # Execute our defined query

Primeiro, atribuímos nosso comando SQL (explicado em detalhes aqui) a uma variável com um nome apropriado.

Neste caso, usamos a notação de aspas triplas do Python para definir strings que se estendem por múltiplas linhas (texto em inglês) para armazenar nossa consulta SQL. Então, nós a passamos para a função execute_query que a executará.

Observe que essa formatação de várias linhas é puramente para facilitar a leitura do código por humanos. Nem SQL nem Python 'se importam' se o comando SQL estiver distribuído dessa maneira. Desde que a sintaxe esteja correta, ambas as linguagens a aceitarão.

Para o bem dos humanos que lerão seu código (mesmo que seja apenas o você do futuro!), é muito útil empregar essa formatação para tornar o código mais legível e compreensível.

O mesmo vale para o uso de expressões do SQL em LETRAS MAIÚSCULAS. Esta é uma convenção amplamente usada e que é fortemente recomendada, mas o software real que executa o código não diferencia maiúsculas de minúsculas e tratará 'CREATE TABLE teacher' e 'create table teacher' como comandos idênticos.

A execução deste código retorna nossas mensagens de sucesso. Também podemos verificar isso no client na linha de comando do MySQL Server:

Excelente! Agora vamos criar as tabelas restantes.

create_client_table = """
CREATE TABLE client (
  client_id INT PRIMARY KEY,
  client_name VARCHAR(40) NOT NULL,
  address VARCHAR(60) NOT NULL,
  industry VARCHAR(20)
);
 """

create_participant_table = """
CREATE TABLE participant (
  participant_id INT PRIMARY KEY,
  first_name VARCHAR(40) NOT NULL,
  last_name VARCHAR(40) NOT NULL,
  phone_no VARCHAR(20),
  client INT
);
"""

create_course_table = """
CREATE TABLE course (
  course_id INT PRIMARY KEY,
  course_name VARCHAR(40) NOT NULL,
  language VARCHAR(3) NOT NULL,
  level VARCHAR(2),
  course_length_weeks INT,
  start_date DATE,
  in_school BOOLEAN,
  teacher INT,
  client INT
);
"""


connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, create_client_table)
execute_query(connection, create_participant_table)
execute_query(connection, create_course_table)

Esses comandos criarão as quatro tabelas necessárias para nossas quatro entidades.

Agora, vamos definir os relacionamentos entre elas e criar mais uma tabela para lidar com o relacionamento muitos-para-muitos entre as tabelas participant e course (participante e curso, em inglês, respectivamente). Veja mais detalhes aqui (texto em inglês).

Fazemos exatamente da mesma forma:

alter_participant = """
ALTER TABLE participant
ADD FOREIGN KEY(client)
REFERENCES client(client_id)
ON DELETE SET NULL;
"""

alter_course = """
ALTER TABLE course
ADD FOREIGN KEY(teacher)
REFERENCES teacher(teacher_id)
ON DELETE SET NULL;
"""

alter_course_again = """
ALTER TABLE course
ADD FOREIGN KEY(client)
REFERENCES client(client_id)
ON DELETE SET NULL;
"""

create_takescourse_table = """
CREATE TABLE takes_course (
  participant_id INT,
  course_id INT,
  PRIMARY KEY(participant_id, course_id),
  FOREIGN KEY(participant_id) REFERENCES participant(participant_id) ON DELETE CASCADE,
  FOREIGN KEY(course_id) REFERENCES course(course_id) ON DELETE CASCADE
);
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, alter_participant)
execute_query(connection, alter_course)
execute_query(connection, alter_course_again)
execute_query(connection, create_takescourse_table)

Agora, nossas tabelas foram criadas, juntamente com as restrições apropriadas e as relações entre chaves primárias e chaves estrangeiras.

Preenchendo as tabelas

A próxima etapa é adicionar alguns registros às tabelas. Novamente, usaremos a função execute_query para enviar nossos comandos SQL existentes ao servidor. Vamos começar mais uma vez com a tabela Teacher.

pop_teacher = """
INSERT INTO teacher VALUES
(1,  'James', 'Smith', 'ENG', NULL, '1985-04-20', 12345, '+491774553676'),
(2, 'Stefanie',  'Martin',  'FRA', NULL,  '1970-02-17', 23456, '+491234567890'), 
(3, 'Steve', 'Wang',  'MAN', 'ENG', '1990-11-12', 34567, '+447840921333'),
(4, 'Friederike',  'Müller-Rossi', 'DEU', 'ITA', '1987-07-07',  45678, '+492345678901'),
(5, 'Isobel', 'Ivanova', 'RUS', 'ENG', '1963-05-30',  56789, '+491772635467'),
(6, 'Niamh', 'Murphy', 'ENG', 'IRI', '1995-09-08',  67890, '+491231231232');
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, pop_teacher)

Funcionou? Podemos verificar no cliente de linha de comando do MySQL:

Agora, vamos preencher as tabelas restantes.

pop_client = """
INSERT INTO client VALUES
(101, 'Big Business Federation', '123 Falschungstraße, 10999 Berlin', 'NGO'),
(102, 'eCommerce GmbH', '27 Ersatz Allee, 10317 Berlin', 'Retail'),
(103, 'AutoMaker AG',  '20 Künstlichstraße, 10023 Berlin', 'Auto'),
(104, 'Banko Bank',  '12 Betrugstraße, 12345 Berlin', 'Banking'),
(105, 'WeMoveIt GmbH', '138 Arglistweg, 10065 Berlin', 'Logistics');
"""

pop_participant = """
INSERT INTO participant VALUES
(101, 'Marina', 'Berg','491635558182', 101),
(102, 'Andrea', 'Duerr', '49159555740', 101),
(103, 'Philipp', 'Probst',  '49155555692', 102),
(104, 'René',  'Brandt',  '4916355546',  102),
(105, 'Susanne', 'Shuster', '49155555779', 102),
(106, 'Christian', 'Schreiner', '49162555375', 101),
(107, 'Harry', 'Kim', '49177555633', 101),
(108, 'Jan', 'Nowak', '49151555824', 101),
(109, 'Pablo', 'Garcia',  '49162555176', 101),
(110, 'Melanie', 'Dreschler', '49151555527', 103),
(111, 'Dieter', 'Durr',  '49178555311', 103),
(112, 'Max', 'Mustermann', '49152555195', 104),
(113, 'Maxine', 'Mustermann', '49177555355', 104),
(114, 'Heiko', 'Fleischer', '49155555581', 105);
"""

pop_course = """
INSERT INTO course VALUES
(12, 'English for Logistics', 'ENG', 'A1', 10, '2020-02-01', TRUE,  1, 105),
(13, 'Beginner English', 'ENG', 'A2', 40, '2019-11-12',  FALSE, 6, 101),
(14, 'Intermediate English', 'ENG', 'B2', 40, '2019-11-12', FALSE, 6, 101),
(15, 'Advanced English', 'ENG', 'C1', 40, '2019-11-12', FALSE, 6, 101),
(16, 'Mandarin für Autoindustrie', 'MAN', 'B1', 15, '2020-01-15', TRUE, 3, 103),
(17, 'Français intermédiaire', 'FRA', 'B1',  18, '2020-04-03', FALSE, 2, 101),
(18, 'Deutsch für Anfänger', 'DEU', 'A2', 8, '2020-02-14', TRUE, 4, 102),
(19, 'Intermediate English', 'ENG', 'B2', 10, '2020-03-29', FALSE, 1, 104),
(20, 'Fortgeschrittenes Russisch', 'RUS', 'C1',  4, '2020-04-08',  FALSE, 5, 103);
"""

pop_takescourse = """
INSERT INTO takes_course VALUES
(101, 15),
(101, 17),
(102, 17),
(103, 18),
(104, 18),
(105, 18),
(106, 13),
(107, 13),
(108, 13),
(109, 14),
(109, 15),
(110, 16),
(110, 20),
(111, 16),
(114, 12),
(112, 19),
(113, 19);
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, pop_client)
execute_query(connection, pop_participant)
execute_query(connection, pop_course)
execute_query(connection, pop_takescourse)

Surpreendente! Acabamos de criar um banco de dados completo com relações, restrições e registros no MySQL, usando apenas comandos em Python.

Fizemos isso passo a passo para que o processo fosse compreensível. Mas a esta altura você já deve ter percebido que todos esses comandos podem facilmente ser incluídos em um script em Python e executados em um único comando no terminal.

Lendo os dados

Agora, temos um banco de dados funcional com o qual podemos trabalhar. Como analista de dados, é provável que você entre em contato com bancos de dados existentes nas organizações em que trabalha. Será muito útil saber como extrair dados desses bancos de dados para que possam ser alimentados em seu pipeline de dados em Python. É nisso que vamos trabalhar a seguir.

Para isso, precisaremos de mais uma função, desta vez, usando cursor.fetchall() em vez de cursor.commit() (textos das duas funções em inglês). Com esta função, leremos dados do banco de dados sem fazer nenhuma alteração.

def read_query(connection, query):
    cursor = connection.cursor()
    result = None
    try:
        cursor.execute(query)
        result = cursor.fetchall()
        return result
    except Error as err:
        print(f"Error: '{err}'")

Novamente, vamos implementar isso de uma maneira muito semelhante ao execute_query. Vamos testar com uma consulta simples para ver como funciona.

q1 = """
SELECT *
FROM teacher;
"""

connection = create_db_connection("localhost", "root", pw, db)
results = read_query(connection, q1)

for result in results:
  print(result)

Exatamente o que estávamos esperando. A função também funciona com consultas mais complexas, como esta envolvendo um JOIN entre as tabelas de course e client (curso e cliente, em inglês, respectivamente).

q5 = """
SELECT course.course_id, course.course_name, course.language, client.client_name, client.address
FROM course
JOIN client
ON course.client = client.client_id
WHERE course.in_school = FALSE;
"""

connection = create_db_connection("localhost", "root", pw, db)
results = read_query(connection, q5)

for result in results:
  print(result)

Muito bom.

Para nossos pipelines de dados e fluxos de trabalho em Python, podemos querer obter esses resultados em formatos diferentes para torná-los mais úteis ou prontos para manipulação.

Vamos ver alguns exemplos para entender como podemos fazer isso.

Formatando os resultados em uma lista

#Inicializa uma lista vazia 
from_db = []

# Percorrer os resultados e inseri-los à lista

# Retorna uma lista de tuplas
for result in results:
  result = result
  from_db.append(result)

Formatando o resultado em uma lista de listas

# Retorna uma lista de listas
from_db = []

for result in results:
  result = list(result)
  from_db.append(result)

Formatando o resultado em um DataFrame do Pandas

Para os analistas de dados usando Python, o pandas (texto em inglês) é o nosso velho amigo, belo e confiável. É muito simples converter a saída do nosso banco de dados em um DataFrame. A partir daí, as possibilidades são infinitas!

# Retorna uma lista de listas e cria um DataFrame do Pandas
from_db = []

for result in results:
  result = list(result)
  from_db.append(result)


columns = ["course_id", "course_name", "language", "client_name", "address"]
df = pd.DataFrame(from_db, columns=columns)

Espero que possa ver as possibilidades se desdobrando diante de você. Com apenas algumas linhas de código, podemos extrair facilmente todos os dados que podemos manipular dos bancos de dados relacionais em que eles residem e trazê-los para nossos pipelines de análise de dados de última geração. Isso é algo realmente útil.

Atualizando registros

Quando mantemos um banco de dados, às vezes precisaremos fazer alterações nos registros existentes. Nesta seção, veremos como fazer isso.

Digamos que a ILS seja notificada de que um de seus clientes existentes, a Big Business Federation, está mudando de escritório para 23 Fingiertweg, 14534 Berlin. Nesse caso, o administrador do banco de dados (nós!) precisará fazer algumas alterações.

Felizmente, podemos fazer isso com nossa função execute_query junto com a instrução UPDATE (texto em inglês) do SQL.

update = """
UPDATE client 
SET address = '23 Fingiertweg, 14534 Berlin' 
WHERE client_id = 101;
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, update)

Note que a cláusula WHERE é muito importante. Se executarmos esse comando sem a cláusula WHERE, todos os endereços de todos os registros em nossa tabela Client serão atualizados para 23 Fingiertweg. Não é exatamente isso que queremos fazer.

Observe também que usamos "WHERE client_id = 101" na consulta UPDATE. Também seria possível usar "WHERE client_name = 'Big Business Federation'" ou "WHERE address = '123 Falschungstraße, 10999 Berlin'" ou mesmo "WHERE address LIKE '%Falschung%'".

O importante é que a cláusula WHERE nos permite identificar exclusivamente o registro (ou registros) que queremos atualizar.

Apagando registros

Também é possível usar nossa função execute_query para excluir registros, usando DELETE (texto em inglês).

Ao usar SQL com bancos de dados relacionais, precisamos ter cuidado ao usar o operador DELETE. Este não é o Windows. Não há um alerta dizendo "Tem certeza de que deseja excluir isso?" em uma janela de pop-up e não há uma lixeira para a reciclagem. Uma vez que excluímos algo, esse algo realmente se foi.

Dito isso, nós às vezes realmente precisamos apagar coisas. Então, vamos ver isso na prática, apagando um curso da nossa tabela Course.

Antes de mais nada, vamos nos lembrar dos cursos que temos.

Digamos que o curso 20, 'Fortgeschrittenes Russisch' (que é 'Russo avançado' para você e para mim), está chegando ao fim. Então, precisamos removê-lo do nosso banco de dados.

A essa altura, você não ficará surpreso com a forma como fazemos isso - salve o comando SQL como uma string e, em seguida, passe-o para a nossa função execute_query.

delete_course = """
DELETE FROM course 
WHERE course_id = 20;
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, delete_course)

Vamos verificar para confirmar que obtivemos o resultado pretendido:

'Russo avançado' se foi, como esperávamos.

Esse comando também funciona para apagar colunas inteiras, usando DROP COLUMN e tabelas inteiras, usando DROP TABLE (ambos os textos de referência em inglês), mas não abordaremos esses comandos neste tutorial.

No entanto, vá em frente e experimente-os - não importa se você excluir uma coluna ou tabela de um banco de dados para uma escola fictícia. É uma boa ideia se familiarizar com esses comandos antes de passar para um ambiente de produção.

E o CRUD?

A essa altura, já podemos concluir as quatro operações principais para o armazenamento de dados persistentes.

Aprendemos a:

• Create (Criar) - bancos de dados, tabelas e registros inteiramente novos
• Read (Ler) - extrair dados de um banco de dados e armazenar em diversos formatos
• Update (Atualizar) - fazer alterações nos registros existentes no banco de dados
• Delete (Apagar) - remover registros que não são mais necessários

Poder fazer essas coisas é incrivelmente útil.

Antes de concluirmos, temos mais uma habilidade muito importante para aprender.

Criando registros a partir de listas

Vimos, ao preencher nossas tabelas, que podemos utilizar o comando SQL INSERT em nossa função execute_query para inserir registros em nosso banco de dados.

Dado que estamos usando Python para manipular nosso banco de dados SQL, seria útil poder obter uma estrutura de dados do Python, tal como uma lista (texto em inglês), e inseri-la diretamente em nosso banco de dados.

Isso pode ser útil quando queremos armazenar logs de atividade do usuário em um aplicativo de mídia social que escrevemos em Python ou entradas de usuários em uma página Wiki que criamos, por exemplo. Existem tantos usos possíveis para isso quantos você possa imaginar.

Esse método também é mais seguro se nosso banco de dados estiver aberto para nossos usuários a qualquer momento, pois ajuda a prevenir ataques de injeção de SQL, que podem danificar ou até destruir (texto em inglês) todo o nosso banco de dados.

Para fazer isso, escreveremos uma função usando o método executemany(), em vez do método execute() (textos de referência em inglês), mais simples, que usamos até agora.

def execute_list_query(connection, sql, val):
    cursor = connection.cursor()
    try:
        cursor.executemany(sql, val)
        connection.commit()
        print("Query successful")
    except Error as err:
        print(f"Error: '{err}'")

Agora temos a função, precisamos definir um comando SQL ('sql') e uma lista contendo os valores que desejamos inserir no banco de dados ('val'). Os valores devem ser armazenados em uma lista de tuplas (textos de referência em inglês), que é uma maneira bastante comum de armazenar dados em Python.

Para adicionar dois novos professores ao banco de dados, podemos escrever um código como este:

sql = '''
    INSERT INTO teacher (teacher_id, first_name, last_name, language_1, language_2, dob, tax_id, phone_no) 
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
    '''
    
val = [
    (7, 'Hank', 'Dodson', 'ENG', None, '1991-12-23', 11111, '+491772345678'), 
    (8, 'Sue', 'Perkins', 'MAN', 'ENG', '1976-02-02', 22222, '+491443456432')
]

Observe aqui que no código 'sql' usamos o '%s' como um espaço reservado para nosso valor. A semelhança com o '%s' para uma string em Python é apenas coincidência (e francamente, muito confusa), pois devemos usar '%s' para todos os tipos de dados (strings, inteiros, datas etc) com o MySQL Python Conector.

Você pode ver vários casos no Stackoverflow em que alguém ficou confuso e tentou usar '%d' para inteiros porque estava acostumado a fazer isso em Python. Isso não funcionará aqui - precisamos usar um '%s' para cada coluna para qual queremos adicionar um valor.

A função executemany, então, pega cada tupla em nossa lista 'val' e insere o valor relevante para aquela coluna no lugar do espaço reservado e executa o comando SQL para cada tupla contida na lista.

Isso pode ser feito para várias linhas de dados, desde que sejam formatadas corretamente. Em nosso exemplo, adicionaremos apenas dois novos professores, para fins ilustrativos, mas em princípio podemos adicionar quantos quisermos.

Vamos executar este comando e adicionar os professores ao nosso banco de dados.

connection = create_db_connection("localhost", "root", pw, db)
execute_list_query(connection, sql, val)

Boas-vindas à ILS, Hank e Sue!

Esta é mais uma função muito útil, permitindo-nos pegar dados gerados em nossos scripts e aplicativos Python e inseri-los diretamente em nosso banco de dados.

Conclusão

Abordamos muitas coisas neste tutorial.

Aprendemos como usar o Python e o MySQL Connector para criar um banco de dados totalmente novo no MySQL Server, criar tabelas dentro desse banco de dados, definir as relações entre elas e preenchê-las com dados.

Abordamos como Criar, Ler, Atualizar e Apagar dados em nosso banco de dados.

Vimos como extrair dados de bancos de dados existentes e carregá-los em pandas DataFrames, prontos para análise e trabalho adicional, aproveitando todas as possibilidades oferecidas pela stack PyData (texto em inglês).

Indo na outra direção, também aprendemos como pegar dados gerados por nossos scripts e aplicativos Python e gravá-los em um banco de dados onde eles podem ser armazenados com segurança para recuperação e manipulação posteriores.

Espero que este tutorial tenha ajudado você a ver como podemos usar Python e SQL juntos para poder manipular dados de forma ainda mais eficaz!

Se você quiser ver mais sobre os projetos e trabalhos do autor, visite o site do autor em craigdoesdata.de. Se você tiver algum feedback sobre este tutorial, entre em contato com ele - todos os comentários serão bem recebidos!

A instrução GROUP BY é poderosa, mas, às vezes, pode causar confusão.

Mesmo após oito anos, sempre que utilizo o GROUP BY, eu tenho de parar e pensar o que exatamente está ocorrendo.

Neste artigo, nós vamos ver como criar uma instrução GROUP BY, o que ela faz em sua consulta e como você pode utilizá-la para gerar agregações e coletar insights sobre os seus dados.

O que vamos fazer:

• Configurar o banco de dados.
• Configurar os dados (criar vendas).
• Como funciona um GROUP BY?.
• Escrever instruções GROUP BY .
• Agregações (COUNT, SUM, AVG).
• Trabalhar com diversos grupos.
• Utilizar funções dentro do GROUP BY.
• Filtrar grupos com HAVING.
• Agregar com agrupamentos implícitos.

Configurar o banco de dados

Antes de escrever nossas consultas, precisamos configurar nosso banco de dados.

Para os exemplos, vamos utilizar o PostgreSQL, mas as consultas e conceitos mostrados serão de fácil compreensão para qualquer sistema de banco de dados moderno (como o MySQL, o SQL Server e assim por diante).

Para trabalhar com nosso banco de dados PostgreSQL, podemos utilizar psql — o comando de linha interativo do PostgreSQL. Se você possui outro serviço de banco de dados de sua preferência, não há problema de trabalhar nele também.

Para começar, vamos criar nossa tabela de dados. Com o PostgreSQL instalado, podemos usar o comando  createdb <database-name> no console para criar um novo banco de dados. Chamei o meu de fcc:

$ createdb fcc

Agora, vamos iniciar o nosso console interativo utilizando o comando psql, e nos conectar ao banco de dados que acabamos de fazer \c <nome-do-banco>:

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

Observação: eu limpei a saída do psql nestes exemplos para facilitar a leitura, então não se preocupe se as saídas mostradas aqui não sejam exatamente iguais ao que você verá no seu terminal.

Eu encorajo você a acompanhar estes exemplos e a fazer as consultas por conta própria. Você vai aprender e lembrar bem mais trabalhando nos exemplos ao invés de apenas lê-los.

Configurar os dados (criar vendas).

Para os nossos exemplos, vamos utilizar um banco que guarde o histórico de vendas de vários produtos através de diferentes localizações.

Iremos chamar esta tabela de vendas. Ela será uma representação simples de uma loja: nome do local, nome do produto, preço e a data em que o produto foi vendido.

Se fossemos construir este banco em uma aplicação real, configuraríamos as chaves estrangeiras para outras tabelas (como local ou produtos). No entanto, para ilustrar os conceitos de GROUP BY, vamos utilizar apenas colunas do tipo TEXT.

Vamos criar a tabela e inserir alguns dados de vendas:

CREATE TABLE vendas(
  local TEXT,
  produto TEXT,
  preco DECIMAL,
  vendido_em TIMESTAMP
);

INSERT INTO vendas(local, produto, preco, vendido_em) VALUES
('Central', 'Café', 2, NOW()),
('Central', 'Café', 2, NOW() - INTERVAL '1 hora'),
('Centro', 'Biscoito', 3, NOW() - INTERVAL '2 hora'),
('Centro', 'Café', 2, NOW() - INTERVAL '1 dia'),
('Central', 'Biscoito', 2, NOW() - INTERVAL '2 dias'),
('Rua 1', 'Biscoito', 3, NOW() - INTERVAL '2 dias' - INTERVAL '1 hora'),
('Rua 1', 'Café', 2, NOW() - INTERVAL '3 dias'),
('Central', 'Biscoito', 3, NOW() - INTERVAL '3 dias' - INTERVAL '1 hora');

Temos 3 localizações: Central, Centro e Rua 1.

Nós temos dois produtos, Café e Biscoito, e inserimos diferentes valores em vendido_em para representar os itens sendo vendidos em dias e horas diferentes.

Existem algumas vendas na data de hoje, algumas na data de ontem, e algumas em dias anteriores a ontem.

Como funciona um GROUP BY?

Para ilustrar como as instruções GROUP BY funcionam, vamos primeiro falar através de um exemplo.

Imagine que temos um quarto cheio de pessoas que nasceram em países diferentes.

Se quiséssemos descobrir a média da altura das pessoas no quarto por país, primeiro, pediríamos para as pessoas se separarem em grupos baseados no país em que nasceram.

Uma vez separados em seus devidos grupos, poderíamos calcular a média de altura de qualquer um desses grupos.

É assim que funcionam as instruções GROUP BY. Primeiro, definimos como queremos agrupar as informações – então, podemos realizar cálculos ou agregações nos grupos.

Diversos grupos

Podemos agrupar os dados em quantos grupos ou subgrupos quisermos.

Por exemplo, depois de pedirmos para as pessoas se separarem baseados no seu país de nascimento, podemos dizer para cada um desses grupos que se separem em grupos baseados na cor de seus olhos.

Ao fazer isso, temos grupos de pessoas baseados na combinação do seu país de nascimento e na cor de seus olhos.

Agora, podemos encontrar a média da altura dentro de cada um desses pequenos grupos. Teremos um resultado mais específico: média de altura por país e cor dos olhos.

As instruções GROUP BY são frequentemente usadas para situações onde você utiliza a frase por alguma coisa ou para cada alguma coisa:

• Média de altura por país
• Número total de pessoas para cada combinação de cor de olhos e cor de cabelo
• Total de vendas por produto

Escrever instruções GROUP BY

Uma instrução GROUP BY é muito fácil de escrever — apenas utilizamos a palavra chave GROUP BY e então especificamos os campos que queremos agrupar:

SELECT ...
FROM vendas
GROUP BY local;

Esta consulta simples agrupa nossos dados de  vendas pela coluna do local.

Fizemos o agrupamento — mas e agora? O que colocamos em nosso SELECT?

A coisa mais clara para selecionar é o nosso local— nós agrupamos para que ao menos vejamos o nome dos grupos que fizemos:

SELECT location
FROM sales
GROUP BY location;

O resultado é as nossas três localizações:

  local
------------
 Rua 1
 Central
 Centro
(3 rows)

Se olharmos para a tabela bruta de dados  (SELECT * FROM vendas;), veremos que temos 4 linhas com o local Central, duas linhas com o local Centro, e duas linhas com o local Rua 1:

 produto 	|    local 	| preco |          vendido_em
---------+---------+------------+-------+----------------------------
 Cafe    	| Central    	|     2 | 2020-09-01 09:42:33.085995
 Cafe  	 	| Central    	|     2 | 2020-09-01 08:42:33.085995
 Biscoito	| Centro     	|     3 | 2020-09-01 07:42:33.085995
 Cafe  	 	| Centro     	|     2 | 2020-08-31 09:42:33.085995
 Biscoito	| Central    	|     2 | 2020-08-30 09:42:33.085995
 Biscoito	| Rua 1   	|     3 | 2020-08-30 08:42:33.085995
 Cafe  	 	| Rua 1		|     2 | 2020-08-29 09:42:33.085995
 Biscoito	| Central    	|     3 | 2020-08-29 08:42:33.085995
(8 rows)

Agrupando na coluna local, nosso banco de dados pega essas linhas de input e identifica locais únicos dentre eles — estes locais únicos servem como nosso "grupo".

E quanto às outras colunas da nossa tabela?

Se tentarmos selecionar a coluna produto que não agrupamos...

SELECT
  local,
  produto
FROM vendas
GROUP BY local;

...receberemos o seguinte erro:

ERROR:  column "vendas.produto" must appear in the GROUP BY clause or be used in an aggregate function

O problema aqui é que pegamos oito linhas e as esprememos ou as transformamos em três.

Não podemos apenas retornar o resto das colunas como normalmente fazíamos — nós possuíamos oito linhas, e agora temos três.

O que faremos com as cincos linhas restantes de dados? Quais das oito linhas de dados devem ser mostradas nestas três linhas de locais distintos?

Não há uma resposta clara e definitiva aqui.

Para utilizar o resto da nossa tabela de dados, nós também temos de analisar os dados do restante das colunas dentro dos nossos três grupos de local.

Isto significa que temos de agregar ou realizar um cálculo para produzir um tipo de resumo das informações sobre os dados restantes.

Agregações (COUNT, SUM, AVG)

Uma vez que definimos como agrupar nossos dados, podemos realizar agregações com as colunas restantes.

São coisas como contar o número de linhas por grupo, somar um valor em específico através do grupo, ou calcular a média das informações dentro do grupo.

Para começar, vamos encontrar o número de vendas por local.

Como cada gravação em nossa tabela de vendas é uma venda, o número de vendas por local seria o número de linhas dentro de cada local do grupo.

Para fazer isto, utilizaremos a função de agregação COUNT() para contar o número de linhas de cada grupo:

SELECT
  local,
  COUNT(*) AS numero_de_vendas
FROM vendas
GROUP BY local;

Nós usamos COUNT(*), que conta todas as linhas de input por grupo (COUNT() também funciona com expressões, mas possui um comportamento levemente diferente).

Abaixo, vemos como o banco de dados executa esta consulta:

• FROM vendas — Primeiro, retornamos todos os dados da tabela vendas
• GROUP BY local — Em seguida, determinamos os grupos únicos de local
• SELECT ... — Finalmente, selecionamos o nome do local e contamos o número de linhas daquele grupo

Nomearemos a contagem de linhas usando AS numero_de_vendas para fazer uma saída de dados mais legível. Se parecerá com o seguinte:

   local    | numero_de_vendas
------------+-----------------
 Rua 1      |       2
 Central    |       4
 Centro     |       2
(3 rows)

O local Rua 1 possui duas vendas, Central possui quatro, e Centro possui duas.

Aqui, podemos ver como pegamos as demais colunas de dados das nossas oito linhas independentes e as colocamos em um resumo das informações para cada local: o número de vendas.

De um jeito similar, ao invés de contar o número de linha em um grupo, podemos somar as informações dentro do grupo — como o dinheiro total ganho nesses locais.

Para fazer isto, utilizamos a função SUM() :

SELECT
  local,
  SUM(preco) AS ganho_total
FROM vendas
GROUP BY local;

No lugar de contar o número de linhas em cada grupo, somamos o total de ganhos de cada venda. Isto mostra o ganho total por local:

  local	    | ganho_total
------------+---------------
 Rua 1      |             5
 Central    |             9
 Centro     |             5
(3 rows)

Média (AVG)

Encontrar a média do valor de vendas por local significa apenas que trocaremos a função SUM() para a função AVG():

SELECT
  local,
  AVG(preco) AS media_de_ganho
FROM vendas
GROUP BY local;

Trabalhar com diversos grupos

Até o momento, temos trabalho apenas com um grupo: local.

E se quisermos subdividir o grupo ainda mais?

É semelhante ao cenário "País de nascimento e cor dos olhos" com o qual começamos. E se quiséssemos encontrar o número de vendas por produto e por local?

Para fazermos tudo isso, precisamos adicionar uma segunda condição de agregação ao declararmos o GROUP BY :

SELECT ...
FROM vendas
GROUP BY local, produto;

Ao adicionarmos uma segunda coluna GROUP BY, nós subdividimos ainda mais o nosso grupo de locais em grupo de locais por produto.

Agora que estamos agrupando também pela coluna produto, podemos retornar ao nosso SELECT (adicionarei a instrução ORDER BY nestas consultas para facilitar a leitura dos dados de saída.)

SELECT
  local,
  produto
FROM vendas
GROUP BY local, produto
ORDER BY local, produto;

Olhando para o resultado de nosso novo agrupamento, podemos ver nossas combinações únicas de local/produto:

  local     | produto
------------+---------
 Rua 1      | Biscoito
 Rua 1      | Café
 Centro     | Biscoito
 Centro     | Café
 Central    | Biscoito
 Central    | Café
(6 rows)

Agora que temos nossos grupos, o que queremos fazer com o resto das nossas colunas de dados?

Bem, podemos encontrar o número de vendas por produto e por local utilizando a mesma função de agregação utilizada antes:

SELECT
  local,
  produto,
  COUNT(*) AS numero_de_vendas
FROM vendas
GROUP BY local, produto
ORDER BY local, produto;

  local  | produto    | numero_de_vendas
------------+---------+-----------------
 Rua 1   | Biscoito   |               1
 Rua 1   | Café       |               1
 Centro  | Biscoito   |               1
 Centro  | Café       |               1
 Central | Biscoito   |               2
 Central | Café       |               2
(6 rows)

Como exercício para o leitor™: encontre o total de ganhos (soma) de cada produto por local.

Utilizar funções dentro do GROUP BY

Agora, tentaremos achar o número total de vendas por dia.

Se seguirmos padrões similares como fizemos com os nossos locais e agrupamos pela nossa coluna vendido_em...

SELECT
  vendido_em,
  COUNT(*) AS vendas_por_dia
FROM vendas
GROUP BY vendido_em
ORDER BY vendido_em;

...esperamos que o resultado seja ter cada grupo em um único dia— no entanto, temos isso:

          vendido_em        | vendas_por_dia
----------------------------+---------------
 2020-08-29 08:42:33.085995 |             1
 2020-08-29 09:42:33.085995 |             1
 2020-08-30 08:42:33.085995 |             1
 2020-08-30 09:42:33.085995 |             1
 2020-08-31 09:42:33.085995 |             1
 2020-09-01 07:42:33.085995 |             1
 2020-09-01 08:42:33.085995 |             1
 2020-09-01 09:42:33.085995 |             1
(8 rows)

Parece que nossos dados não estão agrupados — temos cada linha individualmente.

Nossos dados, no entanto, estão agrupados! O problema é que cada linha de vendido_em possui um valor único — então, nossas linhas são seus próprios grupos!

O GROUP BY está funcionando corretamente, mas não é a saída que queremos.

O que ocasiona esta situação é a informação extra do carimbo de data e hora.

Cada um destes carimbos se diferenciam por horas, minutos ou segundos — assim, eles são colocados em seu devido lugar em seu próprio grupo.

Precisamos converter cada um dos valores de data e hora para apenas data:

• 2020-09-01 08:42:33.085995 => 2020-09-01
• 2020-09-01 09:42:33.085995 => 2020-09-01

Convertido para uma data, todos os carimbos do mesmo dia retornarão apenas a referência do valor da data em que foi efetuada a compra—e será colocado dentro do mesmo grupo.

Para fazermos isso, converteremos os dados de vendido_em para data:

SELECT
  vendido_em::DATE AS date,
  COUNT(*) AS vendas_por_dia
FROM vendas
GROUP BY vendido_em::DATE
ORDER BY vendido_em::DATE;

Em nossa instrução GROUP BY, utilizamos ::DATE para cortar uma parte do carimbo de data/hora para apenas dia. Isto corta efetivamente as horas/minutos/segundos do carimbo e retorna apenas o dia.

No nosso SELECT, retornamos a mesma expressão e damos a ela um nome para deixar o resultado mais bonito.

Pela mesma razão, não podemos retornar o produto sem agrupar ou realizar algum tipo de agrupamento. O banco de dados não deixará retornar apenas o vendido_em — tudo que está no SELECT deve estar dentro do GROUP BY ou em algum tipo de agregação nos grupos resultantes.

O resultado de vendas por dia que originalmente queríamos ver:

    date    | vendas_por_dia
------------+---------------
 2020-08-29 |             2
 2020-08-30 |             2
 2020-08-31 |             1
 2020-09-01 |             3
(4 rows)

Filtrar grupos com HAVING

Dando continuidade, vamos verificar como filtrar nossas linhas agrupadas.

Para fazer isso, vamos tentar encontrar dias onde houve mais de uma venda.

Sem agrupar, normalmente filtraríamos nossas linhas utilizando a instrução. Por exemplo:

SELECT *
FROM vendas
WHERE produto = 'Café';

Com nossos grupos, utilizaremos algo parecido com isso para filtrá-los com base na quantidade de linhas...

SELECT
  vendido_em::DATE AS date,
  COUNT(*) AS vendas_por_dia
FROM vendas
WHERE COUNT(*) > 1      -- filtrar os grupos?
GROUP BY vendido_em::DATE;

Infelizmente, isso não funciona e recebemos este erro:

ERROR:  aggregate functions are not allowed in WHERE

Funções agregadas não são permitidas dentro da instrução WHERE, pois ela é avaliada antes da instrução GROUP BY—não há nenhum grupo para realizar os cálculos.

Existe, contudo, um tipo de instrução que nos permite filtrar e realizar agregações. Ela é avaliada depois da instrução GROUP BY: a instrução HAVING.

A instrução HAVING é como a instrução WHERE para os seus grupos.

Para encontrar os dias em que houve mais de uma venda, adicionamos a instrução HAVING que verificará a quantidade de linhas dentro do grupo:

SELECT
  vendido_em::DATE AS date,
  COUNT(*) AS vendas_por_dia
FROM sales
GROUP BY vendido_em::DATE
HAVING COUNT(*) > 1;

A instrução HAVING filtra qualquer linha onde a contagem de linhas deste grupo não é maior que um. Vemos isso no nosso resultado:

    date    | vendas_por_dia
------------+---------------
 2020-09-01 |             3
 2020-08-29 |             2
 2020-08-30 |             2
(3 rows)

Apenas para deixar completo, segue em ordem a execução de todas as partes de uma instrução SQL:

• FROM — Retorna todas as linhas da tabela FROM
• JOIN — Realiza quaisquer junções
• WHERE — Filtra linhas
• GROUP BY — Forma grupos
• HAVING — Filtra grupos
• SELECT — Seleciona os dados a serem retornados
• ORDER BY — Ordena a saída de linhas
• LIMIT — Retorna uma certa quantidade de linhas

Agregações com agrupamentos implícitos

O último tópico que veremos é a agregação que pode ser realizada sem o GROUP BY— ou, melhor dizendo, que possui um agrupamento implícito.

Estas agregações são úteis em cenários em que você quer encontrar um agrupamento específico dentro de uma tabela — como o total de ganhos ou o maior ou menor valor da coluna.

Por exemplo, podemos encontrar o total de ganhos de todos os locais selecionando a soma da tabela inteira:

SELECT SUM(preco)
FROM vendas;

 sum
-----
  19
(1 row)

Até o momento fizemos $19 de vendas em todos os locais (vivaaaaa!).

Outra coisa útil que podemos consultar é o primeiro ou o último valor de algo.

Por exemplo, qual a data da nossa primeira venda?

Para encontrá-la, basta utilizarmos a função  MIN():

SELECT MIN(vendido_em)::DATE AS primeira_venda
FROM vendas;

 primeira_venda
------------
 2020-08-29
(1 row)

(Para encontrar a data da última venda, bastar substituir MIN() por MAX().)

Como usar MIN / MAX

Embora essas consultas simples possam ser utilizadas em consultas independentes, elas são frequentemente parte de filtros de consultas maiores.

Por exemplo, vamos tentar encontrar o total de vendas do último dia em que tivemos vendas.

Um jeito de podermos escrever esta consulta seria o seguinte:

SELECT
  SUM(preco)
FROM vendas
WHERE vendido_em::DATE = '2020-09-01';

Esta consulta funciona, mas obviamente codificando a data de 2020-09-01.

09/01/2020 talvez seja o último dia em que tivemos uma venda, mas nem sempre será essa data. Precisamos de uma solução dinâmica.

Conseguimos isto através da combinação desta consulta com a função MAX() em uma subconsulta:

SELECT
  SUM(preco)
FROM venda
WHERE vendido_em::DATE = (
  SELECT MAX(vendido_em::DATE)
  FROM vendas
);

Na nossa instrução WHERE, encontramos a data mais antiga dentro da tabela utilizando a subconsulta: SELECT MAX(vendido_em::DATE) FROM vendas.

Então, utilizamos esta "data máxima" como um valor para filtrar a tabela, e somamos cada uma das vendas.

Agrupamento implícito

Chamamos de agrupamento implícito, pois, se tentarmos selecionar e agregar  o valor com uma coluna não agregada dessa forma...

SELECT
  SUM(preco),
  local
FROM vendas;

...teremos um erro familiar:

ERROR:  column "vendas.local" must appear in the GROUP BY clause or be used in an aggregate function

GROUP BY é uma ferramenta

Como muitos dos tópicos em desenvolvimento de software, GROUP BY é uma ferramenta.

Existem várias maneiras de escrever e reescrever estas consultas utilizando a combinação de  GROUP BY, funções agregadas ou outras ferramentas como  DISTINCT, ORDER BY e LIMIT.

Entender e trabalhar com o GROUP BY requer um pouco de pratica, mas ao aprender, você descobrirá um conjunto inteiramente novo de problemas que você poderá resolver!

Se gostou deste post, você pode seguir o autor deste artigo no Twitter, onde ele fala sobre banco de dados e sobre como ter sucesso na carreira como desenvolvedor.

Obrigado pela leitura!