power builder + sql consultas simples [ · pdf filepower builder + sql lic. vladimir...

Power Builder + Sql

Lic. Vladimir Cotaquispe Gutiérrez. 1 E.P. DE INGENIERIA DE SISTEMA E INFORMATICA

Consultas simples

3.1. Introducción

Vamos a empezar por la instrucción que más se utiliza en SQL, la sentencia SELECT. Lasentencia SELECT es, con diferencia, la más compleja y potente de las sentencias SQL, conella podemos recuperar datos de una o más tablas, seleccionar ciertos registros e inclusoobtener resúmenes de los datos almacenados en la base de datos. Es tan compleja que laestudiaremos a lo largo de varias unidades didácticas incorporando poco a poco nuevasfuncionalidades.

El resultado de una SELECT es una tabla lógica que alberga las filas resultantes de laejecución de la sentencia.

La sintaxis completa es la siguiente:

SELECT sentencia::=[WITH <expresion_tabla_comun> [,...n]]

<expresion_consulta>

[ORDER BY {expression_columna|posicion_columna [ASC|DESC] }

[ ,...n ]]

[COMPUTE

{{AVG|COUNT|MAX|MIN|SUM} (expression)}[ ,...n ] [BYexpression[ ,...n ]]

]

[<FOR clausula_for>]

[OPTION (<query_hint>[ ,...n ])]

<expresion_consulta> ::=

{<especificacion_consulta> | ( < expresion_consulta > ) }

[ {UNION [ALL]|EXCEPT|INTERSECT}

<especificacion_consulta> | (<expresion_consulta>) [...n]

]

<especificacion_consulta> ::=

SELECT [ALL|DISTINCT]

[TOP expresion [PERCENT] [WITH TIES] ]

<lista_seleccion>

Power Builder + Sql


[INTO nueva_tabla]

[FROM { <origen> } [ ,...n ] ]

[WHERE <condicion_busqueda> ]

[GROUP BY [ ALL ] expresion_agrupacion [ ,...n ]

[WITH { CUBE | ROLLUP } ]

]

[HAVING < condicion_busqueda > ]

Debido a la complejidad de la sentencia (en la sintaxis anterior no se han detallado algunoselementos), la iremos viendo poco a poco, empezaremos por ver consultas básicas para luegoir añadiendo más cláusulas.

Empezaremos por ver las consultas más simples, basadas en una sola tabla y noslimitaremos a la siguiente sintaxis:

SELECT [ALL|DISTINCT]

[TOP expresion [PERCENT] [WITH TIES]]

<lista_seleccion>

FROM <origen>

[WHERE <condicion_busqueda> ]

[ORDER BY {expression_columna|posicion_columna [ASC|DESC]} [ ,...n]]

3.2. Origen de datos FROM

De la sintaxis anterior, el elemento <origen> indica de dónde se va a extraer la informacióny se indica en la cláusula FROM, es la única cláusula obligatoria. En este tema veremos unorigen de datos basado en una sola tabla.

La sintaxis será la siguiente:

<origen>::= nb_tabla | nb_vista [[ AS ] alias_tabla ]

nb_tabla representa un nombre de tabla.

nb_vista un nombre de vista.

Power Builder + Sql


Tanto para las tablas como para las vistas, podemos hacer referencia a tablas que están enotras bases de datos (siempre que tengamos los permisos adecuados), en este caso tenemosque cualificar el nombre de la tabla, indicando delante el nombre de la base de datos (Lógica) yel nombre del esquema al que pertenece la tabla dentro de la base de datos.

Por ejemplo: MiBase.dbo.MiTabla se refiere a la tabla MiTabla que se encuentra en elesquema dbo de la base de datos MiBase.

Cuando no se definen esquemas, SQL-Server crea uno por defecto en cada base de datosdenominado dbo.

Opcionalmente podemos definir un nombre de alias.

Un nombre de alias (alias_tabla) es un nombre alternativo que se le da a la tabla dentro dela consulta.

Si se define un nombre de alias, dentro de la consulta, será el nombre a utilizar parareferirnos a la tabla, el nombre original de la tabla ya no tendrá validez.

Se utilizan los nombres de alias para simplificar los nombres de tablas a veces largos ytambién cuando queremos combinar una tabla consigo misma; ya volveremos sobre los aliasde tabla cuando veamos consultas multitabla.

La palabra AS no añade ninguna operatividad, está más por estética.

Podemos escribir:

SELECT ...

FROM tabla1 Sacamos los datos de la tabla tabla1

SELECT ...

FROM tabla1 t1 Sacamos los datos de la tabla tabla1 y leasignamos un alias de tabla: t1

SELECT ...

FROM tabla1 AS t1 Es equivalente a la sentencia anterior.

Si la tabla o la vista están en otra base de datos del mismo equipo que está ejecutando lainstancia de SQL Server, se utiliza el nombre cualificado con el formatonbBaseDatos.nbEsquema.nbTabla.

Si la tabla o la vista están fuera del servidor local en un servidor vinculado, se utiliza unnombre de cuatro partes con el formato nbservidor.catalogo.nbEsquema.nbTabla.Volveremos más adelante sobre las conexiones remotas.

3.3. La lista de selección

Power Builder + Sql


En la lista de selección <lista_seleccion> indicamos las columnas que se tienen quevisualizar en el resultado de la consulta.

<lista_seleccion> ::=

{ *

| {nombre_tabla|nombre_vista|alias_tabla}.*

| { [{nombre_tabla|nombre_vista|alias_tabla}.]

{nb_columna|$IDENTITY|$ROWGUID}

|<expresion>

}[[AS] alias_columna]

| alias_columna = <expresion>

} [ ,...n ]

Separamos la definición de cada columna por una coma y las columnas del resultadoaparecerán en el mismo orden que en la lista de selección.

Para cada columna del resultado su tipo de datos, tamaño, precisión y escala son losmismos que los de la expresión que da origen a esa columna.

Podemos definir las columnas del resultado de varias formas, mediante:

Una expresión simple:

o una referencia a una función.

o una variable local

o una constante

o una columna del origen de datos,

Una subconsulta escalar, que es otra instrucción SELECT que devuelve un único valor yse evalúa para cada fila del origen de datos (esto no lo veremos de momento).

Una expresión compleja generada al usar operadores en una o más expresionessimples.

La palabra clave *.

La asignación de variables con el formato @variable_local = expresión.

La palabra clave $IDENTITY.

La palabra clave $ROWGUID.

mailto:@variable_local

Power Builder + Sql


3.4. Columnas del origen de datos

Cuando queremos indicar en la lista de selección una columna del origen de datos, laespecificamos mediante su nombre simple o nombre cualificado. El nombre cualificado consisteen el nombre de la columna precedido del nombre de la tabla donde se encuentra la columna.

Si en el origen de datos hemos utilizado una vista o un nombre de alias, deberemos utilizarese nombre. Es obligatorio utilizar el nombre cualificado cuando el nombre de la columnaaparece en más de una tabla del origen de datos.

Ejemplos de consulta simple.

Listar nombres, oficinas, y fechas de contrato de todos los empleados:

SELECT nombre, oficina, contrato

FROM empleados;

El resultado sería:

nombre oficina contratoAntonio Viguer 12 1986-10-20Alvaro Jaumes 21 1986-12-10Juan Rovira 12 1987-03-01José González 12 1987-05-19Vicente Pantalla 13 1988-02-12Luis Antonio 11 1988-06-14Jorge Gutiérrez 22 1988-11-14Ana Bustamante 21 1989-10-12María Sunta 11 1999-10-12Juan Victor NULL 1990-01-13

Listar una tarifa de productos:

SELECT idfab, idproducto, descripcion, productos.precio

FROM productos;

Hemos cualificado la columna precio aunque no es necesario en este caso.


Power Builder + Sql


Idfab idproducto descripcion precioaci 41001 arandela 0,58aci 41002 bisagra 0,80aci 41003 art t3 1,12aci 41004 art t4 1,23aci 4100x junta 0,26aci 4100y extractor 28,88aci 4100z mont 26,25bic 41003 manivela 6,52bic 41089 rodamiento 2,25

3.5. Alias de columna

Por defecto, en el encabezado de cada columna del resultado, aparece el nombre de lacolumna origen, pero esto se puede cambiar definiendo un alias de columna, el alias decolumna es un nombre alternativo que se le da a esa columna.

El alias de columna se indica mediante la cláusula AS. Se escribe el nuevo texto tal cual sincomillas siguiendo las reglas de los identificadores.

Ejemplo:

SELECT numclie,nombre AS nombrecliente

FROM clientes;

El resultado será :

Numclie nombrecliente

2101 Luis García Antón

2102 Alvaro Rodríguez

2103 Jaime Llorens

en vez de:

Power Builder + Sql


Numclie nombre

2101 Luis García Antón

2102 Alvaro Rodríguez

2103 Jaime Llorens

La palabra AS es opcional.

SELECT numclie,nombre nombrecliente

FROM clientes;

Sería equivalente a la consulta anterior

Si queremos incluir espacios en blanco en el nombre lo debemos encerrar entre corchetes.

SELECT numclie,nombre AS [nombre cliente]

FROM clientes;

Nota importante: Este nombre de alias se podrá utilizar en la lista de selección y en lacláusula ORDER BY pero no en la cláusula WHERE.

3.6. Funciones

Existen funciones que podemos utilizar en la lista de selección, e incluso en otras cláusulasque veremos más adelante, como el WHERE. Las principales funciones son las siguientes:

Funciones de fecha:

Función Descripción

GETDATE Devuelve la fecha actual.

GETUTCDATE Devuelve la hora UTC.

Power Builder + Sql


DATEPART Devuelve un entero que corresponde a la parte de la fecha solicitada.

DAY Devuelve el día de la fecha indicada.

MONTH Devuelve el mes de la fecha indicada.

YEAR Devuelve el año de la fecha indicada.

DATENAME Devuelve una cadena de caracteres que representa el valor de launidad especificada de una fecha especificada.

DATEADD Devuelve un valor datetime nuevo que resulta de sumar un intervalo detiempo a una fecha especificada.>

DATEDIFF Devuelve el nº de intervalos que hay entre dos fechas.

@@DATEFIRST Devuelve el primer día de la semana establecido con SET DATEFIRST.

SETDATEFIRST Establece el primer día de la semana en un número del 1 al 7.

Funciones de cadena:

Función Descripción

ASCII Devuelve el valor de código ASCII del carácter situado más a la izquierdade una expresión de caracteres.

CHAR Devuelve el carácter ASCII del entero indicado.NCHAR Devuelve el carácter Unicode del entero indicado.UNICODE Devuelve el entero que se corresponde al carácter Unicode indicado.

LEN Devuelve el total de caracteres de una cadena, excluidos los espacios enblanco finales.

LTRIM Devuelve una cadena tras quitarle los espacios en blanco iniciales.RTRIM Devuelve una cadena tras quitarle los espacios en blanco finales.LEFT Devuelve los N últimos caracteres de una cadena.RIGHT Devuelve los N primeros caracteres de una cadena.SUBSTRING Devuelve parte de una expresión.LOWER Devuelve la cadena convertida a minúsculas.UPPER Devuelve la cadena convertida a mayúsculas.REPLACE Reemplaza una determinada cadena.

mailto:@DATEFIRST

Power Builder + Sql


STUFF Elimina el número de caracteres especificado e inserta otro conjunto decaracteres en el punto de inicio indicado.

QUOTENAMEDevuelve una cadena Unicode con los delimitadores agregados paraconvertirla en un identificador delimitado válido de Microsoft SQL Server2005.

SPACE Devuelve una cadena de espacios repetidos.STR Devuelve una cadena de caracteres a partir de datos numéricos.REPLICATE Repite una cadena N veces.REVERSE Devuelve una cadena invertida.

CHARINDEX Devuelve la posición inicial de la expresión especificada en una cadenade caracteres.

PATINDEXDevuelve la posición inicial de la primera repetición de un patrón en laexpresión especificada, o ceros si el patrón no se encuentra, en todos lostipos de datos de texto y caracteres.

Otras funciones:

Función DescripciónROUND Redondea un valor a la longitud y precisión indicadas.CAST yCONVERT Convierten de un tipo de datos a otro de forma explícita.

CASE Evalúa una lista de condiciones.ISNULL Reemplaza el valor NULL por otro especificado.

COALESCE Devuelve la primera expresión distinta de NULL entre susargumentos.

3.7. Columnas calculadas

Además de las columnas que provienen directamente de la tabla origen, una consulta SQLpuede incluir columnas calculadas cuyos valores se evalúan a partir de una expresión.

La expresión puede contener cualquier operador válido (+, -, *, /, &…), cualquier funciónválida, nombres de columnas del origen de datos, nombres de parámetros o constantes y paracombinar varias operaciones se pueden utilizar los paréntesis.

Ejemplos de columnas calculadas:

Listar la ciudad, región y el superávit de cada oficina. Consideraremos el superávit como elvolumen de ventas que se encuentran por encima o por debajo del objetivo de la oficina.

SELECT ciudad, region, (ventas-objetivo) AS superavit

FROM oficinas;

Power Builder + Sql


El resultado será:

ciudad region superavit

Valencia este 11800,00

Alicante este -6500,00

Castellon este 1800,00

Badajoz oeste 11100,00

A Coruña oeste -11400,00

Madrid centro NULL

Madrid centro -10000,00

Pamplona norte NULL

Valencia este -90000,00

De cada producto queremos saber el id de fabricante, id de producto, su descripción y elvalor de sus existencias.

SELECT idfab,idproducto,descripcion,(existencias*precio) AS valoracion

FROM productos;


Idfab idproducto descripcion valoracion

aci 41001 arandela 160,66

aci 41002 bisagra 133,60

aci 41003 art t3 231,84

Power Builder + Sql


aci 41004 art t4 170,97

aci 4100x junta 9,62

aci 4100y extractor 722,00

aci 4100z mont 735,00

bic 41003 manivela 19,56

bic 41089 rodamiento 175,50

Listar el nombre, mes y año del contrato de cada vendedor.

SELECT nombre, MONTH(contrato) AS [Mes de contrato], YEAR(contrato) AS[Año de contrato]

FROM empleados;

El resultado será:

Nombre Mes de contrato Año de contratoAntonio Viguer 10 1986Alvaro Jaumes 12 1986Juan Rovira 3 1987

Listar las ventas en cada oficina con el formato: 22 tiene ventas de 186,042.00 €

SELECT oficina, 'tiene ventas de ' AS [ ], ventas

FROM oficinas;


oficina ventas11 tiene ventas de 69300,0012 tiene ventas de 73500,0013 tiene ventas de 36800,00

Power Builder + Sql


21 tiene ventas de 83600,0022 tiene ventas de 18600,0023 tiene ventas de NULL24 tiene ventas de 15000,0026 tiene ventas de NULL28 tiene ventas de 0,00

El incluir una constante como columna en la lista de selección puede parecer inútil (serepetirá el mismo valor en todas las filas) pero veremos más adelante que tiene utilidad enciertos casos.

También podemos utilizar la sintaxis:

alias_columna = <expresion>

Ejemplo:

SELECT oficina, superavit = ventas-objetivo

Esto tiene el mismo efecto que

SELECT oficina, ventas-objetivo AS superavit

3.8. Utilización del asterisco *

Si queremos visualizar todas las columnas del origen de datos, en lugar de indicar todas lascolumnas una a una se puede utilizar el carácter de sustitución *.

Mostrar todos los datos de la tabla oficinas.

SELECT *

FROM oficinas;

Obtener todos los datos y el superávit de cada oficina.

Power Builder + Sql


SELECT *, (ventas-objetivo) AS superavit

FROM oficinas;

También podemos cualificar el * con un nombre de tabla, de vista o un alias de tabla:

SELECT oficinas.*

FROM oficinas;

oficinas.* se interpreta como: todas las columnas de la tabla oficinas.

Esta forma se utiliza normalmente cuando el origen está basado en varias tablas y queremosindicar todas las columnas no del origen completo sino de una tabla concreta.

3.9. Las palabras clave $IDENTITY y $ROWGUID

La palabra clave $IDENTITY se interpreta como la columna de la tabla que tiene lapropiedad IDENTITY (la columna de identidad que vimos en un tema anterior).

Por ejemplo, si en la columna codigo de la tabla usuarios (BD Biblio) se ha definido lapropiedad IDENTITY.

SELECT $IDENTITY, nombre, apellidos

FROM usuarios;

Es equivalente a:

SELECT codigo, nombre, apellidos

FROM usuarios;

La palabra clave $ROWGUID se interpreta como la columna de la tabla que tiene lapropiedad ROWGUIDCOL y se puede utilizar de la misma forma que $IDENTITY.

3.10. Ordenación de las filas del resultado ORDER BY

Si queremos que las filas del resultado de la consulta aparezcan ordenadas, lo podemosindicar mediante la cláusula ORDER BY.

Power Builder + Sql


ORDER BY {expression_columna|posicion_columna [ASC|DESC]}[ ,...n ]

Podemos indicar una columna o varias separadas por una coma, la columna de ordenaciónse especifica mediante el nombre de columna en el origen de datos o su posición dentro de lalista de selección. Si utilizamos el nombre de columna, no hace falta que la columna aparezcaen la lista de selección. Si utilizamos la posición es la posición de la columna dentro de la listade selección empezando en 1.

Por defecto la filas se ordenarán en modo ascendente (ASC), de menor a mayor; siqueremos invertir ese orden añadimos detrás de la columna la palabra DESC.

Si la columna de ordenación es alfanumérica, las filas se ordenarán por orden alfabético.

Si la columna de ordenación es numérica, las filas se ordenarán de menor a mayor.

Si la columna de ordenación es de tipo fecha, las filas se ordenarán de más antigua amás reciente o futura.

Ejemplos:

Mostrar las ventas de cada oficina, ordenadas por orden alfabético de región y dentro decada región por ciudad.

SELECT oficina, region, ciudad, ventas

FROM oficinas

ORDER BY region, ciudad;

Da como resultado:

Oficina region ciudad ventas24 centro Aranjuez 15000,0023 centro Madrid NULL12 este Alicante 73500,0013 este Castellón 36800,0011 este Valencia 69300,0028 este Valencia 0,0026 norte Pamplona NULL22 oeste A Coruña 18600,0021 oeste Badajoz 83600,00

Listar las oficinas de manera que las oficinas de mayores ventas aparezcan en primer lugar.

Power Builder + Sql


SELECT ciudad, region, ventas

FROM oficinas

ORDER BY ventas DESC;

ciudad region ventasBadajoz oeste 83600,00Alicante este 73500,00Valencia este 69300,00Castellon este 36800,00A Coruña oeste 18600,00Aranjuez centro 15000,00Valencia este 0,00Pamplona norte NULLMadrid centro NULL

Listar las oficinas clasificadas en orden descendente de rendimiento de ventas, de modo quelas de mayor rendimiento aparezcan las primeras.

SELECT ciudad, region, ventas-objetivo

FROM oficinas

ORDER BY 3 DESC;

Lo mismo que el anterior pero agrupadas por región.

SELECT region, ciudad, (ventas-objetivo) AS superavit

FROM oficinas

ORDER BY region, superavit DESC;

Resultado:

Region ciudad superavitcentro Aranjuez -10000,00centro Madrid NULLeste Valencia 11800,00este Castellón 1800,00

Power Builder + Sql


este Alicante -6500,00este Valencia -90000,00norte Pamplona NULLoeste Badajoz 11100,00oeste A Coruña -11400,00

En este caso hemos utilizado el alias de columna para hacer referencia a la columnacalculada y también se puede observar que las filas aparecen ordenadas por región ascendente(no hemos incluido nada después del nombre de la columna) y dentro de cada región porsuperávit y descendente.

3.11. Eliminar filas duplicadas DISTINCT/ALL

SQL no elimina las filas duplicadas en el resultado de la consulta, si nosotros no queremosque se repitan las filas, tenemos la cláusula DISTINCT.Al incluir la cláusula DISTINCT en la SELECT, se eliminará del resultado las repeticiones defilas de resultado. Si por el contrario queremos que aparezcan todas las filas seleccionadaspodemos incluir la cláusula ALL o nada, ya que ALL es el valor por defecto.

Listar los nº de empleado de los directores de las oficinas.

SELECT dir

FROM oficinas;

dir106

104

105

108

108

108

108

NULL

NULL

Si un mismo empleado dirige varias oficinas (por ejemplo el 108), su código aparece repetidoen el resultado. Para evitarlo modificamos la consulta:

Power Builder + Sql


SELECT DISTINCT dir

FROM oficinas;

dirNULL

104

105

106

108

Han desaparecido los valores duplicados.

Los que se eliminan son valores duplicados de filas del resultado, por ejemplo:

SELECT DISTINCT dir, region

FROM oficinas;

dir regionNULL este

NULL norte

104 este

105 este

106 este

108 centro

108 oeste

Ahora el 108 aparece dos veces porque las dos filas donde aparece no son iguales (porquetienen distinta región).

NOTA: La cláusula DISTINCT hace que la consulta tarde algo más en ejecutarse debido alproceso adicional de buscar y eliminar las repeticiones, por lo que se aconseja utilizarlaúnicamente cuando sea imprescindible.

3.12. La cláusula TOP

Power Builder + Sql


[TOP <expresión> [PERCENT] [WITH TIES]]

La cláusula TOP indica que en el resultado no deben aparecer todas las filas resultantessino un cierto número de registros, las n primeras. Si la consulta incluye la cláusula ORDER BY,se realiza la ordenación antes de extraer los n primeros registros.

La expresión representa ese número n y debe devolver un número entero sin signo.

Por ejemplo tenemos la siguiente tabla:

SELECT * FROM productos:

Si ordenamos por ventas:

SELECT * FROM productos

ORDER BY ventas;

Obtenemos el siguiente resultado:

Power Builder + Sql


Si añadimos la cláusula TOP:

SELECT TOP 3 * FROM productos

ORDER BY ventas

Obtenemos los 3 primeros registros:

Si existen más registros con las mismas ventas que el último valor de la lista, éstos nosaldrán en el resultado de la consulta.

En el ejemplo el registro con cod = 2 no sale en el resultado y tiene las mismas ventas quecod = 3.

Si queremos que salgan añadimos la cláusula WITH TIES. La cláusula WITH TIES sólo sepuede emplear si la SELECT incluye un ORDER BY, de lo contrario dará error.

Si añadimos la cláusula WITH TIES:

SELECT TOP 3 WITH TIES *

FROM productos

ORDER BY ventas

Obtenemos:

Se incluyen en el resultado todos los registros que tienen ventas iguales al último registro.

Otro ejemplo:

Power Builder + Sql


SELECT TOP 10 oficina, ciudad, ventas

FROM oficinas

ORDER BY ventas;

Devuelve las 10 peores oficinas en cuanto a ventas: ordenamos las oficinas por ventas demenor a mayor y sacamos las 10 primeras.

Si incluimos la palabra PERCENT, entonces n no indica el número de registros a recuperarsino el porcentaje de registros a recuperar del total de filas recuperadas después de ejecutar lacláusula WHERE.

SELECT TOP 50 PERCENT *

FROM productos

ORDER BY ventas

Devuelve:

Si el porcentaje no da exacto, siempre redondea al alza.

3.13. Selección de filas WHERE

La cláusula WHERE se emplea para especificar las filas que se desean recuperar del origende datos.

WHERE <condicion_búsqueda>

<condicion_búsqueda> ::=

{ [NOT]<predicado>

|(<condicion_búsqueda>)

}

[{AND|OR} [NOT] {<predicado>|(<condicion_búsqueda>)}]

Power Builder + Sql


[ ...n ]

En el resultado de la consulta sólo aparecerán las filas que cumplan que la condición debúsqueda sea TRUE, los valores NULL no se incluyen, por lo tanto, en las filas del resultado. Lacondición de búsqueda puede ser una condición simple o una condición compuesta por variascondiciones (predicados) unidas por operadores AND y OR, no hay límite en cuanto al númerode predicados que se pueden incluir. En las condiciones compuestas se pueden utilizarparéntesis para delimitar predicados y se aconseja su uso cuando se incluyen operadores ANDy OR en la misma condición de búsqueda.

3.14. Predicados

En SQL tenemos 7 tipos de predicados, condiciones básicas de búsqueda:

Comparación estándar

Pertenencia a un intervalo (BETWEEN)

Pertenencia a un conjunto (IN)

Test de valor nulo (IS NULL).

Coincidencia con patrón (LIKE)

Si contiene (CONTAINS)

FREETEXT

Comparación estándar.

Compara el valor de una expresión con el valor de otra. Para la comparación se puedenemplear = , <> , !=, < , <= , !<, > , >= ,!>

Sintaxis:

<expresion> {=|<>|!=|>|>=|!>|<|<=|!<} <expresion>

<expresion> Puede ser:

Un nombre de columna,

una constante,

una función (inclusive la función CASE),

una variable,

Power Builder + Sql


una subconsulta escalar o

cualquier combinación de nombres de columna, constantes y funciones conectadosmediante uno o varios operadores o una subconsulta.

Ejemplo:

Listar los "buenos" vendedores (los que han rebasado su cuota).

SELECT numemp, nombre, ventas, cuota

FROM empleados

WHERE ventas > cuota

numemp nombre ventas cuota

101 Antonio Viguer 30500,00 30000,00

102 Alvaro Jaumes 47400,00 35000,00

103 Juan Rovira 28600,00 27500,00

105 Vicente Pantalla 36800,00 35000,00

106 Luis Antonio 29900,00 27500,00

108 Ana Bustamante 36100,00 35000,00

109 María Sunta 39200,00 3000,00

Las columnas que aparecen en el WHERE no tienen por qué aparecer en la lista deselección, esta instrucción es igual de válida:

SELECT numemp, nombre

FROM empleados

WHERE ventas > cuota;

Hallar vendedores contratados antes de 1988.

SELECT numemp, nombre, contrato

FROM empleados

Power Builder + Sql


WHERE contrato < '01/01/1988';

numemp nombre contrato

101 Antonio Viguer 1986-10-20

102 Alvaro Jaumes 1986-12-10

103 Juan Rovira 1987-03-01

104 José González 1987-05-19

También podemos utilizar funciones, ésta es equivalente a la anterior:

SELECT numemp, nombre

FROM empleados

WHERE YEAR(contrato) < 1988;

La función YEAR(fecha) devuelve el año de una fecha.

Hallar oficinas cuyas ventas estén por debajo del 80% de su objetivo:

SELECT oficina

FROM oficinas

WHERE ventas < (.8 * objetivo);

Hallar las oficinas dirigidas por el empleado 108:

SELECT oficina

FROM oficinas

WHERE dir = 108;

Power Builder + Sql


Pertenencia a un intervalo. BETWEEN

<expresion> [NOT] BETWEEN <expresion2> AND <expresion3>

Examina si el valor de la expresión de test está en el rango delimitado por los valoresresultantes de expresion1 y expresion2, estos valores no tienen porqué estar ordenados enANSI/ISO; expresion1 debe ser menor o igual a expresion2.

Hallar vendedores cuyas ventas estén entre 20.000 euros y 50.000.

SELECT numemp, nombre, ventas

FROM empleados

WHERE ventas BETWEEN 20000 AND 100000;

numemp nombre ventas

101 Antonio Viguer 30500,00

102 Alvaro Jaumes 47400,00

103 Juan Rovira 28600,00

105 Vicente Pantalla 36800,00

106 Luis Antonio 29900,00

108 Ana Bustamante 36100,00

109 María Sunta 39200,00

La instrucción anterior es equivalente a:

SELECT numemp, nombre, ventas

FROM empleados

WHERE ventas >= 20000 AND ventas <=100000;

Parece que con BETWEEN se lee mejor.

Observa que no hemos utilizado separadores de millares (100.000), porque se habríainterpretado por una coma decimal.

Power Builder + Sql


Test de pertenencia a conjunto IN

<expresion> IN ( <exp_valor> [ ,...n ] )

Examina si el valor de la expresion es uno de los valores incluidos en la lista de valoresindicados entre paréntesis. Se pueden expresar los valores mediante cualquier expresión, laúnica condición es que todas las exp_valor devuelvan el mismo tipo de datos.

Ejemplo:

Obtener los empleados que trabajan en las oficinas 11, 20 o 22:

SELECT oficina, numemp, nombre

FROM empleados

WHERE oficina IN (11,20,22);

oficina numemp nombre

11 106 Luis Antonio

22 107 Jorge Gutiérrez

11 109 María Sunta

Test de valor nulo IS NULL

<expression> IS [NOT] NULL

Una condición de búsqueda puede ser TRUE, FALSE o NULL/UNKNOW, este último casose produce cuando algún campo que interviene en la condición tiene valor NULL.A veces es útil comprobar explícitamente los valores NULL en una condición de búsqueda yaque estas filas puede que queramos darles un tratamiento especial, para ello tenemos elpredicado IS NULL.

Este test produce un valor TRUE o FALSE, por lo que se podrá combinar con otrascondiciones. El valor NULL no es en sí un valor por eso no lo podemos utilizar en una igualdad.

SELECT numemp,nombre

FROM empleados

Power Builder + Sql


WHERE oficina = NULL;

Esta instrucción no da error pero no obtiene lo que en principio parece que quiere obtener.No obtenemos los empleados cuya oficina sea un valor nulo (es decir los empleados que notienen oficina), no obtenemos nada, en cambio los obtendremos utilizando el test de valor nulo:

SELECT numemp,nombre, oficina

FROM empleados

WHERE oficina IS NULL;

Resultado:

numemp nombre oficina

110 Juan Victor NULL

Juan Victor es el único empleado que no tiene oficina asignada.

Listar los vendedores asignados a alguna oficina.

SELECT numemp, nombre, oficina

FROM empleados

WHERE oficina IS NOT NULL;

numemp nombre oficina

101 Antonio Viguer 12

102 Alvaro Jaumes 21

103 Juan Rovira 12

104 José González 12

105 Vicente Pantalla 13

106 Luis Antonio 11

107 Jorge Gutiérrez 22

Power Builder + Sql


108 Ana Bustamante 21

109 María Sunta 11

Test de correspondencia con patrón LIKE

Se utiliza cuando queremos comparar el valor de una columna con un patrón en el que seutilice caracteres comodines.

<expression> [NOT] LIKE <patron> [ESCAPE 'car_escape']

Con expresión indicamos el valor a comparar (normalmente será el nombre de una columna)y patrón es la cadena que se busca. El patrón es de tipo texto y tiene que escribirse entrecomillas simples. Dentro del patrón podemos utilizar los siguientes comodines:

% representa cualquier cadena de cero o más caracteres.


FROM empleados

WHERE nombre LIKE ‘An%’;

numemp nombre

101 Antonio Viguer

108 Ana Bustamante

Obtiene todos los nombres que empiecen por An.


FROM empleados

WHERE nombre LIKE ‘%z’;

Power Builder + Sql


numemp nombre

104 José González

107 José González

Obtiene los nombres que acaban en z.


FROM empleados

WHERE nombre LIKE ‘%on%’;

numemp nombre

101 Antonio Viguer

104 José González

106 Luis Antonio

Obtiene los nombres que contienen on.

_ representa cualquier carácter (sólo uno).


FROM empleados

WHERE nombre LIKE '__a%';

numemp nombre

103 Juan Rovira

108 Ana Bustamante

110 Juan Victor

Obtiene los nombres cuya tercera letra sea una a (en el patrón tenemos dos caracteressubrayado).

[ ] sirve para indicar un carácter cualquiera perteneciente al conjunto indicando.El conjunto se indica enumerando los caracteres o indicando un intervalo.

Power Builder + Sql



FROM empleados

WHERE nombre LIKE '[a-d]%';

Obtiene los nombres que empiezan por cualquier letra de la a a la d.

Es equivalente a escribir:


FROM empleados

WHERE nombre LIKE '[abcd]%';

[^] significa cualquier carácter individual que no se encuentre en el conjunto.


FROM empleados

WHERE nombre LIKE '[^abcd]%';

Y


FROM empleados

WHERE nombre LIKE '[^a-d]%';

Obtienen los nombres que no empiecen por a, b, c ni d.

Es importante tener en cuenta que dentro del patrón el espacio en blanco es consideradocomo un carácter más, si colocamos dos espacios en el patrón, se buscarán dos espacios en elcampo.

Si queremos incluir en el patrón uno de los caracteres comodines y que no sea interpretadocomo un comodín, sino como un carácter normal, lo tenemos que encerrar entre corchetes outilizar un carácter de escape.

[ESCAPE 'car_escape']

Power Builder + Sql


La cláusula ESCAPE es opcional y permite definir un carácter de escape.

Un carácter de escape es un carácter que se coloca delante de un carácter comodín paraindicar que el comodín no debe interpretarse como tal, sino como un carácter normal.

Por ejemplo queremos buscar los nombres compuestos que incluyen un subrayado. En estecaso tenemos que poner el carácter _ como un carácter normal no como un comodín, así que loescribiremos así:


FROM empleados

WHERE nombre LIKE '%[_]%';

O bien,


FROM empleados

WHERE nombre LIKE '%!_%' ESCAPE '!';

Existen dos predicados más para evaluar expresiones: CONTAINS y FREETEXT. Puedesver más información sobre ellas en este avanzado .

Los predicados CONTAINS y FREETEXT (I)

CONTAINS

Se utiliza para buscar, coincidencias exactas o aproximadas con palabras o frases, palabraspróximas a otra dada en una cierta distancia, o coincidencias ponderadas.

CONTAINS puede buscar:

Una palabra o una frase.

El prefijo de una palabra o una frase.

Una palabra cerca de otra palabra.

Una palabra que sea derivada de otra (por ejemplo, las palabras controles,controladores, controlando y controlado son derivadas de control).

Una palabra que sea un sinónimo de otra palabra usando el diccionario de sinónimos(por ejemplo, la palabra metal puede tener sinónimos como aluminio y acero).

Power Builder + Sql


CONTAINS

( { nombre_columna | (lista_columnas) | * }

, '< condicion_busqueda >'

[ , LANGUAGE idioma ]

)

< condicion_busqueda > ::=

{ < termino_simple >

| < prefijo >

| < generador >

| < proximo >

| < valor_ponderado >

}

| { ( < condicion_busqueda > )

[ { < AND > | < AND NOT > | < OR > } ]

< condicion_busqueda > [ ...n ]

}

< AND > ::= { AND | & }

< AND NOT > ::= { AND NOT | & !}

< OR > ::= { OR | | }

< termino_simple > ::=

palabra | " frase "

< prefijo > ::=

{ "palabra * " | "frase *" }

< generador > ::=

FORMSOF ( { INFLECTIONAL | THESAURUS } , < termino_simple > [,...n ] )

< proximo > ::=

{ < termino_simple > | < prefijo > }

{ { NEAR | ~ }

{ < termino_simple > | < prefijo > }

Power Builder + Sql


} [ ...n ]

< valor_ponderado > ::=

ISABOUT

( { {

< termino_simple >

| < prefijo >

| < generador >

| < proximo >

}

[ WEIGHT ( peso ) ]

} [ ,...n ]

)

Nombre_columna es la columna donde se busca. Las columnas de tipo char, varchar,nchar, nvarchar, text, ntext, image, xml y varbinary(max) son válidas para la búsqueda detexto.

Lista_columnas indica una lista de columnas separadas por comas.

El asterisco ( * ) especifica que todas las columnas de la tabla válidas para búsquedas detexto se deben utilizar en la condición de búsqueda. Las columnas de la cláusula CONTAINSdeben proceder de una sola tabla. Si en la cláusula FROM hay más de una tabla, * se tiene queespecificar con el nombre de la tabla.

Idioma Es el idioma en que el usuario genera la consulta.

Condición búsqueda especifica el texto que se va a buscar y las condiciones para obtenercoincidencias.

Palabra es una cadena de caracteres sin espacios ni signos de puntuación.

Frase es una o varias palabras con espacios entre cada una de ellas.

Generador especifica la coincidencia de palabras cuando los términos simples incluyenvariaciones de la palabra original que se busca para buscar palabras derivadas.

Power Builder + Sql


INFLECTIONAL especifica que se va a utilizar el analizador lingüístico dependiente delidioma en el término simple especificado. El comportamiento del analizador lingüístico se defineen función de las reglas de análisis lingüístico de cada idioma concreto. El idioma neutro notiene ningún analizador lingüístico asociado. El idioma de las columnas que se van a consultarse utiliza para hacer referencia al analizador lingüístico deseado. Si se especifica idioma, seutiliza el analizador lingüístico correspondiente a dicho idioma.

THESAURUS especifica que se utiliza el diccionario de sinónimos correspondiente al idiomade texto de la columna o el idioma especificado en la consulta. El patrón o patrones más largosde termino_simple se hacen coincidir con el diccionario de sinónimos y se generan términosadicionales para expandir o reemplazar el patrón original.

Próximo especifica una coincidencia de palabras o frases que deben estar próximas entresí. funciona de forma similar al operador AND: ambos requieren que existan varias palabras ofrases en la columna examinada.

NEAR | ~ indica que la palabra o frase situada a la izquierda del operador NEAR o ~ tieneque estar bastante cerca de la palabra o frase situada a la derecha del operador NEAR o ~. Sepueden encadenar varios términos de proximidad.

<valor_ponderado> especifica que las filas coincidentes (devueltas por la consulta) coincidencon una lista de palabras y frases a las que se asigna opcionalmente un valor ponderado.WEIGHT(peso) especifica el valor de ponderación como un número entre 0,0 y 1,0. Cadacomponente de < valor_ponderado > puede incluir un peso.

FREETEXT

Es un predicado que se utiliza para buscar en columnas que contengan tipos de datosbasados en caracteres valores que coincidan con el significado y no literalmente con laspalabras de la condición de búsqueda. Cuando se utiliza FREETEXT, el motor de consulta detexto realiza internamente las siguientes acciones en freetext_string, asigna a cada uno de lostérminos un peso y busca las coincidencias.

Separa la cadena en palabras individuales basándose en límites de palabras (separación depalabras).

Genera formas no flexionadas de las palabras (lematización).

Identifica una lista de expansiones o reemplazos de los términos basándose encoincidencias en el diccionario de sinónimos.

FREETEXT ( { nombre_columna | (list_columnas) | * }

, 'cadena_freetext' [ , LANGUAGE idioma ] )

Nombre_columna es la columna donde se busca. Las columnas de tipo char, varchar,nchar, nvarchar, text, ntext, image, xml y varbinary(max) son válidas para labúsqueda de texto.

Power Builder + Sql


Lista_columnas indica una lista de columnas separadas por comas.

El asterisco ( * ) especifica que todas las columnas de la tabla válidas para búsquedasde texto se deben utilizar en la condición de búsqueda. Las columnas de la cláusulaCONTAINS deben proceder de una sola tabla. Si en la cláusula FROM hay más de unatabla, * se tiene que especificar con el nombre de la tabla.

Idioma Es el idioma en que el usuario genera la consulta.

Condición búsqueda especifica el texto que se va a buscar y las condiciones paraobtener coincidencias.

Cadena_freetext es el texto que se va a buscar en nombre_columna. Se puedeescribir cualquier texto, incluidas palabras, frases y oraciones. Se generaráncoincidencias si se encuentra algún término o las formas de algún término en el índicede texto.

Si cadena_freetext se incluye entre comillas dobles, se realiza una búsqueda de frasescoincidentes, por lo que no se extraen las desinencias ni se utiliza el diccionario desinónimos.

Las consultas de búsqueda de texto que utilizan FREETEXT son menos precisas que lasconsultas de texto que utilizan CONTAINS. El motor de búsqueda de texto de SQL Serveridentifica las palabras y las frases importantes. No se le da significado especial a ninguna de laspalabras clave reservadas

3.15. Condiciones de búsqueda compuestas

En una cláusula WHERE podemos incluir una condición de búsqueda simple (formada porun solo predicado) o compuesta (formada por la combinación de predicados unidos por losoperadores lógicos NOT, AND, OR).

Cuando la condición incluye varios operadores lógicos, el orden de prioridad de estosoperadores es:

NOT (el más alto),

seguido de AND y OR (estos dos al mismo nivel).

Como siempre, se pueden utilizar paréntesis para alterar esta prioridad en una condición debúsqueda.

El orden de evaluación de los operadores lógicos puede variar dependiendo de las opcioneselegidas por el optimizador de consultas.

Los operadores lógicos pueden devolver tres valores distintos: TRUE, FALSE, NULL(UNKNOWN).

Tablas de verdad de los operadores:

Power Builder + Sql


AND Combina dos condiciones y se evalúa como TRUE cuando ambas condiciones sonTRUE.

AND TRUE FALSE NULLTRUE TRUE FALSE NULLFALSE FALSE FALSE FALSENULL NULL FALSE NULL

OR Combina dos condiciones y se evalúa como TRUE cuando alguna de las condiciones esTRUE.

OR TRUE FALSE NULLTRUE TRUE TRUE TRUEFALSE TRUE FALSE NULLNULL TRUE NULL NULL

NOT Niega la expresión booleana que especifica el predicado

NOT TRUE FALSE NULLFALSE TRUE NULL

Hallar los vendedores que están por debajo de su cuota y tienen ventas inferiores a 30.000.

SELECT nombre

FROM empleados

WHERE ventas < cuota AND ventas < 30000;

Hallar los vendedores que están debajo de su cuota, pero cuyas ventas no sean inferiores a150.000.

SELECT nombre

FROM empleados

WHERE ventas < cuota AND ventas < 150000;

Hallar las oficinas no dirigidas por el empleado 108

SELECT oficina

Power Builder + Sql


FROM oficinas

WHERE NOT dir = 108;

O

SELECT oficina

FROM oficinas

WHERE dir <> 108;

Devuelven:

oficina111213

Las oficinas sin director no aparecen, para que aparezcan deben añadir otro predicado:

SELECT oficina, dir

FROM oficinas

WHERE NOT dir = 108 or dir is null;

oficina dir11 10612 10413 10526 NULL28 NULL

Power Builder + Sql


Consultas multitabla

4.1. Introducción

Hasta ahora hemos visto consultas que obtienen los datos de una sola tabla, en este temaveremos cómo obtener datos de diferentes tablas. En esta parte ampliaremos la cláusula FROMy descubriremos nuevas palabras reservadas (UNION, EXCEPT e INTERSECT) quecorresponden a operaciones relacionales. Para obtener datos de varias tablas tenemos quecombinar estas tablas mediante alguna operación basada en el álgebra relacional. El álgebrarelacional define una serie de operaciones cuyos operandos son tablas y cuyo resultado estambién una tabla.

Las operaciones de álgebra relacional implementadas en Transact-Sql son:

La unión UNION

La diferencia EXCEPT

La intersección INTERSECT

El producto cartesiano CROSS JOIN

La composición interna INNER JOIN

La composición externa LEFT JOIN, RIGHT JOIN Y FULL JOIN

En todo el tema cuando hablemos de tablas nos referiremos tanto a las tablas quefísicamente están almacenadas en la base de datos como a las tablas temporales y a lasresultantes de una consulta o vista.

4.2. La unión de tablas UNION

La unión de tablas consiste en coger dos tablas y obtener una tabla con las filas de las dostablas, en el resultado aparecerán las filas de una tabla y, a continuación, las filas de la otratabla.Para poder realizar la operación, las dos tablas tienen que tener el mismo esquema (mismonúmero de columnas y tipos compatibles) y la tabla resultante hereda los encabezados de laprimera tabla.

La sintaxis es la siguiente:

{< consulta >|(< consulta >)}

UNION [ALL]

{< consulta >|(< consulta >)}

[{UNION [ALL] {< consulta >|(< consulta >)}}[ ...n ] ]

[ORDER BY {expression_columna|posicion_columna [ASC|DESC]}

Power Builder + Sql


[ ,...n ]]

< consulta > representa la especificación de la consulta que nos devolverá la tabla acombinar.Puede ser cualquier especificación de consulta con la limitación de que no admite la cláusulaORDER BY, los alias de campo se pueden definir pero sólo tienen efecto cuando se indican enla primera consulta ya que el resultado toma los nombres de columna de esta.

Ejemplo: Suponemos que tenemos una tabla Valencia con las nuevas oficinas de Valencia yotra tabla Madrid con las nuevas oficinas de Madrid y queremos obtener una tabla con lasnuevas oficinas de las dos ciudades:

SELECT oficina as OFI, ciudad FROM Valencia

UNION ALL

SELECT oficina, ciudad FROM Madrid;


OFI ciudad

11 Valencia

28 Valencia

23 Madrid

El resultado coge los nombres de columna de la primera consulta y aparecen primero lasfilas de la primera consulta y después las de la segunda.

Si queremos que el resultado aparezca ordenado podemos incluir la cláusula ORDER BY,pero después de la última especificación de consulta, y expresion_columna será cualquiercolumna válida de la primera consulta.

SELECT oficina as OFI, ciudad FROM Valencia

UNION

SELECT oficina, ciudad FROM Madrid

ORDER BY ofi;

Power Builder + Sql


OFI ciudad

11 Valencia

23 Madrid

28 Valencia

Ahora las filas aparecen ordenadas por el número de oficina y hemos utilizado el nombre decolumna de la primera consulta.

Cuando aparezcan en el resultado varias filas iguales, el sistema por defecto elimina lasrepeticiones.Si se especifica ALL, el sistema devuelve todas las filas resultante de la unión incluidas lasrepetidasEl empleo de ALL también hace que la consulta se ejecute más rápidamente ya que el sistemano tiene que eliminar las repeticiones.

Se pueden combinar varias tablas con el operador UNION. Por ejemplo supongamos quetenemos otra tabla Pamplona con las oficinas nuevas de Pamplona:

SELECT oficina, ciudad FROM Valencia

UNION

SELECT oficina, ciudad FROM Madrid

UNION

SELECT oficina, ciudad FROM Pamplona;

Combinamos las tres tablas.

Otro ejemplo:

Obtener todos los productos cuyo precio exceda de 20 € o que se haya vendido más de 300euros del producto en algún pedido.

SELECT idfab, idproducto

FROM productos

WHERE precio > 20

UNION

SELECT fab, producto

FROM pedidos

Power Builder + Sql


WHERE importe > 300;

4.3. La diferencia EXCEPT

Aparecen en la tabla resultante las filas de la primera consulta que no aparecen en lasegunda.Las condiciones son las mismas que las de la unión.

{<consulta>|(<consulta>)}

EXCEPT

{<consulta>|(<consulta>)}

[{EXCEPT {<consulta>|(<consulta>)}}[ ...n ] ]


[ ,...n ]]

