lista_exercicios_02_banco_2.sql

-- 1
SELECT cod_apolice
 ,(SELECT nome from cliente c where c.cod_cliente=a.cod_cliente ) AS nome_cliente
 ,data_inicio_vigencia
 ,data_fim_vigencia
 ,valor_cobertura
 ,valor_franquia
 ,placa
 FROM apolice a
ORDER BY data_fim_vigencia ASC
GO

-- 2
SELECT cod_apolice
 ,nome as nome_cliente
 ,data_inicio_vigencia
 ,data_fim_vigencia
 ,valor_cobertura
 ,valor_franquia
 ,placa
 FROM apolice a
left join cliente c on (
	a.cod_cliente=c.cod_cliente
)
ORDER BY data_fim_vigencia ASC
GO

-- 3
select *
, ROW_NUMBER() OVER (ORDER BY local_sinistro) as ordem 
from sinistro
go

-- 4
select *
, RANK() OVER (ORDER BY local_sinistro) as ordem 
from sinistro
go

-- 5
select *
, DENSE_RANK() OVER (ORDER BY local_sinistro) as ordem 
from sinistro
go

-- 6
select * from (
	select *
		, (select count(*) from sinistro s where s.placa=c.placa) as sinistros
	from carro c
) results
where sinistros > 1
go

select c.*
	, count(s.placa) as sinistros
from carro c
left join sinistro s on (
	s.placa=c.placa
)
group by c.placa, c.modelo, c.chassi, c.marca, c.ano, c.cor
having count(s.placa) > 1
go

-- 7
with cte as (
	select distinct c.*
		, COUNT(c.placa) OVER(PARTITION BY c.placa) as sinistros
		, FIRST_VALUE(s.data_sinistro) OVER(partition BY c.placa ORDER BY s.data_sinistro ASC) as dt_primeiro_sinistro
	from carro c
	left join sinistro s on (
		s.placa=c.placa
	)
)
select * from cte where sinistros > 1
go

-- 8
with cte as (
	select distinct c.*
		, COUNT(c.placa) OVER(PARTITION BY c.placa) as sinistros
		, FIRST_VALUE(s.data_sinistro) OVER(partition BY c.placa ORDER BY s.data_sinistro ASC) as dt_primeiro_sinistro
		, LAST_VALUE(s.data_sinistro) OVER(partition BY c.placa ORDER BY s.data_sinistro ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as dt_ultimo_sinistro
	from carro c
	left join sinistro s on (
		s.placa=c.placa
	)
)
select * from cte where sinistros > 1
go

-- 9
select r.nm_regiao, e.nm_estado
from estado e
inner join regiao r on (
	r.cd_regiao=e.cd_regiao
)
order by nm_regiao, nm_estado
go

select nm_estado
	, (select nm_regiao from regiao r where r.cd_regiao=e.cd_regiao) as nm_regiao
from estado e
order by nm_regiao, nm_estado
go

with cte as (
	select nm_estado
		, (select nm_regiao from regiao r where r.cd_regiao=e.cd_regiao) as nm_regiao
	from estado e
)
select * from cte order by nm_regiao, nm_estado
go

-- 10
-- CUIDDADO COM A ORDENAÇÃO DENTRO DE SUBSELECTS
with cte as (
	select r.nm_regiao
		, e.nm_estado
		, ROW_NUMBER() OVER(order by r.nm_regiao) as posicao
	from estado e
	inner join regiao r on (
		r.cd_regiao=e.cd_regiao
	)
)
select * from cte where posicao = 5
go

-- 11
with cte as (
	select a.cod_apolice
		, (select nome from cliente c where c.cod_cliente = a.cod_cliente) as nome_cliente
		, a.data_fim_vigencia
		, a.data_inicio_vigencia
		, a.valor_cobertura
		, a.valor_franquia
		, a.placa
	from apolice a
)
select *, sum(valor_franquia) over (partition by nome_cliente order by nome_cliente, cod_apolice) as valor_acumulado 
from cte
go