Microsoft SQL Server 2014

1. Contenido del Curso de Bases de datos en Microsoft SQLServer 2014

Instalacin y Configuracin, Seguridad Gestin de Base de Datos Estructura de Una Base de datos Archivos y Grupos Fsicos de Bases de Datos Esquemas, Tablas, Campos, Tipos de datos Relaciones, Llaves primarias y Forneas Restricciones Unique, Check, Default Separar y Adjuntar Base de Datos Backup y Restauracin de Base de Datos LDD Lenguaje de Manipulacin de Datos Create, Alter, Drop DML Lenguaje de Manipulacin de Datos Insert Into, Update, Delete Select, Group By, Having, Union Programacin Transact SQL Variables, Funciones Cast y Convert Estructuras de Control Condicionales e Iterativas o repetitivas Procedimientos Almacenados Cursores, Triggers, Tablas de Referencia CruzadaQu es una Base de Datos?

Es un conjunto de Informacin relacionada sobre un tema en particular, organizada de alguna forma tal que suministra una base o fundamento para procedimientos, como la recuperacin o consulta de la Informacin, la elaboracin de conclusiones en base a la data y la toma de desiciones de una organizacin.

Se dice que cualquier conjunto de Informacin que sirva a lo nombrado anteriormente se le calificar como base de Datos, an cuando la informacin no est almacenada en algn medio Informtico.

Sistemas de Gestin de Base de Datos (SGBD)

Son aplicaciones que permiten la administracin de los datos almacenados en una o varias Bases de datos. Independencia de los datos y los programas Minimizacin de la Redundancia Integracin y Sincronizacin de las Bases de Datos Facilidad de Manipulacin y Recuperacin de la Informacin Seguridad y Control Centralizado.SQL Server 2014

Microsoft SQLServer es un Sistema para la Gestin de Base de Datos basado en el Modelo Relacional. Sus lenguajes para consultas son Transact-SQL y Ansi SQL.Presenta Un entorno grfico de administracin de los Objetos del motor de base de datos, que permite el uso de comando DDL y DML grficamente.

2. Base de Datos en Sql Server

Al instalar SQLServer tambin se registran Bases de datos que administran diferentes aspectos del servidor de Base de Datos.

MASTER:Es el ncleo y contiene Informacin vital de la Instancia de Sqlserver, informacin de la administracin de las diferentes bases de datos.TEMPDB:Base de datos temporal que brinda espacios para realizar algunas operaciones.MODEL:Es una plantilla para todas las bases de datos creadas en el servidor, se implementa cuando se ejecuta el comando CREATE DATABASE.MSDB:Empleada por SQL Server Agent, guarda informacin de servicios de automatizacin, historial de copias de seguridad, tareas, alertas, planes de mantenimiento entre otros registros.

Estructura de Base de Datos en Sql Server

Tablas:La tabla es el primer objeto de una base de datos y se organiza en filas y columnas, una fila equivale a un registro y las columnas definen los campos del registro, los campos se definen sobre un tipo de datos.

Campos:Representa los Atributos de una entidad o tabla. Campo Clave:Es el campo que representa unvalor nico e identifica a un registro de la tabla. Campo Forneo:Campo que une a otra entidadformando una extensin de la tabla fuente. Registro: Representa el Conjunto de valores por cada campo de una misma fila. Dato:Es un valor que no representa nada mientras no se une a otros datos.Estructura de una Base de Datos en Sql Server

Estructura de una Tabla en Sql Server

3. Ejercicio Propuesto

Se desea implementar un sistema web para gestionar la reserva de vuelos; que permita al usuario registrar sus reservas de vuelos y hacer consultas, adems de poder comprar los boletos de viaje de manera remota, sin la necesidad de recurrir a una agencia de viaje.El sistema web debe de mostrar una interfaz describiendo los servicios ofrecidos junto con la opcin para registrarse si es la primera vez que accede a ella, o si ya se encuentra registrado acceder por medio de su usuario (email) y su clave.

Una vez registrado el usuario este podr acceder a los siguientes procesos: Consulta de Vuelos Por Horarios de Vuelos Por Tarifas de Vuelos Informacin de Vuelos Reserva de Vuelos Compra de Boletos de viajeEsquema de Archivos en Sql Server

