BD - Prova Exemplo 2017

/* III */
/* a) */
(SELECT TOP(5) Nome_cliente, Nif, COUNT(*) AS [Quantidade], '+contas', NULL AS [Ano]
    FROM d_Clientes JOIN d_Contas_clientes
        ON d_Clientes.N_cliente = d_Contas_clientes.N_cliente
    GROUP BY d_Contas_clientes.N_cliente
    ORDER BY Quantidade DESC)

UNION

(SELECT TOP(5) Nome_cliente, Nif, COUNT(*) AS [Quantidade], '+movimentos', DATEPART(YEAR,d_Historico_Movimentos.DATA) AS [ANO]
    FROM d_Clientes JOIN d_Contas_clientes
        ON d_Clientes.N_cliente = d_Contas_clientes.N_cliente JOIN d_Contas
            ON d_Contas_clientes.N_Conta = d_Historico_Movimentos.N_Conta
    WHERE (DATEDIFF(YEAR, d_Historico_Movimentos.DATA, getdate()) = -1
            OR DATEDIFF(YEAR, d_Historico_Movimentos.DATA, getdate()) = 0)
        AND DATEDIFF(MONTH, d_Historico_Movimentos.DATA, getdate()) = 0
    GROUP BY d_Contas_clientes.N_cliente, DATEPART(MONTH,d_Historico_Movimentos.DATA)
    ORDER BY Quantidade DESC);

/* b) */
UPDATE d_Clientes SET VariosBalcoes = TRUE
    WHERE N_cliente IN (SELECT N_cliente FROM d_Contas_clientes JOIN d_Contas
            ON d_Contas.N_Conta = d_Contas_clientes.N_Conta JOIN d_Balcoes
                ON d_Contas.N_balcao = d_Balcoes.N_balcao
        GROUP BY d_Contas_clientes.N_Cliente, d_Balcoes.codigo
        HAVING COUNT(*) > 1)

/* c) */
SELECT N_Cliente, Nif FROM
    (SELECT N_Cliente, Nif FROM (
        SELECT d_clientes.N_Cliente
            FROM d_Clientes,  d_bancos.Codigo
                JOIN d_contas_clientes ON d_clientes.N_Cliente = d_contas_clientes.N_cliente
                JOIN d_contas ON d_contas.N_conta = d_contas_clientes.N_conta
                JOIN d_bancos ON d_bancos.Codigo = d_contas.Codigo
            GROUP BY d_Contas_Clientes.N_Cliente, d_bancos.Codigo) AS sub
        GROUP BY sub.n_cliente
        HAVING  COUNT(*) = (SELECT COUNT(*) FROM d_bancos)) AS temp1

    JOIN
    (SELECT N_Cliente FROM (
    SELECT d_clientes.N_Cliente
        FROM d_Clientes
            JOIN d_emprestimos_clientes ON d_clientes.N_Cliente = d_emprestimos_clientes.N_cliente
            JOIN d_emprestimos ON d_emprestimos_clientes.N_emprestimo = d_emprestimos.N_emprestimo
            JOIN d_bancos ON d_bancos.Codigo = d_emprestimos.Codigo
        GROUP BY d_clientes.N_Cliente,d_bancos.Codigo) AS sub
    GROUP BY sub.n_cliente
    HAVING  COUNT(*) = (SELECT COUNT(*) FROM d_bancos)) AS temp2

    ON temp1.N_Cliente = temp2.N_Cliente


/* IV */
/* a) */
CREATE TRIGGER OnInsertMov
ON d_Movimentos
INSTEAD OF INSERT
AS
BEGIN
        DECLARE @Opsuc BIT = 0
        IF (SELECT COUNT(*) FROM INSERTED) == 1
        BEGIN
                DECLARE @Nmov INT,
                        @NCon INT,
                        @Tipo CHAR,
                        @Val MONEY,
                        @DATA DATE

                SELECT @Nmov = N_movimento,
                        @NCon = N_conta,
                        @Tipo = Tipo,
                        @Val = Valor,
                        @DATA = DATA
                FROM INSERTED

                IF Tipo = 'L' /*Levantamentos*/
                BEGIN
                        IF @Val > (SELECT Saldo FROM d_Contas c JOIN d_Movimentos m
                                        ON c.N_conta = m.N_conta
                                        WHERE(c.N_conta = @NCon)) /*Se não tiver saldo*/
                        BEGIN
                                IF (SELECT * FROM d_Contas WHERE N_conta = @NCon*10) != NULL /*se existir*/
                                BEGIN
                                        INSERT INTO d_Movimentos VALUES (@NCon*10,@Tipo,@Val,@DATA)
                                        UPDATE d_Contas SET Saldo = Saldo-@Val WHERE N_conta = @NCon*10
                                        SET @Opsuc = 1
                                        PRINT 'Foi retirado o valor da transação à sua conta a prazo'
                                END
                                ELSE
                                BEGIN
                                        INSERT INTO Falhas_lev VALUES (@NCon,@DATA,@Val)
                                        IF @Val > 400
                                                INSERT INTO Alertas VALUES (@NCon,@DATA,@Val)
                                ELSE
                        END
                        ELSE
                        BEGIN
                                INSERT INTO d_Movimentos VALUES (@NCon,@Tipo,@Val,@DATA)
                                UPDATE d_Contas SET Saldo = Saldo-@Val WHERE N_conta = @NCon
                                SET @Opsuc = 1
                        END
                END
                ELSE/*Depositos*/
                        INSERT INTO d_Movimentos VALUES (@NCon,@Tipo,@Val,@DATA)
                        UPDATE d_Contas SET Saldo = Saldo+@Val WHERE N_conta = @NCon
                        SET @Opsuc = 1
                BEGIN
                END
        END
        ELSE
                RAISERROR('Apenas um movimento por favor.',11,1)

        IF @Opsuc = 0
                PRINT 'OPERAÇÃO SEM SUCESSO'
        ELSE PRINT 'OPERAÇÃO COM SUCESSO'
