Manual SQL para la base de datos Oracle

ÍNDICE:

1. SELECT

2. CAMBIAR NOMBRE DEL CAMPO

3. OPERADORES DE COMPARACIÓN

———>3.1. = (igual), != (diferente), <> (distinto)

———>3.2. > (mayor), >= (mayor o igual), < (menor), <= (menor o igual)

———>3.3. BETWEEN, AND, OR

———>3.4. LIKE

———>3.5. IS NULL, IS NOT NULL

———>3.6. IN(), NOT IN()

4. ORDER BY

5. JOIN

6. PSEUDOCOLUMNAS

7. OPERADORES ARITMÉTICOS

———>7.1. FUNCIONES NUMÉRICAS

8. FUNCIONES PARA CARÁCTERES

9. FUNCIONES PARA FECHAS

10. FUNCIONES DE CONVERSIÓN

11. FUNCIONES AGREGADAS

 

1. SELECT

  • La sentencia SELECT sirve para realizar una consulta.

Obtenemos toda la tabla de alumnos:

SELECT * FROM alumnos;

sql-select

Obtenemos toda la tabla de profesores:

SELECT * FROM profesores;

sql-select-2
El símbolo (*) se utiliza para sacar todos los campos de la tabla.

  • Obtenemos campos específicos de las tablas alumnos y profesores. Como queremos obtener campos concretos de la tabla ya no debemos utilizar el símbolo (*).

De la tabla alumnos obtenemos el campo nombre y apellidos:

SELECT nombre, apellidos FROM alumnos;

select-campos

De la tabla profesores obtenemos el campo DNI:

SELECT dni FROM profesores;

select-campos2

2. Cambiar nombre del campo (alias)

  • Cambiar de nombre los encabezados de las columnas para que se visualice con el nombre que le indiquemos. Por ejemplo existe un campo en la tabla de profesores que se llama “tel” que hace referencia al telefóno, ahora nosotros queremos que cuando ejecutemos la sentencia SQL el campo se muestre con el nombre “telefono”.
SELECT tel AS "teléfono" FROM profesores;

alias-sql

3. Operadores de comparación

  • 3.1. = (igual), != (diferente), <> (distinto)

Símbolo: = (igual)

Vamos a buscar en la tabla alumnos la alumna que se llame Lucia e imprimiremos todos sus campos.

SELECT * FROM alumnos WHERE nombre = 'Lucia';

igual-sql

Símbolo: ¡= (diferente) y <> (distinto)

Ahora vamos a visualizar todos los alumnos que no se llamen Lucia, bien podemos usar ¡= o <>.

SELECT * FROM alumnos WHERE nombre != 'Lucia';
 
SELECT * FROM alumnos WHERE nombre <> 'Lucia';

diferente-sql

  • 3.2. > (mayor), >= (mayor o igual), < (menor), <= (menor o igual)

Por ejemplo en la tabla alumnos cada alumno tiene un campo que indica la nota media del curso anterior.

Visualizaremos los datos de aquellos alumnos que tenga de nota media más de un 8.

SELECT *  FROM alumnos WHERE nota_media_anterior > 8;

mayor-sql-oracle

Mostramos los alumnos que tienen de nota media menos de un 7.

SELECT *  FROM alumnos WHERE nota_media_anterior < 7;

menor-sql-oracle

  • 3.3. BETWEEN, AND y OR

BETWEEN:

Se usa para trabajar con rangos, muy útil para cuando se trabaja con fechas.

Para mostrar los alumnos que tienen de nota media del curso anterior entre un 6 y un 7.

SELECT * FROM alumnos WHERE nota_media_anterior BETWEEN 6 AND 7;

between-sql-oracle

AND:

Se utiliza para encadenar en la WHERE varias condiciones que tienen que cumplirse, por ejemplo para mostrar los datos de las alumnas que se llamen “Alicia” y tengan de nota media del curso anterior más de un 8.

Nuestra sentencia va a tener dos condiciones.

SELECT *  FROM alumnos WHERE nota_media_anterior > 8 AND nombre = 'Lucia';

La consulta devolverá resultados si se cumple las dos condiciones.

and-sql-oracle

OR:

Se utiliza de la misma manera que el and pero a diferencia de este no se deben cumplir estrictamente todas las condiciones, la condición devolverá datos si se cumple alguna condición como la siguiente sentencia.

SELECT *  FROM alumnos WHERE nota_media_anterior > 8 OR  nombre = 'Lucia';

La consulta devolverá resultados si se cumple que la nota sea más de un 8 o que el nombre del alumno sea llame Alicia.

or-sql-oracle

  • 3.4. LIKE

La sentencia LIKE sirve para buscar una serie de caracteres en una secuencia. Por ejemplo, intentamos averiguar la dirección de un profesor, en el campo “direccion” de la tabla profesor está almacenado la siguiente información:

C/ Avenida de la estatua del Elefante

Nosotros queremos encontrar que profesor tiene almacenado la calle anterior pero solo recordamos la palabra “Elefante”, con la siguiente sentencia se realizará una búsqueda buscando la palabra Elefante entre todas las direcciones de los profesores.

SELECT * FROM profesores WHERE direccion LIKE '%Elefante%';

like-sql-oracle

Ahora nos interesa comprobar que el primer apellido de algún profesor comience por la palabra “Lun”, para ello usaremos el comando LIKE pero con la diferencia al anterior que en este caso solo comprobaremos la primera palabra del campo Apellidos, por lo tanto colocaremos el símbolo “%” unicamente al final.

SELECT * FROM profesores WHERE apellidos LIKE 'Lun%';

like-sql-oracle-2jpg

  • 3.5. IS NULL, IS NOT NULL

El operador IS NULL Y IS NOT NULL comprueba si un valor es nulo o no.

Los alumnos tienen un campo llamado ciudad que indica donde la ciudad que nació cada uno.

IS NULL:

Comprobamos los alumnos que en la columna “Ciudad” su campo este vacio (NULL).

SELECT * FROM alumnos WHERE ciudad IS NULL;

is-null-sql

IS NOT NULL:

Al contrario, comprobamos los alumnos que en el campos de la columna “Ciudad” sean difentes a vacio (NULL).

SELECT * FROM alumnos WHERE ciudad IS NOT NULL;

is-null-sql

  • 3.6. IN(), NOT IN()

Comprueba si está o no está un valor específico.

Vamos a comprobar si está o no un DNI dentro de la tabla profesores.

IN ():

Vemos si el DNI con valor “22222222” está en nuestra columna DNI.

SELECT * FROM alumnos WHERE dni IN (22222222);

in-sql-oracle

NOT IN():

Visualizaremos todos los valores DNI que no sean el “22222222”

SELECT * FROM alumnos WHERE dni NOT IN (22222222);

not-in-oracle-sql

4. Order By

Se usa esta cláusula para ordenar filas utilizando una columna específica.

Para indicarle el orden a motrar se debe utilizar ASC (ascendente) o DESC (descenciente).

Es muy usada esta cláusula para ordenar fechas.

Para nuestro ejemplo queremos ordenar las notas medias del curso anterior de los alumnos.

ASC:

Mostramos las notas de forma acendente, por lo tanto visualizaremos primero las notas más bajas y después las notas más altas.

SELECT nombre, nota_media_anterior FROM alumnos ORDER BY nota_media_anterior ASC;

asc-sql

DESC:

Mostramos las notas de forma descendente, por lo tanto visualizaremos primero las notas más altas y después las notas más bajas.

SELECT nombre, nota_media_anterior FROM alumnos ORDER BY nota_media_anterior DESC;

desc-sql

5. JOIN

Se usa para visualizar columnas que están creadas en diferentes tablas.

Por ejemplo queremos saber por cada alumno el nombre del profesor que tiene asignado, para ello utilizaremos el campo DNI del profesor.

