SIBD - Queries (PROJ)

/* GROUP BY */
/*Numero de turnos que os motoristas fizeram numa viagem com mais do que 1 turno*/

SELECT N__VIAGEM AS [Numero Viagem], COUNT(N__TURNO) AS [Numero de Turnos], NOME AS [Nome]
    FROM TURNOS t JOIN MOTORISTA m
        ON t.N__CARTA = m.N__CARTA JOIN FUNCIONARIOS f
            ON m.N__FUNC = f.N__FUNC
    GROUP BY N__VIAGEM, NOME
    HAVING COUNT(N__TURNO) > 1
    ORDER BY [Numero Viagem], [Numero de Turnos]

/* numero reparaçoes e serviços e custo total das reparaçoes de um veiculo */
SELECT v.N__VEICULO AS [ID Veiculo], COUNT(r.N__REPARACAO) AS [Total de Reparações], COUNT(s.N__SERVICO) AS [Total de Serviços], SUM(PRECO) AS [Total Custos]
    FROM VEICULOS v JOIN REPARACOES r
        ON v.N__VEICULO = r.N__VEICULO JOIN REPARACOES_SERVICOS rs
            ON r.N__REPARACAO = rs.N__REPARACAO JOIN SERVICOS s
                ON s.N__SERVICO = rs.N__SERVICO
    GROUP BY v.N__VEICULO
    ORDER BY [Total Custos] DESC

/*SUBQUERY*/
SELECT NOME AS [Nome]
    FROM FUNCIONARIOS f JOIN MOTORISTA m
        ON f.N__FUNC = m.N__FUNC
    WHERE m.N__CARTA IN (SELECT t.N__CARTA FROM TURNOS t JOIN VIAGENS v ON t.N__VIAGEM = v.N__VIAGEM JOIN INFORMACAO_VIAGEM iv ON v.N__VIAGEM = iv.N__VIAGEM WHERE iv.TIPO_INFO_V LIKE 'Carga')

/*INNER*/
/*estado dos veiculos das viagens começadas a pelo menos 7 dias atras da data atual*/
SELECT DISTINCT v.N__VIAGEM AS [ID Viagem], v.N__VEICULO AS [ID Veículo], ESTADO AS [Estado]
    FROM VIAGENS v JOIN VEICULOS ve
        ON ve.N__VEICULO = v.N__VEICULO
    WHERE v.DIA_PARTIDA >= getdate()-7

/*mercadorias fornecidas por cada fornecedor*/
SELECT NOME AS [Nome], DESCRICAO_MERC AS [Mercadoria], 'Carga' AS [Tipo]
    FROM FORNECEDOR f JOIN ENTIDADE_COMERCIAL ec
        ON f.COD_EC = ec.COD_EC JOIN INFORMACAO_VIAGEM iv
            ON ec.COD_EC = iv.COD_EC JOIN MERCADORIA m
                ON iv.N__MERCADORIA = m.N__MERCADORIA
    WHERE iv.TIPO_INFO_V LIKE 'Carga'
UNION
SELECT NOME AS [Nome], DESCRICAO_MERC AS [Mercadoria], 'Descarga' AS [Tipo]
    FROM CLIENTE c JOIN ENTIDADE_COMERCIAL ec
        ON c.COD_EC = ec.COD_EC JOIN INFORMACAO_VIAGEM iv
            ON ec.COD_EC = iv.COD_EC JOIN MERCADORIA m
                ON iv.N__MERCADORIA = m.N__MERCADORIA
    WHERE iv.TIPO_INFO_V LIKE 'Descarga'
    ORDER BY [Tipo]

/*PROCEDIMENTOS*/
GO

CREATE PROC Editar_Motorista
@NCarta INT, @NFun INT, @Tipo VARCHAR(50), @ObsM VARCHAR(250), @NCartaS VARCHAR(15)
AS
BEGIN
    UPDATE MOTORISTA SET
    N__CARTA = @NCarta,
    N__FUNC = @NFun,
    TIPO = @Tipo,
    OBS_MEDICAS = @ObsM,
    N__CARTA_S = @NCartaS
    WHERE N__CARTA_S = @NCartaS
END

GO

SELECT * FROM MOTORISTA

GO

CREATE PROC Inserir_Motorista
@NCarta INT, @NFun INT, @Tipo VARCHAR(50), @ObsM VARCHAR(250), @NCartaS VARCHAR(15)
AS
    IF EXISTS(SELECT * FROM FUNCIONARIOS WHERE N__FUNC = @NFun)
        BEGIN
            INSERT INTO MOTORISTA
            VALUES(@NCarta, @NFun, @Tipo, @ObsM, @NCartaS)
        END
GO

/*FUNÇÃO*/
/*VEICULOS POR FILIAL NAO REPARADOS*/
IF OBJECT_ID('dbo.fnCamSemRepFromFil') IS NOT NULL
    DROP FUNCTION dbo.fnCamSemRepFromFil
