Consultas SQL y PL/SQL: Ejercicios Prácticos de Bases de Datos

Informática de servicios, , , , ,

Ejercicios de Consultas SQL

A continuación se presentan una serie de ejercicios de consultas SQL, con sus respectivas soluciones, para la gestión de una base de datos de carreras de motos.

  1. Obtener un listado con la información de todos los equipos con sus países.

    SELECT ID, Nombre, Pais FROM Equipos;

  2. Obtener la información de todos los pilotos junto con su nombre de equipo. Si no pertenecen a un equipo, mostraremos «SIN EQUIPO».

    SELECT P.ID, P.Nombre, P.Nacionalidad, P.FechaNacimiento, P.EquipoID,
       NVL(E.Nombre, 'SIN EQUIPO') AS Equipo
FROM Pilotos P
LEFT JOIN Equipos E ON P.EquipoID = E.ID;

  3. Mostrar las distintas nacionalidades que tienen los pilotos de motos.

    SELECT DISTINCT Nacionalidad FROM Pilotos;

  4. ¿Qué pilotos pertenecen a los equipos que tienen la cadena «Esp» en el nombre?

    SELECT P.*
FROM Pilotos P
JOIN Equipos E ON P.EquipoID = E.ID
WHERE E.Nombre LIKE '%Esp%';

  5. Mostrar un listado con el número de motos que hay por marca.

    SELECT Marca, COUNT(*) AS Total FROM Motos GROUP BY Marca;

  6. ¿Qué pilotos nacieron después del 1 de enero del 2000?

    SELECT * FROM Pilotos WHERE FechaNacimiento > TO_DATE('2000-01-01', 'YYYY-MM-DD');

  7. ¿Qué modelos de motos han sido utilizados en carreras celebradas en circuitos de España?

    SELECT DISTINCT M.Modelo
FROM Resultados R
JOIN Motos M ON R.MotoID = M.ID
JOIN Carreras C ON C.ID = R.CarreraID
JOIN Circuitos Ci ON Ci.ID = C.CircuitoID
WHERE Ci.Pais = 'España';

  8. ¿Qué pilotos terminaron en primera posición en cualquier carrera?

    SELECT DISTINCT P.*
FROM Resultados R
JOIN Pilotos P ON R.PilotoID = P.ID
WHERE R.Posicion = 1;

  9. ¿Cuántos pilotos hay en total?

    SELECT COUNT(*) FROM Pilotos;

  10. ¿Qué modelos de moto y qué marca utiliza cada equipo?

    SELECT E.Nombre AS Equipo, M.Modelo, M.Marca
FROM Motos M
JOIN Equipos E ON M.EquipoID = E.ID;

  11. ¿Cuál es el número total de carreras por circuito? Mostrar el nombre del circuito y el número total de carreras.

    SELECT C.Nombre, COUNT(*) AS TotalCarreras
FROM Carreras Ca
JOIN Circuitos C ON Ca.CircuitoID = C.ID
GROUP BY C.ID, C.Nombre;

  12. ¿En cuántas carreras ha participado cada piloto? Mostrar el nombre del piloto y el número total de participaciones.

    SELECT P.Nombre, COUNT(R.PilotoID) AS Participaciones
FROM Resultados R
RIGHT JOIN Pilotos P ON R.PilotoID = P.ID
GROUP BY P.ID, P.Nombre
ORDER BY Participaciones DESC;

  13. ¿Cuál es la media de puntos obtenidos por cada piloto?

    SELECT P.Nombre, ROUND(AVG(R.Puntos), 2) AS Promedio
FROM Resultados R
RIGHT JOIN Pilotos P ON R.PilotoID = P.ID
GROUP BY P.Nombre;

  14. ¿Qué motos se han utilizado más de una vez en carreras?

    SELECT M.ID, M.Modelo, COUNT(*) AS VecesUsada
FROM Resultados R
JOIN Motos M ON R.MotoID = M.ID
GROUP BY M.ID, M.Modelo
HAVING COUNT(*) > 1;

  15. Obtener la información de los circuitos de menor longitud.

    SELECT *
FROM Circuitos C
WHERE C.LongitudKM = (SELECT MIN(LongitudKM) FROM Circuitos);

  16. ¿Cuál es el total de puntos acumulados por cada equipo?

    SELECT E.Nombre, SUM(R.Puntos) AS TotalPuntos
FROM Equipos E
JOIN Pilotos P ON E.ID = P.EquipoID
JOIN Resultados R ON P.ID = R.PilotoID
GROUP BY E.Nombre;

  17. ¿Cuántos pilotos tiene cada equipo?

    SELECT E.Nombre, COUNT(P.ID) AS TotalPilotos
