exercicios_procedure.sql

-- EXERCÍCIO 1
DROP PROCEDURE pr_soma GO

CREATE PROC pr_soma (@valor_1 INT, @valor_2 INT, @result NUMERIC(10,2) OUTPUT) AS
	BEGIN
		SELECT @result = @valor_1 + @valor_2
	END
GO

DECLARE @res NUMERIC(14,2)
EXEC pr_soma 5, 7, @res OUTPUT
SELECT @res
GO

-- EXERCÍCIO 2
DROP PROCEDURE pr_imc
GO
CREATE PROC pr_imc (@peso NUMERIC(10,2), @altura NUMERIC(10,2), @result NUMERIC(10,2) OUTPUT) AS
	BEGIN
		SELECT @result = @peso / POWER(@altura, 2)
	END
GO

DECLARE @res NUMERIC(14,2)
EXEC pr_imc 60, 1.60, @res OUTPUT
SELECT @res
GO

-- EXERCÍCIO 3
DROP PROCEDURE pr_media_quatro
GO
CREATE PROC pr_media_quatro (
	@valor_1 NUMERIC(10,2)=NULL, 
	@valor_2 NUMERIC(10,2)=NULL, 
	@valor_3 NUMERIC(10,2)=NULL, 
	@valor_4 NUMERIC(10,2)=NULL
) AS
	BEGIN
		IF @valor_1 IS NULL
			PRINT 'PREENCHA O VALOR 1'

		IF @valor_2 IS NULL
			PRINT 'PREENCHA O VALOR 2'
		IF @valor_3 IS NULL

			PRINT 'PREENCHA O VALOR 3'
		IF @valor_4 IS NULL
			PRINT 'PREENCHA O VALOR 4'
		
		SELECT (@valor_1 + @valor_2 + @valor_3 + @valor_4) / 4
	END
GO

EXEC pr_media_quatro 10,20,30,40
GO

-- EXERCÍCIO 4
DROP PROCEDURE pr_apolices_vencidas
GO
CREATE PROC pr_apolices_vencidas AS
	BEGIN
		SELECT cod_apolice,
				nome,
				apolice.placa as placa,
				marca,
				modelo,
				ano,
				valor_cobertura,
				valor_cobertura * 1.05 as 'valor_cobertura_10%',
				valor_franquia,
				valor_franquia * 1.05 as 'valor_franquia_10%',
				GETDATE() AS data_inicio_vigencia,
				DATEADD(YEAR, 1, GETDATE()) AS data_fim_vigencia
			FROM apolice
			INNER JOIN carro ON (
				carro.placa = apolice.placa
			)
			INNER JOIN cliente ON (
				cliente.cod_cliente = apolice.cod_cliente
			)
			WHERE data_fim_vigencia > CURRENT_TIMESTAMP
	END
GO

EXEC pr_apolices_vencidas
GO

-- EXERCÍCIO 5
DROP PROCEDURE pr_valida_apolice
GO
CREATE PROC pr_valida_apolice(@cod_apolice INT) AS
	BEGIN
		declare @status_apolice char(1)

		SELECT @status_apolice = (select 
									case when data_fim_vigencia > GETDATE() THEN 'A'
									when data_fim_vigencia < GETDATE() THEN 'I'
									else 'N'
									end as ATIVO 
								from apolice a where cod_apolice = @cod_apolice)

		if @status_apolice = 'A'
			print 'ATIVA'
		else if @status_apolice = 'I'
			print 'INATIVA'
		else
			print 'INEXISTENTE'

	END
GO

EXEC pr_valida_apolice '202200007'
GO

