PC3 - Parte B

Trabajando con la base de datos “Ventas”:

1.      Crear un trigger que actualice el monto de la venta total [S_VentaTotal] de la tabla Ventas cuando el vendedor modifique el pedido de cliente.

Tener presente que el campo [S_VentaTotal] se debe actualizar de forma correcta para los siguientes casos:

Ø  El cliente aumenta la cantidad unidades compradas

Ø  El cliente disminuye la cantidad unidades compradas

Ø  Como resultado de aumentar o disminuir la cantidad de unidades también el vendedor modifica el porcentaje de descuento.


Solución:


CREATE TRIGGER TRIGGER1_VENTA
ON VENTA
AFTER INSERT
AS
BEGIN
UPDATE VENTA
SET S_VENTATOTAL = S_VENTATOTAL + (SELECT I.Q_CANTIDAD*P.S_PRECIO_UNITARIO*(1-I.S_DESCUENTO)
FROM INSERTED I JOIN PRODUCTO P ON (I.C_PRODUCTO = P.C_PRODUCTO))        
   WHERE C_VENTA = (SELECT I2.C_VENTA FROM INSERTED I2)                                                
END

2.      Crear un trigger que actualice la cantidad de unidades que se tiene en stock [Q_Stock] de la tabla Productos cuando el vendedor anule o elimine toda la venta de un cliente.

Solución:


UPDATE PRODUCTO SET Q_STOCK = Q_STOCK + 3 WHERE C_PRODUCTO = 1
UPDATE PRODUCTO SET Q_STOCK = Q_STOCK + 2 WHERE C_PRODUCTO = 10

UPDATE VENTA SET S_VENTATOTAL = 0.00 WHERE C_VENTA = 1
DELETE VENTA

3.      Crear un trigger que actualice el monto de la venta total [S_VentaTotal] de la tabla Ventas cuando el vendedor anule o elimine la compra de uno de los productos en venta a un cliente.

Solución:


CREATE TRIGGER TRIGGER2_VENTA
ON VENTA
AFTER INSERT
AS
BEGIN
UPDATE VENTA
SET S_VENTATOTAL = S_VENTATOTAL + (SELECT I.Q_CANTIDAD*P.S_PRECIO_UNITARIO*(1-I.S_DESCUENTO)
FROM INSERTED I JOIN PRODUCTO P ON (I.C_PRODUCTO = P.C_PRODUCTO))        
   WHERE C_VENTA = (SELECT I2.C_VENTA FROM INSERTED I2)                                                
END

Trabajando con la base de datos “Ahorros”:

4.      Crear un procedimiento que permita realizar operaciones de depósito de un determinado monto en nuevos soles o en dólares a una cuenta de ahorros en soles perteneciente a un cliente.

Los parámetros del procedimiento serán el tipo de moneda, monto a depositar y el número de la cuenta de ahorros del cliente.

Considerar las siguientes especificaciones:

·      Cuando el depósito es en dólares deberá obtener y aplicar el cambio del día (Compra dólares).

·      El monto de depósito no puede ser negativo ni cero.

·      Por la operación de depósito se cobra una comisión de S/. 0.10 donde dicho monto se cumula en la cuenta “M0001” como un movimiento de depósito para dicha cuenta.

·      Se guarda en la cuenta “ITF01” la aplicación del impuesto a las transacciones financieras correspondiente al 0.8 % del monto depositado en soles.

Solución:


CREATE PROCEDURE DEPOSITO_MONTO @CUENTA CHAR(5), @MONTO MONEY
AS
BEGIN TRANSACTION
   DECLARE @SALDO MONEY

   SELECT @SALDO = C.S_SALDO
   FROM CUENTA C
   WHERE C.NRO_CUENTA = @CUENTA

         DECLARE @NRO_MOV INT
         SELECT @NRO_MOV = MAX(C_SECUENCIA) + 1
         FROM MOVIMIENTO
         WHERE NRO_CUENTA = @CUENTA
                 
         INSERT INTO MOVIMIENTO VALUES(@CUENTA,@NRO_MOV,'20',@MONTO,GETDATE())
     
         UPDATE CUENTA
         SET S_SALDO = S_SALDO + @MONTO
         WHERE NRO_CUENTA = @CUENTA
     
         COMMIT

