Um tapa na cara de SQL (Pt. I/II)

As empresas demandam por pessoas que saibam analisar e gerenciar dados.

A linguagem SQL, que é a abreviação de Structured Query Language, essa linaguagem nos fornece um meio de acessar e manipular dados de maneira significativa, possibilitando insights empresariais que antes não eram possíveis. As empresas coletam dados a taxas exponenciais e há uma demanda crescente por pessoas que saibam como analisá-los e gerenciá-los.

Nos últimos anos, de repente, os dados ganharam destaque — mesmo assim, poucas pessoas sabem como acessá-los de forma significativa, o que fez com que a demanda por especialistas em SQL aumentasse.

• SQL para quem?

Uma percepção equivocada que se tem sobre o SQL é a de que se trata de uma ferramenta de TI e, portanto, exclusiavamente aos profissionais de tecnologia (e não de negócios). Contudo, executivos, gerentes, profissionais de TI e engenheiros podem se beneficiar do aprendizado de SQL para posicionar melhor suas carreiras.

• Banco de dados?

Um banco de dados é qualquer coisa que colete, armazene e organize dados, onde este banco contém uma ou mais tabelas que podem estar relacionadas entre si. Quando alguém se refere a um “banco de dados” provavelmente está se referindo a um sistema de gerenciamento de banco de dados relacional (RDBMS, Relational Database Management System).

• Examinando bancos de dados relacionais

Uma tabela é um conceito familiar. Uma tabela possoui colunas e linhas para armazenar dados, semelhante ao que ocorre em uma planilha Excel ou um DataFrame em Pandas.

As tabelas podem se relacionar umas com as outras, como a tabela de PEDIDOS, que corresponde a uma tabela CLIENTES para obter informações de clientes. Por exemplo, suponhamos que tivéssemos uma tabela PEDIDOS com um campo chamado CUSTOMER_ID — código do cliente:

Tabela PEDIDOS com campo CUSTOMER_ID

É provável que haja outra tabela, talvez chamada CLIENTES, contendo as informações de cliente de cada CUSTOMER_ID — código do cliente.

Tabela CLIENTES

Ao percorrer a tabela PEDIDOS, podemos usar CUSTOMER_ID para procurar informações de clientes na tabela CUSTOMER. Essa é a ideia básica existente por trás de um “banco de dados relacional”, em que as tabelas podem ter campos que apontem para informações de outras tabelas.

Por que as tabelas são separadas?

O motivo é a normalização, que é a separação dos diferentes tipos de dados em suas respectivas tabelas em vez de serem inseridos na mesma tabela. Se tivéssemos todas as informações em uma única tabela, ela seria redundante, saturada e de difícil manutenção. Imagine se armazenássemos informações de clientes na tabela ORDER:

Tabela não normalizada - redundância

Observe que no caso da Re-Barre Construction (índice 2, 3 e 5), alguém precisou fornecer as informações de cliente (nome, região, endereço, cidade, estado e código postal) três vezes para todos os três pedidos. Isso é redundante, ocupa espaço de armazenamento desnecessário e é de difícil manutenção. Imagine se o endereço de um cliente mudasse e você precisasse atualizar todos os pedidos.

É por isso que é melhor separar CUSTOMERS e ORDERS em duas tabelas distintas. Se precisar alterar o endereço de um cliente, só terá de alterar um único registro na tabela CUSTOMERS:

Tabela normalizada

O que é SQLite? O banco de dados mais popular do mundo.

Muitas vezes, o que precisamos é de um local em que a inserção de dados seja rápida e fácil sem a complexidade de uma instalação cliente-servidor. Queremos armazenar dados em um arquivo simples e editá-los tão facilmente como um documento do Word. É aí onde entra o SQLite.

Porém, todas as tecnologias têm suas vantagens e desvantagens. Já que não há um servidor gerenciando o acesso, ele não é adequado em ambientes multiusuário em que várias pessoas podem editar simultaneamente o arquivo SQLite.

