MariaDB [(none)]> create database practica2;
Query OK, 1 row affected (0,001 sec)

MariaDB [(none)]> use practica2;
Database changed
MariaDB [practica2]> CREATE TABLE IF NOT EXISTS estudiante (
    ->   identificacion VARCHAR(10) PRIMARY KEY,
    ->   nombre VARCHAR(30),
    ->   estadocivil ENUM('soltero', 'casado', 'unión libre', 'divorciado', 'viudo'),
    ->   fechamatricula DATE
    -> );
Query OK, 0 rows affected (0,009 sec)

MariaDB [practica2]> show tables;
+---------------------+
| Tables_in_practica2 |
+---------------------+
| estudiante          |
+---------------------+
1 row in set (0,001 sec)

MariaDB [practica2]> describe estudiante;
+----------------+--------------------------------------------------------------+------+-----+---------+-------+
| Field          | Type                                                         | Null | Key | Default | Extra |
+----------------+--------------------------------------------------------------+------+-----+---------+-------+
| identificacion | varchar(10)                                                  | NO   | PRI | NULL    |       |
| nombre         | varchar(30)                                                  | YES  |     | NULL    |       |
| estadocivil    | enum('soltero','casado','unión libre','divorciado','viudo')  | YES  |     | NULL    |       |
| fechamatricula | date                                                         | YES  |     | NULL    |       |
+----------------+--------------------------------------------------------------+------+-----+---------+-------+
4 rows in set (0,002 sec)

MariaDB [practica2]> INSERT INTO estudiante (identificacion, nombre, estadocivil, fechamatricula) VALUES
    -> ('2040', 'Elizabeth Cano', 'casado', '2013-01-01'),
    -> ('2140', 'Denis Rico', 'divorciado', '2013-02-18'),
    -> ('2341', 'Alfredo Lara', 'casado', '2014-06-20'),
    -> ('1840', 'Armando Casas', 'viudo', '2014-01-28'),
    -> ('2044', 'Eliodoro Puerta', 'casado', '2015-07-20'),
    -> ('2314', 'Mariana Salinas', 'casado', '2016-06-06'),
    -> ('2318', 'Benito Céspedes', 'casado', '2016-06-30'),
    -> ('2045', 'Roberto Jiménez', 'soltero', '2017-01-30');
Query OK, 8 rows affected (0,004 sec)
Records: 8  Duplicates: 0  Warnings: 0

MariaDB [practica2]> 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 [practica2]> SELECT 
    ->     YEAR(fechamatricula) AS año_ingreso,
    ->     COUNT(*) AS cantidad_alumnos
    -> FROM estudiante
    -> GROUP BY YEAR(fechamatricula)
    -> ORDER BY año_ingreso;
+--------------+------------------+
| año_ingreso  | cantidad_alumnos |
+--------------+------------------+
|         2013 |                2 |
|         2014 |                2 |
|         2015 |                1 |
|         2016 |                2 |
|         2017 |                1 |
+--------------+------------------+
5 rows in set (0,001 sec)

MariaDB [practica2]> CREATE TABLE registrocursos AS
    -> SELECT 
    ->     YEAR(fechamatricula) AS año_ingreso,
    ->     COUNT(*) AS cantidad_alumnos
    -> FROM estudiante
    -> GROUP BY YEAR(fechamatricula);
Query OK, 5 rows affected (0,009 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [practica2]> show tables;
+---------------------+
| Tables_in_practica2 |
+---------------------+
| estudiante          |
| registrocursos      |
+---------------------+
2 rows in set (0,001 sec)

MariaDB [practica2]> registrocursos;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'registrocursos' at line 1
MariaDB [practica2]> select * from registrocursos;
+--------------+------------------+
| año_ingreso  | cantidad_alumnos |
+--------------+------------------+
|         2013 |                2 |
|         2014 |                2 |
|         2015 |                1 |
|         2016 |                2 |
|         2017 |                1 |
+--------------+------------------+
5 rows in set (0,001 sec)

MariaDB [practica2]> CREATE TABLE IF NOT EXISTS planilla (
    ->   id_nota INT AUTO_INCREMENT PRIMARY KEY,
    ->   carnet VARCHAR(12),
    ->   nombre VARCHAR(30),
    ->   nota DECIMAL(4,2) UNSIGNED
    -> );
Query OK, 0 rows affected (0,010 sec)

MariaDB [practica2]> describe planilla;
+---------+-----------------------+------+-----+---------+----------------+
| Field   | Type                  | Null | Key | Default | Extra          |
+---------+-----------------------+------+-----+---------+----------------+
| id_nota | int(11)               | NO   | PRI | NULL    | auto_increment |
| carnet  | varchar(12)           | YES  |     | NULL    |                |
| nombre  | varchar(30)           | YES  |     | NULL    |                |
| nota    | decimal(4,2) unsigned | YES  |     | NULL    |                |
+---------+-----------------------+------+-----+---------+----------------+
4 rows in set (0,001 sec)

MariaDB [practica2]> INSERT INTO planilla (carnet, nombre, nota) 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,008 sec)
Records: 12  Duplicates: 0  Warnings: 0

MariaDB [practica2]> select * from planilla;
+---------+--------+----------------+------+
| id_nota | carnet | nombre         | nota |
+---------+--------+----------------+------+
|       1 | 010    | Soledad Ospina | 4.00 |
|       2 | 011    | Marta Salazar  | 1.50 |
|       3 | 012    | Margarita Sol  | 1.50 |
|       4 | 013    | Fabian Juda    | 4.00 |
|       5 | 010    | Soledad Ospina | 2.50 |
|       6 | 011    | Marta Salazar  | 1.00 |
|       7 | 012    | Margarita Sol  | 5.00 |
|       8 | 013    | Fabian Juda    | 4.50 |
|       9 | 010    | Soledad Ospina | 2.00 |
|      10 | 010    | Soledad Ospina | 3.80 |
|      11 | 011    | Marta Salazar  | 3.80 |
|      12 | 013    | Fabian Juda    | 5.00 |
+---------+--------+----------------+------+
12 rows in set (0,001 sec)

MariaDB [practica2]> SELECT 
    ->   carnet, 
    ->   AVG(nota) AS promedio
    -> FROM planilla
    -> GROUP BY carnet;
+--------+----------+
| carnet | promedio |
+--------+----------+
| 010    | 3.075000 |
| 011    | 2.100000 |
| 012    | 3.250000 |
| 013    | 4.500000 |
+--------+----------+
4 rows in set (0,001 sec)

MariaDB [practica2]> CREATE TABLE promedio AS
    -> SELECT 
    ->   carnet,
    ->   AVG(nota) AS promedio
    -> FROM planilla
    -> GROUP BY carnet;
Query OK, 4 rows affected (0,012 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [practica2]> show tables;
+---------------------+
| Tables_in_practica2 |
+---------------------+
| estudiante          |
| planilla            |
| promedio            |
| registrocursos      |
+---------------------+
4 rows in set (0,001 sec)

MariaDB [practica2]> select*from promedio;
+--------+----------+
| carnet | promedio |
+--------+----------+
| 010    | 3.075000 |
| 011    | 2.100000 |
| 012    | 3.250000 |
| 013    | 4.500000 |
+--------+----------+
4 rows in set (0,001 sec)

MariaDB [practica2]> SELECT 
    ->   carnet,
    ->   nombre,
    ->   AVG(nota) AS promedio
    -> FROM planilla
    -> GROUP BY carnet, nombre
    -> HAVING promedio >= 4.0;
+--------+-------------+----------+
| carnet | nombre      | promedio |
+--------+-------------+----------+
| 013    | Fabian Juda | 4.500000 |
+--------+-------------+----------+
1 row in set (0,001 sec)

MariaDB [practica2]> 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 [practica2]> select*from planilla;
+---------+--------+----------------+------+
| id_nota | carnet | nombre         | nota |
+---------+--------+----------------+------+
|       1 | 010    | Soledad Ospina | 4.00 |
|       2 | 011    | Marta Salazar  | 1.50 |
|       3 | 012    | Margarita Sol  | 1.50 |
|       4 | 013    | Fabian Juda    | 4.00 |
|       5 | 010    | Soledad Ospina | 2.50 |
|       6 | 011    | Marta Salazar  | 1.00 |
|       7 | 012    | Margarita Sol  | 5.00 |
|       8 | 013    | Fabian Juda    | 4.50 |
|       9 | 010    | Soledad Ospina | 2.00 |
|      10 | 010    | Soledad Ospina | 3.80 |
|      11 | 011    | Marta Salazar  | 3.80 |
|      12 | 013    | Fabian Juda    | 5.00 |
+---------+--------+----------------+------+
12 rows in set (0,001 sec)

MariaDB [practica2]> SELECT 
    ->   carnet,
    ->   nombre,
    ->   AVG(nota) AS promedio
    -> FROM planilla
    -> GROUP BY carnet, nombre
    -> HAVING promedio >= 4.0;
+--------+-------------+----------+
| carnet | nombre      | promedio |
+--------+-------------+----------+
| 013    | Fabian Juda | 4.500000 |
+--------+-------------+----------+
1 row in set (0,001 sec)

MariaDB [practica2]> CREATE TABLE alumno_aprobado AS
    -> SELECT 
    ->   carnet,
    ->   nombre
    -> FROM planilla
    -> GROUP BY carnet, nombre
    -> HAVING AVG(nota) >= 4.0;
Query OK, 1 row affected (0,010 sec)
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [practica2]> show tables;
+---------------------+
| Tables_in_practica2 |
+---------------------+
| alumno_aprobado     |
| estudiante          |
| planilla            |
| promedio            |
| registrocursos      |
+---------------------+
5 rows in set (0,001 sec)

MariaDB [practica2]> select * from alumno_aprobado;
+--------+-------------+
| carnet | nombre      |
+--------+-------------+
| 013    | Fabian Juda |
+--------+-------------+
1 row in set (0,001 sec)

MariaDB [practica2]> CREATE TABLE IF NOT EXISTS porcentaje_calificacion (
    ->   codpor VARCHAR(5) NOT NULL PRIMARY KEY,
    ->   descrip VARCHAR(30) NOT NULL
    -> );
Query OK, 0 rows affected (0,011 sec)

MariaDB [practica2]> 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,002 sec)

MariaDB [practica2]> INSERT INTO porcentaje_calificacion (codpor, descrip) VALUES
    -> ('01', 'Parcial 1'),
    -> ('02', 'Parcial 2'),
    -> ('03', 'Seguimiento'),
    -> ('04', 'Parcial final');
Query OK, 4 rows affected (0,008 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [practica2]> SELECT * FROM porcentaje_calificacion;
+--------+---------------+
| codpor | descrip       |
+--------+---------------+
| 01     | Parcial 1     |
| 02     | Parcial 2     |
| 03     | Seguimiento   |
| 04     | Parcial final |
+--------+---------------+
4 rows in set (0,001 sec)

MariaDB [practica2]> CREATE TABLE IF NOT EXISTS planilla1 (
    ->   carnet VARCHAR(12),
    ->   nombre VARCHAR(30),
    ->   notapromedio DECIMAL(4,2) UNSIGNED,
    ->   codpor VARCHAR(5),
    ->   FOREIGN KEY (codpor) REFERENCES porcentaje_calificacion(codpor)
    -> );
Query OK, 0 rows affected (0,009 sec)

MariaDB [practica2]> INSERT INTO planilla1 (carnet, nombre, notapromedio, codpor)
    -> VALUES (
    ->   '005',
    ->   'Edilberto Parra',
    ->   3.8,
    ->   (SELECT codpor FROM porcentaje_calificacion WHERE descrip = 'Parcial final')
    -> );
Query OK, 1 row affected (0,008 sec)

MariaDB [practica2]> (SELECT codpor FROM porcentaje_calificacion WHERE descrip = 'Parcial final')
    -> ;
+--------+
| codpor |
+--------+
| 04     |
+--------+
1 row in set (0,001 sec)

MariaDB [practica2]> SELECT * FROM planilla1;
+--------+-----------------+--------------+--------+
| carnet | nombre          | notapromedio | codpor |
+--------+-----------------+--------------+--------+
| 005    | Edilberto Parra |         3.80 | 04     |
+--------+-----------------+--------------+--------+
1 row in set (0,001 sec)

MariaDB [practica2]> notee