1. No category

Bases de Datos
SQL
Consultas Combinadas

Habitualmente cuando necesitamos recuperar la información
de una base de datos nos encontramos con que dicha
información se encuentra repartida en varias tablas,
referenciadas a través de varios códigos.

De este modo si tuviéramos una tabla de coches con un campo
marca, dicho campo contendría el código de la marca de la
tabla de marcas.
Consultas Combinadas

Sin embargo está forma de almacenar la información no resulta
muy útil a la hora de consultar los datos. SQL nos proporciona
una forma fácil de mostrar la información repartida en varias
tablas, las consultas combinadas o JOINS.

Las consultas combinadas pueden ser de tres tipos:

Combinación interna

Combinación externa

Uniones
Combinación Interna

Nos permite mostrar los datos de dos o más tablas a través de
una condición WHERE.

Para realizar la consulta combinada debemos escribir una
consulta SELECT en cuya claúsula FROM escribiremos el
nombre de dos tablas, separados por comas, y una condición
WHERE que obligue a que el código de marca de la tabla de
coches sea igual al código de la tabla de marcas.
Combinación Interna

Demonos cuenta que hemos antepuesto el nombre de cada
tabla a el nombre del campo, esto no es obligatorio si los
nombres de campos no se repiten en las tablas, pero es
aconsejable para evitar conflictos de nombres entre campos
(ambiguedad).

Por ejemplo, si para referirnos al campo marca no
anteponemos el nombre del campo la base de datos no sabe si
queremos el campo marca de la tabla tCoches, que contiene el
código de la marca, o el campo marca de la tabla tMarcas, que
contiene el nombre de la marca.
Combinación Interna

Otra opción es utilizar la cláusula INNER JOIN. Su sintaxis es
idéntica a la de una consulta SELECT habitual, con la
particularidad de que en la cláusula FROM sólo aparece una
tabla o vista, añadiéndose el resto de tablas a través de
cláusulas INNER JOIN.
Combinación Interna

El ejemplo anterior escrito utilizando la clausula INNER
JOIN quedaría de la siguiente manera:
Combinación Externa

La combinación interna es excluyente. Esto quiere decir que si
un registro no cumple la condición de combinación no se
incluye en los resultados. De este modo en el ejemplo anterior
si un coche no tiene grabada la marca no se devuelve en mi
consulta.

Según la naturaleza de nuestra consulta esto puede ser una
ventaja , pero en otros casos significa un serio problema. Para
modificar este comportamiento SQL pone a nuestra disposición
la combinación externa. La combinación externa no es
excluyente.
Combinación Externa

La sintaxis es muy parecida a la combinación interna

La combinación externa puede ser diestra o siniestra, LEFT OUTER
JOIN o RIGHT OUTER JOIN. Con LEFT OUTER JOIN obtenemos
todos los registros de en la tabla que situemos a la izquierda de la
clausula JOIN, mientras que con RIGHT OUTER JOIN obtenemos el
efecto contrario.
Combinación Externa

Como mejor se ve la combinación externa es
con un ejemplo de LEFT OUTER JOIN

Esta consulta devolverá todos los registros de
la tabla tCoches, independientemente de que
tengan marca o no. En el caso de que el
coche no tenga marca se devolverá el valor
null para los campos de la tabla tMarcas.
Combinación Externa

Como mejor se ve la combinación externa es
con un ejemplo de RIGHT OUTER JOIN

Esta consulta devolverá los registros de la
tabla tCoches que tengan marca relacionada
y todos los registros de la tabla tMarcas,
tengan algún registro en tCoches o no.

Visualmente (la consulta devuelve los datos
en azul) ...
Combinación Unión

La cláusula UNION permite unir dos o más conjuntos de
resultados en uno detrás del otro como si se tratase de
una única tabla. De este modo podemos obtener los
registros de mas de una tabla "unidos".

La sintaxis corresponde a la de varias SELECT unidas a
través de UNION, como se muestra a continuación:
Combinación Unión


Para utilizar la clausula UNION debemos cumplir una
serie de normas.

Las consultas a unir deben tener el mismo número campos,
y además los campos deben ser del mismo tipo.

Sólo puede haber una única clausula ORDER BY al final de
la sentencia SELECT.
El siguiente ejemplo muestra el uso de UNION
Combinación Unión

Puede observarse el uso de la constante cero en la segunda
lista de selección para hacer coincidir el número y tipo de
campos que devuelve la consulta UNION.
Vistas
Una vista es una tabla virtual cuyo
contenido está definido por una consulta.
Al igual que una tabla real, una vista
consta de un conjunto de columnas y filas
de datos que proceden de tablas a las que
se hace referencia en la consulta y se
producen de forma dinámica cuando se
hace referencia a la vista.
Vistas
Utilización de vistas
• Para centrarse en datos específicos
• Para simplificar la manipulación de
datos
Utilización de vistas
• Para proporcionar compatibilidad con
versiones anteriores
Vistas encriptadas vs no
encriptadas

Una vista no cifrada nos permite obtener información acerca
de su definición debido a que es posible que necesite ver la
definición de la vista para entender cómo derivan sus datos de
las tablas de origen o para ver los datos que ella misma define.

Una vista no encriptada es aquella cuya secuencia de
comandos no ha sido encriptada, de modo que la columna
VIEW_DEFINITION
de
la
vista
INFORMATION_SCHEMA.VIEWS expone legiblemente al usuario
la T-SQL que genera la vista.

