Servidor SQL Server-1

RPP - Taller de Base de Datos

CAPITULO 01 Objetivos.- aprender a ingresar a SQL Server 2008. Además de manipular los archivos de una base de datos y creación de tablas

1 Acceso al Servidor SQL SERVER

2

2 Entorno de Microsoft SQL Server Management Studio

3

3 Creación De Base De Datos

4

4 Creación de Tablas en una Base de Datos

5

5 Ingresando Registros a La Tabla CLIENTES

6

6 Gestión de Archivos de una base de datos

7

7 Separar Una Base De Datos

8

8 Adjuntar Una Base De Datos

8

9 Eliminar una base de datos SQL SERVER

9

Propuesto Practico 01

10

Página 1 de 101 Docentes:

Lic. Luis M. Rojas Z. Lic. Ramiro De la Cruz

Lic. Zambrano Arce Lic. Israel Huaraca


1 Acceso al Servidor SQL SERVER Para acceder al servidor SQL SERVER debes contar con la instalación de una instancia. Y lo podrás ubicar en la siguiente RUTA: INICIO + PROGRAMAS + MICROSOFT SQL SERVER 2008 + SQL SERVER MANAGEMENT STUDIO

Ingresando Credenciales de Seguridad A.-Tipo De Servidor: debe seleccionar Motor de Base de Datos B.- Nombre de Servidor: Debes seleccionar el servidor existente en la computadora Haciendo clic en BUSCAR MÁS

C.-Autenticación: En estas primeras sesiones haremos uso de Autenticación de Windows. D.- Presionar el botón CONECTAR EL NOMBRE QUE RECIBE UN SERVIDOR SQL SERVER ES DIFERENTE EN CADA COMPUTADORA. COMO ES EL CASO DE MI SERVIDOR QUE RECIBE EL NOMBRE DE PC-315E4BA698B1\MANCHESTER





2 Entorno de Microsoft SQL Server Management Studio

Resolver: Qué tipo de autenticación está configurado en tu servidor

Que cantidad de memoria por consulta tiene asignado tu servidor

Que tiempo de espera de consulta tiene tu servidor

Mencionar las opciones de las propiedades del servidor

Cuáles son las bases de datos del sistema

Mencionar los Objetos del Agente SQL SERVER





3 Creación De Base De Datos a) Expandir la raíz del explorador de objetos b) Seleccionar el objeto BASE DE DATOS c) Clic en NUEVA BASE DE DATOS del menú contextual de dicho objeto

d) Ingresar el Nombre de la base de datos LONDRES e) Presionar el botón ACEPTAR

Una vez creado la base de datos LONDRES debes verificar que se encuentre anclado en el explorador de soluciones Dentro de la base de datos veras como se clasificaría los objetos creados como son tablas, procedimientos almacenados u otros objetos a crearse.

NOTA: Las bases de datos de SQL server. Administra sus archivos físicos de diferente manera como lo haría tal vez un archivo MDB de Access. UNA base de datos creada está constituida por más de 1 o más archivos físicos Además hacer uso de una base de datos existente en otra computadora necesita de un servidor SQL server y adjuntar su base de datos existente.





4 Creación de Tablas en una Base de Datos Para este ejemplo haremos uso de la base de datos LONDRES creada anteriormente. Los pasos son los siguientes: a) Expandir la base de datos LONDRES b) Seleccionar el objeto TABLAS c) Clic en NUEVA TABLA del menú contextual de dicho objeto

d) Ingresar Nombre de Columnas y Tipos de Datos de la siguiente manera

e) Presionar el botón guardar de la barra de herramientas. Y asignar el nombre CLIENTES a la tabla creada

Verificar la creación de la tabla CLIENTES TABLA.- las tablas son objetos que nos permiten organizar la información de una empresa. Como pueden ser sus clientes, proveedores, productos, etc. Cada tabla contiene atributos o CAMPOS y cada Campo un tipo de dato que le asigna el desarrollador. Dentro de cada tabla podemos llenar datos (REGISTROS) Desde la cantidad que uno necesite.

Recuerda verificar la existencia de los objetos creados actualizando el servidor SQL server con la tecla f5





5 Ingresando Registros a La Tabla CLIENTES a) Seleccionar la tabla CLIENTES b) Clic en EDITAR LAS PRIMERAS 200 FILAS del menú contextual de dicho objeto

c) Ingresar los siguientes Registros

d) Para GUARDAR los cambios realizados debes presionar el botón EJECUTAR SQL

Tipos De Datos SQL Server 2008





6 Gestión de Archivos de una base de datos SQL SERVER Ahora aprenderemos manejar los archivos de una base de datos. Manejar una base de datos en diferentes servidores. Ahora veamos paso a paso. Primero identifiquemos la RUTA donde se almacenan los archivos de una base de datos. La ruta es:

Cada vez que uno crea una base de datos a través de un asistente o herramientas visuales de SQL server 2008. Una base de datos creada genera sus archivos en algún lugar físico dentro de un disco. En una carpeta que fue configurado en el momento de instalación. Los archivos que genera son: 1 archivo MDF (solo uno) 1 o más archivos NDF

Archivos de la base de datos LONDRES

1 o más archivos LDF EN CONCLUSION cada vez que Ud. crea una base de datos.Automáticamente cada Generar 2 archivos. DEMOSTRACION: A. Crear una base de datos de nombre JPANDAL B. Verificar la ruta de donde se ha creado los archivos de la base de datos creada C. Buscar en la carpeta DATA los archivos de la base de datos creada

Estos 2 archivos generados serán manipulados por los usuarios. Es decir que si Ud. Desea transportar su base de datos de una computadora a otra. Deberá realizar la acción SEPARAR – COPIAR ADJUNTAR





7 Separar Una Base De Datos a) Seleccionar una base de datos existente en el servidor b) Clic en TAREAS + SEPARAR del menú contextual de la base de datos

c) Presionar ACEPTAR Una vez separado la base de datos UD. Podrá copiar los archivos que pertenecen a la base de datos a un disco extraíble como USB u otro. Para poder HACER USO la base de datos en otra computadora. Copiar los archivos en C:\BASE DE DATOS\

8 Adjuntar Una Base De Datos Primero debes contar con los 2 archivos de la base de datos que deseas adjuntar. Una vez ubicado tus archivos. Es hora de ADJUNTAR. a) Seleccionar el objeto Bases de datos del explorador de soluciones b) Clic en ADJUNTAR del menú contextual del objeto seleccionado




RPP - Taller de Base de Datos c) En la ventana Adjuntar base de datos clic en el botón AGREGAR

d) Seleccionar el archivo MDF de la base de datos e) Clic en ACEPTAR f)

Clic en ACEPTAR

9 Eliminar una base de datos SQL SERVER a) Seleccionar la base de datos existente b) Clic en ELIMINAR del menú contextual de la base de datos seleccionada c) De la ventana elimina r objeto presionar ACEPTAR Una base de datos eliminada. También elimina los archivos que estas generaron.





Propuesto Practico 01 1 Crear las siguientes bases de datos: TRILCE, PAMER, UPC, UPCI, JHONNYPANDAL 2 Separar las bases de datos TRILCE Y PAMER 3 Copiar y Guardar los archivos de cada base de datos separada en una carpeta con su mismo nombre en DISCO C 4 Adjuntar las bases de datos TRILCE Y PAMER desde su nueva ubicación física 5 Crear las siguientes tablas en la base de datos TRILCE e Ingresar 10 registros en cada tabla respetando la Calidad de información que se almacena.

6 Generar un diagrama de las tablas creadas 7 Qué objetos contiene la carpeta SEGURIDAD del explorador de objetos de SQL SERVER

8 ingresar el nombre de los INICIOS DE SESION que existen en el servidor SQL SERVER

9 que nombre recibe el servidor que esta Ud. trabajando

10 que objetos muestra el AGENTE SQL SERVER





CAPITULO 02 Objetivos.- aprender a trabajar con TransactSQL además de crear programas haciendo uso de elementos de la programación en SQL server 2008

1 Transact SQL

12

2 Fundamentos de Transact SQL

12

3 Tipos de datos en Transact SQL

13

4 Variables en Transact SQL

16

5 Operadores en Transact SQL

19

6 Estructuras de control en Transact SQL

20

7 Desarrollo de Ejemplos

25


27





1 Transact SQL Transact SQL es el lenguaje de programación que proporciona SQL Server para ampliar SQL con los elementos característicos de los lenguajes de programación: variables, sentencias de control de flujo, bucles, ... Herramienta de Programación TRANSACT-SQL Para acceder a esta herramienta solo debe hacer clic en el botón NUEVA CONSULTA

1.1 Guardar un Script SQL a) Presionar el botón GUARDAR b) Aplicarle un nombre y ubicación ES IMPORTANTE SABER QUE LOS SCRIPTS CREADOS EN CLASE DEBEN SER GUARDADOS PARA UN POSTERIOR USO

2 Fundamentos de Transact SQL Primeros pasos con Transact SQL Para programar en Transact SQL es necesario conocer sus fundamentos. Como introducción vamos a ver algunos elementos y conceptos básicos del lenguaje. Transact SQL no es CASE-SENSITIVE, es decir, no diferencia mayúsculas de minúsculas como otros lenguajes de programación como C o Java. Un comentario es una aclaración que el programador incluye en el código. Son soportados 2 estilos de comentarios, el de línea simple y de multilínea, para lo cual son empleados ciertos caracters especiales como son: -- Para un comentario de linea simple /* ... */ Para un comentario de varias líneas Un literal es un valor fijo de tipo numérico, caracter, cadena o lógico no representado por un identificador (es un valor explícito). Una variable es un valor identificado por un nombre (identificador) sobre el que podemos realizar modificaciones. En Transact SQL los identificadores de variables deben comenzar por el caracter @, es decir, el nombre de una variable debe comenzar por @.Para declarar variables en Transact SQL debemos utilizar la palabra clave declare, seguido del identificador y tipo de datos de la variable.




RPP - Taller de Base de Datos Veamos algunos ejemplos:

-- Esto es un comentario de linea simple /* Este es un comentario con varias líneas. Conjunto de Lineas. */ declare @nombre varchar(50)-- declare declara una variable -- @nombre es el identificador de la -- variable de tipo varchar set @nombre = ‘Jhonny pandal' -- El signo = es un operador -- Jhonny pandal es un literal print @Nombre -- Imprime por pantalla el valor de @nombre. -- No diferencia mayúsculas ni minúsculas

Scripts y lotes. Un script de Transact SQL es un conjunto de sentencias de Transact SQL en formato de texto plano que se ejecutan en un servidor de SQL Server. Un script está compuesto por uno o varios lotes. Un lote delimita el alcance de las variables y sentencias del script. Dentro de un mismo script se diferencian los diferentes lotes a través de la instrucción GO.

-- Este es el primer lote del script SELECT * FROM COMENTARIOS GO-- GO es el separador de lotes -- Este es el segundo lote del script SELECT getdate()-- getdate() es una función integrada que devuelve -- la fecha

En ocasiones es necesario separar las sentencias en varios lotes, porque Transact SQL no permite la ejecución de ciertos comandos en el mismo lote, si bien normalmente también se utilizan los lotes para realizar separaciones lógicas dentro del script.

3 Tipos de datos en Transact SQL Cuando definimos una tabla, variable o constante debemos asignar un tipo de dato que indica los posibles valores. El tipo de datos define el formato de almacenamiento, espacio que de disco-memoria que va a ocupar un campo o variable, restricciones y rango de valores válidos. Transact SQL proporciona una variedad predefinida de tipos de datos. Casi todos los tipos de datos manejados por Transact SQL son similares a los soportados por SQL.




RPP - Taller de Base de Datos A.- Tipos de datos numéricos. SQL Server dispone de varios tipos de datos numéricos. Cuanto mayor sea el número que puedan almacenar mayor será en consecuencia el espacio utilizado para almacenarlo. Como regla general se recomienda usar el tipo de dato mínimo posible. Todos los dato numéricos admiten el valor NULL. Bit. Una columna o variable de tipo bit puede almacenar el rango de valores de 1 a 0. Tinyint. Una columna o variable de tipo tinyint puede almacenar el rango de valores de 0 a 255. SmallInt. Una columna o variable de tipo smallint puede almacenar el rango de valores -32768 a 32767. 31

Int. Una columna o variable de tipo int puede almacenar el rango de valores -2

31

a 2 -1 . 63

63

BigInt. Una columna o variable de tipo bigint puede almacenar el rango de valores -2 a 2 -1 . Decimal(p,s). Una columna de tipo decimal puede almacenar datos númericos decimales sin redondear. Donde p es la precision (número total del dígitos) y s la escala (número de valores decimales) 308

Float. Una columna de datos float puede almacenar el rango de valores -1,79x-10 con el valor máxmo de precisión. La precisión puede variar entre 1 y 53. 38

Real. Sinónimo de float(24). Puede almacenar el rango de valores -3,4x-10 63

Money. Almacena valores númericos monetarios de -2 unidad monetaria.

308,

a 1,79x-10

, si la definimos

38,

a 3,4x-10

63

a 2 -1, con una precisión de hasta diexz milesimas de la

SmallMoney. Almacena valores númericos monetarios de -214.748,3647 a 214.748,3647, con una precisión de hasta diez milesimas de la unidad monetaria. Todos los tipos de datos enteros pueden marcarse con la propiedad identity para hacerlos autonuméricos.

DECLARE @bit bit, @tinyint tinyint, @smallint smallint, @int int, @bigint bigint, @decimal decimal(10,3), -- 10 digitos, 7 enteros y -- 3 decimales @real real, @double float(53), @money money set @bit = 1 print @bit set @tinyint = 255 print @tinyint set @smallint = 32767 print @smallint set @int = 642325 print @int set @decimal = 56565.234 -- Punto como separador decimal print @decimal set @money = 12.34 print @money




RPP - Taller de Base de Datos B.- Tipos de datos de carácter. Char(n). Almacena n caracteres en formato ASCII, un byte por cada letra. Cuando almacenamos datos en el tipo char, siempre se utilizan los n caracteres indicados, incluso si la entrada de datos es inferior. Por ejemplo, si en un char(5), guardamos el valor 'A', se almacena 'A ', ocupando los cinco bytes. Varchar(n).Almacena n caracteres en formato ASCII, un byte por cada letra. Cuando almacenamos datos en el tipo varchar, unicamente se utilizan los caracteres necesarios,Por ejemplo, si en un varchar(255), guardamos el valor 'A', se almacena 'A', ocupando solo un byte bytes. 31

Varchar(max). Igual que varchar, pero al declararse como max puede almacenar 2 -1 bytes. Nchar(n).Almacena n caracteres en formato UNICODE, dos bytes por cada letra. Es recomendable utilizar este tipo de datos cuando los valores que vayamos a almacenar puedan pertenecer a diferente idomas. Nvarchar(n).Almacena n caracteres en formato UNICODE, dos bytes por cada letra. Es recomendable utilizar este tipo de datos cuando los valores que vayamos a almacenar puedan pertenecer a diferente idomas. 31

Nvarchar(max).Igual que varchar, pero al declararse como max puede almacenar 2 -1 bytes.

C.- Tipos de datos de fecha. Datetime. Almacena fechas con una precisión de milisegundo. Debe usarse para fechas muy específicas. SmallDatetime. Almacena fechas con una precision de minuto, por lo que ocupa la mitad de espacio de que el tipo datetime, para tablas que puedan llegar a tener muchos datos es un factor a tener muy en cuenta..

D.- Tipos de datos binarios. Binary. Se utiliza para almacenar datos binarios de longitud fija, con una longitud máxima de 8000 bytes. Varbinary. Se utiliza para almacenar datos binarios de longitud variable, con una longitud máxima de 8000 bytes..Es muy similar a binary, salvo que varbinary utiliza menos espacio en disco. 31

Varbinary(max).Igual que varbinary, pero puede almacenar 2 -1 bytes

E .- Tipo de datos XML. XML.Una de las grandes mejoras que incorpora SQL Server 2005 es el soporte nativo para XML. Como podemos deducir, este tipo de datos se utiliza para almacenar XML.

DECLARE @myxml XML set @myxml = (SELECT @@SERVERNAME NOMBRE FOR XML RAW, TYPE) print cast(@myxml asvarchar(max)) Obtendremos la siguiente salida:




RPP - Taller de Base de Datos F.- Otros tipos de datos. UniqueIdentifier. Se utiliza para identificadores únicos. Para generar identificadores únicos debemos utilizar la función NEWID().

DECLARE @myuniqueid UNIQUEIDENTIFIER set @myuniqueid = NEWID() print cast(@myuniqueid as varchar(36)) Obtendremos la siguiente salida: 46141D79-102C-4C29-A620-792EA0208637 Sql_Variant.Permite almacenar valores de diferentes tipos de datos. No puede almacena varchar(max), xml, timestamp y tipos de datos definidos por el usuario.

G.- Tipos de datos personalizados. Transact SQL permite la creación de tipos de datos personalizados, a trevés de la instrucción CREATE TYPE. Personalmente, desaconsejo el uso de tipos de datos personalizados.

CREATE TYPEMD5FROMCHAR(32) NULL GO DECLARE @miMD5 MD5 set @miMD5 = '0000000000000000000000000000000A' print @miMD5

