Base de datos Hand to hand
INTEGRANTES
Isabela Mejia Arenas
Lina Marcela Marquinez Palomeque
Yorman Alexander Castaño Suarez
CREATE TABLE ventas (
codventa INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
fecha_venta DATE NOT NULL,
cantidad_vendida INT(11) NOT NULL,
coddomicilio INT(11),
codtrabajador INT(11) NOT NULL
);
CREATE TABLE productventa (
codproductventa INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
codventa INT(11) NOT NULL,
codproducto INT(11) NOT NULL
);
CREATE TABLE productos (
codproducto INT(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
codpedido INT(11) NOT NULL,
nombre_producto VARCHAR(60) NOT NULL,
descripcion TEXT NOT NULL,
valor INT(11) NOT NULL,
cantidad INT(11) NOT NULL,
imagen VARCHAR(30) NOT NULL
);
CREATE TABLE domicilios (
coddomicilio INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
coddomiciliario INT(11) NOT NULL,
direccion VARCHAR(25) NOT NULL,
codcliente INT(11) NOT NULL
);
CREATE TABLE clientes (
codcliente INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
nombre_cliente VARCHAR(60) NOT NULL,
telefono_cliente VARCHAR(15) NOT NULL
);
CREATE TABLE domiciliarios (
coddomiciliario INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
nombre_domiciliario VARCHAR(60) NOT NULL,
telefono_domiciliario VARCHAR(15) NOT NULL
);
CREATE TABLE pedidos (
codpedido INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
cantidad INT(11) NOT NULL,
codproveedor INT(11) NOT NULL
);
CREATE TABLE proveedores (
codproveedor INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
nombre_proveedor VARCHAR(25) NOT NULL,
telefono_proveedor VARCHAR(15) NOT NULL,
correo_proveedor VARCHAR(25)
);
CREATE TABLE trabajadores (
codtrabajador INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
nombre_trabajador VARCHAR(25) NOT NULL,
telefono_trabajador VARCHAR(15) NOT NULL,
correo_trabajador VARCHAR(25) NOT NULL
);
ALTER TABLE productventa
ADD CONSTRAINT fk_productventa_venta
FOREIGN KEY (codventa)
REFERENCES ventas (codventa);
ALTER TABLE productventa
ADD CONSTRAINT fk_productventa_producto
FOREIGN KEY (codproducto)
REFERENCES productos (codproducto);
ALTER TABLE productos
ADD CONSTRAINT fk_producto_pedido
FOREIGN KEY (codpedido)
REFERENCES pedidos (codpedido);
ALTER TABLE pedidos
ADD CONSTRAINT fk_pedido_proveedor
FOREIGN KEY (codproveedor)
REFERENCES proveedores (codproveedor);
ALTER TABLE ventas
ADD CONSTRAINT fk_venta_trabajador
FOREIGN KEY (codtrabajador)
REFERENCES trabajadores (codtrabajador);
ALTER TABLE ventas
ADD CONSTRAINT fk_venta_domicilio
FOREIGN KEY (coddomicilio)
REFERENCES domicilios (coddomicilio);
ALTER TABLE domicilios
ADD CONSTRAINT fk_domicilio_cliente
FOREIGN KEY (codcliente)
REFERENCES clientes (codcliente);
ALTER TABLE domicilios
ADD CONSTRAINT fk_venta_domiciliario
FOREIGN KEY (coddomiciliario)
REFERENCES domiciliarios (coddomiciliario);
-- clientes
INSERT INTO clientes (nombre_cliente, telefono_cliente) VALUES
('Juan Perez', '555-1234'),
('Maria Gomez', '555-5678'),
('Carlos Diaz', '555-8765'),
('Ana Lopez', '555-4321'),
('Luis Sanchez', '555-6789'),
('Sofia Martinez', '555-9876'),
('Jorge Ramirez', '555-6543'),
('Elena Torres', '555-3456'),
('Fernando Rojas', '555-7654'),
('Claudia Fuentes', '555-2345'),
('Ricardo Vargas', '555-8764'),
('Carmen Reyes', '555-6542'),
('Pedro Soto', '555-1235'),
('Veronica Ortiz', '555-5679'),
('Julio Hernandez', '555-4322');
-- domiciliarios
INSERT INTO domiciliarios (nombre_domiciliario, telefono_domiciliario) VALUES
('Juan Dominguez', '555-1122'),
('Marcos Fernandez', '555-3344'),
('Diana Castillo', '555-5566'),
('Ricardo Morales', '555-7788'),
('Sandra Ramirez', '555-9900'),
('Esteban Chavez', '555-2211'),
('Carmen Lopez', '555-3322'),
('Jorge Gutierrez', '555-4433'),
('Marta Paredes', '555-5544'),
('Luis Nuñez', '555-6655'),
('Alejandro Blanco', '555-7766'),
('Andrea Estrada', '555-8877'),
('Pablo Cruz', '555-9988'),
('Teresa Soto', '555-1100'),
('Roberto Flores', '555-2233');
--trabajadores
INSERT INTO trabajadores (nombre_trabajador, telefono_trabajador, correo_trabajador) VALUES
('Carlos Hernandez', '555-4455', 'carlos@empresa.com'),
('Laura Martinez', '555-5566', 'laura@empresa.com'),
('Fernando Ortiz', '555-6677', 'fernando@empresa.com'),
('Ana Diaz', '555-7788', 'ana@empresa.com'),
('Pedro Sanchez', '555-8899', 'pedro@empresa.com'),
('Luis Gonzalez', '555-9900', 'luis@empresa.com'),
('Sofia Lopez', '555-1010', 'sofia@empresa.com'),
('Jorge Ramirez', '555-1111', 'jorge@empresa.com'),
('Claudia Torres', '555-1212', 'claudia@empresa.com'),
('Ricardo Vargas', '555-1313', 'ricardo@empresa.com'),
('Carmen Reyes', '555-1414', 'carmen@empresa.com'),
('Veronica Ortiz', '555-1515', 'veronica@empresa.com'),
('Julio Hernandez', '555-1616', 'julio@empresa.com'),
('Elena Torres', '555-1717', 'elena@empresa.com'),
('Juan Perez', '555-1818', 'juan@empresa.com');
-- proveedores
INSERT INTO proveedores (nombre_proveedor, telefono_proveedor, correo_proveedor) VALUES
('Proveedor A', '555-2020', '1@proveedor.com'),
('Proveedor B', '555-2121', '2@proveedor.com'),
('Proveedor C', '555-2222', '3@proveedor.com'),
('Proveedor D', '555-2323', '4@proveedor.com'),
('Proveedor E', '555-2424', '5@proveedor.com'),
('Proveedor F', '555-2525', '6@proveedor.com'),
('Proveedor G', '555-2626', '7@proveedor.com'),
('Proveedor H', '555-2727', '8@proveedor.com'),
('Proveedor I', '555-2828', '9@proveedor.com'),
('Proveedor J', '555-2929', '10@proveedor.com'),
('Proveedor K', '555-3030', '11@proveedor.com'),
('Proveedor L', '555-3131', '12@proveedor.com'),
('Proveedor M', '555-3232', '13@proveedor.com'),
('Proveedor N', '555-3333', '14@proveedor.com'),
('Proveedor O', '555-3434', '15@proveedor.com');
-- pedidos
INSERT INTO pedidos (cantidad, codproveedor) VALUES
(10, 1),
(20, 2),
(15, 3),
(30, 4),
(25, 5),
(5, 6),
(40, 7),
(35, 8),
(50, 9),
(45, 10),
(10, 11),
(20, 12),
(15, 13),
(30, 14),
(25, 15);
-- productos
INSERT INTO productos (codpedido, nombre_producto, descripcion, valor, cantidad, imagen) VALUES
(1, 'Producto 1', 'Descripcion 1', 100, 10, 'img1.jpg'),
(2, 'Producto 2', 'Descripcion 2', 200, 20, 'img2.jpg'),
(3, 'Producto 3', 'Descripcion 3', 300, 30, 'img3.jpg'),
(4, 'Producto 4', 'Descripcion 4', 400, 40, 'img4.jpg'),
(5, 'Producto 5', 'Descripcion 5', 500, 50, 'img5.jpg'),
(6, 'Producto 6', 'Descripcion 6', 600, 60, 'img6.jpg'),
(7, 'Producto 7', 'Descripcion 7', 700, 70, 'img7.jpg'),
(8, 'Producto 8', 'Descripcion 8', 800, 80, 'img8.jpg'),
(9, 'Producto 9', 'Descripcion 9', 900, 90, 'img9.jpg'),
(10, 'Producto 10', 'Descripcion 10', 1000, 100, 'img10.jpg'),
(11, 'Producto 11', 'Descripcion 11', 1100, 110, 'img11.jpg'),
(12, 'Producto 12', 'Descripcion 12', 1200, 120, 'img12.jpg'),
(13, 'Producto 13', 'Descripcion 13', 1300, 130, 'img13.jpg'),
(14, 'Producto 14', 'Descripcion 14', 1400, 140, 'img14.jpg'),
(15, 'Producto 15', 'Descripcion 15', 1500, 150, 'img15.jpg');
-- ventas
INSERT INTO ventas (fecha_venta, cantidad_vendida, coddomicilio, codtrabajador) VALUES
('2024-01-01', 5, 1, 1),
('2024-01-02', 10, 2, 2),
('2024-01-03', 15, 3, 3),
('2024-01-04', 20, 4, 4),
('2024-01-05', 25, 5, 5),
('2024-01-06', 30, 6, 6),
('2024-01-07', 35, 7, 7),
('2024-01-08', 40, 8, 8),
('2024-01-09', 45, 9, 9),
('2024-01-10', 50, 10, 10),
('2024-01-11', 55, 11, 11),
('2024-01-12', 60, 12, 12),
('2024-01-13', 65, 13, 13),
('2024-01-14', 70, 14, 14),
('2024-01-15', 75, 15, 15);
-- productventa
INSERT INTO productventa (codventa, codproducto) VALUES
(1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 5),
(6, 6),
(7, 7),
(8, 8),
(9, 9),
(10, 10),
(11, 11),
(12, 12),
(13, 13),
(14, 14),
(15, 15);
--domicilios
INSERT INTO domicilios (direccion, codcliente,coddomiciliario) VALUES
('Calle 1', 1,2),
('Calle 2', 2,3),
('Calle 3', 3,5),
('Calle 4', 4,2),
('Calle 5', 5,6),
('Calle 6', 6,7),
('Calle 7', 7,9),
('Calle 8', 8,1),
('Calle 9', 9,13),
('Calle 10', 10,11),
('Calle 11', 11,14),
('Calle 12', 12,15),
('Calle 13', 13,8),
('Calle 14', 14,4),
('Calle 15', 15,5);
CONSULTAS
Nota
Las que no presentan imagen no dieron resultados, pero su codigo no presenta error
--Productos vendidos por cada trabajador--
SELECT t.nombre_trabajador, SUM(v.cantidad_vendida) as total_vendido
FROM ventas v
INNER JOIN trabajadores t ON v.codtrabajador = t.codtrabajador
GROUP BY
t.nombre_trabajador;
--Ventas y saber quien las vendio y entrego—
SELECT v.codventa, v.fecha_venta, v.cantidad_vendida, t.nombre_trabajador, d.nombre_domiciliario
FROM ventas v
INNER JOIN trabajadores t ON v.codtrabajador = t.codtrabajador
LEFT JOIN domiciliarios d ON v.coddomiciliario = d.coddomiciliario;
--ganacias de un dia especifico—
SELECT v.fecha_venta, SUM(p.valor * pv.cantidad) as total_ganancias
FROM ventas v
INNER JOIN productventa pv ON v.codventa = pv.codventa
INNER JOIN productos p ON pv.codproducto = p.codproducto
WHERE v.fecha_venta = '2024-05-01'
GROUP BY v.fecha_venta;
--Cuantos productos han entregado los domiciliarios—
SELECT d.nombre_domiciliario, COUNT(pv.codproducto) as total_productos_entregados
FROM domiciliarios d
INNER JOIN ventas v ON d.coddomiciliario = v.coddomiciliario
INNER JOIN productventa pv ON v.codventa = pv.codventa
GROUP BY d.coddomiciliario;
--cuantos clientes han pedido un mismo producto—
SELECT p.nombre_producto, COUNT(DISTINCT c.codcliente) as total_clientes
FROM productos p
INNER JOIN productventa pv ON p.codproducto = pv.codproducto
INNER JOIN ventas v ON pv.codventa = v.codventa
INNER JOIN domicilios d ON v.coddomiciliario = d.coddomiciliario
INNER JOIN clientes c ON dm.codcliente = c.codcliente
GROUP BY p.nombre_producto;
--cuantas ventas a entregado un domiciliario—
SELECT d.nombre_domiciliario, COUNT(v.codventa) as total_ventas_entregadas
FROM domiciliarios d
JOIN ventas v ON d.coddomiciliario = v.coddomiciliario
GROUP BY d.coddomiciliario;
--cuantos pedidos se tiene de un proveedor—
SELECT pd.nombre_proveedor, COUNT(r.codpedido) as total_pedidos
FROM proveedores pd
JOIN pedidos p ON pr.codproveedor = pd.codproveedor
GROUP BY pd.codproveedor;
--Cuantos productos se han vendido en un dia—
SELECT cantidad_vendida AS productos_vendidos
FROM ventas
WHERE fecha LIKE “2024-01-02”
--que cliente ha pedido mas productos en un dia—
SELECT c.nombre_cliente, v.fecha_venta, SUM(v.cantidad_vendida) AS total_productos
FROM clientes c
INNER JOIN domicilios d ON c.codcliente = d.codcliente
INNER JOIN ventas v ON d.coddomicilio = v.coddomicilio
WHERE v.fecha_venta = '2024-01-01'
GROUP BY c.nombre_cliente, v.fecha_venta
ORDER BY total_productos DESC
LIMIT 1;
--Contar cuantos productos se han vendido—
SELECT sum(v.cantidad_vendida) AS cantidad_vendida, p.nombre_producto AS producto_vendido
FROM ventas v
INNER JOIN productventa pv ON (pv.id_venta = v.id_venta)
INNER JOIN productos p ON (pv.id_producto = p.id_producto)
GROUP BY p.nombre_producto;
--Productos con menos stock—
SELECT nombre_producto, cantidad
FROM productos
ORDER BY cantidad ASC
Limit 5;
--Productos con mas stock--
SELECT nombre_producto, cantidad
FROM productos
ORDER BY cantidad desc
Limit 5;
--Productos los cuales no se han vendido—
SELECT nombre_producto
FROM productos
WHERE id_producto NOT IN ( SELECT id_producto FROM productventa);
--Saber la cantidad de productos en stock--
select sum(cantidad) as Cantidad_total_productos
from Productos