El funcionamiento será el siguiente:

  1. En la tabla profesor estamos usando el campo DNI para identificar a los profesores.
  2. En la tabla alumno existe una columna llamada DNI_PROFESOR que contiene el DNI del profesor.
  3. Estas dos tablas se relacionan entre si por el DNI del profesor.

Las dos consultas siguientes devuelven los mismos resultados, la diferencia es que la primera consulta se usa como nombre para identificar la tabla el nombre de la tabla (alumnos.nombre) y la segunda nos hemos inventado un alias para hacer referencia a la tabla con la que deseemos trabajar(a.nombre).

SELECT alumnos.nombre, alumnos.apellidos, profesores.nombre AS "NOMBRE PROFESOR" FROM alumnos JOIN profesores ON alumnos.dni_profesor=profesores.dni;
SELECT a.nombre, a.apellidos, p.nombre AS "NOMBRE PROFESOR" FROM alumnos a JOIN profesores p ON a.dni_profesor=p.dni;

Existen otros tres tipos de JOIN:

LEFT OUTER JOIN:

Devuelve todas las filas de la tabla de la izquierda, y las filas coincidentes de la tabla a la derecha.

SELECT a.nombre, a.apellidos, p.nombre AS "NOMBRE PROFESOR" FROM alumnos a LEFT OUTER JOIN profesores p ON a.dni_profesor=p.dni;

RIGHT OUTER JOIN:

Devuelve todas las filas de la tabla de la derecha, y las filas coincidentes de la tabla a la izquierda

SELECT a.nombre, a.apellidos, p.nombre AS "NOMBRE PROFESOR" FROM alumnos a RIGHT OUTER JOIN profesores p ON a.dni_profesor=p.dni;

FULL OUTER JOIN:

Devuelve todas las filas cuando hay una relación en una de las tablas

SELECT a.nombre, a.apellidos, p.nombre AS "NOMBRE PROFESOR" FROM alumnos a FULL OUTER JOIN profesores p ON a.dni_profesor=p.dni;

 

6. Pseudocolumnas

SYSDATE:

La declaración SYSDATE muestra la fecha actual del sistema.

SELECT SYSDATE AS "FECHA" FROM DUAL;

USER:

La declaración USER muestra el nombre del usuario actual del sistema.

SELECT USER FROM DUAL;

ROWNUM :

ROWNUM nos permite limitar el número de filas devueltas, por ejemplo si solo queremos que se muestren 20 filas usaremos: ROWNUM < 20

SELECT * FROM alumnos WHERE ROWNUM < 3;

7. Operadores aritméticos

Usamos estos operadores para operar sobre campos ya existentes de nuestra tabla, por ejemplo dentro de la tabla alumnos si queremos que la nota media del curso anterior (nota_media_anterior) no sea sobre 10 y queremos que se muestre las notas sobre 1000 creamos una nueva columna llamada “NOTA SOBRE 1000” donde le insertamos el resultado que nos de la operación (nota_media_anterior * 100).

SELECT nombre, (nota_media_anterior * 100) AS "NOTA SOBRE 1000" FROM alumnos;

También podemos obtener el porcentaje de las notas.

SELECT nombre, ((nota_media_anterior * 100)/1000) AS "PORCENTAJE" FROM alumnos;

Se puede usar operadores aritméticos para crear expresiones para cálculos sobre los datos de las tablas. Los operadores aritméticos incluyen:

  • Signo más + para la suma
  • Signo menos para la resta
  • Asterisco * para la multiplicación
  • Raya vertical / para la división
  • 7.1 Funciones Númericas

ROUND:

Al realizar una operación numérica puede darnos el caso que el resultado tenga muchos decimales como el siguiente ejemplo.

SELECT nombre, (nota_media_anterior / 3) AS "DIVISIÓN CON DECIMALES" FROM alumnos;

oracle-division-con-decimales

Para evitar que nuestro resultado pueda tener tantos decimales usaremos la función ROUND.

Podemos indicarle el número de decimales que queremos visualizar, en nuestro caso le hemos indicado que nos visualice 1 de la siguiente forma: ROUND(nota_media_anterior / 3, 1)

