Dominio de SQL: Estructuras DDL, Consultas Avanzadas y Gestión de Datos

Creación de Tablas (DDL)

La sentencia CREATE TABLE se utiliza para definir la estructura de las tablas. A continuación, se presenta un ejemplo detallado de su sintaxis:

CREATE TABLE NombreTabla (
    -- 1. Definición de columnas y tipos de datos
    id_tabla VARCHAR2(10),
    fecha DATE DEFAULT SYSDATE, -- "Por defecto la fecha actual"
    numero NUMBER(5,2),         -- "Numérico con decimales"
    id_foranea VARCHAR2(10),    -- Columna necesaria para la clave ajena

    -- 2. Restricciones de columna (NN, CC, CHECK simples)
    nombre VARCHAR2(50) CONSTRAINT nom_nn NOT NULL,
    email VARCHAR2(50) CONSTRAINT email_uniq UNIQUE, -- CC (Clave Candidata)
    estado VARCHAR2(10) DEFAULT 'Pendiente',

    -- 3. Clave Primaria (CP)
    CONSTRAINT pk_tabla PRIMARY KEY (id_tabla),

    -- 4. Claves Foráneas / Ajenas (CE)
    CONSTRAINT fk_otra_tabla FOREIGN KEY (id_foranea) REFERENCES OtraTabla(id_foranea),

    -- 5. Restricciones de tabla (CHECKs complejos del texto)
    CONSTRAINT chk_rango CHECK (numero >= 0 AND numero <= 100),
    CONSTRAINT chk_valores CHECK (estado IN ('Pendiente', 'Completada'))
);

Si se requiere una restricción del tipo "No se permiten fechas de 2012", se debe añadir un CHECK utilizando la función TO_CHAR(fecha, 'YYYY') != '2012' o definiendo rangos de fechas con comillas simples.

En el caso de que se solicite una "cadena larga para código fuente", lo recomendable es utilizar los tipos de datos VARCHAR2 o CLOB.

Consultas de División Relacional

Objetivo: Mostrar los elementos (X) tales que, si se seleccionan TODOS los requerimientos (Y) y se le restan los requerimientos que este elemento SÍ ha realizado, el resultado es vacío (no existe).

SELECT id_X
FROM TablaX t1
WHERE NOT EXISTS (
    -- Conjunto total (TODOS los Y que existen)
    SELECT id_Y
    FROM TablaY

    MINUS

    -- Conjunto parcial (Los Y que tiene asociados mi elemento actual t1)
    SELECT id_Y
    FROM RelacionXY t2
    WHERE t1.id_X = t2.id_X
);

Agrupaciones y Filtrado de Datos

Escenario: "Mostrar nombre y cantidad de X, calculando el total de Y, pero SÓLO para aquellos que superen la cantidad Z".

SELECT t.nombre, SUM(r.cantidad) -- O COUNT(*), AVG()...
FROM Tabla t
JOIN Relacion r ON t.id = r.id
WHERE r.condicion = 'Algo'      -- 1º Filtramos las filas individuales ANTES de agrupar
GROUP BY t.id, t.nombre         -- 2º Todo lo que está en el SELECT (y no es función) DEBE ir aquí
HAVING SUM(r.cantidad) > 10;    -- 3º Filtramos los GRUPOS DESPUÉS de calcular

Búsqueda de Valores Extremos

Para encontrar el elemento MÁS caro o el que MENOS tiempo necesita, se utilizan subconsultas con funciones de agregado:

SELECT id, nombre, precio
FROM Tabla
WHERE precio = (
    SELECT MAX(precio)
    FROM Tabla
);

Nota: Si se solicita el máximo cumpliendo una condición específica, recuerde incluir la cláusula WHERE tanto en la consulta principal como dentro de la subconsulta.

Vistas y Operaciones DML Avanzadas

Estas operaciones suelen representar la parte final de los ejercicios técnicos, involucrando vistas extensas con agrupaciones o sentencias UPDATE/DELETE que requieren subconsultas.

Plantilla para la Creación de Vistas:

CREATE VIEW Nombre_Vista (NombreColumna1, NombreColumna2, NombreColumna3) AS (
    -- Aquí se inserta la sentencia SELECT estándar
    SELECT A.id, A.nombre, COUNT(*)
    FROM TablaA A 
    JOIN TablaB B ON A.id = B.id_a
    GROUP BY A.id, A.nombre
);

Plantilla para UPDATE / DELETE Condicional:

Ejemplo de UPDATE: "Actualizar a ESTADO='Revisar' las tareas de los proyectos del año 2024".

UPDATE Tareas
SET Estado = 'Revisar'
WHERE id_proyecto IN (
    SELECT id_proyecto FROM Proyectos WHERE anio = 2024
);

Ejemplo de DELETE: Borrar tuplas basadas en los criterios de otra tabla.

DELETE FROM TablaA
WHERE id IN (
    SELECT id FROM TablaB WHERE condicion = 'X'
);