Creando Vistas en la Base de Datos (PL/SQL)

¿Qué es una Vista?

Una Vista es una representación lógica (no física como una tabla) basada en una tabla, varias tablas o en otra vista. No contiene datos, pero es similar a una “ventana” que a través de ella se pueden ver o modificar datos. Las tablas sobre las cuales está basada la vista se llaman Tablas Base. Las vista se almacenan como una sentencia SELECT en el diccionario de datos de la BD.

• Las vistas restringen el acceso a los datos porque puede mostrar columnas seleccionadas desde la tabla.
• Una vista puede ser usada para construir una consulta simple que recupere datos desde otra consulta compleja.
• Las vistas proporcionan independencia de los datos entre el usuario y la aplicación.
• Las vistas proporcionan a grupos de usuarios acceder a los datos de acuerdo a criterios en particular.

Vistas Simples y Complejas

Existen dos clasificaciones para las vistas: simples y complejas. La diferencia básica está relacionada con las operaciones DML (INSERT, UPDATE y DELETE).

Una Vista Simple es aquella que:

• Deriva de datos de una sola tabla
• No contiene funciones o grupos de datos.
• Se puede realizar operaciones de DML a través de la vista.

Una Vista Compleja es aquella que:

• Deriva de datos de una o más tablas.
• Contiene funciones o grupos de datos.
• No siempre permite operaciones DML través de la vista.

Creando una Vista

Para crear una vista se utiliza la sentencia CREATE VIEW.
Se debe incorporar una subconsulta en la sentencia CREATE VIEW.
La subconsulta puede contener sentencias SELECT complejas.

En la sintaxis:

• OR REPLACE: recrea (vuelve a crear) la vista si ésta ya existe.
• FORCE: crea la vista sin importar que no existan las tablas bases.
• NOFORCE: crea la vista sólo si las tablas bases existen. Es la opción por defecto.
• nombre_vista: es el nombre de la vista que se desea crear.
• alias: especifica el nombre para las expresiones seleccionadas por la consulta de la vista. Deben coincidir con el número de expresiones seleccionadas por la vista. Si no se especifican alias, los nombres de columnas de la vista serán las columna seleccionadas en la lista de la cláusula SELECT.
• subconsulta: es una sentencia SELECT completa.
• WITH CHECK OPTION : Especifica que solo las filas accesibles para la vista pueden ser insertadas o modificadas. La restricción que sigue a esta sección es el nombre que se le da a la restricción de tipo CHECK OPTION. Si no le asigna un nombre a la restricción, el sistema asigna un nombre por defecto en el formato SYS_Cn
• WITH READ ONLY: especifica que la vista sea sólo de lectura.

NOTA: Los tipos de datos de las columnas de la vista corresponden a los tipos de datos de las columnas de la tabla base.

-- Ejemplo

CREATE VIEW v_emp_depto_80
AS SELECT employee_id, last_name, salary
   FROM employees
   WHERE department_id = 80;

DESC v_emp_depto_80;

SELECT *
FROM v_emp_depto_80
ORDER BY salary, last_name;

En el ejemplo, se crea la vista v_emp_depto_80 que mostrará la identificación, apellido y salario de los empleados que trabajan en el departamento 80. Para ver la estructura de la vista creada se puede usar el comando DESCRIBE (DESC). Al momento de mostrar los datos, se puede mostrar todo el contenido que retorna la vista o restringir las filas y columnas a través de alguna condición (de la misma manera como se realiza en una sentencia Select basada en una tabla). En el ejemplo se muestran todas las filas y columnas de la vista v_emp_depto_80 ordenadas por salario y apellido en forma ascendente.

Se pueden controlar los nombres de columna de la vista mediante la inclusión de los alias de columna en la subconsulta.

-- Ejemplo

CREATE VIEW v_salarios_depto_50
AS SELECT employee_id ID_EMPLEADO, last_name APELLIDO,
          salary SALARIO_MENSUAL, salary*12 SALARIO_ANUAL
    FROM employees
    WHERE department_id = 50;
 
SELECT *
FROM v_salarios_depto_50;

El ejemplo, se crea la vista v_salarios_depto_50 que contiene la identificación del empleado con el alias ID_EMPLEADO, su apellido con el alias APELLIDO, su salario con el alias SALARIO_MENSUAL y el salario multiplicado por 12 con el alias SALARIO_ANUAL para todos los empleados del departamento 50. Alternativamente, se puede utilizar un alias en la cláusula CREATE VIEW. El número de alias enumerados debe coincidir con el número de expresiones seleccionadas en la subconsulta.

Modificando una Vista

Con la opción OR REPLACE, una vista se puede crear incluso si ya existe con el mismo nombre, sustituyendo así la antigua versión de la vista. Esto significa que la vista puede ser modificada sin eliminar o tener que volver a asignar privilegios sobre el objeto. Al asignar alias de columna en la cláusula CREATE OR REPLACE VIEW, se debe recordar que los alias se enumeran en el mismo orden que las columnas de la subconsulta.

-- Ejemplo

CREATE OR REPLACE VIEW v_emp_depto_80
(id_empleado, nombre, salario, id_departmento)
AS SELECT employee_id, first_name || ' ' || last_name, salary, department_id
   FROM employees
   WHERE department_id = 80;
   

DESC v_emp_depto_80;

SELECT nombre, salario
FROM v_emp_depto_80
WHERE salario > 10000;

En el ejemplo, a la vista v_emp_depto_80 (creada anteriormente), se le asigna un alias por cada nombre de columna en la cláusula CREATE OR REPLACE VIEW. Por lo tanto, para la columna employee_id la vista tendrá el alias id_empleado, para las columnas first_name y last_name (cocatenadas) se le asigna el alias nombre, a la columnas salary el alias salario y para la columna department_id el alias departamento. Posteriormente, se muestra el nombre y salario de la vista creada para los empleados con salario mayor a 10000.

-- Ejemplo

CREATE OR REPLACE VIEW v_dept_sum
(nombre_depto, minsal, maxsal, avgsal)
AS SELECT d.department_name, MIN(e.salary), MAX(e.salary),  
   ROUND(AVG(e.salary))
   FROM employees e JOIN departments d
   ON (e.department_id = d.department_id)
   GROUP BY d.department_name;
   
SELECT * 
FROM v_dept_sum
ORDER BY minsal, nombre_depto;

En el ejemplo, la vista v_dept_sum contiene funciones de grupo y muestra valores desde dos tablas. La vista contendrá el nombre de departamento (cuyo código exista en las tablas employees y departments), su salario mínimo, salario máximo y salario promedio. En este caso, a los resultados de cada función de grupo se le debe asignar una alias en la vista ya que es un requisito si cualquier columna de la vista se deriva de una función o una expresión. La información de la vista se muestra ordenada por salario mínimo y nombre de departamento en forma ascendente.
Recordar que si se desea ver la estructura de la vista, se puede usar DESCRIBE.

Reglas para Efectuar Operaciones DML sobre una Vista

• Generalmente se pueden realizar operaciones DML sobre vistas simples.
  
• No se pueden eliminar filas si la vista contiene: funciones de grupo, cláusula GROUP BY, la palabra DISTINCT o una pseudocolumna (ej. ROWNUM).
  
• No se pueden modificar datos en una vista si contiene: funciones de grupo, cláusula GROUP BY, la palabra DISTINCT, una pseudocolumna (ej. ROWNUM), columnas definidas por una expresión o si las columnas NOT NULL en la tabla base no han sido seleccionadas en la vista
  

Uso de la Cláusula WITH CHECK OPTION

A través de las vistas es posible realizar controles de la integridad referencial. Por tanto, una vista puede servir de mecanismo para proteger la integridad de los datos. La vista se puede utilizar para proteger la integridad de datos, pero el uso es limitado. La cláusula WITH CHECK OPTION especifica que las inserciones y actualizaciones que se realizan a través de la vista no pueden crear filas que la vista no selecciona. Por lo tanto, permite que las restricciones de integridad y controles de validación de datos se deban ejecutar en los datos que se insertan o actualizan. Por lo tanto, en el ejemplo, la cláusula "with check option", no permite modificar la columna department_id porque al hacerlo la fila ya no aparecería en la vista; sí se pueden actualizar las demás columna. Por ejemplo, si se intenta actualizar a 10 la columna employee_id de alguna fila mediante la vista, Oracle muestra un mensaje de error.

La misma restricción surge al ejecutar un INSERT sobre la vista; solamente se pueden agregar filas el valor 30 para la columna department_id; si se intenta ingresar filas con un valor diferente de 30 a esta columna, Oracle mostrará un mensaje de error.

-- Ejemplo

CREATE OR REPLACE VIEW v_dept30
AS SELECT *
FROM employees
WHERE department_id=30
WITH CHECK OPTION CONSTRAINT control_v_dept30;

-- Ejemplo

UPDATE v_dept30
SET department_id=10
WHERE employee_id = 115;

Si a través de la vista creada anteriormente se intenta una operación INSERT o UPDATE con un departamento que no sea 30 se generará el error ORA-01402

Uso de la Cláusula WITH READ ONLY

Para asegurar de que no se efectúen operaciones DML en la vista se debe agregar la opción WITH READ ONLY al momento de crear la vista.

Cuando se intenta efectuar una operación DML sobre cualquier fila de la vista el servidor Oracle retornará un error ORA-42399. Del mismo modo, cualquier intento de insertar o modificar una fila usando la vista con una restricción de sólo lectura se traduce en el mismo error.

-- Ejemplo

CREATE OR REPLACE VIEW empvu10
(employee_number, employee_name, job_title)
AS SELECT employee_id, last_name, job_id
   FROM employees
   WHERE department_id = 10
   WITH READ ONLY ;
   
DELETE FROM empvu10
WHERE  employee_number = 200;

Uso de Subconsultas para Resolver Consultas (PL/SQL)

Usando Subconsulta para Resolver un Problema

Supongamos que se quiere escribir una consulta para saber quién gana un salario mayor que el salario de Abel.
Para resolver este problema, se necesitan dos consultas: una para ver cuánto gana Abel, y una segunda consulta para encontrar quién gana más de esa cantidad.
Este problema se puede resolver con la combinación de las dos consultas, colocando una consulta dentro de otra consulta. La consulta interna (o subconsulta) devuelve un valor que es utilizado por la consulta externa (o consulta principal).
El uso de una subconsulta es equivalente a realizar dos consultas secuenciales y usar el resultado de la primera consulta como el valor de búsqueda en la segunda consulta.

Una subconsulta es una instrucción SELECT que está en una cláusula de otra instrucción SELECT. Se pueden construir poderosas declaraciones mediante el uso de subconsultas. Pueden ser muy útil cuando se necesita seleccionar filas de una tabla con una condición que depende de los datos de la misma tabla.

Se pueden colocar subconsultas en una cláusula SQL como:

• Cláusula WHERE.
• Cláusula HAVING.
• Cláusula FROM.

En la sintaxis:
operador_comparación : corresponde a una condición de comparación que pueden ser: >, =, >=, <, <>, <= (operadores de una sola fila) o IN, ANY, ALL (operadores de múltiples filas).

-- Ejemplo

SELECT last_name, salary
FROM employees
WHERE salary > 11000       
            (SELECT salary
                FROM employees
                WHERE last_name = 'Abel');

En el ejemplo, la consulta interna o subconsulta es la primera que se resuelve y obtiene el salario del empleado Abel. Posteriormente la consulta externa obtiene a todos los empleados que posean un salario mayor al valor obtenido en la subconsulta que en este caso es 11000.

• Las subconsultas deben ser escritas entre paréntesis.
• Colocar las subconsultas en el lado derecho de la condición de comparación para mejorar la legibilidad. Sin embargo, las subconsultas pueden aparecer en cualquier lado de el operador de comparación.
• La cláusula ORDER BY de la subconsulta no es necesaria.
• Se deben utilizar operadores de una sola fila con subconsultas que retornan una sola fila y operadores de varias filas con subconsultas que retornan varias filas.

Tipos de Subconsultas

Subconsultas de una sola fila: consultas que devuelven una sola fila desde la sentencia SELECT interna.

Subconsultas de múltiples (varias) filas: consultas que devuelven más de una fila desde la sentencia SELECT interna.

NOTA: También hay subconsultas de varias columnas, que son consultas que devuelven más de una columna en la sentencia SELECT interna

Subconsultas de una Fila

Una subconsulta de una sola fila es la que devuelve una fila desde la sentencia SELECT interna. Este tipo de subconsulta utiliza un operador de una sola fila:

• = : igual que
• > : mayor que
• >= : mayor o igual que
• < : menor que



• <= : menor o igual que

-- Ejemplo

SELECT employee_id, job_id, salary
FROM employees
WHERE job_id = ST_CLERK
               (SELECT job_id
                FROM employees
                WHERE employee_id = 141);

En la sentencia, se resuelve la subconsulta para obtener el trabajo del empleado 141. El valor que retorna la subconsulta (ST_CLERK) es el que utiliza la condición de comparación de la consulta principal para mostrar a los empleados que poseen el trabajo ST_CLERK (el trabajo del empleado 141).

