Curso Básico y Avanzado de SQL

Introducción

Este curso está diseñado para principiantes y usuarios intermedios que desean dominar SQL usando SQLite y PostgreSQL en Visual Studio Code (VS Code). Cubre desde conceptos básicos hasta temas avanzados, con ejercicios y un proyecto práctico.

Configuración del Entorno

Antes de empezar, asegúrate de tener:

• VS Code instalado.
• SQLite instalado.
• PostgreSQL instalado.
• Extensión SQLite Explorer y PostgreSQL en VS Code.

Pasos para configurar

1. Crea un archivo .db para SQLite (ej. biblioteca.db).
2. Para PostgreSQL, configura un servidor local y crea una base de datos con createdb biblioteca.
3. Usa las extensiones en VS Code para ejecutar consultas.

Plan de Estudios

• Semana 1-2: Conceptos básicos (tablas, consultas, filtros).
• Semana 3: Relaciones y uniones.
• Semana 4: Temas avanzados (índices, vistas, triggers).
• Semana 5: Proyecto final.

Conceptos y Comandos Básicos

1. Crear y Modificar Tablas

Comandos: CREATE TABLE, ALTER TABLE, DROP TABLE

CREATE TABLE libros (
  id INTEGER PRIMARY KEY,
  titulo TEXT NOT NULL,
  autor TEXT,
  anio INTEGER
);

2. Insertar Datos

Comando: INSERT INTO

INSERT INTO libros (titulo, autor, anio)
VALUES ('Cien años de soledad', 'Gabriel García Márquez', 1967);

3. Consultar Datos

Comando: SELECT

SELECT * FROM libros WHERE anio > 1950;

4. Actualizar y Eliminar

Comandos: UPDATE, DELETE

UPDATE libros SET anio = 1970 WHERE id = 1;
DELETE FROM libros WHERE anio < 1900;

5. Relaciones y Uniones

CREATE TABLE prestamos (
  id INTEGER PRIMARY KEY,
  libro_id INTEGER,
  usuario TEXT,
  FOREIGN KEY (libro_id) REFERENCES libros(id)
);
SELECT libros.titulo, prestamos.usuario
FROM libros JOIN prestamos ON libros.id = prestamos.libro_id;

Conceptos Avanzados

1. Índices

Mejoran el rendimiento de consultas en columnas frecuentemente buscadas.

CREATE INDEX idx_autor ON libros(autor);
-- PostgreSQL
CREATE INDEX idx_autor ON libros(autor) WHERE autor IS NOT NULL;

2. Vistas

Almacenan consultas como objetos reutilizables.

CREATE VIEW libros_recientes AS
SELECT * FROM libros WHERE anio > 2000;
SELECT * FROM libros_recientes;

3. Triggers

Ejecutan acciones automáticamente tras eventos (ej. inserciones).

-- SQLite
CREATE TRIGGER log_prestamo AFTER INSERT ON prestamos
BEGIN
  INSERT INTO log (mensaje) VALUES ('Nuevo préstamo registrado');
END;

-- PostgreSQL
CREATE OR REPLACE FUNCTION log_prestamo_func() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO log (mensaje) VALUES ('Nuevo préstamo registrado');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER log_prestamo
AFTER INSERT ON prestamos
FOR EACH ROW EXECUTE FUNCTION log_prestamo_func();

4. Transacciones

Aseguran que múltiples operaciones se ejecuten como una sola unidad.

BEGIN TRANSACTION;
INSERT INTO libros (titulo, autor, anio) VALUES ('Nuevo libro', 'Autor', 2023);
INSERT INTO prestamos (libro_id, usuario) VALUES (LAST_INSERT_ID(), 'Usuario');
COMMIT;

Ejercicios Prácticos

1. Crear Tabla e Insertar

Crea una tabla autores e inserta 3 autores.

CREATE TABLE autores (
  id INTEGER PRIMARY KEY,
  nombre TEXT NOT NULL,
  pais TEXT
);
INSERT INTO autores (nombre, pais) VALUES
  ('Gabriel García Márquez', 'Colombia'),
  ('Jane Austen', 'Reino Unido'),
  ('Haruki Murakami', 'Japón');

2. Consulta con Filtros

Consulta libros publicados después de 1950.

SELECT * FROM libros WHERE anio > 1950;

3. Trigger

Crea un trigger para registrar inserciones en prestamos.

-- SQLite
CREATE TRIGGER log_nuevo_prestamo AFTER INSERT ON prestamos
BEGIN
  INSERT INTO log (mensaje) VALUES ('Préstamo registrado: ' || NEW.id);
END;

-- PostgreSQL
CREATE OR REPLACE FUNCTION log_nuevo_prestamo_func() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO log (mensaje) VALUES ('Préstamo registrado: ' || NEW.id);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER log_nuevo_prestamo
AFTER INSERT ON prestamos
FOR EACH ROW EXECUTE FUNCTION log_nuevo_prestamo_func();

Proyecto Final: Sistema de Gestión de Biblioteca