FROM Equipos E
LEFT JOIN Pilotos P ON E.ID = P.EquipoID
GROUP BY E.Nombre;

  18. Insertar un nuevo piloto con datos inventados pero que cumplan las restricciones de la base de datos y teniendo en cuenta las filas ya insertadas en la misma.

    INSERT INTO Pilotos (ID, Nombre, Nacionalidad, FechaNacimiento, EquipoID) VALUES
(4, 'Sito Pons', 'Española', TO_DATE('1980-06-10', 'YYYY-MM-DD'), 1);

  19. Insertar a continuación un nuevo resultado para ese piloto, en la carrera con el ID más alto (usar subconsulta), primera posición, con 10 puntos, un tiempo de ‘1:40:25’ y moto de ID igual a 1.

    INSERT INTO Resultados (CarreraID, PilotoID, Posicion, Puntos, TiempoFinal, MotoID)
VALUES ((SELECT MAX(ID) FROM Carreras), 4, 1, 10, '1:45:25', 1);

    Nota: Se corrigió el tiempo final a ‘1:45:25’ para coincidir con el ejemplo original, y se añadió el paréntesis faltante en la subconsulta.

  20. Poner en mayúsculas la marca de las motos que todavía no han participado en una carrera.

    UPDATE Motos
SET Marca = UPPER(Marca)
WHERE ID NOT IN (SELECT DISTINCT MotoID FROM Resultados);

    Nota: Se corrigió la función de texto a UPPER() para cumplir con la instrucción de poner en mayúsculas.

  21. Eliminar todos los resultados con puntos mayor que 20.

    DELETE FROM Resultados WHERE Puntos > 20;

  22. ¿En qué circuitos ganaron pilotos italianos?

    SELECT DISTINCT Ci.Nombre
FROM Resultados R
JOIN Pilotos P ON R.PilotoID = P.ID
JOIN Carreras C ON R.CarreraID = C.ID
JOIN Circuitos Ci ON C.CircuitoID = Ci.ID
WHERE R.Posicion = 1 AND P.Nacionalidad = 'Italiana';

  23. ¿Qué pilotos no han participado en ninguna carrera?

    SELECT P.Nombre
FROM Pilotos P
LEFT JOIN Resultados R ON P.ID = R.PilotoID
WHERE R.PilotoID IS NULL;

  24. ¿Qué equipos no tienen pilotos asignados?

    SELECT E.Nombre
FROM Equipos E
LEFT JOIN Pilotos P ON E.ID = P.EquipoID
WHERE P.ID IS NULL;

  25. Actualizar la fecha de nacimiento de los pilotos cuyo equipo sea España haciendo que sean 30 días más jóvenes.

    UPDATE Pilotos P
SET FechaNacimiento = FechaNacimiento + 30
WHERE EquipoID IN (SELECT DISTINCT ID FROM Equipos
                   WHERE Pais = 'España');

Ejercicios de Programación PL/SQL

Esta sección aborda la creación de bloques PL/SQL, procedimientos y funciones, incluyendo la definición de tablas para su contexto.

Definición de Tablas

Las siguientes tablas son utilizadas como base para los ejercicios de PL/SQL:

CREATE TABLE Autores (
  id_autor NUMBER(8) PRIMARY KEY,
  nombre_autor VARCHAR2(100)
);

CREATE TABLE Comentarios (
  id_comentario NUMBER(10) PRIMARY KEY,
  contenido_comentario VARCHAR2(500),
  id_autor NUMBER(12),
  fecha_comentario DATE,
  CONSTRAINT fk_autor FOREIGN KEY (id_autor) REFERENCES Autores(id_autor)
);

Bloques PL/SQL Anónimos

  1. Construya un bloque en PL/SQL que genere 15 números enteros aleatorios entre 5 y 100. El bloque deberá mostrar por pantalla:

    • El promedio o media aritmética de los números generados.
    • Cuántos números son múltiplos de 5.
    DECLARE
  v_numero INTEGER;
  v_suma NUMBER := 0;
  v_multiplos_5 PLS_INTEGER := 0;
  v_promedio NUMBER;
BEGIN
  -- Generar 15 números aleatorios entre 5 y 100
  FOR i IN 1..15 LOOP
    v_numero := FLOOR(DBMS_RANDOM.VALUE(5, 101)); -- 101 para incluir el 100
    v_suma := v_suma + v_numero;
    IF v_numero MOD 5 = 0 THEN
      v_multiplos_5 := v_multiplos_5 + 1;
    END IF;
    -- DBMS_OUTPUT.PUT_LINE('Número generado: ' || v_numero); -- Comentado para la salida final
  END LOOP;
  v_promedio := v_suma / 15;

  DBMS_OUTPUT.PUT_LINE('Promedio de los números generados: ' || v_promedio);
  DBMS_OUTPUT.PUT_LINE('Cantidad de números múltiplos de 5: ' || v_multiplos_5);
END;

