• Author / Uploaded
• JuanReyes

• Categories
• Tabla (base de datos)
• SQL
• Servidor SQL de Microsoft
• Bases de datos
• Modelo de datos

Citation preview

íNDICE ÍNDICE..................................................................................................... 1 PRESENTACIÓN......................................................................................... 6 1 - OBJETIVOS Y ALCANCES DEL TUTORIAL DE.............................................7 2 - CREAR UNA TABLA (CREATE TABLE - SP_TABLES - SP_COLUMNS - DROP TABLE)..................................................................................................... 8 3 - INSERTAR Y RECUPERAR REGISTROS DE UNA TABLA (INSERT INTO SELECT).................................................................................................. 12 4 - TIPOS DE DATOS BÁSICOS...................................................................14 5 - RECUPERAR ALGUNOS CAMPOS (SELECT).............................................17 6 - RECUPERAR ALGUNOS REGISTROS (WHERE).........................................20 7- OPERADORES RELACIONALES...............................................................22 8 - BORRAR REGISTROS (DELETE).............................................................25 9 - ACTUALIZAR REGISTROS (UPDATE)......................................................28 10 - COMENTARIOS.................................................................................. 32 11 - VALORES NULL (IS NULL)..................................................................33 12 - CLAVE PRIMARIA..............................................................................38 13 - CAMPO CON ATRIBUTO IDENTITY.......................................................41 14 - OTRAS CARACTERÍSTICAS DEL ATRIBUTO IDENTITY.............................44 15 - TRUNCATE TABLE.............................................................................. 48 16 - OTROS TIPOS DE DATOS EN SQL SERVER............................................51 17 - TIPO DE DATO (TEXTO)......................................................................51 18 - TIPO DE DATO (NUMÉRICO)...............................................................55 19 - TIPO DE DATO (FECHA Y HORA).........................................................59 20 - INGRESAR ALGUNOS CAMPOS (INSERT INTO)......................................61 21 - VALORES POR DEFECTO (DEFAULT).....................................................63 1

22 - COLUMNAS CALCULADAS (OPERADORES ARITMÉTICOS Y DE CONCATENACIÓN)...................................................................................67 23 - ALIAS..............................................................................................69 25 - FUNCIONES PARA EL MANEJO DE CADENAS.........................................71 26 - FUNCIONES MATEMÁTICAS................................................................75 27 - FUNCIONES PARA EL USO DE FECHAS Y HORAS...................................77 28 - ORDENAR REGISTROS (ORDER BY).....................................................80 29 - OPERADORES LÓGICOS ( AND - OR - NOT)..........................................83 30 - OTROS OPERADORES RELACIONALES (IS NULL)..................................87 31 - OTROS OPERADORES RELACIONALES (BETWEEN)................................89 32 - OTROS OPERADORES RELACIONALES (IN)...........................................92 33 - BÚSQUEDA DE PATRONES (LIKE - NOT LIKE).......................................94 34 - CONTAR REGISTROS (COUNT)............................................................98 35 - CONTAR REGISTROS (COUNT_BIG)....................................................100 36 - FUNCIONES DE AGRUPAMIENTO (COUNT - SUM - MIN - MAX - AVG).....103 37 - AGRUPAR REGISTROS (GROUP BY)...................................................106 38 - SELECCIONAR GRUPOS (HAVING).....................................................111 39 - MODIFICADOR DEL GROUP BY (WITH ROLLUP)..................................116 40 - MODIFICADOR DEL GROUP BY (WITH CUBE)......................................121 41 - FUNCIÓN GROUPING.......................................................................125 42 - CLÁUSULAS COMPUTE Y COMPUTE BY..............................................128 43 - REGISTROS DUPLICADOS (DISTINCT)................................................132 44 - CLÁUSULA TOP...............................................................................137 45 - CLAVE PRIMARIA COMPUESTA..........................................................140 46 - INTEGRIDAD DE LOS DATOS.............................................................143 47 - RESTRICCIÓN DEFAULT....................................................................144 48 - RESTRICCIÓN CHECK.......................................................................148 2

49 - DESHABILITAR RESTRICCIONES (WITH CHECK - NOCHECK).................153 50 - RESTRICCIÓN PRIMARY KEY.............................................................156 51 - RESTRICCIÓN UNIQUE.....................................................................160 52 - INFORMACIÓN DE RESTRICCIONES (SP_HELPCONSTRAINT)................162 53 - ELIMINAR RESTRICCIONES (ALTER TABLE - DROP)..............................164 54 - CREAR Y ASOCIAR REGLAS (CREATE RULE - SP_BINDRULE).................166 55 - ELIMINAR Y DASASOCIAR REGLAS (SP_UNBINDRULE - DROP RULE).....173 56 - INFORMACIÓN DE REGLAS (SP_HELP - SP_HELPCONSTRAINT)............176 57 - VALORES PREDETERMINADOS (CREATE DEFAULT)..............................178 58 - DESASOCIAR Y ELIMINAR VALORES PREDETERMINADOS....................183 59 - INFORMACIÓN DE VALORES PREDETERMINADOS...............................186 60 - INDICES......................................................................................... 189 61 - INDICES AGRUPADOS Y NO AGRUPADOS (CLUSTERED Y NONCLUSTERED) ............................................................................................................ 190 62 - CREACIÓN DE ÍNDICES..................................................................... 190 63 - REGENERAR ÍNDICES.......................................................................194 64 - ELIMINAR ÍNDICES..........................................................................197 65 - TRABAJAR CON VARIAS TABLAS.......................................................199 66 - COMBINACIÓN INTERNA (INNER JOIN)...............................................200 67 - COMBINACIÓN EXTERNA IZQUIERDA (LEFT JOIN)...............................203 68 - COMBINACIÓN EXTERNA DERECHA (RIGHT JOIN)...............................207 69 - COMBINACIÓN EXTERNA COMPLETA (FULL JOIN)...............................210 70 - COMBINACIONES CRUZADAS (CROSS JOIN).......................................212 71 - AUTOCOMBINACIÓN........................................................................215 72 - COMBINACIONES Y FUNCIONES DE AGRUPAMIENTO..........................218 73 - COMBINACIÓN DE MÁS DE DOS TABLAS............................................220 74 - COMBINACIONES CON UPDATE Y DELETE..........................................224 3

75 - CLAVE FORÁNEA.............................................................................226 76 - RESTRICCIONES (FOREIGN KEY).......................................................226 77 - RESTRICCIONES FOREIGN KEY EN LA MISMA TABLA...........................230 78 - RESTRICCIONES FOREIGN KEY (ACCIONES).......................................233 79 - RESTRICCIONES FOREIGN KEY DESHABILITAR Y ELIMINAR (WITH CHECK NOCHECK)............................................................................................ 238 80 - RESTRICCIONES FOREIGN KEY (INFORMACIÓN).................................244 81 - RESTRICCIONES AL CREAR LA TABLA................................................244 82 - UNIÓN............................................................................................ 249 83 - AGREGAR Y ELIMINAR CAMPOS ( ALTER TABLE - ADD - DROP)............252 84 - ALTERAR CAMPOS (ALTER TABLE - ALTER).........................................255 85 - AGREGAR CAMPOS Y RESTRICCIONES (ALTER TABLE).........................257 86 - CAMPOS CALCULADOS....................................................................260 87 - TIPO DE DATO DEFINIDO POR EL USUARIO (CREAR - INFORMACION)...262 88 - TIPO DE DATO DEFINIDO POR EL USUARIO (ASOCIACIÓN DE REGLAS). 264 89 - TIPO DE DATO DEFINIDO POR EL USUARIO (VALORES PREDETERMINADOS).............................................................................269 90 - TIPO DE DATO DEFINIDO POR EL USUARIO (ELIMINAR)......................276 91 - SUBCONSULTAS..............................................................................279 92 - SUBCONSULTAS COMO EXPRESIÓN...................................................280 93 - SUBCONSULTAS CON IN...................................................................282 4 - SUBCONSULTAS ANY - SOME - ALL.....................................................286 95 - SUBCONSULTAS CORRELACIONADAS................................................289 96 - EXISTS Y NO EXISTS........................................................................ 292 97 - SUBCONSULTA SIMIL AUTOCOMBINACIÓN.........................................295 98 - SUBCONSULTA EN LUGAR DE UNA TABLA..........................................297 99 - SUBCONSULTA (UPDATE - DELETE)...................................................301 100 - SUBCONSULTA (INSERT)................................................................303 4

101 - CREAR TABLA A PARTIR DE OTRA (SELECT - INTO)...........................306 102 - GO............................................................................................... 310 103 - VISTAS......................................................................................... 311 104 - VISTAS (INFORMACIÓN).................................................................317 105 - VISTAS (ENCRIPTAR).....................................................................320 106 - VISTAS (ELIMINAR)........................................................................ 321 107 - VISTAS (WITH CHECK OPTION).......................................................323 108 - VISTAS (MODIFICAR DATOS DE UNA TABLA A TRAVÉS DE VISTAS).....326 109 - VISTAS MODIFICAR (ALTER VIEW)...................................................331 110 - LENGUAJE DE CONTROL DE FLUJO (CASE)........................................335 111 - LENGUAJE DE CONTROL DE FLUJO (IF).............................................340 112 - VARIABLES DE USUARIO................................................................343 113 - TIPOS DE DATOS TEXT, NTEXT Y IMAGE..........................................346 114 - TIPO DE DATO TEXT - NTEXT E IMAGE (PUNTEROS)..........................349 115 - TIPO DE DATO TEXT - NTEXT E IMAGE (LEER)..................................351 116 - TIPO DE DATO TEXT - NTEXT E IMAGE (ESCRIBIR)............................352 117 - TIPO DE DATO TEXT - NTEXT E IMAGE (ACTUALIZAR).......................356 118 - TIPO DE DATO TEXT - NTEXT E IMAGE (FUNCIONES).........................360 119 - PROCEDIMIENTOS ALMACENADOS..................................................362 120 - PROCEDIMIENTOS ALMACENADOS (CREAR - EJECUTAR)....................363 121 - PROCEDIMIENTOS ALMACENADOS (ELIMINAR)................................366 122 - PROCEDIMIENTOS ALMACENADOS (PARÁMETROS DE ENTRADA).......368 123 - PROCEDIMIENTOS ALMACENADOS (PARÁMETROS DE SALIDA)..........374 124 - PROCEDIMIENTOS ALMACENADOS (RETURN)..................................378 125 - PROCEDIMIENTOS ALMACENADOS (INFORMACIÓN).........................382 126 - PROCEDIMIENTOS ALMACENADOS (ENCRIPTADO)............................385 127 - PROCEDIMIENTOS ALMACENADOS (MODIFICAR)..............................386 5

128 - PROCEDIMIENTOS ALMACENADOS (INSERTAR)................................388 129 - PROCEDIMIENTOS ALMACENADOS (ANIDADOS)...............................391 130 - PROCEDIMIENTOS ALMACENADOS (RECOMPILAR)...........................393 131 - PROCEDIMIENTOS ALMACENADOS (CON JOIN).................................394 132 - TABLAS TEMPORALES....................................................................394 133 - FUNCIONES..................................................................................395 134 - FUNCIONES (DROP).......................................................................395 135 - FUNCIONES ESCALARES (CREAR Y LLAMAR)....................................396 136 - FUNCIONES DE TABLA DE VARIAS INSTRUCCIONES..........................402 137 - FUNCIONES CON VALORES DE TABLA EN LÍNEA...............................406 138 - FUNCIONES (MODIFICAR)..............................................................408 139 - FUNCIONES (ENCRIPTADO)............................................................410 140 - FUNCIONES (INFORMACIÓN)..........................................................411 141 - DISPARADORES (TRIGGERS)...........................................................412 142 - DISPARADOR DE INSERCIÓN (INSERT TRIGGER)...............................414 143 - DISPARADOR DE BORRADO (DELETE TRIGGER)................................419 144 - DISPARADOR DE ACTUALIZACIÓN (UPDATE TRIGGER)......................424 145 - DISPARADORES (VARIOS EVENTOS)................................................429 146 - DISPARADOR (INSTEAD OFF Y AFTER)............................................432 147 - DISPARADOR (ELIMINAR)...............................................................438 148 - DISPARADOR (INFORMACIÓN)........................................................438 149 - DISPARADOR (MODIFICAR)............................................................440 150 - DISPARADOR (DESHABILITAR Y HABILITAR).....................................441 151 - DISPARADOR (WITH ENCRYPTION)..................................................444 152 - DISPARADOR (CONDICIONALES).....................................................446 DIRECCIÓN WEB DEL CURSO..................................................................449

6

SQL Server Ya Un curso de Tutoriales ASP.Net MVC Ya C# Ya VB.Net Ya Donar al sitio Políticas de privacidad. Este texto fue escrito por uLoVe-TeCh y esta bajo la licencia de Creative Commons.

7

Presentación SQL Server Ya desde CERO. El tutorial está pensado para que pueda ser desarrollado por una persona que conoce muy poco de programación. El objetivo de este sitio es poder aprender Microsoft SQL Server en forma sencilla viendo un concepto teórico, luego algunos ejercicios resueltos y por último y lo más importante efectuar una serie de ejercicios. Puede desarrollar los ejercicios en el sitio, probarlos y ver los resultados. Un conocimiento profundo de SQL Server nos facilitará el desarrollo de páginas dinámicas con ASP y ASP.NET que acceden a una base de datos.

8

1 - Objetivos y alcances del tutorial de El curso brinda un concepto teórico corto, luego un problema resuelto que invito a ejecutar, modificar y jugar con el mismo. Por último, y lo más importante, una serie de ejercicios propuestos que nos permitirá saber si podemos aplicar el concepto. La única herramienta que necesitamos inicialmente es este sitio ya que podrá ejecutar todos los problemas como son la creación de tablas, insert, delete, update, definición de índices y restricciones, creación y ejecución de procedimientos almacenados, vistas, subconsultas, creación de trigger etc. La única restricción es que todos los visitantes de este sitio comparten la misma base de datos llamada: wi520641_sqlserverya (este nombre un poco singular se debe a que las empresas de hosting es la que lo define) Siempre que lancemos un comando SQL en el sitio www.sqlserverya.com.ar estaremos accediendo a la base de datos wi520641_sqlserverya.

9

2 - Crear una tabla (create table - sp_tables sp_columns - drop table) Una base de datos almacena su información en tablas. Una tabla es una estructura de datos que organiza los datos en columnas y filas; cada columna es un campo (o atributo) y cada fila, un registro. La intersección de una columna con una fila, contiene un dato específico, un solo valor. Cada registro contiene un dato por cada columna de la tabla. Cada campo (columna) debe tener un nombre. El nombre del campo hace referencia a la información que almacenará. Cada campo (columna) también debe definir el tipo de dato que almacenará. Las tablas forman parte de una base de datos. Nosotros trabajaremos con la base de datos llamada wi520641_sqlserverya (este nombre se debe a que las empresas de hosting es la que lo define), que ya he creado en el servidor sqlserverya.com.ar. Para ver las tablas existentes creadas por los usuarios en una base de datos usamos el procedimiento almacenado "sp_tables @table_owner='dbo';": sp_tables @table_owner='dbo';

El parámetro @table_owner='dbo' indica que solo muestre las tablas de usuarios y no las que crea el SQL Server para administración interna. Finalizamos cada comando con un punto y coma. Al crear una tabla debemos resolver qué campos (columnas) tendrá y que tipo de datos almacenarán cada uno de ellos, es decir, su estructura. La sintaxis básica y general para crear una tabla es la siguiente: create table NOMBRETABLA( NOMBRECAMPO1 TIPODEDATO, ... NOMBRECAMPON TIPODEDATO );

La tabla debe ser definida con un nombre que la identifique y con el cual accederemos a ella. Creamos una tabla llamada "usuarios" y entre paréntesis definimos los campos y sus tipos: create table usuarios ( nombre varchar(30), clave varchar(10) );

Cada campo con su tipo debe separarse con comas de los siguientes, excepto el último.

10

Cuando se crea una tabla debemos indicar su nombre y definir al menos un campo con su tipo de dato. En esta tabla "usuarios" definimos 2 campos:  nombre: que contendrá una cadena de caracteres de 30 caracteres de longitud, que almacenará el nombre de usuario y  clave: otra cadena de caracteres de 10 de longitud, que guardará la clave de cada usuario. Cada usuario ocupará un registro de esta tabla, con su respectivo nombre y clave. Para nombres de tablas, se puede utilizar cualquier caracter permitido para nombres de directorios, el primero debe ser un caracter alfabético y no puede contener espacios. La longitud máxima es de 128 caracteres. Si intentamos crear una tabla con un nombre ya existente (existe otra tabla con ese nombre), mostrará un mensaje indicando que ya hay un objeto llamado 'usuarios' en la base de datos y la sentencia no se ejecutará. Esto es muy importante ya que cuando haga los ejercicios en este sitio puede haber otra persona que haya creado una tabla con el nombre que usted especifique. Para ver la estructura de una tabla usamos el procedimiento almacenado "sp_columns" junto al nombre de la tabla: sp_columns usuarios;

aparece mucha información que no analizaremos en detalle, como el nombre de la tabla, su propietario, los campos, el tipo de dato de cada campo, su longitud, etc.: ...COLUMN_NAME TYPE_NAME LENGHT _______________________________________ nombre varchar 30 clave varchar 10

Para eliminar una tabla usamos "drop table" junto al nombre de la tabla a eliminar: drop table usuarios;

Si intentamos eliminar una tabla que no existe, aparece un mensaje de error indicando tal situación y la sentencia no se ejecuta. Para evitar este mensaje podemos agregar a la instrucción lo siguiente: if object_id('usuarios') is not null drop table usuarios;

En la sentencia precedente especificamos que elimine la tabla "usuarios" si existe.

Vamos a crear una tabla llamada "usuarios". En primer lugar vamos a eliminar la tabla "usuarios" averiguando si existe (a esto vamos a repetirlo siempre porque puede haber otro usuario que haya creado una tabla con el mismo nombre): if object_id('usuarios') is not null drop table usuarios;

Recordar que debemos finalizar cada comando con un punto y coma. La tabla "usuarios" contendrá los siguientes campos: 11

- nombre: varchar de 30 caracteres de longitud, - clave: varchar de 10 caracteres de longitud.

Ahora si creamos la tabla: create table usuarios ( nombre varchar(30), clave varchar(10) );

aparece un mensaje indicando que el comando se completó exitosamente. Veamos las tablas existentes: sp_tables @table_owner='dbo';

Veamos la estructura de la tabla "usuarios": sp_columns usuarios;

aparece mucha información que no analizaremos en detalle, como el nombre de la tabla, su propietario, los campos y sus tipos de datos, su longitud, etc.: ...COLUMN_NAME TYPE_NAME LENGHT... _______________________________________ nombre varchar 30 clave varchar 10

Intentemos crear una tabla con el mismo nombre, mostrará un mensaje indicando que ya hay un objeto llamado 'usuarios' en la base de datos y la sentencia no se ejecutará: create table usuarios ( nombre varchar(30), clave varchar(10) );

Eliminemos la tabla: drop table usuarios;

Verifiquemos si se ha eliminado: sp_tables @table_owner='dbo';

no debe aparecer la tabla "usuarios".

problema: Necesita almacenar los datos de sus amigos en una tabla. Los datos que guardará serán: apellido, nombre, domicilio y teléfono. 1- Elimine la tabla "agenda" si existe: if object_id('agenda') is not null drop table agenda; 2- Intente crear una tabla llamada "/agenda": create table /agenda( apellido varchar(30), nombre varchar(20),

12

domicilio varchar(30), telefono varchar(11) ); aparece un mensaje de error porque usamos un caracter inválido ("/") para el nombre. 3- Cree una tabla llamada "agenda", debe tener los siguientes campos: apellido, varchar(30); nombre, varchar(20); domicilio, varchar (30) y telefono, varchar(11): create table agenda( apellido varchar(30), nombre varchar(20), domicilio varchar(30), telefono varchar(11) ); 4- Intente crearla nuevamente. Aparece mensaje de error. 5- Visualice las tablas existentes (sp_tables @table_owner='dbo'). 6- Visualice la estructura de la tabla "agenda" (sp_columns). 7- Elimine la tabla. 8- Intente eliminar la tabla, sin controlar si existe. Debe aparecer un mensaje de error.

3 - Insertar y recuperar registros de una tabla (insert into - select) Un registro es una fila de la tabla que contiene los datos propiamente dichos. Cada registro tiene un dato por cada columna (campo). Nuestra tabla "usuarios" consta de 2 campos, "nombre" y "clave". Al ingresar los datos de cada registro debe tenerse en cuenta la cantidad y el orden de los campos. La sintaxis básica y general es la siguiente: insert into NOMBRETABLA (NOMBRECAMPO1, ..., NOMBRECAMPOn) values (VALORCAMPO1, ..., VALORCAMPOn);

Usamos "insert into", luego el nombre de la tabla, detallamos los nombres de los campos entre paréntesis y separados por comas y luego de la cláusula "values" colocamos los valores para cada campo, también entre paréntesis y separados por comas. Para agregar un registro a la tabla tipeamos: insert into usuarios (nombre, clave) values ('Mariano','payaso');

Note que los datos ingresados, como corresponden a cadenas de caracteres se colocan entre comillas simples. Para ver los registros de una tabla usamos "select": 13

select * from usuarios;

El comando "select" recupera los registros de una tabla. Con el asterisco indicamos que muestre todos los campos de la tabla "usuarios". Es importante ingresar los valores en el mismo orden en que se nombran los campos: insert into usuarios (clave, nombre) values ('River','Juan');

En el ejemplo anterior se nombra primero el campo "clave" y luego el campo "nombre" por eso, los valores también se colocan en ese orden. Si ingresamos los datos en un orden distinto al orden en que se nombraron los campos, no aparece un mensaje de error y los datos se guardan de modo incorrecto. En el siguiente ejemplo se colocan los valores en distinto orden en que se nombran los campos, el valor de la clave (la cadena "Boca") se guardará en el campo "nombre" y el valor del nombre (la cadena "Luis") en el campo "clave": insert into usuarios (nombre,clave) values ('Boca','Luis');

