Oracle Sep 05 Consultas Avanzadas

UNIVERSIDAD NACIONAL JOSE FAUSTINO SANCHEZ CARRION

FACULTAD DE INGENIERIA ESCUELA ACADEMICO PROFESIONAL DE INGENIERIA INFORMATICA

CURSO BASE DE DATOS II

SEPARATA Nº 05 CONSULTAS AVANZADAS Y SUB CONSULTAS

DOCENTE ING. EDWIN IVAN FARRO PACIFICO

HUACHO-LIMA PERU

Universidad Nacional José Faustino Sánchez Carrión

Ing. CIP Edwin Iván Farro Pacífico

CONSULTAS AVANZADAS JOIN DE TABLAS

Un join o consulta correlacionada, permite mostrar datos a partir de múltiples tablas y las muestra en único resultado. Pero que sucede si realizamos la siguiente consulta: SQL> SELECT ename,dname FROM emp,dept; EMP empno ename job mgr hiredate sal comm deptno

DEPT deptno dname loc

Obtenemos un producto cartesiano sin ninguna EQUIJOIN

Recupera filas de una o más tablas unidas sobre la base de una condición de igualdad en la cláusula WHERE. Ejemplo 1

Listar el nombre del empelado y el nombre al departamento al que pertenece: SQL> SELECT emp.ename,dept.dname FROM emp ,dept 2 WHERE emp.deptno=dept.deptno; Ó SQL> SELECT e.ename,d.dname FROM emp e,dept d 2 WHERE e.deptno=d.deptno; Ejemplo 2

Listar el código del empleado, nombre, nro de departamento de los que sean vendedores: SQL> SELECT e.empno,e.ename,d.deptno 2 FROM emp e,dept d 3 WHERE d.deptno=e.deptno AND e.job='SALESMAN'; NON-EQUIJOIN

Recupera filas de dos o más tablas teniendo como base una relación diferente a la condición de igualdad en la cláusula WHERE.


Pag 2



Se utiliza cuando por ejemplo se busca rangos Ejemplo 3

Listar el nombre de los empleados la categoría y a la que pertenecen, de acuerdo al rango en que se encuentra el salario. SQL>SELECT e.ename as Empleado,s.grade as Categoría FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal; Ejemplo 4

Mostrar el nombre del empleado y el salario sólo de aquellos empleados cuyo salario esta en el grado 3 SQL> SELECT e.ename,e.sal 2 FROM emp e,salgrade s 3 WHERE (e.sal BETWEEN s.losal AND s.hisal) 4 AND s.grade=3 Insertar el siguiente registro: SQL> INSERT INTO emp(empno,ename) 2 VALUES (7999,'SALAS'); JOIN EXTERNO

Combina dos o más tablas para recuperar aquellas filas de una tabla que no tienen un relación directa de correspondencia en la otra tabla. Ingresar los siguientes registros empno 7950 7951 7952 7953

ename ROMYNA ANDREA TORIBIA URSULA

job VENDEDOR VENDEDOR VENDEDOR VENDEDOR

mgr 7941 7941 7941

hiredate 10/05/02 11/12/02 20/08/03 21/09/03

sal 1000 1000 1000 1000

comm

Detno

200 150

Ejemplo 5

Mostrar todos los empleados así estos no tengan asociado un número de departamento. SQL> SELECT e.empno,e.ename,d.deptno,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno(+) Ejemplo 6

Mostrar todos los departamentos así estos no tengan asociado ningún empelado. SQL> SELECT e.empno,e.ename,d.deptno,d.dname FROM emp e,dept d WHERE e.deptno(+)=d.deptno Nota: (+) se le conoce como operador Outerjoin


Pag 3



AUTOJOIN

Hace un join de una tabla consigo misma como si se trataran de dos tablas diferentes. Ejemplo 7

Mostrar el número , nombre del empleado y cargo, también el número el nombre de su jefe y cargo utilizando alias para las columnas. SQL> SELECT e.empno cod_emp,e.ename nom_emp, j.empno cod_jefe,j.ename nom_jefe FROM emp j,emp e WHERE e.mgr=j.empno ORDER BY 1,3 PRODUCTO CARTESIANO

Se crea cuando se omite un Join en una sentencia SELECT, lo que provoca que Oracle relacione todas las filas de la primera tabla a todas las filas de la segunda tabla. Ejemplo 8

SQL> SELECT e.empno,e.ename,d.dname FROM emp e,dept d NATURAL JOINS (Versión 9i y posteriores)

