MariaDB [cursos]> create table estudiante (identificacion char(10) primary key, nombre char(30), estadocivil enum('soltero','casado','union libre','divorciado','viudo'),fechamatricula date); Query OK, 0 rows affected (0.037 sec) MariaDB [cursos]> describe estudiante; +----------------+-------------------------------------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------------------------------------------------------+------+-----+---------+-------+ | identificacion | char(10) | NO | PRI | NULL | | | nombre | char(30) | YES | | NULL | | | estadocivil | enum('soltero','casado','union libre','divorciado','viudo') | YES | | NULL | | | fechamatricula | date | YES | | NULL | | +----------------+-------------------------------------------------------------+------+-----+---------+-------+ 4 rows in set (0.012 sec) MariaDB [cursos]> insert into estudiante values -> ('2040','Elizabeth Cano','casado','2013/1/1'), -> ('2140','Denis Rico','divorciado','2013/2/18'), -> ('2341','Alfredo Lara','casado','2014/6/20'), -> ('1840','Armando Casas','viudo','2014/1/28'), -> ('2044','Eliodoro Puerta','casado','2015/7/20'), -> ('2314','Mariana Salinas','casado','2016/6/6'), -> ('2318','Benito CǸspedes','casado','2016/6/30'), -> ('2045','Roberto JimǸnez','soltero','2017/1/30'); Query OK, 8 rows affected (0.014 sec) Records: 8 Duplicates: 0 Warnings: 0 MariaDB [cursos]> select * from estudiante; +----------------+------------------+-------------+----------------+ | identificacion | nombre | estadocivil | fechamatricula | +----------------+------------------+-------------+----------------+ | 1840 | Armando Casas | viudo | 2014-01-28 | | 2040 | Elizabeth Cano | casado | 2013-01-01 | | 2044 | Eliodoro Puerta | casado | 2015-07-20 | | 2045 | Roberto JimǸnez | soltero | 2017-01-30 | | 2140 | Denis Rico | divorciado | 2013-02-18 | | 2314 | Mariana Salinas | casado | 2016-06-06 | | 2318 | Benito CǸspedes | casado | 2016-06-30 | | 2341 | Alfredo Lara | casado | 2014-06-20 | +----------------+------------------+-------------+----------------+ 8 rows in set (0.001 sec) MariaDB [cursos]> create table registrocursos -> select year(fechamatricula) as a o, count(year(fechamatricula)) as cantidad -> from estudiante -> group by year(fechamatricula); Query OK, 5 rows affected (0.041 sec) Records: 5 Duplicates: 0 Warnings: 0 MariaDB [cursos]> select * from registrocursos; +------+----------+ | a o | cantidad | +------+----------+ | 2013 | 2 | | 2014 | 2 | | 2015 | 1 | | 2016 | 2 | | 2017 | 1 | +------+----------+ 5 rows in set (0.001 sec) MariaDB [cursos]> CREATE TABLE planilla ( -> carnet CHAR(12), -> nombre CHAR(30), -> nota FLOAT); Query OK, 0 rows affected (0.026 sec) MariaDB [cursos]> insert into planilla values('010','Soledad Ospina',4.0),('011','Marta Salazar',1.5),('012','Margarita Sol',1.5),('013','Fabian Juda',4.0),('010','Soledad Ospina',2.5),('011','Marta Salazar',1.0),('012','Margarita Sol',5.0),('013','Fabian Juda',4.5),('010','Soledad Ospina',2.0),('010','Soledad Ospina',3.8),('011','Marta Salazar',3.8),('013','Fabian Juda',5.0); Query OK, 12 rows affected (0.013 sec) Records: 12 Duplicates: 0 Warnings: 0 MariaDB [cursos]> select * from promedio; ERROR 1146 (42S02): Table 'cursos.promedio' doesn't exist MariaDB [cursos]> select * from planilla; +--------+----------------+------+ | carnet | nombre | nota | +--------+----------------+------+ | 010 | Soledad Ospina | 4 | | 011 | Marta Salazar | 1.5 | | 012 | Margarita Sol | 1.5 | | 013 | Fabian Juda | 4 | | 010 | Soledad Ospina | 2.5 | | 011 | Marta Salazar | 1 | | 012 | Margarita Sol | 5 | | 013 | Fabian Juda | 4.5 | | 010 | Soledad Ospina | 2 | | 010 | Soledad Ospina | 3.8 | | 011 | Marta Salazar | 3.8 | | 013 | Fabian Juda | 5 | +--------+----------------+------+ 12 rows in set (0.009 sec) MariaDB [cursos]> create table promedio -> select carnet, avg(nota) as promedio -> from planilla -> group by carnet; Query OK, 4 rows affected (0.029 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [cursos]> describe promedio; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | carnet | char(12) | YES | | NULL | | | promedio | double | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 2 rows in set (0.006 sec) MariaDB [cursos]> select * from promedio; +--------+-------------------+ | carnet | promedio | +--------+-------------------+ | 010 | 3.074999988079071 | | 011 | 2.099999984105428 | | 012 | 3.25 | | 013 | 4.5 | +--------+-------------------+ 4 rows in set (0.001 sec) MariaDB [cursos]> create table alumno_aprobado -> select pl.carnet, pl.nombre, avg(nota) -> from planilla as pl -> join promedio as pro -> on pl.carnet=pro.carnet -> where pro.promedio>=4 -> group by carnet; Query OK, 1 row affected (0.024 sec) Records: 1 Duplicates: 0 Warnings: 0 MariaDB [cursos]> describe alumno_aprobado; +-----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+----------+------+-----+---------+-------+ | carnet | char(12) | YES | | NULL | | | nombre | char(30) | YES | | NULL | | | avg(nota) | double | YES | | NULL | | +-----------+----------+------+-----+---------+-------+ 3 rows in set (0.006 sec) MariaDB [cursos]> select * from alumno_aprobado; +--------+-------------+-----------+ | carnet | nombre | avg(nota) | +--------+-------------+-----------+ | 013 | Fabian Juda | 4.5 | +--------+-------------+-----------+ 1 row in set (0.000 sec) MariaDB [cursos]> create table porcentaje_calificacion(codpor varchar(5)not null primary key, descrip varchar(30)not null); Query OK, 0 rows affected (0.018 sec) MariaDB [cursos]> describe porcentaje_calificacion; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | codpor | varchar(5) | NO | PRI | NULL | | | descrip | varchar(30) | NO | | NULL | | +---------+-------------+------+-----+---------+-------+ 2 rows in set (0.015 sec) MariaDB [cursos]> insert into porcentaje_calificacion values('01','Parcial 1'),('02','Parcial 2'),('03','Seguimiento'),('04','Parcial Final'); Query OK, 4 rows affected (0.012 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [cursos]> select * from porcentaje_calificacion; +--------+---------------+ | codpor | descrip | +--------+---------------+ | 01 | Parcial 1 | | 02 | Parcial 2 | | 03 | Seguimiento | | 04 | Parcial Final | +--------+---------------+ 4 rows in set (0.000 sec) MariaDB [cursos]> select * from planilla; +--------+----------------+------+ | carnet | nombre | nota | +--------+----------------+------+ | 010 | Soledad Ospina | 4 | | 011 | Marta Salazar | 1.5 | | 012 | Margarita Sol | 1.5 | | 013 | Fabian Juda | 4 | | 010 | Soledad Ospina | 2.5 | | 011 | Marta Salazar | 1 | | 012 | Margarita Sol | 5 | | 013 | Fabian Juda | 4.5 | | 010 | Soledad Ospina | 2 | | 010 | Soledad Ospina | 3.8 | | 011 | Marta Salazar | 3.8 | | 013 | Fabian Juda | 5 | +--------+----------------+------+ 12 rows in set (0.001 sec) MariaDB [cursos]> create table planilla1(carnet char(12),nombre char(30),notapromedio decimal(4,1)unsigned,codpor varchar(5)not null); Query OK, 0 rows affected (0.022 sec) MariaDB [cursos]> describe planilla1; +--------------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------+------+-----+---------+-------+ | carnet | char(12) | YES | | NULL | | | nombre | char(30) | YES | | NULL | | | notapromedio | decimal(4,1) unsigned | YES | | NULL | | | codpor | varchar(5) | NO | | NULL | | +--------------+-----------------------+------+-----+---------+-------+ 4 rows in set (0.014 sec) MariaDB [cursos]> insert into planilla1(carnet,nombre,notapromedio,codpor) -> select '05','Edilberto Parra',3.8,codpor -> from porcentaje_calificacion -> where descrip='Parcial Final'; Query OK, 1 row affected (0.011 sec) Records: 1 Duplicates: 0 Warnings: 0 MariaDB [cursos]> select * from porcentaje_calificacion; +--------+---------------+ | codpor | descrip | +--------+---------------+ | 01 | Parcial 1 | | 02 | Parcial 2 | | 03 | Seguimiento | | 04 | Parcial Final | +--------+---------------+ 4 rows in set (0.000 sec) MariaDB [cursos]> select * from planilla1; +--------+-----------------+--------------+--------+ | carnet | nombre | notapromedio | codpor | +--------+-----------------+--------------+--------+ | 05 | Edilberto Parra | 3.8 | 04 | +--------+-----------------+--------------+--------+ 1 row in set (0.000 sec) MariaDB [cursos]>