Para crear una vista no encriptada de manera predeterminada
no se utiliza el atributo ENCRYPTION.
Muchas
Gracias…
Consulta Combinadas (Joins)
--------------------------------------------------------------------------CONSULTAS DE VARIAS TABLAS (Producto cartesiano de filas)-------------------------------------------------------------------------- Si no se hacen coincidir los valores de las columnas relacionadas se ob
tiene gran duplicidad de filas. Tantas como
- el producto cartesiano de las filas de las tablas a las que se hace ref
erencia. Ejemplo:
select * from dbo.Products
select * from dbo.Orders
select * from dbo.Products, dbo.Orders
--------------------------------------------------------------------------UTILIZAR ALIAS EN UNA TABLA-------------------------------------------------------------------------- Cuando se relacionan varias tablas es normal que una misma columna form
e parte de varias tablas. Para evitar
- errores de nombres duplicados podemos hacer dos cosas. Una es utilizar
la sintaxis de nombre completo:
-- NombreTabla.NombreColumna.
select * from dbo.Products, dbo.Categories
where dbo.Products.CategoryID = dbo.Categories.CategoryID
-- La otra forma es dar un alias a cada tabla. Ejemplo:
select * from dbo.Products P, dbo.Categories C
where P.CategoryID = C.CategoryID
--------------------------------------------------------------------------INSTRUCCIÓN JOIN ON(Coincidencia INTERNAS de columnas)--------------------------------------------------------------------------NOTA: La instrucción INNER JOIN
ue es el valor predeterminado.
--TEORÍA--
es exactamente lo mismo que JOIN, dado q
- La instrucción JOIN nos permite combinar varias tablas haciendo coincid
ir los valores de las columas que nos
- interesen. Es decir, si tenemos dos tablas A y B que contienen una (o v
arias) columnas con el mismo nombre,
-- podemos relacionar ambas tablas por la columna del mismo nombre.
- Por cada registro de la columna en la tabla A que también esté en la co
lumna de la tabla B, obtendremos un
- una relación. Lo que quiere decir que se produce un producto cartesiano
de cada valor de la columna de la tabla A,
-- por todos los valores coincidentes de la columna en la tabla B.
--VOLVIENDO A LA BASE DE DATOS NORTHWIND-- 1. Por ejemplo, la tabla empleados nos facilita bastante información de
los mismos. La más significativa es el
- número de empleado, el nombre y la ciudad donde vive. Sin embargo, no n
os dice nada de las ventas efectuadas por
-- cada empleado.
- Si miramos la tabla de ventas veremos que además del número de ventas,
tenemos información del empleado que
- realizo estas ventas. Por lo tanto, por medio de la columna EmployeeID
presente en las dos tablas podemos
-- relacionar los empleados con el número de venta. Ejemplo:
select OrderID, LastName, FirstName, City
from dbo.Orders O join dbo.Employees E
on
O.EmployeeID = E.EmployeeID
order by OrderID
- De este modo podemos concluir que la columna que pertenece a una tabla
A y otra B sirve de nexo para relacionar
- los datos de otras columnas de la tabla A que no estan incluidos en la
tabla B y viceversa.
-2. Otro caso es la tabla de productos que nos da mucha información sobre
los mismos pero apenas nos dice nada de
- la categoría a la que pertenece cada producto. Mostremos una tabla prod
uctos personal que muestre la información
- más interesante de los productos, más el nombre de la categoría y una d
escripción de esta.
select ProductID, ProductName, C.CategoryID, CategoryName, Description, U
nitPrice, UnitsInStock
from dbo.Products P join dbo.Categories C
on P.CategoryID = C.CategoryID
-3. COMBINACIÓN DE TRES TABLAS. Si nos fijamos en las tablas de empleados
y de clientes nos damos cuenta que no hay
- forma de relacionar que empleado atendió (o vendió) a que cliente. Pode
mos hacerlo a través de la tabla de ventas
-- que tiene los números de empleados y de clientes.
select E.EmployeeID, LastName, FirstName, OrderID, C.CustomerID, CompanyN
ame, ContactName
from dbo.Orders O
join dbo.Employees E on O.EmployeeID = E.EmployeeID
join dbo.Customers C on O.CustomerID = C.CustomerID
order by EmployeeID
-4. Puede darse el caso en que solo interese esta información para los cli
entes de España.
select E.EmployeeID, LastName, FirstName, OrderID, C.CustomerID, CompanyN
ame, ContactName
from dbo.Orders O
join dbo.Employees E on O.EmployeeID = E.EmployeeID
join dbo.Customers C on O.CustomerID = C.CustomerID
where C.Country = 'Spain'
order by EmployeeID
-5. La instrucción JOIN también podemos utilizarla para sustituir a las su
bconsultas. La forma de hacerlo es dando
-- dos alias diferentes a una misma tabla. Ejemplo:
--
(Mostrar los empleados que son mayores que el empleado 5 (1955-03-
04) ).
-- Técnica de subconsultas.
select EmployeeID, LastName, FirstName, BirthDate from dbo.Employees
where BirthDate > (select BirthDate from dbo.Employees
where EmployeeID = 5)
-- Instrucción JOIN.
select E1.EmployeeID, E1.LastName, E1.FirstName, E1.BirthDate from dbo.Em
ployees E1 join dbo.Employees E2
on E1.BirthDate > E2.BirthDate
where E2.EmployeeID = 5
--------------------------------------------------------------------------INSTRUCCIÓN OUTER JOIN (Coincidencias EXTERNAS de columnas)-------------------------------------------------------------------------- Puede darse el caso que nos interese mostrar todos los valores de una c
olumna (todas las filas) aunque no tengan
-- correspondencia en la otra tabla. Así podemos tener 3 casos:
-1.Mostrar todos los valores de la tabla IZQ (LEFT), con NULL para la tabl
a DCH cuando no hay correspondencia
select ProductID, ProductName, C.CategoryID, CategoryName, C.Description,
UnitPrice, UnitsInStock
from dbo.Products P left outer join dbo.Categories C
on P.CategoryID = C.CategoryID
-2.Mostrar todos los valores de la tabla DCH (RIGHT), con NULL para la tab
la IZQ cuando no hay correspondencia.
select ProductID, ProductName, C.CategoryID, CategoryName, C.Description,
UnitPrice, UnitsInStock
from dbo.Products P right outer join dbo.Categories C
on P.CategoryID = C.CategoryID
-3.Mostrar todos los valores de ambas tablas (FULL) con NULL cuando no hay
correspondencia.
select ProductID, ProductName, C.CategoryID, CategoryName, C.Description,
UnitPrice, UnitsInStock
from dbo.Products P full outer join dbo.Categories C
on P.CategoryID = C.CategoryID
-NOTA: La sentencia OUTER es opcional. Al incluir las sentencias LEFT, RIG
HT Y FULL el sistema sabe que es una
--
consulta de combinación externa. Ejemplo anterior:
select ProductID, ProductName, C.CategoryID, CategoryName, C.Description,
UnitPrice, UnitsInStock
from dbo.Products P full join dbo.Categories C
on P.CategoryID = C.CategoryID
-------------------------------------------------------------------------- INSTRUCCIÓN UNION (Unión de filas en distintas tablas)--------------------------------------------------------------------------1. Unir (UNION) todas (ALL) las filas de dos columnas de tablas diferente
s.
select City from dbo.Employees
union all
select City from dbo.Customers - Devuelve la suma de todas las filas en ambas columnas.
-2. Unir (UNION) las filas de dos columnas de tablas diferentes sin repeti
r ningún valor.
select City from dbo.Employees
union
select City from dbo.Customers - Devuelve la suma de todas las filas en ambas columnas pero con valores
únicos.
-NOTA: Se debe cumplir que las columnas en ambas instruciones SELECT coinc
idan en el tipo de datos.
------------------------------------------------------------------------- INSTRUCCIÓNES EXCEPT Y INTERSECT (Diferencia e Intersección de conjunto
s)--------------------------------------------------------------------------- EXCEPT - Devuelve los valores de la primera consulta que no se encuentran en la
segunda.
select City from dbo.Employees
except
select City from dbo.Customers
-- INTERSECT - Devueleve una intersección de todos los valores, es decir, solo los que
se encuentran ambas columnas.
select City from dbo.Employees
intersect
select City from dbo.Customers
-------------------------------------------------------------------------
Consulta SQL Server para Conocer las Tablas y Vistas de una Base
de Datos
En Sql Server tenemos una sencilla consulta que nos devolverá las tablas y vistas de una base
de datos:
SELECT * from Information_Schema.Tables
De aquí nos interesa el campo table_name (nombre de la tabla) y table_type (nos dice si es
una tabla o una vista). Por tanto, filtrar por tablas o vistas es bastante sencillo (con el campo
table_type).
Para saber si existe una tabla en la base de datos (también nos sirve para las vistas) podemos
utilizar la siguiente consulta (vamos a consultar si en Northwind existe la tabla ‘Customers’):
SELECT * from Information_Schema.Tables where table_name=’Customers’
Si esta consulta nos devuelve registros es que existe la tabla (o vista) y si nos devuelve vacío,
es que no existe.
Vistas
Usando la base de datos northwind crearemos una vista que selecciona las
cinco clientes de la tabla Customers visualizando los siguientes campos:
CustomerID, CompanyName, ContactName, Country, city, y además con la
condición de que el nombre de la ciudad a la que pertenecen empiece con la
letra M, ordenándolos por el campo companyname.
Se dará; cuenta que no usamos el atributo ENCRYPTION, o en todo caso, no
se usa la cláusula WITH ENCRYPTION. Podemos comprobar esto al momento
de ejecutar la siguiente sentencia (explicada en el cuadro anterior):
En contraste, para generar una vista encriptada se usa la cláusula WITH
ENCRYPTION que se encargará de encriptar la T-SQL de la vista. En este cuadro
es explica a detalle todo el proceso de generación y consulta de la vista
encriptada.
Pues ahora usted verá que el campo view_definition está encriptado,
negándonos de esta manera leer la secuencia de comandos que define esta vista
viewCustomersEncrypted.
Base de datos I. Guía 5
1
Facultad:
Ingeniería
Escuela:
Computación
Asignatura: Base de datos I
Tema: Uso de sentencias SQL
Objetivo
•
•
Modificar, eliminar e insertar registros en una tabla
Listar los registros de una tabla utilizando la sentencia select
Materiales y Equipo
• Computadora con SQL Server 2008.
• Guía Número 5
Introducción
En esta guía terminaremos de examinar los elementos que pueden ir en una
instrucción SELECT así como las instrucciones que nos ayudan a definir una
estructura de una base de datos y las instrucciones para poder realizar
modificaciones a los objetos de la base de datos.
Sintaxis general de la consulta SELECT
SELECT <lista de columnas>
[FROM <tabla(s) de origen >]
[WHERE <condición restrictiva >]
[GROUP BY <nombre de columna o expresión que utiliza una
columna en la lista de selección>]
[HAVING <condición restrictiva basadas en los resultados de
GROUP BY>]
[ORDER BY <listas de columna>]
Ejemplo:
USE NORTHWIND
SELECT OrderID, Quantity As [Sin nombre de columna]
FROM [Order Details]
WHERE OrderID BETWEEN 11000 AND 11002
2
Base de datos I, Guía 5
Nota: Observe el uso de corchetes en esta consulta. Se debe de utilizar
corchetes si el nombre de un objeto (en este caso una tabla) tiene espacios
de por medio en él, tenemos que delimitar el nombre utilizando corchetes o
comillas simples, lo que permite a SQL Server saber dónde empieza y dónde
termina el nombre.
Resultado de la consulta anterior es el siguiente:
Aunque se ha solicitado sólo tres pedidos, se están viendo cada línea
individual de detalle del pedido. Podríamos utilizar una calculadora o
podríamos utilizar la cláusula GROUP BY con un agregado (en este caso
vamos a utilizar SUM ( ) ) para obtener el total deseado.
SELECT OrderID, SUM (Quantity) As [Sin nombre de columna]
FROM [Order Details]
WHERE OrderID BETWEEN 11000 AND 11002
GROUP BY OrderID
Así obtiene lo que se está buscando:
Base de datos I. Guía 5 3
Lo que hizo fue agrupar en una suma los ORDER ID de los resultados, es decir
en la primera consulta aparecían
ORDER ID
11000
11000
11000
Sin nombre de columna
25
30
30
Con la función sum lo que hace es agrupar(en este caso por el ORDER ID) y
realizar la suma, por eso da el resultado
ORDER ID
11000
Sin nombre de columna
85
TIPS: Puedo agregarle un titulo a la columna sin nombre con AS Como es
de suponer, la función SUM devuelve totales; ¿pero totales de qué? Si no
hubiésemos suministrado la cláusula GROUP BY, la función SUM habría
sumado todos los valores de todas las filas para la columna con nombre.
Sin embargo, en este caso hemos suministrado una cláusula GROUP BY y,
por tanto, el total proporcionado por la función SUM es el total de cada grupo.
También podemos agrupar basándonos en múltiples columnas. Para ello,
sólo tenemos que añadir una coma y el nombre de la siguiente columna.
Funciones de agregado
Las funciones de agregado realizan un cálculo sobre un conjunto de valores y
devuelven un solo valor. Con la excepción de COUNT, las funciones de agregado
omiten los valores NULL. Las funciones de agregado se suelen utilizar con la
cláusula GROUP BY de la instrucción SELECT.
COUNT:
Devuelve el número de elementos de un grupo (conjunto de resultados).
SELECT COUNT(*) FROM Employees
AVG
Devuelve la media de los valores de un grupo. Los valores nulos se pasan por
alto al probar a ejecutar la misma consulta que ejecutamos anteriormente,
pero ahora vamos a modificarla para que devuelva el promedio de la
cantidad por pedido en lugar del total de cada pedido:
4
Base de datos I, Guía 5
SELECT OrderID, AVG (Quantity) AS promedio
FROM [Order Details]
WHERE OrderID BETWEEN 11000 AND 11002
GROUP BY OrderID
El resultado es:
MAX / MIN:
MAX devuelve el valor máximo de la expresión o del valor de una columna y MIN
lo contrario.
SELECT OrderID, MIN(Quantity) AS promedio
FROM [Order Details]
WHERE OrderID BETWEEN 11000 AND 11002
GROUP BY OrderID
NOTA: En realidad todas las funciones de agregado ignoran los valores
NULL excepto COUNT (*).
Agrupar condiciones con la cláusula HAVING. Hasta el momento, hemos
aplicado todas nuestras condiciones a filas específicas. Si una determinada
columna en una fila no tiene un valor especifico o no se encuentra dentro de un
rango de valores, se omitirá toda la fila, y todo ello antes de pensar
siquiera en las agrupaciones.
¿Qué pasaría si deseáramos agrupar condiciones? Es decir, ¿Qué pasaría
si deseáramos que todas las filas se agregasen a un grupo pero sólo
cuando los grupos se hubiesen acumulado completamente estaríamos
preparados para aplicar la condición? Bueno, aquí es donde entra en acción la
cláusula HAVING.
La cláusula HAVING sólo se utiliza si también existe una cláusula
GROUP BY en la consulta. Mientras la cláusula WHERE se aplica a
todas las filas antes incluso de tener la oportunidad de convertirse en
parte de un grupo, la cláusula HAVING se aplica al valor agregado de
dicho grupo.
Ejemplo:
Base de datos I. Guía 5 5
Primero vamos a realizar un ejemplo que no lleve la cláusula HAVING y
después vamos a utilizar el mismo ejemplo, pero ahora con la cláusula HAVING.
Sin HAVING
SELECT OrderID, SUM(Quantity) AS TOTAL
FROM [Order Details]
GROUP BY OrderID
El resultado es:
(830
filas afectadas)
Lamentablemente, es bastante difícil analizar una lista tan larga. Por tanto, vamos
a dejar que SQL Server reduzca esta lista para ayudarnos a realizar
nuestro análisis. Supongamos que sólo estamos interesados en cantidades
de pedidos más grandes. ¿Podemos modificar la consulta para que
devuelva la misma información pero limitándose a los pedidos cuya cantidad
total esté por encima de 300? La respuesta es sí, y es tan fácil como
añadir la cláusula HAVING.
6
Base de datos I, Guía 5
Con HAVING
SELECT OrderID, SUM(Quantity) AS TOTAL
FROM [Order Details]
GROUP BY OrderID
HAVING SUM(Quantity) > 300
El resultado es:
Como puede comprobar, podemos reducir rápidamente la lista hasta los
elementos que nos interesan.
INSTRUCCION SELECT
Ejemplo:
USE Northwind
SELECT * FROM Employees
En la instrucción anterior la primera línea indica que utilizaremos la base de datos
Northwind.
La segunda line se ha pedido seleccionar información (SELECT puede pensar en
ello como en pedir que se muestre o consulte información). El asterisco * podría
parecer algo extraño, pero realmente funciona igual que en cualquier otro sitio: es
un comodín.
Cuando escribimos SELECT * estamos indicando que deseamos seleccionar
todas las columnas de la tabla (En este caso la tabla Employees.) FROM indica
que hemos terminado de especificar los elementos que deseamos ver y estamos a
punto de indicar el origen de la información (el origen es la tabla Employees).
Ejemplo:
USE AdventureWorks
SELECT FirstName, Lastname, EmailAddress FROM Person.Contact
En esta consulta solamente se piden los campos FirstName, Lastname y
EmailAddress de la table Person.Contact
Base de datos I. Guía 5 7
LA CLAUSULA WHERE.
Esta cláusula nos permite colocar condiciones sobre los resultados deseados.
Los ejemplos que hemos visto hasta el momento son consultas SELECT sin
restricciones en el sentido de que se han incluido en el resultado todas las filas de
la tabla especificada. Las consultas sin restricciones son muy útiles para rellenar
cuadros de lista y cuadros combinados y en otros escenarios en los que
intentamos proporcionar un listado de dominio.
Ejemplo:
Ahora vamos a buscar información más específica de la tabla Person.Address de
la base de datos AdventureWorks:
USE AdventureWorks
SELECT City, AddressLine1, PostalCode,StateProvinceID
FROM Person.Address
WHERE StateProvinceID = 74
Los primeros 3 resultados son:
City
Nevada
Salt Lake
City
Salt Lake
City
AddressLine1
2487 Riverside Drive
683 Larch Ct.
PostalCode
84407
84101
6119 11th
84101
Ejemplo:
Esta sentencia listara productos que tengan un precio único mayor a 60
USE NORTHWIND
SELECT * FROM dbo.Products WHERE UnitPrice >60.00
Esta sentencia listara todas los campos de la tabla categoria donde el nombre de
categoria tenga coincidencia con la palabra produ(en cualquier parte del nombre)
SELECT * FROM dbo.Categories WHERE CategoryName LIKE '%produ%'
OPERADORES QUE PODEMOS USAR CON LA CLAUSULA WHERE
8
Base de datos I, Guía 5
Insertando datos en una Tabla.
Base de datos I. Guía 5 9
El comando INSERT inserta una nueva fila en la tabla, al llenar las columnas con
valores específicos.
Sintaxis:
INSERT INTO <tabla> [(lista de columnas)] VALUES (valores_de_datos)
Ejemplo:
INSERT
INTO
demo(cod_sucursal,nombre,apellido,telefono)
(14,'Julia','Morales','22552100')
VALUES
Eliminando datos de una tabla.
El comando DELETE elimina filas de una tabla o vista, que satisfagan una
condición específica.
Sintaxis:
DELETE FROM table_name WHERE condición.
Ejemplo:
DELETE FROM demo where nombre=’julia’
Si no se incluye la cláusula WHERE, se eliminarán todas las filas en la tabla
indicada.
Actualizando datos de una tabla.
Este comando permite la actualización de uno o más campos de una fila o grupo
de filas de una tabla o vista. Las sentencias UPDATE se utilizan para modificar
datos existentes.
Sintaxis:
UPDATE nombre_tabla | nombre_vista SET (nombre_columna = expresion |
DEFAULT | NULL)
WHERE (condición)
Ejemplo:
UPDATE demo SET cod_sucursal = ‘55’ WHERE apellido= ‘Morales’
Set: especifica la lista de columnas que se actualizarán.
10 Base de datos I, Guía 5
Procedimiento
Ejercicio No 1
Realizar lo siguiente:
1.
Crear
una
Base
SUCARNET_PRACTICA3.
de
datos
con
el
siguiente
nombre:
Nota: recuerde que SUCARNET se refiere a su código de carné como estudiante
de la UDB
2. Poner en uso la base de datos que acaba de crear con el comando USE.
Ejemplo: USE SUCARNET_PRACTICA3
3. Crear las siguientes tablas dentro de la base de datos:
TABLA : ESTUDIANTES
CAMPO
TIPO DE DATO
LONGITUD
IdEstudiante INT
Nombres
VARCHAR
25
Apellidos
VARCHAR
25
Direccion
VARCHAR
50
Telefono
VARCHAR
8
sexo
char
1
TABLA : MATERIAS
CAMPO
TIPO DE DATO
IdMateria
INT
Materia
VARCHAR
LONGITUD
30
TABLA : NOTAS
CAMPO
TIPO DE DATO
IdEstudiante
INT
LONGITUD
Base de datos I. Guía 5 11
IdMateria
INT
Nota
DECIMAL
(10,2)
4. Insertar los siguientes datos a las tablas creadas anteriormente.
IdEstudiante
Nombres
01
Maria
TABLA ESTUDIANTES
Apellidos
Dirección
HERNANDEZ
COL. SANTA
Teléfono
Sexo
2254212
F
2609834
M
ISABEL
02
OSCAR
MEJIA
FINAL 4 CALLE
OTE.
03
HILARIO
URRUTIA
Fnal cl progreso
2907834
M
04
JOSE
QUEZADA
Mejicanos
23663322
M
URRUTIA
Santa Tecla
2778934
M
JOSE
05
ELIAS
ALFREDO
TABLA MATERIAS
IdMateria
Materias
111
Base de datos II
114
Ingenieria del Software
115
SQL SERVER
TABLA NOTAS
IdEstudiante IdMateria Nota
01
111
7
01
114
6.0
01
115
4
02
111
6
02
114
10
02
115
8.0
12 Base de datos I, Guía 5
5. Eliminar registros.
Eliminar los registros de la tabla NOTAS; su IdMateria sea igual a 111
DELETE FROM NOTAS WHERE IdMateria = 113
6. Actualizar registros.
Modificar los registros de la tabla NOTAS para el IdEstudiante = 2 y IdMateria =
115, la nota a modificar es 8.0 a 9.0
UPDATE
NOTAS
SET
Nota=9.0
WHERE
IdEstudiante
=1
AND
IdMateria=115
7-Modificar el nombre de la materia Ingeniería del Software por Desarrollo
del Software
8- Eliminar a todos los alumnos que tengan apellido URRUTIA
9-Listar todos los estudiantes la tabla estudiantes
10-listar los apellidos y la direccion de los estudiantes que tienen id=1
ASIGNACION
A continuación deberá realizar diferentes consultas con distintos niveles de
dificultad, utilizando en algunas de ellas diferentes funciones asociadas con la
sentencia SELECT:
1)
2)
3)
4)
5)
Se desea un listado de los alumnos ordenados ascendentemente apellido
Se desea conocer cuántos estudiantes hay por cada género
Se desea conocer la nota promedio del alumno con id 1
Se desea conocer la nota mayor de las evaluaciones
De la tabla notas que estudiantes tienen promedios mayor o igual que 6
Bibliografía
Francisco Charte Ojeda, SQL Server 2008. Madrid, España : ANAYA, 2009 1era edicion
Base de datos I. Guía 5 13
Guía
5:
USO
Hoja de cotejo:
DE SENTENCIAS
SQL
Alumno:
Máquina No:
Docente:
GL:
Fecha:
EVALUACION
%
CONOCIMIENTO
Del 20
al 30%
APLICACIÓN
DEL
CONOCIMIENTO
Del 40%
al 60%
1-4
5-7
8-10
Conocimie
nto
deficient
e de los
fundament
os
teóricos
Conocimiento
y explicación
incompleta de
los
fundamentos
teóricos
Conocimiento
completo y
explicación
clara de los
fundamentos
teóricos
No tiene
actitud
proactiva
.
Actitud
propositiva y
con
propuestas no
aplicables al
contenido de
la guía.
Tiene actitud
proactiva y sus
propuestas son
concretas.
ACTITUD
Del 15%
al 30%
TOTAL
100%
Nota
5
Base de datos I. Guía 6
1
Facultad:
Ingeniería
Escuela:
Computación
Asignatura: Base de datos I
Tema: Combinación de tablas
Objetivo
•
•
•
Utilice alias en los nombres de las tablas.
Combine datos de varias tablas mediante combinaciones.
Combinación de varios conjuntos de resultados en un único conjunto de resultados
mediante el operador UNION.
Materiales y Equipo
• Computadora con SQL Server 2008.
• Guía Número 6
• Base de datos de ejemplo Joindb y Library
Introducción
Clave primaria
Una clave primaria es un campo (o varios) que identifica un solo registro (fila) en una
tabla.
Para un valor del campo clave existe solamente un registro.
create table usuarios(
nombre varchar(20),
clave varchar(10),
primary key(nombre)
);
Clave foranea
Con la restricción "foreign key" se define un campo (o varios) cuyos valores coinciden con
la clave primaria de la misma tabla o de otra, es decir, se define una referencia a un
campo con una restricción "primary key" o "unique" de la misma tabla o de otra.
La integridad referencial asegura que se mantengan las referencias entre las claves
primarias y las externas. Por ejemplo, controla que si se agrega un código de editorial en
la tabla "libros", tal código exista en la tabla "editoriales".
2
Base de datos I, Guía6
También controla que no pueda eliminarse un registro de una tabla ni modificar la clave
primaria si una clave externa hace referencia al registro. Por ejemplo, que no se pueda
eliminar o modificar un código de "editoriales" si existen libros con dicho código.
alter table NOMBRETABLA1
add constraint NOMBRERESTRICCION
foreign key (CAMPOCLAVEFORANEA)
references NOMBRETABLA2 (CAMPOCLAVEPRIMARIA);
Ejemplo
Se tienen las tablas VENTAS y VENDEDOR, dichas tablas deberán relacionarse por el
idvendedor que es la llave primaria en la tabla VENDEDOR y es foránea en la tabla
VENTAS.
El script es el siguiente
--Creación de BD
CREATE Database ventas
GO
USE ventas
GO
---tabla vendedor
CREATE TABLE VENDEDOR(
idvendedor INT PRIMARY KEY ,
nombre VARCHAR(20),
apellido VARCHAR(10),
);
GO
---tabla ventas
CREATE TABLE VENTAS(
Idventa INT PRIMARY KEY ,
nombre VARCHAR(20),
apellido VARCHAR(10),
idvendedor INT
foreign key (idvendedor) references VENDEDOR(idvendedor))
Cuando creamos relaciones en SQL Server, este nos permite crear un diagrama
relacional de las tablas de la base de datos, como se muestra en la siguiente figura.
Base de datos I. Guía 6 3
Uso de alias en los nombres de tablas.
El uso de alias en los nombres de tablas mejora la legibilidad de las secuencias de
comandos, facilita la escritura de combinaciones complejas y simplifica el mantenimiento
de Transact-SQL.
Al escribir secuencias de comandos, puede sustituir un nombre de tabla descriptivo largo
y complejo por un alias sencillo y abreviado. El alias se utiliza en lugar del nombre
completo de la tabla.
Sintaxis parcial:
aliasTabla
SELECT * FROM servidor.baseDeDatos.esquema.tabla AS
Ejemplo 1:
En este ejemplo se muestran los nombres de los clientes, el identificador del cliente y la
cantidad vendida de las tablas buyers y sales. Esta consulta no utiliza alias en las tablas
de la sintaxis de JOIN.
USE joindb
SELECT buyer_name, sales.buyer_id, qty
FROM buyers
INNER JOIN sales
ON buyers.buyer_id = sales.buyer_id
GO
Ejemplo 2:
En este ejemplo se muestran los nombres de los clientes, el identificador del cliente y la
cantidad vendida de las tablas buyers y sales. Esta consulta utiliza alias en las tablas de
la sintaxis de JOIN.
USE joindb
SELECT buyer_name, s.buyer_id, qty
FROM buyers AS b
INNER JOIN sales AS s
ON b.buyer_id = s.buyer_id
GO
Ahora la tabla buyers se
le podrá conocer con el
alias b y la tabla sales
se podrá conocer con el
alias s
NOTA:
Algunas veces, la compleja sintaxis de JOIN y las subconsultas deben usar alias en los
nombres de tablas. Por ejemplo, al combinar una tabla consigo misma deben utilizarse
alias.
4
Base de datos I, Guía6
• Combinación de datos de varias tablas:
Una combinación es una operación que permite consultar dos o más tablas para producir
un conjunto de resultados que incorpore filas y columnas de cada una de las tablas. Las
tablas se combinan en función de las columnas que son comunes a ambas tablas.
Cuando se combinan tablas, Microsoft® SQL Server™ compara los valores de las
columnas especificadas fila por fila y, después, utiliza los resultados de la comparación
para combinar los valores que cumplan los criterios especificados en nuevas filas.
Hay tres tipos de combinaciones: combinaciones internas, combinaciones externas y
combinaciones cruzadas. Adicionalmente, en una instrucción SELECT se pueden
combinar más de dos tablas mediante un conjunto de combinaciones o se puede
combinar una tabla consigo misma mediante una autocombinación.
JOIN:
Es una operación que combina registros de dos tablas en una base de datos relacional
que resulta en una nueva tabla (temporal) llamada tabla de JOIN.
Las tablas se combinan para producir un único conjunto de resultados que incorpore filas
y columnas de dos o más tablas.
Sintaxis Parcial
SELECT columna [, columna …] FROM {<tablaOrigen >} [, ...n] <tipoCombinación > ::= [
INNER | { { LEFT | RIGHT | FULL } [OUTER] } ] [ <sugerenciaCombinación> ] JOIN
<tablaCombinada> ::= <tablaOrigen > <tipoCombinación > <tablaOrigen > ON
Base de datos I. Guía 6 5
<condiciónBúsqueda>
<tablaCombinada>
•
|
<tablaOrigen
>
CROSS
JOIN
<tablaOrigen
>
|
Selección de columnas específicas de varias tablas:
Una combinación permite seleccionar columnas de varias tablas al expandir la cláusula
FROM de la instrucción SELECT. En la cláusula FROM se incluyen dos palabras clave
adicionales: JOIN y ON.
La palabra clave JOIN especifica qué tablas se van a combinar y cómo.
La palabra clave ON especifica las columnas que las tablas tienen en común.
• Consultas de dos o más tablas para producir un conjunto de resultados:
Una combinación permite consultar dos o más tablas para producir un único conjunto de
resultados. Al implementar combinaciones, tenga en cuenta los siguientes hechos e
instrucciones:
1. Especifique la condición de combinación en función de claves principales y
externas.
2. Si una tabla tiene una clave principal compuesta, cuando combine tablas
debe hacer referencia a toda la clave en la cláusula ON.
3. Para combinar tablas, utilice columnas comunes a las tablas especificadas.
Dichas columnas deben tener tipos de datos iguales o similares.
4. Haga referencia al nombre de la tabla si las columnas de las tablas que va
a combinar tienen el mismo nombre. Califique los nombres de las columnas
con el formato tabla.columna.
5. Limite el número de tablas de las combinaciones porque cuantas más
tablas combine, mayor será la duración del proceso de la consulta.
6. Puede incluir varias combinaciones en una instrucción SELECT.
•
Uso de combinaciones internas:
6
Base de datos I, Guía6
Las combinaciones internas combinan tablas mediante la comparación de los valores de
las columnas que son comunes a ambas tablas. SQL Server sólo devuelve las filas que
cumplen las condiciones de la combinación.
•
Por qué se utilizan combinaciones internas:
Utilice combinaciones internas para obtener información de dos tablas independientes y
combinar dicha información en un conjunto de resultados. Al utilizar combinaciones
internas, tenga en cuenta los siguientes hechos e instrucciones:
1. Las combinaciones internas son el tipo predeterminado de SQL Server.
Puede abreviar la cláusula INNER JOIN como JOIN.
2. Para especificar las columnas que desea presentar en el conjunto de
resultados, incluya los nombres calificados de las columnas en la lista de
selección.
3. Incluya una cláusula WHERE para restringir las filas que se devuelven en
el conjunto de resultados.
4. No utilice valores NULL como condición de combinación, ya que no se
evalúan como iguales entre sí.
Base de datos I. Guía 6 7
Ejemplo 1:
Supongamos que tenemos dos tablas: una de películas y otra de directores
relacionadas entre sí:
movies
directors
id
title
1
Four Rooms
1995
3
2
Die Hard
1988
1
3 The Hunt for Red October 1990
1
4
2
Psycho
year director
1960
id
name
1 John McTiernan
2 Alfred Hitchcock
3 Quentin Tarantino
Si quiero obtener todas las películas y el nombre de su director haría lo siguiente:
USE MOVIES
SELECT m.title, d.name
FROM movies m
INNER JOIN directors d ON m.director = d.id
Donde los campos a seleccionar son title que está en la tabla movies y name que está en
la tabla directores, como movies tiene el alias m, podemos anteponer ese alias a los
campos que queramos seleccionar de la tabla movies, esto se hace para no causar
ambigüedad en caso que hallan 2 campos con nombres iguales en cada tabla.
Ejemplo 2:
Este ejemplo devuelve los valores buyer_name, buyer_id y qty de los clientes que han
adquirido algún producto. Los clientes que no hayan adquirido nada no se incluyen en el
conjunto de resultados. Los clientes que han adquirido más de un producto aparecen una
vez por cada compra realizada.
Las columnas buyer_id de las dos tablas pueden especificarse en la lista de selección.
USE joindb
SELECT buyer_name, sales.buyer_id, qty
FROM buyers
INNER JOIN sales
ON buyers.buyer_id = sales.buyer_id
GO
8
Base de datos I, Guía6
RESULTADO:
Ejemplo 3:
En este ejemplo se devuelven los nombres de los productos y de las compañías que los
suministran. Los productos sin suministradores asignados y los suministradores sin
productos asignados no se incluyen en el conjunto de resultados.
USE northwind
SELECT productname, companyname
FROM products
INNER JOIN suppliers
ON products.supplierid = suppliers.supplierid
GO
RESULTADO
Ejemplo 4:
En este ejemplo se devuelven los nombres de los clientes que han hecho pedidos
después del 1/1/98. Observe que se utiliza una cláusula WHERE para restringir las filas
devueltas en el conjunto de resultados.
USE northwind
SELECT DISTINCT companyname, orderdate
FROM orders INNER JOIN customers
ON orders.customerid = customers.customerid
Base de datos I. Guía 6 9
WHERE orderdate > '1/1/98'
GO
Procedimiento
Realice los siguientes ejercicios:
1-
Inicie Microsoft SQL Server Management Studio y verifique si existen las
bases de datos Library y JoinDB, en caso que las BD antes mencionadas no se
encuentren realice los siguientes pasos:
a. Descargue del mismo sitio donde descargó la guía el archivo Recursos
Guía 6.
b. Cree una Carpeta y descomprima el archivo, se mostraran 4 archivos, que
son el
archivo
Mdf
y
Ldf
de
cada
Base
de
datos
c. Dentro de SQL Server Management de clic derecho en Databases y
seleccione Attach
d. Se mostrará la ventana Attach DataBases, de clic en Add y se aparecerá
una ventana donde deberá indicar la ubicación de los archivos que
descargó.
10 Base de datos I, Guía6
e. Seleccione la base de datos joindb y de clic en OK. Luego se mostrara una
ventana en la que daremos clic en OK
f. Actualice su lista de base de datos y deberá aparecer joindb
Base de datos I. Guía 6 11
Realice el mismo procedimiento para adjuntar la base de datos Library
2
En este ejercicio, va a escribir y ejecutar consultas que combinan tablas en la
base de datos library.
•
•
•
•
Abra una sesión del SQL Server Management Studio e ingrese su
número de carné en el usuario y password.
En la lista de BD, haga clic en library.
Utilice las tablas member y adult para realizar una consulta que devuelva
los campos firstname, middleinitial, lastname, street, city, state y zip.
Concatene las columnas firstname, middleinitial y lastname en una
cadena de texto y asigne a la columna el alias name.
Ejecute la consulta para comprobar que devuelve los resultados deseados
(ver figura 1).
Figura 1.
2. Para este ejercicio va utilizar las tablas title, item y copy, y va a generar una
consulta que devuelva isbn, copy_no, on_loan, title, translation y cover, y
valores para las filas de la tabla copy cuyo ISBN sea 1 (uno), 500 (quinientos) o
12 Base de datos I, Guía6
1000 (mil). Ordene los resultados por la columna isbn. Para cada tabla debe
utilizar un alias.
PASOS
• Escriba la lista de selección de la consulta.
• Escriba una cláusula FROM que cree una combinación interna entre las
tablas title y copy sobre las columnas title_no. En la cláusula FROM,
establezca los alias de las tablas que haya utilizado en la lista de selección.
• Agregue una segunda cláusula INNER JOIN para crear la combinación
entre las tablas item y copy sobre las columnas isbn.
• agregue una cláusula WHERE que restrinja las filas de la tabla copy
obtenidas de aquéllas cuyo ISBN sea 1 (uno), 500 (quinientos) o 1000
(mil).
• Escriba la cláusula ORDER BY para ordenar el resultado por el ISBN.
• Ejecute la secuencia de comandos (ver figura2).
•
•
3- En la base de datos Library (biblioteca) se tienen las siguientes tablas.
Member
Reservation
Ítem
Title
En la tabla Member están los miembros que están afiliados a la biblioteca, en la tabla
Reservation están las reservaciones de libros que han realizado los miembros de la
biblioteca, la reserva se realiza por el isbn(número de identificación de los libros), la tabla
Title esta los títulos de los libros el autor y la sinopsis y la tabla ítem están las
traducciones que se les ha realizado a los libros que están en la tabla Title, cada libro
Base de datos I. Guía 6 13
solo puede tener un isbn, por ende si hay un libro traducido en 4 lenguajes tiene isbn
diferentes para cada uno.
Se pide una consulta que muestra el nombre y apellido (firstname y el lastname) de los
cliente que han reservado libros, la consulta debe contener los siguientes campos:
firstname, lastname, log_date,title, translation.
Investigación
-Investigue en qué consisten las combinaciones externas
Bibliografía
Francisco Charte Ojeda, SQL Server 2008. Madrid, España : ANAYA, 2009 1era edicion
14 Base de datos I, Guía6
COMBINACIO
RELACION DE TABLAS
Guía
Hoja de cotejo:
Y
6:
Alumno:
Máquina No:
Docente:
GL:
Fecha:
EVALUACION
%
CONOCIMIENTO
Del 20
al 30%
APLICACIÓN
DEL
CONOCIMIENTO
Del 40%
al 60%
1-4
5-7
8-10
Conocimie
nto
deficient
e de los
fundament
os
teóricos
Conocimiento
y explicación
incompleta de
los
fundamentos
teóricos
Conocimiento
completo y
explicación
clara de los
fundamentos
teóricos
No tiene
actitud
proactiva
.
Actitud
propositiva y
con
propuestas no
aplicables al
contenido de
la guía.
Tiene actitud
proactiva y sus
propuestas son
concretas.
ACTITUD
Del 15%
al 30%
TOTAL
100%
Nota
6
Base de datos I. Guía 7
1
Facultad:
Ingeniería
Escuela:
Computación
Asignatura: Base de datos I
Tema: USO DE COMBINACIONES EXTERNAS.
Objetivo
Específico


