Post on 02-Jun-2018
transcript
8/10/2019 12 Subconsultas Avanzadas v 3
1/45
Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Subconsultas Avanzadas
8/10/2019 12 Subconsultas Avanzadas v 3
2/45
18-2 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Objetivos
Al finalizar esta leccin, debera estar capacitado para: Escribir una subconsulta de varias columnas
Describir y explicar el comportamiento de las subconsultascuando se recuperan valores nulos
Escribir una subconsulta en una clusula FROM
Utilizar subconsultas escalares en SQL
Describir los tipos de problemas que se pueden resolver consubconsultas correlacionadas
Escribir subconsultas correlacionadas
Actualizar y suprimir filas mediante subconsultascorrelacionadas
Utilizar los operadores EXISTSy NOT EXISTS
Utilizar la clusula WITH
8/10/2019 12 Subconsultas Avanzadas v 3
3/45
18-3 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Qu Es una Subconsulta?
Una subconsulta es una sentencia SELECTque est
embebidaen una clusula de otra sentencia SQL.
SELECT ...FROM ...
WHERE ...(SELECT ...FROM ...
WHERE ...)
Consulta
principal
Subconsulta
8/10/2019 12 Subconsultas Avanzadas v 3
4/45
18-4 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Subconsultas
La subconsulta (consulta interna) se ejecuta una vezantes de la consulta principal.
El resul tado d e la subco nsu l ta lo utiliza la consultaprincipal (consulta externa).
SELECT select_listFROM table
WHEREexpr operator(SELECT select_list
FROM table);
8/10/2019 12 Subconsultas Avanzadas v 3
5/45
18-5 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
SELECT last_nameFROM employees
WHERE salary >(SELECT salaryFROM employees
WHERE employee_id = 149) ;
Uso de una Subconsulta
10500
8/10/2019 12 Subconsultas Avanzadas v 3
6/45
18-6 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Subconsultas de Varias Columnas
Consulta principalWHERE (MANAGER_ID, DEPARTMENT_ID) IN
Subconsulta
100 90102 60
124 50
Cadafila de la consulta principalse compara con los valores
de una subconsulta de varias filas y de varias columnas.
8/10/2019 12 Subconsultas Avanzadas v 3
7/4518-7 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Comparaciones de Columnas
Las comparaciones de columnas en una subconsultade varias columnas pueden ser:
Comparaciones entre pares
Comparaciones no entre pares
8/10/2019 12 Subconsultas Avanzadas v 3
8/4518-8 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Qu hace este Query?
8/10/2019 12 Subconsultas Avanzadas v 3
9/4518-9 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Subconsulta de Comparacin entre Pares
SELECT employee_id, manager_id, department_idFROM employees
WHERE (manager_id, department_id) IN(SELECT manager_id, department_idFROM employees
WHERE employee_id IN (178,174))AND employee_id NOT IN (178,174);
Visualiza los detalles de los empleados dirigidos por el mismo
director yque trabajenen el mismo departamento que los empleadoscuyo EMPLOYEE_IDsea 178 o 174.
8/10/2019 12 Subconsultas Avanzadas v 3
10/4518-10 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Subconsulta de Comparacin
No entre Pares
SELECT employee_id, manager_id, department_id
FROM employeesWHERE manager_id IN
(SELECT manager_idFROM employees
WHERE employee_id IN (174,141))AND department_id IN
(SELECT department_idFROM employees
WHERE employee_id IN (174,141))
AND employee_id NOT IN(174,141);
Visualice los detalles de los empleados dirigidos por el mismodirector que los empleados con EMPLOYEE_ID174 o 141 yque
trabajen en el mismo departamento que los empleados conEMPLOYEE_ID174 o 141.
8/10/2019 12 Subconsultas Avanzadas v 3
11/4518-11 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Qu hace este query?
SELECT department_id,
AVG(salary) salavg
FROM employees
GROUP BY department_id
8/10/2019 12 Subconsultas Avanzadas v 3
12/4518-12 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
SELECT a.last_name, a.salary,a.department_id, b.salavg
FROM employees a, (SELECT department_id,AVG(salary) salavgFROM employees
GROUP BY department_id) bWHERE a.department_id = b.department_idAND a.salary > b.salavg;
Uso de una Subconsultaen la Clusula FROM
8/10/2019 12 Subconsultas Avanzadas v 3
13/4518-13 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Expresiones de Subconsulta Escalar
Una expresin de subconsulta escalar es una subconsulta quedevuelve exactamente un valor de columna de una fila.
Las subconsultas escalares se soportaban en Oracle8islo en
un juego limitado de casos, como, por ejemplo:
Sentencia SELECT(clusulas FROMy WHERE) Lista VALUESde una sentencia INSERT
En Oracle9i, las subconsultas escalares se pueden utilizar en:
Condicin y expresin parte de DECODEy CASE
Todas las clusulas de SELECTexcepto GROUP BY
8/10/2019 12 Subconsultas Avanzadas v 3
14/4518-14 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Subconsultas Escalares: Ejemplos
Subconsultas Escalares en Expresiones
SELECT employee_id, last_name,
(CASE
WHEN department_id =
THEN 'Canada' ELSE 'USA' END) location
FROM employees;
(SELECT department_id FROM departments
WHERE location_id = 1800)
Subconsultas Escalares en la Clusula
SELECT employee_id, last_name
FROM employees e
ORDER BY
20
(SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id);
8/10/2019 12 Subconsultas Avanzadas v 3
15/4518-16 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Subconsultas Correlacionadas
Las subconsultas correlacionadas se utilizan para elprocesamiento fila a fila. Cada subconsulta se ejecuta unavez para cada fila de la consulta externa.
OBTENER
posible fila de consulta externa
EJECUTAR
consulta interna utilizando valor de posible fila
UTILIZAR
valores de la consulta interna para cualificar
o descualificar posible fila
8/10/2019 12 Subconsultas Avanzadas v 3
16/4518-17 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Subconsultas Correlacionadas
SELECT column1, column2, ...FROM table1
WHERE column1operator
(SELECT colum1, column2FROM table2WHERE expr1=
.expr2);
La subconsulta hace referencia a una columna de una tabla
en la consulta principal.
outer
outer
8/10/2019 12 Subconsultas Avanzadas v 3
17/45
8/10/2019 12 Subconsultas Avanzadas v 3
18/4518-19 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Uso de Subconsultas Correlacionadas
SELECT e.employee_id, last_name,e.job_id
FROM employees e
WHERE 2
8/10/2019 12 Subconsultas Avanzadas v 3
19/4518-20 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Uso del Operador EXISTS
El operador EXISTScomprueba la existencia de filas enel juego de resultados de la subconsulta.
Si se encuentra un valor de fila de la subconsulta:
La bsqueda no contina en la consulta interna.
Se sealiza a la condicin como TRUE.
Si no se encuentra un valor de fila de la subconsulta:
Se sealiza a la condicin como FALSE.
La bsqueda contina en la consulta interna.
8/10/2019 12 Subconsultas Avanzadas v 3
20/4518-21 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
SELECT employee_id, last_name, job_id, department_idFROM employees outer
WHERE EXISTS ( SELECT 'X'FROM employees
WHERE manager_id =outer.employee_id);
Uso del Operador EXISTS
Busque los empleados que tengan al menos una persona
que les informe.
8/10/2019 12 Subconsultas Avanzadas v 3
21/4518-22 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
SELECT department_id, department_nameFROM departments d
WHERE NOT EXISTS (SELECT 'X'
FROM employeesWHERE department_id
= d.department_id);
Uso del OperadorNOT EXISTS
Busque todos los departamentos que no tengan empleados.
8/10/2019 12 Subconsultas Avanzadas v 3
22/45
8/10/2019 12 Subconsultas Avanzadas v 3
23/45
18-24 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
UPDATE Correlacionado
Desnormalice la tabla EMPLOYEESagregando unacolumna que almacene el nombre de departamento.
Rellene la tabla mediante una actualizacin
correlacionada.
ALTER TABLE employeesADD(department_name VARCHAR2(14));
UPDATE employees eSET department_name =
(SELECT department_nameFROM departments dWHERE e.department_id = d.department_id);
8/10/2019 12 Subconsultas Avanzadas v 3
24/45
18-26 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
DELETE FROM table1 alias1WHERE column operator
(SELECT expressionFROM table2 alias2
WHERE alias1.column = alias2.column);
DELETECorrelacionado
Utilice una subconsulta correlacionada para suprimir las filas
de una tabla basadas en filas de otra tabla.
8/10/2019 12 Subconsultas Avanzadas v 3
25/45
18-27 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
DELETE FROM employees EWHERE employee_id =
(SELECT employee_idFROM emp_history
WHERE employee_id = E.employee_id);
Utilice una subconsulta correlacionada para suprimirsolamente las filas de la tabla EMPLOYEESque tambinexisten en la tabla EMP_HISTORY.
DELETECorrelacionado
8/10/2019 12 Subconsultas Avanzadas v 3
26/45
18-28 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
La ClusulaWITH
Con la clusulaWITH, puede utilizar el mismo bloque deconsulta en una sentencia SELECTcuando se producems de una vez dentro de una consulta compleja.
La clusulaWITHrecupera el resultado de un bloque de
consulta y lo almacena en el tablespace temporal delusuario.
La clusulaWITHmejora el rendimiento.
8/10/2019 12 Subconsultas Avanzadas v 3
27/45
18-29 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
ClusulaWITH: Ejemplo
Mientras utiliza la clusulaWITH, escriba una consultaque muestre el nombre de departamento y los salariostotales de los departamentos cuyos salarios totalessean mayores que el salario medio de los
departamentos.
8/10/2019 12 Subconsultas Avanzadas v 3
28/45
18-30 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
ClusulaWITH: Ejemplo
WITHdept_costs AS (
SELECT d.department_name, SUM(e.salary) AS dept_totalFROM employees e, departments d
WHERE e.department_id = d.department_idGROUP BY d.department_name),
avg_cost AS (SELECT SUM(dept_total)/COUNT(*) AS dept_avgFROM dept_costs)
SELECT *FROM dept_costs
WHERE dept_total >(SELECT dept_avgFROM avg_cost)
ORDER BY department_name;
8/10/2019 12 Subconsultas Avanzadas v 3
29/45
18-31 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
EJERCICIO DESARROLLADO
Escriba una consulta para mostrar los apellidos de los empleados
que ganen menos que el salario medio en sus departamentos.
SOLUCION 1 EN EL FROM
select e.last_name, e.salary, e.department_id, p.salprom
from employees e,(select avg(salary) salprom,department_id from employees group bydepartment_id) p
where e.department_id = p.department_id
and e.salary
8/10/2019 12 Subconsultas Avanzadas v 3
30/45
18-32 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Resumen
En esta leccin, debera haber aprendido lo siguiente:
Una subconsulta de varias columnas devuelve ms deuna columna.
Las comparaciones de varias columnas pueden serentre pares o no entre pares.
Una subconsulta de varias columnas tambin se puedeutilizar en la clusula FROMde una sentencia SELECT.
Se han mejorado las subconsultas
escalares en Oracle9i.
8/10/2019 12 Subconsultas Avanzadas v 3
31/45
18-33 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Resumen
Las subconsultas correlacionadas son tiles cada vez
que una subconsulta deba devolver un resultado
diferente para cada posible fila.
El operador EXISTSes un operador booleano que
prueba la presencia de un valor.
Se pueden utilizar subconsultas correlacionadas consentencias SELECT, UPDATEy DELETE.
Puede utilizar la clusulaWITHpara usar el mismo
bloque de consulta en una sentencia SELECTsi seproduce ms de una vez.
8/10/2019 12 Subconsultas Avanzadas v 3
32/45
18-34 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Visin General de la Prctica 18
Esta prctica cubre los siguientes temas:
Creacin de subconsultas de varias columnas
Escritura de subconsultas correlacionadas
Uso del operador EXISTS
Uso de subconsultas escalares
Uso de la clusulaWITH
8/10/2019 12 Subconsultas Avanzadas v 3
33/45
18-35 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
EJERCICIOS
Listar nicamente los apellidos de los empleados quesean jefes (manager_id) , acompaados de la cantidad detrabajadores que tienen a su cargo.
8/10/2019 12 Subconsultas Avanzadas v 3
34/45
18-36 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
EJERCICIOS 1
Listar nicamente los apellidos de los empleados quesean jefes (manager_id) , acompaados de la cantidad detrabajadores que tienen a su cargo , siempre y cuando latenga mas de 5 trabajadores a su cargo
8/10/2019 12 Subconsultas Avanzadas v 3
35/45
18-37 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
EJERCICIOS 2
Escriba una consulta para mostrar el identificador deempleado, sus apellidos y nombres, y asi como losnombres de departamento de todos los empleados.
Nota: Utilice una subconsulta escalar para recuperar el
nombre de departamento en la sentencia SELECT.
8/10/2019 12 Subconsultas Avanzadas v 3
36/45
18-38 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
EJERCICIOS 3
Muestre los detalles del identificador de empleado, elapellido y el identificador de departamento de losempleados que vivan en ciudades cuyos nombrescomiencen por T.
8/10/2019 12 Subconsultas Avanzadas v 3
37/45
18-39 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
EJERCICIOS 4
Escriba una consulta para mostrar los apellidos de losempleados que ganen menos que el salario medio ensus departamentos.
Nota: debe utilizar sub consulta en la sentencia WHERE
8/10/2019 12 Subconsultas Avanzadas v 3
38/45
18-40 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
EJERCICIOS 5
Escriba una consulta para mostrar los apellidos de losempleados que ganen menos que el salario medio ensus departamentos.
Nota: NO debe utilizar sub consulta en la sentenciaWHERE
8/10/2019 12 Subconsultas Avanzadas v 3
39/45
18-41 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Realice un query que permita buscar TODOS losdepartamentos que si tengan empleados
EJERCICIOS 6
8/10/2019 12 Subconsultas Avanzadas v 3
40/45
18-42 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Realice un query que permita buscar TODOS losdepartamentos que si tengan empleados
EJERCICIOS 7
8/10/2019 12 Subconsultas Avanzadas v 3
41/45
18-43 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Ejercicios 8
Realice un query que permita buscar solo losdepartamentos que pertenescan al la region 2 y que sitengan empleados.
8/10/2019 12 Subconsultas Avanzadas v 3
42/45
18-44 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Ejercicios 9
Realice un query que permita buscar solo losdepartamentos que pertenescan al la region 2 y que notengan empleados.
8/10/2019 12 Subconsultas Avanzadas v 3
43/45
18-45 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Ejercicios 10
Visualice detalles de los empleados que han cambiado
al menos tres veces de cargo.
8/10/2019 12 Subconsultas Avanzadas v 3
44/45
18-46 Copyright Oracle Corporation, 2001. Todos los derechos reservados.
Visualice los apellidos de los empleados (ordenadosdescendentemente) que son dirigidos por el mismodirector que del empleado shannon yque trabajen enel mismo departamento que del empleado shannon.
Ejercicios 11
8/10/2019 12 Subconsultas Avanzadas v 3
45/45
Para resolver el ejercicio 2 se propone la sgte solucion
selecte.last_name, c.cont from
employees e,
(select manager_id , count(manager_id) as contfrom employees GROUP BY MANAGER_ID) c
where e.employee_id = c.manager_id
andc.cont > 5
Ejercicios