BASE DE DATOS EN ACCESS

Hola a todos quisiera aportar con uno de los trabajos de base de datos que realice en access, son unas consultas utilizando como libro base SISTEMAS DE BASE DE DATOS, Un enfoque práctico para el diseño, implementación y gestión, cuarta edición, de Thomas M. Conolly y Carolyn E. Begg, de donde se resulve los ejercicios del 7.1 al 7.5.; la base de datos usada es la DreamHouse. Espero poderles ayudar con alguna cosita en sus trabajos, los realice en Access 2007, espero sus aportes y comentarios. En la parte izquierda pueden ver los informes de las tablas y de sus consultas.

7.1 Cree las tablas de ejemplo del caso de estudio de DreamHouse y lleve acabo los siguientes ejercicios usando la función QBE

Las tablas son las siguientes:

7.2 Cree las siguientes consultas

Para poder realizarlas utilice el:

a) Extraiga el número de sucursal y la dirección de todas las sucursales

Resultado:

Vista SQL:

SELECT Branch.branchNo, Branch.street

FROM Branch;

b) Extraiga el número de empleado, el puesto y el salario para todos los empleados que trabajen en la sucursal B003

Resultado:

Vista SQL:

SELECT Staff.staffNo, Staff.position, Staff.salary

FROM Staff

WHERE (((Staff.branchNo)="B003"));

c) Extraiga los detalles de todos los apartamentos Flat situados en Glasgow

Resultado:

Vista SQL:

SELECT PropertyForRent.propertyNo, PropertyForRent.street, PropertyForRent.postcode, PropertyForRent.rooms, PropertyForRent.rent, PropertyForRent.ownerNo, PropertyForRent.staffNo, PropertyForRent.branchNo

FROM PropertyForRent

WHERE (((PropertyForRent.city)="Glasgow") AND ((PropertyForRent.type)="Flat"));

d) Extraiga los detalles de todos los empleados del sexo femenino que tenga mas de 25 años

Resultado:

Vista SQL:

SELECT Staff.staffNo, Staff.fName, Staff.iName, Staff.position, Staff.sex, Staff.DOB, Staff.salary, Staff.branchNo, Now()-[Staff].[DOB] AS Edad

FROM Staff

GROUP BY Staff.staffNo, Staff.fName, Staff.iName, Staff.position, Staff.sex, Staff.DOB, Staff.salary, Staff.branchNo, Now()-[Staff].[DOB]

HAVING (((Staff.sex)="F"));

e) Extraiga el nombre completo y el teléfono de todos los clientes que hayan visitado algún apartamento en Glasgow

Resultado:

Vista SQL:

SELECT Client.fName, Client.iName, Client.telNo

FROM (Client INNER JOIN Viewing ON Client.clientNo = Viewing.clientNo) INNER JOIN PropertyForRent ON Viewing.propertyNo = PropertyForRent.propertyNo

WHERE (((PropertyForRent.city)="Glasgow"));

f) Extraiga el número total de inmuebles, clasificados según el tipo de inmuebles

Resultado:

Vista SQL:

SELECT PropertyForRent.type AS TipoInmueble, Count(PropertyForRent.type) AS ContadorTipo

FROM PropertyForRent

GROUP BY PropertyForRent.type;

g) Extraiga el número total de empleados que trabajen en cada sucursal, ordenando el listado según el número de sucursal

Resultado:

Vista SQL:

SELECT Staff.branchNo AS Sucursal, Count(Staff.branchNo) AS NumeroEmpleado

FROM Staff

GROUP BY Staff.branchNo

ORDER BY Staff.branchNo;

7.3 Cree las siguientes consultas QBE avanzadas

Para poder realizarlas utilice el :

esta imagen es la del asistente que les indica paso a paso como realizar las consultas de este tipo.

a) Cree una consulta paramétrica que solicite un número de inmueble y luego muestre los detalles de dicho inmueble.

Resultado:

La ventana anterior aparece ya que es una consulta paramétrica

Vista SQL:

SELECT PropertyForRent.street, PropertyForRent.city, PropertyForRent.postcode, PropertyForRent.type, PropertyForRent.rooms, PropertyForRent.rent, PropertyForRent.ownerNo, PropertyForRent.staffNo, PropertyForRent.branchNo

FROM PropertyForRent

WHERE (((PropertyForRent.propertyNo)=[Ingrese el numero del inmueble para mostrar la informacion]));

b) Cree una consulta paramétrica que solicite el nombre y el apellido de un empleado y a continuación muestre los detalles de los inmuebles de los que ese empleado es responsable

Resultado:

