Una empresa de fabricación de tintas nos ha pedido una base de datos.
La empresa necesita guardar información de sus clientes, concretamente el NIF y la razón social. Los clientes hacen pedidos, de los que queremos saber la fecha y la dirección de envío (calle, cp y población).
Los pedidos pueden ser de varios productos. De los productos queremos guardar la referencia, el precio y el stock que tenemos en almacén.
Por otro lado los productos pueden ser productos básicos o fabricados, los fabricados se hacen a partir de otros productos (que pueden ser base o fabricados). Necesitamos guardar la composición de los productos fabricados que incluye los productos, la cantidad de cada uno y el orden en el que hay que añadirlos.
Datos de prueba:
Clientes:
Sugus, nif b1234354
Grefusa, nif b75756756
S&D, nif b66612333
Productos:
Disolvente, 1 €, 2000
Emulsionante, 3 €, 1500
Colorante rojo, 4 €, 200
Colorante azul, 4 €, 300
Colorante amarillo, 4 €, 250
Tinta naranja, 10 €, 100
Tinta verde, 10 €, 100
La tinta naranja se compone en este orden de
Disolvente 5
Colorante rojo 7
Colorante amarillo 10
Emulsionante 10
La tinta verde se compone en este orden de
Disolvente 5
Colorante azul 7
Colorante amarillo 10
Emulsionante 10
Sugus hizo un pedido de 10 unidades de tinta verde el 2 de febrero para enviar a la calle Agla,5, 08001 Barcelona.
Sugus hizo un pedido de 10 unidades de tinta naranja el 5 de febrero para enviar a la calle Agla,5, 08001 Barcelona.
S&D hizo un pedido de 10 unidades de disolvente y 5 de colorante rojo el 10 de febrero para enviar al carrer perill, 15, 08012 Barcelona.
Se pide:
Insertar el cliente ‘Copistería Martínez’, ‘B21312312’
Modificar el precio de todos los productos para incrementar un 10%
Añadir 10 a la cantidad de la composición de todos los productos
¿Qué productos han comprado los clientes que tienen una ‘u’ en el nombre?
Añade el producto ‘aerosol rojo’, 10 €, 20
Elimina los productos cuyo nombre contenga aerosol
¿Qué clientes no tienen pedidos?
Muestra el total de ventas por cliente, incluyendo los que no tienen pedidos
Muestra el total de unidades vendidas por producto, incluyendo los que no tienen ninguna unidad vendidas
Lista de productos finales
Lista de productos que no son finales
Clientes que nunca han comprado un producto que no sea finales
CREATE TABLE `tintas`.`cliente` (
`idcliente` INT NOT NULL AUTO_INCREMENT,
`nif` VARCHAR(45) NOT NULL,
`razon_social` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idcliente`) );
CREATE TABLE `tintas`.`pedido` (
`idpedido` INT NOT NULL AUTO_INCREMENT,
`idcliente` INT NOT NULL,
`fecha` DATE NOT NULL,
`direccion` VARCHAR(45) NOT NULL,
`cp` VARCHAR(45) NOT NULL,
`poblacion` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idpedido`) );
CREATE TABLE `tintas`.`producto` (
`idproducto` INT NOT NULL AUTO_INCREMENT,
`referencia` VARCHAR(45) NOT NULL,
`precio` DECIMAL(10,2) NOT NULL,
`stock` INT NOT NULL,
PRIMARY KEY (`idproducto`) );
CREATE TABLE `tintas`.`lineaspedido` (
`idlineaspedido` INT NOT NULL AUTO_INCREMENT,
`idpedido` INT NOT NULL,
`idproducto` INT NOT NULL,
`cantidad` INT NOT NULL,
`precio` DECIMAL(10,2) NOT NULL,
PRIMARY KEY (`idlineaspedido`) );
CREATE TABLE `composicion` (
`idcomposicion` int(11) NOT NULL AUTO_INCREMENT,
`idproducto_final` int(11) NOT NULL,
`idproducto_comp` int(11) NOT NULL,
`cantidad` int(11) NOT NULL,
`orden` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`idcomposicion`)
)
ALTER TABLE `tintas`.`pedido`
ADD INDEX `fk_cliente_idx` (`idcliente` ASC) ;
ALTER TABLE `tintas`.`pedido`
ADD CONSTRAINT `fk_cliente`
FOREIGN KEY (`idcliente`)
REFERENCES `tintas`.`cliente` (`idcliente`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;
ALTER TABLE `tintas`.`lineaspedido`
ADD INDEX `fk_pedido_idx` (`idpedido` ASC) ,
ADD INDEX `fk_producto_idx` (`idproducto` ASC) ;
ALTER TABLE `tintas`.`lineaspedido`
ADD CONSTRAINT `fk_pedido`
FOREIGN KEY (`idpedido`)
REFERENCES `tintas`.`pedido` (`idpedido`)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
ADD CONSTRAINT `fk_producto`
FOREIGN KEY (`idproducto`)
REFERENCES `tintas`.`producto` (`idproducto`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;
ALTER TABLE `tintas`.`composicion`
ADD INDEX `fk_productof_idx` (`idproducto_final` ASC) ,
ADD INDEX `fk_productoc_idx` (`idproducto_comp` ASC) ;
ALTER TABLE `tintas`.`composicion`
ADD CONSTRAINT `fk_productof`
FOREIGN KEY (`idproducto_final`)
REFERENCES `tintas`.`producto` (`idproducto`)
ON DELETE RESTRICT
ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_productoc`
FOREIGN KEY (`idproducto_comp`)
REFERENCES `tintas`.`producto` (`idproducto`)
ON DELETE RESTRICT
ON UPDATE NO ACTION;
INSERT INTO `tintas`.`cliente` (`nif`, `razon_social`) VALUES ('b1234354', 'sugus');
INSERT INTO `tintas`.`cliente` (`nif`, `razon_social`) VALUES ('b75756756', 'grefusa');
INSERT INTO `tintas`.`cliente` (`nif`, `razon_social`) VALUES ('b66612333', 's&D');
INSERT INTO `tintas`.`producto` (`referencia`, `precio`, `stock`) VALUES ('Disolvente', '1', '2000');
INSERT INTO `tintas`.`producto` (`referencia`, `precio`, `stock`) VALUES ('Emulsionante', '3', '1500');
INSERT INTO `tintas`.`producto` (`referencia`, `precio`, `stock`) VALUES ('Colorante rojo', '4', '200');
INSERT INTO `tintas`.`producto` (`referencia`, `precio`, `stock`) VALUES ('Colorante azul', '4', '300');
INSERT INTO `tintas`.`producto` (`referencia`, `precio`, `stock`) VALUES ('Colorante amarillo', '4', '250');
INSERT INTO `tintas`.`producto` (`referencia`, `precio`, `stock`) VALUES ('Tinta naranja', '10', '100');
INSERT INTO `tintas`.`producto` (`referencia`, `precio`, `stock`) VALUES ('Tinta verde', '10', '100');
INSERT INTO `tintas`.`pedido` (`fecha`, `direccion`, `cp`, `poblacion`, `idcliente`) VALUES ('2017-2-2', 'Agla, 5', '08001', 'Barcelona', '1');
INSERT INTO `tintas`.`pedido` (`fecha`, `direccion`, `cp`, `poblacion`, `idcliente`) VALUES ('2017-2-5', 'Agla, 5', '08001', 'Barcelona', '1');
INSERT INTO `tintas`.`pedido` (`fecha`, `direccion`, `cp`, `poblacion`, `idcliente`) VALUES ('2017-2-10', 'Perill, 15', '08012', 'Barcelona',3);
INSERT INTO `tintas`.`lineaspedido` (`idpedido`, `idproducto`, `cantidad`, `precio`) VALUES ('1', '7', '10', '10');
INSERT INTO `tintas`.`lineaspedido` (`idpedido`, `idproducto`, `cantidad`, `precio`) VALUES ('2', '6', '10', '10');
INSERT INTO `tintas`.`lineaspedido` (`idpedido`, `idproducto`, `cantidad`, `precio`) VALUES ('3', '1', '10', '3');
INSERT INTO `tintas`.`lineaspedido` (`idpedido`, `idproducto`, `cantidad`, `precio`) VALUES ('3', '3', '5', '4');
INSERT INTO `tintas`.`composicion` (`idproducto_final`, `idproducto_comp`, `cantidad`, `orden`) VALUES ('6', '1', '5', '1');
INSERT INTO `tintas`.`composicion` (`idproducto_final`, `idproducto_comp`, `cantidad`, `orden`) VALUES ('6', '3', '7', '2');
INSERT INTO `tintas`.`composicion` (`idproducto_final`, `idproducto_comp`, `cantidad`, `orden`) VALUES ('6', '5', '10', '3');
INSERT INTO `tintas`.`composicion` (`idproducto_final`, `idproducto_comp`, `cantidad`, `orden`) VALUES ('6', '2', '10', '4');
INSERT INTO `tintas`.`composicion` (`idproducto_final`, `idproducto_comp`, `cantidad`, `orden`) VALUES ('7', '1', '5', '1');
INSERT INTO `tintas`.`composicion` (`idproducto_final`, `idproducto_comp`, `cantidad`, `orden`) VALUES ('7', '4', '7', '2');
INSERT INTO `tintas`.`composicion` (`idproducto_final`, `idproducto_comp`, `cantidad`, `orden`) VALUES ('7', '5', '10', '3');
INSERT INTO `tintas`.`composicion` (`idproducto_final`, `idproducto_comp`, `cantidad`, `orden`) VALUES ('7', '2', '10', '4');
select * from cliente join pedido using(idcliente)
join lineaspedido using (idpedido)
join producto using (idproducto)
select * from producto
join composicion on producto.idproducto=composicion.idproducto_final
join producto p2 on composicion.idproducto_comp=p2.idproducto
Insertar el cliente 'Copistería Martínez', 'B21312312'
INSERT INTO `tintas`.`cliente` (`nif`, `razon_social`) VALUES ('B21312312', 'Copistería Martínez');
Modificar el precio de todos los productos para incrementar un 10%
update producto
set precio=precio*1.1
Añadir 10 a la cantidad de la composición de todos los productos
update tintas.composicion
set cantidad=cantidad+10
Añade el producto 'aerosol rojo', 10 €, 20
INSERT INTO `tintas`.`producto` (`referencia`, `precio`, `stock`) VALUES ('Aerosol rojo', '10', '20');
Elimina los productos cuyo nombre contenga aerosol
delete FROM tintas.producto
where referencia like '%aerosol%';
¿Qué productos han comprado los clientes que tienen una 'u' en el nombre?
select distinct razon_social, referencia from cliente join pedido using(idcliente)
join lineaspedido using (idpedido)
join producto using (idproducto)
where razon_social like '%u%'
¿Qué clientes no tienen pedidos?
SELECT * FROM tintas.cliente left join pedido using (idcliente) where idpedido is null;
Muestra el total de ventas por cliente, incluyendo los que no tienen pedidos
select razon_social, ifnull(sum(cantidad*precio),0) total from cliente left join pedido using(idcliente)
left join lineaspedido using (idpedido)
group by razon_social
Muestra el total de unidades vendidas por producto, incluyendo los que no tienen ninguna unidad vendidas
select referencia, ifnull(sum(cantidad),0) total
from producto left join lineaspedido using(idproducto)
group by referencia
Lista de productos finales
select referencia from producto
left join composicion on producto.idproducto=composicion.idproducto_comp
where idcomposicion is null
Lista de productos que no son finales
select distinct referencia from producto
join composicion on producto.idproducto=composicion.idproducto_comp
Clientes que nunca han comprado un producto que no sea finales
select * from cliente
where idcliente not in (
select distinct idcliente
from cliente left join pedido using(idcliente)
left join lineaspedido using (idpedido)
where idproducto in (select distinct idproducto from producto
join composicion on producto.idproducto=composicion.idproducto_comp
)
)