6_4_Josemaria_proyecto

-- ======================================================
-- PROYECTO: MANEJO DE BASES DE DATOS CON SQL
-- TEMA: GESTIÓN DE VENTAS TECNOLÓGICAS
-- INTEGRANTES: Josemaria Perez Manriquez
Antonio Vazquez Ruiz
    Pedro Pulido Balderas
    Diego Figueroa Azuara
-- ======================================================

-- 1. CREACIÓN DE LAS TABLAS (Requisito B)

CREATE TABLE Categorias (
    id_categoria INT PRIMARY KEY,
    nombre_categoria VARCHAR(50) NOT NULL
);

CREATE TABLE Productos (
    id_producto INT PRIMARY KEY,
    nombre_producto VARCHAR(100) NOT NULL,
    precio DECIMAL(10, 2) NOT NULL,
    id_categoria INT,
    FOREIGN KEY (id_categoria) REFERENCES Categorias(id_categoria)
);

CREATE TABLE Clientes (
    id_cliente INT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    ciudad VARCHAR(50)
);

CREATE TABLE Ventas (
    id_venta INT PRIMARY KEY,
    id_cliente INT,
    id_producto INT,
    fecha_venta DATE,
    cantidad INT,
    FOREIGN KEY (id_cliente) REFERENCES Clientes(id_cliente),
    FOREIGN KEY (id_producto) REFERENCES Productos(id_producto)
);

-- 2. INSERCIÓN DE DATOS (10 registros por tabla - Requisito B)

INSERT INTO Categorias VALUES 
(1, 'Laptops'), (2, 'Audio'), (3, 'Accesorios'), (4, 'Monitores'), (5, 'Teclados'),
(6, 'Mouse'), (7, 'Cámaras'), (8, 'Almacenamiento'), (9, 'Redes'), (10, 'Software');

INSERT INTO Productos VALUES 
(1, 'MacBook Pro', 2500.00, 1), (2, 'AirPods Pro', 250.00, 2), (3, 'Cable HDMI', 15.00, 3),
(4, 'Monitor 4K', 400.00, 4), (5, 'Teclado Mecánico', 120.00, 5), (6, 'Mouse Gamer', 80.00, 6),
(7, 'Webcam HD', 60.00, 7), (8, 'Disco SSD 1TB', 110.00, 8), (9, 'Router WiFi 6', 150.00, 9),
(10, 'Antivirus 1 año', 50.00, 10);

INSERT INTO Clientes VALUES 
(1, 'Juan Perez', 'juan@mail.com', 'CDMX'), (2, 'Maria Lopez', 'maria@mail.com', 'Guadalajara'),
(3, 'Carlos Ruiz', 'carlos@mail.com', 'Monterrey'), (4, 'Ana Garcia', 'ana@mail.com', 'CDMX'),
(5, 'Luis Smith', 'luis@mail.com', 'Cancun'), (6, 'Elena Gomez', 'elena@mail.com', 'Puebla'),
(7, 'Pedro Paez', 'pedro@mail.com', 'CDMX'), (8, 'Sofia Lara', 'sofia@mail.com', 'Merida'),
(9, 'Diego Sosa', 'diego@mail.com', 'Leon'), (10, 'Laura Vigo', 'laura@mail.com', 'Monterrey');

INSERT INTO Ventas VALUES 
(1, 1, 1, '2024-04-01', 1), (2, 2, 2, '2024-04-02', 2), (3, 3, 4, '2024-04-03', 1),
(4, 4, 1, '2024-04-04', 1), (5, 5, 8, '2024-04-05', 3), (6, 6, 5, '2024-04-06', 1),
(7, 7, 3, '2024-04-07', 5), (8, 8, 9, '2024-04-08', 1), (9, 9, 6, '2024-04-09', 2),
(10, 10, 10, '2024-04-10', 1);

-- 3. CONSULTAS SQL (Requisito C)

-- C.1: 5 Consultas de una sola tabla
SELECT * FROM Productos WHERE precio > 100;
SELECT nombre, ciudad FROM Clientes WHERE ciudad = 'CDMX';
SELECT nombre_producto FROM Productos ORDER BY precio DESC;
SELECT * FROM Ventas WHERE cantidad >= 2;
SELECT nombre_categoria FROM Categorias WHERE id_categoria < 5;

-- C.2: 10 Consultas multitabla (Joins)
-- 1. Mostrar ventas con nombre de cliente y nombre de producto
SELECT V.id_venta, C.nombre, P.nombre_producto FROM Ventas V JOIN Clientes C ON V.id_cliente = C.id_cliente JOIN Productos P ON V.id_producto = P.id_producto;
-- 2. Productos y sus categorías
SELECT P.nombre_producto, Cat.nombre_categoria FROM Productos P JOIN Categorias Cat ON P.id_categoria = Cat.id_categoria;
-- 3. Ventas realizadas por clientes de 'Monterrey'
SELECT V.*, C.ciudad FROM Ventas V JOIN Clientes C ON V.id_cliente = C.id_cliente WHERE C.ciudad = 'Monterrey';
-- 4. Total gastado por cada venta (Precio * Cantidad)
SELECT V.id_venta, P.nombre_producto, (P.precio * V.cantidad) AS Total FROM Ventas V JOIN Productos P ON V.id_producto = P.id_producto;
-- 5. Listar productos de la categoría 'Audio'
SELECT P.nombre_producto FROM Productos P JOIN Categorias Cat ON P.id_categoria = Cat.id_categoria WHERE Cat.nombre_categoria = 'Audio';
-- 6. Clientes que compraron 'MacBook Pro'
SELECT DISTINCT C.nombre FROM Clientes C JOIN Ventas V ON C.id_cliente = V.id_cliente JOIN Productos P ON V.id_producto = P.id_producto WHERE P.nombre_producto = 'MacBook Pro';
-- 7. Cantidad total de productos vendidos por categoría
SELECT Cat.nombre_categoria, SUM(V.cantidad) FROM Ventas V JOIN Productos P ON V.id_producto = P.id_producto JOIN Categorias Cat ON P.id_categoria = Cat.id_categoria GROUP BY Cat.nombre_categoria;
-- 8. Email de clientes que compraron más de 2 unidades de cualquier cosa
SELECT C.email, V.cantidad FROM Clientes C JOIN Ventas V ON C.id_cliente = V.id_cliente WHERE V.cantidad > 2;
-- 9. Mostrar productos y su precio que han sido vendidos en abril
SELECT P.nombre_producto, P.precio, V.fecha_venta FROM Productos P JOIN Ventas V ON P.id_producto = V.id_producto WHERE V.fecha_venta LIKE '2024-04%';
-- 10. Nombre de cliente y categoría del producto que compró
SELECT C.nombre, Cat.nombre_categoria FROM Clientes C JOIN Ventas V ON C.id_cliente = V.id_cliente JOIN Productos P ON V.id_producto = P.id_producto JOIN Categorias Cat ON P.id_categoria = Cat.id_categoria;