19 exercícios práticos de PostgreSQL com soluções detalhadas

Você provavelmente já ouviu o ditado "a prática leva à perfeição". Descubra se essa frase é verdadeira experimentando o conjunto abrangente de exercícios práticos de PostgreSQL que você encontrará neste artigo!

Para dominar qualquer idioma, é essencial praticá-lo regularmente para não perder a habilidade. Isso vale tanto para a comunicação verbal quanto para a programação. E a programação SQL não é exceção. Até mesmo o programador PostgreSQL mais experiente precisa praticar diariamente o PostgreSQL para aperfeiçoar (e manter) suas habilidades.

Os iniciantes em PostgreSQL devem desenvolver suas habilidades por meio de exercícios simples que lhes permitam incorporar gradualmente novos conhecimentos. Neste artigo, você encontrará uma série de exercícios práticos de PostgreSQL que o ajudarão a aprender rapidamente como consultar bancos de dados PostgreSQL. Você pode encontrar mais exercícios práticos de SQL para iniciantes neste artigo.

Para trabalhar nesses exercícios, você precisará de um servidor PostgreSQL onde possa criar um banco de dados, adicionar tabelas a ele e preenchê-lo com dados. Você também precisará de um aplicativo cliente que lhe permita executar comandos SQL no servidor PostgreSQL. Com esses elementos, você pode criar as tabelas de exemplo que lhe permitirão fazer todos os exercícios práticos do PostgreSQL ao longo deste artigo. Se precisar de ajuda, leia este artigo sobre como criar seu próprio banco de dados de prática de SQL.

Há muitas maneiras de praticar SQL on-line. A que eu recomendo especificamente para usuários do PostgreSQL é o nosso site interativo Curso de Práticas em SQL in PostgreSQL. Nele, você encontrará 88 exercícios que vão de conceitos simples a avançados. As tabelas de exemplo usadas neste artigo já estão criadas e preenchidas; você pode escrever e executar todos os exercícios de consulta que desejar e ver como eles funcionam. Os exercícios deste artigo foram extraídos diretamente deste curso. Se você os considerar úteis, pode ter certeza de que também achará o restante do curso útil. Não se preocupe: você pode cometer todos os tipos de erros - não há nada que você possa quebrar e ninguém vai ficar com raiva de você por executar consultas estranhas.

Por fim, mantenha nossa folha de consulta gratuita do PostgreSQL à mão enquanto você lê este artigo. Esse guia rápido resume os principais comandos e recursos do PostgreSQL.

Agora, vamos começar a praticar o PostgreSQL!

Conjunto de dados do PostgreTrilha de Práticas em SQL

A tabela principal que usaremos para os exercícios do PostgreSQL neste artigo contém informações sobre gatos - nome, raça, idade etc. O nome da tabela é simplesmente cat, e sua estrutura é a seguinte:

• id: Um valor numérico que identifica cada gato representado na tabela.
• name: O nome do gato.
• breed
• coloration
• age
• Fav_toy: Tipo de brinquedo favorito do gato.

Aqui está uma amostra dos dados:

id	name	breed	coloration	age	fav_toy
1	Micky	Maine Coon	tortoiseshell	3	ball - red
2	Nine	Ragamuffin	cream	2	ball - green
3	Carmen	Persian	brown	2	toy mouse
4	Luna	Abyssinian	tortoiseshell	12	teaser
5	Bella	Siamese	red	15	teaser

Exercício 1: Conhecendo os dados

Exercício: Obtenha todas as informações da tabela cat.

Solução:

SELECT *
FROM cat;

Explicação: O comando SELECT * FROM cat solicita ao PostgreSQL que busque todas as colunas e todas as linhas da tabela cat tabela. A chave para obter todas as colunas é SELECT *. O "*" funciona como um curinga: quando você não sabe os nomes das colunas, SELECT * retornará todas elas.

Com a cláusula FROM cat você está dizendo ao PostgreSQL que deseja obter informações da tabela cat da tabela. Ao não definir nenhuma condição adicional, você obterá todas as linhas. O ponto e vírgula no final do comando é simplesmente uma indicação de que o comando termina ali.

Exercício 2: Seleção de colunas específicas de uma tabela

Exercício: Selecione somente as colunas name e idade da tabela cat.