4 Variables en Transact SQL Declarar variables es Transact SQL Una variable es un valor identificado por un nombre (identificador) sobre el que podemos realizar modificaciones. En Transact SQL los identificadores de variables deben comenzar por el carácter @, es decir, el nombre de una variable debe comenzar por @. Para declarar variables en Transact SQL debemos utilizar la palabra clave declare, seguido del identificador y tipo de datos de la variable.

-- Esto es un comentario de linea simple /* Este es un comentario con varias líneas. Conjunto de Lineas. */ declare @nombre varchar(50)-- declare declara una variable -- @nombre es el identificador de la -- variable de tipo varchar set @nombre = 'MANCHESTER UNITED' -- El signo = es un operador -- MANCHESTER UNITED es un literal print @Nombre -- Imprime por pantalla el valor de @nombre. -- No diferencia mayúsculas ni minúsculas




RPP - Taller de Base de Datos Asignar variables en Transact SQL En Transact SQL podemos asignar valores a una variable de varias formas: A través de la instruncción set. Utilizando una sentencia SELECT. Realizando un FETCH de un cursor. El siguiente ejemplo muestra como asignar una variable utilizando la instrucción SET.

DECLARE @nombre VARCHAR(100) -- La consulta debe devolver un único registro SET @nombre = (SELECT nombre FROM CLIENTES WHERE ID = 1) PRINT @nombre El siguiente ejemplo muestra como asignar variables utilizando una sentencia SELECT.

DECLARE @nombre VARCHAR(100), @apellido1 VARCHAR(100), @apellido2 VARCHAR(100)

SELECT @nombre=nombre , @apellido1=Apellido1, @apellido2=Apellido2 FROM CLIENTES WHERE ID = 1

PRINT @nombre PRINT @apellido1 PRINT @apellido2




RPP - Taller de Base de Datos Un punto a tener en cuenta cuando asignamos variables de este modo, es que si la consulta SELECT devuelve más de un registro, las variables quedarán asignadas con los valores de la última fila devuelta. Por último veamos como asignar variables a través de un cursor.

DECLARE @nombre VARCHAR(100), @apellido1 VARCHAR(100), @apellido2 VARCHAR(100)

DECLARE CDATOS CURSOR FOR SELECT nombre , Apellido1, Apellido2 FROM CLIENTES

OPEN CDATOS FETCH CDATOS INTO @nombre, @apellido1, @apellido2

WHILE (@@FETCH_STATUS = 0) BEGIN PRINT @nombre PRINT @apellido1 PRINT @apellido2 FETCH CDATOS INTO @nombre, @apellido1, @apellido2 END

CLOSE CDATOS DEALLOCATE CDATOS Veremos los cursores con más detalle más adelante en este tutorial.





5 Operadores en Transact SQL La siguiente tabla ilustra los operadores de Transact SQL . Tipo de operador Operador de asignación

Operadores =

Operadores aritméticos + (suma) - (resta) * (multiplicación) / (división) ** (exponente) % (modulo) Operadores relacionales = (igual a) <> (distinto de) o de comparación != (distinto de) < (menor que) > (mayor que) >= (mayor o igual a) <= (menor o igual a) !> (no mayor a) !< (no menor a) Operadores lógicos

Operador de concatenación

AND (y lógico) NOT (negacion) OR (o lógico) & (AND a nivel de bit) | (OR a nivel de bit) ^ (OR exclusivo a nivel de bit)

+

ALL (Devuelve TRUE si el conjunto completo de comparaciones es

Otros

TRUE) ANY(Devuelve TRUE si cualquier elemento del conjunto de comparaciones es TRUE) BETWEEN (Devuelve TRUE si el operando está dentro del intervalo) EXISTS (TRUE si una subconsulta contiene filas) IN (TRUE si el operando está en la lista) LIKE (TRUE si el operando coincide con un patron) NOT (Invierte el valor de cualquier operador booleano) SOME(Devuelve TRUE si alguna de las comparaciones de un conjunto es TRUE)





6 Estructuras de control en Transact SQL Estructura condicional IF La estructura condicional IF permite evaluar una expresión booleana (resultado SI - NO), y ejecutar las operaciones contenidas en el bloque formado por BEGIN END.

IF () BEGIN ... END ELSEIF () BEGIN ... END ELSE BEGIN ... END

Ejemplo de la estructura condicional IF.

DECLARE @Web varchar(100), @diminutivo varchar(3) SET @diminutivo = 'DJK' IF

@diminutivo = 'DJK' BEGIN PRINT 'www.pandalsoft.com' END

ELSE BEGIN PRINT 'Otra Web (peor!)' END




RPP - Taller de Base de Datos La estructura IF admite el uso de subconsultas:

DECLARE @coPais int, @descripcion varchar(255) set @coPais = 5 set @descripcion = 'España' IFEXISTS(SELECT * FROM PAISES WHERE CO_PAIS = @coPais) BEGIN UPDATE PAISES SET DESCRIPCION = @descripcion WHERE CO_PAIS = @coPais END ELSE BEGIN INSERT INTO PAISES (CO_PAIS, DESCRIPCION) VALUES (@coPais, @descripcion) END

Estructura condicional CASE La estructura condicional CASE permite evaluar una expresion y devolver un valor u otro. La sintaxis general de case es:

CASE WHENTHEN WHENTHEN ELSE -- Valor por defecto END Ejemplo de CASE.

DECLARE @Web varchar(100), @diminutivo varchar(3) SET @diminutivo = 'DJK' SET @Web = (CASE @diminutivo WHEN 'DJK' THEN 'www.pandalsoft.com' WHEN 'ALM' THEN 'www.manchester.com' ELSE 'www.united.com' END) PRINT @Web




RPP - Taller de Base de Datos Otra sintaxis de CASE nos permite evaluar diferentes expresiones:

CASE WHEN = THEN WHEN = THEN ELSE -- Valor por defecto END El mismo ejemplo aplicando esta sintaxis:

DECLARE @Web varchar(100), @diminutivo varchar(3) SET @diminutivo = 'DJK' SET @Web = (CASE WHEN @diminutivo = 'DJK' THEN 'www.manchester.com' WHEN @diminutivo = 'ALM' THEN 'www.united.com' ELSE 'www.jhonnypandal.com' END) PRINT @Web

Otro aspecto muy interesante de CASE es que permite el uso de subconsultas.

DECLARE @Web varchar(100), @diminutivo varchar(3) SET @diminutivo = 'DJK' SET @Web = (CASE WHEN @diminutivo = 'DJK' THEN (SELECT web FROM WEBS WHERE id=1) WHEN @diminutivo = 'ALM' THEN (SELECT web FROM WEBS WHERE id=2) ELSE 'www.jhonnypandal.com' END) PRINT @Web




RPP - Taller de Base de Datos Bucle WHILE El bucle WHILE se repite mientras expresion se evalue como verdadero. Es el único tipo de bucle del que dispone Transact SQL.

WHILE BEGIN ... END Un ejemplo del bucle WHILE.

DECLARE @contador int SET @contador = 0 WHILE (@contador < 100) BEGIN SET @contador = @contador + 1 PRINT 'Iteracion del bucle ' + cast(@contador AS varchar) END Podemos pasar a la siguiente iteración del bucle utilizando CONTINUE.

DECLARE @contador int SET @contador = 0 WHILE (@contador < 100) BEGIN SET @contador = @contador + 1 IF (@contador % 2 = 0) CONTINUE PRINT 'Iteracion del bucle ' + cast(@contador AS varchar) END El bucle se dejará de repetir con la instrucción BREAK.

DECLARE @contador int SET @contador = 0 WHILE (1 = 1) BEGIN SET @contador = @contador + 1 IF (@contador % 50 = 0) BREAK PRINT 'Iteracion del bucle ' + cast(@contador AS varchar) END




RPP - Taller de Base de Datos También podemos utilizar el bucle WHILE conuntamente con subconsultas.

DECLARE @coRecibo int WHILE EXISTS (SELECT * FROM RECIBOS WHERE PENDIENTE = 'S')-- Ojo, la subconsulta se ejecuta -- una vez por cada iteracion -- del bucle! BEGIN SET @coRecibo = (SELECTTOP 1 CO_RECIBO FROM RECIBOS WHERE PENDIENTE = 'S') UPDATE RECIBOS SET PENDIENTE = 'N' WHERE CO_RECIBO = @coRecibo END

Estructura GOTO La sentencia goto nos permite desviar el flujo de ejecución hacia una etiqueta. Fué muy utilizada en versiones anteriores de SQL Server conjuntamente con la variable de sistema @@ERROR para el control de errores. Actualmente, se desaconseja el uso GOTO, recomendandose el uso de TRY - CATCH para la gestion de errores.

DECLARE @divisor int, @dividendo int, @resultado int SET @dividendo = 100 SET @divisor = 0 SET @resultado = @dividendo/@divisor IF @@ERROR > 0 GOTO error PRINT 'No hay error' RETURN error: PRINT 'Se ha producido una division por cero'





7 Desarrollo de Ejemplos A.- Programa que ingresa dos números y muestra la suma Declare @Num1 Int ,@Num2 Int,@Total Int Set @Num1 =250 Set @Num2 =550 Set @Total =@Num1 + @Num2 Print @Total B.- Programa que ingrese Nombre De Producto, Precio, Cantidad Y Mostrar Pago Total, IGV Y Pago Neto Declare @NomProd Varchar(50) ,@Precio Money,@Cant Int,@TOTAL Money, @IGV Money,@NETO Money Set @NomProd ='Camiseta Oficial del Manchester United 2011' Set @Precio =250 Set @Cant =10 Set @TOTAL =@Precio * @Cant Set @IGV =@TOTAL * 0.19 Set @NETO =@TOTAL + @IGV Print @TOTAl Print @IGV Print @NETO C.- Programa que ingrese la edad de un alumno y determine si es mayor o menos de edad Declare @Nombre Varchar(50), @Mensaje Varchar(50), @Edad Int Set @Nombre ='Mabel' Set @Edad =12 IF @Edad >=18 Begin Set @Mensaje='MAYOR DE EDAD' End Else Begin Set @Mensaje='MENOR DE EDAD' End Print @Mensaje D.- Programa que permita ingresar el nombre del empleado y la venta del mes. Si las ventas superan los 5000 soles. Recibirá un pago de 800 soles. Caso contrario solo 400 soles. Declare @Nombre Varchar(50), @Ventas Money, @Pago Money Set @Nombre ='Fabio Da Silva' Set @Ventas=5580.25 IF @Ventas >5000 Begin Set @Pago =800 End Else Begin Set @Pago =400 End Print @Pago




RPP - Taller de Base de Datos E.- Programa que ingrese el promedio de un alumno. Su condicion sera: Mayor de 15 EXELENTE, mayor de 10 APROBADO, Mayor de 5 DESAPROBADO, los demas PESIMO Declare @Nombre Varchar(50), @Promedio Int, @Condicion Varchar(30) Set @Nombre ='Alex Ferguson' Set @Promedio=15 IF @Promedio >15 Begin Set @Condicion ='EXELENTE' End Else If @Promedio >10 Begin Set @Condicion ='APROBADO' End Else If @Promedio >5 Begin Set @Condicion ='DESAPROBADO' End Else Begin Set @Condicion ='PESIMO' End Print @Condicion F.- Programa que ingrese el promedio de un alumno. Su condición será: Mayor de 15 EXELENTE, mayor de 10 APROBADO, Mayor de 5 DESAPROBADO, los demás PESIMO Declare @Nombre Varchar(50), @Promedio Int, @Condicion Varchar(30) Set @Nombre =’JHONNY PANDAL POMA' Set @Promedio=15 Set @Condicion=(CASE WHEN @Promedio >15 THEN 'EXELENTE' WHEN @Promedio >10 THEN 'APROBADO' WHEN @Promedio >5 THEN 'DESAPROBADO' ELSE 'PESIMO' END) PRINT 'el alumno ' + @Nombre + ' Esta ' + @Condicion G.- Programa que ingrese el area de trabajo del empleado y defina su sueldo. Sabiendo que OBRERO gana 500, EMPLEADO gana 900 y GERENTE 2500 Declare @Nombre Varchar(50), @Sueldo Money, @Area Varchar(30) Set @Nombre =Ryan Giggs' Set @Area='OBRERO' Set @Sueldo=(CASE @Area WHEN 'OBRERO' THEN 500 WHEN 'EMPLEADO' THEN 900 WHEN 'GERENTE' THEN 2500 END) PRINT 'el SR. ' + @Nombre + ' Recibe S/. ' + CAST(@Sueldo AS VARCHAR(50))





Propuesto Practico 02 1.

EDAD.VB Desarrollar un programa que ingrese el nombre y la edad de un alumno y le muestre como mensaje. Si dicho alumno es MAYOR DE EDAD o MENOR DE EDAD

2.

AULA.VB Desarrollar un programa que ingrese el numero de aula, fecha inicio, fecha termino, turno y cantidad de alumno matriculados. Si el aula programada tiene mas de 15 alumnos el programa debe mostrar un mensaje de ACTIVO caso contrario INACTIVO.

3.

VENTAS.VB Desarrolla un programa que ingrese las ventas de un empleado. Si sus ventas superan los 10000 mensuales. Se mostrara un mensaje de EMPLEADO DEL MES caso contrario EMPLEADO NORMAL.

4.

ALUMNO.VB Desarrolle un programa que ingrese el nombre y el promedio de un alumno. El programa debe mostrar un mensajes indicando si el alumno esta APROBADO o DESAPROBADO

5.

TIPO.VB Desarrollar un programa que ingrese el nombre, precio y tipo (NACIONAL o IMPORTADO) del producto. Los productos nacionales reciben un incremento de S/.100 y los importados S/. 300 soles. El programa debe mostrar: Descuento y pago neto.

6.

CARGO.VB Desarrollar un programa que ingrese el nombre, apellido, edad, sexo y cargo de un empleado. Su sueldo depende del cargo que ocupe. Detalles:(GERENTE gana S/. 2500), (OBRERO gana S/. 500), (VENTAS gana S/. 2500) otro cargo diferente a los anteriores ganaría S/. 350.00

7.

PROMEDIO.VB Desarrolla un Programa que ingrese el nombre y las 4 notas de un alumno. El programa debe mostrar el promedio y el acumulado de notas. ADEMAS El alumno mostrara un mensaje según el promedio que haya sacado. (PROM > 20 promedio invalido), (PROM > 14 alumno excelente), (PROM > 10 alumno aprobado), CASO CONTRARIO Desaprobado.

8.

BUSCADOR.VB Desarrolla un programa que ingrese el código de un alumno y te muestre su nombre, teléfono y dirección. DETALLE: (A001, Juan Perez, 450-9600, Jr.Ica 1200) , (A002, Nadia Ivette, 478-9655, Jr.puno 478), (A003, Joce Ramirez, 987-989955, Calle Bahia 175), (A004, Diana Robles, 785-9655, Calle Los Pinos 125), Si ingresaras un código incorrecto mostrara una mensaje de ERROR DE INGRESO

Es importante repetir. que es muy importante el aprendizaje de este tipo de programación. Ya que será necesario para la comprensión de los siguientes capítulos

Fecha de ENTREGA:





CAPITULO 03 Objetivos.- administrar y ampliar el tamaño de una base de datos. Además de normalizar la misma. Haciendo uso de restricciones que permiten datos correctos

1 Archivos de Base de Datos

29

2 Creación de Bases de Datos

29

3 Ampliando el Tamaño una Base De Datos

30

4 Creación de Tablas

31

5 Integridad de Datos de una Tabla

31

6 Integridad referencial FOREIGN KEY

33

7 Generando el código de CURSO-INSTRUCTOR

34

8 Generando el código de CARGO-EMPLEADO-DISTRITO

35

9 Creación de Diagrama

36


37





1 Archivos de Base de Datos Archivos de base de datos Las bases de datos de SQL Server utilizan tres tipos de archivos: 

Archivos de datos principales El archivo de datos principal es el punto de partida de la base de datos y apunta a los otros archivos de la base de datos. Cada base de datos tiene un archivo de datos principal. La extensión recomendada para los nombres de archivos de datos principales es .mdf.



Archivos de datos secundarios Los archivos de datos secundarios son todos los archivos de datos menos el archivo de datos principal. Puede que algunas bases de datos no tengan archivos de datos secundarios, mientras que otras pueden tener varios archivos de datos secundarios. La extensión de nombre de archivo recomendada para los archivos de datos secundarios es .ndf.



Archivos de registro Los archivos de registro almacenan toda la información de registro que se utiliza para recuperar la base de datos. Como mínimo, tiene que haber un archivo de registro por cada base de datos, aunque puede haber varios. La extensión de nombre de archivo recomendada para los archivos de registro es .ldf.

2 Creación de Bases de Datos A una línea Create Database Empresa Con esta línea estas creando una base de datos de nombre empresa dentro de la carpeta DATA

Con Atributos configurados para cada archivo Create Database Apple ON (Name=Apple_Dat, FileName='C:\Apple\Apple_Dat.MDF', Size=5, Maxsize=10, Filegrowth=2) LOG ON (Name=Apple_Log, FileName='C:\Apple\Apple_Log.LDF', Size=2, Maxsize=5, Filegrowth=1) Detalles: la base de datos esta creado sobre una carpeta APPLE en disco local C. Name.- especifica el nombre del archivo Filename.- determinas la ubicación física del archivo Size.- espacio mínimo que debe tener el archivo al ser construido Maxsize.- espacio máximo que puede almacenar dicho archivo Filegrowth.- el incremente de crecimiento del archivo a partir de su tamaño mínimo hasta su tamaño máximo





