Biblioteca Online : ¡La Suscripción ENI por 9,90 € el primer mes!, con el código PRIMER9. Pulse aquí
¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
  1. Libros
  2. SQL
  3. Las funciones
Extrait - SQL Fundamentos del lenguaje (con ejercicios corregidos) (3ª edición)
Extractos del libro
SQL Fundamentos del lenguaje (con ejercicios corregidos) (3ª edición)
1 opinión
Volver a la página de compra del libro

Las funciones

Introducción

Las funciones son muy variadas y a menudo se implementan de manera diferente en cada SGBDR. No vamos a detallar todas las existentes. Abordaremos las más comunes y utilizadas.

Las funciones numéricas

Se pueden utilizar todos los operadores: +, -, *, / y también funciones como el valor absoluto, el coseno, los logaritmos, el módulo, el redondeo, etc.

Consulte la documentación del SGBDR para conocer las funciones que se implementan en la versión de la base de datos utilizada.

No vamos a ser muy exhaustivos en todas las funciones existentes, pero vamos a describir algunas funciones implementadas en los SGBDR.

1. ABS: valor absoluto

Ejemplo

SELECT idTarifa, FechaInicio, Precio, ABS(Precio) 
AS Valor Absoluto FROM Tarifas; 

idTarifa

FechaInicio

Precio

Valor Absoluto

1

2021-10-01

49,99

49,99

2

2021-10-01

59,99

59,99

3

2021-10-01

68,99

68,99

4

2021-10-01

59,99

59,99

5

2021-10-01

69,99

69,99

6

2021-10-01

79,99

79,99

7

2021-10-01

89,99

89,99

8

2021-12-15

57,49

57,49

2. Valor ASCII de un carácter

En este ejemplo vamos a mostrar el código ASCII del primer carácter de la columna Nombre de los hoteles:

SQL Server, MySQL y PostgreSQL

SELECT idHotel, Nombre, ASCII(SUBSTRING(Nombre, 1, 1)) AS Codigo 
FROM Hoteles; 

Oracle, MySQL y PostgreSQL

SELECT idHotel, Nombre, ASCII(SUBSTR(Nombre, 1, 1)) AS Codigo 
FROM Hoteles; 

idHotel

Nombre

Codigo

1

Ski Hotel

83

2

Art Hotel

65

3

Rose Hotel

82

4

Lions Hotel

76

3. COS: coseno - SIN: seno

En este ejemplo se va a mostrar el coseno del precio para la tabla de Tarifas.

Ejemplo

SELECT idTarifa, FechaInicio, Precio, COS(Precio) AS Coseno 
FROM Tarifas; 

idTarifa

FechaInicio

Precio

Coseno

1

2021-10-01

49,99

0,962294075784641

2

2021-10-01

59,99

-0,955413415572478

3

2021-10-01

68,99

0,992192881610071

Para obtener el seno, utilice la misma sintaxis:

SELECT idTarifa, FechaInicio, Precio, SIN(Precio) AS Seno 
FROM Tarifas; 

4. LOG (<número base>,<columna>): logaritmo de la columna seleccionada en la base indicada

En este ejemplo se va a mostrar el logaritmo en base 2 de las tarifas.

Ejemplo

SELECT idTarifa, FechaInicio, Precio, LOG(2, Precio) AS Log 
FROM Tarifas; 

idTarifa

FechaInicio

Precio

Log

1

2021-10-01

49,99

0,177192879928986

2

2021-10-01

59,99

0,169300699821586

5. MOD(<columna>,<valor>): módulo

El módulo devuelve el resto de la división de una columna por un valor. En este ejemplo se va a mostrar el módulo del precio dividido por 4.

Oracle, MySQL y PostgreSQL

SELECT idTarifa, FechaInicio, Precio, MOD(Precio, 4) AS Modulo 
FROM Tarifas; 

idTarifa

FechaInicio

Precio

Modulo

1

