Laboratorios de BASES DE DATOS. (I.T. Informática)chernan/Bases/Laboratorio/LABS3.pdf ·...

Laboratorios de BASES DE DATOS. (I.T. Informática)

Gestor de bases de datos ORACLE

M. Martínez, C. Hernández, C. CuestaDpto. de Informática (U. Valladolid)

BD (I.T.Informática) M. Martínez, C. Hernández, C. Cuesta 2

Base de datos de ejemplo

EMPLEADOS (NOMBRE, APELLIDOS, NSS, FNAC, DIRECCION, SEXO, SALARIO, SUPERNSS, DEPNO)

DEPARTAMENTOS (NOMBRE, DEPNO, JEFENSS, FJEFE)

LOCALIZACIONES (DEPNO, LOCALIDAD)

PROYECTOS (NOMBRE, PROYNO, LOCALIDAD, DEPNO)

TRABAJAR (NSS, PROYNO, HORAS)

FAMILIARES (NSS, NOMBRE, SEXO, FNAC, RELACION)


Bloque 3

Subconsultas con EXISTS.Agrupación de filas (cláusula GROUP BY).Consultas con varias tablas.Manipulación de fechas. Actualizaciones en las tablas.División con SQL.Consultas propuestas.


Subconsultas con EXISTS

Se utiliza, como siempre, dentro de la cláusula WHERE.El predicado que se construye toma el valor VERDADERO, si la tabla resultado de la subconsulta contiene alguna tupla.Es habitual que en la subconsulta se haga referencia a alguna tupla externa a ella.La sintaxis es:

WHERE [NOT] EXISTS (Subconsulta);


Subconsultas con EXISTS

Ejemplo:Seleccionar el nombre de los empleados que

tienen alguna hija.

select nombrefrom empleados Ewhere exists (select * from familiares

where nss = E.nssand relacion = ‘hija’);


Agrupación de filas.Permitir realizar operaciones con grupos de filas, como si fueran filas individuales.Aplicar funciones agregadas a grupos de filas.Sintaxis:

SELECT {<Atributo>, <Funcion(argumento)>}FROM <Tabla(s)>[WHERE <Predicado>]GROUP BY <Atributo(s)>[HAVING <Condición>];


Agrupación de filas.

Ejemplo.Dar el salario medio de cada departamento.

select avg(salario) from empleadoswhere depno=1;select avg(salario) from empleadoswhere depno=4;select avg(salario) from empleadoswhere depno=5;


Agrupación de filas.Ejemplo utilizando la cláusula GROUP BY.

Dar el salario medio de cada departamento.select depno, avg(salario) from empleadosgroup by depno;

1. SQL ordena internamente los empleados según el nº de departamento y los agrupa juntos.

2. La función agregada se aplica a cada uno de estos grupos.3. Se presenta el resultado



Dar el total de horas trabajadas por proyecto.select proyno, sum(horas) from trabajargroup by proyno;

1. Si se utiliza la cláusula GROUP BY, las columnas que aparecen en la SELECT deben aparecer en la GROUP BY o ser funciones agregadas.



De la misma forma que la cláusula WHERE permite seleccionar filas, la HAVING permite seleccionar grupos.

La cláusula HAVING se utiliza para filtrar los grupos que van a aparecer en la consulta resultado.



Dar el salario medio de los departamentos que tienen un salario medio mayor de 33.000.

select depno, avg(salario) from empleadosgroup by depnohaving avg(salario) > 33000;



Propuesta:¿Cuál es la cantidad total que cada departamento

utiliza en pagar el salario de sus empleados masculinos?



Propuesta:¿Cuál es la cantidad total que cada departamento

utiliza en pagar el salario de sus empleados masculinos?

select depno, sum(salario)from empleadoswhere sexo = ‘h’group by depno;



Propuesta:Dar el total de horas trabajadas por cada empleado,

pero sólo de las empleadas que han trabajado en más de un proyecto.


Agrupación de filas.Propuesta:Dar el total de horas trabajadas por cada empleado,

pero sólo de las empleadas que han trabajado en más de un proyecto.

select nss, sum(horas)from trabajarwhere nss in (select nss from empleados

where sexo = ‘m’)group by nsshaving count(*) > 1;


Consultas con varias tablas.

Puede ser necesario realizar consultas donde estén involucradas varias tablas, porque en el resultado deben aparecer campos de varias de ellas.Por ejemplo,

Dar el nombre de cada empleado junto con el nombre del departamento en el que trabaja.

Hay que “reunir” información de dos tablas.



En la cláusula SELECT aparecen los nombres de las columnas que se necesitan en el resultado final, cualificando los nombres de los atributos, si es necesario.

<Tabla>.<Atributo>

SELECT A1, A2, ..., Am



En la cláusula FROM aparecen las tablas que se ven involucradas en la consulta

FROM R1, R2, ..., RN



En la cláusula WHERE debe aparecer una “condición de reunión” que indica el/los atributo/s por los que las tablas deben “reunirse”.

SQL debe comparar las filas de ambas tablas y listar sólo aquellas filas que cumplan la condición y sólo aquellos campos pedidos.

Pueden aparecer otras condiciones de búsqueda.


Consultas sobre varias tablas.Ej: Dar el nombre de cada empleado junto con el nombre del departamento en el que trabaja.

select E.nombre, E.apellidos, D.nombrefrom empleados E, departamentos Dwhere E.depno=D.depno;


Consultas sobre varias tablas.Ej: Listar todos los empleados que trabajan en el departamento de administración.

