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