Por ejemplo tenemos las tablas T1 y T2.

T1 T2

Cod

1

2

4

5

6

Codigo

2

3

4

5

SELECT cod FROM T1

EXCEPT

SELECT codigo FROM T2;

Devuelve:

Power Builder + Sql


Cod

1

6

Ejemplo:

Listar los productos que no aparezcan en ningún pedido.


FROM productos

EXCEPT

SELECT DISTINCT fab, producto

FROM pedidos;

4.4. La intersección INTERSECT

Tiene una sintaxis parecida a las anteriores pero en el resultado de la intersección aparecenlas filas que están simultáneamente en las dos consultas.Las condiciones son las mismas que las de la unión.

{ <consulta>|(<consulta>)}

INTERSECT

{<especificacion_consulta>|(<especificacion_consulta>)}

[{INTERSECT {<consulta>|(<consulta>)}} [ ...n ] ]


[ ,...n ]]

Retomando el ejemplo anterior:

SELECT cod FROM T1

INTERSECT

SELECT cod FROM T2;

Power Builder + Sql


Devuelve:

Cod

2

4

5

Ejemplo: Obtener todos los productos que valen más de 20 euros y que además se hayavendido en un pedido más de 300 euros de ese producto.


FROM productos

WHERE precio > 20

INTERSECT

