-- Base de Datos: Librería
-- Creación de tablas con tipos de datos y relaciones
-- Tabla Clientes
CREATE TABLE Clientes (
Cliente_ID INT AUTO_INCREMENT PRIMARY KEY,
Nombre VARCHAR(100) NOT NULL,
Apellido VARCHAR(100) NOT NULL,
Direccion VARCHAR(200),
Ciudad VARCHAR(100),
Estado VARCHAR(50),
Codigo_Postal VARCHAR(20)
);
-- Tabla Libros
CREATE TABLE Libros (
Libro_ID INT AUTO_INCREMENT PRIMARY KEY,
Titulo VARCHAR(200) NOT NULL,
Nombre_Autor VARCHAR(100) NOT NULL,
Apellido_Autor VARCHAR(100) NOT NULL,
Categoria VARCHAR(100),
Precio DECIMAL(10,2) NOT NULL
);
-- Tabla Ordenes
CREATE TABLE Ordenes (
Orden_ID INT AUTO_INCREMENT PRIMARY KEY,
Cliente_ID INT NOT NULL,
Libro_ID INT NOT NULL,
Fecha_de_Orden DATE NOT NULL,
FOREIGN KEY (Cliente_ID) REFERENCES Clientes(Cliente_ID),
FOREIGN KEY (Libro_ID) REFERENCES Libros(Libro_ID)
);
-- INSERTS DE DATOS DE EJEMPLO
-- Insertar Clientes
INSERT INTO Clientes (Nombre, Apellido, Direccion, Ciudad, Estado, Codigo_Postal) VALUES
('Carlos', 'Mendoza', 'Avenida Reforma 15-45', 'Guatemala', 'Guatemala', '01001'),
('María', 'Rodríguez', 'Calle Principal 23', 'Antigua Guatemala', 'Sacatepéquez', '03001'),
('José', 'López', 'Zona 4, 8va Calle', 'Chimaltenango', 'Chimaltenango', '04001'),
('Ana', 'García', 'Barrio El Centro 12', 'Quetzaltenango', 'Quetzaltenango', '09001'),
('Roberto', 'Morales', 'Avenida Central 67', 'Escuintla', 'Escuintla', '05001');
-- Insertar Libros
INSERT INTO Libros (Titulo, Nombre_Autor, Apellido_Autor, Categoria, Precio) VALUES
('Cien Años de Soledad', 'Gabriel', 'García Márquez', 'Literatura', 125.50),
('El Quijote de la Mancha', 'Miguel', 'de Cervantes', 'Clásicos', 89.75),
('1984', 'George', 'Orwell', 'Distopía', 95.00),
('Rayuela', 'Julio', 'Cortázar', 'Literatura', 110.25),
('El Principito', 'Antoine', 'de Saint-Exupéry', 'Infantil', 65.00);
-- Insertar Ordenes
INSERT INTO Ordenes (Cliente_ID, Libro_ID, Fecha_de_Orden) VALUES
(1, 1, '2024-07-15'),
(2, 3, '2024-07-16'),
(3, 2, '2024-07-16'),
(4, 5, '2024-07-17'),
(5, 4, '2024-07-17');
-- Consultas de ejemplo para verificar las relaciones
-- Consulta completa con todas las relaciones
SELECT
o.Orden_ID,
o.Fecha_de_Orden,
CONCAT(c.Nombre, ' ', c.Apellido) AS Cliente,
c.Ciudad,
l.Titulo,
CONCAT(l.Nombre_Autor, ' ', l.Apellido_Autor) AS Autor,
l.Categoria,
l.Precio
FROM Ordenes o
JOIN Clientes c ON o.Cliente_ID = c.Cliente_ID
JOIN Libros l ON o.Libro_ID = l.Libro_ID
ORDER BY o.Fecha_de_Orden DESC;
-- Consulta de clientes con sus órdenes
SELECT
c.Cliente_ID,
CONCAT(c.Nombre, ' ', c.Apellido) AS Cliente,
c.Ciudad,
COUNT(o.Orden_ID) AS Total_Ordenes
FROM Clientes c
LEFT JOIN Ordenes o ON c.Cliente_ID = o.Cliente_ID
GROUP BY c.Cliente_ID, c.Nombre, c.Apellido, c.Ciudad;
-- Consulta de libros más vendidos
SELECT
l.Libro_ID,
l.Titulo,
CONCAT(l.Nombre_Autor, ' ', l.Apellido_Autor) AS Autor,
l.Categoria,
l.Precio,
COUNT(o.Orden_ID) AS Veces_Vendido
FROM Libros l
LEFT JOIN Ordenes o ON l.Libro_ID = o.Libro_ID
GROUP BY l.Libro_ID, l.Titulo, l.Nombre_Autor, l.Apellido_Autor, l.Categoria, l.Precio
ORDER BY Veces_Vendido DESC;
-- Consulta de ventas por categoría
SELECT
l.Categoria,
COUNT(o.Orden_ID) AS Total_Ventas,
SUM(l.Precio) AS Total_Ingresos,
AVG(l.Precio) AS Precio_Promedio
FROM Libros l
JOIN Ordenes o ON l.Libro_ID = o.Libro_ID
GROUP BY l.Categoria
ORDER BY Total_Ingresos DESC;
-- Consulta de órdenes por fecha
SELECT
o.Fecha_de_Orden,
COUNT(o.Orden_ID) AS Total_Ordenes,
SUM(l.Precio) AS Total_Ventas
FROM Ordenes o
JOIN Libros l ON o.Libro_ID = l.Libro_ID
GROUP BY o.Fecha_de_Orden
ORDER BY o.Fecha_de_Orden DESC;