SQL Stored Procedures for Driving School Data Management

Posted on Jan 4, 2025 in French

Exercise 2

Create a Stored Procedure to Retrieve the Number of Practices for a Vehicle in a Given Month

Create a stored procedure that, given a vehicle’s license plate and a month (in numerical format, e.g., 1, 2, 3,…12), returns the number of practice sessions conducted by that vehicle during that month.

Validation Requirements:

  • Ensure that the input parameters are not empty.
  • Verify that the vehicle exists in the database.
  • Confirm that the month is between 1 and 12.
  • Display error conditions on the screen.

Stored Procedure Code:


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 'Incorrect Fields'
END
ELSE
BEGIN
    IF (@PE_MATRICULA NOT IN (SELECT DISTINCT MATRICULA FROM COCHE))
    BEGIN
        PRINT 'Vehicle is not in the Database'
    END
    ELSE
    IF (MONTH(@PE_FECHA) < 1 OR MONTH(@PE_FECHA) > 12)
    BEGIN
        PRINT 'Incorrect Date'
    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;

Exercise 4

Create a Stored Procedure to Update a Driving Instructor’s Assigned Vehicle

Create a stored procedure that, given an instructor’s DNI and a vehicle’s license plate, modifies the vehicle assigned to the instructor if they are assigned to 15 students. The new assigned vehicle will be the one introduced via the keyboard.

Validation Requirements:

  • Ensure that the input DNI and license plate are not empty.
  • Verify that both the instructor and the vehicle exist in the database.
  • Display error conditions on the screen.

Stored Procedure Code:


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 in the Fields'
END
ELSE
BEGIN
    IF (@PE_DNI NOT IN (SELECT DNI FROM MONITOR))
    BEGIN
        PRINT 'Instructor does not exist'
    END
    ELSE
    IF (@PE_MATRICULA NOT IN (SELECT MATRICULA FROM COCHE))
    BEGIN
        PRINT 'Vehicle does not exist'
    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
    END

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

Exercise 3

Stored Procedure to List Students Using a Specific Vehicle

Create a stored procedure that takes a license plate as input and displays the students who use that car, along with necessary checks.

Stored Procedure Code:


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

IF (@PE_MATRICULA = '')
BEGIN
    PRINT 'Incorrect Field'
END
ELSE
BEGIN
    IF (@PE_MATRICULA NOT IN (SELECT MATRICULA FROM COCHE))
    BEGIN
        PRINT 'Vehicle does not exist in the Database'
    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

Exercise 4

Stored Procedure to Change Instructor’s Vehicle Based on Student Count

Create a stored procedure that changes the instructor’s assigned vehicle if they have more than 15 students. The new vehicle will be the one specified by the provided license plate.

Stored Procedure Code:


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 'Incorrect Fields'
END
ELSE
BEGIN
    IF (@PE_DNI NOT IN (SELECT DNI FROM MONITOR))
    BEGIN
        PRINT 'Instructor does not exist in the Database'
    END
    ELSE
    BEGIN
        IF (@PE_MATRICULA NOT IN (SELECT MATRICULA FROM COCHE))
        BEGIN
            PRINT 'Vehicle does not exist in the Database'
        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;

EXEC PROC4 '33333333', 'BBB0000'

Model B

Stored Procedure to Calculate Practices Between Two Dates for an Instructor

Create a stored procedure that takes an instructor’s DNI and two dates as input. It should validate the inputs and display the number of practice sessions conducted by the instructor between those two dates.

Stored Procedure Code:


ALTER PROCEDURE PROC_1
    @PE_DNI VARCHAR(10),
    @PE_FECHA1 DATE,
    @PE_FECHA2 DATE
AS
DECLARE @VAR_TOTAL INT
DECLARE @VAR_COD_MON INT

IF (@PE_DNI = '' OR @PE_FECHA1 = '' OR @PE_FECHA2 = '')
BEGIN
    PRINT 'Incomplete Fields'
END
ELSE
BEGIN
    IF (@PE_DNI NOT IN (SELECT DNI FROM MONITOR))
    BEGIN
        PRINT 'Instructor not found'
    END
    ELSE
    BEGIN
        IF (@PE_FECHA1 >= @PE_FECHA2)
        BEGIN
            PRINT 'The second date cannot be less than or equal to the first date'
        END
        ELSE IF (@PE_FECHA1 < @PE_FECHA2)
        BEGIN
            SELECT @VAR_COD_MON = COD_MON
            FROM MONITOR
            WHERE DNI = @PE_DNI

            SELECT @VAR_TOTAL = COUNT(P.COD_A)
            FROM ALUMNO A INNER JOIN PRACTICA P ON A.COD_A = P.COD_A
            WHERE A.COD_MON = @VAR_COD_MON AND FECHA_PRAC BETWEEN @PE_FECHA1 AND @PE_FECHA2

            PRINT @VAR_TOTAL
        END
    END
END