3 Ampliando el Tamaño una Base De Datos Alter Database Apple Add File (Name=Apple_Dat1, FileName='C:\Apple\Apple_Dat1.NDF', Size=3, Maxsize=15, Filegrowth=3)

Archivos de datos Apple_Dat1 con un tamaño de 15 Mb

Alter Database Apple Add File (Name=Apple_Dat2, FileName='C:\Apple\Apple_Dat2.NDF', Size=3, Maxsize=20, Filegrowth=4)



Alter Database Apple Add File (Name=Apple_Dat3, FileName='C:\Apple\Apple_Dat3.NDF', Size=3, Maxsize=7, Filegrowth=1) Alter Database Apple Add File (Name=Apple_Log1, FileName='C:\Apple\Apple_Log1.LDF', Size=3, Maxsize=5, Filegrowth=1), (Name=Apple_Log2, FileName='C:\Apple\Apple_Log2.LDF', Size=3, Maxsize=15, Filegrowth=1)

Grupo de Archivos Lógicos de 5mb y 15 mb

Verificando los archivos Sp_Helpdb Apple





4 Creacion de Tablas Una de las primeras restrcciones que uno aplica dentro de una tabla son los tipos de datos. Al crear una tabla uno debe especificar un tipo de dato relacionado con lo que almacenara dicho campo. Ej. NOMBRE debe almacenar un varchar.

5 Integridad de Datos de una Tabla Restricciones PRIMARY KEY Una tabla suele tener una columna o una combinación de columnas cuyos valores identifican de forma única cada fila de la tabla. Estas columnas se denominan claves principales de la tabla y exigen la integridad de entidad de la tabla. Puede crear una clave principal mediante la definición de una restricción PRIMARY KEY cuando cree o modifique una tabla. Este campo no acepta valores NULL por defecto.

Imposible ingresar 2 productos con mismo código.

ERROR DE USUARIO




RPP - Taller de Base de Datos Restricciones Null o Not Null.- NOT NULL Determina que el campo tenga datos de manera obligatoria. NULL por el contrario null admite campos vacíos. Para hacer uso de estas restricciones debe contar con un análisis y así conseguir unos datos consistentes.

Restricciones Unique.-Solo admit5e valores únicos dentro de un campo. Una tabla puede contar con muchos campos con restricción UNIQUE.

Restricciones Check.- Nos permite aplicar una regla de validación dentro de un campos. Es decir una regla de datos que podrían ingresar dentro de un campo. Ej. Campo EDAD solo admite valores >=18 debido a que los clientes deben ser mayores de edad..




RPP - Taller de Base de Datos Definiciones DEFAULT.- Los valores por defecto indican que valor será guardado en una columna si no se especifica un valor para la columna cuando se inserta una fila. Las definiciones DEFAULT pueden ser creadas cuando la tabla es creada (como parte de la definición de la tabla) o pueden ser agregadas a una tabla existente. Cada columna en una tabla puede contener una sola definición DEFAULT.

6 Integridad referencial FOREIGN KEY La integridad referencial preserva las relaciones definidas entre tablas, cuando se entran, modifican o borran registros. En SQL Server, la integridad referencial esta basada en interrelaciones entre claves ajenas y claves primarias o entre claves ajenas y claves únicas (a través de la restricciones FOREIGN KEY y CHECK). La integridad referencial asegura que los valores de las claves son consistentes a través de distintas tablas. Tal consistencia requiere que no existan referencia a valores inexistentes y que, si un valor clave cambia, todas las referencias cambien consistentemente a lo largo de la base de datos. Cuando se fuerza la integridad referencial, SQL Server previene a los usuarios de realizar lo siguiente: ·

Agregar registros a una tabla relacionada si no hay registros asociados en la correspondiente tabla primaria.

·

Cambiar valores en la tabla primaria que resulten en registros huérfanos en las tablas relacionadas.

·

Borrar registros desde una tabla primaria si existen registros relacionados en la tabla ajena.

EJEMPLO: Para este ejemplo crearemos una tabla cursos y la tabla Instructor.Estas tablas están relacionadas por un campo común CODIGO de la tabla cursos y CURSO de la tabla Instructor.





7 Generando el código de CURSO-INSTRUCTOR Comenzamos creando la tabla cursos e ingresando 3 registros. Luego creamos la tabla instructor y mediante FOREIGN KEY establecemos la relación con la tabla CURSOS ya creado anteriormente. Seguidamente. Ingresamos registros en la tabla INSTRUCTOR. Pero recordando que el último campo hace uso de datos externos para asegurar la integridad de datos.

Create Table Cursos (Codigo Char(5) Primary key, Nombre Varchar(50)) Go Insert Into Cursos Values('C0001','Sql Server Administrator') Insert Into Cursos Values('C0002','Visual Basic 2008 - N Layer') Insert Into Cursos Values('C0003','Analisis de Sistemas POO') Go Create Table Instructor (Codigo Char(5) Primary key, Nombre Varchar(50), DNI Varchar(8), Curso Char(5) Constraint FK_CursoInstr Foreign Key(Curso) References Cursos(Codigo)) Go Ingresa correctamente el script y obtendrás este resultado. PULSA F5

Insert Into Instructor values ('P0001','Jhonny Pandal','10606388','C0001') Insert Into Instructor values ('P0002','Juan Salazar','52362200','C0001') Insert Into Instructor values ('P0003','Ernesto Bonifaz','11225125','C0002') Insert Into Instructor values ('P0004','Nemanja Vidic','00006388','C0002') Insert Into Instructor values ('P0005','Poul Scholes','12306388','C0002') Insert Into Instructor values ('P0006','Park Jing Sung','88559960','C0002') Insert Into Instructor values ('P0007','Rafael Da Silva','15202111','C0003') Go Select*From Cursos Select*From Instructor

LUEGOIngresar los siguientes datos a la Tabla INSTRUCTOR

Que sucede al insertar estos registros? Y como se soluciona?

P0008 Gabriel Milito 12548544 C0008 P0009 Dayana Rose 25632210 C0025





8 Generando el código de CARGO-EMPLEADO-DISTRITO Desarrollamos el script de estas 3 tablas relacionadas. Analizando que un empleado vives en un distrito y asume un cargo. TABLA: CARGO

Create Table Cargo (CodCargo Char(5) Primary key, Nombre Varchar(30) Not Null Constraint Un_Nombre Unique) Go Insert Into Cargo Values('CAR01','Vigilante') Insert Into Cargo Values('CAR02','Ventas') Insert Into Cargo Values('CAR03','Gerente') Go SELECT*FROM Cargo Go TABLA: DISTRITO Create Table Distrito (CodDis Char(5) Primary Key, Nombre Varchar(30) Not Null Constraint Un_NombreDIS Unique) GO Insert Into Distrito Values('DIS01','Chosica') Insert Into Distrito Values('DIS02','Chaclacayo') Insert Into Distrito Values('DIS03','Santa Anita') GO SELECT*FROM Distrito

Creación de Tabla Hijo EMPLEADO Create Table Empleado (CodEmp Char(5) Primary key, NomEmp Varchar(50) Not Null, Edad SmallInt Not Null Constraint Chk_Edad Check(Edad>=18 and Edad<=35), Dni Varchar(8) Not Null Constraint unq_Dni Unique, FechaC Datetime Not Null Constraint Dfl_Fecha Default(Getdate()), Email Varchar(50) Null, TlFono Varchar(15) Null, DISTRITO Char(5) Not Null Constraint FK_EmpDis Foreign Key(DISTRITO) references Distrito(CodDis), CARGO Char(5) Not Null Constraint FK_EmpCar Foreign Key(CARGO) references Cargo(CodCargo) ) GO Insert Into Empleado Values ('E01','Jessica',21,'10603522','12/02/2009','[email protected]', '449-9655','DIS02','CAR01') Insert Into Empleado Values ('E02','Carlos',35,'55663522','02/02/2004','[email protected]', '895-9655','DIS01','CAR03')





Luego debes ingresar estos registros en la tabla EMPLEADO. De tal manera que quede al final de esta manera:

Luego debes Crear el Diagrama de Datos de las 3 tablas.

9 Creación de Diagrama a) Seleccionar Diagramas de base de datos

b) Clic en Nuevo diagrama de base de datos c) Seleccionar y AGREGAR las tablas Cursos, Cargo y Empleado d) Clic en CERRAR

Para el desarrollo de este proyecto de 3 tablas se utilizaron integridad de datos para cada una de las tablas involucradas. No debes olvidar que las tablas tiene campos comunes y que tiene el mimo tipo de dato en cada CAMPO no olvidarse. Además de haber poblado de datos de forma adecuada. Ahora solo se necesita la práctica de parte tuya.





Propuesto Practico 03 Desarrollo de la base de datos UNIVERSIDAD

Desarrollo de la Base de Datos FERRARI .

Fecha de Entrega:





CAPITULO 04 Objetivos.- aprender hacer uso de las funciones de SQL server. Para obtener un mejor resultado con nuestros datos. Importante saber usar estas funciones No olvidarse

1 Funciones de cadena en SQL Server

39

2 Funciones de Fecha

40

3 Funciones Matemáticas

41

4 Funciones de Agregado

41

5 Otras Funciones Importantes

42


43





1 Funciones de cadena en SQL Server Función ASCII. - Descripción: Retorna el valor del código ASCII. - Sintaxis: ASCII (character) - Ejemplos:

Función RIGHT - Descripción: Retorna la parte derecha de una cadena con el número Específico de caracteres. - Sintaxis: RIGHT( string , integer) - Ejemplo:

SELECT ASCII('a') – Resultado = 97

SELECT RIGHT('Jhonny Pandalsoft', 6)

SELECT ASCII('b') – Resultado = 98

-- Resultado = alsoft

SELECT ASCII('c') – Resultado = 99

SELECT RIGHT('JhonnyPandal',6)

SELECT ASCII('A') – Resultado = 65

-- Resultado = Pandal

SELECT ASCII('B') – Resultado = 66 SELECT ASCII('C') – Resultado = 67 SELECT ASCII('1') – Resultado = 49 SELECT ASCII('2') – Resultado = 50 SELECT ASCII('3') – Resultado = 51 SELECT ASCII('4') – Resultado = 52 SELECT ASCII('5') – Resultado = 53

Función SPACE. _ Descripción: Retorna los espacios en SQL Query (Puedes especificar el tamaño del especio). - Sintaxis: SPACE ( integer) - Ejemplos: SELECT ('Manchester') + SPACE(10) + ('United')

Función REPLICATE - Descripción: Repite una cadena por un número específico de veces. - Sintaxis: REPLICATE (string, integer) - Ejemplo: SELECT REPLICATE('JPP', 2) -- Resultado = JPPJPP

Función SUBSTRING - Descripción: Retorna parte de una cadena. - Sintaxis: SUBSTRING ( string, startindex , length ) - Ejemplo: SELECT SUBSTRING('SQLServer', 4, 3)

-- Resultado = ManchesterUnited

-- Resultado = Ser

Función REPLACE - Descripción: Reemplaza todas las ocurrencias de la cadena 2 y la cadena 1 con la cadena 3. - Sintaxis: REPLACE ( 'string1' , 'string2' , 'string3' ) - Ejemplos:

Función LEN - Descripción: Retorna el número de caracteres de una cadena. -Sintaxis: LEN( string) - Ejemplo:

SELECT REPLACE('All Function' , 'All', 'SQL') -- Resultado = SQL Function

Función STUFF - Descripción: Elimina una longitud específica de caracteres e Inserta una cadena en un índice de inicio especifico. - Sintaxis: STUFF ( string1 , startindex , length , string2 ) - Ejemplo:

SELECT LEN('DIOSTEAMA') -- Resultado = 9

Función REVERSE - Descripción: Convierte a una cadena invertida. - Sintaxis: REVERSE( string) - Ejemplo: SELECT REVERSE('Pandal')

SELECT STUFF('SqlTutorial', 4, 6, 'Function') -- Resultado = SqlFunctional SELECT STUFF('GoodMorning', 5, 3, 'good') -- Resultado = Goodgoodning

Función LEFT - Descripción: Retorna la parte izquierda de una - cadena con el número específico de caracteres. - Sintaxis: LEFT ( string , integer) Ejemplo:

-- Resultado = ladnaP

Función UNICODE - Descripción: Retorna el valor entero del estándar unicode. - Sintaxis: UNICODE( char) - Ejemplo: SELECT UNICODE('SqlServer') -- Resultado = 83 (it take first character) SELECT UNICODE('S') -- Resultado = 83

SELECT LEFT('ManchesterCampeon', 6) -- Resultado = Manche SELECT LEFT('Megadeth',6) -- Resultado = Megade





FunciónLOWER - Descripción: Convierte una cadena a minúscula. - Sintaxis: LOWER( string ) - Ejemplo

Función LTRIM - Descripción: Retorna una cadena después de eliminar los espacios de la izquierda. - Sintaxis: LTRIM( string ) - Ejemplo:

SELECT LOWER('PandalSoft')

SELECT LTRIM(' sqlserver') -- Resultado = 'sqlserver' -- (Remove left side space or blanks)

-- Resultado =pandalsoft

Función UPPER - Descripción: Convierte una cadena a mayúscula. - Sintaxis: UPPER( string ) - Ejemplo:

Función RTRIM - Descripción: Retorna una cadena después de eliminar los espacios de la derecha. - Sintaxis: RTRIM( string ) - Ejemplo:

- SELECT UPPER('sistemasjhonny') -- Resultado = SISTEMASJHONNY

SELECT RTRIM('Prof.Pandal

')

-- Resultado = 'Prof.Pandal' -- (Remove right side space or blanks)

2 Funciones de Fecha DATEADD Devuelve un valor datetime nuevo que se basa en la suma de un intervalo a la fecha especificada. Ejemplo

SELECT DATENAME(millisecond , GETDATE())

DATEPART Devuelve un entero que representa la parte de la fecha Especificada de la fecha indicada.. Ejemplo

DECLARE @DateNow DATETIME SET @DateNow='08/10/1980' SELECT DATEADD(Year, 15, @DateNow) AS NewDate

DATEDIFF Devuelve el número de límites de fecha y hora que hay entre dos fechas especificadas.

SELECT SELECT SELECT SELECT SELECT

DATEPART(year, '2007-06-01') AS "Month" DATEPART(month, '2007-06-01') AS "Month" DATEPART(day, '2007-06-01') AS "Day" DATEPART(dayofyear, '2007-06-01') AS "Dy" DATEPART(weekday, '2007-06-01') AS "Weeday"

Ejemplos

DAY Devuelve un entero que representa la parte del día de la fecha especificada.

DECLARE @DateNow DATETIME SET @DateNow='08/10/1980'

Ejemplo

SELECT SELECT SELECT SELECT SELECT SELECT SELECT

DATEADD(Year, 15, @DateNow) AS NewDate DATEADD(Month, 3, @DateNow) AS NewDate DATEADD(dayofyear,3, @DateNow) AS NewDate DATEADD(Day, 3, @DateNow) AS NewDate DATEADD(Hour, 3, @DateNow) AS NewDate DATEADD(minute, 3, @DateNow) AS NewDate DATEADD(second, 3, @DateNow) AS NewDate

DATENAME Devuelve una cadena de caracteres que representa la parte De la fecha especificada de la fecha especificada. Ejemplos SELECT DATENAME(month, GETDATE())

SELECT DAY('02/12/2004') AS "DIA"

GETDATE Devuelve la fecha y hora actuales del sistema en el formato Interno estándar de Microsoft® SQL Server™ para los valores datetime. Ejemplo SELECT GETDATE() as "HOY ES"

MONTH Devuelve un entero que representa el mes de una fecha Especificada.

SELECT DATENAME(dayofyear, GETDATE())

Ejemplo

SELECT DATENAME(day, GETDATE())

SELECT MONTH('02/12/2004') AS "MES"

SELECT DATENAME(week, GETDATE())

YEAR Devuelve un entero que representa la parte de año de la fecha Especificada.

SELECT DATENAME(hour, GETDATE()) SELECT DATENAME(minute, GETDATE()) SELECT DATENAME(second , GETDATE())

Ejemplo SELECT YEAR('02/12/2004') AS "AÑO"





3 Funciones Matematicas RAND (Transact-SQL) Es una expresión de tipo entero (tinyint, smallint o int) que proporciona El valor de inicialización. Si no se especifica seed, SQL Server Database Engine (Motor de base de datos de SQL Server) asigna un valor de Inicialización de forma aleatoria. Para un valor de inicialización especificado, El resultado devuelto es siempre el mismo.

ACOS (Transact-SQL) Función matemática que devuelve el ángulo, en radianes, cuyo coseno es la expresión float especificada; también se denomina arco coseno.

Ejemplos En el siguiente ejemplo se producen cuatro números aleatorios diferentes, Generados con la función RAND.

DECLARE @N_COSENO float; SET @N_COSENO = -1.0; SELECT 'Coseno Es:' + CONVERT(varchar, ACOS(@N_COSENO))

Ejemplos En el ejemplo siguiente se devuelve el valor ACOS del número especificado.

DECLARE @Numero SmallInt ,@Resultado smallint; SET @Numero = 1; WHILE @Numero < 5 BEGIN Set @Resultado =(RAND()*100) SET @Numero = @Numero + 1 Print @Resultado END GO

SQRT (Transact-SQL) Devuelve la raíz cuadrada del valor de tipo flotante especificado. Ejemplos En el ejemplo siguiente se devuelve la raíz cuadrada de los números Comprendidos entre 1.00 y 10.00. DECLARE @myvalue float; SET @myvalue = 1.00; WHILE @myvalue < 10.00 BEGIN SELECT SQRT(@myvalue); SET @myvalue = @myvalue + 1 END GO

4 Funciones de Agregado Las funciones de agregado realizan un cálculo sobre un conjunto de valores y devuelven un solo valor. Si exceptuamos la función COUNT, todas las funciones de agregado ignoran los valores NULL. Las funciones de agregado se suelen utilizar con la cláusula GROUP BY de la instrucción SELECT. Las funciones de agregado sólo se pueden utilizar como expresiones en:

  

La lista de selección de una instrucción SELECT (en una subconsulta o en la consulta externa). Cláusulas COMPUTE o COMPUTE BY. Cláusulas HAVING.

Transact-SQL proporciona las siguientes funciones de agregado: NOTA: Para ejecutar los ejemplos debe crear la tabla VENDEDORES Con los siguientes registros:




RPP - Taller de Base de Datos AVG (Transact-SQL) Devuelve el promedio de los valores de un grupo. Los valores NULL Se pasan por alto. Puede ir seguida de la cláusula OVER.

COUNT (Transact-SQL) Permite contar el número de elementos de un grupo. Normalmente Usamos esta función para obtener un valor entero

Ejemplo Mostrar en promedio de ventas de los vendedores estables

Ejemplo Mostrar la mayor y menor venta de los vendedores

MIN Y MAX (Transact-SQL) Min Devuelve el valor mínimo de una expresión. Max devuelve el valor Máximo de una expresión Ejemplo Mostrar la mayor y menor venta de los vendedores

5 Otras Funciones Importantes SQL server cuenta con los siguientes tipo de funciones queharemos uso y debemos conocer con la iniciativa de investigación del estudiante.

VAR (Transact-SQL) Devuelve la varianza estadística de todos los valores de la expresión Especificada. Ejemplo Este ejemplo devuelve la varianza para todos los valores de Visitas De la tabla Vendedores

Funciones de conjuntos de filas Funciones de categoría Funciones de configuración Funciones del cursor Funciónmatemática Funciones de metadatos Funciones de seguridad Funciones del sistema Funciones estadísticas del sistema Funciones de texto e imagen Aquí algunas que deberemos usar: @@IDENTITY (Transact-SQL) Se trata de una función del sistema que devuelve el último valor de identidad insertado. @@IDENTITY ISDATE (Transact-SQL) Devuelve 1 si expression es un valor date, time o datetime válido; de lo contrario, devuelve 0. ISNULL (Transact-SQL) Reemplaza NULL con el valor de reemplazo especificado. ISNULL ( check_expression , replacement_value ) ISNUMERIC (Transact-SQL) Determina si una expresión es de tipo numérico válido. CAST y CONVERT (Transact-SQL) Convierte una expresión de un tipo de datos en otro.




RPP - Taller de Base de Datos I

Propuesto Practico 04 Según lo aprendido ahora debe resolver las siguientes consultas N-100 a)

