Download Desarrollo de los ejercicios del capítulo 7 en Microsoft Office Access

UNIVERSIDAD TÉCNICA PARTICULAR DE
LOJA
La Universidad Católica de Loja
CIENCIAS DE LA COMPUTACIÓN
QUINTO CICLO
DESARROLLO DE LOS EJERCICIOS DEL
CAPÍTULO 7
PROFESOR:
Ing. Nelson Piedra
AUTORA:
Raquel Luzuriaga
LOJA – ECUADOR
BASE TEÓRICA
Mediante el desarrollo de los siguientes ejercicios se ilustrará la utilidad de QBE (QueyBy-Example, consulta mediante ejemplo) utilizando el SGDB Microsoft Access 2003.
QBE representa una técnica visual para acceder a los datos de una base de datos
utilizando plantillas de consulta para representar la repuesta a una consulta.
QBE fue desarrollado originalmente por IBM en la década de 1970 para ayudar a los
usuarios a extraer datos de una base de datos. La utilidad QBE de Office Acess resulta
fácil de utilizar y tiene capacidades bastante potentes, por ejemplo podemos utilizarla
para realizar peguntas acerca de los datos contenidos en una o más tablas y para
especificar los campos que queremos que aparezcan en la respuesta.
Cuando se crea una base de datos utilizando Microsoft Office Access, se muestra la
ventana DataBase que presenta los objetos existentes en la base de datos por ejemplo
tablas, formularios, consultas e informes.
Para realizar una pregunta acerca de los datos contenidos en la base de datos de
DreamHome, tenemos que diseñar una consulta que indique a Microsoft Office Access
qué datos extraer.
A continuación describiré brevemente cada una de las consultas que se puede realizar en
Microsoft Office Access:
Consulta de Selección.- Realiza una pregunta o define un conjunto de criterios
acerca de los datos contenidos en una o más tablas.
Consulta de Totalización (agregación).- Realiza una serie de cálculos sobre
grupos de registros.
Consulta Paramétrica.- Muestra uno o más cuadros de diálogo predefinidos en los
que usuario puede introducir los valores de los parámetros.
Consulta de localización de correspondencias.- Localiza registros duplicados en
única tabla.
Consulta de localización de no correspondencias.- Localiza registros diferentes
en tablas relacionadas.
Consulta Matricial.- Permite resumir y presentar grandes cantidades de datos en
un formato compacto de hoja de cálculo.
Consulta de Autobúsqueda.- Rellena automáticamente ciertos valores de campo
para un nuevo registro.
Consulta de acción (incluyendo consultas de borrado, adición, actualización y
creación de tablas).- Realiza Cambios a muchos registros en una única operación.
Dichos cambios incluyen la capacidad de borrar, agregar o modificar los registros de
una tabla, así como crear una nueva tabla.
Consultas SQL (incluyendo consultas de unión, de paso, de definición de datos
y subconsultas).- Se utilizan para modificar las consultas descritas anteriormente y
para establecer las propiedades de formularios e informes. Deben emplearse para
crear consultas específicas de SQL, como consultas de unión, consultas de
definición de datos, subconsultas y consultas de paso. Las consultas de paso envían
comandos a una base de datos SQL, como por ejemplo Microsoft o Sybase SQL
Server.
DESARROLLO DE LOS EJERCICIOS
7.1 . Cree las tablas de ejemplo del caso de estudio de DreamHome que se
muestran en la Figura 3.3 y lleve a cabo los ejercicios ilustrados en este capítulo,
usando (siempre que sea posible) la función QBE de su SGBD.
Luego de crear las tablas se puede observar las siguientes relaciones:
7.2. Cree las siguientes consultas adicionales QBE de selección para las tablas de
ejemplo del caso de estudio de DreamHome, usando (siempre que sea posible) la
función QBE de su SGBD.
(a) Extraiga el número de sucursal y la dirección de todas las sucursales.
Sentencias SQL:
SELECT PropertyForRent.staffNo, PropertyForRent.branchNo,
PropertyForRent.city, PropertyForRent.street
FROM PropertyForRent;
Cuadrícula QBE:
Hoja de datos resultante:
(b) Extraiga el número de empleado, el puesto y el salario para todos los empleados
que trabajen en la sucursal B003.
Sentencias SQL:
SELECT Staff.staffNo, Staff.position, Staff.salary
FROM Staff
WHERE (((Staff.branchNo)="B003"));
Cuadrícula QBE:
Hoja de datos resultante:
(c) Extraiga los detalles de todos los apartamentos (flat) situados en Glasgow.
Sentencias SQL:
SELECT PropertyForRent.propertyNo, PropertyForRent.street,
PropertyForRent.city, PropertyForRent.postcode, PropertyForRent.type,
PropertyForRent.rooms, PropertyForRent.rent, PropertyForRent.ownerNo,
PropertyForRent.staffNo, PropertyForRent.branchNo
FROM PropertyForRent
WHERE (((PropertyForRent.city)="Glasgow") AND
((PropertyForRent.type)="Flat")) OR (((PropertyForRent.city)="Glasgow")
AND ((PropertyForRent.type)="Flat"));
Cuadrícula QBE:
Hoja de datos resultante:
(d) Extraiga los detalles de todos los empleados del sexo femenino que tenga más
de 25 años.
Sentencias SQL:
SELECT Staff.staffNo, Staff.fName, Staff.iName, Staff.position, Staff.sex,
Staff.DOB, Staff.salary, Staff.branchNo
FROM Staff
WHERE (((Staff.sex)="F") AND ("DOB">(2007-25)));
Cuadrícula QBE:
Hoja de datos resultante:
(e) Extraiga el nombre completo y el teléfono de todos los clientes que hayan
visitado algún apartamento en Glasgow.
Sentencias SQL:
SELECT Client.fName, Client.iName, Client.telNo, PropertyForRent.city
FROM PropertyForRent INNER JOIN Client ON PropertyForRent.Id =
Client.Id
WHERE (((PropertyForRent.city)="Glasgow"));
Cuadrícula QBE:
Hoja de datos resultante:
(f) Extraiga el número total de inmuebles, clasificados según el tipo de inmuebles.
Sentencias SQL:
SELECT PropertyForRent.propertyNo, PropertyForRent.street,
PropertyForRent.city, PropertyForRent.postcode, PropertyForRent.type,
PropertyForRent.rooms, PropertyForRent.rent, PropertyForRent.ownerNo,
PropertyForRent.staffNo, PropertyForRent.branchNo
FROM PropertyForRent
ORDER BY PropertyForRent.type DESC;
Cuadrícula QBE:
Hoja de datos resultante:
(g) Extraiga el número total de empleados que trabajan en cada sucursal,
ordenando el listado se el número de sucursal.
Sentencias SQL:
SELECT PropertyForRent.staffNo, PropertyForRent.branchNo
FROM PropertyForRent
ORDER BY PropertyForRent.branchNo DESC;
Cuadrícula QBE:
Hoja de datos resultante:
7.3. Cree las siguientes consultas QBE avanzadas para las tablas de ejemplo en el
caso de estudio DreamHome, utilizando (siempre que sea posible) la función QBE
de su SGBD.
(a) Cree una consulta paramétrica que solicite un número de inmueble y luego
muestre los detalles de dicho inmueble.
Sentencias SQL:
SELECT PropertyForRent.propertyNo, PropertyForRent.street,
PropertyForRent.city, PropertyForRent.postcode, PropertyForRent.type,
PropertyForRent.rooms, PropertyForRent.rent, PropertyForRent.ownerNo,
PropertyForRent.staffNo, PropertyForRent.branchNo
FROM PropertyForRent
WHERE (((PropertyForRent.propertyNo)=[Enter PropertyForRent]));
Cuadrícula QBE:
Cuadro de diálogo para ingresar el número de inmueble
Hoja de datos resultante:
(b) Cree una consulta paramétrica que solicite el nombre y apellido de un
empleado y a continuación muestren los detalles de los inmuebles de los que ese
empleado es responsable.
Sentencias SQL:
SELECT PropertyForRent.propertyNo, PropertyForRent.street,
PropertyForRent.city, PropertyForRent.postcode, PropertyForRent.type,
PropertyForRent.rooms, PropertyForRent.rent, PropertyForRent.ownerNo,
PropertyForRent.staffNo, PropertyForRent.branchNo, PrivateOwner.fName,
PrivateOwner.iName
FROM PropertyForRent INNER JOIN PrivateOwner ON PropertyForRent.Id
= PrivateOwner.Id
WHERE (((PrivateOwner.fName)=[Enter fName]) AND
((PrivateOwner.iName)=[Enter iName]));
Cuadrícula QBE:
Cuadro de diálogo que pide e nombre y apellido
Hoja de datos resultante:
(c) Añada más registros a la tabla PropertyForRent para reflejar el hecho de que
los propietarios ‘Carol Farrel’ y ‘Tony Shaw’ poseen ahora numerosos inmuebles
en diversas ciudades. Cree una consulta de selección para mostrar, para cada
propietario, el número de inmuebles que posee cada ciudad. Ahora, convierta la
consulta de selección en una consulta matricial y compruebe si la hoja de datos
resultante es más o menos útil para comparar el número de inmuebles que cada
propietario posee en cada ciudad.
Consulta de selección para mostrar, para cada propietario, el número de
inmuebles que posee en cada ciudad.
Sentencias SQL:
SELECT PropertyForRent.propertyNo, PropertyForRent.street,
PropertyForRent.city, PropertyForRent.postcode, PropertyForRent.type,
PropertyForRent.rooms, PropertyForRent.rent, PropertyForRent.ownerNo,
PropertyForRent.staffNo, PropertyForRent.branchNo
FROM PropertyForRent
ORDER BY PropertyForRent.city DESC;
Cuadrícula QBE:
Hoja de datos resultante:
Consulta matricial
Instrucción SQL
TRANSFORM Avg(PropertyForRent.rooms) AS PromedioDerooms
SELECT PropertyForRent.propertyNo, PropertyForRent.street,
PropertyForRent.city, PropertyForRent.postcode, PropertyForRent.rent,
PropertyForRent.ownerNo, PropertyForRent.staffNo,
PropertyForRent.branchNo
FROM PropertyForRent
GROUP BY PropertyForRent.propertyNo, PropertyForRent.street,
PropertyForRent.city, PropertyForRent.postcode, PropertyForRent.rent,
PropertyForRent.ownerNo, PropertyForRent.staffNo,
PropertyForRent.branchNo
ORDER BY PropertyForRent.city DESC
PIVOT PropertyForRent.type;
Cuadrícula QBE
Hoja de Datos Resultante
Esta hoja resultante nos permite ver e número de inmuebles que cada propietario posee
por ciudad.
(e) Utilice una consulta de localización de no correspondencias para identificar los
empleados que tienen ningún inmueble asignado.
Instrucción SQL:
SELECT PrivateOwner.fName, PrivateOwner.iName, PrivateOwner.address,
PrivateOwner.telNo
FROM PropertyForRent INNER JOIN PrivateOwner ON PropertyForRent.Id
= PrivateOwner.Id
WHERE (((PropertyForRent.ownerNo) Is Null));
Cuadrícula QBE:
Hoja de datos resultantes:
Esto quiere decir que todos los empleados tienen un inmueble asignado.
7.4. Utilice consultas de acción para llevar a cabo las siguientes tareas sobre las
tablas de ejemplo del de estudio de DreamHome, empleando (siempre que sea
posible) la función QBE de su SGBD.
(a) Cree una versión reducida de la tabla PropertyForRent denominada
PropertyGlasgow, que tenga los campos propertyNo, street, postcode y type de la
tabla original y contenga únicamente los detalles de los inmuebles situados en
Glasgow.
Instrucción SQL:
SELECT PropertyForRent.propertyNo, PropertyForRent.street,
PropertyForRent.postcode, PropertyForRent.type INTO PropertyGlasgow
FROM PropertyForRent;
Cuadrícula QBE:
Mensajes de Advertencia
Hoja de Datos Resultantes
(b) Elimine todos los registros de visitas de inmuebles que no tengan ningún dato
en el campo comment.
Instrucción SQL
DELETE Viewing.*, Viewing.comment
FROM Viewing
WHERE (((Viewing.comment) Is Null));
Cuadrícula QBE
Mensaje de Advertencia
Hoja de datos de la tabla donde se eliminaron los registros que no tienen ningún
comentario
(c) Actualice el salario de todos los empleados, salvo de los gerentes (Manager), en
un 12,5%.
Instrucción SQL:
UPDATE Staff SET Staff.salary = [salary]*0.125;
Cuadrícula QBE:
Mensaje de Advertencia
(d) Cree una tabla denominada NewClient que contenga los detalles de nuevos
clientes. Añada estos datos a la tabla Client original.
Instrucción SQL:
INSERT INTO Client ( clientNo, fName, iName, telNo, prefType, maxRent )
SELECT NewClient.clientNo, NewClient.fName, NewClient.iName, NewClient.telNo,
NewClient.prefType, NewClient.maxRent
FROM NewClient;
Mensaje de Advertencia
Hoja de Datos de la Tabla después de insertar los nuevos registros
7.5 Utilizando las tablas de ejemplo del caso de estudio de DreamHome, cree
consultas QBE equivalentes para los ejemplos de SQL dados e el Capítulo 5.
5.1 Generar un listado con todos los detalles de todos los miembros del personal
Instrucción SQL
SELECT Staff.staffNo, Staff.fName, Staff.iName, Staff.position,
Staff.sex, Staff.DOB, Staff.salary, Staff.branchNo
FROM Staff;
Cuadricula QBE
Hoja de datos resultante
5.2 Generar una lista con os salarios de todos los empleados e la que sólo se
muestre el número de empleado, el nombre, el apellido y los datos salariales
Instrucción SQL
SELECT Staff.staffNo, Staff.fName, Staff.iName, Staff.salary
FROM Staff;
Cuadricula QBE
Hoja de datos resultante
5.3 Generar un listado con los números de inmueble de todos los inmuebles que
hayan sido visitados.
Instrucción SQL
SELECT Viewing.propertyNo
FROM Viewing;
Cuadricula QBE
Hoja de datos resultante
5.8 Generar un listado con todos los gerentes y supervisores
Instrucción SQL
SELECT Staff.staffNo, Staff.fName, Staff.iName, Staff.position
FROM Staff
WHERE (((Staff.position)='Manager')) OR
(((Staff.position)='Supervisor'));
Cuadricula QBE
Hoja de datos resultante