BDatos.Guias - Guia resulta de DB SQL PDF

Preview

Summary

Guia resulta de DB SQL...

Description

BASES DE DATOS

GUIA DE TRABAJOS PRACTICOS SQL

Ingeniería en Sistemas de Información

Facultad de Tecnología Informática

Universidad Abierta Interamericana

Profesor: Dr. Sergio Butti

Indice PRACTICA DE S.Q.L..............................................................................................................................1 Creación de Bases de Datos..................................................................................................................1 Creación de Tablas................................................................................................................................1 Creación de Vistas.................................................................................................................................1 SQL.......................................................................................................................................................1 Base de Datos de Ejemplo: Jurasic Park...............................................................................................2 Instrucciones SQL de definición y manipulación de datos...................................................................5 Lenguaje de definición de datos – ejemplos.........................................................................................5 Lenguaje de manipulación de datos - ejemplos....................................................................................5 Ejercicios A:..........................................................................................................................................6 Lenguaje de manipulación de datos - ejemplos....................................................................................7 Lenguaje de manipulación de datos – ejemplos con Subconsultas.......................................................7 Lenguaje de manipulación de datos – Actualización de Datos.............................................................8 Ejercicios B:..........................................................................................................................................8 Práctico 1 SQL......................................................................................................................................9 Práctico 2 SQL....................................................................................................................................10 Práctico 3 SQL....................................................................................................................................11

Lic. Nicolás Bocalandro - Dr. Sergio Butti

Bases de Datos - UAI

PRACTICA DE S.Q.L. Creación de Bases de Datos 1. Crear, abrir y cerrar Bases de Datos en Access Creación de Tablas 1. Qué es una Tabla? 2. Crear una tabla en Access 2.1. Presentación Diseño 2.2. Agregar campos 2.3. Tipos de Datos 2.4. Establecer la clave principal 2.5. Guardar la tabla 2.6. Ver la hoja de datos de una tabla 2.7. Agregar registros 3. Validaciones internas de la tabla 3.1. Establecer las propiedades de los campos 3.1.1. Formatos 3.1.2. Campos obligatorios 3.1.3. Reglas de validación para un campo 3.2. Establecer propiedades de la tabla 3.2.1. Reglas de validación para registros 4. Validaciones referenciales 4.1. Establecer relaciones entre tablas 4.1.1. Integridad referencial: inserción, modificación y eliminación 4.1.2. Tipos de relaciones 4.1.3. Eliminación y actualización en cascada 4.1.4. Tipos de combinaciones 4.1.5. Ver y editar relaciones 5. Crear índices 5.1. Campos a indexar 5.2. Ver y editar índices 5.3. Crear índices de múltiples campos 6. Alteración y eliminación de tablas 6.1. Cómo hacerlo? 6.2. Que implica hacerlo? Creación de Vistas 1. Que es una vista? 2. En Access vista = consulta SQL 1. Instrucciones de definición de datos: CREATE TABLE, ALTER TABLE, etc. 2. Instrucción SELECT Aprendiendo a Normalizar

3 / 18

Lic. Nicolás Bocalandro - Dr. Sergio Butti

Bases de Datos - UAI

2.1. Cláusula SELECT 2.2. Cláusula FROM 2.3. Cláusula WHERE 2.4. Cláusula ORDER BY (DESC) 2.5. Cláusula GROUP BY (HAVING) 2.6. Cláusula JOIN 2.7. Funciones agregadas COUNT, SUM, MAX, MIN, AVG, ETC. 2.8. Consultas de UNION 2.9. Subconsultas 3. Instrucciones de manipulación de datos: INSERT, DELETE, UPDATE

Aprendiendo a Normalizar

4 / 18

Lic. Nicolás Bocalandro - Dr. Sergio Butti

Bases de Datos - UAI