Tipos de Datos en Sql Server 2014

Tipo de Datos CarcterChar:Los datos deben de tener una longitud fija hasta 8Kb. Ejemplo: Si queremos almacenar categoras por ejemplo A, B, C Utilizaremos Char(1)

Varchar:Puede variar en el nmero de carcteres, es decir el valor asignado no es fijo, aqu SQL administra los espacios en blanco y los optimiza.Ejemplo en un Varchar(15) ocupar menos espacio el dato Ana que Juan Carlos.

Tipo de Datos EnterosInt:Nmeros enteros desde-2 31 (-2 147 483 648) a 2 31-1 (2 147 483 647).

Tipos de Datos FechaDate:Tipo de dato que muestra la fecha en el siguiente formato 07-07-2014.

Tipo de Datos DecimalDecimal:Tipo de datos que se utiliza para almacenar nmeros decimales que pueden tener hasta 38 dgitos.

Tipo de Datos MonetarioMoney:Cantidad monetaria positiva o negativa.

Propiedades de DatosPropiedad NULL:Hay dos formas de expresar el trmino NULL, al implementarlo como NULL estamos indicando que el contenido de dicha columna no es obligatorio, si se necesita especificar que el campo es obligatorio se implementar con NOT NULL.

Propiedad IDENTITY:Propiedad slo aplicada a campos numricos, ya que define un autoincremento automtico de valores.

4. RelacionesEl modo de relacionar registros entre tablas es por tanto mediante referencias, para lo cual se usan los identificadores definidos como claves primarias y forneas.

Clave primariaEn el diseo de bases de datos relacionales, se llama clave primaria (Primary Key) a un campo o a una combinacin de campos que identifica de forma nica a cada fila de una tabla. Una clave primaria comprende de esta manera una columna o conjunto de columnas. No puede haber dos filas en una tabla que tengan la misma clave primaria.Una clave primaria debe identificar a todas las posibles filas de una tabla y no nicamente a las filas que se encuentran en un momento determinado. Ejemplos de claves primarias son DNI (asociado a una persona) o ISBN (asociado a un libro). Las guas telefnicas y diccionarios no pueden usar nombres o palabras o nmeros del sistema decimal de Dewey como claves candidatas, porque no identifican unvocamente nmeros de telfono o palabras.

Clave forneaEn el contexto de bases de datos relacionales, una clave fornea o clave ajena (o Foreign Key FK) es una limitacin referencial entre dos tablas. La clave fornea identifica una columna o grupo de columnas en una tabla (tabla hija o referendo) que se refiere a una columna o grupo de columnas en otra tabla (tabla maestra o referenciada). Las columnas en la tabla referendo deben ser la clave primaria u otra clave candidata en la tabla referenciada.

Los valores en una fila de las columnas referendo deben existir solo en una fila en la tabla referenciada. As, una fila en la tabla referendo no puede contener valores que no existen en la tabla referenciada. De esta forma, las referencias pueden ser creadas para vincular o relacionar informacin. Esto es una parte esencial de la normalizacin de base de datos. Mltiples filas en la tabla referendo pueden hacer referencia, vincularse o relacionarse a la misma fila en la tabla referenciada. Mayormente esto se ve reflejado en una relacin uno (tabla maestra o referenciada) a muchos (tabla hija o referendo).

Diagrama Relacional Base de Datos en Sql Server 2014

Dentro de las opciones que nos ofrece nuestra base de datos, encontramos la opcin Diagrama de la Base de Datos, que nos permite realizar las relaciones de las tablas en forma Grfica. Para acceder a esta opcin le damos click derecho y crearemos un nuevo Diagrama de Base de Datos.

Imagen 1: Diagrama relacional de la Base de datos del Curso.

5. Lenguaje SQLEl lenguaje SQL est compuesto por comandos, clusulas, operadores y funciones de agregados. Estos elementos se Combinan en las instrucciones para crear, actualizar y Manipular las base de datos.

Existen 2 tipos de Comandos SQL:Lenguaje de Definicin de Datos (DDL),comandos para crear y definir nuevas base de datos, tablas, campos e ndices.Lenguaje de Manipulacin de Datos (DML),comandos para insertar, modificar y eliminar registros, as como ordenar, filtrar y extraer informacin de la base de datos.