5.      Crear un procedimiento que permita realizar operaciones de pagos de servicios en nuevos soles o en dólares de una cuenta de ahorros en soles perteneciente a un cliente.

Los parámetros del procedimiento es el tipo de moneda, monto a pagar, el número de la cuenta de ahorros del cliente y la cuenta del tipo de servicio que paga el cliente.

Considerar las siguientes especificaciones:

·      El monto de pago o su equivalente traducido a nuevos soles no deberá ser mayor al saldo de la cuenta.

·      Cuando el monto de pago es en dólares deberá obtener y aplicar el cambio del día (Venta dólares).

·      Se guarda en la cuenta “ITF02” la aplicación del impuesto a las transacciones financieras correspondiente al 0.8 % del monto pagado en soles.

·      Las cuentas de las empresas de servicios múltiples puede considerar las siguientes “S0001” Telefónica, “S0002” Claro, “S0003” Nextel, “S0004” Luz del Sur, “S0005” Edelnor, etc.

Solución:

CREATE PROCEDURE PAGO_DE_SERVICIOS @CUENTA_CLIENTE CHAR(5), @CUENTA_DESTINO CHAR(5), @MONTO MONEY AS
BEGIN TRANSACTION
-- SE OBTIENE EL SALDO PARA LA EJECUCION
DECLARE @SALDO MONEY
SELECT @SALDO = C.S_SALDO
FROM CUENTA C
WHERE C.NRO_CUENTA = @CUENTA_CLIENTE


-- SE COMPRUEBA QUE EXISTE EL SALDO SUFICIENTE PARA LA OPERACION
IF @MONTO > @SALDO
ROLLBACK
ELSE
BEGIN
-- SE DECLARA EL IMPUESTO Y LOS NUMEROS DE MOVIMIENTOS
DECLARE @NRO_MOV INT
DECLARE @IMPUESTO MONEY
-- SE OBTIENEN LOS VALORES
SELECT @NRO_MOV = MAX(C_SECUENCIA) + 1
FROM MOVIMIENTO
WHERE NRO_CUENTA = @CUENTA_CLIENTE


SET @IMPUESTO = @MONTO * 0.008
SET @MONTO = @MONTO - @IMPUESTO


-- SE REGISTRAN LOS MOVIMIENTOS


INSERT INTO MOVIMIENTO VALUES (@CUENTA_CLIENTE, @NRO_MOV, '50', @MONTO, GETDATE())
INSERT INTO MOVIMIENTO VALUES (@CUENTA_DESTINO, @NRO_MOV + 1, '50', @MONTO, GETDATE())
INSERT INTO MOVIMIENTO VALUES ('ITF02', @NRO_MOV + 2, '50', @IMPUESTO, GETDATE())


-- SE ACTUALIZAN LOS SALDOS
UPDATE CUENTA
SET S_SALDO = S_SALDO - @MONTO
WHERE NRO_CUENTA = @CUENTA_CLIENTE


UPDATE CUENTA
SET S_SALDO = S_SALDO + @MONTO
WHERE NRO_CUENTA = @CUENTA_DESTINO


UPDATE CUENTA
SET S_SALDO = S_SALDO + @IMPUESTO
WHERE NRO_CUENTA = 'ITF02'
END
COMMIT
GO

6.      Crear un procedimiento que permita realizar la transferencia de un monto en nuevos soles de una cuenta de ahorros origen a otra cuenta de ahorros destino ambas en soles.

Los parámetros del procedimiento serán el monto a transferir, el número de la cuenta de ahorros origen y el número de la cuenta de ahorros destino.

Considerar las siguientes especificaciones:

·      El monto de transferencia en nuevos soles no deberá ser mayor al saldo de la cuenta.

