1Curso Bases de Datos 75

EJERCICIO 2Base de datos Empleados

Curso Bases de Datos 76

SUBCONSULTASUna subconsulta es una sentencia SELECT que aparecedentro de otra sentencia SELECT que llamaremos consultaprincipal.La subconsulta (consulta interna o consulta anidada) se ejecutaantes de la consulta principal. Esto no aplica en subconsultascorrelacionadasEl resultado de la subconsulta se usa para ejecutar la consultaprincipalLas subconsultas se pueden ubicar en las clusulas WHERE,HAVING y FROMEl Operador puede ser de dos tipos:De fila simple como >, >=,

2Curso Bases de Datos 77

SUBCONSULTAS (1 FILA)

SELECT salario FROM emple WHERE apellido = 'GIL'

Cul es el salario de GIL?Subconsulta:

Mostrar los empleados que ganan ms que GIL y el salario.SELECT apellido, salario FROM emple WHERE salario >

Consulta Principal:

Curso Bases de Datos 78

SUBCONSULTAS (1 FILA)

Mostrar los empleados que ganan ms que GIL y el salario.

SELECT apellido, salario FROM emple WHERE salario > SELECT salario FROM emple WHERE apellido = 'GIL';

Subconsulta en el WHERE:

3Curso Bases de Datos 79

SUBCONSULTAS (+ 1 FILA)

SELECT salario FROM emple WHERE oficio= VENDEDOR'

Cules son los salarios de los vendedores?Subconsulta:

Mostrar los empleados que ganan ms que todos los VENDEDOR.SELECT apellido, salario FROM emple WHERE salario > ALL

Consulta Principal:

Curso Bases de Datos 80

SUBCONSULTAS (+ 1 FILA)

Mostrar los empleados que ganan ms todos los VENDEDOR.

SELECT apellido, salario FROM emple WHERE salario > ALL (SELECT salario FROM emple WHERE oficio='VENDEDOR');Mostrar los empleados que ganan ms uno de los VENDEDOR.SELECT apellido, salario FROM emple WHERE salario > ANY(SELECT salario FROM emple WHERE oficio='VENDEDOR');

Subconsulta en el WHERE:

4Curso Bases de Datos 81

SUBCONSULTAS (+ 1 FILA) Retornan cero, una o ms filas Los operadores que se usan son: IN Igual a un miembro de la lista ANY Compara el valor con cada valor retornado por la subconsulta hasta encontrar que se cumpla la condicin ALLCompara el valor con todos los valores retornados por la subconsulta

El operador NOT se puede combinar con los anteriores operadores

Curso Bases de Datos 82

SUBCONSULTAS (+ 1 FILA) < ANY significa < (SELECT MAX )

> ANY significa > (SELECT MIN )

= ANY significa lo mismo que IN

< ALL significa < (SELECT MIN )

> ALL significa > (SELECT MAX )

ALL significa lo mismo que NOT IN

5RECOMENDACIONES: Encerrar las subconsultas entre parntesis. Ubicar las subconsultas en el lado derecho de lacondicin de comparacin. Las subconsultas no necesitan una clusula ORDER BY amenos de que se realice una consulta de anlisis TOP-N(los primeros N). Use operadores de fila simple cuando tiene certeza deque se retorna un solo valor, y use operadores demltiples filas cuando la subconsulta retorna ms de unafila.

SUBCONSULTAS

Subconsulta:Consulta Principal: Subconsultas de fila simple

Subconsulta:Consulta Principal: Subconsultas de mltiples filas

Una columna mltiples columnas?

SUBCONSULTAS

SELECT dept_no FROM empleWHERE apellido = 'GIL'

SELECT salario FROM emple WHERE oficio='VENDEDOR'

6Curso Bases de Datos 85

EJERCICIO

Curso Bases de Datos 86

SUM(atributo) : Sumatoria del atributo. MAX(atributo) : Valor mximo del atributo. MIN(atributo) : Valor mnimo del atributo. AVG(atributo) : Valor promedio del atributo. COUNT(atributo | | | | *) : Conteo de tuplas. Se puede usar GROUP BY con estas funciones para consolidar por grupos Se puede usar HAVING para establecer condiciones para los grupos (HAVING es lo que el WHERE es para las tuplas)

Funciones de agregacin:

7Curso Bases de Datos 87

EJERCICIO

Curso Bases de Datos 88

JOINSSe realiza el producto cartesiano de las 2 tablas.

Empleados: 14 registrosDepartamentos: 4 registros

select * from depart d join emple e;select * from depart, emple;

Total 56 registros

8Curso Bases de Datos 89

JOINS INNERSELECT * FROM TablaA INNER JOIN TablaBON TablaA.campo = TablaB.campo