Consulta que muestre INSTRUCTOR como resultado ROTCURTSNI

b)

Consulta que muestre los 3 últimosdígitos de la palabra MICROSOFT

c)

Consulta que muestre los 2 primero dígitos de tu nombre

d)

Consulta que duplique tu nombre 3 veces

e)

Consulta que muestre la cantidad de dígitos de tus apellidos y nombres

f)

Consulta que muestre el nombre de tu distrito en MAYUSCULA

g)

Consulta que muestre la fecha 15/03/2001 adicionándole 3 meses = 15/06/2001

h)

Consulta que muestre la fecha 15/03/2001 adicionándole 15 años = 15/06/2016

i)

Consulta que muestre el mes de 12/14/2011

j)

Consulta que muestre el promedio de visitas que tuvieron los contratados

k)

Consulta que muestre la menor visita

l)

Consulta que muestre la cantidad de estables que hayan tenido más de 30 visitas

m) Consulta que muestre a los vendedores que hayan tengan ventas superiores al promedio

Fecha de Entrega: Página 43 de 101 Docentes:

Lic. Luis M. Rojas Zúñiga Lic. Ramiro De la Cruz

Lic. Zambrano Arce, Julio Lic. Israel Huaraca


CAPITULO 05 Objetivos.- es importante saber gestionar los datos de un DB. En esta sección debemos aprender consultas que nos permitan hacer uso de los datos

1 DML, Lenguaje de Manipulación de Datos – SELECT 2 Lenguaje de manipulación de datos (I) - INSERT

45 45

3 Consultas N-100 - WARDS SUPERMARKET

47

4 Consulta de Datos I

49

5 Consultas de Actualización I - UPDATE

51

6 Consultas de Actualización II

52

7 Consultas de Actualización III

53

8 Consulta de Eliminación - DELETE

55


61





1 DML, Lenguaje de Manipulación de Datos El Lenguaje de Manipulación de Datos (DML, por sus siglas en inglés) se utiliza para obtener, insertar y modificar información de una base de datos. INSERT.- El comando INSERT en SQL es utilizado para agregar nuevos registros a una tabla existente. Tomando como ejemplo la tabla "personal", imaginemos que el departamento de recursos humanos necesita agregar un nuevo empleado a la base de datos. Se podría utilizar un comando similar al siguiente: Note que hay cuatro valores específicos para el registro. Estos corresponden a los atributos de la tabla en el orden en que fueron definidos: nombre, apellido, empleado_id y salario. Ejemplo

LUEGO Debes ingresar los registros restantes

2 Lenguaje de manipulación de datos (I) Consulta de datos. El proceso más importante que podemos llevar a cabo en una base de datos es la consulta de los datos. De nada serviría una base de datos si no pudiéramos consultarla. Es además la operación que efectuaremos con mayor frecuencia. Para consultar la información SQL pone a nuestra disposición la sentencia SELECT. La sentencia SELECT La sentencia SELECT nos permite consultar los datos almacenados en una tabla de la base de datos. El formato de la sentencia select es: Veamos por partes que quiere decir cada una de las partes que conforman la sentencia.

SELECT ALL DISTINCT FROM WHERE GROUP BY

Significado Palabra clave que indica que la sentencia de SQL que queremos ejecutar es de selección. Indica que queremos seleccionar todos los valores.Es el valor por defecto y no suele especificarse casi nunca. Indica que queremos seleccionar sólo los valores distintos. Indica la tabla (o tablas) desde la que queremos recuperar los datos. En el caso de que exista más de una tabla se denomina a la consulta "consulta combinada" o "join". En las consultas combinadas es necesario aplicar una condición de combinación a través de una cláusula WHERE. Especifica una condición que debe cumplirse para que los datos sean devueltos por la consulta. Admiten los operadores lógicos AND y OR. Especifica la agrupación que se da a los datos. Se usa siempre en combinación con funciones agregadas.




RPP - Taller de Base de Datos I HAVING

ORDER BY

Especifica una condición que debe cumplirse para los datosEspecífica una condición que debe cumplirse para que los datos sean devueltos por la consulta. Su funcionamiento es similar al de WHERE pero aplicado al conjunto de resultados devueltos por la consulta. Debe aplicarse siempre junto a GROUP BY y la condición debe estar referida a los campos contenidos en ella. Presenta el resultado ordenado por las columnas indicadas. El orden puede expresarse con ASC (orden ascendente) y DESC (orden descendente). El valor predeterminado es ASC. Ejemplo

La cláusula WHERE La cláusula WHERE es la instrucción que nos permite filtrar el resultado de una sentencia SELECT. Habitualmente no deseamos obtener toda la información existente en la tabla, sino que queremos obtener sólo la información que nos resulte útil es ese momento. La cláusula WHERE filtra los datos antes de ser devueltos por la consulta. MOSTRAR TODOS LOS REGISTROS

Select * From Empleado

FILTRAR DONDE NOMBRE TERMINE CON LETRA A

FILTRAR CAMPOS

Select * From Empleado Where Nombre LIKE '%A'

Select id_Empleado,Nombre From Empleado

FILTRAR EMPLEADOS SIN SALARIO

FILTRAR Y AGRUPAR POR NOMBRE

Select * From Empleado Where Salario is null

Select id_Empleado,Nombre From Empleado Order By Nombre

CONTAR CANT. DE EMPLEADOS

FILTRAR A LOS EMPLEADOS QUE GANEN DE 5000 A MÁS

MOSTRAR PROMEDIO DE SALARIO

Select * From Empleado Where Salario>=5000 FILTRAR A LOS EMPLEADOS QUE GANEN DE 3000 A MENOS

Select * From Empleado Where Salario<=3000 FILTRAR A LOS EMPLEADOS QUE GANEN EN UN RANGO DE 2000 A 3000

Select * From Empleado Where Salario>=2000 and Salario<=3000

Select COUNT(*) From Empleado Select AVG(Salario) From Empleado MOSTRAR EMPLEADOS CON SALARIO MAYOR AL PROMEDIO

Select * From Empleado Where Salario> (Select AVG(Salario) From Empleado) MOSTRAR MAYOR SALARIO

Select Max(Salario) AS ALTO From Empleado MOSTRAR MENOR SALARIO

FILTRAR DONDE NOMBRE INICIE CON LETRA C

Select * From Empleado Where Nombre LIKE 'C%'

Select Min(Salario) AS BAJO From Empleado





3 Consultas N-100 Para aprender a realizar estas consultas se requiere de la base de datos WardsSupermarket. La cual será facilitado por tu Instructor. Mr. Pandal Poma JHONNY Ahora debes Adjuntar o Restaurar a tu servidor y a practicar sin dudarlo =)

Diagrama de la base de datos





Estructura de Cada Tabla Tabla: CATEGORIASTabla: CLIENTES

Tabla: COMPAÑIASDEENVIOS

Tabla: DETALLESDEPEDIDOSTabla: EMPLEADOS

Tabla: PEDIDOS





Tabla: PRODUCTOS

Tabla: PROVEEDORES

4 Consulta de Datos I 01 Mostrar los productos que inicien su nombre con la letra A

Resultado:

02 Mostrar Código, Nombre y Precio de Productos. Usar ALIAS

Resultado:

03 Mostrar los productos que inicien su nombre con la letra A y C




RPP - Taller de Base de Datos I 04 Mostrar los productos donde el intervalo de precio sea de 12 a 15 soles Select IdProducto,NomProducto,PrecioUnidad From dbo.Productos Where PrecioUnidad >=12 And PrecioUnidad <=15 05 Mostrar los productos donde el precio sea de 70 soles a más Select IdProducto,NomProducto,PrecioUnidad From dbo.Productos Where PrecioUnidad >=70 06 Mostrar los productos donde el precio sea menor igual a 5 soles Select IdProducto,NomProducto,PrecioUnidad From dbo.Productos Where PrecioUnidad <=5 07 Mostrar los productos donde intervalo de precio sea de 30 a 35 soles USAR FUNCION Select IdProducto,NomProducto,PrecioUnidad From dbo.Productos Where PrecioUnidad Between 30 and 35 08 Mostrar los productos donde el proveedor tenga el código 1 Select IdProducto,NomProducto,PrecioUnidad,IdProveedor From dbo.Productos Where IdProveedor=1 09 Mostrar los productos donde el proveedor tenga el código 1 y 5 Select IdProducto,NomProducto,PrecioUnidad,IdProveedor From dbo.Productos Where IdProveedor=1 or IdProveedor=5 10 Mostrar los productos donde el proveedor tenga el código 1, 3 y 5 USAR FUNCION Select IdProducto,NomProducto,PrecioUnidad,IdProveedor From dbo.Productos Where IdProveedor IN(1,3,5) 11 Mostrar los productos activos Select NomProducto,Stock,Suspendido From dbo.Productos Where Suspendido=1 12 Mostrar los productos donde el nombre tenga en su interior la palabra ROD Select NomProducto From dbo.Productos Where NomProducto LIKE '%Rod%' 13 Mostrar el producto de mayor precio Select MAX(stock)AS MAYOR From Productos 14 Mostrar los productos de la categoría de código 7 Select NomProducto,IdCategoria AS [Codigo CAT] From Productos Where IdCategoria=7 15 Mostrar los productos que NO pertenezcan a la categoría de código 1 Select NomProducto,IdCategoria AS [Codigo CAT] From Productos Where NOT IdCategoria=1 16 Mostrar los productos que NO sean de la categoría donde el código es 1, 2, 3, 4 y 5 Select NomProducto,IdCategoria AS [Codigo CAT] From Productos Where NOT IdCategoria IN(1,2,3,4,5) 17 Mostrar los productos donde la categoría sea del código 4 y 5 Select NomProducto,IdCategoria AS [Codigo CAT] From Productos Where IdCategoria IN(4,5) 18 Mostrar los productos que tengan un stock mayor igual a 100 y su proveedor tenga el código 17 Select IdProducto,NomProducto,Stock,IdProveedor From Productos Where Stock >=100 AND IdProveedor=17 19 Mostrar el producto de mayor precio Select IdProducto,NomProducto,Stock,IdProveedor From ProductosWhere Stock Between 100 and 150 AND NomProducto LIKE 'C%'





5 Consultas de Actualizacion I El comando UPDATE puede ser utilizado para modificar información contenida dentro de una tabla. Cada año, la compañía les da a todos los empleados un incremento del 3% sobre el salario. El siguiente comando SQL podría ser utilizado para aplicar esto a todos los empleados de la base de datos:

Vamos a trabajar con la tabla CompañiasDeEnvios

Aquí Tenemos los registros de la tabla de Compañía de Envíos Los datos serán modificados según lo requiera las empresas Los datos se actualizarán por necesidad del usuario CASO 1 La empresa United Package decide cambiar de razón social por motivos económicos. Su nueva razón social será United International Ud. Deberá aplicar una consulta de actualización para realizar dicha modificación.

UPDATE dbo.CompañíasDeEnvíos SET NomCompañía='United International' Where IdCEnvíos=2 CASO 2 La empresa Wester Junior También cambia de razón social a London Union

UPDATE dbo.CompañíasDeEnvíos SET NomCompañía='London Union' Where IdCEnvíos=5 CASO 3 La Empresa Speedy Express Cambia de numero telefonico a 0808-8025-852

UPDATE dbo.CompañíasDeEnvíos SET Telefono='0808-8025-852' Where IdCEnvíos=1 CASO 4 La empresa Federal Shipping decide cambiar de razón social a Federal Peruana y de número telefónico a 330-1012

UPDATE dbo.CompañíasDeEnvíos SET NomCompañía='Federal Peruana', Telefono='330-1012' Where IdCEnvíos=3

Después de haber ejecutado los 4 casos. La tabla Compañía de Envíos tuvo ciertas modificaciones en su información haciendo Uso de Consultas de Actualización





6 Consultas de Actualizacion II Mostrar lo siguientes datos: Select IdEmpleado,Apellidos,Nombre,TelDomicilio From dbo.Empleados

Ahora vamos a modificar los datos de los empleados.

01 Modificar los apellidos y nombre del Empleado donde su código es 1 UPDATE dbo.Empleados SET Apellidos='Pandal', Nombre='Jhonny' WHERE IdEmpleado=1 02 Modificar los 3 campos del empleado donde su código es 2 UPDATE dbo.Empleados SET Apellidos='Gates', Nombre='Bill', TelDomicilio='985-965-845' WHERE IdEmpleado=2 03 Modificar fecha de contratación del empleado Jhonny Pandal UPDATE dbo.Empleados SET FechaContratacion='12/03/2006' WHERE IdEmpleado=1

04 Mostrar código, nombres y teléfono de empleados. Agrupado por el segundo campo calculado Select IdEmpleado,Nombre+SPACE(3)+Apellidos AS [Nombres y Apellidos],FechaContratacion,TelDomicilio From dbo.Empleados 05 Mostrar código, nombres, fecha contratación, teléfono de empleados. Agrupado por el segundo campo calculado Select IdEmpleado,Nombre+SPACE(3)+Apellidos AS [Nombres y Apellidos],TelDomicilio From dbo.Empleados Order By 2

Cada vez que ejecutes una consulta verifica que los datos realmente se hayan actualizado. UPDATE nos permite modificar registros existentes. NO LO OLVIDES





Consultas de Actualizacion III En la seccion anterior aprendimos a actualizar solo un registro a la vez. Pero en ocasiones se deben hacer modificaciones a un grupo de registros. Es facil solo deberas usar lo aprendido anteriormente. Primero: Mostramos los 5 primeros productos(codigo,nombre,precio y categoria) de la categoria 1 Select TOP 5 IdProducto,NomProducto,PrecioUnidad,IdCategoria From where IdCategoria=1

dbo.Productos

