Question

Query #4 Please

Views PROPERTY [pid, stno, stname, sttype, suburb, state, postcode, carspaces] Write the following queries in SQL in your PROPERTY database: SHAREDPROPERTY unitno,pid, proptype] AGENCY [agencylicence, agencyname, website, phone] 1. List all townhouses sold in the suburb Annandale in 2016 2. Find the suburbs that have not had any sales since 2017 BRANCH agencv, brsuburb, email, phonel Find the total number of apartments available for rent on Parramatta Road Which street in the suburb Camperdown has the highest average rent 5. 3. AGENT [agentlicence, name, email, mobile, agencyl 4. What is the total value of properties sold by Poh Ling from Trusted Real Estate LISTING [pid agent, datelisted, dateclose, type, price] since 2014 SHAREDLISTING [unitno pid, agent, sdatelisted, dateclose, type, price] 6. Which suburb's properties have always been sold in less than 4 weeks 7. What is the average number of car spaces available for houses on Pyrmont Bridge Road 8. Which suburb(s) has had the highest increase in average rent between 2014 SHAREDPROPERTY pid references PROPERTY pid BRANCH.agency references AGENCY.agencylicence and 2016 AGENT.agency references AGENCY.agencylicence (Write at least two of the following challenging queries) LISTING.pid references PROPERTY.pid LISTING.agent references AGENT.agentlicence List the properties that have been sold by real estate agencies that are located in the same suburb as the property Who is the most successful real estate agent in 2018. (Hint: who has sold and rented properties of the greatest value) List the agencies that have property listings in every suburb 1. SHAREDLISTING.(unitno, pid) references SHAREDPROPERTY(unitno, pid) 2. SHAREDLISTING.agent references AGENT.agentlicence 3. 4. Which real estate agency (or agencies) has most number of open houses available for inspection on 24 April 2018 Accordingly there is a change in one of the views (query in SQL): Which real estate agency for agencies) has most number of open houses avoilable for inspection on 24 Apnil 2018 Note: Be sure to have sample data that can generate 'some result for these queries You will not be assessed on the correctness of the data, but on the correctness of your query. You can build your queries progressively by building views on views. is now changed to Which real estate agency for agencies) has most number of houses available for rent on 24 Apri 2018 The schema will be provided after the submission for Part 1 has been completed

"Which street in the suburb Camperdown has the highest average rent"

in LISTING, the "type" column determines if it is "rent" or "sell"

Thankyou

Answer 1

Select stname FROM

(SELECT p.stname,AVG(l.price) AS AvgPrice From Property p INNER JOIN Listing l ON p.pid=l.pid WHERE P.suburb='Camperdown' and l.type='rent' GROUP BY p.stname) As SubQueryTable WHERE AvgPrice= (Select Max(AvgPrice) From

(Select p.stname,Avg(l.price) As AvgPrice From property p inner join listing l on p.pid=l.pid where p.suburb='Camperdown' and l.type='rent' group by p.stname) As SubQueryTable2);

To get Max avg price street we have used subquery table. And filter data with help of where clause. Please comment if you see any errors while executing query.