Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --(1)
- SELECT CLI.Nome AS Cliente,
- VEI.Vei_Placa AS Placa,
- MOD.Descricao AS Modelo,
- CLA.Descricao AS Classe,
- FAB.Descricao AS Fabricante,
- LOC.DataLocacao AS DATA,
- LOC.HoraLocacao AS Hora,
- LOC.QtdDias AS Dias,
- LOC.ValorDia AS Valor,
- LOC.ValorDia * LOC.QtdDias AS Total
- FROM Cliente AS CLI
- INNER JOIN Locacao AS LOC ON LOC.CLI_Codigo = CLI.CLI_Codigo
- INNER JOIN Veiculo AS VEI ON VEI.VEI_Placa = LOC.VEI_Placa
- INNER JOIN Modelo AS MOD ON MOD.MOD_Codigo = VEI.MOD_Codigo
- INNER JOIN Classe AS CLA ON CLA.CLA_Codigo = MOD.CLA_Codigo
- INNER JOIN Fabricante AS FAB ON FAB.FAB_Codigo = MOD.FAB_Codigo
- ORDER BY Cliente;
- --(2)
- SELECT VEI.VEI_Placa AS Placa,
- MOD.Descricao AS Modelo,
- FAB.Descricao AS Fabricante
- FROM Veiculo AS VEI
- INNER JOIN Modelo AS MOD ON MOD.MOD_Codigo = VEI.MOD_Codigo
- INNER JOIN Fabricante AS FAB ON FAB.FAB_Codigo = MOD.FAB_Codigo
- ORDER BY Fabricante;
- --(3)
- SELECT FAB.Descricao AS Fabricante,
- COUNT(VEI_Placa) AS Qtd
- FROM Veiculo AS VEI
- JOIN Modelo AS MOD ON MOD.MOD_Codigo = VEI.MOD_Codigo
- JOIN Fabricante AS FAB ON FAB.FAB_Codigo = MOD.FAB_Codigo
- GROUP BY FAB.Descricao
- ORDER BY Fabricante;
- --(4)
- CREATE PROCEDURE SP_Locar
- @CLI_Codigo INT, @VEI_Placa INT
- AS
- IF NOT EXISTS(SELECT * FROM Cliente WHERE CLI_Codigo = @CLI_Codigo)
- PRINT 'Cliente não Cadastrado!'
- ELSE
- IF (SELECT Ativo FROM Cliente WHERE CLI_Codigo = @CLI_Codigo) = 0
- PRINT 'Cliente não Autorizado!'
- ELSE
- IF NOT EXISTS (SELECT * FROM Veiculo WHERE VEI_Placa = @VEI_Placa)
- PRINT 'Veículo não Cadastrado!'
- ELSE
- IF (SELECT Livre FROM Veiculo WHERE VEI_Placa = @VEI_Placa) = 0
- PRINT 'Veículo Locado'
- ELSE
- BEGIN
- DECLARE @ValorDia DECIMAL(8,2)
- SET @ValorDia = (SELECT CLA.Diaria
- FROM Classe AS CLA
- JOIN Modelo AS MOD
- ON MOD.CLA_Codigo = CLA.CLA_Codigo
- JOIN Veiculo AS VEI
- ON VEI.MOD_Codigo = MOD.MOD_Codigo
- WHERE VEI.VEI_Placa = @VEI_Placa)
- INSERT INTO Locacao (CLI_Codigo,
- VEI_Placa,
- DataLocacao,
- HoraLocacao,
- ValorDia,
- Finalizado)
- VALUES (@CLI_Codigo,
- @VEI_Placa,
- CAST(GETDATE() AS DATE),
- CAST(GETDATE() AS TIME),
- @ValorDia,
- 0);
- END;
- --(5)
- CREATE TRIGGER T_Locacao
- ON Locacao
- FOR INSERT, UPDATE
- AS
- BEGIN
- IF EXISTS (SELECT * FROM DELETED)
- BEGIN
- UPDATE Locacao
- SET Finalizado = 1
- WHERE LOC_Codigo = (SELECT LOC_Codigo FROM INSERTED)
- UPDATE Veiculo
- SET Livre = 1
- WHERE VEI_Placa = (SELECT VEI_Placa FROM INSERTED)
- END
- ELSE
- UPDATE Veiculo
- SET Livre = 0
- WHERE VEI_Placa = (SELECT VEI_Placa FROM INSERTED)
- END;
- --(6)
- CREATE FUNCTION F_Consulta (@VEI_Placa CHAR(7))
- RETURNS TABLE
- AS
- RETURN (SELECT VEI.VEI_Placa,
- MOD.Descricao AS Modelo,
- CLA.Descricao AS Classe,
- FAB.Descricao AS Fabricante
- FROM Veiculo AS VEI
- JOIN Modelo AS MOD ON MOD.MOD_Codigo = VEI.MOD_Codigo
- JOIN Classe AS CLA ON CLA.CLA_Codigo = MOD.CLA_Codigo
- JOIN Fabricante AS FAB ON FAB.FAB_Codigo = MOD.FAB_Codigo
- WHERE VEI.VEI_Placa = @VEI_Placa);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement