mysql> create database reporte;
Query OK, 1 row affected (0.12 sec)

mysql> use reporte;
Database changed
mysql> create table ALUMNO
    -> (codestu char(3) not null primary key,
    -> nombre char (60) not null,
    -> direccion char (50) not null,
    -> telefono char (10) not null)engine=innodb;
Query OK, 0 rows affected (0.11 sec)

mysql> show tables;
+-------------------+
| Tables_in_reporte |
+-------------------+
| alumno            |
+-------------------+
1 row in set (0.00 sec)

mysql> create table CARRERA
    -> (codcarre char(3) not null primary key,
    -> carrera char (30) not null)engine=innobd;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> create table PROFESOR
    -> (codprofe char(3) not null primary key,
    -> nombre char(60) not null,
    -> direccion char(50) not null,
    -> telefono char(10) not null)engine=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
+-------------------+
| Tables_in_reporte |
+-------------------+
| alumno            |
| carrera           |
| profesor          |
+-------------------+
3 rows in set (0.00 sec)

mysql> create table MATRICULA
    -> (codmatri char(3) not null primary key,
    -> codestu char(3) not null,
    -> codcarre char(3) not null,
    -> codprofe char(3) not null,
    -> valorsemestre double unsigned not null)engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table matricula;
Query OK, 0 rows affected (0.09 sec)

mysql> show tables;
+-------------------+
| Tables_in_reporte |
+-------------------+
| alumno            |
| carrera           |
| profesor          |
+-------------------+
3 rows in set (0.00 sec)

mysql> create table MATRICULA
    -> (codmatri char(3) not null primary key,
    -> codestu char(3) not null,
    -> codcarre char(3) not null,
    -> codprofe char(3) not null,
    -> valorsemestre double unsigned not null,
    -> foreign key(codestu) references alumno(codestu) on delete cascade on update cascade)engine=innodb;
Query OK, 0 rows affected (0.06 sec)

mysql> describe matricula;
+---------------+-----------------+------+-----+---------+-------+
| Field         | Type            | Null | Key | Default | Extra |
+---------------+-----------------+------+-----+---------+-------+
| codmatri      | char(3)         | NO   | PRI | NULL    |       |
| codestu       | char(3)         | NO   | MUL | NULL    |       |
| codcarre      | char(3)         | NO   |     | NULL    |       |
| codprofe      | char(3)         | NO   |     | NULL    |       |
| valorsemestre | double unsigned | NO   |     | NULL    |       |
+---------------+-----------------+------+-----+---------+-------+
5 rows in set (0.08 sec)

mysql> show tables;
+-------------------+
| Tables_in_reporte |
+-------------------+
| alumno            |
| carrera           |
| matricula         |
| profesor          |
+-------------------+
4 rows in set (0.01 sec)

mysql> create table alumcar
    -> (codestu char(3) not null,
    -> codcarre char(3) not null,
    -> foreign key(codestu) references alumno(codestu) on delete cascade on update cascade,
    -> foreign key(codcarre) references carrera(codcarre) on delete cascade on update cascade)engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> create table profcar
    -> (codprofe char(3) not null,
    -> codcarre char(3) not null,
    -> foreign key(codprofe) references profesor(codprofe) on delete cascade on update cascade,
    -> foreign key(codcarre) references carrera(codcarre) on delete cascade on update cascade)engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-------------------+
| Tables_in_reporte |
+-------------------+
| alumcar           |
| alumno            |
| carrera           |
| matricula         |
| profcar           |
| profesor          |
+-------------------+
6 rows in set (0.00 sec)

mysql> describe alumno;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| codestu   | char(3)  | NO   | PRI | NULL    |       |
| nombre    | char(60) | NO   |     | NULL    |       |
| direccion | char(50) | NO   |     | NULL    |       |
| telefono  | char(10) | NO   |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> insert into alumno(codestu,nombre,direccion,telefono) value("001","Juan Tobon","Cra 59","2335698");
Query OK, 1 row affected (0.05 sec)