Solução:

SELECT name, age
FROM cat;

Explicação: No exercício anterior, você usou o curinga "*" para selecionar todas as colunas de uma tabela. Neste exercício, você só quer ver o nome e a idade de cada gato. Em vez de usar o curinga "*", basta especificar os nomes das colunas name e age após o comando SELECT. Separe as colunas com uma vírgula.

Exercício 3: Coleta de informações sobre gatos siameses

Exercício: Obter todas as informações da tabela cat sobre gatos siameses.

Solução:

SELECT *
FROM cat
WHERE breed = 'Siamese';

Explicação: Para aplicar um filtro nas linhas selecionadas, use a cláusula WHERE. Isso permite que você especifique uma condição que as linhas devem atender para serem incluídas no conjunto de resultados. Neste exercício, a condição WHERE solicita que o banco de dados retorne informações somente sobre gatos siameses.

A condição especificada na cláusula WHERE é (em geral) uma condição lógica que pode ser avaliada como verdadeira ou falsa. Em sua forma mais simples, ela indica o nome de uma das colunas da tabela (breed), depois um operador de comparação (=) e um valor para comparação ('Siamese'). Observe que todos os literais de cadeia de caracteres devem ser colocados entre aspas.

Exercício 4: Como encontrar os gatos mais jovens

Exercício: Obtenha as linhas da tabela cat que correspondam a indivíduos com menos de 8 anos de idade.

Solução:

SELECT *
FROM cat
WHERE age < 8;

Explicação: No exercício anterior, vimos como filtrar os resultados da consulta comparando uma coluna de texto (breed) com um valor literal ('Siamese'). Isso incluiu somente as linhas em que o valor da coluna corresponde ao literal de cadeia de caracteres especificado.

Neste exercício, você também precisa especificar uma condição na cláusula WHERE. Entretanto, agora você está aplicando a condição a um valor numérico e usando o operador de comparação de não igualdade < (less than).

Para obter uma lista de todas as linhas na tabela cat para gatos com menos de 8 anos de idade, você deve usar a coluna idade na cláusula WHERE. Você a combina com o operador de comparação < e o valor numérico 8.

Da mesma forma, você pode usar critérios diferentes para comparar valores numéricos. Para ver os gatos com mais de 8 anos, por exemplo, basta alterar a condição na cláusula WHERE para age > 8.

Exercício 5: Procurando por gatos siameses jovens

Exercício: Obter linhas que correspondam a gatos siameses com menos de 8 anos de idade.

Solução:

SELECT *
FROM cat
WHERE breed = 'Siamese'
AND age < 8;

Explicação: Muitas vezes, você precisará obter linhas que atendam a mais de uma condição. Para conseguir isso, todas as condições devem ser especificadas na cláusula WHERE e unidas pela conjunção AND. Como resultado dessa consulta, o PostgreSQL retornará um conjunto de linhas para gatos siameses com menos de 8 anos de idade.

Além da conjunção AND, há outras maneiras de combinar condições em declarações lógicas que são avaliadas como verdadeiras ou falsas.

Exercício 6: Uso de condições alternativas (uma ou outra)

Exercício: Obtenha uma lista de gatos persas ou siameses na tabela cat tabela.

Solução:

SELECT id, name
FROM cat
WHERE breed = 'Siamese'
OR breed = 'Persian';

Explicação: As condições combinadas pelo operador lógico OR retornam True quando pelo menos uma das expressões é True. Neste exercício, usamos o operador OR para obter dados de gatos siameses ou persas.

Exercício 7: Combinação de diferentes condições lógicas

Exercício: Obtenha uma lista de gatos siameses ou persas da tabela cat que tenham menos de 5 anos de idade ou mais de 10 anos de idade.

Solução:

SELECT id, name
FROM cat
WHERE (breed = 'Siamese' OR breed = 'Persian')
AND (age < 5 OR age > 10);

Explicação: Para obter dados de uma tabela que atenda a uma combinação de condições alternativas ou obrigatórias, você deve combinar os operadores lógicos OR e AND. Fazemos isso com parênteses, para que o PostgreSQL entenda corretamente a precedência das condições.

