MariaDB [(none)]> Create database Cursos;
Query OK, 1 row affected (0.012 sec)

MariaDB [(none)]> use Cursos;
Database changed
MariaDB [Cursos]> CREATE TABLE estudiante (
    ->   identificacion VARCHAR(10) PRIMARY KEY,
    ->   nombre VARCHAR(30),
    ->   estadocivil ENUM('soltero', 'casado', 'union libre', 'divorciado', 'viudo'),
    ->   fechamatricula DATE
    -> );
Query OK, 0 rows affected (0.079 sec)

MariaDB [Cursos]> 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.159 sec)
Records: 8  Duplicates: 0  Warnings: 0

MariaDB [Cursos]> describe Estudiante;
+----------------+-------------------------------------------------------------+------+-----+---------+-------+
| Field          | Type                                                        | Null | Key | Default | Extra |
+----------------+-------------------------------------------------------------+------+-----+---------+-------+
| identificacion | varchar(10)                                                 | NO   | PRI | NULL    |       |
| nombre         | varchar(30)                                                 | YES  |     | NULL    |       |
| estadocivil    | enum('soltero','casado','union libre','divorciado','viudo') | YES  |     | NULL    |       |
| fechamatricula | date                                                        | YES  |     | NULL    |       |
+----------------+-------------------------------------------------------------+------+-----+---------+-------+
4 rows in set (0.052 sec)

MariaDB [Cursos]> show tables;
+------------------+
| Tables_in_cursos |
+------------------+
| estudiante       |
+------------------+
1 row in set (0.005 sec)

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 AS
    -> SELECT YEAR(fechamatricula) AS anio, COUNT(*) AS cantidad_alumnos
    -> FROM estudiante
    -> GROUP BY anio;
Query OK, 5 rows affected (0.032 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [Cursos]> SELECT YEAR(fechamatricula) AS anio, COUNT(*) AS cantidad_alumnos
    -> FROM estudiante
    -> GROUP BY anio;
+------+------------------+
| anio | cantidad_alumnos |
+------+------------------+
| 2013 |                2 |
| 2014 |                2 |
| 2015 |                1 |
| 2016 |                2 |
| 2017 |                1 |
+------+------------------+
5 rows in set (0.002 sec)

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 planilla (
    ->   carnet VARCHAR(12) PRIMARY KEY,
    ->   nombre VARCHAR(30),
    ->   nota DECIMAL(4,2) UNSIGNED
    -> );
Query OK, 0 rows affected (0.037 sec)

MariaDB [Cursos]> 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);
ERROR 1062 (23000): Duplicate entry '010' for key 'PRIMARY'
MariaDB [Cursos]> ALTER TABLE planilla DROP PRIMARY KEY;
Query OK, 0 rows affected (0.105 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [Cursos]> ALTER TABLE planilla ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;
Query OK, 0 rows affected (0.061 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [Cursos]> 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.014 sec)
Records: 12  Duplicates: 0  Warnings: 0

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

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

MariaDB [Cursos]> 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.000 sec)

MariaDB [Cursos]> SELECT p.carnet, pl.nombre, p.promedio
    -> FROM promedio p
    -> JOIN planilla pl ON p.carnet = pl.carnet
    -> WHERE p.promedio >= 4.0
    -> GROUP BY p.carnet, pl.nombre;
+--------+-------------+----------+
| carnet | nombre      | promedio |
+--------+-------------+----------+
| 013    | Fabian Juda | 4.500000 |
+--------+-------------+----------+
1 row in set (0.001 sec)

MariaDB [Cursos]> SELECT p.carnet, pl.nombre, p.promedio
    -> FROM promedio p
    -> JOIN (SELECT DISTINCT carnet, nombre FROM planilla) pl ON p.carnet = pl.carnet
    -> WHERE p.promedio >= 4.0;
+--------+-------------+----------+
| carnet | nombre      | promedio |
+--------+-------------+----------+
| 013    | Fabian Juda | 4.500000 |
+--------+-------------+----------+
1 row in set (0.001 sec)

MariaDB [Cursos]> SELECT carnet, nombre, nota
    -> FROM planilla
    -> ORDER BY carnet;
+--------+----------------+------+
| carnet | nombre         | nota |
+--------+----------------+------+
| 010    | Soledad Ospina | 4.00 |
| 010    | Soledad Ospina | 3.80 |
| 010    | Soledad Ospina | 2.50 |
| 010    | Soledad Ospina | 2.00 |
| 011    | Marta Salazar  | 1.50 |
| 011    | Marta Salazar  | 3.80 |
| 011    | Marta Salazar  | 1.00 |
| 012    | Margarita Sol  | 5.00 |
| 012    | Margarita Sol  | 1.50 |
| 013    | Fabian Juda    | 4.50 |
| 013    | Fabian Juda    | 4.00 |
| 013    | Fabian Juda    | 5.00 |
+--------+----------------+------+
12 rows in set (0.001 sec)

MariaDB [Cursos]> 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.033 sec)
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [Cursos]> SELECT carnet, nombre
    -> FROM planilla
    -> GROUP BY carnet, nombre
    -> HAVING AVG(nota) >= 4.0;
+--------+-------------+
| carnet | nombre      |
+--------+-------------+
| 013    | Fabian Juda |
+--------+-------------+
1 row in set (0.001 sec)

MariaDB [Cursos]> show tables;
+------------------+
| Tables_in_cursos |
+------------------+
| alumno_aprobado  |
| estudiante       |
| planilla         |
| promedio         |
| registrocursos   |
+------------------+
5 rows in set (0.003 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.040 sec)

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

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

MariaDB [Cursos]> show tables;
+-------------------------+
| Tables_in_cursos        |
+-------------------------+
| alumno_aprobado         |
| estudiante              |
| planilla                |
| planilla1               |
| porcentaje_calificacion |
| promedio                |
| registrocursos          |
+-------------------------+
7 rows in set (0.003 sec)

MariaDB [Cursos]> INSERT INTO planilla (carnet, nombre, nota)
    -> SELECT '05', 'Edilberto Parra', 3.8
    -> WHERE EXISTS (
    ->   SELECT 1 FROM porcentaje_calificacion WHERE descrip = 'Parcial final'
    -> );
Query OK, 1 row affected (0.013 sec)
Records: 1  Duplicates: 0  Warnings: 0

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

MariaDB [Cursos]> SELECT * FROM planilla WHERE carnet = '05';
+--------+-----------------+------+----+
| carnet | nombre          | nota | id |
+--------+-----------------+------+----+
| 05     | Edilberto Parra | 3.80 | 13 |
+--------+-----------------+------+----+
1 row in set (0.001 sec)

MariaDB [Cursos]> exit