• Author / Uploaded
• Richard Obregon

• Categories
• SQL
• Base de datos relacional
• Gestión de la información
• Modelo de datos
• Diseño de software

Citation preview

Base de Datos

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

PARTE I: MICROSOFT

SQL SERVER 2012

SQL (Lenguaje de consulta estructurado): Es un tipo de lenguaje vinculado con la gestión de bases de datos de carácter relacional que permite la especificación de distintas clases de operaciones. Gracias a la utilización del álgebra y del cálculo relacional, el lenguaje SQL brinda la posibilidad de realizar consultas que ayuden a recuperar información de las bases de datos de manera sencilla.

Características Algunas de las características de este lenguaje son: 

Compatible con todos los sistemas de bases de datos comerciales importantes.



Estandarizada - nuevas características en el tiempo.



Interactiva a través de interfaz gráfica de usuario o del sistema.



Declarativa, basada en álgebra relacional.

1

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

Lenguaje de descripción de datos (DDL) DDL (Lenguaje de descripción de datos) es un lenguaje que permite definir la base de datos (su estructura o “schemas”), tiene una sintaxis similar a los lenguajes de programación. Ejemplos de DDL: CREATE TABLE nombre_tabla; DROP TABLE nombre_tabla; ALTER TABLE nombre_tabla ADD id INTEGER;

Descripción de los comandos CREATE: 

Para crear una nueva base de datos, índice o almacenamiento de consultas.



Un argumento CREATE en SQL crea un objeto dentro del sistema de administración de la base de datos relacional (RDBMS).



El tipo de objetos que se pueden crear depende de qué RDBMS está siendo utilizado, pero la mayoría soporta la creación de tablas, índices, usuarios y bases de datos.



Algunos sistemas (tales como PostgreSQL) permiten CREATE y otros comandos DDL, dentro de transacciones, y por lo tanto pueden ser revertidos.

DROP: 

Para destruir una base de datos, tabla, índice o vista existente.



Un argumento DROP en SQL remueve un objeto dentro del sistema de administración de la base de datos relacional (RDBMS).



El tipo de objetos que se pueden eliminar depende de que RDBMS está siendo utilizado, pero la mayoría soporta la eliminación de tablas, índices, usuarios y bases de datos.



Algunos sistemas (tales como PostgreSQL) permiten DROP y otros comandos DDL, dentro de transacciones, y por lo tanto pueden ser revertidos.

ALTER:



Se utiliza para modificar la estructura de la tabla, como en los casos siguientes:

2

o

Añadir una columna.

o

Eliminar una columna.

o

Cambiar el nombre de una columna.

o

Cambiar el tipo de datos para una columna.

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

Lenguaje de manipulación de datos (DML) DML (Lenguaje de manipulación de datos) se refiere a los comandos que permiten a un usuario manipular los datos de las tablas, es decir, consultar tablas, añadir filas, borrar filas y actualizar columnas. Ejemplos de DML

SELECT atributo FROM nombre_tabla; INSERT INTO nombre_tabla (atributo_1,..., atributo_n) VALUES (dato_1,..., dato_n); DELETE FROM nombre_tabla WHERE condicion; UPDATE nombre_tabla SET atributo = nuevo dato WHERE condicion;

Descripción de comandos SELECT 

Devuelve el resultado de un conjunto de registros de una o más tablas.



Un argumento SELECT devuelve cero o más filas de una o más tablas de una base de datos o vistas de base de datos.



En la mayoría de las aplicaciones SELECT es el comando DML más usado.



Como SQL es un lenguaje de programación declarativo, consultas SELECT especifican el conjunto de resultado, pero no como calcularlo.



La base de datos traduce la consulta a un “plan de consulta”, que puede variar dependiendo de la ejecución, la versión de la base de datos y el software de base de datos.



Esta funcionalidad es llamada “optimizador de consulta”, puesto que es responsable de buscar el mejor plan de ejecución para la consulta, tomando en cuenta las restricciones aplicables.

Instrucción SELECT básica SELECT A1, ..., An FROM R1, ..., Rm WHERE condición Significado: 

SELECT A1,…,An Atributos que retorna



FROM R1,…,Rm Relaciones o tablas



WHERE condición

3

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA



Combinar, filtrar: Lo que busca esta consulta es mostrar las columnas A1,…,An de las tablas o relaciones R1,…,Rm, siguiendo alguna condición.

Álgebra relacional: πA1,…, An (σcondición (R1×…×Rm)) Comandos SQL 

INSERT : agrega uno o más registros a una tabla de una base de datos relacional.



DELETE : elimina uno o más registros de una tabla. Un subconjunto de datos puede ser eliminado si existe una condición, de lo contrario todos los registros serán eliminados.



UPDATE : cambia los datos de uno o más registros de una tabla. Una fila o un subconjunto de filas puede ser actualizadas utilizando una condición.

Lenguaje de descripción de datos (DDL) 1. SENTENCIA CREATE: Comando de creación de objetos de una base de datos.

Estructura: CREATE OBJETO NOMBREOBJETO ( --ESTRUCTURA DEL OBJETO ) CASO I.1.1: Implementar una consulta que permita crear la base de datos METROPILITANO con valores estándar. Así mismo generar un backup de la base de datos creada. CREATE DATABASE METROPOLITANO

Si en caso deseamos usar la base de datos METROPOLITANO, ejecutar el siguiente comando. USE METROPOLITANO GO

4

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

Para verificar los parámetros de creación de la base de datos METROPOLITANO, servicio local, se tiene que ejecutar el siguiente comando.

SP_HELPDB METROPOLITANO

Ahora veremos cuatro maneras de crear un backup de una base de datos en SQL SERVER.

A. La primera forma de generar una copia de seguridad de la base de datos es copiando los archivos con el nombre de la base de datos con extensión .mdf y LDF, en nuestro caso METROPOLITANO.mdf y METROPOLITANO_log.LDF, los mismos que se encuentran en la siguiente dirección:

C:\Program Files\Microsoft SQL Server\ MSSQL10_50.MSSQLSERVER\ MSSQL\DATA

Como podemos ver no nos permite realizar la copia de la base de datos METROPOLITANO, mostrando el siguiente mensaje.

5

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

Esto es debido a que la base de datos METROPOLITANO está siendo usada, para esto debemos ir al panel de control > herramientas administrativas > servicios, o simplemente ejecutar Inicio + R y escribir services.msn, como se muestra a continuación:

Ahora bien lo que haremos es detener el servicio SQL SERVER, ubicaremos el servicio de nombre SQL Server (MSSQLSERVER).

6

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

Luego podremos copiar los archivos de la base de datos METROPOLITANO, y poder usarla en otras máquinas, con servicio local SQL Server.

Para restaurar la base de datos METROPOLITANO, copiamos los archivos de la base de datos que queremos restaurar en la siguiente dirección:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\ MSSQL\DATA

7

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

Ahora bien iremos a la herramienta SQL SERVER y realizaremos lo siguiente.

Como podemos ver la base de datos METROPOLITANO se encuentra lista para ser usada.

8

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

B. La segunda forma de generar una copia de seguridad de una base de datos cualquiera en SQL Server es la siguiente:

En nuestro caso trabajaremos con la base de datos METROPOLITANO.

Ahora bien lo que haremos es cerrar el servicio solo de la base de datos que queremos salvar, para esto escribimos el siguiente comando SQL:

USE master GO SP_DETACH_DB 'METROPOLITANO' GO

Luego para restaurar la base de datos que queremos copiar en nuestro caso METROPOLITANO, lo que haremos es copiar a la siguiente dirección

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\ MSSQL\DATA

Para restaurar la base de datos desde el SQL Server ejecutaremos los mismos pasos del método anterior de restauración o en su defecto levantar el servicio de la base de datos con el siguiente comando:

USE master GO SP_ATTACH_DB 'METROPOLITANO', 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ METROPOLITADNO.mdf' GO

9

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

C. Para obtener un backup de la base de datos es generando un archivo de tipo .bak, de la siguiente forma, así mismo el backup generado tendrá la siguiente dirección:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\ MSSQL\Backup

10

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

Para restaurar el backup creado lo que haremos es ir a la siguiente opción y listo:

D. La otra forma de generar un backup es a nivel script:

11

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

Luego pasaremos a restaurar la base de datos METROPOLITANO seleccionando el archivo donde se encuentra el script de la base de datos la misma que lo abriremos desde SQL Server y pasaremos a ejecutar el script.

12

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

Caso I.1.2: Implementar un script que permita crear la tabla BOLETA dentro de la base de datos METROPOLITANO con las siguientes características:

BOLETA Nº 000000011515452 FECHA DE EMISION: 06/08/2012 MONTO: 10.00

USE METROPOLITANO GO

CREATE TABLE BOLETA ( numeroBoleta CHAR(15)

NOT NULL,

fechaEmision

DATE

NOT NULL,

monto

MONEY

NOT NULL

) GO ------------ LLENADO DE REGISTROS A LA TABLA BOLETA -----------INSERT INTO BOLETA VALUES ('00000001','22/04/2008', 200) INSERT INTO BOLETA VALUES ('00000002','02/01/2008', 350) INSERT INTO BOLETA VALUES ('00000003','03/10/2008', 250) INSERT INTO BOLETA VALUES ('00000004','01/05/2007', 150) INSERT INTO BOLETA VALUES ('00000005','07/12/2008', 200) INSERT INTO BOLETA VALUES ('00000006','16/08/2008', 120) INSERT INTO BOLETA VALUES ('00000007','03/01/2007', 50) INSERT INTO BOLETA VALUES ('00000008','05/03/2007', 200) INSERT INTO BOLETA VALUES ('00000009','14/09/2008', 10) INSERT INTO BOLETA VALUES ('00000010','18/12/2008', 50) INSERT INTO BOLETA VALUES ('00000011','09/11/2007', 100) INSERT INTO BOLETA VALUES ('00000012','06/10/2007', 80)

13

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

Caso I.1.3: Implementar un script que permita crear el TRIGGER TX_MENSAJE que muestre un mensaje al usuario cuando se realice una inserción o actualización a la tabla BOLETA.

CREATE TRIGGER TX_MENSAJE ON BOLETA FOR INSERT, UPDATE AS PRINT 'LA BOLETA SE ACTUALIZO CORRECTAMENTE' GO PROBAR EL TRIGGER Insertando un nuevo registro INSERT INTO BOLETA VALUES ('00000013','07/08/2012', 20.50) GO UPDATE BOLETA SET FECHAEMISION='07/08/2012', MONTO=20 WHERE NUMEROBOLETA=00000013 GO Caso I.1.4: Implementar un script que permita crear el procedimiento almacenado SP_TOTALBOLETAS de la tabla BOLETA. CREATE PROCEDURE SP_TOTALBOLETAS AS BEGIN SELECT YEAR (FECHAEMISION) AS [AÑO], COUNT (*) AS [TOTAL] FROM BOLETA GROUP BY YEAR (FECHAEMISION) END GO

14

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

Probar el Procedimiento Almacenado EXEC SP_TOTALBOLETAS

Caso

I.1.5:

Implementar

un

script

que

permita

crear

la

función

FN_TOTALBOLETAS de un determinado año ingresado por el usuario a la tabla BOLETA. CREATE FUNCTION FN_TOTALBOLETAS (@AÑO INT) RETURNS INT AS BEGIN DECLARE @TOTAL INT SELECT

@TOTAL=COUNT (*) FROM BOLETA WHERE YEAR (FECHAEMISION)=@AÑO GROUP BY YEAR (FECHAEMISION)

RETURN @TOTAL END Probando la función usando una consulta: SELECT DBO.FN_TOTALBOLETAS (2007) AS [TOTAL DE BOLETAS]

O usando el comando Print: PRINT 'EL TOTAL DE BOLETAS ES:+STR (DBO.FN_TOTALBOLETAS (2007))

Caso I.1.6: Implementar un script que permita crear la vista VBOLETAS donde muestre los registros contenidos en la tabla BOLETAS.

CREATE VIEW VBOLETA AS SELECT B.NUMEROBOLETA, B.FECHAEMISION, B.MONTO FROM BOLETA B GO

15

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

Probar la Vista SELECT * FROM VBOLETA

2. SENTENCIA ALTER: Comando de modificación de objetos de una base de datos.

Estructura: ALTER OBJETO NOMBREOBJETO ( --DATOS MODIFICADOS )

Caso I.2.1: Implementar un script que permita agregar un archivo secundario a la base de datos METROPOLITANO llamado METROPOLITANO_SEC2 de tamaño inicial 5MB y un tamaño máximo de 10MB con un factor de crecimiento de 2%.

