TA (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).

CODIGO:

CREATE TRIGGER ACTUALIZAR_STOCK ON VENTA_DETALLE

FOR UPDATE

AS

BEGIN

DECLARE @CANT INT

DECLARE @AUX INT

DECLARE @PROD INT

DECLARE @STOCK INT

SELECT @AUX = Q_CANTIDAD FROM INSERTED

SELECT @PROD = C_PRODUCTO FROM INSERTED

SELECT @CANT = Q_CANTIDAD FROM DELETED

SELECT @STOCK=P.Q_STOCK FROM PRODUCTO P WHERE P.C_PRODUCTO=@PROD

IF UPDATE(Q_CANTIDAD)

BEGIN

IF(@CANT-@AUX) != 0

BEGIN

IF(@STOCK+@CANT-@AUX<0)

BEGIN

RAISERROR ('EL STOCK ES MENOR QUE CERO',16,1);

ROLLBACK TRANSACTION;

RETURN;

END

ELSE

BEGIN

UPDATE PRODUCTO

SET Q_STOCK=@STOCK+@CANT-@AUX

WHERE C_PRODUCTO=@PROD

END

END

END

END

TA (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.

CODIGO:

CREATE TRIGGER ACTUALIZAR_MONTO_VENTAS ON VENTA_DETALLE

FOR DELETE

AS

BEGIN

DECLARE @cant_prod INT

DECLARE @prec_unit MONEY

DECLARE @VENTA_TOTAL MONEY

SELECT @cant_prod = Q_CANTIDAD FROM DELETED

SELECT @VENTA_TOTAL= S_VENTA_TOTAL FROM VENTA_DETALLE WHERE C_PRODUCTO=(SELECT C_PRODUCTO FROM DELETED)

SELECT @prec_unit = S_PRECIO_UNITARIO FROM PRODUCTO

WHERE C_PRODUCTO=(SELECT C_PRODUCTO FROM DELETED)

IF(@VENTA_TOTAL-@cant_prod*@prec_unit<0)

BEGIN

RAISERROR ('LA VENTA TOTAL ES MENOR QUE CERO',16,1);

ROLLBACK TRANSACTION;

RETURN;

END

ELSE

BEGIN

UPDATE VENTA

SET S_VENTA_TOTAL=@VENTA_TOTAL-@cant_prod*@prec_unit

WHERE C_VENTA = (SELECT C_VENTA FROM DELETED)

END

END

TA (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.

CODIGO:

CREATE TRIGGER ACTUALIZAR_STOCK2 ON VENTA_DETALLE

FOR DELETE

AS

BEGIN

DECLARE @CANT INT

DECLARE @PROD INT

DECLARE @STOCK INT

SELECT @PROD = C_PRODUCTO FROM DELETED

SELECT @CANT = Q_CANTIDAD FROM DELETED

SELECT @STOCK=P.Q_STOCK FROM PRODUCTO P WHERE P.C_PRODUCTO=@PROD

UPDATE PRODUCTO

SET Q_STOCK=@STOCK+@CANT

WHERE C_PRODUCTO=@PROD

END

TA (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.

CODIGO:

CREATE PROCEDURE APERTURA_CUENTA @COD_CLIENTE CHAR(5), @MONTO MONEY

AS

BEGIN

TRANSACTION

DECLARE @CUENTA CHAR(5)

DECLARE @AUX INT

SELECT @CUENTA = MAX(C_CUENTA) FROM CUENTA

IF @CUENTA is NULL

BEGIN

SET @CUENTA = 'C0001'

INSERT INTO CUENTA VALUES(@CUENTA, @COD_CLIENTE, @MONTO)

INSERT INTO MOVIMIENTO VALUES(@CUENTA,1,'1',@MONTO,GETDATE())

END

ELSE

BEGIN

SET @AUX = CONVERT(INT,RIGHT(@CUENTA,4)) + 1

SET @CUENTA = 'C'+RIGHT('000'+CONVERT(VARCHAR,@AUX),4)

INSERT INTO CUENTA VALUES(@CUENTA, @COD_CLIENTE, @MONTO)

INSERT INTO MOVIMIENTO VALUES(@CUENTA,1,'1',@MONTO,GETDATE())

END

COMMIT

TA (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.

CODIGO

CREATE PROCEDURE RETIRAR_MONTO @TIPO_M INT, @MONTO MONEY, @CUENTA VARCHAR(5)

AS

BEGIN

TRANSACTION

DECLARE @MONTO_ACTUAL MONEY

DECLARE @NUM_RETIRO_MES INT

SELECT @MONTO_ACTUAL=C.S_MONTO FROM CUENTA C WHERE C.C_CUENTA=@CUENTA

SELECT @NUM_RETIRO_MES=C.NUM_RET_MES FROM CUENTA C WHERE C.C_CUENTA=@CUENTA

IF(@TIPO_M=1)--DOLARES

BEGIN

SELECT @MONTO=@MONTO*2.70;

END

IF(@MONTO>@MONTO_ACTUAL)

BEGIN

RAISERROR ('EL MONTO A RETIRAR ES MAYOR AL MONTO ACTUAL',16,1)

ROLLBACK

RETURN

END

ELSE

BEGIN

IF(@NUM_RETIRO_MES>4)

BEGIN

UPDATE CUENTA

SET S_MONTO+=0.10

WHERE CUENTA.C_CUENTA='M0002'

UPDATE CUENTA

SET S_MONTO-=0.10

WHERE CUENTA.C_CUENTA=@CUENTA

END

UPDATE CUENTA

SET S_MONTO+=@MONTO*0.008

WHERE CUENTA.C_CUENTA='ITF02'

UPDATE CUENTA

SET S_MONTO-=@MONTO*0.008

WHERE CUENTA.C_CUENTA=@CUENTA

UPDATE CUENTA

SET S_MONTO=@MONTO_ACTUAL-@MONTO,NUM_RET_MES=@NUM_RETIRO_MES+1

WHERE CUENTA.C_CUENTA =@CUENTA

END

COMMIT

TA (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.

CODIGO

CREATE PROCEDURE TRANSFERENCIA_MONTO @MONTO MONEY, @CUENTA_O VARCHAR(5), @CUENTA_D VARCHAR(5)

AS

BEGIN

TRANSACTION

DECLARE @SALDO MONEY

SELECT @SALDO=C.S_SALDO FROM CUENTA C WHERE C.C_CUENTA=@CUENTA_O

IF(@MONTO>@SALDO)

BEGIN

RAISERROR ('EL MONTO SUPERA SU SALDO',16,1)

ROLLBACK

RETURN

END

IF(@CUENTA_O!=@CUENTA_D)

BEGIN

IF(@MONTO<1000)

BEGIN

UPDATE CUENTA

SET S_SALDO-=5

WHERE C_CUENTA=@CUENTA_O

UPDATE CUENTA

SET S_MONTO+=5

WHERE CUENTA.C_CUENTA='M0002'

END

IF(@MONTO>1000)

BEGIN

UPDATE CUENTA

SET S_SALDO-=05*@MONTO

WHERE C_CUENTA=@CUENTA_O

UPDATE CUENTA

SET S_MONTO+=0.005*@MONTO

WHERE CUENTA.C_CUENTA='M0002'

END

END

UPDATE CUENTA

SET S_MONTO+=@MONTO*0.008

WHERE CUENTA.C_CUENTA='ITF02'

UPDATE CUENTA

SET S_MONTO-=@MONTO*0.008

WHERE CUENTA.C_CUENTA=@CUENTA_O

COMMIT

TA (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.

CODIGO

CREATE FUNCTION LISTAR_EMPLEADOS(@Mes INT,@Anno INT)

RETURNS @Resultado TABLE(Names NVARCHAR(40),City NVARCHAR(15),Region NVARCHAR(15),

PostalCode NVARCHAR(10),Country NVARCHAR(15),Ventas FLOAT,

Compras FLOAT)

AS

BEGIN

DECLARE @promedio FLOAT

SELECT @promedio=SUM((d.Quantity * d.unitprice)/ d.Quantity)

FROM [Order Details] d INNER JOIN Orders o ON o.orderid=d.orderid

WHERE MONTH(o.orderdate)=@Mes AND YEAR(o.orderdate)=@Anno

INSERT INTO @Resultado

SELECT E.LastName +' '+ E.FirstName Nombre,E.City,E.Region,E.PostalCode,E.Country,

SUM(d.Quantity * d.unitprice) Ventas,0.00 Compras

FROM EMPLOYEES E JOIN ORDERS O ON E.EmployeeID=O.EmployeeID JOIN [Order Details] D ON O.orderid=D.orderid

WHERE MONTH(o.orderdate)=@Mes AND YEAR(o.orderdate)=@Anno

GROUP BY E.LastName,E.FirstName ,E.City,E.Region,E.PostalCode,E.Country

HAVING SUM(d.Quantity * d.unitprice) >@promedio

UNION

SELECT C.CompanyName Nombre,C.City,C.Region,C.PostalCode,C.Country,

0.00 Ventas,sum(d.Quantity * d.unitprice) Compras

FROM CUSTOMERS C INNER JOIN ORDERS O ON C.CustomerID=O.CustomerID

INNER JOIN [Order Details] D ON O.orderid=D.orderid

WHERE MONTH(o.orderdate)=@Mes AND YEAR(o.orderdate)=@Anno

GROUP BY C.CompanyName,C.City,C.Region,C.PostalCode,C.Country

HAVING SUM(d.Quantity * d.unitprice) <@promedio

RETURN

END

Consultas

BASE DE DATOS: CICLISMO

JOIN

•Mostrar los nombres de los productos y el nombre de su respectivo tipo:

SELECT P.[Nombre producto], T.[Nombre tipo producto]

FROM Productos P JOIN Tipo_Productos T ON (P.[ID tipo producto]=T.[ID tipo producto])

•Mostrar los nombres de los empleados y su respectivo pais:

SELECT E.Nombre, D.Pais

FROM Empleados E JOIN Direccion_Empleados D ON (E.[ID empleado]=D.[ID empleado])

HAVING

•Mostrar los tipos de productos con ID menor a 4 y el precio total de cada uno:

SELECT T.[Nombre tipo producto], SUM(P.Precio) 'Precio total'

FROM Productos P JOIN Tipo_Productos T ON (P.[ID tipo producto]=T.[ID tipo producto])

GROUP BY T.[Nombre tipo producto]

HAVING T.[Nombre tipo producto]<4

•Mostrar la cantidad de empleados en los países Canadá y USA:

SELECT D.Pais, COUNT (E.Nombre) 'cantidad de nombres por pais'

FROM Empleados E JOIN Direccion_Empleados D ON (E.[ID empleado]=D.[ID empleado])

GROUP BY D.Pais

HAVING D.Pais='Canada' OR D.Pais='USA'

GROUP BY

•Mostrar los tipos de productos y el precio total de cada uno:

SELECT T.[Nombre tipo producto], SUM(P.Precio) 'Precio total'

FROM Productos P JOIN Tipo_Productos T ON (P.[ID tipo producto]=T.[ID tipo producto])

GROUP BY T.[Nombre tipo producto]

•Mostrar la cantidad de empleados por cada país:

SELECT D.Pais, COUNT (E.Nombre) 'cantidad de nombres por pais'

FROM Empleados E JOIN Direccion_Empleados D ON (E.[ID empleado]=D.[ID empleado])

GROUP BY D.Pais

JOIN, WHERE, GROUP BY, HAVING

•Mostrar los tipos de productos con ID menor a 4 y precio unitario menor que 16:

SELECT T.[Nombre tipo producto], SUM(P.Precio) 'Precio total'

FROM Productos P JOIN Tipo_Productos T ON (P.[ID tipo producto]=T.[ID tipo producto])

WHERE P.Precio>16

GROUP BY T.[Nombre tipo producto]

HAVING T.[Nombre tipo producto]<4

•Mostrar la cantidad de empleados de los países Canadá y USA que tengan anexo mayor que 500 :

SELECT D.Pais, COUNT (E.Nombre) 'cantidad de nombres por pais'

FROM Empleados E JOIN Direccion_Empleados D ON (E.[ID empleado]=D.[ID empleado])

WHERE E.Anexo>500

GROUP BY D.Pais

HAVING D.Pais='Canada' OR D.Pais='USA'

OUTER JOIN

•Left Join:

SELECT P.[Nombre producto], P.Color.P[M/F],P.Precio,T.[Nombre tipo producto]

FROM Productos P LEFT JOIN Tipo_Productos T ON (P.[ID tipo producto]=T.[ID tipo producto])

•Right Join:

SELECT E.Nombre,E.[Telefono domicilio],D.Ciudad,D.Pais

FROM Empleados E RIGHT JOIN Direccion_Empleados D ON (E.[ID empleado]=D.[ID empleado])