MariaDB [(none)]> create database cursos;
Query OK, 1 row affected (0.047 sec)

MariaDB [(none)]> use cursos;
Database changed
MariaDB [cursos]> create table Estudiante
    -> (identificacion varchar (10) not null primary key,
    -> nombre varchar(30) not null,
    -> estadocivil enum('soltero','casado','union libre','divorciado','viudo') not null,
    -> fechamatricula date not null);
Query OK, 0 rows affected (0.659 sec)

MariaDB [cursos]> insert into estudiante (identificacion,nombre,estadocivil,fechamatricula) values 
    -> ('2040','Elizabeth Cano', 'casado', '2013-01-01'),
    -> ('2140','Denis Rico', 'Divorsiado', '2013-02-18'),
    -> ('2341','Alfredo Lara', 'casado', '2014-06-20'),
    -> ('1840','Armando Casas', 'Viudo', '2014-01-28'),
    -> ('2044','Elihodoro Puerta', 'casado', '2015-07-20'),
    -> ('2314','Mariana Salinas', 'casado', '2016-06-06'),
    -> ('2318','Benito Cespedes', 'casado', '2016-06-30'),
    -> ('2045','Roberto Jimenez', 'soltero', '2017-01-30');
Query OK, 8 rows affected, 1 warning (0.115 sec)
Records: 8  Duplicates: 0  Warnings: 1

MariaDB [cursos]> select distinct fechamatricula 'A¤o de ingreso', count(*) 'Cantidad de estudiantes '  from estudiante group by year(fechamatricula);
+----------------+--------------------------+
| A¤o de ingreso | Cantidad de estudiantes  |
+----------------+--------------------------+
| 2013-01-01     |                        2 |
| 2014-01-28     |                        2 |
| 2015-07-20     |                        1 |
| 2016-06-06     |                        2 |
| 2017-01-30     |                        1 |
+----------------+--------------------------+
5 rows in set (0.036 sec)

MariaDB [cursos]> create table registrocursos
    -> select distinct fechamatricula 'A¤o de ingreso', count(*) as Cantidaddeestudiantes  from estudiante group by year(fechamatricula);
