Clínica: citas, especialidades y facturación
Nueve apartados sobre la base de datos de una clínica privada: edades con fechas, la agenda de un día, LEFT JOIN con la condición en el ON, HAVING con un límite exacto, NOT EXISTS, porcentajes de cancelación, especialidades distintas, la última cita con ROW_NUMBER y un UPDATE.
- Fechas con strftime
- JOIN de cuatro tablas
- LEFT JOIN con condición en ON
- GROUP BY y HAVING
- NOT EXISTS
- COUNT(DISTINCT)
- ROW_NUMBER
- UPDATE
Enunciado
Una clínica privada con cinco especialidades guarda en su base de datos los médicos, los pacientes y las citas. Cada cita tiene un estado: realizada (y entonces tiene importe), cancelada o pendiente (sin importe todavía). Los pacientes sin aseguradora (NULL) son privados y pagan ellos.
La dirección te pide una serie de informes para la reunión de octubre de 2026. Antes de escribir cada consulta, busca en las tablas qué filas deberían salir: varios apartados tienen trampas pensadas (un médico que factura exactamente 100 €, una paciente que cumple años al día siguiente, una especialidad sin citas).
Qué hay que hacer
- Escribe una consulta (o una sentencia, en el último apartado) para cada apartado. La fecha de referencia de los informes es el 1 de octubre de 2026.
- Cada apartado se corrige por separado sobre una copia nueva de la base de datos, con exactamente los datos de las tablas de arriba: lo que hagas en uno no afecta a los demás.
- Importan las filas, el número y el orden de las columnas y, cuando el apartado pide un orden, el orden de las filas; los nombres de las columnas no importan. Redondea con
ROUNDcuando se pidan decimales.
Las tablas y sus datos
Base de datos del ejercicio · 4 tablas
- especialidades
- medicos
- pacientes
- citas
Subrayada, la clave primaria; con flecha, la columna a la que apunta cada clave ajena.
| especialidad_id | nombre | precio_consulta |
|---|---|---|
| 1 | Medicina general | 40 |
| 2 | Pediatría | 50 |
| 3 | Dermatología | 65 |
| 4 | Traumatología | 70 |
| 5 | Cardiología | 85 |
| medico_id | nombre | especialidad_id | anio_alta |
|---|---|---|---|
| 1 | Ana Ruiz | 1 | 2015 |
| 2 | Luis Gómez | 1 | 2019 |
| 3 | Marta Peña | 2 | 2012 |
| 4 | Jorge Sanz | 3 | 2020 |
| 5 | Elena Ríos | 4 | 2017 |
| 6 | Pablo Mena | 5 | 2024 |
| paciente_id | nombre | nacimiento | ciudad | aseguradora |
|---|---|---|---|---|
| 1 | Carmen López | 1958-03-12 | Madrid | Sanitas |
| 2 | Diego Martín | 2018-11-30 | Madrid | NULL |
| 3 | Lucía Fernández | 1990-10-01 | Getafe | Adeslas |
| 4 | Hugo Navarro | 2020-06-15 | Madrid | Sanitas |
| 5 | Rosa Jiménez | 1975-10-02 | Madrid | NULL |
| 6 | Iván Torres | 1988-01-20 | Alcorcón | NULL |
| 7 | Sara Molina | 2001-12-24 | Madrid | Asisa |
| 8 | Tomás Ortega | 1949-07-07 | Getafe | NULL |
| cita_id | paciente_id | medico_id | fecha | hora | estado | importe |
|---|---|---|---|---|---|---|
| 1 | 1 | 1 | 2026-09-02 | 09:00 | realizada | 40 |
| 2 | 2 | 3 | 2026-09-03 | 10:30 | realizada | 50 |
| 3 | 3 | 4 | 2026-09-08 | 12:00 | realizada | 65 |
| 4 | 1 | 5 | 2026-09-10 | 16:00 | realizada | 70 |
| 5 | 5 | 1 | 2026-09-15 | 09:30 | cancelada | NULL |
| 6 | 6 | 2 | 2026-09-16 | 11:00 | realizada | 40 |
| 7 | 4 | 3 | 2026-09-18 | 10:00 | realizada | 50 |
| 8 | 7 | 4 | 2026-09-22 | 13:00 | realizada | 65 |
| 9 | 8 | 5 | 2026-09-25 | 17:30 | cancelada | NULL |
| 10 | 1 | 1 | 2026-09-29 | 09:00 | realizada | 40 |
| 11 | 3 | 2 | 2026-09-30 | 12:30 | pendiente | NULL |
| 12 | 5 | 4 | 2026-10-05 | 10:00 | pendiente | NULL |
| 13 | 2 | 3 | 2026-10-05 | 09:30 | pendiente | NULL |
| 14 | 8 | 1 | 2026-10-05 | 12:00 | pendiente | NULL |
| 15 | 6 | 5 | 2026-10-06 | 16:30 | pendiente | NULL |
| 16 | 7 | 2 | 2026-09-12 | 11:30 | cancelada | NULL |
| 17 | 3 | 5 | 2026-09-19 | 18:00 | realizada | 70 |
Ver el script SQL que crea la base de datos
1CREATE TABLE especialidades (
2 especialidad_id INTEGER PRIMARY KEY,
3 nombre TEXT NOT NULL,
4 precio_consulta REAL NOT NULL
5);
6CREATE TABLE medicos (
7 medico_id INTEGER PRIMARY KEY,
8 nombre TEXT NOT NULL,
9 especialidad_id INTEGER NOT NULL REFERENCES especialidades(especialidad_id),
10 anio_alta INTEGER NOT NULL
11);
12CREATE TABLE pacientes (
13 paciente_id INTEGER PRIMARY KEY,
14 nombre TEXT NOT NULL,
15 nacimiento DATE NOT NULL,
16 ciudad TEXT NOT NULL,
17 aseguradora TEXT
18);
19CREATE TABLE citas (
20 cita_id INTEGER PRIMARY KEY,
21 paciente_id INTEGER NOT NULL REFERENCES pacientes(paciente_id),
22 medico_id INTEGER NOT NULL REFERENCES medicos(medico_id),
23 fecha DATE NOT NULL,
24 hora TEXT NOT NULL,
25 estado TEXT NOT NULL CHECK (estado IN ('realizada', 'cancelada', 'pendiente', 'no presentado')),
26 importe REAL
27);
28
29INSERT INTO especialidades (especialidad_id, nombre, precio_consulta) VALUES
30 (1, 'Medicina general', 40),
31 (2, 'Pediatría', 50),
32 (3, 'Dermatología', 65),
33 (4, 'Traumatología', 70),
34 (5, 'Cardiología', 85);
35
36INSERT INTO medicos (medico_id, nombre, especialidad_id, anio_alta) VALUES
37 (1, 'Ana Ruiz', 1, 2015),
38 (2, 'Luis Gómez', 1, 2019),
39 (3, 'Marta Peña', 2, 2012),
40 (4, 'Jorge Sanz', 3, 2020),
41 (5, 'Elena Ríos', 4, 2017),
42 (6, 'Pablo Mena', 5, 2024);
43
44INSERT INTO pacientes (paciente_id, nombre, nacimiento, ciudad, aseguradora) VALUES
45 (1, 'Carmen López', '1958-03-12', 'Madrid', 'Sanitas'),
46 (2, 'Diego Martín', '2018-11-30', 'Madrid', NULL),
47 (3, 'Lucía Fernández', '1990-10-01', 'Getafe', 'Adeslas'),
48 (4, 'Hugo Navarro', '2020-06-15', 'Madrid', 'Sanitas'),
49 (5, 'Rosa Jiménez', '1975-10-02', 'Madrid', NULL),
50 (6, 'Iván Torres', '1988-01-20', 'Alcorcón', NULL),
51 (7, 'Sara Molina', '2001-12-24', 'Madrid', 'Asisa'),
52 (8, 'Tomás Ortega', '1949-07-07', 'Getafe', NULL);
53
54INSERT INTO citas (cita_id, paciente_id, medico_id, fecha, hora, estado, importe) VALUES
55 (1, 1, 1, '2026-09-02', '09:00', 'realizada', 40),
56 (2, 2, 3, '2026-09-03', '10:30', 'realizada', 50),
57 (3, 3, 4, '2026-09-08', '12:00', 'realizada', 65),
58 (4, 1, 5, '2026-09-10', '16:00', 'realizada', 70),
59 (5, 5, 1, '2026-09-15', '09:30', 'cancelada', NULL),
60 (6, 6, 2, '2026-09-16', '11:00', 'realizada', 40),
61 (7, 4, 3, '2026-09-18', '10:00', 'realizada', 50),
62 (8, 7, 4, '2026-09-22', '13:00', 'realizada', 65),
63 (9, 8, 5, '2026-09-25', '17:30', 'cancelada', NULL),
64 (10, 1, 1, '2026-09-29', '09:00', 'realizada', 40),
65 (11, 3, 2, '2026-09-30', '12:30', 'pendiente', NULL),
66 (12, 5, 4, '2026-10-05', '10:00', 'pendiente', NULL),
67 (13, 2, 3, '2026-10-05', '09:30', 'pendiente', NULL),
68 (14, 8, 1, '2026-10-05', '12:00', 'pendiente', NULL),
69 (15, 6, 5, '2026-10-06', '16:30', 'pendiente', NULL),
70 (16, 7, 2, '2026-09-12', '11:30', 'cancelada', NULL),
71 (17, 3, 5, '2026-09-19', '18:00', 'realizada', 70);Guía paso a paso
Intenta resolverlo por tu cuenta y abre un paso solo cuando te atasques: cada uno te acerca a la solución sin dártela entera.
1. Calcula a mano antes de escribir
Para cada apartado, marca en las tablas las filas que deberían salir. Si tu consulta devuelve otras, sabrás si el problema está en el JOIN, en el filtro o en la agrupación.
2. WHERE, HAVING o ON
WHERE filtra filas antes de agrupar, HAVING filtra grupos después de agrupar, y en un LEFT JOIN la condición del ON decide qué filas se emparejan sin eliminar las de la izquierda. Elegir el sitio equivocado es el error más habitual de estos ejercicios.
3. Contar bien
COUNT(*) cuenta filas, COUNT(columna) las que no son NULL y COUNT(DISTINCT columna) los valores distintos. Tras un LEFT JOIN, COUNT(*) vale 1 aunque no haya pareja.
4. Lo mejor de cada grupo
Para «el último», «el primero» o «el mayor» de cada grupo junto con otros datos de esa fila, numera las filas de cada grupo con una función de ventana y quédate con la primera.
ROW_NUMBER() OVER (PARTITION BY paciente_id ORDER BY fecha DESC)5. Antes de un UPDATE, un SELECT
Escribe primero SELECT * FROM citas WHERE … con la misma condición y comprueba que salen exactamente las filas que quieres cambiar. Después cambia el SELECT * por el UPDATE … SET.
Resuélvelo aquí
Cada apartado se corrige por separado contra la base de datos de arriba: escribe la consulta y pulsa «Comprobar».
Pacientes privados (sin aseguradora) que viven en Madrid, con su edad cumplida el 1 de octubre de 2026: nombre y edad, de mayor a menor edad.
La agenda del 5 de octubre de 2026: hora, nombre del paciente, nombre del médico y especialidad, por orden de hora.
Número de citas realizadas de cada especialidad, incluidas las que no tienen ninguna: especialidad y número de citas, de más a menos citas y, a igualdad, por nombre de la especialidad.
Facturación de septiembre de 2026 por médico, contando solo las citas realizadas: médico, número de citas e importe total, solo de los médicos que facturan más de 100 €, de mayor a menor importe.
Pacientes que tienen alguna cita y no han cancelado nunca ninguna: nombre, por orden alfabético.
Porcentaje de citas canceladas de cada médico que tenga al menos 3 citas (en cualquier estado): médico, citas, canceladas y porcentaje con un decimal, de mayor a menor porcentaje y, a igualdad, por nombre.
Pacientes que han tenido citas realizadas con médicos de más de una especialidad: nombre y número de especialidades distintas, por orden alfabético.
La última cita realizada de cada paciente que tenga alguna: nombre del paciente, fecha y nombre del médico, por orden alfabético del paciente.
Las citas pendientes con fecha anterior al 1 de octubre de 2026 pasan al estado 'no presentado'. Escribe el UPDATE (se comprueban el id y el estado de todas las citas).
Solución explicada
Ver las soluciones de todos los apartados
Apartado 1
SELECT nombre,
(strftime('%Y', '2026-10-01') - strftime('%Y', nacimiento))
- (strftime('%m-%d', nacimiento) > '10-01') AS edad
FROM pacientes
WHERE ciudad = 'Madrid' AND aseguradora IS NULL
ORDER BY edad DESC;Apartado 2
SELECT c.hora, p.nombre, m.nombre, e.nombre
FROM citas c
JOIN pacientes p ON p.paciente_id = c.paciente_id
JOIN medicos m ON m.medico_id = c.medico_id
JOIN especialidades e ON e.especialidad_id = m.especialidad_id
WHERE c.fecha = '2026-10-05'
ORDER BY c.hora;Apartado 3
SELECT e.nombre, COUNT(c.cita_id) AS citas
FROM especialidades e
LEFT JOIN medicos m ON m.especialidad_id = e.especialidad_id
LEFT JOIN citas c ON c.medico_id = m.medico_id AND c.estado = 'realizada'
GROUP BY e.especialidad_id
ORDER BY citas DESC, e.nombre;Apartado 4
SELECT m.nombre, COUNT(*) AS citas, SUM(c.importe) AS total
FROM citas c
JOIN medicos m ON m.medico_id = c.medico_id
WHERE c.estado = 'realizada' AND c.fecha BETWEEN '2026-09-01' AND '2026-09-30'
GROUP BY m.medico_id
HAVING SUM(c.importe) > 100
ORDER BY total DESC;Apartado 5
SELECT p.nombre
FROM pacientes p
WHERE EXISTS (SELECT 1 FROM citas c WHERE c.paciente_id = p.paciente_id)
AND NOT EXISTS (SELECT 1 FROM citas c WHERE c.paciente_id = p.paciente_id AND c.estado = 'cancelada')
ORDER BY p.nombre;Apartado 6
SELECT m.nombre, COUNT(*) AS citas,
SUM(c.estado = 'cancelada') AS canceladas,
ROUND(100.0 * SUM(c.estado = 'cancelada') / COUNT(*), 1) AS porcentaje
FROM medicos m
JOIN citas c ON c.medico_id = m.medico_id
GROUP BY m.medico_id
HAVING COUNT(*) >= 3
ORDER BY porcentaje DESC, m.nombre;Apartado 7
SELECT p.nombre, COUNT(DISTINCT m.especialidad_id) AS especialidades
FROM pacientes p
JOIN citas c ON c.paciente_id = p.paciente_id AND c.estado = 'realizada'
JOIN medicos m ON m.medico_id = c.medico_id
GROUP BY p.paciente_id
HAVING COUNT(DISTINCT m.especialidad_id) > 1
ORDER BY p.nombre;Apartado 8
WITH ordenadas AS (
SELECT c.*, ROW_NUMBER() OVER (PARTITION BY c.paciente_id ORDER BY c.fecha DESC, c.hora DESC) AS n
FROM citas c
WHERE c.estado = 'realizada'
)
SELECT p.nombre, o.fecha, m.nombre
FROM ordenadas o
JOIN pacientes p ON p.paciente_id = o.paciente_id
JOIN medicos m ON m.medico_id = o.medico_id
WHERE o.n = 1
ORDER BY p.nombre;Apartado 9
UPDATE citas
SET estado = 'no presentado'
WHERE estado = 'pendiente' AND fecha < '2026-10-01';La edad se calcula restando años y corrigiendo con una comparación de 'MM-DD': Rosa nació un 2 de octubre y el 1 de octubre aún tiene 50 años, no 51. En SQLite una comparación vale 1 o 0 y se puede restar directamente.
El apartado de especialidades muestra la trampa más frecuente del LEFT JOIN: un WHERE sobre la tabla de la derecha lo convierte en un JOIN normal. La condición del estado tiene que ir en el ON para que Cardiología salga con 0.
EXISTS y NOT EXISTS responden preguntas sobre el conjunto de filas de cada paciente («alguna», «ninguna»), que no se pueden responder mirando las filas de una en una con WHERE.
ROW_NUMBER con PARTITION BY resuelve el problema del «máximo por grupo con el resto de su fila», que con GROUP BY solo se puede hacer con subconsultas correlacionadas más difíciles de leer.
Para ir más allá
- Crea una vista
agenda_diacon la consulta del apartado 2 sin la fecha fija y úsala para cualquier día. - Calcula la facturación mensual de toda la clínica con
strftime('%Y-%m', fecha)y el acumulado del año conSUM(...) OVER (ORDER BY mes). - Añade un disparador que impida dar dos citas al mismo médico a la misma fecha y hora.