Estructuras y Consultas Avanzadas en Bases de Datos SQL

1. El Script de Creación de Tablas (DDL)

En esta sección se definen las estructuras fundamentales de la base de datos, diferenciando entre tablas independientes y aquellas que establecen relaciones.

-- TABLA FUERTE (No depende de otras, o tiene dependencias simples)
CREATE TABLE NOMBRE_TABLA_1 (
    id_tabla1 INT PRIMARY KEY,
    atributo_texto VARCHAR(100),
    atributo_numero INT
    -- FOREIGN KEY (opcional_fk) REFERENCES OTRA_TABLA(id)
);

-- TABLA INTERMEDIA / DÉBIL (Suele ser la que "Relaciona" a otras o depende)
CREATE TABLE NOMBRE_TABLA_2 (
    id_fk1 INT,
    id_fk2 INT,
    atributo_extra DATE, -- o DECIMAL(10,2), etc.
    PRIMARY KEY (id_fk1, id_fk2),
    FOREIGN KEY (id_fk1) REFERENCES TABLA_ORIGEN_1(id),
    FOREIGN KEY (id_fk2) REFERENCES TABLA_ORIGEN_2(id)
);

2. La Consulta Compleja: "El Top N" o "Agrupación con Having"

Esta consulta permite extraer información procesada mediante el uso de uniones, agrupaciones y filtros avanzados.

SELECT 
    A.nombre_o_campo_a_mostrar, 
    COUNT(B.id_a_contar) AS total_calculado
FROM TABLA_PRINCIPAL A
JOIN TABLA_INTERMEDIA AB ON A.id = AB.id_principal
JOIN TABLA_SECUNDARIA B ON AB.id_secundaria = B.id
-- FILTROS COMUNES (Fechas o Textos)
WHERE B.atributo_texto = 'VALOR_PEDIDO' 
  AND EXTRACT(YEAR FROM B.campo_fecha) = 2024 -- o B.campo_fecha BETWEEN 'X' AND 'Y'
-- AGRUPACIÓN (Siempre por el ID y lo que mostramos en el SELECT)
GROUP BY A.id, A.nombre_o_campo_a_mostrar
-- CONDICIÓN SOBRE EL CONTEO (Ej: "más de 3 productos")
HAVING COUNT(B.id_a_contar) > CANTIDAD_MINIMA
-- ORDENACIÓN Y LÍMITE (Solo si dice "Top 10" o "Los 3 mejores")
ORDER BY total_calculado DESC
LIMIT NUMERO_TOP;

3. El Procedimiento Almacenado Condicional

Los procedimientos almacenados permiten encapsular lógica de negocio directamente en el motor de la base de datos.

DELIMITER //
CREATE PROCEDURE sp_Insertar_Accion(
    IN p_id_usuario INT, 
    IN p_id_objeto INT, 
    IN p_valor DECIMAL(5,2) -- o INT, según corresponda
)
BEGIN
    DECLARE v_contador INT;

    -- PASO 1: Comprobar condición (¿Existe en la tabla de compras/reservas?)
    SELECT COUNT(*) INTO v_contador
    FROM TABLA_DE_VERIFICACION
    WHERE idUsuario = p_id_usuario AND idObjeto = p_id_objeto;

    -- PASO 2: Insertar si cumple, bloquear si no
    IF v_contador > 0 THEN
        INSERT INTO TABLA_DESTINO (columna_usuario, columna_objeto, valor)
        VALUES (p_id_usuario, p_id_objeto, p_valor)
        ON DUPLICATE KEY UPDATE valor = p_valor; -- Por si ya existía

        -- (Si también pide actualizar la media aquí mismo, añada un UPDATE de la Tabla 4)
    ELSE
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Operación denegada. Condición no cumplida.';
    END IF;
END; //
DELIMITER ;

4. El Trigger de Actualización Global

Los triggers o disparadores son esenciales para mantener la integridad y consistencia de los datos de forma automática tras un evento DML.

DELIMITER //
CREATE TRIGGER trg_actualizar_estadistica
AFTER INSERT -- Cambiar a AFTER DELETE si el enunciado dice "tras eliminarse"
ON TABLA_DONDE_OCURRE_EL_CAMBIO
FOR EACH ROW
BEGIN
    -- NOTA MENTAL: 
    -- Si es AFTER INSERT, use NEW.id_fk
    -- Si es AFTER DELETE, use OLD.id_fk

    UPDATE TABLA_QUE_SE_ACTUALIZA
    SET valoracion_media = (
        SELECT AVG(campo_valor) -- o SUM() o COUNT()
        FROM TABLA_DONDE_OCURRE_EL_CAMBIO
        WHERE id_foraneo = NEW.id_foraneo 
    )
    WHERE id = NEW.id_foraneo;
END; //
DELIMITER ;