SP_HELPDB METROPOLITANO ALTER DATABASE METROPOLITANO ADD FILE ( NAME='METROPOLITANO_SEC2', FILENAME='C:\Program Files\Microsoft SQL Server\ MSSQL10_50. MSSQLSERVER\MSSQL\DATA\METROPOLITANO_SEC2.NDF', SIZE=5MB, MAXSIZE=10MB, FILEGROWTH=2% )

16

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

Caso I.2.2: Implementar un script que permita modificar la precisión de la columna Número de Boleta asignada inicialmente con el valor 20 modificarlo por 25 de la tabla Boleta:

SP_COLUMNS BOLETA ALTER TABLE BOLETA ALTER COLUMN NUMEROBOLETA CHAR (25) NOT NULL GO

Caso I.2.3: Implementar una consulta que permita modificar el TRIGGER TX_MENSAJE que adicione al mensaje anterior la fecha de inserción o actualización en la tabla Boleta.

ALTER TRIGGER TX_MENSAJE ON BOLETA FOR INSERT, UPDATE AS PRINT 'LA BOLETA SE ACTUALIZO CORRECTAMENTE' PRINT 'FECHA: '+CAST (GETDATE () AS VARCHAR (25)) GO

INSERT INTO BOLETA VALUES ('00000014','01/07/2016', 36.56)

UPDATE BOLETA SET monto=40 WHERE numeroBoleta='00000014'

17

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

Caso I.2.4: Implementar un script que permita modificar el procedimiento almacenado SP_TOTALBOLETAS de la tabla BOLETA donde muestre la columna MES agrupando el número de boletas por cada mes y al final mostrar el total de boletas registradas.

ALTER PROCEDURE SP_TOTALBOLETAS AS BEGIN SELECT YEAR (FECHAEMISION) AS [AÑO], MONTH (FECHAEMISION) AS [MES], COUNT (*) AS [TOTAL] FROM BOLETA GROUP BY ROLLUP (YEAR (FECHAEMISION), MONTH (FECHAEMISION))

END GO

Caso I.2.5: Implementar una consulta que permita modificar la función FN_TOTALBOLETAS de un determinado año y un mes ingresado por el usuario a la tabla BOLETAS.

ALTER FUNCTION FN_TOTALBOLETAS (@AÑO INT,@MES INT) RETURNS INT AS BEGIN DECLARE @TOTAL INT SELECT

@TOTAL=COUNT (*) FROM BOLETA WHERE YEAR (FECHAEMISION)=@AÑO

18

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

AND MONTH (fechaEmision)=@MES GROUP BY YEAR (FECHAEMISION) RETURN @TOTAL END GO

SELECT DBO.FN_TOTALBOLETAS (2007,5) AS [TOTAL BOLETAS] SELECT *FROM BOLETA

DECLARE @AÑO INT=2012,@MES INT=8 PRINT 'EN EL MES: '+CAST (@MES AS CHAR (2))+ ' DEL AÑO: '+ CAST(@AÑO AS CHAR (4))+ ' EXISTEN: '+ CAST(DBO.FN_TOTALBOLETAS(@AÑO,@MES) AS VARCHAR (10))+' BOLETA(S)'

Caso I.2.6: Implementar una consulta que permita modificar la vista VBOLETAS donde muestre solo las columnas Numero de Boleta y Fecha de Emisión de la tabla BOLETAS, además de encriptar la información de la implementación.

ALTER VIEW VBOLETA --WITH ENCRYPTION AS SELECT B.NUMEROBOLETA, B.FECHAEMISION FROM BOLETA B GO

SELECT * FROM VBOLETA

19

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

3.

SENTENCIA DROP: Comando que elimina un objeto de una base de datos

Estructura: DROP OBJETO NOMBREOBJETO

Caso I.3.1: Implementar un script que permita eliminar la base de datos METROPOLITANO.

USE MASTER GO

DROP DATABASE METROPOLITANO GO

Caso I.3.2: Implementar una consulta que permita eliminar la tabla boleta de la base de datos METROPOLITANO. DROP TABLE BOLETA GO

Caso I.3.3: Implementar un script que permita eliminar el trigger TX_MENSAJE asociado a la tabla BOLETA de la base de datos METROPOLITANO.

DROP TRIGGER TX_MENSAJE

Inhabilitar el Trigger TX_MENSAJE Disable Trigger 'TX_MENSAJE' ON DATABASE

DISABLE TRIGGER 'TX_MENSAJE'

20

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

PARTE 02: GESTIÓN CASO PRÁCTICO: RESERVA

DE BASE DE DATOS

DE VUELOS

El sistema de reserva de vuelos es un sistema que permite al usuario hacer consultas y reservas de vuelos, además de poder comprar los boletos de viajes de forma remota, sin la necesidad de recurrir a una agencia de viajes. Se desea implementar un sistema de reservas que sea accesible vía web. El sistema actualmente cuenta con un terminal de servicio de reserva en donde se presenta un mensaje de bienvenida al sistema, describiendo los servicios ofrecidos junto con la opción para registrarse si es la primera vez que accede a ella o caso contrario usar el sistema de reserva de vuelos. Este acceso se da por medio de un usuario (email) y una clave previamente registrada en el sistema. Una vez registrado el usuario este podrá seleccionar los siguientes procesos: 

Consulta de vuelos



Reserva de vuelos



Compra de boletos aéreos

La consulta de vuelos se puede realizar de las siguientes formas: 

Horarios de vuelos



Tarifas de vuelos



Información de vuelos

21

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

La consulta según el horario muestra los horarios de las diferentes aerolíneas que dan el servicio entre dos ciudades. La consulta según tarifa muestra los diferentes vuelos entre dos ciudades ordenados por su costo. La información de vuelos se usa para consultar el estado de un determinado vuelo para el mismo día, si este está en hora. Se pueden incluir preferencias en las búsquedas como fecha y horario deseado, categoría del vuelo, aerolínea deseada y si ocasionalmente desean vuelos directos.

La reserva de vuelo permite al usuario hacer una reserva para un vuelo particular, especificando la fecha y horario, bajo una tarifa establecida. Es posible reservar un itinerario compuesto de múltiples vuelos, para uno o más pasajeros, además de poder reservar asientos. La compra permite al usuario, dada una reserva de vuelo y un número de tarjeta de crédito validada, adquirir los boletos aéreos. Los boletos aéreos serán posteriormente enviados al usuario o estarán listos para ser adquiridos en el terminal del aeropuerto antes de la salida de su vuelo. Es necesario estar previamente registrado con un número de tarjeta de crédito válida para poder hacer compras de boletos aéreos, o bien proveerla en el momento de la compra. Además de los servicios de vuelo, el usuario podrá en cualquier momento leer, modificar o cancelar su propio registro, todo esto después de haber sido validado como usuario del sistema.

