Solución ejercicio completo

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;

Publicado por

Avatar del usuario

Juan Pablo Fuentes

Formador de programación y bases de datos