base de datos bim ii sql manipulacion de datos
DESCRIPTION
Manipulacion de Datos en SQLPonente: Juan Carlos MorochoTRANSCRIPT
ESCUELA:
PONENTE:
BIMESTRE:
BASES DE DATOS I
CICLO:
CIENCIAS DE LA COMPUTACIÓN
II BIMESTRE
Ing. Juan Carlos Morocho
Octubre 2009 – Febrero 2010
Capítulo 5
SQL: Manipulación de datos
Transparencias
© Pearson Education Limited 1995, 2005
Capítulo 5 - Objetivos El propósito y la importancia del lenguaje SQL
(Structured Query Languaje, Lenguaje estructurado de consulta).
Cómo extraer datos de una base de datos usando la instrucción SELECT y construir instrucciones que:
utilicen la cláusula WHERE para extraer filas que satisfagan diversas condiciones.
ordenen los resultados de las consultas mediante ORDER BY.
utilicen las funciones de agregación de SQL agrupen los datos mediante GROUP BY y HAVING. utilicen subconsultas.
3© Pearson Education Limited 1995, 2005
Capítulo 5 - Objetivos
Combinen tablas. Realicen operaciones de conjunto
(UNION, INTERSECT, EXCEPT).
Cómo realizar actualizaciones en la base de datos mediante INSERT, UPDATE, y DELETE.
4© Pearson Education Limited 1995, 2005
Objetivos del SQL Idealmente, un lenguaje de datos debe permitir al
usuario: Crear la bases de datos y las estructuras de relación; Realizar tareas básicas de gestión de datos como la
inserción, modificación y borrado de los datos de las relaciones;
Realizar consultas tanto simples como complejas. Debe realizar estas tareas requiriendo un esfuerzo
mínimo por parte del usuario y su sintaxis y la estructura de los comandos deben ser relativamente fáciles de aprender.
Debe ser portable.
5© Pearson Education Limited 1995, 2005
Objetivos del SQL El SQL es un lenguaje orientado a la transformación
con dos componentes principales:
Un lenguaje de definición de datos (DLL, Data Definition Language).
Un lenguaje de manipulación de datos (DML, Data Manipulation Language).
Hasta SQL:1999, el lenguaje SQL no contenía comandos para control de flujo. Estos comandos tenían que implementarse utilizando un lenguaje de programación o un lenguaje de control de tareas, o bien implementarlos de manera interactiva mediante las decisiones del usuario.
6© Pearson Education Limited 1995, 2005
Objetivos del SQL
El lenguaje SQL es relativamente fácil de aprender.
Se trata de un lenguaje no procedimental: lo que especificamos es qué información queremos, en lugar de especificar cómo obtenerla;
Es, esencialmente, un lenguaje de formato libre.
7© Pearson Education Limited 1995, 2005
Objetivos del SQL Está compuesto por palabras inglesas
normales:
1) CREATE TABLE Staff(staffNo VARCHAR(5), lName VARCHAR(15), salary DECIMAL(7,2));
2) INSERT INTO Staff VALUES (‘SG16’, ‘Brown’, 8300);
3) SELECT staffNo, lName, salary FROM Staff WHERE salary > 10000;
8© Pearson Education Limited 1995, 2005
Objetivos del SQL Puede ser utilizado por diversos tipos de
usuarios, incluyendo administradores de bases de datos (DBA) personal administrativo, desarrolladores de aplicaciones y muchos otros tipos de usuario final.
Actualmente existe una norma ISO internacional, lo que hace que sea tanto el lenguaje estándar formal como el estándar de facto para la definición y manipulación de bases de datos relacionales.
9© Pearson Education Limited 1995, 2005
Historia del SQL
En 1974, el Dr. Chamberlain, del laboratorio de IBM en San José, definió un lenguaje denominado lenguaje de de consulta en inglés estructurado (SEQUEL, Structured English Query Language ).
Una versión revisada, SEQUEL/2, se definió en 1976, pero posteriormente se cambió el nombre a SQL por razones legales.
10© Pearson Education Limited 1995, 2005
Historia del SQL En el mundo anglosajón, muchas personas
lo pronuncian todavía como «sicuel». Posteriormente, IBM desarrolló un
prototipo DBMS denominado System R, basado en SEQUEL/2.
Las raíces de SQL, sin embargo, se encuentran en el lenguaje SQUARE (Specifying Queries as Relational Expressions, especificación de consultas como expresiones relacionales), que es anterior al proyecto System R.
11© Pearson Education Limited 1995, 2005
Historia del SQL A finales de los 70, apareció ORACLE,
probablemente la primera implementación comercial de un SGBD basado en SQL.
En 1987, ANSI e ISO publicaron un estándar para SQL.
En 1989, ISO publicó un anexo que definía una «característica de mejora de la integridad».
En 1992, tuvo lugar la primera revisión principal del estándar ISO, conocida como SQL2 o SQL92.
En 1999, se publicó SQL:1999 que contenía características adicionales para soportar la gestión de datos orientada a objetos.
A finales de 2003 se publicó una nueva versión SQL:2003.
12© Pearson Education Limited 1995, 2005
Importancia de SQL SQL ha pasado a formar parte de
arquitecturas de aplicación tales como SAA (Systems Application Architecture) de IBM.
Es la opción estratégica elegida por muchas organizaciones de gran tamaño y de considerable influencia (Ej. X/OPEN).
SQL también es un estándar FIPS (Federal Information Processing Standard), lo cual es obligatorio para todas las ventas de sistemas SGBD al gobierno de los Estados Unidos.
13© Pearson Education Limited 1995, 2005
Importancia de SQL
Se utiliza en otros estándares e incluso influye sobre el desarrollo de otros estándares como herramienta de definición. Los ejemplos incluyen:
El sistema IRDS (Information Resource Dictionary System, sistema de diccionarios para recursos de información de ISO).
El estándar RDA (Remote Data Access, acceso remoto a datos)
14© Pearson Education Limited 1995, 2005
Escritura de los comandos SQL Una instrucción SQL está compuesta por
palabras reservadas y palabras definidas por el usuario.
– Las palabras reservadas son una parte fija del lenguaje SQL y tienen un significado también fijo.
– Las palabras definidas por el usuario son compuestas por este y representan los nombres de diversos objetos de las bases de datos, como tablas columnas, vistas, índices...
15© Pearson Education Limited 1995, 2005
Escritura de comandos SQL La mayoría de los componentes de una instrucción
SQL no distinguen entre mayúsculas y minúsculas excepto en los datos de caracteres literales.
Serán más legibles si se utilizan las técnicas de sangrado y alineación: Cada cláusula de una instrucción debe comenzar
en una nueva línea. El principio de cada cláusula debe estar alineado
con el comienzo de las cláusulas restantes; Si una cláusula tiene varias partes, cada una de
ellas debe aparecer en una línea distinta y debe estar sangrada con respecto al comienzo de la cláusula.
16© Pearson Education Limited 1995, 2005
Escritura de comandos SQL Se usará la forma ampliada de la notación
BND (Backus Naur Form):
- Las letras mayúsculas representarán palabras reservadas.- Las letras minúsculas representarán palabras definidas por el usuario.- | indica una elección entre diversas alternativas.- Las llaves {} indican un elemento obligatorio.- Los corchetes indican un elemento opcional.- … indica la repetición opcional de un elemento cero o más veces.
17© Pearson Education Limited 1995, 2005
Literales Los literales son constantes que se
utilizan en las instrucciones SQL.
Todos los valores de datos no numéricos deben estar encerrados entre comillas simples (Ej. ‘London’).
Todos los valores de datos numéricos no deben estar encerrados entre comillas simples (Ej. 650.00).
18© Pearson Education Limited 1995, 2005
Comando SELECT
SELECT [DISTINCT | ALL] {* | [expresiónColumna [AS
nuevoNombre]] [,...] }FROM NombreTabla [alias] [, ...][WHEREcondición][GROUP BY listaColumnas] [HAVING
condición][ORDER BY listaColumnas]
19© Pearson Education Limited 1995, 2005
Comando SELECT
FROM Especifica la tabla o tablas que hay que usar.
WHERE Filtra las filas de acuerdo con alguna condición
GROUP BY Forma grupos de filas que tengan el mismo valor de columna.
HAVING Filtra los grupos de acuerdo con alguna condición.
SELECT Especifica qué columnas deben aparecer en la salida.
ORDER BY Especifica el orden de la salida.
20© Pearson Education Limited 1995, 2005
Comando SELECT
El orden de las cláusulas no puede cambiarse.
La únicas cláusulas obligatorias son SELECT y FROM.
21© Pearson Education Limited 1995, 2005
Ejemplo 5.1 Extraer todas las columnas de todas las filas.Generar un listado con todos los detalles de todo el personal.
SELECT staffNo, fName, lName, address, position, sex, DOB, salary, branchNo
FROM Staff;
Se puede utilizar un asterisco(*) como abreviatura de «todas las columnas»:
SELECT *FROM Staff;
22© Pearson Education Limited 1995, 2005
Ejemplo 5.1 Extraer todas las columnas de todas las filas
23© Pearson Education Limited 1995, 2005
Tabla 5.1 Tabla de resultados para el ejemplo 5.1
Ejemplo 5.2 Extraer toda una serie de columnas específicas de todas las filasGenerar una lista con los salarios de todos los empleados en la que sólo se muestre el número de empleado, el nombre, el apellido y los datos salariales.
SELECT staffNo, fName, lName, salary
FROM Staff;
24© Pearson Education Limited 1995, 2005
Ejemplo 5.2 Extraer toda una serie de columnas específicas de todas las filas
25© Pearson Education Limited 1995, 2005
Tabla 5.2 Tabla de resultados para el ejemplo 5.2
Ejemplo 5.3 Uso de DISTINCT
Generar un listado con los números de inmueble de todos los inmuebles que hayan sido visitados.
SELECT propertyNoFROM Viewing;
26© Pearson Education Limited 1995, 2005
Ejemplo 5.3 Uso de DISTINCT
Uso de DISTINCT para eliminar duplicados:
SELECT DISTINCT propertyNoFROM Viewing;
27© Pearson Education Limited 1995, 2005
Ejemplo 5.4 Campos calculados
Generar una lista con el salario mensual de todos los empleados que muestre número de empleado, nombre, apellido y la información salarial.
SELECT staffNo, fName, lName, salary/12
FROM Staff;
28
© Pearson Education Limited 1995, 2005
Tabla 5.4 Tabla de resultados para el ejemplo 5.4
Ejemplo 5.4 Campos calculados
Se puede dar un nombre a la columna mediante la cláusula AT:
SELECT staffNo, fName, lName, salary/12
AS monthlySalaryFROM Staff;
29© Pearson Education Limited 1995, 2005
Ejemplo 5.5 Condición de búsqueda basada en comparación
Generar una lista de todos los empleados con salario superior a 10.000 euros.
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary > 10000;
30© Pearson Education Limited 1995, 2005
Tabla 5.5 Tabla de resultados para el ejemplo 5.5
Ejemplo 5.6 Condición de búsqueda basada en una comparación
compuesta Genere una lista con la dirección de todas las sucursales de Londres y Glasgow
SELECT *FROM BranchWHERE city = ‘London’ OR city
= ‘Glasgow’;
31© Pearson Education Limited 1995, 2005
Tabla 5.6 Tabla de resultados para el ejemplo 5.6
Ejemplo 5.7 Condición de búsqueda basada en rangoGenerar un listado con todos los empleados cuyo salario esté comprendido entre 20.000 y 30.000 euros.
SELECT staffNo, fName, lName, position, salaryFROM StaffWHERE salary BETWEEN 20000 y 30000;
BETWEEN indica los puntos extremos del rango.
32© Pearson Education Limited 1995, 2005
Ejemplo 5.7 Condición de búsqueda basada en rango
33© Pearson Education Limited 1995, 2005
Tabla de resultados para el ejemplo 5.7Tabla 5.7
Ejemplo 5.7 Condición de búsqueda basada en rango
También existe una versión negada NOT BETWEEN.
BETWEEN no añade mucha potencia expresiva a SQL. También se puede escribir:
SELECT staffNo, fName, lName, position, salaryFROM StaffWHERE salary>=20000 y salary <= 30000;
Sin embargo, es la forma más sencilla de expresar una condición de búsqueda cuando lo que estemos considerando sea un rango de valores.
34© Pearson Education Limited 1995, 2005
Ejemplo 5.8 Pertenencia a un conjunto
Generar una lista de todos los gerentes y supervisores.
SELECT staffNo, fName, lName, position
FROM Staff
WHERE position IN (‘Manager’, ‘Supervisor’);
35© Pearson Education Limited 1995, 2005
Tabla de resultados para el ejemplo 5.8Tabla 5.8
Ejemplo 5.8 Pertenencia a un conjunto
Existe una versión negada (NOT IN). IN no añade mucha potencia expresiva a SQL. Podríamos haber expresado la consulta anterior:
SELECT staffNo, fName, lName, position
FROM Staff
WHERE position=‘Manager’ OR
position=‘Supervisor’;
IN resulta más efectivo cuando el conjunto contiene múltiples valores.
36© Pearson Education Limited 1995, 2005
Ejemplo 5.9 Correspondencia de patronesLocalizar todos los propietarios en cuya dirección aparezca la cadena de caracteres «Glasgow».
SELECT ownerNo, fName, lName, address, telNoFROM PrivateOwnerWHERE address LIKE ‘%Glasgow%’;
37© Pearson Education Limited 1995, 2005
Tabla de resultados para el ejemplo 5.9Tabla 5.9
Ejemplo 5.9 Correspondencia de patrones
SQL dispone de dos símbolos especiales para correspondencia de patrones:
%: cualquier secuencia de cero o más caracteres;
_ (subrayado): cualquier carácter individual.
LIKE ‘%Glasgow%’ quiere decir una secuencia de caracteres de cualquier longitud que contenga la palabra Glasgow.
38© Pearson Education Limited 1995, 2005
Ejemplo 5.10 Condición de búsqueda NULLGenerar un listado con los detalles de todas las visitas al inmueble PG4 para las que no se haya introducido ningún comentario.
Se han producido dos visitas al inmueble PG4, una con comentario y otra sin el.
Tenemos que comprobar explícitamente la existencia de valores nulos, utilizando la palabra clave especial IS NULL:
SELECT clientNo, viewDateFROM ViewingWHERE propertyNo = ‘PG4’ y
comment IS NULL;
39© Pearson Education Limited 1995, 2005
Ejemplo 5.10 Condición de búsqueda NULL
También puede emplearse la versión negada (IS NOT NULL) para buscar valores que no sean nulos.
40© Pearson Education Limited 1995, 2005
Ejemplo 5.11 Ordenación de los resultado en una sola columnaGenerar una lista con el salario de todos los empleados, en orden descendente.
SELECT staffNo, fName, lName, salary
FROM StaffORDER BY salary DESC;
41© Pearson Education Limited 1995, 2005
Ejemplo 5.11 Ordenación de resultado en una sola columna
42© Pearson Education Limited 1995, 2005
Tabla de resultados para el ejemplo 5.11Tabla 5.11
Ejemplo 5.12 Ordenación multicolumnaGenerar una lista abreviada de inmuebles ordenada según el tipo de inmueble.
SELECT propertyNo, type, rooms, rent
FROM PropertyForRentORDER BY type;
43© Pearson Education Limited 1995, 2005
Ejemplo 5.12 Ordenación multicolumna
44© Pearson Education Limited 1995, 2005
Tabla 5.12(a) Tabla de resultados para el ejemplo 5.12 con una clave de ordenación
Ejemplo 5.12 Ordenación multicolumna
Hay cuatro pisos en esta lista. Como no hemos especificado ninguna clave secundaria de ordenación, el sistema ordena estas filas de cualquier manera.
Para ordenar lo resultados según el importe del alquiler, podemos especificar un orden secundario:
SELECT propertyNo, type, rooms, rent
FROM PropertyForRent
ORDER BY type, rent DESC;
45© Pearson Education Limited 1995, 2005
Ejemplo 5.12 Ordenación multicolumna
46© Pearson Education Limited 1995, 2005
Tabla 5.12 (b) Tabla de resultados para el ejemplo 5.12 con dos claves de ordenación
Comando SELECT – Funciones de agregación
El estándar ISO define cinco funciones de agregación:
COUNT devuelve el número de valores en una columna especificada.
SUMdevuelve la suma de los valores contenidos en una columna especificada.
AVG devuelve la media de los valores contenidos en una columna especificada.
MIN devuelve el valor más pequeño contenido en una columna especificada.
MAXdevuelve al valor más alto contenido en una columna especificada.
47© Pearson Education Limited 1995, 2005
Comando SELECT - Funciones de agregación
Cada una opera sobre una única columna de una tabla y devuelve un único valor.
COUNT, MIN, y MAX se aplican a campos tanto numéricos como no numéricos, pero SUM y AVG sólo pueden emplearse con campos numéricos.
Dejando aparte COUNT(*), todas las funciones eliminan en primer lugar los valores nulos y utilizan para sus operaciones únicamente los restantes valores no nulos.
48© Pearson Education Limited 1995, 2005
Comando SELECT – Funciones de agregación
COUNT(*) se emplea para contar todas las filas de una tabla, independientemente de si existen valores nulos o valores duplicados.
Se emplea DISTINCT antes de la columna a la que se aplica la función para eliminar los duplicados.
DISTINCT no tiene ningún efecto con las funciones MIN/MAX, pero sí que puede influir sobre el resultado de SUM/AVG.
49© Pearson Education Limited 1995, 2005
Comando SELECT – Funciones de agregación
Las funciones de agregación sólo pueden duplicarse en la lista SELECT y en la cláusula HAVING.
Si la lista SELECT incluye una función de agregación y no se está empleando ninguna cláusula GROUP BY, entonces ningún elemento de la lista SELECT puede incluir ninguna referencia a una columna a menos que dicha columna sea el argumento de una función de agregación. Por ejemplo la siguiente consulta sería ilegal:
SELECT staffNo, COUNT(salary)FROM Staff;
50© Pearson Education Limited 1995, 2005
Ejemplo 5.13 Utilización de COUNT(*)
¿Cuántos inmuebles tienen un alquiler superior a 350 euros por mes?
SELECT COUNT(*) AS myCount
FROM PropertyForRentWHERE rent > 350;
51© Pearson Education Limited 1995, 2005
Ejemplo 5.14 Utilización de COUNT (DISTINCT)
¿Cuántos inmuebles distintos fueron visitados en mayo de 2004?
SELECT COUNT(DISTINCT propertyNo) AS myCount
FROM Viewing
WHERE viewDate BETWEEN ‘1-May-04’
y ‘31-May-04’;
52© Pearson Education Limited 1995, 2005
Ejemplo 5.15 Utilización de COUNT y SUM Hallar el número total de
gerentes y la suma de sus salarios.
SELECT COUNT(staffNo) AS myCount,
SUM(salary) AS mySumFROM StaffWHERE position = ‘Manager’;
53
© Pearson Education Limited 1995, 2005
Ejemplo 5.16 Utilización de MIN, MAX, AVG
Calcular el salario mínimo, máximo y medio de los empleados.
SELECT MIN(salary) AS myMin,
MAX(salary) AS myMax, AVG(salary) AS myAvg
FROM Staff;
54© Pearson Education Limited 1995, 2005
Comando SELECT – Agrupación de resultados
Se utiliza GROUP BY para agrupar los datos de las tablas.
SELECT y GROUP BY están estrechamente integradas: cada elemento de la lista SELECT debe tener un único valor para cada grupo, y la cláusula SELECT sólo puede contener: Nombres de columnas Funciones de agregación Constantes Expresiones en las que se combinen algunos de
los elementos anteriores.
55© Pearson Education Limited 1995, 2005
Comando SELECT - Agrupación Todos los nombres de columna contenidos en la
lista SELECT deben aparecer obligatoriamente en la cláusula GROUP BY, a menos que el nombre se esté usando únicamente dentro de una función de agregación.
Si la cláusula WHERE se emplea con GROUP BY, se aplica primero la cláusula WHERE y luego se forman los grupos a partir de las filas restantes que satisfacen la condición de búsqueda.
El estándar ISO considera que dos valores nulos son iguales en lo que respecta a la cláusula GROUP BY.
56© Pearson Education Limited 1995, 2005
Ejemplo 5.17 Utilización de GROUP BY
Calcular el número de empleados que trabajan en cada sucursal y la suma de sus salarios.
SELECT branchNo, COUNT(staffNo) AS
myCount,SUM(salary) AS mySum
FROM StaffGROUP BY branchNoORDER BY branchNo;
57© Pearson Education Limited 1995, 2005
Ejemplo 5.17 Utilización de GROUP BY
58© Pearson Education Limited 1995, 2005
Restricción de los agrupamientos – Cláusula HAVING
La cláusula HAVING está diseñada para ser utilizada con la cláusula GROUP BY con el fin de restringir los grupos que aparecen en la tabla final de resultados.
Parecida a WHERE, pero WHERE filtra las filas individuales que se encuentran en la tabla final de resultados, mientras que HAVING filtra los grupos.
Los nombres de las columnas utilizados en la cláusula HAVING tienen que aparecer también en la vista GROUP BY o estar contenidos dentro de una función de agregación.
59© Pearson Education Limited 1995, 2005
Ejemplo 5.18 Utilización de HAVINGPara cada sucursal que tenga más de un empleado, averiguar el número de empleados que trabajan en
cada sucursal y la suma de sus salarios.
SELECT branchNo, COUNT(staffNo) AS myCount,
SUM(salary) AS mySumFROM StaffGROUP BY branchNoHAVING COUNT(staffNo) > 1ORDER BY branchNo;
60© Pearson Education Limited 1995, 2005
Ejemplo 5.18 Utilización de HAVING
61© Pearson Education Limited 1995, 2005
Suconsultas Algunas instrucciones SQL pueden
incluir una instrucción SELECT.
Puede utilizarse una subselección en las cláusulas WHERE y HAVING de una instrucción SELECT, en cuyo caso se denomina a esa instrucción subconsulta o consulta anidada.
Las subselecciones pueden aparecer también dentro de las instrucciones INSERT, UPDATE, y DELETE.
62© Pearson Education Limited 1995, 2005
Ejemplo 5.19 Utilización de una subconsulta con el operador de
igualdadGenerar un listado con todos los empleados de ka sucursal ubicada en «163 Main St».
SELECT staffNo, fName, lName, position
FROM StaffWHERE branchNo =
(SELECT branchNo FROM Branch WHERE street = ‘163 Main St’);
63© Pearson Education Limited 1995, 2005
Ejemplo 5.19 Subconsulta con el operador de igualdad
La instrucción SELECT interna extrae el número de sucursal correspondiente a la sucursal situada en la calle «163 Main St» (‘B003’).
La instrucción SELECT externa extrae los detalles de todos los empleados que trabajan en esta sucursal.
La instrucción SELECT externa se convierte en:
SELECT staffNo, fName, lName, positionFROM StaffWHERE branchNo = ‘B003’;
64© Pearson Education Limited 1995, 2005
Ejemplo 5.19 Subconsulta con el operador de igualdad
65© Pearson Education Limited 1995, 2005
Tabla 5.19 Tabla de resultados para el ejemplo 5.19
Ejemplo 5.20 Subconsulta con una función de agregación
Generar un listado de todos los empleados cuyo salario sea superior al salario medio, indicando cuál es la diferencia en cada caso con respecto al salario medio.
SELECT staffNo, fName, lName, position,
salary – (SELECT AVG(salary) FROM Staff) As SalDiff
FROM Staff
WHERE salary >(SELECT AVG(salary) FROM Staff);
66© Pearson Education Limited 1995, 2005
Ejemplo 5.20 Subconsulta con función de agregación
No podemos escribir «WHERE salary > AVG(salary)»
Sin embargo, utilizamos una subconsulta para hallar el salario medio y luego empleamos la instrucción SELECT externa para extraer los empleados que tengan un salario superior al salario medio calculado:
SELECT staffNo, fName, lName, position,
salary – 17000 As salDiff
FROM Staff
WHERE salary > 17000;
67© Pearson Education Limited 1995, 2005
Ejemplo 5.20 Subconsulta con función de agregación
68© Pearson Education Limited 1995, 2005
Tabla de resultados para el ejemplo 5.20Tabla 5.20
Reglas de las subconsultas La cláusula ORDER BY no se puede utilizar dentro de
una subconsulta (aunque sí puede emplearse en la instrucción SELECT más externa).
La lista SELECT de la subconsulta debe estar compuesta por un único nombre de columna o expresión, excepto en el caso de las subconsultas que utilicen la palabra EXISTS.
De manera predeterminada, los nombres de columna de una subconsulta hacen referencia al nombre de tabla incluso en la cláusula FROM de la subconsulta. También puede referirse a una tabla contenida en la cláusula FROM de una consulta externa cualificando el nombre de la columna.
69© Pearson Education Limited 1995, 2005
Reglas de las subconsultas
Cuando una subconsulta actúa como uno de los operandos de una comparación, la subconsulta debe aparecer en el lado derecho de la comparación.
Una subconsulta no puede emplearse como operador en una expresión.
70© Pearson Education Limited 1995, 2005
Ejemplo 5.21 Subconsulta anidada: Utilización de IN
Generar un listado con los inmuebles gestionados por los empleados que trabajan en la sucursal situada en «163 Main St».
SELECT propertyNo, street, city, postcode, type, rooms, rent
FROM PropertyForRentWHERE staffNo IN
(SELECT staffNo FROM Staff WHERE branchNo =
(SELECT branchNo FROM Branch WHERE street = ‘163 Main St’));
71© Pearson Education Limited 1995, 2005
Ejemplo 5.21 Subconsulta anidada: Utilización de IN
72© Pearson Education Limited 1995, 2005
Tabla de resultados para el ejemplo 5.21Tabla 5.21
ANY y ALL ANY y ALL pueden utilizarse con aquellas
subconsultas que generen una única columna de números.
Con ALL, la condición sólo será cierta si se se ve satisfecha por todos los valores generados por la subconsulta.
Con ANY, la condición sólo será cierta si se ve satisfecha por alguno (uno o más) de los valores generados por la subconsulta.
Si la subconsulta está vacía, la condición ALL devuelve un valor verdadero, mientras que la condición ANY devuelve un valor falso.
SOME puede utilizarse en lugar de ANY.
73© Pearson Education Limited 1995, 2005
Ejemplo 5.22 Utilización de ANY/SOME
Determinar todos los empleados cuyo salario sea superior al salario de al menos uno de los empleados de la sucursal B003.
SELECT staffNo, fName, lName, position, salary
FROM StaffWHERE salary > SOME
(SELECT salary FROM Staff WHERE branchNo = ‘B003’);
74© Pearson Education Limited 1995, 2005
Ejemplo 5.22 Utilización de ANY/SOME
La consulta interna produce un conjunto {12000, 18000, 24000} y la subconsulta externa selecciona los empleados cuyos salarios sean superiores a alguno de los valores contenidos en el conjunto.
75© Pearson Education Limited 1995, 2005
Tabla de resultados para el ejemplo 5.22Tabla 5.22
Ejemplo 5.23 Utilización de ALL
Hallar todos los empleados cuyo salario sea superior al salario de todos los empleados que trabajan en la sucursal B003.
SELECT staffNo, fName, lName, position, salaryFROM StaffWHERE salary > ALL
(SELECT salary FROM Staff WHERE branchNo = ‘B003’);
76© Pearson Education Limited 1995, 2005
Ejemplo 5.23 Utilización de ALL
77© Pearson Education Limited 1995, 2005
Tabla de resultados para el ejemplo 5.23Tabla 5.23
Consultas multitabla Hasta ahora hemos podido utilizar las subconsulta
siempre y cuando los resultados provinieran de la misma tabla.
Para combinar columnas de diversas tablas debemos utilizar una operación de combinación.
Para esta operación de combinación debemos incluir más de un nombre de tabla en la cláusula FROM.
Se utiliza una coma como separador, incluyendo una cláusula WHERE para especificar la columna o columnas con las que hay que realizar la combinación.
78© Pearson Education Limited 1995, 2005
Consultas multitabla
También se puede utilizar un alias para las tablas incluidas en la cláusula FROM.
El alias se separa del nombre de la tabla mediante un espacio.
El alias puede emplearse para cualificar los nombres de columna en aquellos casos donde exista ambigüedad.
79© Pearson Education Limited 1995, 2005
Ejemplo 5.24 Combinación simple
Generar un listado con los nombres de todos los clientes que hayan visitado un inmueble, incluyendo en el los comentarios realizados.
SELECT c.clientNo, fName, lName, propertyNo, comment
FROM Client c, Viewing vWHERE c.clientNo = v.clientNo;
80© Pearson Education Limited 1995, 2005
Ejemplo 5.24 Combinación simple Sólo se incluirán en el resultado aquellas
columnas de ambas tablas que tengas valores idénticos en la columna del clientNo, utilizando la condición de búsqueda (c.clientNo = v.clientNo).
Equivalente a la operación de equicombinación del álgebra relacional.
81© Pearson Education Limited 1995, 2005
Tabla de resultados para el ejemplo 5.24Tabla 5.24
Construcciones alternativas de combinación
En SQL existen formas alternativas de especificar esta combinación:
FROM Client c JOIN Viewing v ON c.clientNo = v.clientNo
FROM Client JOIN Viewing USING clientNo
FROM Client NATURAL JOIN Viewing
En cada uno de los casos , la cláusula FROM sustituye a las cláusulas FROM y WHERE originales. Sin embargo, la primer alternativa produce una tabla con dos columnas clientNo idénticas.
82© Pearson Education Limited 1995, 2005
Ejemplo 5.25 Ordenación de una combinación
Para cada sucursal, indicar los números y los nombres de los empledos que gestionan inmuebles, así como los inmuebles que gestionan.
SELECT s.branchNo, s.staffNo, fName, lName, propertyNoFROM Staff s, PropertyForRent pWHERE s.staffNo = p.staffNoORDER BY s.branchNo, s.staffNo, propertyNo;
83© Pearson Education Limited 1995, 2005
Ejemplo 5.25 Ordenación de una combinación
84© Pearson Education Limited 1995, 2005
Tabla de resultados para el ejemplo 5.25Tabla 5.25
Ejemplo 5.26 Combinación de tres tablas
Para cada sucursal, indicar los números y los nombres de los empleados que gestionan inmuebles, así como los inmuebles que gestionan.
SELECT b.branchNo, b.city, s.staffNo, fName, lName, propertyNo FROM Branch b, Staff s, PropertyForRent p WHERE b.branchNo = s.branchNo y s.staffNo = p.staffNo ORDER BY b.branchNo, s.staffNo, propertyNo;
85© Pearson Education Limited 1995, 2005
Ejemplo 5.26 Combinación de tres tablas
El estándar SQL proporciona formulaciones alternativas para las cláusulas FROM y WHERE:
FROM (Branch b JOIN Staff s USING branchNo) AS bs JOIN PropertyForRent p USING staffNo
86© Pearson Education Limited 1995, 2005
Tabla de resultados para el ejemplo 5.26Tabla 5.26
Ejemplo 5.27 Múltiples columnas de agrupamiento
Determinar el número de inmuebles gestionados por cada empleado.
SELECT s.branchNo, s.staffNo, COUNT(*) AS myCount
FROM Staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo
GROUP BY s.branchNo, s.staffNo
ORDER BY s.branchNo, s.staffNo;
87© Pearson Education Limited 1995, 2005
Ejemplo 5.27 Múltiples columnas de agrupamiento
88© Pearson Education Limited 1995, 2005
Cálculo de una combinaciónEl procedimiento para generar los resultados de una combinación son:
1. Formar el producto cartesiano de las tablas especificadas en las cláusulas FROM.
2. Su hay una cláusula WHERE aplicar la condición de búsqueda a cada fila de la tabla del producto, reteniendo únicamente aquellas filas que satisfagan la condición.
3. Para cada una de las filas restantes, determinar el valor de cada elemento de la lista SELECT para generar una fila de la tabla de resultados.
89© Pearson Education Limited 1995, 2005
Cálculo de una combinación
4. Si se ha especificado DISTINCT, eliminar cualesquiera filas duplicadas de la tabla de resultados.
5. Si se ha especificado ORDER BY, ordenar la tabla de resultados según se requiera.
SQL proporciona una forma especial de la instrucción SELECT para calcular el producto cartesiano:
SELECT [DISTINCT | ALL] {* | listaColumnas}
FROM Nombre Tabla1 CROSS JOIN Tabla2
90© Pearson Education Limited 1995, 2005
Combinaciones externas Si una fila de una tabla no tiene
ninguna correspondencia en la otra, se omite dicha fila de la tabla de resultados.
La combinación externa retiene aquellas filas que no satisfagan la condición de combinación.
Consideremos las siguientes tablas:
91© Pearson Education Limited 1995, 2005
Combinaciones externas
La combinación (interna) de estas dos tablas:
SELECT b.*, p.*FROM Branch1 b, PropertyForRent1 p
WHERE b.bCity = p.pCity;
92© Pearson Education Limited 1995, 2005
Tabla 27(b). Tabla de resultados para la combinación interna de las tablas Branch1 y PropertyForRent1.
Combinaciones externas
La tabla de resultados tiene dos filas en las que las ciudades son iguales.
No hay ninguna fila correspondiente a las sucursales de Bristol y Aberdeen.
Para incluir aquellas filas que no satisfagan la condición de la combinación.
93© Pearson Education Limited 1995, 2005
Ejemplo 5.28 Combinación externa izquierda
Generar un listado con todas las sucursales y los inmuebles que estén en la misma ciudad que alguna sucursal.
SELECT b.*, p.*FROM Branch1 b LEFT JOIN
PropertyForRent1 p ON b.bCity = p.pCity;
94© Pearson Education Limited 1995, 2005
Ejemplo 5.28 Combinación externa izquierda
Incluye aquellas filas de la primera tabla (la de la izquierda) que no tienen ninguna fila correspondiente en la segunda tabla (la de la derecha).
Las columnas correspondientes a la segunda tabla se rellenan con valores NULL.
95© Pearson Education Limited 1995, 2005
Tabla 5.28 Tabla de resultados para el ejemplo 5.28
Ejemplo 5.29 Combinación externa derecha
Generar un listado de todos los inmuebles y de las sucursales que estén en la misma ciudad.
SELECT b.*, p.*FROM Branch1 b RIGHT JOIN
PropertyForRent1 p ON b.bCity = p.pCity;
96© Pearson Education Limited 1995, 2005
Ejemplo 5.29 Combinación exterior derecha
Incluye aquellas filas de la segunda tabla (la de la derecha) que ni tienen ninguna fila correspondiente a la primera tabla (la de la izquierda).
Las columnas correspondientes a la primera tabla se rellenan con valores NULL.
97© Pearson Education Limited 1995, 2005
Tabla 5.29 Tabla de resultados para el ejemplo 5.29
Ejemplo 5.30 Combinación externa completa
Generar un listado con las sucursales e inmuebles que estén en la misma ciudad, sino también aquellas tablas que no tienen ninguna correspondencia.
SELECT b.*, p.*FROM Branch1 b FULL JOIN
PropertyForRent1 p ON b.bCity = p.pCity;
98© Pearson Education Limited 1995, 2005
Ejemplo 5.30 Combinación externa completa
Incluye aquellas filas de ambas tablas que no tienen ninguna fila correspondiente.
En las columnas sin correspondencia aparece NULL.
99© Pearson Education Limited 1995, 2005
Tabla 5.30 Tabla de resultados para el ejemplo 5.30
EXISTS y NOT EXISTS EXISTS y NOT EXISTS están diseñadas para utilizarlas
únicamente dentro de subconsultas.
Producen un resultado simple de tipo verdadero/falso.
Es verdadera si y sólo si existe al menos una fila en la tabla de resultados devuelta por la consulta.
Es falsa si la subconsulta devuelve una tabla de resultados vacía.
NOT EXISTS es la condición opuesta a EXISTS.
100© Pearson Education Limited 1995, 2005
EXISTS y NOT EXISTS
Puesto que (NOT) EXISTS sólo comprueba la existencia o no existencia de filas en la tabla de resultados de la subconsulta, la subconsulta puede contener cualquier número de columnas.
Resulta habitual que las subconsultas situadas a continuación de una de estas palabras clave tengan la forma:
(SELECT * ...)
101© Pearson Education Limited 1995, 2005
Ejemplo 5.31 Consulta utilizando EXISTS
Extraer todos los empleados que trabajen en una sucursal de Londres.
SELECT staffNo, fName, lName, position FROM Staff s WHERE EXISTS
(SELECT * FROM Branch b WHERE s.branchNo = b.branchNo y
city = ‘London’);
102© Pearson Education Limited 1995, 2005
Ejemplo 5.31 Consulta utilizando EXISTS
103© Pearson Education Limited 1995, 2005
Tabla 5.31 Tabla de resultados para el ejemplo 5.31
Ejemplo 5.31 Consulta utilizando EXISTS
Observe que la primera parte de la condición de búsqueda s.branchNo = b.branchNo es necesaria para garantizar que estamos considerando la fila correcta de sucursal para cada empleado.
Si se omite esta parte de la consulta, todas las filas de empleados aparecerán en la tabla de resultados de la subconsulta:
SELECT * FROM Branch WHERE city=‘London’
La subconsulta siempre sería cierta y la consulta quedaría:
SELECT staffNo, fName, lName, position FROM StaffWHERE true;
104© Pearson Education Limited 1995, 2005
Ejemplo 5.31 Consulta utilizando EXISTS
También podría reformularse por medio de la combinación:
SELECT staffNo, fName, lName, position
FROM Staff s, Branch bWHERE s.branchNo = b.branchNo y city = ‘London’;
105© Pearson Education Limited 1995, 2005
Unión, intersección y diferencia Se pueden emplear las operaciones normales de
conjuntos, unión, intersección y diferencia para combinar los resultados de dos o más consultas en una única tabla de resultados.
La unión de dos tablas, A y B,es una tabla que contiene todas las filas que están incluidas en la primera tabla A o en la segunda tabla B o en ambas.
Las intersección es una tabla que contiene todas las filas que son comunes a A y B.
La diferencia es una tabla que contiene todas filas que están contenidas en la taba A pero no en B.
Las dos tablas deben ser compatibles con respecto a la unión.
106© Pearson Education Limited 1995, 2005
Unión, Intersección, y diferencia (Except)
El formato de la cláusula del operador de conjuntos es, en todos los casos:
op [ALL] [CORRESPONDING [BY {columna1 [, ...]}]]
Si se especifica CORRESPONDING BY, la operación de conjuntos se realizará sobre las columnas designadas.
Si se especifica CORRESPONDING pero no la cláusula BY, la operación de conjuntos se realiza sobre todas las columnas que sean comunes a ambas tablas.
Si se especifica ALL el resultado puede incluir filas duplicadas.
107© Pearson Education Limited 1995, 2005
Unión, Intersección, y diferencia(Except)
108© Pearson Education Limited 1995, 2005
Unión Intersección
Diferencia
Ejemplo 5.32 Utilización de UNIóN
Construir una lista de todas las ciudades en las que exista una sucursal o un inmueble.
(SELECT cityFROM BranchWHERE city IS NOT NULL) UNION(SELECT cityFROM PropertyForRentWHERE city IS NOT NULL);
109© Pearson Education Limited 1995, 2005
Ejemplo 5.32 Utilización de UNIóN
O bien
(SELECT *FROM BranchWHERE city IS NOT NULL)UNION CORRESPONDING BY city(SELECT *FROM PropertyForRentWHERE city IS NOT NULL);
110© Pearson Education Limited 1995, 2005
Ejemplo 5.32 Utilización de UNIóN
Genera una tabla de resultados a partir de la primera consulta y otra tabla de resultados a partir de la segunda y junta las dos tablas eliminando las filas duplicadas.
111© Pearson Education Limited 1995, 2005
Tabla 5.32 Tabla de resultados para el ejemplo 5.32
Ejemplo 5.33 Utilización de INTERSECTConstruir una lista de todas las ciudades en las que haya tanto una sucursal como un inmueble.
(SELECT city FROM Branch)INTERSECT(SELECT city FROM PropertyForRent);
112© Pearson Education Limited 1995, 2005
Ejemplo 5.33 Utilización de INTERSECT
O también
(SELECT * FROM Branch)INTERSECT CORRESPONDING BY city(SELECT * FROM PropertyForRent);
113
© Pearson Education Limited 1995, 2005
Tabla 5.33 Tabla de resultados para el ejemplo 5.32
Ejemplo 5.33 Utilización de INTERSECT
Podemos reescribir esta consulta sin el operador INTERSECT:
SELECT b.cityFROM Branch b PropertyForRent pWHERE b.city = p.city;
O: SELECT DISTINCT city FROM Branch b
WHERE EXISTS(SELECT * FROM PropertyForRent pWHERE p.city = b.city);
114© Pearson Education Limited 1995, 2005
Ejemplo 5.34 Utilización de EXCEPTConstruir una lista de todas las ciudades en las que haya una sucursal pero no haya ningún inmueble.
(SELECT city FROM Branch)EXCEPT(SELECT city FROM PropertyForRent);
O
(SELECT * FROM Branch)EXCEPT CORRESPONDING BY city(SELECT * FROM PropertyForRent);
115© Pearson Education Limited 1995, 2005
Tabla 5.34 Tabla de resultados para el ejemplo 5.34
Ejemplo 5.34 Utilización de EXCEPT Esta consulta se puede reescribir sin
EXCEPT:
SELECT DISTINCT city FROM BranchWHERE city NOT IN
(SELECT city FROM PropertyForRent); O bien
SELECT DISTINCT city FROM Branch bWHERE NOT EXISTS
(SELECT * FROM PropertyForRent pWHERE p.city = b.city);
116© Pearson Education Limited 1995, 2005
INSERT
INSERT INTO NombreTabla [ (listaColumnas) ]
VALUES (listaValoresDatos)
La ListaColumnas es opcional; si se omite, SQL utilizará de forma predeterminada una lista de todas las columnas en su orden original especificado en CREATE TABLE.
Cualquier columna omitida debe haber sido declarada como NULL cuando se creo la tabla, a menos que se haya utilizado la opción DEFAULT al crear la columna.
117© Pearson Education Limited 1995, 2005
INSERT La ListaValoresDatos debe corresponderse
con la ListaColumnas de la siguiente forma: el número de elementos de cada lista debe
ser el mismo; Debe haber una correspondencia directa
entre las posiciones de los elementos de ambas listas;
El tipo de datos de cada elemento de ListaValoresDatos debe ser compatible con el tipo de datos de la correspondiente columna.
118© Pearson Education Limited 1995, 2005
Ejemplo 5.35 INSERT … VALUES
Insertar una nueva fila en la tabla Staff y suministrarle los datos para todas las columnas.
INSERT INTO StaffVALUES (‘SG16’, ‘Alan’, ‘Brown’,
‘Assistant’, ‘M’, Date‘1957-05-25’, 8300, ‘B003’);
119© Pearson Education Limited 1995, 2005
Ejemplo 5.36 INSERT utilizando valores predeterminadosInsertar una nueva fila en la tabla Staff y suministrarle datos para todas las columnas obligatorias.
INSERT INTO Staff (staffNo, fName, lName, position, salary, branchNo)VALUES (‘SG44’, ‘Anne’, ‘Jones’, ‘Assistant’, 8100, ‘B003’);
OINSERT INTO StaffVALUES (‘SG44’, ‘Anne’, ‘Jones’, ‘Assistant’, NULL, NULL, 8100, ‘B003’);
120© Pearson Education Limited 1995, 2005
INSERT … SELECT
La segunda forma de la instrucción INSERT permite copiar múltiples filas de una o más tablas en otra tabla:
INSERT INTO NombreTabla [ (ListaColumnas) ]
SELECT ...
121© Pearson Education Limited 1995, 2005
Ejemplo 5.37 INSERT … SELECT
Suponga que hay una tabla StaffPropCount que contiene los nombres de los empleados y el número de inmuebles que gestionan.
StaffPropCount(staffNo, fName, lName, propCnt)
Populate StaffPropCount using Staff y PropertyForRent tables.
122© Pearson Education Limited 1995, 2005
Ejemplo 5.37 INSERT … SELECTINSERT INTO StaffPropCount
(SELECT s.staffNo, fName, lName, COUNT(*)FROM Staff s, PropertyForRent pWHERE s.staffNo = p.staffNoGROUP BY s.staffNo, fName, lName)UNION(SELECT staffNo, fName, lName, 0FROM StaffWHERE staffNo NOT IN
(SELECT DISTINCT staffNo FROM PropertyForRent));
123© Pearson Education Limited 1995, 2005
Ejemplo 5.37 INSERT … SELECT
Si omitimos la segunda parte de la operación UNION, excluye a aquellos empleados que no gestionan ningún inmueble en la actualidad.
124© Pearson Education Limited 1995, 2005
UPDATEUPDATE NombreTabla SET NombreColumna1 = valorDatos1
[, NombreColumna2 = valorDatos2...][WHERE condiciónBúsqueda]
NombreTabla puede ser el nombre de una tabla base o de una vista actualizable.
La cláusula SET especifica los nombres de una o más columnas que hay que actualizar.
125© Pearson Education Limited 1995, 2005
UPDATE
La cláusula WHERE es opcional: Si se omite, las columnas especificadas se
actualizarán para todas las filas de la tabla; Si se especifica sólo se actualizarán
aquellas filas que satisfagan la condiciónBúsqueda.
Los nuevos valores valorDatos deben ser compatibles con ,os tipos de datos de las columnas correspondientes.
126© Pearson Education Limited 1995, 2005
Ejemplo 5.38/39 Actualización de todas las filas mediante UPDATE
Incrementar en un 3% el salario de todos los empleados.
UPDATE Staff
SET salary = salary*1.03;
Incrementar un 5% el salario de todos los gerentes.
UPDATE Staff
SET salary = salary*1.05
WHERE position = ‘Manager’;
127© Pearson Education Limited 1995, 2005
Ejemplo 5.40 Actualización de múltiples columnas mediante UPDATEAscender a David Ford (staffNo=‘SG14’) a gerente e incrementar su salario a 18000 euros.
UPDATE StaffSET position = ‘Manager’, salary =
18000WHERE staffNo = ‘SG14’;
128© Pearson Education Limited 1995, 2005
DELETEDELETE FROM NombreTabla [WHERE condiciónBúsqueda]
NombreTabla puede ser el nombre de una base o de una vista actualizable.
condiciónBúsqueda es opcional; si se la omite, todas las filas serán borradas de la tabla. Esto no hace que se borre la propia tabla; Si se especifica condiciónBúsqueda, sólo se borrarán aquellas filas que satisfagan la condición indicada.
129© Pearson Education Limited 1995, 2005
Ejemplo 5.41/42 Borrado de filas específicas mediante DELETE
Borrar todas las filas relativas al inmueble PG4.
DELETE FROM ViewingWHERE propertyNo = ‘PG4’;
Borrar todas las filas de la tabla de búsqueda.
DELETE FROM Viewing;
130© Pearson Education Limited 1995, 2005