A continuación se muestra el Modelo Entidad Relación del caso propuesto, así como también se muestra las capacidades de cada columna.

22

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

23

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

24

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

CASO: II.1.1. Implementar la base de datos AGENCIA con valores por defecto.

1ERA FORMA CREATE DATABASE AGENCIA GO 2DA FORMA IF DB_ID('AGENCIA') IS NOT NULL BEGIN USE master DROP DATABASE AGENCIA END CREATE DATABASE AGENCIA GO PARA

VERIFICAR LA EXISTENCIA DE LA BD AGENCIA

SELECT * FROM SYS.SYSDATABASES OTRA

FORMA DE VERIFICAR

SELECT name,dbid,crdate,filename FROM SYS.SYSDATABASES VERIFICAMOS

LA ESTRUCTURA DE LA BD AGENCIA

SP_HELPDB AGENCIA

25

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

CASO: II.1.2. Implementar la base de datos AGENCIA2 ubicado en la carpeta D:\Base_Datos\especificando un archivo primario con tamaño inicial de 15MB, un tamaño máximo de 30MB y un crecimiento de 5MB, además el archivo de transacciones debe tener un tamaño inicial de 5MB y como máximo 10MB a una tasa de crecimiento de 10%.

CREATE DATABASE AGENCIA2 ON PRIMARY( NAME='AGENCIA2_PRI', FILENAME='D:\Base_Datos\AGENCIA2.MDF', SIZE=15MB, MAXSIZE=30MB, FILEGROWTH=5MB ) LOG ON( NAME='AGENCIA2_LOG', FILENAME='D:\Base_Datos\AGENCIA2.LDF', SIZE=5MB, MAXSIZE=10MB, FILEGROWTH=10% ) GO

SELECT * FROM SYS.SYSDATABASES SELECT name,dbid,crdate,filename FROM SYS.SYSDATABASES SP_HELPDB AGENCIA2

26

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

CASO: II.1.3. Implementar la base de datos AGENCIA3 ubicado en la carpeta D:\Base_Datos\ con la siguiente configuración: 

Archivo de datos: Un tamaño inicial de 10MB, máximo de 40MB y un factor de crecimiento de 5MB.



Archivo secundario: Un tamaño inicial de 5MB, máximo de 30MB y un factor de crecimiento de 5%.



Archivo de transacciones: Un tamaño inicial de 4MB, máximo de 20MB y un factor de crecimiento de 10%. CREATE DATABASE AGENCIA3 ON PRIMARY ( NAME='AGENCIA3_PRI', FILENAME='D:\Base_Datos\AGENCIA3.MDF', SIZE=10MB, MAXSIZE=40MB, FILEGROWTH=5MB ), ( NAME='AGENCIA3_SEC', FILENAME='D:\Base_Datos\AGENCIA3.NDF', SIZE=5MB, MAXSIZE=30MB, FILEGROWTH=5% ) LOG ON ( NAME='AGENCIA3_LOG', FILENAME='D:\Base_Datos\AGENCIA3.LDF', SIZE=4MB, MAXSIZE=20MB, FILEGROWTH=10% )

27

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

GO

SP_HELPDB AGENCIA3

CASO: II.1.4. Implementar la base de datos AGENCIA4 ubicado en la carpeta D:\Base_Datos\ con la siguiente configuración: 

Archivo de datos: Un tamaño inicial de 10MB, máximo de 40MB y un factor de crecimiento de 5MB.



Archivo secundario1: Un tamaño inicial de 5MB, máximo de 30MB y un factor de crecimiento de 5%.



Archivo secundario2: Un tamaño inicial de 2MB, máximo de 15MB y un factor de crecimiento de 2%.



Archivo de transacciones: Un tamaño inicial de 4MB, máximo de 20MB y un factor de crecimiento de 10%.

CREATE DATABASE AGENCIA4 ON PRIMARY ( NAME='AGENCIA4_PRI', FILENAME='D:\Base_Datos\AGENCIA4.MDF', SIZE=10MB, MAXSIZE=40MB, FILEGROWTH=5MB ), ( NAME='AGENCIA4_SEC1', FILENAME='D:\Base_Datos\AGENCIA41.NDF', SIZE=5MB, MAXSIZE=30MB, FILEGROWTH=5% ),

28

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

( NAME='AGENCIA4_SEC2', FILENAME='D:\Base_Datos\AGENCIA42.NDF', SIZE=2MB, MAXSIZE=15MB, FILEGROWTH=2% ) LOG ON ( NAME='AGENCIA4_LOG', FILENAME='D:\Base_Datos\AGENCIA4.LDF', SIZE=4MB, MAXSIZE=20MB, FILEGROWTH=10% ) GO

SP_HELPDB AGENCIA4

CASO: II.1.5. Implementar una consulta que permita separar la base de datos AGENCIA5 ubicado en la carpeta D:\Base_Datos\ con las siguientes características: 

Archivo de datos: Un tamaño inicial de 10MB, máximo de 90MB y un factor de crecimiento de 5MB.



Archivo secundario: Un tamaño inicial de 5MB, máximo de 50MB y un factor de crecimiento de 5%.



Archivo de transacciones: Un tamaño inicial de 5MB, máximo de 50MB y un factor de crecimiento de 10%.

29

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

CREATE DATABASE AGENCIA5 ON PRIMARY ( NAME='AGENCIA5_PRI', FILENAME='D:\Base_Datos\AGENCIA5.MDF', SIZE=10MB, MAXSIZE=90MB, FILEGROWTH=5MB ), ( NAME='AGENCIA5_SEC', FILENAME='D:\Base_Datos\AGENCIA5.NDF', SIZE=5MB, MAXSIZE=50MB, FILEGROWTH=5% ) LOG ON ( NAME='AGENCIA5_LOG', FILENAME='D:\Base_Datos\AGENCIA5.LDF', SIZE=5MB, MAXSIZE=50MB, FILEGROWTH=10% ) GO  PARA SEPARAR LA BD AGENCIA5

USE master GO SP_DETACH_DB AGENCIA5

30

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

 PARA SEPARAR LA BD AGENCIA5

USE master GO SP_DETACH_DB AGENCIA5  PARA ADJUNTAR LA BD AGENCIA5