-- Ejemplo

SELECT employee_id, job_id, salary
FROM employees
WHERE job_id = ST_CLERK
               (SELECT job_id
                FROM employees
                WHERE employee_id = 141)
AND salary > 2600
             (SELECT salary
              FROM employees
              WHERE employee_id = 143);

La sentencia del ejemplo, retorna información de los empleados cuyo trabajo es igual al trabajo de empleado 141 (ST_CLERK) y su salario es mayor al salario que posee el empleado 143 (2600).

Subconsultas de una Fila y Funciones de Grupo

Se pueden mostrar datos de una consulta principal mediante el uso de una función de grupo en una subconsulta de una sola fila. La subconsulta está entre paréntesis y se coloca después de la condición de comparación.

-- Ejemplo

SELECT last_name, job_id, salary
FROM employees
WHERE salary = 2100
               (SELECT MIN(salary)
                FROM employees);
    
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > 2100
                     (SELECT MIN(salary)
                      FROM employees
                      WHERE department_id = 50)
ORDER BY MIN(salary);

En la sentencia del primer ejemplo, primero se resuelve la subconsulta que obtiene el salario mínimo que existe en la tabla EMPLOYEES (2100). El valor obtenido de la subconsulta es el que utiliza la condición de comparación de la consulta principal. Es decir, la consulta principal muestra a los empleados que poseen un salario igual a 2100. En la sentencia del segundo ejemplo, primero se resuelve la subconsulta que obtiene el salario mínimo del departamento 50 (2100). El valor obtenido de la subconsulta es el que utiliza la condición de comparación de la consulta principal. Es decir, la consulta principal muestra los departamentos que poseen un salario mínimo mayor a 2100. Como la condición de comparación está basada en el resultado de una función de grupo se debe utilizar la cláusula HAVING.

-- ¿Cuál es el error en la Sentencia?

SELECT employee_id, last_name, salary
FROM employees
WHERE salary = (SELECT MIN(salary)
                 FROM employees
                 GROUP BY department_id);

Un error común con subconsultas se produce cuando la subconsulta retorna más de una fila y la condición de comparación es de una sola fila. En la sentencia que se muestra, la subconsulta contiene una cláusula GROUP BY, lo que implica que la subconsulta devolverá varias filas, una para cada grupo que encuentre. En este caso, los resultados de la subconsulta son 4400, 6000, 2500, 4200, 7000, 17000, y 8300. La consulta externa toma esos resultados y los utiliza en su cláusula WHERE. La cláusula WHERE contiene un operador de igualdad (=) que es un operador de comparación de una sola fila por lo tanto espera sólo un valor. El operador = no puede aceptar más de un valor de la subconsulta y, por lo tanto, genera el error.

Subconsultas de Múltiples Filas

Las subconsultas que devuelven más de una fila se llaman subconsultas de varias o múltiples filas. Se deben utilizar operadores de varias filas, en lugar de un operador de una sola fila, con una subconsulta de varias filas ya que esperan uno o más valores para comparar.

• IN : igual a cualquier miembro de la lista.
• ANY : compara el valor con cada valor devuelto por la subconsulta.
• ALL : compara el valor con todos los valores devueltos por la subconsulta.

-- Ejemplo

SELECT employee_id, job, salary
FROM employees
WHERE salary IN 2100,2500,4200,4400, … ,17000
             (SELECT MIN(salary)
              FROM employees
              GROUP BY department_id);

En el ejemplo, para poder obtener los empleados cuyo salario sea igual a alguno de los salarios mínimos que existen en cada departamento se debe utilizar el operador de comparación IN.

-- Ejemplo

