COMP3311 Final Exam 21T3

The University of New South Wales COMP3311 Database Systems Final Exam 21T3

Database Systems

Question 2 (5 marks)

Write an SQL view that gives a list of how many unsold properties of each type are in each suburb.

Define the view as q2(suburb,ptype,nprops), where the attributes are:

• suburb = the name of a suburb
• ptype = the type of property (e.g. House)
• nprops = the number of properties of this type in this suburb

The output should be ordered by property type, and then by suburb. What the output should look like:

property=# select * from q2;
     suburb     |   ptype   | nprops 
----------------+-----------+--------
 Alexandria     | Apartment |     42
 Bondi Junction | Apartment |     41
 Coogee         | Apartment |     47
 Kingsford      | Apartment |     43
 Alexandria     | House     |     34
 Bondi Junction | House     |     38
 Coogee         | House     |     38
 Kingsford      | House     |     36
 Alexandria     | Townhouse |      9
 Bondi Junction | Townhouse |      9
 Coogee         | Townhouse |      6
 Kingsford      | Townhouse |      9
(12 rows)

Instructions:

• Work on this question in the work/q2/ directory
• Define the view q2 in the file q2.sql
• You can make use any views/functions from the previous questions.
• Also, place any additional views used by q2 in this file
• You may not use functions in answering this question
• Additional views must be placed before the q2 definition
• Load it into your database using:   psql property -f q2.sql
• Sample output is in the file:   tests/01.expected
• Check it using:   sh check q2   in the q2 directory
• Submit it using:   give cs3311 exam_q2 q2.sql  or  Webcms3