queryLab · software_de_entrenamiento en espera
Nivel 4 · Avanzado · SQL · caso integrador · semanas 8 y 9

[SQL 18] Caso SEIS · matrícula en educación superior

Del modelo físico a las consultas: DDL, CRUD y reportes con JOIN y GROUP BY sobre una base de 19 tablas.
0 / 16 retos resueltos 📘 Clases 8 y 9 (DDL con Workbench, CRUD) · formato de la Solemne 2 · indicadores 1.1 a 1.5
Al terminar esta práctica podrás…
  • Leer un modelo relacional grande e identificar el camino de JOIN entre las tablas que pide un requerimiento.
  • Reconocer la sintaxis correcta de ALTER TABLE, ADD PRIMARY KEY, INSERT, UPDATE y DELETE en MySQL, como en las preguntas de alternativas.
  • Unir tablas con una clave foránea compuesta (carrera → sede) y con tablas de catálogo (área, nivel, vigencia, jornada).
  • Construir métricas con SUM(CASE …), filtrar grupos con HAVING y contar personas distintas con COUNT(DISTINCT …).
Esta práctica usa otra base: SEIS. Sigue el modelo de datos del Servicio de Información de Educación Superior que se usa en la evaluación (región → provincia → comuna → sede → carrera → matrícula ← estudiante), con datos ficticios: 9 instituciones inventadas, 24 carreras, unos 600 estudiantes y 1.600 matrículas entre 2018 y 2023. Ver el modelo en el editor relacional · abrir la base SEIS en la consola libre.

Caso: el ministerio publica, a través del SEIS, la matrícula de los estudiantes de educación superior: tipo de institución, acreditación, sedes, carreras, jornadas, modalidades y los datos de cada matrícula (año, arancel, forma de ingreso). Como analista, debes mantener la base (DDL y DML) y responder requerimientos con consultas.

Antes de escribir una consulta, ubica en el modelo las tablas que necesitas y su camino de unión. Los caminos más usados:

  • matricula → estudiante (sexo, edad) y matricula → carrera (nombre, área, nivel, jornada…).
  • carrera → institucion (nombre, tipo, acreditación) y carrera → sede → comuna → provincia → region. La sede tiene clave primaria compuesta (sed_codigo, sed_institucion), así que el JOIN usa las dos columnas: s.sed_codigo = c.car_sede AND s.sed_institucion = c.car_institucion.
  • Las tablas de catálogo (area, nivel, vigencia, jornada, sexo…) traducen códigos a descripciones. Filtra por la descripción cuando el enunciado la nombra («área Informática»), o por el código si lo da.

En matricula, mat_periodo es el año de la matrícula, mat_agno_ingreso el año en que el estudiante entró a la carrera y mat_ingreso_primer_agno = 1 marca a los estudiantes de primer año. En estudiante, est_sexo = 1 es mujer y 2 es hombre.

Nota sobre el modelo: en el diagrama original, carrera tiene solo car_sede aunque la PK de sede es compuesta. Para que la clave foránea sea válida, esta base agrega car_institucion, y (car_sede, car_institucion) referencia a sede(sed_codigo, sed_institucion).

DDL
CREATE, ALTER, DROP: definen la estructura (tablas, columnas, claves, vistas).
DML · CRUD
INSERT (crear), SELECT (leer), UPDATE (actualizar), DELETE (eliminar).
FK compuesta
Si la PK referenciada tiene dos columnas, la FK y el JOIN también usan las dos.
COUNT(DISTINCT …)
Un estudiante tiene una matrícula por año: para contar personas usa COUNT(DISTINCT est_run).
MySQL · cambiar tipo
ALTER TABLE t CHANGE COLUMN col col TEXT; ALTER TABLE t MODIFY COLUMN col TEXT;
MySQL · claves
ALTER TABLE t ADD PRIMARY KEY (col); ALTER TABLE t ADD CONSTRAINT fk FOREIGN KEY (c) REFERENCES p(c);
Oracle
ALTER TABLE t MODIFY (col VARCHAR2(400)); ALTER TABLE t ADD CONSTRAINT pk PRIMARY KEY (col);
SQLite (esta página)
ALTER TABLE t ADD COLUMN …; RENAME COLUMN …; DROP COLUMN … (sin CHANGE/MODIFY ni ADD PRIMARY KEY)

1 · Ejemplos guiados · ejecuta y modifica cada bloque

01Explora el modelo desde el catálogo
Antes de consultar una base desconocida, lista sus tablas y columnas. pk indica la posición de la columna dentro de la clave primaria (0 = no es parte de ella).
sql-caso-seis.sql
Presiona «ejecutar» para ver el resultado.
02Carreras con su institución y sede (FK compuesta)
La sede se identifica por (sed_codigo, sed_institucion): el JOIN con carrera compara las dos columnas. Si usaras solo sed_codigo, cada carrera se uniría con las sedes número 1 de todas las instituciones.
sql-caso-seis.sql
Presiona «ejecutar» para ver el resultado.
03Matrícula 2023 por tipo de institución
Cadena matricula → carrera → institucion → tipo_institucion, filtro por periodo y agrupación por la descripción del tipo.
sql-caso-seis.sql
Presiona «ejecutar» para ver el resultado.
04Arancel promedio por área en 2023
ROUND(AVG(…)) redondea al peso. El área está en la tabla de catálogo area.
sql-caso-seis.sql
Presiona «ejecutar» para ver el resultado.
05Estudiantes nuevos por sexo y año
SUM(CASE …) (o SUM(condición) en SQLite y MySQL) cuenta por categoría dentro de cada grupo: es la base de las métricas tipo «hombres − mujeres».
sql-caso-seis.sql
Presiona «ejecutar» para ver el resultado.

