Base de datos
CREATE TABLE `cliente` ( `idcliente` int(10) unsigned NOT NULL AUTO_INCREMENT, `nombre` varchar(145) DEFAULT NULL, `dni` varchar(45) DEFAULT NULL, `mail` varchar(45) DEFAULT NULL, `password` varchar(45) DEFAULT NULL, PRIMARY KEY (`idcliente`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; CREATE TABLE `empleado` ( `idempleado` int(10) unsigned NOT NULL AUTO_INCREMENT, `nombre` varchar(145) DEFAULT NULL, `dni` varchar(45) DEFAULT NULL, `mail` varchar(45) DEFAULT NULL, PRIMARY KEY (`idempleado`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; CREATE TABLE `llamada` ( `idllamada` int(10) unsigned NOT NULL AUTO_INCREMENT, `idcliente` int(10) unsigned DEFAULT NULL, `idempleado` int(10) unsigned DEFAULT NULL, `fechahora` datetime DEFAULT NULL, `duracion` smallint(5) unsigned DEFAULT NULL, PRIMARY KEY (`idllamada`), KEY `fk_cliente_idx` (`idcliente`), KEY `fk_empleado_idx` (`idempleado`), CONSTRAINT `fk_cliente` FOREIGN KEY (`idcliente`) REFERENCES `cliente` (`idcliente`) ON UPDATE NO ACTION, CONSTRAINT `fk_empleado` FOREIGN KEY (`idempleado`) REFERENCES `empleado` (`idempleado`) ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Datos de prueba:
INSERT INTO `cliente` (`idcliente`, `nombre`, `dni`, `mail`, `password`) VALUES (1, 'Juan Perez', '12345678A', 'juan.perez@example.com', 'password123'), (2, 'Maria Lopez', '23456789B', 'maria.lopez@example.com', 'password456'), (3, 'Carlos Garcia', '34567890C', 'carlos.garcia@example.com', 'password789'), (4, 'Ana Fernandez', '45678901D', 'ana.fernandez@example.com', 'password012'), (5, 'Luis Martinez', '56789012E', 'luis.martinez@example.com', 'password345'), (6, 'Elena Gomez', '67890123F', 'elena.gomez@example.com', 'password678'), (7, 'Pedro Sanchez', '78901234G', 'pedro.sanchez@example.com', 'password901'), (8, 'Sofia Ramirez', '89012345H', 'sofia.ramirez@example.com', 'password234'), (9, 'Miguel Torres', '90123456I', 'miguel.torres@example.com', 'password567'), (10, 'Laura Morales', '01234567J', 'laura.morales@example.com', 'password890'); INSERT INTO `empleado` (`idempleado`, `nombre`, `dni`, `mail`) VALUES (1, 'Alejandro Rodriguez', '11223344A', 'alejandro.rodriguez@example.com'), (2, 'Beatriz Sanchez', '22334455B', 'beatriz.sanchez@example.com'), (3, 'Cristina Martinez', '33445566C', 'cristina.martinez@example.com'), (4, 'David Fernandez', '44556677D', 'david.fernandez@example.com'), (5, 'Eva Gonzalez', '55667788E', 'eva.gonzalez@example.com'), (6, 'Francisco Lopez', '66778899F', 'francisco.lopez@example.com'), (7, 'Gabriela Herrera', '77889900G', 'gabriela.herrera@example.com'), (8, 'Hector Ruiz', '88990011H', 'hector.ruiz@example.com'), (9, 'Irene Castillo', '99001122I', 'irene.castillo@example.com'), (10, 'Javier Ortega', '00112233J', 'javier.ortega@example.com'); INSERT INTO `llamada` (`idllamada`, `idcliente`, `idempleado`, `fechahora`, `duracion`) VALUES (1, 1, 2, '2024-01-01 09:00:00', 15), (2, 3, 4, '2024-01-01 10:30:00', 30), (3, 2, 1, '2024-01-01 11:00:00', 20), (4, 5, 3, '2024-01-02 14:00:00', 25), (5, 6, 5, '2024-01-02 15:45:00', 10), (6, 4, 6, '2024-01-03 08:15:00', 35), (7, 7, 8, '2024-01-03 09:45:00', 40), (8, 8, 7, '2024-01-04 12:30:00', 50), (9, 9, 9, '2024-01-04 13:15:00', 5), (10, 10, 10, '2024-01-05 16:00:00', 45); INSERT INTO `llamada` (`idllamada`, `idcliente`, `idempleado`, `fechahora`, `duracion`) VALUES (11, 1, 3, '2024-01-05 17:30:00', 20), (12, 2, 4, '2024-01-06 08:45:00', 15), (13, 3, 5, '2024-01-06 09:15:00', 25), (14, 4, 6, '2024-01-07 11:30:00', 30), (15, 5, 7, '2024-01-07 12:00:00', 40), (16, 6, 8, '2024-01-08 14:45:00', 50), (17, 7, 9, '2024-01-08 15:00:00', 10), (18, 8, 10, '2024-01-09 16:15:00', 35), (19, 9, 1, '2024-01-09 17:00:00', 45), (20, 10, 2, '2024-01-10 09:00:00', 20), (21, 1, 3, '2024-01-10 09:45:00', 15), (22, 2, 4, '2024-01-11 10:30:00', 25), (23, 3, 5, '2024-01-11 11:00:00', 30), (24, 4, 6, '2024-01-12 12:30:00', 40), (25, 5, 7, '2024-01-12 13:15:00', 50), (26, 6, 8, '2024-01-13 14:00:00', 10), (27, 7, 9, '2024-01-13 14:45:00', 35), (28, 8, 10, '2024-01-14 15:30:00', 45), (29, 9, 1, '2024-01-14 16:15:00', 20), (30, 10, 2, '2024-01-15 17:00:00', 15), (31, 1, 3, '2024-01-15 08:45:00', 25), (32, 2, 4, '2024-01-16 09:15:00', 30), (33, 3, 5, '2024-01-16 10:00:00', 40), (34, 4, 6, '2024-01-17 11:00:00', 50), (35, 5, 7, '2024-01-17 11:45:00', 10), (36, 6, 8, '2024-01-18 12:30:00', 35), (37, 7, 9, '2024-01-18 13:15:00', 45), (38, 8, 10, '2024-01-19 14:00:00', 20), (39, 9, 1, '2024-01-19 14:45:00', 15), (40, 10, 2, '2024-01-20 15:30:00', 25); INSERT INTO `empleado` (`idempleado`, `nombre`, `dni`, `mail`) VALUES (11, 'Karla Paredes', '10293847K', 'karla.paredes@example.com'), (12, 'Luis Dominguez', '56473829L', 'luis.dominguez@example.com'); INSERT INTO `cliente` (`idcliente`, `nombre`, `dni`, `mail`, `password`) VALUES (11, 'Natalia Herrera', '11223344K', 'natalia.herrera@example.com', 'password112'), (12, 'Roberto Diaz', '22334455L', 'roberto.diaz@example.com', 'password223'); INSERT INTO `llamada` (`idllamada`, `idcliente`, `idempleado`, `fechahora`, `duracion`) VALUES (41, 1, 2, '2023-12-15 10:30:00', 20), (42, 2, 3, '2023-11-20 14:00:00', 30), (43, 3, 4, '2023-10-25 16:45:00', 15), (44, 4, 5, '2023-09-30 08:00:00', 25), (45, 5, 6, '2023-08-10 09:15:00', 40), (46, 6, 7, '2023-07-05 11:30:00', 35), (47, 7, 8, '2023-06-15 13:45:00', 50), (48, 8, 9, '2023-05-20 15:00:00', 10), (49, 9, 10, '2023-04-25 17:15:00', 45), (50, 10, 1, '2023-03-30 19:30:00', 20), (51, 11, 2, '2022-12-15 10:30:00', 15), (52, 12, 3, '2022-11-20 14:00:00', 25), (53, 1, 4, '2022-10-25 16:45:00', 30), (54, 2, 5, '2022-09-30 08:00:00', 40), (55, 3, 6, '2022-08-10 09:15:00', 50), (56, 4, 7, '2022-07-05 11:30:00', 10), (57, 5, 8, '2022-06-15 13:45:00', 35), (58, 6, 9, '2022-05-20 15:00:00', 45), (59, 7, 10, '2022-04-25 17:15:00', 20), (60, 8, 1, '2022-03-30 19:30:00', 15);
Consultas
-- Empleados sin llamadas select empleado.* from empleado left join llamada on empleado.idempleado=llamada.idempleado where idllamada is null; select * from empleado where idempleado not in (select idempleado from llamada); -- Total de llamadas por empleado select empleado.*, count(idllamada) total from empleado left join llamada on empleado.idempleado=llamada.idempleado group by idempleado; -- Total de llamadas por cliente select cliente.*, count(idllamada) total from cliente left join llamada on cliente.idcliente=llamada.idcliente group by idcliente; -- Cliente con la llamada de mayor duración select distinct cliente.*, duracion from cliente join llamada on cliente.idcliente=llamada.idcliente order by duracion desc limit 1; -- bien hecho select distinct cliente.*, duracion from cliente join llamada on cliente.idcliente=llamada.idcliente where duracion=(select max(duracion) from llamada); -- Total de llamadas por año select year(fechahora) anyo, count(idllamada) total from llamada group by anyo;