Estate Agency Q2

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 to find the Forename, Surname and telephone numbers of all buyers who have viewed properties with at 3 or more bedrooms and an asking price of strictly less than 400,000.