lista-3-sql-satc

lista-3-sql-satc.sql

-- 1
-- b) Comando 2
-- ALTER TABLE apolice ALTER COLUMN valor_cobertura numeric(10,2) NULL 

-- 2
-- a), d)
-- Falta uma vírgula depois do 'JEEP'

-- 3
-- b)

-- 4
ALTER TABLE resposta_fechada ADD CONSTRAINT fk_avaliacao_aluno__resposta_fechada
FOREIGN KEY(cd_avaliacao, cd_aluno) REFERENCES avaliacao_aluno (cd_avaliacao, cd_aluno)
go

-- 5
-- a), b), d), f)

-- 6
-- c), e)

-- 7
-- f)

-- 8
-- b)

-- 9
-- a)

-- 10
-- b)

-- 11
-- CREATE TABLE cliente (
-- 	cod_cliente int not null identity,
-- 	nome varchar(59),
-- 	cpf char(11),
-- 	sexo char(1),
-- 	endereco varchar(200),
-- 	telefone_fixo varchar(50),
-- 	telefone_celular varchar(50),
-- 	CONSTRAINT pk_cliente PRIMARY KEY(cod_cliente)
-- )
-- go

-- 12
-- b)

-- 13
select c.placa, count(*) from carro c
inner join sinistro s on (
	s.placa = c.placa
)
group by c.placa
order by c.placa desc
go

-- 14
SELECT c.* 
FROM cliente c
WHERE c.nome LIKE '%a%'
	AND c.nome NOT LIKE '%a'
GO

-- 15
SELECT count(*) FROM cliente c
where c.cod_cliente not in (
	select cod_cliente from apolice 
)
go

select count(*) from apolice a
right join cliente c on (
	c.cod_cliente = a.cod_cliente
)
where a.cod_apolice is null
go

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

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

-- 17
select count(*) from cliente
go

-- 18
select count(*) from carro
where SUBSTRING(placa, 1, 1) IN ('L', 'M')
go

-- 19
select count(*) from sinistro
where  YEAR(data_sinistro)='2022'
go

-- 20
select count(*) from cliente
where telefone_celular is null 
	and telefone_fixo is null
go

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

-- 22
select count(*) from carro c
inner join apolice a on (
	a.placa = c.placa
)
inner join sinistro s on (
	s.placa = c.placa
	and s.data_sinistro > a.data_fim_vigencia
)
go

-- 23
select count(*) from regiao
go

-- 24
select count(*) from estado
go

-- 25
select count(*) from municipio
go

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

-- 27
select count(*) 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
	and r.nm_regiao = 'SUL'
)
go

-- 28
select count(*) 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
	and r.nm_regiao = 'SUL'
)
where SUBSTRING(m.nm_municipio, 1, 1)='C'
go

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

-- 30
select count(*) 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
	and r.nm_regiao = 'NORTE'
)
go

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

-- 32
select count(*) from avaliacao
go


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

-- 34
select count(*) from avaliacao av
inner join questao q on (
	q.cd_avaliacao = av.cd_avaliacao
	and q.tp_questao = 1
)
where av.ds_avaliacao like '2a%'
go

-- 35
select ds_avaliacao, ds_questao, count(*) qtd from avaliacao av
inner join questao q on (
	q.cd_avaliacao = av.cd_avaliacao
)
inner join questao_item qi on (
	qi.cd_questao = q.cd_questao
)
group by ds_avaliacao, ds_questao
having count(*) > 4
go

-- 36
select count(tp_questao) from questao 
where tp_questao = 3
go

-- 37
select ds_avaliacao, tp_questao, count(*) from avaliacao av
inner join questao q on (
	q.cd_avaliacao = av.cd_avaliacao
)
group by ds_avaliacao, tp_questao
order by ds_avaliacao, tp_questao
go




-- 1) 
-- b, c, d, e, f

-- 2)
-- a, e

-- 3)
-- a, b

-- 4)
create table agendamentos (
	cd_agendamento int not null identity,
	dt_agendamento datetime,
	ds_agendamento varchar(200),
	constraint pk_agendamento primary key(cd_agendamento)
)

-- 5)
-- a, b, c

-- 6)
-- V F F F F

-- 7)
-- b

-- 8)
-- b, c,

-- 9)
-- d

-- 10)
-- b

-- 11)
-- 5
select count(distinct cd_aluno) from avaliacao_aluno
where dt_inicio is not null and dt_fim is not null

-- 12)
-- 8
select ds_avaliacao, ds_questao, count(*) qtd from avaliacao av
inner join questao q on (
	q.cd_avaliacao = av.cd_avaliacao
)
inner join questao_item qi on (
	qi.cd_questao = q.cd_questao
)
group by ds_avaliacao, ds_questao
having count(*) > 4
go

-- 13)
-- c)
select count(*), r.nm_regiao from regiao r
inner join estado on estado.cd_regiao = r.cd_regiao
inner join municipio on municipio.cd_estado = estado.cd_estado
where SUBSTRING(r.nm_regiao, LEN(r.nm_regiao), len(r.nm_regiao)) = 'E'
	and r.nm_regiao not like '%R%'
group by r.nm_regiao

-- 14)
-- 4
select count(distinct apolice.cod_cliente)
from apolice 
where data_fim_vigencia < '2023-06-02'


-- 15)
-- 3
select * from cliente
left join apolice on (
	apolice.cod_cliente = cliente.cod_cliente
)
where apolice.cod_apolice is null

-- 16)
-- a) MZT1826
select placa, count(*) from sinistro
group by placa
order by count(*) desc

-- 17)
-- d) 48
select count(*) from MICRODADOS_ENEM_2021_SC 
where TP_SEXO = 'M' and TP_ESTADO_CIVIL=1 and TP_FAIXA_ETARIA <= 7 and NO_MUNICIPIO_ESC = 'Criciúma' and Q010 = 'A'
go

-- 18)
-- a)
select top 1 NO_MUNICIPIO_ESC, count(*) from MICRODADOS_ENEM_2021_SC where TP_SEXO = 'F' and SG_UF_PROVA='SC'
group by NO_MUNICIPIO_ESC
order by count(*) desc
go


-- 19)
-- a)
select NO_MUNICIPIO_ESC, count(*) from MICRODADOS_ENEM_2021_SC 
where Q008 >= 'C' and SG_UF_PROVA='SC'
group by NO_MUNICIPIO_ESC
order by count(*) desc
go

-- 20)
-- a) 4
SELECT * FROM carro WHERE SUBSTRING(placa, 2, 1) IN ('V', 'W', 'A')
go

select * from carro where placa LIKE '_[VWA]%'
go