2021-10-01...

Las funciones de gestión de fechas y horas

Existen multitud de formatos de visualización, de utilización y de funciones de cada base de datos, no podemos describirlas todas aquí. En la sección Los diferentes formatos de visualización de fechas veremos las que se utilizan más comúnmente y el método para manipularlas.

1. Fecha del día: CURRENT_DATE

En Oracle existe CURRENT_DATE y SYSDATE. En MySQL, se puede utilizar CURRENT_DATE, CURDATE y NOW. Para SQL Server, es GETDATE() y SYSDATETIME()

Estas funciones permiten devolver la fecha del día, con precisiones diferentes (milisegundos, nanosegundos, etc.). En función del SGBDR, existen diferentes funciones que realizan lo mismo y son equivalentes.

Una pequeña particularidad en Oracle y SQL Server: SYSDATE y SYSDATETIME() devuelven la fecha del servidor en el que está instalada la base de datos y CURRENT_DATE o GETDATE() retornan la fecha de la sesión del usuario.

Por ejemplo, si la base de datos está instalada en un servidor americano y el usuario está en España, habrá una diferencia de mínimo 6 horas entre las dos fechas.

Oracle

SELECT CURRENT_DATE, SYSDATE FROM DUAL;  

MySQL

SELECT CURRENT_DATE,CURRENT_DATE(), CURDATE(), NOW() FROM 
DUAL; 

SQL Server

SELECT GETDATE() AS fechaSesion, GETUTCDATE() AS fechaUTCSesion, 
CURRENT_TIMESTAMP AS fechaSesion, SYSDATETIME() AS fechaSistema, 
SYSUTCDATETIME() AS fechaUTCSistema, SYSDATETIMEOFFSET() AS 
FechaOffsetSistema; 

PostgreSQL

SELECT CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP, now(), 
transaction_timestamp(), statement_timestamp(), 
clock_timestamp(), timeofday(); 

En MySQL, SQL Server y PostgreSQL, el () significa que se trata de una función. Si no hay paréntesis es una variable.

Existen algunas diferencias de visualización entre funciones y variables. NOW, por ejemplo, muestra la fecha y la hora actual y el resto solo la fecha para MySQL. La visualización de estas funciones de tipo de fecha también depende de las opciones y parámetros utilizados por la base de datos (En Oracle NLS_DATE_FORMAT y dateformat para SQL Server).

Ya hemos visto que es preferible proporcionar el formato de fecha que se desea obtener en la consulta con la función TO_CHAR en Oracle o la función DATE_FORMAT en MySQL.

Ejemplo en Oracle

SELECT TO_CHAR(CURRENT_DATE,'DD/MM/YYYY')...

Las funciones con cadenas de caracteres

1. Intercambio entre mayúsculas y minúsculas LOWER / UPPER / UCASE / LCASE

Existen dos funciones para convertir las cadenas de caracteres en minúsculas o en mayúsculas. Los nombres difieren entre cada SGBD.

En Oracle y SQL Server, se utilizará LOWER y UPPER. En MySQL se permiten LOWER, UPPER, UCASE y LCASE.

LCASE y LOWER se utilizan para convertir a minúsculas y UCASE y UPPER para convertir a mayúsculas.

Ejemplo en Oracle

SELECT LOWER('Esto es una PRUEBA') AS MINUSCULA FROM DUAL;  
  
MINUSCULA 
----------------  
esto es una prueba  
  
SELECT UPPER('Esto es una PRUEBA') AS MAYUSCULA FROM DUAL;  
  
MAYUSCULA  
----------------  
ESTO ES UNA PRUEBA 

Ejemplo en MySQL

SELECT LCASE('Esto es una PRUEBA') AS MINUSCULA  
  
+--------------------+  
| MINUSCULA          |  
+--------------------+  
| esto es una prueba |  
+--------------------+  
  
SELECT UCASE('Esto es una PRUEBA') AS MAYUSCULA 
  
