Curso Online Oracle SQL

¡Bienvenido al Curso Online de Oracle SQL! Si te apasiona la codificación y deseas destacarte en el mundo de la programación con una amplia gama de herramientas, aprender a manejar el lenguaje Oracle SQL es una excelente alternativa para sobresalir.

Nuestro curso de Oracle SQL está diseñado para capacitar a programadores y brindarles la oportunidad de acceder a numerosas oportunidades laborales. Aprenderás a realizar consultas y acceder a Oracle, así como a modificar, eliminar e insertar datos. También adquirirás habilidades para administrar tablas y objetos, utilizar funciones tanto de grupo como simples, y optimizar el uso de los operadores.

Este curso es perfecto para principiantes, ya que no se requieren conocimientos previos de SQL ni de programación. Solo necesitas tener entusiasmo por aprender y disponer de un ordenador. Aunque se recomienda tener algún conocimiento previo de programación, no es esencial, lo que hace que este curso sea accesible para todos.

Oracle se destaca como una solución robusta y eficaz para la administración global de bases de datos, adoptando la arquitectura cliente/servidor. A través de PL/SQL, un lenguaje de quinta generación, altamente potente y capaz, podrás manipular y administrar la base de datos de manera efectiva.

Con el Curso de Oracle Database 21c: Introducción a SQL, obtendrás una comprensión profunda de las funcionalidades avanzadas de SQL, como subconsultas, operadores SET y funciones de grupo. Además, aprenderás a consultar y manipular datos, utilizar vistas de diccionario para recuperar metadatos y crear informes sobre esquemas de objetos. También explorarás el uso de funciones de fecha y hora, así como el soporte de expresiones regulares en SQL.

Este curso forma parte del programa Workforce Development Program (WDP) de Oracle, que busca equipar a la próxima generación de profesionales TI con habilidades en tecnologías Oracle. También te proporcionará ayuda para la preparación de exámenes de certificación asociados al curso, agregando un valor distintivo a tu perfil profesional.

Nuestro programa de formación está dirigido a desarrolladores de software, analistas de negocios, gestores de Data Warehouse, programadores, desarrolladores de formularios, programadores PL/SQL y analistas de sistemas que deseen perfeccionar sus habilidades y conocimientos en las funcionalidades más sofisticadas de SQL.

¡No esperes más! Únete a nuestro Curso Online de Oracle SQL y potencia tus habilidades para destacar en el apasionante mundo de la programación con Oracle.

Características oracle database.

La  versión 11G de Oracle, la tenemos disponible en diferentes ediciones  según la necesidad de organización por diferentes tamaños.

• Standard  edition one (SE1): Es una edición que destaca por la facilidad de uso,  de su potencia y de sus prestaciones, nos permite trabajar en grupo, la  organización departamental y el desarrollo web en servidores  individuales con 2 sockets para comunicación.

• Standard  edition (SE) Edición diposnible para servidores individuales o en  cluster, su capacidad máxima es de 4 sockets, por defecto contiene  Oracle RAC

• Enterprise  edition (EE): Es una edición empresarial que nos proporciona fiabilidad  y seguridad para la gestión de tatos en app OLTP de misíon critica,  almacenes de datos (DW), gestión de contenico y desarrollo web.

• Personal  edition: Es una edición personal para un único usuario compratible con  las ediciones mencionadas en los puntos anteriores.

• Express  edition: Es una edición para iniciarse que contiene una pqueña bas de  datos, es una edición liberada. Es rápida de desrcargar y sencilla de  administrar, tiene ciertas resticciones como serían 4GB de datos de  usuario, 1GB de memoria y una CPU por máquina que se instala.

SQL  y PL/SQL son el núcleo del desarrollo de aplicaciones de las bases de  datos de Oracle y proporcionan un modelo de datos simple, unificado y  conocido.

Los podemos usar en muchas aplicaciones, y otras tecnologías y APIs, como, por ejemplo:

• Java (JDBC)

• Oracle call inerface (OCI)

• Oracle C++ call interface (OCCI)

• XML SQL UTILITY (XSU)

Podemos escribir los paquetes en JAVA.

SQL

Podemos programar de forma estándar las definiciones y la manipulación de datos.

Las  bases de datos SQL implican un modelo relacional y odas las operaciones  en una base de datos Oracle se realizan mediante sentencias SQL  conforme a las sintaxis SQL siguientes:

• DDL: Créate, Alter, Drop, etc….

• DML: Insert, Delete, Etc.

• CONTROL DE TRANSACCIONES: Commit, Rollback, etc….

• CONTROL DE SESIÓN: Alter sesión, Set role, etc….

• CONTROL DE SISTEMA: Alter system.

PL/SQL

Lenguaje  procedente de Oracle que mezcla SQL con la programación estructurada.  (IF, THEN, WHILE ETC…) y dispone de ciertas ventajas sobre SQL.

Tipos de Diseños Relacionales sql.

Existen 3 tipos de diseños relacionales:

• Diseño  Conceptual: Este diseño consiste en describir esquemas conceptuales y  el contenido de la información de la base de datos y no las estructuras  de almacenamiento que se necesita para el manejo de esta información.

• Diseño Lógico: Con este diseño se considera cada instancia de una entidad como una fila de una tabla y se crean:

• Las tablas: Usando las entidades y relaciones que existen.

• Las columnas: Usando los atributos de una relación.

• Diseño Físico: Con este diseño se crean los ficheros físicos que van a almacenar los diferentes datos de una BD.

Oracle entornos de trabajo.

Las principales herramientas de las que podemos disponer para el desarrollo de las aplicaciones de bases de datos de Oracle son:

• SQL  Developer: Es un entorno de desarrollo integrado (IDE) para trabajar  con SQL en bases de datos Oracle. Oracle Corporation ofrece este  producto gratis; utiliza el kit de desarrollo de JAVA.

• JDEVELOPER:  Es una aplicación IDE que se utiliza para desarrollar las aplicaciones  de servicios mediante JAVA, XML, WebServices y SQL.  JDeveloper tiene un  gran entorno de desarrollo formado por:

• MODELADOR: Sirve para diseñar las estructuras como son las entidades, los atributos o las relaciones.

• Navegador: Que se usa para visualizar los objetos de una base de datos.

• SQL WorkSheet: Nos permite ejecutar los comandos SQL.

• EDITOR: Nos permite crear un desarrollo de PL/SQL.

• SQL * PLUS: Esta aplicación se instala por defecto y ofrece una interfaz para el desarrollo en forma de línea de comandos.

Sql sentencia SELECT.

• La sentencia SELECT permite:
  Recuperar los datos de todas las columnas que forman una tabla.
  Recuperar los datos de las columnas deseadas de una tabla.
  Recuperar los datos de las columnas de varias tablas.
  Aplicar funciones o cálculos a los datos recuperados.

• Partes de la sentencia SELECT:

• SELECT, toda consulta de base de datos tiene que empezar por la sentencia
  Select
  Después de la sentencia SELECT, hay que indicar que campo se quiere recuperar:
  "*", con el asterisco se indica que se quiere recuperar todas las columnas que forman una tabla.
  SELECT * FROM COUNTRY.
  Listado  de columnas, si no se quiere recuperar todas las columnas de una tabla  se puede escribir directamente el nombre de la columna o columnas que se  quiere recuperar
  SELECT COUNTRY_ID FROM COUNTRY
  FROM,  esta cláusula es obligatoria y después de indicar que columnas se  quieren recuperar, hay que usar la cláusula FROM, la cual se utiliza  para indicar de qué tabla o vista se va a obtener los datos.
  SELECT * FROM COUNTRY

Sql cláusula WHERE.

WHERE, es una palabra reservada de SQL y es opcional, que se utiliza para realizar un filtrado sobre los datos de una tabla.

Cuando se ejecuta una sentencia SELECT sin la cláusula WHERE, se recuperan todos los registros(filas) de la tabla.

Cuando  se ejecuta una sentencia SELECT con la cláusula WHERE, solo se  recuperan los registros(filas) que cumplan la condición especificada.

• Syntaxis: SELECT * FROM table WHERE condición.

• Ejemplo: SELECT * FROM HR.COUNTRIES WHERE COUNTRY_ID = “AR”.

Operadores SQL Oracle.

Los operadores se utilizan para manipular la información que se recupera o se quiere filtrar en una sentencia SQL.

Tipos de operadores SQL:

• Operadores Aritméticos, son los operadores que se utilizan con campos numéricos, ellos son:
  +, suma.
  -, resta.
  *, multiplicación.
  /, división.

• Operadores de Concatenación de caracteres, para unir caracteres se usa el carácter:
  "||"

• Operadores de Unificación, se utilizan cuando se quieren unir el resultado de varias consultas, y son:
  UNION, concatena los resultados de dos consultas en un único conjunto de resultados. no incluyen filas duplicadas.
  UNION ALL, es igual que UNION, pero sí incluye filas duplicadas.
  INTERSET, devuelve filas distintas que son el resultado del operador de las consultas de entrada izquierda y derecha.
  EXCEPT, devuelve filas distintas de la consulta de entrada izquierda que no son de salida en la consulta de entrada derecha.
  MINUS, devuelve las filas devueltas por la primera consulta que no estén presentes en la segunda.

• Operadores de Comparación, se utilizan para comparar expresiones o valores:
  = , igual.
  <>, diferente.
  >, mayor que.
  <, menor que.
  >=, mayor igual que.
  <=, menor igual que.
  ANY, SOME si alguno de los valores de subconsulta cumple la condición.
  ALL, si todos los valores de subconsulta cumplen la condición.

• Operadores Lógicos, se utilizan para unir varias condiciones:
  NOT, si no se cumple la condición.
  AND, si se cumple ambas condiciones.
  OR, si se cumple una de las dos condiciones

• Operadores Especiales, se utilizan para que se cumplan ciertas condiciones especiales:
  LIKE, se usa para comparar un valor con los siguientes caracteres.
  "%" este carácter equivale a cualquier cadena excepto a un valor null
  "-", este carácter equivale a cualquier carácter individual.
  BETWEEN, para indicar un rango de valores.
  EXITS, para indicar si existe.
  IN, para indicar si el valor se encuentra dentro de una lista de valores.

Operador Valores Nulos sql Oracle.

Si en una tabla existe una columna de una fila sin valor, se considera que el valor de esta columna es un (NULL).

Un  nulo puede aparecer en cualquier tipo de columna de datos que no estén  restringidas por las restricciones de integridad, las cuales serían:

• NOT NULL

• PRIMARY KEY.

En Oracle los valores NULL no se representan con ningún carácter válido, no son ni 0 ni espacios, se considera un valor nulo.

Su sintaxis es:

• SELECT * FROM nom_tabla campo

• Ejemplo: SELECT * FROM HR.COUNTRIES WHERE COUNTRY_ID IS NOT NULL.

Orden de ejecución de los operadores SQL.

Los  operadores SQL, tienen una prioridad de ejecución, es decir, que porque  un operador este antes que otro, no significa que este se resuelva  antes.

El  orden de prioridad de los operadores es el siguiente, pero hay tener en  cuenta que hay operadores que tienen el mismo nivel de prioridad, por  lo cual, cuando se encuentran varios operadores del mismo nivel, se van  ejecutando de izquierda a derecha.

• Nivel de prioridad 1

• Operadores Aritméticos: *, /.

• Nivel de prioridad 2

• Operadores Aritméticos: +, –.

• Nivel de prioridad 3

• Operadores de Comparación: =, <>, <, <=, >, >=.

• Nivel de prioridad 4

• Operadores de Nulos y Especiales: IS NULL, LIKE, BETWEEN, IN.

• Nivel de prioridad 5

• Operadores Lógicos: NOT, AND, OR.

• EJEMPLO 4 + 2 * 3

• Solución sin prioridad: 4 + 2 * 3 = 6 * 3 = 18

• Solución con prioridad: 4 + 2 * 3 = 4 + 6 = 10

En  el caso de querer cambiar el orden de prioridad, se usan los signos de  paréntesis, cuando se encuentra un paréntesis, las operaciones que  existen dentro son las primeras que se tienen que ejecutar

• Ejemplo: (4 + 2) * 3

• Solución sin prioridad: (4 + 2) * 3 = (4) + 6 = 10

• Solución con prioridad: (4 + 2) * 3 = (6) * 3 = 18

Sq cláusula ORDER BY.

Cada  vez que se haga una consulta a una tabla, Oracle recupera los datos y  los muestra, pero el orden en que muestra los registros puede variar de  una consulta a otra.

