SQL_statements.sql

 --UNSIGNED doesn't allow negative values in data type range
DROP DATABASE universidad;
CREATE DATABASE universidad CHAR SET utf8mb4 COLLATE utf8mb4_spanish_ci;
USE universidad;

CREATE TABLE profesores(
    id_profe INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
    nombre VARCHAR(30),
    apellido VARCHAR(30),
    especialidad VARCHAR(30),
    correo VARCHAR(80) UNIQUE,
    fecha_ingreso DATE,
    estado BOOLEAN,
    PRIMARY KEY (id_profe)
);

CREATE TABLE asignaturas(
    cod_asignatura CHAR(4),
    nombre VARCHAR(30),
    numero_horas SMALLINT(4),
    PRIMARY KEY(cod_asignatura)
);

CREATE TABLE imparte(
    profesor INT UNSIGNED,
    asignatura CHAR(4),
    PRIMARY KEY(profesor, asignatura),
    FOREIGN KEY(profesor) REFERENCES profesores(id_profe),
    FOREIGN KEY(asignatura) REFERENCES asignaturas(cod_asignatura) 
);

INSERT INTO profesores(nombre, apellido, especialidad, correo, fecha_ingreso, estado) VALUES('Juan', 'Wills', 'Data science', 'juanwills@gmail.com', '2025-03-18', TRUE);
INSERT INTO profesores(nombre, apellido, especialidad, correo, fecha_ingreso, estado) VALUES('Maria', 'Bolaños', 'Mathemathics', 'maria.b@gmail.com', '2020-03-01', FALSE);
INSERT INTO profesores(nombre, apellido, especialidad, correo, fecha_ingreso, estado) VALUES('Felipe', 'Londono', 'Algebra', 'felipelon@gmail.com', '2020-09-28', TRUE);
INSERT INTO profesores(nombre, apellido, especialidad, correo, fecha_ingreso, estado) VALUES('Anderson', 'Velazquez', 'Fisica', 'andersonvel@gmail.com', '2024-01-11', TRUE);
INSERT INTO profesores(nombre, apellido, especialidad, correo, fecha_ingreso, estado) VALUES('Pedro', 'Fernandez', 'Biologia', 'pedrofer@gmail.com', '2023-06-19', FALSE);

INSERT asignaturas(cod_asignatura, nombre, numero_horas) VALUES 
("MAT1", "Algebra", 40),
("HIS1", "Historia general", 36),
("FIS1", "Fisica clasica", 28),
("BIO1", "Biologia general", 39),
("DTS1", "Data science", 40);

INSERT imparte(profesor, asignatura) VALUES 
(1,'DTS1'),
(3,'MAT1'),
(2,'HIS1'),
(1,'FIS1'),
(5,'BIO1');

-- STATEMENTS --
-- SELECT column_names, ... FROM table_name; --
SELECT * FROM asignaturas;
SELECT nombre, apellido, fecha_ingreso FROM profesores WHERE fecha_ingreso > '2021-01-01';
SELECT profesor FROM imparte WHERE asignatura > 'MAT1';

-- UPDATE table_name SET column_names=values, ... WHERE ...; --
UPDATE asignaturas SET numero_horas= 89 WHERE cod_asignatura = 'FIS1';
UPDATE profesores SET estado= FALSE WHERE correo = 'andersonvel@gmail.com';
UPDATE profesores SET nombre= 'Juan Carlos', apellido= 'Perez' WHERE id_profe= 3;
UPDATE profesores SET nombre= 'Pedro Jose', apellido= 'Fernandez Castro' WHERE id_profe= 5;

-- INSERT table_name(columns_name, ...) VALUES (value_names_1, ...), (value_names_2, ...), ...; ---
INSERT INTO profesores(nombre, apellido, especialidad, correo, fecha_ingreso, estado) VALUES('Carlos', 'Torres', 'Ingenieria', 'carlos.t@gmail.com', '2022-03-18', TRUE);
INSERT INTO profesores(nombre, apellido, especialidad, correo, fecha_ingreso, estado) VALUES('Maria', 'Bolaños', 'Mathemathics', 'mariagomez@gmail.com', '2020-12-01', FALSE);
INSERT INTO profesores(nombre, apellido, especialidad, correo, fecha_ingreso, estado) VALUES('Edison', 'Londono', 'Algebra', 'edison.l@gmail.com', '2023-09-02', TRUE);

-- DELETE FROM table_name WHERE ...; ---
DELETE FROM profesores WHERE id_profe= 11;

-- Classwork
-- TABLES CREATION
DROP TABLE IF EXISTS estudiantes;
CREATE TABLE estudiantes(
    identificacion INT UNSIGNED UNIQUE,
    nombre VARCHAR(30),
    apellido VARCHAR(30),
    correo VARCHAR(80) UNIQUE,
    PRIMARY KEY(identificacion)
);

DROP TABLE IF EXISTS matriculas;
CREATE TABLE matriculas(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
    id_estudiante INT UNSIGNED,
    id_asignatura CHAR(4),
    estado TINYINT(1),
    fecha_ingreso DATETIME,
    fecha_salida DATETIME,
    resultado FLOAT(2),
    PRIMARY KEY(id),
    FOREIGN KEY(id_estudiante) REFERENCES estudiantes(identificacion),
    FOREIGN KEY(id_asignatura) REFERENCES asignaturas(cod_asignatura)
);

-- INSERTS
INSERT estudiantes(identificacion, nombre, apellido, correo) VALUES (1098745863, "Jhon Fredy", "Rojas", "jhon.r@gmail.com"), (1636698447, "Estevan", "Henao", "estevan.h@gmail.com"), (1036654498, "Maria", "Maldonado", "maria.m@gmail.com"); 
INSERT estudiantes(identificacion, nombre, apellido, correo) VALUES (1019697862, "Juan", "Wills", "juan.w@gmail.com");
INSERT estudiantes(identificacion, nombre, apellido, correo) VALUES (1658789263, "Brayan", "Lopez", "brayan.l@gmail.com");

INSERT matriculas(id_estudiante, id_asignatura, estado, fecha_ingreso, fecha_salida, resultado) VALUES (1098745863, 'MAT1', 1, '2025-02-15', '2026-05-31', 3.4), (1019697862, 'FIS1', 1, '2023-02-25', '2025-11-11', 4.2), (1098745863, 'BIO1', 1, '2020-01-31', '2022-08-10', 2.5);

-- UPDATES
UPDATE matriculas SET estado= 0, resultado= 3.4 WHERE id_estudiante=1098745863;
UPDATE matriculas SET fecha_salida= '2023-09-12' WHERE fecha_ingreso<'2021-01-01';
UPDATE matriculas SET id_asignatura= 'DTS1' WHERE id_estudiante=1019697862;

UPDATE estudiantes SET correo= "frenzy.f@gmail.com" WHERE correo="juan.w@gmail.com";
UPDATE estudiantes SET nombre= "Jorge", apellido="Aguirre" WHERE identificacion= 1098745863;
UPDATE estudiantes SET correo= "lukumi@gmail.com" WHERE nombre="Jhon Fredy";

-- DELETES
DELETE FROM matriculas WHERE estado= 0;
DELETE FROM matriculas WHERE id_asignatura= 'BIO1';

DELETE FROM estudiantes WHERE identificacion= 1658789263;

-- End Classwork ---