+--------------------+  
| MAYUSCULA          |  
+--------------------+  
| ESTO ES UNA PRUEBA |  
+--------------------+ 

Ejemplo para SQL Server y PostgreSQL

SELECT LOWER('Esto es una prueba') AS MINUSCULA; 
MINUSCULA 
---------------- 
Esto es una prueba 
SELECT UPPER('Esto es una prueba') AS MAYUSCULA; 
MAYUSCULA 
---------------- 
ESTO ES UNA PRUEBA 

La sintaxis es:

SELECT LOWER o UPPER (<columna o variable>) ... FROM <tabla1>, 
<tabla2> ... 

con la posibilidad de utilizar LCASE y UCASE en MySQL.

2. Eliminar los espacios a la derecha o izquierda de una cadena de caracteres: TRIM / LTRIM / RTRIM

Para eliminar espacios en una cadena de caracteres, hay que utilizar LTRIM si están a la izquierda (Left) o RTRIM si están a la derecha (Right). Para eliminar espacios a izquierda y derecha, existe TRIM.

Ejemplo en Oracle y PostgreSQL

SELECT...

Las principales funciones de conversión

1. Transformar un numérico o una fecha en texto: TO_CHAR

Se trata de las funciones TO_CHAR en Oracle y CAST en MySQL. Permiten convertir un numérico o una fecha en caracteres.

Ejemplo para Oracle

SELECT TO_CHAR(PRECIO) PRECIO, TO_CHAR(FECHAINICIO,'DD/MM/YY') FechaInicio 
FROM Tarifas; 

PRECIO

FECHAINICIO

69.99

16/04/21

59.99

16/04/21

69.99

16/04/21

79.99

16/04/21

89.99

16/04/21

Ejemplo para PostgreSQL, MySQL y SQL Server

SELECT CAST(Precio as char(6)) AS Precio, CAST(FechaInicio as char(10)) 
AS FechaInicio FROM Tarifas; 

Otro ejemplo para SQL Server

SELECT CONVERT(char(6), Precio) AS Precio, CONVERT(char(10), 
FechaInicio) AS FechaInicio FROM Tarifas; 

Por lo tanto, la sintaxis para Oracle es:

SELECT TO_CHAR(<columna o variable>,[<FORMAT>],<cadena   
buscada> ...  FROM <tabla1>, <tabla2> ... 

Por lo tanto, la sintaxis para MySQL y SQL Server es:

SELECT CAST(<columna o variable> as <TYPE>) ...  FROM <tabla1>, 
<tabla2> ... 

Por lo tanto, la sintaxis para SQL Server es:

SELECT CONVERT(<TYPE>, <columna o variable>) ...  FROM <tabla1>, 
<tabla2> ... 

2. Cambiar el tipo de una columna: CAST o CONVERT

Estas funciones permiten cambiar el tipo de una columna durante la sentencia SQL; por ejemplo, cambiar una columna que inicialmente era VARCHAR a INTEGER, para hacer un cálculo o probar...

Las funciones de paginación

Las funciones de paginación permiten ordenar las filas o realizar cálculos sobre subgrupos después de haber particionado los registros.

1. Numeración secuencial y ordenación de filas

Estas son las funciones que permiten ordenar filas. RANK permite numerar filas en un orden determinado. DENSE_RANK permite numerar las filas en un orden con números consecutivos. ROW_NUMBER permite numerar filas con números consecutivos, como una serie. La numeración se puede realizar sobre subgrupos de registros.

A continuación se muestran ejemplos de numeración de la tabla tarifas por precio, con y sin partición por tipo de habitación.

Ejemplos para SQL Server, Oracle y PostgreSQL

SELECT Hotel, tipoHabitacion, Precio, RANK() OVER(ORDER BY Precio DESC) 
AS Numero, DENSE_RANK() OVER(ORDER BY Precio DESC) AS NumeroConsecutivo, 
ROW_NUMBER() OVER(ORDER BY Precio DESC) AS Consecutivo FROM Tarifas; 

Hotel

tipoHabitacion

Precio

Numero

Numero Consecutivo

Consecutivo

2

7

103,49

1

1

1

3

7

103,49

1

1

2

1

7

103,49

1

1

3

4

7

103,49

1

1

4

4

6

91,99

5

2

5

1

6

91,99

5

2

6

SELECT Hotel, tipoHabitacion, Precio  
, RANK() OVER(PARTITION BY TipoHabitacion ORDER BY Precio DESC) AS Numero 
, DENSE_RANK() OVER(PARTITION BY TipoHabitacion ORDER BY Precio DESC) AS 
NumeroConsecutivo  
, ROW_NUMBER() OVER(PARTITION BY TipoHabitacion ORDER BY Precio DESC) AS 
Consecutivo  
FROM Tarifas; 

Hotel

tipoHabitacion

Precio

Numero

Numero Consecutivo

Consecutivo

1

1

58,49

1

1

1

2

1

57,49

2

2

2

3

1

57,49

2

2

3

1

1

57,49

2

2

4

4

1

57,49

2

2

5

2

1

49,99

6

3

6

3

1

49,99

6

3

7

4

1

49,99

6

3

8

1

1

49,99

6

3

9

1

2

69,99

1

1

1

2

2

68,99

2

2

2

3

2

68,99

2

2

3

4

2

68,99

2

2

4

En este ejemplo, hay dos tipos de habitaciones que clasificamos por precio. El primer tipo tiene tres precios diferentes. Vemos la diferencia entre Numero y NumeroConsecutivo en el tercer precio, donde el número (función RANK) retoma la posición de la tarifa, es decir, 6, mientras que NumeroConsecutivo (función DENSE_RANK) continúa la numeración. La secuencia se basa en un paso de 1. La numeración vuelve a ser de 1 sobre el segundo tipo de habitación porque la partición se basa en el campo TipoHabitacion.

2. Distribución de filas en grupos numerados

La función NTILE permite distribuir y numerar filas...

Otras funciones

1. NVL: comprobar si una columna es null

NVL por « Null Value » permite saber si una columna tiene datos o no y asignarle un valor en caso de que esté a nulos. La función correspondiente en SQL Server es ISNULL.

Sintaxis

SELECT NVL(<nombre columna>,<valor asignado>), ... 

El valor asignado debe ser del mismo tipo que la columna.

Ejemplo Oracle

SELECT NumHabitacion, Comentario, NVL(Comentario, 'Vistas al jardín') AS 
Comentarios FROM Habitaciones; 

Ejemplo SQL Server

SELECT NumHabitacion, Comentario, ISNULL(Comentario, 'Vistas al jardín') 
AS Comentarios FROM Habitaciones; 

Resultado

NumHabitacion

Comentario

Comentarios

1

Bella vista

Bella vista

2

 

 

3

NULL

Vistas al jardín

4

NULL

Vistas al jardín

Comprobamos que las habitaciones que tienen la columna Comentario a NULL se sustituyen por el valor «Vistas al jardín». La segunda fila contiene un espacio, que no se considera como NULL.

2. Comprobar varios valores: COALESCE

Esta función permite comprobar varios valores NULL de columnas en una misma función evitando así funciones « IF » « THEN », etc.

Comprueba cada columna y asigna el resultado de izquierda a derecha. Se asigna la primera columna no nula. Si todas las columnas son NULL, la función tomará el valor por defecto que debe ser el último parámetro.

Sintaxis

COALESCE(<columna1>, <columna2>,... <valor por defecto>); 

Ejemplo

Si se tienen los siguientes datos en la tabla Tarifas:

idTarifa

Hotel

tipoHabitacion

FechaInicio

FechaFin

Precio

idTarifa

60

1

4

2022-04-15

2022-09-30

69,99

60

61

1

5

NULL

2022-09-30

81,49

61

62

1

6

NULL

NULL

NULL

62

Queremos tipar las tarifas en función del contenido de estas...

Ejercicios

Estos ejercicios se basan en las tablas indicadas en la sección La selección de datos - Ejercicios sobre la selección de datos del capítulo anterior.

Primer ejercicio

Recuperar la fecha del día en formato DD-MM-YYYY.

Segundo ejercicio

Convertir la siguiente cadena en mayúsculas y a continuación buscar la posición de la cadena ’GOL’.

’En el partido Málaga - Barcelona se marcó el primer gol en el minuto 69’

Tercer ejercicio

Eliminar los espacios a la izquierda y agregar ’-’ a la siguiente cadena hasta obtener una cadena de 50 caracteres.

’             El tiempo no permite realizar el trabajo’

Cuarto ejercicio

Seleccionar las películas cuyo director se llame ’LUC’ y que se hayan estrenado entre el ’01/01/85’ y el ’30/05/1995’.

Quinto ejercicio

Mostrar la fecha y la hora del día en el formato:

Hoy es Viernes 25 de septiembre de 2021 y son las 16 horas 26 minutos

Sexto ejercicio

Calcular el número de días que han pasado desde el estreno de todas las películas de la tabla PELICULAS.

A continuación mostrar estas cifras expresadas en meses.

Soluciones de los ejercicios

Primer ejercicio

Recuperar la fecha del día en formato DD-MM-YYYY.

SELECT TO_CHAR(CURRENT_DATE,'DD/MM/YYYY') FROM DUAL; 

Segundo ejercicio

Convertir la siguiente cadena en mayúsculas y a continuación buscar la posición de la cadena ’GOL’.

’En el partido Málaga - Barcelona se marcó el primer gol en el minuto 69’

SELECT INSTR(UPPER('En el partido Málaga - Barcelona se marcó el primer 
gol en el minuto 69'),'GOL') AS POSICION FROM DUAL; 
 
  POSICION 
---------- 
        52 

Tercer ejercicio

Eliminar los espacios a la izquierda y agregar ’-’ a la siguiente cadena hasta obtener una cadena de 50 caracteres.

’             El tiempo no permite realizar el trabajo’

SELECT RPAD(LTRIM('           El tiempo no permite realizar 
el trabajo'),50,'-') AS MEF FROM DUAL;  
  
MEF  
---  
  
El tiempo no permite realizar el trabajo--- 

Cuarto ejercicio

Seleccionar las películas cuyo director se llame ’LUC’ y que se hayan estrenado entre el ’01/01/85’ y el ’30/05/1995’.

SELECT * FROM PELICULA T1 WHERE 
   EXISTS (SELECT IDENT_DIRECTOR FROM DIRECTOR WHERE NOMBRE 
= 'LUC' AND IDENT_DIRECTOR = T1.IDENT_DIRECTOR) 
   AND FECHA_ESTRENO BETWEEN ('01/01/85') AND ('30/05/1995') 
ORDER BY TITULO; 

o

SELECT * FROM PELICULA T1, DIRECTOR T2 WHERE  
       T1.IDENT_DIRECTOR = T2.IDENT_DIRECTOR AND  
       T2.NOMBRE = 'LUC' AND  
       FECHA_ESTRENO BETWEEN ('01/01/85') AND ('30/05/1995')  
ORDER BY TITULO; 

o

SELECT * FROM PELICULA T1 WHERE   
   T1.IDENT_DIRECTOR IN (SELECT IDENT_DIRECTOR FROM  
DIRECTOR WHERE NOMBRE = 'LUC')   
   AND FECHA_ESTRENO BETWEEN ('01/01/85') AND ('30/05/1995')  
ORDER BY TITULO; 

Este ejercicio muestra claramente que existen diferentes posibilidades al crear una consulta obteniendo el mismo resultado.

Si hay índice...