Base de Datos de Ejemplo: Jurasic Park La empresa Jurasic Park S.A. es la propietaria de un parque de recreación donde el público visita animales pre-históricos. Dicha empresa tiene un área (visitas pre-históricas) que se encarga de ofrecer visitas guiadas para escuelas. El área de visitas pre-históricas vende a las escuelas visitas guiadas al parque para sus alumnos. Las escuelas realizan reservas telefónicas de las visitas que van a realizar, y se les cobra el día que realizan la visita, en el momento de ingresar (según los tipos de visita que realicen). Las escuelas en el momento de realizar la reserva, informan día, hora, tipos de visita y cantidad de alumnos por visita. En una misma reserva pueden contratar distintos tipos de visitas, para distintos grados de la escuela. En el caso de ser la primera reserva que efectúa la escuela, se le pide su domicilio y teléfono(s). Jurasic Park S.A. desea desarrollar un sistema informático que le ayude en un eficiente desempeño y control del área mencionada. Necesita obtener del mismo, todos los días para la boletería del parque (en el momento en que ésta lo solicite) un listado con las visitas que se hayan reservado para el día siguiente, de manera tal que la misma sepa a quienes debe dejar entrar y cuanto cobrarles. El listado debe contener (para cada reserva del día pedido) número de reserva de visita, hora, código de la escuela, nombre de la escuela y por cada tipo de visita de la reserva: código del tipo de visita, descripción del tipo de visita, arancel por alumno del tipo de visita, cantidad de alumnos y grado(s). Al fin del día, la boletería debe informar la cantidad real de ingresantes que fueron por cada reserva. También se debe entregar todos los días un listado con las visitas reservadas para el día siguiente para el Supervisor de Guías, cuando el mismo lo solicite. El supervisor debe, en base al listado, asignar las distintas visitas a su personal a cargo. El listado debe contener (para cada visita del día pedido): número de reserva de la visita, hora, código de escuela, nombre de la escuela y por cada tipo de visita de la reserva: código de tipo de visita, descripción del tipo de visita y grado(s). Al final del día, el supervisor informa los guías que se encargaron de cada tipo de visita en cada reserva. El Gerente del Area desea poder solicitar, en cualquier momento, un listado con la cantidad de alumnos (reales) que ingresaron. Dicha información debe estar discriminada día por día para el período de tiempo que él determine, y por cada día discriminado por tipo de visita. También desea un listado donde pueda observar qué cantidad de cada uno de los grados Aprendiendo a Normalizar

5 / 18

Lic. Nicolás Bocalandro - Dr. Sergio Butti

Bases de Datos - UAI

reservaron, entre dos fechas a determinar, cada uno de los tipos de visita que existen. El sistema debe permitir, además, que el responsable de Recepción de reservas consulte quienes fueron los guías (código de guía y nombre de guía), que participaron en una reserva específica, teniendo identificado cual fue el guía responsable por cada tipo de visita de la misma, ya que puede existir una queja de alguna escuela. Por último, se desea poder realizar un mailing con el nombre, el domicilio y teléfono(s) de todas las escuelas que alguna vez realizaron una reserva. Escuela CodigoEscuel 100 105 108

NombreEscuela

Domicilio

ESC. 23 GRAL. SAN MARTIN ESC. 108 MANUEL ESC. N 37 D.F. SARMIENTO

AV. CALLAO 1234 CUCHA CUCHA 143 AV. CORRIENTES 569

Guía CodigoGuia NonbreGuia 0 No hay Guia asignado 1 PEDRO JUAREZ 2 DAMIAN GONZALEZ 3 MARCELA 4 SANDRA PEREZ Tipo Visita CodigoTipoVisita 10 20 30 40 50

Descripcion ERA PALEOZOICA ERA MESOZOICA PERIODO JURASICO PERIODO INTEGRAL

ArancelAlumno 15.00 20.00 5.00 5.00 35.00

Telefono CodigoEscuela

Telefono

100 4252-5796 100 4259-9779 105 4735-5000 Reserva NumeroReserva

1001 1002 1003

FechaReserva

05/08/98 05/08/98 06/08/98

Aprendiendo a Normalizar

HoraReserva

12:00 12:30 09:00

CodigoEscuela

105 100 108 6 / 18

Lic. Nicolás Bocalandro - Dr. Sergio Butti

1004 1005 1006

07/07/98 07/08/98 07/08/98

Aprendiendo a Normalizar

Bases de Datos - UAI

12:00 15:00 15:15

105 100 108

7 / 18

Lic. Nicolás Bocalandro - Dr. Sergio Butti

Bases de Datos - UAI

