# Desafio - Herbert Nordson

## Grupo 1

#### 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:

Resposta se um dos filtros for escrito como ‘Muggle-born or Half-blood’

select distinct house, count (blood_status) 
from harry_potter
where 
(blood_status = 'Muggle-born' or blood_status = 'Half-blood')
group by house 
order by count(blood_status) 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.

select distinct house, count (blood_status) 
from harry_potter
where 
blood_status = 'Pure-blood' and 
house notnull and 
blood_status notnull
group by house 
order by count(blood_status) asc limit 1;

#### 3 - Quantas pessoas existem na família Weasley (assuma pessoas com algum nome de Weasley)?

select count (name)
from harry_potter
where
name LIKE ('%Weasley');

#### 4 -Existe alguém na família Weasley que não seja ruivo (Red)?

select count (name)
from harry_potter
where
name LIKE ('%Weasley') and 
Hair_colour <> '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 (do inglês, desconhecido), Non-corporeal (Não possui forma animal) ou None (nenhum) e ordene-os pela quantidade decrescentemente, limitando nos 3 maiores resultados.

select Patronus, count (*)
from harry_potter
where
(patronus <> 'Unknown' and patronus <> 'Non-corporeal' and patronus <> 'None')
group by patronus 
order by count(*) desc limit 3;

#### 6 - Utilizando apenas uma consulta com possibilidade de subqueries, 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.

select distinct unnest (string_to_array(name,' ')) as sobrenome, name, species from harry_potter
 where
		(
		name like ('%Gryffindor') or
		name like ('%Slytherin') or 
		name like ('%Ravenclaw') or 
		name like ('%Hufflepuff')
		) and species = 'Human'	
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? Desconsidere aqueles que não estão associados a nenhuma casa. A Resposta deve conter apenas o nome desses bruxos.

select name from harry_potter 
where
house <> 'Slytherin' and
loyalty like ('%Lord Voldemort%');

## Grupo 2

#### 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 the 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 =.

select name, house, loyalty from harry_potter 
where 
death like ('2 May, 1998') and 
(loyalty like ('%Albus Dumbledore%') or loyalty like ('%Dumbledore''s Army%') or loyalty like ('%Order of the Phoenix%')) or 
(loyalty like ('%Lord Voldemort%') or loyalty like ('%Death Eaters%') or loyalty like ('%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 ou a Order of the Phoenix (Ordem da Fênix) eram também inimigos de Voldemort. 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.

select name from harry_potter
where
gender like 'Male' and 
(birth like ('%29 July, 1980%') or 
birth like ('%30 July, 1980%') or 
birth like ('%31 July, 1980%'))
;
 

#### 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.

select * from (
select unnest(String_to_array(job, ' | ')) as trabalho, count(*) from harry_potter
group by trabalho 
order by count(*) desc
) as trabalho
where trabalho.count > 2;

#### 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:

select name from harry_potter 
where
birth like ('%September%') and 
death is null and 
loyalty is not null and 
patronus <> 'Unknown' and patronus <> 'Non-corporeal' and
gender = 'Female' and 
house is not null;

## Grupo 3

#### 1 - Todas proposições abaixo PRECISAM ser executadas em uma só consulta podendo utilizar subconsultas.

#### 1A - Busque por todos os nomes de bruxos e quebre as palavras a fim de encontrarmos cada palavra usada como nome;

select split_part(name, ' ', 1) as nome from harry_potter
group by nome limit 5;

#### 1B - 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));

select  unnest (string_to_array(name, ' ')) as nome, count(*) from     harry_potter
group by     nome 
order by count(*) limit 5;

#### 1C - Ordene-as palavras usadas como nome pela quantidade de repetições;

select  unnest (string_to_array(name, ' ')) as nome, count(*) from     harry_potter
group by     nome 
order by count(*) desc limit 5;

#### 1D - Filtre por todos aqueles nomes que tiveram 4 ou mais repetições;

select * 
from
	(select  unnest (string_to_array(name, ' ')) as nome, count(*) from     harry_potter
	group by     nome 
	order by count(*) desc limit 5) as nova_tabela
where
nova_tabela.count >= 4;