Segundo: La empresa Wards Supermarket decide subir los precios de los productos que pertenezcan a la categoria1. Este incremente será del 50% del mismo. UPDATE dbo.Productos SET PrecioUnidad=PrecioUnidad+(PrecioUnidad*0.50) WHERE IdCategoria =1 El resultado si te das cuenta sera 12 filas afectadas. Ya que hay 12 productos de la categoria1 que fueron modificadas en grupo. Verifica los cambios

CASO 5 La empresa decide reducir el precio de los productos a un 50% pero que pertenezcan a la categoría de condimentos (2) y que tengan un stock mayor igual a 50 unidades en almacén. Mostrando datos Antes:

Select IdProducto,NomProducto,PrecioUnidad,Stock,IdCategoria From dbo.Productos where IdCategoria=2 AND Stock >=50





Vamos a actualizar los datos:

UPDATE dbo.Productos SET PrecioUnidad=PrecioUnidad-(PrecioUnidad*0.50) WHERE IdCategoria =2 AND Stock >=50

Como pueden ver los datos actualizados cumplen el criterio detallado. CASO 6

La empresa decide poner un precio de 15 soles a todas las marcas de las cervezas que vende. Debes modificar dichos precios. Mostrando datos Antes:

Select IdProducto,NomProducto,PrecioUnidad,Stock,IdCategoria From where NomProducto LIKE 'Cerveza%'

dbo.Productos

Vamos a actualizar los datos:

UPDATE dbo.Productos SET PrecioUnidad=15 WHERE NomProducto LIKE 'Cerveza%'

Una consulta de actualización depende de los criterios que deben de cumplir dicha modificación. Es importante tener en cuenta que para poder modificar registros primero debes tener un respaldo. Pues los datos modificados no pueden ser restaurados. Una sentencia que NO DEBES EJECUTAR NUNCA

UPDATE dbo.Productos SET NomProducto='Impresora Canon'





8 Consulta de Eliminación Utilizado para eliminar datos dentro de una tabla. Desafortunadamente, los últimos ingresos de la corporación reportan disminución en el desempeño, esto ha causado que Juan Pérez sea despedido. El comando DELETE con la cláusula WHERE puede ser utilizado para eliminar su registro de la tabla de personal:

01 la empresa decide eliminar de su data todos los productos suspendidos Select * From dbo.Productos Where Suspendido=0 02 la empresa decide eliminar de su data los productos acabados Delete From dbo.Productos Where Stock=0 03 la empresa decide eliminar de su data todos los productos en pedido menores igual a 40 que pertenezcan A la categoría 1 Delete From dbo.Productos Where CantEnPedido <=40 AND IdCategoria=1 04 la que no debes USAR Delete From dbo.Productos

Una empresa real cuenta con una tabla denominada Historial_Productos en esta tabla se almacena los registros eliminados de la tabla principal Productos. Además de los registros de los productos antes de ser actualizados con update

Consulta de Datos II Consulta a Dos Tablas 01.- Mostrar codigo y nombre del producto. ademas el pais y telefono de los proveedores de dichos productos Select P.IdProducto,P.NomProducto,PR.Pais,PR.Telefono From Productos AS P INNER JOIN Proveedores AS PR ON P.IdProveedor =PR.IdProveedor Resultado





02.- Mostrar codigo y nombre del producto. ademas el nombre de la empresa y el nombre del empleado de la empresa proveedora. Ademas configurar el ancabezado con ALIAS Select P.IdProducto AS [COD PRODUCTO],P.NomProducto AS [NOMBRE DEL PRODUCTO], PR.NomEmpresa AS [EMPRESA],PR.NomEmpleado AS [NOMBRE DE EMPLEADO] From Productos AS P INNER JOIN Proveedores AS PR ON P.IdProveedor =PR.IdProveedor Resultado:

DESCRIPCION: a) b) c)

Una consulta multitablas requiere de los campos de cada tabla en la sección SELECT. Luego la unión de las tablas que intervienen en dicha consultas. Las cuales están unidas con la unión INNER JOIN estas tablas deben estas relacionadas por un campo común. La instrucción ON en la cual comparamos los dos campos comunes de dichas tablas.

03.- Además podemos filtrar este resultado acompañando a esta consulta con la instrucción WHERE Select P.IdProducto AS [COD PRODUCTO],P.NomProducto AS [NOMBRE DEL PRODUCTO], PR.NomEmpresa AS [EMPRESA],PR.NomEmpleado AS [NOMBRE DE EMPLEADO] From Productos AS P INNER JOIN Proveedores AS PR ON P.IdProveedor =PR.IdProveedor Where PR.NomEmpleado ='Wendy Mackenzie' Resultado:

WHERE 04.- Mostrar El nombre de la categoria. ademas nombre y precio del producto de dicha categoria Select C.NomCategoria,PD.NomProducto,PD.PrecioUnidad From Categorias AS C INNER JOIN Productos AS PD ON C.IdCategoria =PD.IdCategoria Resultado:




RPP - Taller de Base de Datos I 05.- Mostrar El nombre de la categoria. ademas nombre y precio del producto de dicha categoria. Pero solo de las bebidas Select C.NomCategoria,PD.NomProducto,PD.PrecioUnidad From Categorias AS C INNER JOIN Productos AS PD ON C.IdCategoria =PD.IdCategoria Where C.NomCategoria ='Bebidas' Resultado:

06.- Mostrar los detalles del pèdido 10250. mostrando: codigo y nombre del producto en pedido. ademas de precio unitario y la cantidad a comprar de dicho producto.ademas nos piden que mostremos un campo CALCULADO que es SUBTOTAL que se genera a partir de la multiplicacion de 2 campos Select DT.IdProducto,P.NomProducto,DT.PrecioUnidad,DT.Cantidad, (DT.PrecioUnidad*DT.Cantidad) AS SUBTOTAL From DetallesDePedidos AS DT INNER JOIN Productos AS P ON DT.IdProducto =P.IdProducto Where DT.IdPedido ='10250' Resultado:

06.- Mostrar los detalles del pèdido 10258. mostrando: codigo y nombre del producto en pedido. ademas de precio unitario y la cantidad a comprar de dicho producto.ademas nos piden que mostremos un campo CALCULADO que es SUBTOTAL,DESCUENTO Y NETO haciendo uso de 2 campos calculados Select DT.IdProducto,P.NomProducto,DT.PrecioUnidad,DT.Cantidad, (DT.PrecioUnidad*DT.Cantidad) AS SUBTOTAL, (DT.PrecioUnidad*DT.Cantidad)*DT.Descuento AS DESCUENTO, ((DT.PrecioUnidad*DT.Cantidad)-(DT.PrecioUnidad*DT.Cantidad)*DT.Descuento) AS NETO

From DetallesDePedidos AS DT INNER JOIN Productos AS P ON DT.IdProducto =P.IdProducto Where DT.IdPedido ='10258' Resultado:

Para este ejemplo hemos usado 3 campos de la tabla DetallesDePedidos, 1 Campo de la tabla Productos y 3 campos calculados (SUBTOTAL, DESCUENTO, NETO) haciendo uso de la multiplicación de PrecioUnidad*Cantidad




RPP - Taller de Base de Datos I 07.- Mostrar la cantidad de productos vendidos. Clasificado por el nombre de la categoría. para obtener este resultado debemos hacer el recorrido desde la tabla detallesdepedido hacia la tabla productos y de la tabla productos hacia la tabla categorías y así obtener los datos para los campos seleccionados Select C.NomCategoria AS CATEGORIA,COUNT(DT.IdProducto) AS CANTIDAD From DetallesDePedidos AS DT INNER JOIN Productos AS P ON DT.IdProducto = P.IdProducto INNER JOIN Categorias AS C ON C.IdCategoria =P.IdCategoria Group By C.NomCategoria Resultado:

08.- Mostrar la cantidad de productos vendidos. Clasificado por el nombre del proveedor. Para obtener este resultado debemos hacer el recorrido desde la tabla detallesdepedido hacia la tabla productos y de la tabla productos hacia la tabla proveddores y así obtener los datos para los campos seleccionados Select PR.NomEmpresa AS CATEGORIA,COUNT(DT.IdProducto) AS CANTIDAD From DetallesDePedidos AS DT INNER JOIN Productos AS P ON DT.IdProducto = P.IdProducto INNER JOIN Proveedores AS PR ON PR.IdProveedor =P.IdProveedor Group By PR.NomEmpresa Resultado:




RPP - Taller de Base de Datos I 09.- Mostrar código y nombre del producto. Además de el país y teléfono de dichos productos. Se debe hacer una búsqueda por digito del lado izquierdo SELECT P.IdProducto,P.NomProducto,PR.Pais,PR.Telefono FROM Proveedores AS PR INNER JOIN Productos P ON PR.IdProveedor = P.IdProveedor WHERE P.NomProducto LIKE 'Ca%' Resultado:

Analizando La Consulta

10.- Mostrar Cantidad de Productos Pedidos Por Categoría Select C.NomCategoria AS CATEGORIA,COUNT(DT.IdProducto) AS CANTIDAD From DetallesDePedidos AS DT INNER JOIN Productos AS P ON DT.IdProducto = P.IdProducto INNER JOIN Categorias AS C ON C.IdCategoria =P.IdCategoria Group By C.NomCategoria 11.- Mostrar Cantidad de Productos Pedidos Por Proveedor Select PR.NomEmpresa AS PROVEEDOR,COUNT(DT.IdProducto) AS CANTIDAD From DetallesDePedidos AS DT INNER JOIN Productos AS P ON DT.IdProducto = P.IdProducto INNER JOIN Proveedores AS PR ON PR.IdProveedor =P.IdProveedor Group By PR.NomEmpresa 12.- Mostrar código y nombre de producto. Además del país y teléfono del proveedor. En nombre de producto debe iniciar con las letras CA SELECT P.IdProducto,P.NomProducto,PR.Pais,PR.Telefono FROM Proveedores AS PR INNER JOIN Productos P ON PR.IdProveedor = P.IdProveedor WHERE P.NomProducto LIKE 'Ca%'




RPP - Taller de Base de Datos I 13.- Mostrar todos los pedidos de la empresa Select*From Pedidos 14.- Mostrar el país hacia donde es su destino de todos los pedidos Select PaisDest From Pedidos 15.- Mostrar la cantidad de países que son clientes Select COUNT(Distinct(PaisDest))

FROM Pedidos

16.- Mostrar el nombre de los países clientes. El nombre de debe repetirse Select Distinct(PaisDest) FROM Pedidos 17.- Mostrar código, apellido, nombre y edad(campo calculado) de los empleados Select IdEmpleado,Apellidos,Nombre,(YEAR(GETDATE())-YEAR(FechaNacimiento)) AS EDAD From Empleados 18.- Mostrar código, apellido, nombre y edad de los empleados a partir de los 50 años de edad Select IdEmpleado,Apellidos,Nombre,(YEAR(GETDATE())-YEAR(FechaNacimiento)) AS EDA From Empleados Where(YEAR(GETDATE())-YEAR(FechaNacimiento))>=50 19.- Mostrar el 25% de los empleados de la empresa Select Top 25 Percent * From Empleados 20.- Mostrar empleados donde su edad comprenda del 15/01/1950 hasta 15/01/1960 Select*From EmpleadosWhere FechaNacimiento BETWEEN '15/01/1950' AND '15/01/1960' 21.- Mostrar pedidos del 01/09/1997 Al 31/12/1997 que se realizo dicho pedido Select IdPedido,FecPedido,FecEntrega,FecEnvío From Pedidos Where FecPedido BETWEEN '01/09/1997' AND '31/12/1997' 22.- Mostrar la cantidad de pedidos realizados del 01/09/1997 al 31/12/1997 Select COUNT(IdPedido) CANTIDAD From Pedidos Where FecPedido BETWEEN '01/09/1997' AND '31/12/1997' 23.- Mostrar ciudad y región de francia Select Region,Ciudad From Proveedores Where Pais='Francia' 24.- Mostrar código y fecha de entrega. Además del nombre de la compañía que los enviara. Solo debe mostrar los envíos de la compañía Speedy Express y la fecha de entrega debe ser a partir de la fecha indicada 01/06/1998 Select P.IdPedido,P.FecEntrega,CE.NomCompañía From CompañíasDeEnvíos AS CE INNER JOIN Pedidos AS P On CE.IdCEnvíos =P.IdCompEnvío Where CE.NomCompañía ='Speedy Express' AND P.FecEntrega >='01/06/1998'





Propuesto Practico 05 1. mostrar la cantidad de categorias existentes(numero entero) 2. mostrar el nombre de la categoria con el encabezado 'nombre de categoria' 3. mostrar nombre de empresa y nombre de empleado de la tabla clientes. donde el nombre de la empresa inicie con la letra a y c 4. mostrar la cantidad de clientes de la ciudad de mexico 5. mostra a los clientes que no tienen fax 6. mostrar codigo de pedido,nombre de producto, precio, cantidad de la tabla detalles de pedido ademas de los campos calculados subtotal, descuento 10% y pago neto 7. mostrar el promedio de precios de la tabla detalles de pedido 8. mostrar los detalles de pedido donde la cantidad este entre 50 a 100 y el porcentaje del descuento sea superior a 20% 9. mostrar codigo, nombre y apellido de los empleados. ademas el tiempo de servicio que tiene cada empleado 10. mostra la cantidad de empleados por tratamiento. ej. cuantas señoras,señores, doctores existen. etc. 11. mostrar el nombre y apellido de los empleados en una sola columna de titulo 'datos' 12. mostrar los empleados donde su edad este entre 50 a 60. usar funciones 13. mostra los empleados que no tengan jefe 14. mostrar el promedio del monto de la tabla pedidos 15. mostrar los pedidos donde el monto sea mayor la promedio 16. mostrar el mes de la fecha de pedido,el año de la fecha de entrega y el dia de la fecha de envio. de la tabla pedidos. usar funciones 17. mostrar la cantidad de pedidos que realizo cada cliente 18. mostrar los productos donde el stock este en el intervalo de 40 a 50 19. mostrar los productos de la categoria sea 1 y 3 20. mostrar los productos donde el nombre inicie con a y el precio sea superior a 50 21. mostrar la cantidad de proveedores por cargo de contacto 22. mostrar cargo de contacto de la tabla proveedores. no deben repetirse los datos 23. mostrar la cantidad de proveedores por pais 24. mostrar codigo, nombre y precio de producto. ademas de nombre y descripcion de su categoria 25. mostrar los productos donde el nombre de la categoria sea bebidas 26. mostrar precio, stock y cantidad por unidad de la tabla productos donde el cargo de contacto del proveedor sea 'representante de ventas'




RPP - Taller de Base de Datos I 27. mostrar los clientes donde la region inicia con la letra a y c 28. mostrar codigo, ciudad y pais de la tabla pedidos.ademas el campopais de la tabla clientes donde el cliente sea del pais de alemania 29. mostrar codigo y nombre del producto donde el precio sea mayor igual a 15 y el stock sea mayor a 16 30. mostrar los productos activos 31. mostrar los proveedores que no pertenescan a españa ni japon 32. mostrar los clientes de la ciudad de madrid y berlin 33. mostrar la cantidad de pedidos que realizo cada empleado 34. incrementar el preciounidad de los productos a un 75% de la categoria 1 y 2 35. cambiar el nombre de cervezatibetana barley por cerveza pilsen del producto de codigo 2 36. cambiar apellido y nombre de buchana steven por carlos tevez del empleado de codigo 5 37. asignarle region lima a los empleados que no tengan region 38. la extension sera 088 para los empleados de eeuu 39. modificar la fecha de envio a 08/10/2011 de los pedidos que van hacia belgica 40. reducir el stock menos 3 de los productos de la categoria 5 41. insertar 3 registros a la tabla empleados 42. insertar 15 registros a tabla productos 43. insertar 3 registros a la tabla pedidos asociado con detalles 44. mostrar producto de la categoria 1 donde el precio unidad sea superior a 26 y cantidad en pedido sea 0 45. mostrar los productos donde la cantidad por unidad sean FRASCOS 46. mostrar los productos donde la categoria sea 2 y 3 ademas esten suspendido 47. mostrar los productos que no tengan stock 48. mostrar los pedidos donde el monto sea menor al promedio y el pais sea mexico 49. mostrar los empleados que no tengan ningun pedido 50. eliminar los productos de Peru

Fecha de Entrega:

Un conocimiento adecuado de las consultas sobre las tablas de una base de datos y saber qué información desea uno obtener de dichas consultas es una parte fundamental para poder realizar los siguientes capítulos NO OLVIDARLO practica es lo que hace al maestro.





CAPITULO 06 Objetivos.- aprender a aplicarle rendimientos a nuestras tablas. Cuando estas tengan un gran cantidad de registros. Recuerda que la lentitud es un factor negativo en sistemas de la inf.

1 Optimisando la base de datos con Indices

64

2 CREATE INDEX

65

3 Índices en un Mundo Real – 100.000

66

4 Agregando Índices en la Tabla Usuarios

68

5 Creando Índices en Wards Supermarket

69

6 Seguridad y Rendimiento con Vistas

70

7 Creación de Sinónimos

71


71