Tipo Visita por Reserva NumeroReser CodigoTipoVisi CantidadAlumnoRes CantidadAlumnoAsiste CodigoGu 1001 20 18 18 1 1001 50 23 20 2 1002 10 25 22 3 1002 30 33 27 1 1002 50 54 48 4 1003 40 24 24 2 1004 50 28 26 4 1005 50 26 25 2 1006 50 21 21 3 Grado por Visita NumeroReserva CodigoTipoVisit Grado 1001 20 1001 20 1001 50 1002 10 1002 10 1002 30 1002 30 1002 30 1002 50 1003 40 1003 40 1003 40 1004 50 1005 50 1006 50 Relaciones entre Tablas: Tabla Escuela Reserva Tipovisita Guia Tipovisita_reserva

Aprendiendo a Normalizar

2 3 5 6 7 1 2 3 5 5 6 7 7 7 7

Tabla Relacionada Reserva Tipovisita_reserva Tipovisita_reserva Tipovisita_reserva Grado_visita

Campo/s Codigoescuela Numeroreserva Codigotipovisita Codigoguia Numeroreserva Codigotipovisita

8 / 18

Lic. Nicolás Bocalandro - Dr. Sergio Butti

Bases de Datos - UAI

Instrucciones SQL de definición y manipulación de datos Para escribir instrucciones SQL se deben seguir los siguientes pasos:  Hacer click en Consultas  Hacer click en botón Nuevo  Hacer click en Vista Diseño  Hacer click en Cerrar  Hacer click en SQL  Borrar lo que aparece por defecto para escribir la nueva consulta Para ejecutar una consulta desde la vista diseño:  Una vez ingresada la instrucción SQL, hacer click en botón Ejecutar (!) Para guardar una consulta:  Cerrar consulta y seleccionar un nombre para la nueva consulta. Lenguaje de definición de datos – ejemplos CREATE TABLE escuela2 (cod_escuela short constraint clave primary key, nombre_escuela text(30), domicilio_escuela text(50) ); Resultado: crea la tabla escuela2 CREATE UNIQUE INDEX nom_esc_idx ON escuela2(nombre_escuela) WITH DISALLOW NULL; Resultado: crea un índice para escuela2 sobre el campo nombre_escuela ALTER TABLE escuela2 ADD COLUMN localidad text(20); Resultado: agrega la columna localidad en la tabla escuela2 ALTER TABLE escuela2 DROP COLUMN localidad; Resultado: elimina la columna antes creada DROP TABLE escuela2; Resultado: elimina la tabla escuela2 Lenguaje de manipulación de datos - ejemplos Aprendiendo a Normalizar

9 / 18

Lic. Nicolás Bocalandro - Dr. Sergio Butti

Bases de Datos - UAI

SELECT * FROM escuela; Resultado: selecciona todos los registros de la tabla escuela SELECT descripcion, arancelalumno FROM tipovisita ORDER BY arancelalumno DESC; Resultado: selecciona los valores de los campos descripción y arancel de la tabla tipovisita ordenando los registros por arancel de mayor a menor SELECT TOP 3 descripcion, arancelalumno FROM tipovisita ORDER BY arancelalumno DESC; Resultado: ídem anterior pero sólo los tres aranceles más altos SELECT descripcion, arancelalumno FROM tipovisita WHERE arancelalumno>=20 ORDER BY arancelalumno DESC; Resultado: ídem anterior pero sólo los aranceles mayores o iguales a $20 SELECT r.*, e.nombreescuela FROM reserva AS r, escuela AS e WHERE r.fechareserva BETWEEN #08/06/98# AND #08/08/98# AND e.codigoescuela = r.codigoescuela ORDER BY r.fechareserva, r.horareserva; Resultado: selecciona las reservas y la descripción de la escuela que la realizó, según la fecha de realización, ordenadas por fecha y hora de reserva. Ejercicios A: 1. Seleccionar las escuelas con sus teléfonos 2. Seleccionar para las reservas del día 07/08/98 los números de reservas, las escuelas que reservaron con sus descripciones, el tipo de visita con su descripción, la cantidad de alumnos reservados y el guía (nombre) asignado. 3. Idem 2, pero sin mostrar el guía, pero sí la diferencia entre alumnos asistentes y alumnos reservados. 4. Seleccionar los distintos grados que concurrieron a cada tipo de visita (incluir la descripción) de cada reserva, indicando además el número de reserva y el nombre de la escuela.