Definir las combinaciones externas
Conocer el uso de las combinaciones RIGTH y LEFT
Materiales
Equipo
y
 Computadora con SQL Server 2008 R2.
 Guía Número 7
Introducción
Teórica
Una combinación interna (JOIN) encuentra registros de la primera tabla que se
correspondan con los registros de la segunda, es decir, que cumplan la condición del
"ON" y si un valor de la primera tabla no se encuentra en la segunda tabla, el registro no
aparece.
Si queremos saber qué registros de una tabla NO encuentran correspondencia en la otra,
es decir, no existe valor coincidente en la segunda, necesitamos otro tipo de combinación,
"OUTER JOIN" (combinación externa).
Las combinaciones externas combinan registros de dos tablas que cumplen la condición,
más los registros de la segunda tabla que no la cumplen; es decir, muestran todos los
registros de las tablas relacionadas, aún cuando no haya valores coincidentes entre ellas.
Este tipo de combinación se emplea cuando se necesita una lista completa de los datos
de una de las tablas y la información que cumple con la condición. Las combinaciones
externas se realizan solamente entre 2 tablas.
Hay tres tipos de combinaciones externas: "LEFT OUTER JOIN", "RIGHT OUTER JOIN"
y "FULL OUTER JOIN"; se pueden abreviar con "LEFT JOIN", "RIGHT JOIN" y "FULL
JOIN" respectivamente.
2
Base de datos I, Guía 7
Se emplea una combinación externa izquierda para mostrar todos los registros de la tabla
de la izquierda. Si no encuentra coincidencia con la tabla de la derecha, el registro
muestra los campos de la segunda tabla seteados a "null".
En el siguiente ejemplo solicitamos el título y nombre de la editorial de los libros:
SELECT titulo,nombre
FROM editoriales AS e
LEFT JOIN libros AS l
ON codigoeditorial = e.codigo;
El resultado mostrará el título y nombre de la editorial; las editoriales de las cuales no hay
libros, es decir, cuyo código de editorial no está presente en "libros" aparece en el
resultado, pero con el valor "null" en el campo "titulo".
Es importante la posición en que se colocan las tablas en un "left join", la tabla de la
izquierda es la que se usa para localizar registros en la tabla de la derecha.
Entonces, un "left join" se usa para hacer coincidir registros en una tabla (izquierda) con
otra tabla (derecha); si un valor de la tabla de la izquierda no encuentra coincidencia en la
tabla de la derecha, se genera una fila extra (una por cada valor no encontrado) con todos
los campos
Sintaxis:
SELECT CAMPOS
FROM TABLAIZQUIERDA
LEFT JOIN TABLADERECHA
ON CONDICION;
Ejemplo 1:
SELECT tCoches.matricula,
tMarcas.marca,
tCoches.modelo,
tCoches.color,
tCoches.numero_kilometros,
tCoches.num_plazas
FROM tCoches
LEFT OUTER JOIN tMarcas ON tCoches.marca = tMarcas.codigo
Base de datos I. Guía 7 3
Esta consulta devolverá todos los registros de la tabla tCoches, independientemente de
que tengan marca o no. En el caso de que el coche no tenga marca se devolverá el valor
null para los campos de la tabla tMarcas.
El mismo ejemplo con RIGHT OUTER JOIN.
SELECT
tCoches.matricula,
tMarcas.marca,
tCoches.modelo,
tCoches.color,
tCoches.numero_kilometros,
tCoches.num_plazas
FROM
tCoches
RIGHT OUTER JOIN tMarcas ON tCoches.marca = tMarcas.codigo
4
Base de datos I, Guía 7
Ejemplo 2
En este ejemplo se devuelven los valores buyer_name, buyer_id y qty de todos los
clientes y sus compras. Observe que los clientes que no han adquirido ningún producto se
enumeran en el conjunto de resultados, pero aparecen valores NULL en las columnas
buyer_id y qty.
USE joindb
SELECT buyer_name, sales.buyer_id, qty FROM buyers
LEFT OUTER JOIN sales ON buyers.buyer_id = sales.buyer_id
GO
 Uso de las combinaciones cruzadas
