Question

Branch -branch No - street - city - postcode Staff staffNo fName - IName - position sex - DOB - salary - branch No PropertyForRent -propertyNo -street -city - postCode -type rooms rent -ownerNo, - staffNo branchNo Client -clientNo -fName - IName -telNo - preflype - maxRent Registration clientNo -branch staffNo -date Joined Viewing - clientNo propertyNo -viewDate -comment PrivateOwner ownerNo -Name - IName - address - telNo Q1 For the information given above write Relational Algebra & SQL statements to fulfill the following requirements. 1. Show the Names and positions of the staff members who are supervisors or managers and are managing some property. Also show the details of the owners of the property. (Hint: Use Join) 2. Show the names of the owners whose properties in Glasgow were viewed by clients looking for a flat but left no comments. (Hint: Nested Query) 3. Show the details of staff members who work in a branch not located in the same city as the location of the property that they manage. (Hint: CoRelated Query) 4. Show the details of all clients and the address of the properties that they have viewed in Glasgow. (Hint: Outer Join) 5. Show the number of properties and average expected monthly rent for properties managed by staff members born after 1955. (Hint: Aggregate Function) 6. Show the number of properties managed by each staff member from every London branch. Also show the address of the branch and the name and position of the staff member. (Hint: Group By) 7. For each city show the average expected rent for different type of properties. (Hint: Group By) 8. Show the total number of properties visited by each client and the average length of comments (number of characters) left by them. (Hint: Outer Join & Group By) 9. Show the details of the clients whose max rent limit is less than the average rent of the property type they are looking for. (Hint: Aggregate Function & Condition) *Writing Relational Algebra Is must

Name: branch branch no street city postcode staff staffno fname iname position sex dob salary branch no propertyforrent propertyno street city postcode type rooms rent ownerno staffno branchno cli 23899
Uploaded: 2023-11-13T18:21:08-08:00
Duration: 8 min 8 s
Channel: Akash M
Description: branch branch no street city postcode staff staffno fname iname position sex dob salary branch no propertyforrent propertyno street city postcode type rooms rent ownerno staffno branchno cli 23899

Branch
-branch No
- street
- city
- postcode
Staff
staffNo
fName
- IName
- position
sex
- DOB
- salary
- branch No
PropertyForRent
-propertyNo
-street
-city
- postCode
-type
rooms
rent
-ownerNo,
- staffNo
branchNo
Client
-clientNo
-fName
- IName
-telNo
- preflype
- maxRent
Registration
clientNo
-branch
staffNo
-date Joined
Viewing
- clientNo
propertyNo
-viewDate
-comment
PrivateOwner
ownerNo
-Name
- IName
- address
- telNo
Q1 For the information given above write Relational Algebra & SQL statements to fulfill the following
requirements.
1. Show the Names and positions of the staff members who are supervisors or managers and are
managing some property. Also show the details of the owners of the property. (Hint: Use Join)
2. Show the names of the owners whose properties in Glasgow were viewed by clients looking for
a flat but left no comments. (Hint: Nested Query)
3. Show the details of staff members who work in a branch not located in the same city as the
location of the property that they manage. (Hint: CoRelated Query)
4. Show the details of all clients and the address of the properties that they have viewed in
Glasgow. (Hint: Outer Join)
5. Show the number of properties and average expected monthly rent for properties managed by
staff members born after 1955. (Hint: Aggregate Function)
6. Show the number of properties managed by each staff member from every London branch. Also
show the address of the branch and the name and position of the staff member. (Hint: Group
By)
7. For each city show the average expected rent for different type of properties. (Hint: Group By)
8. Show the total number of properties visited by each client and the average length of comments
(number of characters) left by them. (Hint: Outer Join & Group By)
9. Show the details of the clients whose max rent limit is less than the average rent of the property
type they are looking for. (Hint: Aggregate Function & Condition)
*Writing Relational Algebra Is must

Added by Brian B.

Question

Please give Ace some feedback