• Si se quiere que siempre los devuelva en el mismo orden, o en el orden que desee, hay que usar la sentecia ORDER BY.

• ORDER BY, se utiliza para indicar como tienen que ir ordenados los resultados de la consultay tiene dos características:
  Se puede indicar por que columna o columnas hay que hacer la ordenación.
  Se puede indicar si la ordenación es descendente (DESC) o ascendente (ASC).

• Si  se desea ordenar la tabla COUNTRY por los nombres de las ciudades y de  forma descendente, hay que incluir la cláusula ORDER BY seguido del  campo COUNTRY_NAME y de la opción DESC.
  SELECT * FROM HR.COUNTRIES ORDER BY COUNTRY_NAME DESC

• Si, se quisiera realizar la ordenación de forma ascendente, se usará la opción ASC.
  SELECT * FROM HR.COUNTRIES ORDER BY COUNTRY_NAME ASC.

• En el caso de no indicar el tipo de ordenación de la cláusula ORDER BY,por defecto se ordena de forma ascendente.
  SELECT * FROM HR.COUNTRIES ORDER BY COUNTRY_NAME.

• Si  se desea ordenar la tabla COUNTRY por la región y el nombre de las  ciudades, hay que incluir la cláusula ORDER BY seguido de los campos  REGION_ID yCOUNTRY_NAME, los campos tienen que ir separados por una  coma.
  SELECT * FROM HR.COUNTRIES ORDER BY REGION_ID, COUNTRY_NAME DESC.

• Si  se quisiere realizar la ordenación de los campos región de forma  ascendente y el campo nombre de forma descendente, solo hay que indicar  el tipo de ordenación despues de cada campo.
  SELECT * FROM HR.COUNTRIES ORDER BY REGION_ID ASC, COUNTRY_NAME DESC.

Sql sentencia Distinct.

Especificando  las palabras claves distinct, que es una sentencia sql estandar, y la  palabra unique, que no una sentencia sql estandar, se obtiene un único  registro en el caso de que existan registro duplicados en una tabla.

La  sentencia distinct se aplica a todos los campos que se recuperan en la  sentencia select, no se puede aplicar a uno solo de ellos.

Si  se ejecuta la siguiente setencia, se obtendrá todo los nombres y  apellidos que no se repitan, ya que el distinct se aplica tanto para el  campo nombre como el apellido.

• SELECT nombre, apellido FROM empleados

Funciones de agregación sql.

Las  funciones de agregación en SQL permiten efectuar operaciones sobre un  conjunto de resultados, pero devolviendo un único valor agregado para  todos ellos.

Funciones:

• MAX: Devuelve el valor máximo de un conjunto de valores contenidos en un campo especifico de una tabla.
  Ejemplo: SELECT MAX(TALLA) FROM PERSONAS;

• MIN: Devuelve el valor mínimo de un conjunto de valores contenidos en un campo especifico de una tabla.
  Ejemplo: SELECT MIN(EDAD) FROM PERSONAS;

• COUNT: Devuelve el número de filas de la consulta, es decir, el número de registros que cumplen una determinada condición.
  Ejemplo: SELECT COUNT(ID_PER) FROM PERSONAS;

• AVG: Devuelve el valor medio de una columna de tipo numérico.
  Ejemplo: SELECT AVG(PESO) FROM PERSONAS;

• SUM: Devuelve la suma total de los valores de una columna de tipo numérico, solo se puede ejecutar en columnas numéricas.
  Ejemplo: SELECT SUM(PESO) FROM PERSONAS;

Funciones de agregación con valores NULL.

Todas las funciones de agregación ignoran los valores nulos, excepto la función de agregación Count(*).

• SELECT count(*) FROM hr.departments;
  Resultado: 31.
  SELECT count(manager_id) FROM hr.departments;
  Resultado: 22.

La  función nvl() nos permite obtener un valor concreto en vez de un valor  null, por lo que podemos usarlo para evitar que se descarten los valores  nulos.

• EJEMPLO:
  SELECT count(nvl(manager_id)) FROM hr.departments;
  Resultado: 31.

La  modificación de un valor nulo por otro valor usando la function NVL, no  se realiza fisicamente sobre la table, simplemente se realiza  internamente para realizar la consulta.

Cláusula GROUP BY sql.

La  cláusula GROUP BY se utiliza para agrupar o particionar el conjunto de  datos de la tabla, es decir, con la cláusula GROUP BY podemos aplicar  sentencias a grupos de registros, los cuales estarán agrupados por el  valor de una columna o grupo de columnas.

Sintaxis

• SELECT (sentencia o función) FROM nombre_tabla GROUP BY (campos_agrupacion);

Hay  que entender que cuando se utiliza la cláusula GROUP BY, no se puede  recuperar campos de la tabla sin aplicar una función o sentencia al  campo.

• SELECT SUM(salary), department_id FROM employees GROUP BY department_id;

La sentencia GROUP BY se puede aplicar a más de un campo

Cláusula HAVING sql oracle.

La  cláusula HAVING aplica una condición al conjunto de resultados  agrupados que una consulta devuelve cuando utiliza la cláusula 

GROUP BY,  es decir:

• Solo se utiliza cuando también se usa la cláusula WHERE.

• Solo se puede aplicar a las columnas devueltas en la consulta, no pueden aplicarse a una columna de la tabla.

La  condición HAVING se aplica después de que se aplican todas las  condiciones de la cláusula WHERE y se completan todas las operaciones de  GROUP BY, es decir, se ejecuta después de las cláusulas WHERE y GROUP  BY.

La condición toma la misma forma que cualquier condición de la cláusula WHERE.

• EJEMPLO:
  SELECT department_id, MIN(salary)

FROM  hr.employees

GROUP BY department_id

HAVING MIN(salary) > 24000;

Diferencias entre las clausulas WHERE y HAVING:

• La cláusula WHERE se aplica a todos los registros de la tabla.

• La cláusula HAVING se aplica a los datos devueltos por la sentencia SELECT cuando usa la cláusula GROUP BY.

SELECT  DEPARTMENT_ID, FIRST_NAME, SALARY FROM EMPLOYEES EM WHERE SALARY =  (SELECT MAX(SALARY) FROM EMPLOYEES E WHERE EM.DEPARTMENT_ID =  E.DEPARTMENT_ID ).

Alias sql oracle.

Los  alias se pueden asignar tanto a las tablas como a sus columnas y es una  manera de referirse a ellas sin tener que escribir todo su nombre, de  esta forma cuando se desea referir a una tabla o a una columna, no es  necesario escribir su nombre, sino simplemente su alias.

Los alias se utilizan principalmente para no tener que escribir nombres largos y se utilizan normalmente:

• Cuando hay más de una tabla en una consulta.

• Cuando se utilizan funciones en una consulta.

• Cuando los nombres de los campos son muy grandes o difíciles de leer.

• Cuando se combinan más de dos columnas.

Sintaxis alias tabla: SELECT columna FROM nombreTabla AS aliasTabla.

• Ejemplo: SELECT * FROM countries c. (Donde C será el alias de la tabla countries)

Sintaxis alias columna: SELECT columna AS nombreColumna FROM tabla

• Ejemplo: SELECT country_id AS id FROM countries. (Donde id será el alias de la columna country_id en la tabla countrie

Definición de subconsultas en sql

Una  subconsulta no es más que una consulta que se realiza dentro de otra  consulta. Es decir, la consulta padre utiliza los datos que devuelve la  subconsulta para realizar una comparación, visualizar los datos, etc.

Se puede utilizar tres formas de sintaxis para crear una subconsulta:

• Comparación: Es una expresión y un operador de comparación que compara la expresión con el resultado de la subconsulta.

• Expresión: Es una expresión por la que se busca el conjunto resultante de la subconsulta.

• Instrucción  sql: Es un select que sigue el mismo formato y reglas que cualquier  otra instrucción select. Debe ir entre paréntesis.

Las subconsultas pueden ir:

• Junto a los campos recuperados de una sentencia select.
  SELECT  employee_id, first_name, (SELECT department_id FROM job_history jh  WHERE jh.employee_id = e.employee_id) departamento FROM employees e.

• En la cláusula from:
  SELECT COUNT (*) FROM (SELECT * FROM employees WHERE department_id = 90).

• En la cláusula where:
  SELECT * FROM job_history WHERE job_id IN (SELECT job_id FROM jobs WHERE job_title = 'Programmer').

• En la cláusula having:
  SELECT  department_id "id departamento", COUNT (*) "total empleados" FROM  employees GROUP BY department_id HAVING COUNT (*) = (SELECT max (COUNT  (*)) FROM employees GROUP BY department_id).

Orden o prioridad de ejecución:

• Cuando  se realiza una consulta que utiliza una subconsulta, se ejecuta primero  la subconsulta para obtener los datos y después ejecuta la consulta.

• La subconsulta se ejecuta por cada fila de la consulta principal.

En una subconsulta:

• No  tiene sentido la cláusula order by, ya que los resultados de una  subconsulta se utilizan internamente y no son visibles al usuario.

• Los  nombres de la tabla que aparecen en ella pueden referirse a columnas de  la tabla de la consulta principal y se conocen como referencia externa.

• El resultado de una subconsulta puede ser un valor simple o más de un valor.

Utilización de subconsultas:

• Para realizar comparaciones.
  SELECT  department_id, AVG(salary) "salario medio" FROM employees GROUP BY  department_id          HAVING AVG(salary) > (SELECT AVG(salary) FROM  employees).

• Para realizar tablas temporales para su consulta o comparación.
  SELECT COUNT (*) FROM (SELECT * FROM employees WHERE id_departamento = 90).

• Para crear sentencias dml.
  UPDATE employees SET salary = salary + 2000 WHERE job_id IN (SELECT job_id FROM jobs WHERE jobs.job_title = 'Programmer').

Tipos de subconsultas sql

Existen varios tipos de subconsultas:

• Subconsultas mono-registro o de operadores aritméticos de comparación:
  Devuelve un único resultado.
  Los operadores aritméticos que se utilizan son: =, < >, >, > =, <, ! >, ! < o < =.
  Ejemplo: SELECT job_title FROM jobs WHERE min_salary > (SELECT max_salary FROM jobs WHERE job_title='Programmer’).

• Subconsultas multi-registro o de operadores lógicos:
  evuelve más de una fila.
  Los operadores lógicos que utilizan son: IN, NOT IN, EXISTS, ANY y ALL.
  Ejemplo:  SELECT department_name FROM departments WHERE department_id IN (SELECT  department_id FROM employees WHERE hire_date < '01/01/05').

• Subconsultas correlacionadas:
  Ejemplo:  SELECT department_id, first_name, salary FROM employees em WHERE salary  = (SELECT max(salary) FROM employees e WHERE em.department_id =  e.department_id ).

Operadores para subconsultas multi-registro

Los operadores que se pueden usar con una subconsulta que devuelve más de un registro son:

• El  operador IN: Compara uno o varios valores, con todos los valores que  devuelve la subconsulta. La condición se cumple si el valor está dentro  de los valores que devuelve la subconsulta.

• Ejemplo:  SELECT * FROM employees WHERE department_id IN (SELECT department_id  FROM departments WHERE department_name = 'Marketing').

• El  operador ANY: Compara uno o varios valores, con cualquiera de los  valores que devuelve la subconsulta. La condición se cumple con solo  coincidir un valor. Se usa con los operadores aritméticos de  comparación: =, <>, <, >, <=, >=.

• < ANY : Tiene que ser menor que cualquier valor que devuelva la subconsulta. Es decir, menor que el mayor de la subconsulta.
  Ejemplo: SELECT job_title FROM jobs WHERE max_salary < ANY (SELECT min_salary FROM jobs ).

• > ANY: Tiene que ser mayor a cualquiera que devuelva la subconsulta. Es decir, mayor que el menor de la subconsulta.
  Ejemplo: SELECT job_title FROM jobs WHERE min_salary > ANY (SELECT max_salary FROM jobs ).

• = ANY: Es el equivalente al in.
  Ejemplo:  SELECT department_id, department_name nombre FROM departments WHERE  department_id = ANY (select department_id FROM employees).

• El  operador ALL: Compara uno o varios valores, con todos los valores que  devuelve la subconsulta. Da verdadero si todas las comparaciones se  cumplen. Se usa con los operadores aritméticos de comparación: =,  <>, <, >, <=, >=.
  > ALL: Tiene que ser mayor a todos los valores de la subconsulta. Mayor que el mayor valor de la subconsulta.

• Ejemplo: SELECT job_title FROM jobs WHERE min_salary > ALL (SELECT max_salary FROM jobs).
  < ALL: Tiene que ser menor a todos los valores de la subconsulta. Menor que el menor de los valores de la subconsulta.

• Ejemplo: SELECT job_title FROM jobs WHERE max_salary < ALL (SELECT min_salary FROM jobs).

Operadores EXISTS y NOT EXISTS Sql

Los operadores EXISTS y NOT EXISTS:

• Su principal función es ver si se cumple o no la consulta interna.

• Se emplean para determinar si hay o no datos en una lista de valores.

• Devuelve true o false.

• No necesitan que la subconsulta devuelva alguna columna específica porque no utiliza ninguna expresión de comparación.

Operador EXISTS:

• Devuelve true o false.
  True: si las subconsultas retornan registros.
  False: si las subconsultas no retornan registros.

• Puede  emplearse con subconsultas correlacionadas para restringir el resultado  de una consulta exterior a los registros que cumplen la consulta  interior.

• Ejemplo: SELECT * FROM employees e WHERE EXISTS ( SELECT * FROM departments d WHERE d.department_name = 'marketing').

• Operador EXISTS en una consulta correlacional:
  El uso de EXISTS en una subconsulta correlacional es igual al uso del operador IN
  Ejemplo:  SELECT * FROM employees e WHERE EXISTS ( SELECT * FROM departments d  WHERE d.department_name = 'marketing' and e.department_id =  d.department_id ).

Operados NOT EXISTS:

• La  diferencia con el operador EXISTS es que la cláusula WHERE en la que se  utiliza se cumple si la consulta interna no devuelve ninguna fila como  resultado.

• Devuelve true o false.
  True: si las subconsultas no retornan registros.
  False: si las subconsultas retornan registros.

• Ejemplo: SELECT * FROM employees e WHERE NOT EXISTS ( SELECT * FROM departments d WHERE d.department_name = 'Marketing').

Visualizar varias tablas en sql

La sentencia JOIN se utiliza para unir dos tablas relacionadas entre sí.

Por un una o varias columnas.

Obtener los datos de dichas tablas en una misma sentencia SELECT.

La recuperación de datos en las bases de datos relacionales, se clasifican en:

• Selección:

• Recupera un grupo de registros de una tabla indicando que condiciones tienen que cumplir los registros recuperados.

• Este tipo de recuperación de datos es la que se conoce como una consulta.
  Ejemplo: SELECT * FROM hr.employees WHERE first_name = 'david';

• Proyección:
  Recupera unos campos concretos de una consulta hija:
  Ejemplo:  SELECT * FROM hr.employees WHERE department_id = ( SELECT  departments.department_id FROM hr.departments WHERE department_name  ='it') ;

• Join:
  Recupera un grupo de datos de varias tablas.
  Las cuales tienen una relación entre ellas, utilizando la cláusula JOIN.
  Para indicar por cual columna o columnas se va a realizar la unión se utiliza:
  La cláusula «On».
  La cláusula «USING», se usa cuando el campo en las dos tablas se tienen el mismo nombre.
  Existen 3 tipos de JOIN:
  INNER JOIN O JOIN.
  LEFT JOIN.
  RIGHT JOIN.

INNER JOIN (JOIN):

• Se emplea para obtener información de dos tablas y combinar dicha información en una salida.

• Solo muestra los registros cuyos campos relacionados hagan matching.
  Ejemplo:  SELECT first_name nombre, department_name departamento FROM  hr.employees e JOIN hr.departments d on e.department_id =  d.department_id;
  Ejemplo: SELECT first_name nombre, department_name departamento FROM hr.employees e JOIN hr.departments d USING(department_id);

LEFT JOIN:

• Encuentra registros de la primera tabla que se corresponden con registros de la segunda.

• Si no encuentra coincidencias, devuelve los resultados de la segunda seteados a null.

• Ejemplo:  SELECT first_name nombre, department_name departamento FROM  hr.employees e LEFT JOIN hr.departments d on e.department_id =  d.department_id;

• Ejemplo:  SELECT first_name nombre, department_name departamento FROM  hr.employees e LEFT JOIN hr.departments d USING(department_id);

• Es  importante el orden de las tablas, ya que la tabla de la izquierda es  la que se usa para localizar registros en la tabla de la derecha.

RIGHT JOIN:

• Trabaja igual que LEFT JOIN solo que la tabla derecha es la que localiza los registros en la tabla izquierda.
  Ejemplo:  SELECT first_name nombre, department_namedepartamento from hr.employees  e right join hr.departments d on e.department_id = d.department_id;
  Ejemplo:  SELECT first_name nombre, department_namedepartamento from hr.employees  e right join hr.departments d USING(department_id);

• Al igual que en LEFT JOIN es importante el orden de las tablas.

Natural join sql

Toma las columnas de igual nombre entre dos tablas y las utiliza para realizar un join.

No cruza los registros que tengan un valor nulo.

Se usa cuando las dos tablas tienen la columna de unión con el mismo nombre.

No hay que indicar el nombre de las columnas de unión.

No permite el uso de las sentencias ON y USING.

Puede ser:

• Una combinación interna.

• Una combinación externa derecha.

• Una combinación externa izquierda.

Combinación interna:

• Encuentra registros de la primera tabla que se corresponden con registros de la segunda.

• Los registros que no hagan matching no se muestran.

• Ejemplo: Select country_name, region_name From hr.countries Natural Join hr.regions;

Combinación externa izquierda:

• Encuentra registros de la primera tabla que se corresponden con registros de la segunda.

• Si no encuentra coincidencias, devuelve los resultados de la segunda tabla seteados a null.
  Ejemplo: Select country_name, region_name From hr.countries natural Left Join hr.regions;

Combinación externa derecha:

• Trabaja igual que left join solo que la tabla derecha es la que localiza los registros en la tabla izquierda

• Ejemplo: Select country_name, region_name From hr.countries Natural Right Join hr.regions;

Join externo sql

Hay tablas que, al intentarlas relacionar entre ellas por una columna, sus registros no se emparejan.

Por ejemplo, cuando se quiere emparejar dos tablas, cuyas columnas tienen valores nulos.

No sirve un Natural Join, ya que excluye los valores nulos.

Hay que usar los Join externos, que si permite recuperar valores nulos.

Las combinaciones externas se realizan solamente entre 2 tablas.

Hay tres tipos de combinaciones externas:

• Left outer join: left join.

• Right outer join: right join.

• Full outer join: full join.

LEFT JOIN:

• Encuentra registros de la primera tabla que se corresponden con registros de la segunda.

• Si no encuentra coincidencias, devuelve los resultados de la segunda seteados a null.
  Ejemplo:  select first_name nombre, department_name departamento from  hr.employees e left join hr.departments d on e.department_id =  d.department_id;

• La diferencia con el Natural Left Join es que aquí podemos especificar el campo de unión.

• Es  importante el orden de las tablas, ya que la tabla de la izquierda es  la que se usa para localizar registros en la tabla de la derecha.

• Puede tener cláusula "WHERE" que restrinja el resultado de la consulta.
  Ejemplo:  Select first_name nombre, department_name departamento From employees e  Left Join departments d on e.department_id = d.department_id Where  d.department_id is not null;
  Ejemplo:  Select first_name nombre, department_name departamento From employees e  Left Join departments d on e.department_id = d.department_id Where  d.department_id is null;

RIGHT JOIN:

• Trabaja igual que Left Join solo que la tabla derecha es la que localiza los registros en la tabla izquierda.
  Ejemplo:  Select first_name nombre, department_namedepartamento From hr.employees  e Right Join hr.departments d on e.department_id = d.department_id;

• La diferencia con el Natural Right Join es que aquí podemos especificar el campo de unión.

• Al igual que en Left Join es importante el orden de las tablas.

• Puede tener clausula WHERE:
  Ejemplo:  select first_name nombre, department_name departamento from employees e  right join departments d on e.department_id = d.department_id where  e.department_id is not null;
  Ejemplo:  select first_name nombre, department_name departamento from employees e  right join departments d on e.department_id = d.department_id where  e.department_id is null;

FULL OUTER JOIN O FULL JOIN:

• Es una combinación externa completa.

• Muestra todos los registros de ambas tablas.
  Ejemplo:  select first_name nombre, department_name departamento from employees e  full join departments d on e.department_id = d.department_id;

Cláusulas using y on sql

Cuando  se realiza un join, se usa la cláusula on para indicar el nombre de las  columnas que van a formar la unión de las dos tablas.

• Ejemplo: select first_name, department_name from hr.employeese join hr.departmentsd on e.manager_id = d.maneger_id;

Cuando las columnas de unión tienen el mismo nombre en ambas tablas se puede usar la cláusula using.

• Ejemplo: select first_name, department_name from hr.employees join hr.departments using(manager_id);

La cláusula Using se puede usar en todos los tipos de Join menos en el natural join.

La cláusula On se puede usar en todos los tipos de Join menos en el natural join.

La diferencia con el Using es que aquí los campos de unión pueden tener nombres diferentes en cada tabla.

• Ejemplo: Select first_name, department_name From hr.employees e Join hr.departments d On e.manager_id = d.manager_id;

• Ejemplo: Select first_name, department_name From hr.employees e Join hr.departments d On e.department_id = d.manager_id;

Uso del modificador “(+)”,se puede modificar el uso del On para obtener el mismo resultado que un Left Join o Right Join.

Indicando que se consideran los valores nulos de una tabla u otra.

• Ejemplo  On modificado para obtener Left Join: Select first_name nombre,  department_name departamento From hr.employees e Join hr.departments d  On e.department_id = d.department_id(+);

• Ejemplo  On modificado para obtener Right Join: Select first_name nombre,  department_name departamento From hr.employees e join hr.departments d  On e.department_id(+) = d.department_id;

Join cruzado (Cross Join)

También se conoce con el nombre de join cartesiano.

Muestran todas las combinaciones de todos los registros de las tablas.

Si una tabla tiene 3 registros y otra tabla tiene 10 registros, se recuperarán 30 registros.

No se incluye un campo de unión.

• Ejemplo: SELECT country_name, region_name FROM countries Cross Join regions;

Unir varias tablas en una sola sql

Con las consultas podemos obtener los datos de una tabla.

Con las subconsultas podemos obtener los datos de una tabla para devolverlos a una consulta para su tratamiento.

Con los Join podemos obtener campos de varias tablas.

Con las consultas compuestas, podemos unir los datos devueltos por una consulta a otras consultas.

Se puede unir más de dos consultas.

Operadores:

• Se pueden usar varios tipos de operadores.

• Según el tipo de operador se obtendrá un resultado diferente.

• Los operadores pueden ser:

• Union.

• Union all.

• Intersect.

• Minus.

El orden de prioridad de estos operadores es de:

• 1º de arriba hacia abajo.

• 2º de izquierda a derecha.

Características de los campos de las consultas:

• Que tengan el mismo número de columnas.

• Tienen que tener tipos de datos compatibles.

• No es obligatorio que cada consulta se aplique a tablas diferentes.

• No es obligatorio que las columnas tengan el mismo nombre.

Tipos de operadores de unión sql.

UNION:

• Obtiene todos los registros de las tablas a unir sin producir duplicados.

• Si un registro es igual en ambas tablas, en los datos devueltos solo estará este registro una única vez.

• Ejemplo:  SELECT manager_id FROM hr.employees WHERE department_id=20UNION Select  manager_id FROM hr.employees Where department_id=30;

• Los encabezados del resultado de una unión son los que se especifican en el primer "SELECT".

• Si queremos que se incluyan todos los registros, aún duplicados, debemos emplear "UNION ALL".

UNION ALL sql:

Obtiene todos los registros de las tablas a unir produciendo duplicados.

Si un registro es igual en ambas tablas, en los datos devueltos aparecera este registro 2 veces.

• Ejemplo:  SELECT manager_id FROM hr.employees WHERE department_id=20 UNION ALL  Select manager_id FROM hr.employees WHERE department_id=30;

Los encabezados del resultado de una unión son los que se especifican en el primer "SELECT".

INTERSECT sql:

Devuelve la intersección de las consultas involucradas.

Los registros que todas las consultas tienen en común.

• Ejemplo:  SELECT manager_id FROM hr.employees WHERE department_id=20 INTERSECT  SELECT manager_id FROM hr.employees WHERE department_id=30;

MINUS (diferencia) sql:

Devuelve los registros de la primera consulta que no se encuentran en segunda consulta.

Aquellos registros que no coinciden.

Es importante el orden de las consultas.

• Ejemplo: SELECT manager_id FROM employees WHERE department_id=20 MINUS SELECT manager_id FROM employees WHERE department_id=30;

Cláusula Order BY con operadores de unión

Cuando se realiza una unión de consultas, las columnas se ordenan de forma automática en ascendente desde izquierda a derecha.

No se puede usar la cláusula Order By dentro de cada consulta, ya que se volverán a ordenar una vez realizada la unión.

• Ejemplo: SELECT manager_id FROM employees WHERE department_id=20 ORDER BY manager_id

MINUS

SELECT manager_id FROM employees WHERE department_id=30

ORDER BY manager_id;

Se genera un error, ya que no se puede aplicar order by a nivel de consulta.

Pero si está permitido usarla para aplicarla al resultado final.

• Ejemplo: SELECT manager_id FROM employees WHERE department_id=20

MINUS

SELECT manager_id FROM employees WHERE department_id=30

ORDER BY manager_id;

En  esta consulta la cláusula Order By no se aplica a la segunda consulta,  se está aplicando al resultad de la unión de ambas consultas.

Tipos de datos sql orcle

Oracle dispone de diferentes tipos de datos.

Estos tipos de datos son los que determinan la naturaleza de los valores que pueden tratar.

Un tipo de datos numérico solo puede tratar números, no podría tratar ni letras, ni cadenas, ni fechas.

Tipos de datos:

• CHAR  (n): Define una cadena de caracteres de longitud fija, determinada por  el argumento "n". Su rango es de 1 a 2000 caracteres. Es de tamaño fijo,  de hasta 2000 caracteres. Si no se especifica tamaño será de 1. Si se  introduce un valor superior al indicado dará error.

Longitud  fija significa que: Si definimos un campo como "char(10)" y almacenamos  el valor "hola" (4 caracteres), Oracle rellenará las 6 posiciones  restantes con espacios.

• VARCHAR2(n):  Almacena cadenas de caracteres de longitud variable determinada por el  argumento “n". Su rango es de 1 a 4000 caracteres.

Que  sea una cadena de longitud variable significa que: si definimos un  campo como "Varchar2(10)" y almacenamos el valor "hola" (4 caracteres),  Oracle solamente ocupa las 4 posiciones (4 bytes y no 10 como en el caso  de "Char").

Por  lo tanto, si la longitud es variable, es conveniente utilizar este tipo  de dato y no "Char", así ocupamos menos espacio de almacenamiento en  disco.

• ALFANUMÉRICOS: se usan para tratar números y letras.
  NCHAR(x):  es similar a "Char". Excepto que permite almacenar caracteres ASCII,  Ebcdic y Unicode. Su rango va de 1 a 1000 caracteres. Se emplean 2 bytes  por cada carácter.
  NVARCHAR(x):  es similar a "Varchar2". Excepto que permite almacenar caracteres  unicode. Su rango va de 1 a 2000 caracteres. Se emplean 2 bytes por cada  carácter.

• NUMÉRICOS: se usan para tratar números.
  INTEGER: almacena números enteros sin decimales y tiene una escala de 9.
  NUMBER: almacena números enteros o decimales, positivos o negativos con una precisión de 1 a 38 y una escala de -8 a 127.
  FLOAT: almacena números flotantes con mayor precisión.

• BINARIOS:
  RAW:  Datos de longitud variable. Permite hasta 32767 bytes. A diferencia del  tipo de datos Char y varchar2, Oracle no convierte los conjuntos de  caracteres entre servidor y el cliente.

• FECHAS: tratan fechas.
  DATE: Almacena los datos del siglo, año, mes, día, hora, minuto y segundo. Tiene una longitud máxima de 7 bytes.
  Timestamp: Almacena lo mismo que el tipo de datos date, pero también los milisegundos. Tiene una longitud de 11 bytes.

• Objetos largos: se usan para tratar valores grandes.
  BLOB: Almacena ficheros de imágenes o videos de hasta 4gb.
  CLOB: Almacena texto largo.
  NCLOB: Almacena Clob en Unicode.

• ROWID: se usan para localizar un registro en una tabla y su valor esta codificado en base 64.

Funciones sql para cadenas

En Oracle existe una gran cantidad de funciones que se utilizan para tratar los datos.

Por ejemplo:

• La función de carácter, que convierte el valor de una cadena a mayúsculas.

• La función numérica, que devuelve el coseno de un número.

Estas funciones se pueden clasificar según el tipo de datos que tratan.

Funciones de caracteres o alfanuméricas:

• CHR(n): Función que acepta un código ASCII y retorna el carácter correspondiente.

• CONCAT(n1, n2): Función que concatena dos o más cadenas en una sola.

• SUBSTR(s, m, n): Función para extraer una subcadena de una cadena.

• INSTR(n): Función que devuelve la ubicación de una subcadena en una cadena.

• LENGTH(n):  Función que devuelve el número de caracteres de una cadena. Si es de  tipo CHAR, la longitud incluye los blancos del final.

• LPAD(s,  n): Función que rellena la cadena por la izquierda hasta la longitud  «n» con el carácter definido, por defecto es blanco.

• RPAD(s, n): Función que rellena la cadena por la derecha hasta la longitud «n» con el carácter definido, por defecto es blanco.

• GGNLS_LOWER(n, nlsparam): igual que LOWER pero tiene en cuenta el idioma especificado mediante el parámetro nlsparam.

• INITCAP(n): Función que pasa a mayúscula la primera letra de cada palabra.

• NLS_INITCAP(n, nlsparam): Igual que INITCAP pero tiene en cuenta el idioma especificado mediante el parámetro nlsparam.

• REPLACE(n1, n2, n3): Función que retorna «n1» con cada ocurrencia de «n2» remplazada por «n3».

• REGEXP_REPLACE(n1,  n2): Función que busca un patrón en una columna de caracteres y  sustituye cada incidencia de ese patrón por el patrón que se  especifique.

• REGEXP_SUBST(n1, n2, n3): Función que devuelve los caracteres extraídos de una cadena al buscar un patrón de expresión regular.

• REGEXP_INSTR: Función que busca en una cadena una incidencia especificada de un patrón de expresión regular.

• TRANSLATE(expr,  desde, hasta): Función que retorna «expr» con todas las ocurrencias de  cada carácter de «desde» trasladados a su correspondiente carácter en  «hasta».

Funciones numéricas sql

Funciones numéricas:

• ABS(n): Función que retorna el valor absoluto del parámetro.

• SIGN(n): Función que retorna 1 si «n» es positivo, -1 si es negativo y 0 si es 0.

• SIN(n): Función que retorna el seno de «n».

• COS(n): Función que retorna el coseno de «n».

• TAN(n): Función que retorna la tangente de «n».

• ACOS(n): Función que retorna el arco coseno de «n».

• ASIN(n): Función que retorna el arco seno de «n».

• ATAN(n): Función que retorna el arco tangente de «n».

• ATAN2(n, m): Función que retorna la atn(«n»/»m»).

• EXP(n): Función que retorna e elevado a la «n» potencia.

• POWER(m, n): Función que retorna «m» elevada a «n».

• LOG(m, n): Función que retorna el logaritmo en base «m» de «n».

• LN(n): Función que retorna el logaritmo natural o neperiano de «n».

• MOD(m, n): Función que retorna el resto de «m» al dividirlo por «n», retorna «m» si «n» es 0.

• REMAINDER(m, n): Función que retorna el resto de «m» al dividirlo por «n», es similar a MOD pero usando round en vez de FLOOR.

• SQRT(n): Función que retorna raíz cuadrada de «n».

Funciones para fechas en sql

Funciones de fecha:

• CURRENT_DATE:  Función que retorna la fecha actual de la zona horaria de la sesión. El  valor de retorno es una fecha del calendario gregoriano.

• CURRENT_TIMESTAMP:  Función que retorna la fecha y la hora actuales de la zona horaria de  la sesión como valor del tipo de datos TIMESTAMP WITH TIME ZONE.  TIMESTAMP WITH TIME ZONE es una variante de TIMESTAMP que incluye el  desplazamiento de zona horaria en su valor.

• NEW_TIME(fecha,  zonahoraria1, zonahoraria2): Función que retorna el valor de la fecha  trasladándolo de la «zonahoraria1» a la «zonahoraria2».

• LAST_DAY(d): Función que retorna el último día de mes de la fecha pasada.

• NEXT_DAY(fecha,  char): Función que retorna la fecha del primer día de la semana,  indicado mediante «char» posterior a «fecha». «char» debe contener un  día de la semana en el lenguaje de la sesión en la que nos encontremos.

• MONTHS_BETWEEN(d1, d2): Función que retorna el número de meses entre dos fechas.

• ADD_MONTHS(d, n): Función que suma un número (positivo o negativo) de meses a una fecha.

• SYSDATE: Función que retorna la fecha del sistema.

• LOCALTIMESTAMP:  Función que retorna la fecha y la hora actuales en la zona horaria de  la sesión con un valor del tipo de datos TIMESTAMP. La diferencia entre  esta función y CURRENT_TIMESTAMP es que LOCALTIMESTAMP devuelve un valor  TIMESTAMP, mientras que CURRENT_TIMESTAMP devuelve un valor TIMESTAMP  WITH TIME ZONE.

• SYSTIMESTAMP:  Función que retorna la fecha del sistema incluido fracción de segundo y  zona horaria del sistema donde reside la base de datos. El valor  devuelto es TIMESTAMP WITH TIME ZONE.

• DBTIMEZONE: Función que retorna el valor de la zona horaria de la base de datos.

• SESSIONTIMEZONE: Función que retorna el valor de la zona horaria de la sesión actual.

• EXTRACT:  Función que retorna el valor de un campo de fecha/hora especificado de  una expresión de valor de fecha/hora o intervalo.

• ROUND(d, n): Función que redondea la fecha «d» según el formato indicado en «n».

• TRUNC(d, n): Función que retorna la fecha «d» según el formato indicado en «n».

• TO_CHAR(n): Función que transforma un tipo DATE o NUMBER en una cadena de caracteres.

• TO_TIMESTAMP(n):  Función que convierte una cadena de tipo de datos CHAR, VARCHAR2, NCHAR  o NVARCHAR2 a un valor del tipo de datos TIMESTAMP.

• TO_TIMESTAMP_TZ(n):  Función que convierte una cadena de tipo de datos char, varchar2, nchar  o nvarchar2 a un valor de tipo de datos timestamp with time zone.

• TO_DSINTERVAL(char,  ’nlsparam’): Función que convierte una cadena de los tipos CHAR,  VARCHAR2, NCHAR, NVARCHAR2 a un valor del tipo INTERVAL DAY TO SECOND.

• TO_YMINTERVAL(char):  Función que convierte una cadena de caracteres de tipo de datos Char,  Varchar2, Nchar o Nvarchar2 a un tipo de datos Interval Year To Month.

Conversion de datos en sql

Funciones de conversión:

• ASCIISTR(n): Función que convierte cualquier carácter de string que no son ASCII a un formato utf-16.

• BIN_TO_NUM(n1,  n2, n3): Función que convierte una cadena binaria a formato decimal,  cada expresión «n» debe ser una valor de 0 o 1.

• CHARTOROWID(n): Función que convierte una cadena en tipo de dato ROWID.

• CAST(n): Función que te permite convertir temporalmente el valor de un tipo de datos a otro.

• CONVERT(char,  destino, origen): Función que convierte «char» del conjunto de  caracteres «origen» al conjunto de caracteres «destino».

• COMPOSE(‘char’): Función que convierte «char» a una cadena unicode normalizada al conjunto de caracteres del dato de entrada.

• DECOMPOSE(char): Función que convierte «char» en una cadena única.

• HEXTORAW(char): Función que convierte Char a Rowid.

• RAWTOHEX(raw): Función que convierte un carácter de tipo Raw a su equivalente hexadecimal.

• RAWTONHEX(raw): Función que convierte un carácter de tipo RAW a NVARCHAR2.

• ROWIDTOCHAR(rowid): Función que convierte un valor del tipo ROWID a un valor de tipo VARCHAR2 cuya longitud es siempre 18.

• ROWIDTONCHAR(rowid): Función que convierte un valor de tipo ROWID a NVARCHAR2.

• SCN_TO_TIMESTAMP(number):  Función que transforma el valor «number» SCN (system change number) al  valor aproximado de tipo timestamp asociado a dicho «number».

• TIMESTAMP_TO_SCN(timestamp):  Función que convierte el valor introducido «timestamp» del tipo  timestamp y devuelve el valor aproximado de SCN.

• TO_BINARY_DOUBLE(expr, gfmt, ’nlsparam’): Función que devuelve un número con doble precisión en coma flotante.

• TO_BINARY_FLOAT(expr, fmt, ’nlsparam’): Función que devuelve un número con precisión sencilla en coma flotante.

• TO_CLOB(char):  Función que convierte chart al tipo clob values. «char» puede ser de  uno de los tipos CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, o NCLOB.

• TO_DATE(n1, [n2]): Función que convierte la cadena «n1» en fecha, conforme al formato de conversión «n2».

• TO_LOB(long_column): Función que convierte un valor long o long raw de la columna «long_column» a un valor lob.

• TO_MULTI_BYTE(char):  Función que retorna un valor de carácter con todos los caracteres de un  solo byte convertidos en caracteres multibyte.

• TO_NUMBER(n): Función que convierte una cadena de caracteres en valor numérico.

• TO_SINGLE_BYTE(char):  Función que devuelve CHAR con todos sus multibytes caracteres  convertidos a sus correspondientes caracteres simples. «char» debe ser  de los tipos CHAR, VARCHAR2, NCHAR, o NVARCHAR2. El valor devuelto es  del mismo tipo que el de «char».

Características de las conversiones

Una  expresión no puede contener valores con tipos de datos diferentes, por  ejemplo, no se podría multiplicar un número por una letra.

Oracle  al recuperar datos de una tabla o vista, puede que encuentre tipos de  datos no esperados, y para mostrarlos realiza una conversión automática  de los datos encontrados al tipo de datos esperado, por ejemplo, al  indicar que queremos hacer una operación aritmética sobre campos que son  alfanuméricos, los cuales pueden almacenar números, pero al ser un  campo alfanumérico realmente no se consideran números, por lo que Oracle  convierte esos números alfanuméricos en numéricos, en ese caso se  tendría que usar la función TO_NUMBER.

Tipos de conversiones:

• Conversión  implícita: Oracle al recuperar datos de una tabla o vista, puede que  encuentre tipos de datos no esperados y para mostrarlos realiza una  conversión automática de los datos encontrados al tipo de datos  esperado. A esta conversión automática se le denomina conversión  implícita.

• Conversión  explícita: Es cuando el programador no espera a que Oracle realice la  conversión, sino que es él, el que la realiza, usando las funciones,  tales como TO_CHAR, TO_DATE, TO_NUMBER.

Ventajas de la conversión explícita a la implícita:

• La sentencia SQL puede tener un mayor rendimiento ya que no tiene que decidir qué tipo de conversión debería realizar.

• La  sentencia SQL es más fácil de entender, ya que visualizando el nombre  de una función podemos saber cuál es el tipo del dato origen, y a qué  tipo de datos se va a convertir.

Funciones de caracteres sql

Las funciones de caracteres son funciones que se aplican a cadenas o caracteres.

• CHR(n): Devuelve el carácter asociado al valor numérico, según el código de caracteres de la base de datos.

Ejemplo: Select Chr(62) From Dual;

Resultado: >.

• CONCAT(cadena1, cadena2), devuelve la concatenación de las cadenas

Ejemplo: Select concat('ejemplo','concat') from dual;

Resultado: ejemploconcat.

• SUBSTR(cadena,  posición, tamaño): devuelve una subcadena de la cadena(cadena), donde  hay que indicar en qué posición se empieza la subcadena  substraer(posición) y el número de caracteres a substraer(tamaño)

Ejemplo: Select substr('substrejemplo', '1', '6') from dual;

Resultado: substr.

• LENGTH(cadena): devuelve el número de caracteres de una cadena

Ejemplo: Select length('length') from dual;

Resultado: 6.

• UPPER (cadena): devuelve una cadena con todos sus caracteres en mayúsculas.

Ejemplo: select upper('ejemplo upper') from dual;

Resultado: EJEMPLO UPPER.

• LOWER(cadena): Devuelve una cadena con todos su caracteres en minúsculas.

Ejemplo: Select lower('ejemplo lower') from dual;

Resultado: ejemplo lower.

• LPAD(cadena,  tamaño final, subcadena relleno): rellena la cadena(cadena) con la  subcadena(subcadena relleno) hasta completar el tamaño indicado por un  parámetro (tamaño final) de izquierda a derecha.

Ejemplo: Select lpad('ejemplo', 10, 'abc') from dual;

Resultado: abcejemplo.

• RPAD(cadena, tamaño final, subcadena relleno): igual que LPAD pero desdés la derecha a izquierda

Ejemplo: Select rpad('ejemplo', 10, 'abc') from dual;

Resultado: ejemploabc.

• LTRIM(cadena,[subcadena]),  elimina una subcadena(subcadena) en la cadena(cadena) empezando de  izquierda a derecha., si no se indica la subcadena a borrar, elimina los  espacios en blanco.

Ejemplo: Select ltrim('el ejemplo de ltrim', 'el') from dual;

Resultado: ejemplo de ltrim.

• RTRIM(cadena,[subcadena]): igual que ltrim pero empezando de derecha a izquierda

Ejemplo: Select rtrim('ejemplo de rtrim en oracle', 'en oracle') from dual;

Resultado: ejemplo de rtrim.

• TRIM(cadena): retorna la cadena con los espacios de izquierda y derecha eliminados.

Ejemplo: Select rtrim(' ejemplo de trim ') from dual;

Resultado: ejemplo de trim.

• INSTR(n): Función que devuelve la ubicación de una subcadena en una cadena.

Ejemplo: select instr('ejemplo instr', 'in') from dual;

Resultado: 9.

• REPLACE(n1,n2,n3): Función que retorna n1 con cada ocurrencia de n2 remplazada por n3.

Ejemplo: Select replace('xxx.ejemploreplace.com', 'x', 'w') from dual;

Resultado: www.emeploreplace.com.

• TRANSLATE(expr,  desde, hasta): función que retorna expr con todas las ocurrencias de  cada carácter de desde trasladados a su correspondiente carácter en  hasta.

Ejemplo: select translate('ejemplo translate', 'eo', '30') from dual.

Resultado: 3j3mpl0 translat3.

Funciones numéricas sql oracle

Las funciones numéricas son funciones que se aplica a números.

• ROUND: Devuelve el número redondeado, indicando la posición por el que se redondea.
  Ejemplo: Select ROUND(32.345, 2) From Dual;
  Resultado: 32,35.

• Ejemplo: Select Round(32.345) From Dual;
  : 32.

• TRUNC: Devuelve el número truncado por la posición indicada.
  Ejemplo: Select Trunc (32.345, 2) From Dual;
  Resultado: 32,34.

• Ejemplo: Select Trunc(32,345) From Dual;
  : 32.

• FLOOR: Devuelve el número entero menor, más cercano al número.
  Ejemplo: Select Floor(32.345) From Dual;
  Resultado: 32.

• CEIL: Devuelve el número entero mayor, más cercano al número.
  Ejemplo: Select Ceil(32.345) From Dual;
  Resultado: 33.

• ABS: Retorna el valor absoluto del argumento "x".
  Ejemplo: Select Abs(-32) From Dual;
  Resultado: 32.

• SIGN: Si el argumento es un valor positivo, retorna 1, si es negativo, devuelve -1 y 0 si es 0.
  Ejemplo: Select Sign(-32) From Dual;
  Resultado: -1.

• Ejemplo: Select Sign(32) From Dual;
  Resultado: 1.

• SIN: Retorna seno de n.
  Ejemplo: Select Sin(32) From Dual;
  Resultado: 0,5514.

• COS: Retorna coseno de n.
  Ejemplo: Select Cos(32) From Dual;
  Resultado: 0,8342.

• TAN: Retorna tangente de n.
  Ejemplo: Select Tan(32) From Dual;
  Resultado: 0,6610.

• ACOS: Retorna arco coseno de n.
  Ejemplo: Select Acos(.3) From Dual;
  Resultado: 1,26.

• ASIN: Retorna arco seno de n.
  Ejemplo: Select Asin(.3) From Dual;
  Resultado: 0,3046.

• ATAN: Retorna arco tangente de n.
  Ejemplo: Select Atan(.3) From Dual;
  Resultado: 0,2914.

• EXP: Retorna e elevado a la n potencia
  Ejemplo: Select Exp(4) From Dual;
  Resultado: 54,5981.

• POWER: Retorna el valor de "x" elevado a la "y" potencia.
  Ejemplo: Select Power(2,3) From Dual;
  Resultado: 8.

• LOG: Retorna logaritmo en base m de n.
  Ejemplo: Select Log(2,3) From Dual;
  Resultado: 1,5849.

• SQRT: Devuelve la raíz cuadrada del valor enviado como argumento.
  Ejemplo: Select Sqrt(2) From Dual;
  Resultado: 1,4142.

• MOD: Devuelve el resto de la división x/y.
  Ejemplo: Select Mod(2,3) From Dual;
  Resultado: 2.

Funciones con fecha en oracle

Son funciones que se aplican a fechas (date).

Un valor tipo date almacena:

• El año (4 posiciones).

• El mes (2 posiciones).

• El día (2 posiciones).

• La hora (2 posiciones).

• Los minutos (2 posiciones).

• Los segundos (2 posiciones).

El formato de salida estándar en Oracle es dd-mmm-yy, ejemplo 01-dic-20.

• CURRENT_DATE: Retorna la fecha actual.
  Ejemplo: select current_date from dual;
  Resultado: 14/09/20.

• CURRENT_TIMESTAMP: Retorna la fecha y hora actual de la sesión.
  Ejemplo: select current_timestamp from dual;
  Resultado: 14/09/20 20:23:34,376000000 america/new_york.

• SYSTIMESTAMP: retorna fecha y hora actuales del sistema.
  Ejemplo: select systimestamp from dual;
  Resultado: 14/09/20 20:24:39,137000000 -04:00.

• NEW_TIME(fecha, zona1, zona2): convierte una fecha de una zona horaria a otra.
  Ejemplo: select new_time (to_date ('2003/11/01 01:45', 'yyyy/mm/dd hh24:mi'), 'ast', 'mst') from dual;
  Resultado: 31/10/03.

• LAST_DAY(f): retorna el último día de mes de la fecha enviada como argumento.
  Ejemplo: select last_day('10/02/2020') from dual;
  Resultado: 29/02/20.
  Ejemplo: select last_day('10/08/2020') from dual;
  Resultado: 31/08/20.

• NEXT_DAY (fecha, dia): retorna una fecha correspondiente al primer día especificado en "día" luego de la fecha especificada.
  Ejemplo: select next_day('10/08/2020','lunes') from dual;
  Resultado: 17/08/20.

• TO_DATE(cadena,  formato): convierte una cadena a tipo de dato "date". La cadena tiene  una fecha en el formato establecido por el parámetro “formato”.
  Ejemplo: select to_date ('05-sep-2019 10:00 am','dd-mon-yyyy hh:mi am') from dual;
  Resultado: 05/09/19.

• TO_CHAR(fecha, formato): devuelve una fecha en el formato indicado.
  Ejemplo: select to_char('10/10/2020')from dual;
  Resultado: 10/10/2020.

• ADD_MONTHS(fecha, num_meses): suma el número de meses indicado, a la fecha. Si el argumento num_meses es negativo, se resta.
  Ejemplo: select add_months('10/06/2020',5) from dual;
  Resultado: 10/11/20.
  Ejemplo: select add_months('10/06/2020',-5) from dual;
  Resultado: 10/01/20.

• SYSDATE: devuelve la fecha y hora actual del sistema operativo de la base de datos.
  Ejemplo: Select Sysdate From Dual;
  Resultado: 14/09/20.

• Extract(): extrae el indicador de una fecha.
  SECOND: los segundos.
  Ejemplo: Select Extract(second from systimestamp) from dual;
  Resultado: 19,95.
  TIMEZONE_HOUR: la hora de la zona horaria correspondiente.
  Ejemplo: select extract(timezone_hour from systimestamp) from dual;
  Resultado: -4.
  TIMEZONE_MINUTE: los minutos de la zona horaria correspondiente.
  Ejemplo: Select Extract(timezone_minute from systimestamp) from dual;
  Resulado: 0
  TIMEZONE_REGION: la región de la zona horaria correspondiente.
  Ejemplo: Select Extract(timezone_region from systimestamp) from dual;
  Resultado: unknown.
  TIMEZONE_ABBR: devuelve la abreviatura de la zona horaria correspondiente.
  Ejemplo: Select Extract(timezone_abbr from systimestamp) from dual;
  Resultado: unk.

Funciones anidadas en oracle

Oracle permite que una función llame a otra función.

Las funciones a nivel de filas pueden ser anidadas hasta cualquier nivel.

Las funciones anidadas son evaluadas desde el nivel más profundo, al nivel menos profundo.

EJEMPLOS:

• SELECT TO_CHAR(SYSDATE) FROM DUAL;
  RESULTADO: 20/09/20.

• SELECT CONCAT ('ESTAMOS EN EL MES ‘,

( SELECT TO_CHAR(( SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL)

)FROM DUAL)

) FROM DUAL;

• RESULTADO: ESTAMOS EN EL MES 9.

Insertar datos en tabla sql

Los  datos de las tablas se almacenan en celdas, estas celdas se agrupan en  una fila a la cual se denomina registro, las cuales forman una tabla,  que equivalen a una fila.

En  SQL existen varias sentencias que sirven para manipular los datos de  estos registros, a este grupo de sentencias se les denomina sentencias  DML, y está formado por las sentencias:

• Select: Se utiliza para consultar los registros de las tablas.

• Insert: Se utiliza para insertar registros en las tablas.

• Update: Se utiliza para actualizar los datos existentes en los registros de las tablas.

• Delete: Se utiliza para eliminar los registros de las tablas.

• Commit: Se utiliza para hacer permanente los cambios realizados en una tabla con las sentencia DML

• Rollback: Se utiliza para deshacer los cambios realizados en una tabla con las sentencia DML.

Cuando  se insertan o se modifican registros en las tablas, hay que tener en  cuenta las reglas de integridad (restricciones) que posee dicha tabla,  ya que si se los nuevos datos incumplen estas restricciones, estas  sentencias fallaran.

Existen varias formas de insertar datos en las tablas:

• Insert: Es la sentencia más utilizada y permite insertar uno o varios registros.
  Sintaxis para insertar un solo registro:
  INSERT INTO NOMBRE_TABLA [LISTA_DE COLUMNAS] VALUES LISTA_DE_VALORES.
  Ejemplo:
  INSERT INTO HR.DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME ) VALUES ( 1000, ‘COMPRAS’);
  Sintaxis para insertar un grupo de registros:
  INSERT INTO NOMBRE_TABLA [LISTA_DE_COLUMNAS] SUBCONSULTA.
  Ejemplo:
  INSERT INTO HR.DEPARTMENTS SELECT * FROMHR.DEPART_NEW;

• Utilidades de carga: Se utiliza para insertar grandes cantidades de registros, por ejemplo “SQL *Loader”.

EJEMPLOS:

• INTO HR.DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME ) VALUES ( 1000, ‘COMPRAS’);
  Esta  sentencia que especifica las columnas en las que se van a insertar  datos,  inserta el valor 1000 en la columna id del departamento, y el  valor compras en la columna nombre de departamento de la tabla  departamentos, el resto de campos los dejara sin informar.

• INTO HR.DEPARTMENTS VALUES ( 1000, ‘COMPRAS’, 101, 1700);
  Esta  sentencia que no específica las columnas en las que se van a insertar  datos, inserta el valor 1000 a la primera columna de la tabla, el valor  compras a la segunda columna, el valor 101, a la tercera columna y el  valor 1700, a la cuarta columna de la tabla. Cuando no se especifica las  columnas, el listado de valores tiene que tener el mismo número de  valores que columnas tiene la tabla.

• INTO HR.DEPARTMENTS SELECT * FROM  HR.DEPART_NEW;
  Esta  sentencia que utiliza una subconsulta para obtener los datos a  insertar, inserta todos los datos recuperados de la tabla “nuevos  departamentos”, en la tabla departamentos. El número de columnas  recuperadas en la subconsulta es el mismo que el de la tabla  departamentos.

Actualizar tablas  sql

La sentencia UPDATE permite modificar  una o varias columnas de uno o varios registros.

Las  modificaciones se aplicaran a los registros que cumplan la condición de  la cláusula “WHERE” de la sentencia, por lo que, si una sentencia  UPDATE no implementase esta cláusula, se modificaran todos los registros  de la tabla.

• Su sintaxis es:

UPDATE nombre_tabla

SET nom_columna = (nuevo_valor) [, nom_columna = (nuevo_valor )]

