Prática de consulta SQL básica on-line: 20 exercícios para iniciantes

Esses 20 exercícios são exatamente o que os iniciantes precisam para praticar consultas SQL. Tente resolver cada um deles e, em seguida, dê uma olhada nas soluções. Se algo precisar ser esclarecido, há explicações para cada solução.

Neste artigo, falaremos menos do que o normal. Em vez disso, você escreverá as respostas para as consultas práticas de SQL. (Não se preocupe; incluímos as soluções se você ficar preso.) O objetivo é dar a você, como iniciante, muitas oportunidades de praticar consultas SQL.

Selecionei vinte exemplos do nosso site Exercícios SQL Básico: Treine como um atleta Se você achar que precisa praticar mais até o final do artigo - recomendo de todo o coração! -, encontrará quase 100 outros exercícios interativos de SQL nesse curso. Eles abrangem tópicos como consulta a uma tabela, usando JOINs, classificação de dados com ORDER BY, agregação de dados e uso de GROUP BY, tratamento de NULLs, realização de operações matemáticas e criação de subconsultas.

Todos esses são tópicos que qualquer iniciante em SQL deve conhecer bem antes de passar para os tópicos mais complexos. A melhor maneira de aprender qualquer coisa em SQL é escrever consistentemente seu próprio código. Dessa forma, você dominará a sintaxe do SQL e seus recursos básicos; além disso, compreenderá a solução de problemas. Afinal, o objetivo de saber SQL é saber como usar os dados para resolver problemas.

E você pode ir ainda mais longe! Temos o curso Trilha de Práticas em SQL e o curso Monthly Trilha de Práticas em SQL para praticar ainda mais as consultas SQL.

Dito isso, vamos nos aprofundar na prática do SQL, começando com o conjunto de dados.

• Conjunto de dados
• Exercício 1: Mostrar as datas finais de todos os eventos e os pontos de vento
• Exercício nº 2: Mostrar todas as finais em que o vento foi superior a 0,5 pontos
• Exercício nº 3: Mostrar todos os dados de todas as maratonas
• Exercício nº 4: Mostrar todos os resultados finais de corredores que não estão participando de corridas
• Exercício nº 5: Mostrar todos os dados de resultados para corredores que não estão começando
• Exercício nº 6: Mostrar os nomes das corridas masculinas com menos de 500 metros
• Exercício nº 7: Classificar nomes e abreviações de países
• Exercício nº 8: Classificar o nome e o sobrenome dos atletas
• Exercício nº 9: Classificar os resultados finais em três horas
• Exercício nº 10: Mostrar os nomes e os locais dos 3 principais atletas
• Exercício 11: Mostrar todas as maratonas com o nome da competição, o ano da competição e o nome da disciplina
• Exercício nº 12: Mostrar as pontuações de Mo Farah em todas as disciplinas
• Exercício 13: Mostrar os nomes das competições e o número de eventos
• Exercício 14: Mostrar os nomes dos atletas mais populares
• Exercício 15: Mostre cada país e o número de atletas que terminaram sem classificação
• Exercício 16: Calcular o ritmo médio de cada corrida
• Exemplo nº 17: Encontrar todos os tempos mais rápidos do que a média para corridas de 1.500 metros
• Exercício nº 18: Encontre todos os atletas que participaram de pelo menos dois eventos em uma competição
• Exercício nº 19: Mostrar corredores que só terminaram em primeiro lugar
• Exercício nº 20: Encontrar todos os atletas que não começaram e que ganharam pelo menos uma vez

Prática de consulta SQL

Conjunto de dados

O conjunto de dados contém dados sobre as finais de competições de corrida de pista em campeonatos de atletismo: Jogos Olímpicos do Rio de Janeiro em 2016, Campeonato Mundial de Atletismo da IAAF em Londres em 2017 e Campeonato Mundial de Atletismo da IAAF em Doha em 2019.

Os dados são armazenados em seis tabelas: competition, event, discipline, final_result, athlete, e nationality. O esquema é mostrado abaixo:

As informações da competição são armazenadas na tabela competition. Ela tem as seguintes colunas:

• id - O ID da competição e a chave primária da tabela.
• name - O nome da competição.
• start_date - O primeiro dia da competição.
• end_date - O último dia da competição.
• year - O ano em que a competição ocorreu.
• location - O local da competição.

Aqui estão os dados da tabela.

id	name	start_date	end_date	year	location
7093747	Rio de Janeiro Olympic Games	2016-08-12	2016-08-21	2016	Estádio Olímpico, Rio de Janeiro (BRA)
7093740	London IAAF World Championships in Athletics	2017-08-04	2017-08-13	2017	Olympic Stadium, London (GBR)
7125365	IAAF World Championships in Athletics	2019-09-27	2019-10-06	2019	Khalifa International Stadium, Doha (QAT)