mysql> insert into alumno(codestu,nombre,direccion,telefono) value("002","Mario Gonzales","Cra 89","6325984");
Query OK, 1 row affected (0.03 sec)

mysql> insert into alumno(codestu,nombre,direccion,telefono) value("003","Federico Aguilar","Cra 26","4569782");
Query OK, 1 row affected (0.00 sec)

mysql> insert into alumno(codestu,nombre,direccion,telefono) value("004","Angel Cuadrado","Cra 44","6398521");
Query OK, 1 row affected (0.00 sec)

mysql> insert into alumno(codestu,nombre,direccion,telefono) value("005","Catalina Escobar","Cra 78","4652300");
Query OK, 1 row affected (0.00 sec)

mysql> insert into alumno(codestu,nombre,direccion,telefono) value("006","Paulina Borja ","Cra 45","4599632");
Query OK, 1 row affected (0.05 sec)

mysql> select * from alumno;
+---------+------------------+-----------+----------+
| codestu | nombre           | direccion | telefono |
+---------+------------------+-----------+----------+
| 001     | Juan Tobon       | Cra 59    | 2335698  |
| 002     | Mario Gonzales   | Cra 89    | 6325984  |
| 003     | Federico Aguilar | Cra 26    | 4569782  |
| 004     | Angel Cuadrado   | Cra 44    | 6398521  |
| 005     | Catalina Escobar | Cra 78    | 4652300  |
| 006     | Paulina Borja    | Cra 45    | 4599632  |
+---------+------------------+-----------+----------+
6 rows in set (0.03 sec)

mysql> insert into carrera(codcarre,carrera) value("001","Ingenieria de Sistemas");
Query OK, 1 row affected (0.07 sec)

mysql> insert into carrera(codcarre,carrera) value("002","Contaduria");
Query OK, 1 row affected (0.00 sec)

mysql> insert into carrera(codcarre,carrera) value("003","Economia");
Query OK, 1 row affected (0.00 sec)

mysql> insert into carrera(codcarre,carrera) value("004","Derecho");
Query OK, 1 row affected (0.00 sec)

mysql> insert into carrera(codcarre,carrera) value("005","Ingenieria Agropecuaria");
Query OK, 1 row affected (0.00 sec)

mysql> insert into carrera(codcarre,carrera) value("006","Agronomia");
Query OK, 1 row affected (0.00 sec)

mysql> insert into carrera(codcarre,carrera) value("007","Ciencias de la salud");
Query OK, 1 row affected (0.00 sec)

mysql> insert into carrera(codcarre,carrera) value("008","Veterinaria");
Query OK, 1 row affected (0.03 sec)

mysql> select * from carrera;
+----------+-------------------------+
| codcarre | carrera                 |
+----------+-------------------------+
| 001      | Ingenieria de Sistemas  |
| 002      | Contaduria              |
| 003      | Economia                |
| 004      | Derecho                 |
| 005      | Ingenieria Agropecuaria |
| 006      | Agronomia               |
| 007      | Ciencias de la salud    |
| 008      | Veterinaria             |
+----------+-------------------------+
8 rows in set (0.00 sec)

mysql> describe profesor;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| codprofe  | char(3)  | NO   | PRI | NULL    |       |
| nombre    | char(60) | NO   |     | NULL    |       |
| direccion | char(50) | NO   |     | NULL    |       |
| telefono  | char(10) | NO   |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> insert into profesor(codprofe,nombre,direccion,telefono) value("001","Pablo Juan Gutierrez","cra 45-96","2569856");
Query OK, 1 row affected (0.00 sec)

mysql> insert into profesor(codprofe,nombre,direccion,telefono) value("002","Enrique Saltamontes","cra 25-63","2365914");
Query OK, 1 row affected (0.00 sec)