SELECT fab, producto

FROM pedidos

WHERE importe > 300;

4.5. La composición de tablas

Hasta ahora hemos operado con tablas que tenían el mismo esquema, pero muchas veceslo que necesitamos es obtener una tabla que tenga en una misma fila datos de varias tablas,por ejemplo, obtener las facturas y que en la misma fila de factura aparezca el nombre ydirección del cliente. Pues en lo que queda del tema estudiaremos este tipo de consultasbasadas en la composición de tablas. La composición de tablas consiste en obtener a partir dedos tablas cualesquiera una nueva tabla fusionando las filas de una con las filas de la otra,concatenando los esquemas de ambas tablas. Consiste en formar parejas de filas.

La sentencia SELECT permite realizar esta composición, incluyendo dos o más tablas en lacláusula FROM.

Es hora de ampliar la cláusula FROM que vimos en el tema anterior.

Empezaremos por estudiar la operación a partir de la cual están definidas las demásoperaciones de composición de tabla, el producto cartesiano.

Power Builder + Sql


4.6. El producto cartesiano CROSS JOIN

El producto cartesiano obtiene todas las posibles concatenaciones de filas de la primeratabla con filas de la segunda tabla.

Se indica escribiendo en la cláusula FROM los nombres de las tablas separados por unacoma o utilizando el operador CROSS JOIN.

