COMP3311 Final Exam 21T3

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

Database Systems

Question 1 (4 marks)

Write an SQL view that gives information about the most recently sold properties.

Define the view as q1(date,price,type), where the attributes are:

• date = the date the property was sold
• price = the price that it sold for
• type = what kind of property

Note that while date and type are SQL keywords, it is safe to use them without double-quotes.

The output should be ordered by price. What the output should look like:

property=# select * from q1;
    date    |  price  |   type    
------------+---------+-----------
 2021-11-09 | 1816000 | Townhouse
 2021-11-09 | 1859000 | Townhouse
 2021-11-09 | 2154000 | House
 2021-11-09 | 2230000 | House
 2021-11-09 | 2665000 | House
 2021-11-09 | 2833000 | House
 2021-11-09 | 2836000 | House
(7 rows)

Instructions:

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