SQL EJEMPLOS VARIOS PROGRAMACION, Apuntes de Programación Informática

¡Descarga SQL EJEMPLOS VARIOS PROGRAMACION y más Apuntes en PDF de Programación Informática solo en Docsity!

SQL BÁSICO

I. /ntroducciol

1. 1 Componel1tes del SOL

1.2 Comamlos

1.3 Cltiusllias

1.4 Operadores Logicos

1.5 Operadores de Comparacioll

1.6 Flll1ciol1eS de Agregado

2. COllsuitas de SeleccMn

2.1 Consultas Basicas

2.2 Ordel1ar los Registros

2.3 Consultas COil Preclicado

2.4 Alias

2.5 Bases de Datos Externas

3. Criterios de Seleccion

3.1 Opemdores Logicos

3.2 Interva/os de Va/ores

3.3 EI Orerrador Like

3.4 EI Oreradol' In

3.5 La clausula WHERE

INDICE

4. Agrupa/1liento de Registros v Funciolles Agregadas

4. 1 El cltiusllia GROUP BY

4.2 A VG (Media Aritmetica)

4.3 COUllt (Col1tar Registros)

4.4 j~lax r Min (Valores M(ixillloS v Mfnimos)

4.5 StDev)l StDevP (DesviacMn Esttinda r)

4.6 Sum (Sllmar Valores)

4.7 Var!' VatP (Varianza)

5. Consultas de Actllalizacion

5. 1 Consultas de Eliminacilin

5.2 COllSultas de Datos Aiiadidos

5.3 COl1sultas de Actllali:.acion

