CREATE DATABASE ejemplo;

USE ejemplo;

CREATE TABLE carreras(
id_carrera INT PRIMARY KEY AUTO_INCREMENT,
descripcion VARCHAR(30) NOT NULL
)ENGINE=INNODB;

CREATE TABLE alumnos(
n_control CHAR(8) PRIMARY KEY,
nombre VARCHAR(30) NOT NULL,
id_carrera INT NOT NULL,
FOREIGN KEY (id_carrera) REFERENCES carreras(id_carrera) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=INNODB;

INSERT INTO carreras(descripcion) VALUES(‘ISC’);
INSERT INTO carreras(descripcion) VALUES(‘IIA’);
INSERT INTO carreras(descripcion) VALUES(‘II’);
INSERT INTO carreras(descripcion) VALUES(‘IE’);

SELECT * FROM carreras;

SELECT descripcion FROM carreras;
SELECT DISTINCT descripcion FROM carreras;

DELETE FROM carreras
WHERE id_carrera = 2;

UPDATE carreras SET descripcion = ‘ISC’ WHERE id_carrera=2;

INSERT INTO carreras VALUES(2,’Lo que sea’);

Num Ctrol. nombre id_carrera

INSERT INTO alumnos VALUES(‘99370313’, ‘Oscar Flores’, 7);

INSERT INTO alumnos VALUES(‘99370314’, ‘Aldo Sanchez’, 1);
INSERT INTO alumnos VALUES(‘99370315’, ‘Francisco Xavier’, 1);
INSERT INTO alumnos VALUES(‘99370316’, ‘El malo’, 7);
INSERT INTO alumnos VALUES(‘99370317’, ‘Carina Castilla’, 3);
INSERT INTO alumnos VALUES(‘99370318’, ‘Yualiana Medina’, 3);
INSERT INTO alumnos VALUES(‘99370319’, ‘Alejandro Guzman’, 4);
INSERT INTO alumnos VALUES(‘99370320’, ‘Pilar Rivero’, 5);

SELECT * FROM alumnos ORDER BY id_carrera ASC, nombre DESC;

SELECT * FROM carreras;

UPDATE carreras
SET id_carrera = 7
WHERE id_carrera = 2;

DELETE FROM alumnos WHERE id_carrera = 7;

DELETE FROM carreras WHERE id_carrera = 7;

— Obtener el nombre de los alumnos y su carrera

SELECT nombre
FROM alumnos
ORDER BY 1;

SELECT * FROM carreras;

SELECT * FROM alumnos;

SELECT descripcion FROM carreras;

SELECT *
FROM alumnos AS a, carreras AS c
WHERE c.id_carrera = a.id_carrera;

SELECT a.nombre, c.descripcion AS carrera
FROM alumnos AS a, carreras AS c
WHERE c.id_carrera = a.id_carrera
ORDER BY nombre ASC;