1 Optimisando la base de datos con Indices Introducción a los índices.Un índice es una estructura de datos que permite acceder a diferentes filas de una misma tabla a través de un campo (o campos clave). Un índice permite un acceso mucho más rápido a los datos. Para entender lo que es un índice debemos saber primero como se almacena la información internamente en las tablas de una base de datos. Cada tabla se divide en páginas de datos, imaginemos un libro, podríamos escribirlo en "una sola hoja enorme" al estilo pergamino egipcio, o bien en páginas a las que podemos acceder rápidamente a través de un índice. Está idea es la que se aplica en el mundo de las bases de datos, la información esta guardada en una tabla (el libro) que tiene muchas hojas de datos (las páginas del libro), con un índice en el que podemos buscar la información que nos interesa. Los índices se actualizan automáticamente cuando realizamos operaciones de escritura en la base de datos. Este es un aspecto muy importante de cara al rendimiento de las operaciones de escritura, ya que además de escribir los datos en la tabla se escribirán también en el índice. Un número elevado de índices hará más lentas estas operaciones. Sin embargo, salvo casos excepcionales, el beneficio que aportan los índices compensa (de largo) esta penalización. Las sentencias de SQL para manipular índices son: CREATE INDEX Y DROP INDEX

La palabra clave UNIQUE específica que no pueden existir claves duplicadas en el índice. ASC | DESC especifican el criterio de ordenación elegido, ascendente o descendente, por defecto es ascendente. Ejemplo En el apartado dedicado a la definición de tablas usaremos la tabla Clientes de la base de datos Wards Supermarket, este ejemplo crea un índice único en el campo NOMBREEMPLEADO. Esto nos permitirá buscar mucho más rápido por el campo NOMBREEMPLEADO y nos asegurará que no tengamos dos NOMBREEMPLEADO iguales.

Cada tabla tiene unas carpetas índices. Ahí se guardan estos objetos

¿Cómo Eliminar Índices? DROP INDEX Clientes.UIX_Clientes_NombreEmpleado





2 CREATE INDEX La sentencia CREATE INDEX sirve para crear un índice sobre una o varias columnas de una tabla. ASC: la cláusula ASC es la que se asume por defecto e indica que el orden elegido para el índice es ascendente (en orden alfabético si la columna es de tipo texto, de menor a mayor si es de tipo numérico, en orden cronológico si es de tipo fecha). DESC: indica orden descendente, es decir el orden inverso al ascendente.

Ejemplo: Crea un índice llamado ind1 sobre la tabla empleados formado por las columnas ciudad, región y fecha de nacimiento. Este índice permite tener ordenadas las filas de la tabla empleados de forma que aparezcan los empleados ordenados por ciudad, dentro de la misma ciudad por región y dentro de la misma región por edad y del más joven al más mayor. CREATE INDEX ind1 ON Empleados (Ciudad, Region ASC, FechaNacimiento DESC) Create Index Ux_Cargo_Emp ON Clientes(CargoEmpleado) Create Index Ux_Nom_Cat ON Categorias(NomCategoria)

VELOCIDAD DE DATOS

Create Unique Index Ux_Nom_Cat_Unico ON Categorias(NomCategoria) Create NONCLUSTERED Index Ux_Nombre_Compañia ON CompañíasDeEnvíos(NomCompañía) Create CLUSTERED Index Ux_telefono_Compañia ON CompañíasDeEnvíos(Telefono) Create NONCLUSTERED Index Ux_Pedido_Fechas ON Pedidos(FecPedido,FecEntrega,FecEnvío)

Los INDICES se crean dentro de una tabla cuando el volumen de datos aumenta y es necesario un mecanismo de búsqueda de datos eficiente.

Verificar la existencia de los indices en la Carpeta indice de cada tabla.

DROP INDEX La sentencia DROP INDEX sirve para eliminar un índice de una tabla. Se elimina el índice pero no las columnas que lo forman. La sintaxis es la siguiente:

Ejemplo: DROP INDEX Empleados.Ux_Nom_Cat_Unico Elimina el índice que habíamos creado en el ejemplo anterior.

DESACTIVAR Y ACTIVAR INDICES Hay momentos en donde se debe ingresar información en lotes dentro de una tabla. Eso implica miles de registros para lo cual debemos desactivar los indices Página 65 de 101 Docentes:




3 Índices en un Mundo Real – 100.000 Primero debemos entender que los índices aunque perjudican un poco el ingreso de datos dentro de una tabla. La función más importante es la optimización que nos brinda al momento de hacer consultas. Porque todo sistema realiza muchas búsquedas en una base de datos. ¿Y cómo se mide la velocidad de respuesta de una consulta? Pues cuando tenemos una gran cantidad de registros.

Ejercicio 01 A.- Vamos a crear una tabla de nombre un usuario y le vamos a agregar 100.000 registros de una sola ejecución con la ayuda del procedimiento almacenado

dbo.Usp_CrearUsuarios

El cual me permite realizar el llenado de dichos registros. Bueno comencemos Crear a Tabla Usuario

B.- Ahora desde el Analizador de consultas ejecutas el procedimiento almacenado dbo.Usp_CrearUsuarios que se encuentra en tu DB Wards Supermarket.

dbo.Usp_CrearUsuarios 100000, 10000, 600 Una vez ejecutado esta línea de script te recomiendo vayas a tomarte un cafecito o un helado de vainilla con choco chip (depende como te sientas). Porque la creación de los registros demorara y eso depende del hardware que tiene Nota: como veras en la imagen se demoró 52 segundos en el llenado de datos. Esperemos superar ese tiempo en tu computador. EL TIEMPO ES UN FACTOR IMPORTANTE EN LOS SISTEMAS DE INFORMACION NO OLVIDARLO. Mensaje exitoso

C.-




RPP - Taller de Base de Datos I Verificar los registros ingresados Select * From Usuario

Sin lugar a dudas, el desempeño de una aplicación está directamente relacionado al buen o mal diseño de los índices de la base de datos. Esta tabla no tiene ningún índice creado, por lo cual SQL Server tratará la tabla como un HEAP. Un heap es una estructura de datos que almacena la posición física en la que se almacenó cada nueva fila dentro de las páginas asignadas a la tabla. Puesto que esta tabla no tiene ningún tipo de índice, es bastante eficiente para agregar nuevas filas a la tabla pero muy ineficiente para encontrar una fila específica, esto se debe a que es necesario leer toda la tabla para obtener el resultado deseado. Para ilustrar esto, realicemos el siguiente experimento: utilizando el procedimiento almacenado Usp_CrearUsuarioscrearemos 100.000 usuarios de los cuales el 10.000 no están activos y su fecha de creación esta en los últimos 600 días. D.- Ahora realicemos

una consulta para validar el usuario al inicio de sesión del sistema.

SELECT Clave, Activo FROM Usuario WHERE NombreUsuario = 'PANDAL123' La respuesta probablemente funcione bastante rápido, ya que al recién haber creado los datos, todas estas filas están en memoria, pero veamos el plan de ejecución de esta consulta presionando el botón incluir plan de ejecución real. De la barra de herramientas del SQL Server Management Studio y luego ejecutando la misma consulta.





4 Agregando Índices en la Tabla Usuarios Vuelvo a ejecutar: SELECT Clave, Activo FROM Usuario WHERE NombreUsuario = 'PANDAL123' Y veo el plan de ejecución

ANTES DEL INDICE

Ahora creare un indice Create Index IDX_NombreUsuario ON Usuario(NombreUsuario) Luego volvere a ejecutar SELECT Clave, Activo FROM Usuario WHERE NombreUsuario = 'PANDAL123' Y Veo el plan de ejecucion

DESPUES DEL INDICE





5 Creando Índices en Wards Supermarket Sobre tabla productos:

EXEC Sp_HelpIndex Productos Muestra los indices de la tabla productos EXEC sp_SpaceUsed Productos Muestra el espacio que ocupa la tabla productos

Ejemplos

A. Crear un índice no clúster sencillo El ejemplo siguiente crea un índice no clúster en la columna NomProducto y Precio de la tabla Productos IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_ProductoNomPrecio') DROP INDEX Productos.IX_ProductoNomPrecio GO CREATE INDEX IX_ProductoNomPrecio ON Productos(NomProducto,PrecioUnidad) GO

B. Crear un índice compuesto no clúster sencillo El ejemplo siguiente crea un índice compuesto no agrupado en las columnas Stock y Cant en Pedido de la tabla Productos IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_Stock_CantPed') DROP INDEX Productos.IX_Stock_CantPed GO CREATE NONCLUSTERED INDEX IX_Stock_CantPed ON Productos (Stock,CantEnPedido) GO

C. Crear un índice no clúster único El ejemplo siguiente crea un índice no clúster único en la columna Name de la tabla Production.UnitMeasure. El índice exigirá unicidad en los datos insertados en la columna Name. IF EXISTS (SELECT name from sys.indexes WHERE name = N'UniNomProducto') DROP INDEX Productos.UniNomProducto GO CREATE UNIQUE INDEX UniNomProducto ON Productos(NomProducto) GO





6 Seguridad y Rendimiento con Vistas En el modelo de datos relacional la forma de guardar la información no es la mejor para ver los datos Una vista es una consulta, que refleja el contenido de una o más tablas, desde la que se puede acceder a los datos como si fuera una tabla. Dos son las principales razones por las que podemos crear vistas. 

Seguridad, nos pueden interesar que los usuarios tengan acceso a una parte de la información que hay en una tabla, pero no a toda la tabla. Comodidad, como hemos dicho el modelo relacional no es el más cómodo para visualizar los datos, lo que nos puede llevar a tener que escribir complejas sentencias SQL, tener una vista nos simplifica esta tarea.



Las vistas no tienen una copia física de los datos, son consultas a los datos que hay en las tablas, por lo que si actualizamos los datos de una vista, estamos actualizando realmente la tabla, y si actualizamos la tabla estos cambios serán visibles desde la vista.

Creación de vistas. Para crear una vista debemos utilizar la sentencia CREATE VIEW, debiendo proporcionar un nombre a la vista y una sentencia SQL SELECT válida. CREATE VIEW AS ();

Ejemplo 01 Crear una vista sobre nuestra tabla Empleados, en la que se nos muestre el nombre y apellidos del empleado en lugar de su código. CREATE VIEW vEmpleados AS (SELECT nombre,apellidos FROM Empleados) GO Mostrando el contenido de la Select*From vEmpleados Modificar una Vista Existente. ALTER VIEW vEmpleados AS (SELECT nombre,apellidos,Pais FROM Empleados WHERE Pais='EE.UU.') GO Mostrando el contenido de la Select*From vEmpleados

Por último podemos eliminar la vista a través de la sentencia DROP VIEW. Para eliminar la vista que hemos creado anteriormente se utilizaría: DROP VIEWvEmpleados Una vista se consulta como si fuese una tabla.





7 Creación de Sinónimos Un sinónimo es un nombre alternativo que identifica una tabla en la base de datos. Con un sinónimo se pretende normalmente simplicar el nombre original de la tabla, aunque también se suelen utilizar para evitar tener que escribir el nombre del propietario de la tabla. No todas las bases de datos soportan los sinónimos. Para crear un sinónimo hay que utilizar la sentencia CREATE SYNONYM especificando el nombre que deseamos utilizar como sinónimo y la tabla para la que estamos creando el sinónimo.

CREATE SYNONYM FOR;

Ejemplo 01El siguiente ejemplo crea el sinónimo Coches para la tabla tCoches. CREATE SYNONYM MisEmpleados FOR Empleados ejecutar Select * From MisEmpleados Para eliminar el sinónimo creado debemos emplear la sentencia DROP SYNONYM. DROP SYNONYM MisEmpleados

Propuesto Practico 06 Crear una Base de datos de 3 tablas AGENCIA DE VIAJES con Índices Crear 3 Índices a las tablas (Clientes, Empleados, Pedidos, Productos) de Ward’s Super... Crear 5 Vistas Simples Crear 5 Vistas con Consultas de más de 1 tabla Crear 2 Vistas Con Tablas Particionada Crear Sinónimos para cada tabla





CAPITULO 07 Objetivos.- aprender a administrar la información de una base de datos haciendo uso de objetos los cuales no permiten una programación por lado del servidor. Ademas de generar copias de seguridad

1 Funciones Creadas Por el Usuario

73

2 Funciones Escalares.-

73

3 Funciones de Tabla En Línea

74

4 Procedimientos Almacenados

75

5 Procedimientos Almacenados Con Parámetros

76

6 Procedimientos almacenados con Parámetros de SALIDA

79

7 Procedimientos Almacenados de Mantenimiento

79

8 Desencadenadores o TRIGGERS

83

9 Activar o Desactivar USP

86

10 Copia de Seguridad

86

11 Restaurar una Copia de Seguridad

89

12 Propuesto

91





1 Funciones Creadas Por el Usuario Tipos de funciones.- El servidor 2008 del SQL utiliza tres tipos de funciones: las funciones escalares, tabla en línea, funciones de tabla de multi-sentencias. Los tres tipos de funciones aceptan parámetros de cualquier tipo excepto el rowversion. Las funciones escalares devuelven un solo valor, tabla en línea y Multisentencias devuelven un tipo de dato tabla.

2 Funciones Escalares.Las funciones escalares vuelven un tipo de los datos tal como int, money, varchar, real, etc. Pueden ser utilizadas en cualquier lugar incluso incorporado dentro de sentencias SQL. La sintaxis para una función escalar es la siguiente: Ejemplo 01 CREATE FUNCTION Ufn_VolumenCubico (@CuboLargo decimal(4,1), @CuboAncho decimal(4,1), @CuboAlto decimal(4,1)) RETURNS decimal(12,3) WITH SCHEMABINDING AS BEGIN RETURN (@CuboLargo * @CuboAncho * @CuboAlto) END GO Ejecutar la funcion DECLARE @MyResultado decimal(12,3) EXEC @MyResultado = Ufn_VolumenCubico @CuboLargo = 12.3, @CuboAlto = 4.5, @CuboAncho = 4.5 SELECT @MyResultado GO Ejemplo 02 CREATE FUNCTION Ufn_Promedio (@Nota1 Int, @Nota2 Int, @Nota3 Int) RETURNS Int WITH SCHEMABINDING AS BEGIN RETURN ( @Nota1 + @Nota2 + @Nota3 )/3 END GO Ejecutar la funcion DECLARE @MyPromedio Int EXEC @MyPromedio = Ufn_Promedio @Nota1

= 12.3, @Nota2

= 11, @Nota3

= 17

SELECT @MyPromedio GO





3 Funciones de Tabla En Línea Las funciones de tabla en línea son las funciones que devuelven la salida de una simple declaración SELECT. La salida se puede utilizar adentro de joins o querys como si fuera un tabla de estándar. La sintaxis para una función de tabla en línea es como sigue: Ejemplo 01 Esta función nos permite mostrar los empleados por país. Esta función tiene un parámetro de entrada que se encarga de ingresar el nombre del país CREATE FUNCTION Fn_EmpleadosXPais(@Pais NVarchar(15)) RETURNS TABLE AS RETURN (SELECT * FROM Empleados WHERE Pais=@Pais) GO Ejecutar Funcion 1 Select * From Fn_EmpleadosXPais('EE.UU.') Ejecutar Funcion 2 Select * From Fn_EmpleadosXPais('Reino Unido') Ejemplo 02 Nos permite mostrar todos los empleados CREATE FUNCTION Fn_Empleados() RETURNS TABLE AS RETURN (SELECT * FROM Empleados) GO Ejecutar Funcion Select * From Fn_Empleados() Ejemplo 02 Nos permite mostrar todos los productos donde el precio sea superior al valor ingresado al parametro @Precio CREATE FUNCTION Fn_ProductoMayorPrecio(@Precio Money) RETURNS TABLE AS RETURN (SELECT * FROM Productos WHERE PrecioUnidad>=@Precio) GO Ejecutar Funcion Select * From Fn_ProductoMayorPrecio(25) Ejemplos 03 Funcion para Mostrar los detalles de un pedido. Ingresnado el numero del pedido. Tambien emutra campos calculados CREATE FUNCTION Fn_DetallesXPeido(@CodPedido Int) RETURNs TABLE AS RETURN (Select IdProducto,PrecioUnidad,Cantidad,(PrecioUnidad*Cantidad )AS [Subtotal], (Descuento*(PrecioUnidad*Cantidad)) AS Descuento, ((PrecioUnidad*Cantidad )- (Descuento*(PrecioUnidad*Cantidad))) AS NETO From [Detallesdepedidos] Where idpedido=@CodPedido) GO Ejecutar Funcion Select * From Fn_DetallesXPeido(10255)




RPP - Taller de Base de Datos I Ejemplos 04 Funcion para Mostrar la fecha, nombre de cliente y pais adonde fue entregado dicho pedido. la funcion necesita que ingreses valores en los parametro de pais y nombre de cliente. CREATE FUNCTION Fn_BuscarPedidos01 (@NomCli nVarchar(40),@Pais nVarchar(15)) RETURNS TABLE AS RETURN(Select P.IdPedido,C.NombreEmpleado,P.FecPedido,P.PaisDest From Pedidos As P INNER JOIN Clientes AS C ON P.IdCliente =C.IdCliente Where C.NombreEmpleado LIKE @NomCli+'%' AND P.PaisDest=@Pais) GO Ejecutar Funcion 01 Use [Wards Supermarket] GO Select * From Fn_BuscarPedidos01('H','Alemania') Ejecutar Funcion 02 Use [Wards Supermarket] GO Select * From Fn_BuscarPedidos01('A','Alemania') Ejecutar Funcion 03 Use [Wards Supermarket] GO Select * From Fn_BuscarPedidos01('Y','Suiza') Ejecutar Funcion 04 Select COUNT(*) From Fn_BuscarPedidos01('Y','Suiza')