6. Tiros de D(lIos

7. SubConsllltas

8. Consultas de Re(erellcias Cru:.adas

9. Consu/tas de Union Intemas

IO. COllsultas de Ullion Externas

11. Estructuras de las Tablas

1 I. I Creacion de Tablas

11. 2 La C/cillsllia CONSTRAINT

11.3 Creacion de Indices

11.4 Eliminar !' Aliadil" Campos e Indices

12. Consulta.' con Pan/metros

13. Acceso a Bases de Datos Externas

14. Omit;r los Permi..,o.' de E;eCllCi(in

15. La C1dusu/a Procelilire

16. Anexos

16.1 Reso1ucion de Problemlls

1 6.1 .1 InlortJUlcif51l Dup/ieada

16.1.2 Registl'Os 110 Relaciol/ados

16.2 Utilhal" SOL desde Visual Basic

16.3 FUllciones utilizables

16.4 La lunchill N[

16.5 Manual de Estilo

<> (^) Distinto de <= Menor 0 Igual que

= Mayor 0 Igual que = (^) Igual que BETWEEN Utilizado para especificar un intervalo de valores. LIKE Utilizado en la comparacion de un modelo

In

Utilizado para especificar registros de una base de datos

1.6 FunClOnes de Agregado Las funciones de agregado se usan dentro de una clausula SELECT en grupos de registros para devolver un unico valor que se aplica a un grupo de registros.

Funcion J Descripcion A VG /Utilizada para ca1cular el promedio de los val ores de un campo determinado COUNT jutilizada para devolver el numero de registros de la seleccion SUM jutilizada para devolver la suma de todos los valores de un campo determinado MAX IUtilizada para devolver el valor mas alto de un campo especificado MIN IUtilizada para devolver el valor mas bajo de un campo especificado

2. Consultas de Selecci6n

Las consultas de selecci6n se utilizan para indicar al motor de datos que devuelva infonnacion de las bases de datos, esta informacion es devuelta en forma de conjunto de registros que se pueden almacenar en un objeto record set. Este conjunto de registros es modificable. 2.1 Consultas basicas La sintaxis basica de una consulta de seleccion es la siguiente: SELECT Campos FROM Tabla; En donde campos es la lista de campos que se deseen recuperar y tabla es e1 origen de los mismos, por ejemplo: SELECT Nombre, Telefono FROM Clientes; Esta consulta devuelve un recordset con el campo nombre y telefono^ de la tabla clientes. 2.2 Ordenar los registros Adicionalmente se puede especificar el orden en que se desean recuperar los registros de las tab las mediante la clausula ORDER BY Lista de Campos. En donde Lista de campos representa los campos a ordenar. Ejemplo: SELECT CodigoPostal, Nombre, Telefono FROM Clientes ORDER BY Nombre; Esta consulta devuelve los campos CodigoPostal, Nombre, Telefono de la tabla Clientes ordenados por el campo Nombre. Se pueden ordenar los registros por mas de un campo, como por ejemplo: SEL EC T CodigoPostal, Nombre, Telefono FROM Clientes ORDER BY CodigoPostal, Nombre; Incluso se puede especificar el orden de los registros: ascendente mediante la clausula (ASC -se toma este valor por defecto) 0 descendente (DESC) SELECT CodigoPostal, Nombre, Telefono FROM Clientes ORDER BY CodigoPostal DESC , Nombre ASC ; 2.3 Consultas con Predicado El predicado se incluye entre la clausula y el primer nombre del campo a recuperar, los posibles predicados son:

Predicado lDescripcion

/ALL lDevuelve todos los campos de la tabla

trop lDevuelve un determinado numero de registros de la tabla

DISTINC (^) Omite los registros cuyos campos seleccionados coincidan totalmente

If DISTINC Omite los registros duplicados basandose en la totalidad del registro y no s610 en los campos IrROW seleccionados.

ALL Si no se incluye ninguno de los predicados se asume ALL. EI Motor de base de datos selecciona todos los registros que cumplen las condiciones de la instruccion SQL. No se conveniente abusar de este predicado ya que obligamos al

motor de la base de datos a analizar la estructura de la tabla para averiguar los campos que contiene, es mucho mas rapido indicar ellistado de campos deseados. SELECT ALL FROM Empleados; SELECT * FROM Empleados;

TOP

Devuelve un ciecto numero de registros que entran entre al principio 0 al final de un rango especificado por una clausula ORDER BY. Supongamos que queremos recuperar los nombres de los 25 primeros estudiantes del curso 1994: SELECT TOP 25 Nombre, Apellido FROM Estudiantes

ORDER BY Nota DESC;

Si no se incluye la clausula ORDER BY, la consulta devolvera un conjunto arbitrario de 25 registros de la tabla

Estudiantes .EI predicado TOP no elige entre val ores iguales. En el ejemplo anterior, si la nota media numero 25 y la 26 son iguales, la consulta devolvera 26 registros. Se puede utilizar la palabra reservada PERCENT para devolver un

ciecto porcentaje de registros que caen al principio 0 al final de un rango especificado por la clausula ORDER BY.

Supongamos que en lugar de los 25 primeros estudiantes deseamos el 10 por ciento del curso: SELECT TOP 10 PERCENT Nombre, Apellido FROM Estudiantes

ORDER BY Nota DESC;

EI valor que va a continuacion de TOP debe ser un Integer sin signo.TOP no afecta a la posible actualizacion de la consulta. DISTINCT Omite los registros que contienen datos duplicados en los campos seleccionados. Para que los val ores de cada campo listado en la instruccion SELECT se incluyan en la consulta deben ser unicos. Por ejemplo, varios empleados listados en la tabla Empleados pueden tener el mismo apellido. Si dos registros contienen Lopez en el campo Apellido, la siguiente instruccion SQL devuelve un unico registro: SELECT DISTINCT Apellido FROM Empleados; Con otras palabras el predicado DISTINCT devuelve aquellos registros cuyos campos indicados en la clausula SELECT posean un contenido diferente. EI resultado de una consulta que utiliza DISTINCT no es actua li zable y no refleja los cambios subsiguientes realizados por otros usuarios. DISTINCTROW Devuelve los registros diferentes de una tabla; a diferencia del predicado anterior que solo se fijaba en el contenido

de los campos seleccionados, este 10 hace en el contenido del registro completo independientemente de los campo

indicados en la clausula SELECT. SELECT DISTINCTROW Apellido FROM Empleados; Si la tabla empleados contiene dos registros: Antonio Lopez y Marta Lopez el ejemplo del predicado DISTINCT devuleve un unico registro con el valor Lopez en el campo Apellido ya que busca no duplicados en dicho campo. Este ultimo ejemplo devuelve dos registros con el valor Lopez en el apellido ya que se bus can no duplicados en el registro completo.

2.4 Alias En determinadas circunstancias es necesario asignar un nombre a alguna columna detenninada de un conjunto devuelto, otras veces por simple capricho 0 por otras circunstancias. Para resolver todas ell as tenemos la palabra reservada AS que se encarga de asignar el nombre que deseamos a la columna deseada. Tornado como referencia el ejemplo anterior podemos hacer que la columna devuelta por la consulta, en lugar de llamarse apellido (igual que el cainpo devuelto) se llame Empleado. En este caso procederfamos de la siguiente forma: SELECT DISTfNCTROW Apellido AS Empleado FROM Empleados; 2.5 Recuperar Informacion de una base de Datos Extema Para concluir este capitulo se debe hacer referencia a la recuperacion de registros de bases de datos extema. Es ocasiones es necesario la recuperacion de informacion que se encuentra contenida en una tabla que no se encuentra en la base de datos que ejecutara la consulta 0 que en ese momenta no se encuentra abiecta, esta situacion la podemos salvar con la palabra reservada IN de la siguiente forma: SELECT DISTINCTROW Apellido AS Empleado FROM Empleados IN 'c:\databases\gestion.mdb'; En donde c:\databases\gestion.mdb es la base de datos que contiene la tabla Empleados.

3. Criterios de Seleccion

En el capitulo anterior se vio la forma de recuperar los registros de las tablas, las formas empleadas devolvian todos

los registros de la mencionada tabla. A 10 largo de este capitulo se estudiaran las posibilidades de filtrar los registros

con el fin de recuperar solamente aquellos que cumplan una condiciones preestablecidas. Antes de comenzar el desarrollo de este capitulo hay que recalcar tres detalles de vital importancia. EI primero de ell os es que cad a vez que se desee establecer una condicion referida a un campo de texto la condicion de busqueda

SEL EC T IIf(CodPostal Between 28000 And 28999, 'Provincial', 'Nacional') FROM Editores; (Devuelve el valor 'Provincial' si el c6digo postal se encuentra en el intervalo, 'Nacional' en caso contrario) 3.3 EI Operador Like Se utiliza para comparar una expresi6n de cadena con un modelo en una expresi6n SQL. SU sintaxis es: expresi6n Like modelo En donde expresi6n es una cadena modelo 0 campo contra el que se compara expresi6n. Se puede utilizar el operador Like para encontrar valores en los campos que coincidan con el modelo especificado. Por modelo puede especificar un valor completo (Ana Maria), 0 se pueden utilizar caracteres como din como los reconocidos por el sistema operativo para encontrar un rango de val ores (Like An). EI operador Like se puede utilizar en una expresi6n para comparar un valor de un campo con una expresi6n de cadena. Por ejemplo, si introduce Like C en una consulta SQL, la consulta devuelve todos los valores de campo que comiencen por la letra C. En una consulta con panimetros, puede hacer que el usuario escriba el modelo que se va a utilizar. EI ejemplo siguiente devuelve los datos que comienzan con la letra P seguido de cualquier letra entre A y F Y de tres digitos: Like 'P[A-F]###' Este ejemplo devuelve los campos cuyo contenido empiece con una letra de la A a la D seguidas de cualquier cadena. Like '[A-D]*' En la tabla siguiente se muestra c6mo utilizar el operador Like para comprobar expresiones con diferentes modelos.

Tipo de coincidencia Modelo Planteado ICoincide Varios caracteres l'aa' I'aa', 'aBa', 'aBBBa' Canicter especial j'a[]a' I'aa' Varios caracteres (^) j'ab' I'abcdefg', 'abc' Un solo canicter 'a?a' I'aaa', 'a3a', 'aBa'

Un solo digito 'a#a' l'aOa' , 'ala' ,'a2a'

Rango de caracteres '[a-z)' I'f, 'p', 'j'

Fuera de un rango '[!a-z)' ]'9', '&', '0/0'

Distinto de un digito ,'[!0-9), I'A'^ , 'a' '&', ,,~, Combinada j'a[!b-m]#' l'An9', 'azO', 'a99' 3.4 EI Operador In

1N0 coincide l'aBC' I'aaa' j'cab', 'aab' J'aBBBa' I'aaa', 'a10a' 1'2','&' I'b', 'a'

I'abc', 'ajO'

Este operador devuelve aquellos registros cuyo campo indicado coincide con alguno de los en una lista. Su sintaxis es: expresi6n [Not] In(valorl, valor2, ... ) SELECT * FROM Pedidos WHERE Provincia [n (,Madrid', 'Barcelona', 'Sevilla'); 3.5 La cl<iusula WHERE

La clausula WHERE puede usarse para determinar que registros de las tablas enumeradas en la clausula FROM apareceran en los resultados de la instrucci6n SELECT. Depues de escribir esta clausula se deben especificar las condiciones expuestas en los partados 3.1 y 3.2. Si no se emplea esta clausula, la consulta devolvera todas las filas de la tabla. WHERE es opcional, pero cuando aparece debe ir a continuaci6n de FROM. SELECT Apellidos, Salario FROM Empleados WHERE Salario > 21000; SELECT Id_Producto, Existencias FROM Productos WHERE Existencias <= Nuevo_Pedido; SELECT * FROM Pedidos WHERE Fecha_Envio = #5110194#; SELECT Apellidos, Nombre FROM Empleados WHERE Apellidos = 'King'; SELECT Apellidos, Nombre FROM Empleados WI [ERE Apellidos Like 'S*'; SELECT Apellidos, Salario FROM Empleados WHERE Salario Between 200 And 300; SELECT Apellidos, Salario FROM Empl WHERE Apellidos Betvveen 'Lon' And 'Tol'; SELECT Id]edido, Fecha]edido FROM Pedidos WHERE Fecha]edido Between #1-1-94# And #30-6-94#; SELECT Apellidos, Nombre, Ciudad FROM Empleados WHERE Ciudad In ('Sevilla', 'Los Angeles', 'Barcelona');

4. Agrupamiento de Registros

4.1 GROUP BY Combina los registros con valores identicos, en la lista de campos especificados, en un unico registro. Para cada registro se crea un valor sumario si se incluye una funci6n SQL agregada, como por ejemplo Sum 0 Count, en la instrucci6n SELECT. Su sintaxis es: SELECT campos FROM tabla WHERE criterio GROUP BY campos del grupo GROUP BYes opcional. Los valores de resumen se omiten si no existe una funci6n SQL agregada en la instrucci6n SELECT. Los valores Null en los campos GROUP BY se agrupan y no se omiten. No obstante, los valores Null no se evaluan en ninguna de las funciones SQL agregadas. Se utiliza la chiusula WHERE para excluir aquellas filas que no desea agrupar, y la clausula HA VING para filtrar los registros una vez agrupados. A menos que contenga un dato Memo u Objeto OLE, un campo de la lista de campos GROUP BY puede referirse a cualquier campo de las tablas que aparecen en la clausula FROM, incluso si el campo no esta incluido en la instrucci6n SELECT, siempre y cuando la instrucci6n SELECT incluya al menos una funci6n SQL agregada. Todos los campos de la lista de campos de SELECT deben 0 bien incluirse en la clausula GROUP BY 0 como argumentos de una funci6n SQL agregada. SELECT Id]amilia, Sum(Stock) FROM Productos GROUP BY Id]amilia; Una vez que GROUP BY ha combinado los registros, HAVING muestra cualquier registro agrupado por la clausula GROUP BY que satisfaga las condiciones de la clausula HAVING. HAVING es similar a WHERE, determina que registros se seleccionan. Una vez que los registros se han agrupado utilizando GROUP BY, HAVING determina cuales de ellos se van a mostrar. SELECT Id]amilia Sum(Stock) FROM Productos GROUP BY Id]amilia HAVING Sum(Stock) > 100 AND NombreProducto Like BOS; 4.2AVG Calcula la media aritmetica de un conjunto de valores contenidos en un campo especificado de una consulta. Su sintaxis es la siguiente Avg(expr) En donde expr representa el campo que contiene los datos numericos para los que se desea calcular la media 0 una expresi6n que realiza un calculo utilizando los datos de dicho campo. La media calculada por Avg es la media aritmetica (la suma de los valores dividido por el numero de valores). La funci6n Avg no incluye ningun campo Null en el caIculo. SELECT Avg(Gastos) AS Promedio FROM Pedidos WHERE Gastos > 100; 4.3 Count Calcula el numero de registros devueltos por una consulta. Su sintaxis es la siguiente Count( expr) En don de expr contiene el nombre del campo que desea contar. Los operandos de expr pueden incluir el nombre de un campo de una tabla, una constante 0 una funci6n (la cual puede ser intrinseca 0 definida por el usuario pero no otras de las funciones agregadas de SQL). Puede contar cualquier tipo de datos incluso texto. Aunque expr puede realizar un calculo sobre un campo, Count simplemente cuenta el numero de registros sin tener en cuenta que val ores se almacenan en los registros. La funci6n Count no cuenta los registros que tienen campos null a menos que expr sea el caracter comodin asterisco (). Si utiliza un asterisco, Count calcula el numero total de registros, incluyendo aquellos que contienen campos null. Count() es considerablemente mas rapida que Count(Campo). No se debe poner el asterisco entre dobles comillas (''). SELECT Count(*) AS Total FROM Pedidos; Si expr identifica a multiples campos, la funci6n Count cuenta un registro s610 si al menos uno de los campos no es Null. Si todos los campos especificados son Null, no se cuenta el registro. Hay que separar los nombres de los campos con ampersand (&). SELECT CO llnt(FechaEnvio & Transporte) AS Total FROM Pedidos; 4.4 Max, Min Devuelven el minimo 0 el maximo de un conjunto de valores contenidos en un campo especifico de una consulta. Su sintaxis es: Min(expr) Max(expr) En donde expr es el campo sobre el que se desea realizar el calculo. Expr pueden incluir el nombre de un campo de una tabla, una con stante 0 una funci6n (la cual puede ser intrinseca 0 definida por el usuario pero no otras de las funciones agregadas de SQL). SELECT Min(Gastos) AS ElMin FROM Pedidos WHERE Pais = 'Espafia'; SELECT Max(Gastos) AS EIMax FROM Pedidos WHERE Pais = 'Espafia'; 4.5 StDev, StDevP