Sintaxis en otros motores (referencia, no se ejecuta aquí)

MySQL Implementar la tabla sede (PK compuesta y dos FK)
Así se escribe en MySQL Workbench / Forward Engineer. La PK compuesta se declara al final, y cada FK con su CONSTRAINT.
CREATE TABLE sede (
  sed_codigo      INT NOT NULL,
  sed_institucion INT NOT NULL,
  sed_nombre      VARCHAR(80) NOT NULL,
  sed_comuna      INT NOT NULL,
  PRIMARY KEY (sed_codigo, sed_institucion),
  CONSTRAINT fk_sede_institucion FOREIGN KEY (sed_institucion) REFERENCES institucion (inst_codigo),
  CONSTRAINT fk_sede_comuna      FOREIGN KEY (sed_comuna)      REFERENCES comuna (com_codigo)
) ENGINE = InnoDB;
MySQL Implementar la tabla matricula (PK de tres columnas)
CREATE TABLE matricula (
  mat_estudiante INT NOT NULL,
  mat_carrera    INT NOT NULL,
  mat_periodo    INT NOT NULL,
  mat_valor_matricula INT,
  mat_valor_arancel   INT,
  mat_ingreso_primer_agno TINYINT,
  mat_ingreso_semestre_matricula TINYINT,
  mat_agno_ingreso    INT,
  mat_semestre_ingreso TINYINT,
  mat_forma_ingreso   INT,
  PRIMARY KEY (mat_estudiante, mat_carrera, mat_periodo),
  FOREIGN KEY (mat_estudiante)    REFERENCES estudiante (est_run),
  FOREIGN KEY (mat_carrera)       REFERENCES carrera (car_codigo),
  FOREIGN KEY (mat_forma_ingreso) REFERENCES ingreso (ing_codigo)
) ENGINE = InnoDB;
MySQL Modificar estructuras con ALTER TABLE
CHANGE COLUMN repite el nombre (viejo y nuevo) y permite renombrar; MODIFY COLUMN solo cambia la definición. Ambos exigen ALTER TABLE y el tipo completo.
ALTER TABLE carrera     CHANGE COLUMN car_requisito_ingreso car_requisito_ingreso TEXT;
ALTER TABLE carrera     MODIFY COLUMN car_requisito_ingreso TEXT;
ALTER TABLE institucion ADD COLUMN inst_sitio_web VARCHAR(200);
ALTER TABLE area        ADD PRIMARY KEY (are_codigo);
ALTER TABLE area        ADD CONSTRAINT fk_area_tipo FOREIGN KEY (are_tipo_area) REFERENCES tipo_area (tpar_codigo);
ALTER TABLE estudiante  DROP COLUMN est_rango_edad;

⚠ Errores frecuentes

  • Escribir ALTER estudiante … o MODIFY estudiante …: la instrucción siempre empieza con ALTER TABLE.
  • ADD COLUMN col sin tipo de dato: toda columna nueva necesita su tipo (INTEGER, VARCHAR(200)…).
  • INSERT INTO TABLE …, UPDATE TABLE … o DELETE FROM TABLE …: la palabra TABLE no va en el DML.
  • Unir carrera con sede solo por sed_codigo: con PK compuesta se duplican filas.
  • Contar matrículas cuando se piden estudiantes: un estudiante aparece una vez por año; usa COUNT(DISTINCT …).
  • En un LIKE, un espacio dentro del patrón cuenta: ' %Vida' exige que el texto empiece con un espacio. El patrón correcto es '%Vida'.

2 · Retos con corrección automática

Escribe tu solución y pulsa comprobar (Ctrl + Enter). Tu consulta y la solución se ejecutan en bases limpias y se comparan los resultados: no importan los nombres de las columnas, sí los datos (y el orden, cuando el reto lo pide). Si te atascas, usa la pista o mira el resultado esperado.

3 · Preguntas tipo solemne

1. ¿Cuál instrucción permite cambiar el tipo de dato de car_requisito_ingreso a TEXT en la tabla carrera (MySQL)?
2. ¿Cuál instrucción implementa correctamente la clave primaria de la tabla area, cuyo atributo es are_codigo?
3. ¿Cuál instrucción agrega la columna inst_sitio_web a la tabla institucion?
4. ¿Cuál instrucción agrega la modalidad de código 4 llamada «Híbrida»?
5. ¿Cuál instrucción cambia el nombre de la región de código 9 a «Araucanía»?
6. ¿Cuál instrucción elimina el nivel cuya descripción termina en «Magíster»?
7. La sede tiene PK (sed_codigo, sed_institucion). ¿Cuál es el JOIN correcto entre carrera c y sede s?
8. Se pide «la cantidad de estudiantes que ingresaron en 2020». ¿Qué expresión evita contar dos veces al mismo estudiante matriculado en varios años?

▸ Para seguir practicando

  1. Calcula la tasa de retención: de los que ingresaron en 2021, ¿qué porcentaje sigue matriculado en 2022?
  2. Arma un ranking de instituciones por matrícula 2023 con RANK() OVER (ORDER BY …).
  3. Abre el modelo en el editor relacional, genera el script para MySQL Workbench e impórtalo con Reverse Engineer.
← anterior fin →