📘 Lección 19: Proyecto — Base de datos de biblioteca

🔹 Introducción

Ya aprendiste a:

• Seleccionar datos con SELECT.
• Filtrar con WHERE.
• Ordenar con ORDER BY.
• Usar funciones de agregación y GROUP BY.
• Combinar tablas con JOIN.
• Crear y modificar tablas (CREATE TABLE, ALTER TABLE).
• Insertar, actualizar y borrar registros (INSERT, UPDATE, DELETE).
• Usar transacciones y funciones avanzadas.

Ahora pondrás en práctica todo esto construyendo un proyecto realista:
📚 Una base de datos para una biblioteca.

🔹 Paso 1: Crear las tablas

Primero definimos 3 tablas principales:

1. Autores → quién escribió cada libro.
2. Libros → catálogo de la biblioteca.
3. Prestamos → quién pidió prestado un libro y cuándo.

-- Tabla de autores
CREATE TABLE autores (
  id INT PRIMARY KEY,
  nombre VARCHAR(100),
  nacionalidad VARCHAR(50)
);

-- Tabla de libros
CREATE TABLE libros (
  id INT PRIMARY KEY,
  titulo VARCHAR(150),
  id_autor INT,
  anio_publicacion INT,
  disponible BOOLEAN,
  FOREIGN KEY (id_autor) REFERENCES autores(id)
);

-- Tabla de préstamos
CREATE TABLE prestamos (
  id INT PRIMARY KEY,
  id_libro INT,
  nombre_usuario VARCHAR(100),
  fecha_prestamo DATE,
  fecha_devolucion DATE,
  FOREIGN KEY (id_libro) REFERENCES libros(id)
);

🔹 Paso 2: Insertar datos de ejemplo

-- Autores
INSERT INTO autores (id, nombre, nacionalidad) VALUES
(1, 'Gabriel García Márquez', 'Colombiana'),
(2, 'Miguel de Cervantes', 'Española'),
(3, 'Jane Austen', 'Británica');

-- Libros
INSERT INTO libros (id, titulo, id_autor, anio_publicacion, disponible) VALUES
(1, 'Cien años de soledad', 1, 1967, TRUE),
(2, 'El Quijote', 2, 1605, FALSE),
(3, 'Orgullo y prejuicio', 3, 1813, TRUE);

-- Préstamos
INSERT INTO prestamos (id, id_libro, nombre_usuario, fecha_prestamo, fecha_devolucion) VALUES
(1, 2, 'Laura', '2025-08-01', NULL),
(2, 1, 'Pedro', '2025-07-15', '2025-07-30');

🔹 Paso 3: Consultas prácticas

Ejemplo 1: ver todos los libros y sus autores

SELECT l.titulo, a.nombre AS autor
FROM libros l
INNER JOIN autores a ON l.id_autor = a.id;

📊 Resultado esperado:

Ejemplo 2: libros actualmente prestados

SELECT l.titulo, p.nombre_usuario, p.fecha_prestamo
FROM prestamos p
INNER JOIN libros l ON p.id_libro = l.id
WHERE p.fecha_devolucion IS NULL;

📊 Resultado esperado:

Ejemplo 3: contar cuántos libros escribió cada autor

SELECT a.nombre AS autor, COUNT(l.id) AS total_libros
FROM autores a
LEFT JOIN libros l ON a.id = l.id_autor
GROUP BY a.nombre;

📊 Resultado esperado:

Ejemplo 4: ranking de usuarios con más préstamos

SELECT nombre_usuario, COUNT(*) AS cantidad_prestamos
FROM prestamos
GROUP BY nombre_usuario
ORDER BY cantidad_prestamos DESC;

📊 Resultado esperado:

🔹 Paso 4: Ejercicio práctico

Ahora es tu turno.

1. Muestra los libros disponibles en la biblioteca (disponible = TRUE).
2. Lista los préstamos con nombre de usuario y autor del libro.
3. Obtén el año de publicación más antiguo y el más reciente.
4. Cuenta cuántos libros hay de cada nacionalidad de autor.
5. Agrega un nuevo préstamo de «Orgullo y prejuicio» para el usuario «Carlos» en la fecha de hoy.

🔹 Soluciones

-- 1. Libros disponibles
SELECT titulo FROM libros WHERE disponible = TRUE;

-- 2. Préstamos con autor
SELECT p.nombre_usuario, l.titulo, a.nombre AS autor
FROM prestamos p
INNER JOIN libros l ON p.id_libro = l.id
INNER JOIN autores a ON l.id_autor = a.id;

-- 3. Año más antiguo y más reciente
SELECT MIN(anio_publicacion) AS mas_antiguo, MAX(anio_publicacion) AS mas_reciente
FROM libros;

-- 4. Libros por nacionalidad
SELECT a.nacionalidad, COUNT(l.id) AS cantidad_libros
FROM autores a
INNER JOIN libros l ON a.id = l.id_autor
GROUP BY a.nacionalidad;

-- 5. Nuevo préstamo
INSERT INTO prestamos (id, id_libro, nombre_usuario, fecha_prestamo, fecha_devolucion)
VALUES (3, 3, 'Carlos', NOW(), NULL);

🔹 Mini Quiz

🔹 Resumen de la lección

• Creamos un proyecto real de biblioteca con 3 tablas relacionadas.
• Practicamos JOIN, GROUP BY, condiciones y agregaciones.
• Aprendiste a construir consultas para casos reales: libros disponibles, préstamos activos, ranking de usuarios.

👉 En la próxima lección (Examen final práctico) vas a resolver un conjunto de consultas integradoras sobre esta base de datos para comprobar todo lo que aprendiste.

🖇️ Conoce la estructura completa del curso visitando la página principal del Curso SQL.