SQLiteStudio

Há muitos editores de SQL que você pode usar para trabalhar com um banco de dados SQLite. O SQLiteStudio é intuitivo e torna fácil explorar e gerenciar um banco de dados.

1. SELECT

No trabalho com bancos de dados e SQL, a tarefa mais comum é solicitar dados de uma ou mais tabelas e exibi-los. A instrução SELECT faz isso.

Escreveremos nossa primeira instrução SQL. A operação SQL mais comum é a instrução SELECT, que extrai dados de uma tabela e exibe os resultados:

SELECT * FROM customer;

Quando essa instrução SELECT for executada, ela retornará todas as colunas da tabela CUSTOMER e as exibirá:

Selecionando todos os registros da tabela CUSTOMER.

Não precisamos acessar todas as colunas em uma instrução SELECT. A consulta a seguir só acessará as colunas CUSTOMER_ID e NAME:

SELECT customer_id, name FROM customer;
Selecionando apenas duas colunas de uma tabela

Opcionalmente, uma única instrução SQL pode terminar com ponto e vírgula (;), como mostrado nos exemplos anteriores. No entanto, o símbolo de ponto e vírgula é necessário na execução de várias instruções SQL de uma só vez, o que é útil para gravar dados.

Expressões em instruções SELECT

Também efetuar cálculos em uma ou mais colunas e incluí-los no resultado de sua consulta.

Trabalharemos com outra tabela, chamada PRODUCT. Primeiro, execute uma operação “selecionar tudo” para ver os dados:

SELECT * FROM product;
Tabela PRODUCT

Suponhamos que quiséssemos gerar uma coluna calculada chamada TAXED_PRICE em que o preço fosse 7% mais alto que em PRICE:

SELECT
product_id,
description,
price,
price * 1.07 AS TAXED_PRICE <----------
FROM product;
Tabela PRODUCT

Observe como a coluna TAXED_PRICE foi calculada dinamicamente na consulta SELECT, gerando o valor de TAXED_PRICE para cada registro. Essa coluna não é armazenada na tabela; em vez disso, é calculada e exibida sempre que executamos a consulta.

Podemos usar aliases para dar nomes a expressões. Também podemos usá-los para aplicar um novo nome a uma coluna existente dentro da consulta.

SELECT
product_id,
description,
price AS UNTAXED_PRICE, <----------
price * 1.07 AS TAXED_PRICE
FROM product;
Fornecendo o alias UNTAXED_PRICE para a coluna PRICE

Pode não ser interessante haver mais de duas casas decimais. Todas as plataformas de banco de dados têm funções internas que ajudam nesses tipos de operações e o SQLite fornece uma função round() que aceita dois argumentos em parênteses separados por uma vírgula:

  • o valor a ser arredondado
  • o número de casas decimais do arredondamento.

Para arredondar TAXED_PRICE para duas casas decimais, podemos passar a expressão de multiplicação PRICE * 1.07 como o primeiro argumento e 2 como o segundo:

SELECT
product_id,
description,
round(price * 1.07, 2) AS TAXED_PRICE <----------
FROM product;

Execute a instrução e verá que ela está arredondando TAXED_PRICE, que fica com uma aparência muito melhor com duas casas decimais:

Usando a função round() para limitar as casas decimais de TAXED_PRICE

Concatenação de texto

Também podemos usar expressões com texto e outros tipos de dados. Um operador útil quando usado com texto é o de concatenação, que mescla dois ou mais dados. O operador de concatenação é especificado por um pipe duplo (||); os valores de dados a serem concatenados devem ser colocados nos dois lados do pipe duplo.

Por exemplo, podemos concatenar os campos CITY e STATE da tabela CUSTOMER e inserir uma vírgula e um espaço entre eles para criar um valor LOCATION:

SELECT 
name,
city || ',' || state AS location
FROM customer;
Concatenando CITY e STATE

Podemos até concatenar vários campos em um únicor valor SHIP_ADDRESS:

SELECT name,
street addres || '' || city || ',' || state || '' || zip
AS ship_address
Concatenando vários campos para criar SHIP_ADDRESS

A concatenação deve funcionar com qualquer tipo de dado (números, datas, etc.), que é tratado como texto após a mesclagem.

Muitas plataformas de banco de dados usam pipes duplos (||) na concatenação, exceto o MySQL e algumas outras que requerem o uso de uma função CONCAT().

2. WHERE

No trabalho com dados, uma tarefa muito comum é a filtragem de registros de acordo com critérios, o que pode ser feito com uma instrução WHERE.

Filtrando registros

Usaremos agora a tabela STATION_DATA , ela contém uma grande amostra de dados relacionados ao clima coletadas em várias estações meteorológicas. Começaremos examinando-o com WHERE, que pode ser usado na filtragem de recursos de acordo com um critério.

Usando WHERE com números

Suponhamos que só estivéssemos interessados nos registros de station_data do ano 2010.

SELECT * FROM station_data
WHERE year = 2010;
Registros do ano 2010

Inversamente, podemos usar != ou <> para obter tudo, exceto 2010:

SELECT * FROM station_data
WHERE year != 2010;

Ou:

SELECT * FROM station_data
WHERE year <> 2010;

Essas duas sintaxes fazem a mesma coisa. Atualmente o SQLite e a maioria das plataformas suportam ambas. No entanto, o Microsoft Access e o IBM DB2 só dão suporte a <>.

Também podemos qualificar intervalos inclusivos usando uma instrução BETWEEN:

SELECT * FROM station_data
WHERE year BETWEEN 2005 AND 2010;

Instruções AND, OR e IN

Uma instrução BETWEEN pode ser expressa alternativamente com o uso das expressões maior ou igual a e menor ou igual a e uma instrução AND. É um pouco mais verboso:

SELECT * FROM station_data
WHERE year >= 2005 AND year <= 2010;

Se quiséssemos todos os dados entre 2005 e 2010 exclusivamente — isto é, não incluindo esses dois anos — bastaria remover os caracteres =. Só 2006, 2007, 2008 e 2009 se qualificariam:

SELECT * FROM station_data
WHERE year > 2005 AND year < 2010;

Também temos a opção de usar OR. Em uma instrução OR, pelo menos um dos critérios deve ser atendido para o registro se qualificar. Se só quiséssemos registros dos meses 3, 6, 9 ou 12, poderíamos usar OR para obtê-los:

SELECT * FROM station_data
WHERE MONTH = 3
OR month = 6
OR month = 9
OR month= 12;

Ficou um pouco verboso. Uma maneira mais eficiente de fazer isso é usando uma instrução IN que fornece uma lista válida de valores:

SELECT * FROM station_data
WHERE month IN (3,6,9,12);

Se quiséssemos todos os dados exceto os relativos aos meses 3, 6, 9 e 12, poderíamos usar NOT IN:

SELECT * FROM station_data
WHERE month NOT IN (3,6,9,12);

Também podemos usar outras expressões matemáticas em suas instruções WHERE. Se quiséssemos só meses divisíveis por 3 (os meses “do fim de um trimestre”), poderíamos usar o operador de módulo (%). Isso signifca “retorne todos os meses em que a divisão por 3 forneça 0 de resto”:

SELECT * FROM station_data
WHERE month % 3 = 0;

O Oracle não dá suporte ao operador de módulo. Em vez disso, ele usa a função MOD().

Usando WHERE

Se quiséssemos filtrar um report_code (código de relatório) específico, poderia executar essa consulta:

SELECT * FROM station_data
WHERE report_code = ‘513A63’;

Observe que, já que o campo report_code é de texto (e não numérico), precisamos inserir ‘513A63’ em aspas simples para qualificá-lo como tal.

SELECT * FROM station_data
WHERE report_code IN (‘513A63’,’1F8A7B’,’EF616A’);