Utilice combinaciones cruzadas para presentar todas las combinaciones de filas posibles
de las columnas seleccionadas en las tablas combinadas.
Base de datos I. Guía 7 5

Por qué se utilizan las combinaciones cruzadas.
Aunque las combinaciones cruzadas no se suelen utilizar en una base de datos
normalizada, se pueden utilizar para generar datos de prueba para una base de datos o
listas de todas las combinaciones posibles para elaborar listas de comprobación o
patrones comerciales.
Cuando se utilizan combinaciones cruzadas, SQL Server genera un producto cartesiano
en el que el número de filas del conjunto de resultados es igual al número de filas de la
primera tabla multiplicado por el número de filas de la segunda tabla. Por ejemplo, si hay
8 filas en una tabla y 9 filas en la otra, SQL Server devuelve un total de 72 filas.
Sugerencia:
Señale que la palabra clave ON y la lista de columnas asociada no se utilizan en la
instrucción SELECT porque las combinaciones cruzadas devuelven todas las
combinaciones posibles de las filas de cada tabla especificada.
En las combinaciones cruzadas no se requiere una columna común.
o
Combinación de varios conjuntos de resultados.
El operador UNION combina el resultado de dos o más instrucciones SELECT en un
único conjunto de resultados.
6
Base de datos I, Guía 7
Utilice el operador UNION cuando los datos que desee obtener residan en ubicaciones
diferentes y no se pueda tener acceso a ellas con una única consulta. Al utilizar el
operador UNION, tenga en cuenta los siguientes hechos e instrucciones:
o
SQL Server requiere que las tablas a las que se hace referencia tengan tipos de datos
similares, el mismo número de columnas y el mismo orden de columnas en la lista de
selección de cada consulta.
o
SQL Server quita las filas duplicadas del conjunto de resultados. Sin embargo, si
utiliza la opción ALL, se incluyen todas las filas en el conjunto de resultados, incluso
las duplicadas.
o
Debe especificar los nombres de las columnas en la primera instrucción SELECT. Por
lo tanto, si desea definir nuevos encabezados de columnas en el conjunto de
resultados, deberá crear los alias de columnas en la primera instrucción SELECT.
o
Si desea que todo el conjunto de resultados aparezca con un orden específico, debe
especificar la ordenación con una cláusula ORDER BY en la instrucción UNION. De lo
contrario, puede que el conjunto de resultados no se devuelva en el orden que desea.
o
Puede lograr un mayor rendimiento si divide una consulta compleja en varias
instrucciones SELECT y, después, utiliza el operador UNION para combinarlas.
Sintaxis
instrucciónSelect UNION [ALL] instrucciónSelect
Ejemplo 1:
En este ejemplo se combinan dos conjuntos de resultados. El primero devuelve el
nombre, la ciudad y el código postal de todos los clientes desde la tabla customers. El
segundo devuelve el nombre, la ciudad y el código postal de todos los empleados desde
la tabla employees. Observe que, cuando se utiliza el operador UNION para combinar
ambos conjuntos de resultados, se devuelven los alias de las columnas de la primera lista
de selección.
USE northwind
SELECT (firstname + ' ' + lastname) AS name, city, postalcode
FROM employees
UNION
SELECT companyname, city, postalcode
FROM customers
GO
Base de datos I. Guía 7 7
Procedimiento
Bibliografía
Realice los siguientes ejercicios.
1. Para
Guía
1 combinar varias tablas mediante una combinación externa
En este procedimiento va a escribir y ejecutar una consulta para obtener el nombre
completo y member_no de un miembro de la tabla member y los valores isbn y log_date
de la tabla reservation, para los miembros 250, 341 y 1675 de la base de datos LIBRARY
Ordene los resultados por member_no. Debe mostrar la información de estos miembros
incluso si no tienen libros en préstamo.
Escriba la lista de selección de la consulta:
o
o
o
o
o
Cree la columna name; para ello, concatene las columnas lastname,firstname y
middleinitial de cada miembro.
Cree la columna date; para ello, convierta log_date al tipo de datos char(8).
Escriba una cláusula FROM que cree una combinación externa izquierda entre las
tablas member y reservation sobre las columnas member_no.
Escriba una cláusula WHERE para obtener de la tabla member los miembros
cuyos números sean 250, 341 y 1675.
Escriba la cláusula ORDER BY para ordenar los resultados por los números de los
miembros.
8
Base de datos I, Guía 7
Ejecute la consulta para comprobar que devuelve los resultados deseados.
¿Qué miembros no tienen libros en préstamo?
El resultado debería ser el siguiente:
2. Uso del operador UNION para combinar conjuntos de resultados
En este ejercicio, va a producir un único conjunto de resultados al utilizar el operador
UNION para concatenar los resultados de tres instrucciones SELECT similares.
Se desea obtener como resultado una lista de direcciones completas para enviar
tarjetas a nuestros empleados, clientes y suministradores. Utilice la base de datos
Northwind, las tablas a utilizar son las siguientes: Customers, Suppliers, Employees.
Base de datos I. Guía 7 9