4 Procedimientos Almacenados Un procedimiento es un programa dentro de la base de datos que ejecuta una acción o conjunto de acciones específicas. Un procedimiento tiene un nombre, un conjunto de parámetros (opcional) y un bloque de código. En Transact SQL los procedimientos almacenados pueden devolver valores (numérico entero) o conjuntos de resultados. Para crear un procedimiento almacenado debemos emplear la sentencia CREATE PROCEDURE.

Procedimientos Almacenados Sin Parámetros Ejemplo 01 USP que muestre la lista de pedidos Create Proc Usp_ListaPedidos AS Select*From Pedidos GO Ejecutar USP Exec Usp_ListaPedidos

Lugar físico donde se almacenan los USP’S CREADOS Página 75 de 101 Docentes:



RPP - Taller de Base de Datos I Ejemplo 02 USP que muestre código, nombre y teléfono de los clientes Create Proc Usp_ListaClientes01 AS Select IdCliente,NombreEmpleado,Telefono From Clientes GO Ejecutar USP Exec Usp_ListaClientes01 Ejemplo 03 USP que muestre la fecha de pedido, entrega y envió de los pedidos Create Proc Usp_PedidosFechas AS Select FecPedido,FecEntrega,FecEnvío From Pedidos GO Ejecutar USP Exec Usp_PedidosFecha Ejemplo 04 USP que muestre los productos activos. Solo 3 campos Create Procedure Usp_ProductoxActivos AS Select IdProducto,NomProducto,Suspendido From Productos Where Suspendido =1 GO Un USP es como una caja negra dentro de los sistemas de Ejecutar USP Exec Usp_ProductoxActivos

información. Los programadores solo usan el nombre de dicho USP. Muchas veces sin saber las líneas que código que estas tienen. Lo ideal sería encriptar un USP para una mejor seguridad.

5 Procedimientos Almacenados Con Parámetros Los procedimientos almacenados pueden recibir y devolver información; para ello se emplean parámetros, de entrada y salida, respectivamente.

Ejemplo 01.- este USP hace uso de un parámetro de entrada que permite una resultado dinámico. Es decir que depende del valor que se le asigne al parámetro para que se muestre un resultado deseado. La ejecución ahora debe estar acompañada de un dato de entrada. En este caso el nombre del país Create Procedure Usp_PedidosXPais @PaisDest nvarchar(15) AS Select IdPedido,Monto,FecEntrega,PaisDest From Pedidos Where PaisDest=@PaisDest GO Ejecución de USP Exec Usp_PedidosXPais 'Brasil' Ejecución de USP Exec Usp_PedidosXPais 'Finlandia'




RPP - Taller de Base de Datos I Ejemplo 02.- este USP nos permite mostrar los productos ordenados por un rango de precios. La cual es gestionado por 2 parámetros que participan en el USP Create Procedure Usp_ProductosXRangoPrecio @Precio1 Money, @Precio2 Money AS Select IdProducto,NomProducto,PrecioUnidad From Productos Where PrecioUnidad Between @Precio1 AND @Precio2 GO Ejecución de USP Exec Usp_ProductosXRangoPrecio 10,13 Ejecución de USP Exec Usp_ProductosXRangoPrecio 20,25 Ejemplo 03.- este USP nos permite mostrar los productos por su nombre. Solo ingresando las primeras letras Create Procedure Usp_ProductoXNombre @NomProducto nVarchar(40) AS Select IdProducto,NomProducto,Stock From Productos Where NomProducto LIKE @NomProducto +'%' GO Ejecución de USP Exec Usp_ProductoXNombre 'A' Ejecución de USP Exec Usp_ProductoXNombre 'Ce'

Ejemplo 04.- este USP nos permite mostrar los productos por categoria Create Procedure Usp_ProductoXCategoria @IdCategoria Int AS Select IdProducto,NomProducto,IdCategoria From Productos Where IdCategoria =@IdCategoria GO Ejecución de USP Exec Usp_ProductoXCategoria 1 Ejemplo 05.- este USP nos permite mostrar los productos por un rango del stock y además clasificarlos por categoría. Tenemos 3 parámetros de entrada Create Procedure Usp_ProductoDinamic1 @Stock1 smallint, @Stock2 smallint, @IdCategoria int AS Select IdProducto,NomProducto,Stock,IdCategoria From Productos Where Stock Between @Stock1 and @Stock2 AND IdCategoria =@IdCategoria GO




RPP - Taller de Base de Datos I Ejecución de USP Exec Usp_ProductoDinamic1 10,20,1 Ejecución de USP Exec Usp_ProductoDinamic1 30,50,3

Ejemplo 06.- este USP tiene 2 parámetros con valores por defecto NULL. Además de usar LIKE para una búsqueda más efectiva Create Procedure Usp_ClientesDinamicos01 @Pais nvarchar(15) = Null, @CargoEmpleado nvarchar(30) = NUll AS Select IdCliente,Pais,CargoEmpleado From Clientes Where Pais LIKE @Pais+'%' AND CargoEmpleado LIKE @CargoEmpleado+'%' Go Ejecución de USP Exec Usp_ClientesDinamicos01 'A','G' Ejecución de USP Exec Usp_ClientesDinamicos01

RENDIMIENTO Y SEGURIDADes la característica que nos brinda los USP en nuestros sistemas. Los procedimientos almacenados son objetos principales para el desarrollo de sistemas informáticos. Como ves hace uso de CONSULTAS SQL para lo cual debes manejar ese tipo de consultas. Estos procedimientos almacenados pueden ser usados en reportes de tu módulo de sistema de VB, PowerBuilder u otra herramienta de desarrollo.

Ejemplo 07.- este USP tiene como finalidad elevar el precio de los productos por código de proveedor. Este USP usa 2 parámetros de entrada para conseguir este objetivo. Y verifica los datos antes de ejecutar el USP Create Procedure Usp_ModificarPrecioMAS @PrecioUnidad money, @IdProveedor int AS Update Productos Set PrecioUnidad = PrecioUnidad+@PrecioUnidad Where IdProveedor=@IdProveedor GO Ejecutar Consulta select IdProveedor,PrecioUnidad from Productos Ejecutar USP Exec Usp_ModificarPrecioMAS 50,1 Verificar Cambios select IdProveedor,PrecioUnidad from Productos




RPP - Taller de Base de Datos I Ejemplo 08.- este USP normalmente usado para el control del stock dentro de una tabla productos. Para hacer el control del stock necesitas. Ingresar el código del producto y la cantidad a comprar de dicho producto. Lo cual reducirá el stock según lo comprado. Create Procedure Usp_ReducirStock @IdProducto int, @Stock smallint AS Update Productos Set Stock = Stock-@Stock Where IdProducto=@IdProducto GO Ejecutar Consulta Select IdProducto,NomProducto,Stock From Productos Ejecutar USP Exec Usp_ReducirStock 1,10 Ejecutar Consulta Select IdProducto,NomProducto,Stock From Productos

6 Procedimientos almacenados con Parámetros de SALIDA Dijimos que los procedimientos almacenados pueden devolver información; para ello se emplean parámetros de salida. El valor se retorna a quien realizó la llamada con parámetros de salida. Para que un procedimiento almacenado devuelva un valor se debe declarar una variable con la palabra clave "output" al crear el procedimiento: create procedure NOMBREPROCEDIMIENTO @PARAMETROENTRADA TIPO =VALORPORDEFECTO, @PARAMETROSALIDA TIPO=VALORPORDEFECTO output as SENTENCIAS select @PARAMETROSALIDA=SENTENCIAS; Los parámetros de salida pueden ser de cualquier tipo de datos, excepto text, ntext e image.

7 Procedimientos Almacenados de Mantenimiento Tenemos la tabla categoría. En la cual debes fijarte bien en los tipos de datos que tiene cada campo.

Ahora fíjate en lo datos que tiene esta tabla Porque ahora comenzaremos a Crear el procedimiento que me permitirá ingresar nuevas categorías dentro mi tabla categoría.




RPP - Taller de Base de Datos I Usp_AdicionarCategoria Create Proc Usp_AdicionarCategoria @IdCategoria Int, @NomCategoria nvarchar(15), @Descripcion nvarchar(max) AS INSERT INTO Categorias VALUES (@IdCategoria, @NomCategoria, @Descripcion) GO Ejecucion y Verificacion Exec Usp_AdicionarCategoria 12,'Mascotas','Comida, Champu y mas productos' Select * From Categorias

Usp_ModificarCategoria Este USP nos permite modificar registros existentes dentro de la tabla categoría. Para el ejemplo modificaremos la categoría de código 12. Create Proc Usp_ModificarCategoria @IdCategoria Int, @NomCategoria nvarchar(15), @Descripcion nvarchar(max) AS UPDATE Categorias SET NomCategoria=@NomCategoria, Descripcion=@Descripcion Where IdCategoria=@IdCategoria GO Ejecutar el USP con los Nuevos datos Exec Usp_ModificarCategoria 12,'Mascotas Favoritas','Baño, paseo y productos para mascotas' Verificamos Select * From Categorias

Usp_EliminarCategoria Este USP nos permite eliminar un registro Create Proc Usp_EliminarCategoria @IdCategoria Int AS Delete From Categorias Where IdCategoria=@IdCategoria GO Ejecutar el USP y Eliminar datos Exec Usp_EliminarCategoria 12




RPP - Taller de Base de Datos I Usp_BuscarCategoria Este USP nos permite buscar datos dentro de una tabla. La búsqueda es por el campo codigo Create Procedure Usp_BuscarCategoria @IdCategoria int, @Nom nvarchar(15) OUTPUT, @Des nvarchar(max) OUTPUT AS Select @Nom=NomCategoria,@Des=Descripcion From Categorias Where IdCategoria =@IdCategoria GO Ejecutar Consulta Declare @NOMBRE nvarchar(15),@DESCRIPCION nvarchar(max) Exec Usp_BuscarCategoria @idCategoria=1, @Nom=@NOMBRE output, @Des=@DESCRIPCION output select @NOMBRE select @DEScripcion

WITH ENCRYPTION Una funcionalidad utilizada algunas veces para proteger la propiedad intelectual es la encriptación. En SQL Server podemos crear funciones, vistas, procedimientos almacenados añadiendo la opción "WITH ENCRYPTION" para que no se muestre el texto claro de su definición. Si leemos la explicación sobre dicha opción en los BOL vemos que ha cambiado desde SQL 2000 a 2005/2008 pero su esencia es la misma:

SET NOCOUNT ON Si se establece SET NOCOUNT en ON, no se devuelve el recuento. Cuando SET NOCOUNT es OFF, sí se devuelve ese número





Mantenimiento de Compañía de Envíos 5 Procedimientos almacenados que permitiran lagestion de datos de esta tabla. Estos Usp’s seran usandos desde una aplicación de Visual Basic. Para en funcionamiento de los botones Grabar, Editar, Eliminar, Buscar y una lista de registros. Usp para Lista Datos

Usp para Modificar Datos

Create Proc Usp_CompañiaListado WITH ENCRYPTION AS SET NOCOUNT ON SELECT * FROM CompañíasDeEnvíos SET NOCOUNT ON GO

Create Proc Usp_CompañiaEditar @IdCEnvíos Int, @NomCompañía nvarchar(40), @Telefono nvarchar(24) WITH ENCRYPTION AS SET NOCOUNT ON UPDATE CompañíasDeEnvíos SET NomCompañía=@NomCompañía, Telefono=@Telefono WHERE IdCEnvíos=@IdCEnvíos SET NOCOUNT ON GO

Usp para Buscar Por Nombre Create Proc Usp_CompañiaBuscarNOmbre @NomCompañía nVarchar(40) WITH ENCRYPTION AS SET NOCOUNT ON SELECT * FROM CompañíasDeEnvíos WHERE NomCompañía LIKE @NomCompañía+'%' SET NOCOUNT ON GO Usp para Grabar Datos Create Proc Usp_CompañiaGrabar @IdCEnvíos Int, @NomCompañía nvarchar(40), @Telefono nvarchar(24) WITH ENCRYPTION AS SET NOCOUNT ON INSERT INTO CompañíasDeEnvíos VALUES(@IdCEnvíos, @NomCompañía, @Telefono) SET NOCOUNT ON GO

Usp para Eliminar Datos Create Proc Usp_CompañiaEliminar @IdCEnvíos Int WITH ENCRYPTION AS SET NOCOUNT ON DELETE FROM CompañíasDeEnvíos WHERE IdCEnvíos=@IdCEnvíos SET NOCOUNT ON GO Usp para Buscar Datos Create Proc Usp_CompañiaBuscar @IdCEnvíos Int, @NomCompañía nvarchar(40) OUTPUT, @Telefono nvarchar(24) OUTPUT WITH ENCRYPTION AS SET NOCOUNT ON SELECT

@NomCompañía=NomCompañía, @Telefono=Telefono FROM CompañíasDeEnvíos WHERE IdCEnvíos=@IdCEnvíos SET NOCOUNT ON GO





8 Desencadenadores o TRIGGERS Un trigger( o desencadenador) es una clase especial de procedimiento almacenado que se ejecuta automáticamente cuando se produce un evento en el servidor de bases de datos. SQL Server proporciona los siguientes tipos de triggers: Trigger DML, se ejecutan cuando un usuario intenta modificar datos mediante un evento de lenguaje de manipulación de datos (DML). Los eventos DML son instrucciones INSERT, UPDATE o DELETE de una tabla o vista. Trigger DDL, se ejecutan en respuesta a una variedad de eventos de lenguaje de definición de datos (DDL). Estos eventos corresponden principalmente a instrucciones CREATE, ALTER y DROP de Transact-SQL, y a determinados procedimientos almacenados del sistema que ejecutan operaciones de tipo DDL. Trigger DML. Los trigger DML se ejecutan cuando un usuario intenta modificar datos mediante un evento de lenguaje de manipulación de datos (DML). Los eventos DML son instrucciones INSERT, UPDATE o DELETE de una tabla o vista.

Tablas Inserted y Deleted. Las instrucciones de triggers DML utilizan dos tablas especiales denominadas inserted y deleted. SQL Server 2008 crea y administra automáticamente ambas tablas. La estructura de las tablas inserted y deleted es la misma que tiene la tabla que ha desencadenado la ejecución del trigger. La primera tabla (inserted) solo está disponible en las operaciones INSERT y UPDATE y en ella están los valores resultantes después de la inserción o actualización. Es decir, los datos insertados. Inserted estará vacía en una operación DELETE. En la segunda (deleted), disponible en las operaciones UPDATE y DELETE, están los valores anteriores a la ejecución de la actualización o borrado. Es decir, los datos que serán borrados. Deleted estará vacía en una operación INSERT. ¿No existe una tabla UPDATED? No, hacer una actualización es lo mismo que borrar (deleted) e insertar los nuevos (inserted). La sentencia UPDATE es la única en la que inserted y deleted tienen datos simultáneamente. Ejemplo 01 Para nuestro ejemplo usaremos la tabla Compañía de envíos ANALIZANDO Ud. creo un trigger que emitirá un mensaje 'Usted Acaba de Ingresar DATOS' Que se mostrara cada vez que un cliente ejecute la instrucción INSERT sobre la tabla que apunta.El cliente puede ser:  Por medio de Transact-SQL  Aplicación Visual Basic  Excel  SQL Management Studio, etc  Realizar algo mientras alguien ingresa datos.




RPP - Taller de Base de Datos I Ejemplo 02 Trigger de Actualización ANALIZANDO Ud. creo un trigger que emitirá un mensaje 'Usted Acaba de Ingresar Modificar Datos' Que se mostrara cada vez que un cliente ejecute la instrucción UPDATE sobre la tabla que apunta. El cliente puede ser: . Una vez creado el Trigger lo ejecutamos con una consulta de actualización

Ejemplo 03 Trigger de Eliminacion

ANALIZANDO Ud. creo un trigger que emitirá un mensaje 'Usted Acaba de Ingresar Eliminar Datos' Que se mostrara cada vez que un cliente ejecute la instrucción DELETE sobre la tabla que apunta. Una vez creado el Trigger lo ejecutamos con una consulta de Eliminacion




RPP - Taller de Base de Datos I Ejemplo 04 Este Trigger trabajas con insert, update y delete. Y cada vez que un cliente ejecute una de estas instrucciones. Mostrará un SELECT de la tabla. En el Ejemplo inserte un nuevo registro y puedes ver en la imagen que automáticamente se muestra toda la lista de datos con el nuevo registro. Si Actualizas o eliminas sucederá la misma acción

Ejemplo 05 Permite mostrar un mensaje. Cuando se elimina una tabla en la base de datos

Los desencadenadores DDL pueden activarse en respuesta a un evento de Transact-SQL procesado en la base de datos actual o en el servidor actual. El ámbito del desencadenador depende del evento. Por ejemplo, un desencadenador DDL creado para activarse como respuesta a un evento CREATE_TABLE se activará siempre que se produzca un evento CREATE_TABLE en la base de datos o en la instancia de servidor. Un desencadenador DDL creado para activarse como respuesta a un evento CREATE_LOGIN se activará únicamente cuando que se produzca un evento CREATE_LOGIN en el servidor.