Deve-se tomar cuidado quando os operadores OR e AND são colocados na mesma cláusula WHERE. O PostgreSQL combinará as condições lógicas na ordem em que elas aparecem , a menos que parênteses sejam utilizados. Se forem utilizados parênteses (como no exemplo acima), o resultado do que está entre parênteses é avaliado primeiro; em seguida, esses resultados são combinados com o operador que os conecta.

Neste exercício, primeiro é avaliada a raça do gato. Se ele atender a uma das duas condições do WHERE, o banco de dados avaliará o próximo conjunto de condições para verificar se o gato atende aos requisitos de idade. Somente os gatos que atendem a uma condição de raça e a uma condição de idade são incluídos no resultado.

Exercício 8: Listagem de gatos de diferentes raças

Exercício: Obter uma lista de gatos Persas, Siameses ou Ragdoll sem usar o operador OR.

Solução:

SELECT id, name
FROM cat
WHERE breed IN ('Persian', 'Siamese', 'Ragdoll');

Explicação: Às vezes, é necessário filtrar os resultados de uma consulta por um conjunto de valores. Isso pode ser feito especificando várias condições conectadas por operadores OR, como esta:

SELECT name
FROM cat
WHERE breed = 'Persian' OR breed = 'Siamese' OR breed = 'Ragdoll';

Mas, para simplificar uma consulta com muitas enumerações, é preferível usar o operador lógico IN, que avalia a associação de um valor a um conjunto. Nesse caso, o valor seria a raça da coluna e o conjunto seria uma lista de valores ('Persian', 'Siamese', 'Ragdoll').

O operador IN torna as consultas complexas mais fáceis de ler e entender.

Exercício 9: Pesquisa de correspondências parciais

Exercício: Obter todas as linhas da tabela cat correspondentes aos gatos cujo brinquedo favorito é uma bola de qualquer cor.

Solução:

SELECT *
FROM cat
WHERE fav_toy LIKE '%ball%';

Explicação: Quando você tenta pesquisar correspondências em colunas que contêm descrições, geralmente não sabe exatamente como as descrições estão armazenadas. Às vezes, você precisa pesquisar uma palavra específica que pode aparecer em uma descrição. Nessas situações, o operador de comparação = não é útil, pois ele retorna apenas as linhas em que a correspondência é exata.

Quando você precisa encontrar correspondências parciais, o PostgreSQL permite que você pesquise fragmentos de texto nas colunas varchar. O truque é substituir o operador = por LIKE, adicionando curingas para representar as partes do texto que você não conhece.

Na tabela cat a coluna fav_toy armazena "ball" (bola) e sua cor, por exemplo, "red ball" (bola vermelha) ou "green ball" (bola verde). Com o operador LIKE, você pode solicitar ao PostgreSQL que retorne as linhas em que fav_toy contém a palavra "ball".

O símbolo % colocado em ambos os lados da palavra "ball" funciona como um curinga, indicando que pode haver qualquer texto ao redor de "ball". As linhas resultantes são aquelas em que a palavra "ball" aparece como parte do texto na coluna fav_toy, independentemente de qualquer outro texto que ela contenha.

Exercício 10: Como encontrar valores NULL em determinadas colunas

Exercício: Obtenha todas as linhas da tabela cat correspondentes aos gatos que não têm um brinquedo favorito.

Solução:

SELECT name
FROM cat
WHERE fav_toy IS NULL;

Explicação: Há situações em que você precisa obter as linhas de uma tabela para as quais não há informações em uma determinada coluna. Em outras palavras, você precisa dizer ao banco de dados para buscar apenas as linhas em que uma determinada coluna tenha um valor NULL. Você não pode usar operadores de comparação para isso; no SQL, qualquer comparação envolvendo valores NULL sempre produz o valor lógico False.

A única maneira de dizer ao banco de dados para buscar essas linhas é especificar que o valor da coluna IS NULL: fav_toy IS NULL.

Lembre-se de que a palavra NULL em SQL se refere a um valor desconhecido. Não é o mesmo que o valor 0 em uma coluna numérica ou uma cadeia de caracteres de comprimento zero em uma coluna varchar. Qualquer operação numérica, de data ou de cadeia de caracteres que envolva um valor NULL também resulta em um valor NULL.

Exercício 11: Como evitar linhas sem dados em determinadas colunas