A tabela discipline contém informações de todas as modalidades de corrida. Ela tem estas colunas:

• id - O ID da disciplina e a chave primária da tabela.
• name - O nome da disciplina.
• is_men - TRUE se for uma disciplina masculina, FALSE se for feminina.
• distance - A distância da disciplina, em metros.

Esse é um instantâneo das primeiras cinco linhas dos dados:

id	name	is_men	distance
1	Men's 100m	TRUE	100
2	Men's 200m	TRUE	200
3	Men's 400m	TRUE	400
4	Men's 800m	TRUE	800
5	Men's 1500m	TRUE	1,500

A próxima tabela é event, que armazena informações sobre cada evento específico:

• id - O ID do evento e a chave primária da tabela.
• competition_id - Vincula o evento a uma competição.
• discipline_id - Vincula o evento a uma disciplina.
• final_date - Quando a final desse evento foi realizada.
• wind - Os pontos de vento durante a final.

Aqui estão as cinco primeiras linhas dessa tabela:

id	competition_id	discipline_id	final_date	wind
1	7093747	1	2016-08-14	0.2
2	7093747	2	2016-08-18	-0.5
3	7093747	3	2016-08-14	0
4	7093747	4	2016-08-15	0
5	7093747	5	2016-08-20	0

Os dados sobre cada atleta estão na tabela athlete:

• id - O ID do atleta e a chave primária da tabela.
• first_name - O primeiro nome do atleta.
• last_name - O sobrenome do atleta.
• nationality_id - A nacionalidade do atleta.
• birth_date - A data de nascimento do atleta.

Essas são as cinco primeiras linhas:

id	first_name	last_name	nationality_id	birth_date
14201847	Usain	BOLT	1	1986-08-21
14238562	Justin	GATLIN	2	1982-02-10
14535607	André	DE GRASSE	3	1994-11-10
14201842	Yohan	BLAKE	1	1989-12-26

A tabela nationality A tabela contém informações sobre o país:

• id - O ID do país e a chave primária da tabela.
• country_name - O nome do país.
• country_abbr - A abreviação de três letras do país.

Aqui está um instantâneo de cinco linhas dessa tabela:

id	country_name	country_abbr
1	Jamaica	JAM
2	United States	USA
3	Canada	CAN
4	South Africa	RSA
5	Côte d’Ivoire	CIV

A última tabela é final_result. Ela contém informações sobre os participantes e seus resultados em um determinado evento:

• event_id - O ID do evento.
• athlete_id - O nome do atleta
• result - O tempo/pontuação do atleta (pode ser NULL).
• place - A colocação alcançada pelo atleta (pode ser NULL).
• is_dsq - TRUE se houvedesqualificação.
• is_dnf - TRUE se o atleta não terminoua corrida.
• is_dns - TRUE se o atleta não inicioua corrida.

Aqui está o instantâneo:

event_id	athlete_id	result	place	is_dsq	is_dns	is_dnf
1	14201847	0:00:10	1	FALSE	FALSE	FALSE
1	14238562	0:00:10	2	FALSE	FALSE	FALSE
1	14535607	0:00:10	3	FALSE	FALSE	FALSE
1	14201842	0:00:10	4	FALSE	FALSE	FALSE
1	14417763	0:00:10	5	FALSE	FALSE	FALSE

Agora que você já deu uma boa olhada no conjunto de dados, vamos começar a praticar nossa consulta SQL básica! Todos os exercícios exigirão que você saiba um pouco de SQL, portanto, certifique-se de conhecer todos os elementos básicos de uma consulta SQL.

Exercício 1: Mostrar as datas finais de todos os eventos e os pontos de vento

Exercício: Encontre as datas finais de todos os eventos e os pontos de vento.

Solução:

SELECT
  final_date,
  wind
FROM event;

Explicação: Os dados de que você precisa estão na tabela event. Você precisa selecionar duas colunas dela: final_date e wind. Você faz isso escrevendo a primeira coluna na instrução SELECT. Em seguida, você escreve o nome da segunda coluna e separa os nomes das colunas com uma vírgula.

Por fim, você faz referência à tabela na cláusula FROM.

Saída:

final_date	wind
2016-08-14	0.2
2016-08-18	-0.5
2016-08-14	0
2016-08-15	0
2016-08-20	0

Exercício nº 2: mostrar todas as finais em que o vento estava acima de 0,5 pontos

Exercício: Mostrar todas as datas das finais com vento superior a 0,5 ponto.

Solução:

SELECT final_date
FROM event
WHERE wind > 0.5;

