-- 1. CREACIÓN DE LA TABLA
CREATE TABLE ALUMNOS (
    ID_ALUMNOS INTEGER PRIMARY KEY,
    NOMBRE TEXT NOT NULL,
    SEMESTRE INTEGER NOT NULL,
    PROMEDIO REAL NOT NULL
);

-- 2. INSERCIÓN DE REGISTROS
INSERT INTO ALUMNOS VALUES (1, 'ACEVEDO MORA GAEL', 5, 8.5);
INSERT INTO ALUMNOS VALUES (2, 'ARANGON ANGHELO', 5, 9.2);
INSERT INTO ALUMNOS VALUES (3, 'ASCENCIO ALVARES', 5, 8.6);
INSERT INTO ALUMNOS VALUES (4, 'AVILA SANTANA', 5, 8.7);
INSERT INTO ALUMNOS VALUES (5, 'CALDERON CEJA', 5, 7.4);
INSERT INTO ALUMNOS VALUES (6, 'CARRAZCO FLORES', 5, 6.2);
INSERT INTO ALUMNOS VALUES (7, 'CORDY RODRIGUEZ', 5, 7.4);
INSERT INTO ALUMNOS VALUES (8, 'CRUZ MURILLO', 5, 8.3);
INSERT INTO ALUMNOS VALUES (9, 'DE ALBA TORRES', 5, 9.1);
INSERT INTO ALUMNOS VALUES (10, 'DELGADO LEON', 5, 9.4);
INSERT INTO ALUMNOS VALUES (11, 'ESPINOZA SAUCEDO', 5, 9.4);
INSERT INTO ALUMNOS VALUES (12, 'FLORES SANCHEZ', 5, 9.8);
INSERT INTO ALUMNOS VALUES (13, 'GALLEGOS AVILA', 5, 9.0);
INSERT INTO ALUMNOS VALUES (14, 'GARCIA CASTRO', 5, 8.1);
INSERT INTO ALUMNOS VALUES (15, 'GARCIA RODRIGUEZ', 5, 8.2);
INSERT INTO ALUMNOS VALUES (16, 'GONZALEZ RIVERA', 5, 9.3);
INSERT INTO ALUMNOS VALUES (17, 'GUTIERREZ DUEÑAS', 5, 9.8);
INSERT INTO ALUMNOS VALUES (18, 'GUTIERREZ LOPEZ', 5, 9.0);
INSERT INTO ALUMNOS VALUES (19, 'HEREDIA OBISPO', 5, 8.8);
INSERT INTO ALUMNOS VALUES (20, 'HERNANDEZ ALFARO', 5, 8.2);
INSERT INTO ALUMNOS VALUES (21, 'HERNANDEZ VALENCIA', 5, 9.8);
INSERT INTO ALUMNOS VALUES (22, 'JTHENEZ BURGUEÑO', 5, 9.2);
INSERT INTO ALUMNOS VALUES (23, 'JUAREZ GARCIA', 5, 8.8);
INSERT INTO ALUMNOS VALUES (24, 'JUAREZ VAZQUEZ', 5, 9.1);
INSERT INTO ALUMNOS VALUES (25, 'LEAL MORALES', 5, 8.2);
INSERT INTO ALUMNOS VALUES (26, 'MARIN RODRIGUEZ', 5, 9.7);
INSERT INTO ALUMNOS VALUES (27, 'MARTINEZ HUITZACUA', 5, 8.5);
INSERT INTO ALUMNOS VALUES (28, 'MEDINA SAGASTE', 5, 8.3);
INSERT INTO ALUMNOS VALUES (29, 'MIRELES ALVAREZ', 5, 8.4);
INSERT INTO ALUMNOS VALUES (30, 'MONARREZ ROCHA', 5, 9.3);
INSERT INTO ALUMNOS VALUES (31, 'NERI VAZQUEZ', 5, 8.0);
INSERT INTO ALUMNOS VALUES (32, 'PIÑONES ZAZUETA', 5, 7.1);
INSERT INTO ALUMNOS VALUES (33, 'PLAZA LOPEZ', 5, 9.4);
INSERT INTO ALUMNOS VALUES (34, 'QUINTO MONTAÑO', 5, 8.0);
INSERT INTO ALUMNOS VALUES (35, 'QUIÑONES PARDO', 5, 10.0);
INSERT INTO ALUMNOS VALUES (36, 'RAMIREZ VALDEZ', 5, 8.5);
INSERT INTO ALUMNOS VALUES (37, 'REA NORIEGA', 5, 7.5);
INSERT INTO ALUMNOS VALUES (38, 'REYES HERNANDEZ', 5, 8.9);
INSERT INTO ALUMNOS VALUES (39, 'REYES REYES', 5, 9.2);
INSERT INTO ALUMNOS VALUES (40, 'RODRIGUEZ VAZQUEZ', 5, 9.2);
INSERT INTO ALUMNOS VALUES (41, 'SALAS ESCALANTE', 5, 9.8);
INSERT INTO ALUMNOS VALUES (42, 'SANCHEZ MARIN', 5, 8.3);
INSERT INTO ALUMNOS VALUES (43, 'SOLANO BATALLA', 5, 8.4);
INSERT INTO ALUMNOS VALUES (44, 'SUTI JADA', 5, 8.6);
INSERT INTO ALUMNOS VALUES (45, 'VALENZUELA PEÑA', 5, 9.9);
INSERT INTO ALUMNOS VALUES (46, 'VEGA SOBERANES', 5, 8.7);
INSERT INTO ALUMNOS VALUES (47, 'TONTX AGUIRRE', 5, 9.3);

-- 3. CONSULTAS SOLICITADAS EN LA ACTIVIDAD

-- Buscar todos los alumnos
SELECT * FROM ALUMNOS;

-- Buscar el mayor promedio de 9 y 10
SELECT MAX(PROMEDIO) AS MAYOR_PROMEDIO FROM ALUMNOS WHERE PROMEDIO BETWEEN 9 AND 10;

-- Buscar promedio menor a 7
SELECT * FROM ALUMNOS WHERE PROMEDIO < 7;

-- Buscar alumnos de quinto semestre
SELECT * FROM ALUMNOS WHERE SEMESTRE = 5;

-- Buscar promedios >= 8
SELECT * FROM ALUMNOS WHERE PROMEDIO >= 8;

-- Filtra y ordena de mayor a menor promedio con order by
SELECT * FROM ALUMNOS ORDER BY PROMEDIO DESC;

-- Utilizar count y group by para contar los alumnos totales del semestre
SELECT SEMESTRE, COUNT(*) AS TOTAL_ALUMNOS FROM ALUMNOS GROUP BY SEMESTRE;
select * from alumnos ;