Função lenght()

Outra função de texto útil é length() conta o número de caracteres de um valor específico.

SELECT * FROM station_data
WHERE length(repord_code) != 6;

Curinga LIKE
Outra operação comum é o uso de curingas em uma expressão LIKE, onde % significa qualquer número de caracteres e _ um único caractere. Os outros caracteres são interpretados literalmente.

Então, se você quisesse encontrar todos os códigos de relatório que começassem com a letra “A”, executaria essa instrução para encontrar a letra “A” seguida por quaisquer que fossem os outros caracteres:

SELECT * FROM station_data
WHERE report_code LIKE ‘A%’;

Se quisesse encontrar todos os códigos de relatório que tivessem um “B” como o primeiro caractere e um “C” como o segundo, precisaria especificar um underscore (_) para a segunda posição e qualquer número de caracteres após o “C”:

SELECT * FROM station_data
WHERE report_code LIKE ‘B_C%’;

Usando WHERE com booleanos

Booleanos são valores de tipo verdadeiro/falso.

SELECT * FROM station_data
WHERE tornado = true AND hail = true;

O SQLite, no entanto, não permite isso. Ele demanda o uso explícito de 1 para verdadeiro e 0 para falso. Se você quisesse todos os registros em que aparecessem tornado e granizo, executaria essa instrução:

SELECT * FROM station_data
WHERE tornado = 1 AND hail = 1;

Pomodemos obter os mesmos resultados executando a consulta a seguir:

SELECT * FROM station_data
WHERE tornado AND hail;

Porém, a qualificação de condições falsas precisa ser explícita. Para obter todos os registros sem tornado e com granizo, execute essa consulta:

SELECT * FROM station_data
WHERE tornado = 0 AND hail = 1;

Também podemos usar a palavra-chave NOT para qualificar tornado como falso:

SELECT * FROM station_data
WHERE NOT tornado AND hail;

Manipulando NULL

O valor nulo é aquele que não apresenta valor.Em linguagem coloquial, seria um valor “em branco”.

tabela station_data possui valores NULL

Para manipular valores NULL precisamos usar as instruções IS NULL ou IS NOT NULL para identificar valores nulos. Portanto, para obter todos os registros sem definição de profundidade da neve (snow_depth), poderíamos executar essa consulta:

SELECT * FROM station_data
WHERE snow_depth IS NULL;

Os valores nulos podem ser muito difíceis de manipular na composição de instruções WHERE. Se você quisesse consultar todos os registros em que precipitation fosse menor que 0,5, poderia escrever essa instrução:

SELECT * FROM station_data
WHERE precipitation <= 0.5;

Porém, considerou os valores nulos? E se quisesse que nulos fossem incluídos nessa consulta? Logo, é preciso usar OR para incluir nulos:

SELECT * FROM station_dataWHERE precipitation IS NULL OR precipitation <= 0.5;

Manipulando nulos com coalesce()

Uma maneira mais elegante de manipular valores nulos é usando a função coalesce(), que converte um valor possivelmente nulo em um valor-padrão. Caso contrário, ela deixa o valor no estado em que se encontra.

  • O primeiro argumento é o valor possivelmente nulo
  • o segundo é o valor a usado se ele for realmente nulo.

Dessa forma, se quiséssemos que todos os nulos fossem tratados como 0 dentro de nossa condição, por intermédio de coalesce() poderíamos fazer o campo precipitation converter nulo em 0:

SELECT * FROM station_dataWHERE coalesce(precipitation, 0) <= 0.5;

Como qualquer função, coalesce() também pode ser usada na instrução SELECT, sendo útil para melhorar a aparência de um relatório sem exibir valores nulos e substituindo por placeholders (0, “N/A” ou “None” ):

SELECT report_code, coalesce(precipitation, 0) AS rainfall
FROM station_data;

Agrupando condições

Quando encadear instruções AND e OR, agrupe-as cuidadosamente.

