tema 2=arquitectura de tres capas de internet=capa-de-datos-v1.3
Post on 31-Jan-2016
232 Views
Preview:
DESCRIPTION
TRANSCRIPT
CAPA DE DATOS– Bases de Datos
CAPA DE DATOS
El Lenguaje SQL
Sandra Romero (sandra.romerohh@gmail.com) Agosto 2015
- Objetivos • Entender el propósito e importancia del
Lenguaje de Consulta Estructurado SQL.
• Como recuperar datos de una base de
datos usando la instrucción SELECT
o Uso de condición WHERE compuesta.
o Ordenando resultados usando ORDER BY.
- Objetivos o Uso de funciones agregadas.
o Agrupando datos usando GROUP BY y HAVING.
o Uso de Subconsultas(subqueries.)
o Uniendo Tablas (Join tables together.)
o Realizando operaciones de conjunto (UNION, INTERSECT, EXCEPT).
- Objetivos
• Como realizar actualizaciones a la Base de datos
usando INSERT, UPDATE y DELETE
© Pearson Education Limited 1995, 2005
Objetivos de SQL • Idealmente un lenguaje de base de datos
debería permitir a un usuario: o Crear la base de datos y las estructuras de
relaciones
o Realizar tareas de administración de datos básicas,
o Realizar consultas simples y complejas
o Debe realizar estas tareas con mínimo esfuerzo del usuario
o Su estructura de comandos y sintaxis debe ser fácil de aprender
o Debe ser portable
Objetivos de SQL • SQL es un lenguaje orientado a transformación
con dos principales componentes: o Un DDL para definir la estructura de la base de datos y
controlar el acceso a los datos;
o Un DML para recuperar y actualizar datos
• Hasta 1999 SQL no contenía comando para
control de flujo, tales como IF…THEN…ELSE, GO
TO, o DO … WHILE. Estos tenían que ser
implementados usando lenguajes de
programación o Job Control Lenguaje, o
interactivamente por las decisiones del usuario.
Objetivos de SQL
• SQL es un lenguaje relativamente fácil de
aprender: o Es un lenguaje no procedural: se especifica que información se requiere,
mas que, como obtenerla,
o Es un lenguaje esencialmente de formato libre
Objetivos de SQL
• Consiste de palabras en ingles tales como :
1) CREATE TABLE Empleado(
numEmpleado VARCHAR(5), nombre VARCHAR(15),
salario DECIMAL(7,2));
2) INSERT INTO Empleado VALUES (‘SG16’, ‘Brown’, 8300);
3) SELECT numEmpleado, nombre, salario
FROM Empleado
WHERE salario > 10000;
Objetivos de SQL
• Puede ser usado por un rango de usuarios DBAs,
administradores, desarrolladores de aplicaciones,
y otros tipos de usuarios finales.
• Existe un estándar ISO para SQL, haciéndolo el
lenguaje estándar formal y de hecho para bases
de datos relacionales
Historia de SQL
• En 1974, D. Chamberlin (IBM San Jose Laboratory)
definió un lenguaje llamado ‘Structured English
Query Language’ (SEQUEL).
• Una versión revisada, SEQUEL/2, fue definida en
1976 pero el nombre fue posteriormente cambiado
a SQL por razones legales.
Historia de SQL • Aun pronunciado ‘see-quel’, a pesar que la
pronunciación oficial es ‘S-Q-L’.
• IBM posteriormente produjo un Prototipo de DBMS
llamado System R, basado en SEQUEL/2.
• Las raíces de SQL, están en SQUARE (Specifying
Queries as Relational Expressions), el cual es
anterior al proyecto System R.
Historia de SQL
• Al final de los 70, aparecía ORACLE y fue probablemente el primer RDBMS basado en SQL
• En 1987, ANSI e ISO publicaron un estándar inicial para SQL.
• En 1989, ISO publicó un anexo que definió las características de mejoras de integridad.
• En 1992, ocurrió la primera revisión principal del estándar ISO, referido como SQL2 o SQL/92.
• En 1999, SQL:1999 fue liberado con soporte para administración de datos orientado a objeto.
• Al final de 2003, fue liberado SQL:2003.
Importancia de SQL
• SQL pasa a formar parte de la arquitectura de
aplicaciones tales como IBM’s Systems Application Architecture.
• Es una opción estratégica de muchas
organizaciones grandes e influyentes (Ej:
X/OPEN).
• SQL es el estándar federal para el tratamiento
de la información (FIPS) del cual se requiere la
conformidad para todas las ventas de bases de
datos al gobierno americano.
Importancia de SQL
• El SQL se utiliza en otros estándares e incluso
influencia el desarrollo de otros estándares como
herramienta definicional. Los ejemplos incluyen: o ISO’s Information Resource Directory System (IRDS) Standard
o Remote Data Access (RDA) Standard.
Escribiendo Comandos SQL
• La instrucción SQL consiste de palabras
reservadas y palabras definidas por el
usuario. o Las palabras reservadas son una parte fija del
lenguaje SQL y tiene un significado fijo. Ellas
tienen que ser deletreadas exactamente como
se requiere
o Las palabras definidas por el usuario son creadas por el usuario y representan los nombres de
varios objetos de la base de datos tales como
tablas, columnas, vistas, índices, entre otros.
Escribiendo Comandos SQL
• La mayoría de los componentes de una instrucción SQL son insensible mayúsculas / minúsculas, a excepción de los datos de carácter literales.
• Son mas legibles con indentación y alineación:
o Cada cláusula debería comenzar en una nueva línea.
o El comienzo de una cláusula debe alinearse con el comienzo de otras cláusulas.
o Si la cláusula tiene varias partes, cada una debe aparecer en una línea separada e indentada bajo comienzo de la cláusula.
Escribiendo Comandos SQL
• Uso de la notación BNF extendida:
o Las letras mayúsculas representan palabras reservadas.
o Las letras minúsculas representan palabras
definidas por el usuario.
o | indica una elección entre alternativas.
o {} Indican un elemento requerido.
o [] Indican un elemento opcional.
o … Indica repetición opcional (0 or mas).
Literales
• Los literales son constantes usadas en sentencias
SQL.
• Todos los literales no numéricos deben estar
encerrados en comilla simple (ej. ‘London’).
• Todos los literales numéricos no deben estar
encerrados en comillas (ej. 650.00).
Instrucción SELECT SELECT [DISTINCT | ALL]
{* | [columnExpression [AS newName]] [,...] }
FROM NombreTabla [alias] [, ...]
[WHERE condición]
[GROUP BY listaColumna]
[HAVING condición]
[ORDER BY listaColumna]
Instrucción SELECT FROM Especifica la o las tabla(s) a ser usadas
WHERE Filtra filas.
GROUP BY Forma grupos de filas con el mismo valor de columna.
HAVING Filtra grupos sujeto a la misma condición.
SELECT Especifica que columnas van a aparecer en la salida
ORDER BY Especifica el orden de la salida.
Instrucción SELECT
• El orden de las cláusulas no puede ser cambiado.
• Solamente SELECT y FROM son mandatario.
© Pearson Education Limited 1995, 2005
Ejemplo 5.1 Todas las columnas, Todas las
filas de una tabla
Crear una base llamada TPI y cargar el script llamado: TABLA-PARA-EJERCICIOS-CLASE. sql
• Podemos usar * Como una abreviación para ‘todas las columnas’: SELECT *
FROM tabla;
Esta consulta aplica solo para ambientes de desarrollo.
Ejemplo 5.1 Todas las columnas, Todas las
filas de una tabla
numEmpleado nombre apellido cargo sexo fechNac salario numOficina
SL21 Jhon White Gerente M 01-Oct-45 300000 B005
SG37 Peter Denver Asistente M 10-Nov-60 120000 B003
SG14 David Ford Supervisor M 09-Sep-58 180000 B003
SA9 Mary Lee Asistente F 17-Sep-59 90000 B007
SG5 Susan Sarandon Gerente F 21-Mar-60 240000 B003
SL41 Julie Roberts Asistente F 13-Jun-63 90000 B005
SELECT nombre de columnas
FROM nombre de tabla
Ejemplo 5.2 Recuperar columnas especificas,
todas las filas Producir una lista de placas para todos los ingenios, mostrando solamente, placa, Codigo de ingenio, fecha de registro y comentario siempre y cuando la fecha de ingreso sea mayor a:
06/09/2012
;
Ejemplo 5.3 Uso de DISTINCT
Listar el numero de placas de todos las ingenios
Sintaxis de distinct
SELECT DISTINCT campo
FROM tabla;
Ejemplo 5.3 Uso de DISTINCT
Tabla 1-1 Tabla resultado con duplicados
numPropiedad
IA14
IG4
IG4
IA14
IG36
numPropiedad
IA14
IG4
IG36
Tabla 1-2 Tabla resultado eliminando duplicados
SELECT campo
FROM tabla;
SELECT DISTINCT campo
FROM tabla;
Ejemplo 5.4 Campos calculados Listar las cantidad de placas mensual para todos
los ingenios, mostrando solamente, placa,
Código de ingenio, fecha de registro y
comentario
© Pearson Education Limited 1995, 2005
Ejemplo 5.4 Campos calculados • Para nombrar columnas se puede usar la
clausula AS o no.
SELECT numEmpleado, nombre, apellido, salario/12 AS salarioMensual
FROM Empleado;
numEmpleado nombre apellido salarioMensual
SL21 Jhon White 25000
SG37 Peter Denver 10000
SG14 David Ford 15000
SA9 Mary Lee 7500
SG5 Susan Sarandon 20000
SL41 Julie Roberts 7500
Ejemplo 5.5 Comparación en la Condición de
búsqueda
Ejercicio:
Listar todos las placas del ingenio La Azul cuyo id sea mayor 57009
numEmpleado nombre apellido salario
SL21 Jhon White 300000
SG37 Peter Denver 120000
SG14 David Ford 180000
SG5 Susan Sarandon 240000
Ejemplo
SELECT numEmpleado, nombre, apellido, salario
FROM empleado
WHERE salario > 100000;
Ejemplo 5.6 Comparación
compuesta en la Condición de búsqueda
Listar todas las placas del ingenio Central
Izalco
Ejemplo 5.7 Condición Rango de
Búsqueda
Ejercicio
Listar todo las placas cuya fecha de registro este entre 2012-12-01 y 2012-12-15
numEmpleado nombre apellido cargo salario
SL21 Jhon White Gerente 300000
SG5 Susan Sarandon Gerente 240000
SELECT numEmpleado, nombre, apellido, cargo, salario
FROM Empleado
WHERE salario BETWEEN 200000 AND 300000;
• La condición BETWEEN incluye los extremos del rango
Ejemplo 5.7 Condición Rango de Búsqueda
• BETWEEN no agrega mucho poder expresivo a SQL. También podríamos escribir: SELECT numEmpleado, nombre, apellido, cargo, salario
FROM Empleado
WHERE salario>=20000 AND salario <= 30000;
• Útil aunque para un rango de valores.
MANEJO DE FECHAS EN MYSQL • Funcion STR_TO_DATE y DATE_FORMAT • Sintanxis: STR_TO_DATE(a.FechaFinalRealizacion,'%Y-%m-%d')>='2015-05-01 DATE_FORMAT(fechaalta,'%d/%m/%Y')'
Ejemplo 5.8 pertenencia de conjuntos (IN/NOT
IN)
SELECT numEmpleado, nombre, apellido, cargo
FROM Empleado
WHERE cargo IN (‘Gerente’, ‘Supervisor’)
numEmpleado nombre apellido cargo
SL21 Jhon White Gerente
SG14 David Ford Supervisor
SG5 Susan Sarandon Gerente
Ejercicios: Listar todos los Vendedores y Jefes de la tabla empleados
Ejemplo 5.8 pertenencia de conjuntos
(IN/NOT IN) • También hay una versión negada NOT IN.
• IN no agrega mucho poder expresivo a SQL.
También podríamos escribir:
SELECT numEmpleado, nombre, apellido, cargo FROM Empleado WHERE cargo = ‘Gerente’ OR cargo = ‘Supervisor’);
• IN es más eficiente cuando el conjunto tiene muchos valores
Funciones de fechas
-DATE_ADD(fechahora, INTERVAL tipo expresión fechahora) :
select date_add('2007-1-14', interval 15 day);
- SELECT DAYNAME('2007-01-04')
–DATEDIFF(expression1,expression2) :
select datediff('2007-2-6 17:33:25','2007-1-1');
Link donde se puede ver el resto de funciones de MYSQL para tratar fechas:
–https://www.hscripts.com/es/tutoriales/mysql/fecha-hora-functiones.php
Ejemplo 5.9 Patrón de caracteres
• SQL tiene dos símbolos especiales para expresar
modelos:
o El caracter porcentaje % representa cualquier secuencia
de cero o más caracteres
o El caracter underscore _ representa cualquier carácter
unitario.
• LIKE ‘%Maria%’ significa una secuencia de
caracteres de cualquier longitud
conteniendo el string ‘Maria’.
Ejemplo 5.10 Condición de búsqueda NULL
numCliente numPropiedad Fecha Comentario
Q56 IA14 24-11-1999 muy pequeño
Q76 IG4 20-10-1999 muy lejos
Q56 IG4 26-11-1999
Q62 IA14 14-11-1999 no tiene salón
Q56 IG36 28-10-1999
La Tabla Visita contiene el detalle de todas las visitas a las propiedades.
Ejemplo 5.10 Condición de búsqueda NULL
Ejercicios:
Listar el detalle de todas los empleados con la modalidad ‘ENTRENAMIE’ y el Idempleado sea vacío.
Ejemplo 5.10 Condición de búsqueda NULL
• La versión negada (IS NOT NULL) puede verificar por valores no-null.
numCliente numPropiedad Fecha ComentarioQ56 IG4 26-11-1999
Ejemplo 5.11 Ordenando por una columna
Listar los sueldos para todo el personal, dispuestos
en orden descendente del sueldo.
SELECT numEmpleado, nombre, apellido, salario
FROM Empleado
ORDER BY salario DESC;
Ejemplo 5.11 Ordenando por una columna
numEmpleado nombre apellido salario
SL21 Jhon White 300000
SG5 Susan Sarandon 240000
SG14 David Ford 180000
SG37 Peter Denver 120000
SA9 Mary Lee 90000
SL41 Julie Roberts 90000
SELECT numEmpleado, nombre, apellido, salario
FROM Empleado
ORDER BY salario DESC;
Instrucción SELECT - Agregadas
• El estándar ISO define cinco funciones agregadas:
Función Descripción
AVG Calcula el promedio de los valores de un campo determinado
COUNT Devuelve el número de registros de la seleccionados
SUM Devuelve la suma de todos los valores de un campo determinado
MAX Devuelve el valor más alto de un campo especificado
MIN Devuelve el valor más bajo de un campo especificado
Instrucción SELECT - Agregadas
• Cada uno funciona sobre un sola columna
de una tabla y devuelve un solo valor.
• COUNT, MIN, y MAX se aplican a los campos
numéricos y no numéricos, pero SUM y AVG
se pueden utilizar en campos numéricos
solamente.
• Aparte de COUNT(*), cada función elimina
los nulos primero y opera solamente en
valores no nulos restantes.
Instrucción SELECT - Agregadas
• COUNT(*) cuenta todas las filas de una
tabla, sin importar si ocurren valores nulos o
duplicados.
• Puede utilizar DISTINCT antes del nombre de
la columna para eliminar los duplicados.
• DISTINCT no tiene efecto con MIN/MAX,
pero puede tener con SUM/AVG.
Instrucción SELECT - Agregadas
• Las funciones agregadas se pueden utilizar solamente en lista SELECT y en la cláusula HAVING.
• Si la lista SELECT incluye una función agregada y no hay una cláusula GROUP BY, la lista SELECT no puede referirse a una columna hacia fuera con una función agregada. Por ejemplo, lo que sigue es ilegal:
SELECT staffNo, COUNT(salary)
FROM Staff;
Ejemplo 5.13 Uso de COUNT(*)
Ejercicios
¿Cuántos empleados son mayor de 18 años?
Ejemplo 5.16 Uso de MIN, MAX y AVG
Determinar el sueldo mínimo, máximo y el promedio de los empleados.
numEmpleado nombre apellido cargo sexo fechNac salario numOficina
SL21 Jhon White Gerente M 01-Oct-45 300000 B005
SG37 Peter Denver Asistente M 10-Nov-60 120000 B003
SG14 David Ford Supervisor M 09-Sep-58 180000 B003
SA9 Mary Lee Asistente F 17-Sep-59 90000 B007
SG5 Susan Sarandon Gerente F 21-Mar-60 240000 B003
SL41 Julie Roberts Asistente F 13-Jun-63 90000 B005
Tabla Empleado
Ejemplo 5.16 Uso de MIN, MAX y AVG
Determinar el sueldo mínimo, máximo y el
promedio de los empleados.
myMin myMax myProm
90000 300000 170000
SELECT MIN(salary) AS myMin,
MAX(salary) AS myMax,
AVG(salary) AS myAvg
FROM Staff;
Instrucción SELECT - Agregación
• Use la cláusula GROUP BY para obtener
sub-totales.
• El SELECT y GROUP BY son fuertemente
integrados: cada item en la lista SELECT
debe tener un solo valor por grupo, y la
cláusula SELECT puede contener
solamente: o Nombre de columnas
o Funciones agregadas
o constantes
o Expresiones involucrando combinaciones de las
anteriores.
Instrucción SELECT - Agregación
• Todos los nombres de las columnas en la lista
SELECT deben aparecer en la cláusula GROUP BY a
menos que el nombre se utilice solamente en una
función agregada.
• Si WHERE se utiliza con el GROUP BY, WHERE se
aplica primero, después los grupos se forman de
las filas restantes que satisfacen el predicado.
• La ISO considera dos Null como iguales para los
propósitos del GROUP BY.
Restringiendo los grupos –
Clausula HAVING
• La cláusula HAVING es diseñada para ser utilizada
con la cláusula GROUP BY para restringir los grupos
que aparecen en la tabla final.
• HAVING es similar a WHERE, pero WHERE filtra filas
individuales mientras que HAVING filtra grupos.
• Los nombres de la columna en la cláusula HAVING
deben también aparecer en la lista GROUP BY o
estar contenido dentro de una función agregada.
Example 5.18 Use of HAVING
Para cada oficina con más de 1 empleado, encuentre el número de empleados en cada oficina y la suma de sus sueldos.
numEmpleado nombre apellido cargo sexo fechNac salario numOficina
SL21 Jhon White Gerente M 01-Oct-45 300000 B005
SG37 Peter Denver Asistente M 10-Nov-60 120000 B003
SG14 David Ford Supervisor M 09-Sep-58 180000 B003
SA9 Mary Lee Asistente F 17-Sep-59 90000 B007
SG5 Susan Sarandon Gerente F 21-Mar-60 240000 B003
SL41 Julie Roberts Asistente F 13-Jun-63 90000 B005
Tabla Empleado
Example 5.18 Use of HAVING
Para cada unidad con más de 2 registros, encuentre el número de unidades en cada estado
Tabla rxlog
ESTADO CUANTOS HAY
ARMED 4
Agrupar todas las unidades por departamento y municipio
Tabla rxlog
Ejemplo 5.18 Uso de HAVING SELECT branchNo,
COUNT(staffNo) AS myCount,
SUM(salary) AS mySum
FROM Staff
GROUP BY branchNo
HAVING COUNT(staffNo) > 1
ORDER BY branchNo;
numOficina myCount mySum
B003 3 540000
B005 2 390000
Subconsultas
• Algunas instrucciones SQL pueden tener un SELECT
incrustado dentro de él.
• Un subselect puede ser usado en una cláusula
WHERE y HAVING de un SELECT externo, donde se
llama una subquery o consulta anidada.
• Los Subselects pueden también aparecer en las
instrucciones INSERT, UPDATE, y DELETE.
Ejemplo 5.19 Usando una subconsulta con
Igualdad
Listar los empleados que trabajan en la oficina ubicada en ‘163 Main Street’
numEmpleado nombre apellido cargo sexo fechNac salario numOficina
SL21 Jhon White Gerente M 01-Oct-45 300000 B005
SG37 Peter Denver Asistente M 10-Nov-60 120000 B003
SG14 David Ford Supervisor M 09-Sep-58 180000 B003
SA9 Mary Lee Asistente F 17-Sep-59 90000 B007
SG5 Susan Sarandon Gerente F 21-Mar-60 240000 B003
SL41 Julie Roberts Asistente F 13-Jun-63 90000 B005
numOficina calle ciudad codigoPostal
B005 16 Holhead Aberdeem AB7 5SU
B007 6 Argvill St. London NW2
B003 163 Main Street Glasgow G11 9QX
B004 2 Manor Rd Glasgow G114QX
B002 10 Dale Rd Bristol G12
Tabla Oficina
Tabla Empleado
Ejemplo 5.19 Usando una subconsulta con
Igualdad
SELECT numEmpleado, nombre, apellido, cargo
FROM Empleado
WHERE numOficina = (SELECT numOficina
FROM Oficina
WHERE calle = ‘163 Main St’);
Listar los empleados que trabajan en la oficina ubicada en ‘163 Main Street’
Ejemplo 5.19 Usando una subconsulta con
Igualdad
• El SELECT interno determina el numero de oficina para
la oficina que se encuentra en ‘163 Main St’ (‘B003’).
• El SELECT externo recupera el detalle de todos los
empleados que trabajan en esa oficina.
• El SELECT externo entonces se convierte en:
SELECT numEmpleado, nombre, apellido, cargo
FROM Empleado
WHERE numOficina = ‘B003’;
Ejemplo 5.19 Usando una subconsulta con
Igualdad
numEmpleado nombre apellido cargo
SG37 Peter Denver Asistente
SG14 David Ford Supervisor
SG5 Susan Sarandon Gerente
numEmpleado nombre apellido cargo sexo fechNac salario numOficina
SL21 Jhon White Gerente M 01-Oct-45 300000 B005
SG37 Peter Denver Asistente M 10-Nov-60 120000 B003
SG14 David Ford Supervisor M 09-Sep-58 180000 B003
SA9 Mary Lee Asistente F 17-Sep-59 90000 B007
SG5 Susan Sarandon Gerente F 21-Mar-60 240000 B003
SL41 Julie Roberts Asistente F 13-Jun-63 90000 B005
numOficina calle ciudad codigoPostal
B005 16 Holhead Aberdeem AB7 5SU
B007 6 Argvill St. London NW2
B003 163 Main Street Glasgow G11 9QX
B004 2 Manor Rd Glasgow G114QX
B002 10 Dale Rd Bristol G12
Tabla Oficina
Tabla Empleado
Listar los empleados que trabajan en la oficina ubicada en ‘163 Main Street’
Ejemplo 5.20 subconsulta con una función
agregada.
Listar a todos los empleados cuyo sueldo sea
mayor que el sueldo promedio, e indique por
cuánto.
numEmpleado nombre apellido cargo sexo fechNac salario numOficina
SL21 Jhon White Gerente M 01-Oct-45 300000 B005
SG37 Peter Denver Asistente M 10-Nov-60 120000 B003
SG14 David Ford Supervisor M 09-Sep-58 180000 B003
SA9 Mary Lee Asistente F 17-Sep-59 90000 B007
SG5 Susan Sarandon Gerente F 21-Mar-60 240000 B003
SL41 Julie Roberts Asistente F 13-Jun-63 90000 B005
Tabla Empleado
Ejemplo 5.20 subconsulta con una función
agregada.
SELECT numEmpleado, nombre, apellido, cargo,
salario - (SELECT AVG(salario)
FROM Empleado) AS Diferencia
FROM Empleado
WHERE salario > (SELECT AVG(salario)
FROM Empleado);
Ejemplo 5.20 subconsulta con una función
agregada.
• No puede escribir ‘WHERE salario > AVG(salario)’
• En lugar, utilice un subquery para encontrar el
sueldo promedio (170000), y después utilice un
SELECT externo para encontrar ésos empleados
con sueldo mayores que este:
SELECT numEmpleado, nombre, apellido, cargo,
salario - 170000 AS Diferencia
FROM Empleado
WHERE salario > 170000);
Ejemplo 5.20 subconsulta con una función
agregada.
numEmpleado nombre apellido cargo Diferencia
SG14 David Ford Supervisor 10000
SG5 Susan Sarandon Gerente 70000
SL21 John White Gerente 130000
numEmpleado nombre apellido cargo sexo fechNac salario numOficina
SL21 Jhon White Gerente M 01-Oct-45 300000 B005
SG37 Peter Denver Asistente M 10-Nov-60 120000 B003
SG14 David Ford Supervisor M 09-Sep-58 180000 B003
SA9 Mary Lee Asistente F 17-Sep-59 90000 B007
SG5 Susan Sarandon Gerente F 21-Mar-60 240000 B003
SL41 Julie Roberts Asistente F 13-Jun-63 90000 B005
Tabla Empleado
SELECT numEmpleado, nombre, apellido, cargo, salario - (SELECT AVG(salario)
FROM Empleado) AS Diferencia
FROM Empleado
WHERE salario > (SELECT AVG(salario) FROM Empleado);
Reglas de Subconsulta
• La cláusula ORDER BY no se puede utilizar en una
subconsulta (aunque puede ser utilizada en un
SELECT exterior).
• La lista SELECT de una subconsulta debe consistir
en un solo nombre o expresión de columna, a
excepción de las subconsultas que usan EXIST
• Por defecto, los nombres de la columna refieren a
nombre de la tabla adentro de la cláusula del
subquery. Puede referir a una tabla en el FROM
usando un alias.
Subquery Rules • Cuando una subconsulta es uno de los dos
operandos en una comparación, la subconsulta debe aparecer al lado derecho de la comparación .
• Un subconsulta no se puede utilizar como operando en una expresión.
Ejemplo 5.21 Subconsultas anidadas: Uso de IN
Listar las propiedades que son manejadas por los empleados que trabajan en la sucursal de la calle ‘163 Main Street’.
numPropiedad calle ciudad codigoPostal tipo hab renta numPropietario numEmpleado
PA14 16 Holhead Aberdeem AB7 5SU Casa 6 650 C046 SL21
PL94 6 Argvill St. London NW2 Departamento 4 400 C087 SL21
PG4 6 Lawrence St Glasgow G11 9QX Departamento 3 350 C040 SA9
PG36 2 Manor Rd Glasgow G114QX Departamento 3 375 C093 SA9
PG21 10 Dale Rd Glasgow G12 Casa 5 600 C087 SG5
PG16 5 Novar Dr Glasgow G12 9AX Departamento 4 450 C093 SL21
numEmpleado nombre apellido cargo sexo fechNac salario numOficina
SL21 Jhon White Gerente M 01-Oct-45 300000 B005
SG37 Peter Denver Asistente M 10-Nov-60 120000 B003
SG14 David Ford Supervisor M 09-Sep-58 180000 B003
SA9 Mary Lee Asistente F 17-Sep-59 90000 B007
SG5 Susan Sarandon Gerente F 21-Mar-60 240000 B003
SL41 Julie Roberts Asistente F 13-Jun-63 90000 B005
numOficina calle ciudad codigoPostal
B005 16 Holhead Aberdeem AB7 5SU
B007 6 Argvill St. London NW2
B003 163 Main Street Glasgow G11 9QX
B004 2 Manor Rd Glasgow G114QX
B002 10 Dale Rd Bristol G12
Tabla Oficina
Tabla Empleado
Tabla Propiedad
Ejemplo 5.21 Subconsultas anidadas: Uso de IN
Listar las propiedades que son manejadas por los empleados que trabajan en la sucursal de la calle ‘163 Main Street’.
SELECT numPropiedad, calle, ciudad, codigoPostal, tipo, hab, renta
FROM Propiedad
WHERE numEmpleado IN
(SELECT numEmpleado
FROM Empleado
WHERE numOficina =
(SELECT numOficina
FROM Oficina
WHERE calle = ‘163 Main St’));
numPropiedad calle ciudad codigoPostal tipo hab renta
PG21 10 Dale Rd Glasgow G12 Casa 5 600
ANY y ALL • ANY y ALL pueden ser usadas con subconsultas
que producen una sola columna de números
• Si usa ALL, la condición será verdadera si es satisfecha por todos los valores producidos en la subconsulta
• Si usa ANY, la condición será verdadera si es satisfecha por uno o más valores producidos por la subconsulta .
• Si la subconsulta es vacia, ALL retorna verdadero y ANY retorna falso
• SOME mude ser usado en lugar de ANY.
Ejemplo 5.22
Uso de ANY / SOME
Listar todos los empleados cuyo salario es
mayor que el salario de al menos un
empleado de la sucursal B003.
numEmpleado nombre apellido cargo sexo fechNac salario numOficina
SL21 Jhon White Gerente M 01-Oct-45 300000 B005
SG37 Peter Denver Asistente M 10-Nov-60 120000 B003
SG14 David Ford Supervisor M 09-Sep-58 180000 B003
SA9 Mary Lee Asistente F 17-Sep-59 90000 B007
SG5 Susan Sarandon Gerente F 21-Mar-60 240000 B003
SL41 Julie Roberts Asistente F 13-Jun-63 90000 B005
Tabla Empleado
Ejemplo 5.22
Uso de ANY / SOME
Listar todos los empleados cuyo salario es
mayor que el salario de al menos un
empleado de la sucursal B003.
SELECT numEmpleado, nombre, apellido, cargo, salario
FROM Empleado
WHERE salario > SOME (SELECT salario
FROM Empleado
WHERE numOIficina = ‘B003’);
Ejemplo 5.22
Uso de ANY / SOME
• La consulta interna produce el conjunto {120000,
180000, 240000} y la consulta externa selecciona
aquellos empleados cuyos sueldos son mayores
que cualesquiera de los valores en este conjunto.
numEmpleado nombre apellido cargo salario
SL21 Jhon White Gerente 300000
SG14 David Ford Supervisor 180000
SG5 Susan Sarandon Gerente 240000
Tabla resultado del ejemplo 5.22
Ejemplo 5.23 Uso de ALL
Listar todos los empleados cuyo salario es mayor
que el salario de todos los empleados de la
sucursal B003.
numEmpleado nombre apellido cargo sexo fechNac salario numOficina
SL21 Jhon White Gerente M 01-Oct-45 300000 B005
SG37 Peter Denver Asistente M 10-Nov-60 120000 B003
SG14 David Ford Supervisor M 09-Sep-58 180000 B003
SA9 Mary Lee Asistente F 17-Sep-59 90000 B007
SG5 Susan Sarandon Gerente F 21-Mar-60 240000 B003
SL41 Julie Roberts Asistente F 13-Jun-63 90000 B005
Tabla Empleado
Ejemplo 5.23 Uso de ALL
Listar todos los empleados cuyo salario es mayor
que el salario de todos los empleados de la sucursal
B003.
SELECT numEmpleado, nombre, apellido, cargo, salario
FROM Empleado
WHERE salario > SOME (SELECT salario
FROM Empleado
WHERE numOIficina = ‘B003’);
numEmpleado nombre apellido cargo salario
SL21 Jhon White Gerente 300000
Tabla resultado del ejemplo 5.23
Consultas multi-tablas • Puede utilizar subqueries que proporcione
columnas de resultado que vienen de la misma tabla.
• Si las columnas de resultado vienen de más de una tabla debe utilizar un join.
• Para realizar el join, incluya más de una tabla en la cláusula FROM.
• Utilice la coma como separador e incluya típicamente la cláusula WHERE para especificar la(s) columna(s) del join.
Consultas multi-tablas
• También es posible utilizar un alias para una tabla
nombrada en la cláusula FROM.
• El Alias es separado del nombre de la tabla con
un espacio.
• El alias se puede utilizar para calificar nombres de
la columna cuando hay ambigüedad.
Ejemplo 5.24 Join simple
Listar los nombres de todos los clientes que han
visitado una propiedad y los comentarios efectuados
al visitarla.
numCliente nombre apellido direccion telefono tipoPref maxRent
CR76 Jhon Kay 56 High ST, Londonn SW1 4EH 0207-774-5632 Departamento 425
CR56 Aline Stewart 64 Fern Dr,. Glasgow, G42 OBL 0141-324-1825 Departamento 350
CR74 Mike Ritchie 63 Well St, Glasgow, G42 0141-943-7420 Casa 750
CR62 Mary Tregear 12 Park PI, Glasgow, G4 0QR 0141-225-7421 Departamento 600
numCliente numPropiedad Fecha Comentario
CR56 PA14 24-11-1999 muy pequeño
CR76 PG4 20-10-1999 muy lejos
CR56 PG4 26-11-1999
CR62 PA14 14-11-1999 no tiene salón
CR56 PG36 28-10-1999
Tabla Cliente
Tabla Visita
Ejemplo 5.24 Join simple Listar los nombres de todos los clientes que han visitado una propiedad y los comentarios efectuados al visitarla.
SELECT c.numCliente, nombre, apellido, numPropiedad, comentario
FROM Cliente c, Visita v
WHERE c. numCliente = v. numCliente;
Ejemplo 5.24 Join simple
• Es equivalente a equi-join en Algebra Relacional.
numCliente nombre apellido numPropiedad Comentario
CR56 Aline Stewart PA14 muy pequeño
CR56 Aline Stewart PG4
CR56 Aline Stewart PG36
CR62 Mary Tregear PA14 no tiene salón
CR76 Jhon Kay PG4 muy lejos
• Solamente las filas de ambas tablas que tengan valores idénticos en las columnas numCliente (c.numCliente = v.numCliente) se incluyen en el resultado.
Tabla resultado ejemplo 5.24
Constucciones alternativas de JOIN
• SQL provee maneras alternativas para especificar
JOIN:
FROM Cliente c JOIN Visita v ON c.numCliente = v.numCliente
FROM Cliente JOIN Visita USING numCliente
FROM Cliente NATURAL JOIN Visita
• En cada caso, FROM reemplaza el FROM y WHERE
original. Sin embargo, el primero produce una tabla
con dos columnas idénticas de numCliente.
Ejemplo 5.25 Ordenando un join
Para cada oficina, listar los números y nombres de los empleados que administran propiedades y las propiedades que ellos administran.
numPropiedad calle ciudad codigoPostal tipo hab renta numPropietario numEmpleado
PA14 16 Holhead Aberdeem AB7 5SU Casa 6 650 C046 SL21
PL94 6 Argvill St. London NW2 Departamento 4 400 C087 SL21
PG4 6 Lawrence St Glasgow G11 9QX Departamento 3 350 C040 SA9
PG36 2 Manor Rd Glasgow G114QX Departamento 3 375 C093 SA9
PG21 10 Dale Rd Glasgow G12 Casa 5 600 C087 SG5
PG16 5 Novar Dr Glasgow G12 9AX Departamento 4 450 C093 SL21
numEmpleado nombre apellido cargo sexo fechNac salario numOficina
SL21 Jhon White Gerente M 01-Oct-45 300000 B005
SG37 Peter Denver Asistente M 10-Nov-60 120000 B003
SG14 David Ford Supervisor M 09-Sep-58 180000 B003
SA9 Mary Lee Asistente F 17-Sep-59 90000 B007
SG5 Susan Sarandon Gerente F 21-Mar-60 240000 B003
SL41 Julie Roberts Asistente F 13-Jun-63 90000 B005
Tabla Propiedad
Tabla Empleado
Ejemplo 5.25 Ordenando un join
Para cada oficina, listar los números y nombres de los empleados que administran propiedades y las propiedades que ellos administran.
numOficina numEmpleado nombre apellido numPropiedad
B003 SG5 Susan Sarandon PG21
B005 SL21 Jhon White PA14
B005 SL21 Jhon White PG16
B005 SL21 Jhon White PL94
B007 SA9 Mary Lee PG36
B007 SA9 Mary Lee PG4
SELECT e.numOficina, e.numEmpleado, nombre, apellido, numPropiedad
FROM Empleado e, Propiedad p
WHERE e.numEmpleado = p.numEmpleado
ORDER BY e.numOficina, e.numEmpleado, numPropiedad;
Tabla resultado ejercicio 5.25
Ejemplo 5.26
Un join de tres tablas
Para cada oficina, Listar los números y nombres de los empleados que administran propiedades, incluyendo la ciudad en la cual la oficina está ubicada y las propiedades que ellos administran.
numOficina calle ciudad codigoPostal
B005 16 Holhead Aberdeem AB7 5SU
B007 6 Argvill St. London NW2
B003 163 Main Street Glasgow G11 9QX
B004 2 Manor Rd Glasgow G114QX
B002 10 Dale Rd Bristol G12
numEmpleado nombre apellido cargo sexo fechNac salario numOficina
SL21 Jhon White Gerente M 01-Oct-45 300000 B005
SG37 Peter Denver Asistente M 10-Nov-60 120000 B003
SG14 David Ford Supervisor M 09-Sep-58 180000 B003
SA9 Mary Lee Asistente F 17-Sep-59 90000 B007
SG5 Susan Sarandon Gerente F 21-Mar-60 240000 B003
SL41 Julie Roberts Asistente F 13-Jun-63 90000 B005
numPropiedad calle ciudad codigoPostal tipo hab renta numPropietario numEmpleado
PA14 16 Holhead Aberdeem AB7 5SU Casa 6 650 C046 SL21
PL94 6 Argvill St. London NW2 Departamento 4 400 C087 SL21
PG4 6 Lawrence St Glasgow G11 9QX Departamento 3 350 C040 SA9
PG36 2 Manor Rd Glasgow G114QX Departamento 3 375 C093 SA9
PG21 10 Dale Rd Glasgow G12 Casa 5 600 C087 SG5
PG16 5 Novar Dr Glasgow G12 9AX Departamento 4 450 C093 SL21
Tabla Propiedad
Tabla Empleado
Tabla Oficina
Ejemplo 5.26
Un join de tres tablas
Para cada oficina, Listar los números y nombres de los empleados que administran propiedades, incluyendo la ciudad en la cual la oficina está ubicada y las propiedades que ellos administran.
Tabla resultado ejercicio 5.25
numOficina numEmpleado nombre apellido numPropiedad
B003 SG5 Susan Sarandon PG21
B005 SL21 Jhon White PA14
B005 SL21 Jhon White PG16
B005 SL21 Jhon White PL94
B007 SA9 Mary Lee PG36
B007 SA9 Mary Lee PG4
numOficina calle ciudad codigoPostal
B005 16 Holhead Aberdeem AB7 5SU
B007 6 Argvill St. London NW2
B003 163 Main Street Glasgow G11 9QX
B004 2 Manor Rd Glasgow G114QX
B002 10 Dale Rd Bristol G12
Tabla Oficina
Ejemplo 5.26
Un join de tres tablas Para cada oficina, Listar los números y nombres
de los empleados que administran propiedades, incluyendo la ciudad en la cual la oficina está ubicada y las propiedades que ellos administran.
SELECT e.numOficina, e.numEmpleado, nombre, apellido, numPropiedad
FROM Empleado e, Propiedad p, Oficina o
WHERE e.numEmpleado = p.numEmpleado
AND o.numOficina = e.numOficina
ORDER BY e.numOficina, e.numEmpleado, numPropiedad;
Ejemplo 5.26
Un join de tres tablas
• Formulación alternativa para FROM y WHERE:
FROM (Oficina o JOIN Empleado e USING numOficina) AS oe
JOIN Propiedad p USING numEmpleado
numOficina ciudad numEmpleado nombre apellido numPropiedad
B003 Glasgow SG5 Susan Sarandon PG21
B005 Aberdeem SL21 Jhon White PA14
B005 Aberdeem SL21 Jhon White PG16
B005 Aberdeem SL21 Jhon White PL94
B007 London SA9 Mary Lee PG36
B007 London SA9 Mary Lee PG4
Tabla resultado ejercicio 5.26
Ejemplo 5.27 Agrupando por múltiples columnas
Encuentre el total de propiedades
manejadas por cada empleado.
numPropiedad calle ciudad codigoPostal tipo hab renta numPropietario numEmpleado
PA14 16 Holhead Aberdeem AB7 5SU Casa 6 650 C046 SL21
PL94 6 Argvill St. London NW2 Departamento 4 400 C087 SL21
PG4 6 Lawrence St Glasgow G11 9QX Departamento 3 350 C040 SA9
PG36 2 Manor Rd Glasgow G114QX Departamento 3 375 C093 SA9
PG21 10 Dale Rd Glasgow G12 Casa 5 600 C087 SG5
PG16 5 Novar Dr Glasgow G12 9AX Departamento 4 450 C093 SL41
numEmpleado nombre apellido cargo sexo fechNac salario numOficina
SL21 Jhon White Gerente M 01-Oct-45 300000 B005
SG37 Peter Denver Asistente M 10-Nov-60 120000 B003
SG14 David Ford Supervisor M 09-Sep-58 180000 B003
SA9 Mary Lee Asistente F 17-Sep-59 90000 B007
SG5 Susan Sarandon Gerente F 21-Mar-60 240000 B003
SL41 Julie Roberts Asistente F 13-Jun-63 90000 B005
Tabla Propiedad
Tabla Empleado
Ejemplo 5.27 Agrupando por múltiples
columnas
SELECT e.numOficina, s.numEmpleado, COUNT(*) AS myCount
FROM Empleado e, Propiedad p
WHERE e.numEmpleado = p.numEmpleado
GROUP BY e.numOficina, e.numEmpleado
ORDER BY e.numOficina, e.numEmpleado;
Encuentre el total de propiedades manejadas por cada empleado.
Ejemplo 5.27 Agrupando por múltiples
columnas
numOficina numEmpleado myCount
B003 SG5 1
B005 SL21 2
B005 SL41 1
B007 SA9 2
Tabla Resultado del ejemplo 5.27
SELECT e.numOficina, s.numEmpleado, COUNT(*) AS myCount
FROM Empleado e, Propiedad p
WHERE e.numEmpleado = p.numEmpleado
GROUP BY e.numOficina, e.numEmpleado
ORDER BY e.numOficina, e.numEmpleado;
Calculando un Join
El procedimiento para generar resultados de un join es:
1. Forme el producto cartesiano de las tablas nombradas en la cláusula FROM.
2. Si hay una cláusula WHERE, aplique la condición de búsqueda
a cada fila de la tabla del producto, conservando esas filas que satisfagan la condición
3. Para cada fila restante, determine el valor de cada item en
lista SELECT para producir una sola fila en la tabla resultado.
Calculando un Join
4. Si se ha especificado DISTINCT, eliminar cualquier fila
duplicada de la tabla resultado.
5. Si hay una cláusula ORDER BY, se requiere ordenar la tabla resultado.
• SQL provee una forma especial de SELECT para el
producto Cartesiano:
SELECT [DISTINCT | ALL] {* | columnList}
FROM Tabla1 CROSS JOIN Tabla2
Outer Joins
• Si una fila de una de las tablas a unir no
coincide, la fila es omitida de la tabla resultante.
• El outer join retiene las filas que no satisfacen la condición.
• Considere las siguientes tablas:
numPropiedad ciudad
PA14 Aberdeem
PL94 London
PG4 Glasgow
numOficina ciudad
B003 Glasgow
B004 Bristol
B002 London
Tabla Oficina1 Tabla Propiedad1
Outer Joins • El (inner) join de estas dos tablas:
SELECT o.*, p.*
FROM Oficina1 o, Propiedad1 p
WHERE o.ciudad = p.ciudad;
numOficina ciudad numPropiedad ciudad
B003 Glasgow PG4 Glasgow
B002 London PL94 London
Tabla resultado del
inner join tabla Oficina1 con tabla Propiedad1
Outer Joins
• La tabla resultado tiene dos filas donde son iguales
las ciudades.
• No hay filas que corresponden a las oficinas en
Bristol y Aberdeen.
• Para incluir filas incomparables en la tabla resultado,
utilice un join externo (Outer Join).
numOficina ciudad numPropiedad ciudad
B003 Glasgow PG4 Glasgow
B002 London PL94 London
Tabla resultado del
inner join tabla Oficina1 con tabla Propiedad1
Ejemplo 5.28 Left Outer join
Liste las oficinas y las Propiedades que están en la
misma ciudad junto con cualquier oficina
incomparable.
SELECT o.*, p.*
FROM Oficina1 o LEFT JOIN
Propiedad1 p ON o.ciudad = p.ciudad;
numPropiedad ciudad
PA14 Aberdeem
PL94 London
PG4 Glasgow
numOficina ciudad
B003 Glasgow
B004 Bristol
B002 London
Tabla Oficina1 Tabla Propiedad1
Ejemplo 5.28 Left Outer join
• Incluye aquellas filas de la primera tabla (left)
que no coinciden con las filas de la segunda
tabla (derecha) .
• Las columnas de la segunda tabla son
completadas con valores Nulos .
numOficina ciudad numPropiedad ciudad
B003 Glasgow PG4 Glasgow
B004 Bristol NULL NULL
B002 London PL94 London
Tabla resultado del
ejemplo 5.28
Ejemplo 5.29 Right Outer join
Liste los sucursales y las propiedades en la misma ciudad y cualquier propiedad incomparable (ciudad unmatched).
SELECT o.*, p.*
FROM Oficina1 b RIGHT JOIN
Propiedad1 p ON b.ciudad = p.ciudad;
numPropiedad ciudad
PA14 Aberdeem
PL94 London
PG4 Glasgow
numOficina ciudad
B003 Glasgow
B004 Bristol
B002 London
Tabla Oficina1 Tabla Propiedad1
Example 5.29 Right Outer Join • El Right Outer join incluye aquellas filas de la
segunda tabla (derecha) que no coinciden con la las filas de la primera tabla (izquierda).
• Las columnas de la primera tabla son completadas con valores Nulos.
numOficina ciudad numPropiedad ciudad
NULL NULL PA14 Aberdeem
B002 London PL94 London
B003 Glasgow PG4 Glasgow
Tabla resultado del
ejemplo 5.29
Ejemplo 5.30 Full Outer Join
Listar las oficinas y las propiedades en la misma
ciudad y cualquier oficina o propiedad
incomparable
SELECT b.*, p.*
FROM Branch1 b FULL JOIN
PropertyForRent1 p ON b.bCity = p.pCity;
Ejemplo 5.30 Full Outer Join
• Incluye no solamente aquellas filas que tienen
la misma cuidad, si no también aquellas filas de
ambas tablas que no coinciden.
• Las columnas de las tablas que no coinciden
son completadas con Null.
numOficina ciudad numPropiedad ciudad
NULL NULL PA14 Aberdeem
B003 Glasgow PG4 Glasgow
B004 Bristol NULL NULL
B002 London PL94 London
Tabla resultado del
ejemplo 5.30
EXISTS y NOT EXISTS • EXISTS y NOT EXISTS están para ser usados
solamente con subqueries.
• Produce un resultado verdadero/falso.
• Verdadero si y solo si existe por lo menos una fila en la tabla resultado retornada por la subquery.
• Falso si la subconsulta retorna una tabla vacía.
• NOT EXISTS es el opuesto de EXISTS.
EXISTS y NOT EXISTS
• Como (NO) EXISTS verifica solamente por la
existencia o la no-existencia de filas en la tabla
resultado de la subconsulta, la subquery puede
contener cualquier número de columnas.
• Las subconsultas comunes que siguen a (NOT)
EXISTS son de la forma:
(SELECT * ...)
Ejemplo 5.31
Consulta usando EXIST
Listar todos los empleados que trabajan en alguna oficina de Londres.
numEmpleado nombre apellido cargo sexo fechNac salario numOficina
SL21 Jhon White Gerente M 01-Oct-45 300000 B005
SG37 Peter Denver Asistente M 10-Nov-60 120000 B003
SG14 David Ford Supervisor M 09-Sep-58 180000 B003
SA9 Mary Lee Asistente F 17-Sep-59 90000 B007
SG5 Susan Sarandon Gerente F 21-Mar-60 240000 B003
SL41 Julie Roberts Asistente F 13-Jun-63 90000 B005
Tabla Empleado
numOficina calle ciudad codigoPostal
B005 16 Holhead Aberdeem AB7 5SU
B007 6 Argvill St. London NW2
B003 163 Main Street Glasgow G11 9QX
B004 2 Manor Rd Glasgow G114QX
B002 10 Dale Rd Bristol G12
Tabla Oficina
Ejemplo 5.31
Consulta usando EXIST
SELECT numEmpleado, nombre, apellido, cargo
FROM Empleado e
WHERE EXISTS
(SELECT *
FROM Oficina o
WHERE e.numEmpleado = o.numEmpleado
AND ciudad = ‘London’);
Listar todos los empleados que trabajan en alguna oficina de Londres.
Ejemplo5.31
Consulta usando EXIST
numEmpleado nombre apellido cargo
SA9 Mary Lee Asistente
numEmpleado nombre apellido cargo sexo fechNac salario numOficina
SL21 Jhon White Gerente M 01-Oct-45 300000 B005
SG37 Peter Denver Asistente M 10-Nov-60 120000 B003
SG14 David Ford Supervisor M 09-Sep-58 180000 B003
SA9 Mary Lee Asistente F 17-Sep-59 90000 B007
SG5 Susan Sarandon Gerente F 21-Mar-60 240000 B003
SL41 Julie Roberts Asistente F 13-Jun-63 90000 B005
Tabla Empleado
numOficina calle ciudad codigoPostal
B005 16 Holhead Aberdeem AB7 5SU
B007 6 Argvill St. London NW2
B003 163 Main Street Glasgow G11 9QX
B004 2 Manor Rd Glasgow G114QX
B002 10 Dale Rd Bristol G12
Tabla Oficina
Tabla resultado Ejemplo 5.31
SELECT numEmpleado, nombre, apellido, cargo
FROM Empleado e
WHERE EXISTS
(SELECT *
FROM Oficina o
WHERE e.numEmpleado = o.numEmpleado
AND ciudad = ‘London’);
Ejemplo 5.31
Consulta usando EXIST
• Note que la condición de búsqueda e.numOficina = o.numOficina es necesaria para considerar el correcto registro de oficina para cada empleado
• Si se omite, listaría todos los registros de empleado, debido a que la siguiente subconsulta:
SELECT * FROM Oficina WHERE ciudad=‘London’
• Seria siempre verdad y la consulta quedaría:
SELECT numEmpleado, nombre, apellido, cargo
FROM Empleado
WHERE true;
Ejemplo 5.31
Consulta usando EXIST
• Podria también escribir esta consulta a
través de un join:
SELECT numEmpleado, nombre, apellido, cargo
FROM Empleado e, Oficina o
WHERE e.numOficina = o.numOficina
AND city = ‘London’;
Union, Intersect, y
Difference (Except) • Puede utilizar operaciones de conjuntos normales tales
como unión, intersección, y diferencia para combinar
resultados de dos o más consultas en una sola tabla
resultado.
• La unión de dos tablas, A y B, es una tabla que
contiene todas las filas en A o B o ambos.
• La intersección es una tabla que contiene todas las
filas comunes a A y a B.
• La diferencia es una tabla que contiene todas las filas
en A pero no en B.
• Dos tablas deben ser unión compatible.
Union, Intersect, y
Difference (Except) • El formato de la cláusula del operador de conjunto
en cada caso es:
op [ALL] [CORRESPONDING [BY {columna1 [, ...]}]]
• Si CORRESPONDING BY es especificado, entonces la
operación de conjunto es realizada sobre la o las
columnas nombradas
• Si CORRESPONDING es especificado sin el BY, la
operación de conjunto se aplica sobre las
columnas comunes en ambas tablas .
Union, Intersect, y
Difference (Except)
• Si se especifica ALL el resultado puede incluir filas
duplicadas.
• Algunos dialectos no soportan INTERSECT y EXCEPT;
otros usan MINUS en lugar de EXCEPT
Union, Intersect, y
Difference (Except)
Ejemplo 5.32 Uso de UNION
Construya un listado de todas las ciudades en donde hay ya sea una oficina o una propiedad.
numPropiedad calle ciudad codigoPostal renta
PA14 16 Holhead Aberdeem AB7 5SU 650
PL94 6 Argvill St. London NW2 400
PL95 9 Argvill St. London NW2 800
PG21 10 Dale Rd Glasgow G12 600
numOficina calle ciudad codigoPostal
B005 16 Holhead Aberdeem AB7 5SU
B007 6 Argvill St. London NW2
B003 6 Lawrence StGlasgow G11 9QX
B004 2 Manor Rd Glasgow G114QX
B002 10 Dale Rd Bristol G12
Tabla Propiedad
Tabla Oficina
Ejemplo 5.32 Uso de UNION
(SELECT ciudad FROM Oficina
WHERE ciudad IS NOT NULL) UNION
(SELECT ciudad
FROM Propiedad
WHERE ciudad IS NOT NULL);
Construya un listado de todas las ciudades en donde hay ya sea una oficina o una propiedad.
Ejemplo 5.32 Uso de UNION
• O
(SELECT *
FROM Oficina
WHERE ciudad IS NOT NULL)
UNION CORRESPONDING BY ciudad
(SELECT *
FROM Propiedad
WHERE ciudad IS NOT NULL);
Ejemplo 5.32 Uso de UNION
• Esta consulta es ejecutada produciendo el resultado de la primera consulta y el resultado de la segunda consulta, para finalmente mezclar el resultado en una sola tabla
ciudad
Aberdeem
London
Glasgow
Bristol
Tabla resultado
Ejemplo 5.32
Ejemplo 5.33 Uso de INTERSECT
Construya un listado de todas las ciudades en que
hay una oficina y una propiedad.
numPropiedad calle ciudad codigoPostal renta
PA14 16 Holhead Aberdeem AB7 5SU 650
PL94 6 Argvill St. London NW2 400
PL95 9 Argvill St. London NW2 800
PG21 10 Dale Rd Glasgow G12 600
numOficina calle ciudad codigoPostal
B005 16 Holhead Aberdeem AB7 5SU
B007 6 Argvill St. London NW2
B003 6 Lawrence StGlasgow G11 9QX
B004 2 Manor Rd Glasgow G114QX
B002 10 Dale Rd Bristol G12
Tabla Propiedad
Tabla Oficina
Ejemplo 5.33 Uso de INTERSECT
Construya un listado de todas las ciudades en que
hay una oficina y una propiedad.
(SELECT ciudad FROM Oficina) INTERSECT
(SELECT ciudad FROM Propiedad);
Ejemplo 5.33 Uso de INTERSECT
•O
(SELECT * FROM Oficina)
INTERSECT CORRESPONDING BY ciudad
(SELECT * FROM Propiedad);
© Pearson Education Limited 1995, 2005
Ejemplo 5.33 Uso de INTERSECT • Podríamos reescribir esta consulta, sin el
operador INTERSECT:
SELECT O.city
FROM Oficina o Propiedad p
WHERE o.ciudad = p.ciudad;
• O: SELECT DISTINCT ciudad
FROM Oficina o
WHERE EXISTS
(SELECT * FROM Propiedad p
WHERE p.ciudad = o.ciudad);
Ejemplo 5.34 Uso de EXCEPT Listar todas las ciudades en donde haya
oficinas, pero no propiedades.
(SELECT ciudad FROM Oficina)
EXCEPT
(SELECT ciudad FROM Propiedad);
• O
(SELECT * FROM Oficina)
EXCEPT CORRESPONDING BY ciudad
(SELECT * FROM Propiedad);
Ejemplo 5.34 Uso de EXCEPT
• Podemos reescribir esta consulta sin el operador EXCEPT:
SELECT DISTINCT ciudad FROM Oficina
WHERE ciudad NOT IN
(SELECT ciudad FROM Propiedad);
• O
SELECT DISTINCT ciudad FROM Oficina o
WHERE NOT EXISTS
(SELECT * FROM Propiedad p
WHERE p. ciudad = b. ciudad );
Actualizaciones a la Base de datos El lenguaje SQL puede ser usado para consultar la base de datos como también para modificar los datos.
Tres comandos SQL para modificar el contenido de las tablas en la base de datos:
Insert – Agrega nuevas filas de datos a una tabla
Delete – Elimina filas de datos de una tabla
Update – Modifica los datos existentes en una tabla.
INSERT INSERT INTO Nombretabla [ (listaDeColumnas) ]
VALUES (listaDeValores)
• listaDeColumnas es opcional; si se omite, SQL asume la lista
de todas las columnas en el orden original.
• todas las columnas omitidas en la lista deben haber sido declaradas NULL cuando la tabla fue creada, a menos que se
haya utilizado la opción DEFAULT cuando se creo la columna.
INSERT
• La listaDeValores debe coincidir con
listaDeColumnas como sigue:
• El número de ítems de cada lista debe ser el
mismo
• Debe haber una correspondencia directa en la
posición de los ítems en las dos listas,
• El tipo de datos de cada item en listaDeValores debe ser compatible con el tipo de datos de la
correspondiente columna.
Ejemplo 5.34
INSERT . . . VALUES
Insertar una nueva fila en la tabla Empleado,
entregando los datos para todas las columnas:
numEmpleado nombre apellido cargo sexo fechNac salario numOficina
SL21 Jhon White Gerente M 01-Oct-45 300000 B005
SG37 Peter Denver Asistente M 10-Nov-60 120000 B003
SG14 David Ford Supervisor M 09-Sep-58 180000 B003
SA9 Mary Lee Asistente F 17-Sep-59 90000 B007
SG5 Susan Sarandon Gerente F 21-Mar-60 240000 B003
SL41 Julie Roberts Asistente F 13-Jun-63 90000 B005
Tabla Empleado
Ejemplo 5.34
INSERT . . . VALUES
INSERT INTO Empleado
VALUES (‘SG16’, ‘Alan’, ‘Brown’, ‘Asistente’, ‘M’, Date‘1957-05-25’, 83000, ‘B003’);
Insertar una nueva fila en la tabla Empleado, entregando los datos para todas las columnas:
numEmpleado nombre apellido cargo sexo fechNac salario numOficina
SL21 Jhon White Gerente M 01-Oct-45 300000 B005
SG37 Peter Denver Asistente M 10-Nov-60 120000 B003
SG14 David Ford Supervisor M 09-Sep-58 180000 B003
SA9 Mary Lee Asistente F 17-Sep-59 90000 B007
SG5 Susan Sarandon Gerente F 21-Mar-60 240000 B003
SL41 Julie Roberts Asistente F 13-Jun-63 90000 B005
Tabla Empleado
Ejemplo 5.35 INSERT
usando valores por defecto
INSERT INTO Empleado (numEmpleado, nombre, apellido, cargo, salario, numOficina)
VALUES (‘SG44’, ‘Amme’, ‘Jones’, ‘Asistente’, 8100,
‘B003’); • O
INSERT INTO Empleado
VALUES (‘SG44’, ‘Anne’, ‘Jones’, ‘Asistente’, NULL,
NULL, 8100, ‘B003’);
Insertar una nueva fila en la tabla Empleado suministrando datos para todas las columnas obligatorias. numEmpleado, nombre, apellido, cargo, salario y numOficina.
INSERT … SELECT
• La segunda forma de la instrucción INSERT
permite que múltiples filas sean copiadas
de una o más tablas a otra, y tiene el
siguiente formato:
INSERT INTO NombreTabla [
(listaDeColumnas) ]
SELECT ...
Ejemplo 5.35 INSERT … SELECT
EmpleadoTotalPropiedad (numEmpleado,
Nombre, Apellido, totalProp)
Poblar la tabla EmpleadoTotalPropiedad usando
los detalles de la tabla Empleado y la tabla
Propiedad.
Asuma que hay una tabla EmpleadoTotalPropiedad, que contiene los nombres de los empleados y el número de propiedades que ellos manejan:
Ejemplo 5.35 INSERT … SELECT
• Poblar la tabla EmpleadoTotalPropiedad usando los detalles
de la tabla Empleado y la tabla Propiedad.
EmpleadoTotalPropiedad (numEmpleado, Nombre,
Apellido, totalProp)
numPropiedad calle ciudad codigoPostal tipo hab renta numPropietario numEmpleado
PA14 16 Holhead Aberdeem AB7 5SU Casa 6 650 C046 SL21
PL94 6 Argvill St. London NW2 Departamento 4 400 C087 SL21
PG4 6 Lawrence St Glasgow G11 9QX Departamento 3 350 C040 SA9
PG36 2 Manor Rd Glasgow G114QX Departamento 3 375 C093 SA9
PG21 10 Dale Rd Glasgow G12 Casa 5 600 C087 SG5
PG16 5 Novar Dr Glasgow G12 9AX Departamento 4 450 C093 SL21
numEmpleado nombre apellido cargo sexo fechNac salario numOficina
SL21 Jhon White Gerente M 01-Oct-45 300000 B005
SG37 Peter Denver Asistente M 10-Nov-60 120000 B003
SG14 David Ford Supervisor M 09-Sep-58 180000 B003
SA9 Mary Lee Asistente F 17-Sep-59 90000 B007
SG5 Susan Sarandon Gerente F 21-Mar-60 240000 B003
SL41 Julie Roberts Asistente F 13-Jun-63 90000 B005
Tabla Empleado
Tabla Propiedad
Ejemplo 5.35 INSERT … SELECT INSERT INTO EmpleadoTotalPropiedad
(SELECT e.numEmpleado, Nombre, Apellido, COUNT(*)
FROM Empleado e, Propiedad p
WHERE e.numEmpleado = p.numEmpleado
Group BY e.numempleado , nombre, apellido)
UNION
(SELECT numEmpleado, nombre, apellido, 0
FROM Empleado e
WHERE NOT EXIST (SELECT *
FROM Propiedad p
WHERE e.numEmpleado = p.numEmpleado));
Ejemplo 5.35 INSERT … SELECT
• Si la segunda parte de la UNIÓN se omite, excluye a los empleados que no manejan actualmente ninguna propiedad.
numEmpleado nombre apellido totalProp
SL21 Jhon White 3
SG37 Peter Denver 0
SG14 David Ford 0
SA9 Mary Lee 2
SG5 Susan Sarandon 1
SL41 Julie Roberts 0
Tabla resultado Ejemplo 5.35
UPDATE UPDATE NombreTabla
SET columnName1 = dataValue1
[, columnName2 = dataValue2...]
[WHERE searchCondition] • NombreTabla puede ser el nombre de una tabla
base o una vista actualizable.
• La cláusula SET especifica los nombres de una o más columnas a ser actualizadas
UPDATE
• La cláusula WHERE es opcional: o Si se omite, las columnas nombradas serán actualizadas para todas las
filas en tabla;
o Si se especifica, solamente esas filas que satisfacen la searchCondition
son actualizadas.
• El(los) nuevo(s) dataValue(s) debe(n) ser
compatible con el tipo de datos para la columna
correspondiente.
Ejemplo 5.38/39 UPDATE
todas las filas
• Dé a todos los Empleados un aumento de sueldo del 3%.
• Dé a todos los Gerentes un aumento de sueldo del 5%.
numEmpleado nombre apellido cargo sexo fechNac salario numOficina
SL21 Jhon White Gerente M 01-Oct-45 300000 B005
SG37 Peter Denver Asistente M 10-Nov-60 120000 B003
SG14 David Ford Supervisor M 09-Sep-58 180000 B003
SA9 Mary Lee Asistente F 17-Sep-59 90000 B007
SG5 Susan Sarandon Gerente F 21-Mar-60 240000 B003
SL41 Julie Roberts Asistente F 13-Jun-63 90000 B005
Tabla Empleado
Ejemplo 5.38/39 UPDATE
todas las filas
• Dé a todos los Empleados un aumento de sueldo del 3%.
UPDATE Empleado SET salario = salario*1.03;
numEmpleado nombre apellido cargo sexo fechNac salario numOficina
SL21 Jhon White Gerente M 01-Oct-45 300000 B005
SG37 Peter Denver Asistente M 10-Nov-60 120000 B003
SG14 David Ford Supervisor M 09-Sep-58 180000 B003
SA9 Mary Lee Asistente F 17-Sep-59 90000 B007
SG5 Susan Sarandon Gerente F 21-Mar-60 240000 B003
SL41 Julie Roberts Asistente F 13-Jun-63 90000 B005
Tabla Empleado
Ejemplo 5.38/39 UPDATE
todas las filas
• Dé a todos los Gerentes un aumento de sueldo del 5%.
UPDATE Empleado SET salario = salario*1.05
WHERE cargo = ‘Gerente’;
numEmpleado nombre apellido cargo sexo fechNac salario numOficina
SL21 Jhon White Gerente M 01-Oct-45 300000 B005
SG37 Peter Denver Asistente M 10-Nov-60 120000 B003
SG14 David Ford Supervisor M 09-Sep-58 180000 B003
SA9 Mary Lee Asistente F 17-Sep-59 90000 B007
SG5 Susan Sarandon Gerente F 21-Mar-60 240000 B003
SL41 Julie Roberts Asistente F 13-Jun-63 90000 B005
Tabla Empleado
Ejemplo 5.38/39 UPDATE
múltiples columnas
Promueva a David Ford
(numEmpleado=‘SG14’) a Gerente y
cambie su salario a 190.000.
numEmpleado nombre apellido cargo sexo fechNac salario numOficina
SL21 Jhon White Gerente M 01-Oct-45 300000 B005
SG37 Peter Denver Asistente M 10-Nov-60 120000 B003
SG14 David Ford Supervisor M 09-Sep-58 180000 B003
SA9 Mary Lee Asistente F 17-Sep-59 90000 B007
SG5 Susan Sarandon Gerente F 21-Mar-60 240000 B003
SL41 Julie Roberts Asistente F 13-Jun-63 90000 B005
Tabla Empleado
Ejemplo 5.38/39 UPDATE
múltiples columnas
Promueva a David Ford (numEmpleado=‘SG14’) a
Gerente y cambie su salario a 190.000.
UPDATE Empleado
SET cargo = ‘Gerente’,
salario = 190000
WHERE numEmpleado = ‘SG14’;
numEmpleado nombre apellido cargo sexo fechNac salario numOficina
SL21 Jhon White Gerente M 01-Oct-45 300000 B005
SG37 Peter Denver Asistente M 10-Nov-60 120000 B003
SG14 David Ford Supervisor M 09-Sep-58 180000 B003
SA9 Mary Lee Asistente F 17-Sep-59 90000 B007
SG5 Susan Sarandon Gerente F 21-Mar-60 240000 B003
SL41 Julie Roberts Asistente F 13-Jun-63 90000 B005
Tabla Empleado
DELETE
DELETE FROM NombreTabla
[WHERE searchCondition]
• NombreTabla puede ser una tabla base o
una vista actualizable.
• searchCondition es opcional; si se omite,
todas las filas son eliminadas de la tabla.
Este no elimina la tabla. Si se especifica
search_condition solamente se eliminan
aquellas filar que satisfacen la condición
Ejemplo 5.41/42 DELETE
Filas especificas
• Suprima todas las visitas que se relacionen con
la propiedad PG4.
• Elimine todos los registros de la tabla Visita.
numCliente numPropiedad Fecha Comentario
CR56 PA14 24-11-1999 muy pequeño
CR76 PG4 20-10-1999 muy lejos
CR56 PG4 26-11-1999
CR62 PA14 14-11-1999 no tiene salón
CR56 PG36 28-10-1999
Tabla Visita
Ejemplo 5.41/42 DELETE
Filas especificas
• Suprima todas las visitas que se relacionen con
la propiedad PG4.
DELETE FROM Visita
WHERE numPropiedad = ‘PG4’;
numCliente numPropiedad Fecha Comentario
CR56 PA14 24-11-1999 muy pequeño
CR76 PG4 20-10-1999 muy lejos
CR56 PG4 26-11-1999
CR62 PA14 14-11-1999 no tiene salón
CR56 PG36 28-10-1999
Tabla Visita
Ejemplo 5.41/42 DELETE
Filas especificas
• Elimine todos los registros de la tabla Visita.
DELETE FROM Visita;
numCliente numPropiedad Fecha Comentario
CR56 PA14 24-11-1999 muy pequeño
CR76 PG4 20-10-1999 muy lejos
CR56 PG4 26-11-1999
CR62 PA14 14-11-1999 no tiene salón
CR56 PG36 28-10-1999
Tabla Visita
• Fin
top related