Agrega un registro en una tabla. Se la conoce como una consulta de datos afiadidos. Esta consulta puede ser de dos tipo: Insertar un unico registro 6 Insertar en una tabla los registros contenidos en otra tabla. 5.2.1 Para insertar lin {mico Registro: En este caso la sintaxis es la siguiente: INSERT INTO Tabla (campol, camp02, .. , camp oN) VALUES (valor!, valor2, ... , valorN) Esta consulta graba en el campo 1 el valor!, en el camp02 y valor2 y aSI sucesivamente. Hay que pres tar especial atenci6n a acotar entre comillas simples C) los val ores literales (cadenas de caracteres) y las fechas indicarlas en formato mm-dd-aa y entre caracteres de almohadillas (#). 5.2 .2 Para insertar Registros de otra Tabla: En este caso la sintaxis es: INSERT INTO Tabla [IN base_extema] (campo 1, camp02, ... , campoN) SELECT TablaOrigen.campo 1, TablaOrigen.camp02, ... , TablaOrigen.campoN FROM TablaOrigen En este caso se seleccionanin los campos 1,2, ... , n dela tabla origen y se grabanin en los campos 1,2, .. , n de la Tabla. La condici6n SELECT puede incluir la clausula WHERE para filtrar los registros a copiar. Si Tabla y TablaOrigen poseen la misma estrucutra podemos simplificar la sintaxis a: INSERT INTO Tabla SELECT TablaOrigen. * FROM TablaOrigen De esta forma los campos de TablaOrigen se grabanin en Tabla, para realizar esta operaci6n es necesario que todos los campos de TablaOrigen esten contenidos con igual nombre en Tabla. Con otras palabras que Tabla posea todos los campos de TablaOrigen (igual nombre e igual tipo). En este tipo de consulta hay que tener especial atenci6n con los campos contadores 0 autonumericos puesto que al insertar un valor en un campo de este tipo se escribe el valor que contenga su campo hom610go en la tabla origen, no incrementandose como Ie corresponde. Se puede utilizar la instrucci6n INSERT INTO para agregar un registro unico a una tabla, utilizando la sintaxis de la consulta de adici6n de registro unico tal y como se mostr6 anterionnente. En este caso, su c6digo especifica el nombre y el valor de cad a campo del registro. Debe especificar cada uno de los campos del registro al que se Ie va a asignar un valor aSI como el valor para dicho campo. Cuando no se especifica dicho campo, se inserta el valor predeterminado 0 Null. Los registros se agregan al final de la tabla. Tambien se puede utilizar INSERT INTO para agregar un conjunto de registros pertenecientes a otra tabla 0 consulta utilizando la clausula SELECT ... FROM como se mostr6 anteriormente en la sintaxis de la consulta de adici6n de multiples registros. En este caso la chiusula SELECT especifica los campos que se van a agregar en la tabla destino especificada. La tabla destino u origen puede especificar una tabla 0 una consulta. Si la tabla destino contiene una clave principal, hay que segurarse que es unica, y con valores no-Null; si no es aSI, no se agreganin los registros. Si se agregan registros a una tabla con un campo Contador , no se debe incluir el campo Contador en la consulta. Se puede emplear la clausula IN para agregar registros a una tabla en otra base de datos. Se pueden averiguar los registros que se agreganin en la consulta ejecutando primero una consulta de selecci6n que utilice el mismo criterio de selecci6n y ver el resultado. Una consulta de adici6n copia los registros de una 0 mas tab las en otra. Las tab las que contienen los registros que se van a agregar no se veran afectadas por la consulta de adici6n. En lugar de agregar registros existentes en otra tabla, se puede especificar los val ores de cada campo en un nuevo registro utilizando la clausula VALUES. Si se omite la lista de campos, la clausula VALUES debe incluir un valor para cada campo de la tabla, de otra forma fallara INSERT. INS ERT INTO Clientes SELECT Clientes_Viejos.* FROM Clientes_Nuevos; INSERT INTO Empleados (Nombre, Apellido, Cargo) VALUES ('Luis', 'Sanchez', 'Becario'); INSERT INTO Empleados SELECT Vendedores.* FROM Vendedores WHERE Fecha_Contratacion < NowO - 30;

5.3 UPDATE Crea una consulta de actualizaci6n que cambia los valores de los campos de una tabla especificada basandose en un criterio especifico. Su sintaxis es: UPDATE Tabla SET Campol=Valor!, Camp02=Valor2, ... CampoN=ValorN WHERE Criterio; UPDATE es especialmente util cuando se desea cambiar un gran numero de registros 0 cuando estos se encuentran en mUltiples tablas. Puede cambiar varios campos a la vez. EI ejemplo siguiente incrementa los valores Cantidad pedidos en un 10 por ciento y los valores Transporte en un 3 por ciento para aquellos que se hayan enviado al Reino Unido.: UPDA TE Pedidos SET Pedido = Pedidos * 1.1, Transporte = Transporte * 1. WH ERE PaisEnvlo = 'ES';

UPDATE no genera ningun resultado. Para saber que registros se van a cambiar, hay que examinar primero el resultado de una consulta de selecci6n que utilice el mismo criterio y despues ejecutar la consulta de actualizaci6n. UPDATE Empleados SET Grado = 5 WI -IERE Grado = 2; UPDATE Productos SET Precio = Precio * 1.1 WHERE Proveedor = 8 AND Familia = 3; Si en una consulta de actualizaci6n suprimimos la clausula WHERE todos los registros de la tabla sefialada seran actualizados. UPDATE Empleados SET Salario = Salario * 1.

6. Tipos de Datos

Los tipos de datos SQL se clasifican en 13 tipos de datos primarios y de varios sin6nimos validos reconocidos por dichos tipos de datos. Tipos de datos primarios' lTipo de ILongitud Descripci6n lDatos IEINA (^) 1 byte Para consultas sobre tabla adjunta de productos de bases de datos que definen un tipo de datos !RY Binario. IEIT 1 byte^ Valores^ SilNo^ 6 TruelFalse !BYTE 1 byte Un valor entero entre 0 y 255. COUN

~ bytes tun^ numero incrementado^ automaticamente^ (de tipo Long)

ITER

CURR (^) 8 bytes Un entero escalable entre 922.337.203.685.477,5808 y 922.337.203.685.477,5807. ~NCY PATE [rIME 8 bytes^ Un^ valor de fecha u^ hora^ entre los afios^100 y 9999. SINGL

~ bytes

IUn valor en punto flotante de precisi6n simple con un rango de -3.402823 * 10 38 a -1.401298 * 10- E ~5 para valores negativos, 1.40129810-^45 a 3.40282310 38 para valores positivos, y O.

POUB

Un valor en punto flotante de doble precisi6n con un rango de -1.7976931 3486232* 10 308 a- 8 bytes 14.94065645841247* 10- 324 para valores negativos, 4.94065645841247* 10- 324 a

LE 1.79769313486232*10 308 para valores positivos, y O.

SHOR (^) 2 bytes [r /un^ entero corto entre -32,768 y 32,767. LONG ~ bytes Un^ entero largo entre -2,147,483,648 y 2,147,483,647. LONG 1 byte por De cero a un maximo de 1.2 gigabytes. [rEXT caracter ILONG (^) Segun se BINA (^) inecesite De cero 1 gigabyte. Utilizado para objetos^ OLE. RY

TEXT

1 byte por caracter pe^ cero a 255 caracteres. La slgUlente tabla recoge los smommos de los tJpos de datos defimdos:

Tipo de Dato BINARY

BIT

BYTE

COUNTER

CURRENCY

IoATETIME

SINGLE

Sin6nimos !VARBINARY BOOLEAN LOGICAL LOGICAL! tyESNO IINTEGERI jAUTOINCREMENT MONEY

~

ATE

IME

IMESTAMP

I

FLOAT

IEEESINGLE

El predicado EXISTS (con la palabra reservada NOT opcional) se utiliza en comparaciones de verdad/falso para determinar si la subconsulta devuelve algun registro. Se puede utilizar tambien alias del nombre de la tabla en una subconsulta para referirse a tab las listadas en la cl<lusula FROM fuera de la subconsulta. EI ejemplo siguiente devuelve los nombres de los empleados cuyo salario es igual 0 mayor que el salario medio de todos los empleados con el mismo titulo. A la tabla Empleados se Ie ha dado el alias Tl:: SELECT Apellido, Nombre, Titulo, Salario FROM Empleados AS Tl WHERE Salario >= (S ELECT Avg(Salario) FROM Empleados WHERE T1.Titulo = Empleados.Titulo) ORDER BY Titulo; En el ejemplo anterior, la palabra reservada AS es opcional. SELECT Apellidos, Nombre, Cargo, Salario FROM Empleados WHERE Cargo LlK.E "Agente Ven" AND Salario > ALL (S ELECT Salario FROM Empleados WH ERE (Cargo LIKE "Jefe") OR (Cargo LIK.E "Director*")); Obtiene una !ista con el nombre, cargo y salario de todos los agentes de ventas cuyo salario es mayor que el de todos los jefes y directores. SELECT DISTlNCTROW NombreProducto, Precio_Unidad FROM Productos WHERE (Precio_Unidad = (S ELECT Precio_Unidad FROM Productos WHERE Nombre_Producto = "Almibar anisado"); Obtiene una !ista con el nombre y el precio unitario de todos los productos con el mismo precio que el almibar anisado. SELECT DlSTINCTROW Nombre_Contacto, Nombre_Compafiia, Cargo_Contacto, Telefono FROM Clientes WHERE (ID_Cliente IN (SELECT DISTINCTROW ID_Cliente FROM Pedidos WHERE Fecha]edido >= #04/1/93# <#07/1/93#); Obtiene una !ista de las compafdas y los contactos de todos los clientes que han rea!izado un pedido en el segundo trimestre de 1993. SELECT Nombre, Apellidos FROM Empleados AS E WHERE EX ISTS

(S ELECT * FROM Pedidos AS 0 WHERE O.ID_Empleado = E.ID_Empleado);

Selecciona el nombre de todos los empleados que han reservado al menos un pedido. SELECT DISTINCTROW Pedidos.Id]roducto, Pedidos.Cantidad, (S ELECT DISTINCTROW Productos.Nombre FROM Productos WHERE Productos.Id]roducto = Pedidos.Id]roducto) AS EIProducto FROM Pedidos WHERE Pedidos.Cantidad > 150 ORDER BY Pedidos.Id]roducto; Recupera el C6digo del Producto y la Cantidad pedida de la tabla pedidos, extrayendo el nombre del producto de la tabla de productos.

8. Consultas de Referencias Cruzadas

Una consulta de referencias cruzadas es aquella que nos permite visualizar los datos en filas y en columnas, estilo tabla, por ejemplo:

Producto 1 Ano 1996 1

Pantalones 1.2501 3. Camisas (^) 8.5601 1. Zapatos 4.3691 2. Sl tenemos una tabla de productos y otra tabla de ped1dos, podemos v1suahzar en total de productos ped1dos por ano para un articulo determinado, tal y como se visualiza en la tabla anterior. La sintaxis para este tipo de consulta es la siguiente: TRANSFORM funci6n agregada instrucci6n select PIVOT campo pivot [IN (valor! [, valor2 [, ... ]])] En donde:

funcion agregada

Es una funci6n SQL agregada que opera sobre los datos seleccionados. instruccion select Es una instrucci6n SELECT.

campo pivot

Es el campo 0 expresi6n que desea utilizar para crear las cabeceras de la columna en el resultado de la consulta. valorl, valor Son valores fijos utilizados para crear las cabeceras de la columna. Para resumir datos utilizando una consulta de referencia cruzada, se seleccionan los val ores de los campos 0 expresiones especificadas como cabeceras de columnas de tal forma que pueden verse los datos en un formato mas compacto que con una consulta de selecci6n. '

TRANSFORM es opcional pero si se incluye es la primera instrucci6n de una cadena SQL. Precede a la instrucci6n SELECT que especifica los campos utilizados como encabezados de fila y una cl<iusula GROUP BY que especifica el agrupamiento de las filas. Opcionalmente puede incluir otras chiusulas como por ejemplo WHERE, que especifica una selecci6n adicional 0 un criterio de ordenaci6n. Los val ores devueltos en campo pivot se utilizan como encabezados de columna en el resultado de la consulta. Por ejemplo, al utilizar las cifras de ventas en el mes de la venta como pivot en una consulta de referencia cruzada se crearfan 12 columnas. Puede restringir el campo pivot para crear encabezados a partir de los valores fijos (valor!, valor2) listados en la cl<iusula opcional IN. Tambien puede incluir valores fijos, para los que no existen datos, para crear columnas adicionales. Ejemplos TRANSFORM Sum(Cantidad) AS Ventas SELECT Producto, Cantidad FROM Pedidos WHERE Fecha Between #01-01-98# And #12-31-98# GROUP BY Producto ORDER BY Producto PIVOT DatePart("m", Fecha); Crea una consulta de tabla de referencias cruzadas que muestra las ventas de productos por mes para un ana especijico. Los meses aparecen de izquierda a derecha como columnas y los nombres de los productos aparecen de arriba hacia abajo como jilas. TRANSFORM Sum(Cantidad) AS Ventas SELECT Compania FROM Pedidos

WHERE Fecha Between #01-01-98# And #12-31-98# GROUP BY Compania

ORDER BY Compania PIVOT "Trimes tre" & DatePart("q", Fecha) In (Trimestrel',

Trimestre2', Trimestre 3', Trimestre 4'); Crea una consulta de tabla de referencias cruzadas que muestra las ventas de productos par trimestre de cada proveedor en el ana indicado. Los trimestres aparecen de izquierda a derecha como columnas y los nombres de los proveedores aparecen de arriba hacia abajo como jilas. Un caso practico: Se trata de resolver el siguiente problema: tenemos una tabla de productos con dos campos, el c6digo y el nombre del producto, tenemos otra tabla de pedidos en la que anotamos el c6digo del producto, la fecha del pedido y la cantidad pedida. Deseamos consultar los totales de producto por ano, calculando la media anual de ventas. Estructura y datos de las tablas:

1. Articulos: jNymbre !Zapatos 12 IPantalones

13 IBlusas

2. Pedidos:

lId jFecha ICantidad

t

3 05/0811996 J

~~

(^1) j 2 1511211997 / (^3 1711011997 )

Para resolver la consulta planteamos la slgUlente consulta: TRANSFORM Sum(Pedidos.Cantidad) AS Resultado SELECT Nombre AS Producto, Pedidos.Id AS C6digo, Sum(Pedidos.Cantidad) AS TOTAL, Avg(Pedidos.Cantidad) AS Media FROM Pedidos INN ER JOIN Articulos ON Pedidos.Id = Articulos.Id

GROUP BY Pedidos.Id, Articulos.Nombre PIVOT Year(Fecha);

y obtenemos el siguiente resultado:

IC6digo Media apatatos 87

En el ejemplo anterior, IDCategoria es el campo combinado, pero no esta incluido en la salida de la consulta ya que no esta incluido en la instrucci6n SELECT. Para incluir el campo combinado, incluir el nombre del campo en la instrucci6n SELECT, en este caso, Categorias.lDCategoria. Tambien se pueden en lazar varias clausulas ON en una instrucci6n JOIN, utilizando la sintaxis siguiente: SELECT campos FROM tablal INNER JOIN tabla ON tbl.campo1 comp tb2.campol AND ON tbl.camp02 comp tb2.camp02) OR ON tbl.camp03 comp tb2.camp03)]; Tambien puede anidar instrucciones JOIN utilizando la siguiente sintaxis: SELECT campos FROM tb 1 INNER JOIN (tb2 INNER JOIN [( ]tb [INNER JOIN [( ]tablax [INNER JOIN .. .)] ON tb3.camp03 comp tbx.campox)] ON tb2.camp02 comp tb3.camp03) ON tbl.campol comp tb2.camp02; Un LEFT JOIN 0 un RIGHT JOIN puede anidarse dentro de un INNER JOIN, pero un INNER JOIN no puede anidarse dentro de un LEFT JOIN 0 un RIGHT JOIN. Ejemplo SELECT DlSTr1\CTROW Sum([Precio unidad] * [Cantidad]) AS [Ventas], [Nombre] & " " & [Apellidos] AS [Nombre completo] FROM [Detalles de pedidos], Pedidos, Empleados, Pedidos INNER .lOIN [Detalles de pedidos] ON Pedidos. [ID de pedido] = [Detalles de pedidos].[ID de pedido], Empleados INNER JOIN Pedidos ON Empleados.[ID de empleado] = Pedidos.[ID de empleado] GROUP BY [Nombre] & " "& [Apellidos]; Crea dos combinaciones equivalentes: una entre las tablas Detalles de pedidos y Pedido s, y la otra entre las tablas Pedidos y Empleados. Esto es necesario ya que la tabla Empleados no contiene datos de ventas y la tabla Detalles de pedidos no contiene datos de los empleados. La consulta produce una !ista de empleados y sus ventas to tales. Si empleamos la clausula INNER en la consulta se seleccionaran s610 aquellos registros de la tabla de la que hayamos escrito ala izquierda de INNER JOIN que contengan al menos un registro de la tabla que hayamos escrito a la derecha. Para solucionar esto tenemos dos clausulas que sustituyen a la palabra clave INNER, estas clausulas son LEFT y RIGHT. LEFT tom a todos los registros de la tabla de la izquierda aunque no tengan ningun registro en la tabla de la izquierda. RIGHT realiza la misma operaci6n pero al contrario, toma todos los registros de la tabla de la derecha aunque no tenga ningun registro en la tabla de la izquierda.