Problema: Vamos a crear una tabla llamada "usuarios". En primer lugar vamos a eliminar la tabla "usuarios" averiguando si existe (recuerde que debemos repetir siempre esto porque puede haber otro usuario que haya creado una tabla con el mismo nombre en el servidor www.sqlserverya.com.ar: if object_id('usuarios') is not null drop table usuarios;

Recordar que debemos finalizar cada comando con un punto y coma. Creamos la tabla: create table usuarios( nombre varchar(30), clave varchar(10) );

Agregamos un registro a la tabla: insert into usuarios (nombre, clave) values ('Mariano','payaso');

Veamos si el registro se guardó: select * from usuarios;

Ingresemos otro registro alterando el orden de los campos: insert into usuarios (clave, nombre) values ('River','Juan');

Veamos cómo SQL Server almacenó los datos: select * from usuarios;

Ingresemos otro registro colocando los valores en distinto orden en que se nombran los campos: 14

insert into usuarios (nombre,clave) values ('Boca','Luis');

Veamos cómo se guardaron los datos: select * from usuarios;

Note que la cadena "Boca" se almacenó en el campo "nombre" y la cadena "Luis" en el campo "clave".

problema: Trabaje con la tabla "agenda" que almacena información de sus amigos. 1- Elimine la tabla "agenda", si existe: if object_id('agenda') is not null drop table agenda; 2- Cree una tabla llamada "agenda". Debe tener los siguientes campos: apellido (cadena de 30), nombre (cadena de 20), domicilio (cadena de 30) y telefono (cadena de 11): 3- Visualice las tablas existentes para verificar la creación de "agenda" (sp_tables @table_owner='dbo'). 4- Visualice la estructura de la tabla "agenda" (sp_columns). 5- Ingrese los siguientes registros: insert into agenda (apellido, nombre, domicilio, telefono) values ('Moreno','Alberto','Colon 123','4234567'); insert into agenda (apellido,nombre, domicilio, telefono) values ('Torres','Juan','Avellaneda 135','4458787'); 6- Seleccione todos los registros de la tabla: select * from agenda; 7- Elimine la tabla "agenda": drop table agenda; 8- Intente eliminar la tabla nuevamente (aparece un mensaje de error): drop table agenda;

Segundo problema: Trabaje con la tabla "libros" que almacena los datos de los libros de su propia biblioteca. 1- Elimine la tabla "libros", si existe: if object_id('libros') is not null drop table libros; 2- Cree una tabla llamada "libros". Debe definirse con los siguientes campos: titulo (cadena de 20), autor (cadena de 30) y editorial (cadena de 15). 3- Visualice las tablas existentes (sp_tables @table_owner='dbo'). 4- Visualice la estructura de la tabla "libros" (sp_columns). 5- Ingrese los siguientes registros: insert into libros (titulo,autor,editorial) values ('El aleph','Borges','Planeta'); insert into libros (titulo,autor,editorial)

15

values ('Martin Fierro','Jose Hernandez','Emece'); insert into libros (titulo,autor,editorial) values ('Aprenda PHP','Mario Molina','Emece'); 6- Muestre todos los registros (select).

4 - Tipos de datos básicos Ya explicamos que al crear una tabla debemos resolver qué campos (columnas) tendrá y que tipo de datos almacenará cada uno de ellos, es decir, su estructura. El tipo de dato especifica el tipo de información que puede guardar un campo: caracteres, números, etc. Estos son algunos tipos de datos básicos de SQL Server (posteriormente veremos otros):  varchar: se usa para almacenar cadenas de caracteres. Una cadena es una secuencia de caracteres. Se coloca entre comillas (simples); ejemplo: 'Hola', 'Juan Perez'. El tipo "varchar" define una cadena de longitud variable en la cual determinamos el máximo de caracteres entre paréntesis. Puede guardar hasta 8000 caracteres. Por ejemplo, para almacenar cadenas de hasta 30 caracteres, definimos un campo de tipo varchar(30), es decir, entre paréntesis, junto al nombre del campo colocamos la longitud. Si asignamos una cadena de caracteres de mayor longitud que la definida, la cadena no se carga, aparece un mensaje indicando tal situación y la sentencia no se ejecuta. Por ejemplo, si definimos un campo de tipo varchar(10) e intentamos asignarle la cadena 'Buenas tardes', aparece un mensaje de error y la sentencia no se ejecuta.  integer: se usa para guardar valores numéricos enteros, de -2000000000 a 2000000000 aprox. Definimos campos de este tipo cuando queremos representar, por ejemplo, cantidades.  float: se usa para almacenar valores numéricos con decimales. Se utiliza como separador el punto (.). Definimos campos de este tipo para precios, por ejemplo. Antes de crear una tabla debemos pensar en sus campos y optar por el tipo de dato adecuado para cada uno de ellos. Por ejemplo, si en un campo almacenaremos números enteros, el tipo "float" sería una mala elección; si vamos a guardar precios, el tipo "float" es más adecuado, no así "integer" que no tiene decimales. Otro ejemplo, si en un campo vamos a guardar un número telefónico o un número de documento, usamos "varchar", no "integer" porque si bien son dígitos, con ellos no realizamos operaciones matemáticas.

Vamos a crear una tabla llamada "libros". En primer lugar vamos a eliminar la tabla "libros" averiguando si existe: if object_id('libros') is not null drop table libros;

16

Para almacenar información de los libros de una librería necesitamos los siguientes campos: -titulo, cadena de caracteres de 20 de longitud, -autor, cadena de caracteres de 15 de longitud, -editorial, caracteres de 10 de longitud, -precio, valor numérico con decimales y -cantidad, valor numérico entero.

Al crear la tabla, entonces, elegimos el tipo de dato más adecuado para cada campo: create table libros( titulo varchar(20), autor varchar(15), editorial varchar(10), precio float, cantidad integer );

Note que al especificar el tipo de dato de los campos numéricos, no colocamos entre paréntesis la longitud. Vemos la estructura de la tabla: sp_columns libros;

Aparece la siguiente información: ...COLUMN_NAME TYPE_NAME LENGHT _______________________________________ titulo varchar 20 autor varchar 15 editorial varchar 10 precio float 8 cantidad int 4

Ingresamos algunos registros: insert into libros (titulo,autor,editorial,precio,cantidad) values ('El aleph','Borges','Emece',25.50,100); insert into libros (titulo,autor,editorial,precio,cantidad) values ('Matematica estas ahi','Paenza','Siglo XXI',18.8,200);

Note que al ingresar valores numéricos (float e integer) no se utilizan comillas y para el separador de decimales se usa el caracter punto(.). Veamos los registros cargados: select * from libros;

Veamos lo que sucede si intentamos ingresar para el campo "titulo" una cadena de más de 20 caracteres: insert into libros (titulo,autor,editorial,precio,cantidad) values ('Alicia en el pais de las maravillas','Lewis Carroll','Atlantida',10,200);

aparece un mensaje de error y la sentencia no se ejecuta. vamos a cortar la cadena para que SQL Server acepte el ingreso del registro: insert into libros (titulo,autor,editorial,precio,cantidad) values ('Alicia en el pais','Lewis Carroll','Atlantida',10,200);

17

Veamos los registros cargados: select * from libros;

problema: Un videoclub que alquila películas en video almacena la información de sus películas en una tabla llamada "peliculas"; para cada película necesita los siguientes datos: -nombre, cadena de caracteres de 20 de longitud, -actor, cadena de caracteres de 20 de longitud, -duración, valor numérico entero. -cantidad de copias: valor entero. 1- Elimine la tabla, si existe: if object_id('peliculas')is not null drop table peliculas; 2- Cree la tabla eligiendo el tipo de dato adecuado para cada campo: create table peliculas( nombre varchar(20), actor varchar(20), duracion integer, cantidad integer ); 3- Vea la estructura de la tabla: 4- Ingrese los siguientes registros: insert into peliculas (nombre, actor, duracion, cantidad) values ('Mision imposible','Tom Cruise',128,3); insert into peliculas (nombre, actor, duracion, cantidad) values ('Mision imposible 2','Tom Cruise',130,2); insert into peliculas (nombre, actor, duracion, cantidad) values ('Mujer bonita','Julia Roberts',118,3); insert into peliculas (nombre, actor, duracion, cantidad) values ('Elsa y Fred','China Zorrilla',110,2); 5- Muestre todos los registros.

Segundo problema: Una empresa almacena los datos de sus empleados en una tabla "empleados" que guarda los siguientes datos: nombre, documento, sexo, domicilio, sueldobasico. 1- Elimine la tabla, si existe: if object_id('empleados') is not null drop table empleados; 2- Cree la tabla eligiendo el tipo de dato adecuado para cada campo: create table empleados( nombre varchar(20), documento varchar(8), sexo varchar(1), domicilio varchar(30), sueldobasico float ); 3- Vea la estructura de la tabla: 4- Ingrese algunos registros: insert into empleados (nombre, documento, sexo, domicilio, sueldobasico)

18

values ('Juan Perez','22333444','m','Sarmiento 123',500); insert into empleados (nombre, documento, sexo, domicilio, sueldobasico) values ('Ana Acosta','24555666','f','Colon 134',650); insert into empleados (nombre, documento, sexo, domicilio, sueldobasico) values ('Bartolome Barrios','27888999','m','Urquiza 479',800); 5- Seleccione todos los registros.

5 - Recuperar algunos campos (select) Hemos aprendido cómo ver todos los registros de una tabla, empleando la instrucción "select". La sintaxis básica y general es la siguiente: select * from NOMBRETABLA;

El asterisco (*) indica que se seleccionan todos los campos de la tabla. Podemos especificar el nombre de los campos que queremos ver separándolos por comas: select titulo,autor from libros;

La lista de campos luego del "select" selecciona los datos correspondientes a los campos nombrados. En el ejemplo anterior seleccionamos los campos "titulo" y "autor" de la tabla "libros", mostrando todos los registros. Los datos aparecen ordenados según la lista de selección, en dicha lista los nombres de los campos se separan con comas. Trabajamos con la tabla "libros" que almacena los datos de los libros de una librería. Eliminamos la tabla, si existe: if object_id('libros') is not null drop table libros;

Creamos la tabla: create table libros( titulo varchar(40), autor varchar(30), editorial varchar(15), precio float, cantidad integer );

Veamos la estructura de la tabla: sp_columns libros;

Ingresamos algunos registros: insert into libros (titulo,autor,editorial,precio,cantidad) values ('El aleph','Borges','Emece',25.50,100); insert into libros (titulo,autor,editorial,precio,cantidad) values ('Alicia en el pais de las maravillas','Lewis Carroll','Atlantida',10,200); insert into libros (titulo,autor,editorial,precio,cantidad)

19

values ('Matematica estas ahi','Paenza','Siglo XXI',18.8,200);

Veamos todos los campos la tabla: select *from libros;

Veamos solamente el título, autor y editorial de todos los libros especificando los nombres de los campos separados por comas: select titulo,autor,editorial from libros;

Con la siguiente sentencia seleccionamos los títulos y precios de todos los libros: select titulo,precio from libros;

Veamos solamente la editorial y la cantidad de libros tipeamos: select editorial,cantidad from libros;

Primer problema: Un videoclub que alquila películas en video almacena la información de sus películas en alquiler en una tabla llamada "peliculas". 1- Elimine la tabla, si existe: if object_id('peliculas') is not null drop table peliculas; 2- Cree la tabla: create table peliculas( titulo varchar(20), actor varchar(20), duracion integer, cantidad integer ); 3- Vea la estructura de la tabla (sp_columns). 4- Ingrese alos siguientes registros: insert into peliculas (titulo, actor, duracion, cantidad) values ('Mision imposible','Tom Cruise',180,3); insert into peliculas (titulo, actor, duracion, cantidad) values ('Mision imposible 2','Tom Cruise',190,2); insert into peliculas (titulo, actor, duracion, cantidad) values ('Mujer bonita','Julia Roberts',118,3); insert into peliculas (titulo, actor, duracion, cantidad) values ('Elsa y Fred','China Zorrilla',110,2); 5- Realice un "select" mostrando solamente el título y actor de todas las películas 6- Muestre el título y duración de todas las peliculas 7- Muestre el título y la cantidad de copias

Segundo problema: Una empresa almacena los datos de sus empleados en una tabla llamada "empleados". 1- Elimine la tabla, si existe: if object_id('empleados') is not null

20

drop table empleados; 2- Cree la tabla: create table empleados( nombre varchar(20), documento varchar(8), sexo varchar(1), domicilio varchar(30), sueldobasico float ); 3- Vea la estructura de la tabla: sp_columns empleados; 4- Ingrese algunos registros: insert into empleados (nombre, documento, sexo, domicilio, sueldobasico) values ('Juan Juarez','22333444','m','Sarmiento 123',500); insert into empleados (nombre, documento, sexo, domicilio, sueldobasico) values ('Ana Acosta','27888999','f','Colon 134',700); insert into empleados (nombre, documento, sexo, domicilio, sueldobasico) values ('Carlos Caseres','31222333','m','Urquiza 479',850); 5- Muestre todos los datos de los empleados 6- Muestre el nombre, documento y domicilio de los empleados 7- Realice un "select" mostrando el documento, sexo y sueldo básico de todos los empleados

6 - Recuperar algunos registros (where) Hemos aprendido a seleccionar algunos campos de una tabla. También es posible recuperar algunos registros. Existe una cláusula, "where" con la cual podemos especificar condiciones para una consulta "select". Es decir, podemos recuperar algunos registros, sólo los que cumplan con ciertas condiciones indicadas con la cláusula "where". Por ejemplo, queremos ver el usuario cuyo nombre es "Marcelo", para ello utilizamos "where" y luego de ella, la condición: select nombre, clave from usuarios where nombre='Marcelo';

La sintaxis básica y general es la siguiente: select NOMBRECAMPO1, ..., NOMBRECAMPOn from NOMBRETABLA where CONDICION;

Para las condiciones se utilizan operadores relacionales (tema que trataremos más adelante en detalle). El signo igual(=) es un operador relacional. Para la siguiente selección de registros especificamos una condición que solicita los usuarios cuya clave es igual a "River": 21

select nombre,clave from usuarios where clave='River';

Si ningún registro cumple la condición establecida con el "where", no aparecerá ningún registro. Entonces, con "where" establecemos condiciones para recuperar algunos registros. Para recuperar algunos campos de algunos registros combinamos en la consulta la lista de campos y la cláusula "where": select nombre from usuarios where clave='River';

En la consulta anterior solicitamos el nombre de todos los usuarios cuya clave sea igual a "River". Trabajamos con la tabla "usuarios" que consta de 2 campos: nombre de usuario y clave. Eliminamos la tabla, si existe: if object_id('usuarios') is not null drop table usuarios;

Creamos la tabla: create table usuarios ( nombre varchar(30), clave varchar(10) );

Vemos la estructura de la tabla: sp_columns usuarios;

Ingresamos algunos registros: insert into usuarios (nombre, clave) values ('Marcelo','Boca'); insert into usuarios (nombre, clave) values ('JuanPerez','Juancito'); insert into usuarios (nombre, clave) values ('Susana','River'); insert into usuarios (nombre, clave) values ('Luis','River');

Realizamos una consulta especificando una condición, queremos ver el usuario cuyo nombre es "Leonardo": select *from usuarios where nombre='Leonardo';

Queremos ver el nombre de los usuarios cuya clave es "River": select nombre from usuarios where clave='River';

Realizamos un "select" de los nombres de los usuarios cuya clave es "Santi": select nombre from usuarios where clave='Santi';

22

No se muestra ningún registro ya que ninguno cumple la condición. Primer problema: Trabaje con la tabla "agenda" en la que registra los datos de sus amigos. 1- Elimine "agenda", si existe: if object_id('agenda') is not null drop table agenda; 2- Cree la tabla, con los siguientes campos: apellido (cadena de 30), nombre (cadena de 20), domicilio (cadena de 30) y telefono (cadena de 11). 3- Visualice la estructura de la tabla "agenda". 4- Ingrese los siguientes registros: Acosta, Ana, Colon 123, 4234567; Bustamante, Betina, Avellaneda 135, 4458787; Lopez, Hector, Salta 545, 4887788; Lopez, Luis, Urquiza 333, 4545454; Lopez, Marisa, Urquiza 333, 4545454. 5- Seleccione todos los registros de la tabla 6- Seleccione el registro cuyo nombre sea "Marisa" (1 registro) 7- Seleccione los nombres y domicilios de quienes tengan apellido igual a "Lopez" (3 registros) 8- Muestre el nombre de quienes tengan el teléfono "4545454" (2 registros)

Segundo problema: Trabaje con la tabla "libros" de una librería que guarda información referente a sus libros disponibles para la venta. 1- Elimine la tabla si existe. 2- Cree la tabla "libros". Debe tener la siguiente estructura: create table libros ( titulo varchar(20), autor varchar(30), editorial varchar(15)); 3- Visualice la estructura de la tabla "libros". 4- Ingrese los siguientes registros: El aleph,Borges,Emece; Martin Fierro,Jose Hernandez,Emece; Martin Fierro,Jose Hernandez,Planeta; Aprenda PHP,Mario Molina,Siglo XXI; 5- Seleccione los registros cuyo autor sea "Borges" (1 registro) 6- Seleccione los títulos de los libros cuya editorial sea "Emece" (2 registros) 7- Seleccione los nombres de las editoriales de los libros cuyo titulo sea "Martin Fierro" (2 registros)

23

7- Operadores relacionales Los operadores son símbolos que permiten realizar operaciones matemáticas, concatenar cadenas, hacer comparaciones. SQL Server tiene 4 tipos de operadores: 1. 2. 3. 4.

relacionales (o de comparación) aritméticos de concatenación lógicos.

Por ahora veremos solamente los primeros. Los operadores relacionales (o de comparación) nos permiten comparar dos expresiones, que pueden ser variables, valores de campos, etc. Hemos aprendido a especificar condiciones de igualdad para seleccionar registros de una tabla; por ejemplo: select *from libros where autor='Borges';

Utilizamos el operador relacional de igualdad. Los operadores relacionales vinculan un campo con un valor para que SQL Server compare cada registro (el campo especificado) con el valor dado. Los operadores relacionales son los siguientes: =

> < >= 20;

Queremos seleccionar los libros cuyo precio sea menor o igual a 30: select *from libros where precio20;

Note que el valor con el cual comparamos el campo "precio", como es numérico (float), no se coloca entre comillas. Los libros cuyo precio es menor o igual a 20 pesos no aparece en la selección. Recuperamos aquellos libros cuyo precio es menor o igual a 30: select *from libros where precio=500; 7- Elimine todas las impresoras (1 registro) delete from articulos where nombre='impresora'; 8- Elimine todos los artículos cuyo código sea diferente a 4 (1 registro) delete from articulos where codigo4; 9- Mostrar la tabla después que borra cada registro.

29

9 - Actualizar registros (update) Decimos que actualizamos un registro cuando modificamos alguno de sus valores. Para modificar uno o varios datos de uno o varios registros utilizamos "update" (actualizar). Por ejemplo, en nuestra tabla "usuarios", queremos cambiar los valores de todas las claves, por "RealMadrid": update usuarios set clave='RealMadrid';

Utilizamos "update" junto al nombre de la tabla y "set" junto con el campo a modificar y su nuevo valor. El cambio afectará a todos los registros. Podemos modificar algunos registros, para ello debemos establecer condiciones de selección con "where". Por ejemplo, queremos cambiar el valor correspondiente a la clave de nuestro usuario llamado "Federicolopez", queremos como nueva clave "Boca", necesitamos una condición "where" que afecte solamente a este registro: update usuarios set clave='Boca' where nombre='Federicolopez';

Si Microsoft SQL Server no encuentra registros que cumplan con la condición del "where", no se modifica ninguno. Las condiciones no son obligatorias, pero si omitimos la cláusula "where", la actualización afectará a todos los registros. También podemos actualizar varios campos en una sola instrucción: update usuarios set nombre='Marceloduarte', clave='Marce' where nombre='Marcelo';

Para ello colocamos "update", el nombre de la tabla, "set" junto al nombre del campo y el nuevo valor y separado por coma, el otro nombre del campo con su nuevo valor. rabajamos con la tabla "usuarios". Eliminamos la tabla si existe: if object_id('usuarios') is not null drop table usuarios;

Creamos la tabla: create table usuarios( nombre varchar(20), clave varchar(10) );

30

Ingresamos algunos registros: insert into usuarios (nombre,clave) values ('Marcelo','River'); insert into usuarios (nombre,clave) values ('Susana','chapita'); insert into usuarios (nombre,clave) values ('Carlosfuentes','Boca'); insert into usuarios (nombre,clave) values ('Federicolopez','Boca');

Cambiaremos los valores de todas las claves, por la cadena "RealMadrid": update usuarios set clave='RealMadrid';

El cambio afectó a todos los registros, veámoslo: select *from usuarios;

Necesitamos cambiar el valor de la clave del usuario llamado "Federicolopez" por "Boca": update usuarios set clave='Boca' where nombre='Federicolopez';

Verifiquemos que la actualización se realizó: select *from usuarios;

Vimos que si Microsoft SQL Server no encuentra registros que cumplan con la condición no se modifican registros: update usuarios set clave='payaso' where nombre='JuanaJuarez';

Si vemos la tabla veremos que no a cambiado: select *from usuarios;

Para actualizar varios campos en una sola instrucción empleamos: update usuarios set nombre='Marceloduarte', clave='Marce' where nombre='Marcelo';

Si vemos la tabla: select *from usuarios;

Primer problema: Trabaje con la tabla "agenda" que almacena los datos de sus amigos. 1- Elimine la tabla si existe: if object_id('agenda') is not null drop table agenda; 2- Cree la tabla: create table agenda( apellido varchar(30), nombre varchar(20), domicilio varchar(30), telefono varchar(11)

31

); 3- Ingrese los siguientes registros (1 registro actualizado): insert into agenda (apellido,nombre,domicilio,telefono) values ('Acosta','Alberto','Colon 123','4234567'); insert into agenda (apellido,nombre,domicilio,telefono) values ('Juarez','Juan','Avellaneda 135','4458787'); insert into agenda (apellido,nombre,domicilio,telefono) values ('Lopez','Maria','Urquiza 333','4545454'); insert into agenda (apellido,nombre,domicilio,telefono) values ('Lopez','Jose','Urquiza 333','4545454'); insert into agenda (apellido,nombre,domicilio,telefono) values ('Suarez','Susana','Gral. Paz 1234','4123456'); 4- Modifique el registro cuyo nombre sea "Juan" por "Juan Jose" (1 registro afectado) 5- Actualice los registros cuyo número telefónico sea igual a "4545454" por "4445566" (2 registros afectados) 6- Actualice los registros que tengan en el campo "nombre" el valor "Juan" por "Juan Jose" (ningún registro afectado porque ninguno cumple con la condición del "where") 7 - Luego de cada actualización ejecute un select que muestre todos los registros de la tabla.

Segundo problema: Trabaje con la tabla "libros" de una librería. 1- Elimine la tabla si existe: if object_id('libros') is not null drop table libros; 2- Créela con los siguientes campos: titulo (cadena de 30 caracteres de longitud), autor (cadena de 20), editorial (cadena de 15) y precio (float): create table libros ( titulo varchar(30), autor varchar(20), editorial varchar(15), precio float ); 3- Ingrese los siguientes registros: insert into libros (titulo, autor, editorial, precio) values ('El aleph','Borges','Emece',25.00); insert into libros (titulo, autor, editorial, precio) values ('Martin Fierro','Jose Hernandez','Planeta',35.50); insert into libros (titulo, autor, editorial, precio) values ('Aprenda PHP','Mario Molina','Emece',45.50); insert into libros (titulo, autor, editorial, precio) values ('Cervantes y el quijote','Borges','Emece',25); insert into libros (titulo, autor, editorial, precio) values ('Matematica estas ahi','Paenza','Siglo XXI',15); 4- Muestre todos los registros (5 registros): select *from libros; 5- Modifique los registros cuyo autor sea igual registro afectado) update libros set autor='Adrian Paenza' where autor='Paenza';

32

