Question

Write the following queries in SQL in your PROPERTY database:

1. List the properties that have been sold by real estate agencies that are located in the same suburb as the property

2. Who is the most successful real estate agent in 2018. (Hint: who has sold and rented properties of the greatest value)

3. List the agencies that have property listings in every suburb

4. Which real estate agency (or agencies) has most number of houses available for rent on 24 April 2018

Property Database is as follows:

PROPERTY [pid, stno, stname, sttype, suburb, state, postcode, carspaces] SHAREDPROPERTY [unitno, pid, proptype] me, website, phone BRANCH [agency, brsuburb, email, phone] AGENT [agentlicence, name, email, mobile, agency] LISTING [pid, agent, datelisted, dateclose, type, price] SHAREDLISTING [unitno, pid, agent, sdatelisted, dateclose, type, price] SHAREDPROPERTY.pid references PROPERTY.pid BRANCH.agency references AGENCY.agencylicence AGENT.agency references AGENCY.agencylicence LISTING.agent references AGENT.agentlicence SHAREDLISTING.(unitno, pid) references SHAREDPROPERTY (unitno, pid) SHAREDLISTING.agent references AGENT.agentlicence

Answer 1

--1 To find properties that are sold in same suburb we need to join suburb of property and branch table. below is query.

Select p.* From property p inner join branch b on p.suburb = b.brsuburb;

--2 to get this solution we can use subquery to filter data and top condition to limit data.

select * from agent where agentlicence in (select top 1 agent from sharedlisting group by agent order by sum(price) desc);

in above query subquery get only one record due to top and highest seller due to order by sum(price)

--3

select Agencyname from agency where agencylicence in ( select agency from branch group by agency having count (brsuburb) = (select count(suburb) from property));

--4

select agencyname from agency where agencylicence in (select agency from agent where agentlicence in ( select top 1 agent from sharedlisting where dateclose is null and type='rent' and sdatelisted='2018-04-18' group by agent having count(pid)=max(count(pid))));