Procedimientos Almacenados en SQL: Ejercicios Prácticos con Soluciones

En este documento, se presentan varios ejercicios resueltos sobre procedimientos almacenados en SQL, utilizando una base de datos de ejemplo llamada "Librería". Cada ejercicio incluye el código SQL completo y una breve explicación.

Ejercicio 1: Mostrar Libros por Autor

Enunciado: Crear un procedimiento que reciba el nombre de un autor como parámetro y muestre todos los libros de ese autor.

USE Librería;
GO

CREATE PROCEDURE libros_autor
  @autor VARCHAR(50)
AS
  SELECT * FROM Libros
  WHERE Autor = @autor;
GO

EXEC libros_autor 'Reverte';

Ejercicio 2: Mostrar Libros por Autor y Editorial

Enunciado: Crear un procedimiento que reciba el nombre de un autor y el nombre de una editorial como parámetros, y muestre todos los libros de ese autor y editorial.

USE Librería;
GO

CREATE PROCEDURE libros_autor_editorial
  @autor VARCHAR(50),
  @edit SMALLINT
AS
  SELECT * FROM Libros
  WHERE Autor = @autor AND Editorial = @edit;
GO

EXEC libros_autor_editorial 'Reverte', 'Alfaguara';

Nota: El código original tenía un error. Se asume que la editorial se pasa como un ID numérico (SMALLINT), no como el nombre de la editorial (VARCHAR). Si la editorial se debe buscar por nombre, el tipo de dato del parámetro @edit debería ser VARCHAR y la consulta debería ajustarse para buscar por el nombre de la editorial en la tabla correspondiente (si existe una tabla de editoriales separada).

Ejercicio 3: Calcular el Promedio de Dos Números

Enunciado: Crear un procedimiento almacenado que reciba dos números y devuelva su promedio.

CREATE PROCEDURE promedio
  @num1 INT,
  @num2 INT,
  @media INT OUTPUT
AS
  SELECT @media = (@num1 + @num2) / 2;
GO

DECLARE @resultado INT;
EXEC promedio 5, 6, @resultado OUTPUT;
SELECT @resultado;

Ejercicio 4: Información de Libros por Autor y Cálculo de Precios

Enunciado: Crear un procedimiento almacenado que muestre los títulos, editorial y precio de los libros de un determinado autor (enviado como parámetro de entrada). El procedimiento también debe devolver la suma y el promedio de los precios de todos los libros del autor.

USE Librería;
GO

CREATE PROCEDURE precio_libros
  @autor VARCHAR(50),
  @sum MONEY OUTPUT,
  @media MONEY OUTPUT
AS
  SELECT Titulo, Editorial, Precio FROM Libros WHERE Autor = @autor;
  SELECT @sum = SUM(Precio) FROM Libros WHERE Autor = @autor;
  SELECT @media = AVG(Precio) FROM Libros WHERE Autor = @autor;
GO

DECLARE @suma MONEY;
DECLARE @promedio MONEY;
EXEC precio_libros 'Reverte', @suma OUTPUT, @promedio OUTPUT;
PRINT 'El precio total de los libros es: ' + CAST(@suma AS VARCHAR);
PRINT 'El precio promedio de los libros es: ' + CAST(@promedio AS VARCHAR);

Correcciones:

• Se corrigió la asignación de @sum y @media. Ahora se usa SELECT @sum = ... y SELECT @media = ... en lugar de asignaciones separadas.
• Se añadió la función CAST para convertir @suma y @promedio a VARCHAR para poder concatenarlos con texto en el PRINT.
• Se declaró @suma y @promedio como MONEY.

Ejercicio 5: Mostrar Libros por Autor con Validación de Entrada

Enunciado: Crear un procedimiento que muestre todos los libros de un autor determinado. Si no se ingresa un valor para el autor, se muestra un mensaje.

USE Librería;
GO

CREATE PROCEDURE libros_autor2
  @autor VARCHAR(50) = NULL
AS
  IF @autor IS NULL
  BEGIN
    SELECT 'Debe indicar un autor';
    RETURN;
  END;

  SELECT titulo FROM libros WHERE autor = @autor;
GO

Ejercicio 6: Insertar Registros en la Tabla "Libros"

Enunciado: Crear un procedimiento almacenado que inserte registros en la tabla "libros". Los parámetros de título y autor son obligatorios, los demás son opcionales. El procedimiento retorna 1 si la inserción se realiza correctamente y 0 si falla (debido a que título o autor son nulos).

USE Librería;
GO

CREATE PROCEDURE libros_ingreso
  @titulo VARCHAR(40) = NULL,
  @autor VARCHAR(30) = NULL,
  @editorial VARCHAR(20) = NULL,
  @precio DECIMAL(5, 2) = NULL
AS
  IF (@titulo IS NULL) OR (@autor IS NULL)
  BEGIN
    RETURN 0;
  END
  ELSE
  BEGIN
    INSERT INTO libros (titulo, autor, editorial, precio) VALUES (@titulo, @autor, @editorial, @precio);
    RETURN 1;
  END;
GO

DECLARE @retorno INT;
EXEC @retorno = libros_ingreso 'Alicia en el pais...', 'Lewis Carroll';
SELECT 'Ingreso realizado=1' = @retorno;

EXEC @retorno = libros_ingreso;
SELECT 'Ingreso realizado=1' = @retorno;

Ejercicio Adicional: Cálculo de la Nota Media de una Clase

Enunciado: Calcular la nota media de la clase SIR2, obteniendo el número total de alumnos y la suma de todas las notas. Se asume que existe una tabla llamada "Alumnos" con las columnas "dnialumno" y "nota".

USE Librería; -- Asegúrate de estar en la base de datos correcta
GO

CREATE PROCEDURE notaMedia
  @numal SMALLINT OUTPUT,
  @sumanota SMALLINT OUTPUT,
  @media SMALLINT OUTPUT
AS
  SELECT @numal = COUNT(dnialumno) FROM alumnos;
  SELECT @sumanota = SUM(nota) FROM alumnos;
  SET @media = @sumanota / @numal;
GO


DECLARE @medianota SMALLINT;
EXEC notaMedia @medianota = @medianota OUTPUT;
select @medianota

Correcciones y Mejoras:

• Los parámetros de salida @numal y @sumanota deben ser declarados como OUTPUT en la definición del procedimiento.
• Se corrigió la forma de llamar al procedimiento almacenado. Se debe usar EXEC nombre_procedimiento @parametro = @variable OUTPUT.
• Se utiliza SET para asignar el resultado de la división a @media.
• Se ha añadido la selección de la base de datos USE Librería para asegurar que se ejecuta en el contexto correcto.