[WHERE condición.]

• Ejemplo:

UPDATE hr.employes SET salary = 30000

WHERE employee_id = 100;

Esta sentencia UPDATE, actualiza el campo salario del empleado con la id 100.

Para asignar un valor a las columnas se pueden usar:

• Un valor único.
  hr.employes SET salary = 30000

Esta sentencia UPDATE, actualiza el campo salario de todos los empleados.

• Una subconsulta.
  Pero esta subconsulta solo puede devolver un solo campo con un solo registro, en caso contrario se producirá un error.
  Update hr.employes SET salary = (select  max(salary) FROM employees)

Esta sentencia UPDATE, actualiza el campo salario de todos los  empleados, para que tenga el mismo salario que el salario más alto.

• Ejemplos sentencia update
  La  siguiente sentencia que está actualizando 2 campos con la función LOWER  y donde no está usando la cláusula ” WHERE”, convertirá el nombre y  apellido de todos los empleados de la tabla profesores a minúsculas
  UPDATE  profesores SET nombre = LOWER(nombre) , apellido = LOWER(nombre);
  La  siguiente sentencia que está actualizando el campo sueldo con el  resultado de una subconsulta y usando la cláusula ” WHERE”, actualizará  el sueldo de todos los empleados cuya id sea mayo a 4 y el nuevo sueldo  será igual al sueldo mayor que existe en la tabla.
  UPDATE profesores SET sueldo = (select MAX(sueldo) FROM profesores) WHERE id_profesor > 4;
  La  siguiente sentencia que actualiza el campo sueldo, usando el operador  ”IN” en la cláusula “WHEREe”, actualizará el sueldo a 45000 de todos los  profesores cuya id sea igual a alguna id devuelta por la subconsulta.
  UPDATE profesores SET sueldo = 45000 WHERE id_profesor IN ( SELECT id_profesor  FROM profesores WHERE sueldo < 45000);
  La  siguiente sentencia que está actualizando el campo sueldo con el campo  sueldo de una subconsulta, dará error, ya que esta subconsulta devuelve  más de un registro.
  UPDATE profesores SET sueldo = ( SELECT sueldo  FROM profesores WHERE sueldo > 40000)

