helpmeobiwan

--(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);