-- =========================
-- TABLAS
-- =========================

CREATE TABLE profesores (
    id_profesor INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100),
    especialidad VARCHAR(100),
    correo VARCHAR(100)
);

CREATE TABLE cursos (
    id_curso INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100),
    descripcion VARCHAR(200),
    duracion INT,
    id_profesor INT,
    FOREIGN KEY (id_profesor) REFERENCES profesores(id_profesor)
);

CREATE TABLE estudiantes (
    id_estudiante INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100),
    correo VARCHAR(100),
    fecha_registro DATE
);

CREATE TABLE inscripciones (
    id_inscripcion INT AUTO_INCREMENT PRIMARY KEY,
    id_estudiante INT,
    id_curso INT,
    fecha DATE
);

CREATE TABLE actividades (
    id_actividad INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100),
    tipo VARCHAR(50),
    fecha_entrega DATE,
    id_curso INT
);

CREATE TABLE calificaciones (
    id_calificacion INT AUTO_INCREMENT PRIMARY KEY,
    id_estudiante INT,
    id_curso INT,
    nota DECIMAL(3,1),
    fecha DATE
);

-- =========================
-- DATOS
-- =========================

INSERT INTO profesores VALUES
(1,'Carlos Ramirez','Bases de Datos','carlos@sena.edu.co'),
(2,'Laura Gomez','Programación','laura@sena.edu.co'),
(3,'Andres Torres','Redes','andres@sena.edu.co'),
(4,'Marta Silva','Ciberseguridad','marta@sena.edu.co'),
(5,'Julian Castro','Desarrollo Web','julian@sena.edu.co');

INSERT INTO cursos VALUES
(1,'SQL Básico','Introducción a bases de datos',40,1),
(2,'Java Básico','Fundamentos de Java',60,2),
(3,'Redes I','Conceptos básicos de redes',50,3),
(4,'Seguridad Informática','Principios de ciberseguridad',45,4),
(5,'HTML y CSS','Diseño de páginas web',35,5);

INSERT INTO estudiantes VALUES
(1,'Ana Lopez','ana@gmail.com','2025-01-10'),
(2,'Juan Perez','juan@gmail.com','2025-01-15'),
(3,'Camila Ruiz','camila@gmail.com','2025-01-18'),
(4,'Diego Martinez','diego@gmail.com','2025-01-20'),
(5,'Sofia Hernandez','sofia@gmail.com','2025-01-25');

INSERT INTO inscripciones VALUES
(1,1,1,'2025-03-01'),
(2,2,2,'2025-03-02'),
(3,3,3,'2025-03-03'),
(4,4,4,'2025-03-04'),
(5,5,5,'2025-03-05');

INSERT INTO actividades VALUES
(1,'Taller SQL','Taller','2025-04-01',1),
(2,'Proyecto Java','Proyecto','2025-04-05',2),
(3,'Laboratorio Redes','Práctica','2025-04-08',3),
(4,'Análisis de Riesgos','Informe','2025-04-10',4),
(5,'Página Web Personal','Proyecto','2025-04-12',5);

INSERT INTO calificaciones VALUES
(1,1,1,4.5,'2025-04-02'),
(2,2,2,3.8,'2025-04-06'),
(3,3,3,4.2,'2025-04-09'),
(4,4,4,4.7,'2025-04-11'),
(5,5,5,5.0,'2025-04-13');


-- =========================
-- CONSULTA 1
-- =========================
SELECT 'CONSULTA 1: ESTUDIANTES' AS resultado;
SELECT * FROM estudiantes;

-- =========================
-- CONSULTA 2
-- =========================
SELECT 'CONSULTA 2: CURSOS' AS resultado;
SELECT * FROM cursos;

-- =========================
-- CONSULTA 3
-- =========================
SELECT 'CONSULTA 3: PROFESORES' AS resultado;
SELECT nombre, correo FROM profesores;

-- =========================
-- CONSULTA 4
-- =========================
SELECT 'CONSULTA 4: CURSOS > 40 HORAS' AS resultado;
SELECT * FROM cursos WHERE duracion > 40;

-- =========================
-- CONSULTA 5
-- =========================
SELECT 'CONSULTA 5: ESTUDIANTES REGISTRADOS' AS resultado;
SELECT * FROM estudiantes WHERE fecha_registro > '2025-01-01';

