Se importa el archivo hellodb_innodb.sql para crear la base de datos de ejemplo.

[root@Rocky8-mini2 ~]# mysql < hellodb_innodb.sql

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| test               |
+--------------------+
6 rows in set (0.001 sec)

MariaDB [(none)]> USE hellodb;
Database changed

MariaDB [hellodb]> SHOW TABLES;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.001 sec)

1. Alumnos mayores de 25 años y de género masculino

Obtener nombre y edad de los estudientes cuya edad supere los 25 y cuyo género sea masculino.

MariaDB [hellodb]> SELECT Name, Age, Gender
    -> FROM students
    -> WHERE Age > 25 AND Gender = 'M';
+--------------+-----+--------+
| Name         | Age | Gender |
+--------------+-----+--------+
| Xie Yanke    |  53 | M      |
| Ding Dian    |  32 | M      |
| Yu Yutong    |  26 | M      |
| Shi Qing     |  46 | M      |
| Tian Boguang |  33 | M      |
| Xu Xian      |  27 | M      |
| Sun Dasheng  | 100 | M      |
+--------------+-----+--------+
7 rows in set (0.000 sec)

2. Edad promedio por clase

Agrupar los estudiantes por ClassID y calcular la edad promedio de cada grupo.

MariaDB [hellodb]> SELECT ClassID, AVG(Age) AS edad_promedio
    -> FROM students
    -> GROUP BY ClassID;
+---------+----------------+
| ClassID | edad_promedio  |
+---------+----------------+
|    NULL |        63.5000 |
|       1 |        20.5000 |
|       2 |        36.0000 |
|       3 |        20.2500 |
|       4 |        24.7500 |
|       5 |        46.0000 |
|       6 |        20.7500 |
|       7 |        19.6667 |
+---------+----------------+
8 rows in set (0.001 sec)

3. Clases con edad promedio superior a 30

Filtrar los grupos del punto anterior para mostrar solo aquellos cuya edad promeido sea mayor a 30.

MariaDB [hellodb]> SELECT ClassID, AVG(Age) AS edad_promedio
    -> FROM students
    -> GROUP BY ClassID
    -> HAVING AVG(Age) > 30;
+---------+----------------+
| ClassID | edad_promedio  |
+---------+----------------+
|    NULL |        63.5000 |
|       2 |        36.0000 |
|       5 |        46.0000 |
+---------+----------------+
3 rows in set (0.001 sec)

4. Alumnos cuyos nombres inician con 'L'

Mostrar todos los datos de los estudiantes cuyo nombre comienza con la letra 'L'.

MariaDB [hellodb]> SELECT *
    -> FROM students
    -> WHERE Name LIKE 'L%';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     8 | Lin Daiyu   |  17 | F      |       7 |      NULL |
|    14 | Lu Wushuang |  17 | F      |       3 |      NULL |
|    17 | Lin Chong   |  25 | M      |       4 |      NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.000 sec)

5. Crear usuario con acceso desde una subred específica

Conceder permisos al usuario magedu para que pueda conectarse desde cualquier equipo dentro de la red 192.168.119.0/24.

-- Ver usuarios existentes
MariaDB [hellodb]> SELECT User, Host FROM mysql.user;
+-------------+--------------+
| User        | Host         |
+-------------+--------------+
|             | localhost    |
| mariadb.sys | localhost    |
| mysql       | localhost    |
| root        | localhost    |
|             | rocky8-mini2 |
+-------------+--------------+
5 rows in set (0.001 sec)

-- Crear nuevo usuario
MariaDB [hellodb]> CREATE USER magedu@'192.168.119.%';
Query OK, 0 rows affected (0.009 sec)

-- Confirmar creación
MariaDB [hellodb]> SELECT User, Host, Password FROM mysql.user;
+-------------+---------------+-------------------------------------------+
| User        | Host          | Password                                  |
+-------------+---------------+-------------------------------------------+
| mariadb.sys | localhost     |                                           |
| root        | localhost     | *2690FEEA8D4D050D9B1000360EAE7496905DC8B2 |
| mysql       | localhost     | invalid                                   |
|             | localhost     |                                           |
|             | rocky8-mini2  |                                           |
| magedu      | 192.168.119.% |                                           |
+-------------+---------------+-------------------------------------------+
6 rows in set (0.001 sec)

-- Prueba de conexión desde un cliente en la red 192.168.119.0/24
[root@centos7-mini2 ~]# mysql -umagedu -h192.168.119.138
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
mysql> STATUS
--------------
Connection id:          17
Current user:           magedu@192.168.119.147
...