---
# System prepended metadata

title: Untitled

---

```sql
CUBOS ACADEMY

Semana de Desafio - Módulo 1 - Grupos 1, 2 e 3


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.
```sql
select house, count(*)
	from harry_potter
	where blood_status is not null
		and blood_status ='Half-blood'
		or blood_status = 'Muggle-born'
	group by house
	order by count(*) 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 house, count(*) 
	from harry_potter
	where blood_status is not null
		and  house is not null
		and blood_status ='Pure-blood'
	group by house
	order by count(*)
	limit 1
;


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

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

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

select count(*) 
	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, Non-corporeal ou None(do inglês, desconhecido) 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, 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 *
	from (select split_part(name, ' ', 2) as sobrenome, name, species 
			from harry_potter
			where species <> 'Ghost'
				and birth is not null) as fundadores
where (sobrenome = 'Gryffindor' 
	or sobrenome = 'Ravenclaw' 
	or sobrenome = 'Slytherin' 
	or sobrenome ='Hufflepuff')
;

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.

select name
	from harry_potter
	where house <> 'Slytherin'
		and loyalty like '%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 = '2 May, 1998'
		and death is not null
	order by house
;

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.

select name
	from harry_potter
	where gender = 'Male'
		and birth between '29 July, 1980' and '31, July, 1980'
		and loyalty like '%Dumbledore%'
;

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 nome, count(*) 
			from (select  unnest(string_to_array(job, ' | ')) as nome
					from harry_potter) as trabalho
			group by nome 
			order by count desc) as hp1
where count > 2
;

ou

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

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 name not in (select name
from harry_potter
where house <> 'Slytherin'
	and loyalty like '%Voldemort%')
	and birth like '%September%'
	and death is null 
	and loyalty is not null
	and house not like '%|%'
	and gender = 'Female'
	and patronus <> 'Unknown' 
	and patronus <> 'Non-corporeal'
;

ou

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

GRUPO 3

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;


Primeiros 5 registros (podem não ser os mesmos registros)

select name 
	from (select unnest(string_to_array(name, ' ')) as name 
			from harry_potter
			where gender = 'Male'
			) as nome
	limit 5
;


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));

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


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

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

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

select * from (select name, count(*)
					from (select unnest(string_to_array(name, ' ')) as name 
							from harry_potter
							) as nome1
					group by name
					order by count desc
					) as repeticoes
where count >= 4
;

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);


select tab1.name, tab1.nome, tab1.house  
	from (select unnest(string_to_array(name, ' ')) as nome, name, house
	 		from harry_potter) tab1
inner join (select * 
				from (select name, count(*)
						from (select unnest(string_to_array(name, ' ')) as name 
								from harry_potter) as nome1
								group by name
				 		) as repeticoes
				where count >= 4
				) tab2
 on tab2.name = tab1.nome
 ;

 
 1.F. Agora, filtre apenas por aqueles que estejam vinculados a casa de magia (house sejam IS NOT NULL);

select tab1.name, tab1.nome, tab1.house
	from (select unnest(string_to_array(name, ' ')) as nome, name, house
	 		from harry_potter) tab1
inner join (select * 
				from (select name, count(*)
						from (select unnest(string_to_array(name, ' ')) as name 
								from harry_potter) as nome1
						group by name
				 		) as repeticoes
				where count >= 4
				) tab2
 on tab2.name = tab1.nome
 where house is not null
;

1.G. Agora, encontre a quantidade de pessoas por casa de magia (house).

select tab1.house, count(tab1.house) 
	from (select unnest(string_to_array(name, ' ')) as nome, name, house
			from harry_potter) tab1
inner join (select * 
				from (select name, count(*)
				 		from (select unnest(string_to_array(name, ' ')) as name 
								from harry_potter) as nome1
								group by name
				 		) as repeticoes
				where count >= 4
				) tab2
 on tab2.name = tab1.nome
where tab1.house is not null
group by tab1.house
order by count
;

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.

select * 
	from (select unnest(string_to_array(skills, ' | ')) as nome
			from harry_potter
			) as habilidade
	where nome = 'Seeker' 
		or nome = 'Chaser' 
		or nome = 'Beater' 
		or nome = 'Keeper'
	limit 5
;
		
	
B. Sabendo das informações da questão anterior (2.A), liste a quantidade de jogadores por posição;

select nome, count(*) 
	from (select  unnest(string_to_array(skills, ' | ')) as nome
			from harry_potter) as habilidade
	where nome = 'Seeker' 
		or nome = 'Chaser' 
		or nome = 'Beater' 
		or nome = 'Keeper'
	group by nome
	order by nome  
	;

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;

select nome, count(*) 
	from (select  unnest(string_to_array(skills, ' | ')) as nome
			from harry_potter) as habilidade
	where nome = 'Seeker' 
	or nome = 'Chaser' 
	or nome = 'Beater' 
	or nome = 'Keeper'
	group by nome
	order by count(*) 
	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.

select jogador1.name, jogador2.nome
	from (select name, unnest(string_to_array(skills, ' | ')) as nome1
			from harry_potter) as jogador1 
inner join (select nome, count(*) 
			from (select  unnest(string_to_array(skills, ' | ')) as nome
					from harry_potter) as habilidade
			where nome = 'Seeker' 
				or nome = 'Chaser' 
				or nome = 'Beater' 
				or nome = 'Keeper'
			group by nome
			order by count(*) 
			limit 1) as jogador2 
on jogador1.nome1 = jogador2.nome
;


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.

select donos.name, animal.patronus
	from (select name, patronus
			from harry_potter
			) as donos
inner join (select * 
			from (select patronus, count(*) 
					from harry_potter
					where patronus is not null
						and patronus <> 'None'
						and patronus <> 'Non-corporeal'
						and patronus <> 'Unknown'
					group by patronus 
					order by count(*) desc
					) as patronus 
			where count >= 2) as animal
on donos.patronus = animal.patronus
order by donos.patronus
;


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);

select unnest(string_to_array(loyalty, '|')) as nome
	from harry_potter
	limit 5
;

B. Após saber da informação (4.A), busque por estas mesmas lealdades e suas respectivas quantidades;

select nome, count(*) from (select  unnest(string_to_array(loyalty, ' | ')) as nome
	from harry_potter) as leal
	group by nome
	order by count(*) 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).
select *
	from (select nome, count(*) from (select  unnest(string_to_array(loyalty, ' | ')) as nome
			from harry_potter) as leal
			group by nome
			order by count(*) desc) as leal2
	where nome <> 'Minister of Magic'
		and count = 4
;

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

select name , hp2.nome 
	from(select name, unnest(string_to_array(loyalty, ' | ')) as nome
			from harry_potter) as hp
inner join (select *
			from (select nome, count(*) from (select  unnest(string_to_array(loyalty, ' | ')) as nome
					from harry_potter) as leal
					group by nome
					order by count(*) desc) as leal2
					where nome <> 'Minister of Magic'
						and count = 4
								) as hp2
on hp.nome = hp2.nome
;
