Desvendando o SQL com Deep Purple

Cruze tabelas, crie condições e peça exatamente o que quiser a sua base de dados. Neste tutorial sobre SQL, conheceremos os comandos SUBSTR e DISTINCT, além de aprender a fazer cruzamentos com o JOIN. Tudo isso ao som de rock.

Antes de tudo, é preciso um par de avisos. Este texto é uma continuação de nosso primeiro tutorial sobre como entrevistar grandes bases de dados com SQL. Se você não tem familiaridade com esta linguagem, ele é uma leitura prévia necessária, pois aqui iremos partir dos conhecimentos ali postos para ir além. Ao invés de aprender apenas as operações mais fundamentais usando dados de candidaturas políticas, conheceremos funcionalidades um pouco mais avançadas, ao som de rock.

Isto porque o texto é também uma continuação das aulas do jornalista de dados Marcelo Soares no Dados 360, um curso online inédito criado pela Escola de Dados em 2020. Nas aulas, Marcelo Soares ensinou os participantes a trabalhar com SQL e apresentou uma base de dados que fez para sua banda favorita: o grupo britânico Deep Purple.

Baixe aqui os dados!

A base é composta por três tabelas (discos, shows e formações) que você pode baixar no link acima. Neste tutorial, vamos revisitar alguns comandos apresentados no primeiro tutorial e adicionaremos outros para conseguir “traduzir” perguntas mais complexas na forma de consultas SQL e realizar cruzamento de tabelas.

O primeiro passo é baixar os dados linkados no parágrafo anterior e importá-los no programa que você irá usar para fazer as consultas em SQL. No primeiro tutorial, explicamos como realizar estas etapas.

Se você já se considera experiente em SQL, pode testar seus conhecimentos no quiz abaixo. Nele, você precisará fazer consultas SQL aos dados para encontrar as respostas às seguintes questões:

  1. Em quantas cidades diferentes o Deep Purple tocou?
  2. Destas cidades, quantas foram no Brasil?
  3. Quantos shows o Deep Purple fez em 1975?
  4. Em qual mês/ano fizeram mais shows?
  5. Qual o show foi lançado mais vezes em disco?

Agora, iremos aprender alguns comandos que nos permitem traduzir perguntas como estas! Então, liga o som e vamos começar!

Fatiando e contando: SUBSTR e COUNT

Imagine que você precise responder à seguinte pergunta usando aqueles dados: “Quantos shows o Deep Purple fez por ano?”.

Ao abrir a tabela sobre os shows da banda, você repara que a coluna data contém na mesma célula o dia, mês e ano dos shows (por exemplo: ‘1968-08-03’). Como seria possível extrair apenas o ano neste caso?

Se nosso campo estivesse no formato de data, poderíamos utilizar funções específicas para isso. Porém, neste caso, veremos como fazer isso mantendo o campo como um texto (string).

Para situações assim, onde há um padrão claro, você pode extrair uma fatia, uma parte dos caracteres de certo registro. Isto é feito com a operação SUBSTR ao executar o comando SELECT, que indica as colunas queremos consultar.

Depois de digitar SUBSTR você vai colocar entre parênteses três informações:

  • Primeiro, qual coluna você quer extrair os dados?
  • Depois, a posição do primeiro caractere que você quer extrair;
  • Por fim, a posição do último que você quer extrair.

Podemos terminar dando um novo nome para a coluna que será criada com a “fatia” desejada, usando o comando ‘AS’. Esta é a primeira parte do comando, que cria uma coluna nova chamada ‘ano’ com os quatro primeiros caracteres da coluna ‘data’:

SELECT SUBSTR(data,1,4) AS ano

Mas para responder a nossa pergunta precisamos também contar a quantidade de shows. Como cada show é representado por uma linha da tabela, podemos usar o seguinte comando: count(*).

SELECT SUBSTR (data, 1, 4) as ano, COUNT(*)

 