mysql> insert into profesor(codprofe,nombre,direccion,telefono) value("003","Portacio Cartagena","cra 36-01","4596321");
Query OK, 1 row affected (0.00 sec)

mysql> insert into profesor(codprofe,nombre,direccion,telefono) value("004","Federico Aguilar","cra 56-41","7895624");
Query OK, 1 row affected (0.00 sec)

mysql> insert into profesor(codprofe,nombre,direccion,telefono) value("005","Alberto Cifuentes","cra 20-30","7895002");
Query OK, 1 row affected (0.00 sec)

mysql> insert into profesor(codprofe,nombre,direccion,telefono) value("006","Pascual Bravo","cra 56-41","5698741");
Query OK, 1 row affected (0.06 sec)

mysql> select * from profesor;
+----------+----------------------+-----------+----------+
| codprofe | nombre               | direccion | telefono |
+----------+----------------------+-----------+----------+
| 001      | Pablo Juan Gutierrez | cra 45-96 | 2569856  |
| 002      | Enrique Saltamontes  | cra 25-63 | 2365914  |
| 003      | Portacio Cartagena   | cra 36-01 | 4596321  |
| 004      | Federico Aguilar     | cra 56-41 | 7895624  |
| 005      | Alberto Cifuentes    | cra 20-30 | 7895002  |
| 006      | Pascual Bravo        | cra 56-41 | 5698741  |
+----------+----------------------+-----------+----------+
6 rows in set (0.00 sec)

mysql> insert into matricula(codmatri,codestu,codcarre,codprofe,valorsemestre) value("001","003","004","004",1800000);
Query OK, 1 row affected (0.03 sec)