Explicação: Primeiro, selecione a coluna final_date na tabela event. Com isso, você obterá uma lista de todas as finais. No entanto, você não precisa de toda a lista - apenas as finais em que o vento foi mais forte que 0,5.

Portanto, você precisa filtrar os dados usando a cláusula WHERE. Nela, você escreve o nome da coluna que deseja filtrar; nesse caso, é a coluna wind. Para obter o vento acima de 0,5, use o operador lógico 'greater than' (>).

Saída:

final_date
2017-08-11
2019-09-28
2019-10-02

Exercício nº 3: mostrar todos os dados de todas as maratonas

Exercício: Mostrar os dados da disciplina para todas as maratonas.

Solução:

SELECT *
FROM discipline	
WHERE name LIKE '%Marathon%';

Explicação: Para selecionar todas as colunas, não é necessário escrever seus nomes explicitamente. Há uma abreviação para "todas as colunas" chamada asterisco (*). Em vez dos nomes das colunas, basta colocar um asterisco em SELECT.

Então, quando você quiser dados da tabela disciplinevocê faz referência a eles em FROM.

Por fim, você precisa filtrar os dados. Use WHERE e o operador LIKE. Esse operador examina os dados textuais na coluna e retorna todas as linhas que contêm o texto na condição WHERE. Em outras palavras, a condição procurará a palavra "Marathon". Você deve colocar a palavra entre aspas simples.

Entretanto, você não sabe o nome exato da disciplina; sabe apenas que ela deve conter essa palavra. Ela pode estar em qualquer lugar do nome da disciplina: no início, no meio ou no fim. Para procurar em qualquer lugar da cadeia de caracteres, coloque o operador de módulo (%) antes e depois da palavra que está procurando.

Saída:

id	name	is_men	distance
8	Men's Marathon	TRUE	42,195
16	Women's Marathon	FALSE	42,195

Exercício nº 4: Mostrar todos os resultados finais para corredores que não estão se preparando

Exercício: Mostrar todos os dados dos resultados finais dos corredores que não se classificaram.

Solução:

SELECT *
FROM final_result
WHERE place IS NULL;

Explicação: Você precisa de todas as colunas, portanto, use um asterisco em SELECT e faça referência à tabela final_result em FROM.

Você precisa mostrar somente os resultados em que os corredores terminaram sem colocação. Desta vez, você também usará WHERE e filtrará a coluna place. Se um corredor terminar sem uma colocação, a coluna place estará vazia (ou seja, NULL). Você precisa do operador IS NULL após o nome da coluna para retornar todas essas linhas.

Seria uma boa ideia saber o que é NULL no SQL antes de usar o operador IS NULL.

Saída:

event_id	athlete_id	result	place	is_dsq	is_dns	is_dnf
6	14464221	NULL	NULL	TRUE	FALSE	FALSE
7	14530623	NULL	NULL	FALSE	FALSE	TRUE
7	14573513	NULL	NULL	FALSE	FALSE	TRUE
8	14167397	NULL	NULL	FALSE	FALSE	TRUE
8	14177784	NULL	NULL	FALSE	FALSE	TRUE

Exercício nº 5: mostrar todos os dados de resultados para corredores que não estão começando

Exercício: Mostrar todos os dados de resultados dos corredores que não iniciaram a corrida.

Solução:

SELECT *
FROM final_result
WHERE is_dns IS TRUE;

Explicação: Selecione todas as colunas da tabela final_result usando um asterisco e fazendo referência à tabela em FROM.

Em seguida, você deseja usar WHERE e filtrar a coluna por is_dns. Se o corredor não tiver iniciado a corrida, essa coluna terá o valor TRUE. Portanto, você precisa usar o operador IS TRUE após o nome da coluna.

Saída: Aqui está o resultado completo:

event_id	athlete_id	result	place	is_dsq	is_dns	is_dnf
14	14451797	NULL	NULL	FALSE	TRUE	FALSE
16	14296979	NULL	NULL	FALSE	TRUE	FALSE
19	14176330	NULL	NULL	FALSE	TRUE	FALSE
22	14367867	NULL	NULL	FALSE	TRUE	FALSE
24	14219653	NULL	NULL	FALSE	TRUE	FALSE
24	14225279	NULL	NULL	FALSE	TRUE	FALSE
32	14751813	NULL	NULL	FALSE	TRUE	FALSE
41	14291986	NULL	NULL	FALSE	TRUE	FALSE

Exercício nº 6: Mostrar nomes para corridas de disciplina masculina abaixo de 500 metros

Exercício: Mostrar somente os nomes das disciplinas masculinas em que a distância a ser percorrida é inferior a 500 metros.

Solução:

SELECT name
FROM discipline
WHERE is_men IS TRUE
  AND distance < 500;

