Estate Agency Q1

An estate agency makes details of the properties that is has for sale available to potential customers through a website. The details of the properties and other data that are useful to the agency are stored in a relational database.

The individual web pages about specific properties that a customer can view are generated dynamically by a program from the data in the database.

A client-server system which uses CRUD and REST is used to provide tails of properties in a web page that is being viewed in a web browser on a client computer.

Figure 3 shows the structure of the relations in the database.

Figure 3

Property(PropertyID, HouseNum, Street, Area, Postcode, Bedrooms, Bathrooms, AskingPrice, SellerID)

Seller(SellerID, Title, Forename, Surname, Telephone)

Buyer(BuyerID, Title, Forename, Surname, Telephone, DesiredArea, MinBedrooms, MaxPrice)

Viewing(BuyerID, PropertyID, ViewingDate, ViewingTime)

Sale(SaleID, PropertyID, BuyerID, SalePrice)

• The Property relation stores details of the properties that are for sale. This includes the number of bedrooms and the number of bathrooms that a property gas.
• The Seller relation stores details of people who are selling the properties.
• The Buyer relation stores details of the people who are looking to buy a property, and information about the type of property they want, including the area that they want to live in, the minimum number of bedrooms that they need in a property and the maximum price that they are prepared to pay.
• An entry is mede in the Viewing relation whenever a buyer arranges to look at a property.
• An entry is made in the Sale relation whenever a property is sold to a buyer. The SalePrice may be different to the AskingPrice for the property.

Question

Write an SQL query that will retrieve from the database the list of all properties that the buyer with BuyerID 23 might be interested in buying. The properties should:

• be in the buyers desired area
• have at least the minimum number of bedrooms the buyer requires
• cost no more than the maximum price that the buyer is prepared to pay.

The list of properties returned should only include, for each property, the following:

• the PropertyID
• the street that the property is on
• the number of bedrooms the property has
• the asking price for the property

The list should be ordered with most expensive property at the top of the list and the least expensive at the bottom.