# Exercícios para casa Back-end 19/07/2020

## 1 - Questão

select 
	trunc(avg("math score"::integer),2) as matematica,
	trunc(avg("reading score"::integer),2) as leitura,
	trunc(avg("writing score"::integer),2) as escrita,
	count(*) as quant
from
	grades
where
	("race/ethnicity" = 'group A' or "race/ethnicity" = 'group C')
	and "writing score"::integer >= 60
	and "gender" = 'female';
	

## 2 - Questão

select  
	"parental level of education" as nivel,
	count("gender") as quantidade,
	max("math score"::integer) as nota
from
	grades
where
	"math score"::integer > 75
	and "test preparation course" = 'completed'
	group by nivel;

## 3 - Questão

select  
	"gender" as genero,
	"race/ethnicity" as grupo_etnico,
	"math score"::integer as matematica,
	"reading score"::integer as leitura,
	"writing score"::integer as escrita
from
	grades
where
	"test preparation course" = 'none'
	and ("parental level of education" = 'some college' 
		or "parental level of education" = 'some high school')
	and "lunch" = 'standard'
	order by matematica desc, leitura desc, escrita desc
	limit 10; 

## 4 - Questão

select  
	count("gender") as total
from
	grades
where
	("math score"::integer > 80
	and "reading score"::integer > 80
	and "writing score"::integer > 80)
	and
	"test preparation course" = 'completed'
	and ("parental level of education" = 'masters degree'
		or "parental level of education" = 'bachelors degree'
		or "parental level of education" = 'associates degree')
	and "lunch" = 'free/reduced';

## 5 - Questão

select  
	count("gender") as total
from
	grades
where
	("math score"::integer > 80
	and "reading score"::integer > 80
	and "writing score"::integer > 80)
	and
	"test preparation course" = 'completed'
	and ("parental level of education" = 'masters degree'
		or "parental level of education" = 'bachelors degree'
		or "parental level of education" = 'associates degree')
	and "lunch" = 'standard';

### I - Questão

select  
	"race/ethnicity" as grupo_etnico,
	"parental level of education" as nivel,
	"test preparation course" as preparatorio,
	trunc(avg("math score"::integer),2) as matematica,
	trunc(avg("reading score"::integer),2) as leitura,
	trunc(avg("writing score"::integer),2) as escrita
from
	grades
	group by grupo_etnico, nivel, preparatorio 
	order by matematica desc, leitura desc, escrita desc;

### II - Questão

--- MELHORES ALUNOS EM MATEMÁTICA COMPARADO AS OUTRAS MATÉRIAS ----
select
	("math score"::integer) as matematica,
	("reading score"::integer) as leitura,
	("writing score"::integer) as escrita
from
	grades
	order by matematica desc
	limit 20
--- PIORES ALUNOS EM MATEMÁTICA COMPARADO AS OUTRAS MATÉRIAS ----
select
	("math score"::integer) as matematica,
	("reading score"::integer) as leitura,
	("writing score"::integer) as escrita
from
	grades
	order by matematica asc
	limit 20

### III - Questão

select 
     gender,
     count(gender) 
 from grades
 where 
     ("math score"::INTEGER < 50 
     and "reading score"::INTEGER < 50 
     and "writing score"::INTEGER < 50) 
 group by gender;
 
 select 
     "race/ethnicity",
     count("race/ethnicity") 
 from grades
 where 
     ("math score"::INTEGER < 50
     and "reading score"::INTEGER < 50 
     and "writing score"::INTEGER < 50) 
 group by "race/ethnicity";
 
 select 
     "parental level of education",
     count("parental level of education") 
 from grades
 where 
     ("math score"::INTEGER < 50
     and "reading score"::INTEGER < 50 
     and "writing score"::INTEGER < 50) 
 group by "parental level of education";
 
 select 
     lunch,
     count(lunch) 
 from grades
 where 
     ("math score"::INTEGER < 50 
     and "reading score"::INTEGER < 50 
     and "writing score"::INTEGER < 50) 
 group by lunch;
 
 select 
     "test preparation course", 
     count("test preparation course") 
 from grades
 where 
     ("math score"::INTEGER < 50 
     and "reading score"::INTEGER < 50 
     and "writing score"::INTEGER < 50) 
 group by "test preparation course";