1. Lenguaje de Definicin de DatosSENTENCIA CREATE(Comando de Creacin de Objetos de la Base de Datos): Permite crear base de datos, tablas, desencadenadores, procedimientos, funciones, vistas e ndices de una base de Datos.

SENTENCIA ALTER(Comando de Modificacin de Objetos de la Base de Datos): Permite la modificacin de un objeto asociado a una base de Datos, puede modificar archivos, grupo de archivos, cambiar atributos de un Objeto.

SENTENCIA DROP(Comando de Eliminacin de Objetos de la Base de Datos): Permite la eliminacin de un objeto asociado a una Base de Datos.

Archivos de Base de Datos

Cdigo para Implementar la Base de Datos y los Archivos .MDF y .LDF

--Verificamos que la base de datos no este implementada para implementarlaIFDB_ID('DBRESERVAM')ISNOTNULLBEGINUSEMASTERDROPDATABASEDBRESERVAMEND--creamos la base de datosCREATEDATABASEDBRESERVAMONPRIMARY( NAME='DBRESERVA_MDF',FILENAME='C:\Sqlserver-BaseDatos2\DBRESERVA.MDF', SIZE=10MB, MAXSIZE=40MB, FILEGROWTH=5MB),( NAME='DBRESERVA2_MDF',FILENAME='C:\Sqlserver-BaseDatos2\DBRESERVA2.MDF',SIZE=5MB, MAXSIZE=30MB, FILEGROWTH=5%)LOGON( NAME='DBRESERVA_LOG',FILENAME='C:\Sqlserver-BaseDatos2\DBRESERVA2.LDF', SIZE=4MB, MAXSIZE=20MB,FILEGROWTH=10%)GO

--ACTIVAMOS LA BASE DE DATOS PARA EMPEZAR A TRABAJAR CON ELLAUSEDBRESERVAMGO

6. Tipos de Datos en Sql Server 2014Tipo de Datos CarcterChar:Los datos deben de tener una longitud fija hasta 8Kb. Ejemplo: Si queremos almacenar categoras por ejemplo A, B, C Utilizaremos Char(1)

Varchar:Puede variar en el nmero de carcteres, es decir el valor asignado no es fijo, aqu SQL administra los espacios en blanco y los optimiza.Ejemplo en un Varchar(15) ocupar menos espacio el dato Ana que Juan Carlos.

Tipo de Datos EnterosInt:Nmeros enteros desde-2 31 (-2 147 483 648) a 2 31-1 (2 147 483 647).

Tipos de Datos FechaDate:Tipo de dato que muestra la fecha en el siguiente formato 07-07-2014.

Tipo de Datos DecimalDecimal:Tipo de datos que se utiliza para almacenar nmeros decimales que pueden tener hasta 38 dgitos.

Tipo de Datos MonetarioMoney:Cantidad monetaria positiva o negativa.

Propiedades de DatosPropiedad NULL:Hay dos formas de expresar el trmino NULL, al implementarlo como NULL estamos indicando que el contenido de dicha columna no es obligatorio, si se necesita especificar que el campo es obligatorio se implementar con NOT NULL.

Propiedad IDENTITY:Propiedad slo aplicada a campos numricos, ya que define un autoincremento automtico de valores.

Implementando las tablas Utilizando Lenguaje de Definicin de Datos DDL