Aprendiendo a Normalizar

10 / 18

Lic. Nicolás Bocalandro - Dr. Sergio Butti

Bases de Datos - UAI

Lenguaje de manipulación de datos - ejemplos SELECT r.numeroreserva AS reserva, e.nombreescuela AS escuela, tv.descripcion AS [tipo visita], tvr.cantidadalumnoasistente*tv.arancelalumno AS recaudacion FROM reserva AS r, escuela AS e, Tipovisita_reserva AS tvr, TipoVisita AS tv WHERE e.codigoescuela = r.codigoescuela AND r.numeroreserva = tvr.numeroreserva AND tvr.codigotipovisita = tv.codigotipovisita ORDER BY tvr.cantidadalumnoasistente*tv.arancelalumno DESC; Resultado: Muestra la recaudación por cada tipo de visita da cada reserva, ordenadas de mayor a menor. SELECT r.numeroreserva AS reserva, e.nombreescuela AS escuela, SUM(tvr.cantidadalumnoasistente*tv.arancelalumno) AS recaudacion FROM reserva AS r, escuela AS e, Tipovisita_reserva AS tvr, TipoVisita AS tv WHERE e.codigoescuela = r.codigoescuela AND r.numeroreserva = tvr.numeroreserva AND tvr.codigotipovisita = tv.codigotipovisita GROUP BY r.numeroreserva, e.nombreescuela ORDER BY SUM(tvr.cantidadalumnoasistente*tv.arancelalumno) DESC; Resultado: Idem anterior, pero con la recaudación agrupando todos los tipos de visita de cada reserva. SELECT r.numeroreserva AS reserva, e.nombreescuela AS escuela, SUM(tvr.cantidadalumnoasistente*tv.arancelalumno) AS recaudacion FROM reserva AS r, escuela AS e, Tipovisita_reserva AS tvr, TipoVisita AS tv WHERE e.codigoescuela = r.codigoescuela AND r.numeroreserva = tvr.numeroreserva AND tvr.codigotipovisita = tv.codigotipovisita GROUP BY r.numeroreserva, e.nombreescuela HAVING SUM(tvr.cantidadalumnoasistente*tv.arancelalumno) > 1000 ORDER BY SUM(tvr.cantidadalumnoasistente*tv.arancelalumno) DESC; Resultado: Idem anterior, pero solamente aquellas reservas cuya recaudación es mayor a $1000. Lenguaje de manipulación de datos – ejemplos con Subconsultas SELECT descripcion FROM tipovisita WHERE codigotipovisita IN (SELECT codigotipovisita FROM grado_visita WHERE grado = 7); Resultado: Trae los nombres de los tipos de visita realizados por alumnos de 7mo. Grado. SELECT DISTINCT descripcion FROM tipovisita, grado_visita Aprendiendo a Normalizar

11 / 18

Lic. Nicolás Bocalandro - Dr. Sergio Butti

Bases de Datos - UAI

WHERE tipovisita.codigotipovisita = grado_visita.codigotipovisita AND grado = 7; Resultado: Idem anterior, pero sin utilizar subconsulta. SELECT e.* FROM escuela AS e WHERE EXISTS (SELECT t.* FROM telefono t WHERE e.codigoescuela = t.codigoescuela); Resultado: Trae los datos de las escuelas que tienen teléfonos. SELECT e.* FROM escuela AS e WHERE NOT EXISTS (SELECT t.* FROM telefono t WHERE e.codigoescuela = t.codigoescuela); Resultado: Trae los datos de las escuelas que NO tienen teléfono. SELECT g.nombreguia FROM guia AS g WHERE g.codigoguia = ANY (SELECT tvr.codigoguia FROM TipoVisita_reserva tvr WHERE tvr.cantidadalumnoasistente > 25); Resultado: Trae los nombres de los guías que guiaron a grupos de más de 25 alumnos. SELECT g.codigoguia AS codigo, g.nombreguia AS nombre FROM guia AS g WHERE g.codigoguia = ANY (SELECT tvr.codigoguia FROM tipovisita_reserva tvr WHERE tvr.cantidadalumnoasistente > 20) UNION SELECT tv.codigotipovisita, tv.descripcion FROM tipovisita AS tv WHERE tv.codigotipovisita = ANY (SELECT tvr.codigotipovisita FROM tipovisita_reserva tvr WHERE tvr.cantidadalumnoasistente > 20); Resultado: A partir del comando UNION trae los códigos y nombres de los guías que guiaron a grupos de más de 20 alumnos unidos a los tipos de visitas con más de 20 alumnos. Lenguaje de manipulación de datos – Actualización de Datos INSERT INTO guia VALUES ("5","MARTIN GIMENEZ"); INSERT INTO guia VALUES ("6","LAURA LOPEZ"); Resultado: Cada INSERT agrega un registro a la tabla GUIA. DELETE FROM guia WHERE CODIGOGUIA = 5; Resultado: Se elimina el registro agregado en el primer INSERT. UPDATE tipovisita_reserva SET codigoguia = 4 Aprendiendo a Normalizar

