Ejercicios Resueltos de SQL Server: Triggers, Procedimientos Almacenados y Cursores

Ejercicios de Transact-SQL (T-SQL) en SQL Server

A continuación se presenta una serie de ejercicios prácticos orientados a la gestión de una base de datos de autoescuela mediante triggers (disparadores), procedimientos almacenados (stored procedures) y cursores.

Ejercicio 1: Trigger para Actualización de Kilometraje en Prácticas

Enunciado: Crear un trigger tal que, cada vez que se inserta una práctica a un alumno, se sumen 50 km al vehículo que tiene asignado dicho alumno.

CREATE TRIGGER PRAC
ON PRACTICA
FOR INSERT
AS
    DECLARE @CODMON INT
    DECLARE @CODAL INT

    SELECT @CODAL = A.CodMon#
    FROM ALUMNO A 
    INNER JOIN INSERTED I ON A.CodAl# = I.CodAl#

    SELECT @CODMON = M.CodMon#
    FROM MONITOR M 
    INNER JOIN ALUMNO A ON M.CodMon# = A.CodAl#
    WHERE M.CodMon# = @CODMON

    UPDATE COCHE
    SET KMS -= 50
    WHERE CodCoche# = (
        SELECT CodCoche#
        FROM MONITOR
        WHERE CodMon# = @CODAL
    );

Ejercicio 2: Procedimiento Almacenado para Contabilizar Prácticas por Mes y Matrícula

Enunciado: Crear un procedimiento almacenado que, introduciendo la matrícula de un coche y una fecha/mes, devuelva el número de prácticas que ha realizado ese coche en dicho periodo. Se debe validar que los parámetros de entrada no estén vacíos, que el vehículo exista y que el mes esté comprendido entre 1 y 12. Las condiciones de error se deben notificar por pantalla.

CREATE PROCEDURE proc_2
    @PE_MATRICULA CHAR(7),
    @PE_FECHA DATE
AS
    DECLARE @CODMON INT
    DECLARE @CODMON_AL INT
    DECLARE @TOTAL INT

    IF (@PE_MATRICULA = '' OR @PE_FECHA = '')
    BEGIN
        PRINT 'CAMPOS INCORRECTOS';
    END
    ELSE
    BEGIN
        IF (@PE_MATRICULA NOT IN (SELECT DISTINCT MATRICULA FROM COCHE))
        BEGIN
            PRINT 'NO ESTA EN LA BASE DE DATOS';
        END
        ELSE
        BEGIN
            IF (MONTH(@PE_FECHA) < 1 OR MONTH(@PE_FECHA) > 12)
            BEGIN
                PRINT 'FECHA INCORRECTA';
            END
            ELSE
            BEGIN
                SELECT @CODMON = C.CodCOCHE#
                FROM COCHE C
                WHERE MATRICULA = @PE_MATRICULA;

                SELECT @CODMON_AL = A.CodMon#
                FROM ALUMNO A 
                INNER JOIN MONITOR M ON A.CodMon# = M.CodMon#;

                SELECT @TOTAL = COUNT(Num_Prac)
                FROM Practica P 
                INNER JOIN Alumno A ON P.CodAl# = A.CodAl#
                WHERE A.CodAl# = @CODMON_AL;
            END
        END
    END;

Ejercicio 3: Reasignación de Coche a Monitor según Carga de Alumnos

Enunciado: Crear un procedimiento almacenado que reciba el DNI de un monitor y la matrícula de un vehículo. Si el monitor está asignado a 15 alumnos, se debe modificar el vehículo asignado al monitor por el indicado por teclado. Validar que el DNI y la matrícula no estén vacíos y que ambos existan en la base de datos, mostrando los errores pertinentes por pantalla.

CREATE PROCEDURE proc_3
    @PE_DNI VARCHAR(8),
    @PE_MATRICULA CHAR(6)
AS
    DECLARE @PE_CODMON INT
    DECLARE @PE_TOTAL INT
    DECLARE @PE_MATRICULA_MON INT

    IF (@PE_DNI = '' OR @PE_MATRICULA = '')
    BEGIN
        PRINT 'ERROR EN LOS CAMPOS';
    END
    ELSE
    BEGIN
        IF (@PE_DNI NOT IN (SELECT DNI FROM MONITOR))
        BEGIN
            PRINT 'NO EXISTE';
        END
        ELSE
        BEGIN
            IF (@PE_MATRICULA NOT IN (SELECT MATRICULA FROM COCHE))
            BEGIN
                PRINT 'NO EXISTE';
            END
            ELSE
            BEGIN
                SELECT @PE_MATRICULA_MON = CodCoche#
                FROM COCHE
                WHERE MATRICULA = @PE_MATRICULA;

                SELECT @PE_CODMON = M.CodMon#
                FROM MONITOR M
                WHERE DNI = @PE_DNI;

                SELECT @PE_TOTAL = COUNT(CodAl#)
                FROM ALUMNO A 
                INNER JOIN MONITOR M ON A.CodMon# = M.CodMon#
                WHERE A.CodAl# = @PE_CODMON;

                IF (@PE_TOTAL < 15)
                BEGIN
                    UPDATE COCHE
                    SET CodCoche# = @PE_MATRICULA_MON
                    WHERE CodCoche# = (
                        SELECT CodCoche#
                        FROM MONITOR
                        WHERE MATRICULA = @PE_MATRICULA
                    );
                END
            END
        END
    END;

Ejercicio 4: Trigger para Reinicio de Kilómetros al Asignar Vehículo

