Reference no: EM13336158

SQL query

As a way to demonstrate success, you are to implement a series of queries on the database to reflect the business logic of Best Home real estate.

Query-1:  List all the rental properties  that the asked prices are the same as the rent prices to show:

• The property type
• The property address
• The asked price
• The actual rent price

Query-2:  List all the sale properties that have been available for 6 months but not sold yet to show:

• The property type
• The property address
• The property sale price
• The property available date 2

• The property manager's name

Query-3:  List the properties that were sold in a particular suburb e.g. 'The range' to show:

• The buyer name
• The property manager who sold the property
• The property type
• The property address
• The asked price
• The deal price
• The date when the property was sold

Note: the suburb name can be hardcoded in your query.

Query-4:  List the contact details of the solicitor who involved  in a sold property of a particular address, e.g. '22 George Street' to show:

• The property type
• The property address
• Solicitor name
• All contact details of the solicitor

Note: the property address can be hardcoded in your query.
Query-5:  List all rent properties which have 2 or more  applications to show:

• The property ID
• The property type
• The property address
• The weekly rent
• The property manager's name

Query-6:  An owner wants to complain to a higher level officer about the management of his/her  rent property. Find the supervisor of the property manager, who is currently looking after the property to show:

• The property type
• The property address 3

• The owner name
• The property manager's name
• The supervisor's name of the property manager

Query-7:  List all property managers in descendent order of the numbers of properties that they have been looking after to show:

• The property type
• The property address
• The property status
• The property manager's name

3.  Query Report
Suppose that a property manager will contact the owner of a sale property to talk about the current offers for the property. Create a report to sort the offers in descending order to show:

• The property type
• The property address
• The property sale prices
• The offered price
• The name and his/her contact details of the customer who gave the offer
• The name and his/her contact details of the owner of the property

Note:  You are to create the queries manually by using the SQL view of MS Access query builder to demonstrate your competency in SQL. Failing to do so may result in losing marks.