Explicação: Primeiro, selecione o nome da coluna na tabela discipline.

Você precisa novamente filtrar os dados - desta vez, colocando duas condições em WHERE.

A primeira condição é que se trata de uma disciplina masculina. Portanto, você precisa filtrar a coluna is_men usando o operador IS TRUE. Em seguida, você adiciona a segunda condição: os valores na coluna distance devem estar abaixo de 500. Essa condição usa o operador menor que (<). Como ambas as condições precisam ser satisfeitas, separe as condições usando o operador AND.

Saída:

name
Men's 100m
Men's 200m
Men's 400m

Exercício nº 7: classificar nomes e abreviações de países

Exercício: Mostre os nomes e as abreviações de todos os países. Classifique o resultado em ordem alfabética pelo nome do país.

Solução:

SELECT
  country_name,
  country_abbr
FROM nationality
ORDER BY country_name ASC;

Explicação: Selecione o nome do país e sua abreviação na tabela nationality.

Para classificar a saída, use a cláusula ORDER BY. Você deseja classificar por nome de país, portanto, escreva country_name em ORDER BY. A saída deve ser classificada alfabeticamente, portanto, use a palavra-chave ASC (ascendente) após o nome da coluna.

Saída: Aqui estão as primeiras cinco linhas do resultado:

country_name	country_abbr
Afghanistan	AFG
Algeria	ALG
American Samoa	ASA
Andorra	AND
Angola	ANG

Exercício nº 8: Classificar o nome e o sobrenome dos atletas

Exercício: Mostre o nome e o sobrenome de cada atleta. Classifique o resultado de forma decrescente pelo primeiro nome do atleta. Se vários atletas tiverem o mesmo nome, mostre seus sobrenomes em ordem decrescente.

Solução:

SELECT
  first_name,
  last_name
FROM athlete
ORDER BY first_name DESC, last_name DESC;

Explicação: Selecione o primeiro e o último nome na tabela athlete.

Em seguida, adicione a cláusula ORDER BY. Primeiro, classifique pelo primeiro nome de forma decrescente, adicionando DESC após o nome da coluna. A segunda condição de ordenação classifica pelo sobrenome, também de forma decrescente. Novamente, escreva o nome da coluna e adicione DESC. As condições devem ser separadas por vírgula.

Saída: Aqui estão as primeiras cinco linhas do resultado:

first_name	last_name
Zsófia	ERDÉLYI
Zouhair	AWAD
Zoey	CLARK
Zoe	HOBBS
Zoe	BUCKMAN

Exercício nº 9: Classificar resultados finais ao longo de três horas

Exercício: Para todos os resultados finais, mostre os horários que têm pelo menos três horas. Classifique as linhas pelo resultado em ordem decrescente.

Solução:

SELECT result
FROM final_result
WHERE result >= INTERVAL '3 hours'
ORDER BY result DESC;

Explicação: Selecione a coluna result na tabela final_result.

Em seguida, use WHERE para localizar os resultados que estão abaixo de três horas. Você pode usar os operadores "maior que ou igual" (>=) e INTERVAL.

Os dados na coluna de resultados são formatados como hora. Portanto, você precisa usar INTERVAL para obter a parte específica (intervalo) desses dados. Nesse caso, são três horas. Basta escrever "3 hours" após INTERVAL.

Por fim, classifique a saída de forma decrescente pelo resultado.

Saída: Aqui estão as cinco primeiras linhas do resultado:

result
3:20:20
3:16:11
3:15:18
3:11:31
3:11:05

Exercício nº 10: Mostrar os nomes e locais dos 3 principais atletas

Exercício: Para cada atleta que já subiu ao pódio (ou seja, terminou entre os 3 primeiros), mostre o sobrenome, o nome e a colocação.

Solução:

SELECT
  a.last_name,
  a.first_name,
  fin.place
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.place <= 3;

Explicação: Neste exercício, você precisa usar dados de duas tabelas: athlete e final_result. Portanto, vamos começar a explicação a partir da cláusula FROM.

Você faz referência à tabela athlete e dá a ela um alias "a", para que você não precise escrever o nome completo da tabela em outra parte da consulta. Para obter dados de outra tabela também, você precisa unir as tabelas. Nesse caso, use JOIN, que retornará apenas as linhas correspondentes de ambas as tabelas. Para fazer isso, basta fazer referência à tabela final_result em JOIN e adicionando o alias 'fin'.

Em seguida, você deve especificar a condição de união usando a palavra-chave ON. As tabelas são unidas em colunas compartilhadas: id from athlete e athlete_id from final_result. Você está procurando linhas em que os valores dessas duas colunas sejam iguais, portanto, coloque um sinal de igual (=) entre elas. Na frente de cada nome de coluna, coloque o alias da tabela seguido de um ponto para que o banco de dados saiba em qual tabela a coluna está.