Enunciado: Crear un trigger que, al asignar un coche a un monitor en una inserción, reinicie sus kilómetros a 0. Se incluye la sentencia de prueba correspondiente.

CREATE TRIGGER T1
ON MONITOR
FOR INSERT
AS
    UPDATE COCHE
    SET KMS = 0
    WHERE CodCOCHE# = (SELECT CodCOCHE# FROM inserted);

-- Inserción de prueba:
INSERT INTO MONITOR VALUES (50, '45421454s', 'LUIS', 'ESTRELLA', '10-1-1889', '658974521', 15);

Ejercicio 5: Cursor para Ajuste Dinámico de Precios según Tipo de Carnet

Enunciado: Implementar un cursor que modifique el precio de las clases prácticas: si el carnet del alumno es de tipo A o B se incrementa el precio un 10%, y si es de tipo C o D se le descuenta un 10%.

DECLARE @TIPO AS CHAR
DECLARE @CODAL AS INT

DECLARE CUR_CARNET CURSOR FOR 
    SELECT TIPOCARNET, CodAl# 
    FROM ALUMNO 
    WHERE Tipocarnet LIKE 'A' 
       OR Tipocarnet LIKE 'B' 
       OR Tipocarnet LIKE 'C' 
       OR Tipocarnet LIKE 'D'

OPEN CUR_CARNET
FETCH NEXT FROM CUR_CARNET INTO @TIPO, @CODAL

WHILE (@@FETCH_STATUS = 0)
BEGIN
    IF (@TIPO LIKE 'A' OR @TIPO LIKE 'B')
    BEGIN
        UPDATE PRACTICA
        SET Precio = Precio + (Precio * 0.1)
        WHERE CodAl# = @CODAL;
    END
    ELSE
    BEGIN
        UPDATE PRACTICA
        SET Precio = Precio - (Precio * 0.1)
        WHERE CodAl# = @CODAL;
    END

    FETCH NEXT FROM CUR_CARNET INTO @TIPO, @CODAL
END

CLOSE CUR_CARNET
DEALLOCATE CUR_CARNET

Ejercicio 6: Consulta de Alumnos por Matrícula con Validaciones

Enunciado: Crear un procedimiento almacenado que reciba una matrícula y devuelva la cantidad de alumnos que utilizan dicho vehículo, realizando las comprobaciones de existencia pertinentes.

ALTER PROCEDURE PROCEX
    @PE_MATRICULA CHAR(8)
AS
    DECLARE @CODMON_COCHE INT
    DECLARE @TOTAL INT

    IF (@PE_MATRICULA = '')
    BEGIN
        PRINT 'CAMPO INCORRECTO';
    END
    ELSE
    BEGIN
        IF (@PE_MATRICULA NOT IN (SELECT MATRICULA FROM COCHE))
        BEGIN
            PRINT 'NO EXISTE EN LA BASE DE DATOS';
        END
        ELSE
        BEGIN
            SELECT @CODMON_COCHE = CodMon#
            FROM COCHE C 
            INNER JOIN MONITOR M ON C.CodCOCHE# = M.CodCoche#
            WHERE Matricula = @PE_MATRICULA;

            SELECT COUNT(CodAL#)
            FROM ALUMNO A 
            INNER JOIN MONITOR M ON A.CodMon# = M.CodMon#
            WHERE A.CodMon# = @CODMON_COCHE;
        END
    END;

-- Ejecución de prueba:
EXEC PROCEX 'BBB0000';

Ejercicio 7: Procedimiento Almacenado de Cambio de Coche por Condición de Alumnos

Enunciado: Procedimiento almacenado que, tras validar DNI del monitor y matrícula introducida, evalúa si el monitor cuenta con un número determinado de alumnos asignados para actualizar su vehículo.

ALTER PROCEDURE PROC4
    @PE_DNI VARCHAR(8),
    @PE_MATRICULA CHAR(7)
AS
    DECLARE @CODMON INT
    DECLARE @TOTAL INT
    DECLARE @CODIGO_COCHE INT

    IF (@PE_DNI = '' OR @PE_MATRICULA = '')
    BEGIN
        PRINT 'CAMPOS INCORRECTOS';
    END
    ELSE
    BEGIN
        IF (@PE_DNI NOT IN (SELECT DNI FROM MONITOR))
        BEGIN
            PRINT 'NO EXISTE EN BD';
        END
        ELSE
        BEGIN
            IF (@PE_MATRICULA NOT IN (SELECT MATRICULA FROM COCHE))
            BEGIN
                PRINT 'NO EXISTE EN LA BD';
            END
            ELSE
            BEGIN
                SELECT @CODIGO_COCHE = CodCoche#
                FROM COCHE
                WHERE Matricula = @PE_MATRICULA;

                SELECT @CODMON = CodMon#
                FROM MONITOR
                WHERE DNI = @PE_DNI;

                SELECT @TOTAL = COUNT(CodAl#)
                FROM MONITOR M 
                INNER JOIN ALUMNO A ON M.CodMon# = A.CodMon#
                WHERE M.CodMon# = @CODMON;

                IF (@TOTAL = 10)
                BEGIN
                    UPDATE MONITOR
                    SET CodCoche# = @CODIGO_COCHE
                    WHERE CodCoche# = (
                        SELECT CodCoche#
                        FROM MONITOR
                        WHERE CodMon# = @CODMON
                    );
                END
            END
        END
    END;

-- Ejecución de prueba:
EXEC PROC4 '33333333', 'BBB0000';