Borrar datos de una tabla sql

La  sentencia DELETE permite eliminar uno o varios registros de una tabla,  este borrado se realizara según la condición de la cláusula “WHERE”, por  lo que si se omite esta cláusula en una sentencia DELETE, se producirá  el borrado completo de la tabla.

A  diferencia de la sentencia UPDATE que actúa sobre las columnas de los  registros, la sentencia DELETE actúa sobre un registro completo, por lo  que para borrar el contenido de una columna o de una celda, no se usa la  sentencia DELETE, si no la sentencia UPDATE.

Su sintaxis es:

• DELETE FROM nombre_tabla

[WHERE condición];

Ejemplos;

• DELETE FROM EMPLOYEES;

• DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID = 100;

• DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID > 10000;

Transacciones sql oracle

Una transacción es una o más sentencias DML (INSERT, UPDATE, DELETE) seguidas de las sentencias:

• COMMIT:  Que se utiliza para hacer permanente las modificaciones. (para hacer  permanente las modificaciones realizadas por una sentencia DML, hay que  ejecutar una sentencia COMMIT, o una sentencia DDL).

• ROLLBACK: Para deshacer las modificaciones realizadas.

También  existe la sentencia SAVEPOINT, que se utiliza para crear puntos de  grabado, el cual es utilizado por la sentencia ROLLBACK para restaurar  los datos hasta la ejecución de un SAVEPOINT.

Se puede realizar varios SAVEPOINT y restaurar la que se considere necesaria.

Características ACID de base de datos:

Toda transacción debe cumplir con ciertas características, las cuales se denominan ACID:

• ATOMICIDAD(A):  Todas partes de una transacción deben realizarse, y en caso contrario  no se realizara ninguna, es decir, no se puede terminar una transacción  con un aparte de sus sentencias DML sin realizar.

Ejemplo: Si una transacción realiza la actualización de varias tablas, solo puede terminar:

• Modificando todas las tablas.

• O No realizando modificación en ninguna de ellas.

• CONSISTENCIA  (C): Esta característica  asegura que sólo se empieza aquello que se  puede acabar. Solo se ejecutaran las transacciones que no van a romper  las reglas y directrices de Integridad de la base de datos, es decir,  esta característica  sostiene que cualquier transacción llevará a la  base de datos desde un estado válido a otro también válido.

Ejemplo: Si una transacción va a realizar actualizaciones sobre  tablas que tienen una relación entre ellas y estas modificaciones van a  romper esta relación, la transacción no se ejecutara.

• AISLAMIENTO  (ISOLATION): La modificaciones realizadas sobre una base de datos deben  ser invisibles para el resto de usuarios, hasta que se confirme su  permanencia, es decir,  las modificaciones realizadas no serán  efectuadas hasta que se realice una sentencia COMMIT o una sentencia  DDL.

Ejemplo: Cuando un usuario lanza una transacción y no se producido  un COMMIT, las modificaciones solo serán visibles para el usuario que  lanzara esa transacción, el resto de usuarios verán el estado de la base  de datos, tal como estaba antes de ejecutarse la transacción, y será  así, hasta que se ejecute una sentencia que haga permanente los cambios.

• DURABILIDAD  (D): Esta característica asegura que una vez realizada la operación,  esta persistirá y no se podrá deshacer aunque falle el sistema y que de  esta forma los datos sobrevivan de alguna manera.

Ejemplo:  Toda base de datos debe tener un sistema de seguridad, para que en el  caso que se produzca un fallo, se puedan recuperar todos los datos, por  ejemplo, un servidor de BACKUP.

Crear transacción en sql:

Las transacciones no se pueden crear con un comando o una sentencia, se crean de forma automática.

Cuando  un usuario se conecta a un servidor, este crea una sesión de usuario. Y  cuando este ejecuta la primera sentencia DML (INSERT, UPDATE, DELETE),  se crea una transacción de forma automática.

Todas las posteriores sentencias DML, formaran parte de la misma  transacción, que solo terminara con:

• Una sentencia COMMIT ( que hará permanente todos los cambios) o una sentencia ROCKBALL ( que deshará los cambios)

• O cuando se cierre la sesión del usuario, el cual ejecutaráun ROCKBALL que deshará todos los cambios no confirmados.

Oracle tipos de objetos

En Oracle existe una gran cantidad de objetos, los más usados por los programadores son:

• Tablas.

• Vitas.

• Sinónimos.

• Índices.

• Secuencias.

Y pueden ser creados o modificados por sentencias sql, a este grupo de sentencias se les denomina “sentencias DDL”, que son:

• CREATE: Se usa para crear bases de datos, tablas, vistas, etc.

• ALTER: Se utiliza para modificar las estructuras de un objeto.

• DROP: Se utiliza para eliminar objetos.

También existen muchos objetos que tienen información sobre el reto de objetos de la base de datos, algunos de ellos son:

• Dba_objects:  contiene los nombres e información de todos los objetos de la base de  datos, pero solo puede ser consultado por el administrador del sistema.  (todos los objetos que tengan el prefijo dba, indican que solo pueden  ser usados por el administrador de sistema).

• User_objects:  contiene los nombres e información  de todos los objetos que poseemos,  es decir, a cada usuario se le mostrara un listado diferente, para  mostrarle únicamente los objetos que son de su propiedad.

• All_objects:  contiene el nombre e información de todos los objetos que poseemos y  también de los objetos a los que tenemos permisos.

La  sentencia DESCRIBE, permite conocer la estructura de una tabla, (es  decir, el número de columnas que tiene,  sus nombres, de qué tipo de  datos son etc.)

Durante la instalación del sistema se crean diversos usuarios, los cuales tienen diferentes permisos, los más importantes son:

• SYS:  es el propietario del diccionario de datos, el cual contiene toda la  información del contenido de la base de datos y su definición.

• SYSTEM: es el propietario de objetos que se utilizan para la administración y monitorización de la base de datos.

A  parte de esto usuarios, se pueden crear más y concederles privilegios diferentes a cada uno de ellos.

Para  que un usuario se pueda conectar a una base de datos necesitan un  nombre de usuario y una contraseña,  y tener concedido el privilegio de  conectarse a la base de datos.