a "Paenza", por "Adrian Paenza" (1

6- Nuevamente, modifique los registros cuyo autor sea igual Paenza" (ningún registro afectado porque ninguno cumple la condición) update libros set autor='Adrian Paenza' where autor='Paenza';

a "Paenza", por "Adrian

7- Actualice el precio del libro de "Mario Molina" a 27 pesos (1 registro afectado): update libros set precio=27 where autor='Mario Molina'; 8- Actualice el valor del campo "editorial" por "Emece S.A.", para todos los registros cuya editorial sea igual a "Emece" (3 registros afectados): update libros set editorial='Emece S.A.' where editorial='Emece'; 9 - Luego de cada actualización ejecute un select que mustre todos los registros de la tabla.

10 - Comentarios Para aclarar algunas instrucciones, en ocasiones, necesitamos agregar comentarios. Es posible ingresar comentarios en la línea de comandos, es decir, un texto que no se ejecuta; para ello se emplean dos guiones (--) al comienzo de la línea: select * from libros --mostramos los registros de libros;

en la línea anterior, todo lo que está luego de los guiones (hacia la derecha) no se ejecuta. Para agregar varias líneas de comentarios, se coloca una barra seguida de un asterisco (/*) al comienzo del bloque de comentario y al finalizarlo, un asterisco seguido de una barra (*/). select titulo, autor /*mostramos títulos y nombres de los autores*/ from libros;

todo lo que está entre los símbolos "/*" y "*/" no se ejecuta. Problema: Trabajamos con la tabla "libros" que almacena los datos de los libros de una librería. Eliminamos la tabla, si existe: if object_id('libros') is not null drop table libros;

Creamos la tabla: create table libros( titulo varchar(30), autor varchar(20), editorial varchar(15) );

33

Agregamos un registro: insert into libros (titulo,autor,editorial) values ('El aleph','Borges','Emece');

Mostramos todos los libros y agregamos un comentario de linea: select * from libros --mostramos los registros de libros;

vamos a mostrar el título y autor de todos los libros y agregamos un bloque de comentarios: select titulo, autor /*mostramos títulos y nombres de los autores*/ from libros;

Note que lo que está entre los símbolos no se ejecuta.

11 - Valores null (is null) "null" significa "dato desconocido" o "valor inexistente". No es lo mismo que un valor "0", una cadena vacía o una cadena literal "null". A veces, puede desconocerse o no existir el dato correspondiente a algún campo de un registro. En estos casos decimos que el campo puede contener valores nulos. Por ejemplo, en nuestra tabla de libros, podemos tener valores nulos en el campo "precio" porque es posible que para algunos libros no le hayamos establecido el precio para la venta. En contraposición, tenemos campos que no pueden estar vacíos jamás. Veamos un ejemplo. Tenemos nuestra tabla "libros". El campo "titulo" no debería estar vacío nunca, igualmente el campo "autor". Para ello, al crear la tabla, debemos especificar que dichos campos no admitan valores nulos: create table libros( titulo varchar(30) not null, autor varchar(20) not null, editorial varchar(15) null, precio float );

Para especificar que un campo no admita valores nulos, debemos colocar "not null" luego de la definición del campo. En el ejemplo anterior, los campos "editorial" y "precio" si admiten valores nulos. Cuando colocamos "null" estamos diciendo que admite valores nulos (caso del campo "editorial"); por defecto, es decir, si no lo aclaramos, los campos permiten valores nulos (caso del campo "precio"). Si ingresamos los datos de un libro, para el cual aún no hemos definido el precio podemos colocar "null" para mostrar que no tiene precio: 34

insert into libros (titulo,autor,editorial,precio) values('El aleph','Borges','Emece',null);

Note que el valor "null" no es una cadena de caracteres, no se coloca entre comillas. Entonces, si un campo acepta valores nulos, podemos ingresar "null" cuando no conocemos el valor. También podemos colocar "null" en el campo "editorial" si desconocemos el nombre de la editorial a la cual pertenece el libro que vamos a ingresar: insert into libros (titulo,autor,editorial,precio) values('Alicia en el pais','Lewis Carroll',null,25);

Si intentamos ingresar el valor "null" en campos que no admiten valores nulos (como "titulo" o "autor"), SQL Server no lo permite, muestra un mensaje y la inserción no se realiza; por ejemplo: insert into libros (titulo,autor,editorial,precio) values(null,'Borges','Siglo XXI',25);

Para ver cuáles campos admiten valores nulos y cuáles no, podemos emplear el procedimiento almacenado "sp_columns" junto al nombre de la tabla. Nos muestra mucha información, en la columna "IS_NULLABLE" vemos que muestra "NO" en los campos que no permiten valores nulos y "YES" en los campos que si los permiten. Para recuperar los registros que contengan el valor "null" en algún campo, no podemos utilizar los operadores relacionales vistos anteriormente: = (igual) y (distinto); debemos utilizar los operadores "is null" (es igual a null) y "is not null" (no es null): select * from libros where precio is null;

La sentencia anterior tendrá una salida diferente a la siguiente: select * from libros where precio=0;

Con la primera sentencia veremos los libros cuyo precio es igual a "null" (desconocido); con la segunda, los libros cuyo precio es 0. Igualmente para campos de tipo cadena, las siguientes sentencias "select" no retornan los mismos registros: select * from libros where editorial is null; select * from libros where editorial='';

Con la primera sentencia veremos los libros cuya editorial es igual a "null", con la segunda, los libros cuya editorial guarda una cadena vacía. Entonces, para que un campo no permita valores nulos debemos especificarlo luego de definir el campo, agregando "not null". Por defecto, los campos permiten valores nulos, pero podemos especificarlo igualmente agregando "null".

Trabajamos con la tabla "libros" de una librería. Eliminamos la tabla "libros", si existe: 35

if object_id('libros') is not null drop table libros;

Creamos la tabla especificando que los campos "titulo" y "autor" no admitan valores nulos: create table libros( titulo varchar(30) not null, autor varchar(30) not null, editorial varchar(15) null, precio float );

Los campos "editorial" y "precio" si permiten valores nulos; el primero, porque lo especificamos colocando "null" en la definición del campo, el segundo lo asume por defecto. Agregamos un registro a la tabla con valor nulo para el campo "precio": insert into libros (titulo,autor,editorial,precio) values('El aleph','Borges','Emece',null);

Recuerde que el valor "null" no es una cadena de caracteres, por lo tanto no se coloca entre comillas. Ingresamos otro registro, con valor nulo para el campo "editorial", campo que admite valores "null": insert into libros (titulo,autor,editorial,precio) values('Alicia en el pais','Lewis Carroll',null,0);

Veamos lo que sucede si intentamos ingresar el valor "null" en campos que no lo admiten, como "titulo": insert into libros (titulo,autor,editorial,precio) values(null,'Borges','Siglo XXI',25);

aparece un mensaje y la sentencia no se ejecuta. Para ver cuáles campos admiten valores nulos y cuáles no, empleamos el procedimiento almacenado "sp_columns": sp_columns libros;

nos muestra muchas columnas, una de ellas tiene el encabezado "IS_NULLABLE", vemos que aparece "NO" en los campos que no permiten valores nulos y "YES" en los campos que si los permiten. Dijimos que el valor "null" no es lo mismo que una cadena vacía. Vamos a ingresar un registro con cadena vacía para el campo "editorial": insert into libros (titulo,autor,editorial,precio) values('Uno','Richard Bach','',18.50);

Ingresamos otro registro, ahora cargamos una cadena vacía en el campo "titulo": insert into libros (titulo,autor,editorial,precio) values('','Richard Bach','Planeta',22);

Veamos todos los regiustros ingresados: select *from libros;

Recuperemos los registros que contengan el valor "null" en el campo "precio": select *from libros

36

where precio is null;

La sentencia anterior tendrá una salida diferente a la siguiente: select *from libros where precio=0;

Con la primera sentencia veremos los libros cuyo precio es igual a "null" (desconocido); con la segunda, los libros cuyo precio es 0. Recuperemos los libros cuyo nombre de editorial es "null": select *from libros where editorial is null;

Ahora veamos los libros cuya editorial almacena una cadena vacía: select *from libros where editorial='';

Para recuperar los libros cuyo precio no sea nulo tipeamos: select *from libros where precio is not null;

Primer problema: Una farmacia guarda información referente a sus medicamentos en una tabla llamada "medicamentos". 1- Elimine la tabla, si existe: if object_id('medicamentos') is not null drop table medicamentos; 2- Cree la tabla con la siguiente estructura: create table medicamentos( codigo integer not null, nombre varchar(20) not null, laboratorio varchar(20), precio float, cantidad integer not null ); 3- Visualice la estructura de la tabla "medicamentos": sp_columns medicamentos; note que los campos "codigo", "nombre" y "cantidad", en la columna "IS_NULLABLE" aparece "NO" y en las otras "YES". 4- Ingrese algunos registros con valores "null" para los campos que lo admitan: insert into medicamentos (codigo,nombre,laboratorio,precio,cantidad) values(1,'Sertal gotas',null,null,100); insert into medicamentos (codigo,nombre,laboratorio,precio,cantidad) values(2,'Sertal compuesto',null,8.90,150); insert into medicamentos (codigo,nombre,laboratorio,precio,cantidad) values(3,'Buscapina','Roche',null,200); 5- Vea todos los registros: select *from medicamentos; 6- Ingrese un registro con valor "0" para el precio y cadena vacía para el laboratorio: insert into medicamentos (codigo,nombre, laboratorio,precio,cantidad) values(4,'Bayaspirina','',0,150);

37

7- Ingrese un registro con valor "0" para el código y cantidad y cadena vacía para el nombre: insert into medicamentos (codigo,nombre,laboratorio,precio,cantidad) values(0,'','Bayer',15.60,0); 8- Muestre todos los registros: select *from medicamentos; 9- Intente ingresar un registro con valor nulo para un campo que no lo admite (aparece un mensaje de error): insert into medicamentos (codigo,nombre,laboratorio,precio,cantidad) values(null,'Amoxidal jarabe','Bayer',25,120); 10- Recupere los registros que contengan valor "null" en el campo "laboratorio", luego los que tengan una cadena vacía en el mismo campo. Note que el resultado es diferente. 11- Recupere los registros que contengan valor "null" en el campo "precio", luego los que tengan el valor 0 en el mismo campo. Note que el resultado es distinto. 12- Recupere los registros cuyo laboratorio no contenga una cadena vacía, luego los que sean distintos de "null". Note que la salida de la primera sentencia no muestra los registros con cadenas vacías y tampoco los que tienen valor nulo; el resultado de la segunda sentencia muestra los registros con valor para el campo laboratorio (incluso cadena vacía). 13- Recupere los registros cuyo precio sea distinto de 0, luego los que sean distintos de "null": Note que la salida de la primera sentencia no muestra los registros con valor 0 y tampoco los que tienen valor nulo; el resultado de la segunda sentencia muestra los registros con valor para el campo precio (incluso el valor 0).

Segundo problema: Trabaje con la tabla que almacena los datos sobre películas, llamada "peliculas". 1- Elimine la tabla si existe: if object_id('peliculas') is not null drop table peliculas; 2- Créela con la siguiente estructura: create table peliculas( codigo int not null, titulo varchar(40) not null, actor varchar(20), duracion int ); 3- Visualice la estructura de la tabla sp_columns peliculas; note que el campo "codigo" y "titulo", en la columna "IS_NULLABLE" muestra "NO" y los otros campos "YES". 4- Ingrese los siguientes registros: insert into peliculas (codigo,titulo,actor,duracion) values(1,'Mision imposible','Tom Cruise',120); insert into peliculas (codigo,titulo,actor,duracion)

38

values(2,'Harry Potter y la piedra filosofal',null,180); insert into peliculas (codigo,titulo,actor,duracion) values(3,'Harry Potter y la camara secreta','Daniel R.',null); insert into peliculas (codigo,titulo,actor,duracion) values(0,'Mision imposible 2','',150); insert into peliculas (codigo,titulo,actor,duracion) values(4,'','L. Di Caprio',220); insert into peliculas (codigo,titulo,actor,duracion) values(5,'Mujer bonita','R. Gere-J. Roberts',0); 5- Recupere todos los registros para ver cómo SQL Server los almacenó: select *from peliculas; 6- Intente ingresar un registro con valor nulo para campos que no lo admiten (aparece un mensaje de error): insert into peliculas (codigo,titulo,actor,duracion) values(null,'Mujer bonita','R. Gere-J. Roberts',190); 7- Muestre los registros con valor nulo en el campo "actor" y luego los que guardan una cadena vacía (note que la salida es distinta) (1 registro) 8- Modifique los registros que tengan valor de duración desconocido (nulo) por "120" (1 registro actualizado) 9- Coloque 'Desconocido' en el campo "actor" en los registros que tengan una cadena vacía en dicho campo (1 registro afectado) 10- Muestre todos los registros. Note que el cambio anterior no afectó a los registros con valor nulo en el campo "actor". 11- Elimine los registros cuyo título sea una cadena vacía (1 registro)

12 - Clave primaria Una clave primaria es un campo (o varios) que identifica un solo registro (fila) en una tabla. Para un valor del campo clave existe solamente un registro. Veamos un ejemplo, si tenemos una tabla con datos de personas, el número de documento puede establecerse como clave primaria, es un valor que no se repite; puede haber personas con igual apellido y nombre, incluso el mismo domicilio (padre e hijo por ejemplo), pero su documento será siempre distinto. Si tenemos la tabla "usuarios", el nombre de cada usuario puede establecerse como clave primaria, es un valor que no se repite; puede haber usuarios con igual clave, pero su nombre de usuario será siempre diferente. Podemos establecer que un campo sea clave primaria al momento de crear la tabla o luego que ha sido creada. Vamos a aprender a establecerla al crear la tabla. Hay 2 maneras de hacerlo, por ahora veremos la sintaxis más sencilla. 39

Tenemos nuestra tabla "usuarios" definida con 2 campos ("nombre" y "clave"). La sintaxis básica y general es la siguiente: create table NOMBRETABLA( CAMPO TIPO, ... primary key (NOMBRECAMPO) );

En el siguiente ejemplo definimos una clave primaria, para nuestra tabla "usuarios" para asegurarnos que cada usuario tendrá un nombre diferente y único: create table usuarios( nombre varchar(20), clave varchar(10), primary key(nombre) );

Lo que hacemos agregar luego de la definición de cada campo, "primary key" y entre paréntesis, el nombre del campo que será clave primaria. Una tabla sólo puede tener una clave primaria. Cualquier campo (de cualquier tipo) puede ser clave primaria, debe cumplir como requisito, que sus valores no se repitan ni sean nulos. Por ello, al definir un campo como clave primaria, automáticamente SQL Server lo convierte a "not null". Luego de haber establecido un campo como clave primaria, al ingresar los registros, SQL Server controla que los valores para el campo establecido como clave primaria no estén repetidos en la tabla; si estuviesen repetidos, muestra un mensaje y la inserción no se realiza. Es decir, si en nuestra tabla "usuarios" ya existe un usuario con nombre "juanperez" e intentamos ingresar un nuevo usuario con nombre "juanperez", aparece un mensaje y la instrucción "insert" no se ejecuta. Igualmente, si realizamos una actualización, SQL Server controla que los valores para el campo establecido como clave primaria no estén repetidos en la tabla, si lo estuviese, aparece un mensaje indicando que se viola la clave primaria y la actualización no se realiza. Trabajamos con la tabla "usuarios". Eliminamos la tabla, si existe: if object_id('usuarios') is not null drop table usuarios;

Creamos la tabla definiendo el campo "nombre" como clave primaria: create table usuarios( nombre varchar(20), clave varchar(10), primary key(nombre) );

Al campo "nombre" no lo definimos "not null", pero al establecerse como clave primaria, SQL Server lo convierte en "not null", veamos que en la columna "IS_NULLABLE" aparece "NO": sp_columns usuarios;

Ingresamos algunos registros: 40

insert into usuarios (nombre, clave) values ('juanperez','Boca'); insert into usuarios (nombre, clave) values ('raulgarcia','River');

Recordemos que cuando un campo es clave primaria, sus valores no se repiten. Intentamos ingresar un valor de clave primaria existente: insert into usuarios (nombre, clave) values ('juanperez','payaso');

aparece un mensaje de error y la sentencia no se ejecuta. Cuando un campo es clave primaria, sus valores no pueden ser nulos. Intentamos ingresar el valor "null" en el campo clave primaria: insert into usuarios (nombre, clave) values (null,'payaso');

aparece un mensaje de error y la sentencia no se ejecuta. Si realizamos alguna actualización, SQL Server controla que los valores para el campo establecido como clave primaria no estén repetidos en la tabla. Intentemos actualizar el nombre de un usuario colocando un nombre existente: update usuarios set nombre='juanperez' where nombre='raulgarcia';

aparece un mensaje indicando que se viola la clave primaria y la actualización no se realiza. Primer problema: Trabaje con la tabla "libros" de una librería. 1- Elimine la tabla si existe: if object_id('libros') is not null drop table libros; 2- Créela con los siguientes campos, estableciendo como clave primaria el campo "codigo": create table libros( codigo int not null, titulo varchar(40) not null, autor varchar(20), editorial varchar(15), primary key(codigo) ); 3- Ingrese los siguientes registros: insert into libros (codigo,titulo,autor,editorial) values (1,'El aleph','Borges','Emece'); insert into libros (codigo,titulo,autor,editorial) values (2,'Martin Fierro','Jose Hernandez','Planeta'); insert into libros (codigo,titulo,autor,editorial) values (3,'Aprenda PHP','Mario Molina','Nuevo Siglo'); 4- Ingrese un registro con código repetido (aparece un mensaje de error) 5- Intente ingresar el valor "null" en el campo "codigo" 6- Intente actualizar el código del libro "Martin Fierro" a "1" (mensaje de error)

41

Segundo problema: Un instituto de enseñanza almacena los datos de sus estudiantes en una tabla llamada "alumnos". 1- Elimine la tabla "alumnos" si existe: if object_id('alumnos') is not null drop table alumnos; 2- Cree la tabla con la siguiente estructura intentando establecer 2 campos como clave primaria, el campo "documento" y "legajo" (no lo permite): create table alumnos( legajo varchar(4) not null, documento varchar(8), nombre varchar(30), domicilio varchar(30), primary key(documento), primary key(legajo) ); 3- Cree la tabla estableciendo como clave primaria el campo "documento": create table alumnos( legajo varchar(4) not null, documento varchar(8), nombre varchar(30), domicilio varchar(30), primary key(documento) ); 4- Verifique que el campo "documento" no admite valores nulos: sp_columns alumnos; 5- Ingrese los siguientes registros: insert into alumnos (legajo,documento,nombre,domicilio) values('A233','22345345','Perez Mariana','Colon 234'); insert into alumnos (legajo,documento,nombre,domicilio) values('A567','23545345','Morales Marcos','Avellaneda 348'); 6- Intente ingresar un alumno con número de documento existente (no lo permite) 7- Intente ingresar un alumno con documento nulo (no lo permite)

13 - Campo con atributo Identity Un campo numérico puede tener un atributo extra "identity". Los valores de un campo con este atributo genera valores secuenciales que se inician en 1 y se incrementan en 1 automáticamente. Se utiliza generalmente en campos correspondientes a códigos de identificación para generar valores únicos para cada nuevo registro que se inserta. Sólo puede haber un campo "identity" por tabla. Para que un campo pueda establecerse como "identity", éste debe ser entero (también puede ser de un subtipo de entero o decimal con escala 0, tipos que estudiaremos posteriormente). 42

Para que un campo genere sus valores automáticamente, debemos agregar el atributo "identity" luego de su definición al crear la tabla: create table libros( codigo int identity, titulo varchar(40) not null, autor varchar(30), editorial varchar(15), precio float );

Cuando un campo tiene el atributo "identity" no se puede ingresar valor para él, porque se inserta automáticamente tomando el último valor como referencia, o 1 si es el primero. Para ingresar registros omitimos el campo definido como "identity", por ejemplo: insert into libros (titulo,autor,editorial,precio) values('El aleph','Borges','Emece',23);

Este primer registro ingresado guardará el valor 1 en el campo correspondiente al código. Si continuamos ingresando registros, el código (dato que no ingresamos) se cargará automáticamente siguiendo la secuencia de autoincremento. No está permitido ingresar el valor correspondiente al campo "identity", por ejemplo: insert into libros (codigo,titulo,autor,editorial,precio) values(5,'Martin Fierro','Jose Hernandez','Paidos',25);

generará un mensaje de error. "identity" permite indicar el valor de inicio de la secuencia y el incremento, pero lo veremos posteriormente. Un campo definido como "identity" generalmente se establece como clave primaria. Un campo "identity" no es editable, es decir, no se puede ingresar un valor ni actualizarlo. Un campo de identidad no permite valores nulos, aunque no se indique especificamente. Si ejecutamos el procedimiento "sp_columns()" veremos que en el campo "codigo" en la columna "TYPE_NAME" aparece "int identity" y en la columna "IS_NULLABLE" aparece "NO". Los valores secuenciales de un campo "identity" se generan tomando como referencia el último valor ingresado; si se elimina el último registro ingresado (por ejemplo 3) y luego se inserta otro registro, SQL Server seguirá la secuencia, es decir, colocará el valor "4". Trabajamos con la tabla "libros" de una librería. Eliminamos la tabla "libros", si existe: if object_id('libros') is not null drop table libros;

Creamos la tabla especificando que el campos "codigo" genere valores secuenciales comenzando en 1 e incrementándose en 1 automáticamente: create table libros( codigo int identity, titulo varchar(40) not null,

43

autor varchar(30), editorial varchar(15), precio float );

Ingresamos algunos registros, recordando que si un campo tiene el atributo "identity" debemos omitirlo en la inserción: insert into libros (titulo,autor,editorial,precio) values('El aleph','Borges','Emece',23);

Veamos cómo se almacenó: select *from libros;

Este primer registro ingresado guarda el valor 1 en el campo correspondiente al código. Continuemos ingresando registros: insert into libros (titulo,autor,editorial,precio) values('Uno','Richard Bach','Planeta',18); insert into libros (titulo,autor,editorial,precio) values('Aprenda PHP','Mario Molina','Siglo XXI',45.60); insert into libros (titulo,autor,editorial,precio) values('Alicia en el pais de maravillas','Lewis Carroll','Paidos',15.50);

Veamos cómo se almacenaron: select *from libros;

el código (dato que no ingresamos) se cargó automáticamente siguiendo la secuencia de autoincremento. Intentemos ingresar un valor para el campo "codigo": insert into libros (codigo,titulo,autor,editorial,precio) values(5,'Martin Fierro','Jose Hernandez','Paidos',25);

generará un mensaje de error. Un campo "identity" tampoco puede ser actualizado. Intentemos cambiar el valor de código de un registro: update libros set codigo=9 where titulo='Uno';

aparece un mensaje de error. Vamos a ver la estructura de la tabla ejecutando el siguiente procedimiento almacenado: sp_columns libros;

Note que en el campo "codigo", en la columna "TYPE_NAME" aparece "int identity" y en la columna IS_NULLABLE" aparece "NO", porque un campo "identity" automáticamente se convierte en "not null". En el campo "titulo", en la columna "IS_NULLABLE" aparece "NO" porque explícitamente indicamos que el campo fuera "not null". Eliminemos el último registro: delete from libros where autor='Lewis Carroll';

44

Ingresamos un quinto registro y luego vemos que en el campo código se guardó el valor secuencial sin considerar que el valor "4" ya no existe: insert into libros (titulo, autor, editorial, precio) values('Martin Fierro','Jose Hernandez','Paidos',25); select *from libros;

Primer problema: Una farmacia guarda información referente a sus medicamentos en una tabla llamada "medicamentos". 1- Elimine la tabla,si existe: if object_id('medicamentos') is not null drop table medicamentos; 2- Cree la tabla con un campo "codigo" que genere valores secuenciales automáticamente: create table medicamentos( codigo int identity, nombre varchar(20) not null, laboratorio varchar(20), precio float, cantidad integer ); 3- Visualice la estructura de la tabla "medicamentos": sp_columns medicamentos; 4- Ingrese los siguientes registros: insert into medicamentos (nombre, laboratorio,precio,cantidad) values('Sertal','Roche',5.2,100); insert into medicamentos (nombre, laboratorio,precio,cantidad) values('Buscapina','Roche',4.10,200); insert into medicamentos (nombre, laboratorio,precio,cantidad) values('Amoxidal 500','Bayer',15.60,100); 5- Verifique que SQL Server generó valores para el campo "código" de modo automático: select *from medicamentos; 6- Intente ingresar un registro con un valor para el campo "codigo" 7- Intente actualizar un valor de código (aparece un mensaje de error) 8- Elimine el registro con codigo "3" (1 registro eliminado) 9- Ingrese un nuevo registro: insert into medicamentos (nombre, laboratorio,precio,cantidad) values('Amoxilina 500','Bayer',15.60,100); 10- Seleccione todos los registros para ver qué valor guardó SQL Server en el campo código: select *from medicamentos;

14 - Otras características del atributo Identity El atributo "identity" permite indicar el valor de inicio de la secuencia y el incremento, para ello usamos la siguiente sintaxis: 45

create table libros( codigo int identity(100,2), titulo varchar(20), autor varchar(30), precio float );

Los valores comenzarán en "100" y se incrementarán de 2 en 2; es decir, el primer registro ingresado tendrá el valor "100", los siguientes "102", "104", "106", etc. La función "ident_seed()" retorna el valor de inicio del campo "identity" de la tabla que nombramos: select ident_seed('libros');

La función "ident_incr()" retorna el valor de incremento del campo "identity" de la tabla nombrada: select ident_incr('libros');

Hemos visto que en un campo declarado "identity" no puede ingresarse explícitamente un valor. Para permitir ingresar un valor en un campo de identidad se debe activar la opción "identity_insert": set identity_insert libros on;

Es decir, podemos ingresar valor en un campo "identity" seteando la opción "identity_insert" en "on". Cuando "identity_insert" está en ON, las instrucciones "insert" deben explicitar un valor: insert into libros (codigo,titulo) values (5,'Alicia en el pais de las maravillas');

Si no se coloca un valor para el campo de identidad, la sentencia no se ejecuta y aparece un mensaje de error: insert into libros (titulo,autor, editorial) values ('Matematica estas ahi','Paenza','Paidos');

El atributo "identity" no implica unicidad, es decir, permite repetición de valores; por ello hay que tener cuidado al explicitar un valor porque se puede ingresar un valor repetido. Para desactivar la opción "identity_insert" tipeamos: set identity_insert libros off;

Trabajamos con la tabla "libros" de una librería. Eliminamos la tabla "libros", si existe: if object_id('libros') is not null drop table libros;

Creamos la tabla especificando que el campos "codigo" genere valores secuenciales comenzando en 100 e incrementándose en 2 automáticamente: create table libros( codigo int identity(100,2), titulo varchar(20),

46

autor varchar(30), precio float );

Ingresamos algunos registros, recordando que si un campo tiene el atributo "identity" debemos omitirlo en la inserción: insert into libros (titulo,autor,precio) values('El aleph','Borges',23); insert into libros (titulo,autor,precio) values('Uno','Richard Bach',18); insert into libros (titulo,autor,precio) values('Aprenda PHP','Mario Molina',45.60);

Veamos cómo se almacenaron: select *from libros;

el código (dato que no ingresamos) se cargó automáticamente, iniciándose en 100 y siguiendo la secuencia de autoincremento (2). Para saber cuál es el valor de inicio del campo "identity" de la tabla "libros" tipeamos: select ident_seed('libros');

retorna "2". Si intentamos ingresar un valor para el campo "codigo": insert into libros (codigo,titulo,autor,precio) values(106,'Martin Fierro','Jose Hernandez',25);

generará un mensaje de error. Para permitir ingresar un valor en un campo de identidad activamos la opción "identity_insert": set identity_insert libros on;

Recordemos que si "identity_insert" está en ON, la instrucción "insert" DEBE explicitar un valor: insert into libros (codigo,titulo,autor) values (100,'Matematica estas ahi','Paenza');

Note que ingresamos un valor de código que ya existe; esto está permitido porque el atributo "identity" no implica unicidad. Ingresamos otro registro: insert into libros (codigo,titulo,autor) values (1,'Ilusiones','Richard Bach');

Note que ingresamos un valor de código menor al valor de inicio de la secuencia, está permitido. Si no se coloca un valor para el campo de identidad, la sentencia no se ejecuta y aparece un mensaje de error: insert into libros (titulo,autor) values ('Uno','Richard Bach');

Para desactivar la opción "identity_insert" tipeamos: 47

set identity_insert libros off;

Intentemos ingresar un valor para el campo "codigo": insert into libros (codigo,titulo,autor) values (300,'Uno','Richard Bach');

aparece un mensaje de error. Primer problema:

Una farmacia guarda información referente a sus medicamentos en una tabla llamada "medicamentos". 1- Elimine la tabla,si existe: if object_id('medicamentos') is not null drop table medicamentos; 2- Cree la tabla con un campo "codigo" que genere valores secuenciales automáticamente comenzando en 10 e incrementándose en 1: create table medicamentos( codigo integer identity(10,1), nombre varchar(20) not null, laboratorio varchar(20), precio float, cantidad integer ); 3- Ingrese los siguientes registros: insert into medicamentos (nombre, laboratorio,precio,cantidad) values('Sertal','Roche',5.2,100); insert into medicamentos (nombre, laboratorio,precio,cantidad) values('Buscapina','Roche',4.10,200); insert into medicamentos (nombre, laboratorio,precio,cantidad) values('Amoxidal 500','Bayer',15.60,100); 4- Verifique que SQL Server generó valores para el campo "código" de modo automático: select *from medicamentos; 5- Intente ingresar un registro con un valor para el campo "codigo". 6- Setee la opción "identity_insert" en "on" 7- Ingrese un nuevo registro sin valor para el campo "codigo" (no lo permite): insert into medicamentos (nombre, laboratorio,precio,cantidad) values('Amoxilina 500','Bayer',15.60,100); 8- Ingrese un nuevo registro con valor para el campo "codigo" repetido. 9- Use la función "ident_seed()" para averiguar el valor de inicio del campo "identity" de la tabla "medicamentos" 10- Emplee la función "ident_incr()" para saber cuál es el valor de incremento del campo "identity" de "medicamentos"

Segundo problema:

Un videoclub almacena información sobre sus películas en una tabla llamada "peliculas". 1- Elimine la tabla si existe: if object_id('peliculas') is not null drop table peliculas;

48

2- Créela definiendo un campo "codigo" autoincrementable que comience en 50 y se incremente en 3: create table peliculas( codigo int identity (50,3), titulo varchar(40), actor varchar(20), duracion int ); 3- Ingrese los siguientes registros: insert into peliculas (titulo,actor,duracion) values('Mision imposible','Tom Cruise',120); insert into peliculas (titulo,actor,duracion) values('Harry Potter y la piedra filosofal','Daniel R.',180); insert into peliculas (titulo,actor,duracion) values('Harry Potter y la camara secreta','Daniel R.',190); 4- Seleccione todos los registros y verifique la carga automática de los códigos: select *from peliculas; 5- Setee la opción "identity_insert" en "on" 6- Ingrese un registro con valor de código menor a 50. 7- Ingrese un registro con valor de código mayor al último generado. 8- Averigue el valor de inicio del campo "identity" de la tabla "peliculas". 9- Averigue el valor de incremento del campo "identity" de "peliculas". 10- Intente ingresar un registro sin valor para el campo código. 11- Desactive la opción se inserción para el campo de identidad. 12- Ingrese un nuevo registro y muestre todos los registros para ver cómo SQL Server siguió la secuencia tomando el último valor del campo como referencia.

15 - Truncate table Aprendimos que para borrar todos los registro de una tabla se usa "delete" sin condición "where". También podemos eliminar todos los registros de una tabla con "truncate table". Por ejemplo, queremos vaciar la tabla "libros", usamos: truncate table libros;

La sentencia "truncate table" vacía la tabla (elimina todos los registros) y conserva la estructura de la tabla. La diferencia con "drop table" es que esta sentencia borra la tabla, "truncate table" la vacía. La diferencia con "delete" es la velocidad, es más rápido "truncate table" que "delete" (se nota cuando la cantidad de registros es muy grande) ya que éste borra los registros uno a uno. 49

Otra diferencia es la siguiente: cuando la tabla tiene un campo "identity", si borramos todos los registros con "delete" y luego ingresamos un registro, al cargarse el valor en el campo de identidad, continúa con la secuencia teniendo en cuenta el valor mayor que se había guardado; si usamos "truncate table" para borrar todos los registros, al ingresar otra vez un registro, la secuencia del campo de identidad vuelve a iniciarse en 1. Por ejemplo, tenemos la tabla "libros" con el campo "codigo" definido "identity", y el valor más alto de ese campo es "2", si borramos todos los registros con "delete" y luego ingresamos un registro, éste guardará el valor de código "3"; si en cambio, vaciamos la tabla con "truncate table", al ingresar un nuevo registro el valor del código se iniciará en 1 nuevamente. Trabajamos con la tabla "libros" que almacena los datos de los libros de una librería. Eliminamos la tabla, si existe: if object_id('libros') is not null drop table libros;

Creamos la tabla: create table libros( codigo int identity, titulo varchar(30), autor varchar(20), editorial varchar(15), precio float );

Agregamos algunos registros: insert into libros (titulo,autor,editorial,precio) values ('El aleph','Borges','Emece',25.60); insert into libros (titulo,autor,editorial,precio) values ('Uno','Richard Bach','Planeta',18);

Seleccionamos todos los registros: select *from libros;

Truncamos la tabla: truncate table libros;

Ingresamos nuevamente algunos registros: insert into libros (titulo,autor,editorial,precio) values ('El aleph','Borges','Emece',25.60); insert into libros (titulo,autor,editorial,precio) values ('Uno','Richard Bach','Planeta',18);

Si seleccionamos todos los registros vemos que la secuencia se reinició en 1: select *from libros;

Eliminemos todos los registros con "delete": delete from libros;

Ingresamos nuevamente algunos registros: 50

insert into libros (titulo,autor,editorial,precio) values ('El aleph','Borges','Emece',25.60); insert into libros (titulo,autor,editorial,precio) values ('Uno','Richard Bach','Planeta',18);

Seleccionamos todos los registros y vemos que la secuencia continuó: select *from libros;

Primer problema: Un instituto de enseñanza almacena los datos de sus estudiantes en una tabla llamada "alumnos". 1- Elimine la tabla "alumnos" si existe: if object_id('alumnos') is not null drop table alumnos; 2- Cree la tabla con la siguiente estructura: create table alumnos( legajo int identity, documento varchar(8), nombre varchar(30), domicilio varchar(30) ); 3- Ingrese los siguientes registros y muéstrelos para ver la secuencia de códigos: insert into alumnos (documento,nombre,domicilio) values('22345345','Perez Mariana','Colon 234'); insert into alumnos (documento,nombre,domicilio) values('23545345','Morales Marcos','Avellaneda 348'); insert into alumnos (documento,nombre,domicilio) values('24356345','Gonzalez Analia','Caseros 444'); insert into alumnos (documento,nombre,domicilio) values('25666777','Torres Ramiro','Dinamarca 209'); 4- Elimine todos los registros con "delete". 5- Ingrese los siguientes registros y selecciónelos para ver cómo SQL Server generó los códigos: insert into alumnos (documento,nombre,domicilio) values('22345345','Perez Mariana','Colon 234'); insert into alumnos (documento,nombre,domicilio) values('23545345','Morales Marcos','Avellaneda 348'); insert into alumnos (documento,nombre,domicilio) values('24356345','Gonzalez Analia','Caseros 444'); insert into alumnos (documento,nombre,domicilio) values('25666777','Torres Ramiro','Dinamarca 209'); select *from alumnos; 6- Elimine todos los registros con "truncate table". 7- Ingrese los siguientes registros y muestre todos los registros para ver que SQL Server reinició la secuencia del campo "identity": insert into alumnos (documento,nombre,domicilio) values('22345345','Perez Mariana','Colon 234'); insert into alumnos (documento,nombre,domicilio) values('23545345','Morales Marcos','Avellaneda 348'); insert into alumnos (documento,nombre,domicilio) values('24356345','Gonzalez Analia','Caseros 444'); insert into alumnos (documento,nombre,domicilio) values('25666777','Torres Ramiro','Dinamarca 209'); select *from alumnos;

51

Segundo problema: Un comercio que vende artículos de computación registra los datos de sus artículos en una tabla con ese nombre. 1- Elimine "articulos", si existe: if object_id('articulos') is not null drop table articulos; 2- Cree la tabla, con la siguiente estructura: create table articulos( codigo integer identity, nombre varchar(20), descripcion varchar(30), precio float ); 3- Ingrese algunos registros: insert into articulos (nombre, descripcion, precio) values ('impresora','Epson Stylus C45',400.80); insert into articulos (nombre, descripcion, precio) values ('impresora','Epson Stylus C85',500); 4- Elimine todos los registros con "truncate table". 5- Ingrese algunos registros y muéstrelos para ver que la secuencia de códigos se reinicia: insert into articulos (nombre, descripcion, precio) values ('monitor','Samsung 14',800); insert into articulos (nombre, descripcion, precio) values ('teclado','ingles Biswal',100); insert into articulos (nombre, descripcion, precio) values ('teclado','español Biswal',90); select *from articulos; 6- Elimine todos los registros con "delete". 7- Ingrese algunos registros y muéstrelos para ver que la secuencia de códigos continua: insert into articulos (nombre, descripcion, precio) values ('monitor','Samsung 14',800); insert into articulos (nombre, descripcion, precio) values ('teclado','ingles Biswal',100); insert into articulos (nombre, descripcion, precio) values ('teclado','español Biswal',90); select *from articulos;

16 - Otros tipos de datos en SQL Server Ya explicamos que al crear una tabla debemos elegir la estructura adecuada, esto es, definir los campos y sus tipos más precisos, según el caso. El tipo de dato especificado en la definición de cada campo indica los valores permitidos para cada uno de ellos. Hasta ahora hemos visto 3 tipos de datos: varchar, integer y float. Hay más tipos, incluso, subtipos. 52

Los valores que podemos guardar son: 1. TEXTO: Para almacenar texto usamos cadenas de caracteres. Las cadenas se colocan entre comillas simples. Podemos almacenar letras, símbolos y dígitos con los que no se realizan operaciones matemáticas, por ejemplo, códigos de identificación, números de documentos, números telefónicos. SQL Server ofrece los siguientes tipos: char, nchar, varchar, nvarchar, text y ntext. 2. NUMEROS: Existe variedad de tipos numéricos para representar enteros, decimales, monedas. Para almacenar valores enteros, por ejemplo, en campos que hacen referencia a cantidades, precios, etc., usamos el tipo integer (y sus subtipos: tinyint, smallint y bigint). Para almacenar valores con decimales exactos, utilizamos: numeric o decimal (son equivalentes). Para guardar valores decimales aproximados: float y real. Para almacenar valores monetarios: money y smallmoney. 3. FECHAS y HORAS: para guardar fechas y horas SQL Server dispone de 2 tipos: datetime y smalldatetime. Existen otros tipos de datos que analizaremos en secciones próximas. Entonces, cuando creamos una tabla y definir sus campos debemos elegir el tipo de dato más preciso. Por ejemplo, si necesitamos almacenar nombres usamos texto; si un campo numérico almacenará solamente valores enteros el tipo "integer" es más adecuado que, por ejemplo un "float"; si necesitamos almacenar precios, lo más lógico es utilizar el tipo "money". A continuación analizaremos en detalle cada tipo de dato básicos.

17 - Tipo de dato (texto) Ya explicamos que al crear una tabla debemos elegir la estructura adecuada, esto es, definir los campos y sus tipos más precisos, según el caso. Para almacenar TEXTO usamos cadenas de caracteres. Las cadenas se colocan entre comillas simples. Podemos almacenar letras, símbolos y dígitos con los que no se realizan operaciones matemáticas, por ejemplo, códigos de identificación, números de documentos, números telefónicos. Tenemos los siguientes tipos: 1. varchar(x): define una cadena de caracteres de longitud variable en la cual determinamos el máximo de caracteres con el argumento "x" que va entre paréntesis. Si se omite el argumento coloca 1 por defecto. Su rango va de 1 a 8000 caracteres. 2. char(x): define una cadena de longitud fija determinada por el argumento "x". Si se omite el argumento coloca 1 por defecto. Su rango es de 1 a 8000 caracteres. Si la longitud es invariable, es conveniente utilizar el tipo char; caso contrario, el tipo varchar. 53

3. 4. 5. 6.

Ocupa tantos bytes como se definen con el argumento "x". "char" viene de character, que significa caracter en inglés. text: guarda datos binarios de longitud variable, puede contener hasta 2000000000 caracteres. No admite argumento para especificar su longitud. nvarchar(x): es similar a "varchar", excepto que permite almacenar caracteres Unicode, su rango va de 0 a 4000 caracteres porque se emplean 2 bytes por cada caracter. nchar(x): es similar a "char" excpeto que acepta caracteres Unicode, su rango va de 0 a 4000 caracteres porque se emplean 2 bytes por cada caracter. ntext: es similar a "text" excepto que permite almacenar caracteres Unicode, puede contener hasta 1000000000 caracteres. No admite argumento para especificar su longitud.

En general se usarán los 3 primeros. Si intentamos almacenar en un campo una cadena de caracteres de mayor longitud que la definida, aparece un mensaje indicando tal situación y la sentencia no se ejecuta. Por ejemplo, si definimos un campo de tipo varchar(10) y le asignamos la cadena 'Aprenda PHP' (11 caracteres), aparece un mensaje y la sentencia no se ejecuta. Si ingresamos un valor numérico (omitiendo las comillas), lo convierte a cadena y lo ingresa como tal. Por ejemplo, si en un campo definido como varchar(5) ingresamos el valor 12345, lo toma como si hubiésemos tipeado '12345', igualmente, si ingresamos el valor 23.56, lo convierte a '23.56'. Si el valor numérico, al ser convertido a cadena supera la longitud definida, aparece un mensaje de error y la sentencia no se ejecuta. Es importante elegir el tipo de dato adecuado según el caso, el más preciso. Para almacenar cadenas que varían en su longitud, es decir, no todos los registros tendrán la misma longitud en un campo determinado, se emplea "varchar" en lugar de "char". Por ejemplo, en campos que guardamos nombres y apellidos, no todos los nombres y apellidos tienen la misma longitud. Para almacenar cadenas que no varían en su longitud, es decir, todos los registros tendrán la misma longitud en un campo determinado, se emplea "char". Por ejemplo, definimos un campo "codigo" que constará de 5 caracteres, todos los registros tendrán un código de 5 caracteres, ni más ni menos. Para almacenar valores superiores a 8000 caracteres se debe emplear "text". Tipo Bytes de almacenamiento _______________________________________ varchar(x) 0 a 8K char(x) 0 a 8K text 0 a 2GB nvarchar(x) 0 a 8K nchar(x) 0 a 8K ntext 0 a 2GB

Un comercio que tiene un stand en una feria registra en una tabla llamada "visitantes" algunos datos de las personas que visitan o compran en su stand para luego enviarle publicidad de sus productos. 54

Eliminamos la tabla "visitantes", si existe: if object_id('visitantes') is not null drop table visitantes;

Creamos con la siguiente estructura: create table visitantes( nombre varchar(30), edad integer, sexo char(1), domicilio varchar(30), ciudad varchar(20), telefono varchar(11) );

Los campos "nombre", "domicilio" y "ciudad" almacenarán valores cuya longitud varía, por ello elegimos el tipo "varchar" y le damos a cada uno una longitud máxima estimando su tamaño. El campo "sexo" se define de tipo "char", porque necesitamos solamente 1 caracter "f" o "m", que siempre será fijo. El campo "telefono" también se define como varchar porque no todos los números telefónicos tienen la misma longitud. Intentamos ingresar una cadena de mayor longitud que la definida: insert into visitantes (nombre,edad,sexo,domicilio,ciudad,telefono) values ('Juan Juarez',32,'masc','Avellaneda 789','Cordoba','4234567');

aparece un mensaje de error y la sentencia no se ejecuta Ingresamos un número telefónico olvidando las comillas, es decir, como un valor numérico: insert into visitantes (nombre,edad,sexo,domicilio,ciudad,telefono) values ('Marcela Morales',43,'f','Colon 456','Cordoba',4567890);

lo convierte a cadena, veámoslo: select *from visitantes;

Primer problema:

Una concesionaria de autos vende autos usados y almacena los datos de los autos en una tabla llamada "autos". 1- Elimine la tabla "autos" si existe: if object_id('autos') is not null drop table autos; 2- Cree la tabla eligiendo el tipo de dato adecuado para cada campo, estableciendo el campo "patente" como clave primaria: create table autos( patente char(6), marca varchar(20), modelo char(4), precio float, primary key (patente) ); Hemos definido el campo "patente" de tipo "char" y no "varchar" porque la cadena de caracteres siempre tendrá la misma longitud (6 caracteres). Lo mismo sucede con el campo "modelo", en el cual

55

almacenaremos el año, necesitamos 4 caracteres fijos. 3- Ingrese los siguientes registros: insert into autos values('ACD123','Fiat 128','1970',15000); insert into autos values('ACG234','Renault 11','1990',40000); insert into autos values('BCD333','Peugeot 505','1990',80000); insert into autos values('GCD123','Renault Clio','1990',70000); insert into autos values('BCC333','Renault Megane','1998',95000); insert into autos values('BVF543','Fiat 128','1975',20000); 4- Seleccione todos los autos del año 1990: select *from autos where modelo='1990';

Segundo problema: Una empresa almacena los datos de sus clientes en una tabla llamada "clientes". 1- Elimine la tabla "clientes" si existe: if object_id('clientes') is not null drop table clientes; 2- Créela eligiendo el tipo de dato más adecuado para cada campo: create table clientes( documento char(8), apellido varchar(20), nombre varchar(20), domicilio varchar(30), telefono varchar (11) ); 3- Analice la definición de los campos. Se utiliza char(8) para el documento porque siempre constará de 8 caracteres. Para el número telefónico se usar "varchar" y no un tipo numérico porque si bien es un número, con él no se realizarán operaciones matemáticas. 4- Ingrese algunos registros: insert into clientes values('2233344','Perez','Juan','Sarmiento 980','4342345'); insert into clientes (documento,apellido,nombre,domicilio) values('2333344','Perez','Ana','Colon 234'); insert into clientes values('2433344','Garcia','Luis','Avellaneda 1454','4558877'); insert into clientes values('2533344','Juarez','Ana','Urquiza 444','4789900'); 5- Seleccione todos los clientes de apellido "Perez" (2 registros): select *from clientes where apellido='Perez';

18 - Tipo de dato (numérico) 56

Ya explicamos que al crear una tabla debemos elegir la estructura adecuada, esto es, definir los campos y sus tipos más precisos, según el caso. Para almacenar valores NUMERICOS SQL Server dispone de varios tipos. Para almacenar valores ENTEROS, por ejemplo, en campos que hacen referencia a cantidades, usamos: 1) integer o int: su rango es de -2000000000 a 2000000000 aprox. El tipo "integer" tiene subtipos: - smallint: Puede contener hasta 5 digitos. Su rango va desde ?32000 hasta 32000 aprox. - tinyint: Puede almacenar valores entre 0 y 255. - bigint: De ?9000000000000000000 hasta 9000000000000000000 aprox. Para almacenar valores numéricos EXACTOS con decimales, especificando la cantidad de cifras a la izquierda y derecha del separador decimal, utilizamos: 2) decimal o numeric (t,d): Pueden tener hasta 38 digitos, guarda un valor exacto. El primer argumento indica el total de dígitos y el segundo, la cantidad de decimales. Por ejemplo, si queremos almacenar valores entre -99.99 y 99.99 debemos definir el campo como tipo "decimal(4,2)". Si no se indica el valor del segundo argumento, por defecto es "0". Por ejemplo, si definimos "decimal(4)" se pueden guardar valores entre -9999 y 9999. El rango depende de los argumentos, también los bytes que ocupa. Se utiliza el punto como separador de decimales. Si ingresamos un valor con más decimales que los permitidos, redondea al más cercano; por ejemplo, si definimos "decimal(4,2)" e ingresamos el valor "12.686", guardará "12.69", redondeando hacia arriba; si ingresamos el valor "12.682", guardará "12.67", redondeando hacia abajo. Para almacenar valores numéricos APROXIMADOS con decimales utilizamos: 3) float y real: De 1.79E+308 hasta 1.79E+38. Guarda valores aproximados. 4) real: Desde 3.40E+308 hasta 3.40E+38. Guarda valores aproximados. Para almacenar valores MONETARIOS empleamos: 5) money: Puede tener hasta 19 digitos y sólo 4 de ellos puede ir luego del separador decimal; entre ?900000000000000.5808 aprox y 900000000000000.5807. 6) smallmoney: Entre ?200000.3648 y 200000.3647 aprox. Para todos los tipos numéricos: - si intentamos ingresar un valor fuera de rango, no lo permite. - si ingresamos una cadena, SQL Server intenta convertirla a valor numérico, si dicha cadena consta solamente de dígitos, la conversión se realiza, luego verifica si está dentro del rango, si es así, la ingresa, sino, muestra un mensaje de error y no ejecuta la sentencia. Si la cadena contiene caracteres que SQL Server no puede convertir a valor numérico, muestra un mensaje de error y la sentencia no se ejecuta. Por ejemplo, definimos un campo de tipo decimal(5,2), si ingresamos la cadena '12.22', la convierte al valor numérico 12.22 y la ingresa; si intentamos ingresar la cadena '1234.56', la convierte al valor numérico 1234.56, pero como el máximo valor permitido es 999.99, muestra un 57

