Assignment Information

The following relational schema (primary keys are underlined) are used by the real estate company as described mostly in Assignment 1, with one extra specification that "When a listed property is sold, it is sold by a realtor that may or may not be same as the listing realtor". The ER diagram in Assignment 2 now reflects the extra specification.

Properties(folio, civicAddress, zoning, builtYear, description)
MajorProjects(folio, year, description)
Realtors(RID, serviceName, name, contactInfo, expLevel)
Listings(listingID, folio, listedBy, listDate, status, description, askingPrice)
ClosedListings(listingID, soldBy, closingDate, purchasePrice)
Clients(email, name, otherInfo)
Leads(email, listingID, interest)

This assignment is based on the above relational schema.

Many more details, including constraints, data types, etc, are captured in the SQL definitions of these tables.

You may use the supplied table definitions to create empty tables. You will then need to populate the tables (insert sample data) yourself in order to test your SQL queries logically.

Your Tasks:

Express each of the following queries in a single SQL query against the relational schema defined above.

1. For each year there were property or properties built, list the year number and the number of properties built in that year. (Note: It goes without saying that if there was no property built in a year, that year doesn't need to appear in the result.)
2. For the listing which listed a property with the highest asking price, show its listed property's civic address, its listing date and status. (Note that there may be multiple listings with the same highest asking price marketing different properties or even the same property. Your query should show the information of them all.)
3. Find out the number of properties listed in year 2025 (i.e., their list dates are in year 2025) and their average asking price, and the number of properties sold in year 2025 (i.e., their closing dates are in year 2025) and their average purchase price. The result should be retrieved using one SQL statement and there shouldn't be any confusion how to interpret the result. (Hint: think about the sub-queries and set operations.)
4. For each realtor, show the realtor's service name and experience level, and the number of properties sold by this realtor. If the realtor never sold any property, their information must still show up in the result and their sold property number should be listed as 0.
5. For each property that was built in year 2000 or later and has more than three (3) major projects done on it, show its civic address, and sort the result according to their built year in descending order, and then the actual number of major projects also in descending order. (Note: just to see whether the ordering is right, you can include the built year and number of projects in the result although they are not required.)

How to Submit

You are expected to include all your SQL queries in a text file (preferrably with extension name .txt).

Make your queries logically correct and easy to understand. Make sure that your SQL queries can be successfully executed by Oracle database engine.

You can submit your assignment solutions using one of the following two ways:

• Submit a hard copy before the due date/time.
• Login to your VIU Learn account, find the CSCI 370 course page, click on the "Assessment" drop-down menu, click on the "Assignments" item, then click on the folder named "A5". Then you can click on the "Add a File" button to browse and upload your solution file.