PC 03

Primera parte:

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

Triger:

ALTER TRIGGER ACTUALIZAR_PRODUCTO ON VENTA_DETALLE

FOR UPDATE

AS

BEGIN

   UPDATE PRODUCTO

   SET Q_STOCK = Q_STOCK – (SELECT I.Q_CANTIDAD FROM INSERTED)

   WHERE PRODUCTO.C_PRODUCTO = (SELECT I.C_PRODUCTO FROM INSERTED I)

END

 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.

 

CREATE TRIGGER ACTUALIZAR_PRODUCTO_ANULA_COMPRA ON VENTA_DETALLE

FOR DELETE

AS

BEGIN

            UPDATE PRODUCTO

            SET Q_STOCK = Q_STOCK – (SELECT D.Q_CANTIDAD FROM DELETED D)

            WHERE  C_PRODUCTO = (SELECT D.C_PRODUCTO FROM DELETED D)

END

DELETE FROM VENTA_DETALLE

WHERE C_PRODUCTO=3 AND C_VENTA = 2

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.

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

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.

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.

  (Base de Datos "NortWhind")

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.

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.

 

 

Segunda parte:

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.

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.

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.

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

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.

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.

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

 

 

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.

TAREA NUMERO 6

1.   Funciones:

• Implementar una función que devuelva el número de participantes en el campeonato de AJEDREZ

            FUNCIÓN:

CREATE FUNCTION NUMERO_PARTICIPANTES()
RETURNS INT
AS BEGIN
DECLARE @Num INT
SELECT @Num =COUNT(*)
FROM PARTICIPANTE
RETURN @Num
END

Para corroborar:

SELECT DBO.NUMERO_PARTICIPANTES() ‘CANTIDAD_PARTICIPANTES’

·         Implementar una función que devuelva la lista de participantes en el campeonato de AJEDREZ

            FUNCIÓN:

CREATE FUNCTION DEVOLV_LISTA_PARTICIPANTES()

RETURNS TABLE
AS RETURN
SELECT * FROM PARTICIPANTE

Para corroborar:

SELECT * FROM DBO.DEVOLV_LISTA_PARTICIPANTES()

·         Implementar una función que devuelva el número de equipos en los que participo un jugador en CAMPEONATO

            FUNCIÓN:

CREATE FUNCTION NUMERO_EQUIPOS_X_JUGADOR( @NUM_EQ_JUG VARCHAR(220))

RETURNS INT
AS BEGIN
DECLARE @NUM INT
SELECT @NUM =COUNT(*)
FROM JUG_X_EQ_X_CAMP A
INNER JOIN JUGADOR B ON A.C_JUGADOR=B.C_JUGADOR
WHERE B.N_JUGADOR= @NUM_EQ_JUG
RETURN @NUM
END

Para corroborar:

SELECT DBO.NUMERO_EQUIPOS_X_JUGADOR(‘LEAO BUTRON’) ‘NUM_EQUIPOS’

·         Implementar una función que devuelva la lista de equipos en los que participo un jugador en CAMPEONATO

FUNCIÓN:

CREATE FUNCTION LISTA_EQUIPOS_X_PARTICIPANTE( @N_JUGADOR VARCHAR(220))

RETURNS TABLE
AS RETURN
SELECT A.N_EQUIPO ‘LISTA_EQUIPOS’
FROM JUG_X_EQ_X_CAMP B
INNER JOIN EQUIPO A ON B.C_EQUIPO=A.C_EQUIPO
INNER JOIN JUGADOR C ON B.C_JUGADOR=C.C_JUGADOR
WHERE C.N_JUGADOR= @N_JUGADOR

Para corroborar:

SELECT * FROM LISTA_EQUIPOS_X_PARTICIPANTE(‘WALTER WILCHEZ’)

2.   Procedimientos:

·         Crear un procedimiento que registre un jugador, solo datos personales en CAMPEONATO

Procedimiento:

CREATE PROCEDURE AGREGAR_JUGADOR

@C_JUGADOR char(3),
@N_JUGADOR varchar(220),
@D_NACIMIENTO datetime
AS
INSERT INTO JUGADOR (C_JUGADOR,N_JUGADOR,D_NACIMIENTO)
VALUES (@C_JUGADOR,@N_JUGADOR,@D_NACIMIENTO)

Para ejecutar:

EXEC AGREGAR_JUGADOR ‘J13′,‘JUANITO PEREZ’,’19791007′

·         Crear un procedimiento que liste los jugadores de un Equipo en CAMPEONATO

Procedimiento:

CREATE PROCEDURE LISTAR_JUGADORES_EQUIPO

@N_EQUIPO varchar(220)
AS
SELECT * FROM DBO.JUG_X_EQ_X_CAMP A
INNER JOIN dbo.EQUIPO B ON A.C_EQUIPO=B.C_EQUIPO
WHERE B.N_EQUIPO =@N_EQUIPO

Para ejecutar:

EXEC LISTAR_JUGADORES_EQUIPO ‘UNIVERSITARIO DE DEPORTES’

·         Crear un procedimiento que registre los movimientos realizados por un jugador y su comentario en AJEDREZ

Procedimiento:

CREATE PROCEDURE AGREGAR_MOVIMIENTO