mensaje indicando que está fuera de rango. Si intentamos ingresar el valor '12y.25', SQL Server no puede realizar la conversión y muestra un mensaje de error. Es importante elegir el tipo de dato adecuado según el caso, el más preciso. Por ejemplo, si un campo numérico almacenará valores positivos menores a 255, el tipo "int" no es el más adecuado, conviene el tipo "tinyint", de esta manera usamos el menor espacio de almacenamiento posible. Si vamos a guardar valores monetarios menores a 200000 conviene emplear "smallmoney" en lugar de "money". Tipo Bytes de almacenamiento _______________________________________ int 4 smallint 2 tinyint 1 bigint 8 decimal float real

2 a 17 4u8 4u8

money 8 smallmoney 4

Trabajamos con la tabla "libros" de una librería. Eliminamos la tabla, si existe: if object_id('libros') is not null drop table libros;

Creamos la tabla con la siguiente estructura: create table libros( codigo smallint identity, titulo varchar(40) not null, autor varchar(30), editorial varchar(15), precio smallmoney, cantidad tinyint );

Note que definimos el campo "codigo" de tipo "smallint", esto es porque estimamos que no tendremos más de 30000 libros. Si necesitáramos un rango mayor podemos emplear "int". Como en el campo "precio" no almacenaremos valores mayores a 200000, definimos el campo de tipo "smallmoney". También podemos definirlo de tipo "decimal(5,2)" porque el máximo precio no superará los 999.99. El tipo "float" no es el más adecuado para representar precios porque no es exacto y muestra muchos decimales innecesarios. Como los valores para el campo "cantidad" no superarán los 255, definimos el campo de tipo "tinyint". Si estimamos que tendremos más cantidad de libros podemos emplear "smallint" que tiene un rango mayor; no es adecuado usar int (cuyo rango llega hasta 4000 millones aprox.), porque ocuparíamos más espacio (4 bytes). 58

Analicemos la inserción de datos numéricos. Intentemos ingresar un valor fuera del rango definido, una cantidad que supera el rango del tipo "tinyint", el valor 260: insert into libros (titulo,autor,editorial,precio,cantidad) values('El aleph','Borges','Emece',25.60,260);

aparece un mensaje de error y la inserción no se ejecuta. Intentamos ingresar un precio que supera el rango del tipo "smallmoney", el valor 250000: insert into libros (titulo,autor,editorial,precio,cantidad) values('El aleph','Borges','Emece',250000,100);

aparece un mensaje de error y la instrucción no se ejecuta. Intentamos ingresar una cadena que SQL Server no pueda convertir a valor numérico en el campo "precio" (error): insert into libros (titulo,autor,editorial,precio,cantidad) values('Uno','Richard Bach','Planeta','a50.30',100);

Ingresamos una cadena en el campo "cantidad": insert into libros (titulo,autor,editorial,precio,cantidad) values('Uno','Richard Bach','Planeta',50.30,'100');

lo convierte a valor numérico. Primer problema: Un banco tiene registrados las cuentas corrientes de sus clientes en una tabla llamada "cuentas". La tabla contiene estos datos: Número de Cuenta Documento Nombre Saldo ______________________________________________________________ 1234 25666777 Pedro Perez 500000.60 2234 27888999 Juan Lopez -250000 3344 27888999 Juan Lopez 4000.50 3346 32111222 Susana Molina 1000 1- Elimine la tabla "cuentas" si existe: if object_id('cuentas') is not null drop table cuentas; 2- Cree la tabla eligiendo el tipo de dato adecuado para almacenar los datos descriptos arriba: - Número de cuenta: entero, no nulo, no puede haber valores repetidos, clave primaria; - Documento del propietario de la cuenta: cadena de caracteres de 8 de longitud (siempre 8), no nulo; - Nombre del propietario de la cuenta: cadena de caracteres de 30 de longitud, - Saldo de la cuenta: valores altos con decimales. 3- Ingrese los siguientes registros: insert into cuentas(numero,documento,nombre,saldo) values('1234','25666777','Pedro Perez',500000.60); insert into cuentas(numero,documento,nombre,saldo) values('2234','27888999','Juan Lopez',-250000); insert into cuentas(numero,documento,nombre,saldo) values('3344','27888999','Juan Lopez',4000.50); insert into cuentas(numero,documento,nombre,saldo)

59

values('3346','32111222','Susana Molina',1000); Note que hay dos cuentas, con distinto número de cuenta, de la misma persona. 4- Seleccione todos los registros cuyo saldo sea mayor a "4000" (2 registros) 5- Muestre el número de cuenta y saldo de todas las cuentas cuyo propietario sea "Juan Lopez" (2 registros) 6- Muestre las cuentas con saldo negativo (1 registro) 7- Muestre todas las cuentas cuyo número es igual o mayor a "3000" (2 registros): select *from cuentas where numero>=3000;

Segundo problema:

Una empresa almacena los datos de sus empleados en una tabla "empleados" que guarda los siguientes datos: nombre, documento, sexo, domicilio, sueldobasico. 1- Elimine la tabla, si existe: if object_id('empleados') is not null drop table empleados; 2- Cree la tabla eligiendo el tipo de dato adecuado para cada campo: create table empleados( nombre varchar(30), documento char(8), sexo char(1), domicilio varchar(30), sueldobasico decimal(7,2),--máximo estimado 99999.99 cantidadhijos tinyint--no superará los 255 ); 3- Ingrese algunos registros: insert into empleados (nombre,documento,sexo,domicilio,sueldobasico,cantidadhijos) values ('Juan Perez','22333444','m','Sarmiento 123',500,2); insert into empleados (nombre,documento,sexo,domicilio,sueldobasico,cantidadhijos) values ('Ana Acosta','24555666','f','Colon 134',850,0); insert into empleados (nombre,documento,sexo,domicilio,sueldobasico,cantidadhijos) values ('Bartolome Barrios','27888999','m','Urquiza 479',10000.80,4); 4- Ingrese un valor de "sueldobasico" con más decimales que los definidos (redondea los decimales al valor más cercano 800.89): insert into empleados (nombre,documento,sexo,domicilio,sueldobasico,cantidadhijos) values ('Susana Molina','29000555','f','Salta 876',800.888,3); 5- Intente ingresar un sueldo que supere los 7 dígitos (no lo permite) 6- Muestre todos los empleados cuyo sueldo no supere los 900 pesos (1 registro): 7- Seleccione los nombres de los empleados que tengan hijos (3 registros):

19 - Tipo de dato (fecha y hora) Ya explicamos que al crear una tabla debemos elegir la estructura adecuada, esto es, definir los campos y sus tipos más precisos, según el caso. 60

Para almacenar valores de tipo FECHA Y HORA SQL Server dispone de dos tipos: 1) datetime: puede almacenar valores desde 01 de enero de 1753 hasta 31 de diciembre de 9999. 2) smalldatetime: el rango va de 01 de enero de 1900 hasta 06 de junio de 2079. Las fechas se ingresan entre comillas simples. Para almacenar valores de tipo fecha se permiten como separadores "/", "-" y ".". SQL Server reconoce varios formatos de entrada de datos de tipo fecha. Para establecer el orden de las partes de una fecha (dia, mes y año) empleamos "set dateformat". Estos son los formatos: -mdy: 4/15/96 (mes y día con 1 ó 2 dígitos y año con 2 ó 4 dígitos), -myd: 4/96/15, -dmy: 15/4/1996 -dym: 15/96/4, -ydm: 96/15/4, -ydm: 1996/15/4,

Para ingresar una fecha con formato "día-mes-año", tipeamos: set dateformat dmy;

El formato por defecto es "mdy". Todos los valores de tipo "datetime" se muestran en formato "año-mes-día hora:minuto:segundo .milisegundos", independientemente del formato de ingreso que hayamos seteado. Podemos ingresar una fecha, sin hora, en tal caso la hora se guarda como "00:00:00". Por ejemplo, si ingresamos '25-12-01' (año de 2 dígitos), lo mostrará así: '2001-12-25 00:00:00.000'. Podemos ingresar una hora sin fecha, en tal caso, coloca la fecha "1900-01-01". Por ejemplo, si ingresamos '10:15', mostrará '1900-01-01 10:15.000'. Podemos emplear los operadores relacionales vistos para comparar fechas. Tipo Bytes de almacenamiento _______________________________________ datetime 8 smalldatetime 4

Una empresa almacena los datos de sus empleados en una tabla "empleados". Eliminamos la tabla, si existe: if object_id('empleados') is not null drop table empleados;

Creamos la tabla eligiendo el tipo de dato adecuado para cada campo: create table empleados( nombre varchar(20), documento char(8), fechaingreso datetime );

Seteamos el formato de la fecha para que guarde día, mes y año: set dateformat dmy;

61

Ingresamos algunos registros: insert insert insert insert

into into into into

empleados empleados empleados empleados

values('Ana Gomez','22222222','12-01-1980'); values('Bernardo Huerta','23333333','15-03-81'); values('Carla Juarez','24444444','20/05/1983'); values('Daniel Lopez','25555555','2.5.1990');

El segundo registro ingresado tiene 2 dígitos correspondientes al año; en el tercero empleamos la barra ('/') como separador y en el cuarto empleamos como separador el punto ('.') y colocamos un sólo dígito en la part del día y el mes. Recuperamos los registros: select *from empleados;

Note que el formato de visualización es "y-m-d". Mostramos los datos de los empleados cuya fecha de ingreso es anterior a '01-01-1985': select *from empleados where fechaingreso=0) );

En el ejemplo anterior creamos: - una restricción "default" para el campo "precio" (restricción a nivel de campo); - una restricción "primary key" con índice agrupado para el campo "codigo" (a nivel de tabla); - una restricción "unique" con índice no agrupado (por defecto) para los campos "titulo" y "codigoautor" (a nivel de tabla); - una restricción "foreign key" para establecer el campo "codigoeditorial" como clave externa que haga referencia al campo "codigo" de "editoriales y permita actualizaciones en cascada y no eliminaciones (por defecto "no action"); - una restricción "foreign key" para establecer el campo "codigoautor" como clave externa que haga referencia al campo "codigo" de "autores" y permita actualizaciones en cascada y no eliminaciones; - una restricción "check" para el campo "precio" que no admita valores negativos; Si definimos una restricción "foreign key" al crear una tabla, la tabla referenciada debe existir. Trabajamos con las tablas "libros", "autores" y "editoriales" de una librería: Eliminamos las tablas si existen: if object_id('libros') is not null drop table libros; if object_id('editoriales') is not null drop table editoriales; if object_id('autores') is not null drop table autores;

Creamos la tabla "editoriales" con una restricción "primary key": create table editoriales( codigo tinyint not null, nombre varchar(30), constraint PK_editoriales primary key (codigo) );

Creamos la tabla "autores" con una restricción "primary key", una "unique" y una "check": create table autores( codigo int not null constraint CK_autores_codigo check (codigo>=0), nombre varchar(30) not null, constraint PK_autores_codigo primary key (codigo), constraint UQ_autores_nombre unique (nombre), );

Aplicamos varias restricciones cuando creamos la tabla "libros": 250

create table libros( codigo int identity, titulo varchar(40), codigoautor int not null, codigoeditorial tinyint not null, precio decimal(5,2) constraint DF_libros_precio default (0), constraint PK_libros_codigo primary key clustered (codigo), constraint UQ_libros_tituloautor unique (titulo,codigoautor), constraint FK_libros_editorial foreign key (codigoeditorial) references editoriales(codigo) on update cascade, constraint FK_libros_autores foreign key (codigoautor) references autores(codigo) on update cascade, constraint CK_libros_precio_positivo check (precio>=0) );

Veamos las restricciones de "editoriales": sp_helpconstraint editoriales;

Aparece la restricción "primary key" para el campo "codigo" y la restricción "foreign key" de "libros" "FK_libros_editorial" que referencia esta tabla. Veamos las restricciones de "autores": sp_helpconstraint autores;

Aparecen 4 restricciones: una restricción "check" para el campo "codigo", una restricción "primary key" para el campo "codigo", una restricción "unique" para el campo "nombre" y la restricción "foreign key" de "libros" "FK_libros_autores" que referencia esta tabla. Veamos las restricciones de "libros": sp_helpconstraint libros;

Aparecen 6 restricciones: una restricción "check" sobre el campo "precio", una "default" sobre el campo "precio", una restricción "foreign key" que establece el campo "codigoeditorial" como clave externa que hace referencia al campo "codigo" de "editoriales" y permite actualizaciones en cascada y no eliminaciones, una restricción "foreign key" que establece el campo "codigoautor" como clave externa que hace referencia al campo "codigo" de "autores" y permite actualizaciones en cascada y no eliminaciones, una restricción "primary key" con índice agrupado para el campo "codigo" y una restricción "unique" con índice no agrupado para los campos "titulo" y "codigoautor". Recuerde que si definimos una restricción "foreign key" al crear una tabla, la tabla referenciada debe existir, por ello creamos las tablas "editoriales" y "autores" antes que "libros". También debemos ingresar registros en las tablas "autores" y "editoriales" antes que en "libros", a menos que deshabilitemos la restricción "foreign key". Primer problema: Un club de barrio tiene en su sistema 4 tablas:

251

- "socios": en la cual almacena documento, número, nombre y domicilio de cada socio; - "deportes": que guarda un código, nombre del deporte, día de la semana que se dicta y documento del profesor instructor; - "profesores": donde se guarda el documento, nombre y domicilio de los profesores e - "inscriptos": que almacena el número de socio, el código de deporte y si la matricula está paga o no. 1- Elimine las tablas si existen: if object_id('inscriptos') is not null drop table inscriptos; if object_id('socios') is not null drop table socios; if object_id('profesores') is not null drop table profesores; if object_id('deportes') is not null drop table deportes; 2- Considere que: - un socio puede inscribirse en varios deportes, pero no dos veces en el mismo. - un socio tiene un documento único y un número de socio único. - el documento del socio debe contener 8 dígitos. - un deporte debe tener asignado un profesor que exista en "profesores" o "null" si aún no tiene un instructor definido. - el campo "dia" de "deportes" puede ser: lunes, martes, miercoles, jueves, viernes o sabado. - el campo "dia" de "deportes" por defecto debe almacenar 'sabado'. - un profesor puede ser instructor de varios deportes o puede no dictar ningún deporte. - un profesor no puede estar repetido en "profesores". - el documento del profesor debe contener 8 dígitos. - un inscripto debe ser socio, un socio puede no estar inscripto en ningún deporte. - una inscripción debe tener un valor en socio existente en "socios" y un deporte que exista en "deportes". - el campo "matricula" de "inscriptos" debe aceptar solamente los caracteres 's' o 'n'. 3- Cree las tablas con las restricciones necesarias: create table profesores( documento char(8) not null, nombre varchar(30), domicilio varchar(30), constraint CK_profesores_documento_patron check (documento like '[0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'), constraint PK_profesores_documento primary key (documento) ); create table deportes( codigo tinyint identity, nombre varchar(20) not null, dia varchar(30) constraint DF_deportes_dia default('sabado'), profesor char(8),--documento del profesor constraint CK_deportes_dia_lista check (dia in ('lunes','martes','miercoles','jueves','viernes','sabado')), constraint PK_deportes_codigo primary key (codigo) ); create table socios( numero int identity, documento char(8), nombre varchar(30),

252

domicilio varchar(30), constraint CK_documento_patron check (documento like '[0-9][0-9][0-9][0-9][0-9][0-9] [0-9][0-9]'), constraint PK_socios_numero primary key nonclustered(numero), constraint UQ_socios_documento unique clustered(documento) ); create table inscriptos( numerosocio int not null, codigodeporte tinyint, matricula char(1), constraint PK_inscriptos_numerodeporte primary key clustered (numerosocio,codigodeporte), constraint FK_inscriptos_deporte foreign key (codigodeporte) references deportes(codigo) on update cascade, constraint FK_inscriptos_socios foreign key (numerosocio) references socios(numero) on update cascade on delete cascade, constraint CK_matricula_valores check (matricula in ('s','n')) ); 4- Ingrese registros en insert into profesores insert into profesores insert into profesores

"profesores": values('21111111','Andres Acosta','Avellaneda 111'); values('22222222','Betina Bustos','Bulnes 222'); values('23333333','Carlos Caseros','Colon 333');

5- Ingrese registros en "deportes". Ingrese el mismo día para distintos deportes, un deporte sin día confirmado, un deporte sin profesor definido: insert into deportes values('basquet','lunes',null); insert into deportes values('futbol','lunes','23333333'); insert into deportes values('natacion',null,'22222222'); insert into deportes values('padle',default,'23333333'); insert into deportes (nombre,dia) values('tenis','jueves'); 6- Ingrese registros en "socios": insert into socios values('30111111','Ana Acosta','America 111'); insert into socios values('30222222','Bernardo Bueno','Bolivia 222'); insert into socios values('30333333','Camila Conte','Caseros 333'); insert into socios values('30444444','Daniel Duarte','Dinamarca 444'); 7- Ingrese registros en "inscriptos". Inscriba a un socio en distintos deportes, inscriba varios socios en el mismo deporte. insert into inscriptos values(1,3,'s'); insert into inscriptos values(1,5,'s'); insert into inscriptos values(2,1,'s'); insert into inscriptos values(4,1,'n'); insert into inscriptos values(4,4,'s'); 8- Realice un "join" (del tipo que sea necesario) para mostrar todos los datos del socio junto con el nombre de los deportes en los cuales está inscripto, el día que tiene que asistir y el nombre del profesor que lo instruirá. 5 registros.

253

9- Realice la misma consulta anterior pero incluya los socios que no están inscriptos en ningún deporte. 6 registros. 10- Muestre todos los datos de los profesores, incluido el deporte que dicta y el día, incluyendo los profesores que no tienen asignado ningún deporte. 4 registros. 11- Muestre todos los deportes y la cantidad de inscriptos, incluyendo aquellos deportes para los cuales no hay inscriptos. 5 registros. 12- Muestre las restricciones de "socios". 3 restricciones y 1 "foreign key" de "inscriptos" que la referencia. 13- Muestre las restricciones de "deportes". 3 restricciones y 1 "foreign key" de "inscriptos" que la referencia. 14- Muestre las restricciones de "profesores". 2 restricciones. 15- Muestre las restricciones de "inscriptos". 4 restricciones.

82 - Unión El operador "union" combina el resultado de dos o más instrucciones "select" en un único resultado. Se usa cuando los datos que se quieren obtener pertenecen a distintas tablas y no se puede acceder a ellos con una sola consulta. Es necesario que las tablas referenciadas tengan tipos de datos similares, la misma cantidad de campos y el mismo orden de campos en la lista de selección de cada consulta. No se incluyen las filas duplicadas en el resultado, a menos que coloque la opción "all". Se deben especificar los nombres de los campos en la primera instrucción "select". Puede emplear la cláusula "order by". Puede dividir una consulta compleja en varias consultas "select" y luego emplear el operador "union" para combinarlas. Una academia de enseñanza almacena los datos de los alumnos en una tabla llamada "alumnos" y los datos de los profesores en otra denominada "profesores". La academia necesita el nombre y domicilio de profesores y alumnos para enviarles una tarjeta de invitación. Para obtener los datos necesarios de ambas tablas en una sola consulta necesitamos realizar una unión: 254

select nombre, domicilio from alumnos union select nombre, domicilio from profesores;

El primer "select" devuelve el nombre y domicilio de todos los alumnos; el segundo, el nombre y domicilio de todos los profesores. Los encabezados del resultado de una unión son los que se especifican en el primer "select". Una academia de enseñanza almacena los datos de los alumnos en una tabla llamada "alumnos" y los datos de los profesores en otra denominada "profesores". Eliminamos las tablas si existen: if object_id('alumnos') is not null drop table alumnos; if object_id('profesores') is not null drop table profesores;

Creamos las tablas: create table profesores( documento varchar(8) not null, nombre varchar (30), domicilio varchar(30), primary key(documento) ); create table alumnos( documento varchar(8) not null, nombre varchar (30), domicilio varchar(30), primary key(documento) );

Ingresamos algunos registros: insert insert insert insert insert insert insert

into into into into into into into

alumnos values('30000000','Juan Perez','Colon 123'); alumnos values('30111111','Marta Morales','Caseros 222'); alumnos values('30222222','Laura Torres','San Martin 987'); alumnos values('30333333','Mariano Juarez','Avellaneda 34'); alumnos values('23333333','Federico Lopez','Colon 987'); profesores values('22222222','Susana Molina','Sucre 345'); profesores values('23333333','Federico Lopez','Colon 987');

La academia necesita el nombre y domicilio de profesores y alumnos para enviarles una tarjeta de invitación. Empleamos el operador "union" para obtener dicha información de ambas tablas: select nombre, domicilio from alumnos union select nombre, domicilio from profesores;

Note que existe un profesor que también está presente en la tabla "alumnos"; dicho registro aparece una sola vez en el resultado de "union". Si queremos que las filas duplicadas aparezcan, debemos emplear "all": select nombre, domicilio from alumnos union all select nombre, domicilio from profesores;

Ordenamos por domicilio: 255

select nombre, domicilio from alumnos union select nombre, domicilio from profesores order by domicilio;

Podemos agregar una columna extra a la consulta con el encabezado "condicion" en la que aparezca el literal "profesor" o "alumno" según si la persona es uno u otro: select nombre, domicilio, 'alumno' as condicion from alumnos union select nombre, domicilio,'profesor' from profesores order by condicion;

Primer problema: Un supermercado almacena en una tabla denominada "proveedores" los datos de las compañías que le proveen de mercaderías; en una tabla llamada "clientes", los datos de los comercios que le compran y en otra tabla "empleados" los datos de los empleados. 1- Elimine las tablas si existen: if object_id('clientes') is not null drop table clientes; if object_id('proveedores') is not null drop table proveedores; if object_id('empleados') is not null drop table empleados; 2- Cree las tablas: create table proveedores( codigo int identity, nombre varchar (30), domicilio varchar(30), primary key(codigo) ); create table clientes( codigo int identity, nombre varchar (30), domicilio varchar(30), primary key(codigo) ); create table empleados( documento char(8) not null, nombre varchar(20), apellido varchar(20), domicilio varchar(30), primary key(documento) ); 3- Ingrese algunos registros: insert into proveedores values('Bebida cola','Colon 123'); insert into proveedores values('Carnes Unica','Caseros 222'); insert into proveedores values('Lacteos Blanca','San Martin 987'); insert into clientes values('Supermercado Lopez','Avellaneda 34'); insert into clientes values('Almacen Anita','Colon 987'); insert into clientes values('Garcia Juan','Sucre 345'); insert into empleados values('23333333','Federico','Lopez','Colon 987'); insert into empleados values('28888888','Ana','Marquez','Sucre 333'); insert into empleados values('30111111','Luis','Perez','Caseros 956'); 4- El supermercado quiere enviar una tarjeta de salutación a todos los proveedores, clientes y

256

empleados y necesita el nombre y domicilio de todos ellos. Emplee el operador "union" para obtener dicha información de las tres tablas. 5- Agregue una columna con un literal para indicar si es un proveedor, un cliente o un empleado y ordene por dicha columna.

83 - Agregar y eliminar campos ( alter table add - drop) "alter table" permite modificar la estructura de una tabla. Podemos utilizarla para agregar, modificar y eliminar campos de una tabla. Para agregar un nuevo campo a una tabla empleamos la siguiente sintaxis básica: alter table NOMBRETABLA add NOMBRENUEVOCAMPO DEFINICION;

En el siguiente ejemplo agregamos el campo "cantidad" a la tabla "libros", de tipo tinyint, que acepta valores nulos: alter table libros add cantidad tinyint;

Puede verificarse la alteración de la estructura de la tabla ejecutando el procedimiento almacenado "sp_columns". SQL Server no permite agregar campos "not null" a menos que se especifique un valor por defecto: alter table libros add autor varchar(20) not null default 'Desconocido';

En el ejemplo anterior, se agregó una restricción "default" para el nuevo campo, que puede verificarse ejecutando el procedimiento almacenado "sp_helpconstraint". Al agregar un campo puede especificarse que sea "identity" (siempre que no exista otro campo identity). Para eliminar campos de una tabla la sintaxis básica es la siguiente: alter table NOMBRETABLA drop column NOMBRECAMPO;

En el siguiente ejemplo eliminamos el campo "precio" de la tabla "libros": alter table libros drop column precio;

No pueden eliminarse los campos que son usados por un índice o tengan restricciones. No puede eliminarse un campo si es el único en la tabla. 257

Podemos eliminar varios campos en una sola sentencia: alter table libros drop column editorial,edicion;

Trabajamos con la tabla "libros" de una librería. Eliminamos la tabla, si existe: if object_id('libros') is not null drop table libros;

Creamos la tabla: create table libros( titulo varchar(30), editorial varchar(15), edicion datetime, precio decimal(6,2) );

Agregamos un registro: insert into libros (titulo,editorial,precio) values ('El aleph','Emece',25.50);

Agregamos el campo "cantidad" a la tabla "libros", de tipo tinyint, que acepta valores nulos: alter table libros add cantidad tinyint;

Verificamos la estructura de la tabla empleando el procedimiento almacenado "sp_columns": sp_columns libros;

aparece el nuevo campo. Agregamos un campo "codigo" a la tabla "libros", de tipo int con el atributo "identity": alter table libros add codigo int identity;

Intentamos agregar un campo llamado "autor" de tipo varchar(30) que NO acepte valores nulos: alter table libros add autor varchar(30) not null;

No es posible, porque SQL Server no permite agregar campos "not null" a menos que se especifique un valor por defecto: alter table libros add autor varchar(20) not null default 'Desconocido';

En el ejemplo anterior, se agregó una restricción "default" para el nuevo campo, que puede verificarse ejecutando el procedimiento almacenado "sp_helpconstraint". En el siguiente ejemplo eliminamos el campo "precio" de la tabla "libros": alter table libros drop column precio;

258

Verificamos la eliminación: sp_columns libros;

el campo "precio" ya no existe. Recuerde que no pueden eliminarse los campos con restricciones, intentémoslo: alter table libros drop column autor;

no lo permite. Podemos eliminar varios campos en una sola sentencia: alter table libros drop column editorial,edicion;

Primer problema: Trabaje con una tabla llamada "empleados". 1- Elimine la tabla, si existe, créela y cargue un registro: if object_id('empleados') is not null drop table empleados; create table empleados( apellido varchar(20), nombre varchar(20), domicilio varchar(30), fechaingreso datetime ); insert into empleados(apellido,nombre) values ('Rodriguez','Pablo'); 2- Agregue el campo "sueldo", de tipo decimal(5,2). 3- Verifique que la estructura de la tabla ha cambiado. 4- Agregue un campo "codigo", de tipo int con el atributo "identity". 5- Intente agregar un campo "documento" no nulo. No es posible, porque SQL Server no permite agregar campos "not null" a menos que se especifique un valor por defecto. 6- Agregue el campo del punto anterior especificando un valor por defecto: alter table empleados add documento char(8) not null default '00000000'; 7- Verifique que la estructura de la tabla ha cambiado. 8- Elimine el campo "sueldo". 9- Verifique la eliminación: sp_columns empleados; 10- Intente eliminar el campo "documento". no lo permite. 11- Elimine los campos "codigo" y "fechaingreso" en una sola sentencia. 12- Verifique la eliminación de los campos: sp_columns empleados;

259

84 - Alterar campos (alter table - alter) Hemos visto que "alter table" permite modificar la estructura de una tabla. También podemos utilizarla para modificar campos de una tabla. La sintaxis básica para modificar un campo existente es la siguiente: alter table NOMBRETABLA alter column CAMPO NUEVADEFINICION;

Modificamos el campo "titulo" extendiendo su longitud y para que NO admita valores nulos: alter table libros alter column titulo varchar(40) not null;

En el siguiente ejemplo alteramos el campo "precio" de la tabla "libros" que fue definido "decimal(6,2) not null" para que no acepte valores nulos: alter table libros alter column precio decimal(6,2) null;

SQL Server tiene algunas excepciones al momento de modificar los campos. No permite modificar: - campos de tipo text, image, ntext y timestamp. - un campo que es usado en un campo calculado. - campos que son parte de índices o tienen restricciones, a menos que el cambio no afecte al índice o a la restricción, por ejemplo, se puede ampliar la longitud de un campo de tipo caracter. - agregando o quitando el atributo "identity". - campos que afecten a los datos existentes cuando una tabla contiene registros (ejemplo: un campo contiene valores nulos y se pretende redefinirlo como "not null"; un campo int guarda un valor 300 y se pretende modificarlo a tinyint, etc.). Trabajamos con la tabla "libros" de una librería. Eliminamos la tabla, si existe: if object_id('libros') is not null drop table libros;

Creamos la tabla: create table libros( codigo int identity, titulo varchar(30), autor varchar(30), editorial varchar(15), precio decimal(6,2) not null default 0 );

260

Ingresamos algunos registros: insert into libros values('El aleph','Borges','Planeta',20); insert into libros values('Java en 10 minutos',null,'Siglo XXI',30); insert into libros values('Uno','Richard Bach','Planeta',15); insert into libros values('Martin Fierro','Jose Hernandez',null,30); insert into libros values('Aprenda PHP','Mario Molina','Emece',25);

Vamos a efectuar diferentes modificaciones a los campos de esta tabla. Luego de cada una podemos ver la estructura de la tabla para controlar si los cambios se realizaron o no empleando el procedimiento almacenado "sp_columns". Modificamos el campo "titulo" para que acepte una cadena más larga y no admita valores nulos: alter table libros alter column titulo varchar(40) not null;

Si intentamos modificar el campo "autor" para que no admita valores nulos SQL Server no lo permite porque hay registros con valor nulo en "autor". Eliminamos tal registro y realizamos la modificación: delete from libros where autor is null; alter table libros alter column autor varchar(30) not null;

Intentamos quitar el atributo "identity" del campo "codigo" y lo redefinimos como "smallint": alter table libros alter column codigo smallint;

No aparece mensaje de error pero no se realizó el cambio completo, controlémoslo: sp_columns libros;

el campo "codigo" es "smallint pero aún es "identity". Aprendimos que no se puede modificar el tipo de dato o atributos de un campo que tiene una restricción si el cambio afecta a la restricción; pero si el cambio no afecta a la restricción, si se realiza: alter table libros alter column precio decimal(6,2) null;

El campo "precio" fue alterado para que acepte valores nulos: Primer problema: Trabaje con una tabla llamada "empleados". 1- Elimine la tabla, si existe y créela: if object_id('empleados') is not null drop table empleados; create table empleados( legajo int not null, documento char(7) not null, nombre varchar(10),

261

domicilio varchar(30), ciudad varchar(20) default 'Buenos Aires', sueldo decimal(6,2), cantidadhijos tinyint default 0, primary key(legajo) ); 2- Modifique el campo "nombre" extendiendo su longitud. 3- Controle la modificación: sp_columns empleados; 4- Modifique el campo "sueldo" para que no admita valores nulos. 4- Modifique el campo "documento" ampliando su longitud a 8 caracteres. 5- Intente modificar el tipo de datos del campo "legajo" a "tinyint": alter table empleados alter column legajo tinyint not null; No se puede porque tiene una restricción. 6- Ingrese algunos registros, uno con "nombre" nulo: insert into empleados values(1,'22222222','Juan Perez','Colon 123','Cordoba',500,3); insert into empleados values(2,'30000000',null,'Sucre 456','Cordoba',600,2); 7- Intente modificar el campo "nombre" para que no acepte valores nulos: alter table empleados alter column nombre varchar(30) not null; No se puede porque hay registros con ese valor. 8- Elimine el registro con "nombre" nulo y realice la modificación del punto 7: delete from empleados where nombre is null; alter table empleados alter column nombre varchar(30) not null; 9- Modifique el campo "ciudad" a 10 caracteres. 10- Intente agregar un registro con el valor por defecto para "ciudad": insert into empleados values(3,'33333333','Juan Perez','Sarmiento 856',default,500,4); No se puede porque el campo acepta 10 caracteres y el valor por defecto tiene 12 caracteres. 11- Modifique el campo "ciudad" sin que afecte la restricción dándole una longitud de 15 caracteres. 12- Agregue el registro que no pudo ingresar en el punto 10: insert into empleados values(3,'33333333','Juan Perez','Sarmiento 856',default,500,4); 13- Intente agregar el atributo identity de "legajo". No se puede agregar este atributo.

