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)

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

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.

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

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);

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

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’);

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

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>];

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

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;

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

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

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

Agrupación de filas.

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.

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

Agrupación de filas.

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.

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

Agrupación de filas.

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;

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

Agrupación de filas.

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

utiliza en pagar el salario de sus empleados masculinos?

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

Agrupación de filas.

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;

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

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.

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

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;

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

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.

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

Consultas con varias 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

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

Consultas con varias tablas.

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

FROM R1, R2, ..., RN

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

Consultas con varias tablas.

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.

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

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;

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

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’;

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

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

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

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

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

e2.depno = 5;

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

Consultas sobre varias tablas.

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

empleado y el nombre y fecha de nacimiento del familiar.

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

Consultas sobre varias tablas.

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

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

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

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

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

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

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);

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

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

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

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

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

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;

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

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

DELETE FROM tabla[WHERE condicion];

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

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;

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

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

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

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;

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

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.

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

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)

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

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)

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

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));

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

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

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

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.