DML. Consultas

Manua Manuall de SQL Server 2000

C onsultas.- SELECT ECT

LENGUAJE DE MANIPULACION DE DATOS CONSULTAS .- SELECT INTRODUCCIÓN Esta sentencia forma parte del DML (lenguaje de manipulación de datos), en este tema veremos cómo seleccionar columnas de una tabla, cómo seleccionar filas y cómo obtener las filas ordenadas por el criterio que queramos. SQL Server proporciona a través de Transact SQL una forma fácil de poder consultar datos desde una base de datos. El acrónimo SQL significa: Lenguaje de consulta estructurado (Structured Query Language). Utilizaremos en adelante la base de datos Neptuno como Neptuno como base de datos ejemplo. El resultado de la consulta es una tabla lógica, porque no se guarda en el disco sino que está en memoria y cada vez que ejecutamos la consulta se vuelve a calcular. Cuando ejecutamos la consulta se visualiza el resultado en forma de tabla con columnas y filas, pues en la SELECT tenemos que indicar qué columnas queremos que tenga el resultado y qué filas queremos seleccionar de la tabla origen. Básicamente, una fundamentales:

consulta

SQL

se

constituye

de

las

siguientes

partes

SELECT [ALL] [DISTINCT] [TOP N] FROM [WHERE] [GROUP BY] [HAVING] [ORDER BY] La lista de columnas que queremos que aparezcan en el resultado es lo que llamamos lista de selección (columnas que se desean obtener) y se especifica después de la cláusula SELECT. Se utiliza el asterisco * en para recuperar todas las columnas de la tabla. tabla. Tiene dos ventajas ventajas:: Evitar nombrar las columnas una a una (es más corto). Si añadimos una columna nueva en la tabla, esta nueva columna saldrá sin tener que modificar la consulta. Se puede combinar el * con el nombre de una tabla (Ej. Productos.*), pero esto se utiliza más cuando el origen de la l a consulta son dos tablas. Las columnas se pueden especificar mediante su nombre simple simple (Nombre de Columna) o su nombre cualificado (NombreTabla.NombreColum cualificado (NombreTabla.NombreColumna, na, el nombre de la Ing. Segundo J osé Castillo astillo Zumarán

1



columna precedido del nombre de la tabla que contiene la columna y separados por un punto). El nombre cualificado se puede emplear siempre que queramos y es obligatorio en algunos casos que veremos más adelante. Cuando el nombre nombre de la columna columna o de la tabla contiene espacios en blanco, blanco, hay que poner el nombre entre corchetes [] corchetes [] y además el número de espacios en blanco debe coincidir. Por ejemplo [codigo de cliente] no es lo mismo que [ codigo de cliente] (el segundo lleva un espacio en blanco delante de código) Con la cláusula FROM FROM indicamos indicamos en qué tabla tabla tiene que buscar la información. información. En este capítulo de consultas simples el resultado se obtiene de una única tabla. La cláusula WHERE WHERE selecciona únicamente las filas filas que cumplan la condición de selección especificada. selección especificada. En la consulta sólo aparecerán las filas para las cuales la condición es verdadera (TRUE), los valores nulos (NULL) no se incluyen por lo tanto en las filas del resultado. La condición de selección selección puede ser cualquier condición válida o combinación de condiciones utilizando condiciones utilizando los operadores NOT (no) AND AND (y) (y) y OR OR (ó). (ó). Condiciones de selección Las condiciones de selección son las condiciones que pueden aparecer en la cláusula WHERE. En SQL tenemos cinco condiciones básicas: El Test Test de comparación El Test Test de rango El Test Test de pertenencia a un conjunto El Test Test de valor nulo El Test Test de correspondencia con patrón . patrón . El Test Test de comparació comparación n. Compara el valor de La sintaxis es la siguiente:

= <> < <= > >=

una

expresión

con

el

valor

de

otra.

igual que distinto de menor que menor o igual mayor que mayor o igual

Ing. Segundo J osé Castillo astillo Zumarán

2



Test de rango ( rango (BETWEEN BETWEEN). ). Examina si el valor valor de la expresión está comprendido entre entre los dos valores definidos por exp1 y exp2. Tiene la siguiente sintaxis:

Test de Test de pertenencia a conjunto ( conjunto (IN IN)) Examina si el valor valor de de la expresión es uno de los valores incluidos en la lista de valores.. valores Tiene la siguiente sintaxis:

Test de valor nulo ( nulo (IS IS NULL) NULL) Una condición de selección puede dar como resultado el valor verdadero TRUE, falso FALSE o nulo NULL. Cuando una columna columna que interviene en una condición de selección contiene el valor nulo, el resultado resultado de de la condición no es verdadero ni falso, sino nulo nulo,, sea cual sea el Test Test que se haya utilizado. Por eso si queremos listar las filas que tienen valor en una determinada columna, no podemos utilizar el Test de comparación. Si queremos preguntar si una columna contiene el valor nulo debemos utilizar un Test Test especial, especial, el Test de valor nulo. Tiene la siguiente sintaxis:

Test de correspondencia con patrón ( patrón (LIKE LIKE)) Se utiliza cuando queremos utilizar caracteres comodines comodines para formar el valor con el comparar. Tiene la siguiente sintaxis:

Los comodines más usados son los siguientes: ? Representa un carácter cualquiera % Representa cero o más caracteres # Representa un dígito cualquiera (0-9) Ing. Segundo J osé Castillo astillo Zumarán

3

Manual de SQL Server 2000

Consultas.- SELECT

Nota.- En las Siguientes consultas, trabajaremos con la Base de Datos Neptuno. Empezaremos por estudiar la sentencia SELECT, que permite recuperar datos de una o varias tablas. La sentencia SELECT es con mucho la más compleja y potente de las sentencias SQL. Empezaremos por ver las consultas más simples, basadas en una sola tabla. Ejemplo: Obtener los nombres y apellidos de los empleados que tienen como jefe al empleado con codigo 2. SELECT idEmpleado, apellidos, nombre FROM Empleados WHERE jefe=2

Cuando se visualiza el resultado de la consulta, normalmente las columnas toman el nombre que tiene la columna en la tabla, si queremos cambiar ese nombre lo podemos hacer definiendo un alias de columna mediante la cláusula AS será el nombre que aparecerá como título de la columna. SELECT idEmpleado as [Código Empleado], apellidos as Apellidos, nombre as Nombres FROM Empleados WHERE jefe=2

En adelante, analizaremos cada una de las cláusulas que pueden incluirse en una consulta SQL con la finalidad de explotar la funcionalidad de este lenguaje. Para ordenar las filas del resultado de la consulta, tenemos la cláusula ORDER BY.

Con esta cláusula se altera el orden de visualización de las filas de la tabla pero en ningún caso se modifica el orden de las filas dentro de la tabla. La tabla no se modifica. Podemos indicar la columna por la que queremos ordenar utilizando su nombre de columna (nbcolumna) o utilizando su número de orden que ocupa en la lista de selección (Nºcolumna). Veamos estos ejemplos. Por Defecto Ordena de manera ascendente SELECT idEmpleado,Apellidos+', '+Nombre as NombreCompleto FROM Empleados ORDER BY 2

Se puede Ordenar por N Columnas y especificar la forma de ordenamiento (Ascendente o Descendente). Si no se especifica la forma de ordenamiento, se considera de forma ascendente por defecto. SELECT idCliente,nombreCompañía,País,Ciudad FROM Clientes WHERE País IS NOT NULL ORDER BY 3 ASC,4 DESC Ing. Segundo J osé Castillo Zumarán

4


Consultas.- SELECT

A continuación veremos las cláusulas que nos permiten indicar qué filas queremos visualizar.

Las cláusulas DISTINCT / ALL.- Al incluir la cláusula DISTINCT en la SELECT, se eliminan del resultado las repeticiones de filas. Si por el contrario queremos que aparezcan todas las filas incluidas las duplicadas, podemos incluir la cláusula ALL o nada, ya que ALL es el valor que SQL asume por defecto. Por ejemplo queremos saber los países y sus ciudades a los cuales pertenecen nuestros clientes. SELECT País,Ciudad FROM Clientes WHERE País IS NOT NULL ORDER BY 1,2

En esta Consulta aparecen Filas repetidas SELECT DISTINCT País,Ciudad FROM Clientes WHERE País IS NOT NULL ORDER BY 1,2

En esta Consulta con la Cláusula DISTINCT se eliminan las Filas repetidas La cláusula TOP.- La cláusula TOP permite sacar las N primeras filas de la tabla origen. No elige entre valores iguales, si pido los 25 primeros valores pero el que hace 26 es el mismo valor que el 25, entonces devolverá 26 registros en vez de 25 (o los que sea). Siempre se guia por la columna de ordenación, la que aparece en la cláusula ORDER BY o en su defecto la clave principal de la tabla. Por ejemplo queremos saber los dos empleados más antiguos de la empresa. SELECT TOP 2 dEmpleado,Apellidos+', '+Nombre as NombreCompleto,fechaContratación FROM Empleados ORDER BY 3 DESC

El número de filas que queremos visualizar se puede expresar con un número entero o como un porcentaje sobre el número total de filas que se recuperarían sin la cláusula TOP. En este último caso utilizaremos la cláusula TOP n PERCENT (porcentaje en inglés). SELECT TOP 20 PERCENT idEmpleado,Apellidos+', '+Nombre as NombreCompleto,fechaContratación FROM Empleados ORDER BY 3 DESC

SQL Server 2000 tiene muchas funciones predefinidas que se pueden utilizar, enumerarlas y explicarlas sería demasiado largo. Lo mejor es saber que tenemos muchas funciones y cuando queramos obtener algo diferente consultar la ayuda de SQL Server 2000 para ver si existe una función para lo que queremos hacer. A título de ejemplo tenemos unas que se utilizan más a menudo: GetDate() devuelve la fecha y hora en que estamos Year(fecha) devuelve el año de la fecha Ing. Segundo J osé Castillo Zumarán

5


Consultas.- SELECT

Month(fecha) devuelve el mes de la fecha Day(fecha) devuelve el dia de la fecha Uso del paréntesis. Cuando combinamos varias expresiones podemos utilizar los paréntesis para delimitar cada expresión. Siempre se tiene que utilizar un paréntesis de apertura ( y uno de cierre ). Ejemplo: precioUnidad+ (precioUnidad * 0.1) El uso del paréntesis sirve para que la expresión quede más clara sobre todo cuando combinamos muchas expresiones, y para que los operadores actuen en el orden que nosotros queramos para así olvidarnos de la prioridad de los operadores. Funciones de resumen Son funciones que permiten efectuar una operación aritmética que resume los valores de una columna de toda la tabla, o que resume los valores de la columna agrupados según determinado criterio. La función produce un solo valor que es el resumen de la tabla, o de cada uno de los grupos. F u n c i ón S U M

Realiza una suma acumulada de un atributo para todas las filas accedidas mediante una consulta SQL. Queremos obtener la suma del precio de todos los productos almacenados, bastara con realizar la siguiente consulta. SELECT SUM(precioUnidad) as SumaPrecio FROM Productos

Resultado

SumaPrecio --------------------2222.7100 (1 filas afectadas) Fu n c i o n CO U N T ( * )

Cuenta todas las filas de las tablas accedidas mediante una consulta SQL Queremos mostrar el total de productos con los que actualmente esta vendiendo la Empresa: SELECT COUNT(*) as TotalProductos FROM Productos WHERE suspendido=0

Resultado TotalProductos -------------69 (1 filas afectadas)

Ing. Segundo J osé Castillo Zumarán

6


Consultas.- SELECT

Fu n c i o n A V G ( A v e r a g e )

Realiza una media aritmética de los atributos para todas las filas accedidas mediante una consulta SQL. Ejemplo 14 SELECT AVG (precioUnidad) as PrecioPromedio from PRODUCTOS Where suspendido=0

Resultado PrecioPromedio --------------------26.7343 (1 filas afectadas) Fu n c i o n M A X

Obtiene el maximo valor del atributo especificado, de entre todas las filas seleccionadas mediante una consulta SQL. Queremos saber cual es Producto más caro que actualmente vendemos: SELECT MAX (precioUnidad) as [Mas Caro] FROM Productos

Resultado Mas Caro --------------------263.5000 (1 filas afectadas) Fu n c i o n M I N .

Obtiene el mínimo valor del atributo especificado de entre todas las filas seleccionadas mediante una consulta SQL. SELECT MIN (precioUnidad) as [Mas Barato] FROM Productos

Resultado Mas Barato --------------------2.5000 (1 filas afectadas)


7


Consultas.- SELECT

Para Cimentar esta parte basica de consultas presentamos una serie de Consultas sencillas a una Tabla. 1. Lista de Empleados SELECT * FROM Empleados

2. Lista de Clientes que sean de Brasil SELECT idCliente,NombreCompañía,país FROM Clientes WHERE país='Brasil'

3. Lista de Proveedores cuyo cargo contacto sea Representante de Ventas SELECT IdEmpleado,apellidos+', '+nombre as [Nombre Completo Empleado] FROM EMPLEADOS WHERE Cargo='Representante de Ventas'

4. Lista de Productos cuyo precio unidad sea mayor a 50. SELECT idProducto,NombreProducto,precioUnidad FROM Productos WHERE precioUnidad>50 ORDER BY 2

5. Lista de Productos que comiencen con “M”. SELECT idProducto,NombreProducto,precioUnidad FROM Productos WHERE nombreProducto like 'M%'

6. Lista de Pedidos efectuados entre el 01/08/1994 y el 30/10/1994 SELECT idPedido,fechaPedido as [Fecha de Pedido],paísDestinatario FROM Pedidos WHERE fechaPedido Between '01/08/1994' and '30/10/1994' ORDER BY fechaPedido

7. Lista de proveedores que tengan Fax. SELECT idProveedor, NombreCompañía,NombreContacto,Teléfono,Fax FROM Proveedores WHERE fax is NOT NULL

8. Lista de proveedores que no tengan Fax SELECT idProveedor, NombreCompañía,NombreContacto,Teléfono,Fax FROM Proveedores WHERE fax is NULL

9. Lista de Clientes que tengan la palabra “com” en forma ordenadamente descendentemente. SELECT idCliente, NombreCompañía FROM Clientes WHERE NombreCompañía LIKE '%com%' ORDER BY 2 DESC

10. Lista de productos cuyo proveedor sea de codigo 3,5 y 7. SELECT idProducto,NombreProducto,precioUnidad,idProveedor FROM Productos WHERE idProveedor IN (3,5,7) ORDER BY 2 DESC

11. Lista de clientes cuya pais sea Alemania, España o Francia. SELECT idCliente, NombreCompañía,país FROM Clientes WHERE país IN ('Alemania','España','Francia') ORDER BY 2 DESC Ing. Segundo J osé Castillo Zumarán

8


Consultas.- SELECT

12. Lista de Empleados y sus edades. SELECT IdEmpleado,apellidos+', '+nombre as [Nombre Completo Empleado], DATEDIFF(mm, fechaNacimiento, getdate())/12 as Edad FROM Empleados

13. Lista de pedidos realizados en octubre del año de 1994. SELECT idPedido,fechaPedido FROM Pedidos WHERE year(fechaPedido)=1994 and month(fechaPedido)=8

14. Cual es el producto mas barato. SELECT MAX(precioUnidad) FROM Productos SELECT TOP 1 idProducto,nombreProducto,precioUnidad FROM Productos ORDER BY 3 DESC

15. Cual es el producto mas caro. SELECT MIN(precioUnidad) FROM Productos SELECT TOP 1 idProducto,nombreProducto,precioUnidad FROM Productos ORDER BY 3 ASC


9


Consultas.- SELECT

Cuando deseamos obtener información que está distribuida en más de una tabla podemos hacer uso de las combinaciones. Los tipos de combinaciones más comunes son las reuniones, las que pueden ser internas o externas: INNER JOIN (reunión interna), LEFT OUTER JOIN (reunión externa izquierda), RIGHT OUTER JOIN (reunión externa derecha), FULL OUTER JOIN (reunión externa completa, que es la unión de LEFT OUTER JOIN y RIGHT OUTER JOIN), y CROSS JOIN (producto cartesiano de dos tablas). En conclusión, Un JOIN o consulta correlacionada es la consulta que selecciona columnas de más de una tabla, y las muestra en un único conjunto de resultados. SELECT lista_columna FROM tabla1 Tipo_join JOIN tabla2 ON condición_del_join ±

± ±

Lista_columnas es la lista de columnas a mostrar en el resultado de la consulta. Se recomienda que cada columna sea clasificada con el alias de la tabla a la cual pertenece. Tipo_join indica si el JOIN es interior (INNER), exterior (OUTER) o irrestricto (CROSS). Condición_del_join es una expresión que indica en base a que columnas de cada una de las tablas se establece la relación entre ellas.

USANDO REUNIONES INTERNAS Una reunión interna queda expresada a través de la cláusula INNER JOIN. INNER JOIN devuelve filas de cualquiera de las tablas sólo si tienen una fila correspondiente en la otra tabla. Éste es el tipo de combinación predeterminado si no se especifica ningún tipo de combinación. El INNER JOIN es otro tipo de composición de tablas, permite emparejar filas de distintas tablas de forma más eficiente que con el producto cartesiano cuando una de las columnas de emparejamiento está indexada. Ya que en vez de hacer el producto cartesiano completo y luego seleccionar la filas que cumplen la condición de emparejamiento, para cada fila de una de las tablas busca directamente en la otra tabla las filas que cumplen la condición, con lo cual se emparejan sólo las filas que luego aparecen en el resultado. La sintaxis es la siguiente:

y tabla2 son especificaciones de tabla (nombre de tabla con alias o no, nombre de consulta guardada), de las tablas cuyos registros se van a combinar. tabla1

Pueden ser las dos la misma tabla, en este caso es obligatorio definir al menos un alias de tabla. col1, col2 son

las columnas de emparejamiento.

Observar que dentro de la cláusula ON los nombres de columna deben ser nombres cualificados (llevan delante el nombre de la tabla y un punto). Ing. Segundo J osé Castillo Zumarán

10


Consultas.- SELECT

Las columnas de emparejamiento deben contener la misma clase de datos, las dos de tipo texto, de tipo fecha etc... los campos numéricos deben ser de tipos similares. Por ejemplo, se puede combinar campos AutoNumérico y Long puesto que son tipos similares, sin embargo, no se puede combinar campos de tipo Simple y Doble. Además las columnas no pueden ser de tipo Memo ni OLE. comp representa cualquier operador de comparación ( =, <, >, <=, >=, o <> ) y se utiliza para establecer la condición de emparejamiento. Se pueden combinar más de dos tablas En este caso hay que sustituir en la sintaxis una tabla por un INNER JOIN completo. Departamento (5 Filas) Id_dep 1 2 3 4 5

Nombre Ventas Finanzas Personal Logistica marketing

Empleado (8 Filas) Jefe López Mora Castro Alva Ludeña

Id_emp 101 102 103 104 105 106 107 108

Paterno Quispe Soria Osorio Aliaga Vera Barrios Márquez Cáceres

Id_dep 2 1 2 3 2 NULL 1 2

SELECT e.id_emp, e.paterno, d.jefe FROM Empleado e INNER JOIN departamento d ON e.iddep=d.id_dep Resultado (7 Filas) Id_emp paterno 101 Quispe 102 Soria 103 Osorio 104 Aliaga 105 Vera 107 Márquez 108 Cáceres

Jefe Mora López Mora Castro Mora López Mora

Ejemplo Listar los Productos y los nombres de categoría a los cuales pertenecen. Utilice la Base de Datos Neptuno. Sugerencia:

En la consulta mostrada se hace uso de los “alias” para reducir la cantidad de código de la consulta. Cuando utilizamos un alias (como Empleado As E) nos referimos a la tabla origen a partir del alias creado evitando la necesidad de escribir el nombre completo de la tabla origen. Además, los alias son utilizados también para renombrar las columnas que se mostrarán en la cláusula SELECT, sobretodo cuando se mostrarán valores a partir de expresiones. SELECT idProducto,NombreProducto,precioUnidad,nombreCategoría as Categoria FROM Productos p INNER JOIN Categorías c ON p.idCategoría=c.idCategoría WHERE suspendido=0

Es posible listar las tablas origen en la cláusula FROM una tras otra e incluir las Ing. Segundo J osé Castillo Zumarán

11


Consultas.- SELECT

condiciones de combinación en la cláusula WHERE, aunque esto genera un producto cartesiano entre las tablas origen, lo que afecta directamente el rendimiento de la consulta. Por otra parte, se mezclan en la cláusula WHERE tanto condiciones de reunión, como condiciones de filtrado, lo que hace menos legible la consulta. SELECT idProducto,NombreProducto,precioUnidad,nombreCategoría as Categoria FROM Productos p,Categorías c WHERE p.idCategoría=c.idCategoría AND suspendido=0

Ejemplo Listar los pedidos registrados en la Primera Quincena del mes de Noviembre. La información debe presentar en el siguiente formato. Numero de Pedido, Fecha de Pedido, Nombre de Cliente, Empleado que atendio el pedido.

Como vemos para elaborar la consulta tendremos que trabajar con tres tablas, la tabla Empleados, Pedidos y Clientes. En este caso hay que sustituir en la sintaxis una tabla por un INNER JOIN completo. SELECT idPedido,fechaPedido,NombreCompañía,apellidos+', '+nombre as NombreEmpleado FROM Pedidos p INNER JOIN Clientes c ON p.idCliente=c.idCliente INNER JOIN Empleados e ON p.idEmpleado=e.IdEmpleado WHERE YEAR(p.fechapedido)=1994 AND MONTH(p.fechapedido)=11 AND DAY(p.fechapedido) BETWEEN 1 AND 15 ORDER BY 3

USANDO REUNIONES EXTERNAS - OUTER JOIN Un outer join es la consulta correlacionada que entrega todas las filas que están relacionadas, y además: ± Las filas no relacionadas de la tabla izquierda (LEFT OUTER JOIN ), o ± Las filas no relacionadas de la tabla derecha (RIGHT OUTER JOIN), o ± Las filas no relacionadas de ambas tablas (FULL OUTER JOIN) Se considera como tabla izquierda, a aquella que se menciona en la cláusula FROM. LEFT OUTER JOIN Devuelve todas las filas para las que involucradas. Además, devuelve todas las existe una fila correspondiente en la concordancia, las columnas seleccionadas NULL. Departamento (5 Filas) Id_dep 1 2 3 4 5


Jefe López Mora Castro Alva Ludeña

exista una conexión entre las tablas filas de la primera tabla para las que no segunda tabla. Al devolver filas sin de la segunda tabla se devuelven como

Empleado (8 Filas) Id_emp 101 102 103 104 105 106 107 108


Id_dep 2 1 2 3 2 NULL 1 2

SELECT e.id_emp, e.paterno, d.jefe FROM empleado e LEFT OUTER JOIN departamento d ON e.id_dep=d.id_dep


12


Resultado (8 filas) Id_emp Paterno 101 Quispe 102 Soria 103 Osorio 104 Aliaga 105 Vera 106 Barrios 107 Márquez 108 Cáceres

Consultas.- SELECT

Jefe Mora López Mora Castro Mora NULL López Mora

La consulta muestra a los 7 empleados que están registrados en departamentos existentes, y además al empleado 106 Barrios que figura en un departamento inexistente. Como empleado es la tabla izquierda, la consulta debe mostrar todas las filas de la tabla empleado; es decir, las filas relacionadas y las filas no relacionadas.

RIGHT OUTER JOIN Devuelve todas las filas para las que exista una conexión entre las tablas involucradas. Además, devuelve todas las filas de la segunda tabla para las que no existe una fila correspondiente en la primera tabla. Al devolver filas sin concordancia, las columnas seleccionadas de la primera tabla se devuelven como NULL. Departamento (5 Filas) Id_dep 1 2 3 4 5


Empleado (8 Filas)


Id_emp 101 102 103 104 105 106 107 108


Id_dep 2 1 2 3 2 NULL 1 2

SELECT e.id_emp, e.paterno, d.jefe FROM empleado e RIGHT OUTER JOIN departamento d ON e.id_dep = d.id_dep RESULTADO (9 filas) Id_emp paterno 101 Quispe 102 Soria 103 Osorio 104 Aliaga 105 Vera 107 Márquez 108 Cáceres NULL NULL NULL NULL

Jefe Mora López Mora Castro Mora López Mora Alva Ludeña

La consulta muestra a los 7 empleados que están registrados en departamentos existentes, y además a los jefes de departamento que no tienen empleados a su cargo. Como departamento es la tabla de la izquierda, la consulta debe mostrar todas las filas de la tabla empleado que tienen relación con la tabla departamento y además las filas de tabla departamento que no tienen relación con la tabla empleado. Ing. Segundo J osé Castillo Zumarán

13


Consultas.- SELECT

Como puede suponer, podemos transformar una reunión izquierda en una derecha invirtiendo el orden de las tablas involucradas. Aunque más que una cuestión de orden, las reuniones externas izquierdas y derechas proporcionan mejor expresividad a las consultas y cubren muchas necesidades en cuanto a consultas. FULL OUTER JOIN Devuelve todas las filas para las que exista una conexión entre las tablas involucradas. Además, devuelve todas las filas de la primera tabla para las que no existe una fila correspondiente en la segunda tabla y todas las filas de la segunda tabla para las que no existe una fila correspondiente en la primera tabla. Al devolver filas sin concordancia, las columnas seleccionadas de la primera o segunda tabla se devuelven como NULL. Podemos decir que FULL OUTER JOIN es la unión de L E F T O U T ER J O I N y R I G H T O U TE R J O I N . Departamento (5 Filas) Id_dep 1 2 3 4 5


Empleado (8 Filas)


Id_emp 101 102 103 104 105 106 107 108


Id_dep 2 1 2 3 2 NULL 1 2

SELECT e.id_emp, e.paterno, d.jefe FROM Empleado e FULL OUTER JOIN Departamento d ON e.id_dep = d.id_dep Resultado (10 filas) Id_emp paterno

Jefe

101 102 103 104 105 107 108 106 NULL NULL

Mora López Mora Castro Mora López Mora NULL Alva Ludeña

Quispe Soria Osorio Aliaga Vera Márquez Cáceres Barrios NULL NULL

La consulta muestra a los 7 empleados que estan registrados en departamentos existents, y además a los empleados registrados en departamentos inexistentes, y a los jefes de departamento que no tienen empleados a su cargo.


14


Consultas.- SELECT

CROSS JOIN Un CROSS JOIN es la consulta correlacionada que combina cada una de las filas de las tablas con todas las filas de la otra tabla. Devuelve todas las filas de la primera tabla combinadas con todas las filas de la segunda tabla. La cláusula ON no se incluye. En pocas palabras, CROSS JOIN devuelve el producto cartesiano de las dos tablas. No es necesario que exista una columna común para ejecutar cross join Departamento (5 Filas) Id_dep 1 2 3 4 5


Empleado (8 Filas)


Id_emp 101 102 103 104 105 106 107 108


Id_dep 2 1 2 3 2 NULL 1 2

SELECT e.id_emp, e.paterno, d.jefe FROM empleado e CROSS JOIN departamento d

o SELECT e.id_emp, e.paterno, d.jefe FROM empleado e , departamento d

Aunque la segunda consulta no hace uso de CROSS JOIN, devuelve la misma información que la primera. Listar las tablas separadas por comas en la cláusula FROM genera un producto cartesiano de las filas de las tablas comprometidas. RESULTADO (40 filas) Id_emp Paterno 101 Quispe 101 Quispe 101 Quispe 101 Quispe 101 Quispe 102 Soria 102 Soria 102 Soria 102 Soria 102 Soria 103 Osorio … … … … 107 Márquez 108 Caceres 108 Caceres 108 Caceres 108 Caceres 108 Caceres

Jefe López Mora Castro Alva Ludeña López Mora Castro Alva Ludeña López … … Ludeña López Mora Castro Alva Ludeña


15


Consultas.- SELECT

Más Ejemplos 1. Listar los pedidos atendidos por la compañía de envíos ‘Speedy Express’ SELECT pe.idPedido,pe.fechaPedido,cl.NombreCompañía as Cliente FROM Pedidos pe INNER JOIN [Compañías de Envíos] co ON pe.formaEnvío=co.IdCompañíaEnvíos INNER JOIN Clientes cl ON pe.idCliente=cl.idCliente WHERE co.NombreCompañía='Speedy Express' ORDER BY 2 ASC

2. Lista de productos de la categoría Bebidas. SELECT pr.idProducto,pr.nombreProducto,pr.precioUnidad FROM Productos pr INNER JOIN Categorías ca ON pr.idCategoría=ca.idCategoría WHERE ca.NombreCategoría='Bebidas' ORDER BY 2 ASC

3. Lista de pedidos registrados en el primer trimestre del año de 1995. Mostrar el número de pedido, fecha de registro, nombre de compañía para la cual se registro y el empleado que atendió. SELECT pe.idPedido,pe.fechaPedido,cl.nombreCompañía as Cliente, apellidos+', '+nombre as Empleado FROM Pedidos pe INNER JOIN Clientes cl ON pe.idCliente=cl.idCliente INNER JOIN Empleados em ON pe.idEmpleado=em.idEmpleado WHERE YEAR(fechaPedido) =1995 and MONTH(fechaPedido) IN (1,2,3,4)

4. Mostrar cuántos empleados tienen más de 14 años trabajando en la compañía. SELECT COUNT(*) as Cantidad FROM empleados where DATEDIFF(yy, FechaContratación, GETDATE()) > 14

5. Listar los productos, la categoría a la que pertenece y el proveedor que lo surte. SELECT a.nombreProducto,a.precioUnidad,b.nombreCategoría as Categoria,c.nombreCompañía as Proveedor FROM Productos a INNER JOIN Categorías b ON a.idCategoría=b.idCategoría INNER JOIN Proveedores c ON a.idProveedor=c.idProveedor WHERE suspendido=0

6. Listar los empleados que tengan entre 50 y 60 años. SELECT idEmpleado,apellidos+', '+nombre as Empleado,fechaNacimiento, DATEDIFF(yy, FechaNacimiento, GETDATE()) as Edad FROM Empleados WHERE DATEDIFF(yy, FechaNacimiento, GETDATE()) BETWEEN 50 AND 60

7. Lista de proveedores que no tengan pagina principal. SELECT idProveedor,nombreCompañía,nombreContacto FROM Proveedores WHERE páginaPrincipal IS NULL

8. Quienes son los empleados que actualmente tienen entre 10 y 13 años laborando en la empresa. SELECT idEmpleado,apellidos+', '+nombre as Empleado,fechaContratación, DATEDIFF(yy, fechaContratación, GETDATE()) as Edad FROM Empleados WHERE DATEDIFF(yy, fechaContratación, GETDATE()) BETWEEN 10 AND 13

9. Quien es el empleado que tiene menos edad. SELECT TOP 1 idEmpleado,apellidos+', '+nombre as Empleado,fechaNacimiento, DATEDIFF(yy, FechaNacimiento, GETDATE()) as Edad FROM Empleados ORDER BY 4 ASC


16


Consultas.- SELECT

10. Lista de pedidos que demoraron mas de 10 días entre la fecha que se realizo el pedido y la fecha que se envió. SELECT idPedido,fechaPedido,fechaenvío,DATEDIFF(dd,fechaPedido, fechaenvío) FROM Pedidos WHERE DATEDIFF(dd,fechaPedido, fechaenvío)>10

11. Cual fue el pedido que mas se demoro en atenderse desde que se realizo hasta la fecha que se envió. SELECT TOP 1 idPedido,fechaPedido,fechaenvío,DATEDIFF(dd,fechaPedido, fechaenvío) AS TotalDias FROM Pedidos ORDER BY 4 DESC

12. Lista de Clientes que no tenga Fax. SELECT idCliente,nombreCompañía,nombreContacto,cargoContacto FROM Clientes WHERE fax IS NULL

13. Ver todos los pedidos realizados los días lunes o viernes que demoraron una semana en ser despachados. Revizar las funciones DATEPART ( datepart , date ), DATENAME ( datepart , date ) SELECT idPedido,fechaPedido,fechaenvío FROM Pedidos WHERE DateName(dw,fechaPedido) IN ('Lunes','Viernes') AND DATEDIFF(dd,fechaPedido, fechaenvío)=7

14. Lista de pedidos donde aparece el producto ‘Pan Fino’ como parte del pedido, en que cantidad se compro, a que precio. SELECT idPedido,de.precioUnidad,cantidad FROM [Detalles de Pedidos] de INNER JOIN Productos pr ON de.idProducto=pr.idProducto WHERE pr.nombreProducto LIKE 'Pan Fino'

15. A que categoría pertenece el productos mas caro. SELECT TOP 1 pr.nombreProducto,pr.precioUnidad,ca.nombreCategoría FROM Productos pr INNER JOIN Categorías ca ON pr.idCategoría=ca.idCategoría ORDER BY 2 DESC

16. Que proveedor ofrece el producto mas caro. SELECT TOP 1 a.nombreProducto,a.precioUnidad,b.nombreCompañía FROM Productos a INNER JOIN Proveedores b ON a.idProveedor=b.idProveedor ORDER BY 2 DESC

17. Lista de todos los productos suspendidos, a que categoría pertenece y que proveedor los ofrece. SELECT a.idProducto,a.nombreProducto,a.precioUnidad,cantidadporUnidad,b.nombreCategoría,c.nombreCompañía FROM Productos a INNER JOIN Categorías b ON a.idCategoría=b.idCategoría INNER JOIN Proveedores c ON a.idProveedor=c.idProveedor WHERE suspendido=1


17


Consultas.- SELECT

UNIENDO MÚLTIPLES CONJUNTOS DE RESULTADOS El operador UNION une los resultados de dos o más sentencias SELECT en un solo conjunto de resultados. Use el operador UNION cuando los datos que desea recuperar residen en diferentes localizaciones y no puede acceder a ellos con una sola consulta. Cuando use el operador UNION considere lo siguiente: ±

SQL Server requiere que las consultas a las tablas referenciales tengan el mismo número de columnas, los mismos tipos de datos, y que las columnas se encuentren en el mismo orden en la lista SELECT.

±

SQL Server elimina las filas duplicadas en el resultado. Sin embargo, si usa la opción ALL, todas las filas (incluso las duplicadas) son incluidas en el resultado.

±

Debe especificar los nombres de las columnas en la primera sentencia del SELECT. Por consiguiente, si quiere definir los nuevos titulos de las columnas para el resultado, debe crear los seudónimos de las columnas en la pimera sentencia SELECT.

±

Si quiere que el resultado completo sea devuelto en un orden específico, debe especificar el orden e incluir la cláusula ORDER BY dentro de la sentencia UNION. Sentencia_select UNION [ALL] Sentencia_select UNION [ALL] Sentencia_select, …

Ejemplo con la Base de Datos Neptuno. Este ejemplo combina dos resultados. El primer resultado devuelve el nombre, la ciudad y código postal de cada cliente de la tabla C l i e n t e s . El segundo resultado devuelve el nombre, ciudad y código postal de cada empleado de la tabla Em p l e a d o s . SELECT (nombre + ' ' + apellidos) as Nombre, ciudad, códPostal FROM Empleados UNION SELECT NombreCompañía, ciudad, códPostal FROM Clientes ORDER BY 1 ASC

También podemos indicar que queremos el resultado ordenado por algún criterio, en este caso se incluye la cláusula ORDER BY que ya vimos en el tema anterior. La cláusula ORDER BY se escribe después de la última consulta, al final de la sentencia; para indicar las columnas de ordenación podemos utilizar su número de orden o el nombre de la columna, en este último caso se deben de utilizar los nombres de columna de la primera consulta ya que son los que se van a utilizar para nombrar las columnas del resultado.


18


Consultas.- SELECT

CREANDO UNA TABLA DESDE UN CONJUNTO DE RESULTADOS Es posible crear una tabla a partir del resultado de una consulta. Esta tabla tiene como nombres de columna los mismos especificados en la instrucción SELECT de la consulta y adquieren sus tipos de datos y longitudes originales. Ejemplos: Mostrar la lista de productos que no están suspendidos así como el nombre de la categoría a la cual pertenecen. -- Creación de tabla temporal local SELECT p.idProducto,p.NombreProducto,p.precioUnidad,c.nombreCategoría INTO #Productos_Categoria FROM Productos p INNER JOIN Categorías c ON p.idCategoría=c.idCategoría

Esta consulta ha creado la tabla # P r o d u c t o s _ Ca t e g o r i a y las columnas incluidas en la cláusula SELECT; además, ha incluido como registros aquellos devueltos por la consulta. Podemos utilizar DROP TABLE para eliminar la tabla, hacer alguna consulta sobre ella o inspeccionar sus columnas con sp_columns. EXEC sp_columns Gerentes_Dptos En Transact SQL es posible crear tablas temporales locales y tablas temporales globales para almacenar los resultados de una consulta. La diferencia radica en que las tablas temporales locales (llamadas también privadas) sólo pueden ser accesadas por la conexión que las creó, mientras que las tablas temporales globales existen mientras exista alguna conexión que haga uso de ellas. Para crear una tabla temporal local basta anteponer el símbolo # al nombre de la tabla, y el símbolo ## si desea crear una tabla temporal global. A continuación se muestra la consulta anterior creando una tabla temporal local y una tabla temporal global. -- Creación de tabla temporal local SELECT p.idProducto,p.NombreProducto,p.precioUnidad,c.nombreCategoría INTO ##Productos_Categoria FROM Productos p INNER JOIN Categorías c ON p.idCategoría=c.idCategoría


19


Consultas.- SELECT

INTRODUCCIÓN A LAS SUBCONSULTAS Las subconsultas facilitan la tares de realizar operaciones relativamente complejas, al subdividirlas en pequeñas consultas en la que cada una contribuye en la solución proporcionando datos específicos. En SQL Server la mayor parte de las combinaciones puede expresarse de manera alternativa como subconsultas, aunque suele ser menos eficiente que realizar la operación de combinación. Use las subconsultas para solucionar una consulta compleja en una serie de pasos lógicos y en consecuencia, para resolver un problema con una sola sentencia. Al utilizar subconsultas es común hacer uso de los predicados IN, ALL, ANY y NOT, además de los operadores de comparación ya conocidos (>, <, =, etc.). Debe tenerse en cuenta que el uso de todos estos operadores depende del número de valores devueltos por una subconsulta. Es habitual utilizar la operación IN para subconsultas, tanto para encontrar valores que cumplan los criterios establecidos, como para encontrar valores que no los cumplan, negando dichos criterios (NOT IN). Utilizar el predicado IN es equivalente a utilizar =ANY, de igual forma, NOT IN es equivalente a <> ALL. Porqué no usar subconsultas Se recomienda el uso de JOINS en lugar de subconsultas porque MICROSOFT SQL Server procesa un JOIN mas rápido que las subconsultas. El uso de las subconsultas puede forzar al S QL S er v e r Q u e r y O p t i m i z er a ejecutar pasos adicionales, como ordenar, y puede influir en el rendimiento del proceso. Si una consulta no requiere de múltiples pasos, puede no ser necesario usar una subconsulta. Uso de subconsultas Cuando decida usar subconsultas, considere los hechos siguientes: 

La subconsulta se especifica entre paréntesis.



La lista SELECT de una subconsulta solo puede contener una expresión nombre de columna que devuelva un valor.



Pude utilizar una subconsulta en cualquier lugar donde se define una expresión, con la condición que devuelva un solo valor.



No se puede usar subconsultas en columnas que contienen texto y tipos de datos de imagen.


20


Consultas.- SELECT

Subconsultas que retornan un solo valor. Se definen: ± En la lista de columnas del SELECT externo. ± En la cláusula WHERE con un operador de comparación (test de comparación) Mostrar todos los productos que no estén suspendidos cuyo precio este por encima del precio promedio de dichos productos. Ejemplo: en la lista del SELECT SELECT p.idProducto,p.NombreProducto,precioUnidad,unidadesEnExistencia, CONVERT(Float,unidadesEnExistencia*precioUnidad)*100/ (SELECT SUM(unidadesEnExistencia*precioUnidad) FROM Productos WHERE suspendido=0) as Porcentaje FROM Productos p INNER JOIN Categorías c ON p.idCategoría=c.idCategoría WHERE suspendido=0 ORDER BY 5 DESC

La subconsulta (SELECT SUM(unidadesEnExistencia*precioUnidad) FROM Productos WHERE suspendido=0) calcula el total de ventas. Luego, este resultado es utilizado por la consulta externa para calcular el porcentaje de dinero invertido por cada Producto respecto al Total. Ejemplo: test de comparación SELECT idProducto,NombreProducto,precioUnidad FROM Productos WHERE suspendido=0 AND precioUnidad>(SELECT AVG(precioUnidad) from Productos WHERE suspendido=0)

La subconsulta (SELECT AVG(precioUnidad) from Productos WHERE suspendido=0) determina el precio promedio de los productos que no estan suspendidos. Luego, este valor es utilizado para determinar que productos estan por encima del precio promedio. Subconsultas que retornan un conjunto de valores Se definen en la cláusula WHERE con el operador IN (Test de pertenencia). Ejemplo de test de pertenencia Listar de los clientes registrados, solo los clientes que no han registrado ningún pedido en el año de 1995 SELECT idcliente,nombrecompañía FROM clientes WHERE idcliente NOT IN (SELECT DISTINCT idcliente FROM pedidos WHERE YEAR(fechapedido)=1995)

La Sub consulta (SELECT DISTINCT idcliente FROM pedidos WHERE YEAR(fechapedido)=1995) , genera una lista de códigos de CLIENTES que han registrado pedidos. Luego, la consulta externa utiliza la lista para determinar que clientes no han solicitado pedidos.


21


Consultas.- SELECT

Subconsultas Correlacionadas Se presentan cuando la consulta externa debe entregar datos a la consulta interna para que se pueda ejecutar. ± ±

La consulta interna se evalúa repetidamente, una vez por cada fila de la consulta externa. Se pueden definir en la cláusula WHERE de la consulta externa usando el operador EXISTS (Test de existencia).

Ejemplo Este ejemplo devuelve una lista de clientes que pidieron más de 20 piezas del producto número 23 en un pedido. SELECT p.idPedido,c.NombreCompañía FROM pedidos p INNER JOIN clientes c ON p.idcliente=c.idcliente WHERE (select cantidad FROM [detalles de pedidos] d WHERE idproducto=23 AND d.idpedido=p.idpedido )>20

Ejemplo de de Test de Existencia Genera una lista de clientes que han registrado pedidos en el mes de julio de 1995 SELECT c.idcliente, c.nombrecompañía FROM clientes c WHERE EXISTS (SELECT * FROM pedidos p WHERE p.idcliente=c.idcliente AND YEAR(p.fechapedido)=1995 AND MONTH(p.fechapedido)=7)

Observación: Los dos ejemplos anteriores pueden replantearse como JOINS. En general, se recomienda el uso de JOINS antes que el de subconsultas correlacionadas. Conceptualmente, una consulta correlacionada es similar a un bucle en programación, aunque no se utilizan en absoluto las construcciones procedimentales do-while o for . El resultado de cada ejecución de la subconsulta debe estar correlacionado (asociado o correspondido) a una fila de la consulta principal.


22


Consultas.- SELECT

Funciones de columna Las funciones agregadas permiten resumir columnas de resultados. SQL Server proporciona seis funciones agregadas generales que forman parte del estándar ANSI SQL-92. Usando Funciones Agregadas En la lista de selección de una consulta de resumen aparecen funciones de columna también denominadas funciones de dominio agregadas. Una función de columna se aplica a una columna y obtiene un valor que resume el contenido de la columna. Funciones AVG COUNT COUNT(*) MAX MIN SUM STDEV STDEVP VAR VARP

Descripción Promedio de valores en una expresión Numerica Numero de valores en una expresión Numero de filas seleccionadas Maximo valor en la expresión Minimo valor en la expresión Valores Totales en una expresión numerica Desviación estadistica de todos los valores Desviación estadistica de la población Varianza estadistica de todos los valores Varianza estadistica de todos los valores de la población.

El argumento de la función indica con qué valores se tiene que operar, por eso e x p r e s i ó n suele ser un nombre de columna, columna que contiene los valores a resumir, pero también puede ser cualquier expresión válida que devuelva una lista de valores. La función SUM() calcula la suma de los valores indicados en el argumento. Los datos que se suman deben ser de tipo numérico (entero, decimal, coma flotante o monetario...). El resultado será del mismo tipo aunque puede tener una precisión mayor. Ejemplo: SELECT SUM(cargo) as MontoFlete FROM Pedidos WHERE YEAR(fechaPedido)=1995 AND MONTH(fechaPedido)=5

La función AVG() calcula el promedio (la media arimética) de los valores indicados en el argumento, también se aplica a datos numéricos, y en este caso el tipo de dato del resultado puede cambiar según las necesidades del sistema para representar el valor del resultado. Ing. Segundo J osé Castillo Zumarán

23


Consultas.- SELECT

StDev() y StDevP() calculan la desviación estándar de una población o de una muestra de la población representada por los valores contenidos en la columna indicada en el argumento. Si la consulta base (el origen) tiene menos de dos registros, el resultado es nulo. Es interesante destacar que el valor nulo no equivale al valor 0, las funciones de columna no consideran los valores nulos mientras que consideran el valor 0 como un valor, por lo tanto en las funciones AVG(), STDEV(), STDEVP() los resultados no serán los mismos con valores 0 que con valores nulos. Veámoslo con un ejemplo: Si tenemos esta tabla con el nombre tabla1: Col1 10 5 0 3 6 0 La consulta

SELECT AVG(col1) AS Media FROM tabla1 Devuelve:

Media 4 En este caso los (10+5+0+3+6+0)/6 = 4

ceros

entran

en

la

media

por

lo

que

sale

igual

a

4

Con esta otra tabla, la tabla2:

Col1 10 5 NULL 3 6 NULL SELECT AVG(col1) AS Media FROM tabla2

Devuelve: Media 4 En este caso los ceros se han sustituido por valores nulos y no entran en el cálculo, por lo que la media sale igual a 6. (10+5+3+6)/4 = 4 Ing. Segundo J osé Castillo Zumarán

24


Consultas.- SELECT

Las funciones MIN() y MAX() determinan los valores menores y mayores respectivamente. Los valores de la columna pueden ser de tipo numérico, texto o fecha. El resultado de la función tendrá el mismo tipo de dato que la columna. Si la columna es de tipo numérico MIN() devuelve el valor menor contenido en la columna, si la columna es de tipo texto MIN() devuelve el primer valor en orden alfabético, y si la columna es de tipo fecha, MIN() devuelve la fecha más antigua y MAX() la fecha más reciente. La función COUNT(nb columna) cuenta el número de valores que hay en la columna, los datos de la columna pueden ser de cualquier tipo, y la función siempre devuelve un número entero. Si la columna contiene valores nulos esos valores no se cuentan, si en la columna aparece un valor repetido, lo cuenta varias veces. COUNT(*) permite contar filas en vez de valores. Si la columna no contiene ningún valor nulo, COUNT(nbcolumna) y COUNT(*) devuelven el mismo resultado, mientras que si hay valores nulos en la columna, COUNT(*) cuenta también esos valores mientras que COUNT(nb columna) no los cuenta. Ejemplo: ¿Cuántos empleados tenemos? ¿Cuántos empleados tienen una oficina asignada? Se pueden combinar varias funciones de columna en una expresión pero no se pueden anidar funciones de columna, es decir: SELECT (AVG(ventas) * 3) + SUM(cuota) FROM ... Es correcto SELECT AVG(SUM(ventas)) FROM ... NO es correcto, no se puede incluir una función de columna dentro de una función de columna


25


Consultas.- SELECT

Las consultas de resumen En SQL SERVER 2000 y de la mayoría de los motores de bases de datos relacionales, podemos definir un tipo de consultas cuyas filas resultantes son un resumen de las filas de la tabla origen, por eso las denominamos consultas de resumen, también se conocen como consultas sumarias. Es importante entender que las filas del resultado de una consulta de resumen tienen una naturaleza distinta a las filas de las demás tablas resultantes de consultas, ya que corresponden a varias filas de la tabla origen. Para simplificar, veamos el caso de una consulta basada en una sola tabla, una fila de una consulta 'no resumen' corresponde a una fila de la tabla origen, contiene datos que se encuentran en una sola fila del origen, mientras que una fila de una consulta de resumen corresponde a un resumen de varias filas de la tabla origen, esta diferencia es lo que va a originar una serie de restricciones que sufren las consultas de resumen y que veremos a lo largo del tema. Las consultas de resumen introducen dos nuevas cláusulas a la sentencia SELECT, la cláusula GROUP BY y la cláusula HAVING, son cláusulas que sólo se pueden utilizar en una consulta de resumen, se tienen que escribir entre la cláusula WHERE y la cláusula ORDER BY y tienen la siguiente sintaxis:

En el ejemplo que viene a continuación tienes un ejemplo de consulta normal en la que se visualizan las filas de la tabla pedidos ordenados por fechapedido, en este caso cada fila del resultado se corresponde con una sola fila de la tabla pedidos, mientras que la segunda consulta es una consulta resumen, cada fila del resultado se corresponde con una o varias filas de la tabla pedidos.


26


Consultas.- SELECT

SELECT idpedido,fechapedido,cargo FROM pedidos WHERE YEAR(fechapedido)=1995 AND MONTH(fechapedido)=1 AND DAY(fechapedido) BETWEEN 1 AND 15 ORDER BY 2 SELECT fechapedido,SUM(cargo) as MontoCargoPorDia FROM pedidos WHERE YEAR(fechapedido)=1995 and MONTH(fechapedido)=1 AND DAY(fechapedido) BETWEEN 1 AND15 GROUP BY fechapedido ORDER BY 1

|

La cláusula GROUP BY

Hasta ahora las consultas de resumen que hemos visto utilizan todas las filas de la tabla y producen una única fila resultado. Se pueden obtener subtotales con la cláusula GROUP BY. Una consulta con una cláusula GROUP BY se denomina consulta agrupada ya que agrupa los datos de la tabla origen y produce una única fila resumen por cada grupo formado. Las columnas indicadas en el GROUP BY se llaman columnas de agrupación. Ejemplo: Un columna de agrupación no puede ser de tipo memo u OLE. La columna de agrupación se puede indicar mediante un nombre de o cualquier expresión válida basada en una columna pero no se pueden utilizar los alias de campo. En la lista de selección sólo pueden aparecer : Valores constantes Funciones de columna Columnas de agrupación (columnas que aparecen en la cláusula GROUP BY) O cualquier expresión basada en l as anteriores.


27


Consultas.- SELECT

Se pueden agrupar las filas por varias columnas, en este caso se indican las columnas separadas por una coma y en el orden de mayor a menor agrupación. Se permite incluir en la lista de agrupación hasta 10 columnas. Ejemplo: Queremos obtener la cantidad de clientes por pais y ciudad: SELECT país,ciudad,COUNT(*) as NumeroClientes FROM clientes GROUP BY país,ciudad ORDER BY 1,2 Se agrupa primero por país, y dentro de cada país por ciudad. Todas las filas que tienen valor nulo en el campo de agrupación, pasan a formar un único grupo. Es decir, considera el valor nulo como un valor cualquiera a efectos de agrupación.

La cláusula HAVING

La cláusula HAVING nos permite seleccionar filas de la tabla resultante de una consulta de resumen. Para la condición de selección se pueden utilizar los mismos Tests de comparación descritos en la cláusula WHERE, también se pueden escribir condiciones (unidas por los operadores OR , AND, NOT), pero existe una restricción. En la condición de selección sólo pueden aparecer: .- Valores constantes .- Funciones de columna .- Columnas de agrupación (columnas que aparecen en la cláusula GROUP BY) .- O cualquier expresión basada en las anteriores. Ejemplo: Queremos listar los nombres de Categorías con la cantidad de productos no suspendidos. SELECT c.nombreCategoría,COUNT(*) as [Total Productos] FROM Productos p INNER JOIN Categorías c ON p.idcategoría=c.idcategoría WHERE p.suspendido=0 GROUP BY c.nombreCategoría HAVING COUNT(*)>10 ORDER BY 2 DESC

Ejemplo: Mostrar cuántos empleados han ingresado a la compañía por año. SELECT YEAR(fechaContratación),COUNT(*) FROM empleados GROUP BY YEAR(fechaContratación)

Ejemplo: Relación de clientes cuya participación de compras a las empresas en el mes de enero del año 1995 hayan superado los 5000 dólares en todos sus pedidos registrados en ese mes. SELECT c.idcliente,c.nombrecompañía,SUM(d.precioUnidad*d.cantidad-d.descuento) as MontoCompra FROM pedidos p INNER JOIN clientes c ON p.idcliente=c.idcliente INNER JOIN [detalles de pedidos] d ON p.idpedido=d.idpedido WHERE YEAR(p.fechapedido)=1995 and MONTH(p.fechapedido)=1 GROUP BY c.idcliente,c.nombrecompañía HAVING SUM(d.precioUnidad*d.cantidad-d.descuento)>5000 ORDER BY 3 DESC


28


Consultas.- SELECT

Resumen del tema

¿Cómo se ejecuta internamente una consulta de resumen?    

 



Primero se forma la tabla origen de datos según la cláusula FROM, Se seleccionan del origen de datos las filas según la cláusula WHERE, Se forman los grupos de filas según la cláusula GROUP BY, Por cada grupo se una fila en la tabla resultante con los valores que aparecen en las cláusulas GROUP BY, HAVING y en la lista de selección, Se seleccionan de la tabla resultante las filas según la cláusula HAVING, Se eliminan de la tabla resultante las columnas que no aparecen en la lista de selección, se ordenan las filas de la tabla resultante según la cláusula ORDER BY.

Una consulta se convierte en consulta de resumen en cuanto aparece GROUP BY, HAVING o una función de columna. En una consulta de resumen, la lista de selección y la cláusula HAVING sólo pueden contener: Valores constantes Funciones de columna Columnas de agrupación (columnas que aparecen en la cláusula GROUP BY) O cualquier expresión basada en las anteriores.


29


Consultas.- SELECT

Mas Ejemplos 1. Cual fue el empleado que atendió el pedido con mayor monto en el año de 1995. 2. Lista de categorías y cuantos productos tiene cada una, ordenados por total de productos. 3. Lista de clientes y la cantidad de pedidos que compro cada uno en el año de 1995. 4. Lista de proveedores que despachan mas de 3 productos. 5. Lista de pedidos y sus montos respectivos registrados en el mes de julio del año de 1996. 6. Listado de empleados y cuantos pedidos efectuó cada uno de ellos en el primer trimestre de 1995. 7. Listado de clientes y cuantos pedidos efectuó cada uno de ellos en el segundo trimestre de 1995. 8. Listado de compañías de envíos y cuantos pedidos despacharon cada uno de ellos en el segundo semestre del año 1995. 9. Listado de Proveedores y cuantos productos que no estén suspendidos ofrecen cada uno de ellos. 10. Listado de empleados y el monto acumulado de todos sus pedidos. 11. Listado de clientes y el monto acumulado de todos sus pedidos. 12. Lista de pedidos registrados en la primera semana de enero de 1995 y sus montos totales. 13. Lista de pedidos registrados en la primera semana de enero de 1995 con sus montos totales sin descuentos, montos de descuento y montos netos. 14. Lista de pedidos y los montos netos de cada uno de ellos, realizados por el empleado Andrew. 15. Cual es el monto que genero la producto ‘Pez Espada’ en el Primer trimestre de 1995. 16. Cual fue el producto que tuvo menos ventas en unidades en el año de 1994. 17. Cual fue el producto que mas se vendió en monto en el año de 1994. 18. Quien es el empleado que quedo ultimo en ventas (considere montos) en el año de 1994. 19. Que empleado vendió mas productos de la categoría ‘Bebidas’ (en monto). 20. Quien fue el cliente que mas compro productos de la categoría ‘Condimentos’. Ing. Segundo J osé Castillo Zumarán

30


Consultas.- SELECT

21. Listado de empleados y el monto acumulado de todos los pedidos atendidos. 22. Listado de clientes y el monto acumulado de todos los pedidos registrados para ellos. 23. Cual fue el producto que mas se vendió en monto (cantidad de dinero). 24. Cual fue el producto que tuvo menos ventas en unidades. 25. Listado de los productos mas vendidos y el monto que ha generado. 26. Listado de los 5 empleados que han registrado mas pedidos y los 5 empleados que han registrado menos pedidos (Ranking de empleados – Los 5 primeros y los 5 últimos). 27. La Empresa necesita premiar a sus dos mejores empleados en atención de pedidos. El criterio que se tomara es en base al monto acumulado de pedidos en la que ha participado. Para ello recurre a Ud. Para que le proporcione información de los empleados y sus montos acumulados de pedidos en el año de 1995. Codigo Empleado, Nombre Completo del Empleado, Monto Total Pedidos

28. La Empresa necesita premiar a sus dos mejores empleados en atención de pedidos. El criterio que se tomara es en base al numero total de pedidos atendidos. Para ello recurre a Ud. Para que le proporcione información de los empleados y la cantidad de pedidos en el año de 1995. Codigo Empleado, Nombre Completo del Empleado, Total de Pedidos

29. Listar los 5 productos que han generado mayores ingresos para la empresa en el año de 1995. Nombre Producto, Monto de Venta 1995

30. Listar los 5 productos que han generado menores ingresos para la empresa en el año de 1995. Nombre Producto, Monto de Venta 1995

31. Se necesita premiar a los mejores clientes. Para ello el gerente de ventas recurre a usted para solicitarle información de los 5 mejores clientes que se ha tenido en el Tercer Trimestre del año de 1995. El criterio que se tomara es el cliente que ha generado mayores compras en ese año. Codigo, Razon Social, Monto Registrado

32. La Empresa necesita comparar el monto total de pedidos registrados en el rimer semestre de los años de 1995 y 1996. Mes, Monto registrado 1995, Monto registrado 1996

33. Elaborar un reporte en la que por cada empleado se liste el monto acumulado por participación en pedidos y la cantidad de pedidos registrados en el año de 1995.


31


Consultas.- SELECT

Empleado, Monto Total Participación, Total Pedidos

34. Listar los productos cuyo stock están por debajo del stock mínimo. 35. Listar los empleados que son jefes y las cantidades de empleados que tienen a su cargo. 36. Listar la relación de todos los empleados y el empleado jefe a quien responde. 37. Se necesita premiar a los dos primeros empleados que vendieron mas en el primer trimestre del año de 1995 de la categoría ‘Bebidas’. 38. Se desea premiar a los vendedores cuyo monto total de pedidos registrados en el año de 1996 en el 4to trimestre hayan superado el monto total de pedidos del año de 1995 en ese Trimestre. Nombre Empleado, Monto 4to Trimestre 1996, Monto 4to Trimestre 1995

39. Quien es el cliente que tiene el mayor monto de compra de productos que pertenece a la categoría ‘Lácteos’. 40. Listado de categorías por montos generados en los años 1994, 1995, 1996. Categoría, Monto registrado 1994, Monto registrado 1995, Monto registrado 1996

41. Listar todos los empleados cuyo monto de ventas en el mes de agosto de 1996 hayan superado las ventas del mes anterior. Empleado, Monto Agosto 1996, Monto Julio 1996

42. Listar todos los empleados que hayan superado los 10 pedidos en el primer trimestre del año de 1996. 43. Mostrar a través de un cuadro de doble entrada a todos los clientes que pertenecen al país de Alemania y Brasil, los montos registrados en los años de 1996 y 1997, solo que hayan sido atendidos por el empleado cuyo apellido es ‘Davolio’. Cliente, País, Monto Registrado 1995, Monto Registrado 1996


32


Consultas.- SELECT

Bibliografía: Delaney, Kalen “ A f o n d o M i cr o s o f t S Q L S er v e r 2 0 0 0 ” McGraw-Hill. 1era edición. España 2001. 904 pp.

Microsoft “ M O C 2 0 7 3 A . Pr o g r a m m i n g a M i cr o s o f t S Q L S er v e r 2 0 0 0 D a t a b a s e ” Microsoft. EEUU 2000. 460 pp.

Microsoft “ I m p l e m e n t i n g a D a t a b a s e o n M i cr o s o f t S QL S er v e r 7 . 0 ” Microsoft. EEUU 1999. 594 pp.

Libros en Pantalla de SQL Server 2000 (actualizada al 2004) http://www.microsoft.com/downloads/details.aspx?FamilyId=A6F79CB 1-A420-445F-8A4B D77A7DA194B&displaylang=es


33


Consultas.- SELECT

BASE DE DATOS NEPTUNO


34

DML. Consultas

Recommend Documents