GO
CREATE FUNCTION fnCamSemRepFromFil(@fil INT)
RETURNS INT
BEGIN
    RETURN (SELECT COUNT(*) FROM FILIAIS f JOIN VEICULOS v
                ON f.CODFILIAIS = v.CODFILIAIS
            WHERE f.CODFILIAIS = @fil)
        -
        (SELECT COUNT(v.N__VEICULO) FROM FILIAIS f JOIN VEICULOS v
            ON f.CODFILIAIS = v.CODFILIAIS
        WHERE f.CODFILIAIS = @fil AND v.N__VEICULO IN (SELECT N__VEICULO FROM REPARACOES))
END

GO

/*CURSOR*/
DECLARE CURSOR_FilCam CURSOR
FORWARD_ONLY
READ_ONLY
FOR
    SELECT CODFILIAIS FROM FILIAIS

OPEN CURSOR_FilCam
DECLARE @ID INT
FETCH FROM CURSOR_FilCam INTO @ID

WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'Número de camiões que nunca foram reparados da filial com ID ' + CONVERT(VARCHAR,@ID) + ': ' + CONVERT(VARCHAR,dbo.fnCamSemRepFromFil(@ID))
        FETCH FROM CURSOR_FilCam INTO @ID
    END

CLOSE CURSOR_FilCam
DEALLOCATE CURSOR_FilCam

/*TRIGGERS*/

GO

IF OBJECT_ID('OnUpdate') IS NOT NULL
    DROP TRIGGER OnUpdate
GO

CREATE TRIGGER OnUpdate
    ON MERCADORIA
    AFTER UPDATE
AS
BEGIN
    DECLARE @NMercadoria INT,
            @Peso INT,
            @DESC VARCHAR(50),
            @Qt INT,
            @Tipo VARCHAR(50)


    SELECT @NMercadoria = N__MERCADORIA,
            @Qt = QUANTIDADE,
            @Peso = PESO,
            @DESC = DESCRICAO_MERC,
            @Qt = QUANTIDADE,
            @Tipo = TIPO_MERC
    FROM INSERTED
    IF @Qt > 0 AND @Peso > 0
        UPDATE MERCADORIA SET PESO=@Peso, DESCRICAO_MERC=@DESC, QUANTIDADE=@Qt, TIPO_MERC=@Tipo WHERE N__MERCADORIA=@NMercadoria
    ELSE IF @Qt <= 0
    BEGIN
        SELECT @NMercadoria = N__MERCADORIA,
                @Qt = QUANTIDADE,
                @Peso = PESO,
                @DESC = DESCRICAO_MERC,
                @Qt = QUANTIDADE,
                @Tipo = TIPO_MERC
        FROM DELETED
        UPDATE MERCADORIA SET PESO=@Peso, DESCRICAO_MERC=@DESC, QUANTIDADE=@Qt, TIPO_MERC=@Tipo WHERE N__MERCADORIA=@NMercadoria;
        RAISERROR('A quantidade nao pode ser abaixo de 1.',11,1)
    END
    ELSE
    BEGIN
        SELECT @NMercadoria = N__MERCADORIA,
                @Qt = QUANTIDADE,
                @Peso = PESO,
                @DESC = DESCRICAO_MERC,
                @Qt = QUANTIDADE,
                @Tipo = TIPO_MERC
        FROM DELETED
        UPDATE MERCADORIA SET PESO=@Peso, DESCRICAO_MERC=@DESC, QUANTIDADE=@Qt, TIPO_MERC=@Tipo WHERE N__MERCADORIA=@NMercadoria;
        RAISERROR('O peso nao pode ser abaixo de 1.',11,1)
    END
END

IF OBJECT_ID('OnInsert') IS NOT NULL
    DROP TRIGGER OnInsert

GO

CREATE TRIGGER OnInsert
    ON MERCADORIA
    INSTEAD OF INSERT
AS
BEGIN
    DECLARE @NMercadoria INT,
            @Peso INT,
            @DESC VARCHAR(50),
            @Qt INT,
            @Tipo VARCHAR(50)

    SELECT @NMercadoria = N__MERCADORIA,
            @Qt = QUANTIDADE,
            @Peso = PESO,
            @DESC = DESCRICAO_MERC,
            @Qt = QUANTIDADE,
            @Tipo = TIPO_MERC
    FROM INSERTED

    IF @Qt > 0 AND @Peso > 0 AND (@NMercadoria NOT IN (SELECT N__MERCADORIA FROM MERCADORIA))
        INSERT INTO MERCADORIA VALUES (@NMercadoria,@Peso,@DESC,@Qt,@Tipo)
    ELSE IF @Qt <= 0
        RAISERROR('A quantidade nao pode ser abaixo de 1.',11,1)
    ELSE IF @Peso <= 0
        RAISERROR('O peso nao pode ser abaixo de 1.',11,1)
    ELSE
        RAISERROR('Esse número de mercadoria já existe na Base de Dados.',11,1)
END

GO

UPDATE MERCADORIA SET PESO=-1 WHERE N__MERCADORIA=1
SELECT * FROM MERCADORIA
UPDATE MERCADORIA SET PESO=24000 WHERE N__MERCADORIA=1
SELECT * FROM MERCADORIA
INSERT INTO MERCADORIA VALUES (6,200,'ola',50,'oi')
SELECT * FROM MERCADORIA

DELETE FROM MERCADORIA WHERE N__MERCADORIA = 6