Agora que você uniu as tabelas, pode selecionar as colunas. Na frente de cada nome de coluna, coloque o alias da tabela pelo mesmo motivo explicado anteriormente. Agora, você tem o sobrenome e o nome dos atletas e seus locais.

Como última etapa, basta filtrar os dados usando WHERE e a coluna place. Você está procurando por colocações no pódio, portanto, os valores devem ser iguais ou inferiores a três. Use o operador "menor que ou igual" (<=).

Essa prática de consulta SQL exige que você conheça Cláusulas JOIN em SQL. Se ainda não tiver certeza de como eles funcionam, dê uma olhada nestas perguntas práticas do Cláusulas JOIN em SQL antes de fazer outros exercícios.

Saída: Aqui estão as primeiras cinco linhas do resultado:

last_name	first_name	place
BOLT	Usain	3
BOLT	Usain	1
BOLT	Usain	1
GATLIN	Justin	2
GATLIN	Justin	1

Exercício nº 11: Mostrar todas as maratonas com o nome da competição, o ano da competição e o nome da disciplina

Exercício: Mostre todas as maratonas, o nome (renomeie essa coluna como nome_da_competição) e o ano da competição, além do nome da disciplina (renomeie essa coluna como nome_da_disciplina).

Solução:

SELECT
  c.name AS competition_name,
  c.year,
  d.name AS discipline_name
FROM competition c
JOIN event e
  ON e.competition_id = c.id
JOIN discipline d
  ON e.discipline_id = d.id
WHERE d.name LIKE '%Marathon%';

Explicação: Este exercício mostra como unir várias tabelas. O princípio é o mesmo que o de duas tabelas. Basta adicionar mais JOINs e as condições de união.

Nesse caso, você une as tabelas competition e event onde e.competition_id é igual a c.id column.

Em seguida, você precisa adicionar a tabela discipline à cadeia de junção. Escreva JOIN novamente e faça referência à tabela discipline. Adicione a condição de união: a coluna discipline_id da tabela event tem que ser igual à coluna id da tabela discipline da tabela.

Agora, selecione as colunas necessárias, lembrando-se de colocar o alias da tabela na frente de cada coluna. Coloque o alias competition_name e discipline_name usando a palavra-chave AS para dar a elas os nomes de coluna descritos nas instruções.

Por fim, filtre os resultados para mostrar apenas as disciplinas de maratona.

Saída:

competition_name	year	discipline_name
Rio de Janeiro Olympic Games	2016	Men's Marathon
Rio de Janeiro Olympic Games	2016	Women's Marathon
London IAAF World Championships in Athletics	2017	Men's Marathon
London IAAF World Championships in Athletics	2017	Women's Marathon
IAAF World Championships in Athletics	2019	Men's Marathon

Exercício nº 12: Mostrar as pontuações de Mo Farah em todas as disciplinas

Exercício: Mostre as pontuações de Mo Farah (ID do atleta de 14189197) em todas as modalidades. Mostre NULL se ele nunca tiver participado de uma determinada modalidade. Mostre os nomes, as datas, os locais e os resultados de todas as modalidades masculinas.

Solução:

SELECT
  d.name AS discipline_name,
  e.final_date,
  fin.place,
  fin.result
FROM discipline d
LEFT JOIN event e
  ON e.discipline_id = d.id
LEFT JOIN final_result fin
  ON fin.event_id = e.id
 AND athlete_id = 14189197
WHERE is_men IS TRUE;

Explicação: Una as tabelas discipline e event nas colunas discipline_id e id. Você precisa usar LEFT JOIN. Esse tipo de união retornará todas as linhas da primeira tabela (esquerda) e somente as linhas correspondentes da segunda tabela (direita). Se não houver linhas correspondentes, os valores serão NULL. Isso é ideal para este exercício, pois você precisa mostrar todas as modalidades e usar NULLs se Mo Farah nunca tiver participado da modalidade.

A próxima união também é uma LEFT JOIN. Ela une a tabela event com a tabela final_result. A primeira condição de união aqui une as tabelas nas colunas event_id e id. Você também precisa incluir a segunda condição adicionando a palavra-chave AND. Essa segunda condição procurará somente os dados de Mo Farah, ou seja, o atleta com o ID 14189197.

Como última etapa, use WHERE para encontrar apenas as modalidades masculinas.

Saída:

discipline_name	final_date	place	result
Men's 5000m	2016-08-20	1	0:13:03
Men's 10,000m	2016-08-13	1	0:27:05
Men's 5000m	2017-08-12	2	0:13:33
Men's 10,000m	2017-08-04	1	0:26:50
Men's 800m	2017-08-08	NULL	NULL
Men's Marathon	2019-10-05	NULL	NULL
Men's 100m	2017-08-05	NULL	NULL

Exercício nº 13: mostrar os nomes das competições e o número de eventos

Exercício: Mostre os nomes de todas as competições e o número de eventos de cada competição.

Solução:

SELECT
  c.name AS competition_name,
  COUNT(*) AS events_held
FROM competition c
JOIN event e
  ON e.competition_id = c.id
GROUP BY c.name;

Explicação: Primeiro, mostre o nome da coluna da tabela competition e renomeie a coluna para competition_name.

Em seguida, use a função de agregação COUNT(*) para contar o número de eventos que foram realizados. A função COUNT() com um asterisco contará todas as linhas da saída, inclusive NULLs. Para facilitar a leitura, colocamos o nome da coluna resultante como events_held.

As tabelas que unimos são competition e event. Por fim, para obter o número de eventos por competição, você precisa GROUP BY o nome da competição.

Saída:

competition_name	events_held
IAAF World Championships in Athletics	15
Rio de Janeiro Olympic Games	16
London IAAF World Championships in Athletics	16

Exercício nº 14: mostrar os nomes de atletas mais populares

Exercício: Mostre os nomes mais populares de atletas. Os nomes são populares se pelo menos cinco atletas os compartilharem. Ao lado do nome, mostre também o número de atletas com esse nome. Classifique os resultados de modo que os nomes mais populares apareçam primeiro.

Solução:

SELECT
  first_name,
  COUNT(*) AS name_count
FROM athlete
GROUP BY first_name
HAVING COUNT(*) >= 5
ORDER BY COUNT(*) DESC;

Explicação: Primeiro, selecione os primeiros nomes e conte-os usando COUNT(*). Em seguida, agrupe-os pelo primeiro nome do atleta. Agora você tem todos os nomes e sua contagem.

Mas você precisa mostrar somente os nomes com uma contagem acima de cinco. Para isso, você usará a cláusula HAVING. Ela tem o mesmo uso que WHERE, mas HAVING é usada para filtrar dados agregados.

Por fim, classifique o resultado pela contagem de nomes, do maior para o menor. Você não pode simplesmente escrever o nome da coluna name_count em ORDER BY porque a classificação é feita antes da agregação; o SQL não reconhecerá o nome da coluna. Em vez disso, copie COUNT(*) e classifique de forma decrescente.

Este exercício mostra um problema típico de SQL que requer a filtragem de dados com uma função de agregação.

Saída:

first_name	name_count
David	9
Daniel	7
Michael	7
Jessica	6
Alex	6
Sarah	5
Diana	5
Jonathan	5
Emmanuel	5
Isaac	5
Julian	5
Anna	5

Exercício nº 15: Mostrar cada país e o número de atletas que terminaram sem colocação

Exercício: Mostre todos os países com o número de seus atletas que terminaram sem classificação. Mostre 0 se não houver nenhum. Classifique o resultado em ordem decrescente pelo número de atletas e pelo nome do país em ordem crescente.

Solução:

SELECT
  n.country_name,
  COUNT(fin.athlete_id) AS athletes_no
FROM nationality n
LEFT JOIN athlete a
  ON n.id = a.nationality_id
LEFT JOIN final_result fin
  ON a.id = fin.athlete_id
 AND fin.place IS NULL
GROUP BY n.country_name
ORDER BY
  COUNT(fin.athlete_id) DESC,
  n.country_name ASC;

Explicação: Você precisa manter todas as linhas da tabela nationality portanto, você precisa LEFT JOIN com a tabela athlete tabela. Você faz isso onde id é igual a nationality_id. Em seguida, LEFT JOIN outra tabela em que id da athlete tabela é igual a athlete_id da final_result tabela.

Como você precisa de todas as linhas de nacionalidade, não é possível usar a condição IS NULL em WHERE. Há uma solução: mova-a para a cláusula ON e você obterá todos os valores em que o local é NULL.

Agora, você pode selecionar a coluna country_name. Além disso, use a função COUNT() na coluna athlete_id para obter o número de atletas que terminaram sem classificação. Não é possível usar COUNT(*) aqui, pois ela contaria f, e você precisa da contagem de atletas concretos.

Para obter o valor da contagem por país, agrupe o resultado pelo nome do país.

Por fim, classifique o resultado pelo número de atletas de forma decrescente e pelo nome do país de forma crescente.

Saída: Aqui estão as primeiras cinco linhas do resultado:

country_name	athletes_no
Bahrain	8
Ethiopia	6
Turkey	6
Kenya	5
South Africa	5

Exercício 16: Calcular o ritmo médio de cada corrida

