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

MariaDB [(none)]> use fechas2;
Database changed
MariaDB [fechas2]> create table estudiante
    -> (carnet varchar(3) not null primary key,
    -> nombre varchar(50) not null,
    -> fechamatricula date not null,
    -> fechanacimiento date not null,
    -> sexo char(1) not null);
Query OK, 0 rows affected (0.259 sec)

MariaDB [fechas2]> select * from estudiante;
Empty set (0.012 sec)

MariaDB [fechas2]> describe estudiante;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| carnet          | varchar(3)  | NO   | PRI | NULL    |       |
| nombre          | varchar(50) | NO   |     | NULL    |       |
| fechamatricula  | date        | NO   |     | NULL    |       |
| fechanacimiento | date        | NO   |     | NULL    |       |
| sexo            | char(1)     | NO   |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
5 rows in set (0.032 sec)

MariaDB [fechas2]> create table materia
    -> (codigom varchar(3) not null primary key,
    -> descripcion varchar(50) not null,
    -> valor int not null);
Query OK, 0 rows affected (0.019 sec)

MariaDB [fechas2]> describe materia;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| codigom     | varchar(3)  | NO   | PRI | NULL    |       |
| descripcion | varchar(50) | NO   |     | NULL    |       |
| valor       | int(11)     | NO   |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.034 sec)

MariaDB [fechas2]> create table estmat
    -> (carnet varchar(3) not null,
    -> codigom varchar(3) not null,
    -> foreign key (carnet) references estudiante(carnet) on delete cascade on update cascade,
    -> foreign key (codigom) references materia(codigom) on delete cascade on update cascade);
Query OK, 0 rows affected (0.253 sec)

MariaDB [fechas2]> describe estmat;
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| carnet  | varchar(3) | NO   | MUL | NULL    |       |
| codigom | varchar(3) | NO   | MUL | NULL    |       |
+---------+------------+------+-----+---------+-------+
2 rows in set (0.037 sec)

MariaDB [fechas2]> INSERT INTO estudiante (carnet, nombre, fechamatricula, fechanacimiento, sexo) VALUES
    -> ('001', 'maria', '2010-01-15', '1966-05-13', 'f'),
    -> ('002', 'juana', '2010-06-08', '1973-01-22', 'f'),
    -> ('003', 'carlos', '2010-06-28', '1976-03-05', 'm'),
    -> ('004', 'Maria Parra', '2010-06-11', '1976-08-15', 'f'),
    -> ('005', 'Pablo Neruda', '2011-02-13', '1990-08-25', 'm'),
    -> ('006', 'Bladimir Palacio', '2011-02-04', '1994-09-03', 'm'),
    -> ('007', 'Apolonia Serrano', '2011-02-28', '1994-03-09', 'f'),
    -> ('008', 'Federico Serrano', '2011-03-05', '1991-06-10', 'm');
Query OK, 8 rows affected (0.015 sec)
Records: 8  Duplicates: 0  Warnings: 0

MariaDB [fechas2]> select * from estudiante;
+--------+------------------+----------------+-----------------+------+
| carnet | nombre           | fechamatricula | fechanacimiento | sexo |
+--------+------------------+----------------+-----------------+------+
| 001    | maria            | 2010-01-15     | 1966-05-13      | f    |
| 002    | juana            | 2010-06-08     | 1973-01-22      | f    |
| 003    | carlos           | 2010-06-28     | 1976-03-05      | m    |
| 004    | Maria Parra      | 2010-06-11     | 1976-08-15      | f    |
| 005    | Pablo Neruda     | 2011-02-13     | 1990-08-25      | m    |
| 006    | Bladimir Palacio | 2011-02-04     | 1994-09-03      | m    |
| 007    | Apolonia Serrano | 2011-02-28     | 1994-03-09      | f    |
| 008    | Federico Serrano | 2011-03-05     | 1991-06-10      | m    |
+--------+------------------+----------------+-----------------+------+
8 rows in set (0.001 sec)

MariaDB [fechas2]> INSERT INTO materia (codigom, descripcion, valor) VALUES
    -> ('001', 'software I', 320000),
    -> ('002', 'software II', 328000),
    -> ('003', 'software III', 400000),
    -> ('004', 'matematicas', 300000),
    -> ('005', 'ingles', 300000);