Un inner join producenicamente el conjunto deregistros que estn tanto en laTabla A como en la Tabla B.

Curso Bases de Datos 90

EmpleadoCdigo Nombre Edad Depto

1 Jorge Campos 33 12 Enrique Muoz 25 13 Esteban Paz 21 18 Jorge Arias 30 2

10 Juan Martnez 19 212 Anselmo Rodas 28 6

Depto Descripcin1 Administracin2 Produccin3 Ventas4 Finanzas

Supongamos estas dos relaciones:

Departamento

9Curso Bases de Datos 91

Mediante la clusula INNER JOIN combinada con ON

JOINS

SELECT * FROM empleado INNER JOIN departamento ON empleado.depto = departamento.depto

Otra forma es utilizando la clusula NATURAL JOIN

SELECT * FROM empleado NATURAL JOIN departamento

Curso Bases de Datos 92

Otra forma es comparando los atributos que realizan el join en la clusula WHERE

JOINS

SELECT * FROM empleado, departamentoWHERE empleado.depto = departamento.depto

10

Curso Bases de Datos 93

Se puede emplear la clusula AS para generar alias en las diferentes tablas.SELECT *FROM empleado AS e, departamento AS dWHERE e.depto = d.depto

Nota: El SQL estndar exige siempre el uso de AS, aunque algunos SGBD como Oracle y Access lo suprimen

Renombramiento con AS

Curso Bases de Datos 94

JOINS FULL OUTERSELECT * FROM TablaA FULL OUTER JOIN TablaB ONTablaA.nombre = TablaB.nombre

Full outer join produce elconjunto de todos los recursosde la Tabla A y de la Tabla B,haciendo coincidir los registrosde ambos lados si estndisponibles. Si no haycoincidencias, el lado quefalte, contendr null.

11

Curso Bases de Datos 95

JOINS LEFT OUTERSELECT * FROM TablaA LEFT OUTER JOIN TablaB ONTablaA.nombre = TablaB.nombre

Left outer join reproducetodos los registros de la TablaA, con los registros completos(donde se pueda), de la TablaB. Si no hay coincidencias enel registro, el lado derechocontendr null

Curso Bases de Datos 96

JOINS LEFT OUTER (SLO A)SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name WHERE TablaB.id IS null

Para obtener los registrosque solo estn en la Tabla A,pero no en la Tabla B,realizaremos el mismo leftouter join, y excluimos losregistros de la derecha queno queremos, con where.

12

Curso Bases de Datos 97

JOINS FULL OUTER (NO COMUNES) SELECT * FROM TablaA FULL OUTER JOIN TablaB ON TableA.nombre = TablaB.nombre WHERE TablaA.id IS null OR TablaB.id IS null

En este caso, obtendremos losregistros nicos de la Tabla Ay de la Tabla B. Para lograrlo,ejecutamos el mismo full outerjoin, y luego excluimos losresultados indeseados deambos lados con where.

Curso Bases de Datos 98

JOINS RIGHTLo mismo pero cambiamosLEFT por RIGHT.

13

Curso Bases de Datos 99

EJERCICIO

Curso Bases de Datos 100

Con IN, y con otros operadores como EXISTS, se pueden anidar consultas

Ej: Presentar el ttulo de las pelculas que nunca se han prestado:SELECT DISTINCT pel.titulo FROM pelicula AS pel

WHERE pel.titulo NOT IN (SELECT pel.tituloFROM pelicula AS pel, prestamo AS pr, copia AS coWHERE pr.codcopia=co.codcopia AND co.codpeli=pel.codpeli)

Anidamiento de consultas

14

Curso Bases de Datos 101

Otra manera de realizar la anterior consulta, que evita la realizacin de uno de los JOIN es:

SELECT DISTINCT pel.tituloFROM pelicula AS pelWHERE pel.codpeli NOT IN (

SELECT co.codpeliFROM prestamo AS pr, copia AS coWHERE pr.codcopia=co.codcopia

)

Otra manera

Curso Bases de Datos 102

Calcular el nmero de veces que se ha prestado la pelcula ms prestada.

SELECT MAX(cuenta) AS numero_vecesFROM

( SELECT DISTINCT COUNT(*) AS cuentaFROM pelicula AS pe, prestamo AS pr, copia AS coWHERE pr.codcopia=co.codcopiaAND co.codpeli=pe.codpeliGROUP BY pe.codpeli)

Ejemplo

15

Curso Bases de Datos 103

Insercin de datos: INSERTINSERT INTO departamento VALUES(1,Administracin')

Borrado de datos: DELETEDELETE

FROM empleadoWHERE codigo = 10;