Exercício: Calcule o ritmo médio de cada corrida e mostre-o na coluna denominada average_pace.

Solução:

SELECT fin.result / (d.distance * 1.0 / 1000) AS average_pace
FROM event e
JOIN discipline d
  ON e.discipline_id = d.id
JOIN final_result fin
  ON fin.event_id = e.id;

Explicação: Para obter o ritmo médio por corrida, você precisa dividir o resultado pela distância. Isso é o que a consulta acima faz, mas com dois ajustes.

Primeiro, você precisa multiplicar a distância por 1,0. Isso é feito para converter a distância em um número decimal. Sem isso, a divisão pode retornar um resultado diferente, pois o resultado será dividido pelo número inteiro. O segundo ajuste é dividir a distância por 1.000. Ao fazer isso, você converterá a distância de metros para quilômetros.

Agora que você tem o cálculo, dê a essa coluna o alias average_pace.

O restante da consulta é o que você já viu nos exemplos anteriores: você está unindo a tabela event com a tabela discipline e depois com a tabela final_result.

Resultado: Aqui estão as primeiras cinco linhas do resultado:

average_pace
0:01:38
0:01:39
0:01:39
0:01:39
0:01:39

Exemplo nº 17: Encontrar todos os tempos mais rápidos do que a média para corridas de 1.500 metros

Exercício: Obtenha os tempos de todas as corridas de 1.500 metros. Mostre apenas os tempos que são mais rápidos do que o tempo médio para essa corrida.

Solução:

SELECT fin.result
FROM final_result fin
JOIN event e
  ON fin.event_id = e.id
JOIN discipline d
  ON e.discipline_id = d.id
WHERE distance = 1500
  AND fin.result < (
    SELECT AVG(fin.result)
    FROM final_result fin
    JOIN event e
      ON fin.event_id = e.id
    JOIN discipline d
      ON e.discipline_id = d.id
    WHERE distance = 1500
  );

Explicação: Você precisa conhecer as subconsultas SQL para resolver este exercício. Sua definição básica é que elas são consultas dentro de uma consulta principal. Vamos ver como isso funciona!

Selecione a coluna de resultado da tabela final_result. Em seguida, JOIN a tabela com event e depois com a tabela discipline tabela.

Depois disso, você precisa definir duas condições em WHERE. A primeira seleciona somente as distâncias iguais a 1.500.

A segunda procura dados em que o resultado esteja abaixo da média total de corridas de 1.500 metros. Para calcular a média, use uma subconsulta da seguinte maneira.

Entre parênteses após o operador de comparação, escreva outro comando SELECT (ou seja, uma subconsulta). Nela, use a função de agregação AVG() para calcular o resultado médio. O restante da consulta é igual ao da consulta principal; você está unindo as mesmas tabelas e usando a mesma condição de filtragem em WHERE.

Resultado: Aqui estão as primeiras linhas do resultado:

result
0:03:51
0:03:51
0:03:51
0:03:51
0:03:51
0:03:50
0:03:50
0:03:51

Exercício nº 18: encontrar todos os atletas que participaram de pelo menos dois eventos em uma competição

Exercício: Obtenha uma lista de atletas que participaram de dois ou mais eventos em uma competição. Mostre apenas o nome e o sobrenome deles.

Solução:

SELECT
  first_name,
  last_name
FROM athlete
WHERE id IN (
  SELECT fin.athlete_id
  FROM event e
  JOIN final_result fin
    ON fin.event_id = e.id
  GROUP BY e.competition_id, fin.athlete_id
  HAVING COUNT(*) >= 2
);

Explicação: Comece selecionando o primeiro e o último nome da tabela athlete.

Em seguida, use WHERE para configurar uma condição. Novamente usamos uma subconsulta para retornar os dados que queremos comparar, desta vez com a coluna id. No entanto, no exemplo anterior, usamos o operador 'less than' (<) porque a subconsulta retornou apenas um valor. Desta vez, usamos o operador IN, que percorrerá todos os valores retornados pela subconsulta e retornará aqueles que satisfazem a condição.

A condição é que os atletas compitam em pelo menos dois eventos em uma competição. Para encontrar esses atletas, selecione a coluna athlete_id e junte as tabelas event e final_result. Em seguida, agrupe os resultados pelas IDs da competição e do atleta. Este exemplo mostra que você pode agrupar a saída pela coluna que não está em SELECT. Entretanto, todas as colunas que aparecem em SELECT também devem aparecer em GROUP BY.

Por fim, use HAVING para filtrar os dados. Conte o número de linhas usando COUNT(*). Dessa forma, você está contando quantas vezes cada atleta aparece. Defina a condição para retornar somente os atletas com uma contagem igual ou superior a dois.