--ACTIVAMOS LA BASE DE DATOS PARA EMPEZAR A TRABAJAR CON ELLAUSEDBRESERVAMGO--IMPLEMENTANDO LAS TABLAS DE LA BASE DATOS--TABLA PASCREATETABLEpais( idpaischar(4)notnullprimarykey, nombrevarchar(30)notnullunique)go--TABLA PASAJEROCREATETABLEpasajero(idpasajerochar(8)notnullprimarykey, nombrevarchar(20)notnull, apaternovarchar(20)notnull, amaternovarchar(20)notnull, tipo_documentovarchar(30)notnull, num_documentovarchar(12)notnull,fecha_nacimientodatenotnull, idpaischar(4)notnull, telefonovarchar(15)null,emailvarchar(50)notnullunique,clavevarchar(20)notnull)go--TABLA AEROPUERTOCREATETABLEaeropuerto( idaeropuertochar(5)notnull,nombrevarchar(50)notnull, idpaischar(4)notnull)go--TAMBIEN SE PUEDE AGREGAR LA LLAVE PRIMARIA DESPUES DE CREAR LA TABLAALTERTABLEaeropuertoADDCONSTRAINTpk_aeropuerto_idaeropuertoPRIMARYKEYNONCLUSTERED(IDAEROPUERTO)GO--TAMBIEN VOY A CREAR EL CONSTRAINT DE CAMPO NICOALTERTABLEaeropuertoADDCONSTRAINTuq_aeropuerto_nombreUNIQUE(idaeropuerto)GO--TABLA AEROLNEACREATETABLEaerolinea( idaerolineaintnotnullprimarykey, rucchar(11)notnullunique, nombrevarchar(40)notnullunique)go

--TABLA AVINCREATETABLEavion(idavionchar(5)notnullprimarykey, idaerolineaintnotnull, fabricantevarchar(40)null, tipovarchar(30)null, capacidadintnotnull)go

7. Restricciones de los CamposUnique:Permite determinar que los valores registrados en una misma columna no sean idnticos, es decir se mantengan nicos. Por ejemplo el email de una persona es un campo nico.

Check:Permite restringir el rango de valores que pueden estar permitidos ingresar en una o ms columnas de una tabla.

Default:Permite registrar un dato en un campo por defecto cuando el usuario no ingresa ningn valor, la propiedad del campo de la tabla necesariamente tiene que ser Null.

Ejemplo se puede Utilizar para los campos de tipo Date (fecha), cuando el usuario no ingresa ninguna fecha que se asigne la fecha actual.

Implementado las tablas faltantes de nuestro ejercicio PropuestoDbreservae implementando las restricciones

--Eliminar la tabla avin y la creare de nuevo pero dejando un campo menosdroptableavion

--creando de nuevo la tabla avionCREATETABLEavion( idavionchar(5)notnullprimarykey, idaerolineaintnotnull, fabricantevarchar(40)null,tipovarchar(3)null)go

--voy agregar nuevamente la capacidadALTERTABLEAVIONADDcapacidadintnotnullgo

--eliminar nuevamente capacidad--ALTER TABLE avion--drop column capacidad--go

--voy a modificar la columna tipoALTERTABLEavionALTERCOLUMNtipovarchar(30)notnullgo

--TABLA ASIENTOCREATETABLEasiento(idasientointnotnullprimarykey, letrachar(2)notnull, filaintnotnull)go

--TABLA TARIFACREATETABLEtarifa( idtarifaintnotnullprimarykey, clasevarchar(20)notnullunique, preciomoneynotnull, impuestomoneynotnull)go--TABLA RESERVACREATETABLEreserva( idreservaintnotnullprimarykey, costomoneynotnull, fechadateNULL,observacionvarchar(200)null)go

--AGREGAREMOS UNA RESTRICCIN DEFAULT A LA FECHA PARA QUE LA FECHA POR DEFECTOSEA LA FECHA ACTUALALTERTABLEreservaADDCONSTRAINTDFL_RESERVA_FECHADEFAULTGETDATE()FORfechago

--TABLA VUELOCREATETABLEvuelo(idasientointnotnull, idaeropuertochar(5)notnull, idreservaintnotnull, idavionchar(5)notnull,idtarifaintnotnull)go

--IMPLEMENTANDO LAS LLAVES PRIMARIAS DE LA TABLA VUELOALTERTABLEVUELOADDPRIMARYKEYNONCLUSTERED(idasiento,idaeropuerto,idreserva,idavion)go

--TABLA PAGOCREATETABLEpago( idpagointnotnullprimarykeyidentity, idreservaintnotnull, idpasajerochar(8)notnull, fechadateDEFAULTGETDATE(), montomoneynotnull,tipo_comprobantevarchar(20)notnull, num_comprobantevarchar(15)notnull, impuestodecimal(5,2)notnull)go