mysql> insert into matricula(codmatri,codestu,codcarre,codprofe,valorsemestre) value("002","001","008","003",3500000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into matricula(codmatri,codestu,codcarre,codprofe,valorsemestre) value("003","004","007","006",2800000);
Query OK, 1 row affected (0.02 sec)

mysql> insert into matricula(codmatri,codestu,codcarre,codprofe,valorsemestre) value("004","002","007","006",1950000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into matricula(codmatri,codestu,codcarre,codprofe,valorsemestre) value("005","005","004","001",1800000);
Query OK, 1 row affected (0.02 sec)

mysql> insert into matricula(codmatri,codestu,codcarre,codprofe,valorsemestre) value("006","003","008","003",3500000);
Query OK, 1 row affected (0.01 sec)

mysql> select * from matricula;
+----------+---------+----------+----------+---------------+
| codmatri | codestu | codcarre | codprofe | valorsemestre |
+----------+---------+----------+----------+---------------+
| 001      | 003     | 004      | 004      |       1800000 |
| 002      | 001     | 008      | 003      |       3500000 |
| 003      | 004     | 007      | 006      |       2800000 |
| 004      | 002     | 007      | 006      |       1950000 |
| 005      | 005     | 004      | 001      |       1800000 |
| 006      | 003     | 008      | 003      |       3500000 |
+----------+---------+----------+----------+---------------+
6 rows in set (0.04 sec)

mysql> insert into alumcar(codestu,codcarre) value("001","002");
Query OK, 1 row affected (0.00 sec)

mysql> insert into alumcar(codestu,codcarre) value("003","005");
Query OK, 1 row affected (0.00 sec)

mysql> insert into alumcar(codestu,codcarre) value("005","001");
Query OK, 1 row affected (0.00 sec)

mysql> insert into alumcar(codestu,codcarre) value("001","005");
Query OK, 1 row affected (0.00 sec)

mysql> select * from alumcar;
+---------+----------+
| codestu | codcarre |
+---------+----------+
| 001     | 002      |
| 003     | 005      |
| 005     | 001      |
| 001     | 005      |
+---------+----------+
4 rows in set (0.00 sec)

mysql> insert into profcar(codprofe,codcarre) value("005","003");
Query OK, 1 row affected (0.04 sec)

mysql> insert into profcar(codprofe,codcarre) value("002","006");
Query OK, 1 row affected (0.00 sec)

mysql> insert into profcar(codprofe,codcarre) value("005","005");
Query OK, 1 row affected (0.00 sec)

mysql> insert into profcar(codprofe,codcarre) value("003","008");
Query OK, 1 row affected (0.03 sec)

mysql> insert into profcar(codprofe,codcarre) value("005","001");
Query OK, 1 row affected (0.00 sec)

mysql> insert into profcar(codprofe,codcarre) value("004","002");
Query OK, 1 row affected (0.00 sec)

mysql> insert into profcar(codprofe,codcarre) value("003","001");
Query OK, 1 row affected (0.00 sec)

mysql> insert into profcar(codprofe,codcarre) value("004","004");
Query OK, 1 row affected (0.00 sec)

mysql> insert into profcar(codprofe,codcarre) value("001","004");
Query OK, 1 row affected (0.00 sec)

mysql> insert into profcar(codprofe,codcarre) value("006","007");
Query OK, 1 row affected (0.00 sec)

mysql> select * from profcar;
+----------+----------+
| codprofe | codcarre |
+----------+----------+
| 005      | 003      |
| 002      | 006      |
| 005      | 005      |
| 003      | 008      |
| 005      | 001      |
| 004      | 002      |
| 003      | 001      |
| 004      | 004      |
| 001      | 004      |
| 006      | 007      |
+----------+----------+
10 rows in set (0.00 sec)

mysql> select profesor.codprofe,profesor.nombre,carrera.carrera from profesor inner join profcar on profesor.codprofe=profcar.codprofe inner join carrera on profcar.codcarre=carrera.codcarre where carrera.carrera = "Ingenieria de Sistemas";
+----------+--------------------+------------------------+
| codprofe | nombre             | carrera                |
+----------+--------------------+------------------------+
| 005      | Alberto Cifuentes  | Ingenieria de Sistemas |
| 003      | Portacio Cartagena | Ingenieria de Sistemas |
+----------+--------------------+------------------------+
2 rows in set (0.02 sec)

mysql> select distinct alumno.codestu,alumno.nombre,carrera.carrera,profesor.nombre from matricula inner join alumno on matricula.codestu=alumno.codestu inner join alumcar on alumno.codestu=alumcar.codestu inner join carrera on alumcar.codcarre=carrera.codcarre inner join profcar on carrera.codcarre=profcar.codcarre inner join profesor on profcar.codprofe=profesor.codprofe where matricula.codcarre="004";
+---------+------------------+-------------------------+--------------------+
| codestu | nombre           | carrera                 | nombre             |
+---------+------------------+-------------------------+--------------------+
| 003     | Federico Aguilar | Ingenieria Agropecuaria | Alberto Cifuentes  |
| 005     | Catalina Escobar | Ingenieria de Sistemas  | Alberto Cifuentes  |
| 005     | Catalina Escobar | Ingenieria de Sistemas  | Portacio Cartagena |
+---------+------------------+-------------------------+--------------------+
3 rows in set (0.00 sec)

mysql> select distinct alumno.codestu,alumno.nombre,matricula.valorsemestre from carrera inner join alumcar on carrera.codcarre=alumcar.codcarre inner join alumno on alumcar.codestu=alumno.codestu inner join matricula on alumno.codestu=matricula.codestu where matricula.codcarre="008";
+---------+------------------+---------------+
| codestu | nombre           | valorsemestre |
+---------+------------------+---------------+
| 001     | Juan Tobon       |       3500000 |
| 003     | Federico Aguilar |       3500000 |
+---------+------------------+---------------+
2 rows in set (0.00 sec)

mysql> select alumno.codestu,alumno.nombre,profesor.nombre from alumno inner join alumcar on alumno.codestu=alumcar.codestu inner join carrera on alumcar.codcarre=carrera.codcarre inner join profcar on carrera.codcarre=profcar.codcarre inner join profesor on profcar.codprofe=profesor.codprofe where profesor.nombre="Portacio Cartagena";
+---------+------------------+--------------------+
| codestu | nombre           | nombre             |
+---------+------------------+--------------------+
| 005     | Catalina Escobar | Portacio Cartagena |
+---------+------------------+--------------------+
1 row in set (0.00 sec)

mysql> select max(valorsemestre) as "El valor mas alto del semestre es de: " from matricula;
+----------------------------------------+
| El valor mas alto del semestre es de:  |
+----------------------------------------+
|                                3500000 |
+----------------------------------------+
1 row in set (0.01 sec)

mysql> select avg(valorsemestre) as "El promedio del valor de semestres es de: " from matricula;
+--------------------------------------------+
| El promedio del valor de semestres es de:  |
+--------------------------------------------+
|                         2558333.3333333335 |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from alumno where nombre like"a%" or nombre like"%r";
+---------+------------------+-----------+----------+
| codestu | nombre           | direccion | telefono |
+---------+------------------+-----------+----------+
| 003     | Federico Aguilar | Cra 26    | 4569782  |
| 004     | Angel Cuadrado   | Cra 44    | 6398521  |
| 005     | Catalina Escobar | Cra 78    | 4652300  |
+---------+------------------+-----------+----------+
3 rows in set (0.00 sec)

mysql> select sum(valorsemestre) from matricula where codcarre="001";
+--------------------+
| sum(valorsemestre) |
+--------------------+
|               NULL |
+--------------------+
1 row in set (0.00 sec)

mysql> select sum(valorsemestre) from matricula where codcarre="002";
+--------------------+
| sum(valorsemestre) |
+--------------------+
|               NULL |
+--------------------+
1 row in set (0.00 sec)

mysql> select sum(valorsemestre) from matricula where codcarre="003";
+--------------------+
| sum(valorsemestre) |
+--------------------+
|               NULL |
+--------------------+
1 row in set (0.00 sec)

mysql> select sum(valorsemestre) from matricula where codcarre="004";
+--------------------+
| sum(valorsemestre) |
+--------------------+
|            3600000 |
+--------------------+
1 row in set (0.00 sec)

mysql> select sum(valorsemestre) from matricula where codcarre="005";
+--------------------+
| sum(valorsemestre) |
+--------------------+
|               NULL |
+--------------------+
1 row in set (0.00 sec)

mysql> select sum(valorsemestre) from matricula where codcarre="006";
+--------------------+
| sum(valorsemestre) |
+--------------------+
|               NULL |
+--------------------+
1 row in set (0.00 sec)

mysql> select sum(valorsemestre) from matricula where codcarre="007";
+--------------------+
| sum(valorsemestre) |
+--------------------+
|            4750000 |
+--------------------+
1 row in set (0.00 sec)

mysql> select sum(valorsemestre) from matricula where codcarre="008";
+--------------------+
| sum(valorsemestre) |
+--------------------+
|            7000000 |
+--------------------+
1 row in set (0.00 sec)

mysql> select alumno.codestu,alumno.nombre,carrera.carrera from alumno inner join alumcar on alumno.codestu=alumcar.codestu inner join carrera on alumcar.codcarre=carrera.codcarre where carrera.carrera="Ingenieria de Sistemas";
+---------+------------------+------------------------+
| codestu | nombre           | carrera                |
+---------+------------------+------------------------+
| 005     | Catalina Escobar | Ingenieria de Sistemas |
+---------+------------------+------------------------+
1 row in set (0.00 sec)

mysql> select alumno.codestu,alumno.nombre,matricula.codestu from alumno left join matricula on alumno.codestu=matricula.codestu where matricula.codestu is null;
+---------+---------------+---------+
| codestu | nombre        | codestu |
+---------+---------------+---------+
| 006     | Paulina Borja | NULL    |
+---------+---------------+---------+
1 row in set (0.00 sec)