Esta es la cuarta entrega donde muestro ejercicios propuestos con su respectiva solución para practicar consultas SQL usando el gestor MySQL o MariaDB. Básicamente se proponen algunos ejercicios de consultas sobre un banco de datos ya definido, y luego se resuelven.
La primera parte está aquí, la segunda aquí y la tercera aquí. Hoy vamos a ver algunas consultas y vamos a trabajar con datos de temperatura y humedad por fecha y hora que recogimos usando el sensor DHT22.
Entre algunas funciones que veremos está MAX, MIN, IF, AVG, SUBSTR y las operaciones usando WHERE, ORDER BY, DISTINCT y LIMIT. Verás que será muy entretenido y que sacaremos algunos ejercicios de todos estos datos.
Datos de la tabla
Como te dije anteriormente, vamos a usar datos de medición de temperatura y humedad. La tabla tiene un id, temperatura, nivel de humedad y la fecha y hora del registro.
Puedes importarlo usando phpmyadmin, copiando y pegando o usando cualquier forma que tú conozcas.
Lo único importante aquí es que la tabla se llama dht_log, la humedad corresponde al campo humidity, la fecha a date y la temperatura a temperature.
Ejercicios propuestos
Pasemos a la lista de ejercicios propuestos. Recuerda que sería bueno que practiques antes de ver la solución.
- Usar función para mostrar máxima humedad
- Del mismo modo, pero ahora con otra función, mostrar la mínima humedad registrada
- Mostrar la fecha en la que se registró la mínima temperatura, y también mostrar la fecha en la que se registró la máxima
- Mostrar la fecha, temperatura y humedad de la fila en la que se registró la menor temperatura
- Usar SELECT para mostrar solo la fecha (sin hora) del primer registro que exista
- Con SELECT, ORDER BY y LIMIT mostrar los 10 registros más altos de temperatura
- Usando WHERE mostrar todos los registros que se encuentren entre las fechas 2020-12-23 20:40:07 y 2020-12-24 08:45:00
- Usar SELECT y WHERE para mostrar los registros en donde la temperatura sea menor que 17 grados centígrados
- Usando una consulta, mostrar los distintos registros de temperatura sin repetirse. Es decir, solo mostrar todas las temperaturas registradas pero sin repetir alguna
- Con SELECT, mostrar el promedio de temperatura de todos los datos
- Del mismo modo, mostrar el promedio de humedad
- Mostrar el promedio de la temperatura pero solo para el día 24 de diciembre del 2020
- Mostrar todos los registros, pero agregar una columna extra. Si la temperatura es mayor o igual a 17 grados centígrados, imprimir
Calor. Si no, imprimirFrio(sin acento, pues causa errores)
Ahí tienes los comentarios si quieres proponer algunos ejercicios usando este banco de datos.
Solución
Usar función para mostrar máxima humedad
En este caso simplemente invocamos a MAX para mostrar la máxima humedad registrada. Como no se nos piden otros datos, la consulta queda así:
Del mismo modo, pero ahora con otra función, mostrar la mínima humedad registrada
Ahora usamos la función MIN y la consulta queda muy similar:
Mostrar la fecha en la que se registró la mínima temperatura, y también mostrar la fecha en la que se registró la máxima
Para esta consulta decidí usar ORDER BY, ordenando los datos y seleccionando solo el primero con un LIMIT. La solución queda así:
Fíjate en que en el primer caso es en orden ascendente, y en el segundo es descendente.
Mostrar la fecha, temperatura y humedad de la fila en la que se registró la menor temperatura
Esta consulta es muy parecida a la anterior, solo hay que seleccionar más filas.
Usar SELECT para mostrar solo la fecha (sin hora) del primer registro que exista
Para esta consulta vamos a cortar la cadena. Como sabemos que la fecha sin hora ocupa 10 caracteres, usamos la función SUBSTR para cortar la cadena desde 1 hasta 10, trayendo simplemente la fecha. Después descartamos los demás datos con LIMIT.
Con SELECT, ORDER BY y LIMIT mostrar los 10 registros más altos de temperatura
En este caso es como la consulta anterior en donde mostramos la mayor temperatura, pero ahora ponemos un límite de 10.
Usando WHERE mostrar todos los registros que se encuentren entre las fechas 2020-12-23 20:40:07 y 2020-12-24 08:45:00
Para este ejercicio vamos a comparar las fechas como si fueran cadenas, y de hecho eso son, simples cadenas que se pueden comparar lexicográficamente de manera similar a cuando comparamos números, pero ahora se comparan según el alfabeto.
Entonces usamos SELECT y WHERE con los operadores mayor qué, y menor qué.
Usar SELECT y WHERE para mostrar los registros en donde la temperatura sea menor que 17 grados centígrados
De nuevo vamos a usar WHERE pero ahora comparando números. Queda así:
En este caso se solicitaron los registros completos, por eso mostramos la fecha, temperatura y humedad en la solución a este ejercicio de SQL.
Usando una consulta, mostrar los distintos registros de temperatura sin repetirse.
Es decir, solo mostrar todas las temperaturas registradas pero sin repetir alguna. Para este caso simplemente hay que usar DISTINCT. Este modificador permite seleccionar solo datos únicos:
Con SELECT, mostrar el promedio de temperatura de todos los datos
Ahora vamos a usar AVG, que es Average o promedio traducido al español. Queda así:
En este caso no estamos limitando los datos, así que se va a seleccionar el promedio de todos los valores.
Del mismo modo, mostrar el promedio de humedad
Hacemos lo mismo que en la consulta anterior pero ahora con la humedad:
Mostrar el promedio de la temperatura pero solo para el día 24 de diciembre del 2020
Volvemos a usar AVG pero ahora vamos a limitar con un WHERE usando comparación de cadenas:
Aplicamos un pequeño truco, pues no hay fecha más allá de las 23 con 59 con 59 ya que si así fuera, sería 25 de diciembre. Lo mismo pasa con la otra fecha, la del límite inferior.
Mostrar todos los registros, pero agregar una columna extra. Si la temperatura es mayor o igual a 17 grados centígrados, imprimir Calor. Si no, imprimir Frio
Imprimir esta columna sin acento, pues puede causar errores. Ahora vamos a usar la función IF. La solución queda así:
Poniendo todo junto
Aquí te dejo todas las consultas que resuelven los problemas propuestos en SQL. También incluyo el comentario que indica cuál ejercicio se está resolviendo:
Y aquí la salida general. Cuidado, pues es mucho texto. Solo le falta una portada para que sea una biblia:
Con eso terminamos por hoy. Te dejo más cosas sobre SQL y MySQL por aquí.
