UNIVERSIDAD TÉCNICA PARTICULAR DE LOJA

TRABAJO DE BASE DE DATOS

QBE

POR:

o Maria José Espinosa

CICLO: Quinto ciclo de Sistemas Informáticos

TUTOR: Ing. Nelson Piedra

LOJA –ECUADOR

2007-2008

EJERCICIOS CAPITULO 7

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

7.2 Cree las siguientes consultas

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

SQL

SELECT Branch.branchNo, Branch.streetFROM Branch;

Vista de Diseño

Resultado

Consulta1branchNo streetB005 22 Deer RdB007 16 Argyll St

Consulta1branchNo streetB003 163 Main StB004 32 Manse RdB002 56 Clover Dr

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

SQL

SELECT Staff.staffNo, Staff.position, Staff.salaryFROM Branch INNER JOIN Staff ON Branch.branchNo=Staff.branchNoWHERE (((Branch.branchNo) Like 'B003'));

Vista de diseño

Resultado

Consulta2staffNo position salarySG5 Manager 24000SG14 Supervisor 18000

Consulta2staffNo position salarySG37 Assistant 12000

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

SQL

SELECT PropertyForRent.propertyNo, PropertyForRent.street, PropertyForRent.postcode, PropertyForRent.rooms, PropertyForRent.rentFROM PropertyForRentWHERE (((PropertyForRent.city) Like 'Glasgow') AND ((PropertyForRent.type) Like 'Flat'));

Vista de Diseño

Resultado

Consulta3propertyNo street postcode rooms rentPG4 6 Lawrence St G11 9QX 3 350PG36 2 Mano Rd G32 4QX 3 375PG16 5 Novar Dr G12 9AX 4 450

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

SQL

SELECT Staff.staffNo, Staff.fName, Staff.lName, Staff.position, Staff.salaryFROM StaffWHERE (((Staff.[sex]) Like 'F'))and (dob.year-getdate().year)>25;

Vista de Diseño

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

SQL

SELECT Client.fName, Client.lName, Client.telNoFROM (Client INNER JOIN Viewing ON Client.clientNo = Viewing.clientNo) INNER JOIN PropertyForRent ON Viewing.propertyNo = PropertyForRent.propertyNoWHERE (((PropertyForRent.city) Like 'Glasgow'));

Vista de Diseño

Resultado

Consulta5fName lName telNoAline Stewart 0141-848-1825John Kay 0207-7745632Aline Stewart 0141-848-1825

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

SQL

SELECT Count(PropertyForRent.type) AS Numero, PropertyForRent.typeFROM PropertyForRentGROUP BY PropertyForRent.type;

Vista de Diseño

Resultado

Consulta6Numero type

4 Flat2 House

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

SQL

SELECT count(Staff.staffno) AS Numero, Branch.branchNo

FROM Branch INNER JOIN Staff ON Branch.branchNo=Staff.branchNoGROUP BY Branch.branchNoORDER BY Branch.branchNo;

Vista de Diseño

Resultado

Consulta7Numero branchNo

3 B0032 B0051 B007

7.3 Cree las siguientes consultas QBE avanzadas

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

SQL

SELECT PropertyForRent.propertyNo, PropertyForRent.street, PropertyForRent.city, PropertyForRent.postcode, PropertyForRent.type, PropertyForRent.rentFROM PropertyForRentWHERE (((PropertyForRent.propertyNo)=[Ingrese el numero de Inmueble]));

Vista de Diseño

Resultado

Consulta8propertyNo street city postcode type rentPA14 16 Hilheld Aberdeen AB7 5SU House 650

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

SQL

SELECT Staff.fName, Staff.lName, PropertyForRent.street, PropertyForRent.city, PropertyForRent.postcode, PropertyForRent.type, PropertyForRent.rooms, PropertyForRent.rentFROM PropertyForRent INNER JOIN Staff ON PropertyForRent.staffNo=Staff.staffNoWHERE (((Staff.fName)=[Ingrese nombre de empleado]) AND ((Staff.lName)=[Ingrese apellido de empleado]));

Vista de Diseño

Resultado

Consulta9fName lName street city postcode type rooms rentMary Howe 16 Hilheld Aberdeen AB7 5SU House 6 650

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.

SQL

TRANSFORM Count(PropertyForRent.staffNo) AS CuentaDestaffNoSELECT PropertyForRent.cityFROM Staff INNER JOIN PropertyForRent ON Staff.staffNo = PropertyForRent.staffNoGROUP BY PropertyForRent.cityORDER BY Staff.staffNoPIVOT Staff.staffNo;

Vista de Diseño

Resultado

Consulta10city SA9 SG14 SG37 SL41

Aberdeen 1

Glasgow 1 2

London 1

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.

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úmeroDeDuplicadosFROM StaffGROUP BY Staff.fName, Staff.iName, Staff.position, Staff.branchNoHAVING (((Count(Staff.fName))>1) AND ((Count(Staff.branchNo))>1));

Vista de Diseño

Resultado

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

SQL

SELECT Staff.fName, Staff.lNameFROM Staff LEFT JOIN PropertyForRent ON PropertyForRent.branchNo=Staff.branchNoWHERE (((PropertyForRent.staffNo) Is Null));ç

Vista de Diseño

Resultado

Consulta12fName lNameAnn BeechDavid FordSusan Brand

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

SQL

SELECT Staff.staffNo, Staff.fName, Staff.lNameFROM Staff INNER JOIN PropertyForRent ON Staff.staffNo=PropertyForRent.staffNoWHERE (((PropertyForRent.staffNo) Is Null));

Vista de Diseño

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

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.telNoFROM PrivateOwner INNER JOIN PropertyForRent ON PrivateOwner.ownerNo = PropertyForRent.ownerNo;

Vista de Diseño

Resultado

Consulta14ownerNo

propertyNo street city postco

de type rooms

rent

staffNo

branchNo

fName

Expr1

address telNo

CO46 PA14 16 Hilheld

Aberdeen

AB7 5SU

House

6 650

SA9 B007 Joe 2 Fergus Dr, Aberdeen AB2 7SX

01224-861212

CO40 PG4 6 Lawrence St

Glasgow

G11 9QX

Flat 3 350

B003 Tina 63 Well St, Glasgow G42

0141-943-1728

CO93 PG16 5 Novar Dr

Glasgow

G12 9AX

Flat 4 450

SG14 B003 Tony 12 Park Pl, Glagow G4 0QR

0141-225-7025

CO93 PG36 2 Mano Rd

Glasgow

G32 4QX

Flat 3 375

SG37 B003 Tony 12 Park Pl, Glagow G4 0QR

0141-225-7025

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

SQL

SELECT PropertyForRent.propertyNo, PropertyForRent.street, PropertyForRent.postcode, PropertyForRent.type, PropertyForRent.rooms, PropertyForRent.rent, PropertyForRent.ownerNo, PropertyForRent.staffNo, PropertyForRent.branchN INTO PropertyGlassgowFROM PropertyForRent

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

Vista de Diseño

Resultado

Consulta15propertyNo street postcode type rooms rent ownerNo staffNo Expr1PG4 6 Lawrence St G11 9QX Flat 3 350 CO40

PG36 2 Mano Rd G32 4QX Flat 3 375 CO93 SG37

PG21 18 Dale Rd G12 House 5 600 CO87 SG37

Consulta15propertyNo street postcode type rooms rent ownerNo staffNo Expr1PG16 5 Novar Dr G12 9AX Flat 4 450 CO93 SG14

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

SQL

DELETE Viewing.*, Viewing.Id, Viewing.propertyNo, Viewing.viewDate, Viewing.comment, Viewing.clientNo, [Viewing] AS Expr1, Viewing.Id, Viewing.clientNo, Viewing.viewDate, Viewing.propertyNo, Viewing.commentFROM ViewingWHERE ((([Viewing])="Dondé") AND ((Viewing.Id)="Dondé") AND ((Viewing.clientNo)="Dondé") AND ((Viewing.viewDate)="Dondé") AND ((Viewing.propertyNo)="Dondé") AND ((Viewing.comment)="Dondé")) OR (((Viewing.comment) Is Null));

Vista de Diseño

Resultado