85 - Agregar campos y restricciones (alter table) Podemos agregar un campo a una tabla y en el mismo momento aplicarle una restricción. Para agregar un campo y establecer una restricción, la sintaxis básica es la siguiente: alter table TABLA

262

add CAMPO DEFINICION constraint NOMBRERESTRICCION TIPO;

Agregamos a la tabla "libros", el campo "titulo" de tipo varchar(30) y una restricción "unique" con índice agrupado: alter table libros add titulo varchar(30) constraint UQ_libros_autor unique clustered;

Agregamos a la tabla "libros", el campo "codigo" de tipo int identity not null y una restricción "primary key" con índice no agrupado: alter table libros add codigo int identity not null constraint PK_libros_codigo primary key nonclustered;

Agregamos a la tabla "libros", el campo "precio" de tipo decimal(6,2) y una restricción "check": alter table libros add precio decimal(6,2) constraint CK_libros_precio check (precio>=0);

Trabajamos con la tabla "libros" de una librería. Eliminamos la tabla, si existe: if object_id('libros') is not null drop table libros;

Creamos la tabla con la siguiente estructura: create table libros( autor varchar(30), editorial varchar(15) );

Agregamos el campo "titulo" de tipo varchar(30) y una restricción "unique" con índice agrupado: alter table libros add titulo varchar(30) constraint UQ_libros_autor unique clustered;

Veamos si la estructura cambió: sp_columns libros;

Agregamos el campo "codigo" de tipo int identity not null y en la misma sentencia una restricción "primary key" con índice no agrupado: alter table libros add codigo int identity not null constraint PK_libros_codigo primary key nonclustered;

Agregamos el campo "precio" de tipo decimal(6,2) y una restricción "check" que no permita valores negativos para dicho campo: alter table libros add precio decimal(6,2) constraint CK_libros_precio check (precio>=0);

263

Vemos las restricciones: sp_helpconstraint libros;

Primer problema: Trabaje con una tabla llamada "empleados". 1- Elimine la tabla, si existe y créela: if object_id('empleados') is not null drop table empleados; create table empleados( documento char(8) not null, nombre varchar(10), domicilio varchar(30), ciudad varchar(20) default 'Buenos Aires' ); 2- Agregue el campo "legajo" de tipo int identity y una restricción "primary key": alter table empleados add legajo int identity constraint PK_empleados_legajo primary key; 3- Vea si la estructura cambió y si se agregó la restricción: sp_columns empleados; exec sp_helpconstraint empleados; 4- Agregue el campo "hijos" de tipo tinyint y en la misma sentencia una restricción "check" que no permita valores superiores a 30: alter table empleados add hijos tinyint constraint CK_empleados_hijos check (hijos=0); No lo permite porque no damos un valor por defecto para dicho campo no nulo y los registros existentes necesitan cargar un valor. 7- Agregue el campo "sueldo" de tipo decimal(6,2) no nulo, una restricción "check" que no permita valores negativos para dicho campo y una restricción "default" que almacene el valor "0": alter table empleados add sueldo decimal(6,2) not null constraint CK_empleados_sueldo check (sueldo>=0) constraint DF_empleados_sueldo default 0; 8- Recupere los registros: select *from empleados; 9- Vea la nueva estructura de la tabla: sp_columns empleados; 10- Vea las restricciones:

264

sp_helpconstraint empleados;

86 - Campos calculados Un campo calculado es un campo que no se almacena físicamente en la tabla. SQL Server emplea una fórmula que detalla el usuario al definir dicho campo para calcular el valor según otros campos de la misma tabla. Un campo calculado no puede: - definirse como "not null". - ser una subconsulta. - tener restricción "default" o "foreign key". - insertarse ni actualizarse. Puede ser empleado como llave de un índice o parte de restricciones "primary key" o "unique" si la expresión que la define no cambia en cada consulta. Creamos un campo calculado denominado "sueldototal" que suma al sueldo básico de cada empleado la cantidad abonada por los hijos (100 por cada hijo): create table empleados( documento char(8), nombre varchar(10), domicilio varchar(30), sueldobasico decimal(6,2), cantidadhijos tinyint default 0, sueldototal as sueldobasico + (cantidadhijos*100) );

También se puede agregar un campo calculado a una tabla existente: alter table NOMBRETABLA add NOMBRECAMPOCALCULADO as EXPRESION; alter table empleados add sueldototal as sueldo+(cantidadhijos*100);

Los campos de los cuales depende el campo calculado no pueden eliminarse, se debe eliminar primero el campo calculado. Trabajamos con la tablas "empleados". Eliminamos la tabla, si existe, y la creamos: if object_id('empleados') is not null drop table empleados; create table empleados( documento char(8), nombre varchar(10), domicilio varchar(30),

265

sueldobasico decimal(6,2), hijos tinyint not null default 0, sueldototal as sueldobasico + (hijos*100) );

El campo "sueldototal" es un campo calculado que suma al sueldo básico de cada empleado y la cantidad abonada por los hijos (100 por cada hijo). No puede ingresarse valor para dicho campo: insert into empleados values('22222222','Juan Perez','Colon 123',300,2); insert into empleados values('23333333','Ana Lopez','Sucre 234',500,0);

Veamos los registros: select *from empleados;

Veamos lo que sucede si actualizamos un registro: update empleados set hijos=1 where documento='23333333'; select *from empleados;

Recuperamos los registros: select *from empleados;

el campo calculado "sueldototal" recalcula los valores para cada registro automáticamente. Agregamos un campo calculado: alter table empleados add salariofamiliar as hijos*100;

Veamos la estructura de la tabla: sp_columns empleados;

Recuperemos los registros: select *from empleados;

Primer problema: Un comercio almacena los datos de los artículos para la venta en una tabla denominada "articulos". 1- Elimine la tabla, si existe y créela nuevamente: if object_id('articulos') is not null drop table articulos; create table articulos( codigo int identity, descripcion varchar(30), precio decimal(5,2) not null, cantidad smallint not null default 0, montototal as precio *cantidad ); El campo "montototal" es un campo calculado que multiplica el precio de cada artículo por la cantidad disponible. 2- Intente ingresar un registro con valor para el campo calculado:

266

insert into articulos values('birome',1.5,100,150); No lo permite. 3- Ingrese algunos registros: insert into articulos values('birome',1.5,100); insert into articulos values('cuaderno 12 hojas',4.8,150); insert into articulos values('lapices x 12',5,200); 4- Recupere los registros: select *from articulos; 5- Actualice un precio y recupere los registros: update articulos set precio=2 where descripcion='birome'; select *from articulos; el campo calculado "montototal" recalcula los valores para cada registro automáticamente. 6- Actualice una cantidad y vea el resultado: update articulos set cantidad=200 where descripcion='birome'; select *from articulos; el campo calculado "montototal" recalcula sus valores. 7- Intente actualizar un campo calculado: update articulos set montototal=300 where descripcion='birome'; No lo permite.

87 - Tipo de dato definido por el usuario (crear informacion) Cuando definimos un campo de una tabla debemos especificar el tipo de datos, sabemos que los tipos de datos especifican el tipo de información (caracteres, números, fechas) que pueden almacenarse en un campo. SQL Server proporciona distintos tipos de datos del sistema (char, varchar, int, decimal, datetime, etc.) y permite tipos de datos definidos por el usuario siempre que se basen en los tipos de datos existentes. Se pueden crear y eliminar tipos de datos definidos por el usuario. Se emplean cuando varias tablas deben almacenar el mismo tipo de datos en un campo y se quiere garantizar que todas tengan el mismo tipo y longitud. Para darle un nombre a un tipo de dato definido por el usuario debe considerar las mismas reglas que para cualquier identificador. No puede haber dos objetos con igual nombre en la misma base de datos. Para crear un tipo de datos definido por el usuario se emplea el procedimiento almacenado del sistema "sp_addtype". Sintaxis básica: exec sp_addtype NOMBRENUEVOTIPO, 'TIPODEDATODELSISTEMA', 'OPCIONNULL';

Creamos un tipo de datos definido por el usuario llamado "tipo_documento" que admite valores nulos: exec sp_addtype tipo_documento, 'char(8)', 'null';

267

Ejecutando el procedimiento almacenado "sp_help" junto al nombre del tipo de dato definido por el usuario se obtiene información del mismo (nombre, el tipo de dato en que se basa, la longitud, si acepta valores nulos, si tiene valor por defecto y reglas asociadas). También podemos consultar la tabla "systypes" en la cual se almacena información de todos los tipos de datos: select name from systypes;

Una academia de enseñanza almacena los datos de sus alumnos en una tabla llamada "alumnos". Borramos la tabla alumno por si otro usuario creo tipos de datos definidos para la misma: if object_id('alumnos') is not null drop table alumnos;

Queremos definir un nuevo tipo de dato llamado "tipo_documento". Primero debemos eliminarlo, si existe para volver a crearlo. Para ello empleamos esta sentencia que explicaremos en el siguiente capítulo: if exists (select *from systypes where name = 'tipo_documento') exec sp_droptype tipo_documento;

Creamos un tipo de dato definido por el usuario llamado "tipo_documento" basado en el tipo "char" que permita 8 caracteres y valores nulos: exec sp_addtype tipo_documento, 'char(8)', 'null';

Ejecutamos el procedimiento almacenado "sp_help" junto al nombre del tipo de dato definido anteriormente para obtener información del mismo: sp_help tipo_documento;

Aparecen varias columnas: el nombre, el tipo de dato en que se basa, la longitud, si acepta valores nulos; las columnas "default_name" y "rule_name" muestran "none" porque no tiene valores predeterminados ni reglas asociados. Creamos la tabla "alumnos" con 2 campos: documento (del tipo de dato definido anteriormente) y nombre (30 caracteres). Antes la eliminamos (si existe): create table alumnos( documento tipo_documento, nombre varchar(30) );

Ingresamos un registro con valor para "documento" permitido por el tipo de dato: insert into alumnos values('12345678','Ana Acosta');

No podríamos ingresar en "documento" una cadena de 9 caracteres. Primer problema: Un comercio almacena los datos de sus empleados en una tabla denominada "empleados". 1- Elimine la tabla si existe: if object_id ('empleados') is not null drop table empleados;

268

2- Defina un nuevo tipo de dato llamado "tipo_legajo". Primero debe eliminarlo (si existe) para volver a crearlo. Para ello emplee esta sentencia que explicaremos en el siguiente capítulo: if exists (select name from systypes where name = 'tipo_legajo') exec sp_droptype tipo_legajo; 3- Cree un tipo de dato definido por el usuario llamado "tipo_legajo" basado en el tipo "char" de 4 caracteres que no permita valores nulos. 4- Ejecute el procedimiento almacenado "sp_help" junto al nombre del tipo de dato definido anteriormente para obtener información del mismo. 5- Cree la tabla "empleados" con 3 campos: legajo (tipo_legajo), documento (char de 8) y nombre (30 caracteres): create table empleados( legajo tipo_legajo, documento char(8), nombre varchar(30) ); 6- Intente ingresar un registro con valores por defecto: insert into empleados default values; No se puede porque el campo "tipo_legajo" no admite valores nulos y no tiene definido un valor por defecto. 7- Ingrese un registro con valores válidos.

88 - Tipo de dato definido por el usuario (asociación de reglas) Se puede asociar una regla a un tipo de datos definido por el usuario. Luego de crear la regla se establece la asociación; la sintaxis es la siguiente: exec sp_bindrule NOMBREREGLA, 'TIPODEDATODEFINIDOPORELUSUARIO', 'futureonly';

El parámetro "futureonly" es opcional, especifica que si existen campos (de cualquier tabla) con este tipo de dato, no se asocien a la regla; si creamos una nueva tabla con este tipo de dato, si deberán cumplir la regla. Si no se especifica este parámetro, todos los campos de este tipo de dato, existentes o que se creen posteriormente (de cualquier tabla), quedan asociados a la regla. Recuerde que SQL Server NO controla los datos existentes para confirmar que cumplen con la regla, si no los cumple, la regla se asocia igualmente; pero al ejecutar una instrucción "insert" o "update" muestra un mensaje de error. Si asocia una regla a un tipo de dato definido por el usuario que tiene otra regla asociada, esta última la reemplaza. 269

Para quitar la asociación, empleamos el mismo procedimiento almacenado que aprendimos cuando quitamos asociaciones a campos, ejecutamos el procedimiento almacenado "sp_unbindrule" seguido del nombre del tipo de dato al que está asociada la regla: exec sp_unbindrule 'TIPODEDATODEFINIDOPORELUSUARIO';

Si asocia una regla a un campo cuyo tipo de dato definido por el usuario ya tiene una regla asociada, la nueva regla se aplica al campo, pero el tipo de dato continúa asociado a la regla. La regla asociada al campo prevalece sobre la asociada al tipo de dato. Por ejemplo, tenemos un campo "precio" de un tipo de dato definido por el usuario "tipo_precio", este tipo de dato tiene asociada una regla "RG_precio0a99" (precio entre 0 y 99), luego asociamos al campo "precio" la regla "RG_precio100a500" (precio entre 100 y 500); al ejecutar una instrucción "insert" admitirá valores entre 100 y 500, es decir, tendrá en cuenta la regla asociada al campo, aunque vaya contra la regla asociada al tipo de dato. Un tipo de dato definido por el usuario puede tener una sola regla asociada. Cuando obtenemos información del tipo da dato definido por el usuario ejecutando "sp_help", en la columna "rule_name" se muestra el nombre de la regla asociada a dicho tipo de dato; muestran "none" cuando no tiene regla asociada. Una academia de enseñanza almacena los datos de sus alumnos en una tabla llamada "alumnos" y en otra tabla denominada "docentes" los datos de los profesores. Eliminamos ambas tablas, si existen: if object_id('alumnos') is not null drop table alumnos; if object_id('docentes') is not null drop table docentes;

Queremos definir un nuevo tipo de dato llamado "tipo_documento". Primero debemos eliminarlo, si existe para volver a crearlo. Para ello empleamos esta sentencia que explicaremos próximamente: if exists (select *from systypes where name = 'tipo_documento') exec sp_droptype tipo_documento;

Creamos un tipo de dato definido por el usuario llamado "tipo_documento" basado en el tipo "char" que permita 8 caracteres y valores nulos: exec sp_addtype tipo_documento, 'char(8)', 'null';

Ejecutamos el procedimiento almacenado "sp_help" junto al nombre del tipo de dato definido anteriormente para obtener información del mismo: sp_help tipo_documento;

Aparecen varias columnas que nos informan, entre otras cosas: el nombre (tipo_documento), el tipo de dato en que se basa (char), la longitud (8), si acepta valores nulos (yes); las columnas "default_name" y "rule_name" muestran "none" porque no tiene valores predeterminados ni reglas asociados. Creamos la tabla "alumnos" con 2 campos: documento (del tipo de dato definido anteriormente) y nombre (30 caracteres): create table alumnos(

270

documento tipo_documento, nombre varchar(30) );

Eliminamos si existe, la regla "RG_documento": if object_id ('RG_documento') is not null drop rule RG_documento;

Creamos la regla que permita 8 caracteres que solamente pueden ser dígitos del 0 al 5 para el primer dígito y de 0 al 9 para los siguientes: create rule RG_documento as @documento like '[0-5][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';

Asociamos la regla al tipo de datos "tipo_documento" especificando que solamente se aplique a los futuros campos de este tipo: exec sp_bindrule RG_documento, 'tipo_documento', 'futureonly';

Ejecutamos el procedimiento almacenado "sp_helpconstraint" para verificar que no se aplicó a la tabla "alumnos" porque especificamos la opción "futureonly": sp_helpconstraint alumnos;

Creamos la tabla "docentes" con 2 campos: documento (del tipo de dato definido anteriormente) y nombre (30 caracteres): create table docentes( documento tipo_documento, nombre varchar(30) );

Verificamos que se aplicó la regla en la nueva tabla: sp_helpconstraint docentes;

Ingresamos registros en "alumnos" con valores para documento que infrinjan la regla: insert into alumnos values('a111111','Ana Acosta');

Lo acepta porque en esta tabla no se aplica la regla. Pero no podríamos ingresar un valor como el anterior en la tabla "docentes" la cual si tiene asociada la regla. Quitamos la asociación: exec sp_unbindrule 'tipo_documento';

Volvemos a asociar la regla, ahora sin el parámetro "futureonly": exec sp_bindrule RG_documento, 'tipo_documento';

Note que hay valores que no cumplen la regla, recuerde que SQL Server NO controla los datos existentes al momento de asociar una regla; pero si al ejecutar un "insert" o "update". Verificamos que se aplicó la regla en ambas tablas: sp_helpconstraint docentes; exec sp_helpconstraint alumnos;

271

Eliminamos si existe, la regla "RG_documento2": if object_id ('RG_documento2') is not null drop rule RG_documento2;

Creamos la regla llamada "RG_documento2" que permita 8 caracteres que solamente pueden ser dígitos del 0 al 9 para todas las posiciones: create rule RG_documento2 as @documento like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';

Asociamos la regla al tipo de datos "tipo_documento" (ya tiene una regla asociada): exec sp_bindrule RG_documento2, 'tipo_documento';

Veamos si la asociación fue reemplazada en el tipo de datos: sp_help tipo_documento;

Note que ahora en la columna "Rule_name" muestra "RG_documento2". Veamos si la asociación fue reemplazada en las tablas: sp_helpconstraint alumnos; exec sp_helpconstraint docentes;

Note que ahora la regla asociada es "RG_documento2". Asociamos la regla "RG_documento" al campo "documento" de "alumnos": exec sp_bindrule RG_documento, 'alumnos.documento';

Verificamos que "documento" de "alumnos" tiene asociada la regla "RG_documento": sp_helpconstraint alumnos;

Verificamos que el tipo de dato "tipo_documento" tiene asociada la regla "RG_documento2": sp_help tipo_documento;

Intente ingresar un valor para "documento" aceptado por la regla asociada al tipo de dato pero no por la regla asociada al campo: insert into alumnos values ('77777777','Juan Lopez');

No lo permite. Ingrese un valor para "documento" aceptado por la regla asociada al campo: insert into alumnos values ('55555555','Juan Lopez');

Primer problema: Un comercio almacena los datos de sus empleados en una tabla denominada "empleados" y en otra llamada "clientes" los datos de sus clientes". 1- Elimine ambas tablas, si existen: if object_id ('empleados') is not null drop table empleados; if object_id ('clientes') is not null drop table clientes;

272

2- Defina un nuevo tipo de dato llamado "tipo_año". Primero debe eliminarlo, si existe, para volver a crearlo. Para ello emplee esta sentencia que explicaremos en el siguiente capítulo: if exists (select *from systypes where name = 'tipo_año') exec sp_droptype tipo_año; 3- Cree un tipo de dato definido por el usuario llamado "tipo_año" basado en el tipo "int" que permita valores nulos: exec sp_addtype tipo_año, 'int','null'; 4- Ejecute el procedimiento almacenado "sp_help" junto al nombre del tipo de dato definido anteriormente para obtener información del mismo: sp_help tipo_año; 5- Cree la tabla "empleados" con 3 campos: documento (char de 8), nombre (30 caracteres) y añoingreso (tipo_año): create table empleados( documento char(8), nombre varchar(30), añoingreso tipo_año ); 6- Elimine la regla llamada "RG_año" si existe: if object_id ('RG_año') is not null drop rule RG_año; 7- Cree la regla que permita valores integer desde 1990 (año en que se inauguró el comercio) y el año actual: create rule RG_año as @año between 1990 and datepart(year,getdate()); 8- Asocie la regla al tipo de datos "tipo_año" especificando que solamente se aplique a los futuros campos de este tipo: exec sp_bindrule RG_año, 'tipo_año', 'futureonly'; 9- Vea si se aplicó a la tabla empleados: sp_helpconstraint empleados; No se aplicó porque especificamos la opción "futureonly": 10- Cree la tabla "clientes" con 3 campos: nombre (30 caracteres), añoingreso (tipo_año) y domicilio (30 caracteres): create table clientes( documento char(8), nombre varchar(30), añoingreso tipo_año ); 11- Vea si se aplicó la regla en la nueva tabla: sp_helpconstraint clientes; Si aparece. 12- Ingrese registros con valores para el año que infrinjan la regla en la tabla "empleados": insert into empleados values('11111111','Ana Acosta',2050); select *from empleados; Lo acepta porque en esta tabla no se aplica la regla. 13- Intente ingresar en la tabla "clientes" un valor de fecha que infrinja la regla:

273

insert into clientes values('22222222','Juan Perez',2050); No lo permite. 14- Quite la asociación de la regla con el tipo de datos: exec sp_unbindrule 'tipo_año'; 15- Vea si se quitó la asociación: sp_helpconstraint clientes; Si se quitó. 16- Vuelva a asociar la regla, ahora sin el parámetro "futureonly": exec sp_bindrule RG_año, 'tipo_año'; Note que hay valores que no cumplen la regla pero SQL Server NO lo verifica al momento de asociar una regla. 17- Intente agregar una fecha de ingreso fuera del intervalo que admite la regla en cualquiera de las tablas (ambas tienen la asociación): insert into empleados values('33333333','Romina Guzman',1900); Mensaje de error. 18- Vea la información del tipo de dato: exec sp_help tipo_año; En la columna que hace referencia a la regla asociada aparece "RG_año". 19- Elimine la regla llamada "RG_añonegativo", si existe: if object_id ('RG_añonegativo') is not null drop rule RG_añonegativo; 20- Cree una regla llamada "RG_añonegativo" que admita valores entre -2000 y -1: create rule RG_añonegativo as @año between -2000 and -1; 21- Asocie la regla "RG_añonegativo" al campo "añoingreso" de la tabla "clientes": exec sp_bindrule RG_añonegativo, 'clientes.añoingreso'; 22- Vea si se asoció: sp_helpconstraint clientes; Se asoció. 23- Verifique que no está asociada al tipo de datos "tipo_año": sp_help tipo_año; No, tiene asociada la regla "RG_año". 24- Intente ingresar un registro con valor '-1900' para el campo "añoingreso" de "empleados": insert into empleados values('44444444','Pedro Perez',-1900); No lo permite por la regla asociada al tipo de dato. 25- Ingrese un registro con valor '-1900' para el campo "añoingreso" de "clientes" y recupere los registros de dicha tabla: insert into clientes values('44444444','Pedro Perez',-1900); select *from clientes; Note que se ingreso, si bien el tipo de dato de "añoingreso" tiene asociada una regla que no admite tal valor, el campo tiene asociada una regla que si lo admite y ésta prevalece.

274

89 - Tipo de dato definido por el usuario (valores predeterminados) Se puede asociar un valor predeterminado a un tipo de datos definido por el usuario. Luego de crear un valor predeterminado, se puede asociar a un tipo de dato definido por el usuario con la siguiente sintaxis: exec sp_bindefault NOMBREVALORPREDETERMINADO, 'TIPODEDATODEFINIDOPORELUSUARIO','futureonly';

El parámetro "futureonly" es opcional, especifica que si existen campos (de cualquier tabla) con este tipo de dato, no se asocien al valor predeterminado; si creamos una nueva tabla con este tipo de dato, si estará asociado al valor predeterminado. Si no se especifica este parámetro, todos los campos de este tipo de dato, existentes o que se creen posteriormente (de cualquier tabla), quedan asociados al valor predeterminado. Si asocia un valor predeterminado a un tipo de dato definido por el usuario que tiene otro valor predeterminado asociado, el último lo reemplaza. Para quitar la asociación, empleamos el mismo procedimiento almacenado que aprendimos cuando quitamos asociaciones a campos: sp_unbindefault 'TIPODEDATODEFINIDOPORELUSUARIO';

Debe tener en cuenta que NO se puede aplicar una restricción "default" en un campo con un tipo de datos definido por el usuario si dicho campo o tipo de dato tienen asociado un valor predeterminado. Si un campo de un tipo de dato definido por el usuario tiene una restricción "default" y luego se asocia un valor predeterminado al tipo de dato, el valor predeterminado no queda asociado en el campo que tiene la restricción "default". Un tipo de dato definido por el usuario puede tener un solo valor predeterminado asociado. Cuando obtenemos información del tipo da dato definido por el usuario ejecutando "sp_help", en la columna "default_name" se muestra el nombre del valor predeterminado asociado a dicho tipo de dato; muestra "none" cuando no tiene ningún valor predeterminado asociado. Una academia de enseñanza almacena los datos de sus alumnos en una tabla llamada "alumnos" y en otra tabla denominada "docentes" los datos de los profesores. Eliminamos ambas tablas, si existen: if object_id('alumnos') is not null drop table alumnos; if object_id('docentes') is not null drop table docentes;

Queremos definir un nuevo tipo de dato llamado "tipo_documento". Primero debemos eliminarlo, si existe para volver a crearlo. Para ello empleamos esta sentencia que explicaremos próximamente: if exists (select *from systypes where name = 'tipo_documento')

275

exec sp_droptype tipo_documento;

Creamos un tipo de dato definido por el usuario llamado "tipo_documento" basado en el tipo "char" que permita 8 caracteres y valores nulos: exec sp_addtype tipo_documento, 'char(8)', 'null';

Ejecutamos el procedimiento almacenado "sp_help" junto al nombre del tipo de dato definido anteriormente para obtener información del mismo: sp_help tipo_documento;

Aparecen varias columnas que nos informan, entre otras cosas: el nombre (tipo_documento), el tipo de dato en que se basa (char), la longitud (8), si acepta valores nulos (yes); las columnas "default_name" y "rule_name" muestran "none" porque no tiene valores predeterminados ni reglas asociados. Creamos la tabla "alumnos" con 2 campos: documento (del tipo de dato definido anteriormente) y nombre (30 caracteres): create table alumnos( documento tipo_documento, nombre varchar(30) );

Eliminamos si existe, el valor predeterminado "VP_documento0": if object_id ('VP_documento0') is not null drop default VP_documento0;

Creamos el valor predeterminado "VP_documento0" que almacene el valor '00000000': create default VP_documento0 as '00000000';

Asociamos el valor predeterminado al tipo de datos "tipo_documento" especificando que solamente se aplique a los futuros campos de este tipo: exec sp_bindefault VP_documento0, 'tipo_documento', 'futureonly';

Ejecutamos el procedimiento almacenado "sp_helpconstraint" para verificar que no se aplicó a la tabla "alumnos" porque especificamos la opción "futureonly": sp_helpconstraint alumnos;

Creamos la tabla "docentes" con 2 campos: documento (del tipo de dato definido anteriormente) y nombre (30 caracteres): create table docentes( documento tipo_documento, nombre varchar(30) );

Verificamos que se aplicó el valor predeterminado creado anteriormente al campo "documento" de la nueva tabla: sp_helpconstraint docentes;

Ingresamos un registro en "alumnos" sin valor para documento y vemos qué se almacenó: 276

insert into alumnos default values; select *from alumnos;

En esta tabla no se aplica el valor predeterminado por ello almacena "null", que es el valor por defecto. Si ingresamos en la tabla "docentes" un registro con valores por defecto: insert into docentes default values; select *from docentes;

Si se almacena el valor predeterminado porque está asociado. Quitamos la asociación: exec sp_unbindefault 'tipo_documento';

Volvemos a asociar el valor predeterminado, ahora sin el parámetro "futureonly": exec sp_bindefault VP_documento0, 'tipo_documento';

Ingresamos un registro en "alumnos" y en "docentes" sin valor para documento y vemos qué se almacenó: insert select insert select

into alumnos default values; *from alumnos; into docentes default values; *from docentes;

En ambas se almacenó '00000000'. Eliminamos si existe, el valor predeterminado "VP_documentoDesconocido": if object_id ('VP_documentoDesconocido') is not null drop default VP_documentoDesconocido;

Creamos el valor predeterminado llamado "VP_documentoDesconocido" que almacene el valor 'SinDatos': create default VP_documentoDesconocido as 'SinDatos';

Asociamos el valor predeterminado al tipo de datos "tipo_documento" (ya tiene otro valor predeterminado asociado): exec sp_bindefault VP_DocumentoDesconocido, 'tipo_documento';

Veamos si la asociación fue reemplazada en el tipo de datos: exec sp_help tipo_documento;

Note que ahora en la columna "default_name" muestra "VP_documentoDesconocido". Veamos si la asociación fue reemplazada en la tabla "alumnos": sp_helpconstraint alumnos;

Note que ahora el valor predeterminado asociado es "VP_documentoDesconocido". Quitamos la asociación del valor predeterminado: sp_unbindefault 'tipo_documento';

Veamos si se quitó de ambas tablas: 277

exec sp_helpconstraint alumnos; exec sp_helpconstraint docentes;

Ingresamos un registro en "alumnos" y vemos qué se almacenó en el campo "documento": insert into alumnos default values; select *from alumnos;

Agregue a la tabla "docentes" una restricción "default" para el campo "documento": alter table docentes add constraint DF_docentes_documento default '--------' for documento;

Ingrese un registro en "docentes" con valores por defecto y vea qué se almacenó en "documento" recuperando los registros: insert into docentes default values; select *from docentes;

Asocie el valor predeterminado "VP_documento0" al tipo de datos "tipo_documento": exec sp_bindefault VP_documento0, 'tipo_documento';

Vea qué informa "sp_helpconstraint" acerca de la tabla "docentes": sp_helpconstraint docentes;

Tiene asociado el valor por defecto establecido con la restricción "default". Ingrese un registro en "docentes" con valores por defecto y vea qué se almacenó en "documento": insert into docentes default values; select *from docentes;

Note que guarda el valor por defecto establecido con la restricción. Eliminamos la restricción: alter table docentes drop DF_docentes_documento;

Vea qué informa "sp_helpconstraint" acerca de la tabla "docentes": sp_helpconstraint docentes;

No tiene ningún valor por defecto asociado. Asociamos el valor predeterminado "VP_documento0" al tipo de datos "tipo_documento": exec sp_bindefault VP_documento0, 'tipo_documento';

Intente agregar una restricción "default" al campo "documento" de "docentes": alter table docentes add constraint DF_docentes_documento default '--------' for documento;

SQL Server no lo permite porque el tipo de dato de ese campo ya tiene un valor predeterminado asociado. 278

Primer problema: Un comercio almacena los datos de sus empleados en una tabla denominada "empleados" y en otra llamada "clientes" los datos de sus clientes". 1- Elimine ambas tablas, si existen: if object_id ('empleados') is not null drop table empleados; if object_id ('clientes') is not null drop table clientes; 2- Defina un nuevo tipo de dato llamado "tipo_año". Primero debe eliminarlo, si existe, para volver a crearlo. Para ello emplee esta sentencia que explicaremos en el siguiente capítulo: if exists (select *from systypes where name = 'tipo_año') exec sp_droptype tipo_año; 3- Cree un tipo de dato definido por el usuario llamado "tipo_año" basado en el tipo "int" que permita valores nulos: exec sp_addtype tipo_año, 'int','null'; 4- Ejecute el procedimiento almacenado "sp_help" junto al nombre del tipo de dato definido anteriormente para obtener información del mismo: sp_help tipo_año; 5- Cree la tabla "empleados" con 3 campos: documento (char de 8), nombre (30 caracteres) y añoingreso (tipo_año): create table empleados( documento char(8), nombre varchar(30), añoingreso tipo_año ); 6- Elimine el valor predeterminado "VP_añoactual" si existe: if object_id ('VP_añoactual') is not null drop default VP_añoactual; 7- Cree el valor predeterminado "VP_añoactual" que almacene el año actual: create default VP_añoactual as datepart(year,getdate()); 8- Asocie el valor predeterminado al tipo de datos "tipo_año" especificando que solamente se aplique a los futuros campos de este tipo: exec sp_bindefault VP_añoactual, 'tipo_año', 'futureonly'; 9- Vea si se aplicó a la tabla empleados: sp_helpconstraint empleados; No se aplicó porque especificamos la opción "futureonly": 10- Cree la tabla "clientes" con 3 campos: nombre (30 caracteres), añoingreso (tipo_año) y domicilio (30 caracteres): create table clientes( documento char(8), nombre varchar(30), añoingreso tipo_año ); 11- Vea si se aplicó la regla en la nueva tabla: sp_helpconstraint clientes;