10. Consultas de Union Externas

Se utiliza la operaci6n UNION para crear una consulta de uni6n, combinando los resultados de dos 0 mas consultas o tab las independientes. Su sintaxis es: [TABLE] consultal UNION [ALL] [TABLE] consulta2 [UNION [ALL] [TABLE] consultan [ ... ]] En donde: consuItal, consulta2, consuItan Son instrucciones SELECT, el nombre de una consulta almacenada 0 el nombre de una tabla almacenada precedido por la palabra clave TABLE. Puede combinar los resultados de dos 0 mas consultas, tab las e instrucciones SELECT, en cualquier orden, en una (mica operaci6n UNION. El ejemplo siguiente combina una tabla existente llamada Nuevas Cuentas y una instrucci6n SELECT: TABLE [Nuevas Cuentas] UNION ALL SELECT * FROM Clientes WHERE [Cantidad pedidos] > 1000; Si no se indica 10 contrario, no se devuelven registros duplicados cuando se utiliza la operaci6n UNION, no obstante puede incluir el predicado ALL para asegurar que se devuelven todos los registros. Esto hace que la consulta se ejecute mas rapidamente. Todas las consultas en una operaci6n UNION deb en pedir el mismo numero de campos, no obstante los campos no tienen porque tener el mismo tamaflo 0 el mismo tipo de datos. Se puede utilizar una clausula GROUP BY y/o HA VING en cad a argumento consulta para agrupar los datos devueltos. Puede utilizar una clausula ORDER BY al final del ultimo argumento consulta para visualizar los datos devueltos en un orden especifico.

SELECT [Nombre de compafiia], Ciudad FROM Proveedores WHERE Pais = 'Brasil' UNION SELECT [Nombre de compafiia], Ciudad FROM Clientes WHERE Pais = "Brasil" Recupera los nombres y las ciudades de todos proveedores y clientes de Brasil SELECT [Nombre de compafiia], Ciudad FROM Proveedores WHERE Pais = 'Brasil' UNION SELECT [Nombre de compafiia], Ciudad FROM Clientes WHERE Pais = 'Brasil' ORDER BY Ciudad Recupera los nombres y las ciudades de todos proveedores y clientes radicados en Brasil, ordenados por el nombre de la ciudad SELECT [Nombre de compafiia], Ciudad FROM Proveedores WHERE Pais = 'Brasil' UN ION SELECT [Nombre de compafiia], Ciudad FROM Clientes WHERE Pais = 'Brasil' UNION SELECT [Apellidos], Ciudad FROM Empleados WHERE Region = 'America del Sur' Recupera los nombres y las ciudades de todos los proveedores y clientes de brasil y los apellidos y las ciudades de todos los empleados de America del Sur TABLE [Lista de clientes] UNION TABLE [Lista de proveedores] Recupera los nombres y c6digos de todos los proveedores y clientes

11. Estructuras de las Tablas

11.1 Creacion de Tablas Nuevas Si se esta utilizando el motor de datos de Microsoft para acceder a bases de datos access, solo se puede emplear esta instruccion para crear bases de datos propias de access. Su sintaxis es: CREATE TABLE tabla (campo 1 tipo (tamafio) indicel , camp02 tipo (tamafio) indice2, ... , indice multi campo ,. .. ) En donde:

Parte ,IDescripci6n

abla Es el nombre de la tabla que se va a crear. campo 1 Es el nombre del campo 0 de los campos que se van a crear en la nueva tabla. La nueva tabla debe camp02 contener, al menos, un campo. ipo IEs el tipo de datos de campo en la nueva tabla. (V er Ti120S de Datos) amafio JEs^ el tamafio del campo^ solo^ se aplica para campos de tipo texto. indicel (^) Es una clausula CONSTRA[NT que define el tipo de indice a crear. Esta clausula en opcional. indice indice Es una clausula CONSTRAINT que define el tipo de indice muiticampos a crear. Un indice multi Imulticampo (^) campo es aquel que esta indexado por el contenido de varios campos. Esta clausula en opcional. s CREATE TAB LE Empleados (Nombre TEXT (25), Apelhdos TEXT (50)); Crea una nueva tabla llamada Empleados con dos campos, uno llamado Nombre de tipo texto y longutid 25 y otro llamado apellidos con longitud 50. CREA TE TABLE Empleados (Nombre TEXT (10), Ape Jlid os TEXT, Fecha_Nacimiento DATETlME) CONSTRAINT IndiceGeneral UN IQUE ([Nombre], [Apellidos], [Fecha_Nacimiento]); Crea una nueva tabla llamada Empleados con un campo Nombre de tipo texto y longitud 10, otro con llamado Apellidos de tipo texto y longitud predeterminada (50) y uno nuis llamado Fecha_Nacimiento de tipo FechalHora. Tambien crea un indice unico (no permite valores repetidos) formado por los tres campos. CREATE TABLE Empleados (ID INTEGER CONSTRAINT IndicePrimario PRIMARY, Nombre TEXT, Apellidos TEXT, Fecha_Nacimiento DATETIME); Crea una tabla llamada Empleados con un campo Texto de longitud predeterminada (50) llamado Nombre y otro iguaillamado Apellidos, crea ofro campo llamado Fecha_Nacimiento de tipo FechalHora y el campo ID de tipo entero el que establece como clave principal. 11.2 La clausula CONSTRAINT

Se utiliza la clausula CONSTRAINT en las instrucciones ALTER TABLE Y CREATE TABLE para crear 0 eliminar

indices. Existen dos sintaxis para esta clausula dependiendo si desea Crear 6 Eliminar un indice de un unico campo o si se trata de un campo muitiindice. Si se utiliza el motor de datos de Microsoft, s610 podra utilizar esta clausula con las bases de datos propias de dicho motor. Para los indices de campos unicos: CONSTRAINT nombre {PRIMARY KEY I UNIQUE I REFERENCES tabla externa [(campo externol, campo extern02)]}

Se puede utilizar CREATE INDEX para crear un pseudo indice sobre una tabla adjunta en una fu ente de datos ODBC tal como SQL Server que no tenga todavia un indice. No necesita permiso 0 tener acceso a un servidor remoto para crear un pseudo indice, ademas la base de datos remota no es consciente y no es afectada por el pseudo indice. Se utiliza la misma sintaxis para las tabla adjunta que para las originales. Esto es especialmente u ti l para crear un indice en una tabla que seria de s610 lectura debido a la falta de un indice. CREA TE IN D EX MiIndice ON Empleados (Prefijo, Telefono); Crea un indice llamado Milndice en la tabla empleados con los campos Prefyo y Tele/on o. CREATE UN IQUE fN D EX MiIndice ON Empleados (ID) WlTl-l DISALLOW NUL L; Crea un indice en la tabla Empleados utilizando el campo ID, obligando que que el campo ID no contenga valores nulos ni repetidos. 11.4 Modificar el Disefio de una Tabla Modifica el disefio de una tabla ya existente, se puden modificar los campos 0 los indices existentes. Su sintaxis es: ALTER TABLE tabla {ADD {COLUMN tipo de campo[(tamafio)] [CONSTRAINT indice]

CONSTRAINT indice multicampo} I

DROP {COLUMN campo I CONSTRAINT nombre del indice} } En donde:

Part e Ocscriprion abla Es el nombre de la tabla que se desea modificar. campo Es el nombre del campo que se va a afiadir 0 eliminar. ipo IEs el t iQ o de ca ll112 0 que se va a afiadir. amafio El el tamafio del campo que se va a afiadir (5610 para campos de texto).

indice

Es el nombre del indice del campo (cuando se crean campos) 0 el nombre del in dice de la tabla que se desea eliminar. indice Es el nombre del indice del campo multicampo (cuando se crean campos) 0 el nombre del indice de Imulticampo la tabla que se desea eliminar.

Operari. (^1) DescnpClO1I ... 011 ~DD (^) Se utiliza para afiadir un nuevo campo a la tabla, indicando el nombre, el tipo de campo y opciona lm ente COLU MN

el tamafio (para campos de tipo texto).

ADD Se utliza para agregar un indice de multi campos 0 de un tmico campo. DROP COLU Se utliza para borrar un campo. Se especifica unicamente el nombre del campo. MN

DROP Se^ utiliza para eliminar un indice.^ Se^ especifica unicamente el nombre del indice a continuaci6n de la ipalabra reservada CONSTRAINT. ALTER TAB LE Empleados ADD COLUMN Salano CURRENCY ; Agrega un campo Salario de tipo Moneda a la tabla Empleados. ALTE R TABLE Empleados DROP COLUMN Salario; Elimina el campo Salario de la tabla Empleados. ALTER TAB LE Pedidos ADD CONSTRAINT RelacionPedidos FOREI GN KEY (ID_Empleado) REFERENCES Empleados (ID_Empleado); Agrega un indice externo a la tabla Pedidos. El indice externo se basa en el campo ID _ Empl eado y se refiere al campo ID _ Empleado de la tabla Empleados. En este ejemplo no es necesario indicar el campo junto al nombre de la tabla en fa clausula REFERENCES, pues ID _ Empleado es la clave principal de la tabla Emple ados. ALTER TABLE Pedidos DRO P CONST RAINT RelacionPedidos; Elimina el indide de la tabla Pedid os.

12 Consultas con Parametros

Las consultas con parametros son aque ll as cuyas condiciones de busqueda se definen mediante parametro s. Si se ejecutan directamente desde la base de datos donde han sido definidas aparecera un mensaje solicitando el valor de cad a uno de los parametros. Si deseamos ejecutarlas desde una aplicaci6n hay que asignar primero el va lo r de los parametros y despues ejecutarlas. Su sintaxis es la siguiente: PARAMETERS nombre l tipol, nombre2 tip02, ... ,nombreN tipoN Consulta

En donde:

Parte Descripci6n inombre Es el nombre del panimetro ipo Es el tiRO de datos del panimetro consulta Una consulta SQL .. Puede utlhzar nombre pero no tIPO de datos en una clausula WHERE 0 HAVING. PARAMETERS Precio_Minimo Currency, Fecha_Inicio DateTime; SELECT IDPedido, Cantidad FROM Pedidos WHERE Precio > Precio_Minimo AND FechaPedido >= Fecha_Inicio; EI ejemplo siguiente muestra como utilizar los parametros en el programa de Visual Basic: Public Sub GeneraConsultaO Dim SQL As String Dim Qd As QueryDef Dim Rs As Recordset SQL = "PARAMETERS Precio_Minimo Currency, Fecha_Inicio DateTime; " SQL = SQL & "SELECT IDPedido, Cantidad FROM Pedidos WHERE Precio > " SQL = SQL & "Precio_Minimo AND FechaPedido >= Fecha_Inicio; " Set Qd = BaseDatos.CreateQueryDef(MiConsulta, SQL) Qd.Parameters!Precio_Minimo = 2 Qd.Parameters!FechaInicio = #31/12/95# Set Rs = Qd.OpenRecordsetO End Sub Ejemplo: PARAMETERS [Escriba los Apellidos:] Text; SELECT * FROM Empleados WHERE [Escriba los Apellidos:] = [Apellidos]; La ejecucion desde la base de datos solicita al usuario los apeUidos del empleado y despues muestra los resultados.

13. Bases de Datos Externas

Para el acceso a bases de datos extemas se utiliza la clausula IN. Se puede acceder a base de datos dBase, Paradox 0 Btrieve. Esta clausula s610 permite la conexi6n de una base de datos extema a la vez. Una base de datos extema es una base de datos que no sea la activa. Aunque para mejorar los rendimientos es mejor adjuntarlas a la base de datos actual y trabajar con ellas. Para especificar una base de datos que no pertenece a Access Basic, se agrega un punto y coma (;) al nombre y se encierra entre comillas simples. Tambien puede utilizar la palabra reservada DATABASE para especificar la base de datos extema. Por ejemplo, las !ineas siguientes especifican la misma tabla: FROM Tabla IN '[ dBASE IV; DATABASE=C:\DBASE\DATOS\VENTAS;],; FROM Tabla IN 'C:\DBASE\DATOSWENTAS' 'dBASE IV;'

Acceso a una base de datos externa de Microsoft Access:

SE LECT IDCliente FROM Clientes IN MISDATOS.MDB WHERE IDCliente Like 'A *';

En donde MlSDATOS.MDB es el nombre de una base de datos de Microsoft Access que contiene la tabla Clientes.

Acceso a una base de datos extern a de dBASE III 0 IV:

SELECT IDCliente FROM Clientes IN 'C:\DBASE\DATOSWENTAS' 'dBASE IV';

WHERE IDCliente Like 'A *';

Para recuperar datos de una tabla de dBASE 111+ hay que utilizar 'dBASE 111+;' en fugar de 'dBASE IV;'.

Acceso a una base de datos de Paradox 3.x 0 4.x:

SELECT IDCliente FROM Clientes IN 'C:\PARADOX\DATOSWENTAS' 'Paradox 4.x;' WHERE IDCliente Like 'A *'; Para recuperar datos de una tabla de Paradox version 3.x, hay que sustituir 'Paradox 4.x ,· ' por 'Paradox 3.x;'.

Acceso a una base de datos de Btrieve:

SELECT IDCliente FROM Clientes IN 'C:\BTRIEVE\DATOSWENTAS\FILE.DDF' 'Btrieve;' WHERE IDCliente Like 'A *';

C: IBTRIEVEIDA TOSIVENTASIFILE.DDF es la ruta de acceso y nombre de archivo del archivo de definiCion

de datos de Btrieve.