SELECT employee_id, job_id, salary
FROM employees
WHERE salary < ANY 9000,6000,4800 y 4200
               (SELECT DISTINCT salary
                FROM employees
                WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG'
ORDER BY employee_id;

En la sentencia primero se resuelve la subconsulta que obtiene los diferentes salarios entre los empleados que poseen el trabajo IT_PROG (9000, 6000, 4800 y 4200). Por lo tanto la consulta principal retorna a los empleados que posean un salario menor a cualquiera de los salarios obtenidos en la subconsulta y que además posean un trabajo distinto a IT_PROG.

-- Ejemplo

SELECT employee_id, job_id, salary
FROM employees
WHERE salary < ALL 9000,6000,4800 y 4200
               (SELECT DISTINCT salary
                FROM employees
                WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG'
ORDER BY employee_id;

En la sentencia primero se resuelve la subconsulta que obtiene los diferentes salarios entre los empleados que poseen el trabajo IT_PROG (9000, 6000, 4800 y 4200). Por lo tanto la consulta principal retorna a los empleados que posean un salario menor a todos los salarios obtenidos en la subconsulta y que además posean un trabajo distinto a IT_PROG.

Guia:

/* 1.- La empresa Norteamericana "FACE TO FACE" lo ha contratado a Ud. para desarrollar su nuevo Sistema de Personal.  Como primera etapa se 
requiere que, a partir del  Modelo que se presenta, pueda satisfacer los requerimientos de información que se solicitan a continuación: 
  a) Se ha detectado que dado el trabajo que desempeñan, existen empleados que no les corresponde percibir el mismo salario del empleado 204. 
  Dado esto, se requiere saber la identificación, nombre (nombre y apellido concatenados), trabajo y el salario de estos empleados (incluyendo 
  al empleado 204). La información se debe  mostrar como se visualiza en el ejemplo:  */

SELECT EMPLOYEE_ID "IDENTIFICACION EMPLEADO", FIRST_NAME || ' ' || LAST_NAME EMPLEADO, JOB_ID, SALARY SALARIO 
FROM EMPLOYEES
WHERE SALARY = (SELECT SALARY
                FROM EMPLOYEES
                WHERE EMPLOYEE_ID = 204);
                
  /* b) La empresa desea mejorar la situación económica de los empleados cuyo salario es menor al salario promedio entre todos los empleados. 
  Inicialmente la idea es aumentar los salarios de estos empleados según el tiempo que llevan trabajando en la empresa a la fecha actual (puede 
  ser según los días, meses o años que lleva trabajando). Para ello, se requiere saber nombre, apellido, salario actual, total de días, total 
  de meses y años que llevan contratados los empleados que se encuentran en esta condición. La información se debe mostrar en el formato del 
  ejemplo  y ordena en forma ascendente por salario e  identificación del empleado (la información que se muestra se obtuvo en Febrero del 2014) */

SELECT EMPLOYEE_ID EMPLEADO, TO_CHAR(SALARY,'$99,999') "SALARIO ACTUAL", TO_CHAR(HIRE_DATE,'DD/MM/YYYY') "FECHA CONTRATO", 
       ROUND(SYSDATE - HIRE_DATE) "DIAS TRABAJADOS", ROUND(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) "MESES CONTRATADOS",
       ROUND(MONTHS_BETWEEN(SYSDATE,HIRE_DATE) / 12) "AÑOS TRABAJADOS"
FROM EMPLOYEES
WHERE SALARY < (SELECT AVG(SALARY)
                FROM EMPLOYEES)
ORDER BY SALARY, EMPLOYEE_ID;  

  /* c) Dado la gran diferencia en la distribución actual de los empleados en los diferentes departamentos, se efectuará una nueva redistribución 
  con el objetivo de corregir esta situación. Por lo tanto, la primera medida será cambiar algunos empleados del departamento que tenga más 
  empleados. Para ello, se requiere saber el nombre del departamento y el total de empleados del departamento que cumpla con esa condición como se 
  muestra en el ejemplo: */
  
SELECT d.DEPARTMENT_NAME DEPARTAMENTO, COUNT(e.EMPLOYEE_ID) "TOTAL DE EMPLEADOS"
FROM EMPLOYEES e JOIN DEPARTMENTS d USING(DEPARTMENT_ID)
GROUP BY d.DEPARTMENT_NAME
HAVING COUNT(e.EMPLOYEE_ID) = (SELECT MAX(COUNT(e.EMPLOYEE_ID)) 
                               FROM EMPLOYEES e JOIN DEPARTMENTS d USING(DEPARTMENT_ID)
                               GROUP BY d.DEPARTMENT_NAME);

Mostrando Datos de Múltiples Tablas (PL/SQL)

Calificación de Columnas en un Join

Cuando se unen dos o más tablas , se necesita calificar los nombres de las columnas con el nombre de la tabla para evitar la ambigüedad. Sin los prefijos de tabla, por ejemplo para mostrar la identificación del departamento y su nombre, la columna DEPARTMENT_ID en la lista SELECT podría ser de la tabla DEPARTMENTS o la tabla EMPLOYEES. Es necesario entonces añadir el prefijo de tabla para ejecutar la consulta. Si no hay nombres de columna comunes entre las dos tablas , no hay necesidad de calificar las columnas pero, utilizando el prefijo de la tabla mejora el rendimiento, ya que le dice al servidor de Oracle exactamente dónde encontrar las columnas.
Sin embargo, calificar nombres de columna con los nombres de tabla puede llevar mucho tiempo, sobre todo si los nombres de tabla son largos. En su lugar, puede utilizar alias de tabla . Así como un alias de columna da una columna de otro nombre, un alias de tabla da una tabla con otro nombre. Los alias de tabla ayudan a mantener el código SQL más pequeño, por lo tanto, usando menos memoria.
El nombre de la tabla se especifica por completo, seguido por un espacio y luego el alias de la tabla.

-- Ejemplo

SELECT e.employee_id "ID EMPLEADO", e.job_id "ID TRABAJO",
       j.job_title "DESCRIPCION TRABAJO"
FROM employees e JOIN jobs j
ON(e.job_id = j.job_id)
ORDER BY e.employee_id;

En la sentencia del ejemplo, en la cláusula FROM a la tabla EMPLOYEES se le asigna el alias e y a la tabla JOBS se le asigna el alias j. Son estos alias los que se utilizan en las cláusulas SELECT, ON y ORDER BY para hacer referencia en forma más clara y eficiente a las columnas de cada tabla.
La sentencia entonces, retorna la identificación del empleado, identificación del trabajo y nombre del trabajo que cada empleado desempeña. La información se muestra ordenada por identificación del empleado.

Creando Joins con la cláusula NATURAL JOINS

La cláusula Natural Join establece una relación de igualdad basada en TODAS las columnas de dos tablas que poseen el mismo nombre.
Permite seleccionar filas desde dos tablas que tengan los mismos valores en todas las columnas del mismo nombre.
Si las columnas tienen el mismo nombre pero diferentes tipos de datos el servidor Oracle retorna un error.

-- Ejemplo

SELECT department_id, department_name, location_id, city
FROM departments NATURAL JOIN locations;

En la sentencia del ejemplo, los datos que se desean mostrar existen en las tabla DEPARTMENTS y LOCATIONS las que se relacionan a través de la columna location_id que es la única con el mismo nombre en ambas tablas. Si existieran otras columnas con el mismo nombre en ambas tablas la cláusula NATURAL JOIN también usaría esas columnas para relacionar ambas tablas.

-- Ejemplo

SELECT department_id, department_name, location_id, city
FROM departments NATURAL JOIN locations
WHERE department_id IN(20,50);

En el ejemplo, se muestra información de las tablas DEPARTMENTS y LOCATIONS relacionadas a través de la columna location_id. Las filas que se muestran son para el departamento 20 ó 50.

Creando Joins con la cláusula USING

Si en las tablas desde las cuales se desean obtener datos existen varias columnas que tienen los mismos nombres, a través de la cláusula USING se puede especificar las columnas que se deben utilizar para una unión de igualdad.
La cláusula USING permite hacer coincidir solamente una columna cuando en ambas tablas coincidan varias columnas con el mismo nombre.
Las cláusulas NATURAL JOIN y USING son mutuamente excluyentes.

-- Ejemplo

SELECT employee_id, last_name, department_id, location_id
FROM employees JOIN departments 
USING (department_id)
ORDER BY employee_id;

En la sentencia del ejemplo, las tablas EMPLOYEES y DEPARTMENTS se relacionan a través de la columna department_id para mostrar identificación del empleado, apellido, identificación del departamento y la localización del departamento al que pertenece cada empleado. La información se muestra ordenada en forma ascendente por identificación del empleado.

-- Ejemplo

SELECT department_name, city 
FROM departments JOIN  locations 
USING (location_id)
WHERE location_id = 1400;

En la sentencia del ejemplo, las tablas DEPARTMENTS y LOCATIONS se unen a través de la columna location_id para poder mostrar el nombre del departamento y nombre de la ciudad que se encuentra en la localización 1400.

-- Sentencia incorrecta

SELECT l.city, d.department_name 
FROM locations l JOIN departments d
USING (location_id)
WHERE d.location_id = 1400;

-- Sentencia correcta

SELECT l.city, d.department_name 
FROM locations l JOIN departments d
USING (location_id)
WHERE location_id = 1400;

Al unir tablas con la cláusula USING, no se puede calificar a una columna que se utiliza en la propia cláusula USING. Además, si esa columna se utiliza en cualquier parte de la instrucción SQL, no se puede utilizar un alias en ella. En la sentencia izquierda del ejemplo, no debe usar un alias en la columna location_id en la cláusula WHERE, porque la columna se utiliza en la cláusula USING. La sentencia derecha del ejemplo es la correcta
La columna que se hace referencia en la cláusula USING no deberían tener una calificación (nombre de tabla o alias) en cualquier parte de la instrucción SQL.

Creando Joins con la cláusula ON

La condición de unión para los NATURAL JOIN es básicamente una unión de igualdad de todas las columnas con el mismo nombre.
Para especificar las columnas por las cuales se quiere para unir dos tablas, se utiliza la cláusula ON.
La condición de JOIN o de unión se separan de otras condiciones de búsqueda (WHERE).
También se puede usar la cláusula ON para unir columnas que tengan diferentes nombres pero contienen el mismo dato en la misma tabla o en tablas diferentes.

-- Ejemplo

SELECT e.employee_id, e.last_name, e.department_id "DEPTO.EMPLOYEES",
       d.department_id "DEPTO.DEPARTMENTS", d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
ORDER BY e.employee_id;

En la sentencia del ejemplo, se muestra información de las tablas EMPLOYEES y DEPARTMENTS uniéndolas a través de la columna department_id que existe en ambas. Los datos de ambas tablas se muestran cuando el valor en la columna department_id de la tabla EMPLOYEES exista en la columna department_id de la tabla DEPARTMENTS. Por esta razón se muestran 106 filas ya que el empleado que posee NULO en la identificación de departamento no se muestra.

-- Ejemplo

SELECT d.department_id departamento, d.location_id localidad, l.city ciudad
FROM departments d JOIN locations l  
ON (d.location_id = l.location_id)
ORDER BY d.department_id;

En el ejemplo, se muestra información de las tablas DEPARTMENTS y LOCATIONS uniéndolas a través de la columna location_id que existe en ambas. Los datos de ambas tablas se muestran cuando el valor en la columna location_id de la tabla DEPARTMENTS exista en la columna location_id de la tabla LOCATIONS.

Creando Joins en Tres Sentidos

Un Join en tres sentidos es la unión de tres tablas y se ejecutan de izquierda a derecha. Esto quiere decir, que en el ejemplo el primer join que se ejecuta es EMPLOYEES JOIN DEPARTMENTS y posteriormente el JOIN de DEPARTMENTS con LOCATIONS los departamentos retornados por el primer JOIN,
La condición del primer join puede referenciar sólo las columnas de las tablas del primer Join (en el ejemplo de las tablas EMPLOYEES o DEPARTMENTS) pero no puede referenciar columnas de la tabla del segundo Join (en el ejemplo tabla LOCATIONS). La condición del segundo join puede referenciar columnas de las tres tablas (EMPLOYEES, DEPARTMENTS o LOCATIONS).

-- Ejemplo

SELECT emp.employee_id, dep.department_name, loc.city
FROM employees emp JOIN departments dep
USING(department_id)
JOIN locations loc
USING(location_id);

En el ejemplo, se obtiene la identificación de los empleados, nombre del departamento (si la identificación del departamento existen en las tablas EMPLOYEES y DEPARTAMENTS) y la ciudad en la que se encuentra el departamento (si la localidad de los departamentos retornados en el primer join existe en la tabla LOCATIONS).

-- Ejemplo

SELECT emp.employee_id, dep.department_name, loc.city
FROM employees emp JOIN departments dep
ON emp.department_id = dep.department_id
JOIN locations loc
ON dep.location_id = loc.location_id;

Agregando Condiciones Adicionales a un Join

Para agregar una condición a la cláusula USING se debe agregar la cláusula WHERE. Si se desea incorporar más condiciones, se deben agregar las cláusulas AND que se deseen a continuación a continuación de la cláusula WHERE.

-- Ejemplo

SELECT employee_id, salary, e.manager_id, department_id, department_name
FROM employees e JOIN departments 
USING(department_id)
WHERE e.manager_id IN(149,100)
AND salary < 10000;

En el ejemplo, se muestra la información de los empleados cuyo jefe sea 149 o 100 y además posean un salario menor a 10000

-- Ejemplo

SELECT e.employee_id, e.salary, 
       e.manager_id, e.department_id, 
       d.department_name
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
AND e.manager_id IN(149,100)
AND salary < 10000;

SELECT e.employee_id, e.salary, 
       e.manager_id, e.department_id, 
       d.department_name
FROM employees e JOIN departments d
ON(e.department_id = d.department_id)
WHERE e.manager_id IN(149,100)
AND salary < 10000;

El ejemplo se basa en la misma sentencia anterior, pero como ahora se utiliza la cláusula ON en el Join, para agregar condiciones se puede utilizar la cláusula AND (ejemplo de la izquierda). Opcionalmente, se puede utilizar la cláusula condicional WHERE seguido de las cláusulas AND que se deseen (ejemplo de la derecha).

Join sobre la misma tabla o Self-Join

En ocasiones se debe realizar un join sobre la misma tabla para obtener algún dato. Por lo tanto, es necesario unir las columnas de la tabla que contienen el mismo dato simulando que pertenecen a tablas diferentes.

-- Ejemplo

SELECT e.employee_id "Id Empleado", e.last_name "Empleado", 
       e.manager_id "Id Jefe", m.last_name "Jefe"
FROM employees e JOIN employees m
ON (e.manager_id = m.employee_id)
ORDER BY e.employee_id;

En el ejemplo, para obtener el nombre del jefe de cada empleado se debe realizar un join sobre la misma tabla EMPLOYEES ó Self-Join ya que la información que almacena la columna manager_id es la misma de la columna employee_id. Para ello se unen ambas columnas de la tabla EMPLEOYEES simulando que pertenecen a tablas diferentes.

Nonequijoins

Es una condición de unión que contiene un operador de comparación que no sea el operador de igualdad (=). Por simplicidad se utiliza BETWEEN. La relación entre la tabla EMPLOYEES y la tabla JOB_GRADES es un ejemplo de un nonequijoin. La columna SALARY en la tabla EMPLOYEES oscila entre los valores de las columnas LOWEST_SAL y HIGHEST_SAL de la tabla JOB_GRADES. Por lo tanto, cada empleado puede ser clasificado en función de su salario. Otras condiciones (por ejemplo, <= y >=) se puede utilizar, pero BETWEEN es el más simple.

-- Ejemplo

SELECT e.employee_id, e.last_name, e.salary, j.grade_level
FROM employees e JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal
ORDER BY e.employee_id;

En el ejemplo, se un nonequijoin para evaluar el grado de salario de cada empleado. El salario del empleado debe estar entre cualquier par de los rangos bajo y alto de la tabla JOB_GRADES para que aparezca en la consulta.

Outer Joins o Uniones Externas

-- Ejemplo

SELECT employee_id, department_id, department_name
FROM employees JOIN departments 
USING (department_id)
ORDER BY employee_id;

En un EQUIJOINS o Join de Igualdad, si una fila no satisface la condición del join no aparece en el resultado de la consulta SQL. En el ejemplo, al efectuar un Equijoin entre las tablas EMPLOYEES y DEPARTAMENTS utilizando la columna department_id, sólo se mostrarán los empleados cuyo departamento existe en la tabla DEPARTMENTS. Por lo anterior, el empleado 178 no aparece entre las filas seleccionadas, ya que el departamento Nulo no existe en la tabla DEPARTMENTS. Para que el Join retorne todos los empleados exista o no su departamento en la tabla DEPARTMENTS, se debe efectuar un OUTER JOIN o Uniones Externas.

• En la versión SQL de 1999 la combinación de dos tablas que retornan sólo filas coincidentes se llama INNER JOIN o Unión Interna.
• Un Join entre dos tablas que retorna el resultado del INNER JOIN (filas que coinciden) así como las filas de la tabla a la izquierda que no existen en tabla de la derecha del Join es una Unión
  Externa Izquierda o LEFT OUTER JOIN.
• Una unión entre dos tablas que retorna el resultado de un INNER JOIN así como las filas de la tabla a la derecha que no existen en tabla de la izquierda del Join es una Unión Externa
  Derecha o RIGTH OUTER JOIN.
• Una unión entre dos tablas que retorna el resultado de un INNER JOIN así como los de la Unión Externa izquierda y las filas de la Unión Externa Derecha es una Unión Externa Completa o
  FULL OUTER JOIN.

Left Outer Join

-- Ejemplo

SELECT emp.employee_id, dep.department_id, dep.department_name 
FROM employees emp LEFT OUTER JOIN departments dep
ON(emp.department_id = dep.department_id)
ORDER BY emp.employee_id;

En la sentencia del ejemplo, para poder mostrar todos los empleados de la tabla EMPLOYEES (a la izquierda del Join) exista o no su departamento en la tabla DEPARTMENTS se debe utilizar LEFT OUTER JOIN, de esta forma el empleado con departamento NULO aparece en el resultado final del Join. Si este Join se quisiera construir utilizando cláusula USING sería: SELECT employee_id, department_id, department_name FROM employees LEFT OUTER JOIN departments USING(department_id) ORDER BY employee_id;

Right Outer Join

-- Ejemplo

SELECT emp.employee_id, dep.department_id, dep.department_name 
FROM employees emp RIGHT OUTER JOIN departments dep
ON(emp.department_id = dep.department_id)
ORDER BY emp.employee_id;

En la sentencia del ejemplo, para poder mostrar todos los departamentos de la tabla DEPARTMENTS (a la derecha del Join) exista o no el departamento en la tabla EMPLOYEES se debe utilizar RIGHT OUTER JOIN, por esta razón el empleado 178 (con departamento NULO) no aparece en el resultado final del Join pero si aparecen los departamentos todos los departamentos almacenados en la tabla DEPARTMENTS tengan o o no empleados asignados. Si este Join se quisiera construir utilizando cláusula USING sería: SELECT employee_id, department_id, department_name FROM employees RIGHT OUTER JOIN departments USING(department_id) ORDER BY employee_id;

Full Outer Join

-- Ejemplo

SELECT emp.employee_id, dep.department_id, dep.department_name 
FROM employees emp FULL OUTER JOIN departments dep
ON(emp.department_id = dep.department_id)
ORDER BY emp.employee_id;

En el ejemplo, para poder mostrar todas las filas de ambas tablas existan o no coincidencias se debe usar FULL OUTER JOIN. De esta forma, la sentencia recupera todas las filas de la tabla EMPLOYEES, incluso si no hay ninguna coincidencia en la tabla DEPARTMENTS. También recupera todas las filas de la tabla de DEPARTMENTS, incluso si no hay ninguna coincidencia en la tabla EMPLOYEES. Si este Join se quisiera construir utilizando cláusula USING sería: SELECT employee_id, department_id, department_name FROM employees FULL OUTER JOIN departments USING(department_id) ORDER BY employee_id;

Guia:

/* 1.- La empresa Norteamericana "ACME" lo ha contratado a Ud. para desarrollar su nuevo Sistema de Personal.  Como primera etapa se requiere que, 
a partir del  Modelo que se presenta, pueda satisfacer los requerimientos de información que se solicitan a continuación:
  a) Se desea contar con un reporte actualizado del departamento en el que trabaja cada empleado. Para ello, debe mostrar el nombre completo del 
  empleado y el nombre del departamento en el que trabaja. La información se debe mostrar como en el ejemplo: */
SELECT 'El empleado ' || e.FIRST_NAME || ' ' || e.LAST_NAME || ' trabaja en el departamento ' || d.DEPARTMENT_NAME
FROM EMPLOYEES e JOIN DEPARTMENTS d USING(DEPARTMENT_ID);

  /* b) A los empleados cuyo salario es menor a los $2700 se les asignará un bono especial de acuerdo al trabajo que desempeñen. Para ello se 
  requiere saber la identificación del empleado, su salario actual, identificación del trabajo y nombre del trabajo que desempeña. La información 
  se debe mostrar para todos los empleados que se cumplan con la condición según el formato del ejemplo y ordenada en forma ascendente por salario: */
SELECT e.EMPLOYEE_ID empleado, e.SALARY salario, JOB_ID "ID TRABAJO",
       j.JOB_TITLE "DESCRIPCION TRABAJO"
FROM EMPLOYEES e JOIN JOBS j USING(JOB_ID)
WHERE e.SALARY < 2700
ORDER BY e.SALARY;

 /* c) Por cada departamento que posee empleados, se requiere saber el nombre del departamento y el total de empleados que trabajan en cada 
 uno de ellos. La información se debe mostrar como se visualiza en el ejemplo y en orden alfabético por nombre de departamento: */
SELECT d.DEPARTMENT_NAME departamento, COUNT(e.EMPLOYEE_ID) "TOTAL EMPLEADOS"
FROM DEPARTMENTS d JOIN EMPLOYEES e USING(DEPARTMENT_ID)
GROUP BY d.DEPARTMENT_NAME
ORDER BY d.DEPARTMENT_NAME;

 /* d) Por cada departamento en los que trabajan empleados se requiere saber el nombre del departamento y el salario máximo que se paga en 
 cada uno de ellos. Se deben considerar sólo aquellos departamentos cuyo salario máximo sea mayor  o  igual a  6000 y menor o igual a 20000. 
 La información se debe mostrar como se visualiza en el ejemplo y ordenada en forma descendente por salario máximo: */
SELECT d.DEPARTMENT_NAME departamento, MAX(e.SALARY) "SALARIO MAXIMO"
FROM DEPARTMENTS d JOIN EMPLOYEES e USING(DEPARTMENT_ID)
GROUP BY d.DEPARTMENT_NAME
HAVING MAX(e.SALARY) BETWEEN 6000 AND 20000
ORDER BY "SALARIO MAXIMO" DESC;

 /* e) La Gerencia desea premiar a los empleados que han optado por trabajar en la ciudad de Toronto. Para ello, el gerente desea saber el 
 nombre completo del empleado, el trabajo que desempeña y el departamento en el que trabaja. La información se debe mostrar en el formato 
 del ejemplo: */
SELECT e.FIRST_NAME || ' ' || e.LAST_NAME "NOMBRE EMPLEADO", j.JOB_TITLE trabajo, d.DEPARTMENT_NAME departamento
FROM DEPARTMENTS d JOIN EMPLOYEES e USING(DEPARTMENT_ID)
     JOIN JOBS j USING(JOB_ID);

  /* f) Se efectuará una redistribución del personal que trabaja en la empresa con el objetivo de potenciar los departamentos que en la 
  actualidad NO poseen empleados trabajando en ellos. Para ello, se desea saber qué departamentos se encuentran en esta situación. La 
  información se debe visualizar como se muestra en el ejemplo y ordenada en forma ascendente por identificación del departamento: */
SELECT d.DEPARTMENT_ID "ID DEPARTAMENTO", d.DEPARTMENT_NAME "NOMBRE DEPARTAMENTO", 
       COUNT(e.DEPARTMENT_ID) "TOTAL EMPLEADOS"
FROM DEPARTMENTS d LEFT OUTER JOIN EMPLOYEES e ON (d.DEPARTMENT_ID = e.DEPARTMENT_ID)
GROUP BY d.DEPARTMENT_ID ,d.DEPARTMENT_NAME
HAVING COUNT(e.DEPARTMENT_ID) = 0
ORDER BY d.DEPARTMENT_ID;

/* 2 a) Por motivos de salud, los dueños de la empresa se ausentarán del país por  6 meses aproximadamente. Por esta razón han dejado a 
cargo del negocio a una persona de su confianza la que necesita saber  cuál es el trabajo que desempeña cada empleado. Para ello, la 
información que se requiere es el nombre y apellido del empleado (concatenado) y el nombre del trabajo que desempeña en el formato que 
se muestra en el ejemplo y ordenada en forma ascendente por apellido: */
SELECT 'El empleado ' || PNOMBRE || ' ' || APPATERNO || ' se desempeña como ' || DESC_TIPO_EMPLEADO
FROM EMPLEADO e NATURAL JOIN TIPO_EMPLEADO te
ORDER BY APPATERNO;

/* 2 b) El nuevo encargado de la empresa desea saber todas las ventas que se han efectuado a la fecha. Para ello saber el nombre y apellido 
del empleado (concatenado) que efectuó la venta, el número de la boleta, fecha de la boleta y el monto total de la boleta. La información 
se debe mostrar según el formato del ejemplo y ordenada por número de boleta en forma ascendente:*/
SELECT PNOMBRE || ' ' || APPATERNO "NOMBRE EMPLEADO", NRO_BOLETA "NUMERO BOLETA", FECHA_BOLETA "FECHA BOLETA",
       TO_CHAR(MONTO_TOTAL,'$999,999') "MONTO VENTA"
FROM EMPLEADO e NATURAL JOIN VENTAS v
ORDER BY NRO_BOLETA;

/* 2 c) Se ha detectado que en el mes de Marzo los cálculos de comisiones por venta se efectuaron en forma errónea, calculándose menos del 
valor que les corresponde. Por esta razón, se debe enviar a contabilidad un reporte de las comisiones que se pagaron durante el mes de Marzo 
del 2014. La información que se requiere es el nombre y apellido del empleado (concatenados), número de la boleta, monto total de la boleta 
y el valor de la comisión asociado a la boleta. La información se debe mostrar en el formato que se presenta en el ejemplo y ordena en forma 
ascendente por apellido y monto total de la boleta: */
SELECT PNOMBRE || ' ' || APPATERNO "NOMBRE EMPLEADO", NRO_BOLETA "NUMERO BOLETA", TO_CHAR(MONTO_TOTAL,'$999,999') "MONTO BOLETA",
       TO_CHAR(MONTO_COMISION,'$99,999') "COMISION POR VENTA"
FROM EMPLEADO e NATURAL JOIN VENTAS v
     NATURAL JOIN COMISION_VENTAS cv
WHERE FECHA_BOLETA >= '01032014'
AND FECHA_BOLETA <= '31032014'
ORDER BY APPATERNO, MONTO_TOTAL;

/* 2 d) Se desea saber cuál es el monto total de las ventas y comisiones de los vendedores que efectuaron alguna venta en el mes de Marzo 
del 2014. La información que se requiere es el nombre y apellido del empleado (concatenados), monto total de sus ventas y monto de sus 
comisiones de acuerdo al formato que se muestra en el ejemplo y ordenada por el total de comisión del mes: */
SELECT PNOMBRE || ' ' || APPATERNO "NOMBRE EMPLEADO", TO_CHAR(SUM(MONTO_TOTAL),'$999,999') "TOTAL VENTAS DEL MES", 
       TO_CHAR(SUM(MONTO_COMISION),'$99,999') "TOTAL COMISION DEL MES"
FROM EMPLEADO e NATURAL JOIN VENTAS v
     NATURAL JOIN COMISION_VENTAS cv
WHERE FECHA_BOLETA >= '01032014'
AND FECHA_BOLETA <= '31032014'
GROUP BY PNOMBRE, APPATERNO
ORDER BY SUM(MONTO_COMISION);

/* 2 e) Con el objetivo de garantizar una atención de excelencia a sus clientes, la empresa ha decido que es imprescindible renovar su planta 
de vendedores. Para ello, una de las consideraciones para decidir los vendedores serán reemplazados tiene relación con el total de ventas que 
han efectuado hasta la fecha (cuántas).  Para ello, se deben  considerar a TODOS lo vendedores hayan efectuado o no ventas ya que los primeros 
candidatos a ser reemplazados son los que hasta le fecha no han efectuado ninguna venta. La información que se requiere es el nombre y apellido 
del empleado (concatenados) y el total de ventas efectuadas hasta la fecha según el formato que se muestra en el ejemplo y ordenada por apellido: */
SELECT e.PNOMBRE || ' ' || e.APPATERNO "NOMBRE EMPLEADO", NVL(COUNT(v.NRO_BOLETA),0) "TOTAL VENTAS"
FROM EMPLEADO e LEFT OUTER JOIN VENTAS v ON(e.ID_EMPLEADO = v.ID_EMPLEADO)
GROUP BY e.PNOMBRE, e.APPATERNO
ORDER BY e.APPATERNO;

/* 2 f) A fin de mes se les deberá notificar el despido a los empleados que hasta la fecha no han efectuado ninguna venta. Debido a esto, se 
requiere tener un listado con los nombres de aquellos empleados que están en esa condición. La información que se requiere es el nombre del 
empleado (nombre y apellido) y la fecha en la cuál fue contratado y se debe mostrar en el formato del ejemplo: */
SELECT 'El empleado ' || e.PNOMBRE || ' ' || e.APPATERNO || ' contratado el ' || e.FECHA_CONTRATO  || 
       ' no ha efectuado ventas hasta la fecha' "PERSONAL SIN VENTAS"
FROM EMPLEADO e LEFT OUTER JOIN VENTAS v ON(e.ID_EMPLEADO = v.ID_EMPLEADO)
GROUP BY e.PNOMBRE, e.APPATERNO, e.FECHA_CONTRATO
HAVING COUNT(v.NRO_BOLETA) = 0;

Experiencia de Aprendizaje StringDecisionIteracion

Vamos a desarrollar una aplicación que ofrezca un menú de opciones al usuario, cada opción será implementada por medio de un método propio para ser llamada desde el Main(). Por tanto lo primero es declarar una variable static de tipo string fuera del Main() llamada “inputTexto” e inicializarla vacía. De esta forma esta variable podrá ser vista por todos los métodos que implemente fuera del Main().

En el Main() solicitar al usuario que ingrese una frase por teclado, leer la frase y almacenarla en la variable “inputTexto”.Validar que se haya ingresado texto a la variable, si esto no sucede se debe entregar un mensaje de error al usuario.

Cuando está seguro de que se ha ingresado un texto, declare una variable llamada “opcion” de tipo entera para guardar la opción seleccionada del menú y dentro de un ciclo iterativo presentar un menú al usuario que ofrezca las siguientes opciones (implementar MostrarMenu() como método que retorne un entero con el número de la opción seleccionada):

1. Mostrar la frase en MAYÚSCULA y  minúscula.
2. Mostrar la cantidad de caracteres de la frase.
3. Mostrar unaSubcadena.
4. Reemplazar un texto.
5. Salir.

El ciclo iterativo debe repetirse mientras el usuario no digite la opción 5.

Dentro del ciclo iterativo verifique por medio de un switch o if anidados la opción seleccionada y realice los llamados a los métodos según corresponda.

Si la opción es 5, terminar la ejecución del programa.

Crear un método Pausa que sirva para que la consola no se cierre y se pueda ver el resultado de cada opción, además una vez que se presione cualquier tecla limpie la pantalla para volver a visualizar el menú.

• Opción 1: Crear el método MayusculaMinuscula sin retorno y que muestre la frase ingresada en ambos formatos. *Ayuda: usar métodos ToUpper(),ToLower()

• Opción 2: Crear el método CantidadDeCaracteres que cuente los caracteres de la frase (considerar los espacios en blanco) y muestre el valor por pantalla. *Ayuda: usar métodos Length

• Opción 3: Crear el método MostrarSubCadena que dada una posición de inicio y una cantidad de caracteres muestre la subcadena resultante.
  • Se deben declarar dos enteros “star” y “cantidad” inicializados en cero.
  • Declarar un string “varAux” para leer los valores desde teclado.
  • Solicitar al usuario que ingrese la posición de inicio, guardarla en “varAux”, validar que es un número con el métodoTryParse() y utilizar como variable de salida “star”, además por medio de Lengthvalidar que se ingresó un valor no mayor al largo de la frase.
  • Solicitar al usuario que ingrese la cantidad de caracteres, guardarla en “varAux”, validar que es un número con el método TryParse() y utilizar como variable de salida “cantidad”.
  • Una vez realizada todas las validaciones, muestre la SubCadena resultante utilizando el método Substring().

• Opción 4: Crear el método Reemplazo que buscará una palabra ingresada por el usuario en el texto original y la reemplazará por una nueva palabra ingresada por el mismo.
  • Declarar dos string “textOriginal” y “textNuevo” e inicializarlos vacíos.
  • Solicitar al usuario que ingrese texto a reemplazar, asignarlo a “textOriginal” y por medio del método IndexOf() validar que existe en la frase original (si no encuentra coincidencia recuerde que éste método retorna un -1).
  • Solicitar al usuario el nuevo texto, guardarlo en “textNuevo”, realizar el reemplazo en la frase original con el método Replace() y mostrar los resultados por pantalla.

Código: 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace StringDecisionIteracion
{
    class Program
    {
        //declaro variable para guardar frase ingresada
        static String inputTexto = "";

        static void Main(string[] args)
        {
            //solicito y guardo frase
            Console.WriteLine("ingrese una frase");
            inputTexto = Console.ReadLine();

            //valido que frase ingresada no sea en blanco
            if (inputTexto == "")
            {
                Console.WriteLine("error: no ha ingresado texto");
                Console.Read();
            }
            else
            {
                //declaro variable para guardar opcion seleccionada
                int opcion;
                do
                {
                    //muestro frase ingresada 
                    Console.WriteLine("Frase ingresada: " + inputTexto);
                    Console.WriteLine();
                    //muestro menu
                    opcion = mostrarMenu();
                    //evaluo la opcion ingresada
                    switch (opcion)
                    {
                        case 1: mayusculaMinuscula(inputTexto); pausa();
                            break;
                        case 2: cantidadCaracteres(inputTexto); pausa();
                            break;
                        case 3: mostrarSubCadena(inputTexto); pausa();
                            break;
                        case 4: reemplazo(inputTexto); pausa();
                            break;
                        case 5: Console.WriteLine("Saliendo ...");
                            break;
                        default: Console.WriteLine("Opcion no valida"); pausa();
                            break;
                    }
                //si la opcion es 5 termino la aplicacion, de lo contrario repito el ciclo
                } while (opcion != 5);
            }
        }
        
        //metodo para mostrar el menu, retorna opcion seleccionada
        static int mostrarMenu()
        {
            //declaro variable para guardar opcion
            int opcion;

            //muestro menu y guardo opcion
            Console.WriteLine("1.- Mostrar la frase en MAYÚSCULA y  minúscula.");
            Console.WriteLine("2.- Mostrar la cantidad de caracteres de la frase.");
            Console.WriteLine("3.- Mostrar una Subcadena.");
            Console.WriteLine("4.- Reemplazar un texto.");
            Console.WriteLine("5.- Salir.");
            opcion = int.Parse(Console.ReadLine());

            //retorno opcion
            return opcion;
        }

        //metodo para pausar y limpiar pantalla
        static void pausa()
        {
            //pauso la aplicacion hasta que se presione una tecla
            Console.ReadKey();
            //limpio la pantalla
            Console.Clear();
        }

        //metodo para transformar a mayuscula y minuscula
        static void mayusculaMinuscula(String frase)
        {
            //muestro la frase en mayuscula
            Console.WriteLine("Frase en Mayuscula: " + frase.ToUpper());
            //muestro la frase en minuscula
            Console.WriteLine("Frase en Minuscula: " + frase.ToLower());
        }

        //metodo para mostrar cantidad de carcteres
        static void cantidadCaracteres(String frase)
        {
            //muestro cantidad de caracteres de la frase
            Console.WriteLine("Cantidad de caracteres de '{0}' es '{1}'", frase, frase.Length);
        }

        //metodo para mostrar una subcadena
        static void mostrarSubCadena(String frase)
        {
            //declaro variables para inicio de cadena, cantidad caracteres y guardar ingreso de datos
            int star, cantidad = 0;
            String varAux;

            //pido y guardo posicion de inicio
            Console.WriteLine("Ingrese posicion de inicio");
            varAux = Console.ReadLine();

            //valido que valor ingresado sea un numero
            if (int.TryParse(varAux, out star))
            {
                //valido que la posicion de inicio no sea mayor al largo de la frase
                if (star <= frase.Length)
                {
                    //pido y guardo cantidad de caracteres
                    Console.WriteLine("Ingrese cantidad de caracteres");
                    varAux = Console.ReadLine();

                    //valido que valor ingresado sea un numero
                    if (int.TryParse(varAux, out cantidad))
                    {
                        //obtebgo la subcadena y la muestro por pantalla
                        varAux = frase.Substring(star,cantidad);
                        Console.WriteLine("La sub cadena seleccionada es: " + varAux);
                    }
                    else
                    {
                        //si valor ingresado no es numero, muestro error
                        Console.WriteLine("No ha ingresado un numero");
                    }
                }
                else
                {
                    //si posicion de inicio es mayor al largo de la frase, muestro error
                    Console.WriteLine("Valor excede el largo de la frase");
                }
            }
            else
            {
                //si valor ingresado no es numero, muestro error
                Console.WriteLine("No ha ingresado un numero");
            }            
        }

        //metodo para reemplazar texto 
        static void reemplazo(String frase)
        {
            //declaro variables para texto original y texto nuevo
            String textOriginal, textNuevo = "";

            //pido y guardo el texto que se quiere reemplazar
            Console.WriteLine("Ingrese texto a reemplazar");
            textOriginal = Console.ReadLine();

            //valido que texto se encuentre en la frase (si lo encuentra retorna posicion, de lo contrario -1)
            if (frase.IndexOf(textOriginal) >= 0)
            {
                //pido y guardo texto nuevo
                Console.WriteLine("Ingrese el texto nuevo");
                textNuevo = Console.ReadLine();

                //muestro por pantalla la frase con el texto reemplazado
                Console.WriteLine("El nuevo texto es: " + frase.Replace(textOriginal,textNuevo));
            }
            else
            {
                //si texto no se encuentra, muestro mensaje
                Console.WriteLine("Texto no encontrado");
            }

        }
    }
}

Uso de Funciones de Grupo (PL/SQL)

Funciones de Grupos o Agregadas

• AVG: calcula el promedio del valor de n, ignorando los valores Nulos.
• COUNT: cuenta el número de filas, donde expr puede ser el nombre de una columna.
• MAX: obtiene el máximo valor de expr, ignorando los valores Nulos.
• MIN: obtiene el mínimo valor de expr, ignorando los valores Nulos.
• SUM: suma los valores de expr, ignorando los valores Nulos.
• STDDEV: determina la desviación estándar de n, ignorando los valores Nulos.
• VARIANCE:  devuelve la varianza de un conjunto de número.
• La opción DISTINCT hace que la función considere sólo los valores no duplicados.
• La opción ALL (valor por defecto) hace que la función considere todos los valores incluyendo los duplicados.
• El tipo de datos para expr debe ser CHAR, VARCHAR2, NUMBER, DATE.
• Todas las funciones de grupo ignoran los valores Nulos. Para sustituir los valores Nulos se deben utilizar las funciones NVL, NVL2 ó COALESCE.

-- Funciones AVG, SUM, MAX, MIN

SELECT ROUND(AVG(salary)) "Salario Promedio", MAX(salary) "Salario Máximo",
       MIN(salary) "Salario Mínimo", SUM(salary) "Sumatoria de los Salarios"
FROM employees
WHERE job_id LIKE '%REP%';

SELECT MIN(hire_date), MAX(hire_date), MIN(last_name),   MAX(last_name)
FROM employees;

En el primer ejemplo, la sentencia obtiene el salario promedio, el salario mayor, el salario menor y la sumatoria de todos los salarios de los empleados cuya identificación de trabajo contenga la cadena de caracteres REP independiente con la cantidad de caracteres que comience o termine.

En el segundo ejemplo, la sentencia muestra la fecha de contrato menor, la fecha de contrato mayor, el primer apellido y el último apellidos de los empleados.

Uso de Función COUNT

La función COUNT tiene tres formatos:

• COUNT(*): retorna el número de filas que satisfacen el criterio de la sentencia SELECT incluyendo filas duplicadas y filas que contengan valores Nulos en cualquiera de sus columnas. Si se
  incluye las cláusula WHERE en la sentencia SELECT , COUNT(*) retorna el número de filas que satisfacen o cumplen con la condición en la cláusula WHERE.
• COUNT(expr): retorna el número de filas que poseen valores NO Nulos en la columna identificada por expr.
• COUNT(DISTINCT expr): retorna el número de filas que contengan valores diferentes No Nulos en la columna identificada por expr.

-- Funcion COUNT

SELECT COUNT(*) "Total Empleados", COUNT(commission_pct) "Empleados con Comisión"
FROM employees
WHERE department_id = 50;

SELECT COUNT(manager_id) "Empleados que poseen Jefe", 
       COUNT(DISTINCT department_id) "Deptos. Diferentes"
FROM employees;

En el primer ejemplo, la sentencia retorna el total de empleados o número de filas que pertenecen al departamento 50. Además obtiene el número de filas que poseen un valor NO Nulo en la columna commission_pct (o que poseen porcentaje de comisión) para los empleados que trabajan en el departamento 50.

En el segundo ejemplo, la sentencia retorna la número de filas que poseen un valor NO Nulo en la columna manager, es decir los empleados que poseen jefe. Además muestra cuántos departamentos diferentes existen en la tabla EMPLOYEES.

Uso de Función de Grupo con valores Nulos

Las funciones de grupo ignoran los valores nulos en las columnas excepto la función COUNT en su formato COUNT(*). Sin embargo, la función NVL fuerza a las funciones de grupo a incluir los valores nulos

-- Funciones con valores nulos

SELECT AVG(commission_pct) "PROMEDIO SIN CONSIDERAR FILAS CON NULOS"
FROM employees;

SELECT AVG(NVL(commission_pct,0)) "PROMEDIO CONSIDERANDO NULOS"
FROM employees;

En la primera sentencia, la función de AVG obtiene la comisión promedio pero considerando solo las filas que poseen valor en la columna commission_pct. Para que sean consideras las 107 filas de la tabla EMPLOYEES se debe usar la función NVL. En la segunda sentencia, la función NVL reemplazará con cero cuando la columna commission_pct tenga un valor Nulo. Una vez efectuado esto, la función AVG obtiene el promedio pero ahora considerando todas las filas de la tabla EMPLOYEES.

Creando Grupos de Datos

Se puede usar la cláusula GROUP BY para dividir las filas de la tabla en grupos más pequeños y obtener valores por cada grupo utilizando las funciones de grupos.

Si en una cláusula SELECT se incluye una función de grupo y además se selecciona una columna individual, ésta se DEBE incluir en la cláusula GROUP BY. Si no se incluye dicha columna en la cláusula GROUP BY se produce el error ORA-00937.

Usando la cláusula WHERE se pueden excluir filas antes de que se dividan en grupos.

Todas las columnas que no están incluidas en las funciones de grupos se deben incluir en la cláusula GROUP BY.

No se pueden utilizar columnas alias en la cláusula GROUP BY.

Las columnas de la cláusula GROUP BY no es necesario que estén en la cláusula SELECT, sin embargo esto hace que la información que se entrega no sea muy clara.

-- Ejemplo

SELECT department_id departamento,  
       ROUND(AVG(salary)) "SALARIO_PROMEDIO X DEPTO"
FROM employees
GROUP BY department_id
ORDER BY "SALARIO_PROMEDIO X DEPTO";

En la sentencia del ejemplo, se muestra por cada departamentos el salario promedio. La información se visualiza ordenada en forma ascendente por el salario promedio. A diferencia de la cláusula GROUP BY, en la cláusula ORDER BY puede ir el alias asignado a la columna de salida o puede ir la expresión ROUND(AVG)salary)).

