-- create a table
CREATE TABLE Alumnos (
N_Lista INTEGER PRIMARY KEY,
Nombre TEXT NOT NULL,
Semestre TEXT NOT NULL,
Promedio INTEGER,
Promedio_General INTEGER
);
-- insert some values
INSERT INTO Alumnos VALUES (1, 'ACEVEDO MORA FRANCISCO GAEL','5to',8.5,8.7);
INSERT INTO Alumnos VALUES (2, 'ARAGON ANGELO KARIM','5to',9.2,8.7);
INSERT INTO Alumnos VALUES (3, 'ASCENCIO ALVAREZ KAREN ELIZABETH','5to',8.6,8.7);
INSERT INTO Alumnos VALUES (4, 'AVILA SANTANA YESLIN BRIZELY','5to',8.8,8.7);
INSERT INTO Alumnos VALUES (5, 'CALDERON CEJA ARIANA DENIS','5to',7.4,8.7);
INSERT INTO Alumnos VALUES (6, 'CARRAZCO FLORES AXEL MIGUEL','5to',6.2,8.7);
INSERT INTO Alumnos VALUES (7, 'COROY RODRIGUEZ GIOVANNI RAFAEL','5to',7.4,8.7);
INSERT INTO Alumnos VALUES (8, 'CRUZ MURILLO MARIANA','5to',8.3,8.7);
INSERT INTO Alumnos VALUES (9, 'DE ALBA TORRES PERLA DENISSE','5to',9.1,8.7);
INSERT INTO Alumnos VALUES (10, 'DELGADO LEON DIANA CAMILA','5to',9.4,8.7);
INSERT INTO Alumnos VALUES (11, 'ESPINOSA SAUCEDO MILENA','5to',9.4,8.7);
INSERT INTO Alumnos VALUES (12, 'FLORES SANCHEZ ALLIZON YAITZEL','5to',9.8,8.7);
INSERT INTO Alumnos VALUES (13, 'GALLEGOS AVINA PERLA JACQUELINE','5to',9.0,8.7);
INSERT INTO Alumnos VALUES (14, 'GARCIA CASTRO VALERIA GUADALUPE','5to',8.1,8.7);
INSERT INTO Alumnos VALUES (15, 'GARCIA RODRIGUEZ GABDIEL','5to',8.2,8.7);
INSERT INTO Alumnos VALUES (16, 'GONZALEZ RIVERA JUAN PABLO','5to',9.3,8.7);
INSERT INTO Alumnos VALUES (17, 'GUTIERREZ DUEÑAS CHRISTIAN JOSMAR','5to',9.8,8.7);
INSERT INTO Alumnos VALUES (18, 'GUTIERREZ LOPEZ LITZY SUNARE','5to',9.0,8.7);
INSERT INTO Alumnos VALUES (19, 'HEREDIA OBISPO TANIA ALEJANDRA','5to',8.8,8.7);
INSERT INTO Alumnos VALUES (20, 'HERNANDEZ ALFARO FABIOLA JOLETH','5to',8.2,8.7);
INSERT INTO Alumnos VALUES (21, 'HERNANDEZ VALENCIA ROMINA','5to',9.8,8.7);
INSERT INTO Alumnos VALUES (22, 'JIMENEZ BURGUEÑO ORLANDO MANUEL','5to',9.2,8.7);
INSERT INTO Alumnos VALUES (23, 'JUAREZ GARCIA SAMADHI MONSERRATH','5to',8.8,8.7);
INSERT INTO Alumnos VALUES (24, 'JUAREZ VAZQUEZ JOSMAR LEVI','5to',9.1,8.7);
INSERT INTO Alumnos VALUES (25, 'LEAL MORALES JUAN CARLOS','5to',8.,8.7);
INSERT INTO Alumnos VALUES (26, 'MARIN RODRIGUEZ BALAM','5to',9.7,8.7);
INSERT INTO Alumnos VALUES (27, 'MARTINEZ HUITZACUA WILLIAM ALEXANDER','5to',8.5,8.7);
INSERT INTO Alumnos VALUES (28, 'MEDINA SAGASTE PAULINA GUADALUPE','5to',8.3,8.7);
INSERT INTO Alumnos VALUES (29, 'MIRELES ALVAREZ DANNA SOFIA','5to',8.4,8.7);
INSERT INTO Alumnos VALUES (30, 'MONARREZ ROCHA STEPHANIE YULIANA','5to',9.3,8.7);
INSERT INTO Alumnos VALUES (31, 'NERI VAZQUEZ HECTOR ABRHAM','5to',8.0,8.7);
INSERT INTO Alumnos VALUES (32, 'PIÑONES ZAZUETA LILIAM VIDIANI','5to',7.1,8.7);
INSERT INTO Alumnos VALUES (33, 'PLAZA LOPEZ VALERIA GUADALUPE','5to',9.4,8.7);
INSERT INTO Alumnos VALUES (34, 'QUINIONES PARDO NADLIH JULIANA','5to',10,8.7);
INSERT INTO Alumnos VALUES (35, 'QUINTO MONTAÑO DULCE MAYDALY','5to',8.0,8.7);
INSERT INTO Alumnos VALUES (36, 'RAMIREZ VALDEZ MARIANA','5to',8.5,8.7);
INSERT INTO Alumnos VALUES (37, 'REA NORIEGA AMY ELIZABETH','5to',7.5,8.7);
INSERT INTO Alumnos VALUES (38, 'REYES HERNANDEZ MATEO GABRIEL','5to',8.9,8.7);
INSERT INTO Alumnos VALUES (39, 'REYES REYES DIEGO JARED','5to',9.2,8.7);
INSERT INTO Alumnos VALUES (40, 'RODRIGUEZ VAZQUEZ NATALI KARELI','5to',9.2,8.7);
INSERT INTO Alumnos VALUES (41, 'SALAS ESCALANTE KATHERINE CITLALI','5to',9.8,8.7);
INSERT INTO Alumnos VALUES (42, 'SANCHEZ MARIN LIA GUADALUPE','5to',8.3,8.7);
INSERT INTO Alumnos VALUES (43, 'SOLANO BATALLA ANGELES LIZETH','5to',8.4,8.7);
INSERT INTO Alumnos VALUES (44, 'SUTI JADA ROBERT OSCAR','5to',8.6,8.7);
INSERT INTO Alumnos VALUES (45, 'TONIX AGUIRRE JOSEFA EDITH','5to',9.3,8.7);
INSERT INTO Alumnos VALUES (46, 'VALENZUELA PEÑA BRISA JANETH','5to',9.9,8.7);
INSERT INTO Alumnos VALUES (47, 'VEGA SOBERANES NATAEL','5to',8.7,8.7);

-- MOSTRAR TODOS
SELECT * FROM Alumnos;

-- PROMEDIOS DE 9 A 10
SELECT * FROM Alumnos
WHERE Promedio BETWEEN 9 AND 10
ORDER BY Promedio DESC
Limit 5;

-- MENOR PROMEDIO
SELECT * FROM Alumnos
WHERE Promedio <=7
ORDER BY Promedio ASC;

-- ALUMNOS DE 5TO
SELECT * FROM Alumnos
WHERE Semestre = '5to';

-- PROMEDIO MAYOR O IGUAL A 8
SELECT * FROM Alumnos
WHERE Promedio >= 8;

-- PROMEDIO GENERAL
SELECT Promedio_General, COUNT(*) FROM Alumnos GROUP BY Promedio_General;