COMP3311 20T3 The University of New South Wales Database Systems
COMP3311 Database Systems
20T3
[Instructions] [Notes] [Database] [Course Website] [Cheat Sheets]
[Q1] [Q2] [Q3] [Q4] [Q5] [Q6] [Q7] [Q8] [Q9] [Q10] [Q11] [Q12]
Question 5 (9 marks)
Write a PLpgSQL function that takes the name of a team and returns a single table containing the number of red and yellow cards awarded against players on that team. The function takes a text argument and returns a tuple of type RedYellow.
Note that some teams have had no red cards awarded against them. A zero should appear in the nreds attribute for such teams.
If the function parameter is not the name of a team, return a tuple (NULL,NULL).
Instructions:
Work on this question in the work/q5/ directory
Define the function q5(…) in the file q5.sql
Also, place any additional views used by q5 in this file Additional views must be placed before the q5 definition
Load it into your database using: psql footy -f q5.sql Sample output is in the file: tests/*.expected
Test it via: select * from q5(‘Australia’);
Check it using: sh check q5 in the q5 directory
Submit it using: give cs3311 sample_q5 q5.sql or Webcms3
End of Question
create type RedYellow as (nreds integer, nyellows integer);
create or replace function Q5(_team text) returns RedYellow …