-- Ejemplo

SELECT job_id "TRABAJO", ROUND(AVG(salary)) "SALARIO PROMEDIO X TRABAJO"
FROM employees
WHERE job_id LIKE '%A%'
GROUP BY job_id
ORDER BY job_id;

En la sentencia del ejemplo, se muestra la identificación del trabajo y su salario promedio. Esta información se muestra por cada trabajo que contenga la letra A.

Creando Grupos de Datos por varias Columnas

Las funciones de grupos pueden tratar a toda la tabla como un solo gran grupo, pero existe el caso en que se hace necesario “dividir” la información existente en la tabla en grupos más pequeños utilizando la cláusula GROUP BY.

Las filas de la tabla EMPLOYEES primero se agrupan por departamento, después por cada departamento se agrupan por trabajo para finalmente sumar los salarios de cada trabajo existentes en cada departamento.

-- Ejemplo

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

En la sentencia del ejemplo, en primer lugar , las filas se agrupan por identificación de departamento. En segundo lugar, las filas se agrupan por identificación de trabajo en los grupos de departamento. La cláusula ORDER BY ordena los resultados por identificación de departamento y por cada departamento ordena por identificación de trabajo. La función SUM se aplica a la columna de salario para todos los trabajos en cada grupo de departamentos

-- Sentencia Errónea