Todo  usuario tiene asignado un esquema, que se considera como un contenedor  de todos los objetos que tiene el usuario, cuando nos referimos a un  esquema, nos estamos refiriendo a un usuario y sus objetos.

Para  referirse a cualquier objeto de la base de datos hay que utilizar el  nombre del  objeto y del esquema al que pertenece, ya que un nombre  puede estar repetido para una misma clase de objeto.

Ejemplo:

• SELECT * FROM HR.COMPRAS.

• SELECT * FROM SYS.COMPRAS;

En  la primera sentencia se está accediendo a la tabla compras del esquema  HR y en la segunda sentencia se está accediendo a la tabla compras del  esquema SYS, como es obligatorio indicar el esquema de un objeto, esto  permite que varios objetos puedan tener el mismo nombre)

Para ponerle un nombre a un objeto, tiene que cumplirse:

• Tiene que empezar por un carácter alfabético ysolo puede contener letras, números y los siguiente caracteres:
  "_"
  "&"
  "#"

• Que no sea mayor a 30 caracteres.

• Utilizar palabras reservadas.

• No es sensible a Mayúsculas y minúsculas, es decir, no distingue entre mayúsculas y minúsculas.

Sql restricciones de integridad

Existe  una gran variedad de restricciones, las cuales nos permite controlar  como se relacionan las tablas y los datos que se introducen en ellas,  estas restricciones se suelen crear para garantizar  las reglas de  negocio, por ejemplo para evitar que se cree una factura en la tabla de  facturas si no existe el cliente en la tabla de clientes.

Si  una sentencia DML viola alguna restricción, se lanzará un ROLLBACK que  producirá que se deshaga todos los cambios realizados hasta el momento.

Algunas clases de restricciones son:

• UNIQUE: Impide que un valor (excepto valores nulos) se repita en una o varias columnas.

• NOT NULL: No permite Nulos en la columna especificada,

• PRIMARY  KEY: Permite definir que columna o grupo de columnas serán la clave  principal de la tabla, impidiendo que los valores se puedan repetir,  esta restricción conlleva implícitamente:
  La restricción “UNIQUE”, impidiendo que los valores se puedan repetir.
  La creación de un índice, que facilite la búsqueda de datos.

• FOREIGN KEY: Obliga a que el valor de una o varias columnas ya existan en las columnas PRIMARY KEY de otra tabla.

• CHECK: Se usa para especificar los valores de datos aceptables en una o más columnas.

Creando tablas en sql

Las  tablas son los objetos de la base de datos que contienen los datos de  un usuario. Estos datos se organizan con arreglo a un formato de filas y  columnas, similar al de una hoja de cálculo. Cada fila representa un  registro único y cada columna un campo dentro del registro.

También es el concepto central de una base de datos relacional.

Sintaxis:

• CREATE TABLE [esquema.]Nombre_tabla