Query OK, 5 rows affected (0.008 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [fechas2]> select * from materia
    -> ;
+---------+--------------+--------+
| codigom | descripcion  | valor  |
+---------+--------------+--------+
| 001     | software I   | 320000 |
| 002     | software II  | 328000 |
| 003     | software III | 400000 |
| 004     | matematicas  | 300000 |
| 005     | ingles       | 300000 |
+---------+--------------+--------+
5 rows in set (0.001 sec)

MariaDB [fechas2]> INSERT INTO estmat (carnet, codigom) VALUES
    -> ('001', '003'),
    -> ('001', '005'),
    -> ('002', '002'),
    -> ('002', '003'),
    -> ('002', '004'),
    -> ('003', '004'),
    -> ('003', '001'),
    -> ('004', '004'),
    -> ('004', '005'),
    -> ('005', '002'),
    -> ('005', '005'),
    -> ('006', '001'),
    -> ('006', '005'),
    -> ('007', '003'),
    -> ('007', '004'),
    -> ('008', '001'),
    -> ('008', '004'),
    -> ('008', '005');
Query OK, 18 rows affected (0.006 sec)
Records: 18  Duplicates: 0  Warnings: 0

MariaDB [fechas2]> select * from estmat;
+--------+---------+
| carnet | codigom |
+--------+---------+
| 001    | 003     |
| 001    | 005     |
| 002    | 002     |
| 002    | 003     |
| 002    | 004     |
| 003    | 004     |
| 003    | 001     |
| 004    | 004     |
| 004    | 005     |
| 005    | 002     |
| 005    | 005     |
| 006    | 001     |
| 006    | 005     |
| 007    | 003     |
| 007    | 004     |
| 008    | 001     |
| 008    | 004     |
| 008    | 005     |
+--------+---------+
18 rows in set (0.001 sec)

MariaDB [fechas2]> -- TOTAL DE ESTUDIANTES NACIDOS EN LOS A OS 70 
MariaDB [fechas2]> select count(*) as 'TOTAL ESTUDIANTES' from estudiante where year(fechanacimiento) between 1970 and 1979;
+-------------------+
| TOTAL ESTUDIANTES |
+-------------------+
|                 3 |
+-------------------+
1 row in set (0.021 sec)


MariaDB [fechas2]> 
MariaDB [fechas2]> -- NOMBRES DE ESTUDIANTE MAYORES DE 25 A OS
MariaDB [fechas2]> select nombre as 'NOMBRES' from estudiante where (year(curdate()) - year(fechanacimiento)) > 25;
+------------------+
| NOMBRES          |
+------------------+
| maria            |
| juana            |
| carlos           |
| Maria Parra      |
| Pablo Neruda     |
| Bladimir Palacio |
| Apolonia Serrano |
| Federico Serrano |
+------------------+
8 rows in set (0.012 sec)

MariaDB [fechas2]> -- CALCULAR LA EDAD DE CDA ESTUDIANTE
MariaDB [fechas2]> select nombre as 'NOMBRE', fechanacimiento as 'FECHA NACIMIENTO', (year(curdate()) - year(fechanacimiento)) as 'EDAD' from estudiante;
+------------------+------------------+------+
| NOMBRE           | FECHA NACIMIENTO | EDAD |
+------------------+------------------+------+
| maria            | 1966-05-13       |   59 |
| juana            | 1973-01-22       |   52 |
| carlos           | 1976-03-05       |   49 |
| Maria Parra      | 1976-08-15       |   49 |
| Pablo Neruda     | 1990-08-25       |   35 |
| Bladimir Palacio | 1994-09-03       |   31 |
| Apolonia Serrano | 1994-03-09       |   31 |
| Federico Serrano | 1991-06-10       |   34 |
+------------------+------------------+------+
8 rows in set (0.009 sec)

MariaDB [fechas2]> -- MATERIAS CON PRECIO MAYOR A 300000 CON MENSAJE "CARA" O "ECON MICA"
MariaDB [fechas2]> select descripcion, valor,
    -> case
    -> when valor > 300000 then 'Cara'
    -> else 'Economica'
    -> end as categoria
    -> from materia;
+--------------+--------+-----------+
| descripcion  | valor  | categoria |
+--------------+--------+-----------+
| software I   | 320000 | Cara      |
| software II  | 328000 | Cara      |
| software III | 400000 | Cara      |
| matematicas  | 300000 | Economica |
| ingles       | 300000 | Economica |
+--------------+--------+-----------+
5 rows in set (0.124 sec)

MariaDB [fechas2]> -- AGREGAR CAMPO EDAD, ACTUALIZARLO Y CLASIFICAR ESTUDIANTES
MariaDB [fechas2]> alter table estudiante add column edad int;
Query OK, 0 rows affected (0.051 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [fechas2]> describe estudiante;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| carnet          | varchar(3)  | NO   | PRI | NULL    |       |
| nombre          | varchar(50) | NO   |     | NULL    |       |
| fechamatricula  | date        | NO   |     | NULL    |       |
| fechanacimiento | date        | NO   |     | NULL    |       |
| sexo            | char(1)     | NO   |     | NULL    |       |
| edad            | int(11)     | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
6 rows in set (0.060 sec)

MariaDB [fechas2]> select * from estudiante;
+--------+------------------+----------------+-----------------+------+------+
| carnet | nombre           | fechamatricula | fechanacimiento | sexo | edad |
+--------+------------------+----------------+-----------------+------+------+
| 001    | maria            | 2010-01-15     | 1966-05-13      | f    | NULL |
| 002    | juana            | 2010-06-08     | 1973-01-22      | f    | NULL |
| 003    | carlos           | 2010-06-28     | 1976-03-05      | m    | NULL |
| 004    | Maria Parra      | 2010-06-11     | 1976-08-15      | f    | NULL |
| 005    | Pablo Neruda     | 2011-02-13     | 1990-08-25      | m    | NULL |
| 006    | Bladimir Palacio | 2011-02-04     | 1994-09-03      | m    | NULL |
| 007    | Apolonia Serrano | 2011-02-28     | 1994-03-09      | f    | NULL |
| 008    | Federico Serrano | 2011-03-05     | 1991-06-10      | m    | NULL |
+--------+------------------+----------------+-----------------+------+------+
8 rows in set (0.002 sec)

MariaDB [fechas2]> update estudiante set edad = (year(curdate()) - year(fechanacimiento));
Query OK, 8 rows affected (0.003 sec)
Rows matched: 8  Changed: 8  Warnings: 0

MariaDB [fechas2]> select * from estudiante;
+--------+------------------+----------------+-----------------+------+------+
| carnet | nombre           | fechamatricula | fechanacimiento | sexo | edad |
+--------+------------------+----------------+-----------------+------+------+
| 001    | maria            | 2010-01-15     | 1966-05-13      | f    |   59 |
| 002    | juana            | 2010-06-08     | 1973-01-22      | f    |   52 |
| 003    | carlos           | 2010-06-28     | 1976-03-05      | m    |   49 |
| 004    | Maria Parra      | 2010-06-11     | 1976-08-15      | f    |   49 |
| 005    | Pablo Neruda     | 2011-02-13     | 1990-08-25      | m    |   35 |
| 006    | Bladimir Palacio | 2011-02-04     | 1994-09-03      | m    |   31 |
| 007    | Apolonia Serrano | 2011-02-28     | 1994-03-09      | f    |   31 |
| 008    | Federico Serrano | 2011-03-05     | 1991-06-10      | m    |   34 |
+--------+------------------+----------------+-----------------+------+------+
8 rows in set (0.001 sec)

MariaDB [fechas2]> select nombre, edad,
    -> case
    -> when edad > 30 then 'Hombre mayor'
    -> else 'Joven en desarrollo'
    -> end as categoria
    -> from estudiante;
+------------------+------+--------------+
| nombre           | edad | categoria    |
+------------------+------+--------------+
| maria            |   59 | Hombre mayor |
| juana            |   52 | Hombre mayor |
| carlos           |   49 | Hombre mayor |
| Maria Parra      |   49 | Hombre mayor |
| Pablo Neruda     |   35 | Hombre mayor |
| Bladimir Palacio |   31 | Hombre mayor |
| Apolonia Serrano |   31 | Hombre mayor |
| Federico Serrano |   34 | Hombre mayor |
+------------------+------+--------------+
8 rows in set (0.001 sec)

MariaDB [fechas2]> -- MATERIAS CON PRECIO MAYOR A 200000 Y MENOR IGUAL A 300000  CON MENSAJE "MATERIA ECONOMICA" SI ESTA ENTRE 200000 Y 300000, DE LO CONTRARIO "CARA"
MariaDB [fechas2]> select descripcion, valor, 
    -> case
    -> when valor between 200000 and 300000 then 'Materia economica' 
    -> else 'cara'
    -> end as materias
    -> from materia;
+--------------+--------+-------------------+
| descripcion  | valor  | materias          |
+--------------+--------+-------------------+
| software I   | 320000 | cara              |
| software II  | 328000 | cara              |
| software III | 400000 | cara              |
| matematicas  | 300000 | Materia economica |
| ingles       | 300000 | Materia economica |
+--------------+--------+-------------------+
5 rows in set (0.108 sec)

MariaDB [fechas2]> create table repazo2
    -> (codigo varchar(6) not null primary key,
    -> nombre varchar(50) not null,
    -> sexo varchar(10),
    -> fechanacimiento date not null,
    -> nrohijos int not null);
Query OK, 0 rows affected (0.071 sec)

MariaDB [fechas2]> describe repazo2;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| codigo          | varchar(6)  | NO   | PRI | NULL    |       |
| nombre          | varchar(50) | NO   |     | NULL    |       |
| sexo            | varchar(10) | YES  |     | NULL    |       |
| fechanacimiento | date        | NO   |     | NULL    |       |
| nrohijos        | int(11)     | NO   |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
5 rows in set (0.029 sec)

MariaDB [fechas2]> insert into repazo2(codigo, nombre, sexo, fechanacimiento, nrohijos) values ('111222', 'carlos ramirez', 'hombre', '1969-04-04', 2),
    -> ('333666', 'marina ruiz', 'mujer', '1978-12-15', 3),
    -> ('999111', 'veronica gonzalez', 'mujer', '1982-03-30', 1),
    -> ('888777', 'isabel betancur', 'mujer', '1977-11-15', 1),
    -> ('333777', 'gladys bermudez', 'mujer', '1979-01-01', 2),
    -> ('444666', 'sandra henao', 'mujer', '1982-11-25', 3),
    -> ('555777', 'mario gomez', 'hombre', '1982-05-29', 2),
    -> ('111333', 'andrea gutierrez', 'mujer', '1985-04-12', 1),
    -> ('444777', 'carlos sepulveda', 'hombre', '1974-01-10', 1),
    -> ('555333', 'fernando perez', 'hombre', '1980-11-11', 4),
    -> ('666555', 'juliana arredondo', 'mujer', '1988-12-31', 2);
Query OK, 11 rows affected (0.010 sec)
Records: 11  Duplicates: 0  Warnings: 0

MariaDB [fechas2]> select * from repazo2;
+--------+-------------------+--------+-----------------+----------+
| codigo | nombre            | sexo   | fechanacimiento | nrohijos |
+--------+-------------------+--------+-----------------+----------+
| 111222 | carlos ramirez    | hombre | 1969-04-04      |        2 |
| 111333 | andrea gutierrez  | mujer  | 1985-04-12      |        1 |
| 333666 | marina ruiz       | mujer  | 1978-12-15      |        3 |
| 333777 | gladys bermudez   | mujer  | 1979-01-01      |        2 |
| 444666 | sandra henao      | mujer  | 1982-11-25      |        3 |
| 444777 | carlos sepulveda  | hombre | 1974-01-10      |        1 |
| 555333 | fernando perez    | hombre | 1980-11-11      |        4 |
| 555777 | mario gomez       | hombre | 1982-05-29      |        2 |
| 666555 | juliana arredondo | mujer  | 1988-12-31      |        2 |
| 888777 | isabel betancur   | mujer  | 1977-11-15      |        1 |
| 999111 | veronica gonzalez | mujer  | 1982-03-30      |        1 |
+--------+-------------------+--------+-----------------+----------+
11 rows in set (0.001 sec)

MariaDB [fechas2]> -- CUANTAS MUJERES TIENEN SU NOMBRE TERMINADO EN Z
MariaDB [fechas2]> select count(*) as 'TOTAL MUJERES' from repazo2 where sexo="mujer" and nombre like '%z';
+---------------+
| TOTAL MUJERES |
+---------------+
|             4 |
+---------------+
1 row in set (0.062 sec)

MariaDB [fechas2]> -- TOTAL DE PERSONAS NACIDAS EN LOS 70'S
MariaDB [fechas2]> select count(*) as 'TOTAL PERSONAS' from repazo2 where year(fechanacimiento) between 1970 and 1979;
+----------------+
| TOTAL PERSONAS |
+----------------+
|              4 |
+----------------+
1 row in set (0.010 sec)

MariaDB [fechas2]> -- LISTAR PERSONAS CON EDAD ENTRE 25 Y 30 A OS
MariaDB [fechas2]> select* from repazo2 where year(curdate()) - year(fechanacimiento) between 25 and 30;
Empty set (0.018 sec)

MariaDB [fechas2]> -- CUANTOS HIJOS EXISTEN EN EL TOTAL DE DATOS
MariaDB [fechas2]> select sum(nrohijos) as 'TOTAL HIJOS' from repazo2;
+-------------+
| TOTAL HIJOS |                            
+-------------+
|          22 |
+-------------+
1 row in set (0.010 sec)

MariaDB [fechas2]> -- AGRUPAR POR NRO DE HIJOS
MariaDB [fechas2]> select nrohijos, count(*) as 'TOTAL' from repazo2 group by nrohijos;
+----------+-------+
| nrohijos | TOTAL |
+----------+-------+
|        1 |     4 |
|        2 |     4 |
|        3 |     2 |
|        4 |     1 |
+----------+-------+
4 rows in set (0.018 sec)

MariaDB [fechas2]> -- CUANTAS PERSONAS TIENEN EL NOMBRE CARLOS
MariaDB [fechas2]> select count(*) as 'NUMERO PERSONAS' from repazo2 where nombre like '%carlos';
+-----------------+
| NUMERO PERSONAS |
+-----------------+
|               0 |
+-----------------+
1 row in set (0.002 sec)

MariaDB [fechas2]> select count(*) as 'NUMERO PERSONAS' from repazo2 where nombre like '%Carlos';
+-----------------+
| NUMERO PERSONAS |
+-----------------+
|               0 |
+-----------------+
1 row in set (0.001 sec)

MariaDB [fechas2]> select count(*) as 'NUMERO PERSONAS' from repazo2 where nombre like 'carlos%';
+-----------------+
| NUMERO PERSONAS |
+-----------------+
|               2 |
+-----------------+
1 row in set (0.001 sec)

MariaDB [fechas2]> -- TOTAL DE MUJERES MENORES O IGUALES A 22 A OS
MariaDB [fechas2]> select count(*) from repazo2 where (year(curdate()) - year(fechanacimiento)) between 0 and 22;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.026 sec)

MariaDB [fechas2]> -- TOTAL DE HOMBRES MAYORES A 30 QUE TENGAN MAS DE 1 HIJO
MariaDB [fechas2]> select count(*) as 'TOTAL HOMBRES' from repazo2 where sexo='hombre' and timestampdiff(year, fechanacimiento, curdate()) > 30 and nrohijos >1;
+---------------+
| TOTAL HOMBRES |
+---------------+
|             3 |
+---------------+
1 row in set (0.010 sec)

MariaDB [fechas2]> -- TOTAL DE MUJERES MAYORES A 25 Y TIENEN ENTRE 1 Y 3 HIJOS
MariaDB [fechas2]> select count(*) as 'TOTAL MUJERES' from repazo2 where sexo='mujer0 and timetampdiff(year, fechanacimiento, curdate()) > 25 and nrohijos between 1 and 3;
    '> '
    -> ';
    '> //
MariaDB [fechas2]> select count(*) as 'TOTAL MUJERES' from repazo2 where sexo='mujer' and timestampdiff(year,fechanacimiento, curdate()) >25 and nrohijos between 1 and 3;
+---------------+
| TOTAL MUJERES |
+---------------+
|             7 |
+---------------+
1 row in set (0.009 sec)

MariaDB [fechas2]> -- MOSTRAR LA FECHA DEL SISTEMA
MariaDB [fechas2]> select curdate() as 'FECHA SISTEMA';
+---------------+
| FECHA SISTEMA |
+---------------+
| 2025-03-15    |
+---------------+
1 row in set (0.000 sec)

MariaDB [fechas2]> -- VISUALIZAR LA HORA ACTUAL DEL SISTEMA
MariaDB [fechas2]> select curtime() as 'HORA ACTUAL';
+-------------+
| HORA ACTUAL |
+-------------+
| 21:52:53    |
+-------------+
1 row in set (0.005 sec)

MariaDB [fechas2]> exit