FROM {<tabla_origen>} [ ,...n ]

|<tabla_origen> CROSS JOIN <tabla_origen>

Tabla_origen puede ser un nombre de tabla o de vista o una tabla derivada (resultado deuna SELECT), en este último caso la SELECT tiene que aparecer entre paréntesis y la tabladerivada debe llevar asociado obligatoriamente un alias de tabla. También puede ser unacomposición de tablas.Se pueden utilizar hasta 256 orígenes de tabla en una instrucción, aunque el límite varía enfunción de la memoria disponible y de la complejidad del resto de las expresiones de laconsulta. También se puede especificar una variable table como un origen de tabla.

Ejemplo:

SELECT *

FROM empleados, oficinas;

Si ejecutamos esta consulta veremos que las filas del resultado están formadas por lascolumnas de empleados y las columnas de oficinas. En las filas aparece cada empleadocombinado con la primera oficina, luego los mismos empleados combinados con la segundaoficina y así hasta combinar todos los empleados con todas las oficinas.Si ejecutamos:

SELECT *

FROM empleados CROSS JOIN oficinas;

Obtenemos lo mismo.

Este tipo de operación no es la que se utiliza más a menudo, lo más frecuente seríacombinar cada empleado con los datos de SU oficina. Lo podríamos obtener añadiendo a laconsulta un WHERE para filtrar los registros correctos:

SELECT *

FROM empleados, oficinas

Power Builder + Sql


WHERE empleados.oficina=oficinas.oficina;

Aquí nos ha aparecido la necesidad de cualificar los campos ya que el nombre oficina es uncampo de empleados y de oficinas por lo que si no lo cualificamos, el sistema nos da error.Hemos utilizado en la lista de selección *, esto nos recupera todas las columnas de las dostablas.

SELECT empleados.*,ciudad, region

FROM empleados, oficinas

WHERE empleados.oficina=oficinas.oficina;

Recupera todas las columnas de empleados y las columnas ciudad y región de oficinas.

También podemos combinar una tabla consigo misma, pero en este caso hay que definir unalias de tabla, en al menos una, sino el sistema da error ya que no puede nombrar los campos.

SELECT *

FROM oficinas, oficinas as ofi2;

No insistiremos más sobre el producto cartesiano porque no es la operación más utilizada,ya que normalmente cuando queramos componer dos tablas lo haremos con una condición deselección basada en campos de combinación y para este caso es más eficiente el JOIN queveremos a continuación.

4.7. La composición interna INNER JOIN

Una composición interna es aquella en la que los valores de las columnas que se estáncombinando se comparan mediante un operador de comparación.Es otra forma, mejor, de expresar un producto cartesiano con una condición.Es la operación que más emplearemos ya que lo más frecuente es querer juntar los registros deuna tabla relacionada con los registros correspondientes en la tabla de referencia (añadir acada factura los datos de su cliente, añadir a cada línea de pedido los datos de su producto,etc..,).

FROM

<tabla_origen> INNER JOIN <tabla_origen> ON <condicion_combi>

tabla_origen tiene el mismo significado que en el producto cartesiano.condicion_combi es cualquier condición que permite seleccionar las parejas de filas queaparecen en el resultado. Normalmente será una condición de igualdad.

Power Builder + Sql


SELECT *

FROM empleados INNER JOIN oficinas

ON empleados.oficina=oficinas.oficina;

Obtiene los empleados combinados con los datos de su oficina.

SELECT *

FROM pedidos INNER JOIN productos

ON producto = idproducto AND fab = idfab;

Obtiene los pedidos combinados con los productos correspondientes.

Normalmente la condición de combinación será una igualdad pero se puede utilizar cualquieroperador de comparación (<>, >…).

Es fácil ver la utilidad de esta instrucción y de hecho se utilizará muy a menudo, pero hayalgún caso que no resuelve. En las consultas anteriores, no aparecen las filas que no tienen filacorrespondiente en la otra tabla.

SELECT numemp,nombre,empleados.oficina, ciudad

FROM empleados INNER JOIN oficinas


numemp nombre oficina ciudad

101 Antonio Viguer 12 Alicante

102 Alvaro Jaumes 21 Badajoz

103 Juan Rovira 12 Alicante

104 José González 12 Alicante

105 Vicente Pantalla 13 Castellón

106 Luis Antonio 11 Valencia

107 Jorge Gutiérrez 22 A Coruña

108 Ana Bustamante 21 Badajoz

Power Builder + Sql


109 María Sunta 11 Valencia

No aparecen los empleados que no tienen oficina, ni las oficinas que no tienen empleados,porque para que salga la fila, debe de existir una fila de la otra tabla que cumpla la condición.

Para resolver este problema debemos utilizar otro tipo de composición, la composición externa.

4.8. La Composición externa LEFT, RIGHT y FULL OUTER JOIN

La composición externa se escribe de manera similar al INNER JOIN indicando unacondición de combinación pero en el resultado se añaden filas que no cumplen la condición decombinación.

Sintaxis

FROM

<tabla_origen> {LEFT|RIGHT|FULL} [OUTER] JOIN <tabla_origen>

ON <condicion_combi>

La palabra OUTER es opcional y no añade ninguna función.Las palabras LEFT, RIGHT y FULL indican la tabla de la cual se van a añadir las filas sincorrespondencia.

SELECT numemp,nombre,empleados.oficina, ciudad

FROM empleados LEFT JOIN oficinas


numemp nombre oficina ciudad101 Antonio Viguer 12 Alicante102 Alvaro Jaumes 21 Badajoz103 Juan Rovira 12 Alicante104 José González 12 Alicante105 Vicente Pantalla 13 Castellón106 Luis Antonio 11 Valencia107 Jorge Gutiérrez 22 A Coruña108 Ana Bustamante 21 Badajoz109 María Sunta 11 Valencia

Power Builder + Sql


110 Juan Victor NULL NULL

Ahora sí aparece el empleado 110 que no tiene oficina

Obtiene los empleados con su oficina y los empleados (tabla a la izquierda LEFT del JOIN)que no tienen oficina aparecerán también en el resultado con los campos de la tabla oficinasrellenados a NULL.

SELECT numemp,nombre,empleados.oficina, ciudad, oficinas.oficina

FROM empleados RIGHT JOIN oficinas


numemp nombre oficina ciudad oficina106 Luis Antonio 11 Valencia 11109 María Sunta 11 Valencia 11101 Antonio Viguer 12 Alicante 12103 Juan Rovira 12 Alicante 12104 José González 12 Alicante 12105 Vicente Pantalla 13 Castellón 13102 Alvaro Jaumes 21 Badajoz 21108 Ana Bustamante 21 Badajoz 21107 Jorge Gutiérrez 22 A Coruña 22NULL NULL NULL Madrid 23NULL NULL NULL Aranjuez 24NULL NULL NULL Pamplona 26NULL NULL NULL Valencia 28

Las oficinas 23,24,26 y 28 no tienen empleados.

Obtiene los empleados con su oficina y las oficinas (tabla a la derecha RIGHT del JOIN) queno tienen empleados aparecerán también en el resultado con los campos de la tabla empleadosrellenados a NULL.


FROM empleados FULL JOIN oficinas


Power Builder + Sql


numemp nombre oficina ciudad oficina101 Antonio Viguer 12 Alicante 12102 Alvaro Jaumes 21 Badajoz 21103 Juan Rovira 12 Alicante 12104 José González 12 Alicante 12105 Vicente Pantalla 13 Castellón 13106 Luis Antonio 11 Valencia 11107 Jorge Gutiérrez 22 A Coruña 22108 Ana Bustamante 21 Badajoz 21109 María Sunta 11 Valencia 11110 Juan Victor NULL NULL NULLNULL NULL NULL Madrid 23NULL NULL NULL Aranjuez 24NULL NULL NULL Pamplona 26NULL NULL NULL Valencia 28

Aparecen tanto los empleados sin oficina como las oficinas sin empleados.


FROM empleados FULL OUTER JOIN oficinas


Es equivalente, la palabra OUTER como hemos dicho no añade ninguna funcionalidad y seutiliza si se quiere por cuestiones de estilo.

NOTA: Cuando necesitamos obtener filas con datos de dos tablas con una condición decombinación utilizaremos un JOIN, os aconsejo empezar por escribir el JOIN con la condiciónque sea necesaria para combinar las filas, y luego plantearos si la composición debe de serinterna o externa. Para este segundo paso ésta sería la norma a seguir:

Empezamos con INNER JOIN.

Si pueden haber filas de la primera tabla que no estén relacionadas con filas de lasegunda tabla y nos interesa que salgan en el resultado, entonces cambiamos a LEFTJOIN.

Si pueden haber filas de la segunda tabla que no estén relacionadas con filas de laprimera tabla y nos interesa que salgan en el resultado, entonces cambiamos a RIGHTJOIN.

Si necesitamos LEFT y RIGHT entonces utilizamos FULL JOIN.

Power Builder + Sql


Siguiendo el ejemplo anterior nos preguntaríamos:

¿Pueden haber empleados que no tengan oficina y nos interesan?, si es que sí, necesitamosun LEFT JOIN.

Seguiríamos preguntando:

¿Pueden haber oficinas que no tengan empleados y nos interesan?, si es que sí,necesitamos un RIGHT JOIN.

Si al final necesitamos LEFT y también RIGHT entonces utilizamos FULL JOIN.

4.9. Combinar varias operaciones

En las operaciones anteriores tabla_origen puede ser a su vez una composición de tablas,en este caso aunque sólo sea obligatorio cuando queramos cambiar el orden de ejecución delas composiciones, es recomendable utilizar paréntesis para delimitar las composiciones.

Por ejemplo:

SELECT numemp, nombre, empleados.oficina, ciudad, oficinas.oficina,pedidos.*

FROM (oficinas RIGHT JOIN empleados

ON empleados.oficina = oficinas.oficina)

INNER JOIN pedidos on rep=numemp;

O bien:

SELECT numemp, nombre, empleados.oficina, ciudad, oficinas.oficina,pedidos.*

FROM oficinas RIGHT JOIN (empleados INNER JOIN pedidos on rep = numemp)

ON empleados.oficina = oficinas.oficina);

Power Builder + Sql


Consultas de Resumen

5.1. Introducción

Una de las funcionalidades de la sentencia SELECT es el permitir obtener resúmenes de losdatos contenidos en las columnas de las tablas.

Para poder llevarlo a cabo la sentencia SELECT consta de una serie de cláusulasespecíficas (GROUP BY, HAVING), y Transact-SQL tiene definidas unas funciones para poderrealizar estos cálculos, las funciones de agregado (también llamadas funciones de columna).

La diferencia entre una consulta de resumen y una consulta de las que hemos visto hastaahora es que en las consultas normales las filas del resultado se obtienen directamente de lasfilas del origen de datos y cada dato que aparece en el resultado tiene su dato correspondienteen el origen de la consulta mientras que las filas generadas por las consultas de resumen norepresentan datos del origen sino un total calculado sobre estos datos. Esta diferencia hará quelas consultas de resumen tengan algunas limitaciones que veremos a lo largo del tema.

Un ejemplo sería:

A la izquierda tenemos una consulta simple que nos saca las oficinas con sus ventasordenadas por región, y a la derecha una consulta de resumen que obtiene la suma de lasventas de las oficinas de cada región

5.2. Las funciones de agregado

Una función de agregado SQL acepta un grupo de datos (normalmente una columna dedatos) como argumento, y produce un único dato que resume el grupo. Por ejemplo la funciónAVG() acepta una columna de datos numéricos y devuelve la media aritmética (average) de losvalores contenidos en la columna.