Exercício: Obtenha todas as linhas da tabela cat correspondentes aos gatos que têm um brinquedo favorito.

Solução:

SELECT name
FROM cat
WHERE fav_toy IS NOT NULL;

Explicação: Sabemos como obter linhas em que uma coluna tem um valor NULL. Mas e se você precisar do oposto, ou seja, obter todas as linhas com qualquer valor nessa coluna? A condição IS NOT NULL faz exatamente o oposto de IS NULL: ela retorna todas as linhas que têm algum dado (qualquer dado) na coluna fav_toy.

Exercício 12: Obtendo uma lista de gatos ordenados por idade

Exercício: Selecione o nome e a idade na tabela cat certificando-se de que os resultados sejam ordenados por idade.

Solução:

SELECT name, age
FROM cat
ORDER BY age;

Explicação: Ao executar uma consulta SELECT em um banco de dados SQL, é impossível prever em que ordem os resultados aparecerão, a menos que você especifique um critério de ordenação. Se quiser obter os resultados de uma consulta em uma ordem específica, é necessário especificar as colunas que determinarão a ordem usando uma cláusula ORDER BY.

Exercício 13: Classificação de gatos por raça e idade

Exercício: Selecione a raça, o nome e a idade de todas as linhas da tabela cate obter os resultados ordenados primeiro por raça e depois por idade.

Solução:

SELECT breed, name, age
FROM cat
ORDER BY breed, age;

Explicação: Você pode usar a cláusula ORDER BY para indicar uma ou mais colunas como critério de classificação. A consulta deste exercício classifica os resultados por raça; dentro de cada raça, as linhas são classificadas por idade.

Exercício 14: Uso de ordenação ascendente e descendente

Exercício: Selecione a raça, o nome e a idade de todos os gatos e, em seguida, classifique os resultados primeiro por raça em ordem alfabética e, depois, por idade, do mais velho ao mais novo.

Solução:

SELECT breed, name, age
FROM cat
ORDER BY breed ASC, age DESC;

Explicação: A cláusula ORDER BY também permite que você especifique a ordem ascendente ou descendente para cada uma das colunas de classificação. A palavra ASC indica ordem ascendente (A a Z, 1 a 10); DESC indica ordem descendente (Z a A, 10 a 1). Se a ordem de classificação não for especificada, será assumida a ordem ascendente. Para dados alfanuméricos, a ordem crescente é a mesma que a ordem alfabética.

A consulta deste exercício classifica as linhas em ordem alfabética por raça (em ordem crescente) e depois por idade em ordem decrescente.

Exercício 15: Agrupamento e agregação de dados

Exercício: Selecione as raças de gatos da tabela cat e o número de gatos de cada raça.

Solução:

SELECT breed, COUNT(*)
FROM cat
GROUP BY breed;

Explicação: A cláusula GROUP BY agrupa as linhas com base em um valor de coluna (GROUP BY breed); uma linha é retornada para cada valor de grupo (por exemplo, a tabela cats teria uma linha para Siamês, uma para Ragdoll, etc.)

Para obter o número de gatos de cada raça, usamos a função de agregação COUNT(*), que retorna o número de linhas em cada grupo.

Ao usar a cláusula GROUP BY, é importante observar que as colunas da lista SELECT que não são usadas em uma função agregada também devem aparecer na lista de colunas da cláusula GROUP BY.

Se você achar que precisa de mais prática para entender completamente o agrupamento no SQL, tente estes 10 exercícios práticos de GROUP BY.

Exercício 16: Obter a idade do gato mais novo e do mais velho de cada raça

Exercício: Selecione uma lista das raças de gatos da tabela cat juntamente com a idade máxima e mínima de cada raça.

Solução:

SELECT breed, MIN(age), MAX(age)
FROM cat
GROUP BY breed;

Explicação: O agrupamento nos permite obter dados resumidos. Além de obter o número de elementos em cada grupo (como fizemos no exercício anterior), podemos usar funções para calcular totais, médias e valores máximo/mínimo para cada grupo.

Neste exercício, as funções MIN() e MAX() são usadas para obter as idades máxima e mínima de cada raça.

Exercício 17: Agrupamento por várias colunas

Exercício: Selecione uma lista de raças de gatos e diferentes colorações na tabela cat tabela, contando o número de gatos de cada combinação de raça e coloração.