·      Por las operaciones de transferencia se cobra una comisión de S/.5 para montos menores a S/.1000 y del 0.5% del monto de transferencia a partir de S/. 1000, el cual se cumula en la cuenta “M0002” con sus movimientos de depósito respectivos para transferencias entre cuentas de diferentes clientes.

·      Si la cuenta origen y la cuenta destino pertenecen al mismo cliente no se cobra ninguna comisión.

·      Se guarda en la cuenta “ITF02” la aplicación del impuesto a las transacciones financieras correspondiente al 0.8 % del monto transferido en soles.

Solución:


CREATE PROCEDURE CUENTABASE_CUENTADESTINO @CUENTAB CHAR(5), @CUENTAD CHAR(5),@MONTO MONEY
AS
BEGIN TRANSACTION
      DECLARE @SALDO MONEY
      SELECT @SALDO = C.S_SALDO
      FROM CUENTA C
      WHERE C.NRO_CUENTA = @CUENTAB
   
      IF @MONTO > @SALDO
            ROLLBACK
      ELSE
      BEGIN
            DECLARE @NRO_MOV INT
            SELECT @NRO_MOV = MAX(C_SECUENCIA) + 1
            FROM MOVIMIENTO
            WHERE NRO_CUENTA = @CUENTAB
         
            INSERT INTO MOVIMIENTO VALUES(@CUENTAB,@NRO_MOV,'40',@MONTO,GETDATE())
   
            UPDATE CUENTA
            SET S_SALDO = S_SALDO - @MONTO
            WHERE NRO_CUENTA = @CUENTAB
         
            UPDATE CUENTA
            SET S_SALDO = S_SALDO + @MONTO
            WHERE NRO_CUENTA = @CUENTAD
         
            COMMIT
      END

Trabajando con la base de datos “Northwind”:

7.      Los proveedores de productos quieren conocer a los “n” mejores vendedores que más ventas han realizado de sus productos que ofertan mediante Northwind evaluado dentro de un mes y año, para lo cual se requiere una función que devuelva el nombre del proveedor, nombre de contacto, ciudad, región, código postal, país, y del empleado sus nombres ciudad, país, y monto de venta, monto descontado.

Solución:


CREATE FUNCTION Listar_Mejores_Vendedores (@Mes int,@Anno int,@Top int)
RETURNS @Resultado TABLE (CompanyName nvarchar(40),ContactName nvarchar(30),
                          CompanyCity nvarchar(15),Region nvarchar(15),
                          PostalCode nvarchar(10),CompanyCountry nvarchar(15),
                          LastName nvarchar(20),FirstName nvarchar(10),
                          City nvarchar(15),Country nvarchar(15),MontoVentas float,
                          MontoDescontado float)
AS
BEGIN
    declare @promedio as float
    INSERT INTO @Resultado
    SELECT
        TOP (@Top) S.CompanyName,S.ContactName,S.City AS CompanyCity,
        S.Region, S.PostalCode, S.Country AS CompanyCountry,
        E.LastName, E.FirstName, E.City, E.Country,
        sum(d.Quantity * d.unitprice) as MontoVentas,
        convert(decimal(12,2),sum(d.Discount)) as MontoDescontado
    FROM
        EMPLOYEES E INNER JOIN ORDERS O ON E.EmployeeID=O.EmployeeID INNER JOIN [Order Details] D
        INNER JOIN PRODUCTS P ON D.ProductID=P.ProductID INNER JOIN SUPPLIERS S
        ON P.supplierID=S.supplierID ON O.orderid=D.orderid
    WHERE
        month(orderdate)=@Mes AND year(orderdate)=@Anno
    GROUP BY
        S.CompanyName,S.ContactName,S.City,S.Region,
        S.PostalCode,S.Country,E.LastName,E.FirstName,
        E.City,E.Country
    ORDER BY
        sum(d.Quantity * d.unitprice) DESC

    RETURN
END
GO