Saída: Aqui está o instantâneo de saída.

first_name	last_name
Usain	BOLT
André	DE GRASSE
Aaron	BROWN
LaShawn	MERRITT
Wayde	VAN NIEKERK

Exercício nº 19: Mostrar corredores que terminaram apenas em primeiro lugar

Exercício: Mostre todos os corredores que nunca terminaram em outro lugar que não o primeiro; nunca faltou um lugar para eles. Mostre três colunas: id, first_name, e last_name.

Solução:

SELECT
  a.id,
  a.first_name,
  a.last_name
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.place = 1

EXCEPT

SELECT
  a.id,
  a.first_name,
  a.last_name
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.place != 1 OR fin.place IS NULL;

Explicação: Para essa solução, você precisa usar o operador de conjunto EXCEPT. Os operadores de conjunto são usados para retornar os valores de duas ou mais consultas. EXCEPT retorna todos os registros exclusivos da primeira consulta , exceto aqueles retornados pela segunda consulta.

A primeira consulta da solução procura os atletas que terminaram em primeiro lugar. Para obter esses valores, selecione as colunas necessárias na tabela athlete. Em seguida, junte a tabela com a tabela final_result. Depois disso, defina a condição em WHERE para encontrar apenas os primeiros lugares.

Agora, escreva a palavra-chave EXCEPT e siga-a com a segunda consulta.

A segunda consulta é quase a mesma que a primeira. A única diferença são as duas condições em WHERE.

A primeira condição retorna todas as posições que não são a primeira usando o operador "not equal" (!=). A segunda condição procura os lugares que não sãoNULL, ou seja, o lugar nunca esteve faltando para aquele atleta. As condições são conectadas usando OR porque uma dessas condições precisa ser verdadeira; o atleta não pode terminar abaixo do primeiro lugar e também não terminar.

Observe que, para que os operadores de conjunto funcionem, deve haver o mesmo número de colunas do mesmo tipo de dados em ambas as consultas.

Saída:

id	first_name	last_name
14590785	Elijah Motonei	MANANGOI
14208194	Eliud	KIPCHOGE
14603138	Donavan	BRAZIER
14289014	Jemima Jelagat	SUMGONG
14536762	Noah	LYLES
14377814	Lelisa	DESISA
14209691	David	RUDISHA
14431159	Halimah	NAKAAYI

Exercício nº 20: Encontrar todos os atletas que não começaram e que venceram pelo menos uma vez

Exercício: Obtenha o resultado dos atletas que não começaram pelo menos uma corrida e que venceram pelo menos uma corrida. Mostre três colunas: id, first_name, e last_name.

Solução:

SELECT
  a.id,
  a.first_name,
  a.last_name
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.is_dns IS TRUE

INTERSECT

SELECT
  a.id,
  a.first_name,
  a.last_name
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.place = 1;

Explicação: Este exercício usa outro conjunto de operadores. Desta vez, é INTERSECT, que retorna todos os valores que são iguais em ambas as consultas.

A primeira consulta da solução lista os IDs dos atletas e os nomes e sobrenomes. As tabelas athlete e final_result são unidas nas colunas id e athlete_id das tabelas.

A condição em WHERE procura linhas com TRUE como um valor na coluna is_dns, ou seja, a coluna que mostra se o atleta começou a corrida.

Como no exemplo anterior, escreva o operador set e, em seguida, a segunda consulta.

A segunda consulta é a mesma que a primeira, com exceção de WHERE. A condição de filtragem encontrará os atletas que terminaram primeiro.

Juntas, essas duas consultas produzem os atletas que não iniciaram a corrida pelo menos uma vez, mas que também terminaram em primeiro lugar pelo menos uma vez.

Saída:

id	first_name	last_name
14291986	Dafne	SCHIPPERS

Da prática básica de consultas SQL até se tornar um mestre em SQL

Você precisa começar de algum lugar. Essas 20 práticas básicas de consulta SQL são ideais para construir bases antes de aprender conceitos mais avançados.

Você aprendeu bastante ao praticar a escrita de consultas que usavam WHERE, ORDER BY, JOINs, GROUP BY e HAVING. Também mostrei vários exemplos de como lidar com NULLs, fazer cálculos, escrever subconsultas e usar operadores de conjunto. As consultas deste artigo foram extraídas de nosso artigo Exercícios SQL Básico: Treine como um atleta Lá você encontrará mais exercícios básicos de SQL. E se você quiser praticar mais, confira nossa Trilha de Práticas em SQL que contém 9 cursos práticos de SQL para iniciantes.

Acrescente 20 exemplos de consultas SQL básicas e 10 exercícios práticos de SQL para iniciantes e você estará equipado com um nível intermediário de proficiência em SQL.