1. Bases de Datos Unidad VIII Lenguaje SQL Lenguaje de Manipulacin de Datos (DML) Consultas Avanzadas Sergio Snchez Rios. Ingeniero en Informtica Licenciado en Informtica Docente Jornada Parcial Universidad Via del Mar

2. Lenguaje de Manipulacin de Datos (DML) Operadores Especiales Concatenacin Permite concatenar en una sola salida varios campos. Los campos deben ser String, sino lo son se pueden trasformar utilizando la operacinconvert (tipo dato, campo) Ejemplo 1: Utilizando la base de datosNorthwind . Genere una lista de seleccin de la tabla Employees (Empleado) donde solo se genere una columna de salida y esta contenga los campos: EmployeeID, LastName y FirstName. Selectconvert(nvarchar(2),EmployeeID) + + LastName + + FirstName as Nombre from Employees 3. Lenguaje de Manipulacin de Datos (DML) Operadores Especiales Literales Es cualquier cosa que no sea ni alias ni nombre de columna. Existen dos formas para colocar estos valores anexados a la Lista de Seleccin. Se puede colocar el mensaje entrecomillas simples ()o Colocar el nombre de la columna de salida entre corchetes [ ] = valor de salida. Ejemplo 2: Utilizando la tabla Employees liste las columnas EmployeeID, LastName, FirstName, adems envi dos mensajesen conjunto con cada fila utilizando para cada uno una de las opciones de literales. Select EmployeeID, LastName as Apellido, FirstName as Nombre, 'ESTOS SON EMPLEADOS' as Literal,[Otra Forma] = 'Esta es otra forma' from Employees 4. Lenguaje de Manipulacin de Datos (DML) Operadores Especiales Between AND Este operador se utiliza en la clusula where, para poder restringir una lista de seleccin segn un rango de valores. NOMENCLATURA: WHERENombre_ColumnaBETWEENparametro1ANDparametro2 NOTA: parametro1 (Mnimo) y parametro2 (Mximo) Ejemplo 4: Suponga que queremos ver aquellos productos (Tabla Products) cuyos valores se encuentran entre los 4 y 20 Dlares.Select *fromProducts where UnitPrice Between 5.0 and 20.0 5. Lenguaje de Manipulacin de Datos (DML) Operadores Especiales IN (Lista)Este operador permite verificar si el campo se encuentra en una lista determinada o subconsulta. Retorna verdadero si el campos se encuentra en la lista.NOMENCLATURA: WHERE campo_tabla IN (Lista de datos o Subconsulta) Ejemplo 5: Liste los campos de la tabla productos que tengan exactamenteun precio de 18, 19 y 10 dolares. Select * from Products where UnitPrice in (10.0,18.0,19.0) 6. Lenguaje de Manipulacin de Datos (DML) Operadores Especiales Like Esta palabra clave indica el patrn de ajuste con una condicin de bsqueda. La coincidencia de patrones significa analizar una coincidencia entre una expresin y el patrn especificado en la condicin de bsqueda. NOMENCLATURA: Columna_Tabla LIKE Patron_de_Busqueda Si la expresin coincide con el patrn se devuelve un valor booleano TRUE. En caso contrario se devuelve FALSO. La expresin (COLUMNA_TABLA) debe ser de tipo carcter. Los patrones son realmente expresiones de cadena. Y van acompaados de caracteres de comodn, que colocados en a expresin toman un significado especial. 7. Lenguaje de Manipulacin de Datos (DML) Operadores Especiales Like Carcter comodn no en rango, coincide con cualquier carcter distinto de m, n, o, p (segn ejemplo anterior [ ^ mnop ]. [^ ] Carcter comodn en rango, coincide con cualquier carcter nico en el rango o conjunto, tales como [m p] o [ mnop ], significando que cualquiera de los caracteres puede ser m, n, o ,p. [ ] Subrayado, coincide con un nico carcter. _ Smbolo de porcentaje, coincide con una cadena de cero o ms caracteres.% Descripcin Carcter Comodn 8. Lenguaje de Manipulacin de Datos (DML) Operadores Especiales Like Ejemplo 7: Encontrar todos los apellidos (LastName) en la tabla Employees que comiencen con la letra . Use el carcter comodn %. SELECT LastName FROM Employees WHERE LastName LIKE 'S%' Ac se arma la frase con el comodn Ejemplo 8: Para recuperar el apellido de los Empleados cuya primera letra comienza entre y . Utilice el comodn [ ]. SELECT LastName FROM Employees WHERE LastName LIKE '[A-M ]%' 9. Lenguaje de Manipulacin de Datos (DML) Operadores Especiales Like Ejemplo 9: Usar Base de DatosPubs . Para recuperar la informacin de un autor cuyo ID comienza con el numero 724, sabiendo que cada ID tiene el formato de tres dgitos seguidos por un guin, seguido por dos dgitos, otro guin y finalmente cuatro dgitos. Utilizar el comodn _ .Select *from authorswhere au_id LIKE '724-__-____' 10. Lenguaje de Manipulacin de Datos (DML) Operadores Especiales IS NULL Realiza un testeo para valores que son Nulos. Retorna True si la columna es NULL sino retorna FALSE. NOMENCLATURA: WHERE Columna IS NULL Ejemplo 10: Usar base de datosNorthwind . Liste todos los campos de la tabla Suppliers cuya columna Regin sea NULL. Select * from Suppliers where Region is null 11. Lenguaje de Manipulacin de Datos (DML) Operadores de Agregado Las funciones de agregado son: SUM, AVG, COUNT, COUNT(*), MAX y MINEstas funciones generan valores de resumen en los conjuntos de resultados de las consultas. Una funcin de agregado (con la excepcin de COUNT(*)) procesa todos los valores seleccionados en una nica columna para generar un nico resultado.Las funciones de agregado se pueden aplicar a todas las filas de una tabla, a un subconjunto de la tabla especificado por una clusula WHERE o a uno o varios grupos de filas de la tabla. Cuando se aplica una funcin de agregado, se genera un valor individual por cada conjunto de filas. 12. Lenguaje de Manipulacin de Datos (DML) Operadores de Agregado Valor menor de la expresin. MIN ( expression ) Valor mayor de la expresin. MAX ( expression ) Nmero de filas seleccionadas. COUNT (*) Nmero de valores en la expresin. COUNT ( [ALL | DISTINCT]expression ) Promedio de los valores de la expresin numrica. AVG ( [ALL | DISTINCT]expression ) Total de los valores de la expresin numrica. SUM ( [ALL | DISTINCT]expression ) Resultado Funcin de agregado 13. Lenguaje de Manipulacin de Datos (DML) Operadores de Agregado SUM, AVG, COUNT, MAX y MIN omiten los valores NULL; COUNT(*) no lo hace. La palabra clave opcional DISTINCT se puede usar con SUM, AVG y COUNT para eliminar los valores duplicados antes de que se establezca la funcin de agregado. SUM y AVG se pueden usar slo con columnas numricas, como, por ejemplo, las de los tipos de datosint ,smallint ,tinyint ,decimal ,numeric ,float ,real ,moneyysmallmoney . MIN y MAX no se pueden usar con tipos de datosbit . El resto de las funciones de agregado que no sean COUNT(*) no se puede usar con los tipos de datostexteimage . 14. Lenguaje de Manipulacin de Datos (DML) Operadores de Agregado SUM Retorna:Total de los valores de la expresin numrica. NOMENCLATURA: SUM (Columna para la suma) Ejemplo: Usando la base de dato PUBS. Calcula la suma de las ventas del ao hasta la fecha (ytd_sales) de todos los libros de la tabla titles . USE pubsSELECT SUM(ytd_sales)FROM titles 15. Lenguaje de Manipulacin de Datos (DML) Operadores de Agregado AVG Retorna:Promedio de los valores de la expresin numrica. NOMENCLATURA: AVG (Columna a Promediar) Ejemplo: Usando la base de datos PUBS.Puede averiguar el precio promedio de todos los libros si se duplicaran los precios ( tabla titles ). SELECT avg (price * 2)FROM titles 16. Lenguaje de Manipulacin de Datos (DML) Operadores de Agregado MAX Retorna:Valor mayor de la expresin. NOMENCLATURA: MAX (Columna a obtener el Mximo valor) Ejemplo:Usando la base de dato PUBS. Muestre el mayor valor de laslas ventas del ao (ytd_sales) de todos los libros de la tabla titles. USE pubs SELECT MAX(ytd_sales) FROM titles 17. Lenguaje de Manipulacin de Datos (DML) Operadores de Agregado MIN Retorna:Valor menor de la expresin. NOMENCLATURA: MIN (Columna a obtener el Mnimo valor) Ejemplo:Usando la base de dato PUBS. Muestre el mnimo valor de lasventas del ao (ytd_sales) de todos los libros de la tabla titles. USE pubs SELECT MIN (ytd_sales) FROM titles 18. Lenguaje de Manipulacin de Datos (DML) Operadores de Agregado COUNT Retorna:Nmero de valores en la expresin. En el caso de utilizar (*) retorna el numero de filas. NOMENCLATURA: COUNT (expresin) o COUNT (*) Ejemplo: Usando la base de datos PUBS. Cuente las filas de la tabla titles. Use Pubs SELECT COUNT(*) FROM titles 19. Lenguaje de Manipulacin de Datos (DML) Operadores de Agregado COUNT Ejemplo: Usando la base de datos PUBS. Cuente los datos de la tabla titles, cuyo tipo (TYPE) sea business . Use Pubs SELECT COUNT(TYPE) FROM titles WHERE TYPE = business 20. Lenguaje de Manipulacin de Datos (DML) Sentencia GROUP BY Esta sentencia no permite dividir el resultado de una lista de seleccin en grupos ms pequeos.Para poder listar otras columnas con las funciones de agregacin se debe utilizar esta sentencia. NOMENCLATURA: SELECT lista _de_seleccion FROM Tablas [WHEREOPCIONAL] GROUP BY Nombre_columnas_de_grupo 21. Lenguaje de Manipulacin de Datos (DML) Sentencia GROUP BY Ejemplo: Utilizando la base de datos PUBS. Liste las suma de las ventas por ao ( ytd_sales ) hasta la fecha, clasificndolas por tipo (TYPE) de titulo (titles). Use Pubs SELECT TYPE,SUM(ytd_sales) FROM titles GROUP BY TYPE Resultados mostrados por TYPE Ejemplo: (GRUPOS DENTRO DE GRUPOS). Liste las sumas de las ventas por ao (ydt_sales) hasta la fecha, clasificndolas por tipo (TYPE) y pub_id. Use Pubs SELECT TYPE, pub_id,SUM(ytd_sales) from titles Group by TYPE, pub_id - -El orden de evaluacin es TYPE, pub_id 22. Lenguaje de Manipulacin de Datos (DML) Sentencia GROUP BY CLAUSULA HAVING Esta clusula se utiliza si se desea especificar cuales grupos van a ser desplegados, o sea, restringir los grupos que retornan. NOMENCLATURA: SELECT columnas FROM Tablas [WHERE OPCIONAL] GROUP BY columnas_para_grupos HAVING condiciones_para_gruposEjemplo: Utilizando el ultimo ejemplo. Liste solamente los grupos cuyo pub_id sea igual a 0877. Utilice HAVING SELECT TYPE, pub_id,SUM(ytd_sales) from titles Group by TYPE,pub_id HAVING pub_id = '0877--ACA SE HACE RESTRICCION PARA LOS GRUPOS 23. Lenguaje de Manipulacin de Datos (DML) TABLAS COMBINADAS Una tabla combinada es el resultado de una operacin de combinacin realizada sobre dos o ms tablas. Se pueden realizar varios tipos de combinaciones de tablas: combinaciones internas (inner), externa completa (full outer), externa por la izquierda (left outer), externa por la derecha (rigth outer) y cruzada (cross). Combinacin Interna (Inner Join):Es el tipo de combinacin predeterminado; especifica que solamente se han de incluir en el resultado filas de la tabla que satisfagan la condicin ON. Para especificar una combinacin hay que utilizar la palabra clave JOIN. Se utiliza la palabra clave ON para identificar la condicin de bsqueda sobre la cual se basa la combinacin. NOMENCLATURA: SELECTcolumnas_de_las_tablas FROMtabla1JOINtabla2ONcondiciones 24. Lenguaje de Manipulacin de Datos (DML) TABLAS COMBINADAS Combinacin Interna (Inner Join) Ejemplo: De la base de datos PUBS. Combine las tablas stores y discounts para mostrar que tienda (stor_id) ofrece un descuento y el tipo de descuento (discounttype).Use Pubs SELECT s.stor_id as ALMACEN, d.discounttype as DESCUENTO FROM stores s JOIN discounts dON s.stor_id = d.stor_id SELECT s.stor_id as ALMACEN, d.discounttype as DESCUENTO FROM stores as s,discounts as dWHERE s.stor_id = d.stor_id 25. Lenguaje de Manipulacin de Datos (DML) TABLAS COMBINADAS Combinacin externas Completas (full Outer Join) Especifica que se debera incluir en el resultado las filas no coincidentes (filas que no cumplen con la condicin ON) as como las filas que coincidan (filas que cumplen la condicin ON). En el campo donde no es coincidente la condicin se coloca NULL. Ejemplo: Utilice el mismo ejemplo anterior solo utilice en el from la instruccin FULL OUTER JOIN. Use Pubs SELECT s.stor_id as ALMACEN, d.discounttype as DESCUENTO FROM stores s FULL OUTER JOIN discounts d ON s.stor_id = d.stor_id 26. Lenguaje de Manipulacin de Datos (DML) TABLAS COMBINADAS Combinacin externa por la izquierda (Left Outer Join) Devuelve las filas coincidentes ms todas las filas de la tabla que se especifican a la izquierda de la palabra clave JOIN. Ejemplo: Utilice el mismo ejemplo anterior solo utilice en el from la instruccin LEFT OUTER JOIN. Use Pubs SELECT s.stor_id as ALMACEN, d.discounttype as DESCUENTO FROM stores sLEFT OUTER JOINdiscounts d ON s.stor_id = d.stor_id 27. Lenguaje de Manipulacin de Datos (DML) TABLAS COMBINADAS Combinacin externa por la derecha (Right Outer Join) Es lo contrario a Left Outer Join Ejemplo: Utilice el mismo ejemplo anterior solo utilice en el from la instruccin RIGHT OUTER JOIN. Use Pubs SELECT s.stor_id as ALMACEN, d.discounttype as DESCUENTO FROM stores sRIGHT OUTER JOINdiscounts d ON s.stor_id = d.stor_id 28. Lenguaje de Manipulacin de Datos (DML) SELECT ANIDADOS En algunas situaciones es necesario utilizar SELECT anidados para resolver cierta necesidades de consultas que no pueden ser resueltas utilizando un nico SELECT.Recordar que al realizar un SELECT se genera una tabla temporal de la lista de seleccin, esto es lo que realmente hace posible que se puedan generar consultas sobre consultas o condiciones sobre listas de seleccin. NOMENCLATURA: SELECT Lista_de_seleccin FROM Tablas o Otros Select. WHERE condiciones [JOIN | SUBCONSULTAS | etc. ] 29. Lenguaje de Manipulacin de Datos (DML) SELECT ANIDADOS Ejemplo: Usando base de datosNorthwind . Muestre los Productos (ID del Producto, Nombre y Precio Unitario) que tengan un precio unitario igual al Mximo.SELECT ProductID as ID,ProductName as Nombre, UnitPrice as Precio_UnitarioFROM Products WHERE UnitPrice = (SELECT MAX (UnitPrice) FROM Products) Ejemplo: Usando base de datosNorthwind . Muestre los Productos (ID del Producto, Nombre y Precio Unitario) que tengan un precio unitario igual al Mnimo. SELECT ProductID as ID,ProductName as Nombre, UnitPrice as Precio_UnitarioFROM Products WHERE UNITPRICE = (SELECT MIN(UNITPRICE) FROM Products) 30. Lenguaje de Manipulacin de Datos (DML) Operador UNION Este operador mezcla los resultados de dos o ms consultas en un solo conjunto de resultados que contiene todas las filas que pertenecen a las consultas de la unin.Como restriccin las columnas que se obtienen por la UNION en ambas consultas deben ser del mismo tipo de datos. NOMENCLATURA: SELECT * FROM TABLAS UNION [ ALL ] SELECT * FROM TABLAS 31. Lenguaje de Manipulacin de Datos (DML) Operador UNION NOTA: Al usar el operador opcional ALL se listan como resultados todas las filas de ambas consultas. Sino no se utiliza ALL se lista la UNION es decir no se listan datos duplicados. Ejemplo: Realice una unin de las consultas anidadas vistas anteriormente. Usando ambas opciones de unin (Con y Sin ALL).SELECT ProductID as ID,ProductName as Nombre, UnitPrice as Precio_UnitarioFROM Products WHERE UNITPRICE = (SELECT MIN(UNITPRICE) FROM Products) UNION SELECT ProductID as ID,ProductName as Nombre, UnitPrice as Precio_UnitarioFROM Products WHERE UNITPRICE = (SELECT MIN(UNITPRICE) FROM Products) 32. Lenguaje de Manipulacin de Datos (DML) Operador UNION SELECT ProductID as ID,ProductName as Nombre, UnitPrice as Precio_UnitarioFROM Products WHERE UNITPRICE = (SELECT MIN(UNITPRICE) FROM Products) UNION ALL SELECT ProductID as ID,ProductName as Nombre, UnitPrice as Precio_UnitarioFROM Products WHERE UNITPRICE = (SELECT MIN(UNITPRICE) FROM Products) 33. Lenguaje de Manipulacin de Datos (DML) Ejercicio USE BASE DE DATOS PUBS Obtenga la suma de los Precios Unitarios, cuyos productos tengan un precio igual al Mximo precio unitario o al Mnimo precio unitario de la lista de productos. 34. Bibliografa

Introduccin a los Sistemas de Base de Datos, C. J. Date, Prentice Hall Sptima Edicin, 2001.

Running SQL SERVER 2000, Marci Frohock Garca & Jamie Reding & Edward Whalen & Steve Adrien Deluca, McGraw Hill 2001.

SQL SERVER 7.0 Iniciacin y Referencia, Jos Antonio Ramalho, McGraw-Hill 2000.

Bases de Datos Relacionales, Matilde Celma Gimnez & Juan Casamayor & Laura Mota, Prentice Hall, 2003.

Ctedra Introduccin a las bases de datos, Profesor L. Marti, Universidad de Valparaso, 2004.