select empleados.nombre, apellidos from empleados, departamentoswhere empleados.depno=departamentos.depnoand departamentos.nombre=‘Administration’;


Consultas sobre varias tablas.

PROPUESTA:Seleccionar el nombre de los empleados cuyo

salario es mayor que el de algún empleado del departamento número 5



PROPUESTA:Seleccionar el nombre de los empleados cuyo

salario es mayor que el de algún empleado del departamento número 5

select e1.nombre, e1.apellidos, e1.nssfrom empleados e1, empleados e2where e1.salario > e2.salario and

e2.depno = 5;



PROPUESTA:Para cada familiar, dar el nombre y el salario del

empleado y el nombre y fecha de nacimiento del familiar.



PROPUESTA:Para cada familiar, dar el nombre y el salario del

empleado y el nombre y fecha de nacimiento del familiar.

select E.nombre, E.salario, F.nombre, F.fnacfrom empleados E, familiares Fwhere E.nss = F.nssorder by E.nombre


Inserciones en una tabla.Las inserciones en tablas se realizan con INSERT:

INSERT INTO tabla [(columna {, columna...})]

{VALUES (expr|NULL {, expr|NULL...})|subquery}


Inserciones en una tabla.

Insertar elementos en una tablainsert into <tabla> values (<valor_col1>,...);

Ej: Insertar un empleadoinsert into empleados values

(‘Juan’, ‘Alonso Rodriguez’, ‘4662454’,’05-AUG-1962’, ‘Cervantes, 5, 4B’, ‘h’, 1304, null, null);


Actualizaciones en una tabla.Las actualizaciones en tablas se realizan con UPDATE:

UPDATE tablaSET columna = {expr | NULL | (subquery)}

{, columna = {expr |NULL | (subquery)...}}[WHERE condicion];


Actualizaciones en una tabla.

Actualizar elementos de una tablaupdate <tabla> set <columna> where<condicion>;

Ej: Actualizar el número de supervisor de todos los empleados que trabajan en el departamento 5

update empleados set supernss=‘123456789’ where depno=5;


Eliminaciones en una tabla.Las eliminaciones en tablas se realizan con DELETE:

DELETE FROM tabla[WHERE condicion];


Actualizaciones en una tabla.

Eliminar elementos de una tabladelete from <tabla> where <condicion>;

Ej: Eliminar todos los empleados que trabajan en el departamento 5

delete from empleadoswhere depno=5;


Sentencias de manipulación de fechas

Consultar la fecha del sistemaselect sysdate from dual;

Devuelve la fecha incrementada en 4 mesesselect add_months(fnac, 4), fnac fromempleados;

Devuelve la diferencia en meses entre dos fechas

select months_between(fnac, sysdate) fromempleados


Sentencias de manipulación de fechas

Recuperar la fecha con un formato distintoselect to_char(<col_fecha>,’formato’) from <tabla>;

Ej: Recuperar la fecha de nacimiento de los empleados

select to_char(fnac, ‘day dd-month-syear’) fromempleados; select to_char(fnac, ‘dd-mm-syear’) fromempleados;


División con SQL.

SQL no proporciona un operador que implemente directamente la división. Debemos buscar una consulta equivalente.

Ej: Encontrar todos los empleados que trabajan en todos los proyectos.

Contestamos planteando la pregunta al revés:

¿Existe algún empleado que no trabaja en alguno de los proyectos de la empresa? De ser así, no estará en el resultado.


División con SQL.

Resolvemos las condiciones desde el interior hacia el exterior:

Suponemos que nos hemos fijado en un empleado y proyecto concretos:

cond1: ¿ocurre que en este proyecto (p.proyno) el empleado (e.nss) NO trabaja?

not exists (select * from trabajar twhere t.nss = e.nss and p.proyno = t.proyno)


División con SQL.Una vez resuelta la primera condición, necesitamos probar

(cond2) que NO existe ningún proyecto que cumpla la condición anterior.

not exits (select p.proyno from proyectos p wher cond1)

Conectamos las dos condiciones cond1 y cond2cond2: not exists (select p.proyno from

proyectos p where not exists (select * from trabajar t

where t..nss = e.nss and p.proyno = t.proyno))

(El empleado e.nss trabaja en todos los proyectos)


División con SQL.Por último, debemos encontrar todos los empleados

(e.nss) que cumplen la condición cond2.select e.nss from empleadoswhere not exists (select p.proyno from

proyectos p wherenot exists (select * from trabajar t

where t.nss = e.nss and p.proyno = t.proyno));


Consultas propuestas.1. Empleados que no tienen familiares2. Empleados que no tienen supervisor3. Suma, máximo y mínimo de los salarios4. Suma, máximo y mínimo de los salarios de

‘Investigación’5. Contar el número de empleados de la compañía6. Empleados que tienen más de dos familiares7. Para cada departamento, mostrar el número de

departamento, total de empleados y salario medio8. Para cada proyecto con más de dos empleados,

número y nombre del proyecto, así como número de empleados


Consultas propuestas.9. Empleados cuya dirección está en Valladolid10. Lista de empleados y proyectos, ordenada.11. Nombre y apellidos de los empleados que más ganan.

Laboratorios de BASES DE DATOS. (I.T. Informática)chernan/Bases/Laboratorio/LABS3.pdf ·...

Documents

Transcript of Laboratorios de BASES DE DATOS. (I.T. Informática)chernan/Bases/Laboratorio/LABS3.pdf ·...