M_dulo 07 Curso SQL Server

Curso SQL Server 2000 Capítulo 7

CURSO SQL SERVER 2000

Dictado por Horacio Peñafiel

Módulo 7: Introducción a la programación con T-SQL

FAGDUT 1 CCI


MÓDULO 7 – INTRODUCCIÓN A LA PROGRAMACIÓN CON TRANSACT-SQL La tarea de programación con Transact-SQL es una de las tareas más comunes que debe enfrentar un programador bajo SQL Server 2000. Luego de completar este módulo, estaremos en condiciones de:

• Diferenciar cuáles con las principales construcciones de lenguaje Transact-SQL. • Comprender cuáles con las instrucciones DCL de SQL Server 2000. • Conocer las instrucciones DDL de SQL Server 2000. • Comprender cómo funcionan las instrucciones DML de SQL Server 2000.

7.1 - REFERENCIAS A OBJETOS TRANSACT-SQL Podemos referirnos a objetos de SQL Server de varias formas. Podemos especificar el nombre completo del objetos (su nombre completo calificado – fully qualified name), o especificar sólo parte del nombre del objetos y dejar que SQL Server determine el resto del nombre en el contexto en que estemos trabajando. Nombres completos calificados El nombre completo calificado de un objeto SQL Server incluye cuatro identificadores: nombre de servidor, nombre de base de datos, propietario, y el nombre del objeto en el siguiente formato: Servidor.base de datos.propietario.objeto Un nombre que especificar las cuatro partes es conocido como un nombre completo calificado. Cada objeto creado en SQL Server debe tener un nombre completo calificado único. Por ejemplo, dos tablas pueden ser nombradas como Ordenes en la misma base de datos, siempre y cuando pertenezcan a diferentes propietarios. A su vez, los nombres de columna deben ser únicos dentro de la tabla o vista. Nombres parciales Cuando referenciamos un objeto, no siempre deberemos especificar un servidor, la base de datos y el propietario. Podemos omitir los identificadores iniciales. También podemos omitir identificadores intermedios siempre y cuanto su posición sea indicada con puntos. Los formatos de nombres válidos serían los siguientes: Servidor.base de datos.propietario.objeto Servidor.base de datos..objeto Servidor..propietario.objeto Servidor..objeto Base de datos.propietario.objeto Propietario.objeto Objeto Cuando creamos un objeto, SQL Server usa los siguientes valores por defecto para las distintas partes del nombre no especificadas:

• El servidor por defecto es el servidor local. • La base de datos por defecto es la base de datos actual. • Los propietarios por defecto son los nombres de usuario asociados con el Inicio de

Sesión de la conexión actual.

Un usuario que es miembro de una función puede explícitamente especificar una función como propietario. Un usuario que es miembro de las funciones db_owner o db_ddladmin puede especificar dbo como cuenta de usuario que es propietario de un objeto.

FAGDUT 2 CCI


Esta práctica es recomendada ya que simplifica la administración cuando todos los objetos son poseídos por la misma cuenta de usuario.

El siguiente ejemplo crea la tabla Historial_de_ordenes en la base de datos Northwind: CREATE TABLE Northwind.dbo.Historial_de_ordenes (

NroOrden INT , IDProducto int , PrecioUnitario money , Cantidad int , Descuento decimal )

La mayoría de las referencias usan nombres de tres partes y toman por defecto el servidor actual. Los nombres de cuatro partes son generalmente usados para consultas distribuídas o llamadas a procedimientos almacenados remotos. 7.2 – ELEMENTOS DE TRANSACT-SQL Variables Locales

Las variables con elementos del lenguaje que pueden almacenar un valor. Se define una variable con un nombre y un tipo (en el Módulo 3 vimos los distintos tipos de datos que posee SQL Server, y son los mismos que pueden tomar las variables).

Una variable local es definida en una sentencia DECLARE, se le asigna un valor inicial

en un comando SET, y es usada dentro de la sentencia, lote o procedimiento almacenado donde es declarada. Un nombre de variable local siempre comienza con el símbolo @ precediendo a su nombre.

Sintaxis

DECLARE {@nombre_variable tipo_de_dato} [,...n] SET @nombre_variable = expresión

Ejemplos El siguiente código creala variable local @lvnombre, le asigna un valor, y luego consulta la base de datos para seleccionar el registro que contiene el valor de la variable.

DECLARE @lvnombre char(20) SET @lvnombre = 'Dodsworth' SELECT LastName, FirstName, Title FROM Northwind..Employees

WHERE LastName = @lvnombre En el siguiente ejemplo guardamos el ID de empleado más alto en la variable @EmpIDVariable:

DECLARE @EmpIDVariable int SELECT @EmpIDVariable = MAX(EmployeeID)

FROM Northwind..Employees Si el SELECT retorna múltiples filas, entonces la variable es establecida al valor de la última fila retornada por el SELECT. El siguiente ejemplo guarda el ID de producto del último producto retornado por el SELECT sobre la tabla Products en una variable llamada @ProdIDVariable:

FAGDUT 3 CCI


DECLARE @ProdIDVariable int SELECT @ProdIDVariable = ProductID

FROM Northwind..Products Nota: Versiones previas a la 7.0 de SQL Server teníamos objetos llamados Variables Globales. Estos valores globales retornaban información del sistema. No eran usados como variables porque no podíamos modificar sus valores. Los nombres de esas variables comenzaban con @@. A partir de SQL Server 7.0, estos valores todavía se encuentran disponibles pero se denominan Funciones. Específicamente, estas funciones son parte del grupo de funciones llamadas Funciones Escalares. Operadores Los operadores son símbolos que realizan operaciones en uno o más valores llamados operandos. Las operaciones que pueden ser realizadas incluyen cálculos matemáticos, concatenación de strings, operaciones lógicas, y comparaciones entre columnas, constantes y variables. Pueden ser combinados en expresiones y condiciones de búsqueda. Cuando combinamos operadores, el orden en el cual son procesados está basado en una precedencia predefinida. Tipos de operadores SQL Server 2000 soporta seis tipos de operadores más comunmente utilizados:

• Operador de Asignación: Transact-SQL soporta el simple operador de asignación, el signo igual (=).

• Operadores Aritméticos: Los operadores aritméticos realizan cómputos con columnas numéricas o constantes. Transact-SQL soporta la multiplicación (*), división (/), adición (+), y resta (-). El operador módulo (%), que retorna un resto enterno proveniente de una división entera, también es soportado.

• Operadores Unarios: Los operadores unarios realizan una operación en una sóla expresión numérica. Son el + (positivo), - (negativo), y ~ (NOT). Un operador unario es ubicado comunmente ante de la expresión sobre la que opera.

• Operadores de Comparación: Estos operadores comparan dos expresiones. Se pueden comparar variables, columnas, y expresiones del mismo tipo. Los operadores de comparación incluyen el menor que (<), mayor que (>), igual a (=), y distinto de (<>).

• Operadores de Concatenación de Strings: El operador (+) concatena valores de cadenas de caracteres. Toda otra manipulación de strings es realizada a través de funciones específicas. Por defecto, un string vacío (‘’) es interpretado como un string vacío en sentencias INSERT sobre datos varchar. Cuando concatenamos datos varchar, char, nvarchar, nchar, text o ntext, la cadena vacía es interpretada como tal. Por ejemplo, ‘abc’ + ‘’ + ‘def’ es convertida a ‘abcdef’. La cadena vacía nunca es evaluada como un valor nulo.

• Operadores Lógicos: Los operadores lógicos son AND, OR, y NOT. AND y OR son usados para conectar condiciones de búsqueda en cláusulas WHERE. NOT revierte el resultado de una condición de búsqueda.

Niveles de precedencia de los operadores Si usamos múltiples operadores (lógicos o aritméticos) para combinar expresones, SQL Server procesa estos operadores en orden de precedencia, lo que puede afectar el valor resultante. Los operadores tienen el siguiente orden de precedencia (desde mayor precedencia a menor precedencia).

• + (Positivo), - (Negativo), ~ (NOT) • * (Multiplicación), / (División), % (Modulo) • + (Sumar), (+ Concatenar), - (Restar) • =, >, <, >=, <=, <>, !=, !>, !< (Operadores de comparación) • ^ (OR Exclusivo), & (AND), | (OR) • NOT

FAGDUT 4 CCI


• AND • ALL, ANY, BETWEEN, IN, LIKE, OR, SOME • = (Asignación)

Deberemos usar paréntesis para cambiar la precedencia predefinida de los operadores

en una expresión. SQL Server procesa la expresión que esté más anidada primero. Cuando dos operadores en una expresión tienen el mismo nivel de precedencia, son procesados en orden de izquierda a derecha. Funciones Transact-SQL proporciona una gran cantidad de funciones que retornan información. Las funciones retornan valores que pueden ser utilizados en expresiones. Algunas funciones toman parámetros de entreda mientras que las otras retornan valores sin ninguna entrada. Las funciones pueden ser agrupadas en tres tipos:

• Funciones de Rowset, que retornan un objeto que puede ser utilizado en lugar de una tabla en sentencias Transact-SQL.

• Funciones agregadas, que retornan un solo elemento sumarizador desde un conjunto de valores.

• Funciones escalares. Las funciones escalares toman ninguno, uno o más argumentos de entrada.

La siguiente tabla lista las distintas categorías de las funciones escalares.

Categoría Función Configuración Retornan información de la configuración actual. Cursor Retornan información acerca de cursores. Fecha y Hora Realizan operaciones de Fecha y Hora. Matemática Realizan cálculos matemáticos sobre los valores de entrada y devuelven

valores de salida. Meta datos Retornan información acerca de las bases de datos o objetos de las bases

de datos. Seguridad Retornan información de usuarios, funciones de usuario, y permisos. Strings Realizan operaciones sobre Strings y devuelven Strings como argumentos

de salida. Sistema Realizan operaciones, como ser conversión de tipos, y retornan

información acerca de valores, objetos, o seteos de SQL Server. Estadísticas Retornan información estadística del servidor. Texto e Imágenes

Realizan operaciones sobre campos de texto e imágenes, y retornan información acerca de sus argumentos.

Sintaxis La sintaxis de la mayoría de las funciones es similar a la siguiente: Nombre_de_Función( argumentos ) , o sino: @@Nombre_de_Función EJEMPLOS DE FUNCIONES Ejemplo 1 El siguiente ejemplo muestra el uso de la función @@VERSION para obtener información de la versión del software SQL Server 2000:

FAGDUT 5 CCI


SELECT @@VERSION

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: ) Ejemplo 2 El siguiente fragmento de código usa la función @@TRANCOUNT para determinar si alguna transacción queda abierta antes de ejecutar una consulta o procedimiento almacenado. La función @@TRANCOUNT retorna un número que representa el número de transacciones que tenemos abiertas en ese momento.

IF (@@TRANCOUNT > 0) BEGIN

RAISERROR ( 'La tarea no puede ser ejecutada sin una transacción.', 10, 1)

RETURN END

Ejemplo 3 El siguiente script actualiza el apellido del empleado. Cuando cambiamos el apellido, podemos desear confirmar si hay filas donde realizar el update. Si no se encuentran filas a modificar, es preferible ser notificado que el cambio no tuvo éxito y tomar las medidas necesarias. La funcion @@ROWCOUNT returna un valor que representa el número de filas afectadas por la última consulta.

USE Northwind UPDATE Empleados SET Apellido = 'Rodriguez'

WHERE Apellido = ‘Perez’ IF (@@ROWCOUNT = 0) BEGIN

PRINT ‘Advertencia: no se actualizaron filas’ RETURN