SELECT department_id, COUNT(last_name)
FROM employees;

-- Sentencia Correcta

SELECT department_id, COUNT(last_name)
FROM employees
GROUP BY department_id;

Cualquier columna o expresión especificada en la cláusula SELECT que no es una función de grupo debe estar en la cláusula GROUP BY, de lo contrario se producirá el error oracle ORA-00937 como se muestra en a sentencia errónea. La solución para corregir este error, es que la columna department_id se debe incorporar a la cláusula GROUP BY.

-- Sentencia Errónea

SELECT department_id, MAX(salary)
FROM employees
WHERE MAX(salary) > 8000
GROUP BY department_id;

En la cláusula WHERE NO se puede utilizar funciones de grupos para restringir los grupos de filas a mostrar. En el ejemplo, se desea mostrar solo la información de los departamentos con salario máximo mayor a 8000, pero como no se puede utilizar funciones de grupo en la cláusula WHERE se genera el error oracle ORA-00934.

Restricciones de Resultados del Grupo

Para restringir las filas que se desean seleccionar se utiliza la cláusula WHERE, pero para restringir grupos de filas basado en el resultado de una función de grupo se debe utilizar la cláusula HAVING.

Cuando se usa la cláusula HAVING, el servidor Oracle restringe los grupos en el siguiente orden:
1.- Las filas son agrupadas
2.- La función de grupo es aplicada al grupo.
3.- Se muestran los grupos que coinciden con el criterio de la cláusula HAVING.

Los grupos son formados y las funciones de grupos con calculadas antes de que la cláusula HAVING se aplique a los grupos.

Para encontrar el máximo salario en cada departamento pero que sea mayor a 8000 se debe hacer lo siguiente:
Encontrar el salario máximo por cada departamento agrupando por número de departamento.
Restringir los grupos para los departamentos con un máximo salario mayor a 8000.

-- Ejemplo

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 8000
ORDER BY department_id;

La sentencia del ejemplo muestra los departamentos y los salarios máximos de aquellos que cumplan con la condición de grupo que indica que el salario máximo debe ser mayor a 80000 para que el grupo sea retornado por la sentencia SELECT.

-- Ejemplo

SELECT department_id, ROUND(AVG(salary))
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 1000
ORDER BY ROUND(AVG(salary)) DESC;

En el ejemplo, la sentencia muestra el departamento y el salario promedio (redondeado) de los departamentos que posean con un salario máximo mayor a 10000.

-- Ejemplo

SELECT department_id, MIN(salary), MAX(salary)
FROM employees
HAVING MIN(salary) > 2500
AND MAX(salary) > 5000
GROUP BY department_id;