END

/* b) */
CREATE PROCEDURE ListMov
        @NC INT
AS
BEGIN
        DECLARE @Tot INT = 0,
                @NumM INT,
                @DATA DATE,
                @Val MONEY,
                @Tipo CHAR

        PRINT 'Número de Movimento - Data - Valor'

        DECLARE C_Mov CURSOR
        FOR (SELECT TOP 10 N_movimento, DATA, Valor, Tipo FROM d_Movimentos WHERE N_conta = @NC OREDER BY DATA DESC)
        OPEN C_Mov
        FETCH FIRST FROM C_Mov INTO @NumM, @DATA, @Val, @Tipo
        WHILE @@FETCH_STATUS = 0
        BEGIN
                IF @Tipo = 'L'
                        SET @Val = @Val*-1

                PRINT CONVERT(VARCHAR,@NumM) + ' - ' + CONVERT(VARCHAR,@DATA) + ' - ' + CONVERT(VARCHAR,@Val)
                SET @Tot = @Tot+1
                FETCH NEXT FROM C_Mov INTO @NumM, @DATA, @Val, @Tipo
        END
        CLOSE C_Mov
        DEALLOCATE C_Mov

        IF @Tot < 10
        BEGIN
                DECLARE C_HistMov CURSOR
                FOR (SELECT TOP 10 N_movimento, DATA, Valor, Tipo FROM Histórico_Movimentos WHERE N_conta = @NC OREDER BY DATA DESC)
                OPEN C_HistMov
                FETCH FIRST FROM C_Mov INTO @NumM, @DATA, @Val, @Tipo
                WHILE @@FETCH_STATUS = 0 AND @Tot < 10
                BEGIN
                        IF @Tipo = 'L'
                                SET @Val = @Val*-1

                        PRINT CONVERT(VARCHAR,@NumM) + ' - ' + CONVERT(VARCHAR,@DATA) + ' - ' + CONVERT(VARCHAR,@Val)
                        SET @Tot = @Tot+1
                        FETCH NEXT FROM C_HistMov INTO @NumM, @DATA, @Val, @Tipo
                END
                CLOSE C_HistMov
                DEALLOCATE C_HistMov
        END
END

/* c) */
CREATE PROCEDURE EstatMovAux
        @B INT,
        @Balc INT,
        @Mes INT,
        @Ano INT OUTPUT,
        @Per1 FLOAT OUTPUT,
        @S1 MONEY OUTPUT,
        @Per11 FLOAT OUTPUT,
        @S11 MONEY OUTPUT,
        @Per22 FLOAT OUTPUT,
        @S22 MONEY OUTPUT