12 / 18

Lic. Nicolás Bocalandro - Dr. Sergio Butti

Bases de Datos - UAI

WHERE numeroreserva = 1002; Resultado: Modifica el guía asignado a las visitas de la reserva 1002. Ejercicios B: 1. Seleccionar la cantidad de veces que un grado realizó un tipo de visita, indicando la descripción de la visita 2. Realizar un ranking de cantidad de alumnos asistentes a los distintos tipos de visitas. 3. Seleccionar los datos de las escuelas que realizaron reservas por la tarde (hacerlo con y sin consulta) 4. Seleccionar los códigos y nombres de escuelas y tipos de visitas que hayan superado los 100 alumnos asistentes.

Aprendiendo a Normalizar

13 / 18

Lic. Nicolás Bocalandro - Dr. Sergio Butti

Bases de Datos - UAI

Práctico 1 SQL Dado el siguiente diagrama en el que se muestran las relaciones entre las distintas entidades que forman parte del modelo de datos, se solicita escribir las consultas en SQL que se mencionan a continuación:

1. Listar todos los socios del video club, incluyendo numero_socio, apellido_nombre, dirección y las películas reservadas desde el 01/01/2000 en adelante (código_película y fecha de reserva). 2. Listar todas las películas (código, título, código de género y para las que son o hayan sido estreno fecha desde y fecha hasta) 3. Listar la cantidad de películas por género (sin importar la cantidad de copias que haya en stock, esto es, cada código de película se cuenta una sola vez). 4. Listar todas las películas que no hayan sido alquiladas (reservadas) durante el año 2001. 5. Listar todas las películas reservadas (título y código_película) indicando la cantidad de veces que se alquiló cada una, ordenadas en forma descendente según la cantidad de veces que se alquiló. 6. Listar todas las películas reservadas para la fecha que ingrese el usuario, ordenadas por género (código de género, descripción de género, código de película, título, fecha desde y fecha hasta). 7. Listar solamente los clientes que hayan reservado alguna película desde una fecha a ingresar por el usuario, incluyendo numero_socio, apellido_nombre, código_película, y título, ordenados alfabéticamente por nombre del cliente. Aprendiendo a Normalizar

14 / 18

Lic. Nicolás Bocalandro - Dr. Sergio Butti

Bases de Datos - UAI

8. Listar todas las películas en las que actúe el actor que solicite el socio, ordenadas por la descripción del género de cada pelìcula.

Aprendiendo a Normalizar

15 / 18

Lic. Nicolás Bocalandro - Dr. Sergio Butti

Bases de Datos - UAI

Práctico 2 SQL Dada la siguiente estructura de datos, redactar las consultas SQL que respondan a los requerimientos de los siguientes puntos:

1. Listar todos los autos, incluyendo sus datos y los de las ordenes de trabajo que se les hubieran asignado desde del día 01/01/2002. 2. Listar los autos reparados por el taller, ordenados por marca (alfabético ascendente), modelo (alfabético ascendente) y número de patente. 3. Listar los repuestos utilizados en las ordenes de trabajo ingresadas desde el 01/01/2002 y hasta el 31/07/2002 inclusive, que no hayan estado cubiertos por la garantía y que hayan sido facturados antes del 31/07/...