SELECT nombre, ROUND(nota_media_anterior / 3, 1) AS "DIVISIÓN CON REDONDEO" FROM alumnos;

oracle round limitar decimalesTRUNC:

-Se puede utilizar la función TRUNC para truncar los datos numéricos, en nuestro caso lo truncaremos a 0 para que no muestre ningún decimal: TRUNC(nota_media_anterior / 3, 0)

SELECT nombre, TRUNC(nota_media_anterior / 3, 0) AS "RESULTADO TRUNC" FROM alumnos;

trunc sql base de datos oracle

MOD:

La función MOD devuelve el resto de una división.

MOD se utiliza a menudo para determinar si un número es par o impar.

SELECT nombre, MOD(nota_media_anterior, 2) AS "RESULTADO MOD" FROM alumnos;

sql mod oracleComprobamos que Marcos tiene como resultado un 0 ya que el tenía de nota un 6 por lo tanto es un número par, por otra parte Alicia y Lucia tenian un 7 y un 9 por lo tanto devuelve el valor 1 que nos indica que son números impares.

 

8. Funciones para carácteres

UPPER:

Esta función transforma los carácteres de minúsculas a mayúsculas.

SELECT UPPER(nombre) AS "CARACTERES EN MAYÚSCULAS" FROM profesores;

upper sql mayusculas

INITCAP:

Convierte el primer caracter a mayúsculas.

SELECT INITCAP(nombre) AS "PRIMER CARACTER A MAYÚSCULAS" FROM profesores;

initcap oracle

RTRIM y LTRIM :

Se puede utilizar RTRIM y LTRIM para eliminar los espacios que se encuentran al inicio y al final de
los caracteres.

A parte de eso concatenaremos el valor del nombre del profesor con los apellidos del profesor agregándole un espacio entre ellos.

SELECT RTRIM(nombre) || ' ' || LTRIM(apellidos) AS "RESULTADO" FROM profesores;

RTRIM LTRIM oracle

TRIM:

Elimina los espacios del principio y final.

SELECT TRIM(nombre) || ' ' || TRIM(apellidos) AS "RESULTADO" FROM profesores;

RPAD:

Se usa para añadir espacios.

En nuestro caso vamos a añadirle 20 espacios a la derecha del valor del nombre.

SELECT RPAD(nombre, 20, ' ') AS "AÑADIMOS ESPACIOS", apellidos FROM profesores;

RPAD, añade espacios

 

SUBSTR:

Selecciona un grupo de caracteres dentro de una cadena.

A la función SUBSTR se le debe pasar tres parámetros que son: columna donde debe realizar los cambios + situación a partir de la que debe de mostrar caracteres y número de caracteres que se quiere visualizar.

En nuestro caso queremos que se muestren las tres primeras letras de cada nombre de los profesores.

SELECT nombre, SUBSTR(nombre, 1, 3) AS "TRES PRIMEROS CARACTERES" FROM profesores;

SUBSTR cadena caracteres

Si ahora quiero que comience desde la segunda letra a contar y me muestre 4 caracteres sería de la siguiente manera:

SELECT nombre, SUBSTR(nombre, 2, 4) AS "RESULTADO" FROM profesores;

SUBSTR cortar cadenas

LENGH:

Cuenta el número de caracteres.

SELECT nombre, LENGTH(nombre) AS "CONTADOR CARACTERES" FROM profesores;

length oracle

REPLACE:

Utilizar REPLACE para reemplazar caracteres de una cadena o expresión.

Para nuestro ejemplo duplicaremos la letra a en los nombres de alumnos donde aparezca.

SELECT nombre, REPLACE(nombre,'a','aa') "Doble a" FROM alumnos;

replace sql

9. Funciones para fechas

