COMP3311 Final Exam 22T3

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

Database Systems

Question 1 (3 marks)

Write an SQL view that finds the horse (or horses) that have won the most Group 1 races.

Races are categorized into "groups". Group 1 races are the top level, most prestigious races. Then there are Group 2, Group 3 and Group 4 races. The Group of a race is indicated by the Races.level attribute. The position that a horse finishes in a race is indicated by the Runners.finished field, with a value of 1 meaning finished first.

Define the view as q1(horse).

The expected result is:

racing=# select * from q1;
   horse    
------------
 Brown Jack
(1 row)

Note that a "solution" which does something like   "select 'Brown Jack' as horse"   is worth zero marks.

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, just views
• Load it into your database using:   psql racing -f q1.sql
• Sample output is in the file:   tests/01.expected
• Test it via:   select * from q1;
• Check it using:   sh check   in the q1 directory
• Submit it using:   give cs3311 exam_q1 q1.sql  or  Webcms3