AS
BEGIN
        DECLARE @Tot INT,
                @Tot2 INT
        /* TOTAL DE CONTAS DO MES */
        SET @Tot = (SELECT COUNT(*) FROM d_Bancos b JOIN d_Balcoes bal
                        ON b.Código = bal.Codigo JOIN d_Contas c
                                ON c.N_balcao = bal.N_balcao JOIN d_Movimentos m
                                        ON c.N_conta = m.N_conta
                        WHERE b.Código = @B
                            AND bal.N_Balcão = @Balc
                            AND DATEPART(MONTH,m.DATA) = @Mes
                            AND DATEPART(YEAR,m.DATA) = MAX(DATEPART(YEAR,m.DATA)))

        /* PERCENTAGEM, SOMA E ANO DO MES (LEVANTAMENTO) */
        SELECT @Per1 = COUNT(*), @S1 = SUM(m.Valor), @Ano = MAX(DATEPART(YEAR,m.DATA)
                FROM Banco b JOIN d_Balcoes bal
                        ON b.Código = bal.Codigo JOIN d_Contas c
                                ON c.N_Balcão = bal.N_Balcao JOIN d_Movimentos m
                                        ON c.N_conta = m.N_conta
                WHERE b.Código = @B
                        AND bal.N_Balcão = @Balc
                        AND DATEPART(MONTH,m.DATA) = @Mes
                        AND DATEPART(YEAR,m.DATA) = @Ano)
                        AND m.Tipo = 'L')

        /* PERCENTAGEM E SOMA DO MES (DEPOSITO) */
        SELECT @Per2 = COUNT(*), @S2 = SUM(m.Valor)
                FROM Banco b JOIN d_Balcoes bal
                        ON b.Código = bal.Codigo JOIN d_Contas c
                                ON c.N_Balcão = bal.N_Balcao JOIN d_Movimentos m
                                        ON c.N_conta = m.N_conta
                WHERE b.Código = @B
                        AND bal.N_Balcão = @Balc
                        AND DATEPART(MONTH,m.DATA) = @Mes
                        AND DATEPART(YEAR,m.DATA) = @Ano)
                        AND m.Tipo = 'D')

       /* TOTAL DE CONTAS DO MES ANTERIOR */
        SET @Tot2 = (SELECT COUNT(*) FROM Banco b JOIN d_Balcoes bal
                        ON b.Código = bal.Codigo JOIN d_Contas c
                                ON c.N_Balcão = bal.N_Balcao JOIN d_Movimentos m
                                        ON c.N_conta = m.N_conta
                        WHERE b.Código = @B
                            AND bal.N_Balcão = @Balc
                            AND DATEPART(MONTH,m.DATA) = @Mes-1
                            AND DATEPART(YEAR,m.DATA) = MAX(DATEPART(YEAR,m.DATA)))

        /* PERCENTAGEM, SOMA E ANO DO MES ANTERIOR */
        SELECT @Per11 = COUNT(*), @S11 = SUM(m.Valor), @Ano = MAX(DATEPART(YEAR,m.DATA)
                FROM Banco b JOIN d_Balcoes bal
                        ON b.Código = bal.Codigo JOIN d_Contas c
                                ON c.N_Balcão = bal.N_Balcao JOIN d_Movimentos m
                                        ON c.N_conta = m.N_conta
                WHERE b.Código = @B
                        AND bal.N_Balcão = @Balc
                        AND DATEPART(MONTH,m.DATA) = @Mes-1
                        AND DATEPART(YEAR,m.DATA) = @Ano)
                        AND m.Tipo = 'L')

        /* PERCENTAGEM E SOMA DO MES ANTERIOR (DEPOSITO) */
        SELECT @Per22 = COUNT(*), @S22= SUM(m.Valor)
                FROM Banco b JOIN d_Balcoes bal
                        ON b.Código = bal.Codigo JOIN d_Contas c
                                ON c.N_Balcão = bal.N_Balcao JOIN d_Movimentos m
                                        ON c.N_conta = m.N_conta
                WHERE b.Código = @B
                        AND bal.N_Balcão = @Balc
                        AND DATEPART(MONTH,m.DATA) = @Mes-1
                        AND DATEPART(YEAR,m.DATA) = @Ano)
                        AND m.Tipo = 'D')

        /* calcular percentagens de 0 a 100 */
        SET @Per1 = 100*@Per1/@Tot
        SET @Per2 = 100*@Per2/@Tot
        SET @Per11 = 100*@Per11/@Tot
        SET @Per22 = 100*@Per22/@Tot
END

GO

CREATE PROCEDURE PEstatMov
        @Mes INT
AS
BEGIN
        DECLARE @EstatMov TABLE (Banco VARCHAR(50), Balcão INT, Ano INT, Mês INT, Tipo CHAR(1), PerMov INT, Tot MONEY, PerMovAnt INT, TotAnt MONEY)

        DECLARE @Cod INT,
                @Nome VARCHAR(50),
                @NBal INT,
                @Ano INT,
                @Per1 FLOAT,
                @S1 MONEY,
                @Per11 FLOAT,
                @S11 MONEY,
                @Per2 FLOAT,
                @S2 MONEY
                @Per22 FLOAT,
                @S22 MONEY

        DECLARE C_Mov CURSOR
        FOR (SELECT b.Código, b.Nome_banco, bal.N_Balcão FROM d_Bancos b JOIN d_Balcoes bal
                ON b.Código = bal.Codigo
                ORDER BY Banco b.Nome_banco, bal.N_Balcão)
        OPEN C_Mov
        FETCH FIRST FROM C_Mov INTO @Cod,@Nome,@NBal
        WHILE @@FETCH_STATUS = 0
        BEGIN
                EXEC EstatMovAux @Cod, @NBal, @Mes,@Ano OUTPUT,@Per1 OUTPUT,@S1 OUTPUT,@Per2 OUTPUT,@S2 OUTPUT,@Per11 OUTPUT,@S11 OUTPUT,@Per22 OUTPUT,@S22 OUTPUT
                INSERT INTO @EstatMov VALUES(@Nome,@NBal,@Ano,@Mes,'L',@Per1,@S1,@Per11,@S11)
                INSERT INTO @EstatMov VALUES(@Nome,@NBal,@Ano,@Mes,'D',@Per2,@S2,@Per22,@S22)
                FETCH NEXT FROM C_Mov INTO @Cod,@Nome,@NBal
        END
        CLOSE C_Mov
        DEALLOCATE C_Mov

        SELECT * FROM @EstatMov
END

GO

EXEC PEstatMov 1