@N_ORDEN int,
@N_PARTIDA varchar(3),
@N_FICHA varchar(20),
@T_POS_INICIAL varchar(20),
@T_POS_FINAL varchar(20),
@T_COMENTARIO varchar(20)
AS
INSERT INTO DBO.MOVIMIENTO (#_ORDEN,#_PARTIDA,N_FICHA,T_POS_INICIAL,T_POS_FINAL,T_COMENTARIO)
VALUES (@N_ORDEN,@N_PARTIDA,@N_FICHA,@T_POS_INICIAL,@T_POS_

Para ejecutar:

EXEC AGREGAR_MOVIMIENTO 10,‘R01′,‘Reina’,‘B01′,‘C02′,‘Excelente’

·         Crear un procedimiento que liste los vehículos reservados de un cliente en ALQUILER

Procedimiento:

CREATE PROCEDURE LISTAR_VEHICULOS_RESERVADOS

@N_DNI INT
AS
SELECT C.N_CLIENTE, A.* FROM DBO.VEHICULO_RESERVA_ALQUILER A
INNER JOIN DBO.RESERVA_ALQUILER B ON A.C_RESERVA=B.C_RESERVA
INNER JOIN DBO.CLIENTE C ON B.C_CLIENTE=C.C_CLIENTE
WHERE C.#_DNI=@N_DNI

Para ejecutar:

EXEC LISTAR_VEHICULOS_RESERVADOS 43335728

3.   Triggers:

·         Implementar y comprobar un trigger que contabilice el número de vehículos reservados en ALQUILER

Triggers:

CREATE TRIGGER NUM_VEHICULOS_RESERVADOS

ON DBO.VEHICULO_RESERVA_ALQUILER
FOR INSERT, UPDATE, DELETE
AS
SELECT COUNT(*) ‘NUM_VEHICULOS_RESERVADOS’ FROM DBO.VEHICULO_RESERVA_ALQUILER

Para comprobar:

INSERT INTO DBO.VEHICULO_RESERVA_ALQUILER VALUES (27,‘R08′,‘V07′)

·         Implementar y comprobar un trigger que contabilice el número de participantes en Ajedrez

Trigger:

CREATE TRIGGER NUM_PARTICIPANTES

ON DBO.PARTICIPANTE
FOR INSERT, UPDATE, DELETE
AS
SELECT COUNT(*)AS ‘NUM_PARTICIPANTES’ FROM DBO.PARTICIPANTE

Para comprobar:

INSERT INTO DBO.PARTICIPANTE VALUES (‘A17′, ‘P01′ ,‘REY AJEDRECIO’ ,‘Av. Los Insurgentes 354′, ’1990-05-03′)

·         Implementar y comprobar un trigger que contabilice el número partidos que participo un Jugador en CAMPEONATO

Trigger:

CREATE TRIGGER NUM_PARTIDOS_X_JUGADOR

ON DBO.JUGADOR_X_PARTIDO
FOR INSERT, UPDATE, DELETE
AS
SELECT A.C_JUGADOR,A.N_JUGADOR,COUNT(*) ‘NUM_PARTIDOS’ FROM DBO.JUGADOR_X_PARTIDO B
INNER JOIN DBO.JUGADOR A
ON B.C_JUGADOR = A.C_JUGADOR
GROUP BY A.C_JUGADOR,A.N_JUGADOR

Para comprobar:

INSERT INTO DBO.JUGADOR_X_PARTIDO VALUES (‘J07′,08,‘CENTRO DELANTERO’,12,‘N’,‘N’,‘N’)

·         Implementar mediante triggers la auditoria de participantes en AJEDREZ

Trigger:

CREATE TRIGGER AUDITORIA_PARTICIPANTES

ON DBO.PARTICIPANTE
FOR INSERT, UPDATE, DELETE
AS DECLARE
@VARIABLE VARCHAR(200)
BEGIN
IF
EXISTS (SELECT * FROM INSERTED)
BEGIN
SELECT
@VARIABLE= C_ASOCIADO FROM INSERTED
PRINT
‘CODIGO DE PARTICIPANTE INSERTADO : ‘ + @VARIABLE
PRINT
‘FECHA : ‘ + CONVERT(VARCHAR(30),GETDATE())
PRINT
‘USUARIO : ‘ + CONVERT(VARCHAR(30),SUSER_SNAME())
PRINT
‘COMPUTADOR :’ + CONVERT(VARCHAR(30),HOST_NAME())
END
IF
EXISTS (SELECT * FROM DELETED)
BEGIN
SELECT
@VARIABLE= C_ASOCIADO FROM DELETED
PRINT
‘CODIGO DE PARTICIPANTE ELIMINADO : ‘ + @VARIABLE
PRINT
‘FECHA : ‘ + CONVERT(VARCHAR(30),GETDATE())
PRINT
‘USUARIO : ‘ + CONVERT(VARCHAR(30),SUSER_SNAME())
PRINT
‘COMPUTADOR :’ + CONVERT(VARCHAR(30),HOST_NAME())
END
END

Para comprobar:

DELETE FROM PARTICIPANTE WHERE C_ASOCIADO = ‘A26′

INSERT INTO PARTICIPANTE VALUES (‘A26′, ‘P05′, ‘PEONERO PANTOJA’, ’Av. De La Republica 085′, ’1990-05-27′)