Power Builder + Sql


El mero hecho de utilizar una función de agregado en una consulta, convierte ésta en unaconsulta de resumen.

Todas las funciones de agregado tienen una estructura muy parecida:

Función ([ALL|DISTINCT] expression)

El grupo de valores sobre el que actúa la función lo determina el resultado de la expresiónque será un nombre de columna o una expresión basada en una columna o varias del origen dedatos. En la expresión nunca puede aparecer una función de agregado ni una subconsulta.

La palabra ALL indica que se tiene que tomar en cuenta todos los valores de la columna. Esl valor por defecto.

La palabra DISTINCT hace que se consideren todas las repeticiones del mismo valor comouno sólo (considera valores distintos).

Todas las funciones de agregado se aplican a las filas del origen de datos una vez ejecutadala cláusula WHERE (si la hubiera).

Si exceptuamos la función COUNT, todas las funciones de agregado ignoran los valoresNULL.

Una función de agregado puede aparecer en la lista de selección en cualquier lugar en elque puede aparecer un nombre de columna. Puede, por ejemplo, formar parte de una expresiónpero no se pueden anidar funciones de agregado.

Tampoco se pueden mezclar funciones de columna con nombres de columna ordinarios.Hay excepciones a esta regla pero cuando definimos agrupaciones y subconsultas queveremos más adelante.

5.3. La función COUNT

COUNT ({[ALL|DISTINCT] expresion | * } )

Expresion puede ser de cualquier tipo excepto text, image o ntext. No se permite utilizarfunciones de agregado ni subconsultas. El tipo de dato devuelto es int.Si el número de valores devueltos por expresion es superior a 231-1, COUNT genera un error,en ese caso hay que utilizar la función COUNT_BIG.

La función cuenta los valores distintos de NULL que hay en la columna. La palabra ALLindica que se tienen que tomar todos los valores de la columna, mientras que DISTINCT haceque se consideren todas las repeticiones del mismo valor como uno solo. Estos parámetros sonopcionales, por defecto se considera ALL.

Por ejemplo:

Power Builder + Sql


SELECT COUNT(region) FROM oficinas;

Devuelve 9 porque tenemos nueve valores no nulos en la columna region. A la hora deinterpretar un COUNT es conveniente no olvidar que cuenta valores no nulos, por ejemplo siinterpretáramos la sentencia tal cual se lee, “cuántas regiones tenemos en oficinas” seríaerróneo, realmente estamos obteniendo cuántas oficinas tienen una región asignada.

SELECT COUNT(DISTINCT region) FROM oficinas;

Devuelve 4 porque tenemos cuatro valores distintos, no nulos, en la columna región, losvalores repetidos los considera sólo una vez. Ahora sí nos devuelve cuántas regiones tenemosen oficinas.

Si utilizamos * en vez de expresión, devuelve el número de filas del origen que nos quedandespués de ejecutar la cláusula WHERE.

COUNT(*) no acepta parámetros y no se puede utilizar con DISTINCT. COUNT(*) norequiere un parámetro expression porque, por definición, no utiliza información sobre ningunacolumna específica. En el recuento se incluyen las filas que contienen valores NULL.

SELECT COUNT(*) FROM empleados WHERE oficina=12;

Obtiene el número de empleados asignados a la oficina 12.

Si tenemos un COUNT(columna) y columna no contiene valores nulos, se obtiene el mismoresultado que COUNT(*) pero el COUNT(*) es más rápido por lo que en este caso hay queutilizarlo en vez de COUNT(columna).

Por ejemplo:

SELECT COUNT(*) FROM empleados WHERE oficina IS NOT NULL;

Es mejor que:

SELECT COUNT(oficina) FROM empleados WHERE oficina IS NOT NULL;

Las dos nos devuelven el número de empleados que tienen una oficina asignada pero laprimera es mejor porque se calcula más rápidamente.

Power Builder + Sql


5.4. La función COUNT_BIG

Funciona igual que la función COUNT. La única diferencia entre ambas funciones está en losvalores devueltos, COUNT_BIG siempre devuelve un valor de tipo bigint y por lo tanto admitemás valores de entrada, no está limitado a 231-1 valores de entrada como COUNT.

5.5. La función MAX

MAX ([ALL|DISTINCT] expression)

Devuelve el valor máximo de la expresión sin considerar los nulos.

MAX se puede usar con columnas numéricas, de caracteres y de datetime, pero no concolumnas de bit. No se permiten funciones de agregado ni subconsultas.

Utilizar DISTINCT no tiene ningún sentido con MAX (el valor máximo será el mismo siconsideramos las repeticiones o no) y sólo se incluye para la compatibilidad con SQL-92.

Por ejemplo:

SELECT SUM(ventas) AS VentasTotales, MAX(objetivo) AS MayorObjetivo

FROM oficinas;

Devuelve 9 porque tenemos nueve valores no nulos en la columna region. A la hora deinterpretar un COUNT es conveniente no olvidar que cuenta valores no nulos, por ejemplo siinterpretáramos la sentencia tal cual se lee, “cuántas regiones tenemos en oficinas” seríaerróneo, realmente estamos obteniendo cuántas oficinas tienen una región asignada.

5.6. La función MIN

MIN ([ALL|DISTINCT] expression)

Devuelve el valor mínimo de la expresión sin considerar los nulos.

MIN se puede usar con columnas numéricas, de caracteres y de datetime, pero no concolumnas de bit. No se permiten funciones de agregado ni subconsultas.

Utilizar DISTINCT no tiene ningún sentido con MIN (el valor mínimo será el mismo siconsideramos las repeticiones o no) y sólo se incluye para la compatibilidad con SQL-92.

Power Builder + Sql


5.7. La función SUM

SUM ([ALL|DISTINCT] expresion )

Devuelve la suma de los valores devueltos por la expresión.

Sólo puede utilizarse con columnas numéricas.

El resultado será del mismo tipo aunque puede tener una precisión mayor.

SELECT SUM(importe) FROM pedidos;

Obtiene el importe total vendido en todos los pedidos.

SELECT SUM(ventas) AS VentasTotales, MAX(objetivo) AS MayorObjetivo

FROM oficinas;

Devuelve la suma de las ventas de todas las oficinas y de los objetivos de todas las oficinas,el de mayor importe.

5.8. La función AVG

AVG ([ALL|DISTINCT] expresion )

Devuelve el promedio de los valores de un grupo, para calcular el promedio se omiten losvalores nulos.

El grupo de valores lo determina el resultado de la expresión que será un nombre decolumna o una expresión basada en una columna o varias del origen de datos.

La función se aplica también a campos numéricos, y en este caso el tipo de dato delresultado puede cambiar según las necesidades del sistema para representar el valor delresultado.

5.9. La función VAR

VAR ([ALL|DISTINCT] expresion )

Power Builder + Sql


Devuelve la varianza estadística de todos los valores de la expresión especificada.VAR sólo se puede utilizar con columnas numéricas. Los valores NULL se pasan por alto.

5.10. La función VARP

VARP ([ALL|DISTINCT] expresion )

Devuelve la varianza estadística de la población para todos los valores de la expresiónespecificada.Sólo se puede utilizar con columnas numéricas. Los valores NULL se pasan por alto.

5.11. La función STDEV

STDEV ([ALL|DISTINCT] expresion )

Devuelve la desviación típica estadística de todos los valores de la expresión especificada.Sólo se puede utilizar con columnas numéricas. Los valores NULL se pasan por alto.

5.12. La función STDEVP

STDEVP ([ALL|DISTINCT] expresion )

Devuelve la desviación estadística estándar para la población de todos los valores de laexpresión especificada.Sólo se puede utilizar con columnas numéricas. Los valores NULL se pasan por alto.

5.13. La función GROUPING

GROUPING (nb_columna)

Es una función de agregado que genera como salida una columna adicional con el valor 1 sila fila se agrega mediante el operador CUBE o ROLLUP, o el valor 0 cuando la fila no es elresultado de CUBE o ROLLUP.

Nb_columna tiene que ser una de las columnas de agrupación y la cláusula GROUP BYdebe contener el operador CUBE o ROLLUP.

En el siguiente punto, cuando veamos las cláusulas CUBE y ROLLUP quedará más claro.

Power Builder + Sql


5.14. Agrupamiento de filas (cláusula GROUP BY).

Hasta ahora las consultas sumarias que hemos visto obtienen totales de todas las filas delorigen y producen una única fila de resultado.

Muchas veces cuando calculamos resúmenes nos interesan totales parciales, por ejemplosaber de cada empleado cuánto ha vendido, y cuál ha sido su pedido máximo, de cada clientecuándo fue la última vez que nos compró, etc.

En todos estos casos en vez de obtener una fila única de resultados necesitamos una filapor cada empleado, cliente, etc.

Podemos obtener estos subtotales con la cláusula GROUP BY.

GROUP BY [ ALL ] expresion_agrupacion [ ,...n ]

[ WITH { CUBE | ROLLUP } ]

Una consulta con una cláusula GROUP BY agrupa los datos de la tabla origen y produceuna única fila resultado por cada grupo formado. Las columnas indicadas en el GROUP BY sellaman columnas de agrupación o agrupamiento .

Cuando queremos realizar una agrupación múltiple, por varias columnas, éstas se indican enla cláusula GROUP BY en el orden de mayor a menor agrupación igual que con la cláusulaORDER BY.

expresion_agrupacion puede ser una columna o una expresión no agregada que hagareferencia a una columna devuelta por la cláusula FROM. Un alias de columna que estédefinido en la lista de selección no puede utilizarse para especificar una columna deagrupamiento.No se pueden utilizar columnas de tipo text, ntext e image en expresion_agrupacion.

En las cláusulas GROUP BY que no contengan CUBE o ROLLUP, el número de columnasde agrupación está limitado por los tamaños de columna de GROUP BY, las columnas deagregado y los valores de agregado que participan en la consulta. Este límite procede del límitede 8.060 bytes de la tabla de trabajo intermedia que se necesita para contener los resultadosintermedios de la consulta. Se permite un máximo de 10 expresiones de agrupamiento cuandose especifica CUBE o ROLLUP.

Si en la columna de agrupación existen valores nulos, se generará una fila de resumen paraeste “valor”, en este caso se considera el valor nulo como otro valor cualquiera.

Ejemplo:

SELECT oficina, count(numemp) AS [Número de empleados]

FROM empleados

Power Builder + Sql


GROUP BY oficina;

Resultado:

oficina Número de empleados

NULL 2

11 2

12 3

13 1

21 2

22 1

Hay empleados sin oficinas (con oficina a nulo), estos forman un grupo con el valor NULL enoficina, en este caso hay dos empleados así.

Podemos indicar varias columnas de agrupación.

Ejemplo:

SELECT rep, clie, count(numpedido) AS [Número de pedidos], MAX(importe)AS [Importe máximo]

FROM pedidos

WHERE YEAR(fechapedido) = 1997

GROUP BY rep, clie

ORDER BY rep, clie;

Resultado:

rep clie Númerode pedidos

Importemáximo

101 2113 1 225,00

102 2106 2 21,30

102 2120 1 37,50

Power Builder + Sql


103 2111 2 21,00

105 2103 4 275,00

105 2111 1 37,45

106 2101 1 14,58

107 2109 1 313,50

107 2124 2 24,30

108 2112 1 29,25

108 2114 1 71,00

108 2118 3 14,20

De cada representante obtenemos el número de pedidos y el importe máximo vendido acada cliente, de las ventas de 1997. La cláusula ORDER BY se ha incluido para que las filasaparezcan ordenadas y quede más claro.

Hemos dicho que los resúmenes se calculan sobre todas las filas del origen después dehaber ejecutado el WHERE, pues ALL permite obtener un resumen de las filas que no cumplenel WHERE.

ALL Incluye todos los grupos y conjuntos de resultados, incluso aquellos en los que no hayfilas que cumplan la condición de búsqueda especificada en la cláusula WHERE. Cuando seespecifica ALL, se devuelven valores NULL para las columnas de resumen de los grupos queno cumplen la condición de búsqueda. No se puede especificar ALL con los operadores CUBEy ROLLUP.

GROUP BY ALL no se admite en consultas que tienen acceso a tablas remotas si tambiénhay una cláusula WHERE en la consulta.

