Módulo 40: Colección de ejercicios de SQL
Objetivos del módulo
- Consolidar todo el SQL del curso (módulos 23 a 35) resolviendo ejercicios de examen de dificultad creciente
- Practicar la lectura cuidadosa de enunciados y su traducción a consultas correctas, eficientes y legibles
- Reconocer los errores típicos de examen (nulos,
GROUP BY,NOT IN, joins que pierden filas…) y sus códigosORA-reales - Comparar soluciones alternativas para un mismo problema (join frente a subconsulta,
EXISTSfrente aIN, analíticas frente a agregados…) - Enfrentarte a tres exámenes completos (ciclo formativo, universidad y mixto) con su solución y sus criterios de corrección
- Trabajar con un segundo esquema de práctica, BIBLIOTECA, para comprobar que dominas las técnicas y no solo las tablas de EMPRESA
1. Cómo usar este módulo
Este módulo es un banco de ejercicios: no introduce sintaxis nueva, sino que te obliga a combinar la que ya conoces. Para sacarle partido:
- Lee el enunciado y escribe tu solución antes de desplegar la de referencia. Ejecútala en tu base de datos.
- Compara tu salida con la salida real que se muestra. Si coincide, revisa igualmente la solución: puede haber una forma más clara o más eficiente.
- Lee los comentarios: casi todos incluyen soluciones alternativas y errores típicos, que son los que restan puntos en un examen.
📘 Concepto: Una consulta correcta no es la que “da el resultado esperado con estos datos”, sino la que da el resultado correcto con cualquier contenido válido de las tablas. Por eso los comentarios insisten en los nulos, los empates y los casos vacíos (el departamento 70 sin empleados, el empleado 160 sin departamento, los clientes sin pedidos…).
1.1. Niveles de dificultad
| Marca | Nivel | Qué exige |
|---|---|---|
| ★ | Básico | Una tabla, filtros, ordenación y funciones sencillas |
| ★★ | Medio | Varias tablas, agrupación, subconsultas simples, nulos |
| ★★★ | Avanzado | Subconsultas correlacionadas, división, CTE recursivas, analíticas, casos límite |
1.2. Entorno
Todos los ejercicios se han ejecutado en Oracle AI Database 26ai Free con SQL*Plus sobre el esquema EMPRESA recién cargado (empresa_crear.sql y empresa_datos.sql). La sesión se configuró así (es la misma configuración que usan los módulos de consultas):
SET NULL '(nulo)'
ALTER SESSION SET NLS_LANGUAGE = 'SPANISH';
ALTER SESSION SET NLS_TERRITORY = 'SPAIN';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY';
Sesión modificada.
Sesión modificada.
Sesión modificada.
Con NLS_TERRITORY = 'SPAIN' los decimales se muestran con coma (2829,17), y con SET NULL los valores nulos aparecen como (nulo). Las salidas de más de 12 filas se recortan a 8 seguidas de .... Los mensajes ORA- se muestran tal cual en inglés, porque la edición Free solo incluye ese idioma. Los ejercicios que modifican datos terminan con ROLLBACK o borran sus tablas auxiliares.
1.3. Índice de bloques
| Bloque | Ejercicios | Contenido | Módulos de referencia |
|---|---|---|---|
| A | 1-10 | Consultas básicas | 23 |
| B | 11-20 | Funciones de una fila | 24 |
| C | 21-30 | Agrupación | 25 |
| D | 31-40 | Consultas multitabla | 26 |
| E | 41-50 | Subconsultas | 27 |
| F | 51-58 | Conjuntos y CTE | 28 |
| G | 59-66 | Analíticas y jerárquicas | 29 |
| H | 67-74 | DML | 30 |
| I | 75-82 | DDL | 19-22 |
| — | 3 exámenes | FP, universidad y mixto | Todos |
| — | 20 ejercicios | Esquema BIBLIOTECA | Todos |
2. Bloque A: consultas básicas
Ejercicio 1: Plantilla de Informática ★
Muestra nombre, apellidos, oficio y salario de los empleados del departamento 30, del que más cobra al que menos.
Ver solución
SELECT nombre, apellidos, oficio, salario
FROM empleados
WHERE dept_id = 30
ORDER BY salario DESC;
NOMBRE APELLIDOS OFICIO SALARIO
---------------------------- ------------------ ---------------- ----------
Marta Sánchez Gil DIRECTOR 5000
Pablo Moreno Herrera ANALISTA 3500
Raquel Molina Prieto PROGRAMADOR 2800
Iván Castro Méndez PROGRAMADOR 2600
Hugo Rubio Marín TÉCNICO SISTEMAS 2500
Sara Ortiz Delgado PROGRAMADOR 2400
6 filas seleccionadas.
Si el enunciado pidiera “el departamento de Informática” sin dar el número, habría que hacer un join con DEPARTAMENTOS o una subconsulta (WHERE dept_id = (SELECT dept_id FROM departamentos WHERE nombre = 'INFORMÁTICA')). En un examen, no supongas el código si no te lo dan.
Ejercicio 2: Empleados sin teléfono ★
Lista los empleados que no tienen teléfono registrado. ¿Qué devuelve WHERE telefono = NULL?
Ver solución
SELECT emp_id, nombre, apellidos, telefono
FROM empleados
WHERE telefono IS NULL;
EMP_ID NOMBRE APELLIDOS TELEFONO
---------- ---------------------------- ------------------ ---------------
123 Sara Ortiz Delgado (nulo)
131 Jorge Vázquez Ramos (nulo)
160 Tomás Reyes Fuentes (nulo)
3 filas seleccionadas.
SELECT emp_id FROM empleados WHERE telefono = NULL;
ninguna fila seleccionada
Cualquier comparación con NULL mediante =, <>, <… da desconocido, y WHERE solo deja pasar lo verdadero. No hay error: simplemente no sale nada. Es el fallo más repetido en los exámenes.
Ejercicio 3: Productos de gama media ★
Productos no descatalogados con precio entre 100 y 500 € (ambos incluidos), ordenados por precio.
Ver solución
SELECT producto_id, nombre, precio
FROM productos
WHERE precio BETWEEN 100 AND 500
AND descatalogado = 'N'
ORDER BY precio;
PRODUCTO_ID NOMBRE PRECIO
----------- ---------------------------- ----------
9 Monitor 24 FHD 139
18 Impresora Láser 189
15 Silla Ergonómica 249
20 Tablet 10 279
11 Smartphone Gama Media 299
10 Monitor 27 4K 329
16 Mesa Elevable 399
3 Portátil Básico 15 499
8 filas seleccionadas.
BETWEEN a AND b incluye los extremos y equivale a precio >= 100 AND precio <= 500. Error típico: BETWEEN 500 AND 100 (orden invertido) no devuelve nada.
Ejercicio 4: Sociedades limitadas ★
Clientes cuyo nombre contiene S.L.. ¿Qué pasa si buscas '%S.%'?
Ver solución
SELECT cliente_id, nombre, ciudad
FROM clientes
WHERE nombre LIKE '%S.L.%'
ORDER BY nombre;
CLIENTE_ID NOMBRE CIUDAD
---------- ---------------------------- ------------
1012 Diseños Creativos S.L. Barcelona
1007 Hotel Mar Azul S.L. Palma
1001 Talleres Martín S.L. Valencia
3 filas seleccionadas.
SELECT cliente_id, nombre FROM clientes WHERE UPPER(nombre) LIKE '%S.%' ORDER BY nombre;
CLIENTE_ID NOMBRE
---------- ----------------------------
1002 Construcciones Levante S.A.
1012 Diseños Creativos S.L.
1007 Hotel Mar Azul S.L.
1001 Talleres Martín S.L.
4 filas seleccionadas.
En LIKE el punto es un carácter normal (los comodines son % y _), así que '%S.%' también encuentra la “S.A.”. Si necesitas buscar un % o un _ literal, usa ESCAPE: LIKE '%10\%%' ESCAPE '\'.
Ejercicio 5: Comisiones reales ★
Empleados que cobran comisión (mayor que cero), ordenados por comisión. Explica la diferencia entre comision <> NULL y comision IS NOT NULL.
Ver solución
SELECT emp_id, nombre, salario, comision
FROM empleados
WHERE comision > 0
ORDER BY comision DESC;
EMP_ID NOMBRE SALARIO COMISION
---------- ---------------------------- ---------- ----------
111 David 2100 800
110 Carmen 2200 500
112 Elena 2300 300
3 filas seleccionadas.
SELECT COUNT(*) FROM empleados WHERE comision <> NULL;
SELECT COUNT(*) FROM empleados WHERE comision IS NOT NULL;
COUNT(*)
----------
0
1 fila seleccionada.
COUNT(*)
----------
4
1 fila seleccionada.
comision > 0 ya excluye los nulos (y a Sergio, que tiene comisión 0). IS NOT NULL cuenta 4 porque incluye a Sergio. <> NULL nunca es verdadero.
Ejercicio 6: Pedidos abiertos de 2026 ★
Pedidos de 2026 que estén pendientes o enviados, por fecha.
Ver solución
SELECT pedido_id, cliente_id, fecha_pedido, estado
FROM pedidos
WHERE fecha_pedido >= DATE '2026-01-01'
AND estado IN ('PENDIENTE', 'ENVIADO')
ORDER BY fecha_pedido;
PEDIDO_ID CLIENTE_ID FECHA_PEDI ESTADO
---------- ---------- ---------- ------------
5020 1007 30/05/2026 ENVIADO
5021 1001 15/06/2026 ENVIADO
5022 1012 01/07/2026 PENDIENTE
5023 1009 08/07/2026 PENDIENTE
5024 1008 10/07/2026 PENDIENTE
5 filas seleccionadas.
Para filtrar por año es preferible un rango (>= DATE '2026-01-01' AND < DATE '2027-01-01') a EXTRACT(YEAR FROM fecha_pedido) = 2026: ambos son correctos, pero el rango permite usar un índice sobre la fecha. Nunca compares con una cadena (fecha_pedido >= '01/01/2026'): depende de NLS_DATE_FORMAT.
Ejercicio 7: Los más caros y los empates ★★
Muestra los 3 productos más caros. Después, los 2 empleados que menos cobran: ¿qué cambia con WITH TIES?
Ver solución
SELECT nombre, precio
FROM productos
ORDER BY precio DESC
FETCH FIRST 3 ROWS ONLY;
NOMBRE PRECIO
---------------------------- ----------
Torre Workstation 1899
Portátil Gaming 16 1499
Smartphone Gama Alta 999
3 filas seleccionadas.
SELECT nombre, salario FROM empleados ORDER BY salario ASC FETCH FIRST 2 ROWS ONLY;
SELECT nombre, salario FROM empleados ORDER BY salario ASC FETCH FIRST 2 ROWS WITH TIES;
NOMBRE SALARIO
---------------------------- ----------
Tomás 900
Rosa 1600
2 filas seleccionadas.
NOMBRE SALARIO
---------------------------- ----------
Tomás 900
Rosa 1600
Manuel 1600
3 filas seleccionadas.
Rosa y Manuel empatan con 1600 €: ONLY corta arbitrariamente y deja fuera a Manuel; WITH TIES incluye a todos los que empatan con la última fila. En un examen, si el enunciado dice “los que menos cobran”, la respuesta rigurosa es la de WITH TIES. En Oracle 11g o anteriores se resolvía con ROWNUM sobre una subconsulta ordenada (SELECT * FROM (SELECT ... ORDER BY ...) WHERE ROWNUM <= 3).
Ejercicio 8: Retribución anual ★★
Para el departamento de Ventas, calcula la retribución anual: 14 pagas de salario más 12 meses de comisión. Ordena por ese importe usando el alias.
Ver solución
SELECT nombre, apellidos, salario, comision,
salario * 14 + NVL(comision, 0) * 12 AS anual
FROM empleados
WHERE dept_id = 20
ORDER BY anual DESC;
NOMBRE APELLIDOS SALARIO COMISION ANUAL
---------------------------- ------------------ ---------- ---------- ----------
Luis Martínez Ruiz 4800 (nulo) 67200
David Torres Romero 2100 800 39000
Carmen Ruiz Ortega 2200 500 36800
Elena Navarro Castro 2300 300 35800
Sergio Jiménez Vidal 1900 0 26600
Lucía Romero Serrano 1700 (nulo) 23800
6 filas seleccionadas.
Sin NVL, las expresiones con comisión nula dan NULL y quedan al final (o al principio, según NULLS FIRST/LAST):
SELECT nombre, salario * 14 + comision * 12 AS anual
FROM empleados WHERE dept_id = 20 ORDER BY 2 DESC NULLS LAST;
NOMBRE ANUAL
---------------------------- ----------
David 39000
Carmen 36800
Elena 35800
Sergio 26600
Lucía (nulo)
Luis (nulo)
6 filas seleccionadas.
El alias se puede usar en ORDER BY (se evalúa después del SELECT), pero no en WHERE.
Ejercicio 9: Catálogo de oficios ★
Lista los distintos oficios de la empresa, ordenados alfabéticamente.
Ver solución
SELECT DISTINCT oficio
FROM empleados
ORDER BY oficio;
OFICIO
----------------
ADMINISTRATIVO
ANALISTA
BECARIO
CONTABLE
DIRECTOR
DIRECTOR GENERAL
DISEÑADOR
OPERARIO
PROGRAMADOR
TÉCNICO SISTEMAS
VENDEDOR
11 filas seleccionadas.
DISTINCT afecta a toda la fila del SELECT: SELECT DISTINCT oficio, dept_id devolvería las combinaciones distintas, no los oficios distintos.
Ejercicio 10: Prioridad de AND y OR ★★
Se piden los clientes de Madrid o de Barcelona con un límite de crédito superior a 5000 €. Un alumno escribe esta consulta. ¿Es correcta?
SELECT cliente_id, nombre, ciudad, limite_credito
FROM clientes
WHERE ciudad = 'Madrid' OR ciudad = 'Barcelona' AND limite_credito > 5000;
Ver solución
CLIENTE_ID NOMBRE CIUDAD LIMITE_CREDITO
---------- ---------------------------- ------------ --------------
1003 María José Herrera Madrid 2000
1004 Academia Nova Madrid 8000
1012 Diseños Creativos S.L. Barcelona 7000
3 filas seleccionadas.
No es correcta: AND tiene más prioridad que OR, así que se evalúa ciudad = 'Madrid' OR (ciudad = 'Barcelona' AND limite_credito > 5000) y se cuela María José (2000 €). Con paréntesis, o mejor con IN:
SELECT cliente_id, nombre, ciudad, limite_credito
FROM clientes
WHERE (ciudad = 'Madrid' OR ciudad = 'Barcelona')
AND limite_credito > 5000;
CLIENTE_ID NOMBRE CIUDAD LIMITE_CREDITO
---------- ---------------------------- ------------ --------------
1004 Academia Nova Madrid 8000
1012 Diseños Creativos S.L. Barcelona 7000
2 filas seleccionadas.
Equivalente: WHERE ciudad IN ('Madrid', 'Barcelona') AND limite_credito > 5000.
3. Bloque B: funciones de una fila
Ejercicio 11: Nombre completo ★
Para el departamento 40, muestra “APELLIDOS, NOMBRE” en mayúsculas y su longitud.
Ver solución
SELECT UPPER(apellidos || ', ' || nombre) AS nombre_completo,
LENGTH(apellidos || ', ' || nombre) AS longitud
FROM empleados
WHERE dept_id = 40
ORDER BY nombre_completo;
NOMBRE_COMPLETO LONGITUD
------------------------------ ----------
DOMÍNGUEZ VEGA, ISABEL 22
GÓMEZ DÍAZ, PEDRO 17
VÁZQUEZ RAMOS, JORGE 20
3 filas seleccionadas.
|| concatena; CONCAT solo admite dos argumentos, así que habría que anidarlo: CONCAT(CONCAT(apellidos, ', '), nombre). LENGTH cuenta caracteres (las tildes cuentan como uno); LENGTHB contaría bytes.
Ejercicio 12: Usuario y dominio del correo ★★
Separa el correo de los clientes de Madrid y Sevilla en usuario (antes de la @) y dominio (después).
Ver solución
SELECT email,
SUBSTR(email, 1, INSTR(email, '@') - 1) AS usuario,
SUBSTR(email, INSTR(email, '@') + 1) AS dominio
FROM clientes
WHERE email IS NOT NULL
AND provincia IN ('Madrid', 'Sevilla');
EMAIL USUARIO DOMINIO
---------------------------- ------------ ------------------
mjherrera@correo.es mjherrera correo.es
secretaria@academianova.es secretaria academianova.es
admin@sonrisa.es admin sonrisa.es
3 filas seleccionadas.
INSTR devuelve la posición de la @; SUBSTR sin tercer argumento llega hasta el final. Alternativa con expresiones regulares: REGEXP_SUBSTR(email, '[^@]+', 1, 1) y REGEXP_SUBSTR(email, '[^@]+', 1, 2). Antonio Ruiz (Sevilla) no aparece porque no tiene email.
Ejercicio 13: Antigüedad en años ★★
Calcula la antigüedad en años completos de los empleados de Informática a fecha 1 de octubre de 2026.
Ver solución
SELECT nombre, fecha_alta,
TRUNC(MONTHS_BETWEEN(DATE '2026-10-01', fecha_alta) / 12) AS anios
FROM empleados
WHERE dept_id = 30
ORDER BY anios DESC;
NOMBRE FECHA_ALTA ANIOS
-------------------------- ---------- ----------
Marta 20/06/2011 15
Pablo 05/05/2014 12
Raquel 01/10/2018 8
Iván 16/03/2020 6
Hugo 20/11/2019 6
Sara 01/06/2022 4
6 filas seleccionadas.
Error típico: dividir los días entre 365 y redondear. Hugo (alta el 20/11/2019) aún no ha cumplido 7 años, pero:
SELECT nombre, ROUND((DATE '2026-10-01' - fecha_alta) / 365) AS anios_aprox
FROM empleados WHERE emp_id IN (120, 124);
NOMBRE ANIOS_APROX
-------------------------- -----------
Pablo 12
Hugo 7
2 filas seleccionadas.
ROUND redondea al alza y además la división ignora los años bisiestos. Usa TRUNC(MONTHS_BETWEEN(...) / 12). En un caso real usarías SYSDATE en lugar de una fecha fija.
Ejercicio 14: Fecha en castellano ★
Muestra la fecha de alta de los empleados de Marketing con el formato “02 de Febrero de 2015”.
Ver solución
SELECT nombre,
TO_CHAR(fecha_alta, 'DD "de" fmMonth "de" YYYY', 'NLS_DATE_LANGUAGE = SPANISH') AS alta
FROM empleados
WHERE dept_id = 50;
NOMBRE ALTA
-------------------------- ------------------------------
Laura 02 de Febrero de 2015
Nuria 21 de Mayo de 2018
Álvaro 12 de Abril de 2021
3 filas seleccionadas.
El texto literal va entre comillas dobles dentro del formato. fm (fill mode) elimina los espacios de relleno del nombre del mes (sin él saldría Mayo de). El tercer argumento fija el idioma con independencia de la sesión. Para el mes en minúsculas: fmmonth.
Ejercicio 15: Días hasta el envío ★★
Para los pedidos desde el 5018, muestra los días transcurridos entre pedido y envío como texto (“3 días”) o “Sin enviar” si no se han enviado.
Ver solución
SELECT pedido_id, fecha_pedido, fecha_envio,
NVL2(fecha_envio, TO_CHAR(fecha_envio - fecha_pedido) || ' días', 'Sin enviar') AS envio
FROM pedidos
WHERE pedido_id >= 5018
ORDER BY pedido_id;
PEDIDO_ID FECHA_PEDI FECHA_ENVI ENVIO
---------- ---------- ---------- ------------
5018 03/03/2026 06/03/2026 3 días
5019 21/04/2026 23/04/2026 2 días
5020 30/05/2026 02/06/2026 3 días
5021 15/06/2026 18/06/2026 3 días
5022 01/07/2026 (nulo) Sin enviar
5023 08/07/2026 (nulo) Sin enviar
5024 10/07/2026 (nulo) Sin enviar
7 filas seleccionadas.
La resta de dos DATE da un número de días. NVL2(x, si_no_nulo, si_nulo) elige según x. Error típico: NVL(fecha_envio - fecha_pedido, 'Sin enviar') falla con ORA-01722 porque ambos argumentos de NVL deben ser del mismo tipo (número y texto no lo son). Alternativa: CASE WHEN fecha_envio IS NULL THEN 'Sin enviar' ELSE ... END.
Ejercicio 16: Gamas de producto con CASE ★★
Clasifica los portátiles, ratones/teclados y monitores (categorías 2, 5 y 6) en “Económico” (menos de 100 €), “Medio” (menos de 500 €) y “Premium”.
Ver solución
SELECT nombre, precio,
CASE
WHEN precio < 100 THEN 'Económico'
WHEN precio < 500 THEN 'Medio'
ELSE 'Premium'
END AS gama
FROM productos
WHERE categoria_id IN (2, 5, 6)
ORDER BY precio;
NOMBRE PRECIO GAMA
-------------------------- ---------- ----------
Ratón inalámbrico 19,9 Económico
Pack teclado y ratón 34,5 Económico
Teclado mecánico 79,9 Económico
Monitor 24 FHD 139 Medio
Monitor 27 4K 329 Medio
Portátil Básico 15 499 Medio
Portátil UltraBook 14 899 Premium
Portátil Gaming 16 1499 Premium
8 filas seleccionadas.
CASE evalúa las ramas en orden y se queda con la primera verdadera; por eso la segunda no necesita precio >= 100. Si no hubiera ELSE y ninguna rama se cumpliera, el resultado sería NULL.
Ejercicio 17: Códigos de estado con DECODE ★
Muestra un código de tres letras para el estado de los pedidos 5006 a 5010 usando DECODE.
Ver solución
SELECT pedido_id, estado,
DECODE(estado, 'PENDIENTE', 'PEN',
'ENVIADO', 'ENV',
'ENTREGADO', 'ENT',
'CAN') AS codigo
FROM pedidos
WHERE pedido_id BETWEEN 5006 AND 5010;
PEDIDO_ID ESTADO CODI
---------- ------------ ----
5006 ENTREGADO ENT
5007 ENTREGADO ENT
5008 CANCELADO CAN
5009 ENTREGADO ENT
5010 ENTREGADO ENT
5 filas seleccionadas.
DECODE es exclusivo de Oracle; el equivalente estándar es CASE estado WHEN 'PENDIENTE' THEN 'PEN' ... ELSE 'CAN' END. Una curiosidad: DECODE considera iguales dos NULL (algo que CASE simple no hace).
Ejercicio 18: Teléfono enmascarado ★★
Muestra el teléfono de los empleados de Contabilidad y de Sara (123) con los dígitos centrales ocultos (600***103) o “No consta” si no lo tienen. Un compañero propone COALESCE(SUBSTR(telefono, 1, 3) || '***' || SUBSTR(telefono, -3), 'No consta'). ¿Funciona?
Ver solución
La propuesta del compañero no funciona:
NOMBRE TEL
-------------------------- ------------
Pedro 600***103
Sara ***
Isabel 600***130
Jorge ***
4 filas seleccionadas.
En Oracle, concatenar con NULL no da NULL: el nulo se trata como cadena vacía. La expresión vale '***', que no es nula, y COALESCE nunca usa el segundo argumento:
SELECT nombre, telefono, SUBSTR(telefono, 1, 3) || '***' AS concatenado
FROM empleados WHERE emp_id = 123;
NOMBRE TELEFONO CONCATENADO
-------------- --------------- ---------------
Sara (nulo) ***
1 fila seleccionada.
Hay que preguntar por el teléfono original con NVL2 (o CASE):
SELECT nombre,
NVL2(telefono, SUBSTR(telefono, 1, 3) || '***' || SUBSTR(telefono, -3), 'No consta') AS tel
FROM empleados
WHERE dept_id = 40 OR emp_id = 123;
NOMBRE TEL
-------------- ------------
Pedro 600***103
Sara No consta
Isabel 600***130
Jorge No consta
4 filas seleccionadas.
SUBSTR(x, -3) toma los tres últimos caracteres.
Ejercicio 19: Precio con IVA ★
Calcula el precio con IVA (21 %) de los productos 6, 7 y 13, redondeado, truncado y con techo.
Ver solución
SELECT nombre, precio,
precio * 1.21 AS con_iva,
ROUND(precio * 1.21, 2) AS redondeado,
TRUNC(precio * 1.21) AS truncado,
CEIL(precio * 1.21) AS techo
FROM productos
WHERE producto_id IN (6, 7, 13);
NOMBRE PRECIO CON_IVA REDONDEADO TRUNCADO TECHO
-------------------------- ---------- ---------- ---------- ---------- ----------
Ratón inalámbrico 19,9 24,079 24,08 24 25
Teclado mecánico 79,9 96,679 96,68 96 97
Auriculares Bluetooth 59,9 72,479 72,48 72 73
3 filas seleccionadas.
ROUND(x, 2) redondea a céntimos; TRUNC corta sin redondear; CEIL sube al entero siguiente y FLOOR baja al anterior. En el SQL se escribe el punto decimal (1.21) aunque la sesión muestre comas.
Ejercicio 20: Calendario de pedidos ★★
Para los pedidos desde mayo de 2026, muestra el día de la semana en castellano, el último día de ese mes y la misma fecha un mes después.
Ver solución
SELECT pedido_id, fecha_pedido,
TO_CHAR(fecha_pedido, 'fmDay', 'NLS_DATE_LANGUAGE = SPANISH') AS dia,
LAST_DAY(fecha_pedido) AS fin_mes,
ADD_MONTHS(fecha_pedido, 1) AS mes_siguiente
FROM pedidos
WHERE fecha_pedido >= DATE '2026-05-01'
ORDER BY fecha_pedido;
PEDIDO_ID FECHA_PEDI DIA FIN_MES MES_SIGUIE
---------- ---------- ---------- ---------- ----------
5020 30/05/2026 Sábado 31/05/2026 30/06/2026
5021 15/06/2026 Lunes 30/06/2026 15/07/2026
5022 01/07/2026 Miércoles 31/07/2026 01/08/2026
5023 08/07/2026 Miércoles 31/07/2026 08/08/2026
5024 10/07/2026 Viernes 31/07/2026 10/08/2026
5 filas seleccionadas.
ADD_MONTHS ajusta al último día cuando hace falta (ADD_MONTHS(DATE '2026-01-31', 1) es el 28/02/2026). Para filtrar por día de la semana no compares con el nombre (depende del idioma); usa TO_CHAR(fecha, 'D') con cuidado (depende del territorio) o, mejor, TRUNC(fecha) - TRUNC(fecha, 'IW'), que da 0 para el lunes en cualquier configuración.
4. Bloque C: agrupación
Ejercicio 21: Plantilla por departamento ★
Número de empleados y salario medio por departamento, incluidos los empleados sin departamento.
Ver solución
SELECT dept_id, COUNT(*) AS num_empleados, ROUND(AVG(salario), 2) AS salario_medio
FROM empleados
GROUP BY dept_id
ORDER BY dept_id;
DEPT_ID NUM_EMPLEADOS SALARIO_MEDIO
---------- ------------- -------------
10 1 6500
20 6 2500
30 6 3133,33
40 3 2816,67
50 3 3100
60 4 2237,5
(nulo) 1 900
7 filas seleccionadas.
GROUP BY agrupa también los nulos (forman un grupo propio: el becario). El departamento 70 no aparece porque no tiene filas en EMPLEADOS; para incluirlo hay que partir de DEPARTAMENTOS con LEFT JOIN (ejercicio 56).
Ejercicio 22: Departamentos grandes ★
Departamentos con más de 3 empleados.
Ver solución
SELECT dept_id, COUNT(*) AS num_empleados
FROM empleados
GROUP BY dept_id
HAVING COUNT(*) > 3
ORDER BY num_empleados DESC;
DEPT_ID NUM_EMPLEADOS
---------- -------------
20 6
30 6
60 4
3 filas seleccionadas.
WHERE filtra filas antes de agrupar; HAVING filtra grupos después. Una condición sobre un agregado solo puede ir en HAVING (ejercicio 30).
Ejercicio 23: Los cinco pedidos más grandes ★★
Importe neto (con descuentos) de cada pedido; muestra los cinco mayores.
Ver solución
SELECT pedido_id,
ROUND(SUM(cantidad * precio_unitario * (1 - descuento / 100)), 2) AS importe
FROM lineas_pedido
GROUP BY pedido_id
ORDER BY importe DESC
FETCH FIRST 5 ROWS ONLY;
PEDIDO_ID IMPORTE
---------- ----------
5010 11657,5
5016 8945,05
5002 7402,5
5004 6232,5
5009 4772,44
5 filas seleccionadas.
El descuento está en porcentaje (descuento = 15 significa 15 %), de ahí 1 - descuento / 100. Error típico: cantidad * precio_unitario - descuento, que resta 15 € en lugar del 15 %.
Ejercicio 24: Estadística por oficio ★★
Para cada oficio con más de un empleado: número, salario mínimo, máximo, medio y total, ordenado por la media.
Ver solución
SELECT oficio, COUNT(*) AS n, MIN(salario) AS minimo, MAX(salario) AS maximo,
ROUND(AVG(salario), 2) AS media, SUM(salario) AS total
FROM empleados
GROUP BY oficio
HAVING COUNT(*) > 1
ORDER BY media DESC;
OFICIO N MINIMO MAXIMO MEDIA TOTAL
---------------- ---------- ---------- ---------- ---------- ----------
DIRECTOR 5 4000 5000 4460 22300
ANALISTA 2 2700 3500 3100 6200
PROGRAMADOR 3 2400 2800 2600 7800
VENDEDOR 4 1900 2300 2125 8500
ADMINISTRATIVO 3 1650 1750 1700 5100
OPERARIO 2 1600 1600 1600 3200
6 filas seleccionadas.
Ejercicio 25: Pedidos por año y estado ★★
Cuántos pedidos hay de cada estado en cada año.
Ver solución
SELECT EXTRACT(YEAR FROM fecha_pedido) AS anio, estado, COUNT(*) AS pedidos
FROM pedidos
GROUP BY EXTRACT(YEAR FROM fecha_pedido), estado
ORDER BY anio, estado;
ANIO ESTADO PEDIDOS
---------- ------------ ----------
2025 CANCELADO 1
2025 ENTREGADO 14
2026 ENTREGADO 4
2026 ENVIADO 2
2026 PENDIENTE 3
5 filas seleccionadas.
En el GROUP BY debe repetirse la expresión completa (en 19c no se admite el alias; desde 23ai sí: GROUP BY anio, estado).
Ejercicio 26: Cómo cuentan los agregados ★★
En una sola consulta sobre EMPLEADOS muestra: número de filas, cuántos tienen comisión, cuántos oficios distintos hay, cuántos tienen departamento, la media de comisión y la media de comisión considerando 0 a quien no tiene. Explica cada resultado.
Ver solución
SELECT COUNT(*) AS filas, COUNT(comision) AS con_comision,
COUNT(DISTINCT oficio) AS oficios, COUNT(dept_id) AS con_dept,
AVG(comision) AS media_comision, AVG(NVL(comision, 0)) AS media_todos
FROM empleados;
FILAS CON_COMISION OFICIOS CON_DEPT MEDIA_COMISION MEDIA_TODOS
---------- ------------ ---------- ---------- -------------- -----------
24 4 11 23 400 66,6666667
1 fila seleccionada.
COUNT(*)cuenta filas;COUNT(columna)cuenta valores no nulos.AVG(comision)ignora los nulos: (500 + 800 + 300 + 0) / 4 = 400.AVG(NVL(comision, 0))divide entre 24: 1600 / 24 = 66,67.
Cuál de las dos medias es “la correcta” depende del enunciado: “comisión media de los que cobran comisión” es la primera; “comisión media por empleado”, la segunda.
Ejercicio 27: Ventas con subtotales (ROLLUP) ★★★
Ventas netas por año y mes desde septiembre de 2025 (sin cancelados), con subtotal por año y total general.
Ver solución
SELECT EXTRACT(YEAR FROM p.fecha_pedido) AS anio,
EXTRACT(MONTH FROM p.fecha_pedido) AS mes,
ROUND(SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)), 2) AS ventas
FROM pedidos p JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.estado <> 'CANCELADO' AND p.fecha_pedido >= DATE '2025-09-01'
GROUP BY ROLLUP (EXTRACT(YEAR FROM p.fecha_pedido), EXTRACT(MONTH FROM p.fecha_pedido))
ORDER BY anio, mes;
ANIO MES VENTAS
---------- ---------- ----------
2025 9 1658,8
2025 10 1555
2025 11 358,8
2025 (nulo) 3572,6
2026 1 8945,05
2026 2 838
2026 3 2085,25
2026 4 1296
...
13 filas seleccionadas.
Las filas con MES nulo son los subtotales de cada año (2025: 3572,6; 2026: 20 704,79) y la última, con ambos nulos, el total general (24 277,39). Para distinguir un nulo “de subtotal” de un nulo real se usa GROUPING(columna), que vale 1 en las filas de subtotal: DECODE(GROUPING(EXTRACT(MONTH FROM p.fecha_pedido)), 1, 'TOTAL AÑO', ...).
Ejercicio 28: Empleados de cada departamento en una línea ★★
Para cada departamento, la lista de nombres de sus empleados separados por comas y ordenados.
Ver solución
SELECT dept_id,
LISTAGG(nombre, ', ') WITHIN GROUP (ORDER BY nombre) AS empleados
FROM empleados
WHERE dept_id IS NOT NULL
GROUP BY dept_id
ORDER BY dept_id;
DEPT_ID EMPLEADOS
---------- ------------------------------------------------------------
10 Ana
20 Carmen, David, Elena, Lucía, Luis, Sergio
30 Hugo, Iván, Marta, Pablo, Raquel, Sara
40 Isabel, Jorge, Pedro
50 Álvaro, Laura, Nuria
60 Cristina, Javier, Manuel, Rosa
6 filas seleccionadas.
Fíjate en que “Álvaro” sale el último en el departamento 50: la ordenación por defecto es binaria y la Á va después de la Z. Para una ordenación lingüística usa ORDER BY NLSSORT(nombre, 'NLS_SORT = SPANISH') o ALTER SESSION SET NLS_SORT = 'SPANISH'.
Ejercicio 29: Clientes por encima de su crédito ★★★
Clientes cuyas compras (sin cancelados) superan su límite de crédito.
Ver solución
SELECT c.cliente_id, c.nombre, c.limite_credito,
ROUND(SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)), 2) AS comprado
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.cliente_id
JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.estado <> 'CANCELADO'
GROUP BY c.cliente_id, c.nombre, c.limite_credito
HAVING SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)) > c.limite_credito
ORDER BY comprado DESC;
CLIENTE_ID NOMBRE LIMITE_CREDITO COMPRADO
---------- ---------------------------- -------------- ----------
1010 Instituto Tecnológico Sur 20000 20602,55
1 fila seleccionada.
En el HAVING se puede usar c.limite_credito porque está en el GROUP BY. Si no se hubiera agrupado por él, habría que escribir MAX(c.limite_credito). Error típico: poner la condición en el WHERE (ORA-00934).
Ejercicio 30: Corregir errores de agrupación ★★
Estas dos consultas fallan. Explica por qué y corrige la primera.
SELECT e.dept_id, d.nombre, COUNT(*) AS n
FROM empleados e JOIN departamentos d ON d.dept_id = e.dept_id
GROUP BY e.dept_id;
SELECT dept_id, COUNT(*) FROM empleados WHERE COUNT(*) > 3 GROUP BY dept_id;
Ver solución
SELECT e.dept_id, d.nombre, COUNT(*) AS n
*
ERROR en línea 1:
ORA-00979: "D"."NOMBRE": must appear in the GROUP BY clause or be used in an aggregate function
SELECT dept_id, COUNT(*) FROM empleados WHERE COUNT(*) > 3 GROUP BY dept_id
*
ERROR en línea 1:
ORA-00934: group function is not allowed here
- Toda columna del
SELECTque no esté dentro de un agregado debe figurar en elGROUP BY(aunque nosotros sepamos que cadadept_idtiene un único nombre). - Los agregados no pueden ir en
WHERE; la condición va enHAVING(ejercicio 22).
SELECT e.dept_id, d.nombre, COUNT(*) AS n
FROM empleados e JOIN departamentos d ON d.dept_id = e.dept_id
GROUP BY e.dept_id, d.nombre
ORDER BY e.dept_id;
DEPT_ID NOMBRE N
---------- ---------------------------- ----------
10 DIRECCIÓN 1
20 VENTAS 6
30 INFORMÁTICA 6
40 CONTABILIDAD 3
50 MARKETING 3
60 LOGÍSTICA 4
6 filas seleccionadas.
5. Bloque D: consultas multitabla
Ejercicio 31: Empleados con su departamento ★
Empleados de los departamentos situados en Sevilla, Barcelona o Zaragoza, con el nombre y la localidad del departamento. ¿Cuántas filas daría el join sin filtro?
Ver solución
SELECT e.emp_id, e.nombre, d.nombre AS departamento, d.localidad
FROM empleados e
JOIN departamentos d ON d.dept_id = e.dept_id
WHERE d.localidad IN ('Sevilla', 'Barcelona', 'Zaragoza')
ORDER BY d.nombre, e.nombre;
EMP_ID NOMBRE DEPARTAMENTO LOCALIDAD
---------- ------------ -------------- ----------------------------------------
130 Isabel CONTABILIDAD Sevilla
131 Jorge CONTABILIDAD Sevilla
103 Pedro CONTABILIDAD Sevilla
152 Cristina LOGÍSTICA Zaragoza
105 Javier LOGÍSTICA Zaragoza
151 Manuel LOGÍSTICA Zaragoza
150 Rosa LOGÍSTICA Zaragoza
141 Álvaro MARKETING Barcelona
104 Laura MARKETING Barcelona
140 Nuria MARKETING Barcelona
10 filas seleccionadas.
SELECT COUNT(*) FROM empleados e JOIN departamentos d ON d.dept_id = e.dept_id;
COUNT(*)
----------
23
1 fila seleccionada.
23 y no 24: el INNER JOIN descarta al becario, que no tiene departamento.
Ejercicio 32: Todos los empleados, tengan o no departamento ★★
Muestra la directora general, los contables y el becario con el nombre de su departamento, o “SIN DEPARTAMENTO”.
Ver solución
SELECT e.emp_id, e.nombre, NVL(d.nombre, 'SIN DEPARTAMENTO') AS departamento
FROM empleados e
LEFT JOIN departamentos d ON d.dept_id = e.dept_id
WHERE e.oficio IN ('BECARIO', 'DIRECTOR GENERAL', 'CONTABLE')
ORDER BY e.emp_id;
EMP_ID NOMBRE DEPARTAMENTO
---------- -------------- ----------------
100 Ana DIRECCIÓN
130 Isabel CONTABILIDAD
160 Tomás SIN DEPARTAMENTO
3 filas seleccionadas.
Con LEFT JOIN se conservan todas las filas de la tabla de la izquierda (EMPLEADOS): el total es 24. Error típico: añadir en el WHERE una condición sobre la tabla de la derecha (AND d.localidad = 'Madrid'), lo que elimina las filas con d.* nulo y convierte el join externo en interno. Si el filtro es sobre la tabla opcional, debe ir en el ON.
Ejercicio 33: Departamentos vacíos ★★
¿Qué departamentos no tienen ningún empleado?
Ver solución
SELECT d.dept_id, d.nombre AS departamento
FROM departamentos d
LEFT JOIN empleados e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;
DEPT_ID DEPARTAMENTO
---------- --------------
70 INVESTIGACIÓN
1 fila seleccionada.
Es el patrón anti-join: join externo y quedarse con las filas sin pareja. Se comprueba una columna que no pueda ser nula en una fila real de la derecha (la clave primaria). Equivale a WHERE NOT EXISTS (SELECT 1 FROM empleados e WHERE e.dept_id = d.dept_id) y a un MINUS.
Ejercicio 34: Cada empleado con su jefe ★★
Para los empleados 100, 102, 120, 121 y 160, muestra el nombre del jefe (o nulo si no tiene).
Ver solución
SELECT e.emp_id, e.nombre AS empleado, j.nombre AS jefe
FROM empleados e
LEFT JOIN empleados j ON j.emp_id = e.jefe_id
WHERE e.emp_id IN (100, 102, 120, 121, 160)
ORDER BY e.emp_id;
EMP_ID EMPLEADO JEFE
---------- ------------ ------------
100 Ana (nulo)
102 Marta Ana
120 Pablo Marta
121 Raquel Pablo
160 Tomás Marta
5 filas seleccionadas.
En una autocombinación (self join) los alias son obligatorios para distinguir los dos papeles de la tabla. Con JOIN interno, Ana desaparecería.
Ejercicio 35: Clientes, comerciales y departamentos ★★
Para los clientes 1007 a 1014, el nombre de su comercial y el departamento del comercial, incluidos los clientes sin comercial.
Ver solución
SELECT c.nombre AS cliente, e.nombre AS comercial, d.nombre AS departamento
FROM clientes c
LEFT JOIN empleados e ON e.emp_id = c.comercial_id
LEFT JOIN departamentos d ON d.dept_id = e.dept_id
WHERE c.cliente_id BETWEEN 1007 AND 1014
ORDER BY c.cliente_id;
CLIENTE COMERCIAL DEPARTAMENTO
---------------------------- ---------- --------------
Hotel Mar Azul S.L. David VENTAS
Gestoría Delta Sergio VENTAS
Laura Benítez Sanz (nulo) (nulo)
Instituto Tecnológico Sur Elena VENTAS
Panadería La Espiga Sergio VENTAS
Diseños Creativos S.L. Carmen VENTAS
Carlos Ibáñez Rey (nulo) (nulo)
Farmacia Central Sergio VENTAS
8 filas seleccionadas.
Error típico: hacer LEFT JOIN con EMPLEADOS y luego un JOIN interno con DEPARTAMENTOS; el segundo join eliminaría las filas con comercial nulo. Cuando una cadena de joins empieza a ser externa, los siguientes también deben serlo.
Ejercicio 36: Detalle de un pedido ★★
Detalle del pedido 5010: número de línea, producto, categoría, cantidad, precio, descuento e importe de la línea.
Ver solución
SELECT l.num_linea, p.nombre AS producto, c.nombre AS categoria,
l.cantidad, l.precio_unitario, l.descuento,
ROUND(l.cantidad * l.precio_unitario * (1 - l.descuento / 100), 2) AS importe
FROM lineas_pedido l
JOIN productos p ON p.producto_id = l.producto_id
JOIN categorias c ON c.categoria_id = p.categoria_id
WHERE l.pedido_id = 5010
ORDER BY l.num_linea;
NUM_LINEA PRODUCTO CATEGORIA CANTIDAD PRECIO_UNITARIO DESCUENTO IMPORTE
---------- ------------------------ -------------------- ---------- --------------- ---------- ----------
1 Portátil Básico 15 Portátiles 20 499 15 8483
2 Monitor 24 FHD Monitores 20 139 15 2363
3 Pack teclado y ratón Ratones y teclados 20 34,5 15 586,5
4 Webcam HD Periféricos 5 45 0 225
4 filas seleccionadas.
La suma de los importes (11 657,5) coincide con el ejercicio 23. Se usa l.precio_unitario (el precio al que se vendió), no p.precio (el precio actual del catálogo).
Ejercicio 37: Producto, categoría y categoría padre ★★★
Para los productos 1, 6, 9, 15, 19 y 20, muestra su categoría y la categoría padre de esta.
Ver solución
SELECT p.nombre AS producto, c.nombre AS categoria, cp.nombre AS padre
FROM productos p
JOIN categorias c ON c.categoria_id = p.categoria_id
LEFT JOIN categorias cp ON cp.categoria_id = c.categoria_padre_id
WHERE p.producto_id IN (1, 6, 9, 15, 19, 20)
ORDER BY p.producto_id;
PRODUCTO CATEGORIA PADRE
------------------------ -------------------- --------------
Portátil UltraBook 14 Portátiles Informática
Ratón inalámbrico Ratones y teclados Periféricos
Monitor 24 FHD Monitores Periféricos
Silla Ergonómica Mobiliario Oficina
Disco SSD 1TB Informática (nulo)
Tablet 10 Electrónica (nulo)
6 filas seleccionadas.
CATEGORIAS aparece dos veces con alias distintos. El LEFT JOIN es necesario para los productos asignados a una categoría raíz (sin padre). Para recorrer todos los niveles de la jerarquía se necesita una consulta jerárquica o recursiva (ejercicios 58 y 65).
Ejercicio 38: Productos nunca vendidos ★★
Productos que no aparecen en ninguna línea de pedido.
Ver solución
SELECT p.producto_id, p.nombre AS producto, p.descatalogado
FROM productos p
LEFT JOIN lineas_pedido l ON l.producto_id = p.producto_id
WHERE l.producto_id IS NULL;
PRODUCTO_ID PRODUCTO D
----------- ------------------------ -
14 Altavoz Inteligente S
1 fila seleccionada.
Alternativas: NOT EXISTS y NOT IN (SELECT producto_id FROM lineas_pedido); esta última funciona aquí porque producto_id es NOT NULL en LINEAS_PEDIDO.
Ejercicio 39: Sintaxis tradicional de Oracle ★★
Reescribe el ejercicio 32 (sin la función NVL) y el 33 con la sintaxis tradicional de Oracle para combinaciones externas.
Ver solución
SELECT e.emp_id, e.nombre, d.nombre AS departamento
FROM empleados e, departamentos d
WHERE e.dept_id = d.dept_id (+)
AND e.oficio IN ('BECARIO', 'DIRECTOR GENERAL', 'CONTABLE')
ORDER BY e.emp_id;
EMP_ID NOMBRE DEPARTAMENTO
---------- -------------- ----------------
100 Ana DIRECCIÓN
130 Isabel CONTABILIDAD
160 Tomás (nulo)
3 filas seleccionadas.
SELECT d.dept_id, d.nombre
FROM departamentos d, empleados e
WHERE d.dept_id = e.dept_id (+)
AND e.emp_id IS NULL;
DEPT_ID NOMBRE
---------- --------------
70 INVESTIGACIÓN
1 fila seleccionada.
El (+) se pone en el lado de la tabla opcional (la que puede no tener pareja), justo al contrario de lo que sugiere LEFT. Esta sintaxis está desaconsejada (no admite FULL OUTER JOIN y es fácil equivocarse), pero aparece mucho en código heredado y en exámenes.
Ejercicio 40: Trabajo fuera del propio departamento ★★★
Empleados asignados a proyectos de un departamento distinto del suyo, con las horas dedicadas. ¿Cuántas filas daría un CROSS JOIN de EMPLEADOS y DEPARTAMENTOS?
Ver solución
SELECT e.nombre AS empleado, e.dept_id AS dept_empleado,
pr.nombre AS proyecto, pr.dept_id AS dept_proyecto, a.horas
FROM asignaciones a
JOIN empleados e ON e.emp_id = a.emp_id
JOIN proyectos pr ON pr.proyecto_id = a.proyecto_id
WHERE e.dept_id <> pr.dept_id
ORDER BY e.nombre;
EMPLEADO DEPT_EMPLEADO PROYECTO DEPT_PROYECTO HORAS
------------ ------------- ------------------------ ------------- ----------
Carmen 20 Campaña Navidad 2025 50 40
Nuria 50 Nueva tienda online 30 120
2 filas seleccionadas.
SELECT COUNT(*) FROM empleados CROSS JOIN departamentos;
COUNT(*)
----------
168
1 fila seleccionada.
El producto cartesiano da 24 × 7 = 168 filas. Si algún empleado o proyecto tuviera dept_id nulo, la condición <> sería desconocida y esa fila no saldría; para tratar el nulo como “distinto” habría que escribir DECODE(e.dept_id, pr.dept_id, 0, 1) = 1 (DECODE considera iguales dos nulos). El predicado estándar IS DISTINCT FROM no está disponible en esta versión de Oracle: al probarlo devuelve ORA-00908.
6. Bloque E: subconsultas
Ejercicio 41: Por encima de la media ★
Empleados que ganan más que la media de la empresa.
Ver solución
SELECT nombre, oficio, salario
FROM empleados
WHERE salario > (SELECT AVG(salario) FROM empleados)
ORDER BY salario DESC;
NOMBRE OFICIO SALARIO
---------------------------- ---------------- ----------
Ana DIRECTOR GENERAL 6500
Marta DIRECTOR 5000
Luis DIRECTOR 4800
Laura DIRECTOR 4300
Pedro DIRECTOR 4200
Javier DIRECTOR 4000
Pablo ANALISTA 3500
7 filas seleccionadas.
La media es 2829,17 €. Error típico: WHERE salario > AVG(salario) (ORA-00934): un agregado no puede ir directamente en el WHERE; hay que calcularlo en una subconsulta.
Ejercicio 42: Por encima de la media de su departamento ★★
Empleados que ganan más que la media de su propio departamento.
Ver solución
SELECT e.nombre, e.dept_id, e.salario
FROM empleados e
WHERE e.salario > (SELECT AVG(x.salario) FROM empleados x WHERE x.dept_id = e.dept_id)
ORDER BY e.dept_id, e.salario DESC;
NOMBRE DEPT_ID SALARIO
---------------------------- ---------- ----------
Luis 20 4800
Marta 30 5000
Pablo 30 3500
Pedro 40 4200
Laura 50 4300
Javier 60 4000
6 filas seleccionadas.
Es una subconsulta correlacionada: se reevalúa para cada fila exterior con su dept_id. Ana no aparece (es la única de su departamento: no supera su propia media) y el becario tampoco (x.dept_id = NULL no encuentra filas y la media es nula). Alternativa con analíticas: SELECT ... FROM (SELECT e.*, AVG(salario) OVER (PARTITION BY dept_id) AS media FROM empleados e) WHERE salario > media.
Ejercicio 43: El que más cobra de cada departamento ★★
Empleado con el salario máximo de cada departamento (con empates, si los hubiera).
Ver solución
SELECT dept_id, nombre, salario
FROM empleados
WHERE (dept_id, salario) IN (SELECT dept_id, MAX(salario)
FROM empleados
GROUP BY dept_id)
ORDER BY dept_id;
DEPT_ID NOMBRE SALARIO
---------- ---------------------------- ----------
10 Ana 6500
20 Luis 4800
30 Marta 5000
40 Pedro 4200
50 Laura 4300
60 Javier 4000
6 filas seleccionadas.
Comparación de pares con IN. El becario no sale porque la pareja (NULL, 900) nunca es igual a nada. Error típico: WHERE salario IN (SELECT MAX(salario) ... GROUP BY dept_id) sin el dept_id, que mezclaría departamentos (alguien de un departamento que cobrara lo mismo que el máximo de otro aparecería).
Ejercicio 44: Compradores de portátiles ★★
Clientes que han comprado algún producto de la categoría “Portátiles”, usando solo subconsultas con IN.
Ver solución
SELECT cliente_id, nombre
FROM clientes
WHERE cliente_id IN (SELECT p.cliente_id
FROM pedidos p
WHERE p.pedido_id IN (SELECT l.pedido_id
FROM lineas_pedido l
WHERE l.producto_id IN (SELECT pr.producto_id
FROM productos pr
JOIN categorias c ON c.categoria_id = pr.categoria_id
WHERE c.nombre = 'Portátiles')))
ORDER BY cliente_id;
CLIENTE_ID NOMBRE
---------- ----------------------------
1001 Talleres Martín S.L.
1003 María José Herrera
1004 Academia Nova
1008 Gestoría Delta
1010 Instituto Tecnológico Sur
1012 Diseños Creativos S.L.
6 filas seleccionadas.
Con joins sería SELECT DISTINCT c.cliente_id, c.nombre FROM clientes c JOIN pedidos ... WHERE cat.nombre = 'Portátiles'; el DISTINCT es imprescindible porque un cliente puede haber comprado varios portátiles. Con IN no hay duplicados porque cada cliente se evalúa una vez.
Ejercicio 45: Más caros que todos los monitores ★★
Productos de menos de 500 € más caros que todos los monitores (categoría 6). ¿Qué pasa con > ALL si la subconsulta no devuelve filas?
Ver solución
SELECT nombre, precio
FROM productos
WHERE precio > ALL (SELECT precio FROM productos WHERE categoria_id = 6)
AND precio < 500
ORDER BY precio;
NOMBRE PRECIO
---------------------------- ----------
Mesa Elevable 399
Portátil Básico 15 499
2 filas seleccionadas.
Equivale a precio > (SELECT MAX(precio) FROM productos WHERE categoria_id = 6), que da el mismo resultado. Pero cuidado con el caso vacío:
SELECT COUNT(*) FROM productos WHERE precio > ALL (SELECT precio FROM productos WHERE categoria_id = 99);
COUNT(*)
----------
20
1 fila seleccionada.
> ALL (conjunto vacío) es verdadero para todas las filas, mientras que > (SELECT MAX(...)) con conjunto vacío compara con NULL y no devuelve ninguna. No son totalmente equivalentes.
Ejercicio 46: Clientes sin pedidos ★★
Clientes que nunca han hecho un pedido, con NOT EXISTS.
Ver solución
SELECT c.cliente_id, c.nombre
FROM clientes c
WHERE NOT EXISTS (SELECT 1 FROM pedidos p WHERE p.cliente_id = c.cliente_id);
CLIENTE_ID NOMBRE
---------- ----------------------------
1013 Carlos Ibáñez Rey
1014 Farmacia Central
2 filas seleccionadas.
Lo que se selecciona dentro de EXISTS es indiferente (SELECT 1, SELECT *…): solo importa si hay filas.
Ejercicio 47: El departamento más numeroso ★★★
Departamento(s) con más empleados, sin usar FETCH FIRST.
Ver solución
SELECT dept_id, COUNT(*) AS n
FROM empleados
GROUP BY dept_id
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM empleados GROUP BY dept_id);
DEPT_ID N
---------- ----------
20 6
30 6
2 filas seleccionadas.
Oracle permite anidar dos funciones de grupo (MAX(COUNT(*))) en una consulta agrupada; es una extensión no estándar. Alternativas: una CTE con los recuentos y WHERE n = (SELECT MAX(n) FROM cte), o FETCH FIRST 1 ROW WITH TIES. Fíjate en que hay empate: una solución con ROWNUM = 1 daría solo uno de los dos.
Ejercicio 48: Importe medio por pedido de cada cliente ★★
Para los clientes con al menos dos pedidos, número de pedidos e importe medio por pedido, usando una subconsulta en el FROM.
Ver solución
SELECT c.nombre, t.pedidos, t.media_pedido
FROM clientes c
JOIN (SELECT p.cliente_id, COUNT(DISTINCT p.pedido_id) AS pedidos,
ROUND(SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100))
/ COUNT(DISTINCT p.pedido_id), 2) AS media_pedido
FROM pedidos p JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
GROUP BY p.cliente_id) t ON t.cliente_id = c.cliente_id
WHERE t.pedidos >= 2
ORDER BY t.media_pedido DESC;
NOMBRE PEDIDOS MEDIA_PEDIDO
---------------------------- ---------- ------------
Instituto Tecnológico Sur 2 10301,28
Construcciones Levante S.A. 3 4753,4
Academia Nova 2 3945,65
Hotel Mar Azul S.L. 2 2101,38
Talleres Martín S.L. 3 1568,05
Diseños Creativos S.L. 2 1527
Gestoría Delta 2 1147,5
Clínica Dental Sonrisa 2 1116
María José Herrera 2 418,95
Laura Benítez Sanz 2 249,3
10 filas seleccionadas.
Error típico: AVG(l.cantidad * l.precio_unitario ...) daría la media por línea, no por pedido. Por eso se divide la suma entre el número de pedidos distintos. (Esta consulta incluye el pedido cancelado de Gestoría Delta; añade WHERE p.estado <> 'CANCELADO' si el enunciado lo excluye.)
Ejercicio 49: Subconsultas escalares en el SELECT ★★
Para cada departamento, cuántos proyectos y cuántos empleados tiene, con subconsultas escalares.
Ver solución
SELECT d.dept_id, d.nombre,
(SELECT COUNT(*) FROM proyectos p WHERE p.dept_id = d.dept_id) AS proyectos,
(SELECT COUNT(*) FROM empleados e WHERE e.dept_id = d.dept_id) AS empleados
FROM departamentos d
ORDER BY d.dept_id;
DEPT_ID NOMBRE PROYECTOS EMPLEADOS
---------- ---------------------------- ---------- ----------
10 DIRECCIÓN 1 1
20 VENTAS 0 6
30 INFORMÁTICA 2 6
40 CONTABILIDAD 0 3
50 MARKETING 1 3
60 LOGÍSTICA 1 4
70 INVESTIGACIÓN 0 0
7 filas seleccionadas.
Con dos LEFT JOIN y GROUP BY habría que usar COUNT(DISTINCT ...), porque el join de proyectos y empleados multiplica las filas (6 empleados × 2 proyectos = 12 filas para Informática). Las subconsultas escalares evitan ese efecto y dan 0 (no NULL) a los departamentos sin datos.
Ejercicio 50: División relacional ★★★
Clientes que han comprado todos los productos de la categoría Monitores (categoría 6). Resuélvelo con doble NOT EXISTS y con recuento.
Ver solución
“Clientes para los que no existe ningún monitor que no hayan comprado”:
SELECT c.cliente_id, c.nombre
FROM clientes c
WHERE NOT EXISTS (SELECT 1
FROM productos pr
WHERE pr.categoria_id = 6
AND NOT EXISTS (SELECT 1
FROM pedidos p JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.cliente_id = c.cliente_id
AND l.producto_id = pr.producto_id));
CLIENTE_ID NOMBRE
---------- ----------------------------
1001 Talleres Martín S.L.
1002 Construcciones Levante S.A.
2 filas seleccionadas.
Por recuento: los clientes que han comprado tantos monitores distintos como monitores hay:
SELECT p.cliente_id
FROM pedidos p JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE l.producto_id IN (SELECT producto_id FROM productos WHERE categoria_id = 6)
GROUP BY p.cliente_id
HAVING COUNT(DISTINCT l.producto_id) = (SELECT COUNT(*) FROM productos WHERE categoria_id = 6);
CLIENTE_ID
----------
1001
1002
2 filas seleccionadas.
Es la división del álgebra relacional (÷, módulo 18). Las dos versiones difieren si el divisor está vacío: con doble NOT EXISTS saldrían todos los clientes (verdad vacía); con el recuento, ninguno. El examen de universidad de la sección 12 trata este matiz.
7. Bloque F: operadores de conjuntos y CTE
Ejercicio 51: Ciudades con clientes o departamentos ★
Ciudades de Madrid, Valencia y Bilbao con su origen (cliente o departamento) y, después, la lista de todas las ciudades donde hay clientes o departamentos, sin repetir.
Ver solución
SELECT ciudad, 'CLIENTE' AS origen FROM clientes WHERE provincia IN ('Madrid', 'Valencia', 'Bizkaia')
UNION
SELECT localidad, 'DEPARTAMENTO' FROM departamentos WHERE localidad IN ('Madrid', 'Valencia', 'Bilbao')
ORDER BY 1, 2;
CIUDAD ORIGEN
------------ ------------
Bilbao CLIENTE
Bilbao DEPARTAMENTO
Madrid CLIENTE
Madrid DEPARTAMENTO
Valencia CLIENTE
Valencia DEPARTAMENTO
6 filas seleccionadas.
SELECT ciudad FROM clientes
UNION
SELECT localidad FROM departamentos
ORDER BY 1;
CIUDAD
------------
Alicante
Barcelona
Bilbao
Granada
Madrid
Oviedo
Palma
Sevilla
Valencia
Valladolid
Zaragoza
11 filas seleccionadas.
Las columnas del resultado toman el nombre de la primera consulta (CIUDAD). El ORDER BY va al final, una sola vez, y puede usar la posición.
Ejercicio 52: Comerciales que participan en proyectos ★★
Empleados que son comerciales de algún cliente y están asignados a algún proyecto.
Ver solución
SELECT comercial_id AS emp_id FROM clientes
INTERSECT
SELECT emp_id FROM asignaciones;
EMP_ID
----------
110
1 fila seleccionada.
Solo Carmen. INTERSECT elimina duplicados y, a diferencia de IN, trata los NULL como iguales entre sí (aquí el NULL de los clientes sin comercial no tiene pareja en ASIGNACIONES).
Ejercicio 53: Empleados que no son jefes ★★
Empleados que no son jefes de nadie, con MINUS. ¿Por qué falla la versión con NOT IN?
Ver solución
SELECT emp_id, nombre FROM empleados
MINUS
SELECT j.emp_id, j.nombre FROM empleados j JOIN empleados e ON e.jefe_id = j.emp_id
ORDER BY 1;
EMP_ID NOMBRE
---------- --------------
110 Carmen
111 David
112 Elena
113 Sergio
114 Lucía
121 Raquel
122 Iván
123 Sara
...
17 filas seleccionadas.
SELECT COUNT(*) FROM empleados WHERE emp_id NOT IN (SELECT jefe_id FROM empleados);
SELECT COUNT(*) FROM empleados WHERE emp_id NOT IN (SELECT jefe_id FROM empleados WHERE jefe_id IS NOT NULL);
COUNT(*)
----------
0
1 fila seleccionada.
COUNT(*)
----------
17
1 fila seleccionada.
La subconsulta incluye el jefe_id nulo de Ana, y x NOT IN (..., NULL) nunca es verdadero. Con el filtro IS NOT NULL (o con NOT EXISTS) se obtienen los 17.
Ejercicio 54: UNION frente a UNION ALL ★★
Cuenta cuántas filas devuelve la unión de las provincias de los clientes y las localidades de los departamentos, con UNION y con UNION ALL.
Ver solución
SELECT COUNT(*) AS con_union FROM (
SELECT provincia FROM clientes UNION SELECT localidad FROM departamentos);
SELECT COUNT(*) AS con_union_all FROM (
SELECT provincia FROM clientes UNION ALL SELECT localidad FROM departamentos);
CON_UNION
----------
12
1 fila seleccionada.
CON_UNION_ALL
-------------
21
1 fila seleccionada.
UNION ALL concatena (14 + 7 = 21) y es más rápido porque no ordena ni elimina duplicados; UNION deja 12 valores distintos. Usa UNION ALL siempre que sepas que no hay duplicados o que quieras conservarlos.
Ejercicio 55: Vendedores frente a la media (CTE) ★★
Con una cláusula WITH, calcula las ventas de cada empleado (sin cancelados), la media de todos ellos y la diferencia de cada uno con la media.
Ver solución
WITH ventas_vendedor AS (
SELECT p.empleado_id,
ROUND(SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)), 2) AS ventas
FROM pedidos p JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.estado <> 'CANCELADO' AND p.empleado_id IS NOT NULL
GROUP BY p.empleado_id
),
media AS (
SELECT ROUND(AVG(ventas), 2) AS media_ventas FROM ventas_vendedor
)
SELECT e.nombre, v.ventas, m.media_ventas,
ROUND(v.ventas - m.media_ventas, 2) AS diferencia
FROM ventas_vendedor v
JOIN empleados e ON e.emp_id = v.empleado_id
CROSS JOIN media m
ORDER BY v.ventas DESC;
NOMBRE VENTAS MEDIA_VENTAS DIFERENCIA
-------------- ---------- ------------ ----------
Elena 23833,55 15279,72 8553,83
Carmen 22018,34 15279,72 6738,62
David 12931,97 15279,72 -2347,75
Sergio 2335 15279,72 -12944,72
4 filas seleccionadas.
Una CTE puede usar las anteriores (media usa ventas_vendedor). El CROSS JOIN con una tabla de una sola fila es la forma limpia de “pegar” un valor global a cada fila.
Ejercicio 56: Cuadro de mando por departamento ★★★
Para todos los departamentos: empleados, masa salarial, proyectos y horas asignadas a sus proyectos.
Ver solución
WITH emp AS (
SELECT dept_id, COUNT(*) AS empleados, SUM(salario) AS masa
FROM empleados GROUP BY dept_id
),
pry AS (
SELECT p.dept_id, COUNT(DISTINCT p.proyecto_id) AS proyectos, NVL(SUM(a.horas), 0) AS horas
FROM proyectos p LEFT JOIN asignaciones a ON a.proyecto_id = p.proyecto_id
GROUP BY p.dept_id
)
SELECT d.dept_id, d.nombre,
NVL(emp.empleados, 0) AS empleados, NVL(emp.masa, 0) AS masa,
NVL(pry.proyectos, 0) AS proyectos, NVL(pry.horas, 0) AS horas
FROM departamentos d
LEFT JOIN emp ON emp.dept_id = d.dept_id
LEFT JOIN pry ON pry.dept_id = d.dept_id
ORDER BY d.dept_id;
DEPT_ID NOMBRE EMPLEADOS MASA PROYECTOS HORAS
---------- -------------- ---------- ---------- ---------- ----------
10 DIRECCIÓN 1 6500 1 0
20 VENTAS 6 15000 0 0
30 INFORMÁTICA 6 18800 2 2780
40 CONTABILIDAD 3 8450 0 0
50 MARKETING 3 9300 1 480
60 LOGÍSTICA 4 8950 1 300
70 INVESTIGACIÓN 0 0 0 0
7 filas seleccionadas.
La clave es agregar cada tabla por separado antes de combinar. Si se hiciera un único GROUP BY sobre DEPARTAMENTOS ⟕ EMPLEADOS ⟕ PROYECTOS ⟕ ASIGNACIONES, la masa salarial de Informática se multiplicaría por el número de asignaciones (fan trap).
Ejercicio 57: Subordinados de Marta (CTE recursiva) ★★★
Con una CTE recursiva, todos los subordinados directos e indirectos de Marta (102), con su nivel y sangría.
Ver solución
WITH subordinados (emp_id, nombre, jefe_id, nivel) AS (
SELECT emp_id, nombre, jefe_id, 1
FROM empleados
WHERE emp_id = 102
UNION ALL
SELECT e.emp_id, e.nombre, e.jefe_id, s.nivel + 1
FROM empleados e JOIN subordinados s ON e.jefe_id = s.emp_id
)
SELECT LPAD(' ', (nivel - 1) * 2) || nombre AS empleado, emp_id, jefe_id, nivel
FROM subordinados
ORDER BY nivel, emp_id;
EMPLEADO EMP_ID JEFE_ID NIVEL
------------------------ ---------- ---------- ----------
Marta 102 100 1
Pablo 120 102 2
Hugo 124 102 2
Tomás 160 102 2
Raquel 121 120 3
Iván 122 120 3
Sara 123 120 3
7 filas seleccionadas.
En Oracle la CTE recursiva exige la lista de columnas entre paréntesis tras el nombre, y la parte recursiva debe unirse con UNION ALL. La equivalente con CONNECT BY es START WITH emp_id = 102 CONNECT BY PRIOR emp_id = jefe_id.
Ejercicio 58: Rutas de categorías (CTE recursiva) ★★★
Muestra la ruta completa de cada categoría (“Informática > Periféricos > Monitores”).
Ver solución
WITH rutas (categoria_id, nombre, ruta) AS (
SELECT categoria_id, nombre, CAST(nombre AS VARCHAR2(200))
FROM categorias
WHERE categoria_padre_id IS NULL
UNION ALL
SELECT c.categoria_id, c.nombre, r.ruta || ' > ' || c.nombre
FROM categorias c JOIN rutas r ON c.categoria_padre_id = r.categoria_id
)
SELECT categoria_id, ruta
FROM rutas
ORDER BY ruta;
CATEGORIA_ID RUTA
------------ --------------------------------------------------
7 Electrónica
9 Electrónica > Audio
8 Electrónica > Telefonía
1 Informática
3 Informática > Ordenadores sobremesa
4 Informática > Periféricos
6 Informática > Periféricos > Monitores
5 Informática > Periféricos > Ratones y teclados
2 Informática > Portátiles
10 Oficina
11 Oficina > Mobiliario
11 filas seleccionadas.
El CAST en el miembro ancla fija el tipo y la longitud de la columna ruta. En 26ai la consulta funciona también sin él (lo hemos comprobado), pero el tipo de cada columna de una CTE recursiva lo determina el miembro ancla y depender de la conversión implícita es frágil entre versiones: declara el tipo siempre que la columna vaya a crecer en cada nivel.
8. Bloque G: funciones analíticas y consultas jerárquicas
Ejercicio 59: RANK, DENSE_RANK y ROW_NUMBER ★★
Clasifica por salario a los empleados de los departamentos 20 y 60 dentro de su departamento, con las tres funciones de numeración. Explica las diferencias.
Ver solución
SELECT dept_id, nombre, salario,
RANK() OVER (PARTITION BY dept_id ORDER BY salario DESC) AS rk,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salario DESC) AS drk,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salario DESC) AS rn
FROM empleados
WHERE dept_id IN (20, 60)
ORDER BY dept_id, salario DESC;
DEPT_ID NOMBRE SALARIO RK DRK RN
---------- ------------ ---------- ---------- ---------- ----------
20 Luis 4800 1 1 1
20 Elena 2300 2 2 2
20 Carmen 2200 3 3 3
20 David 2100 4 4 4
20 Sergio 1900 5 5 5
20 Lucía 1700 6 6 6
60 Javier 4000 1 1 1
60 Cristina 1750 2 2 2
60 Rosa 1600 3 3 3
60 Manuel 1600 3 3 4
10 filas seleccionadas.
Rosa y Manuel empatan: RANK y DENSE_RANK les dan el mismo puesto (3); ROW_NUMBER los numera de forma arbitraria. Si hubiera un siguiente, RANK le daría el 5 (deja hueco) y DENSE_RANK el 4. PARTITION BY reinicia la numeración en cada departamento.
Ejercicio 60: Los dos mejor pagados de cada departamento ★★
Top-2 de salarios por departamento.
Ver solución
SELECT dept_id, nombre, salario
FROM (SELECT dept_id, nombre, salario,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salario DESC, emp_id) AS rn
FROM empleados
WHERE dept_id IS NOT NULL)
WHERE rn <= 2
ORDER BY dept_id, salario DESC;
DEPT_ID NOMBRE SALARIO
---------- ------------ ----------
10 Ana 6500
20 Luis 4800
20 Elena 2300
30 Marta 5000
30 Pablo 3500
40 Pedro 4200
40 Isabel 2600
50 Laura 4300
50 Álvaro 2700
60 Javier 4000
60 Cristina 1750
11 filas seleccionadas.
Las funciones analíticas no se pueden usar en el WHERE de la misma consulta (se calculan después), de ahí la subconsulta. Con empates relevantes, usa RANK() o DENSE_RANK() en lugar de ROW_NUMBER(). El criterio de desempate emp_id hace el resultado determinista.
Ejercicio 61: Ventas acumuladas ★★
Ventas por mes (sin cancelados) con el acumulado dentro de cada año y el acumulado total.
Ver solución
SELECT anio, mes, ventas,
SUM(ventas) OVER (PARTITION BY anio ORDER BY mes) AS acumulado_anio,
SUM(ventas) OVER (ORDER BY anio, mes) AS acumulado_total
FROM (SELECT EXTRACT(YEAR FROM p.fecha_pedido) AS anio,
EXTRACT(MONTH FROM p.fecha_pedido) AS mes,
ROUND(SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)), 2) AS ventas
FROM pedidos p JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.estado <> 'CANCELADO'
GROUP BY EXTRACT(YEAR FROM p.fecha_pedido), EXTRACT(MONTH FROM p.fecha_pedido))
ORDER BY anio, mes;
ANIO MES VENTAS ACUMULADO_ANIO ACUMULADO_TOTAL
---------- ---------- ---------- -------------- ---------------
2025 1 9205,4 9205,4 9205,4
2025 2 6791,4 15996,8 15996,8
2025 3 1375,2 17372 17372
2025 4 2539,13 19911,13 19911,13
2025 5 4772,44 24683,57 24683,57
2025 6 12656,5 37340,07 37340,07
2025 9 1658,8 38998,87 38998,87
2025 10 1555 40553,87 40553,87
...
16 filas seleccionadas.
En enero de 2026 el acumulado anual vuelve a empezar (8945,05) y el total sigue (49 857,72). Con ORDER BY dentro de OVER, la ventana por defecto va desde el principio de la partición hasta la fila actual.
Ejercicio 62: Tiempo entre pedidos de un cliente ★★
Para los clientes 1001 y 1002, la fecha del pedido anterior y los días transcurridos desde él.
Ver solución
SELECT cliente_id, pedido_id, fecha_pedido,
LAG(fecha_pedido) OVER (PARTITION BY cliente_id ORDER BY fecha_pedido) AS anterior,
fecha_pedido - LAG(fecha_pedido) OVER (PARTITION BY cliente_id ORDER BY fecha_pedido) AS dias
FROM pedidos
WHERE cliente_id IN (1001, 1002)
ORDER BY cliente_id, fecha_pedido;
CLIENTE_ID PEDIDO_ID FECHA_PEDI ANTERIOR DIAS
---------- ---------- ---------- ---------- ----------
1001 5001 15/01/2025 (nulo) (nulo)
1001 5006 22/03/2025 15/01/2025 66
1001 5021 15/06/2026 22/03/2025 450
1002 5002 28/01/2025 (nulo) (nulo)
1002 5009 19/05/2025 28/01/2025 111
1002 5018 03/03/2026 19/05/2025 288
6 filas seleccionadas.
LAG mira la fila anterior de la partición (LEAD, la siguiente). El primer pedido de cada cliente no tiene anterior. Sin analíticas habría que usar una subconsulta correlacionada con MAX(fecha_pedido) ... WHERE fecha_pedido < actual.
Ejercicio 63: Peso de cada salario en su departamento ★★
Para Informática, el porcentaje que supone cada salario sobre el total del departamento.
Ver solución
SELECT dept_id, nombre, salario,
SUM(salario) OVER (PARTITION BY dept_id) AS total_dept,
ROUND(100 * salario / SUM(salario) OVER (PARTITION BY dept_id), 1) AS pct,
ROUND(100 * RATIO_TO_REPORT(salario) OVER (PARTITION BY dept_id), 1) AS pct2
FROM empleados
WHERE dept_id = 30
ORDER BY salario DESC;
DEPT_ID NOMBRE SALARIO TOTAL_DEPT PCT PCT2
---------- ------------ ---------- ---------- ---------- ----------
30 Marta 5000 18800 26,6 26,6
30 Pablo 3500 18800 18,6 18,6
30 Raquel 2800 18800 14,9 14,9
30 Iván 2600 18800 13,8 13,8
30 Hugo 2500 18800 13,3 13,3
30 Sara 2400 18800 12,8 12,8
6 filas seleccionadas.
SUM(...) OVER (PARTITION BY ...) sin ORDER BY da el total de la partición en cada fila, sin colapsar las filas como haría GROUP BY. RATIO_TO_REPORT es una función de Oracle que hace directamente el cociente.
Ejercicio 64: Organigrama con CONNECT BY ★★
Organigrama completo de la empresa con sangría por nivel, ordenando a los hermanos por nombre.
Ver solución
SELECT LPAD(' ', (LEVEL - 1) * 3) || nombre || ' (' || oficio || ')' AS organigrama, LEVEL
FROM empleados
START WITH jefe_id IS NULL
CONNECT BY PRIOR emp_id = jefe_id
ORDER SIBLINGS BY nombre;
ORGANIGRAMA LEVEL
---------------------------------- ----------
Ana (DIRECTOR GENERAL) 1
Javier (DIRECTOR) 2
Cristina (ADMINISTRATIVO) 3
Manuel (OPERARIO) 3
Rosa (OPERARIO) 3
Laura (DIRECTOR) 2
Álvaro (ANALISTA) 3
Nuria (DISEÑADOR) 3
...
24 filas seleccionadas.
PRIOR emp_id = jefe_id significa “la fila hija es la que tiene como jefe_id el emp_id de la fila anterior” (de arriba abajo). ORDER SIBLINGS BY ordena sin romper la jerarquía; un ORDER BY nombre normal la destrozaría.
Ejercicio 65: Rutas, hojas y raíces de categorías ★★★
Con CONNECT BY, muestra para cada categoría su ruta, si es hoja y cuál es su categoría raíz.
Ver solución
SELECT categoria_id,
SYS_CONNECT_BY_PATH(nombre, '/') AS ruta,
CONNECT_BY_ISLEAF AS hoja,
CONNECT_BY_ROOT nombre AS raiz
FROM categorias
START WITH categoria_padre_id IS NULL
CONNECT BY PRIOR categoria_id = categoria_padre_id
ORDER SIBLINGS BY nombre;
CATEGORIA_ID RUTA HOJA RAIZ
------------ --------------------------------------------- ---------- ----------------------------------------
7 /Electrónica 0 Electrónica
9 /Electrónica/Audio 1 Electrónica
8 /Electrónica/Telefonía 1 Electrónica
1 /Informática 0 Informática
3 /Informática/Ordenadores sobremesa 1 Informática
4 /Informática/Periféricos 0 Informática
6 /Informática/Periféricos/Monitores 1 Informática
5 /Informática/Periféricos/Ratones y teclados 1 Informática
2 /Informática/Portátiles 1 Informática
10 /Oficina 0 Oficina
11 /Oficina/Mobiliario 1 Oficina
11 filas seleccionadas.
Compara con el ejercicio 58: el resultado es el mismo, pero CONNECT BY ofrece pseudocolumnas propias (LEVEL, CONNECT_BY_ISLEAF, CONNECT_BY_ROOT) que con la CTE estándar hay que calcular a mano.
Ejercicio 66: Tabla dinámica de estados (PIVOT) ★★★
Para cada cliente con pedidos, una columna con el número de pedidos de cada estado.
Ver solución
SELECT *
FROM (SELECT c.nombre AS cliente, p.estado
FROM pedidos p JOIN clientes c ON c.cliente_id = p.cliente_id)
PIVOT (COUNT(*) FOR estado IN ('PENDIENTE' AS pend, 'ENVIADO' AS env,
'ENTREGADO' AS ent, 'CANCELADO' AS canc))
ORDER BY cliente;
CLIENTE PEND ENV ENT CANC
---------------------------- ---------- ---------- ---------- ----------
Academia Nova 0 0 2 0
Antonio Ruiz Pérez 0 0 1 0
Clínica Dental Sonrisa 0 0 2 0
Construcciones Levante S.A. 0 0 3 0
Diseños Creativos S.L. 1 0 1 0
Gestoría Delta 1 0 0 1
Hotel Mar Azul S.L. 0 1 1 0
Instituto Tecnológico Sur 0 0 2 0
Laura Benítez Sanz 1 0 1 0
María José Herrera 0 0 2 0
Panadería La Espiga 0 0 1 0
Talleres Martín S.L. 0 1 2 0
12 filas seleccionadas.
PIVOT agrupa implícitamente por las columnas de la subconsulta que no se usan en el pivote (aquí cliente). Por eso la subconsulta debe contener solo las columnas necesarias. Equivalente clásico: SUM(CASE WHEN estado = 'PENDIENTE' THEN 1 ELSE 0 END) AS pend, ... con GROUP BY c.nombre.
9. Bloque H: manipulación de datos (DML)
Todos los ejercicios de este bloque terminan con ROLLBACK o eliminan sus tablas auxiliares.
Ejercicio 67: Alta de un producto ★
Da de alta el producto 21, “Hub USB-C 7 puertos” (categoría 4, 39,90 €, 50 unidades, alta el 1/10/2026), sin indicar si está descatalogado.
Ver solución
INSERT INTO productos (producto_id, nombre, descripcion, categoria_id, precio, stock, fecha_alta)
VALUES (21, 'Hub USB-C 7 puertos', 'Concentrador USB-C con 7 puertos', 4, 39.90, 50, DATE '2026-10-01');
SELECT producto_id, nombre, categoria_id, precio, stock, descatalogado
FROM productos WHERE producto_id = 21;
ROLLBACK;
1 fila creada.
PRODUCTO_ID NOMBRE CATEGORIA_ID PRECIO STOCK D
----------- -------------------------- ------------ ---------- ---------- -
21 Hub USB-C 7 puertos 4 39,9 50 N
1 fila seleccionada.
Rollback terminado.
descatalogado toma su valor por defecto ('N'). En el VALUES el decimal se escribe con punto (39.90), aunque la sesión lo muestre con coma.
Ejercicio 68: Premio a la antigüedad ★★
Sube un 5 % el salario a los empleados con más de 10 años de antigüedad a 1 de octubre de 2026.
Ver solución
SELECT emp_id, nombre, fecha_alta, salario
FROM empleados
WHERE fecha_alta < ADD_MONTHS(DATE '2026-10-01', -120)
ORDER BY fecha_alta;
EMP_ID NOMBRE FECHA_ALTA SALARIO
---------- -------------------------- ---------- ----------
100 Ana 15/01/2010 6500
102 Marta 20/06/2011 5000
101 Luis 01/03/2012 4800
103 Pedro 10/09/2013 4200
120 Pablo 05/05/2014 3500
105 Javier 11/11/2014 4000
104 Laura 02/02/2015 4300
110 Carmen 12/04/2016 2200
130 Isabel 08/08/2016 2600
9 filas seleccionadas.
UPDATE empleados
SET salario = ROUND(salario * 1.05, 2)
WHERE fecha_alta < ADD_MONTHS(DATE '2026-10-01', -120);
ROLLBACK;
9 filas actualizadas.
Rollback terminado.
El SELECT previo con el mismo WHERE confirma que se van a tocar 9 filas. ADD_MONTHS(fecha, -120) es exactamente diez años antes, también en años bisiestos.
Ejercicio 69: Cancelar pedidos antiguos ★★
Cancela los pedidos pendientes anteriores al 5 de julio de 2026.
Ver solución
UPDATE pedidos
SET estado = 'CANCELADO'
WHERE estado = 'PENDIENTE'
AND fecha_pedido < DATE '2026-07-05';
SELECT pedido_id, fecha_pedido, estado FROM pedidos WHERE pedido_id >= 5022;
ROLLBACK;
1 fila actualizada.
PEDIDO_ID FECHA_PEDI ESTADO
---------- ---------- ------------
5022 01/07/2026 CANCELADO
5023 08/07/2026 PENDIENTE
5024 10/07/2026 PENDIENTE
3 filas seleccionadas.
Rollback terminado.
Ejercicio 70: Limpiar asignaciones de proyectos cerrados ★★
Borra las asignaciones de los proyectos que terminaron antes del 1 de octubre de 2026.
Ver solución
SELECT a.emp_id, a.proyecto_id
FROM asignaciones a JOIN proyectos p ON p.proyecto_id = a.proyecto_id
WHERE p.fecha_fin IS NOT NULL AND p.fecha_fin < DATE '2026-10-01';
EMP_ID PROYECTO_ID
---------- -----------
104 3
110 3
120 1
121 1
124 1
140 3
141 3
7 filas seleccionadas.
DELETE FROM asignaciones
WHERE proyecto_id IN (SELECT proyecto_id
FROM proyectos
WHERE fecha_fin < DATE '2026-10-01');
ROLLBACK;
7 filas suprimidas.
Rollback terminado.
Los proyectos con fecha_fin nula (en curso) no cumplen fecha_fin < ... y sus asignaciones se conservan. En 23ai: DELETE FROM asignaciones a FROM proyectos p WHERE p.proyecto_id = a.proyecto_id AND p.fecha_fin < DATE '2026-10-01';.
Ejercicio 71: Bonus con MERGE ★★★
La tabla bonus_vendedores tiene ya bonus de la campaña de verano para los empleados 110 y 113. Súmales, y crea para quien no lo tenga, un bonus del 1 % de sus ventas entregadas en 2026.
Ver solución
CREATE TABLE bonus_vendedores (
emp_id NUMBER(6) CONSTRAINT pk_bonus_vendedores PRIMARY KEY,
importe NUMBER(8,2) NOT NULL,
motivo VARCHAR2(40)
);
INSERT INTO bonus_vendedores VALUES (110, 100, 'Campaña verano');
INSERT INTO bonus_vendedores VALUES (113, 50, 'Campaña verano');
COMMIT;
MERGE INTO bonus_vendedores b
USING (SELECT p.empleado_id AS emp_id,
ROUND(0.01 * SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)), 2) AS bonus
FROM pedidos p JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.estado = 'ENTREGADO' AND p.fecha_pedido >= DATE '2026-01-01'
AND p.empleado_id IS NOT NULL
GROUP BY p.empleado_id) v
ON (b.emp_id = v.emp_id)
WHEN MATCHED THEN
UPDATE SET b.importe = b.importe + v.bonus, b.motivo = 'Verano + ventas 2026'
WHEN NOT MATCHED THEN
INSERT (emp_id, importe, motivo) VALUES (v.emp_id, v.bonus, 'Ventas 2026');
SELECT * FROM bonus_vendedores ORDER BY emp_id;
ROLLBACK;
DROP TABLE bonus_vendedores PURGE;
Tabla creada.
1 fila creada.
1 fila creada.
Confirmación terminada.
3 filas fusionadas.
EMP_ID IMPORTE MOTIVO
---------- ---------- --------------------
110 120,85 Verano + ventas 2026
112 102,41 Ventas 2026
113 58,38 Verano + ventas 2026
3 filas seleccionadas.
Rollback terminado.
Tabla borrada.
David (111) no tiene ventas entregadas en 2026 (su pedido 5020 está ENVIADO) y no aparece. El origen del MERGE debe tener una fila por clave (GROUP BY p.empleado_id) para evitar ORA-30926.
Ejercicio 72: Archivo de pedidos de 2025 ★★
Crea una tabla pedidos_2025 con la estructura de PEDIDOS y cópiale los pedidos de 2025. Muestra cuántos hay por estado.
Ver solución
CREATE TABLE pedidos_2025 AS SELECT * FROM pedidos WHERE 1 = 0;
INSERT INTO pedidos_2025
SELECT * FROM pedidos
WHERE fecha_pedido >= DATE '2025-01-01' AND fecha_pedido < DATE '2026-01-01';
SELECT estado, COUNT(*) FROM pedidos_2025 GROUP BY estado ORDER BY estado;
ROLLBACK;
DROP TABLE pedidos_2025 PURGE;
Tabla creada.
15 filas creadas.
ESTADO COUNT(*)
------------ ----------
CANCELADO 1
ENTREGADO 14
2 filas seleccionadas.
Rollback terminado.
Tabla borrada.
La tabla creada con CREATE TABLE ... AS SELECT conserva tipos y NOT NULL, pero no la clave primaria, las claves ajenas ni los CHECK. Para un archivo definitivo, añádelas con ALTER TABLE.
Ejercicio 73: Límite de crédito según el mayor pedido ★★
Fija el límite de crédito de cada cliente con pedidos al doble de su pedido más caro. Los clientes sin pedidos conservan su límite.
Ver solución
UPDATE clientes c
SET c.limite_credito = (SELECT 2 * MAX(t.importe)
FROM (SELECT p.cliente_id,
SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)) AS importe
FROM pedidos p JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
GROUP BY p.cliente_id, p.pedido_id) t
WHERE t.cliente_id = c.cliente_id)
WHERE EXISTS (SELECT 1 FROM pedidos p WHERE p.cliente_id = c.cliente_id);
SELECT cliente_id, nombre, limite_credito FROM clientes WHERE cliente_id IN (1001, 1003, 1010, 1013) ORDER BY 1;
ROLLBACK;
12 filas actualizadas.
CLIENTE_ID NOMBRE LIMITE_CREDITO
---------- -------------------------- --------------
1001 Talleres Martín S.L. 4924,1
1003 María José Herrera 1117,8
1010 Instituto Tecnológico Sur 23315
1013 Carlos Ibáñez Rey 1500
4 filas seleccionadas.
Rollback terminado.
La subconsulta interior calcula el importe por pedido y la exterior se queda con el máximo del cliente. Sin el WHERE EXISTS, los clientes 1013 y 1014 quedarían con límite NULL.
Ejercicio 74: Eliminar filas duplicadas ★★★
En la tabla contactos_dup hay contactos repetidos. Elimina los duplicados conservando una fila de cada email.
Ver solución
CREATE TABLE contactos_dup (nombre VARCHAR2(30), email VARCHAR2(40));
INSERT INTO contactos_dup VALUES ('Ana', 'ana@correo.es');
INSERT INTO contactos_dup VALUES ('Ana', 'ana@correo.es');
INSERT INTO contactos_dup VALUES ('Luis', 'luis@correo.es');
INSERT INTO contactos_dup VALUES ('Ana', 'ana@correo.es');
INSERT INTO contactos_dup VALUES ('Luis', 'luis@correo.es');
INSERT INTO contactos_dup VALUES ('Eva', 'eva@correo.es');
COMMIT;
SELECT email, COUNT(*) FROM contactos_dup GROUP BY email HAVING COUNT(*) > 1;
EMAIL COUNT(*)
---------------------------------------- ----------
ana@correo.es 3
luis@correo.es 2
2 filas seleccionadas.
DELETE FROM contactos_dup c
WHERE c.ROWID > (SELECT MIN(x.ROWID) FROM contactos_dup x WHERE x.email = c.email);
SELECT * FROM contactos_dup ORDER BY nombre;
COMMIT;
DROP TABLE contactos_dup PURGE;
3 filas suprimidas.
NOMBRE EMAIL
-------------------------- ----------------------------------------
Ana ana@correo.es
Eva eva@correo.es
Luis luis@correo.es
3 filas seleccionadas.
Confirmación terminada.
Tabla borrada.
Las filas duplicadas son idénticas, así que se distinguen por su ROWID (la dirección física de cada fila): se conserva la de menor ROWID de cada email. Después conviene crear una restricción UNIQUE (email) para que no vuelva a ocurrir.
10. Bloque I: definición de datos (DDL)
Los ejercicios de este bloque crean un pequeño subsistema de proveedores encadenado: cada ejercicio parte del anterior. Al final se borra todo.
Ejercicio 75: Tabla de proveedores ★
Crea la tabla PROVEEDORES con: identificador numérico (PK), nombre y CIF obligatorios, CIF único, email opcional que debe contener una @ y un punto, y país obligatorio con valor por defecto “España”. Nombra todas las restricciones. Prueba a insertar tres proveedores, uno de ellos con un email incorrecto.
Ver solución
CREATE TABLE proveedores (
proveedor_id NUMBER(5) CONSTRAINT pk_proveedores PRIMARY KEY,
nombre VARCHAR2(60) CONSTRAINT nn_prv_nombre NOT NULL,
cif VARCHAR2(9) CONSTRAINT nn_prv_cif NOT NULL,
email VARCHAR2(60),
pais VARCHAR2(30) DEFAULT 'España' CONSTRAINT nn_prv_pais NOT NULL,
CONSTRAINT uq_prv_cif UNIQUE (cif),
CONSTRAINT ck_prv_email CHECK (email LIKE '%@%.%')
);
INSERT INTO proveedores (proveedor_id, nombre, cif, email) VALUES (1, 'Distribuciones TecnoSur', 'B41111111', 'ventas@tecnosur.es');
INSERT INTO proveedores (proveedor_id, nombre, cif, email, pais) VALUES (2, 'EuroComponents GmbH', 'DE1234567', 'info@eurocomp.de', 'Alemania');
INSERT INTO proveedores (proveedor_id, nombre, cif, email) VALUES (3, 'Mal Email S.L.', 'B42222222', 'sin-arroba');
SELECT * FROM proveedores;
Tabla creada.
1 fila creada.
1 fila creada.
INSERT INTO proveedores (proveedor_id, nombre, cif, email) VALUES (3, 'Mal Email S.L.', 'B42222222', 'sin-arroba')
*
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_PRV_EMAIL) violated
PROVEEDOR_ID NOMBRE CIF EMAIL PAIS
------------ ------------------------ --------- ------------------------------------------------------------ ------------------------------
1 Distribuciones TecnoSur B41111111 ventas@tecnosur.es España
2 EuroComponents GmbH DE1234567 info@eurocomp.de Alemania
2 filas seleccionadas.
Un CHECK con email nulo da desconocido y se acepta (las restricciones CHECK solo rechazan lo falso). Para validaciones más finas se usaría REGEXP_LIKE(email, '^[^@]+@[^@]+\.[a-z]{2,}$').
Ejercicio 76: Relación N:M proveedores-productos ★★
Crea SUMINISTROS (qué proveedor suministra qué producto, a qué precio de compra y con qué plazo en días, entre 1 y 90, por defecto 7). Si se borra un proveedor, deben desaparecer sus suministros; un producto con suministros no se puede borrar. Inserta datos válidos y prueba un producto inexistente y una clave repetida. Muestra el margen de cada producto.
Ver solución
CREATE TABLE suministros (
proveedor_id NUMBER(5),
producto_id NUMBER(6),
precio_compra NUMBER(8,2) CONSTRAINT nn_sum_precio NOT NULL,
plazo_dias NUMBER(3) DEFAULT 7,
CONSTRAINT pk_suministros PRIMARY KEY (proveedor_id, producto_id),
CONSTRAINT fk_sum_proveedor FOREIGN KEY (proveedor_id) REFERENCES proveedores (proveedor_id) ON DELETE CASCADE,
CONSTRAINT fk_sum_producto FOREIGN KEY (producto_id) REFERENCES productos (producto_id),
CONSTRAINT ck_sum_precio CHECK (precio_compra > 0),
CONSTRAINT ck_sum_plazo CHECK (plazo_dias BETWEEN 1 AND 90)
);
INSERT INTO suministros VALUES (1, 6, 9.50, DEFAULT);
INSERT INTO suministros VALUES (1, 7, 41.00, 3);
INSERT INTO suministros VALUES (2, 5, 1350.00, 20);
INSERT INTO suministros VALUES (2, 99, 10.00, 5);
INSERT INTO suministros VALUES (1, 6, 9.00, 5);
Tabla creada.
1 fila creada.
1 fila creada.
1 fila creada.
INSERT INTO suministros VALUES (2, 99, 10.00, 5)
*
ERROR en línea 1:
ORA-02291: integrity constraint (CURSO.FK_SUM_PRODUCTO) violated - parent key not found
INSERT INTO suministros VALUES (1, 6, 9.00, 5)
*
ERROR en línea 1:
ORA-00001: unique constraint (CURSO.PK_SUMINISTROS) violated on table CURSO.SUMINISTROS columns (PROVEEDOR_ID, PRODUCTO_ID)
ORA-03301: (ORA-00001 details) row with column values (PROVEEDOR_ID:1, PRODUCTO_ID:6) already exists
SELECT s.proveedor_id, pr.nombre, s.precio_compra, s.plazo_dias,
p.precio - s.precio_compra AS margen
FROM suministros s JOIN productos p ON p.producto_id = s.producto_id
JOIN proveedores pr ON pr.proveedor_id = s.proveedor_id
ORDER BY s.proveedor_id, s.producto_id;
COMMIT;
PROVEEDOR_ID NOMBRE PRECIO_COMPRA PLAZO_DIAS MARGEN
------------ ------------------------ ------------- ---------- ----------
1 Distribuciones TecnoSur 9,5 7 10,4
1 Distribuciones TecnoSur 41 3 38,9
2 EuroComponents GmbH 1350 20 549
3 filas seleccionadas.
Confirmación terminada.
Es la transformación de una relación N:M con atributos (módulo 17): clave primaria compuesta por las dos claves ajenas. ON DELETE CASCADE hacia proveedores y la regla por defecto (NO ACTION) hacia productos implementan exactamente lo pedido.
Ejercicio 77: Evolución de la tabla ★★
Sobre PROVEEDORES: añade telefono y activo (CHAR(1), por defecto 'S', obligatorio, solo 'S' o 'N'), amplía nombre a 80 caracteres, renombra pais a pais_origen y elimina telefono. Comprueba la estructura en el diccionario.
Ver solución
ALTER TABLE proveedores ADD (telefono VARCHAR2(15), activo CHAR(1) DEFAULT 'S' NOT NULL);
ALTER TABLE proveedores MODIFY (nombre VARCHAR2(80));
ALTER TABLE proveedores RENAME COLUMN pais TO pais_origen;
ALTER TABLE proveedores ADD CONSTRAINT ck_prv_activo CHECK (activo IN ('S', 'N'));
ALTER TABLE proveedores DROP COLUMN telefono;
SELECT column_name, data_type, data_length, nullable, data_default
FROM user_tab_columns
WHERE table_name = 'PROVEEDORES'
ORDER BY column_id;
Tabla modificada.
Tabla modificada.
Tabla modificada.
Tabla modificada.
Tabla modificada.
COLUMN_NAME DATA_TYPE DATA_LENGTH N DATA_DEFAULT
---------------- ---------- ----------- - --------------------------------------------------------------------------------
PROVEEDOR_ID NUMBER 22 N (nulo)
NOMBRE VARCHAR2 80 N (nulo)
CIF VARCHAR2 9 N (nulo)
EMAIL VARCHAR2 60 Y (nulo)
PAIS_ORIGEN VARCHAR2 30 N 'España'
ACTIVO CHAR 1 N 'S'
6 filas seleccionadas.
Añadir una columna NOT NULL a una tabla con filas solo es posible si lleva DEFAULT (Oracle rellena las existentes). Reducir el tamaño de una columna fallaría si algún valor no cupiera.
Ejercicio 78: Restricción entre dos columnas ★★
Añade a SUMINISTROS un periodo de vigencia (fecha_desde, obligatoria con valor por defecto 1/1/2026, y fecha_hasta, opcional) con una restricción que impida que la fecha final sea anterior o igual a la inicial. Pruébala.
Ver solución
ALTER TABLE suministros ADD (fecha_desde DATE DEFAULT DATE '2026-01-01' NOT NULL, fecha_hasta DATE);
ALTER TABLE suministros ADD CONSTRAINT ck_sum_fechas CHECK (fecha_hasta IS NULL OR fecha_hasta > fecha_desde);
UPDATE suministros SET fecha_hasta = DATE '2025-12-31' WHERE proveedor_id = 1 AND producto_id = 6;
UPDATE suministros SET fecha_hasta = DATE '2026-12-31' WHERE proveedor_id = 1 AND producto_id = 6;
COMMIT;
Tabla modificada.
Tabla modificada.
UPDATE suministros SET fecha_hasta = DATE '2025-12-31' WHERE proveedor_id = 1 AND producto_id = 6
*
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_SUM_FECHAS) violated
1 fila actualizada.
Confirmación terminada.
Una restricción CHECK de tabla puede comparar columnas de la misma fila. El fecha_hasta IS NULL OR no es estrictamente necesario (con nulo la comparación es desconocida y se aceptaría igual), pero documenta la intención.
Ejercicio 79: Vista de ventas por categoría ★★
Crea la vista v_ventas_categoria con pedidos, unidades e importe por categoría. Consúltala y prueba a actualizarla.
Ver solución
CREATE OR REPLACE VIEW v_ventas_categoria AS
SELECT c.categoria_id, c.nombre AS categoria,
COUNT(DISTINCT l.pedido_id) AS pedidos,
SUM(l.cantidad) AS unidades,
ROUND(SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)), 2) AS importe
FROM categorias c
JOIN productos p ON p.categoria_id = c.categoria_id
JOIN lineas_pedido l ON l.producto_id = p.producto_id
GROUP BY c.categoria_id, c.nombre;
SELECT * FROM v_ventas_categoria ORDER BY importe DESC;
UPDATE v_ventas_categoria SET unidades = 0 WHERE categoria_id = 2;
Vista creada.
CATEGORIA_ID CATEGORIA PEDIDOS UNIDADES IMPORTE
------------ ---------------------- ---------- ---------- ----------
2 Portátiles 8 42 27337,25
3 Ordenadores sobremesa 4 14 11636,29
6 Monitores 8 53 9063,45
8 Telefonía 4 13 5033,73
11 Mobiliario 4 12 3738
5 Ratones y teclados 8 52 2202,84
1 Informática 2 14 1213,65
7 Electrónica 2 3 837
4 Periféricos 4 11 783
9 Audio 4 10 570,25
10 filas seleccionadas.
UPDATE v_ventas_categoria SET unidades = 0 WHERE categoria_id = 2
*
ERROR en línea 1:
ORA-01732: data manipulation operation not legal on this view
Una vista con GROUP BY, agregados o DISTINCT no es actualizable: cada fila de la vista resume muchas filas base y Oracle no sabe a cuáles trasladar el cambio. (Las ventas incluyen aquí el pedido cancelado; añade el filtro de estado si el informe lo excluye.)
Ejercicio 80: Vista con WITH CHECK OPTION ★★
Crea una vista de proveedores españoles que impida, a través de ella, dar de alta proveedores de otros países.
Ver solución
CREATE OR REPLACE VIEW v_proveedores_es AS
SELECT proveedor_id, nombre, cif, email, pais_origen
FROM proveedores
WHERE pais_origen = 'España'
WITH CHECK OPTION CONSTRAINT ck_v_proveedores_es;
INSERT INTO v_proveedores_es VALUES (4, 'Componentes Ibéricos', 'B43333333', 'hola@cibericos.es', 'España');
INSERT INTO v_proveedores_es VALUES (5, 'Lusotech', 'PT5555555', 'geral@lusotech.pt', 'Portugal');
SELECT proveedor_id, nombre, pais_origen FROM v_proveedores_es;
SELECT COUNT(*) FROM proveedores;
ROLLBACK;
Vista creada.
1 fila creada.
INSERT INTO v_proveedores_es VALUES (5, 'Lusotech', 'PT5555555', 'geral@lusotech.pt', 'Portugal')
*
ERROR en línea 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
PROVEEDOR_ID NOMBRE PAIS_ORIGEN
------------ ------------------------ ------------------------------
1 Distribuciones TecnoSur España
4 Componentes Ibéricos España
2 filas seleccionadas.
COUNT(*)
----------
3
1 fila seleccionada.
Rollback terminado.
La columna activo, que no está en la vista, toma su valor por defecto en la inserción. La tabla tiene 3 filas (los 2 españoles y el alemán, que no se ve por la vista).
Ejercicio 81: Secuencia e índices ★★
Crea una secuencia que genere los identificadores de proveedor de 10 en 10 a partir de 10 y úsala en dos altas. Crea un índice para las búsquedas de suministros por producto y otro para buscar proveedores por nombre sin distinguir mayúsculas. Lista los índices de ambas tablas.
Ver solución
CREATE SEQUENCE seq_proveedores START WITH 10 INCREMENT BY 10 NOCACHE;
INSERT INTO proveedores (proveedor_id, nombre, cif) VALUES (seq_proveedores.NEXTVAL, 'Ofimática Norte', 'B44444444');
INSERT INTO proveedores (proveedor_id, nombre, cif) VALUES (seq_proveedores.NEXTVAL, 'Muebles Oficina Plus', 'B45555555');
SELECT proveedor_id, nombre FROM proveedores ORDER BY proveedor_id;
CREATE INDEX ix_sum_producto ON suministros (producto_id);
CREATE INDEX ix_prv_nombre_mayus ON proveedores (UPPER(nombre));
SELECT index_name, table_name, uniqueness
FROM user_indexes
WHERE table_name IN ('PROVEEDORES', 'SUMINISTROS')
ORDER BY table_name, index_name;
COMMIT;
Secuencia creada.
1 fila creada.
1 fila creada.
PROVEEDOR_ID NOMBRE
------------ ------------------------
1 Distribuciones TecnoSur
2 EuroComponents GmbH
10 Ofimática Norte
20 Muebles Oficina Plus
4 filas seleccionadas.
Índice creado.
Índice creado.
INDEX_NAME TABLE_NAME UNIQUENES
---------------------- ------------ ---------
IX_PRV_NOMBRE_MAYUS PROVEEDORES NONUNIQUE
PK_PROVEEDORES PROVEEDORES UNIQUE
UQ_PRV_CIF PROVEEDORES UNIQUE
IX_SUM_PRODUCTO SUMINISTROS NONUNIQUE
PK_SUMINISTROS SUMINISTROS UNIQUE
5 filas seleccionadas.
Confirmación terminada.
Las restricciones PRIMARY KEY y UNIQUE crean su índice automáticamente. El índice sobre producto_id en SUMINISTROS es importante: la PK (proveedor_id, producto_id) no sirve para buscar solo por producto, y una clave ajena sin índice provoca bloqueos de tabla al borrar o modificar productos (módulo 35). ix_prv_nombre_mayus es un índice basado en función: lo usan las consultas con WHERE UPPER(nombre) = ....
Ejercicio 82: Restricciones desde el diccionario ★★★
Lista todas las restricciones de SUMINISTROS con su tipo, sus columnas (en una sola línea), la restricción referenciada y la regla de borrado. Explica la restricción con nombre SYS_C.... Después elimina todos los objetos de este bloque.
Ver solución
SELECT c.constraint_name, c.constraint_type,
LISTAGG(cc.column_name, ', ') WITHIN GROUP (ORDER BY cc.position) AS columnas,
c.r_constraint_name, c.delete_rule
FROM user_constraints c
JOIN user_cons_columns cc ON cc.constraint_name = c.constraint_name
WHERE c.table_name = 'SUMINISTROS'
GROUP BY c.constraint_name, c.constraint_type, c.r_constraint_name, c.delete_rule
ORDER BY c.constraint_type, c.constraint_name;
CONSTRAINT_NAME C COLUMNAS R_CONSTRAINT_NAM DELETE_RU
---------------------- - ---------------------------- ---------------- ---------
CK_SUM_FECHAS C FECHA_DESDE, FECHA_HASTA (nulo) (nulo)
CK_SUM_PLAZO C PLAZO_DIAS (nulo) (nulo)
CK_SUM_PRECIO C PRECIO_COMPRA (nulo) (nulo)
NN_SUM_PRECIO C PRECIO_COMPRA (nulo) (nulo)
SYS_C0011011 C FECHA_DESDE (nulo) (nulo)
PK_SUMINISTROS P PROVEEDOR_ID, PRODUCTO_ID (nulo) (nulo)
FK_SUM_PRODUCTO R PRODUCTO_ID PK_PRODUCTOS NO ACTION
FK_SUM_PROVEEDOR R PROVEEDOR_ID PK_PROVEEDORES CASCADE
8 filas seleccionadas.
- Los
NOT NULLson restricciones de tipoC(check).SYS_C0011011es elNOT NULLdefecha_desde, que añadimos en el ejercicio 78 sin darle nombre: Oracle le asigna uno de sistema (el número cambia en cada base de datos). Por eso conviene nombrar siempre las restricciones. Pes la clave primaria yR(referential) las claves ajenas, con su regla de borrado.
DROP VIEW v_proveedores_es;
DROP VIEW v_ventas_categoria;
DROP SEQUENCE seq_proveedores;
DROP TABLE suministros PURGE;
DROP TABLE proveedores PURGE;
Vista borrada.
Vista borrada.
Secuencia borrada.
Tabla borrada.
Tabla borrada.
SUMINISTROS se borra antes que PROVEEDORES porque depende de ella (si no, haría falta CASCADE CONSTRAINTS).
11. Examen tipo 1: ciclo formativo (DAM, DAW, ASIR)
💡 Consejo: Simula condiciones reales: 2 horas, sin apuntes, con acceso a la base de datos. Escribe todas tus respuestas en un fichero
.sqly ejecútalo de una vez al final.
Enunciado. Sobre el esquema EMPRESA, resuelve las siguientes cuestiones (10 puntos):
| N.º | Cuestión | Puntos |
|---|---|---|
| 1 | Empleados dados de alta desde el 1 de enero de 2019, ordenados por fecha de alta | 1 |
| 2 | Para cada categoría con al menos dos productos: nombre de la categoría, número de productos y precio medio (2 decimales) | 1 |
| 3 | Todos los clientes con su número de pedidos, incluidos los que no tienen ninguno | 1,5 |
| 4 | Para los empleados de Informática: su salario, el nombre y salario de su jefe y la diferencia entre ambos | 1,5 |
| 5 | Producto o productos más vendidos en unidades | 1 |
| 6 | En una transacción: crea el departamento 80 “FORMACIÓN” (Valencia, 45 000 €), traslada a él al becario con Marta como jefa y súbele 100 €. Comprueba y deshaz | 1,5 |
| 7 | Crea la tabla VALORACIONES (cliente, producto, puntuación de 1 a 5 obligatoria, fecha por defecto la actual, comentario opcional) con clave primaria compuesta, claves ajenas y restricciones con nombre. Prueba una inserción válida y dos que incumplan restricciones distintas | 1,5 |
| 8 | Crea una vista con los pedidos pendientes, su cliente, su fecha y su importe, y consúltala | 1 |
Solución del examen 1
Ver solución
Cuestión 1.
SELECT emp_id, nombre, apellidos, fecha_alta
FROM empleados
WHERE fecha_alta >= DATE '2019-01-01'
ORDER BY fecha_alta;
EMP_ID NOMBRE APELLIDOS FECHA_ALTA
---------- ------------------------ -------------------------------------------------- ----------
112 Elena Navarro Castro 15/01/2019
152 Cristina Medina Blanco 22/07/2019
124 Hugo Rubio Marín 20/11/2019
114 Lucía Romero Serrano 10/02/2020
122 Iván Castro Méndez 16/03/2020
141 Álvaro Peña Cano 12/04/2021
113 Sergio Jiménez Vidal 01/09/2021
123 Sara Ortiz Delgado 01/06/2022
151 Manuel Herrero Lozano 17/10/2022
131 Jorge Vázquez Ramos 09/01/2023
160 Tomás Reyes Fuentes 01/09/2025
11 filas seleccionadas.
Cuestión 2.
SELECT c.nombre AS categoria, COUNT(*) AS productos, ROUND(AVG(p.precio), 2) AS precio_medio
FROM productos p JOIN categorias c ON c.categoria_id = p.categoria_id
GROUP BY c.nombre
HAVING COUNT(*) >= 2
ORDER BY productos DESC, categoria;
CATEGORIA PRODUCTOS PRECIO_MEDIO
---------------------- ---------- ------------
Portátiles 3 965,67
Ratones y teclados 3 44,77
Audio 2 54,45
Mobiliario 2 324
Monitores 2 234
Ordenadores sobremesa 2 1274
Periféricos 2 117
Telefonía 2 649
8 filas seleccionadas.
Cuestión 3.
SELECT c.nombre AS cliente, COUNT(p.pedido_id) AS pedidos
FROM clientes c LEFT JOIN pedidos p ON p.cliente_id = c.cliente_id
GROUP BY c.cliente_id, c.nombre
ORDER BY pedidos DESC, cliente;
CLIENTE PEDIDOS
---------------------------- ----------
Construcciones Levante S.A. 3
Talleres Martín S.L. 3
Academia Nova 2
Clínica Dental Sonrisa 2
Diseños Creativos S.L. 2
Gestoría Delta 2
Hotel Mar Azul S.L. 2
Instituto Tecnológico Sur 2
...
14 filas seleccionadas.
Los dos últimos son Carlos Ibáñez Rey y Farmacia Central, con 0. Con COUNT(*) en vez de COUNT(p.pedido_id) aparecerían con 1 (cuenta la fila del join externo): es el error que más penaliza esta pregunta.
Cuestión 4.
SELECT e.nombre AS empleado, e.salario, j.nombre AS jefe, j.salario AS salario_jefe,
j.salario - e.salario AS diferencia
FROM empleados e JOIN empleados j ON j.emp_id = e.jefe_id
WHERE e.dept_id = 30
ORDER BY diferencia DESC;
EMPLEADO SALARIO JEFE SALARIO_JEFE DIFERENCIA
---------- ---------- ---------- ------------ ----------
Hugo 2500 Marta 5000 2500
Pablo 3500 Marta 5000 1500
Marta 5000 Ana 6500 1500
Sara 2400 Pablo 3500 1100
Iván 2600 Pablo 3500 900
Raquel 2800 Pablo 3500 700
6 filas seleccionadas.
Cuestión 5.
SELECT p.nombre, SUM(l.cantidad) AS unidades
FROM productos p JOIN lineas_pedido l ON l.producto_id = p.producto_id
GROUP BY p.nombre
ORDER BY unidades DESC
FETCH FIRST 1 ROW WITH TIES;
NOMBRE UNIDADES
------------------------ ----------
Monitor 24 FHD 40
1 fila seleccionada.
Cuestión 6.
INSERT INTO departamentos (dept_id, nombre, localidad, presupuesto)
VALUES (80, 'FORMACIÓN', 'Valencia', 45000);
UPDATE empleados SET dept_id = 80, jefe_id = 102 WHERE emp_id = 160;
UPDATE empleados SET salario = salario + 100 WHERE dept_id = 80;
SELECT e.emp_id, e.nombre, e.salario, d.nombre AS dpto
FROM empleados e JOIN departamentos d ON d.dept_id = e.dept_id
WHERE d.dept_id = 80;
ROLLBACK;
1 fila creada.
1 fila actualizada.
1 fila actualizada.
EMP_ID NOMBRE SALARIO DPTO
---------- ------------------------ ---------- ----------------------------------------
160 Tomás 1000 FORMACIÓN
1 fila seleccionada.
Rollback terminado.
El orden importa: el departamento debe existir antes de trasladar al empleado (clave ajena).
Cuestión 7.
CREATE TABLE valoraciones (
cliente_id NUMBER(6),
producto_id NUMBER(6),
puntuacion NUMBER(1) CONSTRAINT nn_val_puntuacion NOT NULL,
fecha DATE DEFAULT SYSDATE CONSTRAINT nn_val_fecha NOT NULL,
comentario VARCHAR2(200),
CONSTRAINT pk_valoraciones PRIMARY KEY (cliente_id, producto_id),
CONSTRAINT fk_val_cliente FOREIGN KEY (cliente_id) REFERENCES clientes (cliente_id),
CONSTRAINT fk_val_producto FOREIGN KEY (producto_id) REFERENCES productos (producto_id),
CONSTRAINT ck_val_puntuacion CHECK (puntuacion BETWEEN 1 AND 5)
);
INSERT INTO valoraciones (cliente_id, producto_id, puntuacion, comentario) VALUES (1001, 2, 5, 'Muy potente');
INSERT INTO valoraciones (cliente_id, producto_id, puntuacion, comentario) VALUES (1003, 3, 7, 'Genial');
INSERT INTO valoraciones (cliente_id, producto_id, puntuacion) VALUES (1001, 2, 4);
SELECT cliente_id, producto_id, puntuacion, comentario FROM valoraciones;
DROP TABLE valoraciones PURGE;
Tabla creada.
1 fila creada.
INSERT INTO valoraciones (cliente_id, producto_id, puntuacion, comentario) VALUES (1003, 3, 7, 'Genial')
*
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_VAL_PUNTUACION) violated
INSERT INTO valoraciones (cliente_id, producto_id, puntuacion) VALUES (1001, 2, 4)
*
ERROR en línea 1:
ORA-00001: unique constraint (CURSO.PK_VALORACIONES) violated on table CURSO.VALORACIONES columns (CLIENTE_ID, PRODUCTO_ID)
ORA-03301: (ORA-00001 details) row with column values (CLIENTE_ID:1001, PRODUCTO_ID:2) already exists
CLIENTE_ID PRODUCTO_ID PUNTUACION COMENTARIO
---------- ----------- ---------- ------------------------------
1001 2 5 Muy potente
1 fila seleccionada.
Tabla borrada.
Cuestión 8.
CREATE OR REPLACE VIEW v_pedidos_pendientes AS
SELECT p.pedido_id, c.nombre AS cliente, p.fecha_pedido,
ROUND(SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)), 2) AS importe
FROM pedidos p
JOIN clientes c ON c.cliente_id = p.cliente_id
JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.estado = 'PENDIENTE'
GROUP BY p.pedido_id, c.nombre, p.fecha_pedido;
SELECT * FROM v_pedidos_pendientes ORDER BY fecha_pedido;
DROP VIEW v_pedidos_pendientes;
Vista creada.
PEDIDO_ID CLIENTE FECHA_PEDI IMPORTE
---------- ---------------------------- ---------- ----------
5022 Diseños Creativos S.L. 01/07/2026 1499
5023 Laura Benítez Sanz 08/07/2026 418,8
5024 Gestoría Delta 10/07/2026 1497
3 filas seleccionadas.
Vista borrada.
Criterios de corrección del examen 1
| Cuestión | Se valora | Penalizaciones habituales |
|---|---|---|
| 1 | Literal de fecha correcto y ordenación | Comparar con una cadena sin TO_DATE (−0,5) |
| 2 | Join, GROUP BY del nombre, HAVING y redondeo | Condición del recuento en WHERE (−0,5); agrupar por id y mostrar nombre sin agruparlo (−0,5) |
| 3 | LEFT JOIN y COUNT(columna) | JOIN interno (−0,75); COUNT(*) que da 1 a los clientes sin pedidos (−0,5) |
| 4 | Autocombinación con alias y cálculo | No distinguir los dos papeles de la tabla (−1) |
| 5 | Máximo con empates | ROWNUM = 1 o FETCH ... ONLY sin considerar empates (−0,25) |
| 6 | Orden correcto de sentencias, comprobación y ROLLBACK | Olvidar el ROLLBACK o hacer COMMIT (−0,5); trasladar antes de crear el departamento (−0,5) |
| 7 | Tipos adecuados, PK compuesta, FK, CHECK, DEFAULT, nombres | Restricciones sin nombre (−0,25 cada una, máx. −0,75); PK simple (−0,5) |
| 8 | Vista correcta y consultable | Importe sin descuento (−0,25) |
12. Examen tipo 2: universidad
Enunciado. Sobre el esquema EMPRESA (10 puntos; se valora la corrección en cualquier estado válido de la base de datos, no solo con los datos actuales):
| N.º | Cuestión | Puntos |
|---|---|---|
| 1 | Empleados que han trabajado en todos los proyectos de su departamento. Discute qué ocurre con los departamentos sin proyectos y da una versión que los excluya | 2 |
| 2 | Para cada comercial, su mejor cliente (el de mayor importe comprado sin cancelados), contemplando empates | 2 |
| 3 | Para cada empleado que tenga subordinados, el número total de subordinados directos e indirectos | 1,5 |
| 4 | Ventas mensuales de 2026 con la media móvil de los tres últimos meses y la variación porcentual respecto al mes anterior | 1,5 |
| 5 | Traduce a SQL la expresión π[nombre](σ[ciudad = 'Madrid'](CLIENTES) ⋈ PEDIDOS) y da una formulación equivalente con subconsulta. ¿Son equivalentes con multiconjuntos? | 1,5 |
| 6 | Con un único MERGE, fija el presupuesto de cada proyecto con asignaciones en el coste estimado (horas × salario / 160) más un 20 %, solo si no tenía presupuesto o si el actual es más del doble de ese coste. Deshaz al terminar | 1,5 |
Solución del examen 2
Ver solución
Cuestión 1 (división relacional). Formulación directa con doble NOT EXISTS:
SELECT e.emp_id, e.nombre, e.dept_id
FROM empleados e
WHERE NOT EXISTS (SELECT 1
FROM proyectos p
WHERE p.dept_id = e.dept_id
AND NOT EXISTS (SELECT 1 FROM asignaciones a
WHERE a.emp_id = e.emp_id AND a.proyecto_id = p.proyecto_id))
ORDER BY e.dept_id, e.emp_id;
EMP_ID NOMBRE DEPT_ID
---------- ------------------------ ----------
101 Luis 20
110 Carmen 20
111 David 20
112 Elena 20
113 Sergio 20
114 Lucía 20
120 Pablo 30
103 Pedro 40
...
17 filas seleccionadas.
Aparecen todos los empleados de Ventas (20) y Contabilidad (40), y también el becario: sus departamentos no tienen proyectos (o el becario no tiene departamento), así que “no existe ningún proyecto de su departamento en el que no haya trabajado” es trivialmente verdadero (verdad vacía, igual que ∀x ∈ ∅: P(x)). Para excluirlos se exige que el divisor no esté vacío:
SELECT e.emp_id, e.nombre, e.dept_id
FROM empleados e
WHERE EXISTS (SELECT 1 FROM proyectos p WHERE p.dept_id = e.dept_id)
AND NOT EXISTS (SELECT 1
FROM proyectos p
WHERE p.dept_id = e.dept_id
AND NOT EXISTS (SELECT 1 FROM asignaciones a
WHERE a.emp_id = e.emp_id AND a.proyecto_id = p.proyecto_id))
ORDER BY e.dept_id, e.emp_id;
EMP_ID NOMBRE DEPT_ID
---------- ------------------------ ----------
120 Pablo 30
104 Laura 50
140 Nuria 50
141 Álvaro 50
105 Javier 60
150 Rosa 60
152 Cristina 60
7 filas seleccionadas.
Ana no sale: Dirección tiene el proyecto 5 y ella no está asignada. Pablo es el único de Informática en los dos proyectos del departamento.
Cuestión 2 (top-1 por grupo con empates).
WITH compras AS (
SELECT c.comercial_id, c.cliente_id, c.nombre,
ROUND(SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)), 2) AS importe
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.cliente_id
JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.estado <> 'CANCELADO' AND c.comercial_id IS NOT NULL
GROUP BY c.comercial_id, c.cliente_id, c.nombre
),
ranking AS (
SELECT co.*, RANK() OVER (PARTITION BY comercial_id ORDER BY importe DESC) AS rk
FROM compras co
)
SELECT e.nombre AS comercial, r.nombre AS cliente, r.importe
FROM ranking r JOIN empleados e ON e.emp_id = r.comercial_id
WHERE r.rk = 1
ORDER BY r.importe DESC;
COMERCIAL CLIENTE IMPORTE
---------- ---------------------------- ----------
Elena Instituto Tecnológico Sur 20602,55
Carmen Construcciones Levante S.A. 14260,19
David Academia Nova 7891,3
Sergio Gestoría Delta 1497
4 filas seleccionadas.
RANK() (y no ROW_NUMBER()) conserva los empates. Alternativa sin analíticas: correlacionar importe = (SELECT MAX(importe) FROM compras x WHERE x.comercial_id = co.comercial_id).
Cuestión 3 (cierre transitivo).
SELECT s.raiz_id AS jefe_id, j.nombre, COUNT(*) AS subordinados
FROM (SELECT CONNECT_BY_ROOT emp_id AS raiz_id, emp_id
FROM empleados
WHERE LEVEL > 1
CONNECT BY PRIOR emp_id = jefe_id) s
JOIN empleados j ON j.emp_id = s.raiz_id
GROUP BY s.raiz_id, j.nombre
ORDER BY subordinados DESC, jefe_id;
JEFE_ID NOMBRE SUBORDINADOS
---------- ------------ ------------
100 Ana 23
102 Marta 6
101 Luis 5
105 Javier 3
120 Pablo 3
103 Pedro 2
104 Laura 2
7 filas seleccionadas.
Sin START WITH, CONNECT BY arranca un recorrido desde cada fila; CONNECT_BY_ROOT indica desde cuál, y LEVEL > 1 descarta la propia raíz. Ana tiene 23 subordinados (todos menos ella). Un primer intento con el alias jefe_id en la subconsulta y en el join da ORA-00918: column ambiguously specified, porque EMPLEADOS también tiene una columna jefe_id: de ahí el alias raiz_id.
Cuestión 4 (ventanas).
SELECT anio, mes, ventas,
ROUND(AVG(ventas) OVER (ORDER BY anio, mes ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS media_movil_3,
ROUND(100 * (ventas - LAG(ventas) OVER (ORDER BY anio, mes)) / LAG(ventas) OVER (ORDER BY anio, mes), 1) AS var_pct
FROM (SELECT EXTRACT(YEAR FROM p.fecha_pedido) AS anio,
EXTRACT(MONTH FROM p.fecha_pedido) AS mes,
ROUND(SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)), 2) AS ventas
FROM pedidos p JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.estado <> 'CANCELADO' AND p.fecha_pedido >= DATE '2026-01-01'
GROUP BY EXTRACT(YEAR FROM p.fecha_pedido), EXTRACT(MONTH FROM p.fecha_pedido))
ORDER BY anio, mes;
ANIO MES VENTAS MEDIA_MOVIL_3 VAR_PCT
---------- ---------- ---------- ------------- ----------
2026 1 8945,05 8945,05 (nulo)
2026 2 838 4891,53 -90,6
2026 3 2085,25 3956,1 148,8
2026 4 1296 1406,42 -37,8
2026 5 1663,64 1681,63 28,4
2026 6 2462,05 1807,23 48
2026 7 3414,8 2513,5 38,7
7 filas seleccionadas.
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW define una ventana de tres filas; si faltara un mes sin ventas, serían tres filas pero no tres meses (habría que generar antes el calendario o usar RANGE con un número de mes absoluto). En enero solo hay una fila en la ventana, por eso la media coincide con la venta.
Cuestión 5 (álgebra y SQL).
SELECT DISTINCT c.nombre
FROM clientes c JOIN pedidos p ON p.cliente_id = c.cliente_id
WHERE c.ciudad = 'Madrid';
SELECT c.nombre FROM clientes c
WHERE c.ciudad = 'Madrid' AND EXISTS (SELECT 1 FROM pedidos p WHERE p.cliente_id = c.cliente_id);
NOMBRE
------------------------
María José Herrera
Academia Nova
2 filas seleccionadas.
NOMBRE
------------------------
María José Herrera
Academia Nova
2 filas seleccionadas.
El álgebra relacional trabaja con conjuntos: la proyección elimina duplicados, por eso la traducción fiel necesita DISTINCT (sin él, cada cliente saldría una vez por pedido). La versión con EXISTS (una semicombinación, semi-join ⋉) no necesita DISTINCT porque cada cliente se evalúa una sola vez. Con multiconjuntos (SQL sin DISTINCT) las dos formulaciones no son equivalentes; con conjuntos, sí. Además, si dos clientes distintos se llamaran igual, DISTINCT c.nombre los fusionaría, mientras que EXISTS los mostraría por separado.
Cuestión 6 (MERGE condicional).
MERGE INTO proyectos p
USING (SELECT a.proyecto_id, ROUND(SUM(a.horas * e.salario / 160) * 1.2) AS coste
FROM asignaciones a JOIN empleados e ON e.emp_id = a.emp_id
GROUP BY a.proyecto_id) c
ON (p.proyecto_id = c.proyecto_id)
WHEN MATCHED THEN
UPDATE SET p.presupuesto = c.coste
WHERE p.presupuesto IS NULL OR p.presupuesto > c.coste * 2;
SELECT proyecto_id, nombre AS proyecto, presupuesto FROM proyectos ORDER BY proyecto_id;
ROLLBACK;
4 filas fusionadas.
PROYECTO_ID PROYECTO PRESUPUESTO
----------- -------------------------- -----------
1 Migración a la nube 31538
2 Nueva tienda online 27465
3 Campaña Navidad 2025 9930
4 Optimización de almacén 4781
5 Plan estratégico 2027 (nulo)
5 filas seleccionadas.
Rollback terminado.
Los cuatro presupuestos superaban el doble del coste estimado. El proyecto 5 no tiene asignaciones, no aparece en el origen y conserva su NULL. El GROUP BY proyecto_id del origen garantiza una fila por proyecto (si no, ORA-30926).
Criterios de corrección del examen 2
| Cuestión | Se valora | Penalizaciones habituales |
|---|---|---|
| 1 | Doble negación correcta y discusión del divisor vacío | Usar IN sobre un recuento global de proyectos de toda la empresa (−1); no discutir el caso vacío (−0,5) |
| 2 | Agregado previo, partición por comercial y empates | ROW_NUMBER (−0,5); MAX sin correlacionar por comercial (−1) |
| 3 | Recorrido desde cada nodo y recuento | Contar solo subordinados directos (−1) |
| 4 | Ventana correcta y LAG | Media acumulada en lugar de móvil (−0,75); división por cero no considerada (−0,25) |
| 5 | Traducción con DISTINCT y razonamiento conjunto/multiconjunto | Omitir DISTINCT sin justificarlo (−0,5) |
| 6 | Origen agregado, condición en la rama UPDATE y ROLLBACK | Condición en el ON (cambia la semántica) (−0,5); origen sin agrupar (−1) |
13. Examen tipo 3: mixto (diseño, DDL, DML, consultas y transacciones)
Enunciado. La empresa quiere registrar las incidencias de los pedidos (10 puntos):
| N.º | Cuestión | Puntos |
|---|---|---|
| 1 | Crea INCIDENCIAS_PEDIDO: identificador generado automáticamente, pedido (obligatorio; si se borra el pedido se borran sus incidencias), tipo (RETRASO, DAÑADO, ERROR u OTRO), fecha (por defecto la actual), resuelta (S/N, por defecto N) y comentario | 2 |
| 2 | Inserta con una sola sentencia tres incidencias: retraso del 5007 (13/04/2026, resuelta), producto dañado en el 5020 (03/06/2026) y falta de factura en el 5020 (04/06/2026). Prueba también un tipo no válido y confirma | 1,5 |
| 3 | Pedidos con incidencias: cliente, número de incidencias y cuántas siguen abiertas | 1,5 |
| 4 | Clientes cuyo importe total comprado (sin cancelados) supera la media de los clientes, mostrando también esa media | 2 |
| 5 | Indica qué queda en la tabla tras esta secuencia y compruébalo (ver abajo) | 2 |
| 6 | Demuestra qué ocurre con las incidencias si se borra el pedido 5020, sin perder datos. Elimina la tabla al final | 1 |
Secuencia de la cuestión 5:
INSERT INTO incidencias_pedido (pedido_id, tipo, comentario) VALUES (5013, 'ERROR', 'Producto equivocado');
SAVEPOINT tras_insert;
UPDATE incidencias_pedido SET resuelta = 'S' WHERE pedido_id = 5020;
DELETE FROM incidencias_pedido WHERE tipo = 'RETRASO';
ROLLBACK TO tras_insert;
UPDATE incidencias_pedido SET resuelta = 'S' WHERE tipo = 'OTRO';
COMMIT;
Solución del examen 3
Ver solución
Cuestión 1.
CREATE TABLE incidencias_pedido (
incidencia_id NUMBER GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_incidencias_pedido PRIMARY KEY,
pedido_id NUMBER(8) CONSTRAINT nn_inc_pedido NOT NULL,
tipo VARCHAR2(8) CONSTRAINT nn_inc_tipo NOT NULL,
fecha DATE DEFAULT SYSDATE CONSTRAINT nn_inc_fecha NOT NULL,
resuelta CHAR(1) DEFAULT 'N' CONSTRAINT nn_inc_resuelta NOT NULL,
comentario VARCHAR2(200),
CONSTRAINT fk_inc_pedido FOREIGN KEY (pedido_id) REFERENCES pedidos (pedido_id) ON DELETE CASCADE,
CONSTRAINT ck_inc_tipo CHECK (tipo IN ('RETRASO', 'DAÑADO', 'ERROR', 'OTRO')),
CONSTRAINT ck_inc_resuelta CHECK (resuelta IN ('S', 'N'))
);
Tabla creada.
Desde el punto de vista del diseño, INCIDENCIA depende en existencia de PEDIDO (de ahí el ON DELETE CASCADE), pero tiene identificador propio, así que no es una entidad débil por identificación. En 19c la identidad funciona igual; en versiones anteriores a 12c se usaría una secuencia.
Cuestión 2.
INSERT INTO incidencias_pedido (pedido_id, tipo, fecha, resuelta, comentario)
VALUES (5007, 'RETRASO', DATE '2026-04-13', 'S', 'Entregado un día tarde'),
(5020, 'DAÑADO', DATE '2026-06-03', 'N', 'Monitor con la pantalla rayada'),
(5020, 'OTRO', DATE '2026-06-04', 'N', 'Falta la factura');
INSERT INTO incidencias_pedido (pedido_id, tipo) VALUES (5021, 'PERDIDO');
COMMIT;
SELECT incidencia_id, pedido_id, tipo, fecha, resuelta FROM incidencias_pedido ORDER BY incidencia_id;
3 filas creadas.
INSERT INTO incidencias_pedido (pedido_id, tipo) VALUES (5021, 'PERDIDO')
*
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_INC_TIPO) violated
Confirmación terminada.
INCIDENCIA_ID PEDIDO_ID TIPO FECHA R
------------- ---------- -------- ---------- -
1 5007 RETRASO 13/04/2026 S
2 5020 DAÑADO 03/06/2026 N
3 5020 OTRO 04/06/2026 N
3 filas seleccionadas.
El INSERT multifila es de Oracle 23ai; en 19c se resolvería con INSERT ALL ... SELECT * FROM dual o con INSERT ... SELECT ... UNION ALL (módulo 34).
Cuestión 3.
SELECT p.pedido_id, c.nombre AS cliente, COUNT(*) AS incidencias,
SUM(CASE WHEN i.resuelta = 'N' THEN 1 ELSE 0 END) AS abiertas
FROM incidencias_pedido i
JOIN pedidos p ON p.pedido_id = i.pedido_id
JOIN clientes c ON c.cliente_id = p.cliente_id
GROUP BY p.pedido_id, c.nombre
ORDER BY p.pedido_id;
PEDIDO_ID CLIENTE INCIDENCIAS ABIERTAS
---------- ---------------------------- ----------- ----------
5007 Hotel Mar Azul S.L. 1 0
5020 Hotel Mar Azul S.L. 2 2
2 filas seleccionadas.
Cuestión 4.
WITH totales AS (
SELECT p.cliente_id,
ROUND(SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)), 2) AS total
FROM pedidos p JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.estado <> 'CANCELADO'
GROUP BY p.cliente_id
)
SELECT c.nombre AS cliente, t.total,
(SELECT ROUND(AVG(total), 2) FROM totales) AS media
FROM totales t JOIN clientes c ON c.cliente_id = t.cliente_id
WHERE t.total > (SELECT AVG(total) FROM totales)
ORDER BY t.total DESC;
CLIENTE TOTAL MEDIA
---------------------------- ---------- ----------
Instituto Tecnológico Sur 20602,55 5134,79
Construcciones Levante S.A. 14260,19 5134,79
Academia Nova 7891,3 5134,79
3 filas seleccionadas.
La media es la de los clientes con compras (12). Si el enunciado quisiera incluir a los clientes sin compras como 0, la CTE debería partir de CLIENTES con LEFT JOIN y NVL.
Cuestión 5.
1 fila creada.
Punto de grabación creado.
2 filas actualizadas.
1 fila suprimida.
Rollback terminado.
1 fila actualizada.
Confirmación terminada.
SELECT incidencia_id, pedido_id, tipo, resuelta FROM incidencias_pedido ORDER BY incidencia_id;
INCIDENCIA_ID PEDIDO_ID TIPO R
------------- ---------- -------- -
1 5007 RETRASO S
2 5020 DAÑADO N
3 5020 OTRO S
5 5013 ERROR N
4 filas seleccionadas.
ROLLBACK TO tras_insertdeshace elUPDATEde las dos del 5020 y elDELETEdel retraso, pero conserva la nueva incidencia.- El último
UPDATEresuelve solo la de tipoOTRO. - La nueva incidencia recibe el identificador 5, no el 4: el número 4 lo consumió el
INSERTfallido de la cuestión 2 (la identidad, como las secuencias, no reutiliza valores).
Cuestión 6.
SELECT COUNT(*) AS inc_5020 FROM incidencias_pedido WHERE pedido_id = 5020;
DELETE FROM pedidos WHERE pedido_id = 5020;
SELECT COUNT(*) AS inc_5020 FROM incidencias_pedido WHERE pedido_id = 5020;
SELECT COUNT(*) AS lineas_5020 FROM lineas_pedido WHERE pedido_id = 5020;
ROLLBACK;
DROP TABLE incidencias_pedido PURGE;
INC_5020
----------
2
1 fila seleccionada.
1 fila suprimida.
INC_5020
----------
0
1 fila seleccionada.
LINEAS_5020
-----------
0
1 fila seleccionada.
Rollback terminado.
Tabla borrada.
El borrado del pedido se propaga en cascada a sus incidencias y a sus líneas; el ROLLBACK lo deshace todo, así que no se pierde ningún dato.
Criterios de corrección del examen 3
| Cuestión | Se valora | Penalizaciones habituales |
|---|---|---|
| 1 | Identidad, FK con cascada, CHECK y DEFAULT con nombre | Sin ON DELETE CASCADE (−0,5); tipo sin CHECK (−0,5) |
| 2 | Inserción multifila (o alternativa clásica) y COMMIT | Varias sentencias cuando se pedía una (−0,5) |
| 3 | Recuento total y condicional | Contar abiertas con COUNT(resuelta) (−0,5) |
| 4 | Agregado en dos niveles | Comparar con AVG de las líneas en lugar de los totales (−1) |
| 5 | Razonamiento sobre el savepoint y el hueco de la identidad | No explicar el identificador 5 (−0,25) |
| 6 | Demostración y ROLLBACK | Borrar sin poder deshacer (−1) |
14. Segundo esquema de práctica: BIBLIOTECA
14.1. El modelo
Para comprobar que dominas las técnicas y no solo las tablas de EMPRESA, trabajarás con un esquema nuevo: la biblioteca de un centro educativo.
erDiagram
EDITORIALES ||--o{ LIBROS : publica
LIBROS ||--o{ LIBRO_AUTOR : "escrito por"
AUTORES ||--o{ LIBRO_AUTOR : escribe
LIBROS ||--|{ EJEMPLARES : "tiene"
EJEMPLARES ||--o{ PRESTAMOS : "se presta en"
SOCIOS ||--o{ PRESTAMOS : realiza
EDITORIALES (editorial_id, nombre, ciudad) — UK: nombre
AUTORES (autor_id, nombre, nacionalidad, anio_nac)
LIBROS (isbn, titulo, editorial_id, anio_pub, genero, paginas)
editorial_id → EDITORIALES(editorial_id)
LIBRO_AUTOR (*isbn*, *autor_id*, orden)
isbn → LIBROS(isbn)(borrado en cascada),autor_id → AUTORES(autor_id)
EJEMPLARES (*isbn*, num_ejemplar, estado) — entidad débil de LIBROS
isbn → LIBROS(isbn)(borrado en cascada)
SOCIOS (socio_id, nombre, apellidos, email, tipo, fecha_alta) — UK: email
PRESTAMOS (prestamo_id, socio_id, isbn, num_ejemplar, fecha_prestamo, fecha_prevista, fecha_devolucion)
socio_id → SOCIOS(socio_id),(isbn, num_ejemplar) → EJEMPLARES(isbn, num_ejemplar)
Casos especiales útiles: un libro con tres autores (la antología), dos libros nunca prestados, un ejemplar dado de baja, un socio sin préstamos (David, 8), seis préstamos activos (sin fecha_devolucion), dos de ellos con retraso a 1 de octubre de 2026, y cuatro préstamos devueltos tarde. Los ISBN son ficticios.
14.2. Script de creación y carga
Guarda este script como biblioteca.sql y ejecútalo con tu usuario (@biblioteca.sql). No usa ninguna tabla de EMPRESA, así que pueden convivir en el mismo esquema.
-- =====================================================================
-- ESQUEMA DE PRÁCTICA BIBLIOTECA (Curso de Bases de Datos, módulo 36)
-- Creación de tablas y carga de datos. Oracle 19c o superior.
-- =====================================================================
CREATE TABLE editoriales (
editorial_id NUMBER(4) CONSTRAINT pk_editoriales PRIMARY KEY,
nombre VARCHAR2(40) CONSTRAINT nn_edi_nombre NOT NULL,
ciudad VARCHAR2(30),
CONSTRAINT uq_edi_nombre UNIQUE (nombre)
);
CREATE TABLE autores (
autor_id NUMBER(4) CONSTRAINT pk_autores PRIMARY KEY,
nombre VARCHAR2(60) CONSTRAINT nn_aut_nombre NOT NULL,
nacionalidad VARCHAR2(30),
anio_nac NUMBER(4)
);
CREATE TABLE libros (
isbn VARCHAR2(13) CONSTRAINT pk_libros PRIMARY KEY,
titulo VARCHAR2(80) CONSTRAINT nn_lib_titulo NOT NULL,
editorial_id NUMBER(4) CONSTRAINT nn_lib_editorial NOT NULL,
anio_pub NUMBER(4),
genero VARCHAR2(10) CONSTRAINT nn_lib_genero NOT NULL,
paginas NUMBER(5),
CONSTRAINT ck_lib_genero CHECK (genero IN ('NOVELA', 'RELATO', 'ENSAYO', 'POESIA', 'TEATRO')),
CONSTRAINT ck_lib_paginas CHECK (paginas > 0),
CONSTRAINT fk_lib_editorial FOREIGN KEY (editorial_id) REFERENCES editoriales (editorial_id)
);
CREATE TABLE libro_autor (
isbn VARCHAR2(13),
autor_id NUMBER(4),
orden NUMBER(2) DEFAULT 1 CONSTRAINT nn_la_orden NOT NULL,
CONSTRAINT pk_libro_autor PRIMARY KEY (isbn, autor_id),
CONSTRAINT fk_la_libro FOREIGN KEY (isbn) REFERENCES libros (isbn) ON DELETE CASCADE,
CONSTRAINT fk_la_autor FOREIGN KEY (autor_id) REFERENCES autores (autor_id)
);
CREATE TABLE ejemplares (
isbn VARCHAR2(13),
num_ejemplar NUMBER(3),
estado VARCHAR2(10) DEFAULT 'DISPONIBLE' CONSTRAINT nn_eje_estado NOT NULL,
CONSTRAINT pk_ejemplares PRIMARY KEY (isbn, num_ejemplar),
CONSTRAINT ck_eje_estado CHECK (estado IN ('DISPONIBLE', 'PRESTADO', 'BAJA')),
CONSTRAINT fk_eje_libro FOREIGN KEY (isbn) REFERENCES libros (isbn) ON DELETE CASCADE
);
CREATE TABLE socios (
socio_id NUMBER(5) CONSTRAINT pk_socios PRIMARY KEY,
nombre VARCHAR2(30) CONSTRAINT nn_soc_nombre NOT NULL,
apellidos VARCHAR2(50) CONSTRAINT nn_soc_apellidos NOT NULL,
email VARCHAR2(60),
tipo VARCHAR2(10) CONSTRAINT nn_soc_tipo NOT NULL,
fecha_alta DATE DEFAULT SYSDATE CONSTRAINT nn_soc_alta NOT NULL,
CONSTRAINT uq_soc_email UNIQUE (email),
CONSTRAINT ck_soc_tipo CHECK (tipo IN ('ESTUDIANTE', 'PROFESOR', 'EXTERNO'))
);
CREATE TABLE prestamos (
prestamo_id NUMBER(6) GENERATED BY DEFAULT ON NULL AS IDENTITY CONSTRAINT pk_prestamos PRIMARY KEY,
socio_id NUMBER(5) CONSTRAINT nn_pre_socio NOT NULL,
isbn VARCHAR2(13) CONSTRAINT nn_pre_isbn NOT NULL,
num_ejemplar NUMBER(3) CONSTRAINT nn_pre_ejemplar NOT NULL,
fecha_prestamo DATE CONSTRAINT nn_pre_fecha NOT NULL,
fecha_prevista DATE CONSTRAINT nn_pre_prevista NOT NULL,
fecha_devolucion DATE,
CONSTRAINT ck_pre_prevista CHECK (fecha_prevista > fecha_prestamo),
CONSTRAINT ck_pre_devolucion CHECK (fecha_devolucion >= fecha_prestamo),
CONSTRAINT fk_pre_socio FOREIGN KEY (socio_id) REFERENCES socios (socio_id),
CONSTRAINT fk_pre_ejemplar FOREIGN KEY (isbn, num_ejemplar) REFERENCES ejemplares (isbn, num_ejemplar)
);
-- ---------------------------------------------------------------------
-- Datos
-- ---------------------------------------------------------------------
INSERT INTO editoriales VALUES (1, 'Alfaguara', 'Madrid');
INSERT INTO editoriales VALUES (2, 'Anagrama', 'Barcelona');
INSERT INTO editoriales VALUES (3, 'Planeta', 'Barcelona');
INSERT INTO editoriales VALUES (4, 'Cátedra', 'Madrid');
INSERT INTO editoriales VALUES (5, 'Salamandra', 'Barcelona');
INSERT INTO editoriales VALUES (6, 'Tusquets', 'Barcelona');
INSERT INTO autores VALUES (1, 'Gabriel García Márquez', 'Colombia', 1927);
INSERT INTO autores VALUES (2, 'Isabel Allende', 'Chile', 1942);
INSERT INTO autores VALUES (3, 'Carlos Ruiz Zafón', 'España', 1964);
INSERT INTO autores VALUES (4, 'Almudena Grandes', 'España', 1960);
INSERT INTO autores VALUES (5, 'Miguel de Cervantes', 'España', 1547);
INSERT INTO autores VALUES (6, 'Federico García Lorca', 'España', 1898);
INSERT INTO autores VALUES (7, 'Javier Cercas', 'España', 1962);
INSERT INTO autores VALUES (8, 'Rosa Montero', 'España', 1951);
INSERT INTO libros VALUES ('9788400000011', 'Cien años de soledad', 1, 1967, 'NOVELA', 471);
INSERT INTO libros VALUES ('9788400000028', 'El amor en los tiempos del cólera', 1, 1985, 'NOVELA', 464);
INSERT INTO libros VALUES ('9788400000035', 'La casa de los espíritus', 3, 1982, 'NOVELA', 496);
INSERT INTO libros VALUES ('9788400000042', 'Paula', 3, 1994, 'ENSAYO', 368);
INSERT INTO libros VALUES ('9788400000059', 'La sombra del viento', 3, 2001, 'NOVELA', 576);
INSERT INTO libros VALUES ('9788400000066', 'El laberinto de los espíritus', 3, 2016, 'NOVELA', 928);
INSERT INTO libros VALUES ('9788400000073', 'El corazón helado', 6, 2007, 'NOVELA', 928);
INSERT INTO libros VALUES ('9788400000080', 'Don Quijote de la Mancha', 4, 1605, 'NOVELA', 1376);
INSERT INTO libros VALUES ('9788400000097', 'Bodas de sangre', 4, 1933, 'TEATRO', 176);
INSERT INTO libros VALUES ('9788400000103', 'Romancero gitano', 4, 1928, 'POESIA', 224);
INSERT INTO libros VALUES ('9788400000110', 'Soldados de Salamina', 6, 2001, 'NOVELA', 224);
INSERT INTO libros VALUES ('9788400000127', 'La loca de la casa', 1, 2003, 'ENSAYO', 288);
INSERT INTO libros VALUES ('9788400000134', 'Antología de relatos iberoamericanos', 5, 2010, 'RELATO', 320);
INSERT INTO libro_autor VALUES ('9788400000011', 1, 1);
INSERT INTO libro_autor VALUES ('9788400000028', 1, 1);
INSERT INTO libro_autor VALUES ('9788400000035', 2, 1);
INSERT INTO libro_autor VALUES ('9788400000042', 2, 1);
INSERT INTO libro_autor VALUES ('9788400000059', 3, 1);
INSERT INTO libro_autor VALUES ('9788400000066', 3, 1);
INSERT INTO libro_autor VALUES ('9788400000073', 4, 1);
INSERT INTO libro_autor VALUES ('9788400000080', 5, 1);
INSERT INTO libro_autor VALUES ('9788400000097', 6, 1);
INSERT INTO libro_autor VALUES ('9788400000103', 6, 1);
INSERT INTO libro_autor VALUES ('9788400000110', 7, 1);
INSERT INTO libro_autor VALUES ('9788400000127', 8, 1);
INSERT INTO libro_autor VALUES ('9788400000134', 2, 1);
INSERT INTO libro_autor VALUES ('9788400000134', 1, 2);
INSERT INTO libro_autor VALUES ('9788400000134', 8, 3);
INSERT INTO ejemplares VALUES ('9788400000011', 1, 'PRESTADO');
INSERT INTO ejemplares VALUES ('9788400000011', 2, 'DISPONIBLE');
INSERT INTO ejemplares VALUES ('9788400000011', 3, 'BAJA');
INSERT INTO ejemplares VALUES ('9788400000028', 1, 'DISPONIBLE');
INSERT INTO ejemplares VALUES ('9788400000028', 2, 'PRESTADO');
INSERT INTO ejemplares VALUES ('9788400000035', 1, 'DISPONIBLE');
INSERT INTO ejemplares VALUES ('9788400000035', 2, 'DISPONIBLE');
INSERT INTO ejemplares VALUES ('9788400000042', 1, 'DISPONIBLE');
INSERT INTO ejemplares VALUES ('9788400000059', 1, 'DISPONIBLE');
INSERT INTO ejemplares VALUES ('9788400000059', 2, 'DISPONIBLE');
INSERT INTO ejemplares VALUES ('9788400000059', 3, 'PRESTADO');
INSERT INTO ejemplares VALUES ('9788400000066', 1, 'DISPONIBLE');
INSERT INTO ejemplares VALUES ('9788400000073', 1, 'DISPONIBLE');
INSERT INTO ejemplares VALUES ('9788400000073', 2, 'PRESTADO');
INSERT INTO ejemplares VALUES ('9788400000080', 1, 'DISPONIBLE');
INSERT INTO ejemplares VALUES ('9788400000080', 2, 'PRESTADO');
INSERT INTO ejemplares VALUES ('9788400000097', 1, 'PRESTADO');
INSERT INTO ejemplares VALUES ('9788400000103', 1, 'DISPONIBLE');
INSERT INTO ejemplares VALUES ('9788400000103', 2, 'DISPONIBLE');
INSERT INTO ejemplares VALUES ('9788400000110', 1, 'DISPONIBLE');
INSERT INTO ejemplares VALUES ('9788400000127', 1, 'DISPONIBLE');
INSERT INTO ejemplares VALUES ('9788400000134', 1, 'DISPONIBLE');
INSERT INTO socios VALUES (1, 'Lucía', 'Pérez Gómez', 'lucia.perez@correo.es', 'ESTUDIANTE', DATE '2024-09-15');
INSERT INTO socios VALUES (2, 'Marcos', 'Ruiz Vidal', 'marcos.ruiz@correo.es', 'ESTUDIANTE', DATE '2025-02-03');
INSERT INTO socios VALUES (3, 'Elena', 'Soto Marín', 'elena.soto@correo.es', 'PROFESOR', DATE '2023-10-01');
INSERT INTO socios VALUES (4, 'Jorge', 'Lara Peña', NULL, 'EXTERNO', DATE '2025-06-20');
INSERT INTO socios VALUES (5, 'Nerea', 'Gil Ortega', 'nerea.gil@correo.es', 'ESTUDIANTE', DATE '2025-09-10');
INSERT INTO socios VALUES (6, 'Pablo', 'Cano Ruiz', 'pablo.cano@correo.es', 'PROFESOR', DATE '2024-01-12');
INSERT INTO socios VALUES (7, 'Irene', 'Mora Sanz', 'irene.mora@correo.es', 'EXTERNO', DATE '2026-03-05');
INSERT INTO socios VALUES (8, 'David', 'Ibáñez Gil', 'david.ibanez@correo.es', 'ESTUDIANTE', DATE '2026-09-01');
INSERT INTO prestamos (socio_id, isbn, num_ejemplar, fecha_prestamo, fecha_prevista, fecha_devolucion) VALUES (1, '9788400000011', 1, DATE '2026-01-10', DATE '2026-01-25', DATE '2026-01-20');
INSERT INTO prestamos (socio_id, isbn, num_ejemplar, fecha_prestamo, fecha_prevista, fecha_devolucion) VALUES (1, '9788400000059', 1, DATE '2026-02-01', DATE '2026-02-16', DATE '2026-02-20');
INSERT INTO prestamos (socio_id, isbn, num_ejemplar, fecha_prestamo, fecha_prevista, fecha_devolucion) VALUES (2, '9788400000059', 2, DATE '2026-02-05', DATE '2026-02-20', DATE '2026-02-18');
INSERT INTO prestamos (socio_id, isbn, num_ejemplar, fecha_prestamo, fecha_prevista, fecha_devolucion) VALUES (3, '9788400000080', 1, DATE '2026-02-10', DATE '2026-03-12', DATE '2026-03-10');
INSERT INTO prestamos (socio_id, isbn, num_ejemplar, fecha_prestamo, fecha_prevista, fecha_devolucion) VALUES (3, '9788400000097', 1, DATE '2026-03-15', DATE '2026-04-14', DATE '2026-04-20');
INSERT INTO prestamos (socio_id, isbn, num_ejemplar, fecha_prestamo, fecha_prevista, fecha_devolucion) VALUES (4, '9788400000035', 1, DATE '2026-03-20', DATE '2026-04-04', DATE '2026-04-03');
INSERT INTO prestamos (socio_id, isbn, num_ejemplar, fecha_prestamo, fecha_prevista, fecha_devolucion) VALUES (1, '9788400000035', 2, DATE '2026-04-02', DATE '2026-04-17', DATE '2026-04-15');
INSERT INTO prestamos (socio_id, isbn, num_ejemplar, fecha_prestamo, fecha_prevista, fecha_devolucion) VALUES (5, '9788400000011', 2, DATE '2026-04-10', DATE '2026-04-25', DATE '2026-05-02');
INSERT INTO prestamos (socio_id, isbn, num_ejemplar, fecha_prestamo, fecha_prevista, fecha_devolucion) VALUES (6, '9788400000103', 1, DATE '2026-05-05', DATE '2026-06-04', DATE '2026-06-01');
INSERT INTO prestamos (socio_id, isbn, num_ejemplar, fecha_prestamo, fecha_prevista, fecha_devolucion) VALUES (2, '9788400000073', 1, DATE '2026-05-12', DATE '2026-05-27', DATE '2026-05-26');
INSERT INTO prestamos (socio_id, isbn, num_ejemplar, fecha_prestamo, fecha_prevista, fecha_devolucion) VALUES (1, '9788400000042', 1, DATE '2026-06-01', DATE '2026-06-16', DATE '2026-06-14');
INSERT INTO prestamos (socio_id, isbn, num_ejemplar, fecha_prestamo, fecha_prevista, fecha_devolucion) VALUES (7, '9788400000028', 1, DATE '2026-06-10', DATE '2026-06-25', DATE '2026-06-24');
INSERT INTO prestamos (socio_id, isbn, num_ejemplar, fecha_prestamo, fecha_prevista, fecha_devolucion) VALUES (6, '9788400000110', 1, DATE '2026-07-01', DATE '2026-07-31', DATE '2026-07-28');
INSERT INTO prestamos (socio_id, isbn, num_ejemplar, fecha_prestamo, fecha_prevista, fecha_devolucion) VALUES (2, '9788400000066', 1, DATE '2026-07-15', DATE '2026-07-30', DATE '2026-08-05');
INSERT INTO prestamos (socio_id, isbn, num_ejemplar, fecha_prestamo, fecha_prevista, fecha_devolucion) VALUES (5, '9788400000059', 3, DATE '2026-09-01', DATE '2026-09-16', NULL);
INSERT INTO prestamos (socio_id, isbn, num_ejemplar, fecha_prestamo, fecha_prevista, fecha_devolucion) VALUES (3, '9788400000073', 2, DATE '2026-09-10', DATE '2026-10-10', NULL);
INSERT INTO prestamos (socio_id, isbn, num_ejemplar, fecha_prestamo, fecha_prevista, fecha_devolucion) VALUES (4, '9788400000080', 2, DATE '2026-09-15', DATE '2026-09-30', NULL);
INSERT INTO prestamos (socio_id, isbn, num_ejemplar, fecha_prestamo, fecha_prevista, fecha_devolucion) VALUES (7, '9788400000011', 1, DATE '2026-09-20', DATE '2026-10-05', NULL);
INSERT INTO prestamos (socio_id, isbn, num_ejemplar, fecha_prestamo, fecha_prevista, fecha_devolucion) VALUES (1, '9788400000028', 2, DATE '2026-09-25', DATE '2026-10-10', NULL);
INSERT INTO prestamos (socio_id, isbn, num_ejemplar, fecha_prestamo, fecha_prevista, fecha_devolucion) VALUES (6, '9788400000097', 1, DATE '2026-09-28', DATE '2026-10-28', NULL);
COMMIT;
-- Para borrar el esquema:
-- DROP TABLE prestamos CASCADE CONSTRAINTS PURGE;
-- DROP TABLE socios CASCADE CONSTRAINTS PURGE;
-- DROP TABLE ejemplares CASCADE CONSTRAINTS PURGE;
-- DROP TABLE libro_autor CASCADE CONSTRAINTS PURGE;
-- DROP TABLE libros CASCADE CONSTRAINTS PURGE;
-- DROP TABLE autores CASCADE CONSTRAINTS PURGE;
-- DROP TABLE editoriales CASCADE CONSTRAINTS PURGE;
Comprobación de la carga:
SELECT 'EDITORIALES' t, COUNT(*) n FROM editoriales UNION ALL SELECT 'AUTORES', COUNT(*) FROM autores
UNION ALL SELECT 'LIBROS', COUNT(*) FROM libros UNION ALL SELECT 'LIBRO_AUTOR', COUNT(*) FROM libro_autor
UNION ALL SELECT 'EJEMPLARES', COUNT(*) FROM ejemplares UNION ALL SELECT 'SOCIOS', COUNT(*) FROM socios
UNION ALL SELECT 'PRESTAMOS', COUNT(*) FROM prestamos;
T N
----------- ----------
EDITORIALES 6
AUTORES 8
LIBROS 13
LIBRO_AUTOR 15
EJEMPLARES 22
SOCIOS 8
PRESTAMOS 20
7 filas seleccionadas.
⚠️ Importante: La tabla
PRESTAMOSusa una identidadBY DEFAULT ON NULL: los préstamos de la carga reciben los números 1 a 20 en el orden de inserción. Si recargas el esquema sin borrarlo antes, obtendrásORA-00955al crear las tablas.
14.3. Ejercicios resueltos sobre BIBLIOTECA
Ejercicio B1: Libros del siglo XXI ★
Libros publicados después del año 2000, por año y título.
Ver solución
SELECT titulo, anio_pub, genero
FROM libros
WHERE anio_pub > 2000
ORDER BY anio_pub, titulo;
TITULO ANIO_PUB GENERO
------------------------------------ ---------- -------
La sombra del viento 2001 NOVELA
Soldados de Salamina 2001 NOVELA
La loca de la casa 2003 ENSAYO
El corazón helado 2007 NOVELA
Antología de relatos iberoamericanos 2010 RELATO
El laberinto de los espíritus 2016 NOVELA
6 filas seleccionadas.
Ejercicio B2: Estudiantes dados de alta en 2025 ★
Ver solución
SELECT socio_id, nombre, apellidos, fecha_alta
FROM socios
WHERE tipo = 'ESTUDIANTE'
AND fecha_alta >= DATE '2025-01-01' AND fecha_alta < DATE '2026-01-01';
SOCIO_ID NOMBRE APELLIDOS FECHA_ALTA
---------- ---------------------- -------------------------------------------------- ----------
2 Marcos Ruiz Vidal 03/02/2025
5 Nerea Gil Ortega 10/09/2025
2 filas seleccionadas.
Ejercicio B3: Libros de editoriales madrileñas ★
Ver solución
SELECT l.titulo, e.nombre AS editorial, e.ciudad
FROM libros l JOIN editoriales e ON e.editorial_id = l.editorial_id
WHERE e.ciudad = 'Madrid'
ORDER BY e.nombre, l.titulo;
TITULO EDITORIAL CIUDAD
------------------------------------ ------------ ------------------------------
Cien años de soledad Alfaguara Madrid
El amor en los tiempos del cólera Alfaguara Madrid
La loca de la casa Alfaguara Madrid
Bodas de sangre Cátedra Madrid
Don Quijote de la Mancha Cátedra Madrid
Romancero gitano Cátedra Madrid
6 filas seleccionadas.
Ejercicio B4: Libros con sus autores ★★
Cada libro con la lista de sus autores en el orden de firma.
Ver solución
SELECT l.titulo,
LISTAGG(a.nombre, '; ') WITHIN GROUP (ORDER BY la.orden) AS autores
FROM libros l
JOIN libro_autor la ON la.isbn = l.isbn
JOIN autores a ON a.autor_id = la.autor_id
GROUP BY l.titulo
ORDER BY l.titulo;
TITULO AUTORES
------------------------------------ --------------------------------------------------------------
Antología de relatos iberoamericanos Isabel Allende; Gabriel García Márquez; Rosa Montero
Bodas de sangre Federico García Lorca
Cien años de soledad Gabriel García Márquez
Don Quijote de la Mancha Miguel de Cervantes
El amor en los tiempos del cólera Gabriel García Márquez
El corazón helado Almudena Grandes
El laberinto de los espíritus Carlos Ruiz Zafón
La casa de los espíritus Isabel Allende
...
13 filas seleccionadas.
La relación N:M se recorre con dos joins a través de la tabla intermedia. Agrupar por l.titulo es correcto aquí porque no hay títulos repetidos; en general conviene agrupar por la clave (l.isbn, l.titulo).
Ejercicio B5: Ejemplares y disponibilidad ★★
Para los libros con más de un ejemplar: total de ejemplares, cuántos están disponibles y cuántos dados de baja.
Ver solución
SELECT l.titulo,
COUNT(*) AS ejemplares,
SUM(CASE WHEN e.estado = 'DISPONIBLE' THEN 1 ELSE 0 END) AS disponibles,
COUNT(CASE WHEN e.estado = 'BAJA' THEN 1 END) AS de_baja
FROM libros l JOIN ejemplares e ON e.isbn = l.isbn
GROUP BY l.titulo
HAVING COUNT(*) > 1
ORDER BY l.titulo;
TITULO EJEMPLARES DISPONIBLES DE_BAJA
------------------------------------ ---------- ----------- ----------
Cien años de soledad 3 1 1
Don Quijote de la Mancha 2 1 0
El amor en los tiempos del cólera 2 1 0
El corazón helado 2 1 0
La casa de los espíritus 2 2 0
La sombra del viento 3 2 0
Romancero gitano 2 2 0
7 filas seleccionadas.
Dos formas equivalentes de recuento condicional: SUM(CASE ... THEN 1 ELSE 0 END) y COUNT(CASE ... THEN 1 END) (el CASE sin ELSE devuelve NULL, que COUNT no cuenta).
Ejercicio B6: Préstamos en curso ★★
Préstamos no devueltos, con el nombre completo del socio y el título, por fecha prevista de devolución.
Ver solución
SELECT p.prestamo_id, s.nombre || ' ' || s.apellidos AS socio, l.titulo,
p.num_ejemplar, p.fecha_prestamo, p.fecha_prevista
FROM prestamos p
JOIN socios s ON s.socio_id = p.socio_id
JOIN libros l ON l.isbn = p.isbn
WHERE p.fecha_devolucion IS NULL
ORDER BY p.fecha_prevista;
PRESTAMO_ID SOCIO TITULO NUM_EJEMPLAR FECHA_PRES FECHA_PREV
----------- -------------------- ------------------------------------ ------------ ---------- ----------
15 Nerea Gil Ortega La sombra del viento 3 01/09/2026 16/09/2026
17 Jorge Lara Peña Don Quijote de la Mancha 2 15/09/2026 30/09/2026
18 Irene Mora Sanz Cien años de soledad 1 20/09/2026 05/10/2026
19 Lucía Pérez Gómez El amor en los tiempos del cólera 2 25/09/2026 10/10/2026
16 Elena Soto Marín El corazón helado 2 10/09/2026 10/10/2026
20 Pablo Cano Ruiz Bodas de sangre 1 28/09/2026 28/10/2026
6 filas seleccionadas.
Ejercicio B7: Préstamos con retraso ★★
Préstamos no devueltos cuya fecha prevista ya pasó a 1 de octubre de 2026, con los días de retraso.
Ver solución
SELECT s.nombre || ' ' || s.apellidos AS socio, l.titulo, p.fecha_prevista,
DATE '2026-10-01' - p.fecha_prevista AS dias_retraso
FROM prestamos p
JOIN socios s ON s.socio_id = p.socio_id
JOIN libros l ON l.isbn = p.isbn
WHERE p.fecha_devolucion IS NULL
AND p.fecha_prevista < DATE '2026-10-01'
ORDER BY dias_retraso DESC;
SOCIO TITULO FECHA_PREV DIAS_RETRASO
-------------------- ------------------------------------ ---------- ------------
Nerea Gil Ortega La sombra del viento 16/09/2026 15
Jorge Lara Peña Don Quijote de la Mancha 30/09/2026 1
2 filas seleccionadas.
En la aplicación real se usaría TRUNC(SYSDATE) en lugar de la fecha fija.
Ejercicio B8: Devoluciones tardías ★★
Préstamos que se devolvieron después de la fecha prevista y cuántos días tarde.
Ver solución
SELECT p.prestamo_id, s.nombre AS socio, l.titulo, p.fecha_prevista, p.fecha_devolucion,
p.fecha_devolucion - p.fecha_prevista AS dias_tarde
FROM prestamos p
JOIN socios s ON s.socio_id = p.socio_id
JOIN libros l ON l.isbn = p.isbn
WHERE p.fecha_devolucion > p.fecha_prevista
ORDER BY dias_tarde DESC;
PRESTAMO_ID SOCIO TITULO FECHA_PREV FECHA_DEVO DIAS_TARDE
----------- -------------------- ------------------------------------ ---------- ---------- ----------
8 Nerea Cien años de soledad 25/04/2026 02/05/2026 7
5 Elena Bodas de sangre 14/04/2026 20/04/2026 6
14 Marcos El laberinto de los espíritus 30/07/2026 05/08/2026 6
2 Lucía La sombra del viento 16/02/2026 20/02/2026 4
4 filas seleccionadas.
Los préstamos activos (fecha_devolucion nula) quedan fuera porque la comparación es desconocida.
Ejercicio B9: El socio más lector ★★
Ver solución
SELECT s.socio_id, s.nombre, COUNT(*) AS prestamos
FROM socios s JOIN prestamos p ON p.socio_id = s.socio_id
GROUP BY s.socio_id, s.nombre
ORDER BY prestamos DESC
FETCH FIRST 1 ROWS WITH TIES;
SOCIO_ID NOMBRE PRESTAMOS
---------- ---------------------- ----------
1 Lucía 5
1 fila seleccionada.
Ejercicio B10: Libros que nadie ha pedido ★
Ver solución
SELECT l.isbn, l.titulo
FROM libros l
WHERE NOT EXISTS (SELECT 1 FROM prestamos p WHERE p.isbn = l.isbn);
ISBN TITULO
------------- ------------------------------------
9788400000134 Antología de relatos iberoamericanos
9788400000127 La loca de la casa
2 filas seleccionadas.
Ejercicio B11: Autores con varios libros ★★
Ver solución
SELECT a.nombre AS autor, COUNT(*) AS libros
FROM autores a JOIN libro_autor la ON la.autor_id = a.autor_id
GROUP BY a.nombre
HAVING COUNT(*) > 1
ORDER BY libros DESC, autor;
AUTOR LIBROS
------------------------ ----------
Gabriel García Márquez 3
Isabel Allende 3
Carlos Ruiz Zafón 2
Federico García Lorca 2
Rosa Montero 2
5 filas seleccionadas.
La antología cuenta para sus tres autores.
Ejercicio B12: Géneros más prestados ★★
Ver solución
SELECT l.genero, COUNT(*) AS prestamos
FROM prestamos p JOIN libros l ON l.isbn = p.isbn
GROUP BY l.genero
ORDER BY prestamos DESC;
GENERO PRESTAMOS
------- ----------
NOVELA 16
TEATRO 2
POESIA 1
ENSAYO 1
4 filas seleccionadas.
RELATO no aparece porque la antología nunca se ha prestado. Para mostrar todos los géneros con 0 haría falta una tabla de géneros (o un VALUES de 23ai) con join externo: es una ventaja de modelar el género como tabla y no como CHECK.
Ejercicio B13: Lectores de García Márquez ★★
Socios que han tomado prestado algún libro de Gabriel García Márquez.
Ver solución
SELECT DISTINCT s.nombre, s.apellidos
FROM socios s
JOIN prestamos p ON p.socio_id = s.socio_id
WHERE p.isbn IN (SELECT la.isbn
FROM libro_autor la JOIN autores a ON a.autor_id = la.autor_id
WHERE a.nombre = 'Gabriel García Márquez')
ORDER BY s.apellidos;
NOMBRE APELLIDOS
---------------------- --------------------------------------------------
Nerea Gil Ortega
Irene Mora Sanz
Lucía Pérez Gómez
3 filas seleccionadas.
El DISTINCT es necesario porque Lucía e Irene han leído dos libros suyos.
Ejercicio B14: Ranking de libros ★★★
Todos los libros con el número de veces que se han prestado y su puesto (sin huecos en caso de empate).
Ver solución
SELECT l.titulo, COUNT(p.prestamo_id) AS veces,
DENSE_RANK() OVER (ORDER BY COUNT(p.prestamo_id) DESC) AS puesto
FROM libros l LEFT JOIN prestamos p ON p.isbn = l.isbn
GROUP BY l.titulo
ORDER BY puesto, l.titulo;
TITULO VECES PUESTO
------------------------------------ ---------- ----------
Cien años de soledad 3 1
La sombra del viento 3 1
Bodas de sangre 2 2
Don Quijote de la Mancha 2 2
El amor en los tiempos del cólera 2 2
El corazón helado 2 2
La casa de los espíritus 2 2
El laberinto de los espíritus 1 3
...
13 filas seleccionadas.
Una función analítica puede aplicarse sobre un agregado (COUNT(...)) en la misma consulta agrupada: primero se agrupa y después se calcula la ventana. Los dos libros nunca prestados ocupan el puesto 4.
Ejercicio B15: Último préstamo de cada socio ★★★
Ver solución
SELECT socio, titulo, fecha_prestamo
FROM (SELECT s.nombre AS socio, l.titulo, p.fecha_prestamo,
ROW_NUMBER() OVER (PARTITION BY s.socio_id ORDER BY p.fecha_prestamo DESC) AS rn
FROM prestamos p
JOIN socios s ON s.socio_id = p.socio_id
JOIN libros l ON l.isbn = p.isbn)
WHERE rn = 1
ORDER BY fecha_prestamo DESC;
SOCIO TITULO FECHA_PRES
-------------------- ------------------------------------ ----------
Pablo Bodas de sangre 28/09/2026
Lucía El amor en los tiempos del cólera 25/09/2026
Irene Cien años de soledad 20/09/2026
Jorge Don Quijote de la Mancha 15/09/2026
Elena El corazón helado 10/09/2026
Nerea La sombra del viento 01/09/2026
Marcos El laberinto de los espíritus 15/07/2026
7 filas seleccionadas.
David no aparece porque no tiene préstamos.
Ejercicio B16: Obras en coautoría ★
Ver solución
SELECT l.titulo, COUNT(*) AS num_autores
FROM libros l JOIN libro_autor la ON la.isbn = l.isbn
GROUP BY l.titulo
HAVING COUNT(*) > 1;
TITULO NUM_AUTORES
------------------------------------ -----------
Antología de relatos iberoamericanos 3
1 fila seleccionada.
Ejercicio B17: Registrar un préstamo ★★
David (8) se lleva hoy (1/10/2026) un ejemplar disponible de “La sombra del viento” durante 15 días. Registra el préstamo eligiendo el ejemplar disponible de menor número y marca el ejemplar como prestado.
Ver solución
SELECT isbn, num_ejemplar, estado FROM ejemplares WHERE isbn = '9788400000059';
INSERT INTO prestamos (socio_id, isbn, num_ejemplar, fecha_prestamo, fecha_prevista)
SELECT 8, isbn, MIN(num_ejemplar), DATE '2026-10-01', DATE '2026-10-01' + 15
FROM ejemplares
WHERE isbn = '9788400000059' AND estado = 'DISPONIBLE'
GROUP BY isbn;
UPDATE ejemplares
SET estado = 'PRESTADO'
WHERE (isbn, num_ejemplar) IN (SELECT isbn, num_ejemplar FROM prestamos
WHERE socio_id = 8 AND fecha_devolucion IS NULL);
SELECT prestamo_id, socio_id, num_ejemplar, fecha_prestamo, fecha_prevista FROM prestamos WHERE socio_id = 8;
SELECT isbn, num_ejemplar, estado FROM ejemplares WHERE isbn = '9788400000059';
ROLLBACK;
ISBN NUM_EJEMPLAR ESTADO
------------- ------------ ----------
9788400000059 1 DISPONIBLE
9788400000059 2 DISPONIBLE
9788400000059 3 PRESTADO
3 filas seleccionadas.
1 fila creada.
1 fila actualizada.
PRESTAMO_ID SOCIO_ID NUM_EJEMPLAR FECHA_PRES FECHA_PREV
----------- ---------- ------------ ---------- ----------
21 8 1 01/10/2026 16/10/2026
1 fila seleccionada.
ISBN NUM_EJEMPLAR ESTADO
------------- ------------ ----------
9788400000059 1 PRESTADO
9788400000059 2 DISPONIBLE
9788400000059 3 PRESTADO
3 filas seleccionadas.
Rollback terminado.
Las dos sentencias forman una transacción: si una fallara, habría que deshacer la otra para no dejar un préstamo sin ejemplar marcado (o al revés). Si no hubiera ejemplares disponibles, el INSERT ... SELECT insertaría 0 filas, lo que la aplicación debe comprobar. Con concurrencia, el ejemplar debería bloquearse antes con SELECT ... FOR UPDATE SKIP LOCKED (módulo 35).
Ejercicio B18: Registrar devoluciones ★★
Lucía (1) devuelve hoy todos los libros que tiene en préstamo. Actualiza los ejemplares y los préstamos.
Ver solución
UPDATE ejemplares e
SET e.estado = 'DISPONIBLE'
WHERE (e.isbn, e.num_ejemplar) IN (SELECT p.isbn, p.num_ejemplar FROM prestamos p
WHERE p.socio_id = 1 AND p.fecha_devolucion IS NULL);
UPDATE prestamos
SET fecha_devolucion = DATE '2026-10-01'
WHERE socio_id = 1 AND fecha_devolucion IS NULL;
SELECT prestamo_id, isbn, num_ejemplar, fecha_devolucion FROM prestamos WHERE socio_id = 1 ORDER BY prestamo_id;
ROLLBACK;
1 fila actualizada.
1 fila actualizada.
PRESTAMO_ID ISBN NUM_EJEMPLAR FECHA_DEVO
----------- ------------- ------------ ----------
1 9788400000011 1 20/01/2026
2 9788400000059 1 20/02/2026
7 9788400000035 2 15/04/2026
11 9788400000042 1 14/06/2026
19 9788400000028 2 01/10/2026
5 filas seleccionadas.
Rollback terminado.
El orden importa: primero los ejemplares (que se identifican por los préstamos aún abiertos) y después los préstamos. Al revés, la subconsulta ya no encontraría préstamos abiertos y los ejemplares quedarían como prestados.
Ejercicio B19: Lectores de toda la obra de Ruiz Zafón ★★★
Socios que han leído todos los libros de Carlos Ruiz Zafón.
Ver solución
SELECT s.socio_id, s.nombre
FROM socios s
WHERE NOT EXISTS (SELECT 1
FROM libro_autor la JOIN autores a ON a.autor_id = la.autor_id
WHERE a.nombre = 'Carlos Ruiz Zafón'
AND NOT EXISTS (SELECT 1 FROM prestamos p
WHERE p.socio_id = s.socio_id AND p.isbn = la.isbn));
SOCIO_ID NOMBRE
---------- ----------------------
2 Marcos
1 fila seleccionada.
Marcos ha leído “La sombra del viento” y “El laberinto de los espíritus”. Lucía y Nerea solo el primero.
Ejercicio B20: Vista de disponibilidad ★★
Crea una vista con, para cada libro, el número de ejemplares disponibles y prestados, y úsala para listar los libros con algún ejemplar prestado o sin ninguno disponible.
Ver solución
CREATE OR REPLACE VIEW v_disponibilidad AS
SELECT l.isbn, l.titulo,
COUNT(CASE WHEN e.estado = 'DISPONIBLE' THEN 1 END) AS disponibles,
COUNT(CASE WHEN e.estado = 'PRESTADO' THEN 1 END) AS prestados
FROM libros l LEFT JOIN ejemplares e ON e.isbn = l.isbn
GROUP BY l.isbn, l.titulo;
SELECT titulo, disponibles, prestados
FROM v_disponibilidad
WHERE disponibles = 0 OR prestados > 0
ORDER BY titulo;
DROP VIEW v_disponibilidad;
Vista creada.
TITULO DISPONIBLES PRESTADOS
------------------------------------ ----------- ----------
Bodas de sangre 0 1
Cien años de soledad 1 1
Don Quijote de la Mancha 1 1
El amor en los tiempos del cólera 1 1
El corazón helado 1 1
La sombra del viento 2 1
6 filas seleccionadas.
Vista borrada.
El estado PRESTADO de EJEMPLARES es un dato derivado de PRESTAMOS (redundancia controlada): hay que mantenerlo coherente en cada préstamo y devolución (ejercicios B17 y B18). Garantizarlo automáticamente exigiría un trigger (PL/SQL). Alternativa sin redundancia: calcular la disponibilidad siempre a partir de los préstamos abiertos.
Para terminar, borra el esquema BIBLIOTECA con las sentencias comentadas al final del script.
15. Ejercicios propuestos
Ejercicio 1: Clientes fieles
Clientes que han hecho pedidos en 2025 y en 2026. Resuélvelo de tres formas: con INTERSECT, con dos EXISTS y con GROUP BY ... HAVING COUNT(DISTINCT EXTRACT(YEAR ...)) = 2.
Ejercicio 2: Productos sin rotación
Productos con stock que no se han vendido en los últimos seis meses (respecto al 1/10/2026), con el valor inmovilizado (stock × precio).
Ejercicio 3: Pirámide salarial
Para cada nivel del organigrama (LEVEL), número de empleados, salario mínimo, medio y máximo.
Ejercicio 4: Huecos en la numeración
Encuentra los huecos en la numeración de emp_id entre 100 y 160 usando LEAD.
Ejercicio 5: BIBLIOTECA — sanciones
Crea una tabla SANCIONES (socio, fecha de inicio, días) y cárgala con un INSERT ... SELECT que sancione con 2 días por cada día de retraso de las devoluciones tardías. Después, lista los socios sancionados el 1/10/2026.
Ejercicio 6: BIBLIOTECA — reservas
Diseña y crea una tabla de reservas (un socio reserva un libro, no un ejemplar) e implementa con MERGE el proceso que, al devolverse un ejemplar, asigna el préstamo a la reserva más antigua de ese libro.
Resumen
| Bloque | Técnicas clave | Errores típicos |
|---|---|---|
| Básicas | WHERE, BETWEEN, IN, LIKE, IS NULL, FETCH FIRST ... WITH TIES | = NULL; prioridad AND/OR; fechas como cadena |
| Funciones | SUBSTR/INSTR, MONTHS_BETWEEN, TO_CHAR con formato e idioma, NVL2, CASE, DECODE | Concatenar con NULL; NVL con tipos distintos; dividir días entre 365 |
| Agrupación | GROUP BY, HAVING, COUNT(col) frente a COUNT(*), ROLLUP, LISTAGG | ORA-00979, ORA-00934; medias que ignoran nulos |
| Joins | INNER, LEFT + IS NULL (anti-join), self join, cadenas de joins externos, (+) | Filtrar la tabla opcional en el WHERE; fan trap |
| Subconsultas | Escalares, de fila, correlacionadas, EXISTS, ALL, división con doble NOT EXISTS | NOT IN con nulos; > ALL con conjunto vacío; divisor vacío |
| Conjuntos y CTE | UNION [ALL], INTERSECT, MINUS, WITH, CTE recursivas | Agregar después de combinar; olvidar el tipo en la CTE recursiva |
| Analíticas | RANK/DENSE_RANK/ROW_NUMBER, SUM OVER, LAG, ventanas ROWS | Usar analíticas en el WHERE; empates con ROW_NUMBER |
| Jerárquicas | CONNECT BY, LEVEL, SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT, ORDER SIBLINGS BY | ORDER BY normal que rompe la jerarquía |
| DML | INSERT ... SELECT, UPDATE correlacionado + EXISTS, MERGE, borrado de duplicados con ROWID | UPDATE que pone a NULL; origen de MERGE con duplicados |
| DDL | Restricciones con nombre, PK compuesta, ON DELETE CASCADE, ALTER TABLE, vistas, índices, diccionario | Restricciones sin nombre (SYS_C...); vistas agregadas no actualizables |
| Exámenes | Leer el enunciado, pensar en nulos, empates y casos vacíos, probar con datos | Dar por buena una consulta porque “sale algo” |