USE master GO SP_ATTACH_DB AGENCIA5,'D:\Base_Datos\AGENCIA5.MDF'  PARA VERIFICAR SU ESTRUCTURA DE BD AGENCIA5

SP_HELPDB AGENCIA5

SQL, brinda una serie de datos para almacenar la información, la correcta selección del tipo de dato implica un determinado valor; por ejemplo: carácter, entero, binario, fecha, etc., así mismo algunas veces necesitamos algún tipo de dato en especial, como el caso del DNI que cuenta con 8 caracteres de tipo CHAR o el número de celular de 9 caracteres.

Para esto pasaremos a definir la sintaxis para crear nuestro propio tipo de dato. SP_ADDTYPE NOMBRE TIPO, ‘TIPO_DATOS’,’RESTRICCION’ GO Ahora bien si en caso se desea eliminar un tipo de dato creado por el usuario, se debe seguir la siguiente sintaxis, teniendo en cuenta que para eliminar un tipo de dato, este no debe tener objetos asociados. SP_DROPTYPE ‘NOMBRE_TIPO’ GO

31

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

CASO: II.1.6. Implemente una consulta que permita añadir el tipo de dato DNI dentro de la base de datos agencia5, además restringir que no debe contener espacios vacíos.  01 USE AGENCIA5 GO  02 SP_ADDTYPE DNI, ‘CHAR (8)','NOT NULL' GO  03 SELECT S.name, S.xtype, S.length FROM SYS.systypes S

CASO: II.1.7. Implemente una consulta que permita crear la tabla llamada PASAJERO, en la cual deberá hacer referencia al nuevo tipo de dato creado en la consulta anterior.

Para implementar una tabla se tiene la siguiente sintaxis:

CREATE TABLE PROPIETARIO.NOMBRE_TABLA ( CAMPO1 TIPO_DATO RESTRICCION, CAMPO2 TIPO_DATO RESTRICCION, CAMPO3 TIPO_DATO RESTRICCION ) GO

32

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

Dentro de las restricciones podemos tener NULL, NOT NULL y IDENTITY, lo cual implica que para dar consistencia al valor que se registrara en la columna puede ser nulo, no nulo, por defecto, primario, incremental, etc.  01 CREATE TABLE PASAJERO ( IDPASAJERO CHAR (5) NOT NULL, NOMBRES VARCHAR (50) NOT NULL, DNI CHAR (9) NOT NULL, )  02 SP_COLUMNS PASAJERO

CASO: II.1.8. Implemente una consulta que permita crear la tabla llamada PAGO, que tiene las columnas número de pago de tipo entero con un autoincremento de inicio 1 con salto de 1, fecha de tipo dato NO NULO y monto de tipo MONEY NO NULO.  01 CREATE TABLE PAGO ( NUMPAGO INT IDENTITY (1, 1), FECHA DATE NOT NULL, MONTO MONEY NOT NULL )  02 SP_COLUMNS PAGO

33

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

CASO: II.1.9. Implemente una consulta que permita crear la tabla llamada PASAJERO, con las siguientes especificaciones mostradas a continuación:

IDPASAJERO

CHAR (5)

NOMBRES

VARCHAR (50)

IDPAIS

CHAR (4)

TELEFONO

CHAR (5)

EMAIL

VARCHAR (50)

CREATE TABLE PASAJERO ( IDPASAJERO

CHAR (5)

NOT NULL,

NOMBRES

VARCHAR (50)

NOT NULL,

IDPAIS

CHAR (4)

NOT NULL,

TELEFONO

CHAR (15)

NOT NULL,

EMAIL

VARCHAR (50)

NOT NULL

) GO

Tener en cuenta que la creación de la tabla PASAJERO no agrega restricciones, ni campos claves; así mismo como propietario de la tabla se está usando DBO. Ahora bien es importante manejar ESQUEMAS en

SQL, es ingles SCHEMA, describe

la estructura de la misma ya que todos los objetos que se puedan crear o administrar en SQL se encuentran dentro de un mismo esquema (DBO).

El manejo de esquemas tiene que ver con temas de agrupación de objetos y seguridad de las mismas. Cuando se crea un objeto de base de datos o se especifica una entidad de seguridad de dominio valida usuarios o grupos como el propietario del objeto, o la entidad de seguridad de dominio se agregará a la base de datos como esquema. Su sintaxis de creación es:

34

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

CREATE SCHEMA NOMBRE_ESQUEMA AUTHORIZATION NOMBRE_PROPIETARIO.

Su sintaxis de eliminación es:

DROP TABLE NOMBRE_TABLA GO DROP SCHEMA NOMBRE_ESQUEMA AUTHORIZATION NOMBRE_PROPIETARIO GO

CASO: II.1.10. Implementar los esquemas RRHH, CONTABILIDAD, PAGO dentro de la base de datos AGENCIA5.  01 USE AGENCIA5 GO

 02

CREATE SCHEMA RRHH AUTHORIZATION DBO CREATE SCHEMA CONTABILIDAD AUTHORIZATION DBO CREATE SCHEMA PAGO AUTHORIZATION DBO  03

SELECT S.NAME FROM SYS.SCHEMAS S

35

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

CASO: II.1.11. Implementar los esquemas RESERVACIONES, LOGISTICA y CLIENTES sobre la base de datos AGENCIA5. Así mismo deberá distribuir los esquemas de la siguiente manera:

RESERVACIONES 

VUELO



ASIENTO



TARIFA



RESERVA



PAGO

LOGISTICA 

AVION



AEROPUERTO



AEROLINEA



PAIS

CLIENTES 

PASAJERO

Luego deberá crear las tablas antes mencionadas haciendo referencia a sus respectivos esquemas.  01 USE AGENCIA3 GO

36

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

 02

CREATE SCHEMA RESERVACIONES AUTHORIZATION DBO GO CREATE SCHEMA LOGISTICA AUTHORIZATION DBO GO CREATE SCHEMA CLIENTES AUTHORIZATION DBO GO  03

CREATE TABLE RESERVACIONES.VUELO ( NUMVUELO

INT

NOT NULL,

IDAERO

CHAR (5)

NOT NULL,

IDRESERVA

INT

NOT NULL,

IDAVION

CHAR (5)

NOT NULL,

CLASE

VARCHAR (20)

NOT NULL

) GO

CREATE TABLE RESERVACIONES.ASIENTO ( NUMVUELO

INT

NOT NULL,

LETRA

CHAR (2)

NOT NULL,

FILA

INT

NOT NULL

) GO

37

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