-- EXERCÍCIO 6
DROP PROCEDURE pr_detalhes_sinistro
GO
CREATE PROC pr_detalhes_sinistro(@placa varchar(10)) AS
	BEGIN
		declare @placa_qtd varchar(10),
				@cliente varchar(50),
				@veiculo varchar(max),
				@sinistros int

		select 
			@cliente = condutor
			, @veiculo = (select modelo+' | '+marca+' | '+trim(str(ano))+' | '+cor as veiculo from carro where carro.placa = sinistro.placa)
			, @sinistros =count(*) over()
		from sinistro
		where sinistro.placa = @placa

		
		print '----------------------------------------'

		if @sinistros > 0
			BEGIN
				print 'Cliente: ' + @cliente
				print 'Carro: ' + @veiculo
				print 'Sinistros: ' + cast(@sinistros as varchar(10))
			END
		else
			print 'Placa não localizada'

		print '----------------------------------------'

	END
go

EXEC pr_detalhes_sinistro 'ALD3834'
GO


-- EXERCÍCIO 7
DROP PROCEDURE pr_cad_veiculo
GO
CREATE PROC pr_cad_veiculo(
						@placa varchar(10), 
						@modelo varchar(50), 
						@chassi varchar(30), 
						@marca varchar(30), 
						@ano int, 
						@cor varchar(10)
) AS
	BEGIN
		
		INSERT INTO carro (placa, modelo, chassi, marca, ano, cor) VALUES(@placa, @modelo, @chassi, @marca, @ano, @cor);
		
		SELECT * FROM carro;
	END
;

EXEC pr_cad_veiculo 'DTD7871', '206', '1142SAD5214', 'PEUGEOT', 2000, 'AZUL'
;

-- Exercício 8
DROP PROCEDURE pr_sinistros
;
CREATE PROC pr_sinistros(@cliente varchar(100) = '', @placa varchar(10) = '', @saida int output) AS
BEGIN

	IF LEN(TRIM(@cliente)) > 0
	BEGIN;
		SELECT @saida = COUNT(*) FROM sinistro s WHERE placa IN (
			SELECT a.placa FROM cliente c INNER JOIN apolice a ON (a.cod_cliente  = c.cod_cliente) WHERE C.nome = @cliente
		);
		RETURN;
	END;
	
	IF LEN(TRIM(@placa)) > 0
	BEGIN
		SELECT @saida = count(*) FROM sinistro WHERE placa = @placa;
		RETURN;
	END;
		
	SELECT @saida = 0;
END
;

DECLARE @res INT
EXEC pr_sinistros 'MURILO CARVALHO CARDOSO', '', @res OUTPUT
SELECT @res
;

-- Exercício 9
DROP PROCEDURE pr_cad_sinistro
;
CREATE PROC pr_cad_sinistro(@placa varchar(10), @data date, @hora time, @local varchar(100), @condutor char(100)) AS
BEGIN
	DECLARE @carro int = 0, @cod_sinistro int;
	
	SELECT @carro = COUNT(*) from carro WHERE placa = @placa;

	if @carro = 0
	begin
		print('CARRO NÃO EXISTE');
		return;
	end
	
	SELECT @cod_sinistro = MAX(cod_sinistro) + 1 from sinistro;
	
	INSERT INTO sinistro
	(cod_sinistro, placa, data_sinistro, hora_sinistro, local_sinistro, condutor)
	VALUES(@cod_sinistro, @placa, @data, @hora, @local, @condutor);

END
;

EXEC pr_cad_sinistro 'DAK0101', '2023-01-01', '22:00:00', 'CASA DO JOÃO', 'MARIA'
;


-- Exercício 10
DROP PROCEDURE pr_cli_mais_sinistro
;

CREATE PROC pr_cli_mais_sinistro @N INT AS
BEGIN
	with cte as (
		select c.*
			, (
				select count(*) 
				from sinistro s
				inner join apolice a on (
					a.placa = s.placa and a.cod_cliente = c.cod_cliente
				)
			) as qt_sinistros
		from cliente c
	) select top (@N)
		cte.nome as Nome,
		RANK() OVER (ORDER BY cte.qt_sinistros DESC) AS ranking,
		cte.qt_sinistros
		from cte 
		order by qt_sinistros desc 
END
;

EXEC pr_cli_mais_sinistro 1
;