-- create a table
CREATE TABLE Alumnos (
    N_Lista INTEGER PRIMARY KEY,
    Nombre TEXT NOT NULL,
    Semestre TEXT NOT NULL,
    Promedio INTEGER,
    Promedio_General INTERGER
);
-- 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.4,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.2,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, 'PINONES 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 MONTANO 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, 'TONIX AGUILAR JOSEFA EDITH','5to', 9.3,8.7);
INSERT INTO Alumnos VALUES (45, 'SUTI JADA ROBERT OSCAR','5to',8.6,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);
-- Todos los alumnos
SELECT * FROM Alumnos;
-- Alumnos con promedio de 9 y 10
SELECT * FROM Alumnos
WHERE promedio BETWEEN 9 AND 10  
ORDER BY promedio DESC
LIMIT 5;
-- Alumnos con menor promedio
SELECT *
FROM Alumnos
WHERE promedio <= 7
ORDER BY promedio ASC;
-- Alumnos de 5to semestre
SELECT * FROM Alumnos
WHERE semestre = '5to';
-- Promedio igual o mayor a 8
SELECT * FROM Alumnos
WHERE promedio >= 8;
-- Orden de de mayor a menor
SELECT * FROM Alumnos
ORDER BY promedio DESC;
-- Promedio_General
SELECT Promedio_General, COUNT(*) FROM Alumnos GROUP BY Promedio_General;