8.      Crear un Store Procedure que liste los productos y cantidad comprada en unidades y monto a pagar en la compra por cada cliente, obtener el listado para el nombre de una categoría del producto que es ingresado como parámetro al procedimiento. Del cliente indicar su nombre, país y ciudad, del producto su nombre precio unitario y de la categoría a la que pertenece el nombre y descripción de la categoría.

Solución:



CREATE PROCEDURE uspListarProductos @CategoryName nvarchar(15)
AS
    SELECT 
        C.CompanyName,C.City,C.Country,P.ProductName,P.UnitPrice,
        Ct.CategoryName,CONVERT(varchar(300),Ct.[Description]) as Descripcion, 
        sum(d.Quantity) as Unidades,sum(d.Quantity * d.unitprice) as Monto
    FROM 
        CUSTOMERS C INNER JOIN ORDERS O ON C.CustomerID=O.CustomerID INNER JOIN OrderDetails D
        INNER JOIN PRODUCTS P ON D.ProductID=P.ProductID INNER JOIN CATEGORIES Ct 
        ON P.CategoryID=Ct.CategoryID ON O.orderid=D.orderid
    WHERE 
        Ct.CategoryName=@CategoryName
    GROUP BY 
        C.CompanyName,C.City,C.Country,P.ProductName,P.UnitPrice,
        Ct.CategoryName,convert(varchar(300),Ct.[Description])

PC3 - Parte A

*Trabajando con la base de datos “Ventas”:

1.      Crear un trigger que actualice la cantidad de unidades que se tiene en stock [Q_Stock] de la tabla Productos cuando el vendedor modifique el pedido de un cliente.

Tener presente que el campo [Q_Stock] se debe actualizar de forma correcta para los siguientes casos:

Ø  El cliente aumenta la cantidad unidades compradas

Ø  El cliente disminuye la cantidad unidades compradas

Ø  Validar la cantidad de unidades compradas (Mayor que cero).


Solución:



CREATE TRIGGER TRIGGER_VENTA
ON PRODUCTO
AFTER INSERT
AS
BEGIN
UPDATE PRODUCTO
SET Q_STOCK = Q_STOCK - (SELECT I.Q_CANTIDAD FROM INSERTED I)
WHERE C_PRODUCTO = (SELECT I.C_PRODUCTO FROM INSERTED I)
END
GO 

2.      Crear un trigger que actualice el monto de la venta total [S_VentaTotal] de la tabla Ventas cuando el vendedor anule o elimine la compra de uno de los productos en venta a un cliente.

      Solución:


CREATE TRIGGER TRIGGER2_VENTA
ON VENTA
AFTER INSERT
AS
BEGIN
UPDATE VENTA
SET S_VENTATOTAL = S_VENTATOTAL + (SELECT I.Q_CANTIDAD*P.S_PRECIO_UNITARIO*(1-I.S_DESCUENTO)
FROM INSERTED I JOIN PRODUCTO P ON (I.C_PRODUCTO = P.C_PRODUCTO))        
   WHERE C_VENTA = (SELECT I2.C_VENTA FROM INSERTED I2)                                                
END

3.      Crear un trigger que actualice la cantidad de unidades que se tiene en stock [Q_Stock] de la tabla Productos cuando el vendedor anule o elimine la compra de uno de los productos en venta a un cliente.


Solución:



UPDATE PRODUCTO SET Q_STOCK = Q_STOCK + 3 WHERE C_PRODUCTO = 1 
UPDATE PRODUCTO SET Q_STOCK = Q_STOCK + 2 WHERE C_PRODUCTO = 10


UPDATE VENTA SET S_VENTATOTAL = 0.00 WHERE C_VENTA = 1  
DELETE VENTA

*Trabajando con la base de datos “Ahorros”:

4.      Crear un procedimiento que permita abrir una cuenta de ahorros para un cliente con un saldo inicial.

Los parámetros del procedimiento son el código del cliente y monto de apertura.

Considerar las siguientes especificaciones:

·      Las cuentas de ahorro son secuenciales y se numeran anteponiendo la “C” de cuenta y un correlativo. Ejemplo C0001, C0002, C0003,...etc.