279

Si se aplicó. 12- Ingrese un registro con valores por defecto en la tabla "empleados" y vea qué se almacenó en "añoingreso": insert into empleados default values; select *from empleados; Se almacenó "null" porque en esta tabla no se aplica el valor predeterminado. 13- Ingrese en la tabla "clientes" un registro con valores por defecto y recupere los registros: insert into clientes default values; select *from clientes; Se almacenó el valor predeterminado. 14- Elimine el valor predeterminado llamado "VP_año2000", si existe: if object_id ('VP_año2000') is not null drop default Vp_año2000; 15- Cree un valor predeterminado llamado "VP_año2000" con el valor 2000: create default VP_año2000 as 2000; 16- Asócielo al tipo de dato definido sin especificar "futureonly": exec sp_bindefault VP_año2000, 'tipo_año'; 17- Verifique que se asoció a la tabla "empleados": sp_helpconstraint empleados; 18- Verifique que reemplazó al valor predeterminado anterior en la tabla "clientes": sp_helpconstraint clientes; 18- Ingrese un registro en ambas tablas con valores por defecto y vea qué se almacenó en el año de ingreso: insert into empleados default values; select *from empleados; insert into clientes default values; select *from clientes; 19- Vea la información del tipo de dato: exec sp_help tipo_año; La columna que hace referencia al valor predeterminado asociado muestra "VP_año2000". 20- Intente agregar a la tabla "empleados" una restricción "default": alter table empleados add constraint DF_empleados_año default 1990 for añoingreso; No lo permite porque el tipo de dato del campo ya tiene un valor predeterminado asociado. 21- Quite la asociación del valor predeterminado al tipo de dato: sp_unbindefault 'tipo_año'; 22- Agregue a la tabla "empleados" una restricción "default": alter table empleados add constraint DF_empleados_año default 1990 for añoingreso; 23- Asocie el valor predeterminado "VP_añoactual" al tipo de dato "tipo_año": exec sp_bindefault VP_añoactual, 'tipo_año';

280

24- Verifique que el tipo de dato tiene asociado el valor predeterminado: sp_help tipo_año; 25- Verifique que la tabla "clientes" tiene asociado el valor predeterminado: sp_helpconstraint clientes; 26- Verifique que la tabla "empleados" no tiene asociado el valor predeterminado "VP_añoactual" asociado al tipo de dato y tiene la restricción "default": sp_helpconstraint empleados;

90 - Tipo de dato definido por el usuario (eliminar) Podemos eliminar un tipo de dato definido por el usuario con el procedimiento almacenado "sp_droptype": exec sp_droptype TIPODEDATODEFINIDOPORELUSUARIO;

Eliminamos el tipo de datos definido por el usuario llamado "tipo_documento": exec sp_droptype tipo_documento;

Si intentamos eliminar un tipo de dato inexistente, aparece un mensaje indicando que no existe. Los tipos de datos definidos por el usuario se almacenan en la tabla del sistema "systypes". Podemos averiguar si un tipo de dato definido por el usuario existe para luego eliminarlo: if exists (select *from systypes where name = 'NOMBRETIPODEDATODEFINIDOPORELUSUARIO') exec sp_droptype TIPODEDATODEFINIDOPORELUSUARIO;

Consultamos la tabla "systypes" para ver si existe el tipo de dato "tipo_documento", si es así, lo eliminamos: if exists (select *from systypes where name = 'tipo_documento') exec sp_droptype tipo_documento;

No se puede eliminar un tipo de datos definido por el usuario si alguna tabla (u otro objeto) hace uso de él; por ejemplo, si una tabla tiene un campo definido con tal tipo de dato. Si eliminamos un tipo de datos definido por el usuario, desaparecen las asociaciones de las reglas y valores predeterminados, pero tales reglas y valores predeterminados, no se eliminan, siguen existiendo en la base de datos. Una academia de enseñanza almacena los datos de sus alumnos en una tabla llamada "alumnos". Eliminamos la tabla si existe: if object_id('alumnos') is not null drop table alumnos;

281

Definimos un nuevo tipo de dato llamado "tipo_documento". Primero debemos eliminarlo, si existe, para volver a crearlo: if exists (select *from systypes where name = 'tipo_documento') exec sp_droptype tipo_documento;

Creamos un tipo de dato definido por el usuario llamado "tipo_documento" basado en el tipo "char" que permita 8 caracteres y valores nulos: exec sp_addtype tipo_documento, 'char(8)', 'null';

Eliminamos la regla "RG_documento" si existe: if object_id ('RG_documento') is not null drop rule RG_documento;

Creamos la regla que permita 8 caracteres que solamente serán dígitos: create rule RG_documento as @documento like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';

Asociamos la regla al tipo de datos "tipo_documento": exec sp_bindrule RG_documento, 'tipo_documento';

Creamos la tabla "alumnos": create table alumnos( nombre varchar(30), documento tipo_documento );

No podemos eliminar el tipo de dato "tipo_documento" porque hay una tabla "alumnos" que lo utiliza. Entonces eliminamos la tabla: drop table alumnos;

Ahora podemos eliminar el tipo de datos: exec sp_droptype tipo_documento;

Note que no quitamos la asociación de la regla al tipo de datos, pero la asociación se quitó al eliminar el tipo de dato. Volvemos a crear el tipo de dato: exec sp_addtype tipo_documento, 'char(8)', 'null';

Note que no tiene reglas asociadas: sp_help tipo_documento;

Asociamos la regla nuevamente: exec sp_bindrule RG_documento, 'tipo_documento';

Note que no hemos vuelto a crear la regla porque la regla subsiste. Primer problema: 282

Un comercio almacena los datos de sus empleados en una tabla denominada "empleados". 1- Elimine la tabla si existe: if object_id ('empleados') is not null drop table empleados; 2- Defina un nuevo tipo de dato llamado "tipo_año". Primero debe eliminarlo, si existe para volver a crearlo: if exists (select *from systypes where name = 'tipo_año') exec sp_droptype tipo_año; 3- Cree un tipo de dato definido por el usuario llamado "tipo_año" basado en el tipo "int" que permita valores nulos: exec sp_addtype tipo_año, 'int','null'; 4- Elimine la regla llamada "RG_año" si existe: if object_id ('RG_año') is not null drop rule RG_año; 5- Cree la regla que permita valores integer desde 1990 (fecha en que se inauguró el comercio) y el año actual: create rule RG_año as @año between 1990 and datepart(year,getdate()); 6- Asocie la regla al tipo de datos "tipo_año": exec sp_bindrule RG_año, 'tipo_año'; 7- Cree la tabla "empleados" con un campo del tipo creado anteriormente: create table empleados( documento char(8), nombre varchar(30), añoingreso tipo_año ); 8- Intente ingresar un registro con un valor inválido para el campo "añoingreso": insert into empleados values('22222222','Juan Lopez',1980); No lo permite. 9- Ingrese un registro con un valor válido para el campo "añoingreso": insert into empleados values('22222222','Juan Lopez',2000); 10- Intente eliminar la regla asociada al tipo de datos: drop rule RG_año; No se puede porque está asociada a un tipo de datos. 11- Elimine la asociación: exec sp_unbindrule 'tipo_año'; 12- Verifique que la asociación ha sido eliminada pero la regla sigue existiendo: sp_helpconstraint empleados; exec sp_help tipo_año; 13- Elimine la regla: drop rule RG_año; 14- Verifique que la regla ya no existe: sp_help RG_año; 15- Ingrese el registro que no pudo ingresar en el punto 8: insert into empleados values('22222222','Juan Lopez',1980); Lo permite porque el tipo de dato ya no tiene asociada la regla.

283

16- Intente eliminar el tipo de datos "tipo_año": exec sp_droptype tipo_año; No lo permite porque hay una tabla que lo utiliza. 17- Elimine la tabla "empleados": drop table empleados; 18- Verifique que el tipo de dato "tipo_año" aún existe: sp_help tipo_año; 19- Elimine el tipo de datos: exec sp_droptype tipo_año; 20- Verifique que el tipo de dato "tipo_año" ya no existe: sp_help tipo_año;

91 - Subconsultas Una subconsulta (subquery) es una sentencia "select" anidada en otra sentencia "select", "insert", "update" o "delete" (o en otra subconsulta). Las subconsultas se emplean cuando una consulta es muy compleja, entonces se la divide en varios pasos lógicos y se obtiene el resultado con una única instrucción y cuando la consulta depende de los resultados de otra consulta. Generalmente, una subconsulta se puede reemplazar por combinaciones y estas últimas son más eficientes. Las subconsultas se DEBEN incluir entre paréntesis. Puede haber subconsultas dentro de subconsultas, se admiten hasta 32 niveles de anidación. Se pueden emplear subconsultas: - en lugar de una expresión, siempre que devuelvan un solo valor o una lista de valores. - que retornen un conjunto de registros de varios campos en lugar de una tabla o para obtener el mismo resultado que una combinación (join). Hay tres tipos básicos de subconsultas: 1. las que retornan un solo valor escalar que se utiliza con un operador de comparación o en lugar de una expresión. 2. las que retornan una lista de valores, se combinan con "in", o los operadores "any", "some" y "all". 3. los que testean la existencia con "exists". Reglas a tener en cuenta al emplear subconsultas: - la lista de selección de una subconsulta que va luego de un operador de comparación puede incluir sólo una expresión o campo (excepto si se emplea "exists" y "in"). 284

- si el "where" de la consulta exterior incluye un campo, este debe ser compatible con el campo en la lista de selección de la subconsulta. - no se pueden emplear subconsultas que recuperen campos de tipos text o image. - las subconsultas luego de un operador de comparación (que no es seguido por "any" o "all") no pueden incluir cláusulas "group by" ni "having". - "distinct" no puede usarse con subconsultas que incluyan "group by". - no pueden emplearse las cláusulas "compute" y "compute by". - "order by" puede emplearse solamente si se especifica "top" también. - una vista creada con una subconsulta no puede actualizarse. - una subconsulta puede estar anidada dentro del "where" o "having" de una consulta externa o dentro de otra subconsulta. - si una tabla se nombra solamente en un subconsulta y no en la consulta externa, los campos no serán incluidos en la salida (en la lista de selección de la consulta externa).

92 - Subconsultas como expresión Una subconsulta puede reemplazar una expresión. Dicha subconsulta debe devolver un valor escalar (o una lista de valores de un campo). Las subconsultas que retornan un solo valor escalar se utiliza con un operador de comparación o en lugar de una expresión: select CAMPOS from TABLA where CAMPO OPERADOR (SUBCONSULTA); select CAMPO OPERADOR (SUBCONSULTA) from TABLA;

Si queremos saber el precio de un determinado libro y la diferencia con el precio del libro más costoso, anteriormente debíamos averiguar en una consulta el precio del libro más costoso y luego, en otra consulta, calcular la diferencia con el valor del libro que solicitamos. Podemos conseguirlo en una sola sentencia combinando dos consultas: select titulo,precio, precio-(select max(precio) from libros) as diferencia from libros where titulo='Uno';

En el ejemplo anterior se muestra el título, el precio de un libro y la diferencia entre el precio del libro y el máximo valor de precio. Queremos saber el título, autor y precio del libro más costoso: select titulo,autor, precio

285

from libros where precio= (select max(precio) from libros);

Note que el campo del "where" de la consulta exterior es compatible con el valor retornado por la expresión de la subconsulta. Se pueden emplear en "select", "insert", "update" y "delete". Para actualizar un registro empleando subconsulta la sintaxis básica es la siguiente: update TABLA set CAMPO=NUEVOVALOR where CAMPO= (SUBCONSULTA);

Para eliminar registros empleando subconsulta empleamos la siguiente sintaxis básica: delete from TABLA where CAMPO=(SUBCONSULTA);

Recuerde que la lista de selección de una subconsulta que va luego de un operador de comparación puede incluir sólo una expresión o campo (excepto si se emplea "exists" o "in"). No olvide que las subconsultas luego de un operador de comparación (que no es seguido por "any" o "all") no pueden incluir cláusulas "group by". Trabajamos con la tabla "libros" de una librería. Eliminamos la tabla, si existe y la creamos: if object_id('libros') is not null drop table libros; create table libros( codigo int identity, titulo varchar(40), autor varchar(30), editorial varchar(20), precio decimal(5,2) );

Ingresamos los siguientes registros: insert into libros values('Alicia en el pais de las maravillas','Lewis Carroll','Emece',20.00); insert into libros values('Alicia en el pais de las maravillas','Lewis Carroll','Plaza',35.00); insert into libros values('Aprenda PHP','Mario Molina','Siglo XXI',40.00); insert into libros values('El aleph','Borges','Emece',10.00); insert into libros values('Ilusiones','Richard Bach','Planeta',15.00); insert into libros values('Java en 10 minutos','Mario Molina','Siglo XXI',50.00); insert into libros values('Martin Fierro','Jose Hernandez','Planeta',20.00); insert into libros values('Martin Fierro','Jose Hernandez','Emece',30.00); insert into libros values('Uno','Richard Bach','Planeta',10.00);

Obtenemos el título, precio de un libro específico y la diferencia entre su precio y el máximo valor: select titulo,precio, precio-(select max(precio) from libros) as diferencia from libros where titulo='Uno';

Mostramos el título y precio del libro más costoso: 286

select titulo,autor, precio from libros where precio= (select max(precio) from libros);

Actualizamos el precio del libro con máximo valor: update libros set precio=45 where precio= (select max(precio) from libros);

Eliminamos los libros con precio menor: delete from libros where precio= (select min(precio) from libros);