Es un enlace entre dos tablas que comparten el mismo nombre para la columna común. Simplifican de manera importante las combinaciones de tablas, eliminando los alias de tablas y los joins de comparación. Ejercicio 9

Listar los nombres de los empleados y el departamento al que pertenecen SQL> SELECT ename,deptno,dname 2 FROM emp NATURAL JOIN dept; OPERACIONES CON CONJUNTOS

Combinan dos o más consultas en un solo resultado. Se cuentan con los siguientes operadores. Union

Une dos conjuntos de resultados Ejercicio 10

SQL> SELECT ename 2 FROM emp 3 WHERE deptno IN (10,30) 4 UNION 5 SELECT ename 6 FROM emp 7 WHERE deptno IN (10,20);


Pag 4



Intersect

Interfecta dos conjuntos resultados Ejercicio 11

SQL> SELECT ename 2 FROM emp 3 WHERE deptno IN (10,30) 4 INTERSECT 5 SELECT ename 6 FROM emp 7 WHERE deptno IN (10,20); Minus

Resta dos conjuntos de resultados. Ejercicio 12

SQL> SELECT ename 2 FROM emp 3 WHERE deptno IN (10,30) 4 MINUS 5 SELECT ename 6 FROM emp 7 WHERE deptno IN (10,20); FUNCIONES DE GRUPO

SUM (campo) AVG (campo) MAX (campo) MIN (campo) COUNT (campo) Ejercicio 13

Suma de todos los salarios SQL> SELECT SUM(sal) FROM emp; Ejercicio 14

Suma de todos los salaries cuyo número de departamento sea 20 SQL> SELECT SUM(sal) FROM emp 2 WHERE deptno=20; Ejercicio 15

Listar las suma de los salarios, el mayor y menor salario del departamento 20 Ejercicio 16

Contar todos los empleados del departamento 20


Pag 5



Ejercicio 17

Contar el número empleados que han comisionado. Ejercicio 18

Listar el promedio de las comisiones Ejercicio 19

Listar el mayor sueldo por cada departamento (sólo con el código) Ejercicio 20

Listar el mayor sueldo por cada departamento (mostrar el nombre del departamento) Ejercicio 21

Listar los sueldos promedio por departamento Ejercicio 22

Listar el número de personas con el mismo cargo

SUB CONSULTAS Seleccionan filas de una tabla con una condición que depende de los valores de otra consulta. Una subconsulta puede recuperar un solo valor para completar la condición de la cláusula WHERE, opuede haber una subconsulta multifila que recupera mas de una valor para completar la cláusula WHERE. Las subconsultas pueden aparecer en la cláusula WHERE de las siguientes sentencias: SELECT INSERT UPDATE DELETE    

Ejercicio 23

Empleados que ganan más que smith SQL> select ename,sal from emp 2 where sal>(select sal 3 from emp where ename='SMITH'); SQL> select ename,sal from emp 2 where sal<(select avg(sal) 3 from emp) and sal>(select min(sal) 4 from emp where deptno=10);


Pag 6



Ejercicio 24

Quienes trabajan en el mismo departamento que Smith SQL> select ename from emp where deptno=(select deptno 2 from emp where ename='SMITH') and ename<>'SMITH'; Operadores:

IN : Busca que debe ser igual a alguno de los elementos de la subconsulta ANY : mayor que el menor ALL : mayor que el mayor Ejercicio 25

Quienes trababajn en el departamento que tienen el sueldo más alto SQL> select ename 2 from emp 3 where deptno in 4 (select deptno 5 from emp 6 where sal=(select max(sal) from emp)); Ejercicio 26

Empleados que ganan más que todos los vendedores. SQL> select ename 2 from emp 3 where sal>all 4 (select sal from emp 5 where job='SALESMAN'); Ejercicio 27

Quienes son los que ganan más que el vendedor que gana menos. SQL> select ename 2 from emp 3 where sal>any(select sal 4 from emp 5 where job='SALESMAN'); Ejercicio 28

Los empleados que nos son jefes. SQL> select ename 2 from emp 3 where empno not in(select MGR from emp 4 where MGR is not null); Ejercicio 29

El empleado que trabaja en el mismo departamento de un empleado que su nombre tenga S. SQL> select ename from emp 2 where deptno in 3 (select deptno from emp 4 where ename like '%S%');


Pag 7



Ejercicio 30

Empleados cuyo jefe sea KING SQL> select ename from emp 2 where MGR=(select empno 3 from emp where ename='KING');


Pag 8

Oracle Sep 05 Consultas Avanzadas

Recommend Documents