Diseño de la Base de Datos

El sistema gestiona libros, autores, préstamos y un registro de auditoría:

• autores: Almacena autores (id, nombre, país).
• libros: Almacena libros (id, título, autor_id, año, género).
• prestamos: Registra préstamos (id, libro_id, usuario, fecha_prestamo, fecha_devolucion).
• log: Auditoría de acciones (id, mensaje, fecha).

Implementación

-- SQLite
CREATE TABLE autores (
  id INTEGER PRIMARY KEY,
  nombre TEXT NOT NULL,
  pais TEXT
);
CREATE TABLE libros (
  id INTEGER PRIMARY KEY,
  titulo TEXT NOT NULL,
  autor_id INTEGER,
  anio INTEGER,
  genero TEXT,
  FOREIGN KEY (autor_id) REFERENCES autores(id)
);
CREATE TABLE prestamos (
  id INTEGER PRIMARY KEY,
  libro_id INTEGER,
  usuario TEXT,
  fecha_prestamo TEXT,
  fecha_devolucion TEXT,
  FOREIGN KEY (libro_id) REFERENCES libros(id)
);
CREATE TABLE log (
  id INTEGER PRIMARY KEY,
  mensaje TEXT,
  fecha TEXT DEFAULT (CURRENT_TIMESTAMP)
);
CREATE INDEX idx_libro_titulo ON libros(titulo);
CREATE VIEW prestamos_activos AS
SELECT libros.titulo, prestamos.usuario, prestamos.fecha_prestamo
FROM libros JOIN prestamos ON libros.id = prestamos.libro_id
WHERE prestamos.fecha_devolucion IS NULL;
CREATE TRIGGER log_prestamo AFTER INSERT ON prestamos
BEGIN
  INSERT INTO log (mensaje) VALUES ('Nuevo préstamo: ' || NEW.id);
END;
INSERT INTO autores (nombre, pais) VALUES
  ('Gabriel García Márquez', 'Colombia'),
  ('Jane Austen', 'Reino Unido'),
  ('Haruki Murakami', 'Japón');
INSERT INTO libros (titulo, autor_id, anio, genero) VALUES
  ('Cien años de soledad', 1, 1967, 'Realismo mágico'),
  ('Orgullo y prejuicio', 2, 1813, 'Novela romántica'),
  ('Tokio blues', 3, 1987, 'Ficción contemporánea');
INSERT INTO prestamos (libro_id, usuario, fecha_prestamo) VALUES
  (1, 'Ana López', '2025-07-18'),
  (3, 'Carlos Pérez', '2025-07-19');
SELECT * FROM prestamos_activos;

-- PostgreSQL
CREATE TABLE autores (
  id SERIAL PRIMARY KEY,
  nombre VARCHAR(100) NOT NULL,
  pais VARCHAR(50)
);
CREATE TABLE libros (
  id SERIAL PRIMARY KEY,
  titulo VARCHAR(200) NOT NULL,
  autor_id INTEGER,
  anio INTEGER,
  genero VARCHAR(50),
  FOREIGN KEY (autor_id) REFERENCES autores(id)
);
CREATE TABLE prestamos (
  id SERIAL PRIMARY KEY,
  libro_id INTEGER,
  usuario VARCHAR(100),
  fecha_prestamo DATE,
  fecha_devolucion DATE,
  FOREIGN KEY (libro_id) REFERENCES libros(id)
);
CREATE TABLE log (
  id SERIAL PRIMARY KEY,
  mensaje TEXT,
  fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_libro_titulo ON libros(titulo);
CREATE VIEW prestamos_activos AS
SELECT libros.titulo, prestamos.usuario, prestamos.fecha_prestamo
FROM libros JOIN prestamos ON libros.id = prestamos.libro_id
WHERE prestamos.fecha_devolucion IS NULL;
CREATE OR REPLACE FUNCTION log_prestamo_func() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO log (mensaje) VALUES ('Nuevo préstamo: ' || NEW.id);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER log_prestamo
AFTER INSERT ON prestamos
FOR EACH ROW EXECUTE FUNCTION log_prestamo_func();
INSERT INTO autores (nombre, pais) VALUES
  ('Gabriel García Márquez', 'Colombia'),
  ('Jane Austen', 'Reino Unido'),
  ('Haruki Murakami', 'Japón');
INSERT INTO libros (titulo, autor_id, anio, genero) VALUES
  ('Cien años de soledad', 1, 1967, 'Realismo mágico'),
  ('Orgullo y prejuicio', 2, 1813, 'Novela romántica'),
  ('Tokio blues', 3, 1987, 'Ficción contemporánea');
INSERT INTO prestamos (libro_id, usuario, fecha_prestamo) VALUES
  (1, 'Ana López', '2025-07-18'),
  (3, 'Carlos Pérez', '2025-07-19');
SELECT * FROM prestamos_activos;

Recursos Adicionales

• Documentación de SQLite
• Documentación de PostgreSQL
• Tutoriales de SQL