-- create a table
CREATE TABLE alumnos (
id INTEGER PRIMARY KEY,
Nombre TEXT NOT NULL,
semestre INTEGER NOT NULL,
promedio REAL NOT NULL,
    promedio_general INTEGER NOT NULL
);
-- insert some values
INSERT INTO alumnos VALUES  (1,'Acevedo Mora Francisco Gael','5',8.5,7 );
INSERT INTO alumnos VALUES (2,'Aragon Angelo Karim','5',9.2,7 );
INSERT INTO alumnos VALUES (3,'Ascencio Alvarez Karen Elizabeth','5',8.6,7 );
INSERT INTO alumnos VALUES (4,'Avila Santana Yeslin Brizely','5',8.8,7 );
INSERT INTO alumnos VALUES (5,'Calderon Ceja Ariana Denis','5',7.4,7 );
INSERT INTO alumnos VALUES (6,'Carrazco Flores Axel Miguel','5',6.2,7 );
INSERT INTO alumnos VALUES (7,'Coroy Rodriguez Giovanni Rafael','5',7.4,7 );
INSERT INTO alumnos VALUES (8,'Cruz Murillo Mariana','5',8.3,7 );
INSERT INTO alumnos VALUES (9,'De alba Torres Perla Denisse','5',9.1,7 );
INSERT INTO alumnos VALUES (10,'Delgado Leon Diana Camila','5',9.4,7 );
INSERT INTO alumnos VALUES (11,'Esoinosa Saucedo Milena','5',9.4,7 );
INSERT INTO alumnos VALUES (12,'Flores Sanchez Allizon Yaitzel','5',9.8,7 );
INSERT INTO alumnos VALUES (13,'Gallegos Aviña Perla Jacqueline','5',9,7 );
INSERT INTO alumnos VALUES (14,'Garcia Castro Valeria Guadalupe','5',8.1,7 );
INSERT INTO alumnos VALUES (15,'Garcia Rodriguez Gabdiel','5',8.2,7 );
INSERT INTO alumnos VALUES (16,'Gonzalez Rivera Juan Pablo','5',9.3,7 );
INSERT INTO alumnos VALUES (17,'Gutierrez Duelas Christian Josmar','5',9.8,7 );
INSERT INTO alumnos VALUES (18,'Guititerrez Lopez Litzy Sunare','5',9,7 );
INSERT INTO alumnos VALUES (19,'Hernandez Obispo Yania Alejandra','5',8.8,7 );
INSERT INTO alumnos VALUES (20,'Hernandez Alfaro Fabiola Joleth','5',8.2,7 );
INSERT INTO alumnos VALUES (21,'Hernandez Valencia Romina','5',9.8,7 );
INSERT INTO alumnos VALUES (22,'Jimenez Burgueño Orlando Manuel','5',9.2,7 );
INSERT INTO alumnos VALUES (23,'Juarez Garcia Samadhi Monserrath','5',8.8,7 );
INSERT INTO alumnos VALUES (24,'Juarez Vazquez Josmar Levi','5',9.1,7 );
INSERT INTO alumnos VALUES (25,'Leal Moralez Juan Carlos','5',8.2,7 );
INSERT INTO alumnos VALUES (26,'Marin Rodriguez Balam','5',9.7,7 );
INSERT INTO alumnos VALUES (27,'Martinez Huitzacua william Alexander','5',8.5,7 );
INSERT INTO alumnos VALUES (28,'Medina Sagaste Paulina Guadalupe','5',8.3,7 );
INSERT INTO alumnos VALUES (29,'Mireles Alvarez Danna Sofia','5',8.4,7 );
INSERT INTO alumnos VALUES (30,'Monarrez Rocha Stephanie Yuliana','5',9.3,7 );
INSERT INTO alumnos VALUES (31,'Neri Vazquez Hector Abrham','5',8,7 );
INSERT INTO alumnos VALUES (32,'Pinones Zazueta Liliam Vidiani','5',7.1,7 );
INSERT INTO alumnos VALUES (33,'Plaza Lopez Valeria Guadalupe','5',9.4,7 );
INSERT INTO alumnos VALUES (34,'Quinto Montaño Dulce Maydaly','5',8,7 );
INSERT INTO alumnos VALUES (35,'Quinones Pardo Nadlih Juliana','5',10,7 );
INSERT INTO alumnos VALUES (36,'Ramirez Valdez Mariana','5',8.5,7 );
INSERT INTO alumnos VALUES (37,'Rea Noriega Amy Elizabeth','5',7.5,7 );
INSERT INTO alumnos VALUES (38,'Reyes Hernandez Mateo Gabriel','5',8.9,7 );
INSERT INTO alumnos VALUES (39,'Reyes Reyes Diego Jared ','5',9.2,7 );
INSERT INTO alumnos VALUES (40,'Rodriguez Vazquez Natali Kareli','5',9.2,7 );
INSERT INTO alumnos VALUES (41,'Salas Escalante Katherine Citlali','5',9.8,7 );
INSERT INTO alumnos VALUES (42,'Sanches Marin Lia Guadalupe','5',8.3,7 );
INSERT INTO alumnos VALUES (43,'Soleno Batalla Angeles Lizeth','5',8.4,7 );
INSERT INTO alumnos VALUES (44,'Suti Jada Robert Oscar','5',8.6,7 );
INSERT INTO alumnos VALUES (45,'Vlenzuela Peña Brisa Jneth','5',9.9,7 );
INSERT INTO alumnos VALUES (46,'Vega Soberanes Natael','5',8.7,7 );
INSERT INTO alumnos VALUES (47,'Tonix Agurre Josefa Edith','5',9.3,7 );
-- fetch some values
SELECT * FROM alumnos;
SELECT MAX(promedio) AS mayor_promedio FROM alumnos WHERE promedio BETWEEN 9 AND 10;
SELECT * FROM alumnos WHERE promedio < 7;
SELECT * FROM alumnos WHERE semestre = 5;
SELECT * FROM alumnos WHERE promedio >= 8;
SELECT * FROM alumnos ORDER BY promedio DESC;
SELECT semestre, COUNT(*) AS total_alumnos FROM alumnos GROUP BY semestre;