lista_exercicios_01_banco_2.sql

-- 1
select count(1) as sinistros, placa 
from sinistro
group by placa
order by count(*) desc
go

-- 2
select *
from cliente
where nome like '%a%'
  and nome not like '%a'
go

-- 3
select * 
from cliente c
left join apolice a on (
	a.cod_cliente = c.cod_cliente
)
where a.cod_cliente is null
go

-- 4
select * 
from carro c
left join apolice a on (
	a.placa = c.placa
)
where a.placa is null
go

-- 5
select distinct * 
from cliente
go

-- 6
select *
from carro
where SUBSTRING(placa, 1, 1) in ('L', 'M')
go

-- 7
select *
from sinistro
where year(data_sinistro)='2022'
go

-- 8
select * 
from cliente
where telefone_fixo is null
	and telefone_celular is null
go

-- 9
select distinct c.*
from cliente c
inner join apolice a on (
	a.cod_cliente = c.cod_cliente
	and a.data_fim_vigencia < GETDATE()
)
go

-- 10
select distinct c.*
from carro c
inner join sinistro s on (
	s.placa = c.placa
)
inner join apolice a on (
	a.placa = c.placa
)
where s.data_sinistro > a.data_fim_vigencia
go

-- 11
select count(1) regioes
from regiao
go

-- 12
select count(1) estadoss
from estado
go

-- 13
select count(1) municipios
from municipio
go

-- 14
select e.nm_estado, count(1) qtd
from municipio m
inner join estado e on (
	e.cd_estado=m.cd_estado
)
group by e.nm_estado
go

-- 15
select m.* 
from municipio m
inner join estado e on (
	e.cd_estado=m.cd_estado
)
inner join regiao r on (
	r.cd_regiao=e.cd_regiao
)
where r.nm_regiao='SUL'
go

-- 16
select m.* 
from municipio m
inner join estado e on (
	e.cd_estado=m.cd_estado
)
inner join regiao r on (
	r.cd_regiao=e.cd_regiao
)
where r.nm_regiao='SUL'
	and substring(m.nm_municipio,1,1)='C'
go

-- 17
select * 
from municipio
where len(nm_municipio)>10
go

-- 18
select m.* 
from municipio m
inner join estado e on (
	e.cd_estado=m.cd_estado
)
inner join regiao r on (
	r.cd_regiao=e.cd_regiao
)
where r.nm_regiao='NORTE'
go

-- 19
select r.nm_regiao, count(*) as qtd 
from municipio m
inner join estado e on (
	e.cd_estado=m.cd_estado
)
inner join regiao r on (
	r.cd_regiao=e.cd_regiao
)
where substring(r.nm_regiao,1,1)='S'
group by(r.nm_regiao)
go

-- 20
select count(1) avaliacoes
from avaliacao
go

-- 21
select distinct a.* 
from aluno a
inner join avaliacao_aluno aa on (
	aa.cd_aluno=a.cd_aluno
)
where aa.dt_inicio is not null
	and aa.dt_fim is not null
go

-- 22
select q.* 
from questao q
inner join avaliacao a on (
	a.cd_avaliacao=q.cd_avaliacao
)
where q.tp_questao=1
	and substring(a.ds_avaliacao,1,2)='2a'
go

-- 23
select ds_avaliacao, ds_questao, count(*) as qtd_alternativa 
from avaliacao a
inner join questao q on (
	q.cd_avaliacao=a.cd_avaliacao
)
inner join questao_item qi on (
	qi.cd_questao=q.cd_questao
)
group by ds_avaliacao, ds_questao
having count(*) > 4
order by ds_avaliacao, ds_questao
go

-- 24
select count(*) as qtd 
from questao
where tp_questao=3
go

-- 25
select ds_avaliacao
	, (case 
		when tp_questao=1 then 'Objetiva'
		when tp_questao=2 then 'Multipla Escolha'
		when tp_questao=3 then 'Aberta'
		else 'Desconhecido' end
	) as tipo
	, count(*) as qtd_tipo
from avaliacao a
inner join questao q on (
	q.cd_avaliacao=a.cd_avaliacao
)
inner join questao_item qi on (
	q.cd_questao=qi.cd_questao
)
group by ds_avaliacao, tp_questao
order by ds_avaliacao, tp_questao
go