Solução:

SELECT 
  breed, 
  coloration, 
  COUNT(*)
FROM cat
GROUP BY breed, coloration;

Explicação: Talvez você queira agrupar dados por mais de uma coluna, criando uma hierarquia de vários critérios de agrupamento aninhados. Isso pode ser feito detalhando todos os campos de agrupamento na cláusula GROUP BY.

A ordem em que esses campos são escritos determinará a aparência da hierarquia de agrupamento. Neste exercício, obtemos os dados agrupados por raça e, em seguida, por coloração dentro de cada raça. E a função COUNT(*) mostra quantos gatos existem na tabela para cada combinação de raça e coloração.

Exercício 18: Definição de condições em dados agregados

Exercício: Obtenha uma lista das raças que são representadas por mais de um registro na tabela cat tabela.

Solução:

SELECT breed, COUNT(*)
FROM cat
GROUP BY breed
HAVING COUNT(*) > 1;

Explicação: Vimos que o site WHERE permite que você defina condições nas linhas retornadas pela consulta. Mas talvez você precise filtrar as linhas agrupadas definindo condições nos resultados das funções de agregação.

Para fazer isso, defina a condição de que o resultado da função COUNT(*) deve ser maior que 1. Mas você não pode especificar isso na cláusula WHERE; ela é computada em linhas individuais antes que GROUP BY seja processado pelo banco de dados. Portanto, você deve usar a cláusula HAVING, como fizemos neste exercício.

Exercício 19: Obtenção de dados relacionados de tabelas diferentes

Exercício: Selecione a origem da raça de cada gato na tabela cat obtendo-a da tabela relacionada breed_origin tabela relacionada. Aqui está um instantâneo dos dados na tabela breed_origin tabela:

breed	origin
Ragdoll	California
Persian	Unknown
Ragamuffin	California
Maine Coon	Maine
Siamese	Thailand
Abyssinian	Southeast Asia

Solução:

SELECT cat.name, cat.breed, breed_origin.origin
FROM cat JOIN breed_origin
ON cat.breed = breed_origin.breed;

Explicação: Se você precisar de uma consulta que retorne a origem da raça para cada um dos gatos, deverá unir a tabela cat com a tabela breed_origin tabela. Para isso, você deve especificar as duas tabelas na cláusula FROM - separadas pela palavra JOIN.

A condição de união é especificada após os nomes das tabelas e precedida pela palavra ON. É necessário definir essa condição para que o banco de dados saiba como fazer a correspondência correta das linhas. Nesse caso, a condição é que a coluna de raça de cat seja igual à coluna de raça de breed_origin.

Como há mais de uma tabela na cláusula FROM, é melhor colocar o nome da tabela antes do nome da coluna, separando-os com um ponto (por exemplo, cat.breed). Isso evita ambiguidade tanto para o banco de dados quanto para o leitor. Não é obrigatório fazer isso para todas as colunas, mas é essencial para colunas que compartilham o mesmo nome em ambas as tabelas.

No exemplo acima, a coluna breed está em ambas as tabelas cat e breed_originportanto, é obrigatório prefixar o nome da tabela sempre que você se referir a uma das colunas.

Depois de entender como unir tabelas no SQL, você pode fortalecer seu conhecimento concluindo estes 12 exercícios práticos de JOIN.

Pronto para os exercícios avançados do PostgreTrilha de Práticas em SQL?

O que você achou desse conjunto de exercícios práticos do PostgreSQL para iniciantes? Lembre-se de que você pode continuar praticando com os 88 exercícios do curso interativo Curso de Práticas em SQL in PostgreSQL, que é apenas um dos 65 cursos práticos de SQL para iniciantes e especialistas que você pode encontrar em LearnSQL.com.br.

Se você achou os exercícios sugeridos para o PostgreSQL muito fáceis, talvez esteja pronto para começar a aprender PostgreSQL avançado. Tenha em mente que qualquer esforço que você fizer para aprender PostgreSQL terá sido bem investido, pois esse dialeto da linguagem SQL está crescendo rapidamente e abrirá as portas para muitas oportunidades de trabalho interessantes. Continue praticando e aprendendo, e você manterá suas habilidades em SQL atualizadas.