Investigación
Complementaria
La tarea a realizar será asignada por el instructor
Guía 3
Bibliografía
Guía 4
3Charte Ojeda, SQL Server 2008. Madrid, España : ANAYA, 2009 1era edicion
Francisco
fía
Guía
fía
4
10 Base de datos I, Guía 7
Hoja de cotejo:
US DE COMBINACIONES
EXTERNAS
Guía 7:
Docente:
Máquina No:Máquina No:
Alumno
:
Tema: Presentación
del programa
Máquina No:
Alumno:
Docente:
GL:
Docente:
GL:
GL:
Fecha:
a
EVALUACION
%
CONOCIMIENTO
Del 20
al 30%
APLICACIÓN
DEL
CONOCIMIENTO
Del 40%
al 60%
1-4
5-7
8-10
Conocimie
nto
deficient
e de los
fundament
os
teóricos
Conocimiento
y explicación
incompleta de
los
fundamentos
teóricos
Conocimiento
completo y
explicación
clara de los
fundamentos
teóricos
No tiene
actitud
proactiva
.
Actitud
propositiva y
con
propuestas no
aplicables al
contenido de
la guía.
Tiene actitud
proactiva y sus
propuestas son
concretas.
ACTITUD
Del 15%
al 30%
TOTAL
100%
Nota
7
1
Base de datos I. Guía 8
1
Facultad:
Ingeniería
Escuela:
Computación
Asignatura: Base de datos I
Tema: SUBCONSULTAS Y CREACION DE VISTAS.
Objetivo
Específico