9 Activar o Desactivar USP -- Desactiva el trigger TR_CUENTAS DISABLETRIGGER TR_CUENTAS ON CUENTAS GO -- activa el trigger TR_CUENTAS ENABLETRIGGER TR_CUENTAS ON CUENTAS GO -- Desactiva todos los trigger de la tabla CUENTAS ALTERTABLE CUENTAS DISABLETRIGGER ALL GO -- Activa todos los trigger de la tabla CUENTAS ALTERTABLE CUENTAS ENABLETRIGGER ALL

10 Copia de Seguridad Para el ejemplo vamos a crear una base de datos de nombre PANDAL Imaginemos que esta base de datos contiene. Tablas, USP’s, Funciones etc. Y deseamos generar una copia de seguridad. Para luego restaurar nuestra base de datos. Esta práctica es usada para tener una copia de datos si ocurriera algún desastre que con lleve a la perdida de la información. También algunos estudiantes lo usan para transportas sus bases de datos. PASOS A.- Seleccionar la base de datos Clic en TAREAS + COPIAS DE SEGURIDAD como podemos ver en la imagen




RPP - Taller de Base de Datos I B.- ingresar el nombre y la descripción

Nombre y Descripción

C.- Clic en el botón AGREGAR D.- Clic Botón Examinar (botón de 3 puntos) E.- Seleccionar la Unidad C:\ o el que tú elijas F.- ingresar Nombre de Archivo G.- Presionar Aceptar




RPP - Taller de Base de Datos I H.- Presionar Aceptar

I.- Verificar los datos y verificar la copia de seguridad Por generada. Clic en Aceptar

J.- Esperar la Ejecución y Veras.

Presionar Aceptar y Verifica la Existencia del Archivo BackUp de la Db Pandal NOTA: Se Recomienda que elimines la Db Pandal EL ESTUDIANTE DEBE GENERAR LA COPIA SDE SEGURIDAD DE SU BASE DE DATOS PARA LUEGO RESTAURARLO EN EL SERVIDOR QUE HACE USO. LA CREACION DE BASE DE DATOS ES UNA TAREA FUNDAMENTAL DE UN DBA Y SON PROGRAMADAS PARA SU CREACION EN UN MOMENTO DETERMINADO. ADEMAS REDORDAR QUE TAMBIEN SE PUEDE CREAR COPIA DE SEGURIDAD Y RESTAURAR CON CODIGO TRANSACT-SQL





11 Restaurar una Copia de Seguridad A.- Seleccionar Cualquier Base de Datos y Clic en TAREAS + RESTAURAR + BASE DE DATOS

B.- Ingresar el Nombre de la Nueva base de datos A una base de datos: PANDALSOFT

C.- Origen de Restauración Desde Dispositivo

Selecciona

D.- Presionar el botón (de 3 puntos)





E.- Presionar el botón Agregar F.- Buscar la ubicación de tu archivo de Copia de seguridad (archivo .BAK)

G.- Presionar Aceptar

H.- Presionar Aceptar





Propuesto 07 1) Crear una función que me permita Mostrar el sueldo de un profesor sabiendo sus pago hora y sus horas trabajadas

2) Crear una función que me permita ingresar un número y mostrar el número elevado al cuadra

3) Crear un función que permita ingresar la edad de la persona y muestre un mensaje por si es Niño, Joven, Adulto y Adulto mayor

4) Crear una función que muestre el nombre, precio y stock de los productos de la tabla productos

5) Crear una función que me muestre las ventas totales que realizo un empleado. Debes ingresar su código

6) Crear una función que me permita crear código autogenerados ej. P0001, P0002, P0003

7) USP que me permita generar código auto generados. Que dependerá de la categoría. Ej. BEBIDAS B0001, B0002, B0003 - REPORTERIA será R0001, R0002, etc. Y si se agregara una nueva categoría. esa nueva categoría tendrá su nuevo código auto generado.

8) USP Para Mostrar los productos con un intervalo de precios

9) USP Para Mostrar los productos donde el precio sea mayor a un precio ingresado

10) USP Para Mostrar los productos donde el precio sea menor a un precio ingresado

11) USP Para Mostrar los productos donde sea ingresado el nombre del proveedor el proveedor

12) USP Para Mostrar los productos donde depende del país y región del proveedor

13) USP Para Mostrar los productos activos o no activos

14) USP Para restaurar el stock de un producto cuando un cliente cancela dicho pedido 15) USP de mantenimiento de todas las tablas de Ward’s Supermarket 16) Como Restaurar una base de dato con código Transact-SQL - Ejemplos

Fecha de entrega:





CAPITULO 08 Objetivos.-Conocer el manejo de transacciones dentro de un servidor. Los tipos de transacciones. Y por último aprender a gestionar permisos y restricciones de todos los objetos que aprendió en este material

1 Transacciones en Transact SQL

93

2 Creación de Usuarios y Asignar Permisos y Restricciones

98

3 Modificar Usuario SA

99

4 Ingresando como Usuario JHONNY

100

5 Otorgando Permisos a JHONNY

101





1 Transacciones en Transact SQL Concepto de transaccion Una transacción es un conjunto de operaciones Transact SQL que se ejecutan como un único bloque, es decir, si falla una operación Transact SQL fallan todas. Si una transacción tiene éxito, todas las modificaciones de los datos realizadas durante la transacción se confirman y se convierten en una parte permanente de la base de datos. Si una transacción encuentra errores y debe cancelarse o revertirse, se borran todas las modificaciones de los datos. El ejemplo clásico de transacción es una transferencia bancaria, en la que quitamos saldo a una cuenta y lo añadimos en otra. Si no somo capaces de abonar el dinero en la cuenta de destino, no debemos quitarlo de la cuenta de origen. SQL Server funciona por defecto con Transacciones de confirmación automática, es decir, cada instrucción individual es una transacción y se confirma automáticamente. Sobre el ejemplo anterior de la transferencia bancaria, un script debería realizar algo parecido a los siguiente:

DECLARE @importe DECIMAL(18,2), @CuentaOrigen VARCHAR(12), @CuentaDestino VARCHAR(12) /* Asignamos el importe de la transferencia * y las cuentas de origen y destino */ SET @importe = 50 SET @CuentaOrigen = '200700000001' SET @CuentaDestino = '200700000002' /* Descontamos el importe de la cuenta origen */ UPDATE CUENTAS SET SALDO = SALDO - @importe WHERE NUMCUENTA = @CuentaOrigen /* Registramos el movimiento */ INSERTINTO MOVIMIENTOS (IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO) SELECT IDCUENTA, SALDO + @importe, SALDO, @importe, getdate()FROM CUENTAS WHERE NUMCUENTA = @CuentaOrigen /* Incrementamos el importe de la cuenta destino */ UPDATE CUENTAS SET SALDO = SALDO + @importe WHERE NUMCUENTA = @CuentaDestino /* Registramos el movimiento */ INSERTINTO MOVIMIENTOS (IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO) SELECT IDCUENTA, SALDO - @importe, SALDO, @importe, getdate() FROM CUENTASWHERE NUMCUENTA = @CuentaDestino Esta forma de actuar seria errónea, ya que cada instrucción se ejecutaría y confirmaría de forma independiente, por lo que un error dejaría los datos erróneos en la base de datos (ese es el peor error que nos podemos encontrar)




RPP - Taller de Base de Datos I Transacciones implicitas y explicitas Para agrupar varias sentencias Transact SQL en una única transacción, disponemos de los siguientes métodos: Transacciones explícitas Cada transacción se inicia explícitamente con la instrucción BEGIN TRANSACTION y se termina explícitamente con una instrucción COMMIT o ROLLBACK. Transacciones implícitas Se inicia automáticamente una nueva transacción cuando se ejecuta una instrucción que realiza modificaciones en los datos, pero cada transacción se completa explícitamente con una instrucción COMMIT o ROLLBACK. Para activar-desactivar el modo de transacciones implícitas debemos ejecutar la siguiente instrucción. --Activamos el modo de transacciones implicitas SETIMPLICIT_TRANSACTIONSON --Desactivamos el modo de transacciones implicitas SETIMPLICIT_TRANSACTIONSOFF Cuando la opción ANSI_DEFAULTS está establecida en ON, IMPLICIT_TRANSACTIONS también se establece en ON.El siguiente ejemplo muestra el script anterior haciendo uso de TRANSACCIONES EXPLICITAS.

DECLARE @importe DECIMAL(18,2), @CuentaOrigen VARCHAR(12), @CuentaDestino VARCHAR(12) /* Asignamos el importe de la transferencia * y las cuentas de origen y destino */ SET @importe = 50 SET @CuentaOrigen = '200700000002' SET @CuentaDestino = '200700000001' BEGINTRANSACTION -- O solo BEGIN TRAN BEGINTRY /* Descontamos el importe de la cuenta origen */ UPDATE CUENTAS SET SALDO = SALDO - @importe WHERE NUMCUENTA = @CuentaOrigen /* Registramos el movimiento */ INSERTINTO MOVIMIENTOS (IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO) SELECT IDCUENTA, SALDO + @importe, SALDO, @importe, getdate() FROM CUENTAS WHERE NUMCUENTA = @CuentaOrigen /* Incrementamos el importe de la cuenta destino */




RPP - Taller de Base de Datos I UPDATE CUENTAS SET SALDO = SALDO + @importe WHERE NUMCUENTA = @CuentaDestino /* Registramos el movimiento */ INSERTINTO MOVIMIENTOS (IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO) SELECT IDCUENTA, SALDO - @importe, SALDO, @importe, getdate() FROM CUENTAS WHERE NUMCUENTA = @CuentaDestino /* Confirmamos la transaccion*/ COMMITTRANSACTION -- O solo COMMIT ENDTRY BEGINCATCH /* Hay un error, deshacemos los cambios*/ ROLLBACKTRANSACTION -- O solo ROLLBACK PRINT 'Se ha producido un error!' ENDCATCH

El siguiente ejemplo muestra el mismo script con TRANSACCIONES IMPLICITAS.

SETIMPLICIT_TRANSACTIONSON DECLARE @importe DECIMAL(18,2), @CuentaOrigen VARCHAR(12), @CuentaDestino VARCHAR(12) /* Asignamos el importe de la transferencia * y las cuentas de origen y destino */ SET @importe = 50 SET @CuentaOrigen = '200700000002' SET @CuentaDestino = '200700000001' BEGINTRY /* Descontamos el importe de la cuenta origen */ UPDATE CUENTAS SET SALDO = SALDO - @importe WHERE NUMCUENTA = @CuentaOrigen /* Registramos el movimiento */ INSERTINTO MOVIMIENTOS (IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO) SELECT IDCUENTA, SALDO + @importe, SALDO, @importe, getdate() FROM CUENTAS WHERE NUMCUENTA = @CuentaOrigen





/* Incrementamos el importe de la cuenta destino */ UPDATE CUENTAS SET SALDO = SALDO + @importe WHERE NUMCUENTA = @CuentaDestino /* Registramos el movimiento */ INSERTINTO MOVIMIENTOS (IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO) SELECT IDCUENTA, SALDO - @importe, SALDO, @importe, getdate() FROM CUENTAS WHERE NUMCUENTA = @CuentaDestino /* Confirmamos la transaccion*/ COMMITTRANSACTION -- O solo COMMIT ENDTRY BEGINCATCH /* Hay un error, deshacemos los cambios*/ ROLLBACKTRANSACTION -- O solo ROLLBACK PRINT 'Se ha producido un error!' ENDCATCH

La transacción sigue activa hasta que emita una instrucción COMMIT o ROLLBACK. Una vez que la primera transacción se ha confirmado o revertido, se inicia automáticamente una nueva transacción la siguiente vez que la conexión ejecuta una instruccion para modificar datos. La conexión continúa generando transacciones implícitas hasta que se desactiva el modo de transacciones implícitas.Podemos verificar el número de transacciones activas a través de @@TRANCOUNT.

SETIMPLICIT_TRANSACTIONSON BEGINTRY UPDATE CUENTAS SET FXALTA = FXALTA - 1 PRINT @@TRANCOUNT COMMIT ENDTRY BEGINCATCH ROLLBACK PRINT 'Error' ENDCATCH




RPP - Taller de Base de Datos I Transacciones anidadas. Podemos anidar varias transacciones. Cuando anidamos varias transacciones la instrucción COMMIT afectará a la última transacción abierta, pero ROLLBACK afectará a todas las transacciones abiertas. Un hecho a tener en cuenta, es que, si hacemos ROLLBACK de la transacción superior se desharán también los cambios de todas las transacciones internas, aunque hayamos realizado COMMIT de ellas.

BEGINTRAN UPDATE EMPLEADOS SET NOMBRE = 'Jhonny Huber' WHERE ID=101 BEGINTRAN UPDATE EMPLEADOS SET APELLIDO1 = 'Pandal' WHERE ID=101 -- Este COMMIT solo afecta a la segunda transaccion. COMMIT -- Este ROLLBACK afecta a las dos transacciones. ROLLBACK

Una consideración a tener en cuanta cuando trabajamos con transacciones anidadas es la posibilidad de utilizar puntos de guardado o SAVEPOINTs. Puntos de recuperación (SavePoint). Los puntos de recuperación (SavePoints) permiten manejar las transacciones por pasos, pudiendo hacer rollbacks hasta un punto marcado por el savepoint y no por toda la transacción. BEGINTRAN UPDATE EMPLEADOS SET NOMBRE = 'Devjoker' WHERE ID=101 UPDATE EMPLEADOS SET APELLIDO1 = 'Devjoker.COM' WHERE ID=101 SAVETRANSACTION P1 -- Guardamos la transaccion (Savepoint) UPDATE EMPLEADOS SET APELLIDO1 = 'Otra cosa!' WHERE ID=101 -- Este ROLLBACK afecta solo a las instrucciones -- posteriores al savepoint P1. ROLLBACKTRANSACTION P1 -- Confirmamos la transaccion COMMIT





2 Creación de Usuarios y Asignar Permisos y Restricciones Configurar Sesion SA A.- Expandir carpeta Seguridad luego Inicios de Sesión B.- Clic en Propiedades del MC del Inicios de sesión SA C.- Ingresar las NUEVAS CONTRASEÑAS: pandal1980

D.- Clic en Botón ACEPTAR

Ingresar Usuario SA A.- Clic ARCHIVO + DESCONECTAR EXPLORADOR DE OBJETOS B.- Clic ARCHIVO + CONECTAR EXPLORADOR DE OBJETOS

C.- Verifica el Nombre de Sesión





Objetos de ejemplo a administrar A.- Creación de DB

Pandalsoft

B.- Creación de Tabla Planilla

C.- Ingresar Registros

SA es un inicio de sesión que tiene todos los permisos para administrar las herramientas y todas las bases de datos de SQL Server 2008. Es un Súper Usuario. El siguiente paso es crear un Nuevo Usuario con Permisos Limitados

3 ModificarUsuario SA A.- Expandir la carpeta Seguridad y luego Inicios de Sesión B.- Clic en Nuevo Inicio de sesión C.- Ingresar Nombre de Inicio de sesión y su contraseña

La contraseña será: pandal1980 Con autenticación SQL Server Las contraseñas deben tener una combinación especial de dígitos por seguridad

D.- Selecciona base de datos Master E.- Clic en Aceptar





4 Ingresando como Usuario JHONNY A.- Clic ARCHIVO + DESCONECTAR EXPLORADOR DE OBJETOS B.- Clic ARCHIVO + CONECTAR EXPLORADOR DE OBJETOS

C.- Ingresar Usuario y Contraseña D.- Verificar Ingreso

CONSULTAS Abrimos el analizador de consultas y agregamos y ejecutamos lo siguiente Use Pandalsoft Go Select * From Planilla GO

Como Puede Ver El Usuario Jhonny No Tiene Acceso A Ninguna Base De Datos Ni Tampoco Puede Crear Bases De Datos. Este Usuario Es Un Usuario Sin Permisos. ¿Quién Administra Los Permisos? Un Usuario Que Tiene El Rol De Sysadmin En Nuestro Caso El Usuario

SA Página 100 de 101

Docentes:




5 Otorgando Permisos a JHONNY

A.- Desconéctate de JHONNY e Ingresa como Usuario SA

B.- ahora crearemos el siguiente escenario. Te entrego estas líneas de script Use Pandalsoft Go -- Consultando Select * From dbo.Planilla GO -- insertar nuevo registro de PEPE INSERT Planilla VALUES('E0006','PEPE',1200,'12/12/2011','Obrero',5) -- modificando du fecha de contrato de PEPE UPDATE Planilla SET FinContrato='12/12/2013' WHERE IdEmpleado='E0006' --eliminando a PEPE DELETE FROM Planilla WHERE IdEmpleado='E0006' Haremos que el usuario JHONNY solo tenga acceso a la base de datos Pandalsoft. Además el solo podrá insertar registros a la tabla. y NO podrá actualizar ni eliminar registros. Además te darás cuenta que el usuario JHONNY no podrá crear bases de datos Y Bueno este escenario lo haremos en clases. Así que les dejo con las ganas. Investigar y practicar es lo que nos hace más grandes. NUNCA LO OLVIDES




Servidor SQL Server-1

Recommend Documents