Primer problema: Un profesor almacena el documento, nombre y la nota final de cada alumno de su clase en una tabla llamada "alumnos". 1- Elimine la tabla, si existe: if object_id('alumnos') is not null drop table alumnos; 2- Créela con los campos necesarios. Agregue una restricción "primary key" para el campo "documento" y una "check" para validar que el campo "nota" se encuentre entre los valores 0 y 10: create table alumnos( documento char(8), nombre varchar(30), nota decimal(4,2), primary key(documento), constraint CK_alumnos_nota_valores check (nota>=0 and nota any (select precio from libros where autor='Bach');

El precio de cada libro de "Borges" es comparado con cada valor de la lista de valores retornada por la subconsulta; si ALGUNO cumple la condición, es decir, es mayor a ALGUN precio de "Richard Bach", se lista. Veamos la diferencia si empleamos "all" en lugar de "any": select titulo,precio from libros where autor='borges' and precio > all (select precio from libros where autor='bach');

El precio de cada libro de "Borges" es comparado con cada valor de la lista de valores retornada por la subconsulta; si cumple la condición, es decir, si es mayor a TODOS los precios de "Richard Bach" (o al mayor), se lista. Emplear "= any" es lo mismo que emplear "in". Emplear " all" es lo mismo que emplear "not in". Recuerde que solamente las subconsultas luego de un operador de comparación al cual es seguido por "any" o "all") pueden incluir cláusulas "group by". 292

Trabajamos con las tablas "libros" y "editoriales" de una librería. Eliminamos las tablas si existen y las creamos: if object_id('libros') is not null drop table libros; if object_id('editoriales') is not null drop table editoriales; create table editoriales( codigo tinyint identity, nombre varchar(30), primary key (codigo) ); create table libros ( codigo int identity, titulo varchar(40), autor varchar(30), codigoeditorial tinyint, precio decimal(5,2), primary key(codigo), constraint FK_libros_editorial foreign key (codigoeditorial) references editoriales(codigo) on update cascade, );

Ingresamos algunos registros: insert insert insert insert

into into into into

editoriales editoriales editoriales editoriales

insert insert insert insert insert insert insert

into into into into into into into

libros libros libros libros libros libros libros

values('Planeta'); values('Emece'); values('Paidos'); values('Siglo XXI');

values('Uno','Richard Bach',1,15); values('Ilusiones','Richard Bach',4,18); values('Puente al infinito','Richard Bach',2,20); values('Aprenda PHP','Mario Molina',4,40); values('El aleph','Borges',2,10); values('Antología','Borges',1,20); values('Cervantes y el quijote','Borges',3,25);

Mostramos los títulos de los libros de "Borges" de editoriales que han publicado también libros de "Richard Bach": select titulo from libros where autor like '%Borges%' and codigoeditorial = any (select e.codigo from editoriales as e join libros as l on codigoeditorial=e.codigo where l.autor like '%Bach%');

Realizamos la misma consulta pero empleando "all" en lugar de "any": select titulo from libros where autor like '%Borges%' and codigoeditorial = all (select e.codigo from editoriales as e

293

join libros as l on codigoeditorial=e.codigo where l.autor like '%Bach%');

Mostramos los títulos y precios de los libros "Borges" cuyo precio supera a ALGUN precio de los libros de "Richard Bach": select titulo,precio from libros where autor like '%Borges%' and precio > any (select precio from libros where autor like '%Bach%');

Veamos la diferencia si empleamos "all" en lugar de "any": select titulo,precio from libros where autor like '%Borges%' and precio > all (select precio from libros where autor like '%Bach%');

Empleamos la misma subconsulta para eliminación: delete from libros where autor like '%Borges%' and precio > all (select precio from libros where autor like '%Bach%');

95 - Subconsultas correlacionadas Un almacén almacena la información de sus ventas en una tabla llamada "facturas" en la cual guarda el número de factura, la fecha y el nombre del cliente y una tabla denominada "detalles" en la cual se almacenan los distintos items correspondientes a cada factura: el nombre del artículo, el precio (unitario) y la cantidad. Se necesita una lista de todas las facturas que incluya el número, la fecha, el cliente, la cantidad de artículos comprados y el total: select f.*, (select count(d.numeroitem) from Detalles as d where f.numero=d.numerofactura) as cantidad, (select sum(d.preciounitario*cantidad) from Detalles as d where f.numero=d.numerofactura) as total from facturas as f;

El segundo "select" retorna una lista de valores de una sola columna con la cantidad de items por factura (el número de factura lo toma del "select" exterior); el tercer "select" retorna una lista de 294

valores de una sola columna con el total por factura (el número de factura lo toma del "select" exterior); el primer "select" (externo) devuelve todos los datos de cada factura. A este tipo de subconsulta se la denomina consulta correlacionada. La consulta interna se evalúa tantas veces como registros tiene la consulta externa, se realiza la subconsulta para cada registro de la consulta externa. El campo de la tabla dentro de la subconsulta (f.numero) se compara con el campo de la tabla externa. En este caso, específicamente, la consulta externa pasa un valor de "numero" a la consulta interna. La consulta interna toma ese valor y determina si existe en "detalles", si existe, la consulta interna devuelve la suma. El proceso se repite para el registro de la consulta externa, la consulta externa pasa otro "numero" a la consulta interna y SQL Server repite la evaluación. Un almacén almacena la información de sus ventas en una tabla llamada "facturas" en la cual guarda el número de factura, la fecha y el nombre del cliente y una tabla denominada "detalles" en la cual se almacenan los distintos items correspondientes a cada factura: el nombre del artículo, el precio (unitario) y la cantidad. Eliminamos las tablas si existen: if object_id('detalles') is not null drop table detalles; if object_id('facturas') is not null drop table facturas;

Las creamos con las siguientes estructuras: create table facturas( numero int not null, fecha datetime, cliente varchar(30), primary key(numero) ); create table detalles( numerofactura int not null, numeroitem int not null, articulo varchar(30), precio decimal(5,2), cantidad int, primary key(numerofactura,numeroitem), constraint FK_detalles_numerofactura foreign key (numerofactura) references facturas(numero) on update cascade on delete cascade, );

Ingresamos algunos registros: insert insert insert insert

into into into into

facturas facturas facturas facturas

values(1200,'2007-01-15','Juan Lopez'); values(1201,'2007-01-15','Luis Torres'); values(1202,'2007-01-15','Ana Garcia'); values(1300,'2007-01-20','Juan Lopez');

insert insert insert insert insert

into into into into into

detalles detalles detalles detalles detalles

values(1200,1,'lapiz',1,100); values(1200,2,'goma',0.5,150); values(1201,1,'regla',1.5,80); values(1201,2,'goma',0.5,200); values(1201,3,'cuaderno',4,90);

295

insert into detalles values(1202,1,'lapiz',1,200); insert into detalles values(1202,2,'escuadra',2,100); insert into detalles values(1300,1,'lapiz',1,300);

Se necesita una lista de todas las facturas que incluya el número, la fecha, el cliente, la cantidad de artículos comprados y el total: select f.*, (select count(d.numeroitem) from detalles as d where f.numero=d.numerofactura) as cantidad, (select sum(d.precio*cantidad) from detalles as d where f.numero=d.numerofactura) as total from facturas as f;

Primer problema: Un club dicta clases de distintos deportes a sus socios. El club tiene una tabla llamada "inscriptos" en la cual almacena el número de "socio", el código del deporte en el cual se inscribe y la cantidad de cuotas pagas (desde 0 hasta 10 que es el total por todo el año), y una tabla denominada "socios" en la que guarda los datos personales de cada socio. 1- Elimine las tablas si existen: if object_id('inscriptos') is not null drop table inscriptos; if object_id('socios') is not null drop table socios; 2- Cree las tablas: create table socios( numero int identity, documento char(8), nombre varchar(30), domicilio varchar(30), primary key (numero) ); create table inscriptos ( numerosocio int not null, deporte varchar(20) not null, cuotas tinyint constraint CK_inscriptos_cuotas check (cuotas>=0 and cuotas=0 and cuotas (select avg(l2.precio) from libros as l2 where l1.editorial= l2.editorial);

Por cada valor de l1, se evalúa la subconsulta, si el precio es mayor que el promedio. Trabajamos con la tabla "libros" de una librería. Eliminamos la tabla, si existe y la creamos: if object_id('libros') is not null drop table libros; create table libros( codigo int identity, titulo varchar(40), autor varchar(30), editorial varchar(20), precio decimal(5,2) );

Ingresamos los siguientes registros: insert into libros values('Alicia en el pais de las maravillas','Lewis Carroll','Emece',20.00); insert into libros values('Alicia en el pais de las maravillas','Lewis Carroll','Plaza',35.00); insert into libros values('Aprenda PHP','Mario Molina','Siglo XXI',40.00); insert into libros values('El aleph','Borges','Emece',10.00); insert into libros values('Ilusiones','Richard Bach','Planeta',15.00); insert into libros values('Java en 10 minutos','Mario Molina','Siglo XXI',50.00); insert into libros values('Martin Fierro','Jose Hernandez','Planeta',20.00); insert into libros values('Martin Fierro','Jose Hernandez','Emece',30.00); insert into libros values('Uno','Richard Bach','Planeta',10.00);

Obtenemos la lista de los libros que han sido publicados por distintas editoriales empleando una consulta correlacionada: select distinct l1.titulo from libros as l1 where l1.titulo in (select l2.titulo from libros as l2 where l1.editorial l2.editorial);

El siguiente "join" retorna el mismo resultado: select distinct l1.titulo from libros as l1 join libros as l2 on l1.titulo=l2.titulo where l1.editoriall2.editorial;

Buscamos todos los libros que tienen el mismo precio que "El aleph" empleando subconsulta: 301

select titulo from libros where titulo'El aleph' and precio = (select precio from libros where titulo='El aleph');

Obtenemos la misma salida empleando "join": select l1.titulo from libros as l1 join libros as l2 on l1.precio=l2.precio where l2.titulo='el aleph' and l1.titulol2.titulo;

Buscamos los libros cuyo precio supera el precio promedio de los libros por editorial: select l1.titulo,l1.editorial,l1.precio from libros as l1 where l1.precio > (select avg(l2.precio) from libros as l2 where l1.editorial= l2.editorial);

Obtenemos la misma salida pero empleando un "join" con "having": select l1.editorial,l1.titulo,l1.precio from libros as l1 join libros as l2 on l1.editorial=l2.editorial group by l1.editorial, l1.titulo, l1.precio having l1.precio > avg(l2.precio);

Primer problema: Un club dicta clases de distintos deportes a sus socios. El club tiene una tabla llamada "deportes" en la cual almacena el nombre del deporte, el nombre del profesor que lo dicta, el día de la semana que se dicta y el costo de la cuota mensual. 1- Elimine la tabla si existe: if object_id('deportes') is not null drop table deportes; 2- Cree la tabla: create table deportes( nombre varchar(15), profesor varchar(30), dia varchar(10), cuota decimal(5,2), ); 3- Ingrese algunos registros. Incluya profesores que dicten más de un curso: insert into deportes values('tenis','Ana Lopez','lunes',20); insert into deportes values('natacion','Ana Lopez','martes',15); insert into deportes values('futbol','Carlos Fuentes','miercoles',10); insert into deportes values('basquet','Gaston Garcia','jueves',15); insert into deportes values('padle','Juan Huerta','lunes',15); insert into deportes values('handball','Juan Huerta','martes',10);

302

4- Muestre los nombres de los profesores que dictan más de un deporte empleando subconsulta. 5- Obtenga el mismo resultado empleando join. 6- Buscamos todos los deportes que se dictan el mismo día que un determinado deporte (natacion) empleando subconsulta. 7- Obtenga la misma salida empleando "join".

98 - Subconsulta en lugar de una tabla Se pueden emplear subconsultas que retornen un conjunto de registros de varios campos en lugar de una tabla. Se la denomina tabla derivada y se coloca en la cláusula "from" para que la use un "select" externo. La tabla derivada debe ir entre paréntesis y tener un alias para poder referenciarla. La sintaxis básica es la siguiente: select ALIASdeTABLADERIVADA.CAMPO from (TABLADERIVADA) as ALIAS;

La tabla derivada es una subsonsulta. Podemos probar la consulta que retorna la tabla derivada y luego agregar el "select" externo: select f.*, (select sum(d.precio*cantidad) from Detalles as d where f.numero=d.numerofactura) as total from facturas as f;

La consulta anterior contiene una subconsulta correlacionada; retorna todos los datos de "facturas" y el monto total por factura de "detalles". Esta consulta retorna varios registros y varios campos y será la tabla derivada que emplearemos en la siguiente consulta: select td.numero,c.nombre,td.total from clientes as c join (select f.*, (select sum(d.precio*cantidad) from Detalles as d where f.numero=d.numerofactura) as total from facturas as f) as td on td.codigocliente=c.codigo;

La consulta anterior retorna, de la tabla derivada (referenciada con "td") el número de factura y el monto total, y de la tabla "clientes", el nombre del cliente. Note que este "join" no emplea 2 tablas, sino una tabla propiamente dicha y una tabla derivada, que es en realidad una subconsulta.

303

Un comercio que vende artículos de librería y papelería almacena la información de sus ventas en una tabla llamada "facturas", otra "detalles" y otra "clientes". Eliminamos las tablas si existen: if object_id('detalles') is not null drop table detalles; if object_id('facturas') is not null drop table facturas; if object_id('clientes') is not null drop table clientes;

Las creamos con las siguientes estructuras: create table clientes( codigo int identity, nombre varchar(30), domicilio varchar(30), primary key(codigo) ); create table facturas( numero int not null, fecha datetime, codigocliente int not null, primary key(numero), constraint FK_facturas_cliente foreign key (codigocliente) references clientes(codigo) on update cascade ); create table detalles( numerofactura int not null, numeroitem int not null, articulo varchar(30), precio decimal(5,2), cantidad int, primary key(numerofactura,numeroitem), constraint FK_detalles_numerofactura foreign key (numerofactura) references facturas(numero) on update cascade on delete cascade, );

Ingresamos algunos registros: insert into clientes values('Juan Lopez','Colon 123'); insert into clientes values('Luis Torres','Sucre 987'); insert into clientes values('Ana Garcia','Sarmiento 576'); insert insert insert insert

into into into into

facturas facturas facturas facturas

values(1200,'2007-01-15',1); values(1201,'2007-01-15',2); values(1202,'2007-01-15',3); values(1300,'2007-01-20',1);

insert insert insert insert insert

into into into into into

detalles detalles detalles detalles detalles

values(1200,1,'lapiz',1,100); values(1200,2,'goma',0.5,150); values(1201,1,'regla',1.5,80); values(1201,2,'goma',0.5,200); values(1201,3,'cuaderno',4,90);

304

insert into detalles values(1202,1,'lapiz',1,200); insert into detalles values(1202,2,'escuadra',2,100); insert into detalles values(1300,1,'lapiz',1,300);

Vamos a realizar un "select" para recuperar el número de factura, el código de cliente, la fecha y la suma total de todas las facturas: select f.*, (select sum(d.precio*cantidad) from detalles as d where f.numero=d.numerofactura) as total from facturas as f;

Esta consulta contiene una subconsulta correlacionada. Ahora utilizaremos el resultado de la consulta anterior como una tabla derivada que emplearemos en lugar de una tabla para realizar un "join" y recuperar el número de factura, el nombre del cliente y el monto total por factura: select td.numero,c.nombre,td.total from clientes as c join (select f.*, (select sum(d.precio*cantidad) from detalles as d where f.numero=d.numerofactura) as total from facturas as f) as td on td.codigocliente=c.codigo;

Note que para referenciar la tabla derivada debimos colocar un alias a la consulta. Primer problema: Un club dicta clases de distintos deportes. En una tabla llamada "socios" guarda los datos de los socios, en una tabla llamada "deportes" la información referente a los diferentes deportes que se dictan y en una tabla denominada "inscriptos", las inscripciones de los socios a los distintos deportes. Un socio puede inscribirse en varios deportes el mismo año. Un socio no puede inscribirse en el mismo deporte el mismo año. Distintos socios se inscriben en un mismo deporte en el mismo año. 1- Elimine las tablas si existen: if object_id('inscriptos') is not null drop table inscriptos; if object_id('socios') is not null drop table socios; if object_id('deportes') is not null drop table deportes; 2- Cree las tablas con las siguientes estructuras: create table socios( documento char(8) not null, nombre varchar(30), domicilio varchar(30), primary key(documento) ); create table deportes( codigo tinyint identity, nombre varchar(20),

305

profesor varchar(15), primary key(codigo) ); create table inscriptos( documento char(8) not null, codigodeporte tinyint not null, año char(4), matricula char(1),--'s'=paga, 'n'=impaga primary key(documento,codigodeporte,año), constraint FK_inscriptos_socio foreign key (documento) references socios(documento) on update cascade on delete cascade ); 3- Ingrese algunos registros en las 3 tablas: insert into socios values('22222222','Ana Acosta','Avellaneda 111'); insert into socios values('23333333','Betina Bustos','Bulnes 222'); insert into socios values('24444444','Carlos Castro','Caseros 333'); insert into socios values('25555555','Daniel Duarte','Dinamarca 44'); insert insert insert insert

into into into into

deportes deportes deportes deportes

values('basquet','Juan Juarez'); values('futbol','Pedro Perez'); values('natacion','Marina Morales'); values('tenis','Marina Morales');

insert insert insert insert insert insert insert

into into into into into into into

inscriptos inscriptos inscriptos inscriptos inscriptos inscriptos inscriptos

values values values values values values values

('22222222',3,'2006','s'); ('23333333',3,'2006','s'); ('24444444',3,'2006','n'); ('22222222',3,'2005','s'); ('22222222',3,'2007','n'); ('24444444',1,'2006','s'); ('24444444',2,'2006','s');

4- Realice una consulta en la cual muestre todos los datos de las inscripciones, incluyendo el nombre del deporte y del profesor. Esta consulta es un join. 5- Utilice el resultado de la consulta anterior como una tabla derivada para emplear en lugar de una tabla para realizar un "join" y recuperar el nombre del socio, el deporte en el cual está inscripto, el año, el nombre del profesor y la matrícula.

99 - Subconsulta (update - delete) Dijimos que podemos emplear subconsultas en sentencias "insert", "update", "delete", además de "select". La sintaxis básica para realizar actualizaciones con subconsulta es la siguiente: update TABLA set CAMPO=NUEVOVALOR where CAMPO= (SUBCONSULTA);

Actualizamos el precio de todos los libros de editorial "Emece": 306

update libros set precio=precio+(precio*0.1) where codigoeditorial= (select codigo from editoriales where nombre='Emece');

La subconsulta retorna un único valor. También podemos hacerlo con un join. La sintaxis básica para realizar eliminaciones con subconsulta es la siguiente: delete from TABLA where CAMPO in (SUBCONSULTA);

Eliminamos todos los libros de las editoriales que tiene publicados libros de "Juan Perez": delete from libros where codigoeditorial in (select e.codigo from editoriales as e join libros on codigoeditorial=e.codigo where autor='Juan Perez');

La subconsulta es una combinación que retorna una lista de valores que la consulta externa emplea al seleccionar los registros para la eliminación. Trabajamos con las tablas "libros" y "editoriales" de una librería. Eliminamos las tablas si existen y las creamos: if object_id('libros') is not null drop table libros; if object_id('editoriales') is not null drop table editoriales; create table editoriales( codigo tinyint identity, nombre varchar(30), primary key (codigo) ); create table libros ( codigo int identity, titulo varchar(40), autor varchar(30), codigoeditorial tinyint, precio decimal(5,2), primary key(codigo) );

Ingresamos algunos registros: insert insert insert insert

into into into into

editoriales editoriales editoriales editoriales

insert insert insert insert insert insert

into into into into into into

libros libros libros libros libros libros

values('Planeta'); values('Emece'); values('Paidos'); values('Siglo XXI');

values('Uno','Richard Bach',1,15); values('Ilusiones','Richard Bach',2,20); values('El aleph','Borges',3,10); values('Aprenda PHP','Mario Molina',4,40); values('Poemas','Juan Perez',1,20); values('Cuentos','Juan Perez',3,25);

307

insert into libros values('Java en 10 minutos','Marcelo Perez',2,30);

Actualizamos el precio de todos los libros de editorial "Emece" incrementándolos en un 10%: update libros set precio=precio+(precio*0.1) where codigoeditorial= (select codigo from editoriales where nombre='Emece');

Eliminamos todos los libros de las editoriales que tiene publicados libros de "Juan Perez": delete from libros where codigoeditorial in (select e.codigo from editoriales as e join libros on codigoeditorial=e.codigo where autor='Juan Perez');

Primer problema: Un club dicta clases de distintos deportes a sus socios. El club tiene una tabla llamada "inscriptos" en la cual almacena el número de "socio", el código del deporte en el cual se inscribe y si la matricula está o no paga, y una tabla denominada "socios" en la que guarda los datos personales de cada socio. 1- Elimine las tablas si existen: if object_id('inscriptos') is not null drop table inscriptos; if object_id('socios') is not null drop table socios; 2- Cree las tablas: create table socios( numero int identity, documento char(8), nombre varchar(30), domicilio varchar(30), primary key (numero) ); create table inscriptos ( numerosocio int not null, deporte varchar(20) not null, matricula char(1),-- 'n' o 's' primary key(numerosocio,deporte), constraint FK_inscriptos_socio foreign key (numerosocio) references socios(numero) ); 3- Ingrese algunos registros: insert into socios values('23333333','Alberto Paredes','Colon 111'); insert into socios values('24444444','Carlos Conte','Sarmiento 755'); insert into socios values('25555555','Fabian Fuentes','Caseros 987'); insert into socios values('26666666','Hector Lopez','Sucre 344'); insert into inscriptos values(1,'tenis','s'); insert into inscriptos values(1,'basquet','s'); insert into inscriptos values(1,'natacion','s');

308

insert insert insert insert insert insert insert insert

into into into into into into into into

inscriptos inscriptos inscriptos inscriptos inscriptos inscriptos inscriptos inscriptos

values(2,'tenis','s'); values(2,'natacion','s'); values(2,'basquet','n'); values(2,'futbol','n'); values(3,'tenis','s'); values(3,'basquet','s'); values(3,'natacion','n'); values(4,'basquet','n');

4- Actualizamos la cuota ('s') de todas las inscripciones de un socio determinado (por documento) empleando subconsulta. 5- Elimine todas las inscripciones de los socios que deben alguna matrícula. 5 registros eliminados.

100 - Subconsulta (insert) Aprendimos que una subconsulta puede estar dentro de un "select", "update" y "delete"; también puede estar dentro de un "insert". Podemos ingresar registros en una tabla empleando un "select". La sintaxis básica es la siguiente: insert into TABLAENQUESEINGRESA (CAMPOSTABLA1) select (CAMPOSTABLACONSULTADA) from TABLACONSULTADA;

Un profesor almacena las notas de sus alumnos en una tabla llamada "alumnos". Tiene otra tabla llamada "aprobados", con algunos campos iguales a la tabla "alumnos" pero en ella solamente almacenará los alumnos que han aprobado el ciclo. Ingresamos registros en la tabla "aprobados" seleccionando registros de la tabla "alumnos": insert into aprobados (documento,nota) select (documento,nota) from alumnos;

Entonces, se puede insertar registros en una tabla con la salida devuelta por una consulta a otra tabla; para ello escribimos la consulta y le anteponemos "insert into" junto al nombre de la tabla en la cual ingresaremos los registros y los campos que se cargarán (si se ingresan todos los campos no es necesario listarlos). La cantidad de columnas devueltas en la consulta debe ser la misma que la cantidad de campos a cargar en el "insert". Se pueden insertar valores en una tabla con el resultado de una consulta que incluya cualquier tipo de "join". Un profesor almacena las notas de sus alumnos en una tabla llamada "alumnos" (documento, nombre, nota). Tiene otra tabla llamada "aprobados" (documento,nota) en la que guarda los 309

alumnos que han aprobado el ciclo. Eliminamos las tablas si existen: if object_id('alumnos') is not null drop table alumnos; if object_id('aprobados') is not null drop table aprobados;

Creamos las tablas: create table alumnos( documento char(8) not null, nombre varchar(30), nota decimal(4,2) constraint CK_alumnos_nota_valores check (nota>=0 and nota =0 and nota =4;

Note que no se listan los campos en los cuales se cargan los datos porque tienen el mismo nombre que los de la tabla de la cual extraemos la información. Veamos si los registros se han cargado: select *from aprobados;

Primer problema: Un comercio que vende artículos de librería y papelería almacena la información de sus ventas en una tabla llamada "facturas" y otra "clientes". 1- Elimine las tablas si existen: if object_id('facturas') is not null drop table facturas; if object_id('clientes') is not null drop table clientes; 2-Créelas: create table clientes( codigo int identity, nombre varchar(30), domicilio varchar(30),

310

primary key(codigo) ); create table facturas( numero int not null, fecha datetime, codigocliente int not null, total decimal(6,2), primary key(numero), constraint FK_facturas_cliente foreign key (codigocliente) references clientes(codigo) on update cascade ); 3-Ingrese algunos registros: insert into clientes values('Juan Lopez','Colon 123'); insert into clientes values('Luis Torres','Sucre 987'); insert into clientes values('Ana Garcia','Sarmiento 576'); insert into clientes values('Susana Molina','San Martin 555'); insert insert insert insert insert

into into into into into

facturas facturas facturas facturas facturas

values(1200,'2007-01-15',1,300); values(1201,'2007-01-15',2,550); values(1202,'2007-01-15',3,150); values(1300,'2007-01-20',1,350); values(1310,'2007-01-22',3,100);

4- El comercio necesita una tabla llamada "clientespref" en la cual quiere almacenar el nombre y domicilio de aquellos clientes que han comprado hasta el momento más de 500 pesos en mercaderías. Elimine la tabla si existe y créela con esos 2 campos: if object_id ('clientespref') is not null drop table clientespref; create table clientespref( nombre varchar(30), domicilio varchar(30) ); 5- Ingrese los registros en la tabla "clientespref" seleccionando registros de la tabla "clientes" y "facturas". 6- Vea los registros de "clientespref": select *from clientespref;

101 - Crear tabla a partir de otra (select - into) Podemos crear una tabla e insertar datos en ella en una sola sentencia consultando otra tabla (o varias) con esta sintaxis: select CAMPOSNUEVATABLA into NUEVATABLA from TABLA where CONDICION;

Es decir, se crea una nueva tabla y se inserta en ella el resultado de una consulta a otra tabla. 311

Tenemos la tabla "libros" de una librería y queremos crear una tabla llamada "editoriales" que contenga los nombres de las editoriales. La tabla "editoriales", que no existe, contendrá solamente un campo llamado "nombre". La tabla libros contiene varios registros. Podemos crear la tabla "editoriales" con el campo "nombre" consultando la tabla "libros" y en el mismo momento insertar la información: select distinct editorial as nombre into editoriales from libros;

La tabla "editoriales" se ha creado con el campo "nombre" seleccionado del campo "editorial" de "libros". Los campos de la nueva tabla tienen el mismo nombre, tipo de dato y valores almacenados que los campos listados de la tabla consultada; si se quiere dar otro nombre a los campos de la nueva tabla se deben especificar alias. Entonces, luego de la lista de selección de campos de la tabla a consultar, se coloca "into" seguido del nombre de la nueva tabla y se sigue con la consulta. Podemos emplear "group by", funciones de agrupamiento y "order by" en las consultas. También podemos emplear "select... into" con combinaciones, para crear una tabla que contenga datos de 2 o más tablas. Tenemos la tabla "libros" de una librería y queremos crear una tabla llamada "editoriales" que contenga los nombres de las editoriales. Eliminamos las tablas "libros" y "editoriales" si existen: if object_id('libros')is not null drop table libros; if object_id('editoriales')is not null drop table editoriales;

Creamos la tabla "libros": create table libros( codigo int identity, titulo varchar(40) not null, autor varchar(30), editorial varchar(20), precio decimal(5,2), primary key(codigo) );

Ingresamos algunos registros; insert insert insert insert insert insert

into into into into into into

libros libros libros libros libros libros

values('Uno','Richard Bach','Planeta',15); values('El aleph','Borges','Emece',25); values('Matematica estas ahi','Paenza','Nuevo siglo',18); values('Aprenda PHP','Mario Molina','Nuevo siglo',45); values('Ilusiones','Richard Bach','Planeta',14); values('Java en 10 minutos','Mario Molina','Nuevo siglo',50);

Creamos una tabla llamada "editoriales" que contenga los nombres de las editoriales: 312

select distinct editorial as nombre into editoriales from libros;

Veamos la nueva tabla: select *from editoriales;

Necesitamos una nueva tabla llamada "librosporeditorial" que contenga la cantidad de libros de cada editorial. Primero eliminamos la tabla, si existe: if object_id('cantidadporeditorial') is not null drop table cantidadporeditorial;

Creamos la nueva tabla: select editorial as nombre,count(*) as cantidad into cantidadporeditorial from libros group by editorial;

Veamos los registros de la nueva tabla: select *from cantidadporeditorial;

La tabla "cantidadporeditorial" se ha creado con el campo llamado "nombre" seleccionado del campo "editorial" de "libros" y con el campo "cantidad" con el valor calculado con count(*) de la tabla "libros". Queremos una tabla llamada "ofertas4" que contenga los mismos campos que "libros" y guarde los 4 libros más económicos. Primero eliminamos, si existe, la tabla "ofertas4": if object_id('ofertas4') is not null drop table ofertas4;

Creamos "ofertas4" e insertamos la consulta de "libros": select top 4 * into ofertas4 from libros order by precio desc;

La consulta anterior retorna los 4 primeros libros de la tabla "libros", ordenados en forma ascendente por precio y los almacena en la nueva tabla ("ofertas4"). Note que no se listan los campos a extraer, se coloca un asterisco para indicar que se incluyen todos los campos. Veamos los registros de la nueva tabla: select *from ofertas4;

Agregamos una columna a la tabla "editoriales" que contiene la ciudad en la cual está la casa central de cada editorial: alter table editoriales add ciudad varchar(30);

Actualizamos dicho campo: update editoriales set ciudad='Cordoba' where nombre='Planeta'; update editoriales set ciudad='Cordoba' where nombre='Emece'; update editoriales set ciudad='Buenos Aires' where nombre='Nuevo siglo';

313

Queremos una nueva tabla llamada "librosdecordoba" que contenga los títulos y autores de los libros de editoriales de Cordoba. En primer lugar, la eliminamos, si existe: if object_id('librosdecordoba') is not null drop table librosdecordoba;

Consultamos las 2 tablas y guardamos el resultado en la nueva tabla que estamos creando: select titulo,autor into librosdecordoba from libros join editoriales on editorial=nombre where ciudad='Cordoba';

Consultamos la nueva tabla: select *from librosdecordoba;

Primer problema: Un supermercado almacena los datos de sus empleados en una tabla denominada "empleados" y en una tabla llamada "sucursales" los códigos y ciudades de las diferentes sucursales. 1- Elimine las tablas "empleados" y "sucursales" si existen: if object_id('empleados')is not null drop table empleados; if object_id('sucursales')is not null drop table sucursales; 2- Cree la tabla "sucursales": create table sucursales( codigo int identity, ciudad varchar(30) not null, primary key(codigo) ); 3- Cree la tabla "empleados": create table empleados( documento char(8) not null, nombre varchar(30) not null, domicilio varchar(30), seccion varchar(20), sueldo decimal(6,2), codigosucursal int, primary key(documento), constraint FK_empleados_sucursal foreign key (codigosucursal) references sucursales(codigo) on update cascade ); 4- Ingrese algunos registros para ambas tablas: insert into sucursales values('Cordoba'); insert into sucursales values('Villa Maria'); insert into sucursales values('Carlos Paz'); insert into sucursales values('Cruz del Eje'); insert into empleados values('22222222','Ana Acosta','Avellaneda 111','Secretaria',500,1); insert into empleados values('23333333','Carlos Caseros','Colon 222','Sistemas',800,1);

314

insert into empleados values('24444444','Diana Dominguez','Dinamarca 333','Secretaria',550,2); insert into empleados values('25555555','Fabiola Fuentes','Francia 444','Sistemas',750,2); insert into empleados values('26666666','Gabriela Gonzalez','Guemes 555','Secretaria',580,3); insert into empleados values('27777777','Juan Juarez','Jujuy 777','Secretaria',500,4); insert into empleados values('28888888','Luis Lopez','Lules 888','Sistemas',780,4); insert into empleados values('29999999','Maria Morales','Marina 999','Contaduria',670,4); 5- Realice un join para mostrar todos los datos de "empleados" incluyendo la ciudad de la sucursal: select documento,nombre,domicilio,seccion,sueldo,ciudad from empleados join sucursales on codigosucursal=codigo; 6-Cree una tabla llamada "secciones" que contenga las secciones de la empresa (primero elimínela, si existe): if object_id('secciones') is not null drop table secciones; select distinct seccion as nombre into secciones from empleados; 7- Recupere la información de "secciones": select *from secciones; 3 registros. 8- Se necesita una nueva tabla llamada "sueldosxseccion" que contenga la suma de los sueldos de los empleados por sección. Primero elimine la tabla, si existe: if object_id('sueldosxseccion') is not null drop table sueldosxseccion; select seccion, sum(sueldo) as total into sueldosxseccion from empleados group by seccion; 9- Recupere los registros de la nueva tabla: select *from sueldosxseccion; 10- Se necesita una tabla llamada "maximossueldos" que contenga los mismos campos que "empleados" y guarde los 3 empleados con sueldos más altos. Primero eliminamos, si existe, la tabla "maximossueldos": if object_id('maximossueldos') is not null drop table maximossueldos; select top 3 * into maximossueldos from empleados order by sueldo; 11- Vea los registros de la nueva tabla: select *from maximossueldos; 12- Se necesita una nueva tabla llamada "sucursalCordoba" que contenga los nombres y sección de los empleados de la ciudad de Córdoba. En primer lugar, eliminamos la tabla, si existe. Luego, consulte

315

las tablas "empleados" y "sucursales" y guarde el resultado en la nueva tabla: if object_id('sucursalCordoba') is not null drop table sucursalCordoba; select nombre,ciudad into sucursalCordoba from empleados join sucursales on codigosucursal=codigo where ciudad='Cordoba'; 13- Consulte la nueva tabla: select *from sucursalCordoba;

102 - go Esto solo se aplica cuando instale el SQL Server en su máquina. "go" es un signo de finalización de un lote de sentencias. No es una sentencia, es un comando. El lote de sentencias está compuesto por todas las sentencias antes de "go" o todas las sentencias entre dos "go". Las sentencias no deben ocupar la misma linea en la que está "go". Habrá notado que no se puede ejecutar un procedimiento almacenado luego de otras sentencias a menos que se incluya "execute" (o "exec"). Por ejemplo, si tipeamos: select *from empleados; sp_helpconstraint empleados;

muestra un mensaje de error porque no puede procesar ambas sentencias como un solo lote. Para que no ocurra debemos tipear: select *from empleados; exec sp_helpconstraint empleados;

o separar los lotes con "go": select *from empleados; go sp_helpconstraint empleados;

Las siguientes sentencias no pueden ejecutarse en el mismo lote: create rule, create default,create view, create procedure, create trigger. Cada una de ellas necesita ejecutarse separándolas con "go". Por ejemplo: create table.... go create rule... go

Recuerde que si coloca "go" no debe incluir el "punto y coma" (;) al finalizar una instrucción. 316

No está de más recordar que esto solo se aplica cuando instale el SQL Server en su máquina y ejecute los comandos desde el Query Analyzer.

103 - Vistas Una vista es una alternativa para mostrar datos de varias tablas. Una vista es como una tabla virtual que almacena una consulta. Los datos accesibles a través de la vista no están almacenados en la base de datos como un objeto. Entonces, una vista almacena una consulta como un objeto para utilizarse posteriormente. Las tablas consultadas en una vista se llaman tablas base. En general, se puede dar un nombre a cualquier consulta y almacenarla como una vista. Una vista suele llamarse también tabla virtual porque los resultados que retorna y la manera de referenciarlas es la misma que para una tabla. Las vistas permiten: - ocultar información: permitiendo el acceso a algunos datos y manteniendo oculto el resto de la información que no se incluye en la vista. El usuario opera con los datos de una vista como si se tratara de una tabla, pudiendo modificar tales datos. - simplificar la administración de los permisos de usuario: se pueden dar al usuario permisos para que solamente pueda acceder a los datos a través de vistas, en lugar de concederle permisos para acceder a ciertos campos, así se protegen las tablas base de cambios en su estructura. - mejorar el rendimiento: se puede evitar tipear instrucciones repetidamente almacenando en una vista el resultado de una consulta compleja que incluya información de varias tablas. Podemos crear vistas con: un subconjunto de registros y campos de una tabla; una unión de varias tablas; una combinación de varias tablas; un resumen estadístico de una tabla; un subconjunto de otra vista, combinación de vistas y tablas. Una vista se define usando un "select". La sintaxis básica parcial para crear una vista es la siguiente: create view NOMBREVISTA as SENTENCIASSELECT from TABLA;

El contenido de una vista se muestra con un "select": select *from NOMBREVISTA;

En el siguiente ejemplo creamos la vista "vista_empleados", que es resultado de una combinación en la cual se muestran 4 campos: create view vista_empleados as select (apellido+' '+e.nombre) as nombre,sexo, s.nombre as seccion, cantidadhijos

317

from empleados as e join secciones as s on codigo=seccion

Para ver la información contenida en la vista creada anteriormente tipeamos: select *from vista_empleados;

Podemos realizar consultas a una vista como si se tratara de una tabla: select seccion,count(*) as cantidad from vista_empleados;

Los nombres para vistas deben seguir las mismas reglas que cualquier identificador. Para distinguir una tabla de una vista podemos fijar una convención para darle nombres, por ejemplo, colocar el sufijo ?vista? y luego el nombre de las tablas consultadas en ellas. Los campos y expresiones de la consulta que define una vista DEBEN tener un nombre. Se debe colocar nombre de campo cuando es un campo calculado o si hay 2 campos con el mismo nombre. Note que en el ejemplo, al concatenar los campos "apellido" y "nombre" colocamos un alias; si no lo hubiésemos hecho aparecería un mensaje de error porque dicha expresión DEBE tener un encabezado, SQL Server no lo coloca por defecto. Los nombres de los campos y expresiones de la consulta que define una vista DEBEN ser únicos (no puede haber dos campos o encabezados con igual nombre). Note que en la vista definida en el ejemplo, al campo "s.nombre" le colocamos un alias porque ya había un encabezado (el alias de la concatenación) llamado "nombre" y no pueden repetirse, si sucediera, aparecería un mensaje de error. Otra sintaxis es la siguiente: create view NOMBREVISTA (NOMBRESDEENCABEZADOS) as SENTENCIASSELECT from TABLA;

Creamos otra vista de "empleados" denominada "vista_empleados_ingreso" que almacena la cantidad de empleados por año: create view vista_empleados_ingreso (fecha,cantidad) as select datepart(year,fechaingreso),count(*) from empleados group by datepart(year,fechaingreso)

La diferencia es que se colocan entre paréntesis los encabezados de las columnas que aparecerán en la vista. Si no los colocamos y empleamos la sintaxis vista anteriormente, se emplean los nombres de los campos o alias (que en este caso habría que agregar) colocados en el "select" que define la vista. Los nombres que se colocan entre paréntesis deben ser tantos como los campos o expresiones que se definen en la vista. Las vistas se crean en la base de datos activa. Al crear una vista, SQL Server verifica que existan las tablas a las que se hacen referencia en ella. 318

Se aconseja probar la sentencia "select" con la cual definiremos la vista antes de crearla para asegurarnos que el resultado que retorna es el imaginado. Existen algunas restricciones para el uso de "create view", a saber: - no puede incluir las cláusulas "compute" ni "compute by" ni la palabra clave "into"; - no se pueden crear vistas temporales ni crear vistas sobre tablas temporales. - no se pueden asociar reglas ni valores por defecto a las vistas. - no puede combinarse con otras instrucciones en un mismo lote. Se pueden construir vistas sobre otras vistas. Una empresa almacena la información de sus empleados en dos tablas llamadas "empleados" y "secciones". Eliminamos las tablas, si existen: if object_id('empleados') is not null drop table empleados; if object_id('secciones') is not null drop table secciones;

Creamos las tablas: create table secciones( codigo tinyint identity, nombre varchar(20), sueldo decimal(5,2) constraint CK_secciones_sueldo check (sueldo>=0), constraint PK_secciones primary key (codigo) ); create table empleados( legajo int identity, documento char(8) constraint CK_empleados_documento check (documento like '[0-9][0-9][0-9][0-9][0-9] [0-9][0-9][0-9]'), sexo char(1) constraint CK_empleados_sexo check (sexo in ('f','m')), apellido varchar(20), nombre varchar(20), domicilio varchar(30), seccion tinyint not null, cantidadhijos tinyint constraint CK_empleados_hijos check (cantidadhijos>=0), estadocivil char(10) constraint CK_empleados_estadocivil check (estadocivil in ('casado','divorciado','soltero','viudo')), fechaingreso datetime, constraint PK_empleados primary key (legajo), constraint FK_empleados_seccion foreign key (seccion) references secciones(codigo) on update cascade, constraint UQ_empleados_documento unique(documento) );

Ingresamos algunos registros: insert into secciones values('Administracion',300);

319

insert into secciones values('Contaduría',400); insert into secciones values('Sistemas',500); insert into empleados values('22222222','f','Lopez','Ana','Colon 123',1,2,'casado','1990-10-10'); insert into empleados values('23333333','m','Lopez','Luis','Sucre 235',1,0,'soltero','1990-02-10'); insert into empleados values('24444444','m','Garcia','Marcos','Sarmiento 1234',2,3,'divorciado','1998-07-12'); insert into empleados values('25555555','m','Gomez','Pablo','Bulnes 321',3,2,'casado','1998-10-09'); insert into empleados values('26666666','f','Perez','Laura','Peru 1254',3,3,'casado','2000-05-09');

Eliminamos la vista "vista_empleados" si existe. Aún no hemos aprendido a eliminar vistas, lo veremos próximamente: if object_id('vista_empleados') is not null drop view vista_empleados;

Creamos la vista "vista_empleados", que es resultado de una combinación en la cual se muestran 5 campos: create view vista_empleados as select (apellido+' '+e.nombre) as nombre,sexo, s.nombre as seccion, cantidadhijos from empleados as e join secciones as s on codigo=seccion;

Vemos la información de la vista: select *from vista_empleados;

Realizamos una consulta a la vista como si se tratara de una tabla: select seccion,count(*) as cantidad from vista_empleados group by seccion;

Eliminamos la vista "vista_empleados_ingreso" si existe: if object_id('vista_empleados_ingreso') is not null drop view vista_empleados_ingreso;

Creamos otra vista de "empleados" denominada "vista_empleados_ingreso" que almacena la cantidad de empleados por año: create view vista_empleados_ingreso (fecha,cantidad) as select datepart(year,fechaingreso),count(*) from empleados group by datepart(year,fechaingreso);

Vemos la información: select *from vista_empleados_ingreso;

Primer problema: Un club dicta cursos de distintos deportes. Almacena la información en varias tablas.

320

El director no quiere que los empleados de administración conozcan la estructura de las tablas ni algunos datos de los profesores y socios, por ello se crean vistas a las cuales tendrán acceso. 1- Elimine las tablas y créelas nuevamente: if object_id('inscriptos') is not null drop table inscriptos; if object_id('socios') is not null drop table socios; if object_id('profesores') is not null drop table profesores; if object_id('cursos') is not null drop table cursos; create table socios( documento char(8) not null, nombre varchar(40), domicilio varchar(30), constraint PK_socios_documento primary key (documento) ); create table profesores( documento char(8) not null, nombre varchar(40), domicilio varchar(30), constraint PK_profesores_documento primary key (documento) ); create table cursos( numero tinyint identity, deporte varchar(20), dia varchar(15), constraint CK_inscriptos_dia check (dia in('lunes','martes','miercoles','jueves','viernes','sabado')), documentoprofesor char(8), constraint PK_cursos_numero primary key (numero), ); create table inscriptos( documentosocio char(8) not null, numero tinyint not null, matricula char(1), constraint CK_inscriptos_matricula check (matricula in('s','n')), constraint PK_inscriptos_documento_numero primary key (documentosocio,numero) ); 2- Ingrese algunos registros para todas las tablas: insert into socios values('30000000','Fabian Fuentes','Caseros 987'); insert into socios values('31111111','Gaston Garcia','Guemes 65'); insert into socios values('32222222','Hector Huerta','Sucre 534'); insert into socios values('33333333','Ines Irala','Bulnes 345'); insert insert insert insert

into into into into

profesores profesores profesores profesores

values('22222222','Ana Acosta','Avellaneda 231'); values('23333333','Carlos Caseres','Colon 245'); values('24444444','Daniel Duarte','Sarmiento 987'); values('25555555','Esteban Lopez','Sucre 1204');

insert into cursos values('tenis','lunes','22222222'); insert into cursos values('tenis','martes','22222222'); insert into cursos values('natacion','miercoles','22222222');

321

insert insert insert insert insert

into into into into into

cursos cursos cursos cursos cursos

values('natacion','jueves','23333333'); values('natacion','viernes','23333333'); values('futbol','sabado','24444444'); values('futbol','lunes','24444444'); values('basquet','martes','24444444');

insert insert insert insert insert insert

into into into into into into

inscriptos inscriptos inscriptos inscriptos inscriptos inscriptos

values('30000000',1,'s'); values('30000000',3,'n'); values('30000000',6,null); values('31111111',1,'s'); values('31111111',4,'s'); values('32222222',8,'s');

3- Elimine la vista "vista_club" si existe: if object_id('vista_club') is not null drop view vista_club; 4- Cree una vista en la que aparezca el nombre y documento del socio, el deporte, el día y el nombre del profesor. 5- Muestre la información contenida en la vista. 6- Realice una consulta a la vista donde muestre la cantidad de socios inscriptos en cada deporte ordenados por cantidad. 7- Muestre (consultando la vista) los cursos (deporte y día) para los cuales no hay inscriptos. 8- Muestre los nombres de los socios que no se han inscripto en ningún curso (consultando la vista) 9- Muestre (consultando la vista) los profesores que no tienen asignado ningún deporte aún. 10- Muestre (consultando la vista) el nombre y documento de los socios que deben matrículas. 11- Consulte la vista y muestre los nombres de los profesores y los días en que asisten al club para dictar sus clases. 12- Muestre la misma información anterior pero ordenada por día. 13- Muestre todos los socios que son compañeros en tenis los lunes. 14- Elimine la vista "vista_inscriptos" si existe y créela para que muestre la cantidad de inscriptos por curso, incluyendo el número del curso, el nombre del deporte y el día. 15- Consulte la vista: select *from vista_inscriptos;

104 - Vistas (información) Las vistas son objetos, así que para obtener información de ellos pueden usarse los siguientes procedimientos almacenados del sistema: 322

"sp_help" sin parámetros nos muestra todos los objetos de la base de datos seleccionada, incluidas las vistas. En la columna "Object_type" aparece "view" si es una vista. Si le enviamos como argumento el nombre de una vista, obtenemos la fecha de creación, propietario, los campos y demás información. "sp_helptext" seguido del nombre de una vista nos muestra el texto que la define, excepto si ha sido encriptado. Ejecutando "sp_depends" seguido del nombre de un objeto, obtenemos 2 resultados: - nombre, tipo, campos, etc. de los objetos de los cuales depende el objeto nombrado y - nombre y tipo de los objetos que dependen del objeto nombrado. Si ejecutamos el procedimiento "sp_depends" seguido del nombre de una vista: sp_depends vista_empleados;

aparecen las tablas (y demás objetos) de las cuales depende la vista, es decir, las tablas referenciadas en la misma. Si ejecutamos el procedimiento seguido del nombre de una tabla: sp_depends empleados;

aparecen los objetos que dependen de la tabla, vistas, restricciones, etc. También se puede consultar la tabla del sistema "sysobjects": select *from sysobjects;

Nos muestra nombre y varios datos de todos los objetos de la base de datos actual. La columna "xtype" indica el tipo de objeto, si es una vista, aparece 'V'. Si queremos ver todas las vistas creadas por nosotros, podemos tipear: select *from sysobjects where xtype='V' and-- tipo vista name like 'vista%';--búsqueda con comodín

Una empresa almacena la información de sus empleados en dos tablas llamadas "empleados" y "secciones". Eliminamos las tablas, si existen: if object_id('empleados') is not null drop table empleados; if object_id('secciones') is not null drop table secciones;

Creamos las tablas: create table secciones( codigo tinyint identity, nombre varchar(20), sueldo decimal(5,2) constraint CK_secciones_sueldo check (sueldo>=0), constraint PK_secciones primary key (codigo) ); create table empleados(

323

legajo int identity, documento char(8) constraint CK_empleados_documento check (documento like '[0-9][0-9][0-9][0-9][0-9] [0-9][0-9][0-9]'), sexo char(1) constraint CK_empleados_sexo check (sexo in ('f','m')), apellido varchar(20), nombre varchar(20), domicilio varchar(30), seccion tinyint not null, cantidadhijos tinyint constraint CK_empleados_hijos check (cantidadhijos>=0), estadocivil char(10) constraint CK_empleados_estadocivil check (estadocivil in ('casado','divorciado','soltero','viudo')), fechaingreso datetime, constraint PK_empleados primary key (legajo), constraint FK_empleados_seccion foreign key (seccion) references secciones(codigo) on update cascade, constraint UQ_empleados_documento unique(documento) );

Ingresamos algunos registros: insert into secciones values('Administracion',300); insert into secciones values('Contaduría',400); insert into secciones values('Sistemas',500); insert into empleados values('22222222','f','Lopez','Ana','Colon 123',1,2,'casado','1990-10-10'); insert into empleados values('23333333','m','Lopez','Luis','Sucre 235',1,0,'soltero','1990-02-10'); insert into empleados values('24444444','m','Garcia','Marcos','Sarmiento 1234',2,3,'divorciado','1998-07-12'); insert into empleados values('25555555','m','Gomez','Pablo','Bulnes 321',3,2,'casado','1998-10-09'); insert into empleados values('26666666','f','Perez','Laura','Peru 1254',3,3,'casado','2000-05-09');

Eliminamos la vista "vista_empleados" si existe: if object_id('vista_empleados') is not null drop view vista_empleados;

Creamos la vista "vista_empleados", que es resultado de una combinación en la cual se muestran 5 campos: create view vista_empleados as select (apellido+' '+e.nombre) as nombre,sexo, s.nombre as seccion, cantidadhijos from empleados as e join secciones as s on codigo=seccion;

Vemos la información de la vista: select *from vista_empleados;

Ejecutamos "sp_help" enviándole como argumento el nombre de la vista: 324

sp_help vista_empleados;

Vemos el texto que define la vista: sp_helptext vista_empleados;

Ejecutamos el procedimiento almacenado del sistema "sp_depends" seguido del nombre de la vista: sp_depends vista_empleados;

aparecen las tablas y campos de las cuales depende la vista, es decir, las tablas referenciadas en la misma. Ejecutamos el procedimiento "sp_depends" seguido del nombre de la tabla "empleados": sp_depends empleados;

aparece la vista "vista_empleados" y las restricciones que dependen de ella. Consultamos la tabla del sistema "sysobjects": select *from sysobjects;

Si queremos ver todas las vistas creadas por nosotros, podemos tipear: select *from sysobjects where xtype='V' and-- tipo vista name like 'vista%';--búsqueda con comodín

105 - vistas (encriptar) Podemos ver el texto que define una vista ejecutando el procedimiento almacenado del sistema "sp_helptext" seguido del nombre de la vista: sp_helptext NOMBREVISTA;

Podemos ocultar el texto que define una vista empleando la siguiente sintaxis al crearla: create view NOMBREVISTA with encryption as SENTENCIASSELECT from TABLA;

"with encryption" indica a SQL Server que codifique las sentencias que definen la vista. Creamos una vista con su definición oculta: create view vista_empleados with encryption as select (apellido+' '+e.nombre) as nombre,sexo, s.nombre as seccion, cantidadhijos from empleados as e join secciones as s on codigo=seccion

325

Si ejecutamos el procedimiento almacenado del sistema "sp_helptext" seguido del nombre de una vista encriptada, aparece un mensaje indicando tal situación y el texto no se muestra. Una empresa almacena la información de sus empleados en dos tablas llamadas "empleados" y "secciones". Eliminamos las tablas, si existen: if object_id('empleados') is not null drop table empleados; if object_id('secciones') is not null drop table secciones;

Creamos las tablas: create table secciones( codigo tinyint identity, nombre varchar(20), sueldo decimal(5,2) constraint CK_secciones_sueldo check (sueldo>=0), constraint PK_secciones primary key (codigo) ); create table empleados( legajo int identity, documento char(8) constraint CK_empleados_documento check (documento like '[0-9][0-9][0-9][0-9][0-9] [0-9][0-9][0-9]'), sexo char(1) constraint CK_empleados_sexo check (sexo in ('f','m')), apellido varchar(20), nombre varchar(20), domicilio varchar(30), seccion tinyint not null, cantidadhijos tinyint constraint CK_empleados_hijos check (cantidadhijos>=0), estadocivil char(10) constraint CK_empleados_estadocivil check (estadocivil in ('casado','divorciado','soltero','viudo')), fechaingreso datetime, constraint PK_empleados primary key (legajo), constraint FK_empleados_seccion foreign key (seccion) references secciones(codigo) on update cascade, constraint UQ_empleados_documento unique(documento) );

Eliminamos la vista "vista_empleados" si existe: if object_id('vista_empleados') is not null drop view vista_empleados;

Creamos una vista con su definición oculta: create view vista_empleados with encryption as select (apellido+' '+e.nombre) as nombre,sexo, s.nombre as seccion, cantidadhijos from empleados as e join secciones as s

326

on codigo=seccion;

Ejecutamos el procedimiento almacenado del sistema "sp_helptext" seguido del nombre de una vista encriptada: sp_helptext vista_empleados;

No se muestra.

106 - Vistas (eliminar) Para quitar una vista se emplea "drop view": drop view NOMBREVISTA;

Si se elimina una tabla a la que hace referencia una vista, la vista no se elimina, hay que eliminarla explícitamente. Solo el propietario puede eliminar una vista. Antes de eliminar un objeto, se recomienda ejecutar el procedimiento almacenado de sistema "sp_depends" para averiguar si hay objetos que hagan referencia a él. Eliminamos la vista denominada "vista_empleados": drop view vista_empleados;

Una empresa almacena la información de sus empleados en dos tablas llamadas "empleados" y "secciones". Eliminamos las tablas, si existen: if object_id('empleados') is not null drop table empleados; if object_id('secciones') is not null drop table secciones;

Creamos las tablas: create table secciones( codigo tinyint identity, nombre varchar(20), sueldo decimal(5,2) constraint CK_secciones_sueldo check (sueldo>=0), constraint PK_secciones primary key (codigo) ); create table empleados( legajo int identity, documento char(8) constraint CK_empleados_documento check (documento like '[0-9][0-9][0-9][0-9][0-9] [0-9][0-9][0-9]'), sexo char(1) constraint CK_empleados_sexo check (sexo in ('f','m')), apellido varchar(20), nombre varchar(20), domicilio varchar(30),

327

seccion tinyint not null, cantidadhijos tinyint constraint CK_empleados_hijos check (cantidadhijos>=0), estadocivil char(10) constraint CK_empleados_estadocivil check (estadocivil in ('casado','divorciado','soltero','viudo')), fechaingreso datetime, constraint PK_empleados primary key (legajo), constraint FK_empleados_seccion foreign key (seccion) references secciones(codigo) on update cascade, constraint UQ_empleados_documento unique(documento) );

Ingresamos algunos registros: insert into secciones values('Administracion',300); insert into secciones values('Contaduría',400); insert into secciones values('Sistemas',500); insert into empleados values('22222222','f','Lopez','Ana','Colon 123',1,2,'casado','1990-10-10'); insert into empleados values('23333333','m','Lopez','Luis','Sucre 235',1,0,'soltero','1990-02-10'); insert into empleados values('24444444','m','Garcia','Marcos','Sarmiento 1234',2,3,'divorciado','1998-07-12'); insert into empleados values('25555555','m','Gomez','Pablo','Bulnes 321',3,2,'casado','1998-10-09'); insert into empleados values('26666666','f','Perez','Laura','Peru 1254',3,3,'casado','2000-05-09');

Eliminamos la vista "vista_empleados" si existe: if object_id('vista_empleados') is not null drop view vista_empleados;

Creamos la vista "vista_empleados", que es resultado de una combinación en la cual se muestran 5 campos: create view vista_empleados as select (apellido+' '+e.nombre) as nombre,sexo, s.nombre as seccion, cantidadhijos from empleados as e join secciones as s on codigo=seccion;

Veamos la información de la vista: select *from vista_empleados;

Eliminamos la tabla "empleados": drop table empleados;

Verificamos que la vista aún existe: sp_help;

Eliminamos la vista: 328

drop view vista_empleados;

Verificamos que la vista ya no existe: sp_help vista_empleados;

107 - Vistas (with check option) Es posible obligar a todas las instrucciones de modificación de datos que se ejecutan en una vista a cumplir ciertos criterios. Por ejemplo, creamos la siguiente vista: create view vista_empleados as select apellido, e.nombre, sexo, s.nombre as seccion from empleados as e join secciones as s on seccion=codigo where s.nombre='Administracion' with check option;

La vista definida anteriormente muestra solamente algunos de los datos de los empleados de la sección "Administracion". Además, solamente se permiten modificaciones a los empleados de esa sección. Podemos actualizar el nombre, apellido y sexo a través de la vista, pero no el campo "seccion" porque está restringuido. Una empresa almacena la información de sus empleados en dos tablas llamadas "empleados" y "secciones". Eliminamos las tablas, si existen: if object_id('empleados') is not null drop table empleados; if object_id('secciones') is not null drop table secciones;

Creamos las tablas: create table secciones( codigo tinyint identity, nombre varchar(20), sueldo decimal(5,2) constraint CK_secciones_sueldo check (sueldo>=0), constraint PK_secciones primary key (codigo) ); create table empleados( legajo int identity, documento char(8) constraint CK_empleados_documento check (documento like '[0-9][0-9][0-9][0-9][0-9] [0-9][0-9][0-9]'), sexo char(1) constraint CK_empleados_sexo check (sexo in ('f','m')), apellido varchar(20),

329

nombre varchar(20), domicilio varchar(30), seccion tinyint not null, cantidadhijos tinyint constraint CK_empleados_hijos check (cantidadhijos>=0), estadocivil char(10) constraint CK_empleados_estadocivil check (estadocivil in ('casado','divorciado','soltero','viudo')), fechaingreso datetime, constraint PK_empleados primary key (legajo), constraint FK_empleados_seccion foreign key (seccion) references secciones(codigo) on update cascade, constraint UQ_empleados_documento unique(documento) );

Ingresamos algunos registros: insert into secciones values('Administracion',300); insert into secciones values('Contaduría',400); insert into secciones values('Sistemas',500); insert into empleados values('22222222','f','Lopez','Ana','Colon 123',1,2,'casado','1990-10-10'); insert into empleados values('23333333','m','Lopez','Luis','Sucre 235',1,0,'soltero','1990-02-10'); insert into empleados values('24444444','m','Garcia','Marcos','Sarmiento 1234',2,3,'divorciado','1998-07-12'); insert into empleados values('25555555','m','Gomez','Pablo','Bulnes 321',3,2,'casado','1998-10-09'); insert into empleados values('26666666','f','Perez','Laura','Peru 1254',3,3,'casado','2000-05-09');

Eliminamos la vista "vista_empleados" si existe: if object_id('vista_empleados') is not null drop view vista_empleados;

Creamos la vista "vista_empleados", que es resultado de una combinación: Es posible obligar a todas las instrucciones de modificación de datos que se ejecutan en una vista a cumplir ciertos criterios. Por ejemplo, creamos la siguiente vista: create view vista_empleados as select apellido, e.nombre, sexo, s.nombre as seccion from empleados as e join secciones as s on seccion=codigo where s.nombre='Administracion' with check option;

Consultamos la vista: select *from vista_empleados;

Actualizamos el nombre de un empleado a través de la vista: 330

update vista_empleados set nombre='Beatriz' where nombre='Ana';

Veamos si la modificación se realizó en la tabla: select *from empleados;

Primer problema:

Una empresa almacena la información de sus clientes en dos tablas llamadas "clientes" y "ciudades". 1- Elimine las tablas, si existen: if object_id('clientes') is not null drop table clientes; if object_id('ciudades') is not null drop table ciudades; 2- Cree las tablas: create table ciudades( codigo tinyint identity, nombre varchar(20), constraint PK_ciudades primary key (codigo) ); create table clientes( nombre varchar(20), apellido varchar(20), documento char(8), domicilio varchar(30), codigociudad tinyint constraint FK_clientes_ciudad foreign key (codigociudad) references ciudades(codigo) on update cascade ); 3- Ingrese algunos registros: insert into ciudades values('Cordoba'); insert into ciudades values('Carlos Paz'); insert into ciudades values('Cruz del Eje'); insert into ciudades values('La Falda'); insert insert insert insert insert insert

into into into into into into

clientes clientes clientes clientes clientes clientes

values('Juan','Perez','22222222','Colon 1123',1); values('Karina','Lopez','23333333','San Martin 254',2); values('Luis','Garcia','24444444','Caseros 345',1); values('Marcos','Gonzalez','25555555','Sucre 458',3); values('Nora','Torres','26666666','Bulnes 567',1); values('Oscar','Luque','27777777','San Martin 786',4);

4- Elimine la vista "vista_clientes" si existe: if object_id('vista_clientes') is not null drop view vista_clientes; 5- Cree la vista "vista_clientes" para que recupere el nombre, apellido, documento, domicilio, el código y nombre de la ciudad a la cual pertenece, de la ciudad de "Cordoba" empleando "with check option". 6- Consulte la vista: select *from vista_clientes; 7- Actualice el apellido de un cliente a través de la vista. 8- Verifique que la modificación se realizó en la tabla:

331

select *from clientes; 9- Intente cambiar la ciudad de algún registro. Mensaje de error.

108 - Vistas (modificar datos de una tabla a través de vistas) Si se modifican los datos de una vista, se modifica la tabla base. Se puede insertar, actualizar o eliminar datos de una tabla a través de una vista, teniendo en cuenta lo siguiente, las modificaciones que se realizan a las vistas: - no pueden afectar a más de una tabla consultada. Pueden modificarse datos de una vista que combina varias tablas pero la modificación solamente debe afectar a una sola tabla. - no se pueden cambiar los campos resultado de un cálculo. - pueden generar errores si afectan a campos a las que la vista no hace referencia. Por ejemplo, si se ingresa un registro en una vista que consulta una tabla que tiene campos not null que no están incluidos en la vista. - la opción "with check option" obliga a todas las instrucciones de modificación que se ejecutan en la vista a cumplir ciertos criterios que se especifican al definir la vista. - para eliminar datos de una vista solamente UNA tabla puede ser listada en el "from" de la definicion de la misma. Una empresa almacena la información de sus empleados en dos tablas llamadas "empleados" y "secciones". Eliminamos las tablas, si existen: if object_id('empleados') is not null drop table empleados; if object_id('secciones') is not null drop table secciones;

Creamos las tablas: create table secciones( codigo tinyint identity, nombre varchar(20), sueldo decimal(5,2) constraint CK_secciones_sueldo check (sueldo>=0), constraint PK_secciones primary key (codigo) ); create table empleados( legajo int identity, documento char(8) constraint CK_empleados_documento check (documento like '[0-9][0-9][0-9][0-9][0-9] [0-9][0-9][0-9]'), sexo char(1)

332

constraint CK_empleados_sexo check (sexo in ('f','m')), apellido varchar(20), nombre varchar(20), domicilio varchar(30), seccion tinyint not null, cantidadhijos tinyint constraint CK_empleados_hijos check (cantidadhijos>=0), estadocivil char(10) constraint CK_empleados_estadocivil check (estadocivil in ('casado','divorciado','soltero','viudo')), fechaingreso datetime, constraint PK_empleados primary key (legajo), sueldo decimal(6,2), constraint FK_empleados_seccion foreign key (seccion) references secciones(codigo) on update cascade, constraint UQ_empleados_documento unique(documento) );

Ingresamos algunos registros: insert into secciones values('Administracion',300); insert into secciones values('Contaduría',400); insert into secciones values('Sistemas',500); insert into empleados values('22222222','f','Lopez','Ana','Colon 123',1,2,'casado','1990-10-10',600); insert into empleados values('23333333','m','Lopez','Luis','Sucre 235',1,0,'soltero','1990-02-10',650); insert into empleados values('24444444', 'm', 'Garcia', 'Marcos', 'Sarmiento 1234', 2, 3, 'divorciado', '1998-07-12',800); insert into empleados values('25555555','m','Gomez','Pablo','Bulnes 321',3,2,'casado','1998-10-09',900); insert into empleados values('26666666','f','Perez','Laura','Peru 1254',3,3,'casado','2000-05-09',700);

Eliminamos la vista "vista_empleados" si existe: if object_id('vista_empleados') is not null drop view vista_empleados;

Creamos la vista "vista_empleados", que es resultado de una combinación en la cual se muestran 5 campos: create view vista_empleados as select (apellido+' '+e.nombre) as nombre,sexo, s.nombre as seccion, cantidadhijos from empleados as e join secciones as s on codigo=seccion;

Vemos la información contenida en la vista: select *from vista_empleados;

Eliminamos la vista "vista_empleados2" si existe: if object_id('vista_empleados2') is not null drop view vista_empleados2;

333

Creamos otra vista de "empleados" denominada "vista_empleados2" que consulta solamente la tabla "empleados" con "with check option": create view vista_empleados2 as select nombre, apellido,fechaingreso,seccion,estadocivil,sueldo from empleados where sueldo>=600 with check option;

Consultamos la vista: select *from vista_empleados2;

Ingresamos un registro en la vista "vista_empleados2": insert into vista_empleados2 values('Pedro','Perez','2000-10-10',1,'casado',800);

No es posible insertar un registro en la vista "vista_empleados" porque el campo de la vista "nombre" es un campo calculado. Actualizamos la sección de un registro de la vista "vista_empleados": update vista_empleados set seccion='Sistemas' where nombre='Lopez Ana';

Si intentamos actualizar el nombre de un empleado no lo permite porque es una columna calculada. Actualizamos el nombre de un registro de la vista "vista_empleados2": update vista_empleados2 set nombre='Beatriz' where nombre='Ana';

Verifique que se actualizó la tabla: select *from empleados;

Eliminamos un registro de la vista "vista_empleados2": delete from vista_empleados2 where apellido='Lopez';

Si podemos eliminar registros de la vista "vista_empleados2" dicha vista solamente consulta una tabla. No podemos eliminar registros de la vista "vista_empleados" porque hay varias tablas en su definición. Primer problema: Un club dicta cursos de distintos deportes. Almacena la información en varias tablas. 1- Elimine las tabla "inscriptos", "socios" y "cursos", si existen: if object_id('inscriptos') is not null drop table inscriptos; if object_id('socios') is not null drop table socios; if object_id('cursos') is not null drop table cursos; 2- Cree las tablas: create table socios( documento char(8) not null, nombre varchar(40), domicilio varchar(30), constraint PK_socios_documento primary key (documento) );

334

create table cursos( numero tinyint identity, deporte varchar(20), dia varchar(15), constraint CK_inscriptos_dia check (dia in('lunes','martes','miercoles','jueves','viernes','sabado')), profesor varchar(20), constraint PK_cursos_numero primary key (numero), ); create table inscriptos( documentosocio char(8) not null, numero tinyint not null, matricula char(1), constraint PK_inscriptos_documento_numero primary key (documentosocio,numero), constraint FK_inscriptos_documento foreign key (documentosocio) references socios(documento) on update cascade, constraint FK_inscriptos_numero foreign key (numero) references cursos(numero) on update cascade ); 3- Ingrese algunos registros para todas las tablas: insert into socios values('30000000','Fabian Fuentes','Caseros 987'); insert into socios values('31111111','Gaston Garcia','Guemes 65'); insert into socios values('32222222','Hector Huerta','Sucre 534'); insert into socios values('33333333','Ines Irala','Bulnes 345'); insert insert insert insert insert insert insert

into into into into into into into

cursos cursos cursos cursos cursos cursos cursos

values('tenis','lunes','Ana Acosta'); values('tenis','martes','Ana Acosta'); values('natacion','miercoles','Ana Acosta'); values('natacion','jueves','Carlos Caseres'); values('futbol','sabado','Pedro Perez'); values('futbol','lunes','Pedro Perez'); values('basquet','viernes','Pedro Perez');

insert insert insert insert insert insert insert

into into into into into into into

inscriptos inscriptos inscriptos inscriptos inscriptos inscriptos inscriptos

values('30000000',1,'s'); values('30000000',3,'n'); values('30000000',6,null); values('31111111',1,'s'); values('31111111',4,'s'); values('32222222',1,'s'); values('32222222',7,'s');

4- Realice un join para mostrar todos los datos de todas las tablas, sin repetirlos: select documento,nombre,domicilio,c.numero,deporte,dia, profesor,matricula from socios as s join inscriptos as i on s.documento=documentosocio join cursos as c on c.numero=i.numero; 5- Elimine, si existe, la vista "vista_cursos": if object_id('vista_cursos') is not null drop view vista_cursos; 6- Cree la vista "vista_cursos" que muestre el número, deporte y día de todos los cursos.

335

7- Consulte la vista ordenada por deporte. 8- Ingrese un registro en la vista "vista_cursos" y vea si afectó a "cursos". Puede realizarse el ingreso porque solamente afecta a una tabla base. 9- Actualice un registro sobre la vista y vea si afectó a la tabla "cursos". Puede realizarse la actualización porque solamente afecta a una tabla base. 10- Elimine un registro de la vista para el cual no haya inscriptos y vea si afectó a "cursos". Puede realizarse la eliminación porque solamente afecta a una tabla base. 11- Intente eliminar un registro de la vista para el cual haya inscriptos. No lo permite por la restricción "foreign key". 12- Elimine la vista "vista_inscriptos" si existe y créela para que muestre el documento y nombre del socio, el numero de curso, el deporte y día de los cursos en los cuales está inscripto. 13- Intente ingresar un registro en la vista. No lo permite porque la modificación afecta a más de una tabla base. 14- Actualice un registro de la vista. Lo permite porque la modificación afecta a una sola tabla base. 15- Vea si afectó a la tabla "socios": select *from socios; 16- Intente actualizar el documento de un socio. No lo permite por la restricción. 17- Intente eliminar un registro de la vista. No lo permite porque la vista incluye varias tablas.

109 - Vistas modificar (alter view) Para modificar una vista puede eliminarla y volver a crearla o emplear "alter view". Con "alter view" se modifica la definición de una vista sin afectar los procedimientos almacenados y los permisos. Si elimina una vista y vuelve a crearla, debe reasignar los permisos asociados a ella. Sintaxis básica para alterar una vista: alter view NOMBREVISTA with encryption--opcional as SELECT

En el ejemplo siguiente se altera vista_empleados para agregar el campo "domicilio": alter view vista_empleados with encryption as select (apellido+' '+e.nombre) as nombre,sexo, s.nombre as seccion, cantidadhijos,domicilio from empleados as e

336

join secciones as s on codigo=seccion

Si creó la vista con "with encryption" y quiere modificarla manteniendo la encriptación, debe colocarla nuevamente, en caso de no hacerlo, desaparece. Si crea una vista con "select *" y luego agrega campos a la estructura de las tablas involucradas, los nuevos campos no aparecerán en la vista; esto es porque los campos se seleccionan al ejecutar "create view"; debe alterar la vista. Una empresa almacena la información de sus empleados en dos tablas llamadas "empleados" y "secciones". Eliminamos las tablas, si existen: if object_id('empleados') is not null drop table empleados; if object_id('secciones') is not null drop table secciones;

Creamos las tablas: create table secciones( codigo tinyint identity, nombre varchar(20), constraint PK_secciones primary key (codigo) ); create table empleados( legajo int identity, documento char(8), nombre varchar(30), domicilio varchar(30), seccion tinyint not null, constraint FK_empleados_seccion foreign key (seccion) references secciones(codigo) on update cascade, constraint PK_empleados primary key (documento) );

Ingresamos algunos registros: insert into secciones values('Administracion'); insert into secciones values('Contaduría'); insert into secciones values('Sistemas'); insert insert insert insert insert

into into into into into

empleados empleados empleados empleados empleados

values('22222222','Lopez Ana','Colon 123',1); values('23333333','Lopez Luis','Sucre 235',1); values('24444444','Garcia Marcos','Sarmiento 1234',2); values('25555555','Gomez Pablo','Bulnes 321',3); values('26666666','Perez Laura','Peru 1254',3);

Eliminamos la vista "vista_empleados" si existe: if object_id('vista_empleados') is not null drop view vista_empleados;

Creamos la vista "vista_empleados" encriptada que muestre algunos campos de los empleados de la sección 1 y colocamos "with check option": 337

create view vista_empleados with encryption as select documento,nombre,seccion from empleados where seccion=1 with check option;

Consultamos la vista: select *from vista_empleados;

Veamos el texto de la vista: sp_helptext vista_empleados;

No lo permite porque está encriptada. Si intentamos modificar la sección de un empleado desde la vista la opción "with check option" no lo permite. Modificamos la vista para que muestre el domicilio y no colocamos la opción de encriptación ni "with check option": alter view vista_empleados as select documento,nombre,seccion, domicilio from empleados where seccion=1;

Consultamos la vista para ver si se modificó: select *from vista_empleados;

Aparece el nuevo campo. Veamos el texto de la vista: sp_helptext vista_empleados;

Lo permite porque ya no está encriptada. Actualizamos la sección de un empleado: update vista_empleados set seccion=2 where documento='22222222';

Si se permite porque la opción "with check option" se quitó de la vista. Consultamos la vista: select *from vista_empleados;

Note que el registro modificado ya no aparece porque no pertenece a la sección 1. Eliminamos la vista "vista_empleados2" si existe: if object_id('vista_empleados2') is not null drop view vista_empleados2;

Creamos la vista "vista_empleados2" que muestre todos los campos de la tabla "empleados": 338

create view vista_empleados2 as select *from empleados;

Consultamos la vista: select *from vista_empleados2;

Agregamos un campo a la tabla "empleados": alter table empleados add sueldo decimal(6,2);

Consultamos la vista "vista_empleados2": select *from vista_empleados2;

Note que el nuevo campo agregado a "empleados" no aparece, a pesar que la vista indica que muestre todos los campos de dicha tabla; esto sucede porque los campos se seleccionan al ejecutar "create view", para que aparezcan debemos alterar la vista: alter view vista_empleados2 as select *from empleados;

Consultemos la vista: select *from vista_empleados2;

Primer problema: Un club dicta cursos de distintos deportes. Almacena la información en varias tablas. 1- Elimine las tabla "inscriptos", "socios" y "cursos", si existen: if object_id('inscriptos') is not null drop table inscriptos; if object_id('socios') is not null drop table socios; if object_id('cursos') is not null drop table cursos; 2- Cree las tablas: create table socios( documento char(8) not null, nombre varchar(40), domicilio varchar(30), constraint PK_socios_documento primary key (documento) ); create table cursos( numero tinyint identity, deporte varchar(20), dia varchar(15), constraint CK_inscriptos_dia check (dia in('lunes','martes','miercoles','jueves','viernes','sabado')), profesor varchar(20), constraint PK_cursos_numero primary key (numero), ); create table inscriptos( documentosocio char(8) not null, numero tinyint not null,

339

matricula char(1), constraint PK_inscriptos_documento_numero primary key (documentosocio,numero), constraint FK_inscriptos_documento foreign key (documentosocio) references socios(documento) on update cascade, constraint FK_inscriptos_numero foreign key (numero) references cursos(numero) on update cascade ); 3- Ingrese algunos registros para todas las tablas: insert into socios values('30000000','Fabian Fuentes','Caseros 987'); insert into socios values('31111111','Gaston Garcia','Guemes 65'); insert into socios values('32222222','Hector Huerta','Sucre 534'); insert into socios values('33333333','Ines Irala','Bulnes 345'); insert insert insert insert insert insert insert

into into into into into into into

cursos cursos cursos cursos cursos cursos cursos

values('tenis','lunes','Ana Acosta'); values('tenis','martes','Ana Acosta'); values('natacion','miercoles','Ana Acosta'); values('natacion','jueves','Carlos Caseres'); values('futbol','sabado','Pedro Perez'); values('futbol','lunes','Pedro Perez'); values('basquet','viernes','Pedro Perez');

insert insert insert insert insert insert insert

into into into into into into into

inscriptos inscriptos inscriptos inscriptos inscriptos inscriptos inscriptos

values('30000000',1,'s'); values('30000000',3,'s'); values('30000000',6,null); values('31111111',1,'n'); values('31111111',4,'s'); values('32222222',1,'n'); values('32222222',7,'n');

4- Elimine la vista "vista_deudores" si existe: if object_id('vista_deudores') is not null drop view vista_deudores; 5- Cree la vista "vista_deudores" que muestre el documento y nombre del socio, el deporte, el día y la matrícula, de todas las inscripciones no pagas colocando "with check option". 6- Consulte la vista: select *from vista_deudores; 7- Veamos el texto de la vista. 8- Intente actualizar a "s" la matrícula de una inscripción desde la vista. No lo permite por la opción "with check option". 9- Modifique el documento de un socio mediante la vista. 10- Vea si se alteraron las tablas referenciadas en la vista: select *from socios; select *from inscriptos; 11- Modifique la vista para que muestre el domicilio, coloque la opción de encriptación y omita "with check option". 12- Consulte la vista para ver si se modificó: select *from vista_deudores; Aparece el nuevo campo.

340

13- Vea el texto de la vista. No lo permite porque está encriptada. 14- Actualice la matrícula de un inscripto. Si se permite porque la opción "with check option" se quitó de la vista. 15- Consulte la vista: select *from vista_empleados; Note que el registro modificado ya no aparece porque la matrícula está paga. 16- Elimine la vista "vista_socios" si existe: if object_id('vista_socios') is not null drop view vista_socios; 17- Cree la vista "vista_socios" que muestre todos los campos de la tabla "socios". 18- Consulte la vista. 19- Agregue un campo a la tabla "socios". 20- Consulte la vista "vista_socios". El nuevo campo agregado a "socios" no aparece, pese a que la vista indica que muestre todos los campos de dicha tabla. 21- Altere la vista para que aparezcan todos los campos. 22- Consulte la vista.

110 - Lenguaje de control de flujo (case) La función "case" compara 2 o más valores y devuelve un resultado. La sintaxis es la siguiente: case VALORACOMPARAR when VALOR1 then RESULTADO1 when VALOR2 then RESULTADO2 ... else RESULTADO3 end

Por cada valor hay un "when" y un "then"; si encuentra un valor coincidente en algún "where" ejecuta el "then" correspondiente a ese "where", si no encuentra ninguna coincidencia, se ejecuta el "else"; si no hay parte "else" retorna "null". Finalmente se coloca "end" para indicar que el "case" ha finalizado. Un profesor guarda las notas de sus alumnos de un curso en una tabla llamada "alumnos" que consta de los siguientes campos: - nombre (30 caracteres), - nota (valor entero entre 0 y 10, puede ser nulo).

341

Queremos mostrar los nombres, notas de los alumnos y en una columna extra llamada "resultado" empleamos un case que testee la nota y muestre un mensaje diferente si en dicho campo hay un valor: - 0, 1, 2 ó 3: 'libre'; - 4, 5 ó 6: 'regular'; - 7, 8, 9 ó 10: 'promocionado';

Esta es la sentencia: select nombre,nota, resultado= case nota when 0 then 'libre' when 1 then 'libre' when 2 then 'libre' when 3 then 'libre' when 4 then 'regular' when 5 then 'regular' when 6 then 'regular' when 7 then 'promocionado' when 8 then 'promocionado' when 9 then 'promocionado' when 10 then 'promocionado' end from alumnos;

Note que cada "where" compara un valor puntual, por ello los valores devueltos son iguales para algunos casos. Note que como omitimos la parte "else", en caso que el valor no encuentre coincidencia con ninguno valor "when", retorna "null". Podemos realizar comparaciones en cada "where". La sintaxis es la siguiente: case when VALORACOMPARAR OPERADOR VALOR1 then RESULTADO1 when VALORACOMPARAR OPERADOR VALOR2 then RESULTADO2 ... else RESULTADO3 end

Mostramos los nombres de los alumnos y en una columna extra llamada "resultado" empleamos un case que teste si la nota es menor a 4, está entre 4 y 7 o supera el 7: select nombre, nota, condicion= case when nota=4 and nota=7 then 'promocionado' else 'sin nota' end from alumnos;

Puede utilizar una expresión "case" en cualquier lugar en el que pueda utilizar una expresión. También se puede emplear con "group by" y funciones de agrupamiento. Un profesor guarda las notas de sus alumnos de un curso en una tabla llamada "alumnos" que consta de los siguientes campos: - nombre (30 caracteres), - nota (valor entero entre 0 y 10, puede ser nulo),

342

Eliminamos la tabla si existe y la creamos nuevamente: if object_id('alumnos') is not null drop table alumnos; create table alumnos( nombre varchar(40), nota tinyint, constraint CK_alunos_nota check (nota>=0 and nota