Guía de Laboratorio #5a

U N I V E R S I DA D D E E L S A LVA D O R

FA C U LTA D D E I N G E N I E R I A Y A R Q U I T E C T U R A

E S C U E L A D E I N G E N I E R I A D E S I S T E M A S

I N F O R M AT I C O S

Base de Datos BAD115

Catedráticos: Ing. Elmer Arturo Carballo Ruiz MSc.

Ing. César Augusto González Rodríguez MAF.

Ciclo

I

Guía de Laboratorio #5a

Lenguaje estructurado de Consulta SQL

Contenido Objetivos .................................................................................................................................. 1

Introducción ............................................................................................................................. 1

Desarrollo. ................................................................................................................................ 2

Consultas Multitablas ................................................................................................................ 2

Consultas Simples .................................................................................................................. 2

Consultas Complejas .............................................................................................................. 3

Uso de Alias .......................................................................................................................... 3

NATURAL JOIN....................................................................................................................... 4

JOIN . . . USING ...................................................................................................................... 7

Producto Cartesiano .............................................................................................................. 9

Combinaciones Externas ........................................................................................................ 9

Left Outer Joins ....................................................................................................................10

Right Outer Join....................................................................................................................11

Full Outer Join ......................................................................................................................11

Otras Consultas Multitablas ......................................................................................................12

Autoreferenciadas (Self-joins) ...............................................................................................12

Operadores de Conjuntos .....................................................................................................13

Clausula WITH ......................................................................................................................16

Universidad de El Salvador BAD-115 Guía de Laboratorio #5

Ciclo II-2012 1

Ing. Elmer Arturo Carballo Ruiz MSc. / Ing. Cesar Augusto González Rodríguez MAF

Objetivos Que el estudiante sea capaz de:

Realizar las operaciones de consulta en SQL sobre Oracle utilizando una o más tablas.

Utilizar consultas simples, complejas, de unión(joins), autoreferenciadas, con cláusula

with, de conjunto, etc.

Introducción

En esta guía veremos cómo escribir sentencias SELECT para acceder a los datos

de dos o más tablas usando equality y non-equality joins (combinaciones por igualdad y

por desigualdad). Visualizar datos que no se cumplirían normalmente con una condición

de join, outer joins (uniones externas), combinaciones (self-Joins) de una tabla consigo

misma, consultas con cláusula with, de conjunto, etc..


Ciclo II-2012 2


Desarrollo.

Consultas Multitablas

¿Qué es un Join?

Un Join es usado para consultar datos desde más de una tabla. Las filas se combinan

(joined) relacionando valores comunes, típicamente valores de primary key y foreign key.

Métodos de Join:

Equijoin Non-equijoin

Outer join Self join

Consultas Simples a) Consultar los países por región.

Script: select regions.region_id, region_name, country_name from regions, countries where

regions.region_id = countries.region_id;


Ciclo II-2012 3


Consultas Complejas

b) Consultar los departamentos que se encuentran fuera de EEUU, y su respectiva

ciudad.

Script: select locations.location_id, city, department_name from locations, departments where

(locations.location_id = departments.location_id) and (country_id != 'US');

Uso de Alias Los alias simplifican la referencia a las columnas de las tablas que se utilizan en una

consulta.


Ciclo II-2012 4


c) Consultar los países de Asia.

Script: select r.region_id, r.region_name, c.country_name from regions r, countries c where

(r.region_id = c.region_id) and (r.region_name = 'Asia');

Usando Sintaxis ANSI

<table name> NATURAL [INNER] JOIN <table name>

<table name> [INNER] JOIN <table name> USING (<columns>)

<table name> [INNER] JOIN <table name> ON <condition>

NATURAL JOIN

Se combinas está basada en todas las columnas con igual nombre entre ambas tablas.

No es necesario utilizar alias.

d) Mostrar los códigos de localización, ciudad, nombre del departamento de locations

y departments.

Script: select location_id, city, department_name from locations natural join departments;

Script2: select location_id, city, department_name from departments natural join locations;


Ciclo II-2012 5


El resultado en ambos casos es el mismo.

Las columnas comunes solo se muestran una vez en el conjunto de resultado.


Ciclo II-2012 6


e) Mostrar los códigos de localización, ciudad, nombre del departamento de locations

y departments cuando los nombres de país Tengan la letra A al inicio.