SELECT department_id, MIN(salary), MAX(salary)
FROM employees
WHERE department_id > 20
HAVING MIN(salary) > 2500
AND MAX(salary) > 5000
GROUP BY department_id;

En la primera sentencia se muestran los departamentos, sus salarios mínimos y máximos de aquellos que posean un salario mínimo mayor a 2500 y un salario máximo mayor a 5000.

En la segunda sentencia se muestran los departamentos, sus salarios mínimos y máximos de aquellos que cumplan con las siguientes tres condiciones:

• La identificación del departamento debe ser mayor a 20.
• Deben poseer un salario mínimo mayor a 2500
• y deben poseer un salario máximo mayor a 5000,

En el resultado por lo tanto no se muestra el departamento 20 y tampoco el departamento con identificación NULL.

-- Ejemplo

SELECT ROUND(MAX(AVG(salary))) "MÁXIMO SALARIO PROMEDIO"
FROM employees
GROUP BY department_id;

En la sentencia del ejemplo, primero se obtiene el salario por cada departamento, después entre todos los salarios promedios de obtiene el máximo para finalmente mostrar ese valor redondeado.

Guía:

/* 1.- El departamento de Finanzas desea conocer cuál es la realidad de la empresa en relación a los salarios que hasta la fecha perciben los 
empleados. Para ello, se desea que Ud. genere un informe con el salario mínimo, salario máximo, valor total de los salarios y salario promedio 
entre todos los empleados. La información se debe mostrar en el formato como se muestra en el ejemplo y con los valores redondeados: */
SELECT ROUND(MIN(SALARY)) "SALARIO MINIMO", ROUND(MAX(SALARY)) "SALARIO MAXIMO",
       ROUND(SUM(SALARY)) "VALOR TOTAL SALARIOS", ROUND(AVG(SALARY)) "SALARIO PROMEDIO"
FROM EMPLOYEES;

/* 2.- Se desea evaluar el aumentar los salarios de acuerdo a los trabajos que se desempeñan en la actualidad en la empresa. Por esta razón, se 
requiere que Ud. genere un reporte que por cada trabajo se muestre el salario máximo, salario mínimo, valor total de los salarios y salario 
promedio. La información se debe mostrar en el formato como se muestra en el ejemplo, con los valores redondeados y ordenada en forma ascendente 
por trabajo: */
SELECT JOB_ID trabajo, ROUND(MAX(SALARY)) "SALARIO MAXIMO", ROUND(MIN(SALARY)) "SALARIO MINIMO", 
       ROUND(SUM(SALARY)) "VALOR TOTAL SALARIOS", ROUND(AVG(SALARY)) "SALARIO PROMEDIO"
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY JOB_ID;

/* 3.- La cadena de supermercados SANTA CHABELA posee un Sistema Informático que gestiona la información de  sus  empleados. Debido a que aún 
posee algunas falencias en la entrega de información, se requiere que Ud. genere una serie de informes para satisfacer los requerimientos de 
información planteados en cada caso. La información que se debe generar en el formato de ejemplo de cada requerimiento:
  a) Total de empleados por cada trabajo: */
SELECT JOB_ID trabajo, COUNT(JOB_ID) "TOTAL EMPLEADOS" 
FROM EMPLOYEES
GROUP BY JOB_ID; 
  
  /* b)  Total de empleados que tiene a su cargo cada jefe. Mostrar la información ordenado en forma descendente por el total de empleados y 
  ascendente por jefe: */
SELECT MANAGER_ID jefe, COUNT(EMPLOYEE_ID) "TOTAL DE EMPLEADOS A SU CARGO"
FROM EMPLOYEES
GROUP BY MANAGER_ID
ORDER BY "TOTAL DE EMPLEADOS A SU CARGO" DESC, MANAGER_ID;

  /* c)  Cuadro Resumen del total de empleados y total de salarios por cada departamento. El valor del salario total por departamento debe 
  aparecer con el símbolo punto como separador de miles: */
SELECT NVL(TO_CHAR(DEPARTMENT_ID),' ') departamento, COUNT(EMPLOYEE_ID) "TOTAL DE EMPLEADOS", 
       TO_CHAR(SUM(SALARY),'$999G999') "SALARIO TOTAL X DEPTO" 
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID;

  /* d) Diferencia que existe entre el salario mayor y menor que perciben los empleados: */
SELECT TO_CHAR(MAX(SALARY) - MIN(SALARY),'$99,999') diferencia 
FROM EMPLOYEES;

  /* e) Cuántos jefes diferentes existen en la empresa: */
SELECT COUNT(DISTINCT(NVL(TO_CHAR(MANAGER_ID),' '))) "Total de Jefes" FROM EMPLOYEES;

  /* f) Departamentos en los que trabajan más de 5 empleados: */
SELECT DEPARTMENT_ID DEPARTAMENTO, COUNT(EMPLOYEE_ID) "TOTAL DE EMPLEADOS" 
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(EMPLOYEE_ID) > 5;

  /* g)  Por cada trabajo que se realiza en cada departamento mostrar el salario mínimo que se paga para ese trabajo en ese departamento. 
  Excluir los departamentos nulos y los grupos donde el salario mínimo es menor o igual a 6000. Ordenar los datos de salida en forma ascendente  
  por departamento y trabajo. */
SELECT DEPARTMENT_ID departamento, JOB_ID trabajo, MIN(SALARY) "SALARIO MINIMO"
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID, JOB_ID
HAVING MIN(SALARY) > 6000
ORDER BY DEPARTMENT_ID, JOB_ID;

/* 4.- Después de una reunión efectuada entre la Gerencia y el departamento de Informática de la Cadena del Supermercado, Ud. deberá dar solución 
con urgencia a los siguientes requerimientos:
  a) Se han detectado algunas irregularidades en el gasto de pagos de salarios de algunos departamentos versus la cantidad de empleados que 
  trabajan en ellos. Por esta razón, se debe emitir un informe con los departamentos en donde el gasto total por pago de salarios está entre los 
  $15000 y los $30000. La información se debe mostrar según el ejemplo y ordenada por el valor total del salario por departamento en forma ascendente: */
SELECT DEPARTMENT_ID departamento, COUNT(EMPLOYEE_ID) "TOTAL EMPLEADOS",
       TO_CHAR(SUM(SALARY),'$99,999') "TOTAL SALARIO X DEPTO"
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING SUM(SALARY) BETWEEN 15000 AND 30000
ORDER BY SUM(SALARY);

  /* b) Se desea evaluar la posibilidad de pagarle a cada jefe una comisión basada en el salario máximo del empleado del cual es jefe y el número total 
  de empleados a su cargo. Así por ejemplo si el jefe 100 posee 5 empleados a su cargo la comisión que le corresponderá será el 5% del salario máximo 
  entre sus 5 empleados, si el jefe 201 posee 3 empleados su comisión corresponderá al 3% del salario máximo entre los 3 empleados a su cargo, etc. 
  La información que se solicitada es la que se muestra en el ejemplo y ordena en forma ascendente por jefe: */
SELECT NVL(TO_CHAR(MANAGER_ID),' ') jefe, COUNT(EMPLOYEE_ID) "TOTAL DE EMPLEADOS A CARGO", 
       MAX(SALARY) "SALARIO MAXIMO", ROUND(MAX(SALARY) * COUNT(EMPLOYEE_ID) / 100) comision
FROM EMPLOYEES
GROUP BY MANAGER_ID
ORDER BY MANAGER_ID;

  /* c) Se está efectuando una auditoría por irregularidades de salarios que se están pagando en los departamentos 20, 50, 80 y 90. Por lo tanto 
  los auditores requieren de un informe que muestre el valor total de salario que se paga por cada trabajo en cada uno de estos departamentos. 
  La información que se requiere es la que se muestra en el ejemplo en el formato que se muestra: */
SELECT JOB_ID trabajo, 
       NVL(TO_CHAR(CASE DEPARTMENT_ID WHEN 20 THEN SUM(SALARY) END,'$999,999'),' ') depto_20,
       NVL(TO_CHAR(CASE DEPARTMENT_ID WHEN 50 THEN SUM(SALARY) END,'$999,999'),' ') depto_50,
       NVL(TO_CHAR(CASE DEPARTMENT_ID WHEN 80 THEN SUM(SALARY) END,'$999,999'),' ') depto_80,
       NVL(TO_CHAR(CASE DEPARTMENT_ID WHEN 90 THEN SUM(SALARY) END,'$999,999'),' ') depto_90
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (20,50,80,90)
GROUP BY JOB_ID,DEPARTMENT_ID;

Uso de funciones de una fila (PL/SQL)

Funciones de Caracteres

Funciones de conversión del texto a mayúsculas y minúsculas:

• LOWER: Convierte el texto a minúsculas
• UPPER: Convierte el texto a mayúsculas
• INITCAP: Coloca la primera letra de cada palabra en mayúscula

Funciones de reemplazo o manipulación de caracteres:

• CONCAT: concatena el valor del primer caracter con el valor del segundo caracter. Equivalente al operador de concatenación ||.
• SUBSTR: obtiene los n siguientes caracteres de la columna o texto a partir de la posición m. Si no se indica n, se recuperan los caracteres desde la posición m hasta el final.
• LENGTH: obtiene el número de caracteres o largo de la expresión.
• RTRIM–LTRIM: Elimina los espacios en blanco a la derecha (RTRIM) ó a la izquierda (LTRIM) de la columna o expresión.
• REPLACE: busca el texto especificado en la columna o expresión y lo cambia por el texto indicado como reemplazo
• TRIM: Elimina los espacios en blanco a la izquierda y a la derecha del texto o columna.
• LPAD-RPAD: rellena el texto a la izquierda (LPAD) ó a la derecha (RPAD) n posiciones con el caracter indicado.
• INSTR: obtiene la posición en la se encuentra el texto buscado en la columna o expresión. Opcionalmente se puede indicar la posición inicial (m) desde donde se desea comenzar la búsqueda y la ocurrencia o número de posición (n) del texto buscado. Por defecto ambas posiciones es 1.
• TRIM(‘caracter’ FROM columna|expresión): Elimina el carácter especificado de la derecha e izquierda de la columna o expresión

-- Ejemplo
SELECT last_name, UPPER(last_name), job_id, LOWER(job_id), INITCAP(job_id)
FROM employees;

En la sentencia del ejemplo, la función UPPER muestra el apellido del empleado en mayúscula, la función LOWER muestra la identificación del trabajo en minúscula y la función INITCAP muestra en mayúscula las primeras letras de la identificación del trabajo.

-- Ejemplo
SELECT last_name apellido, CONCAT('Su salario es ',salary),
       SUBSTR(last_name,2,3), LENGTH(last_name), INSTR(last_name,'a')
FROM employees;

En el ejemplo, la función CONCAT muestra el texto Su salario es unido al valor del salario, SUBSRT a partir de la segunda letra del
apellido muestra tres caracteres del apellido, LENGTH muestra el largo del apellido e INSTR muestra la posición en la se encuentra la
primera letra a en el apellido primera letra a en el apellido primera letra a en el apellido de cada empleado.

-- Ejemplo
SELECT last_name "Apellido", INSTR(last_name,'e',1,2) "Resultado INSTR",
       SUBSTR(last_name,-2,2) "Resultado SUBSTR",
       TRIM('B' FROM last_name) "Resultado 1er. TRIM",
       salary, TRIM(1 FROM salary) "Resultado 2do. TRIM"
FROM employees
WHERE salary between 9500 AND 10000
ORDER BY last_name;
    

En el ejemplo, la función INSTR muestra la posición donde se encuentra la segunda letra “e” en el apellido (la búsqueda comienza desde la posición 1), la función SUBSTR muestra desde la penúltima posición del apellido dos caracteres, la primera función TRIM muestra el apellido del empleado eliminando desde la izquierda y la derecha la letra B y la segunda función TRIM muestra el salario sin el número 1 en la derecha y la izquierda. La información se visualiza para los empleados cuyo salario esté entre los 9500 y los 10000 y ordenada en forma ascendente por apellido.

-- Ejemplo
SELECT last_name apellido, REPLACE(last_name,'A','Hola'),
       salary salario, LPAD(salary,10,'*'), RPAD(salary,10,'*')
FROM employees
ORDER BY last_name;
    

En la sentencia, la función INSTR muestra la posición donde se encuentra la segunda letra a en el apellido (la búsqueda comienza desde la posición 1) y la función SUBSTR muestre desde la penúltima posición del apellido dos caracteres.

Funciones de Números

• ROUND: redondea la columna, expresión o valor a n posiciones decimales. Si no se especifica n o su valor es cero, el valor se redondea al valor entero. Si n es negativo, los números a la izquierda del punto decimal se redondean a decenas, centenas etc.
• TRUNC: Trunca la columna, expresión o valor a n posiciones decimales. Si no se especifica n decimales el valor es 0, por lo tanto se trunca el valor sólo en su parte entera. Por defecto en cero. Si n es negativo trunca hacia la izquierda del punto decimal (coloca cero).
• MOD: Devuelve el resto que resulta de dividir m por n.