(nombre_columna1 tipo_de_datos [DEFAULT valor] [ [CONSTRAINT  “nombre_del_constraint”] clase_de_constraint [ENABLE/DISABLE]]

(, nombre_columna2 tipo_de_datos [default valor] [  [CONSTRAINT “nombre_del_constraint”] clase_de_constraint  [ENABLE/DISABLE]]

[… [CONSTRAINT "nombre_constraint" clase_de_constraint [ENABLE/DISABLE]]

);

Por defecto todos los CONSTRAINT están activados, por lo que no es necesario indicar “ENABLE”.

Ejemplo:

• CREATE TABLE "HR"."DEPARTMENTS"

("DEPARTMENT_ID" NUMBER(4,0),

"DEPARTMENT_NAME" VARCHAR2(30 BYTE) CONSTRAINT

"DEPT_NAME_NN1" NOT NULL ENABLE,

"MANAGER_ID" NUMBER(6,0),

"LOCATION_ID" NUMBER(4,0),

CONSTRAINT "DEPT_ID_PK1" PRIMARY KEY("DEPARTMENT_ID"),

CONSTRAINT "DEPT_MGR_FK1" FOREIGN KEY ("MANAGER_ID")

REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE,

CONSTRAINT "DEPT_LOC_FK1" FOREIGN KEY ("LOCATION_ID")

REFERENCES "HR"."LOCATIONS" ("LOCATION_ID") ENABLE

) ;

Crear restricciones en oracle:

Unique:

1º  Forma: con esta sintaxis se crea una restricción UNIQUE en la propia  declaración del campo, y en la cual no se usa la cláusula CONSTRAINT  para indicar el nombre a la restricción.  Este campo no permitirá  valores duplicados.

• CREATE TABLE HR.DEPARTMENTO

(DEPARTMENT_ID NUMBER(4,0) UNIQUE);

2º  Forma: con esta sintaxis se crea una restricción UNIQUE en la propia  declaración del campo, y en la cual si se usa la cláusula CONSTRAINT  para indicar el nombre a la restricción y también se indica que estará  activa desde la creación de la tabla.

• CREATE TABLE HR.DEPARTMENTO

(DEPARTMENT_ID NUMBER(4,0) CONSTRAINT

DEPARTMENT_ID_UK UNIQUE ENABLE);

3º  Forma: con esta sintaxis se crea una restricción UNIQUE después de la  declaración de los campos, y en la cual se usa la cláusula CONSTRAINT  para indicar el nombre a la restricción y es obligatorio indicar el  nombre del campo al que se le va a aplicar la restricción.

• CREATE TABLE HR.DEPARTMENTO

(DEPARTMENT_IDN NUMBER(4,0), CONSTRAINT

DEPARTMENT_ID_UK UNIQUE (DEPARTMENT_ID) ENABLE);

Not null:

1º  Forma: con esta sintaxis se crea una restricción NOT NULL en la propia  declaración del campo, y en la cual no se usa la cláusula CONSTRAINT  para indicar el nombre a la restricción.

• CREATE TABLE HR.DEPARTMENTO

(DEPARTMENT_ID NUMBER(4,0)NOT NULL);

2º  Forma: con esta sintaxis se crea una restricción NOT NULL en la propia  declaración del campo, y en la cual si se usa la cláusula CONSTRAINT  para indicar el nombre a la restricción y también se indica que estará  activa desde la creación de la tabla.

• CREATE TABLE HR.DEPARTMENTO

(DEPARTMENT_ID NUMBER(4,0) CONSTRAINT DEPARTMENT_ID_NN NOT NULL ENABLE);

3º  Forma: con esta sintaxis se crea una restricción NOT NULL después de la  declaración de los campos, y en la cual se usa la cláusula CONSTRAINT  para indicar el nombre a la restricción y es obligatorio indicar el  nombre del campo al que se le va a aplicar la restricción.

• CREATE TABLE HR.DEPARTMENTO

(DEPARTMENT_ID NUMBER(4,0),

CONSTRAINT DEPARTMENT_ID_NN NOT NULL (DEPARTMENT_ID) ENABLE);

Primary key:

1º  Forma: con esta sintaxis se crea una restricción PRIMARY KEY en la  propia declaración del campo, y en la cual no se usa la cláusula  CONSTRAINT para indicar el nombre a la restricción.

• CREATE TABLE HR.DEPARTMENTO

(DEPARTMENT_ID NUMBER(4,0) PRIMARY KEY);

2º  Forma: con esta sintaxis se crea una restricción PRIMARY KEY en la  propia declaración del campo, y en la cual si se usa la cláusula  CONSTRAINT para indicar el nombre a la restricción y también se indica  que estará activa desde la creación de la tabla.

• CREATE TABLE HR.DEPARTMENTO

(DEPARTMENT_ID NUMBER(4,0) CONSTRAINT DEPARTMENT_ID_PK PRIMARY KEY ENABLE);

3º  Forma: con esta sintaxis se crea una restricción NOT NULL después de la  declaración de los campos, y en la cual se usa la cláusula CONSTRAINT  para indicar el nombre a la restricción y es obligatorio indicar el  nombre del campo al que se le va a aplicar la restricción.

• CREATE TABLE HR.DEPARTMENTO

(DEPARTMENT_ID NUMBER(4,0),

CONSTRAINT DEPARTMENT_ID_PK PRIMARY KEY (DEPARTMENT_ID) ENABLE);

Foreign key:

1º  Forma: con esta sintaxis se crea una restricción FOREIGN KEY después de  la declaración de los campos, y en la cual no se usa la cláusula  CONSTRAINT para indicar el nombre a la restricción y es obligatorio  indicar el nombre del campo al que se le va a aplicar la restricción.

• CREATE TABLE HR.DEPARTMENTO

(DEPARTMENT_ID NUMBER(4,0),

FOREIGN KEY (“DEPARTMENT_ID”)

REFERENCES HR.EMPLOYEES(EMPLOYEE_ID) ENABLE);

2º  Forma: con esta sintaxis se crea una restricción FOREIGN KEY después de  la declaración de los campos, y en la cual se usa la cláusula  CONSTRAINT para indicar el nombre a la restricción y es obligatorio  indicar el nombre del campo al que se le va a aplicar la restricción.

• CREATE TABLE HR.DEPARTMENTO

(DEPARTMENT_ID NUMBER(4,0),

CONSTRAINT DEPARTMENT_ID_FK FOREIGN KEY (DEPARTMENT_ID)

REFERENCES HR.EMPLOYEES(EMPLOYEE_ID) ENABLE);

CHEK:

1º  Forma: con esta sintaxis se crea una restricción CHECK en la propia  declaración del campo, y en la cual no se usa la cláusula CONSTRAINT  para indicar el nombre a la restricción,

• CREATE TABLE HR.DEPARTMENTO

(DEPARTMENT_ID NUMBER(4,0) CHECK (DEPARTMENT_ID > 1000);

2º  Forma: con esta sintaxis se crea una restricción CHECK en la propia  declaración del campo, y en la cual si se usa la cláusula CONSTRAINT  para indicar el nombre a la restricción y también se indica que estará  activa desde la creación de la tabla.

• CREATE TABLE HR.DEPARTMENTO

(DEPARTMENT_ID NUMBER(4,0) CONSTRAINT DEPARTMENT_ID_MIN CHECK (DEPARTMENT_ID > 1000) ENABLE);

3º  Forma: con esta sintaxis se crea una restricción CHECK después de la  declaración de los campos, y en la cual se usa la cláusula CONSTRAINT  para indicar el nombre a la restricción y es obligatorio indicar el  nombre del campo al que se le va a aplicar la restricción.

• CREATE TABLE HR.DEPARTMENTO

(DEPARTMENT_ID NUMBER(4,0),

CONSTRAINT DEPARTMENT_ID_MIN CHECK (DEPARTMENT_ID > 1000));

Sql crear tabla con select:

También  existe una forma de crear tablas usando subconsultas, de esta forma se  crean las tablas según lo campos de que devuelve la subconsulta y  posteriormente insertar los datos.

Esta opción solo copia la estructura de una tabla, no copia sus restricciones.

SINTAXIS:

• CREATE TABLE NOMBRE_TABLA AS SUBCONSULTA;

EJEMPLOS:

• CREATE TABLE DEPARTAMENTO AS SELECT * FROM DEPARTMENTS;

• CREATE TABLE DEPARTAMENTO AS SELECT DEPARTMENT_ID, DEPARTMENT_NAMEFROM DEPARTMENTS WHERE DEPARTMENT_ID > 50;

Sql modicar estrutura tabla

Para modificar la estructura de una tabla se utiliza el comando DDL “”.

Este comando realiza un COMMIT implícito y permite:

Añadir columnas:

• ALTER TABLE hr.employees ADD (job_id number);

Modificar columnas:

• ALTER TABLE hr.employees MODIFY (job_id number (5,2));

Borrar columnas:

• ALTER TABLE hr.employees DROP COLUMN job_id;

Renombrar columnas:

• ALTER TABLE hr.employees RENAME COLUMN job_id to job_id2;

Marcar una columna como no usada:

• ALTER TABLE hr.employees SET UNUSED COLUMN job_id;

Modificar una tabla a solo lectura:

• ALTER TABLE hr.employees READ ONLY;

Consideraciones al modificar los campos de una tabla:

• Si se cambia el tipo de dato de un campo, la tabla debe estar vacía.

• No se puede cambiar el tipo de dato de un campo que es "FOREIGN KEY" o que es referenciado por una "FOREIGN KEY".

• Para  modificar un campo disminuyendo la longitud (precisión o escala) del  tipo de dato, la tabla debe estar vacía, los registros deben tener  valores nulos en tal campo o los datos existentes deben ser inferiores o  iguales a la nueva longitud.

• Se puede modificar un campo definido "NULL" a "NOT NULL", siempre que la tabla esté vacía o no contenga valores nulos.

• No puede redefinirse como "NOT NULL" un campo que es clave primaria.

• Sí  un campo tiene un valor por defecto y se modifica el tipo de dato de  tal campo, Oracle analiza que el valor por defecto pueda convertirse al  nuevo tipo de dato cuando sea necesario insertarlo; si el valor por  defecto no se puede convertir al nuevo tipo de dato que se intenta  modificar, la modificación del campo no se realiza.

Sql eliminando tablas

DROP:

• Se usa para eliminar toda la tabla, no solo los datos.
  Ejemplo: DROP TABLE hr.empleyees;

TRUNCATE:

• Elimina los datos que contiene la tabla dejando intacto su estructura.

• Elimina todos los registros de la tabla.
  Ejemplo: TRUNCATE TABLE hr.employees;

• Al emplear "DELETE“ se guarda una copia de los registros borrados y son recuperables.

• Con TRUNCATE no es posible la recuperación.

Sql descripción tabla

La  sentencia DESCRIBE TABLE, describe las columnas de una tabla o sus  valores actuales, así como los valores predeterminados, para las  propiedades de la etapa de una tabla.

También se puede abreviar como desc.

• Ejemplo: DESC TABLE jobs

Tratando Vistas

No se permite a los usuarios trabajar directamente sobre las tablas y los motivos son:

• Por la seguridad, ya que puede eliminar datos importantes.

• Prevenir errores del usuario.

Para lo cual se les ofrece las vistas.

Una vista, aunque se parezca a una tabla no lo es.

Es una consulta a una o varias tablas a las que se le asigna un nombre.

Permite que los datos de la consulta se puedan tratar como si fuese una tabla.

Características de la estructura de una vista:

• No es obligatorio que la vista tenga todas las columnas de una tabla.

• Se le puede asignar la columna o columnas que se desee de una o varias tablas.

• No es obligatorio que contenga todos los registros de las tablas.

• Se puede crear filtrando los registros.

• La vista realmente no contiene datos físicos, lo que consulta la tabla para recuperar los datos.

• Cuando se realiza una operación DML sobre una vista, realmente se aplica a las tablas origen.

Con las vistas se ofrece al usuario final:

• Simplificar el SQL.

• Mejorar la comprensión de los datos de las tablas, utilizando nombres de columnas más simples o intuitivas.

Existen 2 tipos de vistas:

• Vistas simples:
  se crean con los datos de una única tabla y se le puede realizar sentencia DML.

• Vistas complejas:
  Se crean con los datos de una o varias tablas utilizando joins.
  Generalmente no se le puede realizar sentencias DML.

Creación y opciones de vistas:

• Sintaxis:
  REATE  [or REPLACE] [FORCE | NOFORCE] VIEW nombre_vista AS subconsulta [WITH  CKECK OPTION [CONSTRAINT nombre_restriccion]] [WITH READ ONLY  [CONSTRAINT nombre_restriccion]]

• Opciones:
  OR REPLACE: remplaza la vista, si esta ya existe.
  FORCE / NOFORCE: obliga a crear la vista aunque la tabla origen no exista.
  WITH  CKECK OPTION: si la vista se crea utilizando la cláusula WHERE, no se  permite realizar INSERT o UPDATES sobre registros que no son accesibles  con la vista.
  WITH READ ONLY: solo permite realizar consultas sobre la vista, es decir impide realizar INSERT, UPDATE o DELETES.
  CONSTRAINT: permite poner nombres a las restricciones para hacer más legibles los mensajes de error asociados a su violación.

Modificación y opciones de vistas:

• Sintaxis:
  ALTER VIEW nombrevista [ADD | DROP RESTRICCION | COMPILE];

• Opciones
  ADD: se usa para añadir una restricción a la vista.
  MODIFY CONSTRAINT: se usa para borrar una restricción a la vista.
  COMPILE: se usa para recompilar la vista.

Eliminación de vistas:

• Sintaxis:
  DROP VIEW nombrevista;

Consultando una vista:

• Sintaxis:
  SELECT * From nombrevista;

Consultando una vista:

• Sintaxis:
  SELECT * From nombrevista;

• Sintaxis:
  SELECT * From emp_details_view ORDER BY job_title;

Tratando sinónimos

Los sinónimos son nombres alternativos que se le asignan a objetos que contienen datos como son las tablas y las vistas.

La ventaja de crear un sinónimo es que elimina la necesidad de especificar el propietario del esquema.

Se consigue una capa de abstracción entre el objeto y su esquema.

Se pueden crear sinónimos de:

• Tablas.

• Vistas y otros sinónimos.

• Paquetes, procedimientos y funciones.

• Secuencias.

• Tipos.

• Java Class.

Existen 2 tipos de sinónimos:

• Públicos:
  Existen independientemente del esquema.
  Puede ser referido por cualquier usuario.
  Pueden tener el mismo nombre que otro objeto del esquema.

• Privados:
  Debe ser un nombre único dentro del esquema.

Oracle busca al objeto en el esquema local, y si no lo encuentra, lo buscará como un sinónimo público.

Un usuario necesita tener asignado permisos para crear sinónimos.

Normalmente solo el administrador de bases de datos puede crear y eliminar sinónimos públicos.

Sintaxis para crear un sinónimo:

• Create [public] synonym nombre_sinonimo for object;

La opción “public” indica que no es un objeto de esquema y por lo tanto no se indica el esquema.

Esto no significa que todos los usuarios tengan permisos para utilizarlo.

Sintaxis para borrar un sinónimo:

• Drop [public] synonym nombre_sinonimo;

Sintaxis para compilar un sinónimo:

• Alter synonym nombre_sinonimo COMPILE;

Un sinónimo no se puede modificar hay que borrarlo y volverlo a crear.

Tratando Índices

Los índices son estructuras asociadas a tablas.

Se utilizan para proporcionar acceso inmediato a valores claves y realizan la búsqueda de estos valores de forma instantánea.

Los índices aumentan la velocidad de búsqueda, pero ocupan espacio.

Sus funciones principales son:

• Obligar  al cumplimiento de las restricciones primary key y unique ya que cada  vez que se inserta un nuevo registro obliga a que no viole ninguna  restricción.

• Mejoran el rendimiento de las consultas a las tablas por lo que la consulta se realizará más rápido.

• Todas las columnas claves primary key (pks) o unique se crean automáticamente con un índice.

Sintaxis y opciones para crear un índice:

• CREATE [UNIQUE |BITMAP ] INDEX [esquema.]Nombre_indice ON [esquema.]Nombre_tabla (columna1 [columna n]);

Opciones:

• UNIQUE: se utiliza cuando queremos que el valor de la columna o columnas no se repita y sea único.

• BITMAP:  se utiliza para especificar que el índice se cree con un mapa de bits  por cada clave distinta y no para cada registro separado.

Ejemplo crear índice:

• CREATE BITMAP INDEX hr.minsalary On hr.jobs(min_salary);

Sintaxis para borrar índices:

• DROP INDEX [esquema.]Nombre_indice;

Ejemplo borrar índice:

• DROP INDEX hr.minsalary;

Tratando Secuencias

Una secuencia es una tabla con un campo numérico en el cual se almacena un valor.

Las secuencias se crean para generar valores enteros únicos y consecutivos.

Se utiliza principalmente para generar valores para las claves primarias (pks).

Sentencia crear una secuencia:

• CREATE  SEQUENCE [esquema.] Nombre_secuencia [START WITH número] [INCREMENT BY  número] [MAXVALUE número | NOMAXVALUE] [MINVALUE número | NOMINVALUE]  [CYCLE | NOCYCLE];

Ejemplo crear una secuencia:

• CREATE SEQUENCE hr.nueva_seq START WITH 1;

Opciones para crear una secuencia:

• INCREMENT BY:
  El valor que se va a sumar al valor actual.
  Puede ser un número negativo o diferente de 0.
  Si no se indica, por defecto es 1.

• MAXVALUE:
  Indica el máximo valor al que puede llegar la secuencia.
  Si no se indica, por defecto es 99999999999999999999999999.

• MINVALUE:
  Indica el mínimo valor al que puede llegar la secuencia.
  Si no se indica, por defecto es 1.

• CYCLE:
  Indica que cuando se llegue al máximo valor, se reinicie al mínimo valor nuevamente.
  Si no se indica, por defecto la secuencia se crea nocycle.

Sentencias para recuperar los valores:

• NEXTVAL: devuelve el siguiente valor del valor actual (incrementándolo o decrementándolo).
  Ejemplo: SELECT nueva_seq.NEXTVAL FROM dual;

• CURRVAL: devuelve el valor actual de la secuencia.
  Ejemplo: SELECT nueva_seq.currval FROM dual;

Ejemplo de uso de secuencias en un insert:

• Ejemplo: INSERT INTO hr.jobs VALUES (hr.nueva_seq.currval, 'designer', 1700, 2500);

Sentencia y opciones para modificar una sentencia:

• ALTER  SEQUENCE [esquema.] Nombre_secuencia [INCREMENT BY número][MAXVALUE  número | NOMAXVALUE] [MINVALUE número | NOMINVALUE] [CYCLE | NOCYCLE]

• El único valor que no se puede modificar de una sentencia es el valor de comienzo.

Ejemplo para incrementar el valor de la secuencia:

• ALTER SEQUENCE hr.nueva2_seq INCREMENT BY 3;

Sentencia para eliminar una secuencia:

• DROP SEQUENCE nombre_secuencias;

• Ejemplo: DROP SEQUENCE nueva2_seq;

Sentencia para ver todas las secuencias a la que se tiene acceso:

• SELECT * FROM all_sequences;

Sentencia para ver todos los objetos a los que se tiene acceso:

• SELECT * FROM all_objects;

Condicionales para null

En sql existen funciones que tratan valores nulos.

• Nullif(expresion1, expresion2):
  Esta función compara las dos expresiones y devuelve:
  Null, si sus resultados son iguales.
  O la expresion1 si sus resultados no son iguales.
  Ejemplo:
  Nullif (“hola”,”hola”);
  Null.
  Nullif (“hola”,”adiós”);
  Hola.

• Coalesce(expresion1, expresion2, [expresionn]):
  Esta función compara todas las expresiones indicadas y devuelve:
  La primera expresión no nula de la lista de expresiones.
  Si todas las expresiones son nulas devolvera null.
  Ejemplo:
  Coalesce (null,null,null);
  Null.
  Coalesce (null, “hola”,null);
  Hola.

• Nanvl(n2, n1):
  Esta función compara las dos expresiones y devuelve:
  N2, si es un valor numérico válido.
  N1, si n2 no es un valor numérico válido.
  Ejemplo:
  Nanvl (10,20);
  10.
  Nanvl (“hola”,20);
  20.

• Nvl(expresion1, expresion2):
  Esta función compara las dos expresiones y devuelve:
  Expresion1 si esta no es nula.
  Expresion2, sila expresion1 es nula.
  Ejemplo:
  Nvl (“hola”,”adiós”);
  Hola.
  Nvl (null,”adiós”);
  Adiós.

Función case

En Oracle podemos usar la funcionalidad case.

• Sintaxis:

Case

When expresión then salida

When expresión then salida

..

Else salida

End.

Empieza con la utilización de “case”.

Y finaliza con “end”.

La palabra “when” se utiliza para evaluar una expresión.

Y “ then ” para ejecutar una salida a cumplirse esa expresión.

En caso de no cumplirse ninguna de las condiciones anteriores se ejecuta la salida del “else”.

Ejemplo:

• Select

Case

When salary <400 then 'salario muy bajo'

When salary >=400 and salary <1000 then 'salario bajo'

When salary >=1000 and salary <1500 then 'salario normal'

Else 'buen salario'

End employeed_id from hr.employees;

Variables de sustitución

Este tipo de variables se utiliza para crear sentencias sql.

Al ejecutar el sistema solicitara la información.

Existen dos tipos de variables:

• Con un ampersand “&”:

• El sistema solicitará la información tantas veces como existan.

• Si una sentencia utiliza la variable &var 3 veces, el sistema pedida 3 vece la información a introducir por cada variable

• Con dos ampersand “&&”:

• El sistema solo solicitara una vez la información por cada variable que tenga dos ampersand.

Ejemplos:

• Select &columna, country_name from hr.countries order by &columna;

• Select &&columna, country_name from hr.countries order by &&columna;