--AGREGARE UNA RESTRICCIN CHECK PARA SOLO INGRESAR FECHAS IGUALES O MENORESA LA FECHA ACTUALALTERTABLEpagoADDCONSTRAINTCHK_PAGO_FECHACHECK(fecha70go

5.Eliminar los registros de la tabla PAGO, que se han efectuado en el ao 2012 2013.

Tabla Pago

deletefrompagowhereyear(fecha)=2012oryear(fecha)=2013go

14. Back Up de Base de DatosUna copia de seguridad, copia de respaldo en tecnologas de la informacin es una copia de los datos originales que se realiza con el fin de disponer de un medio para recuperarlos en caso de su prdida. Los Backup son tiles ante distintos eventos y usos: Recuperar los datos de los sistemas informticos de una catstrofe informtica, natural o ataque. Restaurar una pequea cantidad de informacin que pueden haberse eliminado accidentalmente, corrompido.Restaurar Base de Datos

El proceso de copia de seguridad se complementa con otro conocido como restauracin de los datos (en ingls restore), que es la accin de leer y grabar en la ubicacin original u otra alternativa los datos requeridos que han sido respaldados con anterioridad.

15. Lenguaje de Manipulacin de Datos

COMANDO SELECT(Recuperacin de Registros):Uno de los propsitos de la Gestin de Base de Datos es almacenar informacin Lgica y ordenada dentro de tablas, usaremos la sentencia SELECT y sus variadas formas de recuperar informacin desde una tabla en la base de datos activa.

SintxisSELECT[ALL - DISTINCT] [TOP nmero [PERCENT] ] [FROM] tabla [WHERE ] [] [HAVING ] [ORDER BY columna [ASC-DESC]]

Clusulas PrincipalesALL:Especifica el conjunto de filas devueltas por la consulta.DISTINCT:Sentencia utilizada para mostrar filas nicas no repetidas.TOP:Para mostrar solo un nmero o porcentaje indicado de filas obtenidas.FROM:Permite indicar las filas que estn involucradas.WHERE:Clusula que permite condicionar el resultado de una consulta.GROUP BY:Permite agrupar un conjunto de registros en forma de resumen.HAVING:Permite condicionar el resultado despus de haber agrupado los registros.ORDER BY:Permite ordenar los registros de acuerdo a una columna espcfica.

EjemplosEjemplo 1:Mostrar los registros de la tabla PASAJERO , ordenarlos de manera ascendente por su apellido paterno.

Tabla Pasajero

--Seleccionando todas las filas y columnasselectall*fromPASAJEROorderbyapaternoascgo

--Seleccionando slo algunas columnas--1ra formaselectidpasajeroasCodigo,apaternoasApellidoPaterno,amaternoasApellidoMaternofromPASAJEROgo

--2da Formaselectidpasajeroas[Cdigo],apaternoas[Apellido Paterno],amaternoas[Apellido Materno]fromPASAJEROgo

--Concatenando los Apellidosselectidpasajeroas[Cdigo],apaternoasApellidoPaterno,amaternoasApellidoMaternofromPASAJEROgo

Clusula TOP:Especfica que el conjunto de filas devueltas por la consulta puede ser controlado en un nmero y en porcentaje. Considere que la muestra de registros devueltas siempre ser el primer conjunto de filas especificadas. Para especificar el porcentaje slo agregar la palabra PERCENT.

Ejemplo 2:Mostrar los 3 primeros registros de la tabla PASAJERO ordenados por su apellido paterno .

selecttop3 idpasajeroasID,nombreasNombre,apaternoasApellidoP,amaternoasApellidoMfrompasajeroorderbyapaternoascgo

Ejemplo 3:Mostrar los 3 ltimos registros de la tabla PASAJERO ordenados por ambos apellidos.

selecttop3 idpasajeroasID,nombreasNombre,apaternoasApellidoP,amaternoasApellidoMfrompasajeroorderbyapaternodesc,amaternodescgo

Ejemplo 4:Mostrar el 30% de registros de la tabla RESERVA.

Tabla Reserva

selecttop30percent*fromreservago

16. Comando Select

Clusula WhereClusula que permite condicionar el resultado de una consulta.

Ejemplos

Ejemplo 1:Script que permita mostrar los PASAJEROS cuyo tipo de documento sea DNI.

Tabla Pasajero

select*frompasajerowheretipo_documento='DNI'go

Ejemplo 2:Mostrar los pagos que se han realizado la fecha 27-01-2013.

Tabla Pago

selectfecha,idpasajero,montofrompagowherefecha='2013-1-27'go

Ejemplo 3:Mostrar los pagos realizados en el mes de mayo del ao 2014.

selectfecha,idpasajero,montofrompagowheremonth(fecha)='5'andyear(fecha)='2014'go

Ejemplo 4:Mostrar los Pasajeros que no tienen asignado un telfono.

selectnombre,apaterno,amaterno,telefonofrompasajerowheretelefonoisnullgo

Ejemplo 5:Implementar un Script que permita mostrar los PASAJEROS con su correspondiente pas de residencia.

selectpas.nombreasNombre,apaternoasApellidoP,amaternoasApellidoM,pai.nombreasPaisfrompasajero pas,pais paiwherepas.idpais=pai.idpaisgo

17. Operadores en SQL ServerUn Operador es un smbolo que especfica una accin que es realizada por una o ms expresiones. Categoras de Operadores: Aritmticos Asignacin LgicosA) Operadores Aritmticos+ Sumar :Operador de Suma Nmerica y concatenacin de Columnas.- Restar :Operador de resta Nmerica y tambin representa a nmeros negativos.* Multiplicar: Operador de Multiplicacin./ Dividir :Operador de Divisin entera y fraccionaria.% Mdulo :Operador que Devuelve el resto de una divisin.

