Oracle Certificación Blog 1Z-051

Generar filas para ser pasadas a una sentencia DML insert into sales_hist select * from sales where date > sysdate-1; En este primer ejemplo se utiliza un subquery en una sentencia INSERT, el subquery selecciona las ventas de una tabla SALES donde la fecha sea mayor a SYSDATE-1. Las filas seleccionadas serán insertadas en la tabla SALES. update employees set salary = (select avg(salary) from employees); En el ejemplo se utiliza un subquery en una sentencia UPDATE que actualiza el campo SALARY de la tabla EMPLOYEES asignando un nuevo valor al campo SALARY el cual es producto de un subquery, el cual obtiene el promedio de salario de los empleados. delete from departments where department_id not in (select department_id from employees); Y por último en una sentencia DELETE a la tabla DEPARTMENTS. Aquí se eliminaran los departamentos que nunca han sido asignados a un empleado. También hace uso de subquery el cual obtiene los ID de Departamentos asignados a los

Generar valores para proyección. El tercer lugar donde podemos colocar los subquerys es la lista de la clausula SELECT. Como podemos identificar el salario más alto y la comisión más alta de la tabla empleado. -- MEDIENTE EL USO DE UN SUBQUERY SELECT (SELECT MAX(SALARY) FROM EMPLOYEES) SALARIO_MAX,        (SELECT MAX(COMMISSION_PCT) FROM EMPLOYEES) COMISION_MAX FROM   DUAL; La lista SELECT es utilizada para proyectar columnas que son llenadas con el resultado de subquerys. Un subquery utilizado de esta manera debe ser escalar o la consulta padre fallará.

Generar una Tabla a partir de la cual seleccionar. Subquerys pueden también ser utilizados en la clausula FROM, donde son referidos como inline views. Considere un problema basado en el esquema HR. Los empleados son asignados a un departamento un departamento tiene una localización cada localización esta en un país. Como podemos encontrar el promedio de salario del personal de cada país, incluso si trabajan en diferentes departamentos. -- MEDIENTE EL USO DE UN SUBQUERY select avg(salary),country_id from   (select salary,department_id,location_id,country_id         from   employees                     natural join departments                     natural join locations) group by country_id; El subquery genera una tabla la cual el query padre agrupa por country_id y asi obtenemos el promedio. Con esto demostramos que los subquerys pueden ser colocados en la clausula FROM y son también llamados vistas en línea.

Transformación Estrella Una extensión del uso de subquerys como alternativa a los JOINS es para la transformación estrella a menudo necesaria en aplicaciones Date Warehouse. Considere una gran tabla que almacena ventas. Cada venta tiene asociado un producto particular, un comprador y un canal. Estos atributos son identificados mediante códigos, mediante el uso de llaves foráneas la tabla puede describir el producto, en comprador y el canal. Necesitamos escribir una consulta de todas las ventas de libros de comprador de Alemania a través del canal internet. select … from sales s, products p, buyers b, channels c where s.prod_code=p.prod_code and s.buy_code=b.buy_code and s.chan_code=c.chan_code and p.product=’Books’ and b.country=’Germany’ and c.channel=’Internet’; Esta consulta utiliza la clausula WHERE para unir las tablas y luego filtrar el resultado. La siguiente es una alternativa utilizando subquerys. select … from sales where prod_code in (select prod

Uso de un subquery para propósitos de comparación Que empleado tiene un salario menor al promedio de salario? Esto puede ser resuelto mediante un subquery. -- ESTE PROCEDIMIENTO SERIA SIN EL USO DE UN SUBQUERY SELECT ROUND(AVG(salary)) FROM employees; -- EL PROMEDIO ES 6462 SELECT employee_id, first_name, last_name, salary FROM employees WHERE salary < 6462; -- MEDIENTE EL USO DE UN SUBQUERY SELECT employee_id, first_name, last_name, salary FROM   employees WHERE  salary < (SELECT ROUND(AVG(salary)) FROM employees); En este ejemplo utilizamos un subquery de tipo escalar que devuelve un solo valor y lo utilizamos en la clausula WHERE como devuelve un solo valor hacemos una comparación contra otro valor si el subquery devolviera una lista de valor la consulta marcaría error. Un subquery también puede devolver un lista de valores que sea utilizada por otra consulta. Ahora buscaremos todos los departamentos que al menos tiene asignado un empleado. --

Subquery o subconsulta Un subquery es una sentencia SELECT que su salida es utilizada por otra sentencia SELECT. Un Subquery es una consulta que esta anidado dentro de una sentencia SELECT, INSERT, UPDATE o DELETE o dentro de otro subquery. Un subquery puede devolver un conjunto de filas o solo una fila a la consulta padre. Un subquery Escalar es una consulta que devuelve exactamente un valor: una sola fila con una sola columna. Subquery escalares pueden ser utilizados en muchos lugares, en una sentencia SQL donde puede haber una expresión o un valor literal. Lugares en una consulta donde puede ser utilizado un subquery son: ·         En la Lista SELECT. ·         En la clausula FROM. ·         En la clausula WHERE. ·         En la clausula HAVING. Un subquery es a menudo referido como  un inner query y la sentencia dentro de la cual aparece es llamada el outer query. La implementación de subquery en Oracle no tiene niveles de anidamiento, solo en la clausula WHERE e