Por ejemplo, vamos a modificar la consulta anterior:


FROM pedidos


GROUP BY ALL rep, clie

ORDER BY rep, clie;

Resultado:

Power Builder + Sql



Importemáximo

101 2102 0 NULL101 2108 0 NULL101 2113 1 225,00102 2106 2 21,30102 2120 1 37,50103 2111 2 21,00105 2103 4 275,00105 2111 1 37,45106 2101 1 14,58106 2117 0 NULL107 2109 1 313,50107 2124 2 24,30108 2112 1 29,25108 2114 1 71,00108 2118 3 14,20

Cuál ha sido el efecto de añadir ALL? Se han añadido filas para las filas del origen que nocumplen la condición del WHERE pero sin que intervengan en el cálculo de las funciones deagregado.Por ejemplo el representante 101 tiene pedidos con el cliente 2102 pero estos pedidos no sondel año 1997, por eso aparece la primera fila (no estaba en el resultado de la otra consulta)pero con 0 y NULL como resultados de las funciones de agregado.

ROLLUP especifica que, además de las filas que normalmente proporciona GROUP BY, seincluyen filas de resumen en el conjunto de resultados. Los grupos se resumen en un ordenjerárquico, desde el nivel inferior del grupo hasta el superior. La jerarquía del grupo sedetermina por el orden en que se especifican las columnas de agrupamiento. Cambiar el ordende las columnas de agrupamiento puede afectar al número de filas generadas en el conjunto deresultados.Por ejemplo:


FROM pedidos


GROUP BY rep, clie WITH ROLLUP;

Resultado:

Power Builder + Sql



Importemáximo

101 2113 1 225,00101 NULL 1 225,00102 2106 1 21,30102 2120 1 37,50102 NULL 3 37,50103 2111 2 21,00103 NULL 2 21,00105 2103 4 275,00105 2111 1 37,45105 NULL 5 275,00106 2101 1 14,28106 NULL 1 14,28107 2109 1 313,50107 2124 2 24,30107 NULL 3 313,50108 2112 1 29,25108 2114 1 71,00108 2118 3 14,20108 NULL 5 71,00... ... ... ...NULL NULL 23 450,00

Efecto: Se han añadido automáticamente subtotales por cada nivel de agrupamiento y unalínea de totales generales al final. En este caso no hemos incluido ORDER BY porque las filassalen ya ordenadas.

CUBE especifica que, además de las filas que normalmente proporciona GROUP BY, debenincluirse filas de resumen en el conjunto de resultados. Se devuelve una fila de resumenGROUP BY por cada posible combinación de grupo y subgrupo del conjunto de resultados. Enel resultado se muestra una fila de resumen GROUP BY como NULL, pero se utiliza paraindicar todos los valores.

Por ejemplo:


FROM pedidos


Power Builder + Sql


GROUP BY rep, clie WITH CUBE;

Resultado:


Importemáximo

101 2113 1 225,00101 NULL 1 225,00102 2106 1 21,30102 2120 1 37,50102 NULL 3 37,50103 2111 2 21,00103 NULL 2 21,00105 2103 4 275,00105 2111 1 37,45105 NULL 5 275,00106 2101 1 14,28106 NULL 1 14,28107 2109 1 313,50107 2124 2 24,30107 NULL 3 313,50108 2112 1 29,25108 2114 1 71,00108 2118 3 14,20108 NULL 5 71,00... ... ... ...NULL NULL 23 450,00NULL 2101 1 14,58NULL 2103 4 275,00NULL 2106 2 21,30NULL 2107 1 6,32NULL 2108 1 56,25NULL 2109 1 313,50NULL 2111 3 37,45NULL 2112 2 450,00NULL 2113 1 225,00NULL 2114 1 71,00

Power Builder + Sql


NULL 2118 3 14,20NULL 2120 1 37,50NULL 2124 2 24,30

Efecto: Obtenemos además de los resultados obtenidos con ROLLUP (los totales por cadarepresentante), los totales por el otro criterio (los totales por cada cliente).El número de filas de resumen del conjunto de resultados se determina mediante el número decolumnas que contiene la cláusula GROUP BY. Cada operando (columna) de la cláusulaGROUP BY se enlaza según el agrupamiento NULL y se aplica el agrupamiento al resto de losoperandos (columnas). CUBE devuelve todas las combinaciones posibles de grupo y subgrupo.

Tanto si utilizamos CUBE como ROLLUP, nos será útil la función de agregado GROUPING.

Si cogemos por ejemplo la primera fila remarcada (101 NULL …) el valor NULL, no sabemossi se refiere a una fila de subtotal o a que el representante 101 ha realizado un pedido sinnúmero de cliente. Para poder salvar este problema se utiliza la función de agregadoGROUPING.

SELECT rep, clie, count(numpedido) AS [Número de pedidos], MAX(importe)AS [Importe máximo], GROUPING(clie) AS [Fila resumen]

FROM pedidos


GROUP BY rep, clie WITH ROLLUP;


Importemáximo

FilaResumen

101 2113 1 225,00 0101 NULL 1 225,00 1102 2106 2 21,30 0102 2120 1 37,50 0102 NULL 3 37,50 1103 2111 2 21,00 0

Las filas que corresponden a subtotales aparecen con un 1 y las normales con un cero.

Ahora que estamos más familiarizados con las columnas de agrupamiento debemoscomentar una regla a no olvidar:

EN LA LISTA DE SELECCIÓN DE UNA CONSULTA DE RESUMEN UN NOMBRE DECOLUMNA NO PUEDE APARECER FUERA DE UNA FUNCIÓN DE AGREGADO SI NO ESUNA COLUMNA DE AGRUPACIÓN.

Power Builder + Sql


5.15. Selección sobre grupos de filas, la cláusula HAVING

Cuando queremos incluir una cláusula de selección sobre las filas del origen, utilizamos lacláusula WHERE, pero cuando estamos definiendo una consulta de resumen, no podemosutilizar esta cláusula para seleccionar filas del resultado ya que cada una de éstas representaun grupo de filas de la tabla original. Para seleccionar filas del resumen tenemos la cláusulaHAVING.

HAVING condición de búsqueda

HAVING funciona igual que la cláusula WHERE pero en vez de actuar sobre las filas delorigen de datos, actúa sobre las filas del resultado, selecciona grupos de filas por lo que lacondición de búsqueda sufrirá alguna limitación, la misma que para la lista de selección:Ejemplo:

SELECT oficina, count(numemp) AS [Número de empleados]

FROM empleados

GROUP BY oficina

HAVING COUNT(numemp)<2;

Resultado:

oficina Número de empleados

13 1

22 1

Esta SELECT es la misma que la del primer ejemplo del apartado sobre la cláusula GROUPBY, la diferencia es que le hemos añadido la cláusula HAVING, que hace que del resultado sólose visualicen los grupos que cumplan la condición. Es decir sólo aparecen las oficinas quetienen menos de 2 empleados.Siempre que en una condición de selección haya una función de columna, la condición deberáincluirse en la cláusula HAVING, además, como HAVING filtra filas del resultado, sólo puedecontener expresiones (nombres de columnas, expresiones, funciones…) que también puedenaparecer en la lista de selección, por lo que también se aplica la misma regla a no olvidar:

EN LA CLÁUSULA HAVING UN NOMBRE DE COLUMNA NO PUEDE APARECERFUERA DE UNA FUNCIÓN DE AGREGADO SI NO ES UNA COLUMNA DE AGRUPACIÓN.

Las expresiones que pongamos en HAVING no tienen porqué aparecer en la lista deselección, por ejemplo en la SELECT anterior se podía haber escrito:

Power Builder + Sql


HAVING SUM(ventas)=10000

Las Sub consultas

6.1. Introducción

Una subconsulta es una consulta que aparece dentro de otra consulta o subconsultas, en lalista de selección o en la cláusula WHERE o HAVING, originalmente no se podían incluir en lalista de selección.Una subconsulta se denomina también consulta o selección interna, mientras que la instrucciónque contiene la subconsulta es conocida como consulta o selección externa.

Aparece siempre encerrada entre paréntesis y tiene la misma sintaxis que una sentenciaSELECT normal con alguna limitación:No puede incluir una cláusula COMPUTE o FOR BROWSE y sólo puede incluir una cláusulaORDER BY cuando se especifica también una cláusula TOP.

Una subconsulta puede anidarse en la cláusula WHERE o HAVING de una instrucciónexterna SELECT, INSERT, UPDATE o DELETE, o bien en otra subconsulta. Se puede disponerde hasta 32 niveles de anidamiento, aunque el límite varía dependiendo de la memoriadisponible y de la complejidad del resto de las expresiones de la consulta. Hay que tener encuenta que para cada fila de la consulta externa, se calcula la subconsulta, si anidamos variasconsultas, el número de veces que se ejecutarán las subconsultas ¡puede dispararse!

Cuando la subconsulta aparece en la lista de selección de otra consulta, deberá devolver unsolo valor, de lo contrario provocará un error.

Ejemplo de subconsulta: Listar los empleados cuya cuota no supere el importe vendido porel empleado.

SELECT nombre

FROM empleados

WHERE cuota <= (SELECT SUM(importe)

FROM pedidos

WHERE rep = numemp);

Por cada fila de la tabla de empleados (de la consulta externa) se calcula la subconsulta y seevalúa la condición, por lo que utilizar una subconsulta puede en algunos casos ‘ralentizar’ laconsulta, en contrapartida se necesita menos memoria que una composición de tablas.

Muchas de las instrucciones Transact-SQL que incluyen subconsultas se pueden formulartambién utilizando composiciones de tablas. Otras preguntas se pueden formular sólo consubconsultas.

Power Builder + Sql


En Transact-SQL, normalmente no hay una regla fija en cuanto a diferencias de rendimientoentre una instrucción que incluya una subconsulta y una versión semánticamente equivalenteque no la incluya.

Podremos utilizar una subconsulta siempre y cuando no se quiera que aparezcan en elresultado columnas de la subconsulta ya que si una tabla aparece en la subconsulta y no en laconsulta externa, las columnas de esa tabla no se pueden incluir en la salida (la lista deselección de la consulta externa).

Tenemos tres tipos de subconsultas:

Las que devuelven un solo valor, aparecen en la lista de selección de la consulta externao con un operador de comparación sin modificar.

Las que generan una columna de valores, aparecen con el operador IN o conun operador de comparación modificado con ANY, SOME o ALL.

Las que pueden generar cualquier número de columnas y filas, son utilizadas en pruebasde existencia especificadas con EXISTS.

A lo largo del tema las estudiaremos todas.

Antes de terminar con la introducción queda comentar el concepto de referencia externa muyútil en las subconsultas.

A menudo, es necesario, dentro del cuerpo de una subconsulta, hacer referencia al valor deuna columna en la fila actual de la consulta externa, el nombre de columna de la consultaexterna dentro de la subconsulta recibe el nombre de referencia externa, ya que hace referenciaa una columna externa.

En el ejemplo anterior numemp es una referencia externa, no es una columna del origen dedatos de la subconsulta (pedidos), es una columna del origen de la consulta externa(empleados).

Hay que tener en cuenta de cómo se ejecuta la consulta; por cada fila de la consulta externase calcula el resultado de la subconsulta y se evalúa la comparación.

En el ejemplo, se coge el primer empleado (numemp= 101, por ejemplo) y se calcula lasubconsulta sustituyendo numemp por el valor 101, se calcula la suma de los pedidos del rep =101, y el resultado se compara con la cuota de ese empleado, y así se repite el proceso contodas las filas de empleados.

El nombre de una columna dentro de la subconsulta se presupone del origen de datos de lasubconsulta y, sólo si no se encuentra en ese origen, la considera como columna externa y labusca en el origen de la consulta externa.

Por ejemplo:

Power Builder + Sql


SELECT oficina, ciudad

FROM oficinas

WHERE objetivo > (SELECT SUM(ventas)

FROM empleados

WHERE oficina = oficina);

La columna oficina se encuentra en los dos orígenes (oficinas y empleados) pero estaconsulta no dará error (no se nos pedirá cualificar los nombres como pasaría en unacomposición de tablas), dentro de la subconsulta se considera oficina el campo de la tablaempleados. Con lo que compararía la oficina del empleado con la misma oficina del empleado yeso no es lo que queremos, queremos comparar la oficina del empleado con la oficina deoficinas, lo escribiremos pues así para forzar a que busque la columna en la tabla oficinas.