Precisamos então declarar de qual tabela vamos extrair as informações (FROM shows). E, por fim, digitar GROUP BY ano para o SQL agrupar os registros de acordo com a nova coluna ‘ano’ para então fazer a contagem de linhas de cada um.

SELECT substr(ano,1,4) AS ano, COUNT(*)

FROM shows

GROUP BY ano

 

EVITANDO REPETIÇÕES COM O DISTINCT

E se a pergunta fosse: “Em quantas cidades diferentes eles tocaram?”.

Neste caso, precisamos fazer uma contagem de cidades, mas considerando apenas os valores diferentes ou distintos entre si. Isso é fácil: basta combinar o COUNT com o comando DISTINCT.

Assim, teríamos a seguinte consulta:

SELECT count(distinct cidade) FROM shows.


Se você não colocasse o DISTINCT ele iria apenas contar o total de linhas com valores não nulos. Repare na imagem abaixo, onde fazemos uma consulta sem o comando DISTINCT.

O que este resultado nos diz é que há 2600 linhas com a coluna Cidade preenchida com algum valor (ou seja, não-nulos). Se você navegar pelos dados, vai reparar que quando o local é desconhecido o campo ‘Cidade’ está preenchido com duas interrogações, ou seja, ele não é nulo, mas também não indica uma cidade válida.

Se incluirmos o comando DISTINCT, obtemos o número de valores diferentes neste coluna.

O único problema é que ele seguirá contado os registros preenchidos com interrogações como um valor não-nulo, então, para termos o número de cidades, poderíamos fazer um filtro ou simplesmente subtrair o resultado exibido por um (1152-1 = 1151 cidades).

Dica: Particularidades assim variam bastante de acordo com os dados. A solução é primeiro buscar um dicionário de dados e, na ausência deste, realizar uma análise exploratória, agrupando, ordenando e verificando o preenchimento das suas colunas de interesse.

CRUZANDO TABELAS COM O JOIN

Vamos a nossa última pergunta. Ao longo de sua trajetória, o Deep Purple gravou discos tanto em estúdio, quanto em apresentações ao vivo. Imagine que você queria entender melhor apenas o segundo grupo.

Para responder uma pergunta como “Quando e onde foram gravados os discos ao vivo?”, precisaremos consultar não uma, mas duas tabelas.

Para responder ao “quando?” precisamos da coluna data. O “onde” é respondido com a coluna de Cidade e País. Todas estão na tabela “shows”, mas para saber se aquele show foi lançado como um álbum precisamos consultar a tabela “discos”.

E agora? Como juntar tabelas diferentes na nossa consulta?

Como sempre, precisamos indicar de que tabela serão extraídas as informações: nesse caso, FROM shows. A diferença é que seguimos com o comando JOIN, que fará a junção desta tabela com outra, a de “discos”, na nossa consulta.

O “JOIN” nos permite cruzar tabelas a partir de um campo em comum, um identificador. Este identificador é uma coluna presente nas duas tabelas, que servirá como uma “ponte” entre os dois conjuntos de dados.

No nosso exemplo, trata-se da primeira coluna da tabela ‘shows’. Ela traz uma numeração única para cada show da banda e está presente tanto na tabela ‘shows’, quanto na tabela ‘discos’, neste último caso apenas para os discos que foram gravados ao vivo.

SELECT data, Cidade, País, Título

FROM shows JOIN discos ON shows.Formação = discos.Formação

 

Por último, você irá escrever o comando GROUP BY, agrupando o resultado pelas colunas data, Cidade e País, pois existem discos diferentes relacionados ao mesmo show. Assim, removemos as duplicadas!

SELECT data, Cidade, País, Título

FROM shows JOIN discos ON discos.Show = shows.nr

GROUP BY data, Cidade, País


Agora que você já está mais perto de ser um rockstar do SQL, que tal testar seus conhecimentos respondendo a este quiz?

* Este tutorial foi escrito por Adriano Belisário, Luan Rodrigues e Manuella Caputo, a partir do quiz elaborado por Marcelo Soares, que também colaborou com a revisão.

Deixe um comentário

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.