Ejercicio
A partir del siguiente enunciado:
Se quiere almacenar información de los empleados, clientes, productos y pedidos de una empresa. De cada cliente queremos almacenar un código único, el nombre y la ciudad. Un cliente puede hacer varios pedidos, pero un pedido es dolo de un cliente. De cada pedido queremos almacenar un código único y la fecha del pedido. Cada pedido lo gestiona un empleado. Un empleado puede gestionar varios pedidos. De cada empleado queremos almacenar un código único, nombre y ciudad. Cada pedido puede incluir varias unidades de un solo producto. De cada producto almacenamos un código único su nombre y precio unitario.
Obtener las siguientes sentencias:
A. muestra un listado con las siguientes columnas: numero_de_pedido,nombre_cliente,nombre_empleado,nombre_producto,cantidad, precio_unitario y total_importe_pedido
B. Muestra un listado con las siguientes columnas: numero_de_pedidos, nombre_cliente, media_importe_pedidos y total_importe_pedidos para cada cliente ordenado por nombre de cliente
C. Muestra un listado con las siguientes columnas: numero_de_pedidos, numero_empleados, ciudad, media_importe_pedidos y total_importe_pedidos para cada ciudad de residencia de los empleados siempre que al menos en la ciudad haya 2 o mas empleados y que en total en la ciudad se hayan firmado al menos 6 pedidos.
D. Muestra todos los datos de los empleados que hayan firmado pedidos a clientes de su misma ciudad.
Solución
El script para crear las tablas seria:
DROP TABLE IF EXISTS `examen_cliente`;
CREATE TABLE `examen_cliente` (
`codigo` int(11) NOT NULL AUTO_INCREMENT,
`nombre` varchar(255) DEFAULT NULL,
`ciudad` varchar(255) DEFAULT NULL,
PRIMARY KEY (`codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
— —————————-
— Table structure for examen_empleado
— —————————-
DROP TABLE IF EXISTS `examen_empleado`;
CREATE TABLE `examen_empleado` (
`codigo` int(11) NOT NULL AUTO_INCREMENT,
`nombre` varchar(255) DEFAULT NULL,
`ciudad` varchar(255) DEFAULT NULL,
PRIMARY KEY (`codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
— —————————-
— Table structure for examen_pedido
— —————————-
DROP TABLE IF EXISTS `examen_pedido`;
CREATE TABLE `examen_pedido` (
`codigo` int(11) NOT NULL AUTO_INCREMENT,
`fecha` date DEFAULT NULL,
`id_cliente` int(11) DEFAULT NULL,
`id_empleado` int(11) DEFAULT NULL,
`id_producto` int(11) DEFAULT NULL,
`cantidad` int(11) DEFAULT NULL,
PRIMARY KEY (`codigo`),
KEY `id_cliente` (`id_cliente`),
KEY `id_empleado` (`id_empleado`),
KEY `id_producto` (`id_producto`),
CONSTRAINT `fk_cliente2` FOREIGN KEY (`id_cliente`) REFERENCES `examen_cliente` (`codigo`),
CONSTRAINT `fk_empleado2` FOREIGN KEY (`id_empleado`) REFERENCES `examen_empleado` (`codigo`),
CONSTRAINT `fk_producto2` FOREIGN KEY (`id_producto`) REFERENCES `examen_producto` (`codigo`)
) ENGINE=InnoDB AUTO_INCREMENT=1014 DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `examen_producto`;
CREATE TABLE `examen_producto` (
`codigo` int(11) NOT NULL AUTO_INCREMENT,
`nombre` varchar(255) DEFAULT NULL,
`precio` float DEFAULT NULL,
PRIMARY KEY (`codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
A.
select p.codigo as numero_de_pedido,
c.nombre as nombre_cliente,
pr.nombre as nombre_producto,
p.cantidad,
pr.precio,
p.cantidad*pr.precio as total_importe_pedido
from examen_pedido p
inner join examen_cliente c on p.id_cliente=c.codigo
inner join examen_producto pr on p.id_producto=pr.codigo
inner join examen_empleado e on p.id_empleado=e.codigo
B.
select count(*) as numero_de_pedidos,
c.nombre as nombre_cliente,
avg(p.cantidad*pr.precio) as media_importe_pedidos,
sum(p.cantidad*pr.precio) as total_importe_pedidos
from examen_pedido p
inner join examen_cliente c on p.id_cliente=c.codigo
inner join examen_producto pr on p.id_producto=pr.codigo
inner join examen_empleado e on p.id_empleado=e.codigo
group by c.codigo
C.
select count(p.codigo) as numero_de_pedidos,
count(e.codigo) as numero_empleados,
e.ciudad,
avg(p.cantidad*pr.precio) as media_importe_pedidos,
sum(p.cantidad*pr.precio) as total_importe_pedidos
from examen_pedido p
inner join examen_cliente c on p.id_cliente=c.codigo
inner join examen_producto pr on p.id_producto=pr.codigo
inner join examen_empleado e on p.id_empleado=e.codigo
group by e.ciudad having count(e.codigo)>=2 and sum(p.codigo)>=6
D.
select e.*,p.codigo,p.fecha,c.nombre,c.ciudad
from examen_pedido p
inner join examen_cliente c on p.id_cliente=c.codigo
inner join examen_producto pr on p.id_producto=pr.codigo
inner join examen_empleado e on p.id_empleado=e.codigo
where e.ciudad=c.ciudad