-- =========================
-- CONSULTA 6
-- =========================
SELECT 'CONSULTA 6: ACTIVIDADES ORDENADAS' AS resultado;
SELECT * FROM actividades ORDER BY fecha_entrega;

-- =========================
-- CONSULTA 7
-- =========================
SELECT 'CONSULTA 7: NOTAS > 4.0' AS resultado;
SELECT * FROM calificaciones WHERE nota > 4.0;

-- =========================
-- CONSULTA 8
-- =========================
SELECT 'CONSULTA 8: ESTUDIANTES Y CURSOS' AS resultado;
SELECT e.nombre, c.nombre
FROM inscripciones i
JOIN estudiantes e ON i.id_estudiante = e.id_estudiante
JOIN cursos c ON i.id_curso = c.id_curso;

-- =========================
-- CONSULTA 9
-- =========================
SELECT 'CONSULTA 9: PROFESORES Y CURSOS' AS resultado;
SELECT p.nombre, c.nombre
FROM profesores p
JOIN cursos c ON p.id_profesor = c.id_profesor;

-- =========================
-- CONSULTA 10
-- =========================
SELECT 'CONSULTA 10: ACTIVIDADES POR CURSO' AS resultado;
SELECT c.nombre, a.nombre
FROM cursos c
JOIN actividades a ON c.id_curso = a.id_curso;

-- =========================
-- CONSULTA 11
-- =========================
SELECT 'CONSULTA 11: CALIFICACIONES' AS resultado;
SELECT e.nombre, cal.nota
FROM estudiantes e
JOIN calificaciones cal ON e.id_estudiante = cal.id_estudiante;

-- =========================
-- CONSULTA 12
-- =========================
SELECT 'CONSULTA 12: TOTAL ESTUDIANTES' AS resultado;
SELECT COUNT(*) FROM estudiantes;

-- =========================
-- CONSULTA 13
-- =========================
SELECT 'CONSULTA 13: PROMEDIO NOTAS' AS resultado;
SELECT AVG(nota) FROM calificaciones;

-- =========================
-- CONSULTA 14
-- =========================
SELECT 'CONSULTA 14: CURSO MAYOR DURACIÓN' AS resultado;
SELECT * FROM cursos ORDER BY duracion DESC LIMIT 1;

-- =========================
-- CONSULTA 15
-- =========================
SELECT 'CONSULTA 15: CURSOS POR PROFESOR' AS resultado;
SELECT p.nombre, COUNT(c.id_curso)
FROM profesores p
JOIN cursos c ON p.id_profesor = c.id_profesor
GROUP BY p.nombre;

-- =========================
-- CONSULTA 16
-- =========================
SELECT 'CONSULTA 16: CURSOS INSCRITOS' AS resultado;
SELECT e.nombre, COUNT(i.id_curso)
FROM estudiantes e
JOIN inscripciones i ON e.id_estudiante = i.id_estudiante
GROUP BY e.nombre;

-- =========================
-- CONSULTA 17
-- =========================
SELECT 'CONSULTA 17: PROMEDIO POR CURSO' AS resultado;
SELECT c.nombre, AVG(cal.nota)
FROM cursos c
JOIN calificaciones cal ON c.id_curso = cal.id_curso
GROUP BY c.nombre;

-- =========================
-- CONSULTA 18
-- =========================
SELECT 'CONSULTA 18: NOTAS < 3.0' AS resultado;
SELECT e.nombre, cal.nota
FROM estudiantes e
JOIN calificaciones cal ON e.id_estudiante = cal.id_estudiante
WHERE cal.nota < 3.0;

-- =========================
-- CONSULTA 19
-- =========================
SELECT 'CONSULTA 19: CURSOS SIN ACTIVIDAD' AS resultado;
SELECT c.nombre
FROM cursos c
LEFT JOIN actividades a ON c.id_curso = a.id_curso
WHERE a.id_actividad IS NULL;

-- =========================
-- CONSULTA 20
-- =========================
SELECT 'CONSULTA 20: MEJOR ESTUDIANTE' AS resultado;
SELECT e.nombre, cal.nota
FROM estudiantes e
JOIN calificaciones cal ON e.id_estudiante = cal.id_estudiante
ORDER BY cal.nota DESC
LIMIT 1;