Script: select * from regions natural join countries where country_name like 'A% ';

f) Mostrar las ciudades, pais y region a la que pertenecen.

Script: select region_name, country_name, city from regions natural join countries natural join

locations;


Ciclo II-2012 7


JOIN . . . USING

Permite indicar las columnas a combinar entre dos tablas.

g) Mostrar los codigos de localizacion, nombre de ciudad y su respectivo departamento, donde los

nombres de ciudad no posean la letra S de inicial.

Script: select location_id, city, department_name from locations join departments using (location_id)

where city not like 'S% ';

h) Mostrar las ciudades, pais y region a la que pertenecen, dentro de Estados Unidos(US).

Script: select region_name, country_name, city from regions join countries using(region_id) join

locations using(country_id) where country_id = 'US';


Ciclo II-2012 8


JOIN ... ON

La condición que permite combinar ambas tablas se debe especificar en la cláusula ON.

i) Mostrar las ciudades, pais y region a la que pertenecen, dentro de Estados Unidos(US).

Script: select region_name, country_name, city from regions r join countries c on

(r.region_id=c.region_id) join locations l on (c.country_id=l.country_id) where l.country_id = 'US';


Ciclo II-2012 9


Producto Cartesiano

Si dos tablas en una consulta no tienen ninguna condición de combinación, entonces

Oracle vuelve su producto cartesiano. Oracle combina cada fila de una tabla con cada fila

de la otra tabla. Un producto cartesiano genera muchas filas y es siempre raramente útil.

Por ejemplo, el producto cartesiano de dos tablas, cada una con 100 filas, su resultado

seria una tabla de 10.000 filas.

j) Combine los datos de Regiones(4 filas) y Paises(25)

Script: select region_name, country_name from regions, countries;

Modifique el script anterior utilizando la sintaxis ANSI, el resultado será el mismo

Script2: select region_name, country_name from regions cross join countries;

Combinaciones Externas

Una combinación externa amplía el resultado de una combinación simple. Una

combinación externa devuelve todas las filas que satisfagan la condición de combinación

y también vuelve todos o parte de las filas de una tabla para la cual ninguna filas de la

otra satisfagan la condición de combinación.

k) Mostrar todos los países y ciudades de la tabla countries y locations cuando los

nombres de países empiecen con la letra A.

Script: select c.country_name, l.city from countries c, locations l where ( c.country_id = l.country_id

(+) ) and ( c.country_name like 'A% ' );


Ciclo II-2012 10


Usando Sintaxis ANSI

Left Outer Joins

Todos estos ejemplos producen el mismo resultado, y muy similar al del Script 8.10.

Script2: select c.country_name, l.city from countries c left outer join locations l on c.country_id =

l.country_id where c.country_name like 'A% ' ;

Script3: select country_name, city from countries natural left join locations where

countries.country_name like 'A% ';

Script4: select country_name, city from countries left join locations using (country_id) where


Script5: select c.country_name, l.city from countries c, locations l where l.country_id (+) = c.country_id

and c.country_name like 'A% ' ;


Ciclo II-2012 11


Right Outer Join

Si se utiliza el Right Outer Join y invierte el orden de las tablas en los ejemplos anteriores

los resultados serán iguales a los mostrados en el grupo de consultas de Left Outer Join.

Script: select c.country_name, l.city from locations l right outer join countries c on l.country_id =

c.country_id where c.country_name like 'A% ' ;

Script2: select country_name, city from locations natural right outer join countries where

countries.country_name like 'A% '

Script3: select country_name, city from locations right outer join countries using ( country_id ) where


Script4 : select c.country_name, l.city from locations l, countries c where c.country_id = l.country_id

(+)and c.country_name like 'A% ' ;

Full Outer Join

l) Mostrar los empleados y sus departamentos y los demas departamentos aunque no

posean empleados en ellos.

Script: select e.employee_id, e.last_name, d.department_id, d.department_name from

employees e full outer join departments d on e.department_id = d.department_id;

Script2: select e.employee_id, e.last_name, d.department_id, d.department_name from

employees e, departments d where e.department_id(+) = d.department_id union select

e.employee_id, e.last_name, d.department_id, d.department_name from employees e,

departments d where e.department_id = d.department_id(+);

El resultado de estas dos consultas es el mismo, y se muestra a continuación.


Ciclo II-2012 12


Otras Consultas Multitablas

Autoreferenciadas (Self-joins)

