Question 3 (5 marks)
Write an SQL view that gives the cheapest unsold house(s).
Define the view as q3(id,price,street,suburb),
where the attributes are:
- id = the property ID
- price = list price of the property
- street = street address where property located
"streetNum streetName streetType" (e.g "25 Smith Street")
- suburb = suburb where property located
The output should be ordered by property ID.
What the output should look like:
property=# select * from q3;
id | price | street | suburb
-------+---------+---------------------+-----------
46083 | 1742000 | 86 Leonard Avenue | Kingsford
46634 | 1742000 | 88 Broadbent Street | Kingsford
(2 rows)
Instructions:
- Work on this question in the work/q3/ directory
- Define the view q3 in the file q3.sql
- You can make use of any views/functions from the previous questions.
- Also, place any additional views used by q3 in this file
- Additional views must be placed before the q3 definition
- Load it into your database using: psql property -f q3.sql
- Sample output is in the file: tests/01.expected
- Check it using: sh check q3 in the q3 directory
- Submit it using: give cs3311 exam_q3 q3.sql or Webcms3