·      Al crear la cuenta su primer movimiento será el de apertura de cuenta de ahorros.

·      El monto de apertura de una cuenta de ahorros será mayor a 100 nuevos soles.

Solución:



CREATE PROCEDURE ABRIR_CUENTA @CLIENTE VARCHAR(5), @MONTO MONEY
AS
BEGIN TRANSACTION
   IF (@MONTO >= 100)
   BEGIN
   DECLARE @CTA INT
   DECLARE @CTA_NUM CHAR(5)

   SELECT @CTA = COUNT(*)
   FROM CUENTA
   SET @CTA_NUM = 'C' + RIGHT( '000' + CAST(@CTA +1 AS VARCHAR(5)),4)
         INSERT INTO CUENTA VALUES(@CTA_NUM, @CLIENTE, @MONTO)
         INSERT INTO MOVIMIENTO VALUES(@CTA_NUM,1,'10',@MONTO,GETDATE())
       
   END
   ELSE
   BEGIN
      PRINT 'NO ALCANZA EL MONTO MINIMO'
   END
   COMMIT

5.      Crear un procedimiento que permita realizar operaciones de retiro de un determinado monto en nuevos soles o en dólares de una cuenta de ahorros en soles perteneciente a un cliente.

Los parámetros del procedimiento serán el tipo de moneda, monto a retirar y el número de la cuenta de ahorros del cliente.

Considerar las siguientes especificaciones:

·      El monto de retiro o su equivalente traducido a nuevos soles no deberá ser mayor al saldo de la cuenta.

·      Cuando el monto de retiro es en dólares deberá obtener y aplicar el cambio del día (Venta dólares).

·      Por las operaciones de retiro se cobra una comisión de S/. 0.10 a partir del quinto retiro en un mes, el cual se cumula en la cuenta “M0002” con sus movimientos de depósito respectivos.

·      Se guarda en la cuenta “ITF02” la aplicación del impuesto a las transacciones financieras correspondiente al 0.8 % del monto retirado en soles.

Solución:




CREATE PROCEDURE RETIRA_MONTO @CUENTA CHAR(5), @MONTO MONEY
AS
BEGIN TRANSACTION
      DECLARE @SALDO MONEY
      SELECT @SALDO = C.S_SALDO  -- LEER SALDO DE LA CUENTA
      FROM CUENTA C
      WHERE C.NRO_CUENTA = @CUENTA
   
      IF @MONTO > @SALDO    -- VALIDA EL MONTO A RETIRAR
            ROLLBACK        -- SOLO SI SE REALIZARON CAMBIOS
      ELSE
      BEGIN
            DECLARE @NRO_MOV INT    -- CALCULAR EL NRO_MOV SECUENCIAL
            SELECT @NRO_MOV = MAX(C_SECUENCIA) + 1
            FROM MOVIMIENTO
            WHERE NRO_CUENTA = @CUENTA
                     
            -- INSERTA EL NUEVO MOVIMIENTO
            INSERT INTO MOVIMIENTO VALUES(@CUENTA,@NRO_MOV,'30',@MONTO,GETDATE())
         
            -- ACTUALIZA EL SALDO
            UPDATE CUENTA
            SET S_SALDO = S_SALDO - @MONTO
            WHERE NRO_CUENTA = @CUENTA
         
            COMMIT  -- ACTUALIZA CAMBIOS
      END

6.      Crear un procedimiento que permita realizar la transferencia de un monto en nuevos soles de una cuenta de ahorros origen a otra cuenta de ahorros destino ambas en soles.

Los parámetros del procedimiento serán el monto a transferir, el número de la cuenta de ahorros origen y el número de la cuenta de ahorros destino.

Considerar las siguientes especificaciones:

·         El monto de transferencia en nuevos soles no deberá ser mayor al saldo de la cuenta.