END El resultado de este script es el siguiente: Advertencia: no se actualizaron filas Las funciones son comunmente utilizadas para convertir datos de fechas desde el formato de un país al de otro. Para cambiar formatos de fecha, usaremos la función CONVERT con la opción de estilo para determinar el formato de fecha que será retornado. Ejemplo 4 Este ejemplo demuestra cómo podemos usar la función CONVERT para convertir un valor DateTime retornado por la función GETDATE a un string varchar usando diferentes estilos. La función GETDATE retorna la fecha y hora del servidor.

SELECT 'ANSI:', CONVERT(varchar(30), GETDATE(), 102) AS Style UNION SELECT 'Japonés:', CONVERT(varchar(30), GETDATE(), 111) UNION SELECT 'Europeo:', CONVERT(varchar(30), GETDATE(), 113)

FAGDUT 6 CCI


El resultado es el siguiente: Style --------- ------------------------------ Europeo: 25 Feb 2006 19:29:44:893 Japonés: 25/02/2006 ANSI: 25.02.2006 (3 filas(s) afectadas) Ejemplo 5 El siguiente ejemplo retorna el nombre de usuario logueado y el nombre de la aplicación que está utilizando para la conexión actual: SELECT SUSER_SNAME(), APP_NAME() El resultado sería: ----------------------- ------------------------------ WXPPROF\Administrador Analizador de Consultas de SQL (1 fila(s) afectadas) ELEMENTOS DE CONTROL DE FLUJO DE TRANSACT-SQL Transact-SQL contiene varios elementos de lenguaje que controlan el flujo de ejecución de sentencias en scripts, lotes, y procedimientos almacenados. Bloques BEGIN…END Un bloque BEGIN…END enciera una serie de sentencias Transact-SQL que se tomarán como una unidad dentro del bloque. Sintaxis: BEGIN {Sentencia SQL | Bloque} END Bloques IF…ELSE Un bloque IF contiene una condición. Si la condición se evalúa como verdadera, entonces la sentencia o bloque siguiente a la palabra clave IF es ejecutado; de otra manera, la sentencia o bloque que sigue a la palabra clave ELSE es ejecutado. La palabra clave ELSE es opcional. Sintaxis:

IF Expresión_Booleana {Sentencia_SQL | Bloque}