SELECT * FROM station_data
WHERE rain = 1 AND temperature <= 32
OR snow_depth > 0;

No entanto, há um problema aqui. Embora tecnicamente essa operação funcione, há alguma ambiguidade. Isso ocorre porque a resposta à pergunta “Que condições pertencem a AND e quais pertencem a OR?” não está clara.

É por isso que é melhor agrupar as condições explicitamente em parênteses:

SELECT * FROM station_data
WHERE (rain = 1 AND temperature <= 32)
OR snow_depth > 0

O agrupamento com parênteses em instruções WHERE torna a semântica mais clara e a execução mais segura. Qualquer coisa que estiver em parênteses é calculada antes. Quando escrever condições WHERE complicadas, essa prática será ainda mais crítica.

3. GROUPB BY e ORDER BY

Também chamada de totalização, resumo ou agrupamento de dados, a agregação cria algum tipo de total a partir de vários registros. Soma, mínimo, máximo, contagem e média são operações de agregação comuns.

SELECT COUNT(*) AS record_count FROM station_data;

COUNT(*) significa contar os registros. Para contar o número de registros em que um tornado estava presente, digite o seguinte:

SELECT COUNT(*) AS record_count FROM station_data
WHERE tornado = 1;

Identificamos três mil registros com tornados presentes. Porém, e se quiséssemos separar a contagem por ano?

SELECT year, COUNT(*) AS record_count FROM station_data
WHERE tornado = 1
GROUP BY year;
Contagem de tornados por ano

Podemos fatiar esses dados considerando mais de um campo. Se quiséssemos uma contagem por ano e mês, também poderíamos fazer o agrupamento considerando o campo month (Figura 6.2):

SELECT year, month, COUNT(*) AS record_count FROM station_data
WHERE tornado = 1
GROUP BY year, month
Contagem por ano e mês

Alternativamente, podemos usar posições ordinais em vez de especificar as colunas em GROUP BY. Logo, em vez de escrever GROUP BY year, month, poderíamos usar GROUP BY 1, 2:

SELECT year, month, COUNT(*) AS record_count FROM station_data
WHERE tornado = 1
GROUP BY 1, 2;

Ordenando registros

month não está na ordem natural esperada. Essa á uma boa oportunidade para examinarmos o operador ORDER BY, após as instruções WHERE e GROUP BY. Se fosseclassificar por ano, e depois por mês, só teria que adicionar esse comando:

SELECT year, month, COUNT(*) AS record_count FROM station_data
WHERE tornado = 1
GROUP BY year, month
ORDER BY year, month BY 1, 2

No entanto, é provável que esteja mais interessado em dados recentes e que eles apareçam primeiro. Por padrão, a classificação é executada com o operador ASC, que ordena os dados em ordem crescente.

Para classificar em ordem decrescente, aplique o operador DESC à ordem de year para fazer os registros mais recentes aparecerem no início dos resultados:

SELECT year, month, COUNT(*) AS record_count FROM station_data
WHERE tornado = 1
GROUP BY year, month
ORDER BY year DESC, month;

Funções de agregação

Primeriamente, examinaremos outra maneira de usar COUNT(). Ao especificar uma coluna em vez de *, ela contará quantos valores não nulos existem nessa coluna. Poderíamos fazer a contagem de registros de snow_depth, que retornaria o número de valores não nulos :

SELECT COUNT(snow_depth) as recorded_snow_depth_count
FROM STATION_DATA;
Contagem de registros não nulos para coluna específica

Se quiser calcular a temperatura média de cada mês a partir do ano 2000, poderia fazer uma filtragem aceitando apenas o ano 2000 e anos posteriores, agrupar por mês e calcular a média da temperatura :

SELECT month, AVG(temperature) as avg_temp
FROM station_data
WHERE year >= 2000
GROUP BY month;
Temperatura média por mês desde 2000

Podemos usar funções nos valores agregados e executar tarefas como a de arredondamento para lhes dar uma aparência melhor:

SELECT month, round(AVG(temperature),2) as avg_temp
FROM station_data
WHERE year >= 2000
GROUP BY month
Arredondando a temperatura média por mês

SUM() é outra operação comum de agregação. Para calcular a soma dos valores de profundidade da neve por ano desde 2000:

SELECT year, SUM(snow_depth) AS total_snow
FROM station_data
WHERE year >= 2000
GROUP BY year;

Na consulta a seguir estamos calculando total_snow e total_precipitation para cada ano desde o ano 2000 na mesma consulta:ax_precipitation:

SELECT year,
SUM(snow_depth) AS total_snow,
SUM(precipitation) AS total_precipitation,
MAX(precipitation) AS max_precipitation
FROM station_data
WHERE year >= 2000
GROUP BY year

Podemos calcular alguns totais muito específicos usando WHERE. Se quiséssemos calcular a precipitação total por ano somente quando um tornado estivesse presente (verdadeiro):

SELECT year,
SUM(precipitation) AS tornado_precipitation
FROM station_data
WHERE tornado = 1
GROUP BY year;

Instrução HAVING

Filtrar registros de acordo com um valor agregado. Inicialmente, poderia ficar tentado a usar uma instrução WHERE, mas isso não funcionará porque WHERE filtra registros e não agregações.

Se quiséssemos filtrar pelo valor de SUM(), seria preciso que a filtragem ocorresse após o cálculo. É aí que entra em cena HAVING:

SELECT year,
SUM(precipitation) AS total_precipitation
FROM station_data
GROUP BY year
HAVING total_precipitation > 30 tornado;

HAVING é o equivalente na agregação a WHERE. A palavra-chave WHERE filtra registros individuais, mas HAVING filtra agregações.

Se estivéssemos executando a consulta anterior em um banco de dados Oracle, precisaríamos escrevê-la dessa forma:

SELECT year,
SUM(precipitation) AStotal_precipitation
FROM station_data
GROUP BY year
HAVING SUM(precipitation) > 30 station_data;

Obtendo registros distintos

Sabemos que há 28.000 registros em nossa tabela station_data. Se quiséssemos obter uma lista distinta dos valores:

SELECT DISTINCT station_number FROM station_data;station_number FROM station_data;

Ou resultados distintos para mais de uma coluna:

SELECT DISTINCT station_number, year FROM station_data

Resumo Neste capítulo aprendemos como agregar e classificar dados usando GROUP BY e ORDER BY. Também empregamos as funções de agregação SUM(), MAX(), MIN(), AVG() e COUNT() para reduzir milhares de registros a apenas alguns registros totalizados de maneira significativa. Já que não podemos usar WHERE para filtrar campos agregados, usamos a palavra-chave HAVING para fazer isso. Também utilizamos o operador DISTINCT para obter resultados distintos em nossas consultas e eliminar duplicatas.

Espero que a esta altura você já tenha percebido a flexibilidade que o SQL oferece para o desenvolvimento rápido de relatórios significativos baseados em milhares ou milhões de registros. Antes de prosseguir, eu recomendaria que você fizesse testes com tudo que aprendeu até agora e empregasse SELECT, WHERE e GROUP BY em suas consultas. Faça a si próprio perguntas úteis como “A temperatura vem aumentando em todo mês de janeiro nos últimos 20 anos?” ou “Quantas vezes tivemos e não tivemos granizo durante um tornado?”. Tente criar consultas SQL com os dados climáticos para responder a essas perguntas.

Familiarize-se com o que aprendeu até aqui, mas não se preocupe em memorizar todas as funcionalidades do SQL. Isso virá com o tempo à medida que você usar e testar repetidamente a linguagem. Mais conhecimentos serão adquiridos nos próximos capítulos e não há problemas em recorrer ao Google ou a este guia se você esquecer como compor as instruções.

Composing a repository of books (i bought), authors (i follow) & blogs (direct ones) for my own understanding.

Get the Medium app