B) Operador de AsignacinSql server solo cuenta con un operador para la asignacin de valores.El operador = se tendr que colocar en cualquier expresin que necesite asignar un valor de cualquier tipo.

Ejemplos:

Ejemplo 1:Script que permita mostrar el resultado de la expresin 2*(10+15) - (8/3), utilizar la sentencia SELECT para mostrar el resultado.

Select'2*(10+5) - (8/3)'as[Operacin],2*(10+5)-(8/3)as[Resultado]go

Ejemplo 2:Script que permita mostrar el resultado de la expresin Celsius=(Fharenheit-32)*5/9, usar la sentencia Select.

select'100'asFahrenheit,(100-32)*5/9asCelsiusgo

Ejemplo 3:

Calcular el rea de un Crculo, almacenando el valor del radio y de PI en sus variables respectivas.

declare@pifloatdeclare@radiofloatdeclare@areafloat--Asignar valoresset@pi=3.1415set@radio=7--Encontrar el rea del Crculoset@area=@pi*Power(@radio,2)

select@areaas[rea]go

Ejemplo 4:Script que permita capturar en una variable el correo electrnico del pasajero con cdigo P0000005, usar variables Transact-SQL y sentencia Select para mostrar el email.

declare@correovarchar(70)

select@correo=emailfrompasajerowhereidpasajero='P0000005'--Mostramo el correo obtenidoselect'P0000005'as[Cdigo],@correoas[Email]go

18. Lenguaje de Manipupacin de Datos

C) Operadores LgicosLos operadores lgicos tienen por misin comprobar la veracidad de Alguna Condicin, estos como los operadores de comparacin, devuelven el tipo de datos BOOLEAN (True, False, Unknown).

AND:Representa la Lgica Y, dos expresiones deben ser TRUE para poder devolver TRUE.ANY:Devuelve TRUE si alguna expresin del conjunto de expresiones es TRUE.

BETWEEN:Devuelve TRUE si el valor se encuentra dentro de un rango nmerico o cadena.EXISTS:Devuelve TRUE si una determinada subconsulta devuelve por lo menos una fila de registros.IN:Devuelve TRUE si el operando se encuentra dentro de una lista de valores especficos.NOT:Invierte el valor booleano de una expresinOR:Devuelve FALSE cuando ambas expresiones sean FALSE.SOME:Devuelve TRUE si de un conjunto de comparaciones alguna es TRUE.

Smbolos que representan a los operadores Lgicos, tenemos los siguientes:

=Igualdad de Expresiones !=Diferencia de Expresiones> >=Mayor / Mayor o Igual