-- create a table
CREATE TABLE estudiante (
  id INTEGER PRIMARY KEY,
  nombre VARCHAR(100) NOT NULL,
  apellidos VARCHAR(100) NOT NULL,
  fechanacimiento DATE NOT NULL,
  genero VARCHAR(1) NOT NULL);
CREATE TABLE matricula (
  id INTEGER PRIMARY KEY,
  id_estudiante INTEGER NOT NULL,
  year_lectivo INTEGER NOT NULL,
  fecha DATETIME NOT NULL,
  FOREIGN KEY (id_estudiante) REFERENCES estudiante(id));
-- insert some values
INSERT INTO estudiante VALUES (1, 'dewin', 'montoya', '2011-06-29', 'm');
INSERT INTO estudiante VALUES (2, 'carlos', 'rodriguez', '2010-04-12', 'm');
INSERT INTO estudiante VALUES (3, 'maria', 'lopez', '2011-08-05', 'f');
INSERT INTO estudiante VALUES (4, 'luis', 'hernandez', '2012-01-23', 'm');
INSERT INTO estudiante VALUES (5, 'ana', 'martinez', '2010-09-18', 'f');
INSERT INTO estudiante VALUES (6, 'juan', 'gonzalez', '2011-11-30', 'm');
INSERT INTO estudiante VALUES (7, 'sofia', 'perez', '2012-02-07', 'f');
INSERT INTO estudiante VALUES (8, 'diego', 'sanchez', '2010-06-14', 'm');
INSERT INTO estudiante VALUES (9, 'camila', 'torres', '2011-10-21', 'f');
INSERT INTO estudiante VALUES (10, 'andres', 'ramirez', '2012-05-03', 'm');

INSERT INTO matricula VALUES (1, 1, 2024, '2024-02-05 08:00:00');
INSERT INTO matricula VALUES (2, 2, 2024, '2024-02-05 08:15:00');
INSERT INTO matricula VALUES (3, 2, 2025, '2024-02-06 09:00:00');
INSERT INTO matricula VALUES (4, 4, 2023, '2023-01-20 10:00:00');
INSERT INTO matricula VALUES (5, 4, 2025, '2025-01-15 07:30:00');
INSERT INTO matricula VALUES (6, 6, 2025, '2025-01-15 08:00:00');
INSERT INTO matricula VALUES (7, 6, 2026, '2026-02-01 09:20:00');
INSERT INTO matricula VALUES (8, 8, 2023, '2023-03-10 11:00:00');
INSERT INTO matricula VALUES (9, 8, 2024, '2024-02-10 08:45:00');
INSERT INTO matricula VALUES (10, 10, 2026, '2026-02-02 10:10:00');
--fetch some values
SELECT e.nombre, e.apellidos, m.id AS 'n°matricula',year_lectivo 
FROM estudiante AS e
INNER JOIN matricula AS m ON m.id_estudiante = e.id
where id_estudiante= 1