·         Por las operaciones de transferencia se cobra una comisión de S/.5 para montos menores a S/.1000 y del 0.5% del monto de transferencia a partir de S/. 1000, el cual se cumula en la cuenta “M0002” con sus movimientos de depósito respectivos para transferencias entre cuentas de diferentes clientes.

·         Si la cuenta origen y la cuenta destino pertenecen al mismo cliente no se cobra ninguna comisión.

·         Se guarda en la cuenta “ITF02” la aplicación del impuesto a las transacciones financieras correspondiente al 0.8 % del monto transferido en soles.


Solución:



CREATE PROCEDURE CUENTABASE_CUENTADESTINO @CUENTAB CHAR(5), @CUENTAD CHAR(5),@MONTO MONEY
AS
BEGIN TRANSACTION
      DECLARE @SALDO MONEY
      SELECT @SALDO = C.S_SALDO
      FROM CUENTA C
      WHERE C.NRO_CUENTA = @CUENTAB
     
      IF @MONTO > @SALDO
            ROLLBACK
      ELSE
      BEGIN
            DECLARE @NRO_MOV INT
            SELECT @NRO_MOV = MAX(C_SECUENCIA) + 1
            FROM MOVIMIENTO
            WHERE NRO_CUENTA = @CUENTAB
           
            INSERT INTO MOVIMIENTO VALUES(@CUENTAB,@NRO_MOV,'40',@MONTO,GETDATE()) 
     
            UPDATE CUENTA
            SET S_SALDO = S_SALDO - @MONTO
            WHERE NRO_CUENTA = @CUENTAB
           
            UPDATE CUENTA
            SET S_SALDO = S_SALDO + @MONTO
            WHERE NRO_CUENTA = @CUENTAD
           
            COMMIT 
      END

      

*Trabajando con la base de datos “Northwind”: 

7.      Listar mediante una función los empleados que han realizado ventas superiores al promedio durante el año 1997 junto a los clientes que han realizado compras inferiores al promedio en el mismo año, la función debe retornar los nombres, ciudad, región, código postal, país, ventas y compras en una sola tabla.

      Modificar la función de tal modo que se aplique lo mismo pero para cualquier mes y año.

Solución:


CREATE FUNCTION PROMEDIO()
RETURNS TABLE
AS RETURN(SELECT     Orders.OrderDate, Employees.FirstName, Employees.LastName, Employees.City, Employees.Region,
                     Employees.PostalCode, Employees.Country, Customers.CompanyName, Customers.City AS Expr1, Customers.Region AS Expr2,
                     Customers.PostalCode AS Expr3, Customers.Country AS Expr4
FROM                 dbo.Employees INNER JOIN
                      Orders ON Employees.EmployeeID = Orders.EmployeeID INNER JOIN
                      Customers ON Orders.CustomerID = Customers.CustomerID
WHERE               (Orders.OrderDate >= '1997-01-01') AND (Orders.OrderDate <= '1997-12-31'))

8.   Escribir un procedimiento que permita insertar, modificar y eliminar productos considerando la integridad de los datos de acuerdo a la operación a realizar.

            Asumir los parámetros de entrada/salida necesarios del procedimiento.

Solución:


 CREATE PROCEDURE GESTION_PRODUCTO @N INT, @CPROD INT, @NPROD NVARCHAR(40), @SUPLIER INT,
                                 @CATEGO INT, @QUANT NVARCHAR(20), @UPRICE MONEY,
                                 @UINSTOCK SMALLINT, @UONORDER SMALLINT, @REORDER SMALLINT,
                                 @DISCON BIT, @STOCK INT
 AS
 BEGIN
 IF @N = 1

              INSERT INTO Products
              values( @NPROD , @SUPLIER,@CATEGO, @QUANT, @UPRICE ,
                     @UINSTOCK , @UONORDER , @REORDER , @DISCON)
 IF @N = 2
               UPDATE Products
               SET UnitsInStock = UnitsInStock - @STOCK
               WHERE ProductID = @CPROD
 IF @N = 3

               DELETE FROM Products
               WHERE ProductID = @CPROD
 END