CREATE TABLE RESERVACIONES.TARIFA ( CLASE

VARCHAR (20)

NOT NULL,

PRECIO

MONEY

NOT NULL,

IMPUESTO

MONEY

NOT NULL

) GO

CREATE TABLE RESERVACIONES.RESERVA ( IDRESERVA

INT

IDENTITY NOT NULL,

COSTO

MONEY

DEFAULT 0,

FECHA

DATE

DEFAULT GETDATE ()

) GO

CREATE TABLE RESERVACIONES.PAGO ( NUMPAGO

INT

NOT NULL,

IDRESERVA

INT

NOT NULL,

IDPASAJERO

CHAR (5)

NOT NULL,

FECHA

DATE

NOT NULL,

MONTO

MONEY

NOT NULL

) GO

CREATE TABLE LOGISTICA.AVION ( IDAVION

CHAR (5)

NOT NULL,

RUC

CHAR (11)

NOT NULL,

COMPAÑIA

VARCHAR (40)

NOT NULL,

TIPO

VARCHAR (30)

NOT NULL,

PASAJEROS

INT

NOT NULL

) GO

38

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

CREATE TABLE LOGISTICA.AEROLINEA ( RUC

CHAR (11)

NOT NULL,

NOMBRE

VARCHAR (40)

NOT NULL

) GO

CREATE TABLE LOGISTICA.AEROPUERTO ( IDAERO

CHAR (5)

NOT NULL,

NOMBRE

VARCHAR (40)

NOT NULL,

IDPAIS

CHAR (4)

NOT NULL

) GO

CREATE TABLE LOGISTICA.PAIS ( IDPAIS PAIS

CHAR (4)

NOT NULL,

VARCHAR (30)

NOT NULL

) GO

CREATE TABLE CLIENTES.PASAJERO ( IDPASAJERO

CHAR (5)

NOT NULL,

NOMBRES

VARCHAR (50)

NOT NULL,

IDPAIS

CHAR (4)

NOT NULL,

TELEFONO

CHAR (15)

NOT NULL,

EMAIL

VARCHAR (50)

NOT NULL

) GO

39

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

DEFINICION DE LAS LLAVES PRIMARIAS Y FORANEAS: Es un tipo de restricción estructural que permite la vinculación de los datos a otras tablas y hacer obligatoria la consistencia de las mismas. Sintaxis de asignación de llaves primarias:

ALTER TABLE NOMBRE_TABLA ADD PRIMARY KEY NONCLUSTERED (COLUM1, COLUM2…)

Sintaxis de asignación de llaves foráneas:

ALTER TABLE NOMBRE_TABLA ADD FOREIGN KEY (COLUMNA) REFERENCES TABLA_ASOCIADA (COLUMNA_ASOCIADA)

CASO: II.1.12. Implementar una consulta que permita añadir la restricción estructural campo clave a la columna IDPASAJERO de la tabla PASAJERO.  01 USE AGENCIA3 GO  02 CREATE TABLE CLIENTES.PASAJERO ( IDPASAJERO

CHAR (5)

NOT NULL,

NOMBRES

VARCHAR (50)

NOT NULL,

IDPAIS

CHAR (4)

NOT NULL,

TELEFONO

CHAR (15)

NOT NULL,

EMAIL

VARCHAR (50)

NOT NULL

) GO

40

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

 03

ALTER TABLE CLIENTES.PASAJERO ADD PRIMARY KEY NONCLUSTERED (IDPASAJERO)

OTRA FORMA:

CREATE TABLE CLIENTES.PASAJERO ( IDPASAJERO

CHAR (5)

PRIMARY KEY NOT NULL,

NOMBRES

VARCHAR (50)

NOT NULL,

IDPAIS

CHAR (4)

NOT NULL,

TELEFONO

CHAR (15)

NOT NULL,

EMAIL

VARCHAR (50)

NOT NULL

) GO

CASO: II.1.13. Implementar una consulta que permita añadir la restricción estructural entre las columnas IDPAIS de la tabla PASAJERO asociado a la tabla PAIS.  01 USE AGENCIA3 GO  02 CREATE TABLE LOGISTICA.PAIS ( IDPAIS

CHAR (4)

NOT NULL,

PAIS

VARCHAR (30)

NOT NULL

) GO  03 ALTER TABLE LOGISTICA.PAIS ADD PRIMARY KEY NONCLUSTERED (IDPAIS)

41

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

 04 ALTER TABLE CLIENTES.PASAJERO ADD FOREIGN KEY (IDPAIS) REFERENCES LOGISTICA.PAIS (IDPAIS)

RESTRICCIONES DE LOS CAMPOS: UNIQUE, CHECK Y DEFAULT:

UNIQUE: Determina que los valores registrados en una misma columna no sean idénticos, es decir, se mantengan únicos. La restricción de UNIQUE permite controlar cualquier otro campo que no sea llave. Así mismo tener en cuenta que la columna definida como UNIQUE acepta a lo más un valor NULL mientras que una columna restringida con PRIMARY KEY no permite dicha acción.

CASO: II.1.14. Implementar una consulta que permita añadir la restricción UNIQUE a la columna PAIS, es decir solo permita registrar nombre de países sin repetirse.  01 USE AGENCIA5 GO  02 SELECT * FROM DBO.PAIS;  03 ALTER TABLE DBO.PAIS ADD PRIMARY KEY NONCLUSTERED (IDPAIS) GO;  04 ALTER TABLE DBO.PASAJERO ADD FOREIGN KEY (IDPAIS) REFERENCES LOGISTICA.PAIS (IDPAIS) GO;

42

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

 PRIMERA FORMA DE USAR UNIQUE

ALTER TABLE DBO.PAIS ADD CONSTRAINT NOM_PAIS UNIQUE (PAIS) GO;  INSERTANDO

INSERT INTO DBO.PAIS VALUES('1','ESTADOS UNIDOS'); INSERT INTO DBO.PAIS VALUES('2','ESTADOS UNIDOS');

CHECK: Permite restringir el rango de valores que pueden estar permitidos ingresar en una o más columnas de una tabla.

CASO: II.1.15. Implementar una consulta que permita añadir la restricción a la columna FECHA de la tabla PAGO en donde solo permitirá registrar fechas menores o iguales al día actual.  OTRA FORMA DE USAR UNIQUE

CREATE TABLE PAIS( IDPAIS CHAR(4) PAIS

PRIMARY KEY NOT NULL,

VARCHAR(30) NOT NULL UNIQUE

) GO  VER LAS COLUMNAS