Definir que es una subsonsulta
Utilizar vistas para seguridad de los datos
Materiales
Equipo
y
 Computadora con SQL Server 2008.
 Guía Número 8
Introducción
Teórica
¿Qué es una subconsulta?
Una subconsulta es una consulta T-SQL normal anidada dentro de otra consulta, se
crean utilizando paréntesis en una instrucción SELECT que sirve como base para
cualquier parte de los datos o de la condición de otra consulta.
Normalmente las subconsultas se utilizan para satisfacer una o un par de las siguientes
necesidades:



Desglosar una consulta en una serie de pasos lógicos.
Proporcionar un listado que va a ser el destino de una cláusula WHERE con [IN |
EXISTS |ANY | ALL].
Proporcionar una búsqueda dirigida por cada registro individual de una consulta
principal.
Crear una subconsulta anidada.
Las subconsultas pueden anidarse de forma que una subconsulta aparezca en la cláusula
WHERE (por ejemplo) de otra subconsulta que a su vez forma parte de otra consulta
principal. En la práctica, una consulta consume mucho más tiempo y memoria cuando se
incrementa el número de niveles de anidamiento. La consulta resulta también más difícil
de leer , comprender y mantener cuando contiene más de uno o dos niveles de
2
Base de datos I, Guía 8
subconsultas.
Ejemplo 1:
SELECT numemp, nombre
FROM empleados
WHERE numemp = (SELECT rep FROM pedidos WHERE clie = (SELECT numclie
FROM clientes WHERE nombre = 'Julia Antequera'))
En este ejemplo, por cada línea de pedido se calcula la subconsulta de clientes, y esto se
repite por cada empleado, en el caso de tener 10 filas de empleados y 200 filas de
pedidos (tablas realmente pequeñas), la subconsulta más interna se ejecutaría 2000
veces (10 x 200).
Evidentemente, la sintaxis exacta variará, no sólo por sustituir la lista de selección y los
nombres exactos de las tablas, sino también porque tenemos uniones de múltiples tablas
en la consulta interior, en la exterior o en ambas.
Consultas anidadas utilizando instrucciones SELECT: de un solo valor vamos a practicar
un ejemplo explicito.
Supongamos, por ejemplo, que deseamos conocer los ProductID (ID de productos) de
todos los artículos vendidos el primer día que se compró cualquier producto desde el
sistema.
Si ya sabemos cuál es el primer día en que se insertó un pedido en el sistema, no habrá
ningún problema; la consulta sería como la siguiente:
USE NORTHWIND
SELECT DISTINCT (o.OrderDate), od.ProductID
FROM Orders o
INNER JOIN [Order Details] od
ON o.OrderID = od.OrderID
WHERE OrderDate = ' 1996/7/4' --Esta es la primera fecha de un pedido en el sistema
Pero ahora supongamos, por ejemplo, que depuramos regularmente los datos de nuestro
sistema y deseamos seguir haciendo la misma pregunta como parte de un informe
automatizado. Como se va a automatizar, no podemos ejecutar una consulta para buscar
cual es la primera fecha en el sistema e insertarla manualmente en nuestra consulta, lo
mejor es automatizar el procedimiento.
USE NORTHWIND
SELECT DISTINCT o.OrderDate, od.ProductID
FROM Orders o
INNER JOIN [Order Details] od
ON o.OrderID = od.OrderID
WHERE OrderDate = (SELECT MIN(OrderDate) FROM Orders)
Base de datos I. Guía 8 3
Consultas anidadas utilizando subconsultas que devuelven: Múltiples valores: La consulta
que vamos a realizar en estos momentos es para buscar una lista de todas las tiendas
que tienen registros de descuento. Las tiendas se encuentran en una tabla denominada
Stores (Tiendas). Los descuentos están en una tabla denominada discounts
(descuentos).
USE PUBS
SELECT stor_id, stor_name
FROM Stores
WHERE stor_id IN (SELECT stor_id FROM Discounts)
Mezclar tipos de datos: CAST y CONVERT Podrá ver CAST y CONVERT utilizadas con
mucha frecuencia. Considerando que vamos a utilizar con frecuencia estas dos funciones
éste es el momento ideal para examinarlas con más detalle para saber lo que pueden
hacer por nosotros.
Tanto CAST como CONVERT ejecutan conversiones de tipos de datos. En su mayoría,
ambas hacen lo mismo, exceptuando a CONVERT que también realiza algunas
conversiones de formatos de fecha que CAST no ofrece.
Vamos a examinar la sintaxis de ambas funciones:
CAST (expresión AS tipo-de-datos)
CONVERT (tipo-de-datos, expresión [, estilo])
CAST y CONVERT pueden tratar una amplia variedad de conversiones de tipos de datos
que vamos a necesitar cuando SQL Server no lo haga implícitamente por nosotros. Por
ejemplo, convertir un número en una cadena es una necesidad muy común.
use Northwind
SELECT 'El cliente ha realizado la siguiente orden' + OrderID
FROM Orders
WHERE CustomerID = 'ALFKI'
Nos va a producir un error: Pero si cambiamos el código para que convierta primero
el número:
SELECT 'El cliente ha realizado la siguiente orden: ' + CAST (OrderID AS varchar)
FROM Orders
WHERE CustomerID = 'ALFKI'
Observe que con CAST no tenemos el mismo control sobre el formato de fechas que con
CONVERT.
Por ejemplo:
SELECT OrderDate, CONVERT (varchar (12), OrderDate, 111) AS ‘Convertido’
FROM Orders WHERE OrderID = 11050
4
Base de datos I, Guía 8
Es un resultado algo diferente de CAST. De hecho, podríamos haber convertido a uno de
los formatos de año de dos dígitos.
SELECT OrderDate, CONVERT(varchar(12), OrderDate, 5) AS “Convertido”
FROM Orders
WHERE OrderID = 11050
Para elegir el formato de fecha que deseamos, lo que tenemos que hacer es suministrar
un código al final de la función CONVERT. (111 en el ejemplo anterior nos habría
proporcionado el estándar japonés, con un año de cuatro dígitos; 5 es el estándar italiano,
con un año de dos dígitos). Los códigos de 100 son para años de cuatro dígitos; los
códigos menores que 100 (con algunas excepciones) son para años de dos dígitos. Los
formatos disponibles se pueden encontrar en el tema CONVERT o CASE de los libros en
pantalla de SQL Server.
PERTENENCIA A CONJUNTO IN
Examina si el valor de la expresión es uno de los valores incluidos en la lista de valores
SELECT numemp, nombre, oficina FROM empleados
WHERE oficina IN (12,14,16)
SELECT numemp, nombre FROM empleados
WHERE (oficina = 12) OR (oficina = 14) OR (oficina =
16)
Lista los empleados de las oficinas 12, 14 y 16
Obtenemos lo mismo que en el ejemplo anterior.
Los paréntesis son opcionales
La subconsulta debe generar una única columna y las filas que sean. Si la subconsulta no
produce ninguna fila, el test da falso.
Sintaxis:
SELECT numemp, nombre, oficina
FROM empleados
WHERE oficina IN (SELECT oficina FROM
oficinas WHERE region = 'este')
Con la subconsulta se obtiene la lista de los
números de oficina del este y la consulta
principal obtiene los empleados cuyo
número de oficina sea uno de los números
de
oficina
del
este.
Por lo tanto lista los empleados de las
oficinas del este.
Vistas
Las vistas tiene una tendencia a ser utilizadas mucho o poco: en raras ocasiones se
utilizan en su justa medida. Las vistas se podrían utilizar para:
1) Reducir la complejidad aparente de la base de datos para los usuarios finales
Base de datos I. Guía 8 5
2) Prevenir la selección de columnas confidenciales a la vez que permite el acceso a otros
datos importantes En el fondo, una vista no es más que una consulta almacenada. lo
extraordinario es que podemos combinar y hacer corresponder datos desde tablas base
(o desde otras vistas) para crear lo que, en general, funciona como cualquier otra tabla
base.
Ejemplo 1:
USE northwind
CREATE VIEW Categorias
AS
SELECT CategoryID, CategoryName, Description
FROM Categories
Para llamar a la vista creada: SELECT * FROM Categorias
Ejemplo 2:
CREATE VIEW Vista_Products
AS
SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice
FROM Products
Para llamar a la vista creada: SELECT * FROM Vista_Products
Si queremos modificar la vista del ejemplo 2 agregándole un nuevo campo, lo podemos
hacer con el comando ALTER de la siguiente manera
ALTER VIEW Vista_Products
AS
SELECT ProductID, ProductName, QuantityPerUnit,
UnitPrice,UnitsInStock
FROM Products
Las vistas se pueden utilizar como tablas. Para eliminar una vista podemos utilizar DROP.
Ejemplo:
DROP VIEW Vista_Products
Se pueden tambien utilizar vistas con combinaciones de tablas.
Base de datos I, Guía 8
6
Procedimiento
Realice los siguientes ejercicios sin utilizar Join, solo subconsultas.
Bibliografía
1. Escriba un script utilizando subconsulta, liste los empleados de la editorial
(pub_name) New Moon Books. La base a utilizar es PUBS y las tablas son
Guía 1employee y publishers.
2. Haga una consulta haciendo uso de subconsultas, que liste el nombre de los
procutos (ProductID, ProductName de la tabla product) que tengan una
cantidad(quantity de la tabla [Order Details]), de pedido mayor a 115
3. Cree las vistas que contengan las siguientes consultas.
Realizar una consulta que muestre los siguientes campos:
1) de la tabla HumanResources.EmployeeAddress el campo EmployeeID
2) de la tabla Person.Address los campos AddressLine1 y PostalCode
3) de la tabla Person.StateProvince los campos StateProvinceID y nameDe la base
de datos AdventureWorks
Realizar una consulta que muestre los siguientes campos:
1) de la tabla Customers el campo CustomerID.
2) de la tabla Orders el campo OrderDate.
3) de la tabla Employees los campos FirstName, LastName y BirthDate de la base
de datos Northwind.
Investigación
Complementaria
 Investigar que son los Procedimientos Almacenados en SQL Server.
Guía 3
Guía 4
fía
Bibliografía
Guía 3
 Francisco Charte Ojeda, SQL Server 2008. Madrid, España : ANAYA,
2009 1era edición
Guía 4
fía
Base de datos I. Guía 8 7
SUBCONSULTAS
CREACION DE VISTAS
Guía
Hoja de cotejo:
Y
8:
Docente:
Máquina No:Máquina No:
Alumno
:
Tema: Presentación
del programa
Máquina No:
Alumno:
Docente:
GL:
Docente:
GL:
GL:
Fecha:
a
EVALUACION
%
CONOCIMIENTO
Del 20
al 30%
APLICACIÓN
DEL
CONOCIMIENTO
Del 40%
al 60%
1-4
5-7
8-10
Conocimie
nto
deficient
e de los
fundament
os
teóricos
Conocimiento
y explicación
incompleta de
los
fundamentos
teóricos
Conocimiento
completo y
explicación
clara de los
fundamentos
teóricos
No tiene
actitud
proactiva
.
Actitud
propositiva y
con
propuestas no
aplicables al
contenido de
la guía.
Tiene actitud
proactiva y sus
propuestas son
concretas.
ACTITUD
Del 15%
al 30%
TOTAL
100%
Nota
8
1