Aqui son dos parámetros los que se pide por eso apareceran esa dos ventanas

Vista SQL:

SELECT Staff.fName, Staff.iName, PropertyForRent.propertyNo, PropertyForRent.street, PropertyForRent.city, PropertyForRent.postcode, PropertyForRent.type, PropertyForRent.rooms, PropertyForRent.rent, PropertyForRent.ownerNo

FROM Staff INNER JOIN PropertyForRent ON Staff.staffNo = PropertyForRent.staffNo

WHERE (((Staff.fName)=[ingrese el nombre del empleado]) AND ((Staff.iName)=[Ingrese el apellido del empleado]));

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 en 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.

Resultado:

Vista SQL:

TRANSFORM Count(PropertyForRent.staffNo) AS CuentaDestaffNo

SELECT PropertyForRent.city

FROM Staff INNER JOIN PropertyForRent ON Staff.staffNo = PropertyForRent.staffNo

GROUP BY PropertyForRent.city

ORDER BY Staff.staffNo

PIVOT Staff.staffNo;

d) Introduzca un error en su tabla Staff añadiendo un registro adicional para el empleado denominado ‘David Ford’ con un nuevo número de empleado. Utilice una consulta de localización de duplicados para identificar este error.

Resultado:

Vista SQL:

SELECT First(Staff.fName) AS fNameCampo, First(Staff.iName) AS iNameCampo, First(Staff.position) AS positionCampo, First(Staff.branchNo) AS branchNoCampo, Count(Staff.fName) AS NúmeroDeDuplicados

FROM Staff

GROUP BY Staff.fName, Staff.iName, Staff.position, Staff.branchNo

HAVING (((Count(Staff.fName))>1) AND ((Count(Staff.branchNo))>1));

e) Utilice una consulta de localización de no correspondencias para identificar los empleados que no tienen ningún inmueble asignado.

Resultado:

Vista SQL:

SELECT Staff.staffNo, Staff.fName, Staff.iName

FROM Staff LEFT JOIN PropertyForRent ON Staff.[staffNo] = PropertyForRent.[staffNo]

WHERE (((PropertyForRent.staffNo) Is Null));

f) Cree una consulta de autobúsqueda que rellene los detalles de un propietario cuando se introduce un nuevo registro inmueble en la tabla PropertyForRent y el propietario del inmueble ya existe en la base de datos

Resultado:

Vista SQL:

SELECT PropertyForRent.ownerNo, PropertyForRent.propertyNo, PropertyForRent.street, PropertyForRent.city, PropertyForRent.postcode, PropertyForRent.type, PropertyForRent.rooms, PropertyForRent.rent, PropertyForRent.staffNo, PropertyForRent.branchNo, PrivateOwner.fName, PrivateOwner.iName, PrivateOwner.address, PrivateOwner.telNo

FROM PrivateOwner INNER JOIN PropertyForRent ON PrivateOwner.ownerNo = PropertyForRent.ownerNo;

7.4 Utilice consultas de acción para las siguientes tareas:

Las tareas de acción:

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

Resultado:

Vista SQL:

SELECT PropertyForRent.propertyNo, PropertyForRent.street, PropertyForRent.postcode, PropertyForRent.type INTO PropertyGlassgow

FROM PropertyForRent

WHERE (((PropertyForRent.city)="Glasgow"));

b) Elimine todos lo registros de visitas de inmuebles que no tengan ningún dato en el campo comment

Resultado:

El resultado es un mensaje en la que confirma la eliminación

Vista SQL:

DELETE Viewing.*, Viewing.Id, Viewing.propertyNo, Viewing.viewDate, Viewing.comment, Viewing.clientNo

FROM Viewing

WHERE (((Viewing.comment) Is Null));

c) Actualice el salario de todos los empleados, salvo de los gerentes Manager en un 12.5%

Resultado

Vista SQL:

UPDATE Staff SET Staff.salary = [salary]*0.125

WHERE (((Staff.position)<>"Manager"));

d) Cree una tabla denominada NewClent que contenga los detalles de nuevos clientes. Añada estos datos a la tabla client original.

Resultado

Vista SQL:

INSERT INTO Client ( clientNo, fName, telNo, prefType, maxRent, iName )

SELECT [NuevosClientesTabla].[clientNo] AS numero, [NuevosClientesTabla].[fName] AS nombre, [NuevosClientesTabla].[telNo] AS telefono, [NuevosClientesTabla].[prefType] AS tipo, [NuevosClientesTabla].[maxRent] AS renta, [NuevosClientesTabla].[iName] AS apellido

FROM NuevosClientesTabla;