COMP3311 Final Exam 20T3

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

Database Systems

Question 4 (8 marks)

Write a PLpgSQL function that reports the number of "short" and "long" songs by each group over their whole recording career. A "short" song is defined to be one whose length is strictly less that 3 minutes (180 seconds). A "long" song is defined to be one whose length is strictly greater than 6 minutes (360 seconds).

create type SongCounts as ( "group" text, nshort integer, nlong integer );

create or replace function q4() returns setof SongCounts ...

The attributes in SongCounts are as follows:

• "group" = the name of a group
• nshort = the number of short (< 3 mins) songs by this group
• nlong = the number of long (> 6 mins) songs by this group

Note that the function has no parameters, so will always produce the same result, e.g.

music=# select * from q4() order by "group";
        group         | nshort | nlong 
----------------------+--------+-------
 After the Sea        |      4 |    14
 After the Sky        |      0 |     0
 All of the Floyd     |      3 |     5
 All of the Sea       |      2 |    15
 Black Eyed Boy       |      4 |    26
 Black Eyed Nails     |      6 |    30
 Black Mountains      |      3 |     7
 Black Vanilla        |      6 |    18
 Blue Floyd           |     10 |    18
 Blue Mountains       |      4 |    11
... etc etc etc ...

Warning: you may have a different collation setting in your PostgreSQL server to the one that produced the expected results. This affects the ordering of certain characters, such as space. If you get a message that your test failed with "Different order", then your solution is most likely correct.

Instructions:

• Work on this question in the work/q4/ directory
• Define the function q4(...) in the file q4.sql
• Also, place any additional views/functions used by q4 in this file
• These must be placed before the q4 definition
• Load it into your database using:   psql music -f q4.sql
• Sample output is in the file:   tests/01.expected
• Test it via:   select * from q4() order by "group";
• Check it using:   sh check q4 in the q4 directory
• Submit it using:   give cs3311 exam_q4 q4.sql   or   Webcms3