m) Mostrar los Empleados(apellido) con su Jefe(apellido)

Script: select e.last_name Employee, m.last_name Manager from employees e, employees m

where m.employee_id = e.manager_id;

Script2: select e.last_name Employee, m.last_name Manager from employees e inner join employees m

on m.employee_id = e.manager_id;

Estas dos consultas muestran una lista de los empleados y sus respectivos jefes.

n) Mostrar los Empleados(apellido) con su Jefe(apellido) cuando el sueldo del

empleado sea superior al del jefe

Script: select e.last_name Employee, e.salary Employee_sal, m.last_name Manager, m.salary

Manager_sal from employees e, employees m

where e.manager_id =m.employee_id and e.salary> m.salary;


Ciclo II-2012 13


Consultas Basadas en Desigualdades (Nonequality Joins)

En esquema Scott

o) Mostrar los nombres, salario y grado correspondiente de los empleados segun el salario(sal), es

decir si el salario(sal) esta entre el minimo(losal) y maximo(hisal) de su grado. Muestre los

rangos si lo desea.

Script: Select Ename, Grade,Sal,Losal, Hisal From Emp, Salgrade where sal between losal and hisal;

Operadores de Conjuntos La siguiente tabla describe los diferentes operadores de conjuntos.

Operador Descripción

UNION Retorna todas la filas únicas seleccionas por las consultas.

UNION ALL Retorna todas las filas (incluidas las duplicadas)

seleccionadas por las consultas.

INTERSECT Retorna las filas seleccionadas por ambas consultas.


Ciclo II-2012 14


MINUS Retorna las filas únicas seleccionadas por la primera

consulta, pero que no son seleccionadas por la segunda

consulta.

Cambie el formato de fechas a DD-Mon-YYYY

Script: alter session set nls_date_format='DD-Mon-YYYY';

p) Muestre los empleados(con su apellidos y fecha de ingreso a la empresa), que pertenecen al

departamento 90.

Script: select last_name, hire_date from employees where department_id = 90;

q) Muestre los empledos(con su apellidos y fecha de ingreso a la empresa), cuyo apellido empieza

con la letra K

Script: select last_name, hire_date from employees where last_name like 'K% ';


Ciclo II-2012 15


La operador UNION es usado para retornar las filas de ambas consultas pero sin

considerar las duplicadas.

r) Muestre a los empleados (con su apellidos y fecha de ingreso a la empresa), que pertenecen al

departamento 90 UNIDO a los empleados(apellidos y fecha de ingreso) cuyo apellido empieza

con la letra K.

(consultas p y q unidas)

Script: select last_name, hire_date from employees where department_id = 90

UNION

select last_name, hire_date from employees where last_name like 'K% ';

Pruebe con la misma estructura(consultas p y q) las operaciones INTERSECT y MINUS


Ciclo II-2012 16


Clausula WITH Sintaxis

WITH

nombre_subconsulta

AS

(El sql que se materializará)

SELECT

(SQL en el que se usará la subconsulta);

s) Muestre los datos de los empleados que son jefes(código,nombre,

apellido y salario)

Script: With Codigosjefes

As (Select Distinct Manager_Id Codjefe From Employees)

Select Employee_Id,First_Name, Last_Name, Salary From

Employees,CodigosJefes WHERE Employee_Id=codjefe;


Ciclo II-2012 17


t) Muestre los datos de los empleados que ganan más que su jefe

Script:

With Jefes As

(select employee_id codjefe,last_name apejefe,first_name nomjefe, salary saljefe

From Employees Where Employee_Id In (Select Distinct Manager_Id From Employees))

Select Employee_Id,First_Name, Last_Name, Salary,Codjefe,Apejefe,Nomjefe,Saljefe

From Employees,Jefes

Where Employees.manager_id=jefes.Codjefe and Employees.salary>jefes.saljefe;

u) Encuentre todos los departamentos(codigos) donde el salario

total es mayor que el promedio del salario total de todos los

departamentos.

Script: with dept_total (department_id, value) as

(Select Department_Id, Sum(Salary) from employees group by department_id),

dept_total_avg(value) as (select avg(value) from dept_total)

select department_id from dept_total, dept_total_avg where dept_total.value >

dept_total_avg.value;


Ciclo II-2012 18


Guía de Laboratorio #5a

Documents

Transcript of Guía de Laboratorio #5a