SP_COLUMNS PAGO;  USANDO LA CONSULTA CHECK

ALTER TABLE DBO.PAGO ADD CONSTRAINT CHK_FECHA CHECK(FECHA=0 AND MONTO0; GO

INSERT INTO RESERVA_TEMP_TEMP (COSTO,FECHA) SELECT TOP 5 COSTO,FECHA FROM RESERVA_TEMP WHERE COSTO>0; GO

CASO III.1.8. Una tabla temporal para pasajero e ingresar solo los pasajeros que tengan solo gmail

CREATE TABLE PASAJERO_TEMP( IDPASAJERO

CHAR (5)

NOT NULL PRIMARY KEY,

NOMBRES

VARCHAR (50) NOT NULL,

IDPAIS

CHAR (4),

TELEFONO

VARCHAR (10),

EMAIL

VARCHAR (30)

) GO

53

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

DROP TABLE PASAJERO_TEMP GO

SELECT * FROM PASAJERO WHERE (EMAIL LIKE '%GMAIL%') GO

INSERT INTO PASAJERO_TEMP (IDPASAJERO,NOMBRES,IDPAIS,TELEFONO,EMAIL) SELECT IDPASAJERO,NOMBRES,IDPAIS,TELEFONO,EMAIL FROM PASAJERO WHERE(EMAIL LIKE '%GMAIL%') GO

CASO III.1.9. Implementar una consulta que permita insertar un registro a la tabla PASAJERO por medio de una variable temporal de tipo table

DECLARE @TEMP_PASAJEROS TABLE(ID CHAR(5) , NOM VARCHAR(50), IDP CHAR(4), TELE CHAR(15), EMAIL VARCHAR(50)) INSERT PASAJERO OUTPUT INSERTED.IDPASAJERO, INSERTED.NOMBRES, INSERTED.IDPAIS, INSERTED.TELEFONO, INSERTED.EMAIL INTO @TEMP_PASAJEROS VALUES ('P0011','LENIN ELIO','0001','123456789','[email protected]') GO

54

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

SELECT * FROM RESERVA GO

COMO USAR LA TABLA RESERVA Y VALORES POR DEFECTO

DECLARE @TEMP_RESERVA TABLE (ID INT, COST MONEY, FEC DATE) INSERT RESERVA OUTPUT INSERTED.IDRESERVA, INSERTED.COSTO, INSERTED.FECHA INTO @TEMP_RESERVA DEFAULT VALUES GO

SELECT * FROM RESERVA GO

CASO III.1.10. Implementar una consulta que permita registrar aviones a una nueva tabla temporal llamada MIS AVIONES, usar la instruccion with

CREATE TABLE MISAVIONES ( ID

CHAR (5),

NIF

CHAR (11),

COMP VARCHAR (40), TIP

VARCHAR (30),

PASAJ

INT

) GO

55

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

WITH AVION_TEMP (IDA,NIF1,COMP1,TIP1,PAS) AS ( SELECT * FROM AVION ) INSERT INTO MISAVIONES SELECT * FROM AVION_TEMP GO

SELECT * FROM MISAVIONES;  PARA RESERVA

CREATE TABLE MISRESERVAS( ID

INT,

COST MONEY, FECH DATE ) GO

WITH RESERVA_TEMP(ID, COST, FECH) AS ( SELECT * FROM RESERVA ) INSERT INTO MISRESERVAS SELECT * FROM RESERVA_TEMP GO

SELECT * FROM MISRESERVAS GO

56

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

MODIFICACION Y ACTUALIZACION DE DATOS DE UNA TABLA CON UPDATE

La sentencia UPDATE permite mof¿dificar o actualizar un conjunto de registros de una tabla o vista, el mismo que se encuentra en funcion a una condición. Sintaxis UPDATE TABLA_O_VISTA SET {COLUMNA1=VALOR1, COLUMNA2=VALOR2,… COLUMNAn=VALORn} WHERE {CONDICIONES}

Caso III.1.11. Implementar una consulta que permita actualizar los valores de la columna impuesto por el valor 11 a todos los registros.

UPDATE TARIFA SET IMPUESTO=11 GO

Caso III.1.12. Implementar una consulta que permita actualizar los valores de la columna impuesto aumentando en 2 a todos los registros de la tabla tarifa.

UPDATE TARIFA SET IMPUESTO+=2 GO

Caso III.1.13. Implementar una consulta

que permita asignar el impuesto a cero solo a

sólo a los registros cuya clase sea Económico de la tabla tarifa.

UPDATE TARIFA SET IMPUESTO=0

57

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

WHERE CLASE='ECONOMICO' GO Caso III.1.14. Implementar una consulta que permita actualizar los costos de la tabla RESERVA disminuyendo en 50 a los registros cuyo ingreso se realizó el año 2013, utilizar variables.

UPDATE RESERVA SET COSTO=COSTO - 50 WHERE YEAR (FECHA) = 2013 GO Caso III.1.15. Implementar una consulta que asigne el texto ‘SIN FONO’ en el campo teléfono de los pasajeros que sean de Perú. Todo esto deberá ser realizado en la tabla Pasajero. Usar subconsultas. UPDATE PASAJERO SET TELEFONO = 'SIN FONO' WHERE IDPAIS= (SELECT IDPAIS FROM PAIS WHERE NOMBRE='PERÚ')

GO

ELIMINACIÓN DE REGISTROS DE UNA TABLA CON DELETE

La sintaxis correcta es la siguiente: DELETE [TOP (EXPRESSION)] FROM TABLE [WHERE {}] Caso III.1.16. Script que permita eliminar todos los registros de la tabla AEROLINEA  01 DELETE AEROLINEA  02

58

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

TRUNCATE TABLE AEROLINEA Caso III.1.17. Script que permita eliminar el registro de la tabla PASAJERO cuyo país sea ECUADOR usando subconsultas para el proceso. SELECT * FROM PASAJERO WHERE IDPAIS = (SELECT IDPAIS FROM PAIS WHERE PAIS='ECUADOR')

GO

Caso III.1.18. Implementar una consulta que permita eliminar el 5% de los registros de la tabla pasajero, cuyo pais sea Brasil. Usar subconsultas.

DELETE TOP (5) PERCENT FROM PASAJERO WHERE IDPAIS = ( SELECT IDPAIS FROM PAIS WHERE PAIS='BRASIL' ) GO Caso III.1.19. Implemetar una consulta que permita eliminar el 5% de los registros de la tabla pasajero cuyo pais sea brasil, usar subconsultas para el proceso y el operador OUTPUT.

DECLARE @MISPASAJEROS TABLE ( IDPAS

CHAR (5),

NOMBR

VARCHAR (40),

TELEF

CHAR (15)

)

DELETE PASAJERO OUTPUT

deleted.IDPASAJERO, deleted.NOMBRES,

59

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

deleted.TELEFONO INTO @MISPASAJEROS FROM PASAJERO P WHERE IDPAIS = ( SELECT IDPAIS FROM PAIS WHERE PAIS='BRASIL')

SELECT IDPAS, NOMBR, TELEF FROM @MISPASAJEROS GO

DECLARACION GENERAL DEL COMANDO

SELECT PARA LA RECUPERACION

DE REGISTROS.

 USO DEL SELECT.

 01 SELECT * FROM PASAJERO  02 SELECT ALL * FROM PASAJERO  03 SELECT IDPASAJERO,NOMBRES,IDPAIS,TELEFONO,EMAIL FROM PASAJERO  04 SELECT NOMBRES,EMAIL,TELEFONO FROM PASAJERO GO  05 SELECT

IDPASAJERO AS CODIGO,

NOMBRES AS 'PASAJERO', IDPAIS AS 'CODIGO PAIS',

60

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

TELEFONO, EMAIL AS 'CORREO ELECTRONICO' FROM PASAJERO  06 SELECT IDPASAJERO AS [CODIGO], NOMBRES AS [PASAJERO], IDPAIS AS [CODIGO PAIS], TELEFONO, EMAIL AS [CORREO ELECTRONICO] FROM PASAJERO  07 SELECT IDPASAJERO [CODIGO], NOMBRES [PASAJERO], IDPAIS [CODIGO PAIS], TELEFONO, EMAIL [CORREO ELECTRONICO] FROM PASAJERO  08 SELECT CODIGO=IDPASAJERO, PASAJERO=NOMBRES, [CODIGO PAIS]=IDPAIS, TELEFONO, [CORREO ELECTRONICO]=EMAIL FROM PASAJERO

61

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

 09 SELECT P.IDPASAJERO, P.NOMBRES, P.IDPAIS,P.TELEFONO,P.EMAIL FROM PASAJERO P  10 SELECT PASAJERO.IDPASAJERO, PASAJERO.NOMBRES, PASAJERO.IDPAIS,PASAJERO.TELEFONO,PASAJERO.EMAIL FROM PASAJERO

FORMAS DE USAR EL DISTINCT

 01 SELECT IDPAIS FROM PASAJERO  02 SELECT DISTINCT IDPAIS FROM PASAJERO  USO DEL ORDER BY

 01 SELECT P.* FROM PASAJERO P ORDER BY P.NOMBRES  02 SELECT P.* FROM PASAJERO P ORDER BY P.NOMBRES ASC

62

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

 03 SELECT P.IDPAIS,IDPASAJERO,P.NOMBRES,P.TELEFONO,P.EMAIL FROM PASAJERO P ORDER BY P.IDPAIS  04 SELECT P.IDPAIS,IDPASAJERO,P.NOMBRES,P.TELEFONO,P.EMAIL FROM PASAJERO P ORDER BY P.IDPAIS, P.IDPASAJERO DESC  05 SELECT P.IDPAIS,P.NOMBRES,P.IDPASAJERO,P.TELEFONO,P.EMAIL FROM PASAJERO P ORDER BY P.IDPAIS, P.NOMBRES DESC  USO DEL TOP

 01 SELECT TOP 5 * FROM PASAJERO  02 SELECT TOP 5 * FROM PASAJERO ORDER BY IDPASAJERO DESC  03, CONSULTA QUE MUESTRA LOS 5 ULTIMOS REGISTROS DE LA TABLA PASAJERO.

SELECT TOP 5 * FROM (SELECT ROW_NUMBER() OVER (ORDER BY P.IDPASAJERO DESC) AS [NUMERO],*

63

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

FROM PASAJERO P) X ORDER BY IDPASAJERO DESC CONSULTA QUE MUESTRA EL 30% DE LOS REGISTROS.

SELECT TOP 30 PERCENT * FROM RESERVA  CONSULTA QUE PERMITE REPLICAR LOS REGISTROS DE LA TABLA PASAJERO, EN UNA NUEVA VENTANA LLAMADA MIS PASAJEROS, USANDO LA CLAUSULA INTO.

 01 IF OBJECT_ID('MISPASAJEROS') IS NOT NULL BEGIN DROP TABLE MISPASAJEROS END GO  02 SELECT P.* INTO MISPASAJEROS FROM PASAJERO P GO  01 IF OBJECT_ID('MISPASAJEROS') IS NOT NULL BEGIN DROP TABLE MISPASAJEROS END GO  02 SELECT P.* INTO MISPASAJEROS

64

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

FROM PASAJERO P WHERE P.EMAIL LIKE '%HOTMAIL%' GO  03 SELECT * FROM MISPASAJEROS GO CONSULTA QUE PERMITE MOSTRAR LOS REGISTROS DE LAS TABLAS PASAJERO Y PAIS COMBINADOS.

 01 SELECT PAS.*,PAI.* FROM PASAJERO PAS,PAIS PAI  02 SELECT PAS.*,PAI.* FROM PASAJERO PAS,PAIS PAI WHERE PAS.IDPAIS=PAI.IDPAIS  03 SELECT PAI.*,PAS.* FROM PASAJERO PAS,PAIS PAI WHERE PAS.IDPAIS=PAI.IDPAIS CONSULTA QUE PERMITE MOSTRAR LOS REGISTROS DE LOS PASAJEROS CUYO PAIS REGISTRADO SEA ECUADOR, USANDO SUBCONSULTAS.

 01 SELECT * FROM (SELECT P.* FROM PASAJERO P WHERE P.IDPAIS='0004') X

65

Base de Datos – 2016 II

UNASAM – INGENIERÍA DE SISTEMAS E INFORMÁTICA

 02 SELECT X.IDPASAJERO,X.NOMBRES,X.EMAIL FROM (SELECT P.* FROM PASAJERO P WHERE P.IDPAIS='0004') X  03

SELECT X.IDPASAJERO,X.NOMBRES,X.EMAIL FROM (SELECT P.* FROM PASAJERO P WHERE P.IDPAIS=(SELECT PA.IDPAIS FROM PAIS PA WHERE PA.PAIS='ECUADOR')) X

66

Base de Datos – 2016 II