---
# System prepended metadata

title: exercícios back-end 01/08

---

# exercícios back-end 01/08 

## sessão 01 

### exercício 01

``` sql
select purpose, avg(end_date - start_date)
from uber_drives ud 
group by purpose; 
```


### exercício 02

``` sql
select purpose, avg(miles)
from uber_drives ud 
group by purpose;
```


### exercício 03
``` sql
select category, count(*)
from uber_drives ud 
group by category 
order by count desc; 
```


### exercício 04
``` sql
select distinct start
from uber_drives ud; 
```

### exercício 05

``` sql
select distinct stop 
from uber_drives ud;
```

### exercício 06
``` sql
select max(miles), min(miles) 
from uber_drives ud; 
```

## sessão 02

### exercício 7.A

``` sql
select distinct purpose 
from uber_drives ud 
```

### exercício 7.B

``` sql
select distinct purpose, count(*) 
from uber_drives ud 
group by purpose 
```

### exercício 7.C

``` sql
select distinct purpose, count(*) 
from uber_drives ud 
group by purpose 
order by count desc
```


### exercício 7.D

``` sql
select * from (
	select distinct purpose, count(*) 
	from uber_drives ud 
	group by purpose 
	order by count desc
) as dados 
where dados.count between 100 and 170;
```

### exercício 7.E

``` sql
select * 
from uber_drives ud 
where purpose in (select purpose from (select * from (
	select distinct purpose, count(*) 
	from uber_drives ud 
	group by purpose 
	order by count desc
) as dados 
where dados.count between 100 and 170) as purposes)
```

### exercício 7.F

``` sql
select count(*) from (select * 
from uber_drives ud 
where purpose in (select purpose from (select * from (
	select distinct purpose, count(*) 
	from uber_drives ud 
	group by purpose 
	order by count desc
) as dados 
where dados.count between 100 and 170) as purposes)) as viagens 
where miles between 5 and 10 
```

### exercício 7.G

``` sql
select * from (select * 
from uber_drives ud 
where purpose in (select purpose from (select * from (
	select distinct purpose, count(*) 
	from uber_drives ud 
	group by purpose 
	order by count desc
) as dados 
where dados.count between 100 and 170) as purposes)) as viagens 
where miles between 5 and 10
and date_part('hour', end_date - start_date ) <= 1;
```


### exercício 7.H
Como a questão G estava com problemas, fiz as demais sem a condição acrescentada por ela. 

``` sql
select * from (select * 
from uber_drives ud 
where purpose in (select purpose from (select * from (
	select distinct purpose, count(*) 
	from uber_drives ud 
	group by purpose 
	order by count desc
) as dados 
where dados.count between 100 and 170) as purposes)) as viagens 
where miles between 5 and 10
and stop <> 'Unknown Location'
and start <> stop;
```

### exercício 7.I

``` sql
select * from (select * 
from uber_drives ud 
where purpose in (select purpose from (select * from (
	select distinct purpose, count(*) 
	from uber_drives ud 
	group by purpose 
	order by count desc
) as dados 
where dados.count between 100 and 170) as purposes)) as viagens 
where miles between 5 and 10
and stop <> 'Unknown Location'
and start <> stop
and start_date between '2016-04-01%' and '2016-04-15';
```

### exercício 7.J

``` sql
select * from (select * 
from uber_drives ud 
where purpose in (select purpose from (select * from (
	select distinct purpose, count(*) 
	from uber_drives ud 
	group by purpose 
	order by count desc
) as dados 
where dados.count between 100 and 170) as purposes)) as viagens 
where miles between 5 and 10
and stop <> 'Unknown Location'
and start <> stop
and start_date between '2016-04-01%' and '2016-04-15'
order by miles desc
limit 1 
```