Procedimientos PL/SQL

  1. Construya un procedimiento que reciba como argumento los años de edad que tiene una persona. El procedimiento deberá mostrar si la persona es un niño, adolescente, adulto o anciano, dependiendo de su edad. Escriba el código necesario para probar el funcionamiento de ese procedimiento.

    • Niño: Edad menor que 13 años
    • Adolescente: Edad entre 13 y 17 años
    • Adulto: Edad entre 18 y 65 años
    • Anciano: Edad mayor que 65 años
    CREATE OR REPLACE PROCEDURE CATEDAD (edad NUMBER) IS
  v_categoria VARCHAR2(50);
BEGIN
  -- Determinar la categoría según la edad
  IF edad < 13 THEN
    v_categoria := 'Niño';
  ELSIF edad >= 13 AND edad <= 17 THEN
    v_categoria := 'Adolescente';
  ELSIF edad >= 18 AND edad <= 65 THEN
    v_categoria := 'Adulto';
  ELSE
    v_categoria := 'Anciano';
  END IF;
  DBMS_OUTPUT.PUT_LINE('La persona es un ' || v_categoria);
END;
    Probar el procedimiento:
    SET SERVEROUTPUT ON;
EXEC CATEDAD(18);

  1. Escriba un procedimiento que reciba el id_autor de un autor y muestre una lista de todos los comentarios de dicho autor, junto con la fecha en que se hicieron. Si el autor no tiene comentarios, el procedimiento mostrará un mensaje indicando que no tiene comentarios.

    CREATE OR REPLACE PROCEDURE comentarios_autor(p_id_autor autores.id_autor%type)
IS
BEGIN
  FOR reg IN (SELECT contenido_comentario, fecha_comentario FROM Comentarios
              WHERE id_autor = p_id_autor) LOOP
    DBMS_OUTPUT.PUT_LINE('Comentario: ' || reg.contenido_comentario || ' Fecha: '
                         || TO_CHAR(reg.fecha_comentario, 'YYYY-MM-DD'));
  END LOOP;
  IF SQL%ROWCOUNT = 0 THEN
    DBMS_OUTPUT.PUT_LINE('El autor con ID ' || p_id_autor ||
                         ' no tiene comentarios.');
  END IF;
END comentarios_autor;
    Probar el procedimiento:
    SET SERVEROUTPUT ON;
EXEC comentarios_autor(1); -- Reemplazar 1 con un ID de autor existente o no existente

  2. Crea un procedimiento que reciba un id_autor y un texto de comentario. El procedimiento debe:

    1. Verificar si el autor existe.
    2. Si existe, insertar un nuevo comentario con el texto recibido y la fecha actual. El id_comentario será el valor máximo de la tabla + 10.
    3. Si no existe, mostrar un mensaje indicando que el autor no fue encontrado.
    CREATE OR REPLACE PROCEDURE agregar_comentario_autor(
  p_id_autor autores.id_autor%type,
  p_texto_comentario comentarios.contenido_comentario%type) IS
  v_existe_autor PLS_INTEGER;
  v_nuevo_id PLS_INTEGER;
BEGIN
  SELECT COUNT(*) INTO v_existe_autor FROM Autores
  WHERE id_autor = p_id_autor;

  IF v_existe_autor = 0 THEN
    DBMS_OUTPUT.PUT_LINE('Autor con ID ' || p_id_autor || ' no existe.');
  ELSE
    SELECT NVL(MAX(id_comentario), 0) + 10 INTO v_nuevo_id
    FROM Comentarios;

    INSERT INTO Comentarios (id_comentario, contenido_comentario,
                             id_autor, fecha_comentario)
    VALUES (v_nuevo_id, p_texto_comentario, p_id_autor, SYSDATE);
    DBMS_OUTPUT.PUT_LINE('Comentario insertado para el autor con ID '
                         || p_id_autor || '.');
  END IF;
END agregar_comentario_autor;
    Probar el procedimiento:
    SET SERVEROUTPUT ON;
-- Para un autor existente (ej. ID 1)
EXEC agregar_comentario_autor(1, 'Este es un nuevo comentario de prueba.');

-- Para un autor no existente (ej. ID 999)
EXEC agregar_comentario_autor(999, 'Este comentario no se insertará.');

Funciones PL/SQL

  1. Construya una función que reciba un id_autor y devuelva el número de comentarios que ha creado dicho autor. Escriba el código necesario para probar esa función.

    CREATE OR REPLACE FUNCTION num_comentarios (p_id_autor autores.id_autor%type)
RETURN PLS_INTEGER
IS
  v_num PLS_INTEGER;
BEGIN
  SELECT COUNT(*)
  INTO v_num
  FROM Comentarios
  WHERE id_autor = p_id_autor;

  -- Retornamos el número de comentarios
  RETURN v_num;
END num_comentarios;
    Probar la función:
    SELECT num_comentarios(1) FROM DUAL; -- Reemplazar 1 con un ID de autor

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.