[ELSE {Sentencia_SQL | Bloque}

Ejemplo: Este ejemplo determina si un cliente tiene órdenes antes de eliminar el mismo de la lista de clientes:

FAGDUT 7 CCI


USE Northwind GO

IF EXISTS (SELECT OrderID FROM Orders

WHERE CustomerID = 'frank') PRINT '*** No se puede eliminar el cliente ***'

ELSE BEGIN

DELETE Customers WHERE CustomerID = 'frank' PRINT '*** Customer eliminado ***'

END Construcciones WHILE Las construcciones WHILE ejecutan una sentencia o bloque de sentencias SQL repetidamente siempre y cuando la condición que albergan sea verdadera. Las sentencias BREAK y CONTINUE dentro de un bloque controlan la operación de las sentencias dentro del bucle WHILE. Una sentencia BREAK transfiere la ejecución a la primera sentencia siguiente del final del bucle (sentencia END). Una sentencia CONTINUE transfiere la ejecución de vuelta al inicio del bucle, saltándose las sentencias que siguen al CONTINUE. Sintaxis:

WHILE Expresión_Booleana {Sentencia_SQL | Bloque}

[BREAK | CONTINUE | Sentencia_SQL | Bloque ] […n] END

Ejemplo: Este ejemplo crea una tabla temporaria y luego declara una variable local, la cual es utilizada como un contador para insertar filas con valores incrementados en un bucle WHILE. La sentencia BREAK termina el bucle antes de que el contador llegue a su valor máximo.

CREATE TABLE #Test (Counter int, DateCreated datetime) GO DECLARE @counter int SET @counter = 1 WHILE @counter <= 10 BEGIN INSERT #Test VALUES (@counter, GETDATE()) IF (SELECT COUNT(*) FROM #Test) > 8 BEGIN PRINT 'Se llegó al máximo de filas' BREAK END SET @counter = @counter + 1 END

Sentencia RETURN La sentencia RETURN sale incondicioalmente de una consulta o procedimiento. La ejecución de RETURN es inmediata, y puede ser utilizada en cualquier punto para salir del procedimiento, lote, o bloque. Las sentencias siguientes a RETURN no son ejecutadas.

FAGDUT 8 CCI


Sintaxis: RETURN [Expresión_entera] , donde Expresión_entera es un valor opcional de retorno. Los procedimientos almacenados de sistema retornan un valor de 0, a menos que se especifique lo contrario. Un valor no cero es usualmente utilizado para indicar algún error. Ejemplo: En este ejemplo, usamos RETURN para salir si no se encontraron productos con su PrecioUnitario mayor que 30:

USE Northwind GO

SELECT NombreProd, PrecioUnitario FROM Productos WHERE PrecioUnitario > 30 IF @@ROWCOUNT = 0 RETURN . . . código para procesar la filas seleccionadas.

Comentarios Los comentarios son cadenas de caracteres de texto que no se ejecutan y están ubicados en el medio de las sentencias SQL para describir la acción que la senencia realiza. Los comentarios pueden ser utilizados en dos formas distintas – en linea con una sentencia SQL o como un bloque de comentarios. Comenarios en linea Podemos crear comentarios en linea usando dos guiones (--) para establecer un comentario aparte de una sentencia SQL. Transact-SQL ignora el texto a la derecha de los caracteres de comentario. Un comentario en linea puede ser utilizado de la misma forma que una sentencia, a continuación de una sentencia o al inicio de la línea a comentar. Si deseamos más de un comenario en un bloque, deberemos repetir los guiones (--) en cada línea. Ejemplo 1 El siguiente ejemplo muestra el uso de comentarios en una linea para explicar que se realiza un cálculo:

SELECT ProductName , (UnitsInStock - UnitsOnOrder) – Calcula Inventario , SupplierID FROM Northwind..Products

Ejemplo 2 Este ejemplo usa el segundo comentario para evitar la ejecución de una sección del lote:

SELECT ProductName , (UnitsInStock - UnitsOnOrder) – Calcula Inventario -- , SupplierID FROM Northwind..Products

FAGDUT 9 CCI


Comentarios en bloque Creamos comentarios en bloque ubicando el par de comentario (/*) al inicio del texto de comentario, y luego concluyendo el comentario con el par de comentario (*/) para cerrar el comentario. Utilizaremos en este caso comentarios de múltiples líneas. Ejemplo 3 Este ejemplo muestra un caso de comentarios multilíneas o en bloque:

/* ** Este código recupera todas las filas de la tabla Product ** y muestra el precio unitario, el precio unitario aumentado ** en 10%, el nombre del producto. */ SELECT UnitPrice, (UnitPrice * 1.1), ProductName FROM Northwind..Products

7.3 - DCL - LENGUAJE DE CONTROL DE DATOS Las sentencias DCL son utilizadas para cambiar los permisos asociados a una base de datos o función de usuario. La siguiente tabla describe los elementos DCL: Sentencia Descripción GRANT Crea una entrada en el sistema de seguridad que permite a un usuario

el trabajar con datos o ejecutar ciertos comandos Trasact-SQL. DENY Crea una entrada en el sistema de seguridad negando un permiso de

una cuenta de seguridad y previene que el usuario, grupo, o función herede el permiso a través de su pertenencia en grupos y funciones.

Nota: Por defecto, sólo miembros de las funciones sysadmin, dbcreator, db_owner, o db_securityadmin pueden ejecutar sentencias DCL. Ejemplo Este ejemplo garantiza el permiso de consulta sobre la tabla Productos a la función public:

USE Northwind GRANT SELECT ON Products TO public

FAGDUT 10 CCI


7.4 – DML – LENGUAJE DE MANIPULACIÓN DE DATOS Las sentencias DML son las encargadas de manipular los datos. Básicamente tenemos cuatro sentencia Transact-SQL básicas de DML: SELECT, INSERT, UPDATE y DELETE: SELECT Una sentencia SELECT en Transact-SQL nos permite recuperar datos existentes en una base de datos SQL Server. La mayoría de las sentencias SELECT describen las siguientes propiedades del conjunto de resultados:

• Las columnas a ser incluídas en el conjunto de resultados. • Las tablas desde donde el conjunto de resultados es obtenido. • Las condiciones que las filas deben cumplir para ser incluídas en el conjunto de

resultados. • La secuencia de ordenamiento de las filas en el conjunto de resultados.

Por ejemplo, la siguiente sentencia SELECT obtiene el ID de producto, nombre, y

precio unitario de todos los productos en la tabla Productos, cuyo precio unitario exceda los $40:

SELECT ProductID, ProductName, UnitPrice FROM Products WHERE UnitPrice > 40 ORDER BY UnitPrice ASC

La sentencia SELECT en el ejemplo previo, define cuáles valores de columna deben ser recuperados, y en la cláusula FORM identifica la tabla que contiene estas columnas. La cláusula WHERE limita el conjunto de resultados a los productos cuyo precio unitario (UnitPrice) sea mayor que $40. La cláusula ORDER BY especifica que el conjunto de resultados deberá ser ordenado en forma ascendente, basándose en los valores del campo UnitPrice.

La sentencia SELECT de Transact-SQL básicamente tiene la siguiente sintaxis:

SELECT lista_select [INTO nuevo_nombre_de_tabla] FROM lista_de_tablas [WHERE condiciones_de_búsqueda] [GROUP BY lista_agrupamientos] [HAVING condiciones_de_búsqueda] [ORDER BY lista_ordenamiento [ASC | DESC] ]

La cláusula SELECT La cláusula SELECT incluye la palabra clave SELECT en la lista de selección. La lista de selección es una serie de expresiones separadas por comas. Cada expresión define una columna en el conjunto de resultados. Las columnas en el conjunto de resultados aparecerán en el mismo orden que la secuencia de expresiones en la lista de selección. La lisa de selección puede contener las siguientes palabras clave:

• DISTINCT • TOP n • AS

FAGDUT 11 CCI


Distinct La palabra clave DISTINCT elimina filas duplicadas del conjunto de resultados. Por ejemplo, la tabla Orders de la base de datos Northwind contiene valores duplicados en la columna ShipCity. Para obtener una lista de los valores de ShipCity sin los valores duplicados, ingresaremos el siguiente código:

SELECT DISTINCT ShipCity, ShipRegion FROM Orders ORDER BY ShipCity

TOP n La palabra clave TOP n especifica que las primeras n filas de un conjunto de resultados sean retornados. Si se especifica ORDER BY, las filas son seleccionadas luego de que el conjunto de resultados es ordenado. n especifica el número de filas a retornar (a menos que usemos la palabra clave PERCENT). PERCENT especifica que el n por ciento de las filas en el conjunto de resultados sea retornado. Por ejemplo, el siguiente ejemplo retorna las primeras 10 ciudades en orden alfabético de la tabla Orders:

SELECT DISTINCT TOP 10 ShipCity, ShipRegion FROM Orders ORDER BY ShipCity

La palabra clave AS Podemos mejora la legibilidad de nuestras sentencias SELECT usando la palabra clave AS. Utilizando AS asignamos alias a nuestras tablas en la sección FROM. El siguiente ejemplo muestra el uso de AS:

USE pubs SELECT p.pub_id, p.pub_name FROM publishers AS p

Nota: Si se especifica un alias para una tabla en una sentencia SELECT, todas las referencias a dicha tabla en la sentencia Transact-SQL deben referenciar este alias en particular. La cláusula INTO La cláusula INTO nos permite especificar que el conjunto de resultados será usado para crear una nueva tabla con el nombre definido en la cláusula. Una sentencia SELECT..INTO puede ser usado para combinar datos de varias tablas o vistas en uns sóla tabla. También podemos usarla para crear una nueva tabla que contenga datos seleccionados de un servidor vinculado. El siguiente ejemplo usa una sentencia SELECT para obtener datos de las columnas FirstName y LastName de la tabla Employees:

SELECT FirstName, LastName INTO EmployeeNames FROM Employees

El conjunto de resultados que es generado por la sentencia crea una tabla llamada EmployeeNames. La nueva tabla contendrá la columnas FirstName y LastName, y estas columnas contendrán los valores de la tabla Employees. El conjunto de resultados no es mostrado en el panel de resultados a menos que específicamente consultemos la nueva tabla.

FAGDUT 12 CCI


La cláusula FROM La cláusula FORM es requerida en cada sentencia SELECT en donde los datos son obtenidos de tablas o vistas. Podemos utilizar la cláusula FORM para listar las tablas y vistas conteniendo las columnas referenciadas en la lista de selección y en la cláusula WHERE. Podemos dar a la tabla o vista alias de nombres usando la cláusula AS. Tambien podemos usar la cláusula FORM para realizar joins de tablas especificando las condiciones de Join en la cláusula ON. La cláusula FORM tiene la forma de una lista separada por coma, conteniendo nombres de tablas, vistas, y cláusulas JOIN. El siguiente SELECT usa la cláusula FORM para especificar la tabla Shippers:

SELECT * FROM Shippers

También podemos usar la cláusula FORM para especificar joins entre dos tablas o vistas. Más adelante veremos este caso. Las cláusulas WHERE, GROUP BY y HAVING Las cláusulas WHERE y HAVING en una sentencia SELECT controlan las filas que son utilizadas para construir el conjunto de resultados. Las cláusulas WHERE y HAVING son filtros. Especifican una serie de condiciones de búsqueda, y sólo aquellas filas que cumplen con los términos de las condiciones de búsqueda son utilizados para construir el conjunto de resultados. Estas filas que cumplen las condiciones de búsqueda se dicen que son calificadas para participar en el conjunto de resultados. Por ejemplo, en el siguiente SELECT, la cláusula WHERE retorna sólo filas cuya región sea el estado de Washington:

SELECT CustomerID, CompanyName FROM Northwind.dbo.Customers WHERE Region = 'WA'

La cláusula HAVING es típicamente utilizada en conjunto con la cláusula GROUP BY, aunque puede ser especificada sin un GROUP BY. La cláusula HAVING especifica más filtros que son aplicados luego que una cláusula WHERE ha realizado su acción de filtrado. La siguiente sentencia SELECT incluye una cláusula WHERE, una cláusula GROUP BY y una HAVING:

SELECT OrdD1.OrderID AS OrderID, SUM(OrdD1.Quantity) AS "Units Sold", SUM(OrdD1.UnitPrice * OrdD1.Quantity) AS Revenue

FROM [Order Details] AS OrdD1 WHERE OrdD1.OrderID in

(SELECT DISTINCT OrdD2.OrderID FROM [ Order Details] AS OrdD2 WHERE OrdD2.UnitPrice > $100) GROUP BY OrdD1.OrderID HAVING SUM(OrdD1.Quantity) > 100

En esta sentencia SELECT, la cláusula WHERE retorna sólo las órdenes que se hayan hecho sobre un producto cuyo precio unitario excede $100, y la cláusula HAVING restringe aún más el resultado mostrando sólo aquellas órdenes que incluyen más de 100 unidades. La cláusula GROUP BY limita las filas para cada valor diferente de la columna OrderID. La cláusula ORDER BY La cláusula ORDER BY ordena el conjunto de resultado por una o más columnas (con un máximo de 8060 bytes). Un ordenamiento puede ser ascendente (ASC) o descendente (DESC). Si no se especifica el tipo de ordenamiento, se asume ASC. Si más de una columna es nombrada en la cláusula ORDER BY, los ordenamientos son anidados.

FAGDUT 13 CCI


El siguiente SELECT ordena las filas de la tabla Title, primero por editorial (publisher), en orden desdendente, luego por tipo, en orden ascendente para cada editorial, y finalmente por precio (también ascendente), ya que DESC no es especificado.

USE Pubs GO

SELECT Pub_id, Type, Title_id, Price

FROM Titles ORDER BY Pub_id DESC, Type, Price

JOINS Usando Joins, podemos obtener datos de dos o más tablas basándonos en relaciones lógicas entre las tablas. Los Joins indican cómo SQL Server debería usar datos de una tabla para seleccionar filas en otra. Los Joins pueden ser especificados ya sea en la cláusula FROM como en la WHERE. Las condiciones de join se combinan con las condiciones WHERE y HAVING para controlar qué filas serán seleccionadas de las tablas referenciadas por el FROM. Especificando las condiciones de join en la cláusula FROM, sin embargo, las separa de otras condiciones de búsqueda que podrían ser especificadas en el WHERE, y es el método más recomendado para especificar joins. Cuando múltiples tablas son referenciadas en una sóla consulta, todas las referencias de columnas deben ser no ambiguas. El nombre de tabla debe ser usado para calificar cualquier nombre de columna que esté duplicada en dos o más tablas referenciadas por la misma consulta. La lista seleccionada por un join puede referenciar todas las columnas en las tablas unidas o cualquier subconjunto de las columnas. La lista seleccionada no requiere contener columnas para cada tabla en el join. Por ejemplo, en un join de tres tablas, sólo una tabla puede ser utilizada como puente desde una a otra tabla, y ninguna de las columnas de la tabla del medio debe ser especificada en la lista de selección. Aunque las condiciones de join normalmente usan signos de igual como operador de comparación, otros operadores pueden ser especificados. Las columnas usadas en la condición de join no necesitan tener el mismo nombre o ser del mismo tipo de datos. Si los tipos de datos no son idénticos, sin embargo, deben entonces ser compatibles o ser de tipos que SQL Server pueda convertir implícitamente. Si los tipos de datos no pueden ser implícitamente convertidos, la condición de join debe explícitamente convertir los tipos de datos usando la función CAST. Inner Joins Un inner join es un join en el cual los valores de las columnas que son unidas son comparados a través de un operador de comparación. En el estándar SQL-92, los inner joins pueden ser especificados tanto en el FROM como en el WHERE. Los inner joins son el único tipo joins que la especificación SQL-92 soporta tanto en el FROM como en el WHERE. Los joins especificados en el WHERE se llaman old-style joins (joins estilo antiguo). El siguiente SELECT usa un inner join para obtener datos de la tabla Publishers y la tabla Titles en la base de datos Pubs:

FAGDUT 14 CCI


SELECT t.Title, p.Pub_name

FROM Publishers AS p INNER JOIN Titles AS t ON p.Pub_id = t.Pub_id

ORDER BY Title ASC

La sentencia SELECT obtiene los datos de la columna Title de la tabla Titles (t) y de la columna Pub_name de la tabla Publishers (p). Ya que el SELECT usa un inner join, retornará sólo aquellas filas para las cuales haya un valor igual de las columnas unidas (p.pub_id y t.pub_id). Outer Joins SQL Server soporta tres tipos de outer joins: left, right y full. Todas las filas obtenidas de la tabla de la izquierda son referenciadas con un left outer join, y todas las filas de la tabla de la derecha son referenciadas en un right outer join. En un full outer join, todas las filas de ambas tablas son retornadas. SQL Server supports three types of outer joins: left, right, and full. All rows retrieved from the left table are referenced with a left outer join, and all rows from the right table are referenced in a right outer join. All rows from both tables are returned in a full outer join. Left Outer Joins Un conjunto de resultados generado por una sentencia SELECT que incluya un left outer join incluye todas las filas de la tabla referenciada del lado izquierdo. Las únicas filas obtenidas del lado derecho son las que cumplen con la condición de join. En el siguiente SELECT, un left outer join es utilizado para obtener el nombre y apellido del autor, y (cuando sea aplicable), los nombres de todos los editores (publishers) que se guardan en la misma ciudad de los autores:

USE Pubs GO

SELECT a.Au_fname, a.Au_lname, p.Pub_name

FROM Authors a LEFT OUTER JOIN Publishers p ON a.City = p.City ORDER BY p.Pub_name ASC, a.Au_lname ASC, a.Au_fname ASC

El conjunto de resultados de esta consulta lista el nombre de cada autor en la tabla Authors. El mismo incluirá sólo aquellos editores (publishers) que estén ubicados en las mismas ciudades que los autores. Si un editor no está ubicado en la misma ciudad que un autor, se retorna un valor nulo para la columna pub_name del conjunto de resultados. Right Outer Joins Un resultado generado por una sentencia SELECT que incluye un Right Outer Join, incluye todas las filas de la tabla referenciada a la derecha del RIGHT OUTER JOIN. Sólo las filas que cumplen la condición de join son devueltas del lado izquierdo. En el siguiente SELECT, un Right Outer Join es usado para recuperar una lista de editoriales (publishers) y los nombres y apellidos de los autores (Authors), si dichos autores se encuentran ubicados en las mismas ciudades que los editores:

FAGDUT 15 CCI


USE Pubs GO SELECT a.Au_fname, a.Au_lname, p.Pub_name

FROM Authors a RIGHT OUTER JOIN Publishers p ON a.City = p.City

ORDER BY p.Pub_name ASC, a.Au_lname ASC, a.Au_fname ASC El conjunto de resultados de esta consulta listará el nombre de cada uno de los editores en la tabla Publishers. El mismo incluirá sólo los autores ubicados en la misma ciudad que los editores. Si un autor no se encuentra en una ciudad de editores, un valor null es retornado para las columnas au_fname y au_lname. Full Outer Joins Un conjunto de resultados generado por un SELECT que incluye un Full Outer Join incluye todas las filas de ambas tablas, a pesar de que las tablas no tengan un valor en común definido en la condición de join. En la siguiente sentencia SELECT, se usa un full outer join para obtener la lista de los editores y los nombres y apellidos de los autores. USE Pubs GO SELECT a.Au_fname, a.Au_lname, p.Pub_name

FROM Authors a FULL OUTER JOIN Publishers p ON a.City = p.City

ORDER BY p.Pub_name ASC, a.Au_lname ASC, a.Au_fname ASC SUBCONSULTAS Una subconsulta es una sentencia SELECT que retorna un solo valor y que se encuentra anidada dentro de una instrucción SELECT, INSERT, UPDATE o DELETE, o dentro de otra subconsulta. Una subconsulta puede ser usada en cualquier lugar donde se espera una expresión.

En el siguiente ejemplo, una subconsulta es anidada en la cláusula WHERE de la sentencia SELECT principal: USE Northwind GO SELECT ProductName

FROM Products WHERE UnitPrice = (

SELECT UnitPrice FROM Products WHERE ProductName = 'Sir Rodney''s Scones'

) La sentencia SELECT embebida primero identifica el precio unitario para Sir Rodney’s Scones el cual es de $10. El valor de $10 es entonces usado en la consulta exterior para retornar el nombre de producto cuyo precio equivale a $10.-

Si una tabla aparece sólo en la subconsulta y no en la consulta exterior, entonces las columnas de esta tabla no pueden aparecer en la lista del SELECT de la consulta exterior.

FAGDUT 16 CCI


En algunas sentencias Transact-SQL, la subconsulta puede ser evaluada como si fuera una consulta independiente. Conceptualmente, la subconsulta puede ser substituída dentro de una consulta exterior (aunque no es necesariamente cómo SQL Server realmente procesa las sentencias Transact-SQL que tienen subconsultas). Tipos de Subconsultas Las subconsultas pueden ser especificadas en varios lugares dentro de la sentencia SELECT. Las sentencias que incluyen una subconsulta pueden tomar uno de estos formatos:

• WHERE <expresión> [NOT] IN (<subconsulta>) • WHERE <expresión> <op_de_comparación> [ANY | ALL] (<subconsulta>) • WHERE [NOT] EXISTS (<subconsulta>)

Subconsultas que usan IN El resultado de una subconsulta introducida con IN (o con NOT IN) es una lista de cero o más valores. Luego de que la subconsulta devuelva el resultado, la consulta exterior hace uso del mismo. En el siguiente ejemplo, una subconsulta es anidada dentro de la cláusula WHERE, y la palabra clave IN es utilizada:

USE Pubs GO

SELECT Pub_name

FROM Publishers WHERE Pub_id IN

( SELECT Pub_id FROM Titles WHERE Type = 'business'

) Podemos evaluar esta sentencia en dos pasos. Primero, la consulta interior retorna los

números de identificación de los editores (publishers) que tienen publicado libros (1389 y 0736). Segundo, esos valores son substituídos en la consulta exterior, la cual encuentra los nombres que concuerdan con los números de identificación de la tabla Publishers.

Las subconsultas generadas con las palabras clave NOT IN también retornan una lista de cero o más valores. La consulta es exactamente igual que las de IN, excepto que NOT IN reemplaza a IN. Subconsultas que usan operadores de comparación Los operadores de comparación que introduce una subconsulta pueden ser modificados por las palabras clave ALL o ANY. Las subconsultas introducidas con un operador de comparación modificado retornarán una lista de cero o más valores y pueden incluir una cláusula GROUP BY o HAVING. Estas subconsultas pueden ser reescritas usando EXISTS. Las palabras clave ALL y ANY comparan un valor escalar con un conjunto de valores de una columna. La palabra clave ALL se aplica a cualquier valor, y ANY aplica a al menos un valor.

FAGDUT 17 CCI


En el siguiente ejemplo, el operador (>) es usado con la palabra clave ANY:

USE Pubs GO

SELECT Title

FROM Titles WHERE Advance > ANY

( SELECT Advance

FROM Publishers INNER JOIN Titles ON Titles.Pub_id = Publishers.Pub_id

AND Pub_name = 'Algodata Infosystems' )

Esta sentencia encuentra todos los títulos que recibieron un avance más grande que el mínimo avance pagado por Algodata Infosystems (el cual es de $5000). La cláusula WHERE en el SELECT exterior contiene una subconsulta que usa un join para recuperar los montos de los avances para Algodata Infosystems. Este monto mínimo es usado para determinar cuáles titulos recuperar de la tabla Titles. Subconsultas que usan EXISTS y NOT EXISTS Cuando aparece una subconsulta con la palabra clave EXISTS, esta funciona como un test de existencia. La cláusula WHERE de la consulta exterior comprueba la existencia de filas retornadas por la subconsulta. La subconsulta no produce datos, realmente; en cambio, retorna un valor TRUE o FALSE. En el siguiente ejemplo, la cláusula WHERE en la sentencia SELECT exterior contiene la subconsulta y usa la palabra clave EXISTS:

USE Pubs GO

SELECT Pub_name

FROM Publishers WHERE EXISTS (

SELECT * FROM Titles WHERE Titles.Pub_id = Publishers.Pub_id AND Type = 'business'

) Para determinar el resultado de esta consulta, consideremos cada nombre de editor por turno. En este caso, el primer editor (publisher) es Algodata Infosystems, el cual tiene un número de identificación de 1389. Hay filas en la tabla Titles en las que pub_id sea 1389 y el tipo sea business? Si es así, Algodata Infosystems debería ser uno de los valores seleccionados. El mismo proceso es repetido para cada uno de los otros editores (publishers). Las palabras claves NOT EXISTS funcionan en forma análoga a EXISTS, excepto que la cláusula WHERE en la cual NOT EXISTS es utilizada es satisfecha si la subconsulta no devuelve ninguna fila.

FAGDUT 18 CCI


INSERT La sentencia INSERT agrega una o más filas a una tabla. En su forma simplificada, una sentencia INSERT tiene la siguiente forma:

INSERT [INTO] tabla_o_vista [(lista_de_columnas)] datos Este comando causa que los valores de datos (datos) sean insertados como una o más filas en la tabla o vista especificada. La lista de nombres de columna (lista_de_columnas), las cuales se encuentran separadas, pueden ser usadas para especificar las columnas a las que se insertarán datos. Si sólo una lista parcial es especificada, un valor NULL o un valor del default correspondiente (si existe un DEFAULT) es insertada en cada columna no especificada en la lista. Todas las columnas no especificadas en la lista de columnas deben permitir nulos o tener un default asociado. A su vez, una sentencia INSERT no especifica valores para los siguientes tipos de columnas (SQL Server genera automáticamente los mismos):

• Columnas con la propiedad IDENTITY. • Columnas con una definición de DEFAULT que usa la función NEWID(). • Columnas computadas.

Nota: La palabra clave INTO de una sentencia INSERT es opcional y es utilizado sólo para hacer el código más claro. Los valores de datos se especifican en la lista de columnas. Al definir un INSERT, podemos usar la cláusula VALUES para especificar los valores de datos para una fila, o podemos usar una subconsulta SELECT para especificar los valores de datos para una o más filas. Uso de INSERT para agregar datos La cláusula VALUES nos permite especificar los valores de una fila en una tabla. Los valores son especificados como una lista de expresiones escalares separadas por comas. Si no se especifica una lista de columnas, los valores deberán ser especificados en la misma secuencia de las columnas de la tabla o vista. Por ejemplo, supongamos que creamos la siguiente tabla en la base de datos Pubs:

USE Pubs GO

CREATE TABLE NewBooks

( BookID INT IDENTITY(1,1) NOT NULL,

BookTitle VARCHAR(80) NOT NULL, BookType CHAR(12) NOT NULL

CONSTRAINT [booktype_df] DEFAULT ('Undecided'), PubCity VARCHAR(50) NULL

Una vez creada la tabla, decidimos agregar datos a la misma. La siguiente sentencia INSERT usa una cláusula VALUES para insertar una nueva fila en la tabla NewBooks.

USE Pubs GO

INSERT INTO NewBooks (BookTitle, PubCity)

VALUES ('Life Without Fear', 'Chicago')

FAGDUT 19 CCI


En esta sentencia, los valores son definidos para las columnas BookTitle y PubCity. No necesitaremos incluir la columna BookID en el INSERT, ya que dicha columna fue especificada como IDENTITY. Por lo tanto, los valores de esta columna son generados automáticamente. A su vez, ya que no definimos un valor para la columna BookType, SQL Server automáticamente inserta el valor por defecto en la columna cuando corremos el INSERT. Usar una subconsulta para agregar datos Podemos usar una subconsulta SELECT dentro de un INSERT para agregar valores a una tabla desde una o más tablas o vistas. Una subconsulta nos permite agregar más de una fila por vez. Nota: Una subconsulta SELECT es usada para agregar subconjuntos de datos existentes a una tabla, mientras que la cláusula VALUES es utilizada en sentencias INSERT para agregar nuevos datos a una tabla. La siguiente sentencia INSERT usa una subconsulta SELECT para insertar filas en la tabla NewBooks:

USE Pubs GO

INSERT INTO NewBooks (BookTitle, BookType)

SELECT Title, Type FROM Titles WHERE Type = 'mod_cook'

Esta sentencia INSERT usa la salida del SELECT para proporcionar los datos que

serán insertados en la tabla NewBook.

UPDATE El comando UPDATE puede cambiar valores de datos en filas, grupos de filas, o en todas las filas de una tabla o vista. También podemos usar este comando para actualizar filas en un servidor remoto usando un servidor vinculado o las funciones OPENROWSET, OPENDATASOURCE y OPENQUERY. Un UPDATE que referencia una tabla o vista puede cambiar los datos en una sóla tabla base por vez. Nota: Un UPDATE se considera exitoso sólo si el nuevo valor es compatible con el tipo de datos de la columna seleccionada, y si también se adhiere a todas las restricciones que se aplican a esa columna. La sentencia UPDATE tiene tres cláusulas principales:

• SET • WHERE • FROM

Usando SET para modificar datos La cláusula SET especifica las columnas que serán cambiadas y los nuevos valores de las columnas. Los valores en las columnas específicas son actualizadas con los valores datos en la cláusula SET en todas las filas que cumplan la condición del WHERE. Si no se especifica un WHERE, todas las filas serán modificadas. Por ejemplo, el siguiente UPDATE incluye la cláusula SET necesaria para incrementar los precios de los libros en la tabla NewBooks por un 10 por ciento.

FAGDUT 20 CCI


USE Pubs GO

UPDATE NewBooks

SET Price = Price * 1.1 En esta sentencia, no se especificó la cláusula WHERE, por lo que todos las filas de la

tabla serán actualizadas (a menos que la columna Price contenga un valor nulo (null).

Usando WHERE para modificar datos La cláusula WHERE realiza dos funciones:

• Especifica las filas a actualizar. • Indica las filas de las tablas de origen que califican para suministrar valores para la

actualización si se especifica una cláusula FROM.

Si no se especifica un WHERE, todas las filas de la tabla son modificadas. En el siguiente UPDATE, la cláusula WHERE es usada para limitar la actualización a sólo aquellas filas que cumplen con la condición dada por el mismo:

USE Pubs GO

UPDATE NewBooks

SET BookType = 'popular' WHERE BookType = 'popular_comp'

Usando FROM para modificar datos Podemos usar la cláusula FROM para obtener datos de una o más tablas o vista y actualizar una tabla que deseemos. Por ejemplo, en el siguiente UPDATE, la cláusula FROM incluye un inner join que une las tablasTitles y NewBooks:

USE Pubs GO

UPDATE NewBooks

SET Price = Titles.Price FROM NewBooks JOIN Titles

ON NewBooks.BookTitle = Titles.Title En esta sentencia UPDATE, los valores de Precio (Price) de la tabla NewBooks son actualizados con los mismos valores de la tabla Titles. DELETE Una sentencia DELETE remueve una o más filas de una tabla o vista. La siguiente es la sintaxis simplificada de un DELETE:

DELETE tabla_o_vista FROM tabla WHERE condicion_de_busqueda

En tabla_o_vista nombramos la tabla o vista a la que se le eliminarán filas. Todas las filas en una tabla que cumplen con el WHERE son eliminadas. Si no se especifica WHERE, todas las filas serán eliminadas. La cláusula FROM especifica tablas adicionales o vistas y condiciones de join que pueden ser usadas como predicados en la cláusula WHERE para calificar las filas a ser eliminadas por el DELETE. Las filas no son eliminadas en las tablas nombradas por la cláusula FROM, sólo las tablas nombradas en la cláusula DELETE.

FAGDUT 21 CCI


Una tabla que tiene todas sus filas removidas permanece en la base de datos. Para eliminar una tabla usaremos DROP TABLE. Consideremos el siguiente DELETE:

USE Pubs GO

DELETE NewBooks

FROM Titles WHERE NewBooks.BookTitle = Titles.Title AND Titles.Royalty = 10

En esta sentencia, las filas son eliminadas de la tabla NewBooks si el Royalty de cada

libro es del 10%.

Uso de TRUNCATE TABLE La sentencia TRUNCATE TABLE es un método rápido y no logueado en el registro de transacciones. El método es casi siempre más rápido que la sentencia DELETE sin condiciones, ya que DELETE registra cada eliminación, mientras que TRUNCATE TABLE registra sólo la liberación de las páginas de datos. TRUNCATE TABLE libera inmediatamente todo el espacio ocupado por una tabla y sus índices. La siguiente sentencia TRUNCATE TABLE elimina todas las filas de la tabla NewBooks en la base de datos Pubs:

USE Pubs GO

TRUNCATE TABLE NewBooks

Como con una sentencia DELETE, la definición de la tabla permanece en la base de datos luego de usar TRUNCATE TABLE (así como sus indices y otros objetos asociados). La sentencia DROP TABLE es necesaria para eliminar una tabla por completo.

FAGDUT 22 CCI

Documents

M_dulo 07 Curso SQL Server