---
# System prepended metadata

title: Exercício Aula 4 - Backend

---

# Exercício Aula 4 - Backend

## Instruções:

1. Obtenha o conjunto de dados (dataset.txt ou dataset.sql) aqui:  https://drive.google.com/drive/folders/1UkFYuemcgUoV3rRJz55OTjA7LAGz9v1u

2. Acesse o https://www.db-fiddle.com

3. Copie (CTRL + C) o conteúdo desses arquivos por completo e cole (CTRL + V)
dentro da seçcão `SQL SCHEMA` (grupo a esquerda) `DB Fiddle`.

4. Rode essa atualização
```sql
UPDATE imoveis SET andar = '0' WHERE andar = '-';
ALTER TABLE imoveis ALTER COLUMN andar TYPE integer USING (trim(andar)::integer);
```
6. Leia os exercícios e responda-os!

## Lista de Exercícios 

1. Para a cidade de São Paulo, busque por todos os imóveis que sejam no terceiro ou no quarto andar e que custem no total até R$ 2000,00. Quantos imóveis entram nessa restrição? Gabarito: `80`;

```sql
SELECT 
	COUNT(*) 
FROM 
	imoveis 
WHERE 
	cidade = 'São Paulo' 
AND total <= (2000*100) 
AND (andar = '3' OR andar = '4')
```

2. Qual é a media de aluguel e condomínio dos imóveis do exercício 1 em reais? Gabarito: `R$ 1244.06 e R$ 338.90, respectivamente`;

```sql
SELECT 
	TRUNC(AVG(aluguel/100),2) as aluguel ,
    TRUNC(AVG(condominio/100),2) as condominio
FROM 
	imoveis 
WHERE 
	cidade = 'São Paulo' 
AND total <= (2000*100) 
AND (andar = '3' OR andar = '4')
```

3. Agora, se a gente considerasse Rio de Janeiro e Belo Horizonte como possibilidades junto com de São Paulo, sob as mesmas condições, quantos imóveis teríamos no total? Gabarito: `283 imóveis`

```sql
SELECT 
	COUNT(*)
FROM 
	imoveis 
WHERE 
	total <= (2000*100) 
AND (andar = '3' OR andar = '4')
AND (cidade = 'São Paulo' OR	cidade = 'Belo Horizonte' OR cidade = 'Rio de Janeiro')
```

4. Assumindo o exercício anterior, quais seriam as médias de aluguel e condomínio? Gabarito: `R$ 1125.33 e R$ 331.90, respectivamente`

```sql
SELECT 
	TRUNC(AVG(aluguel/100),2) as aluguel,
    TRUNC(AVG(condominio/100),2) as condominio
FROM 
	imoveis 
WHERE 
	total <= (2000*100) 
AND (andar = '3' OR andar = '4')
AND (cidade = 'São Paulo' OR	cidade = 'Belo Horizonte' OR cidade = 'Rio de Janeiro')
```

5. Quanto apartamentos existem listados em São Paulo no total? Gabarito: `4273 apartamentos`

```sql
SELECT 
	COUNT(*)
FROM 
	imoveis 
WHERE 
	cidade = 'São Paulo'
AND andar > '0'
```

6. Quantas casas exitem listadas em São Paulo? Gabarito: `1614 casas.`

```sql
SELECT 
	COUNT(*)
FROM 
	imoveis 
WHERE 
	cidade = 'São Paulo'
AND andar = '0'
```

7. Quantos imóveis existem por cidade no total? Gabarito: 

| Cidade | Imóveis |
| -------- | -------- |
| São Paulo      | 5887     |
| Campinas      | 853     |
| Belo Horizonte      | 1258     |
| Rio de Janeiro      | 1501     |
| Porto Alegre     | 1193     |

```sql
SELECT 
	cidade, COUNT(*) as imoveis
FROM 
	imoveis 
GROUP BY cidade
```

8. Quantos imóveis existem por cidade que possuem dois banheiros ou mais e que aceitem animais? Gabarito:

| Cidade | Imóveis |
| -------- | -------- |
| São Paulo      | 3128     |
| Campinas      | 360     |
| Belo Horizonte      | 677     |
| Rio de Janeiro      | 636     |
| Porto Alegre     | 481     |

```sql
SELECT 
	cidade, COUNT(*) as imoveis
FROM 
	imoveis 
WHERE
	banheiros >= '2'
AND animais = TRUE
GROUP BY cidade
```

9. Qual a média de preço de aluguel por cidade? 
10. Qual a média de preço de condomínio por cidade?

Gabarito de 9 e 10:

| Cidade | Aluguel | Condomínio |
| -------- | -------- | -------- |
| São Paulo      | 6007.14     | 1536.79     |
| Campinas      | 3656.28     | 840.64     |
| Belo Horizonte      | 4816.42     | 657.06     |
| Rio de Janeiro      | 4514.04     | 3128     |
| Porto Alegre     | 1419.07     | 632.71     |

```sql
SELECT 
	cidade, 
    TRUNC((AVG(aluguel)/100),2) as aluguel, 
    TRUNC((AVG(condominio)/100),2) as condominio 
FROM 
	imoveis 
GROUP BY cidade
```

11. Existem CASAS mobiliadas que aceitam animais e possuem 3 banheiros e 3 quartos na cidade de Campinas? Se sim, qual a média de valor total? Gabarito: `Sim, R$ 8820.00`

```sql
SELECT 
	TRUNC(AVG(total/2),2) as media
FROM 
	imoveis 
WHERE
	andar = '0'
AND mobilias = TRUE
AND animais = TRUE
AND banheiros = '3'
AND quartos = '3'
AND cidade = 'Campinas'
```

12. Existem apartamentos que possuem 60 ou menos de área e possuem 3 banheiros? Se sim, qual a média de valores? Gabarito: `Sim, R$ 2192.75`

```sql
SELECT 
	TRUNC(AVG(total/2),2) as media
FROM 
	imoveis 
WHERE
	area <= '60'
AND banheiros = '3'
AND andar > '0'
```

13. Com as seguintes restrições:
    - Se em São Paulo, apartamentos de 4 banheiros ou mais, aceitam animais e não mobiliados que estejam abaixo de R$ 3000 reais no total;
    - Se em Campinas, casas de 3 banheiros ou mais, que aceitam animais e não mobiliados que custam até R$ 2500 no total;
    - Se em Belo Horizonte, casas de 2 banheiros que aceitam animais, mobiliados de até R$ 2000
Responda quantos apartamentos existem no total utilizando apenas uma consulta.

Gabarito: `3 imóveis`
 
```sql
SELECT 
	COUNT(id) as total 
FROM 
	imoveis 
WHERE 
	(cidade = 'São Paulo' AND andar > '-' AND banheiros >= '4' AND animais = TRUE AND mobilias = FALSE AND total <= '300000') 
OR
	(cidade = 'Campinas' AND andar = '-' AND banheiros >= '3' AND animais = TRUE AND mobilias = FALSE AND total <= '250000') 
OR 
	(cidade = 'Belo Horizonte' AND andar = '-' AND banheiros = '2' AND animais = TRUE AND mobilias = TRUE AND total <= '200000');
``` 
 
14. Quais os maiores e menores valores totais para apartamentos por cidade?

Gabarito:

| Cidade | Máximos | Mínimos |
| -------- | -------- | -------- |
| São Paulo      | 316900     | 803     |
| Campinas      | 20560     | 711     |
| Belo Horizonte      | 1120000     | 545     |
| Rio de Janeiro      | 95610    | 507     |
| Porto Alegre     | 32750     | 733     |

```sql
SELECT DISTINCT 
	cidade, MAX(total/100) as Maximos, MIN(total/100)  as Minimos
FROM 
	imoveis 
WHERE
	andar > '0'
GROUP BY cidade
``` 

15. Quais são as opções de andares listados? Qual o maior andar listado?

Gabarito: `301 é o maior andar`

```sql
SELECT MAX(andar) FROM	imoveis
``` 