S134b19n

1--(1)
2SELECT CLI.Nome     AS Cliente,
3       VEI.Vei_Placa   AS Placa,
4	   MOD.Descricao   AS Modelo,
5	   CLA.Descricao   AS Classe,
6	   FAB.Descricao   AS Fabricante,
7	   LOC.DataLocacao AS Data,
8	   LOC.HoraLocacao AS Hora,
9	   LOC.QtdDias     AS Dias,
10	   LOC.ValorDia    AS Valor,
11	   LOC.ValorDia * LOC.QtdDias AS Total
12
13FROM Cliente AS CLI
14	 INNER JOIN Locacao    AS LOC ON LOC.CLI_Codigo = CLI.CLI_Codigo
15	 INNER JOIN Veiculo    AS VEI ON VEI.VEI_Placa  = LOC.VEI_Placa
16	 INNER JOIN Modelo     AS MOD ON MOD.MOD_Codigo = VEI.MOD_Codigo
17	 INNER JOIN Classe     AS CLA ON CLA.CLA_Codigo = MOD.CLA_Codigo
18	 INNER JOIN Fabricante AS FAB ON FAB.FAB_Codigo = MOD.FAB_Codigo
19
20ORDER BY Cliente;
21
22--(2)
23SELECT VEI.VEI_Placa AS Placa,
24       MOD.Descricao AS Modelo,
25	   FAB.Descricao AS Fabricante
26
27FROM Veiculo AS VEI
28	 INNER JOIN Modelo     AS MOD ON MOD.MOD_Codigo = VEI.MOD_Codigo
29	 INNER JOIN Fabricante AS FAB ON FAB.FAB_Codigo = MOD.FAB_Codigo
30
31ORDER BY Fabricante;
32
33--(3)
34SELECT FAB.Descricao    AS Fabricante,
35       COUNT(VEI_Placa) AS Qtd
36
37FROM Veiculo AS VEI
38	JOIN Modelo AS MOD ON  MOD.MOD_Codigo = VEI.MOD_Codigo
39	JOIN Fabricante AS FAB ON FAB.FAB_Codigo = MOD.FAB_Codigo
40
41GROUP BY FAB.Descricao
42ORDER BY Fabricante;
43
44--(4)
45CREATE PROCEDURE SP_Locar
46	@CLI_Codigo INT, @VEI_Placa INT
47AS
48	IF NOT EXISTS(SELECT * FROM Cliente WHERE CLI_Codigo = @CLI_Codigo)
49		PRINT 'Cliente não Cadastrado!'
50	ELSE
51		IF (SELECT Ativo FROM Cliente WHERE CLI_Codigo = @CLI_Codigo) = 0
52			PRINT 'Cliente não Autorizado!'
53		ELSE
54			IF NOT EXISTS (SELECT * FROM Veiculo WHERE VEI_Placa = @VEI_Placa)
55				PRINT 'Veículo não Cadastrado!'
56			ELSE
57				IF (SELECT Livre FROM Veiculo WHERE VEI_Placa = @VEI_Placa) = 0
58					PRINT 'Veículo Locado'
59				ELSE
60					BEGIN
61						DECLARE @ValorDia DECIMAL(8,2)
62						SET @ValorDia = (SELECT CLA.Diaria
63						                 FROM Classe AS CLA
64						                 JOIN Modelo AS MOD
65											  ON MOD.CLA_Codigo = CLA.CLA_Codigo
66										 JOIN Veiculo AS VEI
67											  ON VEI.MOD_Codigo = MOD.MOD_Codigo
68									     WHERE VEI.VEI_Placa = @VEI_Placa)
69						INSERT INTO Locacao (CLI_Codigo,
70						                     VEI_Placa,
71											 DataLocacao,
72											 HoraLocacao,
73											 ValorDia,
74											 Finalizado)
75									VALUES   (@CLI_Codigo,
76									          @VEI_Placa,
77											  CAST(GETDATE() AS DATE),
78											  CAST(GETDATE() AS TIME),
79											  @ValorDia,
80											  0);
81				    END;
82
83--(5)
84CREATE TRIGGER T_Locacao
85ON Locacao
86FOR INSERT, UPDATE
87AS
88BEGIN
89	IF EXISTS (SELECT * FROM DELETED)
90		BEGIN
91			UPDATE Locacao
92			SET Finalizado = 1
93			WHERE LOC_Codigo = (SELECT LOC_Codigo FROM INSERTED)
94
95			UPDATE Veiculo
96			SET Livre = 1
97			WHERE VEI_Placa = (SELECT VEI_Placa FROM INSERTED)
98		END
99	ELSE
100		UPDATE Veiculo
101		SET Livre = 0
102		WHERE VEI_Placa = (SELECT VEI_Placa FROM INSERTED)
103END;
104
105--(6)
106CREATE FUNCTION F_Consulta (@VEI_Placa CHAR(7))
107	RETURNS TABLE
108AS
109	RETURN (SELECT VEI.VEI_Placa,
110	               MOD.Descricao AS Modelo,
111			       CLA.Descricao As Classe,
112			       FAB.Descricao AS Fabricante
113			FROM Veiculo AS VEI
114			JOIN Modelo AS MOD ON MOD.MOD_Codigo = VEI.MOD_Codigo
115			JOIN Classe  AS CLA ON CLA.CLA_Codigo = MOD.CLA_Codigo
116			JOIN Fabricante AS FAB ON FAB.FAB_Codigo = MOD.FAB_Codigo
117			WHERE VEI.VEI_Placa = @VEI_Placa);