Query OK, 5 rows affected (0.374 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [cursos]> select * from registrocursos;
+----------------+-----------------------+
| A¤o de ingreso | Cantidaddeestudiantes |
+----------------+-----------------------+
| 2013-01-01     |                     2 |
| 2014-01-28     |                     2 |
| 2015-07-20     |                     1 |
| 2016-06-06     |                     2 |
| 2017-01-30     |                     1 |
+----------------+-----------------------+
5 rows in set (0.000 sec)

MariaDB [cursos]> show tables;
+------------------+
| Tables_in_cursos |
+------------------+
| estudiante       |
| registrocursos   |
+------------------+
2 rows in set (0.001 sec)

MariaDB [cursos]> Create table planilla
    -> (carnet varchar(12) not null primary key,
    -> nombre varchar (30) not null,
    -> nota decimal (4,2) unsigned);
Query OK, 0 rows affected (0.361 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]> drop table planilla
    -> ;
Query OK, 0 rows affected (0.397 sec)

MariaDB [cursos]> create table planilla
    -> (carnet varchar(12),
    -> nombre varchar(30),
    -> nota decimal(4,2) unsigned);
Query OK, 0 rows affected (0.494 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');
Query OK, 12 rows affected (0.086 sec)
Records: 12  Duplicates: 0  Warnings: 0

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

MariaDB [cursos]> select distinct carnet as Carnet, sum(nota)/count(*) 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.043 sec)

MariaDB [cursos]> create table promedio
    -> select distinct carnet as Carnet, sum(nota)/count(*) as promedio from planilla group by carnet;
Query OK, 4 rows affected (0.564 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [cursos]> select * from promedio;
+--------+----------+
| 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 as p
    -> join planilla as pl
    -> on p.promedio>=4.0 and pl.carnet=p.carnet
    -> group by carnet;
+--------+-------------+----------+
| carnet | nombre      | promedio |
+--------+-------------+----------+
| 013    | Fabian Juda | 4.500000 |
+--------+-------------+----------+
1 row in set (0.008 sec)

MariaDB [cursos]> select p.carnet,pl.nombre
    -> from promedio as p
    -> join planilla as pl
    -> on p.promedio>=3.0 and pl.carnet=p.carnet
    -> group by carnet;
+--------+----------------+
| carnet | nombre         |
+--------+----------------+
| 010    | Soledad Ospina |
| 012    | Margarita Sol  |
| 013    | Fabian Juda    |
+--------+----------------+
3 rows in set (0.001 sec)

MariaDB [cursos]> create table alumno_aprobado
    -> select p.carnet,pl.nombre
    -> from promedio as p join planilla as pl
    -> on p.promedio>=3.0 and pl.carnet=p.carnet
    -> group by carnet;
Query OK, 3 rows affected (0.387 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [cursos]> select * from alumno_aprobado;
+--------+----------------+
| carnet | nombre         |
+--------+----------------+
| 010    | Soledad Ospina |
| 012    | Margarita Sol  |
| 013    | Fabian Juda    |
+--------+----------------+
3 rows in set (0.001 sec)

MariaDB [cursos]> Create table porcentaje_calificacion
    -> (codpor varchar (5) primary key not null,
    -> descrip varchar (30) not null);
Query OK, 0 rows affected (0.270 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.063 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [cursos]> select pl.carnet, pl.nombre, por.codpor, pr.promedio 'nota_promedio'
    -> from planilla as pl
    -> join porcentaje_calificacion as por,
    -> promedio pr where pl.Carnet=pr.carnet
    -> group by pl.Carnet;
+--------+----------------+--------+---------------+
| carnet | nombre         | codpor | nota_promedio |
+--------+----------------+--------+---------------+
| 010    | Soledad Ospina | 01     |      3.075000 |
| 011    | Marta Salazar  | 01     |      2.100000 |
| 012    | Margarita Sol  | 01     |      3.250000 |
| 013    | Fabian Juda    | 01     |      4.500000 |
+--------+----------------+--------+---------------+
4 rows in set (0.001 sec)

MariaDB [cursos]> create table planilla1
    -> select pl.carnet, pl.nombre, por.codpor, pr.promedio 'nota_promedio'
    -> from planilla as pl
    -> join porcentaje_calificacion as por, promedio pr
    -> where pl.Carnet=pr.carnet
    -> group by pl.Carnet;
Query OK, 4 rows affected (0.289 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [cursos]> select * from planilla1;
+--------+----------------+--------+---------------+
| carnet | nombre         | codpor | nota_promedio |
+--------+----------------+--------+---------------+
| 010    | Soledad Ospina | 01     |      3.075000 |
| 011    | Marta Salazar  | 01     |      2.100000 |
| 012    | Margarita Sol  | 01     |      3.250000 |
| 013    | Fabian Juda    | 01     |      4.500000 |
+--------+----------------+--------+---------------+
4 rows in set (0.001 sec)

MariaDB [cursos]> describe planilla1;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| carnet        | varchar(12)   | YES  |     | NULL    |       |
| nombre        | varchar(30)   | YES  |     | NULL    |       |
| codpor        | varchar(5)    | NO   |     | NULL    |       |
| nota_promedio | decimal(30,6) | YES  |     | NULL    |       |
+---------------+---------------+------+-----+---------+-------+
4 rows in set (0.058 sec)

MariaDB [cursos]> insert into planilla1 (carnet,nombre,codpor,nota_promedio)
    -> select '05','Edilberto Parra',codpor,3.8
    -> from porcentaje_calificacion
    -> where codpor='01';
Query OK, 1 row affected (0.038 sec)
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [cursos]> select * from planilla1;
+--------+-----------------+--------+---------------+
| carnet | nombre          | codpor | nota_promedio |
+--------+-----------------+--------+---------------+
| 010    | Soledad Ospina  | 01     |      3.075000 |
| 011    | Marta Salazar   | 01     |      2.100000 |
| 012    | Margarita Sol   | 01     |      3.250000 |
| 013    | Fabian Juda     | 01     |      4.500000 |
| 05     | Edilberto Parra | 01     |      3.800000 |
+--------+-----------------+--------+---------------+
5 rows in set (0.001 sec)

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

MariaDB [cursos]> exit