# Desafio - Por Lucas Rafael Barros
## Grupo 1 - Questões - Contagem, Agrupamentos e Comparações
1. Quantos bruxos nascidos-trouxa (Muggle-born) ou mestiços (Half-blood) existem em cada casa (`house`)? Ordene-os decrescentemente e limite a busca em quatro (4) registros. Observe que não temos certeza sobre `blood_status` de alguns bruxos. Mas, nós estamos buscando aqui nessa questão pelos bruxos que são `Muggle-born`, `Half-blood` ou `Muggle-born or Half-blood`.
Resposta:
|house|count|
|-----|-----|
|Gryffindor|12|
|Ravenclaw|7|
|Hufflepuff|5|
|Slytherin|5|
```sql=
select
house,
count(id)
from
harry_potter
where
blood_status ilike 'Half-blood'
or blood_status ilike 'Muggle-born'
group by house
order by count(id) desc
limit 4
```
2. Qual é a casa que tem a menor quantidade de sangues-puro (`Pure-blood`), assumindo apenas aqueles que têm-se certeza? Remova, também, aqueles casos que a casa (`House`) seja nula e o (`Blood Status`) seja nula.
Resposta:
|house|count|
|-----|-----|
|Hufflepuff|2|
Curiosidade: Na verdade, a casa que possui a menor quantidade de sangues-puro certo é a Corvinal (Ravenclaw) que sequer aparece na listagem, isso deve-se ao fato de que existem poucos personagens listados da Ravenclaw.
```sql=
select
house,
count(id)
from
harry_potter
where
blood_status ilike 'Pure-blood'
and house is not null
and blood_status is not null
group by house
order by count(id) asc
limit 1
```
3. Quantas pessoas existem na família Weasley (assuma pessoas com algum nome de Weasley)?
Resposta:
|count|
|-----|
|10|
```sql=
select
count(id)
from
harry_potter
where
"name" ilike '%Weasley%'
```
4. Existe alguém na família Weasley que não seja ruivo (`Red`)?
Resposta:
|count|
|-----|
|0|
```sql=
select
count(id)
from
harry_potter
where
"name" ilike '%Weasley%'
and hair_colour not ilike 'red'
```
5. Todo bruxo consegue aprender o feitiço do patrono, um feitiço de proteção que toma forma de um animal. É um feitiço complexo e nem todos os bruxos possuem um patrono. Existem pessoas que possuem o mesmo animal patrono (`patronus`)? Remova os casos em que os valores sejam `Unknown`, `Non-corporeal` ou `None`(do inglês, desconhecido) e ordene-os pela quantidade decrescentemente, limitando nos 3 maiores resultados.
Resposta:
|patronus|count|
|--------|-----|
|Doe|2|
|Cat|2|
|Stag|2|
```sql=
select
patronus,
count(id)
from
harry_potter
where
patronus is not null
and patronus not ilike 'Unknown'
and patronus not ilike 'Non-corporeal'
and patronus not ilike 'None'
group by patronus
order by count(id) desc
limit 3
```
6. Utilizando apenas uma consulta, responda: busque pelos quatro bruxas ou bruxos que possuem nomes que deram origem ao nome das casas de Hogwarts (`House`). Remova aqueles que sejam da espécie fantasma (`Ghost`). A resposta deve conter o sobrenome, o nome e a espécie.
Resposta:
|sobrenome|name|species|
|---------|----|-------|
|Slytherin|Salazar Slytherin|Human|
|Gryffindor|Godric Gryffindor|Human|
|Ravenclaw|Rowena Ravenclaw|Human|
|Hufflepuff|Helga Hufflepuff|Human|
```sql=
select
split_part("name" , ' ', 2) as sobrenome,
"name",
species
from
harry_potter
where
split_part("name" , ' ', 2) in (
select house
from harry_potter
where house is not null
group by house )
and species not ilike 'Ghost'
group by house, "name", species
limit 4
```
7. Existiam bruxos que não eram da casa (`house`) Sonserina (`Slytherin`) que eram leais ao Lord Voldemort? Se existiram, qual são seus nomes e suas casas? Desconsidere aqueles que não estão associados a nenhuma casa. A Resposta deve conter apenas o nome desses bruxos.
Resposta:
|name|
|----|
|Peter Pettigrew|
|Quirinus Quirrell|
```sql=
select "name"
from harry_potter
where
house not ilike 'Slytherin'
and (loyalty ilike 'Lord Voldemort'
or loyalty ilike '%death%')
```
## Grupo 2 - Questões - Lógica
1. A Batalha de Hogwarts foi uma intensa batalha em que vários bruxos morreram. Esta batalha ocorreu no dia 2 de maio (`2 May`) de 1998. Busque pelas quantidade de mortos nesta batalha entre aqueles que lutavam por Albus Dumbledore (`Dumbledore's Army ou Albus Dumbledore`) ou a Ordem da Fênix (`Order of Phoenix`) versus aqueles que lutavam a favor de `Lord Voldemort` ou eram comensais da morte (`Death Eaters`), inclui-se também o nome `Tom Marvolo Riddle` que é o verdadeiro nome de Lord Voldemort. A resposta deve conter apenas os nomes, as casas e as lealdades. Dica: a busca por `Dumbledore's Army` pode ser feita da seguinte forma `Dumbledore''s Army`, ou seja com duas aspas simples ao pesquisar usando `LIKE` ou `=`.
Resposta:
|name|house|loyalty|
|----|-----|-------|
|Fred Weasley|Gryffindor|Dumbledore's Army | Order of the Phoenix | Hogwarts School of Witchcraft and Wizardry|
|Remus John Lupin|Gryffindor|Order of the Phoenix|
|Lavender Brown|Gryffindor|Dumbledore's Army | Hogwarts School of Witchcraft and Wizardry|
|Colin Creevey|Gryffindor|Dumbledore's Army | Hogwarts School of Witchcraft and Wizardry|
|Severus Snape|Slytherin|Albus Dumbledore|
|Vincent Crabbe|Slytherin|Lord Voldemort|
|Bellatrix Lestrange|Slytherin|Lord Voldemort | Death Eaters|
|Tom Marvolo Riddle|Slytherin||
|Nymphadora Tonks|Hufflepuff|Ministry of Magic | Order of the Phoenix|
```sql=
select "name", house, loyalty
from harry_potter
where
death ilike '%2 may%'
and (loyalty ilike '%dumbledore%'
or loyalty ilike '%phoenix%'
or loyalty ilike '%vold%'
or loyalty ilike '%death%')
or "name" ilike 'Tom Marvolo Riddle'
```
2. A primeira profecia de Sibila Trelawney é uma famosa profecia que colocou em dúvida quem seria o herói que teria poder para derrotar o grande vilão, Lord Voldemort. Provavelmente os bruxos não tinham um banco de dados tão interessante quanto o nosso mas a profecia tinha alguns requisitos que curiosamente **alguns** destes podem ser validados pelo dataset, a profecia dizia que um menino teria as seguintes características:
- ser do gênero masculino.
- seus pais tinham desafiado Voldemort três vezes e sobreviveram.
- ele teria um poder que Voldemort era incapaz ou não queria compreender.
- nascido nos últimos dias de julho de 1980, ano em que a profecia foi feita (possivelmente 29, 30 ou 31).
- se mais de uma pessoa nascesse que satisfizer os requisitos, o próprio Voldemort iria escolher a quem acabaria por se referir.
Sua meta é encontrar nomes que poderiam satisfazer nossa vontade de descobrir quem poderia ser o herói.
Observação: Bom, como dizia o ditado: O inimigo do meu inimigo é meu amigo. Podemos assumir que aliados a Albus Dumbledore, Albus ou a Order of the Phoenix (`Ordem da Fênix`) eram também inimigos de Voldermort. Então isso pode ser um bom indicativo para tentar identificar nosso(s) herói(s).
Com base nesses requisitos quem são os possíveis heróis? A resposta deve conter apenas o nome do bruxo.
Resposta:
|name|
|----|
|Harry James Potter|
|Neville Longbottom|
```sql=
select "name"
from harry_potter
where
gender ilike 'male'
and birth ilike '%1980%'
and birth ilike '%july%'
and (birth ilike '%29%' or birth ilike '%30%' or birth ilike '%31%')
```
3. Busque por todos os trabalhos (`job`) e separe-os individualmente e por fim, ordene-os por quantidade descrecente, além disso, filtre-os por aqueles que possuem acima de 2. Lembre-se um personagem pode ter zero ou mais trabalhos e àqueles maiores que 1 são separados por ` | ` (barra vertical entre espaços); Cuidado: existe uma armadilha nessa pergunta: na resposta deve existir apenas trabalhos **distintos**.
Resposta:
|nome|count|
|----|-----|
|Student|53|
|Auror|5|
|Minister for Magic|3|
|Advance Guard|3|
```sql=
select
nome, cont
from (select
distinct unnest(string_to_array(job,' | ') ) as nome, count(*) as cont
from
harry_potter
group by unnest(string_to_array(job,' | ') )
having count(*) > 2
order by count(*) desc ) as hp
group by nome, cont
order by cont desc
```
4. Busque por todos aqueles que entram nessas restrições:
- Nasceram em Setembro `September` (`birth`);
- Não faleceram (`death`);
- Não possuem nomes iguais àqueles achados na questão 7 (utilize subquery);
- Possuem lealdade associada a alguém (`loyalty`);
- Que seu patrono não seja `Unknown` nem `Non-corporeal`;
- Que possuam alguma casa (`house`);
- Sejam do gênero feminino (`gender`);
A resposta deve conter os nomes das bruxas:
Resposta:
|name|
|----|
|Hermione Jean Granger|
|Cho Chang|
```sql=
select "name"
from harry_potter
where birth ilike '%september%'
and death is null
and loyalty notnull
and patronus not like 'Unknown'
and patronus not like 'Non-corporeal'
and house notnull
and gender ilike 'female'
and "name" not in(
select "name"
from harry_potter
where
house not ilike 'Slytherin'
and (loyalty ilike 'Lord Voldemort'
or loyalty ilike '%death%')
)
```
## Grupo 3 - Questões - Subqueries e JOIN
**1.** Todas proposições abaixo PRECISAM ser executadas em uma só consulta podendo utilizar subconsultas.
**1.A.** Busque por todos os nomes de bruxos e quebre as palavras a fim de encontrarmos cada palavra usada como nome;
Resposta parcial:
Primeiros 5 registros (podem não ser os mesmos registros)
|nome|
|----|
|Harry|
|James|
|Potter|
|Ronald|
|Bilius|
```sql=
select unnest(string_to_array("name", ' ')) from harry_potter
```
**1.B.** Agora, agrupe os nomes a fim de encontrar a quantidade de vezes que o certo nome é repetido. (Lembre-se, existem pessoas com famílias, então espera-se repetição de nomes (que apareçam número maior do que 1));
Resposta parcial:
Primeiros 5 registros (podem ser diferentes)...
|nome|count|
|----|-----|
|Edward|1|
|Pomfrey|1|
|Hermione|1|
|Flint|1|
|Lavender|1|
```sql=
select
unnest(string_to_array("name", ' ')), count(*)
from
harry_potter
group by unnest(string_to_array("name", ' '))
```
**1.C.** Ordene-as palavras usadas como nome pela quantidade de repetições;
Resposta parcial:
Primeiros 5 registros...
|nome|count|
|----|-----|
|Weasley|9|
|Potter|5|
|Dursley|4|
|Malfoy|4|
|James|3|
```sql=
select
unnest(string_to_array("name", ' ')), count(*)
from
harry_potter
group by unnest(string_to_array("name", ' '))
order by count(*) desc
```
**1.D.** Filtre por todos aqueles nomes que tiveram 4 ou mais repetições;
Resposta parcial:
|nome|count|
|----|-----|
|Weasley|9|
|Potter|5|
|Malfoy|4|
|Dursley|4|
```sql=
select nome, cont
from
(select
unnest(string_to_array("name", ' ')) as nome, count(*) as cont
from
harry_potter
group by unnest(string_to_array("name", ' '))
) as hp
where cont >= 4
group by nome, cont
order by cont desc
```
**1.E.** Agora utilizando o recurso de `JOIN`, encontre todos os personagens que possuem nome que esteja no grupo encontrado na questão anterior (1.D);
Resposta parcial:
|name|nome|house|
|----|----|-----|
|Harry James Potter|Potter|Gryffindor|
|Ronald Bilius Weasley|Weasley|Gryffindor|
|Fred Weasley|Weasley|Gryffindor|
|George Weasley|Weasley|Gryffindor|
|Ginevra (Ginny) Molly Weasley|Weasley|Gryffindor|
|Lily J. Potter|Potter|Gryffindor|
|James Potter|Potter|Gryffindor|
|Percy Ignatius Weasley|Weasley|Gryffindor|
|(Bill) William Arthur Weasley|Weasley|Gryffindor|
|Charles Weasley|Weasley|Gryffindor|
|Molly Weasley|Weasley|Gryffindor|
|Arthur Weasley|Weasley|Gryffindor|
|Draco Malfoy|Malfoy|Slytherin|
|Lucius Malfoy|Malfoy|Slytherin|
|Narcissa Malfoy|Malfoy|Slytherin|
|Petunia Dursley|Dursley||
|Vernon Dursley|Dursley||
|Dudley Dursley|Dursley||
|Marge Dursley|Dursley||
|Albus Severus Potter|Potter|Slytherin|
|Scorpius Hyperion Malfoy|Malfoy|Slytherin|
|James Sirius Potter|Potter|Gryffindor|
```sql=
select
hp."name", filtro.nome, hp.house
from
(select *, unnest(string_to_array("name", ' ')) as part from harry_potter) as hp
inner join (select
unnest(string_to_array("name", ' ')) as nome, count(*)
from
harry_potter
group by unnest(string_to_array("name", ' '))
having count(*) >= 4
order by count(*) desc) as filtro on hp.part = filtro.nome;
```
**1.F.** Agora, filtre apenas por aqueles que estejam vinculados a casa de magia (`house` sejam `IS NOT NULL`);
Resposta parcial:
|name|nome|house|
|----|----|-----|
|Harry James Potter|Potter|Gryffindor|
|Ronald Bilius Weasley|Weasley|Gryffindor|
|Fred Weasley|Weasley|Gryffindor|
|George Weasley|Weasley|Gryffindor|
|Ginevra (Ginny) Molly Weasley|Weasley|Gryffindor|
|Lily J. Potter|Potter|Gryffindor|
|James Potter|Potter|Gryffindor|
|Percy Ignatius Weasley|Weasley|Gryffindor|
|(Bill) William Arthur Weasley|Weasley|Gryffindor|
|Charles Weasley|Weasley|Gryffindor|
|Molly Weasley|Weasley|Gryffindor|
|Arthur Weasley|Weasley|Gryffindor|
|Draco Malfoy|Malfoy|Slytherin|
|Lucius Malfoy|Malfoy|Slytherin|
|Narcissa Malfoy|Malfoy|Slytherin|
|Albus Severus Potter|Potter|Slytherin|
|Scorpius Hyperion Malfoy|Malfoy|Slytherin|
|James Sirius Potter|Potter|Gryffindor|
```sql=
select
hp."name", filtro.nome, hp.house
from
(select *, unnest(string_to_array("name", ' ')) as part from harry_potter) as hp
inner join (select
unnest(string_to_array("name", ' ')) as nome, count(*)
from
harry_potter
group by unnest(string_to_array("name", ' '))
having count(*) >= 4
order by count(*) desc) as filtro on hp.part = filtro.nome
where hp.house is not null
```
**1.G.** Agora, encontre a quantidade de pessoas por casa de magia (`house`).
Resposta final:
|house|count|
|-----|-----|
|Slytherin|5|
|Gryffindor|13|
```sql=
select house, count(*) from (select
hp."name", filtro.nome, hp.house
from
(select *, unnest(string_to_array("name", ' ')) as part from harry_potter) as hp
inner join (select
unnest(string_to_array("name", ' ')) as nome, count(*)
from
harry_potter
group by unnest(string_to_array("name", ' '))
having count(*) >= 4
order by count(*) desc) as filtro on hp.part = filtro.nome
where hp.house is not null
) as h
group by house
```
**2.** Todas proposições abaixo PRECISAM ser executadas em uma só consulta podendo utilizar subconsultas. O quadribol é um esporte bastante popular entre bruxos e existem várias posições muito parecidas com o Futebol ou Volei. No Quadribol, existem o apanhador (`Seeker`), o artilheiro (`Chaser`), o batedor (`Beater`) e até mesmo o goleiro (`Keeper`); A coluna de habilidades (`skills`) listam várias habilidades separadas por ` | ` (barra vertical entre espaços). Responda as perguntas abaixo com uma única consulta podendo utilizar subqueries:
**A.** Entre todas as habilidades, quebre-as e busque por aquelas que falem sobre as posições do Quadribol citadas anteriormente no enunciado.
Resposta parcial:
Primeiros 5 registros (esta resposta pode mudar)
|nome|
|----|
|Seeker|
|Keeper|
|Beater|
|Beater|
|Chaser|
```sql=
select
skl
from
(select
unnest(string_to_array(skills , ' | ')) as skl
from
harry_potter) as hp
where
skl ilike 'Seeker'
or skl ilike 'Chaser'
or skl ilike 'Beater'
or skl ilike 'Keeper'
```
**B.** Sabendo das informações da questão anterior `(2.A)`, liste a quantidade de jogadores por posição;
Resposta parcial:
|nome|count|
|----|-----|
|Beater|4|
|Chaser|8|
|Keeper|2|
|Seeker|4|
```sql=
select
skl, qtd
from
(select
unnest(string_to_array(skills , ' | ')) as skl, count(*) as qtd
from
harry_potter
group by skl) as hp
where
skl ilike 'Seeker'
or skl ilike 'Chaser'
or skl ilike 'Beater'
or skl ilike 'Keeper'
group by skl, qtd
```
**C.** Sabendo das informações da questão anterior `(2.B)`, ordene-os os registros encontrados e busque pela posição que possui **MENOR** quantidade de jogadores;
Resposta parcial:
|nome|count|
|----|-----|
|Keeper|2|
```sql=
select
skl, qtd
from
(select
unnest(string_to_array(skills , ' | ')) as skl, count(*) as qtd
from
harry_potter
group by skl) as hp
where
skl ilike 'Seeker'
or skl ilike 'Chaser'
or skl ilike 'Beater'
or skl ilike 'Keeper'
group by skl, qtd
order by qtd asc
limit 1
```
**D.** Sabendo das informações da questão anterior `(2.C)`, busque por todos os jogadores que são da posição encontrada.
Resposta final:
|name|nome|
|----|----|
|Ronald Bilius Weasley|Keeper|
|Oliver Wood|Keeper|
```sql=
select hp.name, hp.skl as nome from (select name,
unnest(string_to_array(skills , ' | ')) as skl, count(*) as qtd
from
harry_potter
group by skl, name) as hp
inner join (select
skl, qtd
from
(select
unnest(string_to_array(skills , ' | ')) as skl, count(*) as qtd
from
harry_potter
group by skl) as hp
where
skl ilike 'Seeker'
or skl ilike 'Chaser'
or skl ilike 'Beater'
or skl ilike 'Keeper'
group by skl, qtd
order by qtd asc
limit 1) as filtro on hp.skl = filtro.skl
```
**3.** Para aqueles bruxos possuam patrono e que esses patronos não sejam `Unknown`, `None` e `Non-corporeal` nem NULL, busque pelos bruxos ou bruxas que possuem o mesmo animal. Por fim, ordene-os alfabeticamente e traga a informação de nome e o patrono.
Resposta:
|name|patronus|
|----|--------|
|Minerva McGonagall|Cat|
|Dolores Jane Umbridge|Cat|
|Lily J. Potter|Doe|
|Severus Snape|Doe|
|Harry James Potter|Stag|
|James Potter|Stag|
```sql=
select name, patronus from harry_potter inner join (select patronus as patron from (select
patronus, count(patronus) as qtd
from
harry_potter
where
patronus not ilike 'Unknown'
and patronus not ilike 'None'
and patronus not ilike 'Non-corporeal'
group by patronus) as querry
where qtd > 1) as filtro on harry_potter.patronus = patron
```
**4.** Todas proposições abaixo PRECISAM ser executadas em uma só consulta podendo utilizar subconsultas:
**A.** Busque por todas possibilidades individuais de lealdade (`Loyalty`). Dica: os valores estão listados separando cada valor por ` | ` (barra vertical entre espaços);
Resposta parcial:
Primeiros 5 registros encontrados (esses valores podem mudar)
|unnest|
|------|
|Albus Dumbledore|
|Dumbledore's Army|
|Order of the Phoenix|
|Hogwarts School of Witchcraft and Wizardry|
|Dumbledore's Army|
```sql=
select unnest(string_to_array(loyalty, ' | ')) as leal from harry_potter group by leal
```
**B.** Após saber da informação `(4.A)`, busque por estas mesmas lealdades e suas respectivas quantidades;
Resposta parcial:
|nome|count|
|----|-----|
|Hogwarts School of Witchcraft and Wizardry|36|
|Order of the Phoenix|31|
|Dumbledore's Army|31|
|Lord Voldemort|15|
|Death Eaters|13|
|Original Order of the Phoenix|8|
|Albus Dumbledore|4|
|Minister of Magic|4|
|Ministry of Magic|2|
|British Ministry of Magic|2|
|Gellert Grindelwald's Acolytes|1|
|Gringotts Wizarding Bank|1|
|Harry Potter|1|
```sql=
select
unnest(string_to_array(loyalty, ' | ')) as leal,
count(*) as qtd
from
harry_potter
group by leal
order by qtd desc
```
**C.** Após saber da informação `(4.B)`, busque por aquela lealdade que possui quantidade igual a 4 e que não seja igual ao ministro da magia (`Minister of Magic`).
Resposta parcial:
|nome|count|
|----|-----|
|Albus Dumbledore|4|
```sql=
select
leal,
qtd
from
(select
unnest(string_to_array(loyalty, ' | ')) as leal,
count(*) as qtd
from
harry_potter
group by leal
order by qtd asc) as filtro
where filtro.qtd = 4
and leal not ilike 'Minister of Magic'
limit 1
```
**D.** Após saber da informação `(4.C)`, busque pelo nome das pessoas que possuem lealdade ao indíviduo ou organização encontrada na (C).
Resposta final:
|name|
|----|
|Harry James Potter|
|Rubeus Hagrid|
|Minerva McGonagall|
|Severus Snape|
```sql=
select name
from ( select
name, unnest(string_to_array(loyalty, ' | ')) as lealdade
from harry_potter) as flt
inner join ( select
leal, qtd
from ( select
unnest(string_to_array(loyalty, ' | ')) as leal, count(*) as qtd
from harry_potter
group by leal
order by qtd asc) as filtro
where
filtro.qtd = 4
and leal not ilike 'Minister of Magic') as filtrinho on
flt.lealdade = filtrinho.leal
```
Sign in with Wallet
Connect another wallet