Obtenemos la fecha/hora del sistema con la función SYSDATE.

  • Para obtener el año actual:
  • SELECT TO_CHAR(EXTRACT(YEAR FROM SYSDATE)) AS "Año actual" FROM DUAL;

    Devuelve: 2016

  • Para obtener el mes actual:
  • SELECT TO_CHAR(EXTRACT(MONTH FROM SYSDATE)) AS "Mes actual" FROM DUAL;

    Devuelve: 8

  • Para obtener el día actual:
  •   SELECT TO_CHAR(EXTRACT(DAY FROM SYSDATE)) AS "Día actual" FROM DUAL;

    Devuelve: 5

    Las fechas pueden visualizarse con diferentes formatos como:

  • Formato 1:
  • SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY AD') "Fecha 1 de Hoy" FROM DUAL;

    Devuelve: 05-AGO-2016 DC

  • Formato 2:
  • SELECT TO_CHAR(SYSDATE, 'FMMonth DD YYYY') "Fecha 2 de Hoy" FROM DUAL;

    Devuelve: Agosto 5 2016

  • Formato 3:
  • SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "Fecha 3 de Hoy" FROM DUAL;

    Devuelve: 08-05-2016 20:17:31

    10. Funciones de conversión

    Se puede realizar conversión de tipos dentro de Oracle, por ejemplo pasar un String a un Number o viceversa, por supuesto tiene limitaciones ya que lógicamente no se va a poder pasar un carácter a un número, ya que una letra no es un número, pero si que se podrán convertir un String que contenga números a tipo Number.

  • Convertir de String a Number
  • En este caso vamos a convertir la cadena ‘888,44’ a un tipo Number para así poder realizar una operación aritmética de suma.

    SELECT TO_NUMBER('888,44') + 100 FROM DUAL;

    Resultado: 988,44

    SELECT TO_DATE('27-OCT-98', 'DD-MON-RR') FROM DUAL;

    Resultado: 27/10/98

  • Convertir de String a Timestamp(Fecha y hora)
  • SELECT TO_TIMESTAMP('08-Sep-05 20:12:15.324005', 'DD-Mon-RR HH24:MI:SS.FF') FROM DUAL;

    Resultado: 08/09/05 20:12:15,324005000

    11. Funciones agregadas

    COUNT:

    La función Count cuenta el número de filas devueltas tras realizar una búsqueda.

    SELECT COUNT(*) FROM alumnos WHERE nota_media_anterior BETWEEN 6 AND 7;

    Max:
    La función Max devuelve la fila con el valor más alto, por ejemplo si queremos obtener el estudiante con la nota media más alta.

    Min:
    Min es lo contrario a la función Max, obtendríamos el estudiante con la nota media más baja.

    AVG:
    Devuelve el valor promedio, la podemos usar para obtener la nota media de todos los estudiantes.

    DISTINCT:
    La función DISTINCT elimina las filas duplicadas de los resultados obtenidos de una consulta SQL.

    COALESCE:

    Cuando en nuestra tabla tenemos campos a (null) y consultamos sobre estos los argumentos que nos devolverá nuestra consulta serán (null), esto no siempre queda demasiado “limpio” y una manera más eficaz para rellenar los registros que nos han dado (null) con un argumento es la función COALESCE.

    Por poner un caso práctico si miramos el campo ciudad de nuestra tabla alumnos puede ser que algunos de nuestros alumnos no tengan disponible esa información por alguna razón, por lo tanto en vez que nos salga el campo a (null) podemos poner que cuando un alumno no tenga ciudad se obtenga un “no disponible”.

      SELECT COALESCE(ciudad , 'No disponible') AS "ciudad" FROM alumnos;

    UTL_MATCH.EDIT_DISTANCE_SIMILARITY:

    La función UTL_MATCH.EDIT_DISTANCE_SIMILARITY sirve para comparar dos cadenas y devolverte un resultado del 0 a 100 dependiendo el grado de similitud entre ambas.

    Iván Martínez

    Ingeniero Informático apasionado por el mundo del desarrollo WEB. Me gusta caminar por este mundo lleno de tecnologías y día a día aprender cosas nuevas como el doctor Frankenstein ¡dadme cosas para experimentar!

    Deja un comentario

    Tu dirección de correo electrónico no será publicada.