SELECT oficina, ciudad

FROM oficinas

WHERE objetivo > (SELECT SUM(ventas)

FROM empleados

WHERE oficina = oficinas.oficina);

6.2. Subconsultas de resultado único

Existen subconsultas que deben obligatoriamente devolver un único valor, son las queaparecen en la lista de selección de la consulta externa o las que aparecen en WHERE oHAVING combinadas con un operador de comparación sin modificar.

Los operadores de comparación sin modificar son los operadores de comparación que vimoscon la cláusula WHERE.

Sintaxis:

<expresion> {=|<>|!=|>|>=|!>|<|<=|!<} <subconsulta>

En este caso la segunda expresión será una subconsulta, con una sola columna en la listade selección y deberá devolver una única fila como mucho.

Ese valor único será el que se compare con el resultado de la primera expresión.

Power Builder + Sql


Si la subconsulta no devuelve ninguna fila, la comparación opera como si la segundaexpresión fuese nula.

Si la subconsulta devuelve más de una fila o más de una columna, da error.

Ejemplo:

SELECT nombre

FROM empleados

WHERE cuota <= (SELECT SUM(importe)

FROM pedidos

WHERE rep = numemp);

La subconsulta devuelve una sola columna y como mucho una fila ya que es una consultade resumen sin cláusula GROUP BY.

6.3. Subconsultas de lista de valores

Otro tipo de subconsultas son las que devuelven una lista de valores en forma de unacolumna y cero, una o varias filas.

Estas consultas aparecen en las cláusulas WHERE o HAVING combinadas con el operadorIN o con comparaciones modificadas.

6.4. El operador IN con subconsulta

<expresion> IN subconsulta

IN examina si el valor de expresion es uno de los valores incluidos en la lista de valoresgenerados por la subconsulta.

La subconsulta tiene que generar valores de un tipo compatible con la expresión.

Ejemplo:

SELECT *

FROM empleados

WHERE oficina IN (SELECT oficina

FROM oficinas

Power Builder + Sql


WHERE region = 'Este');

Por cada empleado se calcula la lista de las oficinas del Este (nº de oficina) y se evalúa si laoficina del empleado está en esta lista. Obtenemos pues los empleados de oficinas del Este.

numemp nombre edad oficina titulo contrato jefe cuota ventas

101 AntonioViguer

45 12 representante 1986-10-20

104 30000,00 30500,00

103 JuanRovira


104 27500,00 28600,00

104 JoséGonzález

33 23 dir ventas 1987-05-19

106 20000,00 14300,00

105 VicentePantalla


104 35000,00 36800,00

106 LuisAntonio

52 11 dir general 1988-06-14

NULL 27500,00 29900,00

Si la subconsulta no devuelve ninguna fila:

SELECT *

FROM empleados

WHERE oficina IN (SELECT oficina

FROM oficinas

WHERE region = 'Otro');

La lista generada está vacía por lo que la condición IN devuelve FALSE y en este caso nosale ningún empleado.

Muchas veces la misma pregunta se puede resolver mediante una composición de tablas.

SELECT empleados.*

FROM Empleados INNER JOIN oficinas ON empleados.oficina =oficinas.oficina

WHERE region = 'Este';

Esta sentencia es equivalente. En el resultado no queremos ver ninguna columna de la tablaoficinas, el JOIN lo tenemos sólo para la pregunta, en este caso pues se puede sustituir por unasubconsulta.

Power Builder + Sql


Si combinamos el operador IN con NOT obtenemos el operador NOT IN.

<expresion> NOT IN subconsulta

Devuelve TRUE si el valor de la expresión no está en la lista de valores devueltos por lasubconsulta.

SELECT *

FROM empleados

WHERE oficina NOT IN (SELECT oficina

FROM oficinas

WHERE region = 'Este');

Devuelve los empleados cuya oficina no esté en la lista generada por la subconsulta, esdecir empleados que trabajan en oficinas que no son del Este.

OJO con NOT IN.

Hay que tener especial cuidado con los valores nulos cuando utilizamos el operador NOT INporque el resultado obtenido no siempre será el deseado por ejemplo:

* En la consulta anterior no salen los empleados que no tienen oficina ya que para esosempleados la columna oficina contiene NULL por lo que no se cumple el NOT IN.

* Si la subconsulta no devuelve ninguna fila, la condición se cumplirá para todas las filas dela consulta externa, en este caso todos los empleados.

* Si la subconsulta devuelve algún valor NULL, la condición NOT IN es NULL lo que nospuede ocasionar algún problema.

Por ejemplo, queremos obtener las oficinas que no están asignadas a ningún empleado.

Power Builder + Sql


SELECT *

FROM Oficinas


FROM empleados);

Esta consulta no devuelve ninguna fila cuando sí debería ya que hay oficinas que nos estánasignadas a ningún empleado. El problema está en que la columna oficina de la tablaempleados admite nulos por lo que la subconsulta devuelve valores nulos en todos losempleados que no están asignados a ninguna oficina. Estos valores nulos hacen que no secumpla el NOT IN. La solución pasa por eliminar estos valores molestos:

SELECT *

FROM Oficinas


FROM empleados

WHERE oficina IS NOT NULL);

En el primer ejemplo no tenemos ese problema porque la columna oficina en oficinas noadmite nulos.

A diferencia de IN, NOT IN no siempre puede resolverse con una composición:

SELECT numemp AS [IN]

FROM empleados

WHERE numemp IN (SELECT rep

FROM pedidos

WHERE fab = 'ACI');

Se puede resolver con una composición:

SELECT DISTINCT empleados.numemp AS [=]

FROM Empleados INNER JOIN pedidos ON numemp = rep

WHERE fab = 'ACI';

Power Builder + Sql


En este caso, como un empleado puede tener varios pedidos hay que añadir DISTINCT paraeliminar las repeticiones de empleados (si un empleado tiene varios pedidos de ACI apareceríavarias veces).

Sin embargo esta sentencia con NOT IN, queremos los empleados que no tienen pedidos deACI:

SELECT numemp AS [NOT IN]

FROM empleados

WHERE numemp NOT IN (SELECT rep

FROM pedidos

WHERE fab = 'ACI');

No se puede resolver con una composición:

SELECT DISTINCT empleados.numemp AS [<>]

FROM Empleados INNER JOIN pedidos ON numemp = rep

WHERE fab <> 'ACI';

Esta consulta devuelve los empleados que tienen pedidos que no son de ACI, pero unempleado puede tener pedidos de ACI y otros de otros fabricantes y por estos otros saldría enel resultado cuando sí tiene pedidos de ACI y no debería salir.Hay que tener mucho cuidado con este tipo de preguntas.

6.5. La comparación modificada (ANY, ALL)

Los operadores de comparación que presentan una subconsulta se pueden modificarmediante las palabras clave ALL, ANY o SOME. SOME es un equivalente del estándar de SQL-92 de ANY.

Se utiliza este tipo de comparación cuando queremos comparar el resultado de la expresióncon una lista de valores y actuar en función del modificador empleado.

El test ANY

<expresion> {=|<>|!=|>|>=|!>|<|<=|!<} {ANY|SOME} subconsulta

Power Builder + Sql


ANY significa que, para que una fila de la consulta externa satisfaga la condiciónespecificada, la comparación se debe cumplir para al menos un valor de los devueltos por lasubconsulta.

Por cada fila de la consulta externa se evalúa la comparación con cada uno de los valoresdevueltos por la subconsulta y si la comparación es True para alguno de los valores ANY esverdadero, si la comparación no se cumple con ninguno de los valores de la consulta, ANY daFalse a no ser que todos los valores devueltos por la subconsulta sean nulos en tal caso ANYdará NULL.Si la subconsulta no devuelve filas ANY da False incluso si expresion es nula.

Ejemplo:

SELECT *

FROM empleados

WHERE cuota > ANY (SELECT cuota

FROM empleados empleados2

WHERE empleados.oficina =empleados2.oficina);

Obtenemos los empleados que tienen una cuota superior a la cuota de alguno de suscompañeros de oficina, es decir los empleados que no tengan la menor cuota de su oficina.

En este caso hemos tenido un alias de tabla en la subconsulta (empleados2) para poderutilizar una referencia externa.

El test ALL

<expresion> {=|<>|!=|>|>=|!>|<|<=|!<} ALL subconsulta

Con el modificador ALL, para que se cumpla la condición, la comparación se debe cumplircon cada uno de los valores devueltos por la subconsulta.

Si la subconsulta no devuelve ninguna fila ALL da True.

SELECT *

FROM empleados

WHERE cuota > ALL (SELECT cuota

FROM empleados empleados2

Power Builder + Sql


WHERE empleados.oficina =empleados2.oficina);

En el ejemplo anterior obtenemos los empleados que tengan una cuota superior a todas lascuotas de la oficina del empleado. Podríamos pensar que obtenemos el empleado de mayorcuota de su oficina pero no lo es, aquí tenemos un problema, la cuota del empleado aparece enel resultado de subconsulta por lo tanto > no se cumplirá para todos los valores y sólo saldránlos empleados que no tengan oficina (para los que la subconsulta no devuelve filas).

Para salvar el problema tendríamos que quitar del resultado de la subconsulta la cuota delempleado modificando el WHERE:

WHERE empleados.oficina = empleados2.oficina

AND empleados.numemp <> empleados2.numemp);

De esta forma saldrían los empleados que tienen una cuota mayor que cualquier otroempleado de su misma oficina.

O bien

WHERE empleados.oficina = empleados2.oficina

AND empleados.cuota <> empleados2.cuota);

Para no considerar los empleados que tengan la misma cuota que el empleado. En estecaso saldrían los empleados con la mayor cuota de sus oficina, pero si dos empleados tienen lamisma cuota superior, saldrían, hecho que no sucedería con la otra versión.

Cuando la comparación es una igualdad, = ANY es equivalente a IN y <> ALL es equivalentea NOT IN (con los mismos problemas).

6.6. Subconsultas con cualquier número de columnas (EXISTS)

Existe otro operador de subconsulta con el que la subconsulta puede devolver más de unacolumna, el operador EXISTS.En este caso la sintaxis es algo diferente:

WHERE [NOT] EXISTS subconsulta

Power Builder + Sql


No se realiza ninguna comparación con los valores devueltos por la subconsulta,simplemente se evalúa si la subconsulta devuelve alguna fila, en este caso EXISTS será True ysi la subconsulta no devuelve ninguna fila, EXISTS será False.

Ejemplo:

SELECT *

FROM empleados

WHERE EXISTS (SELECT *

FROM pedidos

WHERE numemp = rep and fab ='ACI');

Obtenemos los empleados que tengan un pedido del fabricante ACI. Por cada empleado, secalcula la subconsulta (obteniendo los pedidos de ese empleado y con fabricante ACI), si existealguna fila, el empleado sale en el resultado, si no, no sale.

Cuando se utiliza el operador EXISTS es muy importante añadir una referencia externa, noes obligatorio pero en la mayoría de los casos será necesario. Veámoslo con ese mismoejemplo, si quitamos la referencia externa:

SELECT *

FROM empleados

WHERE EXISTS (SELECT *

FROM pedidos

WHERE fab ='ACI');

Sea el empleado que sea, la subconsulta siempre devolverá filas (si existe algún pedidocuyo fabricante sea ACI) o nunca, indistintamente del empleado que sea, por lo que seobtendrán todos los empleados o ninguno para que el resultado varíe según las filas de laconsulta externa habrá que incluir una referencia externa.

Otra cosa a tener en cuenta es que la lista de selección de una subconsulta que seespecifica con EXISTS casi siempre consta de un asterisco (*). No hay razón para enumerar losnombres de las columnas porque no se van a utilizar y supone un trabajo extra para el sistema.

Si utilizamos NOT EXISTS el resultado será el contrario.

SELECT *

FROM empleados

Power Builder + Sql


WHERE NOT EXISTS (SELECT *

FROM pedidos

WHERE fab ='ACI' AND rep=numemp);

Devuelve los empleados que no tienen ningún pedido de ACI.

power builder + sql consultas simples [ · pdf filepower builder + sql lic. vladimir...

Documents