-- Ejemplo
SELECT ROUND(1234.5678,2), ROUND(1234.5678), ROUND(1235.5678,-1),
       TRUNC(1234.5678,2), TRUNC(1234.5678), TRUNC(1234.5678,-2)
FROM DUAL;
    

En el ejemplo, las funciones ROUND y TRUNC se utilizan para redondear y truncar el valor 1234.5678 de diferentes formas (recordar que en oracle el punto corresponde a decimales). La primera función ROUND redondea el valor en dos decimales, la segunda función ROUND redondea al valor entero (sin decimales, esto es similar a ROUND(1234.5678,0), la tercera función ROUND redondeado el valor en su parte entera a la décima más cercana a 35, la primera función TRUNC muestra el valor truncado a dos decimales, la segunda función TRUNC muestra el valor truncado solo en su valor entero (esto es similar a TRUNC(1234.5678,0) y la última función TRUNC muestra el valor truncado en su parte entera y reemplazada los dos últimos números por ceros.

-- Ejemplo
SELECT last_name, salary, MOD(salary,5000)
FROM employees
WHERE job_id = 'ST_MAN';

En la sentencia del ejemplo, se muestra el apellido, salario y el resto de la división del salario por 5000 de los empleados que poseen el trabajo ST_MAN.

Funciones de Fecha

La Base de Datos Oracle almacena las fechas en un formato numérico interno: siglo, año, mes, día, horas, minutos y segundos.
El formato por defecto de visualización de las fechas es :DD-MON-RR, donde DD corresponde al día, MON al mes en 3 letras y RR al año en 2 dígitos
según el siglo.
Cuando se desea generar una condición por una fecha en particular, ésta debe ir entre comillas simples.

• SYSDATE: retorna la fecha y hora actual de la Base de Datos
• MONTHS_BETWEEN: obtiene la diferencia en meses entre las dos fechas. El resultado puede ser positivo o negativo. Si fecha1 es posterior a fecha2, el resultado es positivo, si fecha1 es anterior a fecha2, el resultado es negativo. La parte no entera del resultado representa una porción de la mes.
• ADD_MONTHS: añade a la fecha el número de meses indicado por n. El valor de n debe ser un número entero y puede ser negativo.
• NEXT_DAY: retorna la fecha del día de la semana del argumento busca y que es posterior a la fecha entregada. El día puede ser el nombre del día (inglés o español según como esté configurada la Base de Datos) ó el número del día de la semana 1=Lunes, 2=Martes etc.
• LAST_DAY: obtiene el último día del mes de la fecha especificada.
• ROUND: redondea la fecha al formato indicado. El formato puede ser:
  - YEAR: redondea la fecha al año.
  - MONTH: redondea la fecha al mes.
• TRUNC: trunca la fecha al formato indicado. El formato es el mismo al usado por ROUND.

-- Ejemplo
            SELECT MONTHS_BETWEEN('01/ENE/2014','01/OCT/2013') "MONTHS_BETWEEN",
                   ADD_MONTHS('20/ENE/2014',6) "ADD_MONTHS",
                   NEXT_DAY('11/MAR/2014','DOMINGO') "NEXT_DAY",
                   LAST_DAY('01/FEB/2014') "LAST_DAY"
            FROM dual;
        

En el primer ejemplo, la función MONTHS_BETWEEN muestra cuántos meses existen entre el 01 de enero del 2014 y el 01 de octubre del 2013 (dependiendo de las fechas comparadas, la función puede retornar un valor entero o con decimales), la función, la función ADD_MONTHS muestra la fecha que corresponde al sumar 6 meses a la fecha 20 de enero del 2014, la función NEXT_DAY muestra la fecha que corresponde al día domingo posterior al 11 de marzo del 2014 y la función LAST_DAY muestra el último día de febrero del 2014.

-- Ejemplo
    SELECT ROUND(SYSDATE,'MONTH'), ROUND(SYSDATE,'YEAR'),
           TRUNC(SYSDATE,'MONTH'), TRUNC(SYSDATE,'YEAR')
    FROM dual;
    

En el segundo ejemplo, al utilizar las funciones ROUND y TRUNC con fechas el resultado estará basado en redondear las fechas al mes o al año y mostrar el primer día del mes o del año. La primera función redondea la fecha al mes de octubre del 2014 y como el día es 05 el resultado es 01/10/2014, la segunda función redondea la fecha al año 2014 y como el mes es 10 el resultado es 01/01/2015, la tercera función muestra el primer día del mes de octubre y la última función muestra el primer día del año 2014.

Las fechas en la Base de Datos se almacenan como números, por lo tanto se pueden realizar cálculos usando operadores aritméticos:
Fecha + Número: retorna una fecha. Suma un número de días a la fecha.
Fecha - Número: retorna una fecha. Resta un número de días desde la fecha.
Fecha - Fecha: retorna el número de días. Resta una fecha desde otra

-- Ejemplo
    SELECT last_name, hire_date, ROUND((SYSDATE - hire_date) / 7 ) "SEMANAS CONTRATADO",
           hire_date - 2 "FECHA CONTRATO MENOS 2 DIAS"
    FROM employees
    WHERE department_id = 90;
    

En el ejemplo, la sentencia muestra el apellido del empleado, la fecha de contrato, el número de semanas (entre la fecha actual y la fecha) que lleva contratado y la fecha resultante al restar la fecha de contrato menos 2 días de cada empleado del departamento 90.

Conversión de Tipos de Datos

En algunos casos, el servidor Oracle usa tipos de datos distintos a los que se requieren. Cuando esto sucede, se debe convertir al tipo de datos que se requiere.
La conversión de un tipo de dato a otro puede ser efectuada en forma implícita o automática por el servidor Oracle o en forma explícita por el usuario usando funciones de conversión.

-- Ejemplo
    SELECT employee_id, hire_date
    FROM employees
    WHERE  hire_date > '05/03/2008'
    ORDER BY hire_date;

En el ejemplo, en la expresión hire_date > '05/03/2008' el string es convertido implícitamente a fecha al momento de comparar.

Oracle proporciona tres funciones para convertir en forma explícita un tipo de dato en otro:

• TO_CHAR: Obtiene un texto a partir de un número o fecha. Opcionalmente se puede dar un formato específico de conversión.
• TO_NUMBER: Convierte textos en números, indicándole, si se desea, el formato de salida.
• TO_DATE: Convierte textos en fechas, indicándole, si se desea, el formato de salida.

-- Ejemplo
          TO_CHAR(fecha, ' formato_conversión')
      

Se puede usar la función TO_CHAR para convertir la fecha desde su formato por defecto a un formato especificado por el usuario.

Los principales elementos de formatos de fechas válidos son:

• YYYY: año en formato de 4 dígitos.
• YEAR: año en palabras.
• MM: mes en formato de 2 dígitos.
• MONTH: nombre completo del mes.
• MON: las tres primeras letras del mes.
• DY: día de la semana abreviado en tres letras.
• DAY: día completo de la semana en palabras.
• DD: día del mes en formato de dos dígitos.

Los principales elementos de horas válidos para los formatos de fechas son:

• AM o PM: indicador de meridiano.
• HH: hora del día
• HH12: hora del día 1 a 12
• HH24: hora del día de 0 a 23
• MI: minutos (0-59).
• SS: segundos (0-59).
• / . , : separadores que se ven reflejados en el formato final de la fecha.
• "caracteres_a_visualizar“ : cadena de caracteres a visualizar en el formato final de la fecha.

Los sufijos que se pueden utilizar en los elementos de fechas y horas para modificar la forma de visualización:

• SP: muestra el número en palabras. Ej: para el 04 muestra la palabra cuatro.
• SPTH o THSP: muestra en palabras el número ordinal. Ej: para 04 muestra la palabra cuarto.

-- Ejemplo
    SELECT last_name "Apellido", TO_CHAR(hire_date, 'dd/mm/yyyy') "Formato Fecha 1",
           TO_CHAR(hire_date,'DD Month YYYY') "Formato Fecha 2",
           TO_CHAR(SYSDATE,'dd "de" MONTH "del" yyyy hh24:mi:ss') "Fecha-Hora del Sistema"
    FROM employees;

En el ejemplo, el primer formato de fecha de contrato se muestra en número (año en 4 dígitos) y separado por /. El segundo formato muestra el día y año de contrato en número y el mes en palabras. El tercer formato muestra la fecha y hora del sistema incorporando las palabras de y del (la sentencia fue ejecutada el 09 de Enero del 2014 a las 16:04).

Cuando se desean trabajar los valores numéricos como una cadena de caracteres se deben convertir esos números a un tipo de dato caracter utilizando la función TO_CHAR. Esta función convierte un dato de tipo NUMBER a un dato de tipo

Otros elementos de formatos de números válidos:

• G: devuelve el separador de grupo en la posición especificada. Ejemplo: 9G999 Resultado: 1.234
• D: devuelve el carácter decimal en la posición especificada (defecto es punto). Ejemplo: 9999D99 Resultado: 1234,00
• V: multiplica por 10 n veces (n = número de 9s después V). Ejemplo: 99999V9999 Resultado: 12340000

-- Ejemplo
          SELECT salary, TO_CHAR(salary,'$99,999.00') Formato1,
                 TO_CHAR(salary,'$0099,999.00') Formato2,
                 TO_CHAR(salary,'$99G999D00') Formato3,
                 TO_CHAR(salary,'$99999V000') Formato4
          FROM employees
          WHERE last_name = 'Ernst';
      

En la sentencia del ejemplo, se muestra el salario del empleado Ernest en 4 formatos diferentes. En el formato1 se muestra separado por miles y decimales (en una BD Oracle la coma es el separador de miles y punto de decimales). En el formato2 se antepondrán ceros hasta completar 4 caracteres antes del separador de miles, en el formato3 se mostrará un punto como separador de miles y una coma para los decimales y en formato4 dado que la cantidad de ceros después de V son tres, se muestra el valor del salario multiplicado por 1000.

Funciones Anidadas

• Las funciones que operan sobre un fila se pueden anidar sin límites.
• Se evalúan desde el nivel más interno hasta el nivel más externo.

-- Ejemplo
    SELECT last_name, department_id, UPPER(CONCAT(SUBSTR(last_name,1, 8), '_chile'))
    FROM employees
    WHERE department_id between 10 AND 40;
    

En el ejemplo, la función SUBSTR retorna los primeros ocho caracteres del apellido del empleado. La función CONCAT concatena el resultado de la función SUBSTR con el string '_chile'. Finalmente la función UPPER convierte en mayúscula el resultado entregado por la función CONCAT.

Funciones Generales

Estas funciones trabajan con cualquier tipo de datos y se relacionan con el uso de valores nulos en la lista de expresiones:

• NVL: Si el valor de expr1 es NULO , devuelve el valor de expr2. Si no es NULO retorna el valor de expr1.
• NVL2: Devuelve el valor de expr2 si el valor de expr1 no es NULO. Si expr1 es NULO devuelve el valor de expr3.
• NULLIF: devuelve NULO si expr1 y expr2 son iguales. Si no lo son devuelve el valor de expr1.
• COALESCE: retorna el valor de expr1 si no es NULO. Si es NULO devuelve el valor de expr2 si no es NULO. Si los valores de expr1 y expr2 son NULOS devuelve el valor de expr3 sino es NULO y así sucesivamente.

-- Uso de la función NVL
    SELECT last_name, salary, NVL(commission_pct,0) "PORCENTAJE COMISION",
           (salary*12) + (salary*12*NVL(commission_pct,0)) "SALARIO ANUAL",
           NVL(TO_CHAR(manager_id), 'No posee Jefe') JEFE
    FROM employees;

En la sentencia del ejemplo, la primera y segunda función NVL retornan un cero cuando el porcentaje de comisión del empleado sea nulo. La última función
NVL retorna el string No posee Jefe cuando la identificación del jefe sea nula. debido a que se retorna un string el valor de la columna a validar se debe
convertir a un string usando la función TO_CHAR(manager_id).

-- Uso de la función NVL2
    SELECT employee_id, salary, commission_pct,
           NVL2(commission_pct,'SALARIO+COMISION','SOLO SALARIO')
           AS "SALARIO MENSUAL CORRESPONDE A"
    FROM employees
    WHERE employee_id IN(100,101,114,147,148,149);

En el ejemplo, la función NVL2 mostrará el string SALARIO+COMISION si el porcentaje de comisión no es nulo. Si el porcentaje de comisión es nulo mostrará el string SOLO SALARIO para los empleados con identificación 100, 101, 114, 147,148 o 149.

-- Uso de la función NULLIF
    SELECT first_name, LENGTH(first_name) "Largo Nombre", last_name,
           LENGTH(last_name) "Largo Apellido",
           NULLIF(LENGTH(first_name),LENGTH(last_name)) "Resultado Función NULLIF"
    FROM employees
    WHERE employee_id IN(100,104,106,110);

En la sentencia del ejemplo, las funciones LENGTH retornas el total de caracteres del nombre y apellido del empleado respectivamente. Si el total de caracteres del nombre y apellido del empleado son iguales la función NULLIF mostrará NULO y si son diferentes mostrará el total de caracteres del nombre. La información se muestra para los empleados con identificación 100, 104, 106 o 110.

-- Uso de la función COALESCE
    SELECT last_name, commission_pct, manager_id,
           COALESCE(commission_pct,manager_id,9999) "Resultado Función COALESCE"
    FROM employees
    WHERE department_id IN(10,20,90);

En el ejemplo, la función COALESCE mostrará el valor del porcentaje de comisión si no es nulo, de lo contrario mostrará el valor de la identificación del jefe si no es nulo y si ambos valores son nulos mostrará 9999 para los empleados que trabajan en el departamento 10, 20 o 90.

Expresiones Condicionales

Existe dos métodos que se pueden utilizar para implementar el procesamiento condicional de lógica IF-THEN-ELSE en una sentencia SQL.

-- Uso de la función CASE
    SELECT employee_id, job_id, department_id, salary,
           CASE job_id WHEN 'PR_REP' THEN 1.15*salary
                       WHEN 'MK_MAN' THEN 1.20*salary
           ELSE salary END "Salario Incrementado"
    FROM employees
    WHERE department_id IN(70,20,110);

En el ejemplo, en la expresión CASE de la sentencia, el valor de la columna job_id es la condición de búsqueda. Si el trabajo del empleado es PR_REP el salario se mostrará incrementado en 15%, si es MK_MAN el salario se mostrará incrementado en 20% y para el resto de los trabajos no se incrementa el salario mostrándose sólo el salario actual

-- Uso de la función DECODE
    SELECT employee_id, job_id, department_id, salary,
                DECODE (job_id, 'PR_REP', 1.15*salary,
                        'MK_MAN', 1.20*salary,
                salary) "Salario Incrementado"
    FROM employees
    WHERE department_id IN(70, 20, 110);

En el ejemplo, el valor de la columna job_id es la condición de búsqueda para la función DECODE. Si el trabajo del empleado es PR_REP el salario se mostrará incrementado en 15%, si es MK_MAN el salario se mostrará incrementado en 20% y para el resto de los trabajos no se incrementa el salario mostrándose sólo el salario actual. La información de muestra para los empleados que trabajan en el departamento 20, 70 0 90.

Guía:

/* 1.- La Gerencia desea contar con información de los jefes a cargo de cada empleado. Para ello se requiere que construya una consulta que 
muestre el nombre completo del empleado (nombre y apellido concatenados con un espacio en blanco) y la identificación de su jefe. Si el empleado 
NO posee jefe se debe mostrar el mensaje NO POSEE JEFE. La información se debe mostrar según el ejemplo y ordenada en forma descendente por 
identificación del jefe: */
SELECT FIRST_NAME || ' ' || LAST_NAME empleado, NVL(TO_CHAR(MANAGER_ID),'NO TIENE JEFE') jefe
FROM EMPLOYEES
ORDER BY MANAGER_ID DESC;

/* 2.- Los empleados han planteado la necesidad de que se les aumente el valor de movilización mensual que se les debe pagar por ley.  Por ello, 
la  Gerencia ha aceptado la petición y ha definido que el valor de movilización será un porcentaje del salario mensual del empleado. Este 
porcentaje corresponderá por cada $1000 del salario de cada empleado es decir, si el salario del empleado es 8200 el porcentaje de aumento de 
movilización será de 8%, si el salario del empleado es de 15000 el porcentaje de aumento será de 15% etc. Como primera etapa se debe generar un 
informe que muestre la identificación del empleado, su salario actual y el porcentaje del salario que le corresponderá como movilización. Dar 
a cada columna de salida un alias según se muestra en el ejemplo: */
SELECT EMPLOYEE_ID empleado, SALARY salario, TRUNC(SALARY/1000) "PORCENTAJE DE MOVILIZACION"
FROM EMPLOYEES;

/* 3.- La empresa ha decido modificar las políticas de asignación de usuario y clave a acceso del personal a los diferentes sistemas 
informáticos que existen. Desde el próximo mes la política de asignación de nombres de usuarios y claves será:
? Nombre de Usuario: corresponderá a las tres primeras letras del nombre el empleado (la primera en mayúscula y las otras dos en minúsculas), 
seguido del largo de su nombre y de la identificación del trabajo que desempeña. 
? Clave del Usuario: corresponderá al mes y año (en 4 dígitos) de contrato del empleado seguido de las dos últimas letras de su apellido en 
mayúsculas.
Se requiere que Ud. construya una consulta que permita obtener el nombre del empleado, su apellido, identificación de su trabajo, nombre de 
usuario y clave. La información se requiere de acuerdo como se muestra en el ejemplo y ordenada por apellido en forma ascendente: */
SELECT FIRST_NAME nombre, LAST_NAME apellido, JOB_ID trabajo, 
       INITCAP(SUBSTR(FIRST_NAME,1,3)) || LENGTH(FIRST_NAME) || JOB_ID nombre_usuario,
       TO_CHAR(HIRE_DATE,'MMYYYY') || UPPER(SUBSTR(LAST_NAME,-2)) "CLAVE USUARIO"
FROM EMPLOYEES
ORDER BY LAST_NAME;

/* 4.- Se desea poder informatizar el Listado de Salarios de los empleados que hasta ahora se maneja en forma manual. El informe que Ud. 
desarrolle deberá mostrar la información ajustada para que visualmente se vea ordenada. Para ello, a través de una sentencia SQL implemente 
lo requerido según se muestra en el ejemplo de la derecha: */
SELECT RPAD(LAST_NAME,20,' ') || 'posee un salario de' || LPAD(SALARY,10,' ') "LISTADO DE SALARIOS" 
FROM EMPLOYEES;

/* 5.- Para poder gestionar información del personal que trabaja en la empresa, el departamento de recursos humanos de la empresa requiere 
contar con un informe que permita saber la fecha en que cada empleado se ha contratado. Para ello, se requiere saber  el nombre, apellido y 
fecha de contrato (el día en palabras) de todos los empleados. La información se debe mostrar ordenada por fecha de contrato en forma 
ascendente y en el formato del ejemplo: */
SELECT 'El empleado ' || FIRST_NAME || ' ' || LAST_NAME || ' ha sido contratado el ' || 
       INITCAP(TO_CHAR(HIRE_DATE,'day')) || TO_CHAR(HIRE_DATE,' dd') || ' de ' || 
       INITCAP(TO_CHAR(HIRE_DATE,'month')) || ' del ' || TO_CHAR(HIRE_DATE,'yyyy') contratos
FROM EMPLOYEES
ORDER BY HIRE_DATE;

/* 6.- Se ha efectuado una encuesta entre los empleados de la empresa para saber cuál sería el salario que ellos consideran el ideal según 
el trabajo que efectúan.  Con excepción del empleado que NO posee jefe, coincidieron que a ellos les gustaría ganar tres veces más de su 
salario actual. Con esta información, se  le solicita a Ud. que genere un reporte  que muestre el apellido, salario actual y el salario soñado 
de los empleados que desean ganar tres veces de su salario actual. La información se debe mostrar según se muestra en el ejemplo asignando 
además el formato indicado a los valores del salario actual y soñado: */
SELECT 'El empleado ' || LAST_NAME || ' posee un salario de ' || 
       TO_CHAR(SALARY,'$99,999.99') || ' pero su sueño es ganar ' ||
       TO_CHAR(SALARY * 3,'$99,999.99') "SALARIO SOÑADO"
FROM EMPLOYEES
WHERE MANAGER_ID IS NOT NULL;

/* 7.- Se desea aumentar en un 25,8% los salarios de los empleados que ganan menos de $5000. Para ello se requiere que Ud. genere un informe 
mostrando la identificación del empleado, identificación del departamento en el que trabaja, valor actual de su salario, el valor del reajuste 
(redondeado) y el salario reajustado en 25,8% (redondeado). Mostrar el informe en el formato que se muestra y la información ordenada en forma 
ascendente por departamento y por cada departamento ordenado en forma descendente por el salario reajustado como se muestra en el ejemplo: */
SELECT EMPLOYEE_ID empleado, DEPARTMENT_ID departamento, SALARY "SALARIO ACTUAL",
       ROUND(SALARY * 0.258) reajuste, ROUND(SALARY + (SALARY * 0.258)) "SALARIO REAJUSTADO"
FROM EMPLOYEES
WHERE SALARY < 5000
ORDER BY DEPARTMENT_ID, SALARY DESC;

/* 8.- La Gerencia  ha definido que a contar del próximo mes se pagará un bono de acuerdo al grado del trabajo que desempeña cada empleado y 
que se clasificará de la siguiente manera: 
TRABAJO GRADO
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
Cualquier otro trabajo O
Para ello, se requiere de un listado de los empleados con su nueva categorización según el trabajo que desempeña. La información que se 
solicitó es nombre completo del empleado, trabajo que desempeña y grado que le corresponde por su trabajo. Se debe mostrar ordenada por 
apellido del empelado y en el formato que se muestra en el ejemplo: */
SELECT FIRST_NAME || ' ' || LAST_NAME "NOMBRE EMPLEADO", JOB_ID "TRABAJO QUE DESEMPEÑA",
       CASE JOB_ID WHEN 'AD_PRES' THEN 'A'
                   WHEN 'ST_MAN' THEN 'B'
                   WHEN 'IT_PROG' THEN 'C'
                   WHEN 'SA_REP' THEN 'D'
                   WHEN 'ST_CLERK' THEN 'E'
       ELSE 'O' END "GRADO SEGUN SU TRABAJO" 
FROM EMPLOYEES
ORDER BY LAST_NAME;

/* 9.-  La empresa desea que se automatice algunos de los procesos  involucrados en el cálculo de las remuneraciones mensuales de los 
empleados. Para ello,  Ud. deberá implementar tres rutinas de acuerdo a los requerimientos planteados:
  ? En un esfuerzo por mejorar las necesidades económicas de los empleados, se desea saber el costo que significaría efectuar un aumento en 
  sus salarios. Para ello, se ha pensado que el porcentaje de reajuste corresponderá al primer dígito del salario actual de cada empleado es 
  decir, si el empleado posee un salario de 24000 su aumento será de 2%, si su salario es de 7500 su aumento será de 7% etc. Se requiere de 
  un reporte que muestre el nombre y apellido del empleado concatenado, salario actual y porcentaje de reajuste. Dar a cada columna de salida 
  el alias correspondiente y formato a los valores del salario y reajuste como se muestra en el ejemplo: */
SELECT FIRST_NAME || ' ' || LAST_NAME "NOMBRE EMPLEADO", TO_CHAR(SALARY,'$99,999') salario,
       TO_CHAR((SALARY * SUBSTR(SALARY,1,1)/100),'$999') reajuste
FROM EMPLOYEES;

  /* ? Implementar el cálculo de las comisiones y  salario total de cada uno de los empleados considerando las siguientes especificaciones:
      ? Si el empleado posee porcentaje de comisión se debe mostrar, de lo contrario se debe mostrar el valor cero.
      ? El valor de la comisión  corresponderá al valor del salario actual multiplicado por el porcentaje de comisión. Si el empleado no posee 
      porcentaje de comisión se debe mostrar el valor cero.
      ? El valor salario total corresponderá a la suma del valor del salario actual más el valor de la comisión. Si el empleado no posee 
      comisión el salario total será igual al valor del salario actual.
  En esta primera etapa, solo se requiere de un listado que muestre  la identificación del empleado con el alias, el valor del salario actual, 
  el porcentaje de comisión, el valor de la comisión calculada y el valor del salario total calculado. La información se debe mostrar en el 
  formato que se muestra en el ejemplo: */
SELECT EMPLOYEE_ID "ID EMPLEADO", SALARY "SALARIO SIN COMICION",
       NVL(COMMISSION_PCT,0) "PORC. COMISION",
       NVL2(COMMISSION_PCT,SALARY*COMMISSION_PCT,0) "VALOR COMISION",
       NVL2(COMMISSION_PCT,SALARY+(SALARY*COMMISSION_PCT),SALARY) "SALARIO TOTAL"
FROM EMPLOYEES;

  /* ? La Gerencia ha decidido que a contar del mes Mayo a los empleados cuyo salario esté entre 1000 y 5000 se les aumentará el salario 
  según los años que lleva trabajando en la empresa. Así por ejemplo, si el empleado lleva trabajando 9 años su salario se aumentará en un 9%, 
  si lleva trabajando 15 años su salario aumentará en un 15%, etc. Para ello, se requiere almacenar en la tabla COMISION la identificación del 
  empleado, la fecha de contrato, salario actual, los años que lleva contratado en la empresa  según el año actual y el valor del aumento 
  (redondeado) para cada empleado. Dar a cada columna de la tabla el nombre correspondiente según se muestra en el ejemplo (la sentencia se 
  ejecutó el año 2014): */
SELECT EMPLOYEE_ID "ID EMPLEADO", HIRE_DATE "FECHA CONTRATO", SALARY salario, 
       ROUND(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12) "AÑOS CONTRATADOS",
       ROUND(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)*SALARY/100 "AUMENTO"
FROM EMPLOYEES
WHERE SALARY BETWEEN 1000 AND 5000;